约束条件
mysql> desc t12;
+-------+--------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------------------+------+-----+---------+-------+
| name | char(5) | YES | | NULL | |
| level | int(3) unsigned zerofill | YES | | NULL | |
| money | int(5) unsigned zerofill | YES | | NULL | |
+-------+--------------------------+------+-----+---------+-------+
3 rows in set (0.01 sec)
其中Null(yes/no)允许/不允许为空值 key 索引信息
Default 默认值(如果用insert不给字段赋值就执行默认值)
Default默认值要根据实际情况来(比方游戏等级方面写默认值为0,那么所有游戏等级初始的时候默认为0.但如果你等级这里写名字什么不相关的那就不适合了)
例如:name不允许为空 ,level 默认值为0 ,money 默认值为0
mysql> create t4(name char(5) not null ,level int(3) zerofill default 0 ,money tinyint(2) zerofill default 0);
Query OK, 0 rows affected (0.25 sec)
mysql> desc t14;
+-------+------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------------------+------+-----+---------+-------+
| name | char(5) | NO | | NULL | |
| level | int(3) unsigned zerofill | YES | | 000 | |
| money | tinyint(2) unsigned zerofill | YES | | 00 | |
+-------+------------------------------+------+-----+---------+-------+
mysql> insert into t14(level,money) values(2.19);
ERROR 1136 (21S01): Column count doesn't match value count at row 1
如果name字段不写东西,那么将会报错(因为Null不允许为空,Default默认为空,所以name字段想要“空”的效果必须如下书写。
mysql> create table t15(
-> name char(5) not null default"", 在默认值后面加入""
-> level int(5) zerofill default 0,
-> money tinyint(2) zerofill default 0);
Query OK, 0 rows affected (0.26 sec)
结果如下
mysql> desc t15;
+-------+------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------------------+------+-----+---------+-------+
| name | char(5) | NO | | | |
| level | int(5) unsigned zerofill | YES | | 00000 | |
| money | tinyint(2) unsigned zerofill | YES | | 00 | |
+-------+------------------------------+------+-----+---------+-------+
就可以正常赋你想赋的值
mysql> insert into t15(level,money) values(1,7);
Query OK, 1 row affected (0.37 sec)
mysql> select * from t15;
+------+-------+-------+
| name | level | money |
+------+-------+-------+
| | 00001 | 07 |
+------+-------+-------+
Extra 自动增长
**********************************修改表结构*******************************************
如果你不会修改表结构那么只能像上面操作一样重新创建了,这在工作中是veryvery费事的。
基本用法
– ALTER TABLE 表名 执行动作(以下选项就是执行操作) ;
Add 添加字段
Modify 修改字段类型
Change 修改字段名
Drop 删除字段
Rename 修改表名
add添加为例
mysql> alter table t15
-> add TK varchar(10); 添加后面格式跟建立表一样(包括字段类型,空,默认值等)
Query OK, 0 rows affected (0.64 sec)
连续添加重复书写add即可
mysql> desc t15;
+-------+------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------------------+------+-----+---------+-------+
| name | char(5) | NO | | | |
| level | int(5) unsigned zerofill | YES | | 00000 | |
| money | tinyint(2) unsigned zerofill | YES | | 00 | |
| TK | varchar(10) | YES | | NULL | |
+-------+------------------------------+------+-----+---------+-------+
添加的字段默认按顺序排列放在最后,如果像放在那一行可以加 first
mysql> alter table t15 add stu_id char(9) first;
如果想创建的字段放在某个字段后面,加 after name(放在name字段后面)
mysql> alter table t15 add sex enum("boy","girl","no")default"no" after name;
```````````````````````````````````````````````````````````````````````````````````````
Drop 删除字段
– ALTER TABLE 表名
– drop 字段名 ;
能添加就能删除,这个更简单 如下:
mysql> alter table t15 drop
-> TK; 删除完成
Query OK, 0 rows affected (0.82 sec)
mysql> desc t15;
+-------+------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------------------+------+-----+---------+-------+
| name | char(5) | NO | | | |
| level | int(5) unsigned zerofill | YES | | 00000 | |
| money | tinyint(2) unsigned zerofill | YES | | 00 | |
如果你想删除两个那就逗号跟drop 字段
mysql> alter table t15 drop atu_id,drop tel;
````````````````````````````````````````````````````````````````````````````````````````
modify 修改字段类型
– ALTER TABLE 表名
– modify 字段名 类型 ( 宽度 ) 约束条件 ;
可加 AFTER 字段名 ;
或者 FIRST;
要点:需要修改的目标抄写下来,不需要修改的不用(比如自带默认的)
mysql> alter table t15 modify name char(7) not null;
mysql> alter table t15 modify name char(10) not null default"";
Query OK, 0 rows affected (0.10 sec)
mysql> desc t15;
+--------+------------------------------+------+-----+-------------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+------------------------------+------+-----+-------------+-------+
| atu_id | varchar(10) | YES | | NULL | |
| name | char(10) | NO | | | |
modify还支持移动位置
比如我要把 tel 移动到 name后面
mysql> alter table t15 modify tel char(15) after name;
mysql> desc t15;
+--------+------------------------------+------+-----+-------------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+------------------------------+------+-----+-------------+-------+
| atu_id | varchar(10) | YES | | NULL | |
| name | char(10) | NO | | | |
| tel | char(15) | YES | | NULL | |
| sex | enum('boy','girl','no') | YES | | no | |
| level | int(5) unsigned zerofill | YES | | 00000 | |
| money | tinyint(2) unsigned zerofill | YES | | 00 | |
| email | varchar(30) | YES | | stu@tedu.cn | |
+--------+------------------------------+------+-----+-------------+-------+
````````````````````````````````````````````````````````````````````````````````````````
修改表字段名
– ALTER TABLE 表名
– change 源字段名 新字段名 类型 ( 宽度 ) 约束条件;
mysql> alter table t15
-> change level llll int(5) unsigned zerofill default"00000";
Query OK, 0 rows affected (0.42 sec)
mysql> desc t15;
+-------+------------------------------+------+-----+-------------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------------------+------+-----+-------------+-------+
| name | char(10) | NO | | | |
| sex | enum('boy','girl','no') | YES | | no | |
| llll | int(5) unsigned zerofill | YES | | 00000 | |
-------------------------------------------------------------------------------------------------------------------------------------------
Rename 修改表名
格式
– ALTER TABLE 表名
– Rename 新表名;
mysql> alter table t15
-> rename t51;
Query OK, 0 rows affected (0.18 sec)
查看
mysql> desc t51;
+-------+------------------------------+------+-----+-------------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------------------+------+-----+-------------+-------+
| name | char(10) | NO | | | |
| sex | enum('boy','girl','no') | YES | | no | |
| level | int(5) unsigned zerofill | YES | | 00000 | |
| money | tinyint(2) unsigned zerofill | YES | | 00 | |
| email | varchar(30) | YES | | stu@tedu.cn | |
+-------+------------------------------+------+-----+-------------+-------+
**************************************************************************************
索引
**************************************************************************************
INDEX :普通索引
• UNIQUE :唯一索引
• FULLTEXT :全文索引
• PRIMARY KEY :主键
• FOREIGN KEY :外键
优点:相当于书的目录,方面更快速找到想要的内容
缺点:做增删改查时索引也要做维护
1. 创建表的时候可以添加索引。
create table t16(
name char(5),
age int(3),
class char(7),
index (name),
index (class));
可以看到Key 下出现MUL
mysql> desc t16;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| name | char(10) | YES | MUL | NULL | |
| age | int(3) | YES | | NULL | |
| class | char(7) | YES | MUL | NULL | |
– SHOW INDEX FROM 表名 ;
show index from t16 能看表使用那些索引和算法。 \G排列
删除表中的健值 mysql> drop index class on t16;
2, 往已经创建好的表里添加索引也可以
create index 索引名(方便记忆可以敢字段名一样) on t16(目标字段名);
mysql> create index SYname on t16(name);
mysql> desc t16;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| name | char(10) | YES | MUL | NULL | |
| age | int(3) | YES | MUL | NULL | |
| class | char(7) | YES | MUL | NULL | |
+-------+----------+------+-----+---------+-------+
查看用了那些索引
mysql> show index from t16\G;
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++
***********************
primary key主健 *
特点:字段值不允许重复不能空
mysql> select * from t18;
+---------+------+
| stu_id | name |
+---------+------+
| nsd1803 | boy |
mysql> insert into t18 values("nsd1803","tom");
ERROR 1062 (23000): Duplicate entry 'nsd1803' for key 'PRIMARY'
mysql> insert into t18 values("nsd1804","tom");
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1. 创建表时给字段添加主健
mysql> create table t17(
stu_id char(19) primary key,
name char(10));
可以看到 字段stu_id的key健值有一个PRI主健
mysql> desc t17;
+--------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+----------+------+-----+---------+-------+
| stu_id | char(19) | NO | PRI | NULL | |
| naem | char(5) | YES | | NULL | |
+--------+----------+------+-----+---------+-------+
**primary key 不一定要下在目标字段后面啦,可以写在最后的结尾处但是要加括号字段名,如下:
mysql> create table t18(
stu_id char(19),
name char(10),
primary key(stu_id));
mysql> desc t18;
+--------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+----------+------+-----+---------+-------+
| stu_id | char(19) | NO | PRI | NULL | |
| name | char(5) | YES | | NULL | |
+--------+----------+------+-----+---------+-------+
----------------------------------------------------------------------------------------------------------------------------------------
2, 删除主健
msql> alter tabel t18 drop primary key; //它和删除索引的命令不一样
可以看见主健已经删除,但是不允许为空值
mysql> desc t18;
+--------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+----------+------+-----+---------+-------+
| stu_id | char(19) | NO | | NULL | |
| name | char(5) | YES | | NULL | |
+--------+----------+------+-----+---------+-------+
sp56