MySQL（2）DDL详解

一、DDL

1.1 操作 Database

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

1 、创建数据库

create database 数据库名 [charset 字符集]; (关键字大写效果：CREATE DATABASE 数据库名;)
如果不指定字符集，则按照安装 mysql 服务时选择的默认字符集。

2 、查看有哪些数据库

show databases;
提示：当前用户有权限查看的。

3 、删除数据库

drop database 数据库名;

4 、选择数据库

use 数据库名;

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

select database();

注意：要操作表格和数据之前必须先说明是对哪个数据库进行操作，否则就要对所有对象加上“数据库名.”。

2.2 表结构的操作 TABLE

1 、查看当前数据库的所有表格

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

2 、创建表结构

（1）基础版

CREATE TABLE 表名称(
	字段名 1 数据类型 1,
	字段名 2 数据类型 2,
	字段名 3 数据类型 3
};

CREATE TABLE t_stu(
	sid INT,
	sname VARCHAR(100),
	gender CHAR
); 

（2）详细版

CREATE TABLE 表名称(
字段名 1 数据类型 1 主键 自增长,
字段名 2 数据类型 2 非空 默认值,
字段名 3 数据类型 3
)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;

3 、查看表结构

desc 表名称;
查看表的定义：SHOW CREATE TABLE 表名;

4 、删除表结构

drop table 表名称;

注意：

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

5 、修改表结构

（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】 列名 新列名 数据类型;

6 、约束：CONSTRAINTS

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

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

根据约束的特点，分为几种：

• 键约束：主键约束、外键约束、唯一键约束
• Not NULL 约束：非空约束
• Check 约束：检查约束
• Default 约束：缺省约束

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

SELECT * FROM information_schema.table_constraints WHERE table_name = ‘表名称’;
SHOW INDEX FROM 表名称;
SHOW CREATE TABLE 表名;

（2 ）主键约束 PRIMARY KEY

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

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

如何建立主键？

如何删除主键？
删除主键约束，不需要指定主键名，一个表只有一个主键。
alter table 表名称 drop primary key;

（3 ）唯一键 Unique key ，简称 UK

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

如何建立唯一性约束？

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

主键和唯一键的区别：
（1）主键是非空，唯一键允许空
（2）主键一个表只能一个，唯一键可以有多个

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

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

注意：

• 在从表上建立外键，而且主表要先存在。
• 从表的外键列，在主表中引用的只能是键列（主键，唯一键，外键）。
• 从表的外键列与主表被参照的列名字可以不相同，但是数据类型必须一样。
• 一个表可以建立多个外键约束。
• 从表的外键值必须"在主表中能找到"或者为空，从而约束了从表的外键列的值的添加和修改。
• 当主表的记录被从表参照时，主表中被参考记录的删除和更新也会受到限制。
  • （1）默认情况下，主表和从表是严格依赖关系 RESTRICT。当主表的记录被从表参照时，主表的记录将不允许删除，如果要删除数据，需要先删除从表中依赖该记录的数据，然后才可以删除主表的数据。
  • （2）但是有一种是级联“修改、删除”：
  • ON DELETE SET NULL(级联置空)：当外键设置了 SET NULL,当主表的相关记录删除时，从表对应的字段改为NULL。注意从表外键字段得允许为空才行。
  • ON DELETE CASCADE(级联删除)：当外键设置了 CASCADE（级联），当主表的相关记录删除时，从表对应的行都删除了。
  • 对于外键约束,最好是采用: ON UPDATE CASCADE ON DELETE 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 非空约束，规定某个字段不能为空

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

（6 ）检查约束

注意： MySQL 不支持 check 约束，但可以使用 check 约束，而没有任何效果；
例如：age tinyint check(age >20) 或 sex char(2) check(sex in(‘男’,’女’))

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

（7 ）Default 缺省约束

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

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

7 、索引：INDEX

索引：索引是对数据库表中一列或多列的值进行排序的一种结构。 索引是一个单独的 、 物理的数据库结构，它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。由此可知， 索引是要消耗数据库空间的。而约束是一种逻辑概念。

索引好处：加快了查询速度(select )
索引坏处:降低了增,删,改的速度(update/delete/insert)，增大了表的文件大小(索引文件甚至可能比数据文件还大)MySQL 提供多种索引类型供选择：

• 普通索引：
• 唯一性索引：
• 主键索引：只有一个主键索引
• 全文索引：MySQL5.X 版本只有 MyISAM 存储引擎支持 FULLTEXT，并且只限于 CHAR、VARCHAR 和 TEXT 类型的列上创建。

MySQL 的索引方法：

• HASH
• BTREE
  MySQL 中多数索引都以 BTREE 的形式保存。

索引的使用原则：
（1）不过度索引
（2）索引条件列(where 后面最频繁的条件比较适宜索引)
（3）索引散列值,过于集中的值不要索引，例如:给性别"男","女"加索引,意义不大
CREATE INDEX 索引名 ON 表名称 (column_name,[column_name…]); 最左边的列最关键
alter table 表名称 drop index 索引名;

8 、自增列：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：
（1）整数类型的字段才可以设置自增长。
（2）当需要产生唯一标识符或顺序值时，可设置自增长。
（3）一个表最多只能有一个自增长列
（4）自增长列必须非空
（5）自增长列必须是主键列或唯一键列。
（6）InnoDB 表的自动增长列可以手动插入，但是插入的值如果是空或者 0，则实际插入的将是自动增长后的值。

---

如果有收获！！！ 希望老铁们来个三连，点赞、收藏、转发
创作不易，别忘点个赞，可以让更多的人看到这篇文章，顺便鼓励我写出更好的博客