1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
| 语法
ALTER {DATABASE | SCHEMA } db_name alter_specification…
# alter_specification…表示改什麼，能修改的就是字符集與排序規則，偶爾會昇級數據字典名稱，在老版本遷移到新版本數據庫時才用昇級數據字典名稱。用ALTER DATABASE | SCHEMA db_name UPGRADE DATA DIRECTORY NAME就可升級。alter_specification可以是[DEFAULT] CHARACTER SET [=] charset_name或[DEFAULT] COLLATE [=] collation_name
mysql> use students;
mysql> ALTER TABLE test ADD UNIQUE KEY (Couse);
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
# 添加唯一鍵索引
mysql> ALTER TABLE test CHANGE Couse Course VARCHAR(50) NOT NULL;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
# 改變字段名稱
mysql> ALTER TABLE test ADD starttime date default '20190105';
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
# 向test表添加字段 starttime，類型是date默認值是default '20190105'
mysql> ALTER TABLE test RENAME TO testcourses1;
Query OK, 0 rows affected (0.00 sec)
# 将test改表名为testcourses1
mysql> RENAME TABLE testcourses1 TO test;
Query OK, 0 rows affected (0.00 sec)
# 使用RENAME TABLE命令将名字改回test。
mysql> SHOW TABLES;
+--------------------+
| Tables_in_students |
+--------------------+
| courses |
| test |
| testcourses |
+--------------------+
3 rows in set (0.00 sec)
例：
mysql> CREATE TABLE student (SID INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,Name VARCHAR(30),CID INT NOT NULL);
Query OK, 0 rows affected (0.01 sec)
mysql> INSERT INTO student (Name,CID) VALUES ('Yue Buqun',2),('Zhang Wuji',1);
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> SELECT Name,Couse FROM student,courses WHERE student.CID=courses.CID;
+------------+-------------+
| Name | Couse |
+------------+-------------+
| Yue Buqun | pixiejianfa |
| Zhang Wuji | Hamogong |
+------------+-------------+
2 rows in set (0.00 sec)
# 組合查詢/多表查詢；查询student,courses两个表，显示其中student表CID字段等于courses表CID字段的部分
mysql> ALTER TABLE student MODIFY CID TINYINT UNSIGNED NOT NULL;
Query OK, 2 rows affected (0.02 sec)
Records: 2 Duplicates: 0 Warnings: 0
# 改字段類型
mysql> ALTER TABLE courses ENGINE=InnoDB;
Query OK, 3 rows affected (0.02 sec)
Records: 3 Duplicates: 0 Warnings: 0
# 改存儲引擎
mysql> ALTER TABLE student ADD FOREIGN KEY (CID) REFERENCES courses (CID);
Query OK, 2 rows affected (0.02 sec)
Records: 2 Duplicates: 0 Warnings: 0
# 添加外鍵用FOREIGN KEY，外鍵只能在支持事物的存儲引擎上使用，如Inodb，添加外鍵後就有了引用型約束，當插入的信息是關聯表中沒有的信息時是不能插入的。 REFERENCES表示這張表參考哪張表的哪個字段
# 添加时两个表的存储引擎应该都是InnoDB，两个字段的属性也应该一样。如下：
mysql> desc courses;
+-------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+----------------+
| CID | tinyint(3) unsigned | NO | PRI | NULL | auto_increment |
mysql> desc student;
+-------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+----------------+
| CID | tinyint(3) unsigned | NO | MUL | NULL | |
mysql> ALTER TABLE student ADD FOREIGN KEY foreign_cid (CID) REFERENCES courses (CID);
Query OK, 2 rows affected (0.02 sec)
Records: 2 Duplicates: 0 Warnings: 0
# 給外鍵取名叫foreign_cid，就是要添加外键时在FOREIGN KEY后面加入一个名字。与上一条命令相同。
mysql> select * from courses;
+-----+---------------+
| CID | Couse |
+-----+---------------+
| 1 | Hamogong |
| 2 | pixiejianfa |
| 3 | Kuihuabaodian |
+-----+---------------+
3 rows in set (0.00 sec)
mysql> select * from student;
+-----+------------+-----+
| SID | Name | CID |
+-----+------------+-----+
| 1 | Yue Buqun | 2 |
| 2 | Zhang Wuji | 1 |
+-----+------------+-----+
2 rows in set (0.00 sec)
mysql> INSERT INTO student (Name,CID) VALUES ('chenjialuo',1);
Query OK, 1 row affected (0.01 sec)
# 插入一条数据，因为student表中的CID字段是有外键约束的，依赖于courses表的CID字段。所以添加时，CID的数字一定是courses表中的CID字段有的数字，上面查看表courses表中的CID字段只有1、2、3,所以这里使用了1。如果是courses表中的CID字段中不存在的数字，那么就会报错，"ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`students`.`student`, CONSTRAINT `student_ibfk_1` FOREIGN KEY (`CID`) REFERENCES `courses` (`CID`))"
mysql> select * from student;
+-----+------------+-----+
| SID | Name | CID |
+-----+------------+-----+
| 1 | Yue Buqun | 2 |
| 2 | Zhang Wuji | 1 |
| 4 | chenjialuo | 1 |
+-----+------------+-----+
3 rows in set (0.00 sec)
mysql> DELETE FROM courses WHERE CID=1;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`students`.`student`, CONSTRAINT `student_ibfk_1` FOREIGN KEY (`CID`) REFERENCES `courses` (`CID`))
# 刪表中信息，有外鍵約束時是不能刪的，因为courses表中CID为1的行已经被student表中的CID表引用了，如果将上面的CID改为等于3，就不会有报错了，因为没被引用。外鍵與存儲引擎有關，外鍵約束會極大地消耗資源的
|