MySQL数据库详解

MySQL数据库

一、数据库

1.1 为什么要学习数据库

• 实现数据持久化
• 使用完整的管理系统统一管理，易于查询

二、数据库相关概念

2.1 DB

数据库（Database）：存储数据的仓库，它保存了一系列有组织的数据

2.2 DBMS

数据库管理系统（Database Management System）。数据库是通过DBMS创建和操作的容器

2.3 SQL

结构化查询语言（Structure Query Language）:专门用来与数据库通信的语言

2.3.1 SQL的优点

1、不是某个特定数据库供应商专有的语言，几乎所有DBMS都支持SQL

2、简单易学

3、虽然简单，但实际是一种强有力的语言，灵活使用其语言元素，可以进行非常复杂和高级的数据库操作

三、数据库存储数据的特点

1、将数据放入到表中，表再放入数据库中

2、一个数据库中可以有多张表，每个表都有一个名字，用来标识自己。表名具有唯一性

3、表具有一些特性，这些特性定义了数据在表中如何存储。

4、表是由列组成的，也称之字段，所有表都是由一个或多个列组成的。

5、表中的数据是按行存储的。

四、初始MySQL

4.1 MySQL简介

• MySQL数据库隶属于MySQL AB公司，总部位于瑞典，后背oracle收购

• 优点

  1、成本低：开放源代码，一般可以免费使用
  2、性能高：执行很快
  3、简单：很容易安装和使用
  

4.2 DBMS

DBMS分为两类：

1、基于共享文件系统的DBMS（Access）

2、基于客户机–服务器的DBMS
（MySQL、Oracle、Sqlserver）

五、MySQl常见命令

• 显示所有数据库

  show databases;
  

mysql数据库用于保存用户信息
information_schema保存原数据信息
performance_schema搜集性能参数

• 进入数据库

  use 数据库名
  

• 显示数据库中所有表

  show tables
  

• 在当前数据库中显示别的数据库中的表

  show tables from mysql;
  

• 显示当前所在的数据库

  select database();
  

• 创建表

  create table 表名(指定列);
  

• 查看表的结构

  desc 表名;
  

• 查看版本

方式一：登录客户端查询

select version();

方式二：没有登录客户端查询

mysql --version 或者 mysql --v

六、Mysql语法规范

1、不区分大小写。建议关键字大写，表名、列名小写

2、每条命令用分号结尾

3、每条命令根据需要，可以进行缩进或者换行

4、注释

单行注释：#注释文字
单行注释：-- 注释文字
多行注释：/* 注释文字 */

七、MySQL基础操作

7.1 基本查询

7.1.1 语法

select 查询列表 from 表名

1、查询列表可以是：表中的字段、常量值、表达式、函数

2、查询的结果是一个虚拟的表格

7.1.1.1 查询表中的单个字段

SELECT email FROM employees;

7.1.1.2 查询表中的多个字段

SELECT first_name, last_name FROM employees;

7.1.1.3 查询表中所有字段

SELECT * FROM employees;

7.1.1.4 查询常量值

SELECT 100;
SELECT 'join';

7.1.1.5 查询表达式

SELECT 10*10;

7.1.1.6 查询函数

SELECT VERSION();

7.2 起别名

7.2.1 AS关键字

使用AS关键字，后面加上要起的别名

语法：

SELECT 要查询的内容 FROM 表名 AS 别名;
SELECT 要查询的内容 AS 将要查询的内容起的别名 FROM employees;

示例：

SELECT e.email FROM `employees` AS e;
SELECT last_name AS x FROM employees;

7.2.2 省略AS关键字

起别名时直接将AS关键字省略

示例：

SELECT first_name m FROM employees;

注意：

起别名时字段之间不能有空格

• 优点

1、便于理解

2、如果要查询的字段有重名的情况，使用别名可以区分开来

7.3 去重

使用DISINCT关键字实现去重处理

语法：

SELECT DISTINCT 要查询的内容 FROM 表名;

示例：

SELECT DISTINCT department_id FROM employees;

7.4 “+”的作用

MySQL中的 “+” 仅仅只有一个功能：运算符

1、当两个操作数都为数值型，则做加法运算

2、当其中一方为字符型，试图将字符性数值转换成数值型.如果转换成功，则继续加法运算。如果转换失败，则将字符型数值转换成0

3、只要其中乙方为null，则结果肯定为null

7.5 concat实现连接

使用concat关键字可以实现字段的拼接

语法：

SELECT CONCAT(拼接字段1, 拼接字段2，...) AS 姓名 FROM employees;

示例：

SELECT CONCAT(last_name, first_name) AS 姓名 FROM employees;
SELECT CONCAT(last_name, first_name, '+', email) AS 姓名 FROM employees;

7.6 条件查询 WHERE

语法：

SELECT 查询列表 FROM 表名 WHERE 筛选条件;

7.6.1 条件表达式筛选

条件运算符：

>   大于
<   小于
!=或者<>    不等于（建议使用<>）
>=  大于等于
<=  小于等于

示例：

SELECT 
CONCAT(last_name,first_name) AS 姓名, salary 
FROM 
employees 
WHERE
salary > 10000;

SELECT * FROM employees WHERE department_id <> 90;

7.6.2 逻辑表达式筛选

逻辑运算符：推荐使用后一种

&&或者and   与（当两个条件都为true时，结果为true，反之为false）
||或者or    或（当两个条件都为false时，结果为false，反之为true）
！或者not   非（如果连接的条件本身为false，结果为true，反之为false）

示例：

SELECT 
CONCAT(last_name,first_name) AS 姓名, salary 
FROM 
employees 
WHERE 
salary >= 10000 AND salary <= 20000;

SELECT
* 
FROM 
employees 
WHERE
NOT(department_id>=90 AND department_id <= 110) OR salary > 15000;

7.6.3 模糊查询

7.6.3.1 like关键字

“ % ”通配符：匹配多个任意字符（包含0个）

“ _ ”通配符：匹配单个任意字符

示例：

SELECT * FROM employees WHERE last_name LIKE '%a%'

SELECT last_name, salary FROM employees WHERE last_name LIKE '__n_l%'

SELECT last_name FROM employees WHERE last_name LIKE '_\_%'

“ \ ” 转义字符

示例：

SELECT last_name FROM employees WHERE last_name LIKE '_\_n_l%';

7.6.3.1.1 指定转义符号ESCAPE关键字

示例：

SELECT last_name FROM employees WHERE last_name LIKE '_$_%' ESCAPE '$';

7.6.3.2 between and关键字

between and在…之间

示例：

SELECT * FROM employees WHERE manager_id BETWEEN 100 AND 120;

特点：

1、使用between and 可以提高语句的简洁度

2、包含临界值

3、临界值不能颠倒顺序

7.6.3.3 in关键字

IN关键字判断某字段的值是否属于IN列表中的某一项

示例：

SELECT last_name, job_id FROM employees WHERE job_id IN('IT_PROT', 'AD_VP', 'AD_PRES');

特点：

1、使用IN提高语言简洁度

2、IN列表的值类型必须一致或兼容

3、

7.6.3.4 is null关键字

查询为不存在的字段

示例：

SELECT last_name, commission_pct FROM employees WHERE commission_pct IS NULL;

注意：

1、=或<>不能用于判断null值

2、is null或is not null 可以判断null值

7.6.3.5 补充：安全等于

• 安全等于：<=> 可以判断null值，还可以判断普通类型的值

示例：

SELECT last_name, commission_pct FROM employees WHERE commission_pct <=> NULL;

SELECT last_name, salary FROM employees WHERE salary <=> 12000;

八、排序查询

使用ORDER BY关键字可以对筛选的结果进行排序。

语法：

SELECT 查询列表 FROM 表 WHERE 筛选条件 ORDER BY 排序列表 ASC|DESC;

示例：

SELECT
* 
FROM 
employees 
ORDER BY 
salary ASC;

SELECT
* 
FROM 
employees 
WHERE 
department_id >= 90
ORDER BY 
hiredate ASC;

SELECT 
*, salary * 12 AS 年薪 
FROM 
employees 
ORDER BY s
alary * 12 DESC;
  
SELECT 
LENGTH(last_name) AS 字节长度, last_name, first_name 
FROM 
employees 
ORDER BY 
字节长度	DESC;  
  
SELECT
* 
FROM 
employees 
ORDER BY s
alary DESC , employee_id ASC;

特点：

1、ASC为升序排列，DESC为降序排列。如果不写，默认为升序

2、ORDER BY子句中可以支持单个字段、多个字段、表达式、函数、别名

3、ORDER BY子句一般放在查询语句的最后面。LIMIT子句除外

九、常见函数

9.1 函数介绍

概念：将一组逻辑语句封装在方法体中，对外暴露方法名

• 好处：

  1、隐蔽了实现细节

  2、提高代码可用性

• 调用

  SELECT 函数名（实参列表） [FROM 表]

• 特点：

  1、函数名
  2、函数功能

• 分类：

1、单行函数

如concat、length、isnull等

2、分组函数

功能：做统计使用，又称为统计函数、聚合函数、组

9.2 单行函数

9.2.1 字符函数

9.2.1.1 length函数

作用：

统计字节个数

示例：

SELECT LENGTH('join')

注意：

utf8编码时一个中文占3个字节，gbk编码时一个中文占2个字节
英文一个字母占一个字节

9.2.1.2 concat函数

作用：

拼接字符产

示例：

SELECT CONCAT(first_name,last_name) AS xm FROM employees;

9.2.1.3 upper/lower函数

作用：

转换为大写或是小写

示例：

SELECT UPPER(last_name) FROM employees;

9.2.1.4 substr函数

作用：

截取字符串

示例：

SELECT SUBSTR('李莫愁爱上了陆展元', 7) AS out_put;

SELECT SUBSTR('李莫愁爱上了陆展元', 1, 3) AS out_put;   #截取从指定索引处指定字符长度的字符

注意：

索引是从1开始

9.2.1.5 instr函数

作用：

判段指定字符在字符串中的索引位置

示例：

SELECT INSTR('杨不悔爱上了殷六侠','殷六侠') AS out_put;

注意：

当指定字符出现多次时，返回第一次出现的索引。如果找不到则返回零

9.2.1.6 trim函数

作用：

去掉字符串两端的空格及其他字符

示例：

SELECT TRIM('  lisi    ') AS out_put

SELECT TRIM('a' FROM 'aaa张三丰aaa')

9.2.1.6 lpad/rpad函数

作用：

用指定的字符实现左右填充

示例：

SELECT LPAD('张三',10,'*');

SELECT RPAD('李四',2,'*');

9.2.2 数学函数

9.2.2.1 四舍五入round函数

作用：

四舍五入

示例：

SELECT ROUND(1.02); # 1

SELECT ROUND(-1.55);    # -2

SELECT ROUND(1.567,2);  # 1.57 第二个参数表示小数点后位数

9.2.2.2 ceil函数

作用：

向上取整

示例：

SELECT CEIL(1.002); # 2

SELECT CEIL(-1.02); # -1

9.2.2.3 floor函数

作用：

向下取整

示例：

SELECT FLOOR(1.02); # 1

SELECT FLOOR(-1.02);    # -2

9.2.2.4 truncate函数

作用：

截断

示例：

SELECT TRUNCATE(1.65,1);    # 第二个参数表示保留的数位数

9.2.2.5 mod函数

作用：

取余

示例：

SELECT MOD(10,3);

9.2.3 日期函数

9.2.3.1 now函数

作用：

返回当前系统日期+时间

示例：

SELECT NOW();

9.2.3.2 curdate函数

作用：

返回当前系统日期，不包含时间

示例：

SELECT CURDATE();

9.2.3.3 curtime函数

作用：

返回当前的时间，不包含日期

示例：

SELECT CURTIME();

9.2.3.4 monthname函数

作用：

返回英文的月份

示例：

SELECT MONTHNAME(NOW());

9.2.3.5 str_to_date函数

作用：

将日期格式的字符转换成指定格式的日期

示例：

SELECT STR_TO_DATE('9-13-2020','%m-%d-%Y');

9.2.3.6 date_format函数

作用：

将日期转换成字符

示例：

SELECT DATE_FORMAT(NOW(),'%Y-%m-%d %H:%c:%s');

9.2.4 流程控制函数（补充）

9.2.4.1 if函数：if else

作用：

判断IF条件值，如果条件满足，输出第二个参数，否则输出第三个参数

示例：

SELECT IF(10>5,'da','xiao');

SELECT last_name, IF(commission_pct IS NULL,'没奖金','有奖金') FROM employees;

9.2.4.2 case函数

1、实现switch case效果

语法：

case 要判断的子段或表达式
when 常量1 then 要显示的值或语句1；
when 常量2 then 要显示的值或语句2;
...
else 要显示的值或语句n;
end

示例：

SELECT salary, department_id, 
CASE department_id 
	WHEN 30 THEN
      salary*1.1
	WHEN 40 THEN
		salary * 1.2
	WHEN 50 THEN 
	   salary*1.3
	 ELSE salary
END AS new_salary FROM employees;

2、多重IF效果

语法：

case 
when 条件1 then 要显示的值1或语句1;
when 条件2 then 要显示的值2或语句2;
...
else 要显示的值n或语句n;
end

示例：

SELECT salary,
CASE 
WHEN salary > 20000 THEN 'A'
WHEN salary > 15000 THEN 'B'
WHEN salary > 10000 THEN 'C'
ELSE 'D'
END AS New_salary FROM employees;

9.2.3 分组函数

9.2.3.1 sum函数

作用：

对指定字段求和

示例：

SELECT SUM(salary) FROM employees;

注意：

参数类型只能是数值型
忽略NULL值
可以和distinct搭配，实现去重处理

9.2.3.2 avg函数

作用：

对指定字段求平均值

示例：

SELECT AVG(salary) FROM employees;

注意：

参数类型只能是数值型
忽略NULL值
可以和distinct搭配，实现去重处理

9.2.3.3 min/max函数

作用：

求指定字段的最小值/最大值

示例：

SELECT MAX(salary) FROM employees;
SELECT MIN(salary) FROM employees;

注意：

参数类型可以是数值型也可以是字符型
忽略NULL值
可以和distinct搭配，实现去重处理

9.2.3.5 count函数

作用：

求指定字段的个数

示例：

SELECT 
COUNT(salary) 
FROM 
employees;

SELECT 
COUNT(*) 
FROM 
employees; # 统计所有非NULL值的行，只要一行中任意字段不为空，则统计

注意：

参数类型可以是数值型也可以是字符型
忽略NULL值
可以和distinct搭配，实现去重处理

9.2.4 分组查询

语法：

SELECT 分组函数, 列（要求出现在group by之后）
FROM 表
[WHERE 筛选条件]
GROUP BY 分组的列表
[ORDER BY 子句]

示例：

SELECT 
MAX(salary), job_id 
FROM 
employees 
GROUP BY 
job_id;

SELECT COUNT(*), location_id FROM departments GROUP BY location_id;

9.2.4.1 添加筛选条件

示例：

SELECT 
AVG(salary), 
department_id 
FROM 
employees 
WHERE 
email LIKE '%a%' 
GROUP BY 
department_id;

SELECT 
MAX(salary), manager_id 
FROM 
employees 
WHERE 
commission_pct is NOT NULL 
GROUP BY 
manager_id;

9.2.4.2 添加分组后的查询

使用HAVEING关键字可以实现分组后的查询

示例：

SELECT 
COUNT(*), department_id 
FROM 
employees 
GROUP BY 
department_id 
HAVING 
COUNT(*) > 2;

SELECT 
MAX(salary) AS msalary, job_id 
FROM 
employees 
WHERE 
commission_pct IS NOT NULL 
GROUP BY j
ob_id HAVING msalary > 12000;

SELECT 
MIN(salary), manager_id 
FROM employees 
WHERE 
manager_id > 102 
GROUP BY 
manager_id 
HAVING
MIN(salary) > 5000;

特点：

1、分组查询中的筛选条件分两类

2、分组函数做条件肯定是放在having子句中

3、能用分组前筛选的，就优先考虑使用分组前筛选

4、group by子句支持单个字段分组，多个字段分组（多个字段之间用逗号隔开，没有顺序要求），表达式函数（用的较少）

5、也可以添加排序，（排序放在整个分组查询的最后）

9.2.4.3 按多个字段分组

示例：

SELECT 
AVG(salary), department_id, job_id 
FROM 
employees 
GROUP BY 
job_id, department_id;

9.2.4.4 分组排序

示例：

SELECT 
AVG(salary), department_id, job_id 
FROM 
employees 
GROUP BY 
job_id, department_id 
ORDER BY 
AVG(salary) DESC;

十、连接查询

又称为多表查询，当查询的字段来自多个表时，就会用到连接查询

笛卡尔乘积现象：表1有m行，表2有n行，结果有m*n行

发生原因：没有有效的连接条件

如何避免：添加有效的连接条件

10.1 SQL92标准

10.1.1 按功能分类：

10.1.1.1 内连接

10.1.1.1.1 等值连接

示例1：查询员工名和对应的部门名

SELECT 
last_name, department_name 
FROM 
employees, departments 
WHERE 
employees.department_id = departments.department_id;

示例2：查询员工名、工种号、工种名

SELECT 
last_name, employees.job_id, job_title 
FROM 
employees, jobs WHERE employees.job_id = jobs.job_id;

注意：

当出现歧义时，可以使用别名避免歧义。
如果为表起了别名，则查询的字段就不能使用原来的表名去限定

示例3：查询有奖金的员工名、部门名

SELECT 
last_name, departments.department_name, commission_pct 
FROM 
employees, departments 
WHERE 
employees.department_id =  departments.department_id AND commission_pct IS NOT NULL;

示例4：查询城市名中第二个字母为o的部门名和城市名

SELECT
department_name, city 
FROM 
departments, locations 
WHERE 
departments.location_id = locations.location_id AND locations.city LIKE '_o%';

示例5：查询每个城市的部门个数

SELECT 
city, COUNT(*) 
FROM 
locations, departments 
WHERE
locations.location_id = departments.location_id
GROUP BY 
city;

示例6：查询有奖金的每个部门的部门名和部门的领导编号和该部门的最低工资

SELECT
department_name, departments.manager_id, MIN(salary) 
FROM 
departments, employees 
WHERE 
employees.department_id = departments.department_id AND commission_pct IS NOT NULL 
GROUP BY 
department_name, departments.manager_id; 

示例7：查询每个工种的工种名和员工的个数，并且按员工的个数降序

SELECT
job_title, COUNT(*) 
FROM 
jobs, employees 
WHERE
jobs.job_id = employees.job_id GROUP BY job_title ORDER BY COUNT(*) DESC;

示例8：三表连接查询员工名、部门名和所在的城市

SELECT
last_name, department_name, city 
FROM 
employees, departments, locations 
WHERE
employees.department_id = departments.department_id AND locations.location_id = departments.location_id;

添加筛选
SELECT
last_name, department_name, city 
FROM 
employees, departments, locations 
WHERE
employees.department_id = departments.department_id AND locations.location_id = departments.location_id AND city LIKE 's%';

添加排序
SELECT
last_name, department_name, city 
FROM 
employees, departments, locations 
WHERE 
employees.department_id = departments.department_id AND locations.location_id = departments.location_id AND city LIKE 's%' 
ORDER BY 
department_name DESC;

总结：

1、多表等值连接的结果为多表的交集部分

2、n表连接，至少需要n-1个连接条件

3、多表的顺序没有要求

4、一般需要为表起别名

5、可以搭配前面介绍的所有子句使用，比如排序、分组、筛选

10.1.1.1.2 非等值连接

示例1：查询员工的工资和工资级别

SELECT 
salary, grade_level 
FROM 
employees AS e, job_grades AS j 
WHERE 
salary 
BETWEEN 
j.lowest_sal AND j.highest_sal;

示例2：查询员工的工资和工资级别为A的

SELECT 
salary, grade_level 
FROM 
employees AS e, job_grades AS j  
WHERE 
salary 
BETWEEN
j.lowest_sal AND j.highest_sal AND j.grade_level = "A";

10.1.1.1.3 自连接

含义：在同一张表中进行两次查询

示例：

SELECT 
e.employee_id, e.last_name, m.employee_id, m.last_name 
FROM 
employees AS e, employees AS m 
WHERE
e.employee_id = m.employee_id;

10.2 sql99标准[推荐]

语法：

SELECT 查询列表
FROM 表1 别名 [连接类型]
JOIN 表2 别名
ON 连接条件
[WHERE 筛选条件]
[GROUP BY 分组]
[HAVING 筛选条件]
[ORDER BY 排序列表]

10.2.1 内连接

关键字：INNER

语法：

SELECT  查询列表
FROM 表1 别名
INNER JOIN 表2 别名
ON 连接条件;

10.2.1.1 等值连接

示例1：查询员工名、部门名

SELECT
	last_name,
	department_name 
FROM
	employees AS e
	INNER JOIN departments AS d ON e.department_id = d.department_id;

示例2：查询名字中包含e的员工名和工种名

SELECT
	last_name,
	job_title 
FROM
	employees AS e
	INNER JOIN jobs AS d ON e.job_id = d.job_id 
WHERE
	e.last_name LIKE '%e%';

示例3：查询部门个数 > 3的城市名和部门个数

SELECT
	city,
	COUNT(*) 
FROM
	locations AS l
	INNER JOIN departments AS d ON l.location_id = d.location_id 
GROUP BY
	city 
HAVING
	COUNT(*) > 3;

示例4：查询哪个部门的员工个数 > 3的部门名和员工个数，并按个数降序

    SELECT
	COUNT(*),
	department_name 
FROM
	employees AS e
	INNER JOIN departments AS d ON e.department_id = d.department_id 
GROUP BY
	department_name 
HAVING
	COUNT(*) > 3 
ORDER BY
	COUNT(*) DESC;

示例5：三表连接查询员工名、部门名、工种名、并按部门名降序

    SELECT
	last_name,
	department_name,
	job_title 
FROM
	employees AS e
	INNER JOIN departments AS d ON e.department_id = d.department_id
	INNER JOIN jobs AS j ON e.job_id = j.job_id 
ORDER BY
	department_name DESC;

特点：

1、添加排序、分组、筛选

2、INNER可以省略

3、筛选条件放在WHERE后面，连接条件放在ON后面，提高分离性，便于阅读

4、INNER JOIN连接和SQL92语法中的等值连接效果是一样的，都是查询交集部分

10.2.1.2 非等值连接

示例1：查询工资级别

SELECT
	last_name,
	grade_level 
FROM
	employees AS e
	INNER JOIN job_grades AS g ON salary BETWEEN g.lowest_sal 
	AND g.highest_sal;

示例2：查询每个工资级别大于20的的个数，并且按工资级别进行排序

SELECT
	COUNT(*) grade_level 
FROM
	employees AS e
	INNER JOIN job_grades AS g ON salary BETWEEN g.lowest_sal 
	AND g.highest_sal 
GROUP BY
	grade_level 
HAVING
	COUNT(*) > 20 
ORDER BY
	grade_level DESC;

10.2.1.3 自连接

示例1：查询员工的名字、上级的名字

SELECT
	e.last_name,
	m.last_name
FROM
	employees AS e
	INNER JOIN employees AS m 
ON
	e.manager_id = m.employee_id;

示例2：查询姓名中包含字符k的员工姓名、上级的名字

    SELECT
	e.last_name,
	m.last_name
FROM
	employees AS e
	INNER JOIN employees AS m 
ON
	e.manager_id = m.employee_id
WHERE e.last_name
LIKE '%k%';

10.2.2 外连接

应用场景：

1、用于查询一个表中有，另一个表中没有的记录

特点：

1、外连接的查询结果为主表中的所有记录。如果从表中有和它匹配的，则显示匹配的值，如果从表中没有和它匹配的，则显示null
2、外连接的查询结果 = 内连接的结果 + 主表中有而从表中没有的记录
3、左外连接中，LEFT左边的是主表，右外连接中，RIGHT JOIN右边的是主表
4、左外和右外交换两个表的顺序，可以实现同样的效果

10.2.2.1 左外连接

关键字：LEFT [OUTER]

示例1：查询男朋友没有在男神表中的女神名

SELECT
	b.NAME,
	bo.* 
FROM
	beauty b
	LEFT OUTER JOIN boys bo ON b.boyfriend_id = bo.id;
	
	
SELECT
	b.NAME,
	bo.* 
FROM
	boys bo
	LEFT OUTER JOIN beauty b ON b.boyfriend_id = bo.id;

示例2：查询哪个部门没有员工

SELECT
	d.*,
	e.employee_id 
FROM
	departments d
	LEFT OUTER JOIN employees e ON d.department_id = e.department_id;

10.2.2.2 右外连接

关键字：RIGHT [OUTER]

示例1：查询哪个部门没有员工

SELECT
	d.*,
	e.employee_id 
FROM
	employees e
	RIGHT OUTER JOIN departments d ON d.department_id = e.department_id;

10.2.2.3 全外连接

关键字：FULL [OUTER]

10.2.3 交叉连接

关键字：CROSS

交叉连接能实现笛卡尔乘积现象

示例：

SELECT
	b.*,
	bo.* 
FROM
	beauty b
	CROSS JOIN boys bo;

10.3 练习

示例1：查询编号>3的女神的男朋友信息，如果有则列出详情，如果没有，用null填充

SELECT
	b.*,
	bo.* 
FROM
	beauty b
	LEFT OUTER JOIN boys bo
ON b.boyfriend_id = bo.id
WHERE b.id > 3;

示例2：查询哪个城市没有部门

左外连接实现：

SELECT
	city 
FROM
	locations l
	LEFT OUTER JOIN departments d ON l.location_id = d.location_id 
WHERE
	l.state_province IS NULL;

右外连接实现：

SELECT
	city 
FROM
	departments d
	RIGHT OUTER JOIN locations l ON d.location_id = l.location_id 
WHERE
	d.department_id IS NULL;

示例3：查询部门名为SAL或IT的员工信息

SELECT
	last_name,
	department_name 
FROM
	departments d
	LEFT OUTER JOIN employees e ON d.department_id = e.department_id 
WHERE
	department_name IN ( 'SAL', 'IT' );

十一、 子查询

• 含义

  出现在其他语句中的SELECT语句，称为子查询或者内查询。外部的查询语句，称为主查询或者外查询
  

• 特点

  1、子查询都会放在小括号内
    
  2、子查询一般放在条件的内侧
    
  3、标量子查询，一般搭配着单行操作符使用（> < >= <= = <>）
    
  4、列子查询，一般搭配着多行操作符使用(in any/some all)
  

11.1 分类

11.1.1 按子查询出现的位置分类

• 1、SELECT后面

SELECT后面只能放标量子查询

• 2、FROM后面

FROM后面支持表子查询

• 3、WHERE或HAVING后面

WHERE或HAVING后面支持标量子查询（单行），列子查询（多行），行子查询

• 4、EXISTS后面（相关子查询）

EXISTS后面支持表子查询

11.1.2 按结果集的行列数分类

1、标量子查询（结果集只有一行一列）

2、列子查询（结果集只有一列多行）

3、行子查询（结果集只有一行多列）

4、表子查询（结果集一般为多行多列）

11.2 WHERE或HAVING后面子查询

11.2.1 标量子查询

示例1：谁的工资比Abel高

SELECT
	* 
FROM
	employees 
WHERE
	salary > ( SELECT salary FROM employees WHERE last_name = 'Abel' );

示例2：返回job_id与141号员工相同，salary比143号员工多的员工姓名，job_id和工资

SELECT
	last_name,
	job_id,
	salary 
FROM
	employees 
WHERE
	job_id = ( SELECT job_id FROM employees WHERE employee_id = 141 ) 
	AND salary > ( SELECT salary FROM employees WHERE employee_id = 143 );

示例3：返回公司工资最少的员工的last_name, job_id, salary

SELECT
	last_name,
	job_id,
	salary 
FROM
	employees 
WHERE
	salary = ( SELECT MIN( salary ) FROM employees );

示例4：查询最低工资大于50号部门最低工资部门的id和其最低工资

SELECT
	department_id,
	MIN( salary ) 
FROM
	employees 
GROUP BY
	department_id 
HAVING
	MIN( salary ) > ( SELECT MIN( salary ) FROM employees WHERE department_id = 50 );

11.2.2 WHERE后面列子查询（多行子查询）

• 返回多行
• 使用多行比较操作符

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-mQnHTbYw-1620400269534)(https://i.loli.net/2021/04/23/ARg2iLIOr9o8qBt.png)]

示例：返回location_id和是1400或1700的部门中的所有的员工姓名

SELECT
	last_name 
FROM
	employees e 
WHERE
	department_id IN (
	SELECT DISTINCT
		department_id 
	FROM
		departments 
	WHERE
	location_id IN ( 1400, 1700 ));

示例2：返回其他工种中比job_id为’IT_PROG’工种任一工资低的员工的员工号、姓名、job_id以及salary

SELECT
	employee_id,
	last_name,
	job_id,
	salary 
FROM
	employees 
WHERE
	salary < ANY ( SELECT DISTINCT salary FROM employees WHERE job_id = 'IT_PROG' ) 
	AND job_id <> 'IT_PROG';

示例3：返回其他工种中比job_id为’IT_PROG’工种所有工资低的员工的员工号、姓名、job_id以及salary

SELECT
	employee_id,
	last_name,
	job_id,
	salary 
FROM
	employees 
WHERE
	salary < ALL ( SELECT DISTINCT salary FROM employees WHERE job_id = 'IT_PROG' ) 
	AND job_id <> 'IT_PROG';

11.2.3 WHERE后面的行子查询（结果集一行多列或多行多列）

示例1：查询员工编号最小并且工资最高的员工信息

SELECT
	* 
FROM
	employees 
WHERE
	employee_id = ( SELECT MIN( employee_id ) FROM employees ) 
	AND salary = ( SELECT MAX( salary ) FROM employees );

11.3 SELECT后子查询

示例1：查询每个部门的员工个数

SELECT
	d.*, ( SELECT COUNT(*) FROM employees e WHERE e.department_id = d.department_id ) num 
FROM
	departments d;

示例2：查询员工号为102的部门名

SELECT
( 
SELECT 
department_name 
FROM 
departments d 
INNER JOIN 
employees e 
ON 
d.department_id = e.department_id WHERE e.employee_id = 102 ) 部门名;

11.4 FROM后子查询

示例1：查询每个部门的平均工资的工资等级

SELECT
	e.*, grade_level 
FROM
	( SELECT department_id, AVG( salary ) ag FROM employees GROUP BY department_id ) AS e
	INNER JOIN job_grades g ON e.ag BETWEEN lowest_sal 
	AND highest_sal;

11.5 EXISTS后子查询(相关子查询)

语法：EXISTS(完整的查询语句)，结果为1或0。先查询外层查询，后查询内层查询

示例1：查询员工名和部门名

SELECT
	department_name 
FROM
	departments d 
WHERE
	EXISTS (
	SELECT
		department_id 
	FROM
		employees e 
WHERE
	d.department_id = e.department_id);

示例2：查询没有女朋友的男神信息

SELECT
	bo.* 
FROM
	boys bo 
WHERE
	NOT EXISTS ( SELECT boyfriend_id FROM beauty b WHERE bo.id = b.boyfriend_id );

11.6 练习

示例1：查询和Zlotkey相同部门的员工姓名和工资

SELECT
	last_name,
	salary 
FROM
	employees 
WHERE
	department_id = ( SELECT department_id FROM employees WHERE last_name = 'Zlotkey' );

示例2：查询工资比公司平均高的员工的员工号，姓名和工资

SELECT
	employee_id,
	last_name,
	salary 
FROM
	employees 
WHERE
	salary > ( SELECT AVG( salary ) FROM employees WHERE salary );

示例3：查询各部门中工资比本部门平均工资高的员工的员工号，姓名和工资

SELECT
	employee_id,
	last_name,
	salary, 
	e.department_id 
FROM
	employees e
	INNER JOIN ( SELECT department_id, AVG( salary ) ag FROM employees GROUP BY department_id ) AS a ON e.department_id = a.department_id 
WHERE
	salary > a.ag;

示例4：查询和姓名中包含字母u的员工在相同部门的员工的员工号和姓名

SELECT
	last_name,
	employee_id 
FROM
	employees 
WHERE
	department_id IN( SELECT DISTINCT department_id FROM employees WHERE last_name LIKE '%u%' );

示例5：查询在部门的location_id为1700的部门工作的员工的员工号

方法一：
SELECT
	employee_id 
FROM
	employees e
	INNER JOIN ( SELECT department_id FROM departments WHERE location_id = 1700 ) d 
WHERE
	d.department_id = e.department_id;
	
方法二：	
SELECT
	employee_id 
FROM
	employees 
WHERE
	department_id = ANY (
	SELECT
		department_id 
	FROM
		departments 
	WHERE
	location_id = 1700)    	

示例6：查询管理者是King的员工的姓名和工资

SELECT
	last_name,
	salary 
FROM
	employees AS e
WHERE 
    manager_id IN (SELECT employee_id FROM employees WHERE last_name = 'K_ing' );

示例7：查询工资最高的员工的姓名，要求 first_name 和 last_name 显示为一列，列名为姓名

SELECT
	CONCAT( first_name, last_name ) "姓·名" 
FROM
	employees 
WHERE
	salary = ( SELECT MAX( salary ) FROM employees );

示例8：查询工资最低的员工信息

示例9：查询平均工资最低的部门信息

示例10：查询平均工资最低的部门信息和该部门的平均工资

示例11：查询平均工资最高的job信息

示例12：查询平均工资高于公司平均工资的部门有哪些

示例13：查询出公司中所有manager的详细信息

示例14：各个部门中，最高工资中最低的那个部门的最低工资是多少

示例15：查询平均工资最高的部门的manager的详细信息：last_name, department_id, email, salary

十二、 分页查询

关键字：LIMIT OFFSET,SIZE;

OFFSET 要显示条目的起始索引（起始索引从0开始）

示例1：查询前5条员工信息

SELECT * FROM employees LIMIT 0,5;

示例2：查询第11条到第25条

SELECT * FROM employees LIMIT 10, 15;

示例3：有奖金的员工信息，并且工资较高的前10名

SELECT
	* 
FROM
	employees 
WHERE
	commission_pct IS NOT NULL 
ORDER BY
	salary DESC 
	LIMIT 10;

十三、联合查询

关键字：UNION 合并：将多条查询语句合并成一个结果

应用场景：要查询的结果来自多个表，且多个表没有直接的连接关系，但查询的信息一致时

语法：

查询语句1
UNION
查询语句2
...;

示例1：查询email中带有a且department_id大于90的员工信息

SELECT
	* 
FROM
	employees 
WHERE
	email LIKE '%a%' UNION
SELECT
	* 
FROM
	employees 
WHERE
	department_id > 90;

注意事项：

1、要求多条查询语句的查询列数是一致的

2、要求多条查询语句的查询的每一列的类型和顺序最好是一致的

3、UNION默认会去重，UNION ALL可以包含重复项

十四、DML语言

数据操作语言（增、删、改）

14.1 插入语句

14.1.1 方法一插入语句

语法：

INSERT INTO 表名 (列名,...)
VALUES (值,...)

要求：

1、插入值的类型要与列的类型一致或兼容

2、不可以为NULL的列必须插入值

3、列的顺序可以调换，但值必须一一对应

4、列数和值的个数必须一致

5、可以省略列名，默认所有列，而且列的顺序和表中列的顺序是一致的

6、方式一支持插入多行，方式二不支持

7、方式一支持子查询，方式二不支持

示例：

INSERT INTO beauty ( id, NAME, sex, borndate, phone, photo, boyfriend_id )
VALUES
	( 13, '唐艺昕', '女', '1990-4-23', '189888888', NULL, 2 );

示例2：可以为NULL的列插入值

INSERT INTO beauty ( id, NAME, sex, borndate, phone, boyfriend_id )
VALUES ( 14, '金星', '女', '1990-4-23', '18359888888', 9 );

14.1.1 方法二插入语句

语法：

INSERT INTO 表名
SET ；列名=值, 列名=值...

示例：

INSERT INTO beauty SET id=19, `name`= '刘涛', phone='110';

14.2 修改语句

14.2.1 修改单表的记录

语法：

UPDATE 表名
SET 列=新值, 列=新值, ...
WHERE 筛选条件;

示例1：修改beauty表中姓唐的人的电话为138

UPDATE 
beauty 
SET phone = '138' 
WHERE
`name` LIKE '唐%';

示例2：修改boys表中id号为2的名称为张飞，魅力值为10

UPDATE boys 
SET boyName = '张飞',
userCP = 10 
WHERE
id = 2;

14.2.2 修改多表的记录

SQL92语法：

UPDATE 
表1 别名, 表2 别名
SET 列=值, ...
WHERE 
连接条件
AND
筛选条件

SQL99语法：

UPDATE 
表1 别名
INNER | LEFT | RIGHT JOIN 表2 别名
ON 
连接条件
SET
列=值,...
WHERE
筛选条件;

示例1：修改张无忌的女朋友的手机号为114

UPDATE 
boys bo
INNER JOIN 
beauty b ON bo.id = b.boyfriend_id 
SET 
phone = '114' 
WHERE
bo.boyName = '张无忌';

示例2：修改没有男朋友的女神的男朋友编号都为2号

UPDATE 
boys bo
RIGHT JOIN 
beauty b ON bo.id = b.boyfriend_id 
SET 
b.boyfriend_id = 2 
WHERE
b.boyfriend_id IS NULL;

14.3 删除语句

14.3.1方式一 DELETE 关键字

14.3.1.1 单表的删除

语法：

DELETE FROM 表名 WHERE 筛选条件;

示例1：删除手机号以9结尾的女神信息

DELETE 
FROM
	beauty 
WHERE
phone LIKE '%9';

14.3.1.2 多表的删除

SQL92语法：

DELETE 表1的别名, 表2的别名
FROM
表1 别名, 表2 别名
WHERE
连接条件
AND
筛选条件;

SQL99语法：

DELETE 表1的别名, 表2的别名
FROM
表1 别名
INNER | LEFT | RIGHT JOIN
表2 别名
ON
连接条件
WHERE
筛选条件;

示例1：删除张无忌女朋友的信息

DELETE b 
FROM
	beauty b
	INNER JOIN boys bo ON b.boyfriend_id = bo.id 
WHERE
	bo.boyName = '张无忌';

示例2：删除黄晓明的信息和他女朋友的信息

DELETE 
b, bo 
FROM
	beauty b
	INNER JOIN boys bo ON b.boyfriend_id = bo.id 
WHERE
	bo.boyName = '黄晓明';

14.3.2 方式二 TRUNCATE 关键字

语法：

TRUNCATE TABLE 表名;

示例1：将魅力值大于100的删除

TRUNCATE TABLE boys;

14.3.3 删除方式区别

1、DELETE可以加WHERE条件，TRUNCATE不能加

2、TRUNCATE效率较高

3、假如要删除的表中有自增长列，如果用DELETE删除后，在插入数据，自增长列的值会从断店开始，而TRUNCATE删除后，在插入数据，自增长列的值从1开始

4、TRUNCATE删除没有返回值，DELETE删除有返回值

5、TRUNCATE删除不能回滚，DELETE删除可以回滚

十五、DDL语言

数据定义语言

库和表的管理

15.1 库的管理

创建、修改、删除

15.1.1 创建库

关键字：CREATE DATABASE

示例1：创建Book库

CREATE DATABASE Book;

示例2：如果数据库不存在则创建，反之不创建

CREATE DATABASE IF NOT EXISTS Book;

注意：

创建完成后再次创建该库时，则会报错

15.1.2 修改库

关键字：ALTER DATABASE

示例1：修改库的字符集

ALTER DATABASE Book CHARACTER SET GBK;

ALTER DATABASE Book CHARACTER SET UTF8;

15.1.3 删除库

关键字：DROP DATABASE

示例1：删除Book库

DROP DATABASE book;

15.2 表的管理

创建、修改、删除

15.2.1 创建表

关键字：CREATE TABLE

语法：

CREATE TABLE 表名 ( 
                    列名 列的类型[(长度) 约束], 
                    列名 列的类型[(长度) 约束],
                    ...
                    列名 列的类型[(长度) 约束]
                   );

示例1：创建表

CREATE TABLE book ( 
    id INT, # 编号
	bNAME VARCHAR ( 20 ), # 图书名
	price DOUBLE, # 价格
	asthorId INT # 作者编号
);

示例2：创建author表

CREATE TABLE author ( id INT, au_name VARCHAR ( 20 ), nation VARCHAR ( 10 ) );

15.2.2 修改表

示例1：修改表名

ALTER TABLE book CHANGE COLUMN asthorId au_Id INT;

示例2：修改列的类型

ALTER TABLE book MODIFY COLUMN au_Id INT;

示例3：添加列

ALTER TABLE author ADD COLUMN annual DOUBLE;

# 在指定列后面插入新的一列    
ALTER TABLE author ADD  COLUMN  nation_id INT AFTER au_name;

#插入到首列
ALTER TABLE author ADD  COLUMN  au_name_id INT FIRST;

示例4：删除列

ALTER TABLE author DROP COLUMN annual;

示例5：修改表名

ALTER TABLE author RENAME TO book_author;

15.2.3 删除表

关键字：DROP TABLE

示例：删除表

DROP TABLE book_author;

15.2.4 表的复制

15.2.4.1 仅仅复制表的结构

语法：

CREATE TABLE 复制的表名 LIKE 原表名;

示例1：

CREATE TABLE copy LIKE author;

15.2.4.2 复制表的结构+数据

语法：

CREATE TABLE 复制的表名 SELECT * FROM 原表名;

示例1：

CREATE TABLE copy2 SELECT * FROM author;

15.2.4.2 复制部分数据

示例1：复制部分数据

CREATE TABLE copy3 SELECT * FROM author WHERE nation = '中国';

十六、数据类型

16.1 数值型

16.1.1 整形

tinyint  1个字节
smallint    2个字节
mediumint   3个字节
int/integer 4个字节
bigint  8个字节

特点：

1、如果不设置无符号还是有符号，默认是有符号，如果想设置无符号，需要添加unsigned关键字

2、如果插入的数值超出了整形的范围，会报out of range异常，并插入临界值

3、如果不设置长度，会有默认的长度。长度代表了显示的最大长度，如果不够会用0在左边填充，但必须搭配zerofill使用

16.1.2 小数

16.1.2.1 定点型

DEC(M,D) m+2个字节

16.1.2.2 浮点型

FLOAT(M, D)   4个字节
DOUBLE(M, D)  8个字节

特点：

1、M代表整数部位外加小数部位

2、D代表小数范围，如果超过范围，则插入临界值

3、M和D都可以省略，如果是DECIMAL，则M默认为10，D默认为0

4、如果是FLOAT和DOUBLE，则会根据插入数值的精度来决定长度

5、定点型精度较高，如果要求插入数值的精度较高则考虑使用

选用原则：

所选择的类型越简单越好，能保存数值的类型越小越好

16.2 字符型

16.2.1 较短的文本

CHAR(M) M为0~255之间的整数    
VARCHAAR(M) M为0~65535之间的整数       

特点：

1、CHAR代表的固定长度的字符，VARCHAR代表可变长度的字符

2、CHAR比较耗费空间，VARCHAR比较节省空间

3、CHAR效率较高，VARCHAR效率较低

日期型

DATE 4个字节
DATETIME    8个字节
TIMESTAMP   4个字节
TIME    3个字节
YEAR    1个字节

十七、约束

含义：一种限制，用于限制表中的数据，为了保证表中数据的准确性和可靠性

17.1 六大约束

17.1.1 非空约束

NOT NULL：非空，用于保证该字段的值不能为空

17.1.2 默认约束

DEFAULT：默认约束，用于保证该字段有默认值

17.1.3 主键约束

PRIMARY KEY：主键，用于保证该字段的值具有唯一性，并且非空

17.1.4 唯一约束

UNIQUE：唯一，用于保证该字段的值具有唯一性，可以为空

17.1.5 检查约束

MySQL中不支持

CHECK：检查约束

17.1.6 外键约束

FOREIGN KEY：外键，用于限制两个表的关系，用于保证该字段值必须来自于主表的关联列的值

在从表中添加外键约束，用于引用主表中某列的值

添加约束的时机：

创建表时
修改表时

约束添加的分类：

列级约束：六大约束语法上都支持，但外键约束没有效果

表级约束：除了非空，默认。其他都支持

17.2 添加约束

17.2.1 创建表时添加列级约束

示例1：

CREATE TABLE stuinfo (
id INT PRIMARY KEY,# 添加住键约束
stuname VARCHAR ( 20 ) NOT NULL, # 添加非空约束 
gender CHAR ( 1 ) CHECK ( gender = '男' OR gender = '女' ),# 添加检查约束
seat INT UNIQUE,# 唯一约束
age INT DEFAULT 18,# 默任约束
majorId INT REFERENCES major ( id ) # 外键约束
);

CREATE TABLE major ( id INT PRIMARY KEY, majorName VARCHAR ( 20 ) );

17.2.2 创建表时添加表级约束

示例2：

CREATE TABLE stuinfo (
id INT, 
stuname VARCHAR ( 20 ),
gender CHAR ( 1 ),
seat INT,
age INT,
majorId INT,

# 添加表级约束
CONSTRAINT pk PRIMARY KEY(id), # 主键
CONSTRAINT uq UNIQUE(seat), # 唯一键
CONSTRAINT ck CHECK(gender='男' OR gender='女'), # 检查
CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorId) REFERENCES major(id) # 外键
);

外键的特点：

1、要求在从表设置外键关系

2、从表的外键列的类型和主表的对应列类型要求一致或兼容，名称无要求

3、主表中的关联列必须是一个key（一般是主键或唯一）

4、插入数据时，先插入主表，再插入从表。删除数据时，先删除从表，后删除主表

17.2.3 修改表时添加约束

1、添加列级约束

ALTER TABLE 表名 MODIFY COLUMN  字段名 字段类型 新约束;

2、添加表级约束

ALTER TABLE 表名 ADD [CONSTRAINT 约束名] 约束类型（字段名） [外键的引用]

示例：

# 添加非空约束
ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) NOT NULL;

# 添加默认约束
ALTER TABLE stuinfo MODIFY COLUMN age INT 18;

# 添加主键
ALTER TABLE stuinfo MODIFY COLUMN id INT PRIMARY KEY;

17.2.4 修改表时删除约束

1、删除非空约束

ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) NULL;

2、删除默认约束

ALTER TABLE stuinfo MODIFY COLUMN  age INT;

3、删除主键

ALTER TABLE stuinfo DROP PRIMARY KEY;

4、删除唯一

ALTER TABLE stuinfo DROP INDEX seat;

5、删除外键

ALTER TABLE stuinfo DROP FOREIGN KEY fk_stuinfo_major;

列级约束：跟在列的后面。语法都支持，但外键没有效果。不可以起约束名

表级约束：所有列的下面。默认和非空不支持，其他支持。可以起约束名（主键没有效果）

17.3 标识列

含义：又称为自增长列，可以不用手动的插入值，系统提供默认的序列值

17.3.1 创建表时设置标识列

CREATE TABLE tab_identify ( id INT PRIMARY KEY AUTO_INCREMENT, NAME VARCHAR ( 20 ) );

特点：

1、标识列不一定和主键搭配，但要求是一个key

2、一个表只能有一个标识列

3、标识列的类型只能是数值型

4、标识列可以通过 SET AUTO_INCREMENT_INCREMENT=3;设置步长。

17.3.2 修改表时设置标识列

ALTER TABLE tab_identify MODIFY COLUMN id INT PRIMARY KEY AUTO_INCREMENT;

17.3.3 修改表时删除标识列

ALTER TABLE tab_identify MODIFY COLUMN id INT;

十八、事务

18.1 概念

事务由单独单元的一个或多个SQL语句组成，在这个单元中，每个MySQL语句是相互依赖的。而整个单独单元作为一个不可分割的整体，如果单元中某条SQL语句一旦执行失败据或产生错误，整个单元将会回滚。所有受到影响的数据将返回到事物开始以前的状态；如果单元中的所有SQL语句均执行成功，则事物被顺利执行。

查看MySQL中引擎

show engines

存储引擎

innodb  支持事务
myisam  不支持事务
memory  不支持事务

18.2 事务的属性（ACID）

18.2.1 原子性

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

18.2.2 一致性

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

18.2.3 隔离性

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

18.2.4 持久性

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

18.3 创建事务

隐式事务：事务没有明显的开启和结束的标记

显式事务：事务具有明显的开启和结束的标记。前提是必须先设置自动提交功能和禁用

18.3.1 步骤

1、开启事务

SET AUTOCOMMIT=0;
START TRANSACTION [可选] 

2、编写事务中的语句（SELECT、INSERT、UPDATE、DELETE）

语句1;
语句2;
...

3、结束事务

COMMIT; # 提交事务
ROLLBACK; # 回滚事务

18.4 事务并发问题

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

18.4.1 脏读

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

18.4.2 不可重复读

对于两个事务T1，T2。T1读取了一个字段，然后T2更新了该字段之后，T1再次读取了同一个字段，值就不同了

18.4.3 幻读

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

18.5 事务的隔离级别

一个事务与其他事务隔离的程度称为隔离级别，数据库规定了多中事务的隔离级别，不同隔离级别对应不同的干扰程度，隔离级别越高，数据一致性就越好，但并发性越弱

查看当前隔离级别

SELECT @@tx_isolation;

设置当前MySQL连接的隔离级别

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

设置数据库系统的全局隔离级别

SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-FslBRfql-1620400269536)(https://static01.imgkr.com/temp/f3550c4dbbd74bfe88d179963d52ebf8.bmp)]

十九、视图

含义：虚拟表，和普通表一样使用，行和列的数据来自定义视图的查询中使用的表，并且在是在使用视图时动态生成的，只保存SQL逻辑，不保存查询结果

应用场景：

多个地方用到同样的额查询结果
该查询结果使用的SQL语句较复杂

19.1 创建视图

语法

CREATE VIEW 视图名 AS 查询语句;

示例1：查询邮箱中包含a字符的员工名、部门名和工种信息

1、创建视图

CREATE VIEW viw1 AS SELECT
last_name,
department_name,
job_title 
FROM
	employees AS e
	INNER JOIN departments AS d ON e.department_id = d.department_id
	JOIN jobs ON jobs.job_id = e.job_id';

2、使用视图

SELECT
	* 
FROM
	viw1 
WHERE
	last_name LIKE '%a%';

示例2：查询各部门的平均工资级别

1、创建视图

CREATE VIEW viw2 AS SELECT
avg( salary ) ag,
department_id 
FROM
	employees 
GROUP BY
	department_id;

2、使用视图

SELECT * FROM viw2;

示例3：查询平均工资最低的部门信息

1、使用视图

SELECT
	* 
FROM
	viw2 
ORDER BY
	ag 
	LIMIT 1;

示例4：查询平均工资最低的部门名和工资

1、创建视图

CREATE VIEW viw3 AS SELECT * FROM viw2 ORDER BY ag LIMIT 1;

2、使用视图

SELECT
	d.*,
	m.ag 
FROM
	viw3 m
JOIN departments d ON m.department_id = d.department_id;

优点：

1、重用SQL语句

2、简化复杂的SQL操作，不必知道它的查询细节

3、保护数据，提高安全性

19.2 视图的修改

19.2.1 方式一

语法：

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

示例1、修改原视图的查询结果

CREATE OR REPLACE VIEW 
viw3 AS 
SELECT
	avg( salary ),
	job_id 
FROM
	employees 
GROUP BY
	job_id;

19.2.2 方式二

语法：

ALTER VIEW 视图名 AS 查询语句;

示例：修改原视图的查询结果

ALTER VIEW viw3 AS SELECT * FROM employees;

19.3 删除视图

语法：

DROP VIEW 视图名, 视图名,...;

示例1：删除视图

DROP VIEW viw3, viw2;

19.4 查看视图

语法：

DESC 视图名;
SHOW CREATE VIEW 视图名;

示例1：查看viw1视图

DESC viw1;
SHOW CREATE VIEW viw1;

19.5 视图更新

19.5.1 插入

INSERT INTO myv1 VALUES('张飞', 'zf@qq.com',100000);

19.5.2 修改

UPDATE myv1 SET last_name = '张无忌' WHERE last_name = '张飞'

19.5.3 删除

DELETE FROM myv1 WHERE last_name = '张无忌';

19.6 注意

视图的可更新行和视图中查询的定义有关系，以下类型的视图是不能更新的

1、包含以下关键字的SQL语句：分组函数、distinct、group by、having、union或者union all

2、常量视图

3、select中包含子查询

4、join

5、from一个不能更新的视图

6、where子句的子查询引用from子句中的表

二十、变量

20.1 系统变量

变量由系统提供，不是用户定义，属于服务器层面

示例1：查看所有的系统变量

SHOW VARIABLES;

示例2：查看指定的某个系统变量的值

SELECT @@globel | [SESSION] .系统变量名;

示例3：为某个系统变量赋值

SET GLOBAL | [SESSION] 系统变量名 = 值;

SET @@GLOBAL | [SESSION].系统变量名 = 值;

注意：如果是全局变量，则需要加global关键字。如果是会话级别，则需要加session。如果不写，则默认session

20.1.1 全局变量

作用域：服务器每次启动都将为所有的全局变量赋初始值，针对所有会话（连接）有效

示例1：查看系统全局变量

SHOW GLOBAL VARIABLES;

示例2：查看满足条件的部分系统变量

SHOW GLOBAL VARIABLES LIKE 'char%';

20.1.2 会话变量

作用域：仅仅针对于当前会话（连接）有效

示例1：查看系统会话变量

SHOW SESSION VARIABLES;

20.2 自定义变量

说明：变量是用户自定义的，不是由系统的

20.2.1 使用步骤

1、声明
2、赋值
3、使用（查看、比较、运算等）

20.2.1 用户变量

作用域

针对当前会话（连接）有效，同于会话变量的作用域。可以应用在任何地方，也就是begin end里面或者外面

20.2.1.1 声明并初始化

SET @用户变量名 = 值;
SET @用户变量名 := 值;
SELECT @用户变量名 := 值; 

20.2.1.2 赋值

方式一：

SET @用户变量名 = 值;
SET @用户变量名 := 值;
SELECT @用户变量名 := 值; 

赋值变量符

= 或 :=

方式二：

通过SELECT INTO

语法：

SELECT 字段 INTO 变量名 FROM 表;

示例1：将COUNT(*)的值赋给@count

SELECT COUNT(*) INTO @count　FROM employees;

20.2.1.3 使用变量

SELECT　＠用户变量名;

示例1：查看@count

SELECT @count

示例2：声明两个变量并赋初始值，求和，并打印

SET @n = 10;
SET @m = 20;
SET @sum = @n + @m;
SELECT @sum; 

20.2.2 局部变量

作用域

仅仅在定义它的begin end中有效。并且只能放在begin end中的第一句话

20.2.2.1 声明

DECLARE 变量名 类型;
DECLARE 变量名 类型  DEFAULT 值;

20.2.2.2 赋值

方式一：

SET 局部变量名 = 值;
SET 局部变量名 := 值;
SELECT @局部变量名 := 值; 

赋值变量符

= 或 :=

方式二：

通过SELECT INTO

语法：

SELECT 字段 INTO 局部变量名 FROM 表;

20.2.2.3 使用

SELECT 局部变量名;

二十一、存储过程和函数

21.1 存储过程

含义：一组预先编译好的SQL语句的集合，理解成批处理语句

特点：

1、提高代码的重用性
2、简化操作
3、减少了编译次数并且减少了和数据库服务器的连接次数，提高了效率

21.1.1 创建

语法：

CREATE PROCEDURE 存储过程名 (参数列表) 
BEGIN

    存储过程体 (一组合法的 SQL语句 ) 

END

注意：

如果存储过程体只有一句话，begin end可以省略。反之不能省

存储过程体中的每条SQL语句结尾必须加分号，存储过程的结尾可以使用 DELIMITER 重新设置

DELIMITER 结束标记

示例：

DELIMITER $

21.1.1.1 参数列表

1、参数列表包含三部分

参数模式 参数名 参数类型

示例：

IN stuname VARCHAR(20)

21.1.1.1.1 参数模式

关键字：

IN：该参数可以作为输入，也就是该参数需要调用方传入值
OUT：该参数可以作为输出，也就是该参数可以作为返回值
INOUT：该参数既可以作为输入又可以作为输出，也就是该参数既需要传入值，又可以返回值

21.1.1.2 调用

语法：

CALL 存储过程名(实参列表);

21.1.2 空参的存储过程

示例1：创建

DELIMITER $

CREATE PROCEDURE 
myv1 () 
BEGIN
	INSERT INTO admin ( username, `password` )
	VALUES
		( 'rose', 4444 ),
		( 'sherry', 5555),
		( 'tom',1111 ),
		('lily',2222 ),
		('mark',6666 );
END $

示例2：调用

CALL myv1 () $

21.1.3 IN模式的存储过程

示例1：创建存储过程实现根据女神名，查询对应的男神信息

DELIMITER $

CREATE PROCEDURE myp2(IN beautyName VARCHAR ( 20 )) 
	
BEGIN
	SELECT
		bo.* 
	FROM
		boys bo
	RIGHT JOIN beauty b ON bo.id = b.boyfriend_id 
	WHERE b.name = beautyName;

END $

# 调用
CALL myp2('王语嫣') $   

示例2：创建存储过程，用户是否登录成功

DELIMITER $ 

CREATE PROCEDURE myp3(IN username VARCHAR(20), IN pwd VARCHAR(20))
BEGIN
	DECLARE result VARCHAR(20) DEFAULT ''; # 声明并初始化 
	
	SELECT COUNT(*) INTO result # 赋值
	FROM admin WHERE admin.username = username AND admin.password = pwd;
	
	SELECT result; # 打印
END $ 

# 调用
CALL myp3('张飞','8888') $

21.1.4 OUT模式的存储过程

示例1：根据女神名，返回对应的男神名

CREATE PROCEDURE myp4(IN beautyName VARCHAR(20), OUT boyName VARCHAR(20))
BEGIN
	SELECT bo.boyName INTO boyName
	FROM boys bo
	INNER JOIN beauty b ON bo.id = b.boyfriend_id
	WHERE b.`name` = beautyName;
END $

# 定义变量
SET @bName $
# 调用
CALL myp4('小昭', @bName) $
# 打印
SELECT @bName $

示例2：根据女神名，返回对应的男神名和男神魅力值

CREATE PROCEDURE myp5(IN beautyName VARCHAR(20), OUT boyName VARCHAR(20), OUT userCP INT)

begin
	SELECT bo.boyName, bo.userCP INTO boyName, userCP # 赋值
	FROM boys bo
	INNER JOIN beauty b ON bo.id = b.boyfriend_id
	WHERE b.`name` = beautyName;
END $


# 调用
CALL myp5('王语嫣', @bName, @userCP) $ 

# 打印
SELECT @bName, @userCP $

21.1.5 INOUT模式的存储过程

CREATE PROCEDURE myp6(INOUT a INT, INOUT b INT)

BEGIN
	SET a = a * 2;
	SET b = b * 2; 
END $

# 定义两个用户变量
SET @m = 10 $
SET @n = 20 $

# 调用
CALL myp6(@m, @n) $

# 打印
SELECT @m, @n $

21.1.6 存储过程的删除

语法：

DROP PROCEDURE 存储过程名

示例1：删除myv1存储过程

DROP PROCEDURE myv1;

21.1.7 存储过程的查看

语法：

SHOW CREATE PROCEDURE 存储过程名;

示例1：查看myp2存储过程

SHOW CREATE PROCEDURE myp2;

21.2 函数

21.2.1 函数介绍

含义：一组预先编译好的SQL语句的集合，理解成批处理语句

特点：

1、提高代码的重用性
2、简化操作
3、减少了编译次数并且减少了和数据库服务器的连接次数，提高了效率

区别：

存储过程可以有0个或者多个返回值
函数有且仅有一个返回值，适合做处理数据后返回一个结果

21.2.2 函数的创建及调用

21.2.2.1 函数的创建

创建语法：

CREATE FUNCTION 函数名(参数列表) RETURNS 返回类型

BEGIN
	函数体
END

注意：参数列表包含两部分

参数名
参数类型

函数体：

肯定会有RETURN语句，如果没有会报错
如果RETURN语句没有放在函数体的最后也不报错，但不建议

RETURN值：

函数体中仅有一句话，则可以省略BEGIN END
使用DELIMITER语句结束标记

21.2.2.1 函数的调用

语法：

SELECT 函数名(参数列表 )

21.2.3 函数的使用

示例1：无参有返回 返回公司员工的个数

DELIMITER $

CREATE FUNCTION fun1() RETURNS INT
BEGIN

	DECLARE c INT DEFAULT 0; # 定义变量
	SELECT COUNT(*) INTO c # 赋值
	FROM employees;
	RETURN c; # 返回值
	
END $

SELECT fun1() $ # 调用

示例2：有参有返回 根据员工名返回员工工资

CREATE FUNCTION fun2(empName VARCHAR(20)) RETURNS DOUBLE
BEGIN
	SET @sal = 0; # 定义一个用户变量
	SELECT salary INTO @sal # 赋值
	FROM employees 
	WHERE last_name = empName;
	RETURN @sal; # 返回值
END $

SELECT fun2('kochhar') $ # 调用

示例3：根据部门名返回该部门的平均工资

CREATE FUNCTION fun3(depName VARCHAR(20)) RETURNS DOUBLE
BEGIN
	DECLARE sal DOUBLE; # 定义变量
	SELECT avg(salary) INTO sal # 赋值
	FROM employees e
	JOIN departments d ON e.department_id = d.department_id
	WHERE d.department_name = depName;
	RETURN sal; # 返回值
END $

SELECT fun3('IT') $ # 调用

注意：

MySQL默认是不支持开启函数的，如果报错 This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary 则需要开启函数。

单次开启函数可使用 SET GLOBAL log_bin_trust_function_creators = 1; 设置开启函数

永久开启函数在 my.cnf 里面设置 log-bin-trust-function-creators=1;  然后重启服务 

21.2.4 函数的查看和删除

21.2.4.1 查看函数

语法：

SHOW CREATE FUNCTION 函数名;

示例1：查看fun2函数

SHOW CREATE FUNCTION fun2;

21.2.4.2 删除函数

语法：

DROP FUNCTION 函数名;

示例1：删除fun2函数

DROP FUNCTION fun2;

二十二、流程控制结构

22.1 分支结构

含义：

程序从两条或多条路径中选择一条去执行

22.1.1 IF函数

功能：实现简单的双分支

语法：

SELECT IF(表达式1, 表达式2, 表达式3)

执行顺序：

如果表达式1成立，则IF函数返回表达式2的值，否则返回表达式3的值

22.1.2 CASE结构

22.1.2.1 等值判断

语法：

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

22.1.2.2 区间判断

语法：

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

特点：

1、可以作为表达式，嵌套在其他的语句中使用，可以放在任何地方，BEGIN END 中或 BEGIN END 的外面
2、可以作为独立的语句使用，只能放在BEGIN END中
3、如果WHEN中的值满足或条件成立，则执行对应的THEN后面的语句，并且结束CASE。如果都不满足，则执行ELSE中的语句或值
4、ELSE可以省略，如果ELSE省略了，并且所有WHEN条件都不满足，则返回NULL

示例1：创建存储过程，根据传入的成绩，来显示等级

DELIMITER $

CREATE PROCEDURE test_case(IN score INT)
BEGIN
	CASE 
	WHEN score >= 90 AND score <= 100 THEN SELECT 'A';
	WHEN score >= 80 THEN SELECT 'B';
	WHEN score >= 60 THEN SELECT 'C';
	ELSE SELECT 'D';
	END CASE;

END $

# 调用
CALL test_case(95) $		

22.1.3 IF结构

功能：实现多重分支

语法：

IF 条件1 THEN 语句1;
ELSEIF 条件2 THEN 语句2;
...
[ELSE 语句n;]
END IF;

示例1：根据传入的成绩，显示等级

CREATE FUNCTION test_if(score INT) RETURNS CHAR
BEGIN
	IF score >= 90 AND score <= 100 THEN RETURN 'A';
	ELSEIF score >= 80 THEN RETURN 'B';
	ELSEIF score >= 60 THEN RETURN 'C';
	ELSE RETURN 'D';
	END IF;
END $


# 调用

SELECT test_if(90) $

22.2 循环结构

含义：

程序在满足一定条件的基础上，重复执行一段代码

分类：

WHILE
LOOP
REPEAT

循环控制：

ITERATE 类似于CONTINUE
LEAVE 类似于BREAK

22.2.1 WHILE

先判断后执行

语法：

WHILE　循环条件　DO
循环体；
END WHILE [标签];

22.2.2 LOOP

没有条件的死循环

语法：

[标签] LOOP
循环体;
END LOOP [标签];

22.2.3 REPEAT

先执行后判断

语法：

[标签] REPEAT 
循环体;
UNTIL 结束循环的条件
END REPEAT [标签];

22.2.1 循环结构演示

示例1：批量插入，根据次数插入到admin表中多条记录

CREATE PROCEDURE pro(IN inco INT)
    BEGIN
    	DECLARE i INT DEFAULT 1;
    	WHILE i <= inco DO
    		INSERT INTO admin(username, password) VALUES(CONCAT('Rose',i), '666');
    		SET i = i + 1;
    	END WHILE;
    
    END $
    # 调用
    CALL pro(10) $

示例2：批量插入，根据次数插入到admin表中多条记录，如果次数大于20则停止

CREATE PROCEDURE pro(IN inco INT)
    BEGIN
    	DECLARE i INT DEFAULT 1;
    	a:WHILE i <= inco DO
    		INSERT INTO admin(username, password) VALUES(CONCAT('Xiaohua',i), '666');
    		# 结束循环条件
    		IF i >= 20 THEN LEAVE a;
    		END IF;
    		# 自加
    		SET i = i + 1;
    	END WHILE a;
    
    END $
    
    # 调用
    CALL pro(20) $

示例3：添加ITERATE语句，根据次数插入到admin表中多条记录，只插入偶数次

# 删除admin表
TRUNCATE TABLE admin $
# 删除存储过程
DROP PROCEDURE pro $

CREATE PROCEDURE pro(IN inco INT)
BEGIN
	DECLARE i INT DEFAULT 0;
	a:WHILE i <= inco DO # a为标签
		# 自加
		SET i = i + 1;
		IF MOD(i,2) != 0 THEN ITERATE a;
		END IF;
		INSERT INTO admin(username, password) VALUES(CONCAT('Xiaohua',i), '666');
		# 结束循环条件
		
	
	END WHILE a;

END $

# 调用
CALL pro(20) $

22.2.2 循环结构案例

示例1：向表中插入指定个数的，随机字符串

# 判断表是否存在，如果存在，则删除，不存在则创建
DROP TABLE IF EXISTS stringcontent;
CREATE TABLE stringcontent(
	id INT PRIMARY KEY AUTO_INCREMENT,
	content VARCHAR(20)
);

# 创建存储过程
CREATE PROCEDURE test_insert(IN insertCount INT)

BEGIN
		DECLARE i INT DEFAULT 1; # 定义一个循环变量i，标识插入的次数
		DECLARE str VARCHAR(26) DEFAULT 'abcdefghijklmnopqrstuvwxyz'; # 定义str字符串
		DECLARE startIndex INT DEFAULT 1; # 代表起始索引
		DECLARE len INT DEFAULT 1; # 代表截取的字符的长度
		WHILE i <= insertCount DO
			SET len = FLOOR(RAND() * (20-startIndex+1) + 1) ; # 产生一个随机整数，代表截取的长度，1 - (26-startIndex+1)
			SET startIndex = FLOOR(RAND() * 26 + 1); # 产生一个随机的整数，代表起始索引1-26
			INSERT INTO stringcontent(content) VALUES(SUBSTR(str, startIndex, len));
			SET i = i + 1; # 循环变量更新
		END WHILE;
END $

二十三、备份和恢复

23.1 备份数据库

23.1.1 备份多个数据库

语法：

mysqldump -u 用户名 -p -B 数据库1 数据库2 数据库n > 文件名.sql

示例：将girls数据库备份到D盘

mysqldump -u root -p -B girls > d:\\bak.sql
mysqldump -u root -p -B book myemployees > d:\\bak.sql

注意：

1、进入MySQL命令行执行
2、末尾不能加分号

23.1.2 备份指定表

语法：

mysqldump -u 用户名 -p 密码 数据库 表1 表2 表n > d:\\文件名.sql

示例：备份girls数据库下的beauty表

mysqldump	-u root -p**** girls beauty > d:\\beauty.sql

注意：

1、-p密码之间没有空格

23.2 恢复数据库

语法：

Source 文件名.sql

示例：恢复bak.sql数据库

source d:\\bak.sql