目录
05、DQL语句的学习
DQL就是数据查询语言，数据库执行DQL语句不会对数据进行改变，而是让数据库发送结果集给客户端
- 终极语法：
SELECT
selection_list /*要查询的列名称*/
FROM
table_list /*要查询的表名称*/
WHERE
condition /*行条件*/
GROUP BY
grouping_columns /*对结果分组*/
HAVING
condition /*分组后的行条件*/
ORDER BY
sorting_columns /*对结果分组*/
LIMIT
offset_start, row_count /*结果限定*/
SQL语句的执行顺序：
FROM -> ON -> JOIN -> WHERE -> GROUP BY -> HAVING -> SELECT -> DISTINCT -> UNION -> ORDER BY -> LIMIT
5.1、基础查询：SELECT…FROM…
语法：
SELECT 查询列表 FROM 表名;
特点：
- 1、通过select查询完的结果 ，是一个虚拟的表格，不是真实存在
- 2、查询的东西可以是常量值、可以是表达式、可以是字段、可以是函数
案例：
- 1.查询表中的单个字段
SELECT last_name FROM employees;
- 2.查询表中的多个字段
SELECT last_name,salary,email FROM employees;
- 3.查询表中的所有字段
SELECT
`employee_id`,
`first_name`,
`last_name`,
`phone_number`,
`last_name`,
`job_id`,
`phone_number`,
`job_id`,
`salary`,
`commission_pct`,
`manager_id`,
`department_id`,
`hiredate`
FROM
employees ;
SELECT * FROM employees;
- 4.查询常量值
SELECT 100;
SELECT 'aismall';
- 5.查询表达式
SELECT 10%8;
- 6.查询函数
SELECT VERSION();
- 7.起别名（重点）
为了便于理解，可以对查询的字段进行重新命名，即使用别名
方式一：使用as
SELECT last_name AS xing,first_name AS ming FROM employees;
方式二：使用空格
SELECT last_name xing,first_name ming FROM employees;
- 8.去重：使用关键字（
DISTINCT）
SELECT DISTINCT department_id FROM employees;
- 9.
+号的作用
java中的+号：
1、运算符，两个操作数都为数值型
2、连接符，只要有一个操作数为字符串
mysql中的+号：
1、仅仅只有一个功能：运算符
//两个操作数都为数值型，则做加法运算
select 100+90;
//只要其中一方为字符型，试图将字符型数值转换成数值型
//如果转换成功，则继续做加法运算
select '123'+90;
//如果转换失败，则将字符型数值转换成0
select 'john'+90;
//只要其中一方为null，则结果肯定为null
select null+10;
5.2、条件查询：SELECT…FROM…WHERE…
条件查询：根据条件过滤原始表的数据，查询到想要的数据
语法：
SELECT
要查询的字段|表达式|常量值|函数
FROM
表
WHERE
条件 ;
分类一：条件表达式
示例：salary>10000
- 条件运算符：
大于：>
小于：<
等于：=
大于等于 >=
小于等于：<=
不等于：!= 或者 <>
- 案例1：查询工资>12000的员工信息
SELECT * FROM employees WHERE salary>12000;
- 案例2：查询
部门编号不等于90号的员工名和部门编号
SELECT
last_name,
department_id
FROM
employees
WHERE
department_id<>90;
分类二：逻辑表达式
示例：salary>10000 && salary<20000
- 逻辑运算符：
and（&&）:两个条件如果同时成立，结果为true，否则为false
or(||)：两个条件只要有一个成立，结果为true，否则为false
not(!)：如果条件成立，则not后为false，否则为true
- 案例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
NOT(department_id>=90 AND department_id<=110) OR salary>15000;
分类三：模糊查询
示例：last_name like ‘a%’
- 模糊查询关键字：
like：一般和通配符搭配使用
通配符：%任意多个字符,包含0个字符，_任意单个字符
between and：
1、使用between and 可以提高语句的简洁度
2、包含临界值
3、两个临界值不要调换顺序
in：判断某字段的值是否属于in列表中的某一项，类比
或运算符
is null 和 is not null：=或<>不能用于判断null值，is null或is not null 可以判断null值
- 案例1：查询员工名中
包含字符a的员工信息
SELECT
*
FROM
employees
WHERE
last_name LIKE '%a%';
案例2：查询员工编号在100到120之间的员工信息
SELECT
*
FROM
employees
WHERE
employee_id BETWEEN 120 AND 100;
- 案例3：查询员工的工种编号是 IT_PROG、AD_VP、AD_PRES中的一个员工名和工种编号
SELECT
last_name,
job_id
FROM
employees
WHERE
job_id IN( 'IT_PROT' ,'AD_VP','AD_PRES');
- 案例4：查询没有奖金的员工名和奖金率
SELECT
last_name,
commission_pct
FROM
employees
WHERE
commission_pct IS NULL;
总结：
结合条件表达式，我们可以进行精准的查询
注意：is null 和 <=> 的区别
-
安全等于 ：<=>
-
案例：查询没有奖金的员工名和奖金率
SELECT
last_name,
commission_pct
FROM
employees
WHERE
commission_pct <=> NULL;
IS NULL：仅仅可以判断NULL值，可读性较高，建议使用<=>：既可以判断NULL值，又可以判断普通的数值，可读性较低
5.3、排序查询：SELECT…FROM…WHERE…ORDER BY…
语法：
SELECT
要查询的东西
FROM
表
WHERE
条件
ORDER BY 排序的字段|表达式|函数|别名 ASC|DESC
特点：
- ASC代表的是升序，可以省略，系统默认
- DESC代表的是降序，不可省略
- ORDER BY子句：支持，单个字段、别名、表达式、函数、多个字段
- ORDER BY子句：在查询语句的最后面，除了limit子句
案例：
- 1、按单个字段排序：查询的结果按照salary字段排序
SELECT * FROM employees ORDER BY salary DESC;
- 2、按表达式排序：查询员工信息 按年薪降序
SELECT *,salary*12*(1+IFNULL(commission_pct,0))
FROM employees
ORDER BY salary*12*(1+IFNULL(commission_pct,0)) DESC;
IFNULL(commission_pct,0):如果字段值为null使用0代替
- 3、按别名排序：查询员工信息按年薪升序
SELECT *,salary*12*(1+IFNULL(commission_pct,0)) 年薪
FROM employees
ORDER BY 年薪 ASC;
- 4、按函数排序：查询员工名，并且按名字的长度降序
SELECT LENGTH(last_name),last_name
FROM employees
ORDER BY LENGTH(last_name) DESC;
- 5、按多个字段排序：查询员工信息，要求先按工资降序，再按employee_id升序
SELECT *
FROM employees
ORDER BY salary DESC,employee_id ASC;
5.4、常见函数
类似于java的方法，将一组逻辑语句封装在方法体中，对外暴露方法名
好处：
- 1、隐藏了实现细节
- 2、提高代码的重用性
调用：
SELECT 函数名(实参列表) FROM 表;
分类：
- 单行函数：如 concat、length、ifnull等
- 分组函数：做统计使用，又称为统计函数、聚合函数、组函数
单行函数
字符函数
concat：拼接
substr：截取子串
upper：转换成大写
lower：转换成小写
trim：去前后指定的空格和字符
ltrim：去左边空格
rtrim：去右边空格
replace：替换
lpad：左填充
rpad：右填充
instr：返回子串第一次出现的索引
length： 获取字节个数
SELECT LENGTH('aismall');
SELECT CONCAT(last_name,'_',first_name) 姓名 FROM employees;
SELECT UPPER('aismall');
SELECT LOWER('AISMALL');
//将姓变大写，名变小写，然后拼接
SELECT CONCAT(UPPER(last_name),LOWER(first_name)) 姓名 FROM employees;
//注意：索引从1开始
//截取从指定索引处后面所有字符
SELECT SUBSTR('java天下无敌',7) out_put;
//截取从指定索引处指定字符长度的字符
SELECT SUBSTR('java天下无敌',1,4) out_put;
//姓名中首字符大写，其他字符小写然后用_拼接，显示出来
SELECT CONCAT(UPPER(SUBSTR(last_name,1,1)),'_',LOWER(SUBSTR(last_name,2))) out_put
FROM employees;
//instr返回子串第一次出现的索引，如果找不到返回0
SELECT INSTR('java天下无敌','java') AS out_put;
SELECT LENGTH(TRIM(' aismall ')) AS out_put;
SELECT TRIM('aa' FROM 'aaaaJaaAaaVaaaAa') AS out_put;
//用指定的字符实现左填充指定长度
SELECT LPAD('天下无敌',10,'java') AS out_put;
//用指定的字符实现右填充指定长度
SELECT RPAD('天下无敌',12,'java') AS out_put;
//替换
SELECT REPLACE('JAVA天下无敌','JAVA','MySQL') AS out_put;
数学函数
round ：四舍五入
rand ：随机数
floor：向下取整
ceil：向上取整
mod：取余
truncate：截断
//四舍五入
SELECT ROUND(-1.55);
SELECT ROUND(1.567,2);
日期函数
now：当前系统日期+时间
curdate：当前系统日期
curtime：当前系统时间
str_to_date：将字符转换成日期
date_format：将日期转换成字符
//返回当前系统日期+时间
SELECT NOW();
//返回当前时间，不包含日期
SELECT CURTIME();
//可以获取指定的部分，年、月、日、小时、分钟、秒
SELECT YEAR(NOW()) 年;
SELECT YEAR('1998-1-1') 年;
SELECT MONTH(NOW()) 月;
SELECT MONTHNAME(NOW()) 月;
//将字符通过指定的格式转换成日期
SELECT STR_TO_DATE('1998-3-2','%Y-%c-%d') AS out_put;
SELECT * FROM employees WHERE hiredate = STR_TO_DATE('4-3 1992','%c-%d %Y');
//将日期转换成字符
SELECT DATE_FORMAT(NOW(),'%y年%m月%d日') AS out_put;
流程控制函数
if 处理双分支
case语句 处理多分支
情况1：处理等值判断
情况2：处理条件判断
mysql中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;
案例：查询员工的工资的情况
如果工资>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;
其他函数
version：版本
database：当前库
user：当前连接用户
SELECT VERSION();
SELECT DATABASE();
SELECT USER();
分组函数
功能：用作统计使用，又称为聚合函数或统计函数或组函数
分类：
sum：求和
max：最大值
min：最小值
avg：平均值
count：计数
特点：
- 1、sum、avg一般用于处理数值型
- 2、max、min、count可以处理任何类型
- 3、以上分组函数都忽略null值
- 4、可以和
distinct搭配实现去重的运算 - 5、
count(*)用作统计行数 - 6、和分组函数一同查询的字段要求是
group by后的字段
分组函数的简单使用
//计算薪资的总和
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;
分组函数参数支持哪些类型
//sum，avg一般用于处理数值型，对于字符和日期类型的数值不适用
//虽然求取的时候不会报错但是结果是没有意义的
SELECT SUM(last_name) ,AVG(last_name) FROM employees;
SELECT SUM(hiredate) ,AVG(hiredate) FROM employees;
//max，min，count可以处理任何类型
SELECT MAX(last_name),MIN(last_name) FROM employees;
SELECT MAX(hiredate),MIN(hiredate) FROM employees;
//会忽略null值，也就是不统计null的行
SELECT COUNT(commission_pct) FROM employees;
分组函数会忽略null值
分组函数对字段进行统计的时候，不会统计null的行
//查看commission_pct字段
SELECT commission_pct FROM employees;
//统计commission_pct字段，为null的不会被统计
SELECT COUNT(commission_pct) FROM employees;
与distinct搭配
统计salary字段：去重统计，和不去重统计
SELECT SUM(DISTINCT salary),SUM(salary) FROM employees;
SELECT COUNT(DISTINCT salary),COUNT(salary) FROM employees;
count函数的详细介绍
案例：
//统计salary字段的行数，忽略null值
SELECT COUNT(salary) FROM employees;
//统计字段，在表的一行中，只要有一个字段的值为非null，统计就会加一
//一般使用这个统计行数
SELECT COUNT(*) FROM employees;
//统计表中所有行，不忽略null值
SELECT COUNT(1) FROM employees;
效率：
- MYISAM存储引擎下 ：
COUNT(*)的效率高 - INNODB存储引擎下：
COUNT(*)和COUNT(1)的效率差不多，比COUNT(字段)要高一些
总结：
一般使用count(*)用来统计行数
和分组函数一同查询的字段有限制
注意：我们所查询的结果一般要求规则，所以下面这样的语法会报错，避免使用：
SELECT AVG(salary),employee_id FROM employees;
和分组函数一同查询的字段要求是group by后的字段
分组查询语句的使用我们接下来进行介绍：GROUP BY
5.5、分组查询：SELECT…FROM…WHERE…GROUP BY…HAVING…
在SELECT 语句中，使用GROUP BY子句进行分组后，如果需要对分组后的数据进行筛选，可以使用HANING子句指定筛选条件。
注意：
- HAVING 子句和 WHERE 子句非常相似，都是对数据进行过滤
- HAVING 子句是在数据
分组后进行过滤，WHERE 子句会在数据分组前进行过滤，WHERE 子句排除的行不包含在分组中。 - HAVING 子句支持 WHERE 子句中所有的操作符和语法
- HAVING子句支持分组函数(聚合函数)，但是WHERE子句不支持
语法：
SELECT
查询列表
FROM
表
WHERE
查询条件
GROUP BY
分组的字段
ORDER BY
排序的字段;
HAVING
筛选条件
特点：
-
1、分组可以按单个字段也可以按多个字段
-
2、和分组函数一同查询的字段要求是
group by后的字段 -
3 、筛选分为两类：分组前筛选和分组后筛选
| 筛选 | 针对的表 | 连接的关键字 |
|---|---|---|
| 分组前筛选 | 原始表 | WHERE |
| 分组后筛选 | group by后的结果集 | HAVING |
- 4、可以搭配着排序使用：ORDER BY 子句
问题：分组函数做筛选能不能放在WHERE后面
- 答：不能，因为WHERE子句不支持分组函数
问题：where——group by——having
- 一般来讲，能用分组前筛选的，尽量使用分组前筛选，提高效率
简单的分组：SELECT…FROM…GROUP BY…
案例1：查询每个工种的员工平均工资
分析：先按工种分类，然后在求取每个工种的平均工资
注意：和分组函数一同查询的字段要求是group by后的字段
SELECT AVG(salary),job_id
FROM employees
GROUP BY job_id;
- 案例2：查询每个位置的部门个数
- 分析：先按位置进行分类，然后再求取每个分类的个数
SELECT COUNT(*),location_id
FROM departments
GROUP BY location_id;
分组前的筛选：使用WHERE字句
案例1：查询邮箱中包含a字符的每个部门的最高工资
分析：先使用WHERE子句刷选，再分组，再求取薪资最高的
SELECT MAX(salary),department_id
FROM employees
WHERE email LIKE '%a%'
GROUP BY department_id;
- 案例2：查询有奖金的每个领导手下员工的平均工资
- 分析：先使用WHERE子句刷选，再分组，再求取平均工资
SELECT AVG(salary),manager_id
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY manager_id;
分组后的筛选：使用HAVING子句
案例1：查询哪个部门的员工个数>5
分析：先按照部门进行分组，再求取部门员工个数，再使用HAVING对结果进行最后的刷选
- ①查询每个部门的员工个数
SELECT COUNT(*),department_id
FROM employees
GROUP BY department_id;
- ② 筛选刚才①结果
SELECT COUNT(*),department_id
FROM employees
GROUP BY department_id
HAVING COUNT(*)>5;
案例2：每个工种有奖金的员工的最高工资>12000的工种编号和最高工资
分析：先使用WHERE子句进行刷选有奖金的员工，使用GROUP BY再按工种分类，使用HAVING 再对结果进行进一步的筛选，最后输出筛选的结果
SELECT job_id,MAX(salary)
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY job_id
HAVING MAX(salary)>12000;
案例3：领导编号>102的每个领导手下的最低工资大于8000的领导编号和最低工资
SELECT manager_id,MIN(salary)
FROM employees
WHERE manager_id>102
GROUP BY manager_id
HAVING MIN(salary)>8000;
添加排序：使用ORDER BY子句
案例：每个工种有奖金的员工的最高工资>6000的工种编号和最高工资，按最高工资升序
分析：先使用WHERE子句进行刷选有奖金的员工，使用GROUP BY再按工种分类，使用HAVING 再对结果进行进一步的筛选，使用ORDER BY对结果进行排序，最后输出筛选的结果
SELECT job_id,MAX(salary) m
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY job_id
HAVING m>6000
ORDER BY m;
按多个字段分组(重点)
- 案例：查询每个工种每个部门的最低工资,并按最低工资降序
- 分析：把一列中
job_id,department_id相同的分为一个组，举例如下：
SELECT MIN(salary),job_id,department_id
FROM employees
GROUP BY department_id,job_id
ORDER BY MIN(salary) DESC;
5.6、多表连接查询(重点)：JOIN关键字
多表连接查询：又称多表查询，当查询的字段来自于多个表时，就会用到连接查询
使用的关键字为：JION，含义就如英文单词join一样，连接两张表
注意：多表联合查询借助笛卡尔积，是从两个表中记录的笛卡尔积中选取满足连接的记录
连接查询分类：
语法：
SELECT 查询字段
FROM 表1
INNER(内连接)|LEFT OUTER(左外连接)|RIGHT OUTER(右外连接)|CROSS(交叉连接) JOIN 表2 ON 连接条件
INNER(内连接)|LEFT OUTER(左外连接)|RIGHT OUTER(右外连接)|CROSS(交叉连接) JOIN 表3 ON 连接条件
WHERE 筛选条件
GROUP BY 分组字段
HAVING 分组后的筛选条件
ORDER BY 排序的字段或表达式
sql92：内连接查询(了解)
等值连接
1、简单等值连接查询
- 案例1：查询员工名和对应的部门名
SELECT last_name,department_name
FROM employees,departments
WHERE employees.`department_id`=departments.`department_id`;
2、为表起别名
提高语句的简洁度
区分多个重名的字段
注意：如果为表起了别名，则查询的字段就不能使用原来的表名去限定
- 案例1：查询员工名、工种号、工种名
SELECT e.last_name,e.job_id,j.job_title
FROM employees e,jobs j
WHERE e.`job_id`=j.`job_id`;
3、两个表的顺序是否可以调换
- 案例1：查询员工名、工种号、工种名
SELECT e.last_name,e.job_id,j.job_title
FROM jobs j,employees e
WHERE e.`job_id`=j.`job_id`;
4、可以加筛选
- 案例1：查询有奖金的员工名、部门名
SELECT last_name,department_name,commission_pct
FROM employees e,departments d
WHERE e.`department_id`=d.`department_id`
AND e.`commission_pct` IS NOT NULL;
- 案例2：查询城市名中第二个字符为o的部门名和城市名
SELECT department_name,city
FROM departments d,locations l
WHERE d.`location_id` = l.`location_id`
AND city LIKE '_o%';
5、可以加分组
- 案例1：查询每个城市的部门个数
SELECT COUNT(*),city
FROM departments d,locations l
WHERE d.`location_id`=l.`location_id`
GROUP BY city;
- 案例2：查询有奖金的每个部门的部门名和部门的领导编号和该部门的最低工资
SELECT department_name,d.`manager_id`,MIN(salary)
FROM departments d,employees e
WHERE d.`department_id`=e.`department_id`
AND commission_pct IS NOT NULL
GROUP BY department_name,d.`manager_id`;
6、可以加排序
- 案例：查询每个工种的工种名和员工的个数，并且按员工个数降序
SELECT job_title,COUNT(*)
FROM employees e,jobs j
WHERE e.`job_id`=j.`job_id`
GROUP BY job_title
ORDER BY COUNT(*) DESC;
7、可以实现三表连接？
- 案例：查询员工名、部门名和所在的城市
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;
非等值连接
非等值连接：就是查询条件不是等于号，是其他的连接符号，例如：大于，小于等
- 案例1：查询员工的工资和工资级别
SELECT salary,grade_level
FROM employees e,job_grades g
WHERE salary BETWEEN g.`lowest_sal` AND g.`highest_sal`;
- 案例2：查询员工的工资和工资级别为A
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';
自身连接
一个表需要查询多次，自己需要连接自己，使用自连接：也就是把一张表当成多张表使用
- 案例：查询员工名和上级的名称
先找到员工名字，找到对应的领导id，通过员工id，找到领导名字
SELECT e.last_name employee_name,m.last_name manager_name
FROM employees e,employees m
WHERE e.`manager_id`=m.`employee_id`;
sql99：内连接查询(掌握)
功能：查询多表的交集
语法：
SELECT 查询列表
FROM 表1 别名
INNER JOIN 表2 别名
ON 连接条件;
分类：等值，非等值，自连接
特点：
- 添加排序、分组、筛选
- inner可以省略
- 筛选条件放在where后面，连接条件放在on后面，提高分离性，便于阅读
- inner join连接和sql92语法中的等值连接效果是一样的，都是查询多表的
交集
等值连接
- 案例1：查询员工名和所属的部门名
SELECT last_name,department_name
FROM departments d
JOIN employees e
ON e.`department_id` = d.`department_id`;
- 案例2：查询名字中包含e的员工名以及对应的工种名（
添加筛选）
SELECT last_name,job_title
FROM employees e
INNER JOIN jobs j
ON e.`job_id`= j.`job_id`
WHERE e.`last_name` LIKE '%e%';
-
案例3：查询部门个数>3的城市名以及城市中的部门个数（
添加分组+筛选） -
分析：部门个数先按城市进行分组，找到部分个数大于3的城市，显示该城市以及该城市中的部门个数
SELECT city,COUNT(*) 部门个数
FROM departments d
INNER JOIN locations l
ON d.`location_id`=l.`location_id`
GROUP BY city
HAVING COUNT(*)>3;
- 案例4：查询哪个部门的员工个数>3的部门名和员工个数，并按个数降序（
添加排序）
SELECT COUNT(*) 个数,department_name
FROM employees e
INNER JOIN departments 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 e
INNER JOIN departments d ON e.`department_id`=d.`department_id`
INNER JOIN jobs j ON e.`job_id` = j.`job_id`
ORDER BY department_name DESC;
非等值连接
- 查询员工的工资级别
SELECT salary,grade_level
FROM employees e
JOIN job_grades g
ON e.`salary` BETWEEN g.`lowest_sal` AND g.`highest_sal`;
- 查询工资级别的个数>20的个数，并且按工资级别降序
SELECT COUNT(*),grade_level
FROM employees e
JOIN job_grades g
ON e.`salary` BETWEEN g.`lowest_sal` AND g.`highest_sal`
GROUP BY grade_level
HAVING COUNT(*)>20
ORDER BY grade_level DESC;
自连接
- 查询员工的名字、上级的名字
SELECT e.last_name,m.last_name
FROM employees e
JOIN employees m
ON e.`manager_id`= m.`employee_id`;
- 查询姓名中包含字符k的员工的名字、上级的名字
SELECT e.last_name,m.last_name
FROM employees e
JOIN employees m
ON e.`manager_id`= m.`employee_id`
WHERE e.`last_name` LIKE '%k%';
自然连接
自然连接：是一种特殊的等值连接，它要求两个关系进行比较的分量必须是相同的属性组，并且在结果集中将重复属性列去掉。
举一个简单的例子，将表R和表S进行自然连接：
表R：
| A | B | C |
|---|---|---|
| a | b | c |
| b | a | d |
| c | d | e |
| d | f | g |
表S：
| A | C | D |
|---|---|---|
| a | c | d |
| d | f | g |
| b | d | g |
建表语句：
CREATE DATABASE /*!32312 IF NOT EXISTS*/`link`;
USE `link`;
DROP TABLE IF EXISTS `S`;
CREATE TABLE `S`(
`A` varchar(10) NOT NULL,
`B` varchar(10) NOT NULL,
`C` varchar(10) NOT NULL
);
insert into `S`(`A`,`B`,`C`) values ('a','b','c'),('b','a','d'),('c','d','e'),('d','f','g');
/*Table structure for table `beauty` */
DROP TABLE IF EXISTS `G`;
CREATE TABLE `G`(
`A` varchar(10) NOT NULL,
`C` varchar(10) NOT NULL,
`D` varchar(10) NOT NULL
) ;
insert into `G`(`A`,`C`,`D`) values ('a','c','d'),('d','f','g'),('b','d','g');
自然连接查询语句：
SELECT * FROM S NATURAL JOIN G;
分析：
-
首先要对两个关系中
相同属性组的分量进行相等比较，即比较R.A，R.C和S.A，S.C； -
因 表R中
A，C属性第一行元组分量a、c与表S中A，C属性第一行元组分量a、c相等，选取为结果之一 -
因表R中
A，C属性第二行元组分量b、d与表S中A，C属性第三行元组分量b、d相等，选取为结果之一 -
因 表S中A，C属性第三行元组分量d、f与表R中A，C属性各个元组分量均不等，不选取
-
其余属性不重复则保留，且保留的分量为选取的元组同组分量。
因此进行连接得到结果：
| A | B | C | D |
|---|---|---|---|
| a | b | c | d |
| b | a | d | g |
sql99：外连接查询(掌握)
用于查询一个表中有，另一个表没有的记录
特点：
-
1、外连接的查询结果为主表中的所有记录，如果从表中有和它匹配的，则显示匹配的值，如果从表中没有和它匹配的，则显示null，
外连接查询结果=内连接结果+主表中有而从表没有的记录 -
2、左外连接：
left join左边的是主表，右外连接：right join右边的是主表 -
3、左外和右外交换两个表的顺序，可以实现同样的效果
-
4、全外连接=内连接的结果+表1中有但表2没有的+表2中有但表1没有的
左外连接
- 案例1：查询哪个部门没有员工
SELECT d.*,e.employee_id
FROM departments d
LEFT OUTER JOIN employees e
ON d.`department_id` = e.`department_id`
WHERE e.`employee_id` IS NULL;
右外连接
SELECT d.*,e.employee_id
FROM employees e
RIGHT OUTER JOIN departments d
ON d.`department_id` = e.`department_id`
WHERE e.`employee_id` IS NULL;
全外连接（注意：MySQL不支持）
SELECT d.*,e.*
FROM employees e
FULL OUTER JOIN departments d
ON d.`department_id` = e.`department_id`
sql99：交叉连接查询
SELECT d.*,e.*
FROM employees e
CROSS JOIN departments d
sql92和 sql99比较
功能：sql99支持的较多
可读性：sql99实现连接条件和筛选条件的分离，可读性较高
5.7、子查询
含义：出现在其他语句中的select语句，称为子查询或内查询，外部的查询语句，称为主查询或外查询
分类：
- 按子查询出现的位置：
select后面：
仅仅支持标量子查询（结果集只有一行一列）
from后面：
支持表子查询（结果集一般为多行多列）
where或having后面:
1、标量子查询（单行子查询）
2、列子查询（一列多行子查询）
3、行子查询（一行多列）
exists后面（相关子查询）
表子查询
- 按结果集的行列数不同：
标量子查询（结果集只有一行一列）
列子查询（结果集只有一列多行）
行子查询（结果集有一行多列）
表子查询（结果集一般为多行多列）
特点：
- 1、子查询放在小括号内
- 2、子查询一般放在条件的右侧
- 3、标量子查询，一般搭配着单行操作符使用: >，< ，>=， <= ，= ，<>
- 4、列子查询，一般搭配着多行操作符使用: in，any/some，all
- 5、子查询的执行优先于主查询执行，主查询的条件用到了子查询的结果
where或having后面
标量子查询(单行子查询)：一行一列（掌握）
案例1：谁的工资比Abel高
- ①查询Abel的工资
SELECT salary
FROM employees
WHERE last_name = 'Abel'
- ②查询员工的信息，满足 salary>①结果
SELECT *
FROM employees
WHERE salary>(
SELECT salary
FROM employees
WHERE last_name = 'Abel'
);
案例2：返回job_id与141号员工相同，salary比143号员工多的员工姓名，job_id 和工资
- ①查询141号员工的job_id
SELECT job_id
FROM employees
WHERE employee_id = 141
- ②查询143号员工的salary
SELECT salary
FROM employees
WHERE employee_id = 143
- ③查询员工的姓名，job_id 和工资，要求job_id=①并且salary>②
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 MIN(salary)
FROM employees
- ②查询last_name,job_id和salary，要求salary=①
SELECT last_name,job_id,salary
FROM employees
WHERE salary=(
SELECT MIN(salary)
FROM employees
);
案例4：查询最低工资大于50号部门最低工资的部门id和其最低工资
- ①查询50号部门的最低工资
SELECT MIN(salary)
FROM employees
WHERE department_id = 50
- ②查询每个部门的最低工资
SELECT MIN(salary),department_id
FROM employees
GROUP BY department_id
- ③ 在②基础上筛选，满足min(salary)>①
SELECT MIN(salary),department_id
FROM employees
GROUP BY department_id
HAVING MIN(salary)>(
SELECT MIN(salary)
FROM employees
WHERE department_id = 50
);
案例5：非法使用标量子查询
- 分析：子查询
前面是单行操作符，所以子查询应该为标量子查询，查询的结果为一行一列，然而这个子查询查询的结果却是一列多行为列子查询，所以会报错。
SELECT MIN(salary),department_id
FROM employees
GROUP BY department_id
HAVING MIN(salary)>(
SELECT salary
FROM employees
WHERE department_id = 250
);
列子查询(多行子查询)：一列多行（掌握）
案例1：返回location_id是1400或1700的部门中的所有员工姓名
- ①查询location_id是1400或1700的部门编号
SELECT DISTINCT department_id
FROM departments
WHERE location_id IN(1400,1700)
- ②查询员工姓名，要求部门号是①列表中的某一个
SELECT last_name
FROM employees
WHERE department_id <>ALL(
SELECT DISTINCT department_id
FROM departments
WHERE location_id IN(1400,1700)
);
案例2：返回其它工种中比job_id为IT_PROG工种任一工资低的员工的员工号、姓名、job_id 以及salary
- ①查询job_id为IT_PROG部门任一工资
SELECT DISTINCT salary
FROM employees
WHERE job_id = 'IT_PROG'
- ②查询员工号、姓名、job_id 以及salary，salary<(①)的任意一个
SELECT last_name,employee_id,job_id,salary
FROM employees
WHERE salary<ANY(
SELECT DISTINCT salary
FROM employees
WHERE job_id = 'IT_PROG'
) AND job_id<>'IT_PROG';
- 或
SELECT last_name,employee_id,job_id,salary
FROM employees
WHERE salary<(
SELECT MAX(salary)
FROM employees
WHERE job_id = 'IT_PROG'
) AND job_id<>'IT_PROG';
案例3：返回其它部门中比job_id为‘IT_PROG’部门所有工资都低的员工 的员工号、姓名、job_id 以及salary
SELECT last_name,employee_id,job_id,salary
FROM employees
WHERE salary<ALL(
SELECT DISTINCT salary
FROM employees
WHERE job_id = 'IT_PROG'
) AND job_id<>'IT_PROG';
- 或
SELECT last_name,employee_id,job_id,salary
FROM employees
WHERE salary<(
SELECT MIN( salary)
FROM employees
WHERE job_id = 'IT_PROG'
) AND job_id<>'IT_PROG';
行子查询（结果集一行多列或多行多列）
案例：查询员工编号最小并且工资最高的员工信息
SELECT *
FROM employees
WHERE (employee_id,salary)=(
SELECT MIN(employee_id),MAX(salary)
FROM employees
);
- ①查询最小的员工编号
SELECT MIN(employee_id)
FROM employees;
- ②查询最高工资
SELECT MAX(salary)
FROM employees;
- ③查询员工信息
SELECT *
FROM employees
WHERE employee_id=(
SELECT MIN(employee_id)
FROM employees
)AND salary=(
SELECT MAX(salary)
FROM employees
);
select后面
案例：查询每个部门的员工个数
SELECT d.*,(
SELECT COUNT(*)
FROM employees e
WHERE e.department_id = d.`department_id`
) 个数
FROM departments d;
案例2：查询员工号=102的部门名
SELECT (
SELECT department_name,e.department_id
FROM departments d
INNER JOIN employees e
ON d.department_id=e.department_id
WHERE e.employee_id=102
) 部门名;
from后面
注意：将子查询结果充当一张表，要求必须起别名
案例：查询每个部门的平均工资的工资等级
- ①查询每个部门的平均工资
SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id
- ②连接①的结果集和job_grades表，筛选条件平均工资 between lowest_sal and highest_sal
SELECT ag_dep.*,g.`grade_level`
FROM (
SELECT AVG(salary) ag,department_id
FROM employees
GROUP BY department_id
) ag_dep
INNER JOIN job_grades g
ON ag_dep.ag BETWEEN lowest_sal AND highest_sal;
exists后面（相关子查询）
语法：
exists(完整的查询语句)
结果：
1或0
SELECT EXISTS(SELECT employee_id FROM employees WHERE salary=300000);
案例1：查询有员工的部门名
- in
SELECT department_name
FROM departments d
WHERE d.`department_id` IN(
SELECT department_id
FROM employees
);
- exists
SELECT department_name
FROM departments d
WHERE EXISTS(
SELECT *
FROM employees e
WHERE d.`department_id`=e.`department_id`
);
5.8、分页查询：LIMIT
应用场景：当要显示的数据，一页显示不全，需要分页提交sql请求，采用分页查询，本页显示多少就查询多少。
语法：
select 查询列表
from 表
join 表2
on 连接条件
where 筛选条件
group by 分组字段
having 分组后的筛选
order by 排序的字段
limit offset, size;
- offset:要显示条目的起始索引（起始索引从0开始：默认为0）
- size:要显示的条目个数
特点：
- limit语句放在查询语句的最后
公式：
- 要显示的页数 page
- 每页的条目数size
SELECT 查询列表
FROM 表
LIMIT (page-1)*size,size;
案例：
- 案例1：查询前五条员工信息
SELECT * FROM employees LIMIT 0,5;
SELECT * FROM employees LIMIT 5;
- 案例2：查询第11条—第25条：起始索引从0开始
SELECT * FROM employees LIMIT 10,15;
5.9、联合查询：UNION
UNION：联合，合并：将多条查询语句的结果合并成一个结果
语法：
查询语句1
union
查询语句2
union
...
应用场景：要查询的结果来自于多个表，且多个表没有直接的连接关系，但查询的信息一致时
特点：
- 1、要求多条查询语句的查询
列数是一致的！ - 2、要求多条查询语句的查询的每一列的类型和顺序最好一致
- 3、union关键字默认去重，如果使用union all 可以包含重复项
案例：
- 案例：查询部门编号>90或邮箱包含a的员工信息
SELECT * FROM employees WHERE email LIKE '%a%' OR department_id>90;
SELECT * FROM employees WHERE email LIKE '%a%'
UNION
SELECT * FROM employees WHERE department_id>90;
3万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
