DDL
四、DDL
DDL主要是库和表的管理。
1 库的基本操作
1.1 增
# 简单语法：创建数据库
CREATE DATABASE 数据库名;
# 完整语法：创建数据库，指定它的默认字符集和排序规则，如果已存在则不创建
CREATE DATABASE [IF NOT EXISTS] 数据库名 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
# 示例：
CREATE DATABASE Books;
1.2 查
SHOW DATABASES; # 查看所有数据库
SELECT DATABASE(); # 查看当前所在的库的名字
USE dbname; # 切换到指定库下
1.3 改
对于数据库名一般不更改，要修改数据库名可以先删除数据库，再创建新的数据库。
更改数据库的字符集和排序规则：
ALTER DATABASE 数据库名 CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
1.4 删
DROP DATABASE [IF EXISTS] 数据库名;
# 示例：
DROP DATABASE IF EXISTS Books;
2 表的基本操作
2.1 增
CREATE TABLE 表名(
列名1 列的类型[长度] [列的约束],
列名2 列的类型[长度] [列的约束],
……
列名n 列的类型[长度] [列的约束]
);
# 示例
CREATE TABLE student (
id INT primary KEY auto_increment,
`name` VARCHAR(255)
);
2.2 查
SHOW TABLES; # 查看当前库下面所有的表名
DESC tablename # 查看某个表结构
2.3 改
ALTER TABLE 表名 add|drop|modify|change column 列名 [列类型 约束];
# 示例：
# 修改列名
ALTER TABLE student CHANGE COLUMN `name` stu_name VARCHAR ( 255 );
# 修改列的类型或约束
ALTER TABLE student MODIFY COLUMN `stu_name` VARCHAR ( 32 );
# 添加新列
ALTER TABLE student ADD COLUMN `salary` DOUBLE;
# 删除列
ALTER TABLE student DROP COLUMN `salary`;
# 修改表名
ALTER TABLE student RENAME TO `new_student`;
2.4 删
DROP TABLE IF EXISTS 表名;
# 示例：
DROP TABLE IF EXISTS `student`;
2.5 复制表
仅复制表结构，不复制数据
# 仅复制表的结构
CREATE TABLE 要复制的表名 LIKE 原表名;
# 示例
CREATE TABLE admin_back LIKE admin;
复制表的结构和数据：
# 复制表的结构和数据：
CREATE TABLE 要复制的表名 SELECT * FROM 原表名;
# 示例
CREATE TABLE admin_back SELECT * FROM admin;
# 本质就是利用子查询，因此可以做到复制部分数据或者结构，比如：
CREATE TABLE admin_back SELECT * FROM admin WHERE nation="中国";
3 数据类型和约束
3.1 数据类型
创建表的语法中，字段的数据类型主要有以下几种。
- 数值型：整型、小数（定点数、浮点数）
- 字符型：较短的文本（char、varchar）、较长的文本（text、longtext、blob）
- 日期型：date、datetime、timestamp、time、year
数值类型：
- 如果不设置无符号还是有符号，默认都是有符号的。如果需要设置无符号，需要添加
unsigned关键字。 - 如果插入的数值超过了整型的范围，MySQL 会报（Out of range for column ...）异常。
- 如果不设置长度，会有默认的长度。
字符类型：
- char是定长的，可能会浪费空间，但是存取简单；varchar是变长的，节省空间，但是存取稍微麻烦一点。
- varchar最多能存储65535个字节的数据，一般我们最多定义varchar(255)，超过255会被转成text类型
时间类型：
| 类型名称 | 说明 |
|---|---|
| year | YYYY 1901~2155 |
| time | HH:MM:SS -838:59:59~838:59:59 |
| date | YYYY-MM-DD 1000-01-01~9999-12-3 |
| datetime | YYYY-MM-DD HH:MM:SS 1000-01-01 00:00:00~ 9999-12-31 23:59:59 |
| timestamp | YYYY-MM-DD HH:MM:SS 19700101 00:00:01 UTC~2038-01-19 03:14:07UTC |
3.2 约束
为了保证数据的一致性和完整性，SQL 规范以约束的方式对表数据进行额外的条件限制。约束是表级的强制规定。可以在创建表的时候添加约束，或者在表创建之后数据添加之前添加约束。
有如下的六大约束：
-
NOT NULL，非空约束，规定某个字段不能为空。
-
UNIQUE，唯一约束，规定某个字段在整个表中是唯一的。
-
PRIMARY KEY，主键，非空且唯一。
-
CHECK，检查约束。
-
DEFAULT，默认值。
-
FOREIGN KEY，外键。
根据约束的作用范围，约束可以分为：
- 列级约束：只能作用在一个列上，并且是根据列的定义后面。六大约束语法上支持，但是外键约束没有效果。
- 表级约束：可以作用在多个列上，不和列在一起，而是单独定义。除了非空、默认约束，其他约束都支持。
CREATE TABLE 表名(
列名 字段类型 列级约束,
列名 字段类型 列级约束,
表级约束(列名1,列名2)
);
# 示例：创建表的时候添加列级约束
CREATE TABLE stu_info (
id INT PRIMARY KEY,# 主键
stu_name VARCHAR ( 255 ) NOT NULL,# 非空
gender CHAR ( 1 ) CHECK (
gender IN ( '男', '女' )),# 检查约束
seat INT UNIQUE,# 唯一约束
age INT DEFAULT 18 # 默认约束
);
# 创建表的时候添加表级约束
CREATE TABLE major (
id INT PRIMARY KEY,
`name` VARCHAR ( 255 )
);
在修改表时也可以添加约束，语法：
# 添加列级约束
ALTER TABLE 表名 MODIFY COLUMN 列名 字段类型 新约束;
# 添加表级约束
ALTER TABLE 表名 ADD CONSTRAINT 约束名 约束类型 (字段名) [外键的引用];
# 示例：
# 修改表时添加约束
ALTER TABLE stu_info MODIFY COLUMN id INT PRIMARY KEY;
ALTER TABLE stu_info MODIFY COLUMN age INT DEFAULT 18;
ALTER TABLE stu_info ADD CONSTRAINT fk_major_id FOREIGN KEY (major_id) REFERENCES major(id);
# 修改表时删除非空约束
ALTER TABLE stu_info MODIFY COLUMN stu_name VARCHAR(255) ;
# 修改表时删除默认约束
ALTER TABLE stu_info MODIFY COLUMN age INT;
# 修改表时删除主键
ALTER TABLE stu_info DROP PRIMARY KEY;
# 修改表时删除主键
# 查询唯一键 show index from stu_info
ALTER TABLE stu_info DROP INDEX seat;
# 修改表时删除外键
ALTER TABLE stu_info DROP FOREIGN KEY fk_marjor_id;
3.3 主键和唯一的区别
| 保证唯一性 | 是否允许为空 | 一个表中可以有多少个 | 是否允许组合 | |
|---|---|---|---|---|
| 主键 | √ | × | 最多有1个 | √，但是不推荐 |
| 唯一 | √ | √ | 可以有多个 | √，但是不推荐 |
3.4 外键的特点
-
外键的设置：
- 一对一关系，外键字段建在哪里都可以，但是推荐你建在查询频率较高的一方
- 一对多关系，外键字段建在多的那方
- 多对多关系，需要建立第三张表
-
要求从表的外键列的类型要和主表的关联列的类型要求一致或兼容，但是名称不要求一致。
-
主表的关联列必须是一个 key（一般是主键或唯一键）。
-
在删除时，先删除从表，再删除主表（可以设置级联删除）
4 标识列
标识列又称为自增长列，可以不用手动插入值，系统提供默认的序列值。特点如下：
-
标识列必须不一定和主键搭配，但是要求是一个 key。
-
一个表中至多有一个标识列。
-
标识列的类型只能是数值型。
-
标识列可以通过
set auto_increment_increment = 3;设置步长，也可以通过手动插入值的方式设置标识列的起始值INSERT INTO student(id,name) VALUES (5,'xxx')。
示例，创建表的时候设置标识列：
CREATE TABLE student (
id INT PRIMARY KEY auto_increment, # 设置id自增
`name` VARCHAR ( 255 )
);
# 新增数据
INSERT INTO student(`name`) VALUES ('xxx'); # 在插入的时候不需要指定主键id
也可以修改表时设置标识列
CREATE TABLE student (
id INT PRIMARY KEY , # 创建时不设置自增
`name` VARCHAR ( 255 )
);
# 修改表的时候设置标识列
ALTER TABLE student MODIFY COLUMN id PRIMARY KEY auto_increment; # 设置id自增
# 新增数据
INSERT INTO student(`name`) VALUES ('xxx'); # # 在插入的时候不需要指定主键id
删除标识列，比如删除自增：
# 创建表的时候设置标识列
CREATE TABLE student (
id INT PRIMARY KEY auto_increment,
`name` VARCHAR ( 255 )
);
# 修改表时删除标识列
ALTER TABLE student MODIFY COLUMN id PRIMARY KEY;
5 一对多表关系的创建
示例：
# 学生表和班级表是一对多关系，外键在学生表建立
CREATE TABLE class(
id int PRIMARY KEY auto_increment,
NAME VARCHAR(255)
);
CREATE TABLE student (
id INT PRIMARY KEY auto_increment,
name VARCHAR ( 255 ) ,
class_id INT,
FOREIGN KEY(class_id) REFERENCES class(id)
);
注意：
- 先创建从表，再创建主表
- 录入数据时，先录入从表，再录入主表
- 删除数据时，先删除从表，再删除主表
这种操作方式比较费力，可以设置级联更新和级联删除。如下：
# 学生表和班级表是一对多关系，外键在学生表建立
CREATE TABLE class(
id int PRIMARY KEY auto_increment,
NAME VARCHAR(255)
);
CREATE TABLE student (
id INT PRIMARY KEY auto_increment,
name VARCHAR ( 255 ) ,
class_id INT,
# cascade 级联操作，设置级联更新和级联删除
FOREIGN KEY(class_id) REFERENCES class(id) ON UPDATE CASCADE ON DELETE CASCADE
);
6 多对多表关系创建
示例：
# 学生表和老师表是多对多关系，在第三张表创建关系
CREATE TABLE student(
id int PRIMARY KEY auto_increment,
NAME VARCHAR(255)
);
CREATE TABLE teacher(
id int PRIMARY KEY auto_increment,
NAME VARCHAR(255)
);
CREATE TABLE student2teacher(
id int PRIMARY KEY auto_increment,
teacher_id INT,
student_id INT,
FOREIGN KEY(teacher_id) REFERENCES teacher(id)
ON UPDATE CASCADE
ON DELETE CASCADE,
FOREIGN KEY(student_id) REFERENCES student(id)
ON UPDATE CASCADE
ON DELETE CASCADE
);
7 一对一表关系创建
示例：
# 学生表和学生信息是一对一关系，外键在哪张表都可以创建，可以创建在查询频率高的表上
CREATE TABLE student(
id int PRIMARY KEY auto_increment,
NAME VARCHAR(255),
details_id INT,
FOREIGN KEY(details_id) REFERENCES student_details(id)
ON UPDATE CASCADE
ON DELETE CASCADE
);
CREATE TABLE student_details(
id int PRIMARY KEY auto_increment,
telephone INT,
address VARCHAR(255)
);
本文来自博客园，作者：yyyz，转载请注明原文链接：https://www.cnblogs.com/yyyzyyyz/p/15848646.html
