MySQL

时间：2020-11-29 01:25:05

MySQL

MySQL

一、存储数据的方式

瞬时状态：存在内存中

持久化状态：存在文件中

缺点：

  • 数据类型
  • 存储量级
  • 安全
  • 备份恢复机制

二、概念

按照数据结构组织、存储、管理数据的仓库。database，简称DB。

三、分类

  • 网状结构：以节点的形式存储和访问。
  • 层次结构：定向有序的树状结构。
  • 关系SQL结构：也称为关系型，以表格存储，多个表格之间建立关联关系，通过多种运算实现访问。（主流）具有代表性的有以下4种：
    • Oracle：甲骨文公司的主力产品。
    • DB2：IBM公司的大型数据库。
    • MySQL：免费开源的数据库。MariaDB是MySQL的一个重要分支。
    • SQL Server：微软的产品，中大型数据库。
    • 还有类似于SQLLite这种使用在手机上的数据库。
  • 非关系结构：NO-SQL（Not Only SQL），主要是使用大型的文件系统，来实现三高（海量数据、高性能、高并发）处理，主流有Redis、MongoDB、ES等。

四、数据库管理系统

管理数据的软件，用于建立、管理和维护数据库。DBMS。

五、MySQL

是目前主流的关系型数据库管理系统（RDBMS）之一。

目录结构：

  • bin：命令文件
  • lib：库文件
  • include：头文件
  • share：与INNODB引擎相关，多语言设置等

六、SQL语言

Structured Query Language结构化查询语言。

用来操作数据库的语言。

对于数据库的增删改查，也可以简称为：C（create）R（read）U（update）D（delete）

七、数据库连接

打开cmd命令行界面：

输入mysql -uroot -p回车，然后输入密码。（需要事先设置好环境变量）

在数据库的命令行窗口，输入mysql的操作，需要以分号结束。

八、常见的数据库命令

注意：mysql、information_schema，performance_schema、sys这些都是系统数据库，不要随意操作。

-- 查看所有的数据库
show databases;

-- 创建数据库
create database mydb1; -- 创建一个使用默认字符集的数据库
create database mydb1 character set utf8; -- 创建一个使用指定字符集的数据库
create database if not exists mydb1; -- 当数据库不存在时，创建一个新的数据库

-- 查看创建数据库的信息
show create database mydb1;

-- 修改数据库
alter database mydb1 character set gbk;

-- 删除数据库
drop database mydb1;

-- 查看当前所使用的数据库
select database(); -- 调用一个函数

-- 切换使用的数据库
use mydb1;

经典面试题：utf8与utf8mb4的区别？

utf8只支持普通的文字信息，utf8mb4支持4个字节的一些特殊的文字，例如emoji等。

九、常见的客户端工具

Navicat：收费工具，功能比较强大。

SQLyog：免费工具。

十、数据查询

10.1 导入预备数据

1、先查看需要导入的脚本文件(后缀名为.sql)中是否存在创建和使用数据库的命令。

2、如果没有则先创建数据库，并使用use命令切换数据库。

create database companydb character set utf8;
use companydb;

3、使用source命令导入脚本文件

source C:\Users\wangliang\Desktop\companydb.sql
10.2 基本查询

关系型数据库由多个表组成，每个表由行和列组成，类似Excel。

在表中可以进行查询，查询返回的结果集（结果的集合）以虚拟表的形式显示。

10.2.1 查询所有

查询整个表中的所有数据。

语法：SELECT 列名1, 列名2, 列名N FROM 表名

注意：所有的SQL语法不区分大小写

-- 查询所有列，可以用*来代替所有的列名
select * FROM t_employees; 
-- 指定列名查询
SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME FROM t_employees;
10.2.2 对查询的列进行运算

支持+、-、*、/加减乘除运算，不支持%作为求模运算，也不支持用+连接字符串。

注意：运算时，需要列的类型可以运算。

-- 查询年薪，通过月薪*12计算
SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, SALARY * 12 FROM t_employees;
10.2.3 别名

可以对查询的列名起别名。使用AS关键字，可以省略。

-- 起别名时，AS可以省略，别名可以加单引号，也可以不加
SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, SALARY * 12 AS 年薪 FROM t_employees;
-- 当别名或者列名表名用到关键字时，应该使用`符号引起来
SELECT FIRST_NAME `DESC` FROM t_employees;
10.2.4 对查询结果去重

注意：

  • DISTINCT用来去重，必须写在所有列的最前面。
  • DISTINCT必须要求所有的列完全相同，才能去重，如果有些列值不相同，则不能去重。
-- 对查询结果去重
SELECT DISTINCT MANAGER_ID FROM t_employees;
10.3 排序查询

将查询结果进行排序。

语法：SELECT 列名 FROM 表名 ORDER BY 排序列名 排序规则。

排序规则：

  • ASC：升序，由小到大
  • DESC：降序，由大到小

注意：可以不写排序规则，默认ASC

-- 根据SALARY降序排列
SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, SALARY FROM t_employees ORDER BY SALARY DESC;

可以根据多列排序，多列使用逗号隔开。

排序的规则是，先按第一列排序，如果排序后有相同的，则按第二列排序，以此类推。

-- 先将查询结果按照SALARY降序排列，然后如果SALARY相同的按照EMPLOYEE_ID升序排列
SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, SALARY FROM t_employees ORDER BY SALARY DESC, EMPLOYEE_ID ASC;
10.4 条件查询

语法：

SELECT 列名 FROM 表名 WHERE 条件

10.4.1 等值判断

使用=号判断是否相等，与java中不一样。

SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, SALARY FROM t_employees WHERE SALARY = 11000;
SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, SALARY FROM t_employees WHERE FIRST_NAME = 'Ellen';

注意：在数据库操作中，值除了数字可以直接写以外，其他的日期，字符串等都需要加单引号。

10.4.2 不等值判断

使用>、<、>=、<=、!=、<>来进行不等值判断。

注意：!=和<>都表示不等于，没有区别，早期版本使用<>表示不等于，后来为了与其他数据库语法通用，新增了!=。

SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, SALARY FROM t_employees WHERE SALARY >=12000;
10.4.3 逻辑判断

使用AND、OR、NOT

SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, SALARY FROM t_employees WHERE SALARY >=12000 AND FIRST_NAME = 'Steven';
-- 薪资不大于等于12000，相当于取反
SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, SALARY FROM t_employees WHERE NOT SALARY >=12000;
10.4.4 区间判断

使用 BETWEEN 小值 AND 大值。

SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, SALARY FROM t_employees WHERE  SALARY BETWEEN 9000 AND 10000;
-- 相当于
SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, SALARY FROM t_employees WHERE SALARY >= 9000  AND SALARY <= 10000;

经典面试题：BETWEEN AND与>=、<=的区别：

  • 使用>=、<=没有要求顺序，可以先写大值或小值。
  • 使用BETWEEN AND只能小值在前，否则没有查询结果。
10.4.5 Null判断

要判断Null，需要使用IS NULL或者IS NOT NULL

注意：不能使用 = NULL

SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, SALARY, MANAGER_ID FROM t_employees WHERE MANAGER_ID IS NULL;
SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, SALARY, MANAGER_ID FROM t_employees WHERE MANAGER_ID IS NOT NULL;
10.4.6 枚举值判断

当需要查询多个=值时，可以使用IN来枚举。

SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, SALARY, MANAGER_ID FROM t_employees 
WHERE EMPLOYEE_ID = '100' OR EMPLOYEE_ID = '102' OR EMPLOYEE_ID = '104';
-- 可以写为
SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, SALARY, MANAGER_ID FROM t_employees 
WHERE EMPLOYEE_ID IN ('100', '102', '104');
10.4.7 模糊查询

使用LIKE 通配符。

  • 例如：LIKE ‘张_’，匹配张后面一个字符，可以匹配张三，但是不能匹配张三丰。
  • 例如：LIKE ‘张%’，可以匹配张后面任意个字符，可以匹配张，也可以匹配张三，也可以匹配张三丰等。

如果要匹配任意位置包含一个张字，可以使用LIKE ‘%张%’。

注意：模糊查询只能结合LIKE关键字使用。

-- 匹配所有以L开头的
SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, SALARY, MANAGER_ID FROM t_employees 
WHERE FIRST_NAME LIKE 'L%';

-- 匹配以L开头的，长度为4的
SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, SALARY, MANAGER_ID FROM t_employees 
WHERE FIRST_NAME LIKE 'L___';
10.4.8 分支结构查询

语法：

CASE

​ WHEN 条件1 THEN 结果1

​ WHEN 条件2 THEN 结果2

​ WHEN 条件3 THEN 结果3

​ ELSE 结果4

END;

SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, SALARY, 
CASE
	WHEN SALARY >= 20000 THEN 'A'
	WHEN SALARY >= 15000 AND SALARY < 20000 THEN 'B'
	WHEN SALARY >= 10000 AND SALARY < 15000 THEN 'C'
	WHEN SALARY >= 6000 AND SALARY < 10000 THEN 'D'
	ELSE 'E'
END AS 级别
 FROM t_employees ORDER BY SALARY DESC;
10.5 时间（函数）查询

可以在查询的列中，也可以在查询的条件中。

-- 当前时间
SELECT SYSDATE();
SELECT NOW();
-- 当前日期
SELECT CURDATE();
SELECT CURRENT_DATE();
-- 当前时间
SELECT CURTIME();
SELECT CURRENT_TIME();
-- 获得第几周
SELECT WEEK('2021-12-31');
SELECT WEEK(NOW());
-- 获得年份
SELECT YEAR('2021-12-31');
-- 查询2000年之后出生的员工，用作条件
SELECT * FROM t_employees WHERE YEAR(HIRE_DATE) >= 2000;
SELECT * FROM t_employees WHERE HIRE_DATE >= '2000-02-01';
-- 获得小时，分钟，秒
SELECT HOUR(CURTIME());
SELECT MINUTE(CURTIME());
SELECT SECOND(CURTIME());
-- 获取两个日期之间相隔的天数，前面的日期-后面的日期
SELECT DATEDIFF('2020-12-12','2021-11-11');
-- 获取3天以后的日期
SELECT ADDDATE('2020-12-12', 3);
10.6 字符串（函数）查询

字符串函数，与Java中类似，不推荐在sql中使用太多函数，影响性能。

-- 拼接字符串
SELECT EMPLOYEE_ID, CONCAT(FIRST_NAME, ', ', LAST_NAME) FROM t_employees;
-- 添加、删除、替换字符串
-- 位置从1开始
SELECT INSERT('Hello', 2, 0, 'aaa'); -- 添加
SELECT INSERT('Hello', 2, 3, ''); -- 删除
SELECT INSERT('Hello', 2, 3, 'bbb'); -- 替换
-- 转换大小写
SELECT LOWER('Hello');
SELECT UPPER('Hello');
-- 截取字符串
SELECT SUBSTRING('hello, world',4, 5);
SELECT SUBSTR('hello, world',4, 5);
10.7 聚合函数

聚合函数一般用来对数据进行统计查询。

常用的聚合函数：

  • SUM：求和
  • AVG：求平均数
  • MAX：求最大值
  • MIN：求最小值
  • COUNT：计数

注意：聚合函数使用时，一般不能查询其他字段，除非使用分组，并且查询的内容与分组有关。

-- 统计数量
SELECT COUNT(EMPLOYEE_ID) FROM t_employees;
SELECT COUNT(*) FROM t_employees;
SELECT COUNT(1) FROM t_employees;
-- 求和
SELECT SUM(SALARY) FROM t_employees;
-- 求平均
SELECT AVG(SALARY) FROM t_employees;
-- 求最大值
SELECT MAX(SALARY) FROM t_employees;
-- 求最小值
SELECT MIN(SALARY) FROM t_employees;
-- 一起查询
SELECT SUM(SALARY), MAX(SALARY), MIN(SALARY),AVG(SALARY),COUNT(1) FROM t_employees;
-- 不正确的写法（不能查询其他字段）
SELECT EMPLOYEE_ID,FIRST_NAME,LAST_NAME,MIN(SALARY) FROM t_employees;
10.8 分组查询

注意：分组一定会聚合。

-- 按部门分组，求每个部门的平均薪资
SELECT DEPARTMENT_ID, AVG(SALARY) FROM t_employees GROUP BY DEPARTMENT_ID;
-- 按部门分组，求每个部门的最高薪资
SELECT DEPARTMENT_ID, MAX(SALARY) FROM t_employees GROUP BY DEPARTMENT_ID;
-- 按部门分组，求每个部门的最低薪资
SELECT DEPARTMENT_ID, MIN(SALARY) FROM t_employees GROUP BY DEPARTMENT_ID;
-- 按部门分组，求每个部门的总薪资
SELECT DEPARTMENT_ID, SUM(SALARY) FROM t_employees GROUP BY DEPARTMENT_ID;
-- 按部门分组，求每个部门的人数
SELECT DEPARTMENT_ID, COUNT(1) FROM t_employees GROUP BY DEPARTMENT_ID;

SELECT DEPARTMENT_ID, COUNT(1), AVG(SALARY),MAX(SALARY),MIN(SALARY),SUM(SALARY)  FROM t_employees GROUP BY DEPARTMENT_ID;

注意：当使用聚合函数时，前面查询的列必须在聚合函数中，或者是后面的分组的列，以及与分组的列有一对一关系的。

-- 错误的查询语句
SELECT DEPARTMENT_ID, MIN(SALARY), EMPLOYEE_ID, FIRST_NAME, LAST_NAME FROM t_employees GROUP BY DEPARTMENT_ID;

-- 正确的写法
SELECT DEPARTMENT_ID, MIN(SALARY) FROM t_employees GROUP BY DEPARTMENT_ID;
10.9 分组过滤查询

使用having关键字，

注意：一般情况下，having关键字是要以聚合函数作为条件时使用，如果条件没有聚合函数，应该将该条件写在where，而不应该使用having。

-- 查询部门最高薪资大于10000的部门以及其最高薪资
SELECT DEPARTMENT_ID, MAX(SALARY) FROM t_employees GROUP BY DEPARTMENT_ID HAVING MAX(SALARY) > 10000;
10.10 限定查询

限制查询结果的数量：【mysql专用，其他数据库不一样】

使用limit关键字

  • limit 10 表示显示前10条
  • limit 20, 10 表示跳过20条，显示10条，即显示第21-30条

所以根据上面的规则，如果需要实现分页效果：limit skip, size

skip的公式应该是skip = (page - 1) * size;

-- 显示前面30条
SELECT * FROM t_employees LIMIT 30;
-- 跳过30条，显示10条，即显示第31-40条
SELECT * FROM t_employees LIMIT 30, 10;
10.11 基础查询总结

SQL的编写顺序：

  • SELECT 列名
  • FROM 表名
  • WHERE 条件
  • GROUP BY 分组列
  • HAVING 过滤条件
  • ORDER BY 排序列 ASC\DESC
  • LIMIT skip, size

执行的顺序：

  • FROM 数据来源的表
  • WHERE 第一次筛选
  • GROUP BY 分组
  • HAVING 第二次过滤
  • SELECT 显示部分列
  • ORDER BY 将结果进行排序
  • LIMIT 显示部分行

十一、复杂查询

11.1 子查询

将一个查询结果作为另一个查询语句中的一部分。

11.1.1 将查询结果作为条件(普通等值或不等值)

查询薪资高于Bruce的所有员工信息。

分析：

  • 表：员工表
  • 查询的内容：员工信息，查询列为所有列
  • 条件：薪资高于
  • 子查询：薪资高于Bruce，并非一个固定值，所有需要先查询Bruce的薪资
-- 先查询Bruce的薪资，发现查询结果为6000
SELECT SALARY FROM t_employees WHERE FIRST_NAME = 'Bruce';
-- 再查询高于Bruce的员工信息
SELECT * FROM t_employees WHERE SALARY > 6000;
-- 最后，整合成一条语句，使用子查询
SELECT * FROM t_employees WHERE SALARY > (SELECT SALARY FROM t_employees WHERE FIRST_NAME = 'Bruce');

注意：上面的写法，要求查询Bruce的薪资结果必须是单行单列的。

当查询结果不是单行时，上面的写法会出错，那么此时有3种选择：等于、大于、小于

11.1.2 将查询结果作为条件(枚举值)

当要使用等于时，那么只需要等于其中一个值。使用IN。

-- 使用IN判断等于其中的任何一个值
SELECT * FROM t_employees WHERE SALARY IN (SELECT SALARY FROM t_employees WHERE FIRST_NAME = 'David');
11.1.3 将查询结果作为条件(使用ALL或ANY)

当要使用大于或小于时，需要决定是大于或小于所有的记录，还是大于或小于其中的一条（部分）记录。

ALL表示所有，ANY表示部分。

-- 大于后面查询的所有结果，即大于最大
SELECT * FROM t_employees WHERE SALARY > ALL (SELECT SALARY FROM t_employees WHERE FIRST_NAME = 'David');
-- 结果等同于
SELECT * FROM t_employees WHERE SALARY > (SELECT MAX(SALARY) FROM t_employees WHERE FIRST_NAME = 'David');

-- 大于后面查询的任意结果，即大于最小
SELECT * FROM t_employees WHERE SALARY > ANY (SELECT SALARY FROM t_employees WHERE FIRST_NAME = 'David');
-- 结果等同于
SELECT * FROM t_employees WHERE SALARY > (SELECT MIN(SALARY) FROM t_employees WHERE FIRST_NAME = 'David');
11.1.4 将查询结果作为临时表

查询结果也是有行有列的，与表结构一样，所以查询结果可以看作一个虚拟的表，可以对其进行再次的查询。

使用查询结果作为临时表的常见场景：

  • 需要将查询中的别名作为条件
  • 需要将查询的结果作为临时表与其他表进行关联时
  • 需要在查询结果中再次查询的
-- 需要将查询中的别名作为条件
SELECT * FROM
(SELECT *, SALARY * 12 年薪 FROM t_employees) AS t
 WHERE 年薪 > 200000;

注意：将查询结果作为临时表再次查询，一定要给该结果起个别名。

11.2 合并查询

使用UNION或UNION ALL实现将多个查询结果合并到一起。以第一个查询为基础，将后面的查询结果合并到第一次的结果上。

  • 查询的列数要一样。
  • 查询的列名或者类型可以不一样。
  • UNION会去掉重复记录，UNION ALL会保留重复记录。所有列都相同才叫重复记录。
SELECT EMPLOYEE_ID, DEPARTMENT_ID FROM t_employees WHERE EMPLOYEE_ID = 100
UNION 
SELECT EMPLOYEE_ID, DEPARTMENT_ID FROM t_employees WHERE EMPLOYEE_ID < 103
UNION ALL
SELECT EMPLOYEE_ID, FIRST_NAME FROM t_employees WHERE EMPLOYEE_ID = 107;
11.3 连接查询

将多个表一起查。

11.3.1 直接查询多个表

直接查询多个表，在where里面写关联条件，如果不写，则形成笛卡尔积。

在MySQL老版本里只支持此连接方式，不支持INNER JOIN内连接的写法等。

SELECT 列名 FROM 表1 , 表2, 表3 WHERE 表1和2连接条件 AND 表2(1)和表3的连接条件

SELECT e.EMPLOYEE_ID, e.FIRST_NAME, e.LAST_NAME, e.DEPARTMENT_ID, d.DEPARTMENT_NAME 
FROM t_employees e, t_departments d WHERE e.DEPARTMENT_ID = d.DEPARTMENT_ID;
11.3.2 内连接

结果相当于上面的查询结果。

语法：

SELECT 列名 FROM 表1

INNER JOIN 表2

ON 连接条件

INNER JOIN 表3

ON 连接条件

SELECT e.EMPLOYEE_ID, e.FIRST_NAME, e.LAST_NAME, e.DEPARTMENT_ID, d.DEPARTMENT_NAME 
FROM t_employees e
INNER JOIN 
t_departments d 
ON e.DEPARTMENT_ID = d.DEPARTMENT_ID;

注意：内连接会将连接条件相等的数据显示，如果有无法关联的记录，则不会显示。

11.3.3 外连接

以一张表为基准，连接另一张表。分为左外连接和右外连接。

左外连接：以左边表为基准。

右外连接：以右边表为基准。

LEFT OUTER JOIN和RIGHT OUTER JOIN，可以不写OUTER

  • 会将基准表中的所有数据查询，另一张表的数据有连接的显示连接，没有连接的显示为空。
SELECT e.EMPLOYEE_ID, e.FIRST_NAME, e.LAST_NAME, e.DEPARTMENT_ID, d.DEPARTMENT_NAME 
FROM t_employees e
LEFT JOIN 
t_departments d 
ON e.DEPARTMENT_ID = d.DEPARTMENT_ID;
11.3.4 交叉连接

会得到笛卡尔积。

SELECT e.EMPLOYEE_ID, e.FIRST_NAME, e.LAST_NAME, e.DEPARTMENT_ID, d.DEPARTMENT_NAME 
FROM t_employees e
CROSS JOIN 
t_departments d ;

十二、复杂查询案例

12.1 案例1

查询FIRST_NAME重复的记录

-- 查询重复的数据
-- 查询FIRST_NAME重复的记录
SELECT FIRST_NAME FROM t_employees GROUP BY FIRST_NAME  HAVING COUNT(1) > 1;
12.2 案例2

查询各部门最高薪资员工信息。

分析：

  • 查询：员工信息
  • 条件：将各部门的最高薪资查询，以部门分组
  • 将员工表与各部门最高薪资的查询结果进行内连接
  • 条件，当前员工的薪资等于最高薪资
SELECT * FROM t_employees t1
INNER JOIN
(SELECT DEPARTMENT_ID, MAX(SALARY) MAX_SALARY FROM t_employees GROUP BY DEPARTMENT_ID) t2
ON t1.DEPARTMENT_ID = t2.DEPARTMENT_ID
WHERE t1.SALARY = t2.MAX_SALARY;
12.3 案例3

查询各部门(显示部门名称)不低于平均薪资的人数。

分析：

查询：部门名称，人数，需要员工表和部门表

条件：查询各部门平均薪资

条件：高于平均薪资的人数

SELECT t3.DEPARTMENT_NAME, COUNT(1) FROM t_employees t1
INNER JOIN
(SELECT DEPARTMENT_ID, AVG(SALARY) AVG_SALARY FROM t_employees GROUP BY DEPARTMENT_ID) t2
ON t1.DEPARTMENT_ID = t2.DEPARTMENT_ID
INNER JOIN
t_departments t3
ON t1.DEPARTMENT_ID = t3.DEPARTMENT_ID
WHERE t1.SALARY >= t2.AVG_SALARY GROUP BY t1.DEPARTMENT_ID;
12.4 案例4

行转列

分析：

查询：先查询学生每门课程的成绩

SELECT s.s_name, c.co_name, sc.score FROM student s
INNER JOIN scores sc
ON s.s_id = sc.s_id
INNER JOIN courses c
ON c.co_id = sc.co_id;
姓名 课程 成绩
张三 HTML 80
张三 Java 90
李四 HTML 95
李四 Java 98

行转列后会显示为：

姓名 HTML Java 总分
张三 80 90 170
李四 95 98 193 
-- 行转列
SELECT s.s_name,  
MAX(CASE WHEN co_name = 'HTML' THEN score ELSE 0 END) AS HTML,
MAX(CASE WHEN co_name = 'SQL' THEN score ELSE 0 END) AS `SQL`,
MAX(CASE WHEN co_name = 'Java' THEN score ELSE 0 END) AS Java,
SUM(score) AS 总分
 FROM student s
INNER JOIN scores sc
ON s.s_id = sc.s_id
INNER JOIN courses c
ON c.co_id = sc.co_id
GROUP BY s_name;

十三、DML操作

DML：数据操作语言，对数据表中的数据进行增删改操作。

13.1 添加

语法：

  • 添加单行：INSERT INTO 表名(列名1, 列名2, 列名3) VALUES(值1, 值2, 值3);
    • 如果要对所有列进行添加，可以不指定列名。
  • 添加多行：INSERT INTO 表名(列名1, 列名2, 列名3) VALUES(值1, 值2, 值3), (值1, 值2, 值3), (值1, 值2, 值3), (值1, 值2, 值3);
-- 单行添加，添加部分列，没有添加的列必须允许为NULL
INSERT INTO t_jobs(JOB_ID, JOB_TITLE) VALUES ('AAA', 'AAAAAAAA');
-- 单行添加，添加全部列，可以指定所有的列名，也可以不指定列名
INSERT INTO t_jobs(JOB_ID, JOB_TITLE, MIN_SALARY, MAX_SALARY) VALUES ('BBB', 'BBBBBBB', 2000, 20000);
INSERT INTO t_jobs VALUES ('CCC', 'CCCCCCC', 3000, 30000);

-- 添加多行
INSERT INTO t_jobs(JOB_ID, JOB_TITLE, MIN_SALARY, MAX_SALARY) 
VALUES 
('A1', 'A1A1', 2000, 20000), 
('A2', 'A2A1', 2000, 20000), 
('A3', 'A3A1', 2000, 20000),
('A4', 'A4A1', 2000, 20000),
('A5', 'A5A1', 2000, 20000);

注意：添加时列名和值要一一对应。

13.2 修改

语法：

UPDATE 表名 SET 列名1 = 值1, 列名2 = 值2, 列名3 = 值3 WHERE 条件

注意：如果没有写条件，会对所有数据进行修改。

-- 根据条件判断修改的行数
-- 修改1行
UPDATE t_jobs SET JOB_TITLE = 'B1B1', MIN_SALARY = 3000, MAX_SALARY = 15000 WHERE JOB_ID = 'BBB';
-- 修改多行
UPDATE t_jobs SET JOB_TITLE = 'B1B1', MIN_SALARY = 3000, MAX_SALARY = 15000 WHERE JOB_TITLE LIKE '%A1';
13.3 删除

语法：

DELETE FROM 表名 WHERE 条件

注意：如果没有写条件，会对所有数据进行删除。

DELETE FROM t_jobs WHERE job_title = 'B1B1';
13.4 清空

清空整张表

语法：

TRUNCATE TABLE 表名

TRUNCATE TABLE t_jobs;

经典面试题：

TRUNCATE 与 DELETE 区别：

DELETE 是删除表中的数据，不会删除表结构，也不会重置表中的设置（自动增长列）。

而TRUNCATE相当于删除表后重新新建一张新表，里面的所有设置都会重置。

十四、数据表操作

14.1 数据类型

数据类型的作用是约束当前列的数据。

大体分为三类：

  • 数值
  • 时间日期
  • 字符串

常见的数值类型：

  • INT
  • DOUBLE
  • DECIMAL（M,D），M为总长度，D为小数位，例如DECIMAL(5, 2)，范围为 -999.99 ~ 999.99

常见的日期时间类型：

  • DATE：只包含日期
  • TIME：只包含时间
  • DATETIME：包含日期和时间
  • TIMESTAMP：混合了日期时间，时间戳

小技巧：如果项目中的时间需要时区，可以使用TIMESTAMP或者干脆使用数值，记录距离1970-1-1的毫秒数。

常见的字符串类型：

  • CHAR：固定长度的字符串。需要设置长度。
  • VARCHAR：可变长度，但是总长度固定的字符串。需要设置长度。
  • BLOB：二进制内容，长文本数据
  • TEXT：文本内容，长文本数据

注意：无论是CHAR还是VARCHAR对应Java中都是String，不要使用Java中的char。

经典面试题：CHAR和VARCHAR的区别。

  • CHAR是固定长度，例如设置长度为10，如果添加的数据长度不为10，那么会在后面添加相应的空格补充到10。

  • VARCHAR是可变长度，例如设置长度为10，如果添加的数据长度不为10，也不会在后面添加空格。

  • CHAR和VARCHAR都是必须设置长度的，如果不设置，默认为255

  • 当设置长度后，内容不能超出了长度。

14.2 创建表

语法：

CREATE TABLE 表名(
列名 类型 [约束],
列名 类型 [约束],
列名 类型 [约束]
)[charset=utf8];

CREATE TABLE student(
	id INT,
	`name` VARCHAR(20),
	sex VARCHAR(1),
	age INT,
	cid INT
)charset=utf8;
14.3 修改表

修改表操作比较多：

  • 添加列
  • 修改列（类型和约束）
  • 修改列名
  • 删除列
  • 修改表名
-- 查看表结构
DESC student;
-- 添加列
ALTER TABLE student ADD phone VARCHAR(20);
-- 修改列（类型或约束）
ALTER TABLE student MODIFY phone VARCHAR(11) NOT NULL;
-- 修改列名
ALTER TABLE student CHANGE phone tel INT;
-- 删除列
ALTER TABLE student DROP tel;
-- 修改表名
ALTER TABLE student RENAME stu;

经典面试题：

当一个表中有海量数据（亿级）时，需要在生产环境不关闭的情况下，需要添加一个列，如何实现？

  • 创建一个新表，表与原表格式一致，并添加新的列。
  • 复制原表中的所有数据到新表
  • 将原表表名改掉，将新表表名改成原表表名。
  • 删除原表。

小技巧：推荐在建表时预留几个字段，类型推荐使用varchar，长度自行设置。

14.4 删除表

语法：

DROP TABLE 表名

DROP TABLE stu;

十五、约束

限制存入表中的数据的类型和规范叫作约束。

15.1 三范式

数据表设计的规范一共有5范式，一般要求至少要满足三范式。

第一范式：要求满足域完整性。主要是要求列不可再分。

第二范式：要求满足实体完整性。主要是要求必须要有主键，且其他字段都由主键决定。

第三范式：要求满足引用完整性。

15.2 域完整性
  • 列不可再分，不能使用一个列写多个内容。例如：爱好，不能写看小说,打游戏,睡觉，应该将其分开，再创建一个中间表。
  • 非空约束
    • NOT NULL，该列在添加时必须要有值，否则报错
    • DEFAULT，默认值
-- 默认值
ALTER TABLE student MODIFY age INT DEFAULT 20;
15.3 实体完整性
  • 主键约束。
    • PRIMARY KEY，该列的值不能重复，且不能为空，唯一。
  • 唯一约束。
    • UNIQUE，该列的值不能重复，但是可以为空，而且允许多条数据为空。一般情况下会设置为非空。
  • 自动增长列，（仅针对数字）
    • 只能设置在主键上，且只能有一个
    • 如果不设置主键值，会自动使用自动增长列。
    • 设置了自增列，还是可以手动添加主键。
    • 如果手动设置了主键大于当前应该使用的自动增长列值，那么下一次自动增长会在手动设置的主键最大值后+1。
    • 经典面试题：当使用int类型时，如果使用自动增长，主键已经达到最大值后，再添加会出现什么情况？答：会一直使用最大值，不再自增，如果表中最大值的记录已经删除，则会继续使用，如果还存在，则出现主键重复的错误。
-- 通过修改设置主键
ALTER TABLE student MODIFY id INT PRIMARY KEY;
-- 通过修改设置唯一键
ALTER TABLE student MODIFY name VARCHAR(20) UNIQUE;
-- 通过修改设置自动增长
ALTER TABLE student MODIFY id INT auto_increment;

-- 创建表使用多种约束
CREATE TABLE student(
	id INT PRIMARY KEY auto_increment,
	`name` VARCHAR(20) UNIQUE,
	sex VARCHAR(1) NOT NULL,
	age INT DEFAULT 20,
	cid INT
)charset=utf8;
15.4 引用完整性

使用外键来引用另一个表的主键，避免出现无效的数据。

语法：

CONSTRAINT 外键名 FOREIGN KEY(外键列) REFERENCES 对应表(主键列);

-- 使用修改表添加外键
ALTER TABLE stu ADD CONSTRAINT stu_fk FOREIGN KEY(cid) REFERENCES cla(cid);
-- 创建表时顺便创建主键和外键
CREATE TABLE stu(
	id INT auto_increment,
	`name` VARCHAR(20),
	sex VARCHAR(1) NOT NULL,
	age INT DEFAULT 20,
	cid INT, 
	PRIMARY KEY(id), -- 创建主键
	CONSTRAINT stu_fk FOREIGN KEY(cid) REFERENCES cla(cid) -- 创建外键
);

十六、表关系

16.1 一对一

两个表中的数据一一对应。例如：用户基本信息表和详情信息表

外键可以在任意一张表中建立。

user userinfo

1 张三 123456 1 城市学院 A

16.2 一对多（多对一）

例如：班级和学生就是一对多的关系，反之则是多对一的关系，一个班级有多个学生，每个学生只属于一个班级。

注意：一对多或者多对一的关系，外键需要建立在多方。

stu cla

1 张三 1 Java2203

2 李四

16.3 多对多

例如：学生和课程就是多对多的关系。一个学生可以学习多门课程，一门课程可以被多个学生学习。

注意：多对多关系必须要建立中间表来建立关系。

stu cour

1 张三 1 语文

2 李四 2 数学

stu_cou

1 1

1 2

2 1

2 2

CREATE TABLE stu(
	id INT auto_increment,
	`name` VARCHAR(20),
	PRIMARY KEY(id)
);

CREATE TABLE course(
	cid INT PRIMARY KEY auto_increment,
	cname VARCHAR(20)
);

CREATE TABLE stu_cou(
	sid INT,
	cid INT,
	PRIMARY KEY(sid, cid), -- 联合主键
	CONSTRAINT stu_fk FOREIGN KEY(sid) REFERENCES stu(id), -- 创建外键
	CONSTRAINT cou_fk FOREIGN KEY(cid) REFERENCES course(cid) -- 创建外键
);

-- 多表关联查询
SELECT
	s.`name`,
	s.id,
	c.cid,
	c.cname
FROM
	stu AS s
INNER JOIN stu_cou AS sc ON s.id = sc.sid
INNER JOIN course AS c ON sc.cid = c.cid

十七、事务

17.1 概念

事务是指由一条或多条SQL语句组成的一个最小的操作单元，当该事务中有一个操作失败了，则所有的SQL执行全部失败，只有所有的都成功，才成功。

17.2 原理

数据库会为每一个客户端连接建立一个临时的缓存区，事务中多个SQL的操作会暂存在缓存区，可以通过commit来提交最终结果（成功），或者使用rollback撤销（回滚）操作（失败）。

17.3 流程

1、模拟没有事务情形

  • 减钱，不加钱，发现无法撤销（回滚）

2、模拟有事务的情形

  • 失败回滚情形
    • 设置自动提交失效
    • 减钱，不加钱
    • 回滚，发现回到初始状态
    • 设置自动提交生效
  • 成功提交情形
    • 设置自动提交失效
    • 减钱
    • 加钱
    • 提交
    • 设置自动提交生效
-- 减钱
UPDATE account SET money = money - 1000 WHERE account = '1001';
-- 加钱
UPDATE account SET money = money + 1000 WHERE account = '1002';

-- 查询系统变量自动提交参数
SELECT @@autocommit;
-- 设置自动提交失效
SET autocommit = 0;
-- 设置自动提交有效
SET autocommit = 1;
-- 提交事务
COMMIT;
-- 回滚事务
ROLLBACK;
17.4 事务的特征

ACID

  • 原子性 Atomicity，一个事务内多个SQL形成一个整体，要么全部成功，要么全部失败
  • 一致性 Consistency，一个事务内有一个SQL失败，所有状态回滚到之前。
  • 隔离性 Isolation，两个事务是相互隔离的，只能看到操作前后的状态，不能看到中间状态。
  • 持久性 Duration，事务执行后，影响是永久的。

十八、权限管理

创建用户：

CREATE USER 用户名 IDENTIFIED BY 密码

授权：所有权限可以使用ALL，所有的数据库以及所有的表，可以使用*.*

GRANT 权限名 ON 数据库名.表名 TO 用户名;
撤销权限
REVOKE 权限名 ON 数据库名.表名 FROM 用户名;
删除用户
DROP USER 用户名;
– 刷新权限
FLUSH PRIVILEGES;

注意：所有的上述操作应该在有权限操作的用户登录时操作。

-- 创建用户
CREATE USER java2110 IDENTIFIED BY '123456';
-- 授权(ALL、SELECT、UPDATE、DELETE、INSERT、CREATE等)
-- 数据库.表名（可以使用通配符*）
GRANT SELECT ON companydb.account TO java2110;
-- 撤销权限
REVOKE SELECT ON companydb.account FROM java2110;
-- 删除用户
DROP USER java2110;
-- 刷新权限
FLUSH PRIVILEGES;

十九、视图

19.1 概念

是一个虚拟的表，作用与真实表相似。但是实际是查询出来的结果集。

作用：

  • 以此为基础进行查询，简化查询操作。
  • 还可以有限的做一些增删改操作。
19.2 基本操作

语法：

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

修改：

  • CREATE OR REPLACE VIEW 视图名 AS 查询语句
  • ALTER VIEW 视图名 AS 查询语句

删除：DROP VIEW 视图名

注意：视图删除不影响原表。

-- 将之前的行转列写为视图
CREATE VIEW V1 AS
SELECT s.s_name,  
MAX(CASE WHEN co_name = 'HTML' THEN score ELSE 0 END) AS HTML,
MAX(CASE WHEN co_name = 'SQL' THEN score ELSE 0 END) AS `SQL`,
MAX(CASE WHEN co_name = 'Java' THEN score ELSE 0 END) AS Java,
SUM(score) AS 总分
 FROM student s
INNER JOIN scores sc
ON s.s_id = sc.s_id
INNER JOIN courses c
ON c.co_id = sc.co_id
GROUP BY s_name;

-- 查询总分前3名
SELECT * FROM v1 ORDER BY 总分 DESC LIMIT 3;

注意：

  • 视图不会存储数据，原表发生变化，视图的内容也会发生变化。
  • 没有优化性能
  • 如果视图包含以下内容，则不能通过操作视图中内容影响原表（修改数据）
    • 聚合函数的结果
    • 使用了DISTINCT去重
    • GROUP BY
    • HAVING
    • UNION和UNION ALL

注意：不推荐对视图的数据进行修改。

二十、SQL语句的分类

  • DDL：DATA Definition LANGUAGE 数据定义语言，包含CREATE、ALTER、DROP等
  • DQL：Query 数据查询语言，包含SELECT、WHERE、GROUP BY、ORDER BY、LIMIT等
  • DCL：Control 数据控制语言，包含GRANT、REVOKE等
  • DML：Manipulation 数据操纵语言，包含INSERT、DELETE、UPDATE等
  • TPL：Transaction Process 事务处理语言，包含COMMIT、ROLLBACK等
标签：

相关文章