MySQL基础篇：数据定义语言DDL

---

1、介绍

DDL（Data Definition Languages）：数据定义语言，这些语言定义了不同的数据段、数据库、表、列、索引等数据库对象。

2、操作数据库Database

• database不能改名。
• 一些可视化工具可以改名，他是建新库，把所有表复制到新库，再删掉旧库完成的。

创建数据库

create database 数据库名 [charset  字符集];
#关键字大小写效果
CREATE DATABASE 数据库名;

如果不指定字符集，则按照安装mysql服务时选择的默认字符集

查看有哪些数据库

show databases;

提示：当前用户有权限查看的

删除数据库

drop database 数据库名;

选择数据库

use 数据库名;

查看当前正在使用哪个数据库

select database();

注意：要操作表和数据之前必须先说明是对哪个数据库进行操作

3、表结构的操作

查看当前数据库的所有表

# 前面必须有use 数据库名语句，否则报错
show tables; 
show tables from 数据库名;

创建表结构

类型	语句	示例
基础版	CREATE TABLE 表名称( 字段名1 数据类型1, 字段名2 数据类型2, 字段名3 数据类型3, 字段名4 数据类型4, );	CREATE TABLE emp( eid INT, ename VARCHAR(100), gender CHAR);
详细版	CREATE TABLE 表名称( 字段名1 数据类型1 主键 自增长, 字段名2 数据类型2 非空 默认值, 字段名3 数据类型3, 字段名4 数据类型4, ) ENGINE =当前表格的引擎 AUTO_INCREMENT=自增长的起始值 DEFAULT CHARSET=表数据的默认字符集;	CREATE TABLE t_stu( sid INT PRIMARY KEY AUTO_INCREMENT, sname VARCHAR(100) NOT NULL, gender CHAR NOT NULL DEFAULT ‘男’ )ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

查看表结构

desc 表名称；
#查看表的定义
show create table 表名；

删除表结构

drop table 表名称;

说明：

• 数据和结构都被删除
• 所有正在运行的相关事务被提交
• 所有相关索引被删除
• DROP TABLE语句不能回滚

修改表结构

（1）重命名表

alter table 表名 rename 新表名；
rename table 表名 to 新表名；

（2）增加一列

#默认在最后
alter table 表名 add [column] 列名 数据类型；
alter table 表名 add [column] 列名 数据类型 after 某一列；
alter table 表名 add [column] 列名 数据类型 first；

（3）删除列

alter table 表名 drop [column] 列名；

（4）修改列类型

alter table 表名 modify [column] 列名 数据类型；
alter table 表名 modify [column] 列名 数据类型 after 某一列；
alter table 表名 modify [column] 列名 数据类型 first；

（5）修改列名称

alter table 表名 change [column] 列名 新列名 数据类型；

约束：CONSTRAINTS

数据完整性（Data Integrity）是指数据的精确性（Accuracy）和可靠性（Reliability）。

它是应防止数据库中存在不符合语义规定的数据和防止因错误信息的输入输出造成无效操作或错误信息而提出的。数据的完整性要从以下四个方面考虑：

1. 实体完整性（Entity Integrity）：例如，同一个表中，不能存在两条完全相同无法区分的记录
2. 域完整性（Domain Integrity）：例如：年龄范围 0-120，性别范围“男/女”
3. 引用完整性（Referential Integrity）：例如：员工所在部门，在部门表中要能找到这个部门
4. 用户自定义完整性（User-defined Integrity）：例如：用户名唯一、密码不能为空等，本部门经理的工资不得高于本部门职工的平均工资的5倍

根据约束的特点，可以分为以下几类：

• 键约束：主键约束、外键约束、唯一键约束
• NOT NULL约束：非空约束
• CHECK约束：检查约束
• DEFAULT约束：缺省约束

（1）查看某个表的约束和索引

SELECT * FROM emp WHERE table_name='表名称';
SHOW INDEX FROM 表名称；
SHOW CREATE TABLE 表名

（2）主键约束PRIMARY KEY

主键Primary key，简称PK，数据库主键的作用保证实体的完整性，可以是一个列或者多列的组合。

• 主键约束相当于唯一约束+非空约束的组合，主键约束列不允许重复，也不允许出现空值，如果是多列组合的主键约束，那么这些列都不允许为空值，并且组合的值不允许重复
• 每个表有且最多只允许一个主键约束
• MySQL的主键名总是PRIMARY，就算自己命名了主键约束名也没用
• 当创建主键约束时，MySQL默认在对应的列上建立主键索引。删除主键时，也会直接删除主键索引

如何建立主键？

在主键列后面直接加主键约束，复合主键不能使用这种方式	单独声明主键约束	声明复合主键，复合主键只能使用这种方式
CREATE TABLE t_stu( sid INT PRIMARY KEY, sname VARCHAR(100), gender CHAR );	CREATE TABLE t_course( cid INT , cname VARCHAR(100), decription VARCHAR(200), PRIMARY KEY(cid) );	CREATE TABLE t_stu_course( sid INT, cid INT, score DOUBLE(4,1), PRIMARY KEY(sid,cid) );
ALTER TABLE t_stu ADD PRIMARY KEY(sid);	ALTER TABLE t_course ADD PRIMARY KEY(cid);	ALTER TABLE t_stu_course ADD PRIMARY KEY(sid,cid);

建表后添加主键约束的方法：

alter table 表名称 add 【constraint 约束名】 primary key (字段名);
alter table 表名称 add 【constraint 约束名】 primary key (字段名 1，字段名 2);

删除主键约束

不需要指定主键名，一个表只有一个主键

alter table 表名称 drop primary key;

（3）唯一约束Unique key，简称UK

• 同一个表可以有多个唯一约束
• 唯一约束可以是某一个列的值唯一，也可以多个列组合值的唯一
• MySQL会给唯一约束的列上默认创建一个唯一索引
• 删除唯一键只能通过删除唯一索引的方式删除，删除时需要指定唯一索引名，唯一索引名就是唯一约束名一样。
• 如果创建唯一约束时未指定名称，如果是单列，就默认和列名相同，如果是组合列，那么默认和()中排在第一个的列名相同。也可以自定义唯一性约束名。

如何建立唯一性约束？

在某个列后面直接加唯一性约束	单独指定表的唯一性约束	组合列唯一性约束
CREATE TABLE t_course( cid INT PRIMARY KEY, cname VARCHAR(100) UNIQUE, description VARCHAR(200) );	CREATE TABLE t_stu( sid INT PRIMARY KEY, sname VARCHAR(100), card_id CHAR(18), CONSTRAINT uk_card_id UNIQUE KEY(card_id) ); #其中CONSTRAINT uk_cname和KEY可以省略	CREATE TABLE t_stu_course( id INT PRIMARY KEY, sid INT, cid INT, score DOUBLE(4,1), CONSTRAINT uk_sid_cid UNIQUE KEY(sid,cid) ); #其中 CONSTRAINT uk_sid_cid 和 KEY可以省略

建表后增加唯一性约束

alter table 表名称 add 【constraint 约束名】 unique 【key】 (字段名);
alter table 表名称 add 【constraint 约束名】 unique 【key】 (字段名 1，字段名 2);


#其中 CONSTRAINT uk_cname 和 KEY可以省略
ALTER TABLE t_course ADD CONSTRAINT uk_cname UNIQUE KEY(cname);
#其中 CONSTRAINT uk_cname 和 KEY可以省略
ALTER TABLE t_stu ADD CONSTRAINT uk_card_id UNIQUE KEY(card_id);
#其中 CONSTRAINT uk_cname 和 KEY可以省略
ALTER TABLE t_stu_course ADD CONSTRAINT uk_sid_cid UNIQUE KEY(sid,cid);

如何删除唯一性约束？

ALTER TABLE 表名称 DROP INDEX 唯一性约束名;
#注意：如果忘记名称，可以通过查看表的约束或索引的方式查看

主键和唯一键的区别：

• 主键是非空，唯一键允许空
• 主键一个表只能一个，唯一键可以有多个

（4）外键：Foreign key，简称FK

• 外键约束是保证一个或两个表之间的==参照完整性,外键是构建于一个表的两个字段或是两个表的两个字段之间的参照关系==。
• 在创建外键约束时，如果不给外键约束名称，默认名不是列名，而是自动产生一个外键名（例如student_ibfk_1;），也可以指定外键约束名。
• 当创建外键约束时，系统默认会在所在的列上建立对应的普通索引。但是索引名是列名，不是外键的约束名。
• 删除外键时，关于外键列上的普通索引需要单独删除。

注意事项

• 在从表上建立外键，而且主表要先存在

• 从表的外键列，在主表中引用的只能是键列（主键，唯一键，外键）

• 从表的外键列与主表被参照的列名字可以不相同，但是数据类型必须一样
  一个表可以建立多个外键约束

• 从表的外键值必须"在主表中能找到"或者为空，从而约束了从表的外键列的值的添加和修改

• 当主表的记录被从表参照时，主表中被参考记录的删除和更新也会受到限制

  1. 默认情况下，主表和从表是严格依赖关系 RESTRICT。当主表的记录被从表参照时，主表的记录将不允许删除，如果要删除数据，需要先删除从表中依赖该记录的数据，然后才可以删除主表的数据
  2. 但是有一种是级联“修改、删除”：
  3. ON DELETE SET NULL(级联置空)：当外键设置了 SET NULL,当主表的相关记录删除时，从表对应的字段改为NULL。注意从表外键字段得允许为空才行
  4. ON DELETE CASCADE(级联删除)：当外键设置了 CASCADE（级联），当主表的相关记录删除时，从表对应的行都删除了
  5. 对于外键约束,最好是采用: ON UPDATE CASCADE ON DELETE RESTRICT 的方式

• 如果要删除表，需要**先删除从表，才能删除主表**

如何建立外键约束？

创建外键语句如下

CREATE TABLE t_department(
	did INT PRIMARY KEY,
	dname VARCHAR(100) NOT NULL UNIQUE, 
	description VARCHAR(200) NOT NULL
);

CREATE TABLE t_employee(
	eid INT PRIMARY KEY,
	ename VARCHAR(100) NOT NULL,
	dept_id INT,
	CONSTRAINT fk_emp_dept_did FOREIGN KEY(dept_id) REFERENCES t_department(did) 
	ON UPDATE CASCADE ON DELETE RESTRICT
);
#其中 CONSTRAINT fk_emp_dept_did 可以省略
#ON UPDATE CASCADE ON DELETE RESTRICT 如果省略表示都是 RESTRICT

建表后创建外键语句如下

alter table 表名称 add 【constraint 约束名】 foreign key (从表字段名) references 主表名(主表被参照字段名);
ALTER TABLE t_emp ADD CONSTRAINT fk_emp_dept_did FOREIGN KEY(dept_id) REFERENCES
t_department(did) ON UPDATE CASCADE ON DELETE RESTRICT;
#其中 CONSTRAINT fk_emp_dept_did 可以省略
#ON UPDATE CASCADE ON DELETE RESTRICT 如果省略表示都是 RESTRICT

如何删除外键约束？

ALTER TABLE 表名称 DROP FOREIGN KEY 外键约束名;
ALTER TABLE t_emp DROP FOREIGN KEY fk_emp_dept_did;

如何删除外键列上的索引？

ALTER TABLE 表名称 DROP INDEX 外键列索引名;
ALTER TABLE t_emp DROP INDEX dept_id;

（5）非空约束NOT NULL

NOT NULL非空约束，规定某个字段不能为空

CREATE TABLE emp(
	sid INT PRIMARY KEY,
	sname VARCHAR(100) NOT NULL
);

（6）检查约束

说明：MySQL不支持check约束，但可以使用check约束，而没有任何效果。

例如：age tinyint check(age > 20) 或者sex char(2) check(sex in(‘M’,‘W’))

（7）Default缺省约束

default：默认值，在插入数据时某列如果没有指定其他的值，那么会将默认值添加到新记录。

CREATE TABLE t_stu(
	sid INT PRIMARY KEY, sname VARCHAR(100) NOT NULL,         gender CHAR NOT NULL CHECK(gender IN('男','女'))
);

自增列：AUTO_INCREMENT

CREATE TABLE t_stu(
	sid INT PRIMARY KEY AUTO_INCREMENT,
	sname VARCHAR(100) NOT NULL, 
    gender CHAR NOT NULL DEFAULT '男', 
    birthday DATE, 
    address VARCHAR(200)
);

关于自增长 auto_increment：

• 整数类型的字段才可以设置自增长
• 当需要产生唯一标识符或顺序值时，可设置自增长
• 一个表最多只能有一个自增长列
• 自增长列必须非空
• 自增长列必须是主键列或唯一键列
• InnoDB 表的自动增长列可以手动插入，但是插入的值如果是空或者 0，则实际插入的将是自动增长后的值