约束条件：
null default
实验准备：
mysql> create database king;
Query OK, 1 row affected (0.04 sec)
mysql> use king;
Database changed
mysql> create table t1(name char(7) not null, class char(7) default "nsd1804", age tinyint(2) not null default 19, sex enum("boy","gril") not null default "boy");
Query OK, 0 rows affected (0.23 sec)
mysql> desc t1;
+-------+--------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------------+------+-----+---------+-------+
| name | char(7) | NO | | NULL | |
| class | char(7) | YES | | nsd1804 | |
| age | tinyint(2) | NO | | 19 | |
| sex | enum('boy','gril') | NO | | boy | |
+-------+--------------------+------+-----+---------+-------+
4 rows in set (0.01 sec)
mysql> insert into t1(name) values("bob");
Query OK, 1 row affected (0.08 sec)
mysql> insert into t1 values("bob2","nsd1804",21,"gril");
Query OK, 1 row affected (0.04 sec)
mysql> insert into t1 values ("jim",null,22,"boy");
Query OK, 1 row affected (0.05 sec)
mysql> select * from t1;
+------+---------+-----+------+
| name | class | age | sex |
+------+---------+-----+------+
| bob | nsd1804 | 19 | boy |
| bob2 | nsd1804 | 21 | gril |
| jim | NULL | 22 | boy |
+------+---------+-----+------+
3 rows in set (0.00 sec)
mysql> insert into t1 values("","nsd1804",22,"boy");
Query OK, 1 row affected (0.08 sec)
mysql> select * from t1;
+------+---------+-----+------+
| name | class | age | sex |
+------+---------+-----+------+
| bob | nsd1804 | 19 | boy |
| bob2 | nsd1804 | 21 | gril |
| jim | NULL | 22 | boy |
| | nsd1804 | 22 | boy |
+------+---------+-----+------+
4 rows in set (0.00 sec)
修改表结构
对已经创建的表进行修改
添加新的字段，add 字段名 类型（宽度） 约束条件
修改表名
添加新字段 add
mysql> alter table t8 add email varchar(50) default 'student@tedu.cn';
Query OK, 0 rows affected (0.46 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table t8 add email varchar(50) default 'student@tedu.cn', add qq char(11);
ERROR 1060 (42S21): Duplicate column name 'email'
不能重复添加表结构，必须先删除 表结构具有唯一性不能出现重复
mysql> alter table t8 drop email;
Query OK, 0 rows affected (0.49 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc t8;
+-------+--------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------------+------+-----+---------+-------+
| name | char(15) | YES | | NULL | |
| class | char(8) | YES | | NULL | |
| age | int(3) | YES | | NULL | |
| sex | enum('boy','gril') | YES | | NULL | |
+-------+--------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
mysql> alter table t8 add email varchar(50) default 'student@tedu.cn', add qq char(11);
Query OK, 0 rows affected (0.48 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc t8;
+-------+--------------------+------+-----+-----------------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------------+------+-----+-----------------+-------+
| name | char(15) | YES | | NULL | |
| class | char(8) | YES | | NULL | |
| age | int(3) | YES | | NULL | |
| sex | enum('boy','gril') | YES | | NULL | |
| email | varchar(50) | YES | | student@tedu.cn | |
| qq | char(11) | YES | | NULL | |
+-------+--------------------+------+-----+-----------------+-------+
6 rows in set (0.00 sec)
按照顺序添加表结构：
mysql> alter table t8 add stu_num char(8) first,add likes set("eat","sleeps","game") after sex;
Query OK, 0 rows affected (0.48 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc t8;
+---------+----------------------------+------+-----+-----------------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+----------------------------+------+-----+-----------------+-------+
| stu_num | char(8) | YES | | NULL | |
| name | char(15) | YES | | NULL | |
| class | char(8) | YES | | NULL | |
| age | int(3) | YES | | NULL | |
| sex | enum('boy','gril') | YES | | NULL | |
| likes | set('eat','sleeps','game') | YES | | NULL | |
| email | varchar(50) | YES | | student@tedu.cn | |
| qq | char(11) | YES | | NULL | |
+---------+----------------------------+------+-----+-----------------+-------+
8 rows in set (0.00 sec)
删除表结构：
mysql> select * from t1;
+------+---------+-----+------+
| name | class | age | sex |
+------+---------+-----+------+
| bob | nsd1804 | 19 | boy |
| bob2 | nsd1804 | 21 | gril |
| jim | NULL | 22 | boy |
| | nsd1804 | 22 | boy |
+------+---------+-----+------+
4 rows in set (0.00 sec)
mysql> alter table t1 drop age;
Query OK, 0 rows affected (0.50 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> select * from t1;
+------+---------+------+
| name | class | sex |
+------+---------+------+
| bob | nsd1804 | boy |
| bob2 | nsd1804 | gril |
| jim | NULL | boy |
| | nsd1804 | boy |
+------+---------+------+
4 rows in set (0.00 sec)
删除表结构，表记录也会被删除，（要先把表进行备份）
删除两个表结构：
mysql> desc t8;
+---------+----------------------------+------+-----+-----------------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+----------------------------+------+-----+-----------------+-------+
| stu_num | char(8) | YES | | NULL | |
| name | char(15) | YES | | NULL | |
| class | char(8) | YES | | NULL | |
| age | int(3) | YES | | NULL | |
| sex | enum('boy','gril') | YES | | NULL | |
| likes | set('eat','sleeps','game') | YES | | NULL | |
| email | varchar(50) | YES | | student@tedu.cn | |
| qq | char(11) | YES | | NULL | |
+---------+----------------------------+------+-----+-----------------+-------+
8 rows in set (0.00 sec)
mysql> alter table t8 drop email, drop qq;
Query OK, 0 rows affected (0.46 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table t8 drop email, qq;
这样删除就会出现语法错误
mysql> select * from t8;
+---------+------+---------+------+------+-------+
| stu_num | name | class | age | sex | likes |
+---------+------+---------+------+------+-------+
| NULL | | nsd1804 | 45 | boy | NULL |
+---------+------+---------+------+------+-------+
1 row in set (0.00 sec)
修改表结构：
修改已有的字段类型 modify
修改字段类型时，如果新的类型与字段已经存储数据冲突，不允许修改. 不能修改的还要抄一遍
如果不修改的部分就要原样的抄下来，不然就会恢复成mysql的默认字段
mysql> select * from t8;
+---------+------+---------+------+------+-------+
| stu_num | name | class | age | sex | likes |
+---------+------+---------+------+------+-------+
| NULL | | nsd1804 | 45 | boy | NULL |
+---------+------+---------+------+------+-------+
1 row in set (0.00 sec)
mysql> alter table t8 modify stu_num varchar(8) not null ;
ERROR 1138 (22004): Invalid use of NULL value
存储记录与修改字段出现冲突，修改表结构也会记录的属性也会跟着改变
mysql> alter table t8 modify stu_num varchar(8);
Query OK, 1 row affected (0.59 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> desc t8;
+---------+----------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+----------------------------+------+-----+---------+-------+
| stu_num | varchar(8) | YES | | NULL | |
| name | char(15) | YES | | NULL | |
| class | char(8) | YES | | NULL | |
| age | int(3) | YES | | NULL | |
| sex | enum('boy','gril') | YES | | NULL | |
| likes | set('eat','sleeps','game') | YES | | NULL | |
+---------+----------------------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
mysql> alter table t8 modify sex enum('boy','gril','no') not null default "boy";
Query OK, 0 rows affected (0.45 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc t8;
+---------+----------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+----------------------------+------+-----+---------+-------+
| stu_num | varchar(8) | YES | | NULL | |
| name | char(15) | YES | | NULL | |
| class | char(8) | YES | | NULL | |
| age | int(3) | YES | | NULL | |
| sex | enum('boy','gril','no') | NO | | boy | |
| likes | set('eat','sleeps','game') | YES | | NULL | |
+---------+----------------------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
修改字段的位置
mysql> alter table t8 modify sex enum('boy','gril','no') not null default "boy" after name;
Query OK, 0 rows affected (0.48 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc t8;
+---------+----------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+----------------------------+------+-----+---------+-------+
| stu_num | varchar(8) | YES | | NULL | |
| name | char(15) | YES | | NULL | |
| sex | enum('boy','gril','no') | NO | | boy | |
| class | char(8) | YES | | NULL | |
| age | int(3) | YES | | NULL | |
| likes | set('eat','sleeps','game') | YES | | NULL | |
+---------+----------------------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
修改字段名：change
不需要修改的就全部抄下来
mysql> alter table t8 change stu_num stu_id varchar(8);
Query OK, 0 rows affected (0.11 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc t8;
+--------+----------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+----------------------------+------+-----+---------+-------+
| stu_id | varchar(8) | YES | | NULL | |
| name | char(15) | YES | | NULL | |
| sex | enum('boy','gril','no') | NO | | boy | |
| class | char(8) | YES | | NULL | |
| age | int(3) | YES | | NULL | |
| likes | set('eat','sleeps','game') | YES | | NULL | |
+--------+----------------------------+------+-----+---------+-------+
6 rows in set (0.01 sec)
修改表名：rename
mysql> alter table t8 rename king;
Query OK, 0 rows affected (0.12 sec)
mysql> show tables;
+----------------+
| Tables_in_king |
+----------------+
| king |
| t1 |
+----------------+
2 rows in set (0.00 sec)
mysql> select * from king;
+--------+------+-----+---------+------+-------+
| stu_id | name | sex | class | age | likes |
+--------+------+-----+---------+------+-------+
| NULL | | boy | nsd1804 | 45 | NULL |
+--------+------+-----+---------+------+-------+
1 row in set (0.00 sec)
2万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
