创建表
数据类型 int（×××，整数） not null，char（字符） tinyint（最小的×××） varchar （变长的字符类型）
create table xiaohu(
id int(4) not null,
name char(20) not null,
age tinyint(2) not null default '0',（不可以为空，但可以给0）
dept varchar(16) default null (可以为空)
如
mysql> create table student(
-> id int(4) not null,
-> name char(20) not null,
-> age tinyint(20) not null default '0',
-> dept varchar(16) default null
-> );
Query OK, 0 rows affected (0.01 sec)
查看见过表的语句：
show create table 表名\G；
mysql> show create table student\G;
*************************** 1. row ***************************
Table: student
Create Table: CREATE TABLE `student` (
`id` int(4) NOT NULL,
`name` char(20) NOT NULL,
`age` tinyint(20) NOT NULL DEFAULT '0',
`dept` varchar(16) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
查看表结构
desc 加表名
mysql> desc student;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(4) | NO | | NULL | |
| name | char(20) | NO | | NULL | |
| age | tinyint(20) | NO | | 0 | |
| dept | varchar(16) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.01 sec)
创建索引 提升查询速度
索引分类：
主键索引 主键列所有内容必须唯一 比如学号，准考证号
mysql> create table student(id int(4) not null AUTO_INCREMENT,递增 name char(20) not null, age tinyint(2) not null default '0', dept varchar(16) default null, primary key(id),主键索引 key index_name (name)普通索引 );
Query OK, 0 rows affected (0.01 sec)
如果在创建表的时候忘记加索引怎么办？
可以添加 语法
mysql> alter table student change id id int primary key auto_increment;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc student
-> ;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | char(20) | NO | | NULL | |
| age | tinyint(2) | NO | | 0 | |
| dept | varchar(16) | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
普通索引：
mysql> alter table student add index index_name(name); 改表 表的名字 添加 索引 索引名字（在哪个列上添加）
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc student;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | char(20) | NO | MUL | NULL | |
| age | tinyint(2) | NO | | 0 | |
| dept | varchar(16) | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
指定前N个字符创建索引
mysql> create index index_dept on student(dept(8)); 创建 索引 索引名 在 表（第几个列（前几个字符））
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
查看索引 详细
mysql> show index from student\G;
*************************** 1. row ***************************
Table: student
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: id
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
*************************** 2. row ***************************
Table: student
Non_unique: 1
Key_name: index_name
Seq_in_index: 1
Column_name: name
Collation: A
Cardinality: NULL
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
*************************** 3. row ***************************
Table: student
Non_unique: 1
Key_name: index_x
Seq_in_index: 1
Column_name: age
Collation: A
Cardinality: NULL
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
*************************** 4. row ***************************
Table: student
Non_unique: 1
Key_name: index_dept
Seq_in_index: 1
Column_name: dept
Collation: A
Cardinality: NULL
Sub_part: 8
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
4 rows in set (0.00 sec)
联合索引语法
create index index_name_dept on student(name,dept);
Table: student
Non_unique: 1
Key_name: ind_name_dept
Seq_in_index: 2
Column_name: dept
Collation: A
Cardinality: NULL
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
6 rows in set (0.00 sec)
mysql> drop index ind_name_dept on student; 删除索引 索引名 在 哪个表
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
创建联合索引前几个字符创建索引
create index ind_name_dept on student(name(8),dept(10));
到底给那些数据创建索引呢？
索引占空间，更新数据库还需要维护索引数据 写频繁读取好的语句少建立索引 十到几百行的小表不用创建索引
尽量在唯一值多的大表上建立索引
运维就是开个慢查询的语句开个监控