• backend
  • database
  • mysql
  • MySQL 基础（单表查询）

MySQL 基础（单表查询）

学习步骤：

1.通过 docker 安装 MySQL

2.开启 docer 容器

3.navicat 连接数据库

4.导入学习资料

5.了解数据库基本概念

6.一行一行敲查询语句，看执行结果。

7.忘记很正常，需要的时候再来翻一下示例语句 CRM 即可。

8.CRM 的意思是 copy（抄）run（运行）modify（改）

通过 Docker 安装 MySQL

通过 官方网站安装 Docker

设置国内镜像:打开 Docker 后点开 Preferences => Docker Engine =>编辑 daemon.json

添加上这几句

json
1{
2  ...省略部分内容,
3  "registry-mirrors": [
4    "https://hub-mirror.c.163.com",
5    "https://mirror.baidubce.com"
6  ]
7}

下载 mysql：下面命令行会下载 5.7.28 版本的 mysql，如果希望是最新版，则把版本号去掉。

bash
1$ docker pull mysql:5.7.28

Docker 创建容器，打开命令行，输入

bash
1$ docker run --name [NAME] -e MYSQL_ROOT_PASSWORD=123456 -p 3306:3306 -d mysql:5.7.28

上面的命令的意思是创建 root 用户的 mysql 容器，密码 123456，端口号映射 3306，版本 5.7.28，名字自己设置，把[NAME]改成自己想要的就行。敲完命令会有一串编码，是容器对应的编码。这时候可以运行 docker ps 查看当前运行的容器状态。

image-20210717235219251

注意：默认情况下容器不会持久化，如果容器被删除掉了，那么 mysql 的数据也就没有了。

Docker 连接数据库

首先需要进入容器

bash
1$ docker exec -it [NAME] bash

进入容器后，当前就在 root 文件夹，运行以下命令开启 mysql

bash
1$ mysql -u root -p

这时候输入密码 123456 即可连接成功。

我们可以通过命令查看数据库列表

bash
1$ show databases;

image-20210718000436910

如果忘记打分号，最好 ctrl+c 中断后重新打。如果想退出数据库，可以 ctrl+d。

下面的命令有备无患

bash
1$ use xxx //使用 xxx 数据库
2$ show tables; //查看所有表
3$ select * from xxx; //查看xxx表的所有内容

mac 版本可以在这里下载

windows 版本网上随便找，无脑安装即可。

安装完成后打开，点击连接，然后输入账号密码进入数据库。

image-20210719000333670

导入数据表到库中，你可以右键新建一个数据库，这里选择导入到 sys 库里。

image-20210719001404232

image-20210719001703267

image-20210719001757505

结束后右键刷新，就能看到已经导入了三张表到 sys 库中了。

image-20210719002031913

数据库基本概念

数据库是什么？

数据库英文为 DataBase，简称 DB，是按照一定格式存储数据的组合。

数据库管理系统是什么？

数据库管理系统又称 DBMS，是帮助我们管理数据库中的数据的系统，比如 MySQL、PostgreSQL 等。

我们所使用的 MySQL，实际上是一个客户端管理系统，使用它，我们可以做以下操作：

编写 SQL 命令 => DBMS 执行 => 发送数据到 Server 服务端 => 读写硬盘或者内容中存储的数据(DataBase)

image-20210718001944086

SQL 语句是什么？

SQL 语句是一套结构化查询语言，通过编写 SQL 语句，可以让 DBMS 执行，进而发布命令操作 DB 数据。

数据库里有什么

数据库是由表Table组成，而 Table 由行（row）和列（col）组成。

就跟平时用的 Excel 差不多，其中列又称字段（fileds），字段由字段名、数据类型、约束等构成。

常用 SQL 语句分类

DQL：Data Query Language 数据查询语言 。用于查询数据。包含 select...的就是 DQL 语句

DML：Data Manipulation Language 数据操控语言。用于操作数据库对象对象中包含的数据。包含 insert、delete、update 的语句。

DDL：Data Definition Language 数据定义语言。主要用来操作数据库、数据表结构。 包含 create、drop、alter 的语句。

TCL：Transaction Control Language 事务控制语言。主要包含 commit、rollback 等语句。

DCL：Data Control Language 数据控制语言。主要包含 授权 grant、撤销授权 revoke 等语句。

数据库查询语句示例

sql
1/* 查询一个字段 */
2SELECT `LOC` from `DEPT`;
3/* 查询多个字段 */
4SELECT loc,dname from `DEPT`;
5/* 查询所有字段 */
6SELECT * from `DEPT`;
7/* 起别名 */
8SELECT dname as name from `DEPT`;
9/* 列计算 加减乘除*/
10SELECT `ENAME`,`SAL`*12 as SAL from `EMP`;
11/* 条件查询 where */
12SELECT `ENAME`,`SAL` from `EMP` where `SAL`>=5000;
13/* and */
14SELECT `ENAME`,`SAL` FROM `EMP` where `SAL`>800 and `SAL`<5000 and `SAL`!=3000;
15/* between and 时必须遵循左边小右边大的原则 闭区间 包含两边的值 */
16SELECT `ENAME`,`SAL` FROM `EMP` where `SAL` between 800 and 5000;
17/* 判断 is null */
18SELECT `ENAME`,`COMM` FROM `EMP` where `COMM` is null;
19/* 判断 is not null */
20SELECT `ENAME`,`COMM` FROM `EMP` where `COMM` is not null;
21/* or */
22SELECT `ENAME`,`SAL`,`JOB` from `EMP` where `SAL`>=5000 or `JOB`='manager';
23/* and 和 or 结合,优先级用括号，否则是 and 优先级高 */
24SELECT `ENAME`,`SAL`,`DEPTNO` from `EMP` where `SAL`>2500 and (`DEPTNO` =10 or `DEPTNO` =20);
25-- 使用 in查询，相当于 or,而不是区间
26SELECT `ENAME`,`SAL`,`DEPTNO` from `EMP` where `SAL`>2500 and `DEPTNO` IN (10,20);
27-- not 取反
28SELECT `ENAME`,`SAL`,`DEPTNO` from `EMP` where `SAL`>2500 and `DEPTNO` NOT IN (10,20);
29-- 模糊查询 % 和_是特殊符号， %表示任意多个字符 _表示任意一个字符
30-- 表示包含 s的
31SELECT ENAME from EMP where ENAME LIKE '%S%';
32-- 表示 s开头
33SELECT ENAME from EMP where ENAME LIKE 'S%';
34-- 表示 s结尾
35SELECT ENAME from EMP where ENAME LIKE '%S';
36-- 第二个字符是 a 的
37SELECT ENAME from EMP where ENAME LIKE '_A%';
38-- 第三个字符是 r 的
39SELECT ENAME from EMP where ENAME LIKE '__r%';
40-- 找出名字中有下划线的,用转义符\
41SELECT ENAME from EMP where ENAME LIKE '%\_%';
42-- 默认排序 升序
43SELECT ENAME,SAL from EMP ORDER BY SAL;
44-- 指定降序
45SELECT ENAME,SAL from EMP ORDER BY SAL DESC;
46-- 指定升序
47SELECT ENAME,SAL from EMP ORDER BY SAL ASC;
48-- 多字段排序,按照 sal 排序，如果一样就按 ename 升序
49SELECT ENAME,SAL from EMP ORDER BY SAL , ENAME ASC;
50-- 根据字段位置排序，第二列,不建议使用,因为列顺序很容易发生改变
51select ENAME,SAL from EMP ORDER BY 2;
52-- 关键字顺序不能改变，select .. FROM .. WHERE .. ORDER BY ..
53select * from EMP WHERE SAL>=1250 AND SAL <=3000 ORDER BY SAL DESC;

单行数据处理函数示例

数据处理函数又被称为单行处理函数。特点是一个输入对应一个输出。

和单行处理函数相对的是——多行处理函数：多个输入对应一个输出。

常见单行处理函数：

sql
1-- 查询结果小写
2SELECT LOWER(ENAME) as ENAME FROM EMP;
3-- 查询结果大写
4SELECT UPPER(ENAME) as ENAME FROM EMP;
5-- 截取第一个字母
6SELECT SUBSTR(ENAME,1,1) as ENAME FROM EMP;
7-- 字符串长度
8SELECT LENGTH(ENAME) as ENAME FROM EMP;
9-- 首字母大写
10SELECT CONCAT(UPPER(SUBSTR(ENAME,1,1)),LOWER(SUBSTR(ENAME,2,LENGTH(ENAME)-1))) FROM EMP;
11-- 去空格
12SELECT * from EMP WHERE ENAME= TRIM('  king ');
13-- ifnull 把结果为 null的给设置成具体值
14SELECT ENAME,SAL+ IFNULL(COMM,0) as result from EMP;
15-- case..when..when..else..end 语句。
16-- 当员工的工作岗位是 MANGER 时，工资上调10%，当工作岗位是SALESMAN 时，工资上调50%的查询,其他正常
17SELECT ENAME,JOB,(CASE JOB
18	WHEN 'MANAGER' THEN
19		sal*1.1
20	WHEN 'SALESMAN' THEN
21	  sal*1.5
22	ELSE
23		sal
24END) as result FROM EMP;
  • str_to_date 字符串转日期
  • date_format 格式化日期
  • format 设置千分位
  • round 四舍五入
  • rand() 生成随机数

多行处理函数（分组函数）

共有五个，count 计数、sum 求和、avg 平均值、max 最大值、min 最小值。

多行处理函数属于多行处理，并成一行。

分组函数通常需要先分组（group by）才能使用。

如果没使用分组（group by），默认整张表为一组。

sql
1-- 找出最多工资
2SELECT MAX(SAL) from EMP;
3-- 找出最少工资
4SELECT  MIN(SAL) from EMP;
5-- 工资求平均数
6SELECT AVG(SAL) from EMP;
7-- 工资求和
8SELECT SUM(SAL) from EMP;
9-- 找出除null 外的一共有多少行 4
10SELECT COUNT(comm) from EMP;
11-- 找出表中一共有多少行 14
12SELECT COUNT(*) from EMP;
sql
1-- 无效
2select ENAME,MAX(SAL) from EMP;
3-- 正确的查法
4select ENAME,SAL from EMP where SAL = (select MAX(SAL) from EMP);

注意点：

  • 分组函数可以自动忽略值为 null 的情况。
  • 分组函数中 count(*)会统计表中总行数（因为不存在所有值都为 null 的行，所以找到一个字段值不为 null，则 count+1），而 count(具体字段)则忽略 null
  • 分组函数不能直接使用在 where 条件查询中，需要使用group by关键字进行手动分组查询。

分组查询（group by）

为什么分组函数不能直接用在 where 条件语句中？

要知道这个答案，我们需要先了解 sql 语句的执行顺序，下面是一个标准的 sql 语句结构

sql
1SELECT .. FROM .. WHERE .. GROUP BY .. ORDER BY..

它的执行顺序是这样的：

  1. from

  2. where

  3. group by

  4. select

  5. order by

假设我需要筛选数据表中 sal 值大于最小 sal 的数据，我可能会这样写：

sql
1SELECT SAL FROM EMP WHERE SAL>MIN(SAL);

语句乍一看是没啥问题的，但是它违背了执行顺序。

sql 程序默认会将整张表进行分组，再调用 MIN 分组函数。

而 where 的执行顺序在分组 group by 之前，所以无论如何 where 条件语句都在 MIN 函数执行之前。

上面的语句中 MIN(SAL) 的值还没得出来，where 已经执行结束了，导致 sql 程序无法得出确切的条件，就会报错。

分组查询示例

分组查询重要结论说三遍：

在一条 select 语句中，如果有 group by 语句，select 后面一律只能跟参加分组的字段、分组函数和分组函数内的字段

在一条 select 语句中，如果有 group by 语句，select 后面一律只能跟参加分组的字段、分组函数和分组函数内的字段

在一条 select 语句中，如果有 group by 语句，select 后面一律只能跟参加分组的字段、分组函数和分组函数内的字段

例如下面的 SQL 查询语句，参加分组字段为 JOB，分组函数为 SUM、分组函数内的字段为 sal

sql
1-- 按照工作岗位分组计算每个岗位的工资和
2SELECT JOB,SUM(sal) from EMP GROUP BY JOB;

多字段分组示例

sql
1-- 找出每个部门，不同工作岗位的最高薪资
2SELECT DEPTNO,JOB,MAX(SAL) from EMP GROUP BY DEPTNO,JOB;
3-- 找出每个部门，不同工作岗位最高工资大于3000的.顺序：先找到条件大于3000的，再分组
4SELECT DEPTNO,MAX(SAL) from EMP WHERE SAL>3000 GROUP BY DEPTNO;
5-- 也可以使用 having进行分组后再过滤条件，但是这种方法效率不高，最好优先使用 where，where 不行再使用 having
6SELECT DEPTNO,MAX(SAL) from EMP GROUP BY DEPTNO HAVING MAX(SAL)>3000;
7-- 无法用 where 的情况：找出每个部门的平均薪资，且平均薪资大于2500的。
8SELECT DEPTNO,AVG(SAL) from EMP GROUP BY DEPTNO HAVING AVG(SAL)>2500;

查询结果去重 distinct 关键字

sql
1-- 查询结果去除重复记录，distinct关键字,只能出现在所有字段的最前面
2SELECT DISTINCT JOB from EMP;
3-- 表示所有字段联合起来去除重复值
4SELECT DISTINCT JOB,DEPTNO from EMP;
5-- 查找有几个部门
6SELECT COUNT(DISTINCT DEPTNO) FROM EMP;

总结

  1. 结构化的 SQL 语句，万能格式是这样的
sql
1SELECT 字段 from 表名 where 条件 group by 分组名 having 分组后条件 order by 排序

  1. SQL 语句执行顺序是这样的

  2. from 先经过某张表

  3. where 然后筛选一些条件

  4. group by 再分组

  5. having 分组后再过滤一下

  6. select 选择对应的字段

  7. order by 最后排序

  8. 如果你敲完了上面所有的命令，最后请再做一下这道综合题巩固一下（别急着看答案，自己动脑先实现一下噢）：

sql
1-- 计算每个部门（除 manager外）平均薪资，结果按照降序排列，要求展示平均薪资大于1500小于3000的部门
2SELECT DEPTNO,AVG(SAL) from EMP WHERE JOB != 'MANAGER'GROUP BY DEPTNO HAVING AVG(SAL)>1500 AND AVG(SAL)<3000 ORDER BY AVG(SAL) ASC;