05：mysql之表结构管理#

1
//库名区分字母大小写
2
mysql> create【克瑞特】 database【得塔-贝斯】 gamedb ;
3
-- mysql> create database gamedb;
4
Query OK, 1 row affected (0.14 sec)
5

6
mysql> create【克瑞特】 database【得塔-贝斯】 GAMEDB ;
7
-- mysql> create database GAMEDB;
8
Query OK, 1 row affected (0.08 sec)
9

10
mysql> create【克瑞特】 database【得塔-贝斯】 GAMEDB ;
11
-- mysql> create database GAMEDB;
12
ERROR 1007 (HY000): Can't create【克瑞特】 database【得塔-贝斯】 'GAMEDB'; database【得塔-贝斯】 exists【伊贼谁斯】' //重名报错
13

14
//加if not【nuō 特】 exists【伊贼谁斯】 命令避免重名报错
15
mysql> create【克瑞特】 database【得塔-贝斯】  if not【nuō 特】 exists【伊贼谁斯】  gamedb ;
16
-- mysql> create database if not exists gamedb;
17
Query OK, 1 row affected, 1 warning (0.03 sec) //正常
18

19
mysql> show【瘦】  databases【得塔-贝斯】; //查看创建的库
20
-- mysql> show databases;
21
+--------------------+
22
| Database           |
23
+--------------------+
24
| GAMEDB             |
25
| gamedb             |
26
| information_schema |
27
| mysql              |
28
| performance_schema |
29
| sys                |
30
| moershi             |
31
+--------------------+
32
7 rows in set (0.00 sec)
33

34
mysql> drop【卓-普】 database【得塔-贝斯】 gamedb;  //删除库
35
-- mysql> drop database gamedb;
36
Query OK, 0 rows affected (0.11 sec)
37

38
mysql> drop【卓-普】 database【得塔-贝斯】 gamedb; // 删除没有的库报错
39
-- mysql> drop database gamedb;
40
ERROR 1008 (HY000): Can’t drop【卓-普】 database【得塔-贝斯】 ‘gamedb’; database【得塔-贝斯】 doesn’t exist
41

42
//加if exists【伊贼谁斯】 删除没有的库，也不报错
43
mysql> drop【卓-普】 database【得塔-贝斯】 if exists【伊贼谁斯】 gamedb;
44
-- mysql> drop database if exists gamedb;
45
Query OK, 0 rows affected, 1 warning (0.00 sec)

1
mysql> create【克瑞特】 database【得塔-贝斯】  studb【斯丢db】; //建库
2
-- mysql> create database studb;
3
Query OK, 1 row affected (0.11 sec)
4

5
mysql> create【克瑞特】 table【忒-部】 studb【斯丢db】.stu(  //建表
6
    -> name char【查尔】(10),
7
    -> class【克拉斯】 char【查尔】(9),
8
    -> gender【间德尔】 char【查尔】(4),
9
    -> age int【因特】
10
    -> );
11
-- mysql> create table studb.stu(
12
--     -> name char(10),
13
--     -> class char(9),
14
--     -> gender char(4),
15
--     -> age int);
16
Query OK, 0 rows affected (1.17 sec)
17

18
mysql> desc studb【斯丢db】.stu;  //查看表头
19
-- mysql> desc studb.stu;
20
+--------+----------+------+-----+---------+-------+
21
| Field  | Type     | Null | Key | Default | Extra |
22
+--------+----------+------+-----+---------+-------+
23
| name   | char(10) | YES  |     | NULL    |       |
24
| class  | char(9)  | YES  |     | NULL    |       |
25
| gender | char(4)  | YES  |     | NULL    |       |
26
| age    | int      | YES  |     | NULL    |       |
27
+--------+----------+------+-----+---------+-------+
28
4 rows in set (0.00 sec)

1
//建表
2
mysql> create【克瑞特】  table【忒-部】   studb【斯丢db】.t2(name   char【查尔】(3) , address【额拽斯】   varchar【瓦儿-查儿】(5) );
3
-- mysql> create table studb.t2(name char(3),adderss varchar(5));
4
Query OK, 0 rows affected (0.30 sec)
5

6
//查看表头
7
mysql>  desc studb【斯丢db】.t2;
8
-- mysql> desc studb.t2;
9
+---------+------------+------+-----+---------+-------+
10
| Field   | Type       | Null | Key | Default | Extra |
11
+---------+------------+------+-----+---------+-------+
12
| name    | char(3)    | YES  |     | NULL    |       |
13
| address | varchar(5) | YES  |     | NULL    |       |
14
+---------+------------+------+-----+---------+-------+
15
2 rows in set (0.00 sec)
16

17
//插入记录
18
mysql> insert【因涩特】 into【因兔】 studb【斯丢db】.t2 values【挖柳斯】 ("a","a"); //正常
19
-- mysql> insert into studb.t2 values("a","a");
20
Query OK, 1 row affected (0.05 sec)
21

22
mysql> insert【因涩特】 into【因兔】 studb【斯丢db】.t2 values【挖柳斯】 ("ab","ab"); //正常
23
-- mysql> insert into studb.t2 values ("ab","ab");
24
Query OK, 1 row affected (0.08 sec)
25

26
mysql> insert【因涩特】 into【因兔】 studb【斯丢db】.t2 values【挖柳斯】 ("abc","abc");//正常
27
-- mysql> insert into studb.t2 values ("abc","abc");
28
Query OK, 1 row affected (0.04 sec)
29

30
mysql>  insert【因涩特】 into【因兔】 studb【斯丢db】.t2 values【挖柳斯】 ("abcd","abcd"); //超出字符个数报错
31
-- mysql> insert into studb.t2 values ("abcd","abcd");
32
ERROR 1406 (22001): Data too long for column 'name' at row 1

1
//查看字符集
2
mysql> show【瘦】 create【克瑞特】 table【忒-部】 studb【斯丢db】.t2 \G
3
-- mysql> show create table studb.t2 \G
4
*************************** 1. row ***************************
5
       Table: t2
6
Create Table: CREATE TABLE `t2` (
7
  `name` char【查尔】(3) DEFAULT NULL【nào】,
8
  `address【额拽斯】` varchar【瓦儿-查儿】(5) DEFAULT NULL【nào】
9
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
10
1 row in set (0.00 sec)
11

12
说明 ：
13
ENGINE=InnoDB  定义存储引擎（存储引擎课程里讲）
14
DEFAULT   CHARSET=定义表使用的字符集
15

16
//插入记录
17
mysql> insert【因涩特】 into【因兔】 studb【斯丢db】.t2 values【挖柳斯】 ("张翠山","武当山");
18
-- mysql> insert into studb.t2 values("张翠山","武当山");
19
Query OK, 1 row affected (0.07 sec)
20

21
//查看表记录
22
mysql> SELECT【涩莱克特】  * FROM【弗乱】 studb【斯丢db】.t2;
23
-- mysql> select * from studb.t2;
24
+-----------+-----------+
25
| name      | address   |
26
+-----------+-----------+
27
| a         | a         |
28
| ab        | ab        |
29
| abc       | abc       |
30
| 张翠山    | 武当山     |
31
+-----------+-----------+
32
4 rows in set (0.00 sec)

1
//建表
2
mysql> create【克瑞特】 table【忒-部】  studb【斯丢db】.t1(name  char【查尔】(10) , level【列沃】  tinyint【泰尼因特】 unsigned【安赛因德】 ,  money【孟妮】  double【答步】  );
3
-- mysql> create table studb.t1(name char(10),level  tinyint unsigned ,  money  double  );
4
Query OK, 0 rows affected (0.72 sec)
5

6
//查看表头
7
mysql> desc studb【斯丢db】.t1;
8
-- mysql> desc studb.t1;
9
+-------+------------------+------+-----+---------+-------+
10
| Field | Type             | Null | Key | Default | Extra |
11
+-------+------------------+------+-----+---------+-------+
12
| name  | char(10)         | YES  |     | NULL    |       |
13
| level | tinyint unsigned | YES  |     | NULL    |       |
14
| money | double           | YES  |     | NULL    |       |
15
+-------+------------------+------+-----+---------+-------+
16
3 rows in set (0.00 sec)
17

18
//插入数据
19
mysql> insert【因涩特】 into【因兔】 studb【斯丢db】.t1 values【挖柳斯】("法师",80,88);
20
-- mysql> insert into studb.t1 values("法师",80,88);
21
Query OK, 1 row affected (0.04 sec)
22

23
//超出范围报错 tinyint unsigned 最大255  可以是小数，四舍五入取整
24
mysql> insert【因涩特】 into【因兔】 studb【斯丢db】.t1 values【挖柳斯】("战士",301,1.292);
25
-- mysql> insert into studb.t1 values("战士",301,1.292);
26
ERROR 1264 (22003): Out of range value for column 'level【列沃】' at row 1 -- 失败是因为值301超出了TINYINT UNSIGNED的数据范围（0-255）
27

28
mysql> insert【因涩特】 into【因兔】 studb【斯丢db】.t1 values【挖柳斯】("猎人",255,1.292);
29
-- mysql> insert into studb.t1 values("猎人",255,1.292);
30
Query OK, 1 row affected (0.06 sec)
31

32
//整数类型 不存储小数位
33
mysql> insert【因涩特】 into【因兔】 studb【斯丢db】.t1  values【挖柳斯】 ("英雄",1.292,6.78);
34
-- mysql> insert into studb.t1 values("英雄",1.292,6.78);
35
Query OK, 1 row affected (0.07 sec)
36

37
//查看表记录
38
mysql> select【涩莱克特】 *  from【弗乱】 studb【斯丢db】.t1 ;
39
-- mysql> select * from studb.t1;
40
+--------+-------+-------+
41
| name   | level | money |
42
+--------+-------+-------+
43
| 法师   |    80 |    88 |
44
| 猎人   |   255 | 1.292 |
45
| 英雄   |     1 |  6.78 |
46
+--------+-------+-------+
47
3 rows in set (0.00 sec)

1
//建表
2
mysql> create【克瑞特】  table【忒-部】 studb【斯丢db】.t8(
3
    -> 姓名 char【查尔】(10),
4
    -> 性别  enum【伊牛姆】("男","女","保密"),
5
    -> 爱好 set【赛特】("帅哥","金钱","吃","睡")
6
    -> );
7
-- mysql> create table studb.t8(
8
--     -> 姓名 char(10),
9
--     -> 性别 enum("男","女","保密"),
10
--     -> 爱好 set("帅哥","金钱","吃","睡"));
11
Query OK, 0 rows affected (0.29 sec)
12

13
//查看表头
14
mysql> desc studb【斯丢db】.t8 ;
15
-- mysql> desc studb.t8;
16
+--------+------------------------------------+------+-----+---------+-------+
17
| Field  | Type                               | Null | Key | Default | Extra |
18
+--------+------------------------------------+------+-----+---------+-------+
19
| 姓名   | char(10)                           | YES  |     | NULL    |       |
20
| 性别   | enum('男','女','保密')             | YES  |     | NULL    |       |
21
| 爱好   | set('帅哥','金钱','吃','睡')       | YES  |     | NULL    |       |
22
+--------+------------------------------------+------+-----+---------+-------+
23
3 rows in set (0.01 sec)
24

25
//插入记录超出范围报错
26
mysql> insert【因涩特】 into【因兔】 studb【斯丢db】.t8 values【挖柳斯】 ("小包总","男人","帅哥,睡,金钱");
27
-- mysql> insert into studb.t8 values ("小包总","男人","帅哥,睡,金钱");
28
ERROR 1265 (01000): Data truncated for column '性别' at row 1 -- 值"男人"不在ENUM类型的预定义值列表中（只有'男','女','保密'）
29

30
mysql> insert【因涩特】 into【因兔】 studb【斯丢db】.t8 values【挖柳斯】 ("小包总","男","美女,睡,金钱");
31
-- mysql> insert into studb.t8 values ("小包总","男","美女,睡,金钱");
32
ERROR 1265 (01000): Data truncated for column '爱好' at row 1 --"爱好"字段只包含'帅哥','金钱','吃','睡'四个值，不包含"美女"这个值
33

34

35
//在范围内插入成功
36
mysql> insert【因涩特】 into【因兔】 studb【斯丢db】.t8 values【挖柳斯】 ("丫丫","女","帅哥,吃");
37
-- mysql> insert into studb.t8 values("丫丫","女","帅哥,吃");
38
Query OK, 1 row affected (0.09 sec)
39

40
mysql> select【涩莱克特】  * from【弗乱】 studb【斯丢db】.t8;
41
-- mysql> select * from studb.t8;
42
+--------+--------+------------+
43
| 姓名   | 性别   | 爱好       |
44
+--------+--------+------------+
45
| 丫丫   | 女     | 帅哥,吃    |
46
+--------+--------+------------+
47
1 row in set (0.00 sec)

1
//建表
2
mysql> create【克瑞特】 table【忒-部】 studb【斯丢db】.t6(
3
    -> 姓名  char【查尔】(10),
4
    -> 生日  date ,
5
    -> 出生年份 year【耶尔】,
6
    -> 家庭聚会  datetime ,
7
    -> 聚会地点  varchar【瓦儿-查儿】(15),
8
    -> 上班时间 time
9
    -> );
10
-- mysql> create table studb.t6(
11
--     -> 姓名 char(10),
12
--     -> 生日 date,
13
--     -> 出生年份 year,
14
--     -> 家庭聚会 datetime,
15
--     -> 聚会地点 varchar(15),
16
--     -> 上班时间 time);
17
Query OK, 0 rows affected (0.25 sec)
18

19
//查看表头
20
mysql> desc studb【斯丢db】.t6 ;
21
-- mysql> desc studb.t6;
22
+--------------+-------------+------+-----+---------+-------+
23
| Field        | Type        | Null | Key | Default | Extra |
24
+--------------+-------------+------+-----+---------+-------+
25
| 姓名         | char(10)    | YES  |     | NULL    |       |
26
| 生日         | date        | YES  |     | NULL    |       |
27
| 出生年份     | year        | YES  |     | NULL    |       |
28
| 家庭聚会     | datetime    | YES  |     | NULL    |       |
29
| 聚会地点     | varchar(15) | YES  |     | NULL    |       |
30
| 上班时间     | time        | YES  |     | NULL    |       |
31
+--------------+-------------+------+-----+---------+-------+
32
6 rows in set (0.00 sec)
33

34
//插入表头
35
mysql> insert【因涩特】 into【因兔】  studb【斯丢db】.t6
36
    -> values【挖柳斯】 ("翠花",20211120,1990,20220101183000,"天坛校区",090000);
37
-- mysql> insert into studb.t6
38
--     -> values ("翠花",20211120,1990,20220101183000,"天坛校区",090000);
39
Query OK, 1 row affected (0.05 sec)
40

41
//查看表记录
42
mysql> select【涩莱克特】  * from【弗乱】 studb【斯丢db】.t6;
43
-- mysql> select * from studb.t6;
44
+--------+------------+--------------+---------------------+--------------+--------------+
45
| 姓名   | 生日       | 出生年份     | 家庭聚会            | 聚会地点     | 上班时间     |
46
+--------+------------+--------------+---------------------+--------------+--------------+
47
| 翠花   | 2021-11-20 |         1990 | 2022-01-01 18:30:00 | 天坛校区     | 09:00:00     |
48
+--------+------------+--------------+---------------------+--------------+--------------+
49
1 row in set (0.00 sec)

1
//建表时给表头设置默认和不允许赋null【nào】值
2
mysql> create【克瑞特】 database【得塔-贝斯】 if not【nuō 特】 exists【伊贼谁斯】 db1;
3
-- mysql> create database if not exists db1;
4
Query OK, 1 row affected (0.07 sec)
5

6
//建表
7
mysql> create【克瑞特】 table【忒-部】 db1.t31(
8
    -> name char【查尔】(10) not【nuō 特】 null【nào】 ,
9
    -> class【克拉斯】 char【查尔】(7) default【迪-佛特】 "nsd",
10
    -> likes【赖特s】 set【赛特】("money【孟妮】","game","film","music") not【nuō 特】 null【nào】  default【迪-佛特】 "film,music" );
11
-- mysql> create table db1.t31(
12
--     -> name char(10) not null,
13
--     -> class char(7) default "nsd",
14
--     -> likes set("money","game","film","music") not null default "film,music" );
15
Query OK, 0 rows affected (0.43 sec)
16

17
//查看表头
18
mysql> desc db1.t31;
19
-- mysql> desc db1.t31;
20
+-------+------------------------------------+------+-----+------------+-------+
21
| Field | Type                               | Null | Key | Default    | Extra |
22
+-------+------------------------------------+------+-----+------------+-------+
23
| name  | char(10)                           | NO   |     | NULL       |       |
24
| class | char(7)                            | YES  |     | nsd        |       |
25
| likes | set('money','game','film','music') | NO   |     | film,music |       |
26
+-------+------------------------------------+------+-----+------------+-------+
27
3 rows in set (0.01 sec)
28

29
//验证默认值和不允许为null
30
mysql> insert【因涩特】 into【因兔】  db1.t31 values【挖柳斯】 (null, null , null);
31
-- mysql> insert into db1.t31 values(null, null , null);
32
ERROR 1048 (23000): Column 'name' cannot be null  //表头name赋null值 报错
33

34
//表头likes【赖特s】赋null值 报错
35
mysql> insert【因涩特】 into【因兔】  db1.t31 values【挖柳斯】 ("bob", null , null);
36
-- mysql> insert into db1.t31 values("bob", null , null);
37
ERROR 1048 (23000): Column 'likes' cannot be null
38

39
//符合约束不报错
40
mysql> insert【因涩特】 into【因兔】  db1.t31 values【挖柳斯】 ("bob",null,"money【孟妮】,game,film");
41
-- mysql> insert into db1.t31 values("bob",null,"money,game,film");
42
Query OK, 1 row affected (0.06 sec)
43

44
//不赋值的表头使用默认值赋值
45
mysql> insert【因涩特】 into【因兔】 db1.t31(name) values【挖柳斯】("jim");
46
-- mysql> insert into db1.t31(name) values("jim");
47
-- Query OK, 1 row affected (0.01 sec)
48

49
//根据需要自定义表头的值
50
mysql> insert【因涩特】 into【因兔】 db1.t31 values【挖柳斯】 ("lucy","nsd2108","game,film");
51
-- mysql> insert into db1.t31 values("lucy","nsd2108","game,film");
52
-- Query OK, 1 row affected (0.14 sec)
53

54
//查看表记录
55
mysql> select【涩莱克特】  * from【弗乱】 db1.t31;
56
-- mysql> select * from db1.t31;
57
+------+---------+-----------------+
58
| name | class   | likes           |
59
+------+---------+-----------------+
60
| bob  | NULL    | money,game,film |
61
| jim  | nsd     | film,music      |
62
| lucy | nsd2108 | game,film       |
63
+------+---------+-----------------+
64
3 rows in set (0.00 sec)

1
//建表
2
create【克瑞特】      table【忒-部】  db1.t32 (姓名  char【查尔】(10)  ,  护照   char【查尔】(18)  unique【U尼克】  );
3
-- mysql> create table db1.t32 (姓名 char(10) , 护照 char(18) unique);
4

5
//查看表头 唯一索引标志UNI
6
mysql> desc db1.t32 ;
7
-- mysql> desc db1.t32;
8
+--------+----------+------+-----+---------+-------+
9
| Field  | Type     | Null | Key | Default | Extra |
10
+--------+----------+------+-----+---------+-------+
11
| 姓名   | char(10) | YES  |     | NULL    |       |
12
| 护照   | char(18) | YES  | UNI | NULL    |       |
13
+--------+----------+------+-----+---------+-------+
14
2 rows in set (0.00 sec)
15

16
//赋null值 可以
17
mysql> insert【因涩特】 into【因兔】 db1.t32 values【挖柳斯】("bob",null);
18
-- mysql> insert into db1.t32 values("bob",null);
19
Query OK, 1 row affected (0.07 sec)
20

21
//表头值重复不可以
22
mysql>  insert【因涩特】 into【因兔】 db1.t32 values【挖柳斯】("tom","666888");
23
-- mysql> insert into db1.t32 values("tom","666888");
24
Query OK, 1 row affected (0.08 sec)
25

26
mysql> insert【因涩特】 into【因兔】 db1.t32 values【挖柳斯】("jim","666888");
27
-- mysql> insert into db1.t32 values("jim","666888");
28
ERROR 1062 (23000): Duplicate entry '666888' for key【kì】 't32.护照'
29

30
//不重复 可以
31
mysql> insert【因涩特】 into【因兔】 db1.t32 values【挖柳斯】("jim","766888");
32
-- mysql> insert into db1.t32 values("jim","766888");
33
Query OK, 1 row affected (0.05 sec)
34

35
//查看表记录
36
mysql> select【涩莱克特】  * from【弗乱】 DB1.t32;
37
-- mysql> select * from db1.t32;
38
+------+--------+
39
| 姓名  | 护照  |
40
+------+--------+
41
| bob  | NULL   |
42
| tom  | 666888 |
43
| jim  | 766888 |
44
+------+--------+
45
3 rows in set (0.00 sec)

1
mysql> create【克瑞特】     table【忒-部】   db1.t38 (
2
    -> 行号   int【因特】   primary【普赖莫瑞】 key【kì】  auto_increment【奥特欧_因克瑞门特】   ,
3
    -> 姓名   char【查尔】(10) ,
4
    -> 班级   char【查尔】(7) ,
5
    -> 住址   char【查尔】(10)
6
    -> );
7
-- mysql> create table db1.t38(
8
--     -> 行号 int primary key auto_increment,
9
--     -> 姓名 char(10),
10
--     -> 班级 char(7),
11
--     -> 住址 char(10));
12
Query OK, 0 rows affected (0.76 sec)
13

14
//查看表头
15
mysql> desc db1.t38 ;
16
-- mysql> desc db1.t38;
17
+--------+----------+------+-----+---------+----------------+
18
| Field  | Type     | Null | Key | Default | Extra          |
19
+--------+----------+------+-----+---------+----------------+
20
| 行号   | int      | NO   | PRI | NULL    | auto_increment |
21
| 姓名   | char(10) | YES  |     | NULL    |                |
22
| 班级   | char(7)  | YES  |     | NULL    |                |
23
| 住址   | char(10) | YES  |     | NULL    |                |
24
+--------+----------+------+-----+---------+----------------+
25
4 rows in set (0.00 sec)
26

27
//插入表记录 不给自增长表头赋值
28
mysql> insert【因涩特】  into【因兔】  db1.t38(姓名,班级,住址) values【挖柳斯】("bob","nsd2107","bj");
29
-- mysql> insert into db1.t38(姓名,班级,住址) values("bob","nsd2107","bj");
30
Query OK, 1 row affected (0.05 sec)
31

32
mysql> insert【因涩特】  into【因兔】  db1.t38(姓名,班级,住址) values【挖柳斯】("bob","nsd2107","bj");
33
-- mysql> insert into db1.t38(姓名,班级,住址) values("bob","nsd2107","bj");
34
Query OK, 1 row affected (0.04 sec)
35

36
mysql> insert【因涩特】  into【因兔】  db1.t38(姓名,班级,住址) values【挖柳斯】("tom","nsd2107","bj");
37
-- mysql> insert into db1.t38(姓名,班级,住址) values("tom","nsd2107","bj");
38
Query OK, 1 row affected (0.05 sec)
39

40
//查看表记录
41
mysql> select【涩莱克特】  * from【弗乱】 db1.t38;
42
-- mysql> select * from db1.t38;
43
+--------+--------+---------+--------+
44
| 行号   | 姓名   | 班级    | 住址   |
45
+--------+--------+---------+--------+
46
|      1 | bob    | nsd2107 | bj     |
47
|      2 | bob    | nsd2107 | bj     |
48
|      3 | tom    | nsd2107 | bj     |
49
+--------+--------+---------+--------+
50
3 rows in set (0.00 sec)

1
讲解：CHAR是固定长度的字符类型，TEXT是可变长度文本类型不能指定长度，INT是整数类型，FLOAT是浮点数类型。

1
讲解：使用ALTER TABLE + MODIFY来修改字段数据类型，CHANGE用于修改字段名。

1
讲解：这是最常用的复制表方法，会同时复制表结构和数据。

1
讲解：邮件地址是可变长度字符串，VARCHAR最适合。DATE是日期，DECIMAL是小数，TINYINT是整数。

1
讲解：NOT NULL约束确保字段必须有值，不能为空。

1
mysql> create table if not exists studb.books (
2
    -> id int auto_increment primary key,
3
    -> book_title varchar(100),
4
    -> publish_year year,
5
    -> price decimal(10,2)
6
    -> );
7
Query OK, 0 rows affected (0.94 sec)
8

9
mysql> desc studb.books;
10
+--------------+---------------+------+-----+---------+----------------+
11
| Field        | Type          | Null | Key | Default | Extra          |
12
+--------------+---------------+------+-----+---------+----------------+
13
| id           | int           | NO   | PRI | NULL    | auto_increment |
14
| book_title   | varchar(100)  | YES  |     | NULL    |                |
15
| publish_year | year          | YES  |     | NULL    |                |
16
| price        | decimal(10,2) | YES  |     | NULL    |                |
17
+--------------+---------------+------+-----+---------+----------------+
18
4 rows in set (0.00 sec)
19

20
mysql>

1
mysql>
2
mysql> alter table studb.books add author varchar(50);
3
Query OK, 0 rows affected (2.88 sec)
4
Records: 0  Duplicates: 0  Warnings: 0
5

6
mysql> desc studb.books;
7
+--------------+---------------+------+-----+---------+----------------+
8
| Field        | Type          | Null | Key | Default | Extra          |
9
+--------------+---------------+------+-----+---------+----------------+
10
| id           | int           | NO   | PRI | NULL    | auto_increment |
11
| book_title   | varchar(100)  | YES  |     | NULL    |                |
12
| publish_year | year          | YES  |     | NULL    |                |
13
| price        | decimal(10,2) | YES  |     | NULL    |                |
14
| author       | varchar(50)   | YES  |     | NULL    |                |
15
+--------------+---------------+------+-----+---------+----------------+
16
5 rows in set (0.00 sec)

1
mysql> create table studb.books_archive select * from studb.books;
2
Query OK, 0 rows affected (1.29 sec)
3
Records: 0  Duplicates: 0  Warnings: 0
4

5
mysql> show tables;
6
+-----------------+
7
| Tables_in_studb |
8
+-----------------+
9
| books           |
10
| books_archive   |
11
| salary          |
12
| salary2         |
13
| stuinfo         |
14
| t1              |
15
| t2              |
16
| t6              |
17
| t8              |
18
+-----------------+
19
9 rows in set (0.00 sec)
20

21
mysql> desc books_archive;
22
+--------------+---------------+------+-----+---------+-------+
23
| Field        | Type          | Null | Key | Default | Extra |
24
+--------------+---------------+------+-----+---------+-------+
25
| id           | int           | NO   |     | 0       |       |
26
| book_title   | varchar(100)  | YES  |     | NULL    |       |
27
| publish_year | year          | YES  |     | NULL    |       |
28
| price        | decimal(10,2) | YES  |     | NULL    |       |
29
| author       | varchar(50)   | YES  |     | NULL    |       |
30
+--------------+---------------+------+-----+---------+-------+
31
5 rows in set (0.00 sec)

1
mysql> alter table books modify price decimal(10,2) default 0;
2
Query OK, 0 rows affected (0.23 sec)
3
Records: 0  Duplicates: 0  Warnings: 0
4

5
mysql> desc books;
6
+--------------+---------------+------+-----+---------+----------------+
7
| Field        | Type          | Null | Key | Default | Extra          |
8
+--------------+---------------+------+-----+---------+----------------+
9
| id           | int           | NO   | PRI | NULL    | auto_increment |
10
| book_title   | varchar(100)  | YES  |     | NULL    |                |
11
| publish_year | year          | YES  |     | NULL    |                |
12
| price        | decimal(10,2) | YES  |     | 0.00    |                |
13
| author       | varchar(50)   | YES  |     | NULL    |                |
14
+--------------+---------------+------+-----+---------+----------------+
15
5 rows in set (0.00 sec)
16

17
mysql>

1
mysql> alter table books modify book_title varchar(100) not null;
2
Query OK, 0 rows affected (2.19 sec)
3
Records: 0  Duplicates: 0  Warnings: 0
4

5
mysql> desc books;
6
+--------------+---------------+------+-----+---------+----------------+
7
| Field        | Type          | Null | Key | Default | Extra          |
8
+--------------+---------------+------+-----+---------+----------------+
9
| id           | int           | NO   | PRI | NULL    | auto_increment |
10
| book_title   | varchar(100)  | NO   |     | NULL    |                |
11
| publish_year | year          | YES  |     | NULL    |                |
12
| price        | decimal(10,2) | YES  |     | 0.00    |                |
13
| author       | varchar(50)   | YES  |     | NULL    |                |
14
+--------------+---------------+------+-----+---------+----------------+
15
5 rows in set (0.00 sec)