02. MySQL - 02. DDL数据定义语言 - 《Python BI AI》

01. 数据库、数据表的基本操作
- 1.1 数据库的基本操作
- 1.2 数据表的基本操作
02. MySQL数据类型与字段约束
声明字段
声明约束
- 2.4.4 默认值约束
03. 表结构相关操作

01. 数据库、数据表的基本操作

1.1 数据库的基本操作

1.1.1 数据库的创建

创建数据库的基本SQL语法：CREATE DATABASE 数据库名;

mysql> CREATE DATABASE db1;
Query OK, 1 row affected (0.01 sec)

当数据库不存在时创建数据库：CREATE DATABASE IF NOT EXISTS 数据库名;
- 当数据库已经存在时，再执行基本的CREATE语句就会报错。
```
mysql> CREATE DATABASE db1;
ERROR 1007 (HY000): Can't create database 'db1'; database exists
```
- 要让DBMS动态的识别数据库是否已经存在，存在就不创建，不存在就创建，则可以给SQL语句加上IF NOT EXISTS这些关键字。 ```sql mysql> CREATE DATABASE IF NOT EXISTS db1; Query OK, 1 row affected, 1 warning (0.00 sec) — 可以看见，此时只会产生一个警告，而不会报错。 — 警告并不会影响程序的继续运行。

mysql> CREATE DATABASE IF NOT EXISTS db2; Query OK, 1 row affected (0.00 sec)


- 创建数据库时指定字符集：`CREATE DATABASE [IF NOT EXISTS] 数据库名 charset=字符集;`
   - MySQL 5.x默认的字符集是latin1，这个字符集在处理中文字符时可能出现乱码。
   - 推荐使用`utf8mb4`编码集，这个编码集相比于`utf8`可以兼容更多的特殊字符。（MySQL 8.x默认的字符集就是utf8mb4）
```sql
mysql> CREATE DATABASE IF NOT EXISTS db3 charset='utf8mb4';
Query OK, 1 row affected (0.00 sec)

使用以下语句可以查看DBMS中所有数据库的字符集。

SELECT schema_name,default_character_set_name FROM information_schema.schemata;

改变数据库字符集的SQL语句：ALTER DATABASE 库名 CHARACTER SET 字符集编码;
示例：将db2库的字符集改为GBK编码格式： ```sql mysql> ALTER DATABASE db2 CHARACTER SET gbk; Query OK, 1 row affected (0.00 sec)

<a name="nHKb5"></a>
#### 1.1.2 显示所有数据库
- 可以使用`SHOW DATABASES;`命令查看当前DBMS中所有已存在的数据库。
```sql
mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| db1                |
| db2                |
| db3                |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
7 rows in set (0.00 sec)

注意：information_schema、mysql、performance_schema、sys这四个都是系统数据库，主要用于维持DBMS的运行，不要随意删除。

1.1.3 切换当前数据库
在1.1.2的运行结果中可以看到一个DBMS中存在着多个DB。
SQL中大多数的操作是针对于某个具体的DB而言的，因此可以使用USE 数据库名;语句切换到指定的数据库上。
示例：将当前数据库切换成db3数据库。
```
mysql> USE db3;
Database changed
```
1.1.4 删除数据库

删除数据库的基本SQL语法：DROP DATABASE 数据库名;

mysql> DROP DATABASE db1;
Query OK, 0 rows affected (0.03 sec)

当数据库存在时删除数据库：DROP DATABASE IF EXISTS 数据库名;
- 与CREATE语句思想一样，当数据库已经不存在时，在使用基本删除SQL就会报错。 ```sql mysql> SHOW DATABASES; +——————————+ | Database | +——————————+ | db2 | | db3 | | information_schema | | mysql | | performance_schema | | sys | +——————————+ 6 rows in set (0.00 sec)

mysql> DROP DATABASE db1; ERROR 1008 (HY000): Can’t drop database ‘db1’; database doesn’t exist


   - 此时，可以通过添加`IF EXISTS`关键字来动态删除数据库。
```sql
mysql> DROP DATABASE IF EXISTS db1;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> DROP DATABASE IF EXISTS db2;
Query OK, 0 rows affected (0.00 sec)

1.2 数据表的基本操作

1.2.1 数据表的创建

创建表的CREATE语句：

CREATE TABLE [IF NOT EXISTS] 表名(
  列名1 列的类型[(长度) 约束],
  列名2 列的类型[(长度) 约束],
  ...
  列名n 列的类型[(长度) 约束]
);

注意点：
- 列的数据类型可以不指定数据长度，每个数据类型长度都存在一个缺省值。
- 字段的约束不是必须的，若一个字段对其数据没有什么限制，则该字段可以省略约束的声明。
表创建完成后，可以通过SHOW CREATE TABLE 表名;的方式来查看建表语句。

1.2.2 显示所有表
在指定的数据库内使用SHOW TABLES;语句可以查看当前数据库内所有已存在的数据表。

1.2.3 表结构查看
可以使用DESCRIBE 表名;的方式查看表结构。
这条命令可以简写成：DESC 表名;。

1.2.4 删除数据表
可以使用DROP TABLE 表名;的方式来删除一张表。
为了防止表已经不存在导致的报错，可以给SQL语句加上IF EXISTS关键字，即：DROP TABLE IF EXISTS 表名;。

02. MySQL数据类型与字段约束
从CMD中进入MySQL，使用? data types;语句可以查看到MySQL中支持的所有数据类型。

2.1 数据类型一：数值型

2.1.1 整型
MySQL中的整型有：TINYINT、SMALLINT、MEDIUMINT、INT、INTEGER、BIGINT。其中INT与INTEGER是相同的。

上述所有整型类型的数据只有存储范围的差别，其他完全相同。
整型数据类型的特点：
- 如果不设置无符号还是有符号，则默认是设置有符号。如果想要设置无符号，则需要在类型后面添加UNSIGNED关键字。
- 如果插入的数值超出了整型的范围，会报OUT OF RANGE异常，并且就近插入临界值。
- 如果不设置类型的长度，则会有默认长度（如无符号INT默认长度为11，有符号默认为10）。
  - 长度代表了显示的最大宽度，如果不够会用0在左边填充。如果需要显示填充的0，则需要在类型后使用ZEROFILL关键字。
使用UNSIGNED关键字可以创建无符号数据类型的字段。无符号数据类型不支持负数数据。 ```sql USE db3;

DROP TABLE IF EXISTS tab_int; CREATE TABLE tab_int ( tId INT ); INSERT INTO tab_int VALUE(-11); # 成功 SELECT * FROM tab_int; # 有数据，数据为-11

DROP TABLE IF EXISTS tab_int; CREATE TABLE tab_int ( tId INT UNSIGNED ); INSERT INTO tab_int VALUE(-11); # 失败 SELECT * FROM tab_int; # 有数据，数据为0


- 使用`ZEROFILL`可以看见填充的0。
```sql
DROP TABLE IF EXISTS tab_int;
CREATE TABLE tab_int (
    t1 INT(7),
    t2 INT(7) ZEROFILL
);
INSERT INTO tab_int VALUE(123, 123);
SELECT * FROM tab_int;

2.1.2 浮点型

浮点型的全写是FLOAT(M, D)与DOUBLE(M, D)，但是可以简写为FLOAT与DOUBLE。
- M是指插入数据的长度（整数部位+小数部位），D是指精确到小数点后D位（即小数部位）。
- 比如对于FLOAT(5, 2)而言，插入123.456的结果就是123.46，插入123.4的结果就是123.40。
- 对于(5, 2)，其最大范围是999.99，加入这时候插入大于999.99的数，就显示999.99。
- FLOAT和DOUBLE省略M和D后没有精度限制。

T2LN387T1LH(CIKWELWGOBG.png

一般而言，优先使用FLOAT存储，当FLOAT存不下时再考虑使用DOUBLE存储。

2.1.3 定点型
DECIMAL(M, D)可以简写为DEC(M, D)。
对于定点型数据而言，M和D也可以省略，省略后使用默认值DECIMAL(10, 0)代替。

1630215576(1).jpg

如果对于存储的数据的精度要求较高（如货币运算等），建议选择使用定点数存储。

2.2 数据类型二：字符型

2.2.1 CHAR与VARCHAR（最常用）
在MySQL中保存较短的文本可以用CHAR或者VARCHAR。

CHAR与VARCHAR最大的区别是CHAR是固定长度的字符，VARCHAR是可变长度的字符。
- 加入在建表时nation字段的类型设置为CHAR(10)，那么在插入”中国”两个字符时，CHAR(10)存储的中国依旧占10个字符，而VARCHAR(10)存储的中国只占其本身的2个字符 + 1个（即3个字符）。
- 相对而言，CHAR(M)在效率上要比VARCHAR(M)高。
- 加入插入字段的数据的长度变化不大（比如sex字段只有可能是男、女，固定占1个字符），那么建议使用CHAR(M)，即不会浪费太多空间还可以提升效率；反之，如果数据的长度变化较大，那么建议使用VARCHAR(M)。
CHAR(M)的M可以省略，缺省值为CHAR(1)；VARCHAR(M)的M不可以省略。
CHAR的数据丢失问题：
- 对于CHAR(M)而言，若插入的数据字符长度不足M时，会在字符串的右边用空格补齐，直到长度为M时，再将数据插进去。
- 此时若字符串的右边本来就带有空格，比如abc，那么这个数据插到CHAR(M)的字段列后，取出来的就只剩下了abc。
  2.2.2 ENUM与SET
ENUM称为枚举类型，要求插入的值必须属于列表中指定的值之一。
- 如果列表成员为1~255，则需要1个字节存储。
- 如果列表成员为255~65535，则需要2个字节存储。
- 如果插入的数据不在Enum的列表中：
  - MySQL 5.x中：则会出现一个警告，并且插入的是空值。
  - MySQL 8.x中：报错，无法插入。 ```sql DROP TABLE IF EXISTS test_enum; CREATE TABLE test_enum ( t ENUM(‘a’, ‘b’, ‘c’) ); INSERT INTO test_enum VALUE(‘a’); # 插入a INSERT INTO test_enum VALUE(‘b’); # 插入b INSERT INTO test_enum VALUE(‘c’); # 插入c INSERT INTO test_enum VALUE(‘m’); # 5.x警告并插入空值；8.x无法插入。 INSERT INTO test_enum VALUE(‘A’); # 插入a

SELECT * FROM test_enum;


- `SET`称为集合类型，与`ENUM`很类似。
   - `SET`类型与`ENUM`类型最大的区别是：`SET`类型一次可以选取多个成员，而`ENUM`只能选一个。
   - `SET`可以保存0~64个成员，根据成员个数不同，存储所占的字节数也不同。
![image.png](https://cdn.nlark.com/yuque/0/2022/png/2692415/1665836475613-cc35e3cc-2043-4c6b-a0d5-95219af7f884.png#averageHue=%23e3e3e3&clientId=uc55cb38b-3ae1-4&from=paste&height=116&id=uaaed426c&originHeight=116&originWidth=257&originalType=url&ratio=1&rotation=0&showTitle=false&size=3517&status=done&style=none&taskId=u829c2312-2ff3-417f-bb54-b56273c9d14&title=&width=257)
```sql
DROP TABLE IF EXISTS test_set;
CREATE TABLE test_set (
    t SET('a', 'b', 'c', 'd')
);
INSERT INTO test_set VALUE('a');      # 插入a
INSERT INTO test_set VALUE('a,b');    # 插入a与b
INSERT INTO test_set VALUE('a,c,d');  # 插入a、c和d
INSERT INTO test_set VALUE('m');      # 5.x警告并插入空值；8.x无法插入。
INSERT INTO test_set VALUE('A');      # 插入a
SELECT * FROM test_set;

值得注意的是，插入集合类的数据值，”,”前后不能有空格，比如’a,b’插入的是a和b，而’a, b’则只插入a。
- SET类型与ENUM类型都不区分大小写，所以插入数据A时数据表中实际上得到的是a。
  2.2.3 BLOB、BINARY、VARBINARY与TEXT

BLOB类型主要用于存储二进制数据，一般来说需要结合编程语言的数据库连接实现，无法直接用INSERT插入数据。
BLOB用于存储比较大的二进制数据，而BINARY与VARBINARY用于存储比较小的二进制数据。且二者的关系如同CHAR与VARCHAR。
TEXT用于存储较长的文本数据，当存储的数据大于0~65535这个范围时，CHAR和VARCHAR都无法存储，此时可以考虑使用TEXT存储。

2.3 数据类型三：时间日期型

2.3.1 时间日期概述
时间日期型用于保存时间与日期数据，SQL要求时间日期型的数据必须用单引号引起来。

存储单种数据可以使用DATE（存储日期）、TIME（存储时间）、YEAR（存储年份）。
存储复合数据可以使用DATETIME（推荐使用，范围大）、TIMESTAMP（不推荐使用，范围小）。

2.3.2 DATETIME与TIMESTAMP的区别
TIMESTAMP支持的时间范围较小，取值范围：19700101080001—2038年的某个时间；DATETIME支持的时间范围较大，取值范围：1000-1-1—9999-12-31。
TIMESTAMP和实际时区有关，更能反映实际的日期；而DATETIME则只能反映出插入时的当地时区。
TIMESTAMP的属性受MySQL版本和SQLMode的影响很大。 ```sql DROP TABLE IF EXISTS test_timedate; CREATE TABLE test_timedate ( t1 DATETIME, t2 TIMESTAMP ); INSERT INTO test_timedate VALUE(NOW(), NOW());

SELECT * FROM test_timedate;

<a name="OZRAT"></a>
#### 2.3.3 不同时区的数据查询
- 在MySQL中存在一个常量`time_zone`用于存储当前的时区。
- 同一个时间日期值在不同时区下`TIMESTAMP`查询到的结果不一样，而`DATETIME`在不同时区下查询到的结果都是表中确定的值。
```sql
-- 查看当前MySQL的时区与表中的数据
SHOW VARIABLES LIKE 'time_zone';
SELECT * FROM test_timedate;
-- 将时区设置为+9区，并查看修改结果。
SET time_zone='+9:00';
SHOW VARIABLES LIKE 'time_zone';
-- 再次查询表中的数据，发现有变化。
-- 这时就是查询表中的时间值在+9区的时区中的时间。
SELECT * FROM test_timedate;

2.4 字段约束

注意：字段约束不是必须的；只要给需要约束数据的字段加上字段约束即可，不需要约束数据的字段可以省略约束声明。

2.4.1 主键索引约束
主键索引约束的概念：
- 给表设置一个字段，这个字段中的每一行数据都是唯一的。
- 可以通过这个字段的索引值来精确定位某一行数据，类似于人的身份证号码可以唯一定位一个人一样。
- 建议每一张表都有一个主键约束字段。
主键索引约束的要求：数据不允许重复，数据不能为空（null）。
建表时给字段添加主键约束的方式：
- 方式一：字段名字段类型 PRIMARY KEY；在声明字段的同时，加上主键的声明。 ```sql DROP TABLE IF EXISTS pk_table1; CREATE TABLE IF NOT EXISTS pk_table1 ( id INT PRIMARY KEY, name VARCHAR(20) ); INSERT INTO pk_table1 VALUE(1, ‘Jack’); INSERT INTO pk_table1 VALUE(2, ‘Tom’); INSERT INTO pk_table1 VALUE(1, ‘Bob’); # 主键值重复，数据插入失败。

SELECT * FROM pk_table1;


   - 方式二：`CONSTRAINT 主键约束名 PRIMARY KEY(主键字段)`；在字段声明结束后，单独声明主键约束。
```sql
DROP TABLE IF EXISTS pk_table2;
CREATE TABLE IF NOT EXISTS pk_table2 (
    id INT,
    name VARCHAR(20),
    CONSTRAINT pk PRIMARY KEY(id)
);
INSERT INTO pk_table2 VALUE(1, 'Jack');
INSERT INTO pk_table2 VALUE(2, 'Tom');
INSERT INTO pk_table2 VALUE(1, 'Bob');  # 主键值重复，数据插入失败。
SELECT * FROM pk_table2;

AUTO_INCREMENT自增长：如果主键的值是整形数据，则可以让值进行自增长（从1开始，自动+1）。 ```sql DROP TABLE IF EXISTS auto_table; CREATE TABLE IF NOT EXISTS auto_table ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(20) ); INSERT INTO auto_table(name) VALUE (‘Tom’); # 对于自增长的列而言，无需手动插值 INSERT INTO auto_table(name) VALUE (‘Bob’); INSERT INTO auto_table(name) VALUE (‘Jack’);

SELECT * FROM auto_table;

<a name="wAErt"></a>
#### 2.4.2 唯一索引约束
- 唯一索引约束的概念：
   - 唯一约束`UNIQUE`用于保证每条记录上该字段的值唯一。
   - 与`PRIMARY KEY`最大的区别就是`UNIQUE`字段的数据允许为`NULL`。
- 建表时给字段添加唯一约束的方式：
   - 方式一：`字段名 字段类型 UNIQUE`；在声明字段的同时，加上唯一约束的声明。
```sql
DROP TABLE IF EXISTS classroom;
CREATE TABLE IF NOT EXISTS classroom (
    sid INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(20),
    seat INT UNIQUE  # 教室里的学生应该都有自己唯一的座位号
);
INSERT INTO classroom(name, seat) VALUE ('Tom', 25);
INSERT INTO classroom(name, seat) VALUE ('Bob', 37);
INSERT INTO classroom(name, seat) VALUE ('Jack', 13);
INSERT INTO classroom(name, seat) VALUE ('Alice', 25);  # 座位重复，数据插入失败
SELECT * FROM classroom;

方式二：CONSTRAINT 唯一约束名 UNIQUE(唯一约束字段)；在字段声明结束后，单独声明唯一约束。 ```sql DROP TABLE IF EXISTS classroom; CREATE TABLE IF NOT EXISTS classroom (
声明字段
sid INT AUTO_INCREMENT, name VARCHAR(20), seat INT,
声明约束
CONSTRAINT pk PRIMARY KEY(sid), CONSTRAINT uq UNIQUE(seat) );

INSERT INTO classroom(name, seat) VALUE (‘Tom’, 25); INSERT INTO classroom(name, seat) VALUE (‘Bob’, 37); INSERT INTO classroom(name, seat) VALUE (‘Jack’, 13); INSERT INTO classroom(name, seat) VALUE (‘Alice’, 25); # 座位重复，数据插入失败

SELECT * FROM classroom;

<a name="rydr2"></a>
#### 2.4.3 非空约束
- 非空约束`NOT NULL`，要求每条记录中该字段的位置必须有值，不能为空。
- 建表时给字段添加非空约束的方式：`字段名 字段类型 NOT NULL`。
```sql
DROP TABLE IF EXISTS classroom;
CREATE TABLE IF NOT EXISTS classroom (
    sid INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(20) NOT NULL,
    seat INT UNIQUE
);
INSERT INTO classroom(name, seat) VALUE ('Tom', 25);
INSERT INTO classroom(name, seat) VALUE ('Bob', 37);
INSERT INTO classroom(name, seat) VALUE ('Jack', 13);
INSERT INTO classroom(seat) VALUE (36);
SELECT * FROM classroom;

表级约束不支持非空约束，因此无法用CONSTRAINT的方式给字段添加非空约束。

2.4.4 默认值约束
默认约束DEFAULT，当INSERT插入时，该字段没有值时，MySQL会自动为其添加指定的默认值。
建表时给字段添加默认约束的方式：字段名字段类型 DEFAULT 默认值。 ```sql DROP TABLE IF EXISTS student1; CREATE TABLE IF NOT EXISTS student1 ( sid INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(20) NOT NULL, gender INT DEFAULT 1 # 性别字段，1为男；0为女，默认为1 );

INSERT INTO student1(name, gender) VALUE (‘Tom’, 1); INSERT INTO student1(name, gender) VALUE (‘Alice’, 0); INSERT INTO student1(name) VALUE (‘Jack’); # 不设置默认值字段数据，默认为1

SELECT * FROM student1;


- 表级约束不支持默认约束，因此无法用`CONSTRAINT`的方式给字段添加默认约束。
<a name="SAN7j"></a>
#### 2.4.5 外键约束
- 外键约束`FOREIGN KEY`用于限制两张表的关系。
- 建表时给字段添加外键约束的方式：`CONSTRAINT 外键约束名 FOREIGN KEY(外键字段) REFERENCES 主表(关联主表的字段)`
   - 外键约束是表级约束，不能直接添加在一个字段上。
   - 外键约束添加在从表中。
- 外键约束的细节：
   - 外键用于保证从表中的指定字段的数据值必须来自主表中关联字段中的值（但外键字段允许存在空值）。
   - 对于一对多的关系而言，多为从表，一为主表。（比如班级表为主表，学生表为从表）
```sql
-- 班级表（主表）
DROP TABLE IF EXISTS class;
CREATE TABLE IF NOT EXISTS class (
    c_id INT PRIMARY KEY AUTO_INCREMENT,  # 班级编号
    c_name VARCHAR(20) NOT NULL,  # 班级名称
    c_address VARCHAR(20) NOT NULL DEFAULT '上海'  # 班级地址
);
INSERT INTO class(c_name, c_address) VALUE ('Python-202112', '北京');
INSERT INTO class(c_name, c_address) VALUE ('Java-202205', '北京');
INSERT INTO class(c_name) VALUE ('BigData-202204');
INSERT INTO class(c_name) VALUE ('AI-202201');
SELECT * FROM class;
-- 学生表（从表）
DROP TABLE IF EXISTS student;
CREATE TABLE IF NOT EXISTS student (
    sid VARCHAR(10) PRIMARY KEY,  # 学号
    name VARCHAR(20) NOT NULL,  # 姓名
    age INT DEFAULT 18,  # 年龄
    class_id INT,  # 班级编号
    CONSTRAINT fk_student_class FOREIGN KEY(class_id) REFERENCES class(c_id)
);
INSERT INTO student(sid, name, age, class_id) VALUE ('211200101', '杨承德', 21, 1);
INSERT INTO student(sid, name, age, class_id) VALUE ('211200102', '宋博雅', 23, 1);
INSERT INTO student(sid, name, age, class_id) VALUE ('220500201', '李慕云', 22, 2);
INSERT INTO student(sid, name, age, class_id) VALUE ('220500202', '施凤兰', 20, 2);
INSERT INTO student(sid, name, age, class_id) VALUE ('220400301', '凌飞轩', 21, 3);
INSERT INTO student(sid, name, age, class_id) VALUE ('220400302', '唐婧冉', 24, 3);
INSERT INTO student(sid, name, age, class_id) VALUE ('220100401', '戴岳', 19, 4);
INSERT INTO student(sid, name, age, class_id) VALUE ('220100402', '胡金辉', 23, 4);
SELECT * FROM student;
-- 关联查询
SELECT s.*, c.c_name, c.c_address
FROM student AS s
JOIN class AS c
ON c.c_id = s.class_id;

对于多对多的关系而言，需要设置一张中间联系表，来建立两表的关联规则。（学生信息和课程信息就是多对多的关系） ```sql — 课程信息表 DROP TABLE IF EXISTS course; CREATE TABLE IF NOT EXISTS course ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(20) NOT NULL ); INSERT INTO course(name) VALUES (‘Python’), (‘MySQL’), (‘Django’), (‘Java’), (‘SSM’), (‘Hadoop’), (‘OpenCV’);

— 学生课程表（中间联系表） DROP TABLE IF EXISTS stu_course; CREATE TABLE IF NOT EXISTS stu_course ( s_id VARCHAR(10), c_id INT, CONSTRAINT fk_stu_course_student FOREIGN KEY(s_id) REFERENCES student(sid), CONSTRAINT fk_stu_course_course FOREIGN KEY(c_id) REFERENCES course(id) );

— 建立数据联系 INSERT INTO stu_course VALUES (‘211200101’, 1), (‘211200101’, 2), (‘211200101’, 3), (‘211200101’, 7), (‘211200102’, 1), (‘211200102’, 2), (‘211200102’, 3), (‘211200102’, 7), (‘220500201’, 4), (‘220500201’, 2), (‘220500201’, 5), (‘220500202’, 4), (‘220500202’, 2), (‘220500202’, 5), (‘220400301’, 4), (‘220400301’, 2), (‘220400301’, 6), (‘220400302’, 4), (‘220400302’, 2), (‘220400302’, 6), (‘220100401’, 1), (‘220100401’, 2), (‘220100401’, 7), (‘220100402’, 1), (‘220100402’, 2), (‘220100402’, 7); SELECT * FROM stu_course;

— 连接查询 SELECT * FROM student AS s JOIN stu_course AS sc ON s.sid = sc.s_id JOIN course AS c on sc.c_id = c.id JOIN class AS c2 on c2.c_id = s.class_id;

<a name="XRo7M"></a>
#### 2.4.6 注释约束
- 注释约束`COMMENT`实际上就是字段的注释，用于解释说明一个字段的信息罢了。
- 建表时给字段添加注释约束的方式：`字段名 字段类型 COMMENT 注释信息`。
- 示例：2.4.5中学生表在创建时有很多注释信息，可以改写成：
```sql
CREATE TABLE IF NOT EXISTS student (
  sid VARCHAR(10) PRIMARY KEY COMMENT '学号',
  name VARCHAR(20) NOT NULL COMMENT '姓名',
  age INT DEFAULT 18 COMMENT '年龄',
  class_id INT COMMENT '班级编号',
  CONSTRAINT fk_student_class FOREIGN KEY(class_id) REFERENCES class(c_id)
);

03. 表结构相关操作

3.1 数据表准备

运行以下SQL语句： ``sql -- 创建数据库 CREATE DATABASE IF NOT EXISTSschool; USEschool`;

— 创建班级表 DROP TABLE IF EXISTS class; CREATE TABLE IF NOT EXISTS class ( cno INT PRIMARY KEY COMMENT ‘班级编号’, cname VARCHAR(255) NOT NULL COMMENT ‘班级名称’ ); DESC class;

— 创建学生表 DROP TABLE IF EXISTS student; CREATE TABLE IF NOT EXISTS student ( sno INT PRIMARY KEY AUTO_INCREMENT COMMENT ‘学号’, sname VARCHAR(255) NOT NULL COMMENT ‘学生姓名’, birthday DATE COMMENT ‘出生日期’, gender VARCHAR(5) COMMENT ‘性别’, cno INT COMMENT ‘班级编号’, CONSTRAINT fk_student_class FOREIGN KEY(cno) REFERENCES class(cno) ); DESC student;

— 创建课程表 DROP TABLE IF EXISTS course; CREATE TABLE IF NOT EXISTS course ( cid INT PRIMARY KEY COMMENT ‘课程编号’, cname VARCHAR(255) NOT NULL COMMENT ‘课程名称’ ); DESC course;

— 创建成绩表 DROP TABLE IF EXISTS score_tb; CREATE TABLE IF NOT EXISTS score_tb ( sid INT PRIMARY KEY AUTO_INCREMENT COMMENT ‘成绩表的主键’, sno INT COMMENT ‘学生编号，用于关联学生表’, cid INT COMMENT ‘课程编号，用于关联课程表’, score DECIMAL(5, 2) DEFAULT 0 COMMENT ‘成绩’, CONSTRAINT fk_score_student FOREIGN KEY(sno) REFERENCES student(sno), CONSTRAINT fk_score_course FOREIGN KEY(cid) REFERENCES course(cid) ); DESC score_tb;

— 检查表创建 SHOW TABLES;

<a name="N6l0e"></a>
### 3.2 修改字段
<a name="Cdnzy"></a>
#### 3.2.1 增加字段
- 增加字段的基本语句：`ALTER TABLE 表名 ADD 列名 数据类型 [约束];`
- 示例：在班级表中增加一行`VARCHAR`类型的班级位置字段，这个字段没有特殊的约束。
```sql
ALTER TABLE `class` ADD `location` VARCHAR(255);
DESC `class`;

注意：这种形式添加的字段，默认是该表当前的最后一列。

3.2.2 删除字段
增加字段的基本语句：ALTER TABLE 表名 DROP 列名;
示例：将3.2.1在班级表中添加的location字段删除。
```
ALTER TABLE `class` DROP `location`;
DESC `class`;
```
3.2.3 增加字段（添加在第一列）
将字段新增到表第一列的SQL语句：ALTER TABLE 表名 ADD 列名数据类型 [约束] FIRST;
示例：在班级表的第一列添加location字段，这里要求非空约束。
```
ALTER TABLE `class` ADD `location` VARCHAR(255) NOT NULL FIRST;
DESC `class`;
```
3.2.4 增加字段（添加在指定字段后）
在已有字段后增加字段的SQL语句：ALTER TABLE 表名 ADD 列名数据类型 [约束] AFTER 已存在的字段名;
示例：在班级表的cno字段之后添加location字段，这里要求非空约束。 ``sql -- 因为3.2.3的代码已经向class表添加了location字段。 -- 因此需要先删除。 ALTER TABLEclassDROPlocation`;

— 添加字段 ALTER TABLE class ADD location VARCHAR(255) NOT NULL AFTER cno; DESC class;


- 为了不影响后续实验，请将location字段删除。
```sql
ALTER TABLE `class` DROP `location`;

3.2.5 修改已有字段

修改已有字段包括：修改字段名、修改字段的数据类型、修改字段约束。
修改已有字段的SQL语句：ALTER TABLE 表名 CHANGE 字段名新的字段名新的数据类型新的约束;
- 对于字段约束而言，若原来有字段约束，在CHANGE时没有声明约束，则代表去掉约束。（主键约束、外键约束无法用这种方式去处） ``sql -- 去掉成绩表sid字段的自增长约束。 ALTER TABLEscore_tbCHANGEsid`sid INT; DESC score_tb;

— 重新为成绩表sid字段设置自增长约束。 ALTER TABLE score_tb CHANGE sid sid INT AUTO_INCREMENT; DESC score_tb;


   - 对于字段名和数据类型而言，若不需要改变，则使用老的字段名和数据类型即可。
```sql
-- 将课程表的课程名称字段的数据类型改为300个长度的VARCHAR，字段名称和约束都不变。
-- 去掉成绩表sid字段的自增长约束。
ALTER TABLE `course` CHANGE `cname` `cname` VARCHAR(300) NOT NULL;
DESC `course`;

3.3 修改约束

这里主要针对主键约束、外键约束、唯一约束的修改。
非空约束、默认约束、自增长约束、注释约束使用3.2.5中介绍的方法修改即可。

3.3.1 查看表约束
查看表中所有索引约束的SQL语句：SHOW INDEX FROM 表名;
示例：查看课程表中的约束。
```
SHOW INDEX FROM course;
```
3.3.2 唯一约束的添加与删除
给表字段添加唯一索引约束的SQL语句：ALTER TABLE 表名 ADD UNIQUE(字段名);

示例：给课程表的课程名称字段添加唯一约束。

ALTER TABLE `course` ADD UNIQUE(`cname`);
SHOW INDEX FROM `course`;

删除表中指定字段的唯一索引约束的SQL语句：ALTER TABLE 表名 DROP INDEX 索引名;（一般情况下，唯一索引的索引名就是字段名）
示例：删除课程表中课程名称字段的唯一约束。
```
ALTER TABLE `course` DROP INDEX `cname`;
SHOW INDEX FROM `course`;
```
3.3.3 主键约束的添加与删除
删除表中指定字段的主键索引约束的SQL语句：ALTER TABLE 表名 DROP PRIMARY KEY;
两种不能直接删除主键约束的情况：
- 若需要删除主键的字段被其他表中的外键所关联，则无法直接删除主键。这种情况会报错：[HY000][1553] Cannot drop index 'PRIMARY': needed in a foreign key constraint。
- 若需要删除主键的字段存在自增长约束，则无法直接删除主键。这种情况会报错：[42000][1075] Incorrect table definition; there can be only one auto column and it must be defined as a key。
示例：删除成绩表中的主键。 ``sql -- 成绩表的主键sid字段存在自增长约束，需要先取消该字段自增长。 ALTER TABLEscore_tbCHANGEsid`sid INT; DESC score_tb;

— 删除主键约束。 ALTER TABLE score_tb DROP PRIMARY KEY; SHOW INDEX FROM score_tb;


- 给表字段添加主键索引约束的SQL语句：`ALTER TABLE 表名 ADD PRIMARY KEY(字段名);`
- 示例：给成绩表中的sid字段添加主键约束和自增长约束。
```sql
-- 添加主键约束
ALTER TABLE `score_tb` ADD PRIMARY KEY(`sid`);
-- 添加自增长约束
ALTER TABLE `score_tb` CHANGE `sid` `sid` INT AUTO_INCREMENT;
-- 验证修改
DESC `score_tb`;

3.3.4 外键约束的添加与删除

删除表中指定字段的外键索引约束的SQL语句：ALTER TABLE 表名 DROP FOREIGN KEY 外键约束名;
- 查看外键约束名的两种方式：查看表约束：SHOW INDEX FROM 表名;；查看建表语句：SHOW CREATE TABLE 表名;。
示例：删除成绩表中cid字段上的外键约束。 ``sql -- 通过查看表约束的Key_name的值确定外键约束名 SHOW INDEX FROMscore_tb`;

— 删除cid字段的外键约束。 ALTER TABLE score_tb DROP FOREIGN KEY fk_score_course;

— 删除完成后，查看建表语句可以发现cid字段对应的CONSTRAINT语句消失了。 SHOW CREATE TABLE score_tb;


- 给表字段添加外键索引约束的SQL语句：`ALTER TABLE 表名 ADD CONSTRAINT 外键约束名 FOREIGN KEY(外键字段) REFERENCES 主表(关联主表的字段);`。
- 示例：给成绩表中的cid字段加上外键约束，关联课程表的cid字段。
```sql
ALTER TABLE `score_tb` ADD CONSTRAINT `fk_score_course` FOREIGN KEY(`cid`) REFERENCES `course`(`cid`);

3.4 修改表名

修改数据表表名的SQL语句：ALTER TABLE 表名 RENAME 新表名;

02. DDL数据定义语言

01. 数据库、数据表的基本操作

1.1 数据库的基本操作

1.1.1 数据库的创建

1.1.3 切换当前数据库

1.1.4 删除数据库

1.2 数据表的基本操作

1.2.1 数据表的创建

1.2.2 显示所有表

1.2.3 表结构查看

1.2.4 删除数据表

02. MySQL数据类型与字段约束

2.1 数据类型一：数值型

2.1.1 整型

2.1.2 浮点型

2.1.3 定点型

2.2 数据类型二：字符型

2.2.1 CHAR与VARCHAR（最常用）

2.2.2 ENUM与SET

2.2.3 BLOB、BINARY、VARBINARY与TEXT

2.3 数据类型三：时间日期型

2.3.1 时间日期概述

2.3.2 DATETIME与TIMESTAMP的区别

2.4 字段约束

2.4.1 主键索引约束

声明字段

声明约束

2.4.4 默认值约束

03. 表结构相关操作

3.1 数据表准备

3.2.2 删除字段

3.2.3 增加字段（添加在第一列）

3.2.4 增加字段（添加在指定字段后）

3.2.5 修改已有字段

3.3 修改约束

3.3.1 查看表约束

3.3.2 唯一约束的添加与删除

3.3.3 主键约束的添加与删除

3.3.4 外键约束的添加与删除

3.4 修改表名