MySQL数据库教学内容和要求 mysql数据库课程

教程来自b站尚硅谷视频MySQL数据库（入门到高级，菜鸟到大牛）。

需要的某些课件软件： 腾讯微云：https://share.weiyun.com/yd7OxoMm 密码：cg4mvb 百度网盘：https://pan.baidu.com/s/1cLCb0zcitTOzS5dcCOtHww 密码：yyds

作为一个合格的后端，数据库最起码要到这个程度：

接下来这些都会涉及到。

1. 数据库概述与MySQL安装

1.1 数据库概述

1.1.1 常见DBMS

1.1.2 数据库分类

• 关系型（sql）：
  
  优势：
• 非关系型(nosql)：
  
  
  
  
  
  注：列式降低io的原理是查一条记录时会将该记录所有信息加载到内存中，如果一共10个单元格，就用两个单元格，那浪费了8个单元格，这种情况就是消耗了过多的io。以后在介绍数据库优化的时候，降低io是个优化方向。

1.1.3 sql和nosql的选择

1.1.4 关系型数据库设计

ORM思想：

表的关联关系：

• 一对一：
• 一对多：
• 多对多：
• 自我引用：

1.2 mysql环境搭建

mysql主要由c,c++开发。

1.2.1 卸载

安之前先卸干净，不然会有一些奇怪的问题。

1.任务管理器停服务

2.控制面板卸载

3.path环境变量和data数据

删除重要的数据库data数据要备份！

4. 清注册表

1.2.2 下载、安装、配置（8.0.26版）

版本：

下载：

https://downloads.mysql.com/archives/installer/

安装：

后面的安装截图就不展示了，一路默认，到这里就是配置了：

starting server有问题的解决方案：

配环境变量：

如果要用cmd操作就配，不用就不配。

补充：常见问题

（1）使用第三方可视化工具登录mysql8数据库的root账号提示密码不对，发现root密码改为了空

是因为8版本安全性考虑改了编码导致的，5版本没这个问题。

该问题在视频教程的解释：

代码别输错，这里提供可以复制的代码：

参考了

USE mysql;
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';
FLUSH PRIVILEGES;

（2）数据库密码忘记，修改密码

1.2.2 下载、安装、配置（5.7.35版）

按照8.0.26版本下载说明，找到5.7.35的历史版本，下载。

前面装过8.0.26，所以是这个界面：

端口号重复，要换一个：

my.ini设置字符集等：

因为mysql是欧洲那边做的，所以字符集是latin，存中文有问题，要改为utf8。这个操作8版本以后默认utf8，可以不改，5版本要改。

1.2.3 图形化数据库管理工具

mysql workbench,navicat,sqlyog,dbeaver.

这里只使用开头课件的sqlyog13.1.7社区版。

安装后登录：

2. SQL语句-SELECT

sql语句在编程过程中自己设置时，为了保证正确，一定要做做测试，看是不是该sql语句查询出符合你要求的数据，其实不只是sql，方方面面的测试是编程中很重要的一环。

2.1 基本SELECT

2.1.1 sql概述

2.1.2 sql语言规范

规则（必须遵守）：

规范（推荐遵守）：

• 大小写规范
  
  windows下大小写不敏感是因为windows本身不区分大小写：

2.1.3 基本select语句

首先要有个表做例子，表在开头的课件里：

运行这个sql文件，可以cmd执行，也可以sqlyog的工具-执行sql脚本：

这种方式要注意，有可能把表给你插入到奇怪的库里，最好还是新建个库，然后在这个库里执行复制进去的sql语句。

案例库的表：

(1)SELECT … FROM …

SELECT * 
FROM `employees`;

SELECT employee_id,last_name,salary 
FROM `employees`;

(2)列的别名 AS

as可以省略，按照规范，别名双引号引起来。

列的别名只能在排序ORDER BY中使用，不能在WHERE中使用。
这是由sql语句的执行顺序决定的。顺序：from-where-select-order by, where在select前面，这个时候还没起别名呢。

SELECT employee_id,last_name,salary AS "s"
FROM `employees`;
SELECT employee_id,last_name,salary s 
FROM `employees`;

属性也可以小小运算一下：

SELECT employee_id,last_name,salary*12 AS "年工资"
FROM `employees`;

(3)去重复行 DISTINCT

SELECT DISTINCT department_id 
FROM `employees`;

# 报错，其中一个属性去重，另外的不去，数据就合不起来了
SELECT salary,DISTINCT department_id 
FROM `employees`;

# DISTINCT 给所有属性去重可以运行，会给完全一致的行去重。
SELECT DISTINCT department_id,salary
FROM `employees`;

(4)空值参与运算 null

# null参与运算，结果为null
SELECT employee_id,salary AS "月工资",salary *(1+commission_pct)*12 AS "年工资",commission_pct
FROM `employees`;

# 通过IFNULL判断，将null视为0
SELECT employee_id,salary AS "月工资",salary *(1+IFNULL(commission_pct,0))*12 AS "年工资",commission_pct
FROM `employees`;

(5)着重号 `

着重号是键盘字母区上面数字的左边那个，我们有时候也叫飘号`，前面我们使用表名的时候用过了。

# order 是关键字，报错，要用着重号引起来。
# 错误：
SELECT * 
FROM ORDER;
# 正确：
SELECT * 
FROM `order`;

(6)查询常数

# 给每行数据加相同的属性，在某些情况下也许要用
SELECT 'atguigu',123,employee_id,last_name
FROM `employees`;

2.1.4 显示表结构 DESCRIBE DESC

# 显示表中每个属性的信息
DESCRIBE `employees`;
# 简写
DESC `employees`;

2.1.5 过滤数据 WHERE

# WHERE 表示过滤条件
SELECT * 
FROM `employees`
WHERE department_id=90;

# AND连接多个条件
SELECT * 
FROM `employees`
WHERE department_id=90 AND last_name='King';

# windows下，表名和属性名变大写，查询条件变小写，结果不受影响
SELECT * 
FROM `EMPLOYEES`
WHERE DEPARTMENT_ID=90 AND last_name='king';

2.2 运算符

2.2.1 算术运算符

# 和java不同，“+”符号只有运算作用，没有连接作用（连接有专门的关键字CONCAT），加个数字字符串正常运算，非数字字符串会忽略
# 和java不同，5/3不是整数，而是浮点数
# DUAL表示伪表，虚拟的表，不是特定的某张表。
SELECT 100,100+12,100-5,10+5.5,10-4.4,10+'1',10-'ab',5/3,5%3
FROM DUAL;

# 除法默认浮点型，/0为null
# %运算，结果的正负符号和%前面的数值的正负符号相同,和后面的数值无关
SELECT 100,100*1,100*1.0,100/1,100/1.0,5/3,5%3,-5%3,100/0
FROM DUAL;

2.2.2 比较运算符

（1）运算符类比较

注：关于= ,<=>

# '1'隐式转化为1；'a'隐式转换为0
SELECT 1='1',1='a',0='a',1=NULL,
FROM DUAL;

# WHERE NULL这个条件，没有结果，想筛选属性为NULL的，要用ISNULL()函数或者安全等与,不用等号

# 错误：
SELECT * 
FROM `employees`
WHERE commission_pct=NULL;

# 正确1：
SELECT * 
FROM `employees`
WHERE ISNULL(commission_pct);

# 正确2：
# 安全等与与等于相同，就是多了支持比较NULL
SELECT * 
FROM `employees`
WHERE commission_pct<=>NULL;

（2）关键字类比较

ISNULL

其中ISNULL那个不能这样用，用法是要当函数用: WHERE ISNULL(属性名).

LEAST,GREATEST

# 比较字符串会比较字符编码序号
SELECT LEAST('a','c','f'),GREATEST('a','c','f')
FROM DUAL;

between and 的两个条件是上下界，不能交换，先写下界，再写上界。

in 的条件是离散的，between的条件是连续的。

LIKE

# %代表不确定个数的字符，_代表一个不确定字符,
# 就想查找%和_,转义请补\。
# 查包含a的
SELECT *
FROM `employees`
WHERE last_name LIKE '%a%';
# 查a开头的
SELECT *
FROM `employees`
WHERE last_name LIKE 'a%';
# 查第二个字符是a的
SELECT *
FROM `employees`
WHERE last_name LIKE '_a%';

# 查第二三个字符是_a的
SELECT *
FROM `employees`
WHERE last_name LIKE '_\_a%';

2.2.3 逻辑运算符

2.2.4 位运算符

使用频率巨低。

2.2.5 运算符优先级

记不住，可以使用括号将需要先算的式子括起来，保证一定先算。

2.2.6 正则表达式 RLIKE,REGEXP

更多正则规则请查阅相关资料。https://www.runoob.com/regexp/regexp-syntax.html

# 匹配s开头
SELECT *
FROM `employees`
WHERE last_name REGEXP '^s';

# 匹配s结尾
SELECT *
FROM `employees`
WHERE last_name REGEXP 's$';

# 匹配含有as
SELECT *
FROM `employees`
WHERE last_name REGEXP 'as';

2.3 排序与分页 ORDER BY,LIMIT

2.3.1 排序规则

不排序时，输出结果的排序就是先后添加的顺序。

排序时不指明升降序，默认升序。

2.3.2 单列排序

# salary降序排列
SELECT * 
FROM `employees`
ORDER BY salary DESC;

2.3.3 多列排序

# department_id升序排列，相同的department_id中salary降序排列
SELECT employee_id,department_id,salary
FROM `employees`
ORDER BY department_id ASC,salary DESC;

2.3.4 分页实现规则

LIMIT index,显示数量
index从0开始。
公式：
每页显示记录数：count,第几页：pageindex
LIMIT (pageindex-1)*count,count。
简写：LIMIT 0,count---->LIMIT count

# department_id不为null，升序排列，相同的department_id中salary降序排列,显示第二页20个，（第21-40个）
SELECT employee_id,department_id,salary
FROM `employees`
WHERE department_id IS NOT NULL
ORDER BY department_id ASC,salary DESC
LIMIT 20,20;

LIMIT X,Y（5版本）=LIMIT Y OFFSET X（8版本）

补充：UNION

注意UNOIN ALL和UNION相同的情况需要没有重复数据，主键是肯定不重复的，如果select显示的不是主键就不一定了

# UNOIN 48条结果
SELECT *
FROM `employees` AS e
WHERE e.salary>9000
UNION 
SELECT *
FROM `employees` AS e
WHERE e.department_id>70;
# UNION ALL 68条结果
SELECT *
FROM `employees` AS e
WHERE e.salary>9000
UNION ALL
SELECT *
FROM `employees` AS e
WHERE e.department_id>70;

2.4 多表查询

现在已知的查询关键字：

这些都是在一个表里面查询，接下来进行多表查询操作教学。

为什么要用到多表查询呢？其实多表查询是可以拆成多个单表查询的，但是多个单表查询意味着请求数据库的次数也是多次的，数据库的连接池资源其实是个很稀缺的资源，是个性能瓶颈，因此，可以得出结论（以下是博主在写这篇博客时的理解，也许和实际有出入）：

• 能一次查找，就绝不多次查找，
• 能复用查找结果，就绝不再次查找。

既然表之间是有关联的，为什么不合成一张大表？

1. 表中会有很多冗余字段，浪费空间
2. 查询时io操作费劲，时间效率低
3. 粒度太粗，修改表时不便于并发、分布式。

2.4.1 笛卡尔积连接

# 交叉连接 CROSS JOIN:笛卡尔积，全部组合排列
SELECT employee_id,department_name
FROM `employees` CROSS JOIN `departments`;

# 不写连接关键字:也是笛卡尔积，全部组合排列
SELECT employee_id,department_name
FROM `employees`,`departments`;

# 添加两个表的连接条件
SELECT employee_id,department_name
FROM `employees`,`departments`
WHERE `employees`.department_id=`departments`.department_id;

# SELECT的属性名如果多个表中都有要明确来自于哪张表。
# 建议：从sql优化的角度，多表查询时在每个查询的属性名前都加表名。因为mysql寻找该属性属于哪个表也是要花时间的。
SELECT employee_id,department_name,`employees`.department_id
FROM `employees`,`departments`
WHERE `employees`.department_id=`departments`.department_id;

# 上一个sql语句表名太冗余了，可以给表起别名，表起别名后，用表名只能用别名
# 这里发现别名的双引号会报错
SELECT emp.employee_id,dept.department_name,emp.department_id
# FROM `employees` as "emp",`departments` as "dept"
FROM `employees` AS emp,`departments` AS dept
WHERE emp.department_id=dept.department_id;

# 三表联合
# n个表联合，最少n-1个连接条件
SELECT e.employee_id,d.department_name,l.city
FROM `employees` AS e,`departments` AS d,`locations` AS l
WHERE e.department_id=d.department_id AND d.location_id=l.location_id;

2.4.2 多表查询的不同分类角度

（1）等值/非等值

# 非等值连接
SELECT e.employee_id,e.salary,j.grade_level
FROM `employees` AS e,`job_grades` AS j
WHERE e.salary BETWEEN j.lowest_sal AND j.highest_sal;

# 与上面sql相同效果
SELECT e.employee_id,e.salary,j.grade_level
FROM `employees` AS e,`job_grades` AS j
WHERE e.salary >=j.lowest_sal AND e.salary<=j.highest_sal;

（2）自连接/非自连接

# 自连接
SELECT e1.employee_id,e1.last_name,e2.employee_id AS "manager_id",e2.last_name AS "manager_name"
FROM `employees` AS e1,`employees` AS e2
WHERE e1.manager_id=e2.employee_id;

（3）内连接/外连接

前面章节使用的的sql语句都是属于内连接。

注：mysql不支持全（满）外连接方式，因此需要其他连接方式的组合来得到全外连接的结果。

# 内连接，只有匹配条件的数据

# SQL92
SELECT e.last_name,d.department_name
FROM `employees` AS e,`departments` AS d
WHERE e.department_id=d.department_id;

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

# 三表
# JOIN=INNER JOIN
SELECT e.last_name,d.department_name,l.city
FROM `employees` AS e 
JOIN `departments` AS d
ON e.department_id=d.department_id
INNER JOIN `locations` AS l
ON d.location_id=l.location_id;

# 外连接，除了内连接的数据外，还有左表/右表（基础表）中不匹配的数据，不匹配的地方用NULL填充
# mysql中外连接只支持SQL99语法

# 左外连接
SELECT e.last_name,d.department_name
FROM `employees` AS e
LEFT OUTER JOIN `departments` AS d
ON e.department_id=d.department_id;

# 右外连接
# 外连接的OUTER可省略
SELECT e.last_name,d.department_name
FROM `employees` AS e
RIGHT JOIN `departments` AS d
ON e.department_id=d.department_id;

# 满外连接,
# 注意UNOIN ALL和UNION相同的情况需要没有重复数据，id是肯定不重复的，如果select的是e.last_name就不一定了
(SELECT e.employee_id,d.department_name
FROM `employees` AS e
LEFT JOIN
`departments` AS d
ON e.department_id=d.department_id)
UNION ALL
(SELECT e.employee_id,d.department_name
FROM `employees` AS e
RIGHT JOIN
`departments` AS d
ON e.department_id=d.department_id
WHERE e.department_id IS NULL);

注：

（4）SQL99新特性：自然连接 NATURAL JOIN 和 USING的使用

NATURAL JOIN：

USING：

SELECT e.employee_id,d.department_name
FROM `employees` AS e
LEFT JOIN `departments` AS d
ON e.department_id=d.department_id;

# USING可以用作ON条件的简写，用在两表相同属性名上
SELECT e.employee_id,d.department_name
FROM `employees` AS e
LEFT JOIN `departments` AS d
USING (department_id);

2.5 单行函数

2.5.1 数值函数

（1） 基本函数

（2）角度与弧度互换函数

（3）三角函数

（4）指数与对数

（5）进制间的转换

2.5.2 字符串函数

# CONCAT 连接
# 连接的内容有NULL，连接结果为NULL
SELECT CONCAT (e1.last_name,' worked for ',e2.last_name) AS "results"
FROM `employees` AS e1
LEFT JOIN `employees` AS e2
ON e1.manager_id=e2.employee_id;

# CONCAT_WS 指定分隔符的连接
SELECT CONCAT_WS('-','one','two','three') 
FROM DUAL;

# INSERT 某位置替换
# 替换部位为从第2个字符开始，长度为3个字符
# 字符串的索引是从1开始的
SELECT INSERT('helloworld',2,3,'aaaa') # haaaaoworld
FROM DUAL;

SELECT INSERT('爱上建档立卡觉得萨达',2,3,'aaaa') # 爱aaaa立卡觉得萨达
FROM DUAL;


# REPLACE 某字符串替换
# 没有符合要求的替换大不了不换，不会报错
SELECT REPLACE('hello','ll','mm') # hemmo
FROM DUAL;

# UPPER,LOWER 大小写转换
SELECT UPPER('Hello'),LOWER('Hello') # HELLO,hello
FROM DUAL;

# LEFT,RIGHT 取字符串左边或右边几个字符
SELECT LEFT('hello',2),RIGHT('hello',3)# he,llo
FROM DUAL;

# LPAD,RPAD 特定字符左/右补位
SELECT LPAD('coderhao',10,'*'),RPAD('coderhao',10,'*') # **coderhao,coderhao**
FROM DUAL;

# TRIM 去左右空格，LTRIM 去左空格, RTRIM 去右空格
SELECT 
TRIM('  he   llo   '),# ‘he   llo’
LTRIM('  he   llo   '),# ‘he   llo   ’
RTRIM('  he   llo   '),# ‘  he   llo’
TRIM('o' FROM 'oheollo') # 'heoll'
FROM DUAL;

# REPEAT,STRCMP
SELECT 
REPEAT ('hello',4),# hellohellohellohello
STRCMP('abc','abe')# -1
FROM DUAL;

2.5.3 日期时间函数

（1）获取日期、时间

SELECT 
CURDATE(),#2021-12-27
CURTIME(),#15:31:36
NOW(),#2021-12-27 15:31:36
UTC_DATE(),#2021-12-27
UTC_TIME()#07:31:36 这是零时区的时间，我们是东八区，时间要+8
FROM DUAL;

（2）日期与时间戳的转换

SELECT 
CURDATE(),#2021-12-27
CURTIME(),#15:31:36
NOW(),#2021-12-27 15:31:36
UTC_DATE(),#2021-12-27
UTC_TIME()#07:31:36 这是零时区的时间，我们是东八区，时间要+8
FROM DUAL;

SELECT 
UNIX_TIMESTAMP(),# 1640594884
UNIX_TIMESTAMP(NOW()),# 1640594884
FROM_UNIXTIME(UNIX_TIMESTAMP())# 2021-12-27 16:48:04
FROM DUAL;

（3）获取月份、星期、星期数、天数等函数

（4）日期的操作函数

（5）时间和秒钟转换的函数

SELECT 
TIME_TO_SEC(CURTIME()),#61009
SEC_TO_TIME(TIME_TO_SEC(CURTIME()))#16:56:49
FROM DUAL;

（6）计算日期和时间的函数

（7）日期的格式化与解析

2.5.4 流程控制函数

2.5.5 加密解密函数

PASSWORD/ENCODE/DECODE函数在mysql8被弃用了。

2.5.6 MySQL信息函数

2.5.7 其他函数

2.6 聚合函数

2.6.1 聚合函数介绍

(1) AVG/SUM

(2) MIN/MAX

(3) COUNT

2.6.2 GROUP BY

(1) 基本使用

(2) 使用多个列分组

(3) GROUP BY+WITH ROLLUP

2.6.3 HAVING

(1) 基本使用

能在where中放条件，就别放到having中，因为执行顺序是FROM-WHERE-GROUP BY-HAVING-SELECT-DISTINCT-ORDER BY-LIMIT,数据肯定是越早处理，数据量越小，在后续的处理中会越省时间。

(2) WHERE和HAVING的对比

2.6.4 SELECT的执行过程

(1) 结构

(2) 执行顺序

FROM-ON-WHERE-GROUP BY-HAVING-SELECT-DISTINCT-ORDER BY-LIMIT

(3) SQL执行原理

在MYSQL-高级篇解释。

2.7 子查询

2.7.1 需求分析与问题解决

（1）实际问题

（2）子查询基本使用

上面问题的解法：

（3）子查询分类

2.7.2 单行子查询

（1）单行比较操作符

（2）例子

2.7.3 多行子查询

（1）多行比较操作符

（2）代码示例

查询结果看成是个表，放在FROM里，但要加别名：

在各种嵌套查询中，一定要注意排除查询条件中的NULL情况，不然会导致结果各种奇怪问题。

2.7.4 相关子查询

（1）执行流程

（2）代码示例

# 查询员工中工资大于本部门平均工资的员工信息
SELECT *
FROM `employees` AS e1
WHERE e1.salary>(SELECT  AVG(e2.salary)
		 FROM `employees` AS e2
		 WHERE e1.department_id=e2.department_id
		 );

# 查询员工id,salary,按照department_name排序
SELECT e.employee_id,e.salary
FROM `employees` AS e
ORDER BY (
	  SELECT d.department_name
	  FROM `departments` AS d
	  WHERE e.department_id=d.department_id) ASC;

（3）EXIST/NOT EXIST

# 查询公司管理者的信息
# 方式1
SELECT DISTINCT e2.employee_id,e2.last_name
FROM `employees` e1
LEFT JOIN `employees` e2
ON e2.employee_id=e1.manager_id
WHERE e1.manager_id IS NOT NULL;
# 方式2
SELECT *
FROM `employees` AS e1
WHERE e1.employee_id IN (
						 SELECT e2.manager_id
						 FROM `employees` AS e2
						 WHERE e2.manager_id IS NOT NULL
						 GROUP BY e2.manager_id);
# 方式3 EXISTS:是否存在，是个判断，判断成功，WHERE条件成立，输出一条数据
SELECT *
FROM `employees` AS e1
WHERE EXISTS(
		     SELECT *
		     FROM `employees` AS e2
		     WHERE e1.employee_id=e2.manager_id);

（4）相关更新

（5）相关删除

2.7.5 思考