[MySQL] MySQL学习笔记：数据处理之查询-CSDN博客

本文链接：https://blog.csdn.net/weixin_43429677/article/details/108523458

这篇博客详细介绍了MySQL数据库中的数据处理，特别是查询操作。从基础查询、条件查询、排序查询到分组查询，深入讲解了各种查询技巧和函数的使用，包括单行函数、分组函数等。此外，还涵盖了连接查询的概念，如等值连接、非等值连接和自连接，并提供了丰富的示例和练习，帮助读者掌握MySQL查询语句的运用。

摘要由CSDN通过智能技术生成

视频链接：MySQL_基础+高级篇- 数据库 -sql -mysql教程_mysql视频_mysql入门_尚硅谷.

数据库概述

数据库相关概念

DB：数据库(database),存储数据的仓库，保存了一系列有组织的数据。
DBMS:数据库管理系统(Datebase Management System), 又称为数据库软件或数据库产品，用于创建或管理DB
SQL：结构化查询语言（Structure Query Language），用于和数据库通信的语言，不是某个数据库软件特有的，而是几乎所有的主流数据库软件通用的语言

数据库存储数据的特点

将数据放到表中，表再放到库中
一个数据库可以有多张表，每个表都有一个名字，表名具有唯一性
表由列组成，也称为字段
表中的数据是按行存储的

DML(Data Manipulation Language):数据操纵语言，用于添加、删除、修改、查询数据库记录，并查找数据完整性。
DDL(Data Definition Language):数据定义语言，用于库和表的创建、修改、删除。
DCL(Data Control Language):数据控制语言，用于定义用户的访问权限和安全级别。

MySQL的使用

启动退出

MySQL软件的服务器端必须先启动，客户端才可以连接和使用数据库。

启动MySQL：管理员身份运行命令提示符，输入net start mysql(也可以通过"服务"开关)
停止：net stop mysql

客户端登录：命令行 mysql -h 主机名 -P 端口号 -u 用户名 -p密码
例如：mysql -h localhost -P 3306 -u root -proot

注意：

-p与密码之间不能有空格，其他参数之间的空格可有可无
mysql -hlocalhost -P3306 -uroot -proot
密码建议在下一行输入
mysql -h localhost -P 3306 -u root -p
Enter password:
如果是连本机：-hlocalhost可以省略;如果端口号没改，-P3306也可以省，简写为：
myslql -uroot -p

连接成功后，会出现MySQL Server 服务版本的信息，还有第几次链接的id标识，也可通过命令行查看(没有登录MySQL服务端)：
mysql --version 或者mysql -V
或登录后，通过以下方式查看：
SELECT VERSION();

命令行以分号(;)结束

退出客户端：exit 或者Ctrl+C

MySQL常用命令

显示有哪些数据库 show databases;
进入某个库：use sys; sys库名
查看库内的表：show tables;

现在已经进入到sys库内，要查看另一个库内的表：show tables from mysql,此时还在sys库
查看当前在哪个库：select database();

创建表:

create table stuinfo(  # 以下定义有哪些列
id int,  # id列，类型是整数
name varchat(20));  # name列，类型为字符串

查看表的结构：desc stuinfo; desc+表名
查看表内数据：select * from stuinfo;
插入数据：insert into stuinfo (id, name) values(1,'john');
修改：update stuinfo set name='lilei' where id=1;
删除：delete from stuinfo where id=1;

MySQL语法规范

不区分大小写，但建议关键字大写，表名、列名小写
每条命令最好用分号结尾
每条命令根据需要，可以进行缩进或换行
注释：
单行注释：#注释文字
单行注释： -- 注释文字 中间有空格
多行注释：/* 注释文字 */

数据处理之查询

基础查询

语法：select 查询列表 from 表名

特点：

查询列表可以是：表中的字段、常量值、表达式、函数，也可以是多个
查询的结果是虚拟的表格

先打开库：USE myemployees;

查询表中的单个字段： SELECT last_name FROM employees;
查询表中的多个字段： SELECT last_name,salary,email FROM employees;
查询表中所有的字段：SELECT 双击选择要查询的表 FROM employees;用此方法可规定输出表的顺序
全选按F12可以格式化（我下的普通版，不能用）
或者SELECT * FROM employees;

为区别关键字和字段，可使用着重号`
若这个字段名容易跟关键字混淆还可以使用着重号将其括起来

查询常量值：
SELECT 100;
SELECT 'john'; 不区分字符和字符串
注意：字符型和日期型的常量值必须用单引号引起来，数值型不需要
查询表达式：
SELECT 100*98;
查询函数：
select 函数名（实参列表）；
SELECT VERSION();
起别名：
①便于理解
②如果眼查询的字段有重名的情况，使用的别名可以区分开来
方式一：
SELECT 100%98 AS 结果；
SELECT last_name AS 姓,first_name AS 名 FROM employees; 逗号是英文
方式二：省略as
SELECT last_name 姓,first_name 名 FROM employees;

案例：查询salary, 显示结果为 out put
SELECT salary AS "out put" FROM employees; out为关键字，将重命名用双引号括起来，着重号也可以

去重
DISTINCT 只能查询一个信息
案例：查询员工表中涉及到的所有的部门编号
SELECT DISTINCT department_id FROM employees;
+号的作用：运算符（加法运算）
select 100+90; 两个操作数都为数值型，则做加法运算
select '123'+90; 只要其中一方为字符型，试图将字符型转换为数值；如果转换成功，则继续做加法运算；若果转换失败，则将字符型转换为0.select 'john'+90; 返回90
select null+10; 只要其中一方为null，则结果肯定为null

案例：查询员工名和姓连接成一个字段，并显示为姓名
拼接：CONCAT
SELECT CONCAT(last_name,first_name) AS 姓名 FROM employees;

小练习

以下语句可以执行成功：
SELECT last_name, job_id, salary AS sal FROM employees;
查询表departments的结构，并查询其中的全部数据：
DESC departments;
SELECT * FROM departments;
显示出表employees中的全部job_id（不能重复）：
SELECT DISTINCT job_id FROM employees;
显示出表employees的全部列，各个列之间用逗号连接，列头显示out_put:

SELECT  # 错误
		CONCAT( `first_name`,  ',' , `last_name`,  ',' , `phone_number`,  ',', `commission_pct`,) AS out_put
FROM
		employees;

部分员工commission_pct为null，它与任何字符拼接后最终结果都为null，在此引入一个函数IFNULL()

ifnull函数：判断某字段或表达式是否为null，如果为null返回指定值，否则返回原本的值

SELECT
		IFNULL(COMMISSION_PCT,0) AS 奖金率
FROM employees;

第一个参数是可能显示null的变量，第二个参数为当它为null时要返回的值，则语句可以改成：

SELECT
		CONCAT( `first_name`,  ',' , `last_name`,  ',' , `phone_number`,  ',', IFNULL(commission_pct, 0)) AS out_put
FROM
		employees;

isnull函数：判断某字或表达式是否为null，如果是，则返回1；否则返回0

select isnull(commission_pct) from employees; 返回布尔值，是null返回1

条件查询

语法：

SELECT
		查询列表  # 3
FROM
		表名  # 1
WHERE
		筛选条件；# 2

分类：

①. 按条件表达式筛选
条件运算符： <， >， =，不等于<>,!=也支持，>=, <=，<=>安全等于

案例1：查询工资>12000的员工信息

SELECT
		*
FROM
		employees
WHERE
		salary > 12000;

案例2：查询部门编号不等于90号的员工名和部门编号

SELECT
		last_name,
		department_id
FROM
		employees
WHERE
		department_id <> 90;

②. 按逻辑表达式筛选
作用：用于连接条件表达式
逻辑运算符：
① 与 and(&&) 如果两个条件都为true，结果为true，反之为false，
② 或 or(| |)，只要一个条件为true，结果为true，反之为false，
③ 非 not(!)，如果连接的条件本身为false，结果为true，反之为false

案例1：查询工资在10000和20000之间的员工名、工资以及奖金

SELECT
		last_name,
		salary,
		commission_pct
FROM
		employees
WHERE
		salary>=10000 AND salary <=20000;

案例2：查询部门编号不是在90到110之间，或者工资高于15000的员工信息

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

③. 模糊查询
like, between and, in, is null, is not null

1）like一般和通配符搭配使用,可以判断字符型或数值型

通配符：
% 任意多个字符
_ 任意单个字符

案例1：查询员工名中包含字符a的信息

SELECT 
		*
FROM
		employees
WHERE
		last_name LIKE '%a%';  # a前边后边都可能有字，%通配符

案例2：查询员工名中第三个字符为n，第五个字符为l的员工名和工资

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

案例3：查询出员工名中第二个字符为_的员工名

SELECT
		last_name
FROM
		employees
WHERE
		last_name LIKE '_\_%';  # 或者随意指定转义字符
		# last_name like '_$_%' escape '$';

案例4：查询数值型,部门编号为100-199的（100多的）

SELECT *
FROM employees 
WHERE department_id LIKE '1__';

2）between and
a. 使用between and 可以提高语句的简洁度
b. 包含临界值
c. between 小数 and 大数

案例1：查询员工编号在100到120之间的员工信息

SELECT
		*
FROM
		employees
WHERE
		employee_id >=100 AND employee_id <= 120;

或者使用between and

SELECT
		*
FROM
		employees
WHERE
		employee_id BETWEEN 100 AND 120;

3）in
含义：判断某字段的值是否属于in列表中的某一项
特点：
a. 使用in提高语句简洁度
b. in列表中的值类型必须统一或兼容('123’可以转换为123)
c. 不支持通配符

案例：查询员工的工种编号是 IT_PROG、AD_VP、AD_PRES中的一个员工名和工种编号

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

4）is null
=或者<>不能用于判断null值
is null 或 is not null 可以判断null值

案例1：查询没有奖金的员工名和奖金率

SELECT
		last_name,
		commission_pct
FROM
		employees
WHERE
		commission_pct IS NULL;
		# 有奖金 commission_pct is not null;

安全等于：<=>

可以查询null值；
可以查询数值

案例1：查询没有奖金的员工名和奖金率

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

案例2：查询工资为12000的员工信息

SELECT 
		*
FROM
		employees
WHERE
		salary <=> 12000;

比较is null 和 <=>:
is null ：仅仅可以判断null值，可读性较高，建议使用
<=>: 既可以判断null值，又可以判断普通的数值，可读性较差

小练习

例1：查询员工号为176的员工名和部门号和年薪

SELECT
		last_name,
		department_id,
		salary*12*(1+IFNULL(commission_pct,0)) AS 年薪
FROM
		employees
WHERE
		employee_id = 176;

例2：查询没有奖金，且工资小于18000的salary, last_name

SELECT
		salary,
		last_name
FROM 
		employees
WHERE
		commission_pct IS NULL AND salary <18000;

例3：查询employees表中，job_id不为’IT’ 或者工资为12000的员工信息

SELECT
		*
FROM
		employees
WHERE
		job_id <>  'IT' OR salary = 12000;  # 不能用is not,只有is not null

例4：查询部门departments表的结构
DESC departments;

例5：查询部门departments表中设计到了哪些位置编号

SELECT
		DISTINCT location_id
FROM
		departments;

例6:经典面试题：

试问，select * from employees; 和
select * from employees where commission_pct like '%%' and last_name like '%%'结果是否一样，并说明原因

不一样！如果判断的字段有null值，不一样；没有null值，一样

与select * from employees where commission_pct like '%%' or last_name like '%%' or ...(所有)一样，因为总归有一个不为null

排序查询

排序可直接在结果表格中点击表头实现

排序查询语法：

select  查询列表  3
from  表  1
[where 筛选条件]  2
order by 排序列表 [asc升序(默认)|desc降序]  4

特点：

order by 字句中可以支持单个字段、多个字段、表达式、函数、别名
order by 字句一般放在查询语句的最后面，limit字句除外

案例1：查询员工信息，要求工资从高到底
SELECT * FROM employees ORDER BY salary DESC;

案例2【添加筛选条件】：查询部门编号>=90的员工信息，按入职时间先后排序

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

案例3【按表达式排序】：按年薪的高低显示员工的信息和年薪

SELECT *, salary*12*(1+IFNULL(commission_pct,0)) AS 年薪
FROM employees
ORDER BY salary*12*(1+IFNULL(commission_pct,0)) DESC;

案例4【按别名排序】：按年薪的高低显示员工的信息和年薪

SELECT *, salary*12*(1+IFNULL(commission_pct,0)) AS 年薪
FROM employees
ORDER BY 年薪 DESC;

案例5【按函数排序】：按姓名的长度显示员工的姓名和工资

SELECT LENGTH(last_name) 字节长度, last_name , salary
FROM employees
ORDER BY LENGTH(last_name) DESC;

案例6：【按多个字段排序】查询员工信息，要求先按工资排序，再按员工编号排序

SELECT *
FROM employees
ORDER BY salary ASC,employee_id DESC;

小练习

查询员工的姓名和部门号和年薪，按年薪降序按姓名升序

SELECT last_name, department_id, salary*12*(1+IFNULL(commission_pct,0)) AS 年薪
FROM employees
ORDER BY 年薪 DESC, last_name ASC;

选择工资不在8000到17000的员工的姓名和工资，按工资降序

SELECT last_name,salary
FROM employees
WHERE salary NOT BETWEEN 8000 AND 17000
ORDER BY salary DESC;

查询邮箱中包含e的员工信息，并先按邮箱的字节数降序，再按部门号升序

SELECT *
FROM employees
WHERE email LIKE '%e%'
ORDER BY LENGTH(email) DESC, department_id ASC;

常见函数

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

好处：

隐藏了实现细节
提高代码的重要性

调用：select 函数名(实参列表) [from 表];

特点：

叫什么（函数名）
干什么（函数功能）

分类：

单行函数
如concat, length, ifnull等
分组函数
功能：做统计使用，又称为统计函数、聚合函数、组函数

单行函数

一. 字符函数

length 获取参数值的字节个数
SELECT LENGTH('john'); 返回4
SELECT LENGTH('张三丰hahaha'); 返回15

查看客户端使用的字符集SHOW VARIABLES LIKE '%char%'; client为utf-8，默认英文字母一个字节，一个汉字三个字节
若为gbk，一个汉字两个字节

concat 拼接字符
SELECT CONCAT(last_name, '_', first_name) FROM employees;
upper, lower 变大小写
SELECT UPPER('john');

示例：将姓变大写，名变小写，然后拼接
SELECT CONCAT(UPPER(last_name), LOWER(first_name)) 姓名 FROM employees;

substr、substring 截取字符串
MySQL索引从1开始
①截取从指定索引处后边所有的字符：
SELECT SUBSTR('李莫愁爱上了陆展元', 7) out_put; 输出：陆展元
②截取从指定索引处指定字符长度的字符：
SELECT SUBSTR('李莫愁爱上了陆展元', 1,3) out_put;输出：李莫愁

案例：姓名中首字符大写，其他字符小写，用下划线_拼接
SELECT CONCAT(UPPER(SUBSTR(last_name,1,1)), '_', LOWER(SUBSTR(last_name,2))) out_put FROM employees;

instr 返回子串第一次出现的索引，如果找不到返回0
SELECT INSTR('杨不悔爱上了殷六侠', '殷六侠') AS out_up; 返回7
trim
去掉前后空格SELECT LENGTH(TRIM(' 张翠山 ')) AS out_put; 返回9
去掉前后的a SELECT TRIM('a' FROM 'aaaaa张aaaaa翠aaaa山aaaaaa') AS out_put; 返回张aaaaa翠aaaa山
lpad 用指定的字符实现左填充指定长度
SELECT LPAD('殷素素', 10, '*') AS out_put;*******殷素素
SELECT LPAD('殷素素', 2, '*') AS out_put; 殷素
rpad 用指定的字符实现右填充指定长度
SELECT RPAD('殷素素', 10, '*') AS out_put;殷素素*******
replace 替换
SELECT REPLACE('周芷若周芷若张无忌爱上了周芷若', '周芷若','赵敏') AS out_put;赵敏赵敏张无忌爱上了赵敏

二. 数学函数

round 四舍五入
SELECT ROUND(-1.45); 返回-1
SELECT ROUND(-1.65); 返回-2（绝对值四舍五入，再加上正负号）
SELECT ROUND(1.567,2); 返回1.57 小数点后保留两位
ceil 向上取整，返回>=该参数的最小整数
SELECT CEIL(1.52); 返回2
SELECT CEIL(1.00); 返回1
floor 向下取整，返回<=该参数的最大整数
SELECT FLOOR(-9.99); 返回-10
truncate 截断
SELECT TRUNCATE(1.65,1); 返回1.6 小数点后保留一位
mod 取余
MOD(a,b) : a- a/b *a
mod(-10,-3): -10-(-10)/(-3) * (-3)=-1
SELECT MOD(10,3); 返回1
相当于：SELECT 10%3;
rand获取随机数，返回0-1之间的小数

三. 日期函数

now 返回当前系统日期+时间
SELECT NOW();
curdate 返回当前系统日期，不包含时间
SELECT CURDATE();
curtime 返回当前时间，不包含日期
SELECT CURTIME();
可以获取指定的部分，年、月、日、小时、分钟、秒
SELECT YEAR(NOW()) 年;
SELECT YEAR('1998-1-1') 年；
SELECT YEAR(hiredate) 年 FROM employees;
SELECT MONTH(NOW()) 月；
SELECT MONTHNAME(NOW()) 月; 返回October
day, minute, second,
str_to_date 将日期格式的字符转换成指定格式的日期
SELECT STR_TO_DATE('1998-3-2', '%Y-%c-%d') out_put; 返回1998-03-02

查询入职日期为1992年4月3号的员工信息：
SELECT * FROM employees WHERE hiredate = STR_TO_DATE('4-3 1992', '%c-%d %Y');

格式符	功能
%Y	4位的年份
%y	2位的年份
%m	月份(01,02,…,12)
%c	月份(1,2,…,12)
%d	日(01,02,…)
%H	小时(24小时制)
%h	小时(12小时制)
%i	分钟(00,01,…,59)
%s	秒(00,01,…,59)

date_format 将日期转换为字符
SELECT DATE_FORMAT(NOW(),'%y年%m月%d日') AS out_put; 返回20年10月18日

查询有奖金的员工名和入职日期（xx月/xx日 xx年）

SELECT last_name, DATE_FORMAT(hiredate, '%m月/%d日  %y年') 入职日期
FROM employees
WHERE commission_pct IS NOT NULL;

datediff：返回两个日期相差的天数
monthname: 以英文形式返回月

四. 其他函数

SELECT VERSION(); 查看版本号
SELECT DATABASE();查看当前数据库,返回myemployees
SELECT USER(); 查看当前用户

MD5(‘字符’) ：返回该字符的MD5加密形式（自动加密）
SELECT MD5('aaa');

五. 流程控制函数

if 函数：if else的效果
SELECT IF(10>5,'大','小');
if(条件表达式，表达式1，表达式2)：如果条件表达式成立，返回表达式1，否则返回表达式2

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

case 函数的使用一：类似于switch case

语法：

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

案例：查询员工的工资，要求
部门号=30，显示的工资为1.1倍
部门号=40，显示的工资为1.2倍
部门号=50，显示的工资为1.3倍
其他部门，显示的工资为原工资

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 新工资
FROM employees;

case 函数的使用二：类似于多重if

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

案例：查询员工的工资情况
如果工资>20000,显示A级别
如果工资>15000,显示B级别
如果工资>10000,显示C级别
否则, 显示D级别

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

小练习

查询员工名，姓名，工资，以及工资提高20%后的结果（new salary）

SELECT employee_id, last_name, salary*(1+0.2) AS "new salary"
FROM employees;

将员工的姓名按首字母排序，并写出姓名的长度（length）

SELECT last_name,LENGTH(last_name), SUBSTR(last_name,1,1) AS 首字符
FROM employees
ORDER BY 首字符;

如果order by last_name 结果会不一样，按字典顺序排

做一个查询，产生下面的结果

<last_name> earns <salary> monthly but wants <salary*3>
Dream Salary
King earns 24000 monthly but wants 72000

SELECT CONCAT(last_name, ' earns ', salary, ' monthly but wants ', salary*3) AS "Dream Salary"
FROM employees
WHERE salary = 24000;

使用case_when，按照下面的条件

job        grade
AD_PRES    A
ST_MAN     B
IT_PROG    C

SELECT last_name,job_id,
CASE job_id
WHEN 'AD_PRES' THEN 'A'
WHEN 'ST_MAN' THEN 'B'
WHEN 'IT_PROG' THEN 'C'
WHEN 'SA_REP' THEN 'D'
WHEN 'ST_CLERK' THEN 'E'
END AS Grade
FROM employees
WHERE job_id = 'AD_PRES';

分组函数

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

分类：
sum 求和、avg 平均值、max 最大值、min 最小值、count 计数

简单的使用

SELECT SUM(salary) FROM employees;
SELECT AVG(salary) FROM employees;
SELECT MIN(salary) FROM employees;
SELECT MAX(salary) FROM employees;
SELECT COUNT(salary) FROM employees;

SELECT SUM(salary) 和, AVG(salary) 平均, MAX(salary) 最高, MIN(salary) 最低, COUNT(salary) 个数
FROM employees;

SELECT ROUND(AVG(salary),2) 平均 FROM employees; round,四舍五入保留两位小数

参数支持哪些类型

sum和avg一般用于处理数值型
max、min、count可以处理任意类型（支持字符型，日期型）

count计算的是非空的个数

以上分组函数都忽略null值
都可与和distinct搭配（去重）

SELECT SUM(DISTINCT(salary)), SUM(salary) FROM employees;

SELECT COUNT(DISTINCT(salary)) , COUNT(salary) FROM employees;

count函数

SELECT COUNT(*) FROM employees; 统计行数（此行有一个数据不为null，计数+1）
SELECT COUNT(1) FROM employees; 统计行数（在前边加上一个全1列，计算1的个数，有几个1就有多少行）

效率：
MyISAM存储引擎下，count(*)效率高；
InnoDB存储引擎下，count(*)和count(1)的效率差不多，比count(字段)要高

主要用count(*)

和分组函数一同查询的字段有限制

和分组函数一同查询的字段要求是group by 后的字段

小练习

查询公司员工工资的最大值，最小值，平均值，求和

SELECT MAX(salary) 最大值, MIN(salary) 最小值, AVG(salary) 平均值, SUM(salary) 求和
FROM employees;

查询员工表中的最大入职时间和最小入职时间的相差天数（difference）

SELECT DATEDIFF(MAX(hiredate), MIN(hiredate)) DIFFERENCE FROM employees;

查询部门编号为90的员工个数

SELECT COUNT(*)
FROM employees
WHERE department_id = 90;

分组查询

语法：

select 分组函数，分组后的字段（要求出现在group by的后面） 5
from 表  1
【where 筛选条件】  2
group by 分组后的字段  3
【having 分组后的筛选】 4
【order by 子句】 6

注意：

查询列表必须特殊，要求是分组函数和group by后出现的字段

特点：

分组查询中的筛选条件分为两类：

分组前筛选：数据源为原始表；group by子句的前面；使用关键字where
分组后筛选：数据源为分组后的结果集；group by 子句的后面；使用关键字having

① 分组函数做条件肯定是放在having子句中
② 能用分组前筛选的，就优先考虑使用分组前筛选

group by 子句支持单个字段分组，多个字段分组（多个字段之间用逗号隔开，没有顺序要求），表达式或函数（用得较少）
也可以添加排序（排序放在整个分组查询的最后）

简单的分组查询：

案例1：查询每个工种的最高工资

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

案例2：查询每个位置上的部门个数

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

添加筛选条件：

案例1：查询邮箱中包含a字符的，每个部门的平均工资

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

案例2：查询每个领导手下有奖金的员工的最高工资

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

添加分组后的筛选条件：

案例1：查询哪个部门的员工个数>2
① 查询每个部门的员工个数

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

② 根据①的结果进行筛选，查询哪个部门的员工个数>2（关键字having）

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

案例2：查询每个工种有奖金的员工的最高工资>12000的工种编号和最高工资

① 查询每个工种有奖金的员工的最高工资

SELECT MAX(salary), job_id
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY job_id;

② 根据①的结果继续筛选，最高工资>12000

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

案例3：查询领导编号>102的每个领导手下最低工资>5000的领导编号是哪个，以及其最低工资

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

补充：
where VS having
where 语句在group by 语句之前；在分组之前计算
having 语句在group by 语句之后；在分组之后计算

按表达式或函数分组：

案例1：按员工姓名的长度分组，查询每一组的员工个数，筛选员工个数>5的有哪些

① 查询每个姓名长度的员工个数

SELECT COUNT(*),LENGTH(last_name) len_name
FROM employees
GROUP BY LENGTH(last_name);

② 添加筛选条件

SELECT COUNT(*), LENGTH(last_name) len_name
FROM employees
GROUP BY LENGTH(last_name)
HAVING COUNT(*) > 5;

group by 和 having 子句后边都支持别名，但where后不支持

按多个字段分组

案例：查询每个部门每个工种的员工的平均工资

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

添加排序：

案例：查询每个部门每个工种的员工的平均工资，并且按平均工资的高低显示

SELECT AVG(salary) a, department_id, job_id
FROM employees
WHERE department_id IS NOT NULL
GROUP BY department_id, job_id
HAVING a > 10000
ORDER BY a DESC;

小练习

案例1：查询个job_id的员工工资的最大值、最小值、平均值、综合，并按job_id升序

SELECT MAX(salary) 最大, MIN(salary) 最小, AVG(salary) 平均, SUM(salary) 总和, job_id
FROM employees
GROUP BY job_id
ORDER BY job_id ASC;

案例2：查询员工最高工资和最低工资的差距，difference

SELECT MAX(salary)-MIN(salary) DIFFERENCE
FROM EMPLOYEES;

案例3：查询各个管理者手下员工的最低工资，其中最低工资不得低于6000，没有管理者的员工不计算在内

SELECT MIN(salary), manager_id
FROM employees
WHERE manager_id IS NOT NULL
GROUP BY manager_id
HAVING MIN(salary)>=6000;

案例4：查询所有部门的编号，员工数量和工资平均值，并按平均工资降序

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

案例5：选择具有各个job_id的员工个数

SELECT COUNT(*), job_id 
FROM employees
GROUP BY job_id;

连接查询

含义：又称多表查询，当查询的字段来自与多个表时，就会用到连接查询。

select 字段1, 字段2
from 表1, 表2, ... ;

笛卡尔乘积现象：当查询多个表时，没有添加有效的连接条件，导致多个表所有的行实现完全连接
表1 有m行，表2 有n行，结果有m*n行

如何解决：添加有效的连接条件

连接查询分类：

按年代分类：
sql92标准：仅仅支持内连接，也支持一部分外连接（用于Oracle、SqlServer，MySQL不支持）
sql99标准【推荐】：支持内连接+外连接（左外和右外）+交叉连接

按功能分类：
内连接：等值连接、非等值连接、自连接
外连接：左外链接、右外连接、全外连接（MySQL不支持）
交叉链接

sql92标准

等值连接

语法：

select 查询列表
from 表1 别名, 表2 别名
where 表1.key=表2.key
【and 筛选条件】
【group by 分组字段】
【having 分组后的筛选】
【order by 排序字段】

案例1：查询女神名和对应的男神名

要使用girls库，右键root@localhost>>执行sql脚本，选择girls库的位置

SELECT NAME, boyName 
FROM boys, beauty
WHERE beauty.boyfriend_id = boys.id;

案例2：查询员工名和对应的部门名

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

为表起别名

优点：
①可提高语句的简洁度；
②区分多个重名的字段

注意：如果为表起了别名，则查询的字段就不能使用原来的表名去限定

案例：查询员工名、工种号、工种名

SELECT last_name, e.`job_id`, j.`job_title`  # 3
FROM employees AS e, jobs AS j  # 1
WHERE e.`job_id`=j.`job_id`;  # 2

两个表的顺序可以调换

上边案例这样写也可以

SELECT e.`last_name`, e.`job_id`, j.`job_title`  
FROM jobs AS j, employees AS e
WHERE e.`job_id`=j.`job_id`;

可以加筛选

案例1：查询有奖金的员工名、部门名

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

案例2：查询城市名中第二个字符为o的部门名和城市名

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

可以加分组

案例1：查询每个城市的部门个数

SELECT COUNT(*) 个数, city
FROM departments d, locations l
WHERE d.`location_id` = l.`location_id`
GROUP BY city;

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

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

可以加排序

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

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

可以实现三表连接

案例：查询员工名、部门名和所在的城市

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

再加一个条件city以s开头，加排序

SELECT last_name, department_name, city
FROM employees e, departments d, locations l
WHERE e.`department_id` = d.`department_id`
AND d.`location_id` = l.`location_id`
AND city LIKE 's%'
ORDER BY department_name DESC;

小结

多表等值连接的结果为多表的交集部分
n表连接，至少需要n-1个连接条件
多表的顺序没有需求
一般需要为表起别名
可以搭配前面介绍的所有子句使用，比如排序、分组、筛选

非等值连接

语法：

select 查询列表
from 表1 别名, 表2 别名
where 非等值的连接条件
【and 筛选条件】
【group by 分组字段】
【having 分组后的筛选】
【order by 排序字段】

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

工资级别先执行：

CREATE TABLE job_grades
(grade_level VARCHAR(3),
 lowest_sal  int,
 highest_sal int);

INSERT INTO job_grades
VALUES ('A', 1000, 2999);

INSERT INTO job_grades
VALUES ('B', 3000, 5999);

INSERT INTO job_grades
VALUES('C', 6000, 9999);

INSERT INTO job_grades
VALUES('D', 10000, 14999);

INSERT INTO job_grades
VALUES('E', 15000, 24999);

INSERT INTO job_grades
VALUES('F', 25000, 40000);

注意别把注释粘贴进去，运行后右键root@localhost刷新对象浏览器

可以再加筛选

SELECT salary,grade_level
FROM employees e, job_grades g
WHERE salary BETWEEN g.`lowest_sal` AND g.`highest_sal`
AND g.`grade_level` = 'A';

自连接

语法：

select 查询列表
from 表 别名1, 表 别名2
where 等值连接条件
【and 筛选条件】
【group by 分组字段】
【having 分组后的筛选】
【order by 排序字段】

案例：查询员工名和上级名称

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

练习

一、显示员工表的最大工资，工资平均值

SELECT MAX(salary), AVG(salary) FROM employees;

二、查询员工表的employee_id, job_id, last_name, 按department_id降序，salary升序

SELECT employee_id, job_id, last_name
FROM employees
ORDER BY department_id DESC, salary ASC;

三、查询员工表的job_id中包含a 和e 的，并且a在e前面

SELECT job_id FROM employees WHERE job_id LIKE '%a%e%';

四、已知表student里面有id(学号)，name, gradeId(年级编号)
已知表grade，里面有id(年级编号),name(年级名)
已知表result, 里面有id, score, studentNo(学号)
要求查询姓名、年级名、成绩

SELECT `name`, `name`,score
FROM student s, grade g, result r
WHERE s.`id` = r.`studentNo` AND s.`gradeId` = g.`id`

五、显示当前日期，以及去前后空格，截取子字符串的函数

select now();
select trim(字符 from '');
select substr(str,startIndex);
select substr(str, strInsex,length);

一、显示所有员工的姓名，部门号和部门名称

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

二、查询90号部门员工的 job_id 和90号部门的 location_id

SELECT job_id, location_id
FROM employees e, departments d
WHERE e.department_id= d.department_id 
AND e.department_id = 90;

三、选择所有有奖金的员工的last_name, department_id, city

SELECT last_name, e.department_id, city
FROM employees e, departments d, locations l
WHERE e.department_id = d.department_id AND d.location_id = l.location_id
AND e.commission_pct IS NOT NULL;

四、选择city在Toronto工作的员工的last_name, job_id, department_id, department_name

SELECT last_name, job_id, e.department_id, department_name
FROM employees e, departments d, locations l
WHERE e.department_id=d.department_id AND d.location_id = l.location_id
AND city = 'Toronto';

五、查询每个工种、每个部门的部门名、工种名和最低工资

SELECT job_title, department_name, MIN(salary) 最低工资
FROM employees e, departments d, jobs j
WHERE e.department_id = d.department_id AND e.job_id = j.job_id
GROUP BY job_title, department_name;

六、查询每个国家下的部门个数大于2的国家编号

SELECT country_id,COUNT(*) 部门个数
FROM locations l, departments d
WHERE l.location_id = d.location_id
GROUP BY country_id
HAVING COUNT(*) >2;

两个表连接后department_id 是无重复的，则可以用count(*)来统计部门个数
departments表中本来department_id 就没有重复的

七、选择指定员工的姓名，员工号，以及他的管理者的姓名和员工号，结果类似于下表

employees	Emp	manager	Mgr
kochhar	101	king	100

SELECT e.last_name AS employees, e.employee_id AS "Emp#", m.last_name AS manager, m.employee_id AS "Mgr#"
FROM employees e, employees m
WHERE e.manager_id = m.employee_id
AND e.last_name="kochhar";