一、SQL基本概念

1、SQL（Structured Query Language）

SQL是一种领域专用编程语言，用于管理关系数据库管理系统（RDBMS），或在关系流数据管理系统（RDSMS）中进行流处理。SQL的范围包括数据的增删改查(CRUD)、数据库模式创建和修改，以及数据访问控制。

2、DB（database）

数据库是一个以某种有组织方式存储的数据集合，简而言之可视为电子化的文件柜——存储电子文件的处所，用户可以对文件中的资料运行新增、截取、更新、删除等操作，一个数据库由多个表(table)构成。它本质上就是一个保存有组织数据的容器。

3、DBMS（database management system）

数据库管理系统是一种针对对象数据库，为管理数据库而设计的大型电脑软件管理系统。每种DBMS都会针对SQL进行一定程度的扩展，本文讲的是其中的MySQL的语法知识，使用的是MySQL5.7。

二、数据存储特点

1、表

在关系型数据库中，数据库表是一系列二维数组的集合，可以用来存储和表示数据对象之间的关系。每个表的命名都是唯一的，对于MySQL来说指的是在同一个库中唯一。存储在同一个表中的数据必须是同一种类型的数据，如现需要存储用户信息和用户订单，那么必须使用两个表来存储相关的数据。

2、行与列

数据库表由列组成，列表示的是表中的一个字段，可以把所有列组成的集合看成是表格的表头，每一列都有对应的数据类型，它限制了该列存储的数据种类。相对的，表中的一条数据记录称为行。

三、语法规范与常用命令

1、语法规范

1）不区分大小写,但建议关键字大写，表名与列名小写
2）SQL语句也包括分号(语句终结符)，多条语句必须加上分号，尽管并不是每个平台都必需在单语句后加分号，但它是作为SQL语法的标准部分定义的
3）注释：
单行注释：#注释文字
单行注释：-- 注释文字
多行注释：/* 注释文字 */
4）对于有歧义的表名/字段名应该使用``进行转义

2、常用命令

#1 查看当前数据库服务器中所有库
SHOW DATABASES;
#2 打开指定的库，下面的所有操作都是对该库进行
USE 库名；
#3 显示库中的所有表
SHOW TABLES;
#4 显示指定库中的所有表
SHOW TABLES FROM 库名;
#5 查看指定表的结构
DESC 表名;

四、SQL语言分类

1）DQL（Data Query Language）：数据查询语言 如：select

2）DML(Data Manipulate Language):数据操作语言 如：insert 、update、delete

3）DDL（Data Define Languge）：数据定义语言 如：create、drop、alter

4）TCL（Transaction Control Language）：事务控制语言 如：commit、rollback

5）DCL（Data Control Language）： 数据库控制语言。用来设置或更改数据库用户或角色权限的语句，包括（grant,deny,revoke等）语句。

五、DQL语言

使用到的数据库的表结构：

image

image

image

1、查询/检索【SELECT】

在MySQL中使用SELECT语句可以用来检索一个或多个数据列，标准的SELECT不会对数据库有持久影响。查询允许用户描述所需的数据，将计划、优化以及执行用以产生它选取的结果的物理操作交给数据库管理系统（DBMS）负责。查询包含一系列含有最终结果的字段, 紧跟SELECT关键词。星号（"*"）也可以用来指定查询应当返回查询表所有字段。

1.1 基本查询

语法：SELECT 查询内容【FROM 表名】;
①通过select查询完的结果 是一个虚拟的表格

②要查询的东西可以是常量值、表达式、字段、函数

③可以同时检索多个列，每个列之间用逗号分隔

④可以使用*检索所有字段，但会降低检索性能

⑤使用DISTINCT关键字可以实现去重，该关键字置于列名前

⑥MySQL中可以限制检索结果的条目，limit x,y表示从检索出第x行开始的y行数据

#1 查询employees表中的name列信息
SELECT name FROM employees;
#2 查询employees表中的所有字段信息
SELECT * FROM employees;
#3 查询employees表中的name、phone字段信息
SELECT name,phone FROM employees;
#4 DISTINCT关键字实现去重
SELECT DISTINCT id FROM employees;
#5 limit限制检索结果的条目
SELECT id FROM employees LIMIT 3,5;

1.2 排序查询

语法：SELECT 查询内容 FROM 表 ORDER BY 排序的字段|表达式|函数|别名 【ASC|DESC】

①ORDER BY子句需要位于SELECT语句的最后

②可以使用非检索列进行排序

③可以多个列进行排序，列名之间用逗号隔开

④ASC表示升序排列(默认)，DESC表示降序排列，该标记直接应用到前面的列名

#1 检索employees表中的id和phone字段 并按照salary进行排序
SELECT id,phone FROM employees ORDER BY salary;
#2 检索employees表中的id和phone字段 并按照salary和id进行排序
SELECT id,phone FROM employees ORDER BY salary,id;
#3 检索employees表中的id和phone字段 并按照salary降序排序
SELECT id,phone FROM employees ORDER BY salary DESC;

1.3 条件查询

条件查询指的是在检索所需数据时加入过滤条件，在MySQL中使用的是WHERE子句，该子句位于表名之后。

语法：SELECT 查询内容 FROM 表 WHERE 过滤条件 ;

①过滤条件可以是带有条件运算符(< > = <= >= <>)的一个表达式，如：id<>10，这里的<>为不等于的含义

②如果要匹配范围值，可以使用 BETWEEN x AND y，这代表检索出某字段位于x与y之间的数据；与之类似可以使用IN操作符来指定条件范围

③在MySQL中检测NULL不能直接使用=NULL判断，应该使用IS NULL子句，检测非NULL可以使用IS NOT NULL

④WHERE子句允许多个过滤条件，条件之间使用AND或者OR隔开，需要注意的是AND和OR同时出现时会优先处理AND操作符，可以使用 ( ) 对条件操作顺序进行指定

⑤如果要对数据进行模糊查询可以使用LIKE操作符搭配通配符（%表示匹配出现任意次的任意字符，_表示匹配单个字符，[]表示匹配一个字符集，类似正则），非文本数据类型字段不能模糊查询

#1 查询工资大于1000的员工id
SELECT id FROM employees WHERE salary>1000;
#2 查询工资在1000到3000的员工id
SELECT id FROM employees WHERE salary BETWEEN 1000 AND 3000;
#3 查询phone不为null的员工的id
SELECT id FROM employees WHERE phone IS NOT NULL;
#4 查询phone为null且salary大于1000的员工的id
SELECT id FROM employees WHERE phone IS NULL AND salary>1000;
#5 查询name中结尾包含i字符的员工的id
SELECT id FROM employees WHERE name LIKE '%i';

1.4 别名查询

语法：SELECT 查询内容 【AS】 别名 FROM 表 ;

①可以使用别名将计算字段查询出来，这里的计算字段可以是字符串的拼接，也可以是算术表达式的结果

②在MySQL中拼接字段使用的是concat函数，传入需要拼接的字段作为参数即可

③别名查询一般用于替换查询字段/表达式，其中的AS可以省略

#1 查询employees表中的员工id并返回为e_id
SELECT id AS e_id FROM employees;
#2 查询employees表中的员工薪资*12
SELECT salary*12 AS y_salary FROM employees;
#3 同时查询出员工id和姓名作为一个字段 中间用空格隔开
SELECT CONCAT(id,' ',`name`) id_name FROM employees;

1.5 聚集查询

语法：SELECT 聚集函数 FROM 表 ;

①AVG函数返回某列的平均值，COUNT函数返回某列的行数，MAX函数返回某列的最大值，MIN函数返回某列的最大值，SUM函数返回某列的值总和

②COUNT(*)可以统计对应列的数据行数，不忽略NULL

③对非数值数据使用MAX函数时会返回排序后的最后一行，MIN会返回第一行

④在使用聚集函数时可以结合别名查询，而别名不应该是表中的实际列名

#1 返回员工salary的平均值与最大值
SELECT AVG(salary) salary_avg,MAX(salary) salary_max
FROM employees;
#2 返回表中员工个数
SELECT COUNT(*) sum_employee FROM employees;

其他函数：

1、字符函数

concat（x,y,...） 拼接数据x,y,...

substr（str,pos,len） 在str字符串中从pos位置开始截取长度为len的子串

upper（str） 将str内容转换成大写

lower（str） 将str内容转换成小写

trim（str） 去除str内容前后的空格

ltrim（str） 去除str内容左边空格

rtrim（str） 去除str内容右边空格

replace（str,from_str,to_str） 将str内容中的from_str内容替换为to_str内容

lpad（str,len,padstr） 对str使用padstr进行左填充，填充至len长度

rpad（str,len,padstr） 对str使用padstr进行右填充，填充至len长度

instr（str,substr） 返回str的子串substr第一次出现的索引

length（str） 获取str的长度

2、数学函数

round（x，y） 对x的小数点后第y位开始进行四舍五入，y省略默认保留整数部分

floor（x） 对x向下取整

ceil（x） 对x向上取整

mod（a,b） 对a取模b，计算过程：a-a/b*b ，因此如a为负结果负

truncate（x，y） 对x从小数点后y位开始截断

3、日期函数

now（） 当前系统日期+时间

curdate（） 当前系统日期

curtime（） 当前系统时间

str_to_date（） 将字符转换成日期

date_format（） 将日期转换成字符

1.6 分组查询

语法：SELECT 查询内容/聚集函数 FROM 表 GROUP BY 分组字段;

①可以按单个字段分组，也可以按多个字段分组，字段之间用逗号隔开，需要注意的是在SELECT中出现的字段必须同时出现在GROUP BY之后

②分组查询也可以进行筛选，如果是分组之前的筛选，操作的是原始表，需要在GROUP BY前使用WHERE筛选；如果是分组之后的筛选，操作的是分组后的结果集，需要在GROUP BY后使用HAVING筛选

③与聚集函数一同查询的字段最好是分组后的字段

④支持对分组后的数据进行排序，ORDER BY须放在语句末尾

#1 查询每个部门的人数并按照部门id排序
SELECT COUNT(*),department_id FROM employees 
GROUP BY department_id ORDER BY department_id;
#2 查询每个工种的平均工资并排序
SELECT AVG(salary),job_id FROM employees 
GROUP BY job_id ORDER BY salary;
#3 查询哪个部门的人数大于2
SELECT COUNT(*) sum_person,department_id FROM employees 
GROUP BY department_id HAVING sum_person>2;

1.7 连接查询

在关系型数据库中，用来在一条SELECT语句中关联表的机制称为联结。简单地说，当查询的数据来自不同的表时，就需要使用连接查询。在MySQL中连接查询的标准有sql92，sql99，在ANSI SQL中推荐的是使用sql99语法。连接查询种类有内连接、外连接以及交叉连接。

1）sql92标准：等值连接/非等值连接/自连接

语法：SELECT 查询内容 FROM 表1,表2... WHERE 连接条件;

①连接条件不可缺少，否则会出现笛卡尔积(结果集行数为表1行数 * 表2行数 *...)

②可以给表起别名，起别名之后查询时不能使用原始名

③如果在连接查询后需要进行筛选，可在连接条件后用AND连接筛选条件

④自连接表示的是同一张表的连接，这时必须给表起别名

#1 查询员工姓名对应的部门名
SELECT `name`,department_name
FROM employees,departments
WHERE employees.`department_id`=departments.`department_id`;
#2 查询电话不为空的员工名和部门名
SELECT `name`,department_name,phone
FROM employees e,departments d
WHERE e.`department_id`=d.`department_id`
AND e.`phone` IS NOT NULL;
#3 查询员工名与上级名
SELECT e.`id` id_employee,e.`name` name_employee,
m.`id` id_manager,m.`name` name_manager
FROM employees e,employees m
WHERE e.`manager_id`=m.`id`;

2）sql99标准

语法：SELECT 查询内容 FROM 表1 连接类型 JOIN 表2 IN 连接条件 ;

连接类型：内连接(INNER)，左外连接(LEFT[OUTER])， 右外连接(RIGHT[OUTER]) ，全外连接(FULL[OUTER]) ，交叉连接(CROSS)

①sql99标准中连接查询的好处是在语句中连接条件和筛选条件实现了分离

②内连接与sql92标准除了语法差异外其他并无不同

③对于外连接，假设查找的是a表中有，但是b表中没有的数据，那么如果b表中有与a表匹配的数据则返回，没有则返回null。因此，对于该假设外连接查询结果=内连接结果+a表有但b表无的数据

④对于左外连接，LEFT JOIN 左边是主表；对于右外连接，RIGHT JOIN右边是主表

⑤全连接查询结果=内连接结果+a表中有但b表中没有的+b表中有但a表中没有的

#1 查询员工名与对应部门名
SELECT `name`,department_name
FROM employees e
INNER JOIN departments d
ON e.`department_id`=d.`department_id`;
#2 查询电话不为空的员工名和部门名
SELECT `name`,department_name
FROM employees e
INNER JOIN departments d
ON e.`department_id`=d.`department_id`
WHERE e.phone IS NOT NULL;
#3 查询员工名与上级名
SELECT e.`id`,e.`name`,m.`id`,m.`name` 
FROM employees e
INNER JOIN employees m
ON e.`manager_id`=m.`id`;
#4 查询哪个部门没有员工
SELECT department_name,employees.`id`
FROM departments
LEFT OUTER JOIN employees
ON employees.`department_id`=departments.`department_id`
WHERE employees.`id` IS NULL
GROUP BY departments.`department_name`;

1.8 子查询

在一条查询语句中又嵌套了另一条完整的select语句，其中被嵌套的select语句，称为子查询或内查询，在外面的查询语句，称为主查询或外查询。按结果集来分类有四种子查询，分别是标量子查询(结果集一行一列)、列子查询(结果集一列多行)、行子查询(结果集一行多列)、表子查询(结果集一般为多行多列)。

语法：
SELECT（标量子查询） FROM（表子查询）
WHERE/HAVING （标量子查询/列子查询/行子查询） EXISTS（表子查询）;

①子查询均放在小括号中

②子查询可以放在from后面、select后面、where后面、having后面，但一般放在条件的右侧

②子查询可以放在from后面、select后面、where后面、having后面，但一般放在条件的右侧

③子查询优先于主查询执行，主查询使用了子查询的执行结果

④单行结果的子查询一般搭配单行操作符使用：> < = <> >= <= ，多行结果的子查询一般搭配多行操作符使用：ANY、ALL、IN、NOT IN

#1 查询工资比lily高的用户信息
SELECT * FROM employees
WHERE salary>(
    SELECT salary FROM employees 
    WHERE last_name = "lily"
);
#2 查询公司工资最少的员工的用户信息
SELECT * FROM employees 
WHERE salary=(
    SELECT MIN(salary)
    FROM employees
);
#3 返回部门位置id是1或者2的对应部门中的所有员工的姓名
SELECT name FROM employees
WHERE department_id IN (
    SELECT DISTINCT department_id
    FROM departments
    WHERE location_id
    IN (1,2)
);
#4 查询员工编号最小且工资最高的员工信息
SELECT * FROM employees
WHERE (id,salary)=(
    SELECT MIN(id),MAX(salary)
    FROM employees
);
#5 查询每个部门的员工个数 
SELECT departments.*,(
    SELECT COUNT(*)
    FROM employees e
    WHERE e.id=d.id
) sum_person
FROM departments d;
#6 查询有员工归属的部门名
SELECT department_name
FROM departments d
WHERE EXISTS(
    SELECT * FROM employees e 
    WHERE e.`department_id`=d.`department_id`
);

1.9 分页查询

语法：SELECT 查询内容 FROM 表 LIMIT 起始的索引, 条目数;

①起始条目索引从0开始

②limit子句放在查询语句的最后

③公式：SELECT * FROM 表 LIMIT （page-1）*sizePerPage,sizePerPage;
其中sizePerPage为每页显示条目数，page为要显示的页数

#1 查询前五条员工信息
SELECT * FROM employees
LIMIT 0,5;
#2 查询第11~25条员工信息
SELECT * FROM employees
LIMIT 10,15; 
#3 查询电话不为空，并且工资排行前十名的员工信息
SELECT * FROM employees
WHERE phone IS NOT NULL
ORDER BY salary DESC
LIMIT 0,10;

1.10 联合查询

语法：查询语句1 UNION [ALL] 查询语句2 ...

①多条查询语句的查询的列数必须是一致的

②查询后的结果默认是第一条查询语句的表头

③UNION代表去重，UNION ALL代表不去重

#1 查询部门编号>100且姓名包含a的员工信息
SELECT * FROM employees WHERE department_id>100
UNION
SELECT * FROM employees WHERE `name` LIKE '%a%';

2、插入（INSERT）

语法一：INSERT INTO 表名(字段名，...) VALUES(值1，...);

语法二：INSERT INTO 表名 SET 字段1=值1,字段2=值2,... ;

①表字段类型和传入的值类型必须一致或兼容，而且一一对应，并且个数也必须一致

②表中允许为空的字段，可用null填充，表中不允许为空的字段，必须插入值

③表名后的字段可以省略，默认插入所有字段，并且顺序应该和表中的存储顺序一致

#1 只插入必填字段
INSERT INTO stuinfo(id,NAME)
VALUES(14,'张三');
#2 按需插入字段
INSERT INTO stuinfo(NAME,id,phone,sex)
VALUES('李四',15,'15812345678','男');
#3 省略字段名，默认全部插入
INSERT INTO stuinfo
VALUES(16,'王五','男','1999-12-01','15212345678',NULL)
#4 使用语法二插入
INSERT INTO stuinfo
SET id=17,NAME='赵六',sex='男',phone='13912345678';

3、修改（UPDATE）

3.1 单表修改

语法：UPDATE 表名 SET 字段1=值1,字段2=值2,... 【筛选条件】；

3.2 多表修改

语法(sql92)：UPDATE 表1,表2 SET 字段=值,... WHERE 连接条件 AND 筛选条件;

语法(sql99)：UPDATE 表1 连接类型 JOIN 表2 ON 连接条件 SET 字段=值,... WHERE 筛选条件;

#1 修改stuinfo中nan开头的姓名对应的电话为12300(单表修改)
UPDATE stuinfo
SET phone='12300'
WHERE `name` LIKE 'nan%';
#2 修改stuinfo表中id为1的姓名为jack，phone为188000(单表修改)
UPDATE stuinfo
SET `name`='jack',phone='188000'
WHERE id=1;
#3 修改所属部门为“test”的员工的备注信息为“test”
UPDATE employees e
INNER JOIN departments d
ON e.`department_id`=d.`department_id`
SET e.remark='test'
WHERE d.`department_name`='test';

4、删除（DELETE、TRUNCATE）

4.1 DELETE

单表删除：DELETE FROM 表 WHERE 筛选条件；

多表删除（sql92）：DELETE 表名 FROM 表1,表2 WHERE 连接条件 AND 筛选条件；

多表删除（sql99）：DELETE 表名 FROM 表1 连接类型 JOIN 表2 ON 连接条件 WHERE 筛选条件;

4.2 TRUNCATE

语法：TRUNCATE TABLE 表名；

①DELETE可以按需求删除数据，TRUNCATE删除的是整个表的数据

②TRUNCATE不能加WHERE条件，而DELETE可以加where条件

③同等情况下，TRUNCATE效率较高

④TRUNCATE删除带自增长的列的表后，如果再插入数据，自增长列数据从1开始，DELETE删除带自增长列的表后，如果再插入数据，自增长列数据从上一次的断点处开始

⑤TRUNCATE删除不能回滚，DELETE删除可以回滚

#1 删除手机号以00结尾的用户信息
DELETE FROM stuinfo WHERE phone LIKE "%00";
#2 删除所属部门为“test”的员工的信息
DELETE e
FROM departments d
INNER JOIN employees e
ON e.`department_id`=d.`department_id`
WHERE d.`department_name`='test';

六、DML语言

1、数据类型

1.1 数值型

1）整型

分类: TINYINT(1字节) SMALLINT(2字节) MEDIUMINT(3字节) INT/INTEGER(4字节) BIGINT(8字节)

①默认为有符号，如需使用无符号，应标明UNSIGNED

②如果插入值超过范围，会抛出error

③在使用这些类型时可能会出现如：INT(3) 这种形式，实际上括号里面的数只表示显示的最大宽度，而非所占字节数

④搭配ZEROFILL可在长度不够时在左侧填0，在使用ZEROFILL后，对应字段将变成UNSIGNED。如果不设置长度，会给出默认长度

2）小数

2.1）浮点型
FLOAT(M,D) DOUBLE(M,D)

2.2）定点型
DEC(M,D) DECIMAL(M,D)

①M:整数部位+小数部位长度，D：小数部位长度， 如果M超出范围会报错，D超出会四舍五入

②M D可以省略 如果是DECIMAL默认为(10,0)

③FLOAT存储需求为4字节，DOUBLE存储需求为8字节，DEC、DECIMAL存储需求为M+2字节

④定点型精度较浮点型高

1.2 字符型

1）短文本：

CHAR(M): 储存固定长度字符，空间占用大，效率高，M表示长度，可省略，默认为1

VARCHAR(M):存储可变长度字符，空间占用小，效率低，M表示长度，不可省略

2）长文本：

TEXT：保存非二进制字符串

3）其他：

ENUM：用于保存枚举类型 SET：用于保存集合类型

①不管是哪种形式的字符型，都要包括在括号里(荐小括号)

②VARCHAR和TEXT类型是变长类型，其存储需求取决于列值的实际长度（最终长度为实际长度+1）

1.3 二进制类型

BIT(M)：位字段类型，大约 (M+7)/8 字节

BINARY(M)：固定长度二进制字符串，M 字节

VARBINARY(M)：可变长度二进制字符串，M+1 字节

TINYBLOB(M)：L+1 字节，L为实际大小

BLOB(M)：L+2 字节，L为实际大小

MEDIUMBLOB(M)：L+3 字节，L为实际大小

LONGBLOB(M)：L+4 字节，L为实际大小

1.4 日期时间类型

TIME：时间，表示形式为：HH:MM:SS

DATE：日期，表示形式为：YYYY-MM-DD

DATETIME： 日期+时间，表示形式为：YYYY-MM-DD HH:MM:SS

TIMESTAMP： 日期+时间，也称为时间戳，表示形式为：YYYY-MM-DD HH:MM:SS

YEAR： 年份，表示形式为：YYYY

①DATETIME为8字节，表示范围为1000-9999，不受时区影响

②TIMESTAMP为4字节，表示范围为1970-2038，受时区影响

③DATETIME 在存储日期数据时，按实际输入的格式存储，与时区无关；而 TIMESTAMP 值的存储是以 UTC（世界标准时间）格式保存的，存储时对当前时区进行转换，检索时再转换回当前时区。即查询时，根据当前时区的不同，显示的时间值是不同的。

2、表的管理

2.1 新增表

语法：CREATE TABLE 表名( 列名 列类型 [约束], 列名 列类型 [约束], ... );

可以在列类型后加上DEFAULT关键字指定字段的默认值

# 为方便阅读这里只显示基本语法(不含约束)
# 创建书籍表
CREATE TABLE `books`(
    id INT,
    bname VARCHAR(20),
    price DOUBLE,
    authorId INT,
    publicDate DATETIME
);
# 创建authors表
CREATE TABLE `authors`(
    id INT,
    au_name VARCHAR(20),
    nation VARCHAR(20)
);

2.2 修改表

语法： ALTER TABLE 表名 CHANGE|MODIFY|ADD|DROP COLUMN 列名 [列类型 约束]

①修改列名，使用的是CHANGE

②修改列的类型或约束，使用的是MODIFY

③添加/删除列，使用的是ADD/DROP

④修改表名，使用的是RENAME

#1 修改books表的publicDate字段为pubDate，类型为DATETIME
ALTER TABLE books CHANGE COLUMN publicDate pubDate DATETIME;
#2 修改books表中pubDate列的类型为TIMESTAMP
ALTER TABLE books MODIFY COLUMN pubDate TIMESTAMP;
#3 向authors表添加annual列，类型为DOUBLE
ALTER TABLE `authors` ADD COLUMN annual DOUBLE;
#4 删除authors表中的annual字段
ALTER TABLE `authors` DROP COLUMN annual;
#5 将authors表更名为books_authors
ALTER TABLE `authors` RENAME TO books_authors;

2.3 删除表

语法：DROP TABLE [IF EXISTS] 表名;

#1 加上IF EXISTS，如果表存在才删除(避免报错)
DROP TABLE IF EXISTS books;

2.4 复制表

1）仅复制表结构

语法：CREATE TABLE 复制到的表 LIKE 被复制的表;

#1 将authors表的结构复制到copy_authors
CREATE TABLE `copy_authors` LIKE `authors`;

2）复制表结构+全部数据

语法：CREATE TABLE 复制到的表 SELECT * FROM 被复制的表;

#1 将authors表的结构与全部数据复制到copy_authors2
CREATE TABLE `copy_authors2` SELECT * FROM `authors`;

3）复制结构+部分数据

语法：CREATE TABLE 复制到的表 SELECT * FROM 被复制的表 WHERE 筛选条件;

#1 将authors表的结构与id为1的数据复制到copy_authors3
CREATE TABLE `copy_authors3` SELECT * FROM `authors` WHERE id=1;

4）复制部分结构 不复制数据

语法：CREATE TABLE 复制到的表 SELECT 列名 FROM 被复制的表 WHERE FALSE;

#1 将authors表的id、nation列结构复制到copy_authors4
CREATE TABLE `copy_authors4` SELECT id,nation FROM `authors` WHERE FALSE;

3、约束

3.1 概述

约束用于限制表中的数据，保证数据的准确性和可靠性。

3.2 分类

MySQL中的约束共有五种

①NOT NULL 非空 -- 保证字段不为空

②DEFAULTS 默认 -- 保证字段有默认值

③PRIMARY KEY 主键 -- 保证字段的唯一性，并且非空

④UNIQUE 唯一 -- 保证字段的唯一性，可以为空

⑤FOREIGN KEY 外键 -- 保证该字段的值来自主表的关联列的值，外键在从表中添加

按照他们的定义位置又可以分为列级约束和表级约束，列级约束支持除外键之外的约束定义，表级约束支持除了非空与默认，其他都支持。

3.3 添加约束

1） 创建表时添加列级约束

语法：CREATE TABLE 表名( 列名1 列类型1 约束1, 列名2 列类型2 约束2, ... );

CREATE TABLE stuinfo2(
    id INT PRIMARY KEY,#主键
    stuName VARCHAR(20) NOT NULL, #非空
    seat INT UNIQUE, #唯一
    age INT DEFAULT 18, #默认
    gender CHAR(1),
    majorId INT
);

2） 创建表时添加表级约束

语法：[constraint 约束名] 约束类型(字段名)

外键定义语法为：CONSTRAINT 约束名 FOREIGN KEY(从表字段名) REFERENCES 主表(字段名)

CREATE TABLE stuinfo3(
    id INT,
    stuName VARCHAR(20),
    gender CHAR(1),
    seat INT,
    age INT,
    majorId INT,
    CONSTRAINT pk PRIMARY KEY(id),#主键
    CONSTRAINT uq UNIQUE(seat),#唯一
    CONSTRAINT fk FOREIGN KEY(majorId) REFERENCES major(id)#外键 
);

3） 修改表时添加列级约束

语法：ALTER TABLE 表名 MODIFY COLUMN 字段 字段类型 约束;

# 假设已存在空表stuinfo4（结构与stuinfo3一致但没有约束）
#1 添加非空约束
ALTER TABLE stuinfo5 MODIFY COLUMN stuName VARCHAR(20) NOT NULL;
#2 添加默认约束
ALTER TABLE stuinfo5 MODIFY COLUMN age INT DEFAULT 18;
#3 添加主键约束
ALTER TABLE stuinfo5 MODIFY COLUMN id INT PRIMARY KEY;
#4 添加唯一键
ALTER TABLE stuinfo5 MODIFY COLUMN seat INT UNIQUE;

4） 修改表时添加表级约束

语法：ALTER TABLE 表名 ADD 【constraint 约束名】 约束类型(字段名) 【外键引用...】

#1 添加主键约束
ALTER TABLE stuinfo5 ADD PRIMARY KEY(id);
#2 添加唯一键
ALTER TABLE stuinfo5 ADD UNIQUE(seat);
#3 添加外键
ALTER TABLE stuinfo5 ADD CONSTRAINT fk_stuinfo5_major FOREIGN KEY(majorId) REFERENCES major(id);
ALTER TABLE stuinfo5 ADD FOREIGN KEY(majorId) REFERENCES major(id);

3.4 删除约束

语法1：ALTER TABLE 表名 MODIFY COLUMN 字段 字段类型 约束的反值;

语法2：ALTER TABLE 表名 DROP 约束类型 约束名;

#1 删除非空约束
ALTER TABLE stuinfo5 MODIFY COLUMN stuName VARCHAR(20) NULL;
#2 删除默认约束
ALTER TABLE stuinfo5 MODIFY COLUMN age INT ;
#3 删除主键
ALTER TABLE stuinfo5 DROP PRIMARY KEY;
#4 删除唯一
ALTER TABLE stuinfo5 DROP INDEX seat;
#5 删除外键
ALTER TABLE stuinfo5 DROP FOREIGN KEY fk_stuinfo5_major;

七、DDL语言

1、库的管理

1.1 新增库

语法：CREATE DATABASE [IF NOT EXISTS] 库名;

#1 加上IF NOT EXISTS，如果数据库不存在才创建(避免报错)
CREATE DATABASE IF NOT EXISTS dbtest;

1.2 删除库

语法：DROP DATABASE [IF EXISTS] 库名;

#1 加上IF EXISTS，如果数据库存在才删除(避免报错)
DROP DATABASE IF EXISTS dbtest;

八、TCL语言

1、事务

1.1 事务概念

事务：一组逻辑操作单元，使数据从一种状态变换到另一种状态。需要注意的是在MySQL的存储引擎中，只有InnoDB支持事务。

事务处理（事务操作）：保证所有事务都作为一个工作单元来执行，即使出现了故障，都不能改变这种执行方式。当在一个事务中执行多个操作时，要么所有的事务都被提交(commit)，那么这些修改就永久地保存下来；要么数据库管理系统将放弃所作的所有修改，整个事务回滚(rollback)到最初状态。

1.2 事务特性(ACID)

1）原子性（Atomicity）
原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。

2）一致性（Consistency）
事务必须使数据库从一个一致性状态变换到另外一个一致性状态。

3）隔离性（Isolation）
事务的隔离性是指一个事务的执行不能被其他事务干扰，即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。

4）持久性（Durability）
持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来的其他操作和数据库故障不应该对其有任何影响。

1.3 事务创建

1）隐式事务

隐式事务没有明显的开始结束标志，如 INSERT、DELETE、UPDATE等。

2）显式事务

显式事务有明显的开始结束标志，在进行事务创建前要确保自动提交功能为禁用。

步骤①：关闭自动提交功能

SET autocommit=0;

步骤②：编写事务中的SQL语句(SELECT、INSERT、UPDATE、DELETE ...)

步骤③：结束事务，使用COMMIT提交事务；使用ROLLBACK回滚事务；搭配使用ROLLBACK与SAVEPOINT可以回滚到保存点

需要注意的是如果使用TRUNCATE删除表，那么即使用ROLLBACK回滚，数据仍会被删除。也就是说对于CREATE、DROP等操作，即使回滚也无法撤销。

3）实例

下面用一个常见的转账场景来说明事务如何创建以及有何作用

假设有这么一个用户信息表：有id,姓名,存款三个字段

image

现在张三给李四转账100块钱，正常来说张三账户出账和李四账户进账应该是一个整体，不可以独立完成，因此这个过程在数据库中就可以称之为事务。实际在开发中通过捕获异常或者添加某些条件，可以设置合适的提交时机以及回滚前提。

#1 模拟事务正常执行提交时，此时事务生效，数据被持久化到数据库中
SET autocommit=0;
UPDATE money_test SET deposit=1100 WHERE id=1;
UPDATE money_test SET deposit=900 WHERE id=2;
COMMIT;
#2 模拟事务出现异常回滚时，此时事务回滚，数据保持不变
SET autocommit=0;
UPDATE money_test SET deposit=1100 WHERE id=1;
UPDATE money_test SET deposit=900 WHERE id=2;
ROLLBACK;

2、隔离级别

2.1 事务并发问题

对于同时运行的多个事务, 当这些事务访问数据库中相同的数据时, 如果没有采取必要的隔离机制, 就会导致各种并发问题:

1）脏读: 对于两个事务 T1、T2，T1 读取了已经被 T2 更新但还没有被提交的字段。之后, 若 T2 回滚, T1读取的内容就是临时且无效的。

2）不可重复读: 对于两个事务T1、T2， T1 读取了一个字段, 然后 T2 更新了该字段。之后, T1再次读取同一个字段, 值就不同了。

3）幻读: 对于两个事务T1、T2，T1 从一个表中读取了一个字段, 然后 T2 在该表中插入了一些新的行。之后, 如果 T1 再次读取同一个表, 就会多出几行。

2.2 事务隔离

数据库系统必须具有隔离并发运行各个事务的能力, 使它们不会相互影响, 避免各种并发问题。一个事务与其他事务隔离的程度称为隔离级别。数据库规定了多种事务隔离级别, 不同隔离级别对应不同的干扰程度, 隔离级别越高, 数据一致性就越好, 但并发性越弱。

2.3 隔离级别

图源：尚硅谷

MySQL支持 4 种事务隔离级别，默认的事务隔离级别为: REPEATABLE READ。

#1 查看当前的隔离级别: 
SELECT @@tx_isolation;
#2 设置当前 mySQL 连接的隔离级别为READ COMMITTED:  
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
#3 设置数据库系统的全局的隔离级别为READ COMMITTED:
SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;

九、视图

视图是虚拟的表，和普通表一样使用，其中的数据来自于自定义语句中查询的表，并且是在使用视图时动态生成的，只保留SQL逻辑。视图中的数据是依赖于真实表中的数据的，一旦真实表中的数据发生改变，显示在视图中的数据也会发生改变。一般用于替换复用程度高/结构复杂的SQL语句，有时也为了保护数据提高安全性。

1、创建视图

创建语法：CREATE VIEW 视图名 AS 查询语句;

假设对于本文的employees表：现在只想把其中的name和phone开放给其他人查看，那么要新建一个表吗？有新数据了怎么办？或许建立视图可以很好解决这个问题。我们可以建立一个仅包含name和phone列数据的视图，由于它只是对原始数据表的一个展示，因此也不用担心数据同步的问题。

#1 创建视图名为employees_view
CREATE VIEW employees_view 
AS 
SELECT `name`,`phone` FROM employees;

2、查看视图

语法1：DESC 视图名; 【查看字段信息】

语法2：SHOW CREATE VIEW 视图名; 【查看视图的详细定义】

3、修改视图

语法1：CREATE OR REPLACE VIEW 视图名 AS 查询语句;

语法2：ALTER VIEW 视图名 AS 查询语句;

4、删除视图

语法：DROP VIEW 视图名1,视图名2, ... ;

5、视图更新

如果通过插入、修改和删除操作更新视图中的数据，实质上是在更新视图所引用的基本表的数据，因此要满足基本表的数据定义。对于可更新的视图，视图中的行和基本表的行之间必须具有一对一的关系，具有以下特点的视图不能更新：

①包含关键字： GROUP BY，DISTINCT，HAVING ，UNION，UNION ALL。

②视图是常量视图

③SELECT中包含子查询

④数据源是一个不能更改的视图，查询语句中的FROM后面是一个不能更改的视图

十、存储过程与函数

1、变量

MySQL中的变量可分为系统变量和自定义变量。

1）系统变量又包括会话变量（session）与全局变量（global），需要注意的是服务器每次重启都会为全局变量赋初值，也就是说修改的全局变量重启之后将会恢复原来的值（即不能跨重启）。

#1 查看所有会话变量
SHOW SESSION VARIABLES; 
#2 查看所有全局变量
SHOW GLOBAL VARIABLES; 
#3 查看满足条件的变量
SHOW SESSION VARIABLES LIKE '%char%';
SHOW GLOBAL VARIABLES LIKE '%char%';
#4 查看指定的变量
SELECT @@global.变量名
SELECT @@session.变量名    或   SELECT @@变量名
#5 为全局变量赋值
方式①： set global 变量名=值
方式②： set @@global.变量名=值
#6 为会话变量赋值
方式①： set session 变量名=值
方式②： set @@session.变量名=值
方式③： set @@变量名=值

2）自定义变量又分为用户变量与局部变量，用户变量在设置的当前连接/会话有效，而局部变量仅在BEGIN END中有效，且需要定义在首部。自定义变量需要先声明、赋值再使用。

# 用户变量
#1 声明并初始化
方式①： SET @变量名=值;
方式②： SET @变量名:=值;
方式③： SELECT @变量名:=值;
#2 赋值
方式①： SET @变量名=值;
方式②： SET @变量名:=值;
方式③： SELECT @变量名:=值;
方式④： SELECT 字段 INTO @变量名 FROM 表;(需为单值)
#3 查看变量
SELECT @变量名;

# 局部变量
#1 声明并初始化
DECLARE 变量名 类型 DEFAULT 值
#2 赋值
方式①： SET 变量名=值;
方式②： SET 变量名:=值;
方式③： SELECT @变量名:=值;
方式④： SELECT 字段 INTO 变量名 FROM 表;
#3 使用
SELECT 变量名;

2、存储过程

存储过程是一组预先编译好的SQL语句的集合，类似批处理语句。对于调用者来说，存储过程封装了 SQL 语句，调用者无需考虑逻辑功能的具体实现过程。使用存储过程提高了SQL语句的重用性，
同时有利于降低网络负载。

2.1 创建与调用

创建语法：CREATE PROCEDURE 存储过程名(参数列表) BEGIN 存储过程体(一组合法的SQL语句) END;

调用语法：CALL 存储过程名 结束标志

①如果存储过程仅为一句SQL语句BEGIN END可省略

②存储过程体的SQL语句要求使用;结尾，因此需要使用 DELIMITER 结束标志 重新设置存储过程的结束标志，例如：delimiter

③参数列表包括三部分: 参数模式+参数名+参数类型，其中参数模式有三种： IN OUT INOUT，其中IN代表该参数作为输入，OUT代表该参数作为输出，INOUT代表该参数即可作为输入也可作为输出

④如果使用OUT/INOUT模式，需要先定义一个变量用于接收得到的参数值

以下示例均在MySQL命令行客户端中进行

#1 创建参数列表为空的存储过程：用于查询stuinfo表的name、sex、phone字段数据
DELIMITER $
CREATE PROCEDURE myp1()
BEGIN
    SELECT `name`,`sex`,`phone` FROM stuinfo;
END $
#2 调用
CALL myp1()$

#1 创建带IN模式参数的存储过程：用于根据传入的姓名查询对应的学生信息
CREATE PROCEDURE myp2(IN uname VARCHAR(50))
BEGIN
    SELECT * 
    FROM stuinfo
    WHERE stuinfo.`name`=uname;
END $
#2 调用
CALL myp2("张三") $

#1 创建带OUT模式参数的存储过程：用于根据传入的姓名查询对应学生的phone并作为参数值返回
CREATE PROCEDURE myp3(IN uname VARCHAR(50),OUT uphone VARCHAR(11))
BEGIN
    SELECT phone INTO uphone
    FROM stuinfo
    WHERE stuinfo.`name`=uname;
END $
#2 创建变量，调用存储过程，查看变量值
SET @phone_out='' $
CALL myp3("张三",@phone_out) $
SELECT @phone_out $

#1 创建带INOUT模式的存储过程：传入num1，num2两个值，返回各自的平方
CREATE PROCEDURE myp4(INOUT num1 INT ,INOUT num2 INT)
BEGIN
    SET num1=num1*num1;
    SET num2=num2*num2;
END $
#2 创建变量，调用存储过程，查看变量值
set @num1=10 $
SET @num2=6 $
call myp4(@num1,@num2) $
select @num1,@num2 $

2.2 删除存储过程

语法：DROP PROCEDURE 存储过程名

2.3 查看存储过程

语法：SHOW CREATE PROCEDURE 存储过程名

3、函数

函数的优点、用途等与存储过程相同，不同的是存储过程可以无返回，也可以有多个返回，而函数只允许有一个返回。

3.1 创建与调用

创建语法：CREATE FUNCTION 函数名(参数列表) RETURNS 返回类型 BEGIN 函数体 END

调用语法：SELECT 函数名(参数) 结束标志

①参数列表包括参数名和参数类型

②函数体中必须有RETURN语句，RETURN语句可以不放在末尾

③如果函数体仅有一句，可以省略begin end

④同样需要使用DELIMITER设置结束标志

#1 创建无入参函数：用于返回stuinfo中学生数
CREATE FUNCTION myf1() RETURNS INT
BEGIN
    DECLARE countx INT DEFAULT 0;
    SELECT COUNT(*) INTO countx
    FROM stuinfo;
    RETURN countx;
END $
#2 调用
SELECT myf1()$

#1 创建有入参函数：用于根据传入的员工姓名返回对应的电话号码
CREATE FUNCTION myf2(uname VARCHAR(50)) RETURNS VARCHAR(11)
BEGIN
    DECLARE uphone VARCHAR(11) DEFAULT '';
    SELECT phone INTO uphone 
    FROM stuinfo
    WHERE `name`=uname;
    RETURN uphone; 
END $
#2 调用
SELECT myf2('张三') $

3.2 删除函数

语法：DROP FUNCTION 函数名

3.3 查看函数

语法：SHOW CREATE FUNCTION 函数名

十一、流程控制

1、分支

1.1 IF函数

语法：IF(条件，值1，值2)

①实现简单的双分支，可以应用在任何地方

②如果条件成立则返回值1，否则返回值2

1.2 IF结构

语法：IF 条件1 THEN 语句1; ELSEIF 条件2 THEN 语句2; ... 【ELSE 语句n;】 END IF;

①实现多重分支，需要在BEGIN END中使用

1.3 CASE函数

情况一：用于实现等值判断

语法:

CASE 变量|表达式|字段
WHEN 要判断的值 THEN 返回的值1或语句1
WHEN 要判断的值 THEN 返回的值2或语句2;
...
ELSE 要返回的值n或语句n；
END CASE;

情况二：用于实现区间判断

语法：

CASE
WHEN 要判断的条件1 THEN 返回的值1或语句1;
WHEN 要判断的条件2 THEN 返回的值2或语句2;
...
ELSE 要返回的值n或语句n;
END CASE;

①作为表达式，嵌套在其他的语句中使用，可以放在任何地方

②作为独立语句，放在BEGIN END中

③如果WHEN中的值满足或条件成立，则执行对应的THEN后的语句，并且结束CASE，如果都不满足则执行ELSE中的语句或值

④ELSE可以省略，如果ELSE省略了，并且所有WHEN条件都不满足，则返回NULL

2、循环

循环控制包括两部分：ITERATE指示循环继续，类似于continue；LEAVE指示跳出循环，类似于break

2.1 WHILE循环

语法：【标签：】 WHILE 循环条件 DO 循环体； END WHILE 【标签】;

2.2 LOOP循环

主要用于简单的死循环

语法：【标签：】 LOOP 循环体 END LOOP 【标签】;

2.3 REPEAT循环

语法：【标签：】 REPEAT 循环体; UNTIL 结束循环的条件; END REPEAT 【标签】；

参考资料：

[1] 《SQL必知必会》

[2] 《MySQL教程：MySQL数据库学习宝典（从入门到精通） (biancheng.net)》

[3] 《mysql字段类型tinyint、smallint、mediumint、int、bigint详解_贝伦酱-CSDN博客》

[4] 《SQL语句之查询数据（MySQL）Star's Tech Blog-CSDN博客sql查询数据》

[5] 《尚硅谷MySQL数据库全套完整版（sql数据库优化）_哔哩哔哩 (゜-゜)つロ 干杯~-bilibili》