目录
数据查询语言(data query language)
附上教程使用的SQL文件
基础查询
语法:
SELECT 查询列表 FROM 表名;
特点:
1.查询列表可以是:表中的字段、常量值、表达式、函数
2.查询的结果是一个虚拟的表格
use myemployees;
1.查询表中的单个字段
SELECT last_name from employees;
2.查询表中的多个字段
SELECT last_name,salary,email from employees;
3.查询表中的所有字段
SELECT * FROM employees;(影响性能)
# `email` 着重号代表字段,区分关键字
4.查询常量值(通常用于检测连接是否成功)
SELECT 100;
5.查询表达式
SELECT 100*2;
6.查询函数
SELECT fun();
7.起别名
1便于理解
2如果要查询的字段有重名的情况,使用别名可以区分开
方式一:
SELECT 原名 AS 新名;
SELECT last_name AS 姓,first_name AS 名 FROM employees;
方式二:
SELECT last_name 姓,first_name 名 FROM employees;(使用空格代替AS)
#案例:显示salary,显示结果为output
SELECT salary AS "out put" FROM employees;
8.去重 DISTINCT
案例:查询员工表中所涉及到的所有部门的编号
SELECT DISTINCT department_id from employees;
9.+号的作用
mysql中的+号:只有一个功能:运算符
select 90+100;两个操作数都为数值型,则做加法运算
select '123'+90;其中一方为字符型,将字符型数值转化为数值型,如果转化成功,继续做加法运算,如果转化失败,将字符数值转化为0
select NULL+ 只要其中一方为null,结果为null
concat()函数
作用:将多个字符串连接成一个字符串
没有分隔符
select concat (id,user_name,pwd) as temp from t_user;
添加分隔符
select concat (id, ',',user_name,',',pwd) as temp from t_user;
查询多个字段需要重复输入分隔符,会跟麻烦
concat_ws()函数
作用和concat()一样,将多个字符串连接成一个字符串,但是可以一次性指定分隔符
(!!!--需要注意!!!第一个参数指定分隔符。需要注意的是分隔符不能为null,如果为null,则返回结果为null。)
select concat_ws(',', id,user_name,pwd) as temp from t_user;
#案例:查询员工名和姓拼接成一个字段,并显示为姓名
SELECT CONCAT(last_name,first_name) AS 姓名 from employees;
练习
#1显示表结构
DESC departments;
#2显示表employees全部job_id(不重复)
SELECT DISTINCT job_id FROM employees;
#3显示表employees的全部列,各个列之间用逗号连接,列头显示OUT_PUT,null值用0表示
SELECT
CONCAT(
first_name,
',',
last_name,
',',
job_id,
',',
IFNULL(commission_pct,0) #判断是否为null,是则返回指定的值,否返回原有的值
) AS out_put
FROM
employees;
条件查询
语法:
select
查询列表 3
from
表名 1
where
筛选条件; 2
分类:
一、按条件表达式筛选
条件运算符:> < = !=(<>) >= <=
二、按逻辑表达式筛选
逻辑运算符:and or not
三、模糊查询
like,(NOT) between and ,in,is (not) null
一、按条件表达式查询
#案例一:查询工资>12000的员工工资
SELECT
*
FROM
employees
WHERE
salary > 12000;
#案例二:查询部门编号不等于90号的员工名和部门编号
SELECT
last_name,
department_id
FROM
employees
WHERE
department_id <> 90;
二、按逻辑表达式筛选
#案例一:查询工资在10000-20000的员工名、工资及奖金
SELECT
last_name,
salary,
commission_pct
FROM
employees
WHERE
salary > 10000
AND salary < 20000;
# between and 包含两边的边界
#案例二:查询部门编号不是在90-110之间,或者工资高于15000的员工信息
SELECT
*
FROM
employees
WHERE
salary > 15000
OR NOT (
department_id >= 90
AND department_id <= 110
);
#三、模糊查询
1.like 一般和通配符搭配使用
通配符:
%任意多个字符,包含0个字符
_任意单个字符
2.between and,
3.in,
4.is null | is not null
#1.like
#案例1:查询员工名中包含字符a的员工信息
SELECT
*
FROM
employees
WHERE
last_name LIKE '%a%';
#案例2:查询员工名中第二个字符为_的员工名
SELECT
*
FROM
employees
WHERE
last_name LIKE '_\_%'; / last_name LIKE '_$_%' ESCAPE '$';(自定义转义字符)
#2.between and
使用between and可以提高使用语句的简洁度
包含临界值
不要掉换临界值位置
#案例1:查询员工编号在100-120之间的员工信息
SELECT
last_name
FROM
employees
WHERE
department_id BETWEEN 100
AND 120;
#3.in
含义:判断某字段的值是否属于in列表中的某一项
特点:
1.使用in比or提高语句简洁度
2.in列表的值类型必须统一或兼容
#案例:查询员工的工种编号是IT_PROG、AD_VG、AD_PRES中的员工名和工种编号
SELECT
last_name,
job_id
FROM
employees
WHERE
job_id IN (
'IT_PROT',
'AD_VP',
'AD_PRES'
)
#is null
=或<>不能用于判断null值
is null或is not null仅仅可以判断null值
#案例1:查询没有(有)奖金的员工名和奖金率
SELECT
last_name,
commission_pct
FROM
employees
WHERE
commission_pct IS (NOT) NULL;
#安全等于 <=>
可以判断null值和数值
#案例:查看员工号为176的员工的年薪
SELECT
last_name,
department_id,
salary*12*(1+IFNULL(commission_pct,0)) total_sal
FROM
employees
WHERE
employee_id = 176;
#面试题
select * from employees;和select * from employees where commission like '%%' and last_name like '%%';结果是否一样?
不一样:如果判断的字段中有null
排序查询
语法:
select 查询列表
from 表
【where 筛选条件】
order by 排序列表 【asc|desc】
特点:
asc代表的是升序,desc代表的降序,默认是升序 ASC
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)) 年薪
FROM
employees
ORDER BY
(
salary * 12 * (1 + IFNULL(commission_pct, 0))
) DESC;
#案例4:按年薪的高低显示员工信息和年薪【按别名排序】
SELECT
*, salary * 12 * (1 + IFNULL(commission_pct, 0)) 年薪
FROM
employees
ORDER BY
年薪 DESC;
#案例5:按姓名的长度显示员工的姓名和工资【按函数排序】
SELECT
LENGTH(last_name) 字节长度,last_name,salary
FROM
employees
ORDER BY
LENGTH(last_name);
#案例6:查询员工信息,先按员工工资排序,再按员工编号排序【按多个字段排序】
SELECT
*
FROM
employees
ORDER BY
salary ASC,
employee_id DESC;
练习
#练习:选择工资不在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;
常见函数_单行函数
进阶4:常见函数
好处:实现了隐藏细节,提高代码的重用性
调用:select 函数名(实参列表) 【from 表】;
特点:
函数名
函数功能
分类:
单行函数,如CONCAT,LENGTH,IFNULL
分组函数 一组值返回一个值
功能:做统计使用,又称统计函数、聚合函数、组函数
一、字符函数
1.length 获取参数值的字节个数
SELECT LENGTH('JOIN');
#查看目前客户端使用的字符集,utf中字母1字节,汉子3字节
SHOW VARIABLES LIKE '%char%'
2.concat 拼接字符串
SELECT
CONCAT(last_name, '_', first_name)
FROM
employees;
3.upper、lower
#示例:将姓变大写,名变小写,然后拼接
SELECT
CONCAT(
UPPER(first_name),
'_',
LOWER(last_name)
) 姓名
FROM
employees;
4.substr、substring 截取
注意:索引从1开始
截取从指定索引处后面的所有字符
SELECT SUBSTR(str,pos)
截取从指定索引处指定字符长度的字符
SELECT SUBSTR(str,pos,len)案列:姓名中首字符大写,其他字符小写然后用_拼接
SELECT CONCAT(UPPER(SUBSTR(last_name,1,1)),'_',LOWER(SUBSTR(last_name,2))) output FROM employees;
5.INSTR
#返回子串在主串中第一次出现的索引
SELECT INSTR(12334,234)
**查询邮箱的用户名(@前面的字符)
select substr(email,1,instr(email,'@')-1)
6.TRIM 去除前面空格
SELECT TRIM(' aaa ' )
#去除前后的a
SELECT TRIM('a' FROM 'aaaaadfggfaa');
7. LPAD(str,len,padstr) 用指定的字符实现左填充指定长度
8. RPAD(str,len,padstr) 用指定的字符实现右填充指定长度
9.REPLACE 替换
SELECT `REPLACE`(str,from_str,to_str)
二、数学函数
round 四舍五入
select round(1.455);
select round(1.456,2);
ceil 向上取整
select ceil(1.01); 2
floor 向下取整
select floor(9.99) 9
truncate 截断
select truncate(1.6999,1); 1.6
mod取余
MOD(A,B) A-A/B*B 符号只和被除数有关
select MOD(10,3); 1
三、日期函数
now 返回当前系统日期时间
select now()
curdate 返回当前系统日期,不包含时间
select curdate()
curtime 返回当前时间,不包含日期
获取指定的部分,年月日时分秒
select year(now())
SELECT dayname(now()); sunday
将日期格式的字符转成指定格式的日期
STR_TO_DATE(str,format)
SELECT STR_TO_DATE('6-20-1994','%c-%d-%Y') 日期; #1994-06-20
SELECT * from employees where STR_TO_DATE(06-20-1994,'%m-%d-%Y')
将日期转成字符
DATE_FORMAT('2008/1/1','%Y年%m月%c日')
select DATE_FORMAT(NOW(),'%c-%d-%Y')
datadiff:返回两个日期相差的天数
四:其他函数
select version();当前版本
select database();当前数据库
select user();当前用户
password('字符') 返回字符的MD5加密形式
MD5('字符') 返回字符的加密形式
五:流程控制函数
1.if函数: if else 的效果
if(条件表达式,表达式1,表达式2) :如果条件表达式成立,返回表达式1,否则返回表达式2
SELECT IF(10>5,'大','小');
2.case函数
使用一 :switch case效果 #判断等值
CASE 要判断的表达式或字段;
WHEN 常量 THEN 要显示的值一或语句;
WHEN 常量 THEN 要显示的值一或语句;
...
ELSE 要显示的值或语句;
END
案例:查询员工的工资,要求
部门号=30,显示工资的1.1倍;
部门号=40,显示工资的1.2倍;
其他部门,显示的工资为原工资
SELECT
salary 原始工资,
department_id,
CASE department_id
WHEN 30 THEN
salary * 1.1
WHEN 40 THEN
salary * 1.2
ELSE
salary
END AS 新工资
FROM
employees;
3.case函数的使用二:类似于多重if #判断区间
case
when 条件1 then 要显示的值或语句
when 条件2 then 要显示的值或语句
...
else 要显示的值或语句
end
案例:查询员工的工资情况
如果工资大于2万,显示A级别
如果工资大于1.5万,显示B级别
如果工资大于1万,显示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;
常见函数_分组函数
分类:
sum,avg,max,min,count
特点:
1.sum,avg一般用于数据处理,max,min,count可以处理任何数据类型
2.以上分组函数都忽略null值
3.可以和distinct关键字搭配实现去重运算
4.count函数单独介绍
1.简单的使用
select sum(salary) from employees;
select sum(salary),round(avg(salary),2),count(salary) from employees;
2.和distinct搭配
select sum(distinct salary) from employees;
3 COUNT函数的详细介绍
count(字段):统计该字段非空值的个数
count(*) :统计结果集的行数
SELECT COUNT(*) from employees; #统计函数
SELECT COUNT(1) from employees;
效率:
MYISAM存储引擎下,COUNT(*)效率最高
INNODB存储引擎下,COUNT(*)和count(1)d的效率差不多,比count(字段)要高一些
4.和分组函数一同查询的字段要求是group by后的字段
#练习:查询员工表中最大入职时间和最小入职时间的相差天数(DATEDIFF)
SELECT DATEDIFF(MAX(hiredate),MIN(hiredate)) from employees;
进阶5:分组查询
语法:
select 分组函数,列(出现在group by后面)
from 表
【where 筛选条件】
group by 分组的列表 #体现在每个
【order by子句】
注意:查询列表必须特殊,要求是分组函数和group by后出现的字段
特点:
1.分组查询中的筛选条件分为两类
数据源不一样 位置 关键字
分组前筛选 原始表 group by子句前面 where
分组后筛选 分组后的结果集 group by子句后面 having
分组函数做条件肯定是放在having后
能用分组前筛选,优先使用分组前筛选
2.group by子句支持单个字段分组,多个字段分组(多个字段之间用逗号隔开)
3.也可以添加排序(放在整个语句最后)
1简单的分组查询:查询每个工种的最高工资
SELECT
MAX(salary),
job_id
FROM
employees
GROUP BY
job_id;
2.添加分组前的筛选条件
案例1:查询邮箱中包含a字符的,每个部门的平均工资
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;
添加分组后的晒选条件
案例1:查询哪个部门的员工个数>2
1.查询每个部门的员工个数
2.根据1的结果进行筛选,查询哪个部门的额员工个数>2
SELECT
COUNT(*),department_id
FROM
employees
where count(*)>2
GROUP BY
department_id;
#上述错误。筛选顺序不对
SELECT
COUNT(*),department_id
FROM
employees
GROUP BY
department_id
HAVING count(*)>2;
#案例2:查询每个工种有奖金的员工的最高工资>12000的工种编号和最高工资
SELECT
job_id,
MAX(salary)
FROM
employees
WHERE
commission_pct IS NOT NULL
GROUP BY
job_id
HAVING
MAX(salary) > 12000;
按函数或表达式分组
-- 案例:按员工姓名的长度分组,查询每一组员工个数,筛选员工个数>5的有哪些
SELECT
COUNT(*) c,LENGTH(last_name) len_name
FROM
employees
GROUP BY
len_name
HAVING
c>5;
按多个字段分组
-- 案例:查询每个部门每个工种的员工平均工资
SELECT
AVG(salary),
department_id,
job_id
FROM
employees
GROUP BY
department_id,
job_id;
添加排序
SELECT
AVG(salary),
department_id,
job_id
FROM
employees
GROUP BY
department_id,
job_id
ORDER BY (SALARY) DESC;
连接查询
使用的数据表资源:girls.sql
连接查询
含义:又称多表查询,当查询的字段来自于多个表,就会用到连接查询
笛卡尔乘积现象:表1有m行,表2有n行,查询得到结果有n*m行
发生原因:没有有效的连接条件
避免发生:添加有效的连接条件
分类:
按年代分类:
sql92标准:仅支持内连接
sql99标准【推荐】:支持内连接、外连接(左外和右外)、交叉连接
按功能分类:
内连接:
等值连接
非等值连接
自连接
外连接:
左外连接
右外链接
全外连接(mysql不支持)
交叉连接
一、sql92标准
1.等值连接
多表等值连接的结果为多表的交集部分
n表连接,至少需要n-1个连接条件
多表的顺序没有要求
一般需要为表起别名
可以搭配前面介绍的所有子句使用语法:
select 查询列表
from 表1 别名,表2 别名
where 表1.key = 表2.key
【and 筛选条件】
【group by 分组字段】
【having 分组后的筛选】
【order by 排序字段】
案例1.查询女和对应的男朋友
SELECT
`name`,
boyName
FROM
beauty,
boys
WHERE
boys.id = beauty.boyfriend_id;
案例2:查询员工名和对应的部门名
SELECT
last_name,
department_name
FROM
employees,
departments
WHERE
employees.department_id = departments.department_id;
2.为表起别名
提高语句的简洁度
区分多个重名的字段
注意:如果为表起了别名,则查询的字段就不能使用原来的表名去限定
案例:查询员工号、工种号、工种名
SELECT
employee_id,
e.job_id,
job_title
FROM
employees as e,jobs as j
WHERE
e.job_id = j.job_id;
3.两个表的顺序可以调换
4.可以加筛选
SELECT
last_name,
department_name,
commission_pct
FROM
employees AS e,
departments d
WHERE
e.department_id = d.department_id
AND e.commission_pct IS NOT NULL;
4.可以加分组
案例1:查询每个城市的部门个数
SELECT
count(*),
l.city
FROM
locations AS l,
departments d
WHERE
l.location_id = d.location_id
GROUP BY
l.location_id
5.可以加排序
6可以实现三表连接
2.非等值连接
select 查询列表
from 表1 别名,表2 别名
where 非等值连接的条件
【and 筛选条件】
【group by 分组字段】
【having 分组后的筛选】
【order by 排序字段】
案例一:查询员工的工资和工资级别
SELECT
salary,
g.grade_level
FROM
employees,
job_grades g
WHERE
salary BETWEEN g.lowest_sal
AND g.highest_sal;
3.自连接(一张表找了两遍)
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;
练习:
查询每个国家下的部门个数大于2的国家编号
SELECT
country_id,count(*)
FROM
departments AS d ,
locations l
WHERE d.location_id= l.location_id
GROUP BY country_id
HAVING count(*) >2;
二、sql99语法
语法:
select 查询列表
from 表1 别名 【连接类型】
join 表2 别名 on 连接条件
【where筛选条件】
【group by分组】
【having 筛选条件】
【order by排序列表】
分类:
内连接:inner
外连接:
左外连接 left[outer]
右外链接 right [outer]
全外连接(mysql不支持)full outer
交叉连接 cross
一、内连接
语法:
select 查询列表
from 表1 别名
inner join 表2 别名 on 连接条件
【where筛选条件】 【group by分组】
【having 筛选条件】
【order by排序列表】
分类:
等值
非等值
自连接
特点:inner可以省略
筛选条件放在where后,连接条件放在on后面,提高分离性便于阅读
inner join 连接效果和92语法效果一致
案例:查询员工名、部门名
SELECT
e.last_name,
d.department_name
FROM
employees e
INNER JOIN departments d ON d.department_id = e.department_id;
案例:查询名字中包含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的城市名和部门个数(分组,筛选)
SELECT
city,
count(*) c
FROM
locations l
INNER JOIN departments d ON d.location_id = l.location_id
GROUP BY
city
HAVING
c > 3;
案例:查询哪个部门的员工个数>3的部门名和员工个数,并按个数降序。
SELECT
department_name,COUNT(*) c
FROM
employees e
INNER JOIN departments d ON e.department_id = d.department_id
GROUP BY
department_name
HAVING
c > 3
ORDER BY c DESC;
案例:查询员工名、部门名、工种名,并按部门排序(多表连)
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 AS e
INNER JOIN job_grades AS j ON salary BETWEEN j.lowest_sal
AND j.highest_sal;
查询每个工资级别的个数>2的个数并按工资级别降序
SELECT
grade_level,COUNT(*) c
FROM
employees AS e
INNER JOIN job_grades AS j ON salary BETWEEN j.lowest_sal
AND j.highest_sal
GROUP BY grade_level
HAVING c >2
ORDER BY grade_level DESC;
三)自连接
查询员工的名字、上级的名字
SELECT
e.last_name,
m.last_name
FROM
employees AS e
INNER JOIN employees AS m ON e.manager_id = m.employee_id;
二:外连接
应用场景:用于查询一个表中没有,另一个表中有的记录
特点:
1.外连接的查询结果为主表中的所有记录,
如果从表中有和它匹配的,则显示匹配值,
如果从表中没有和它匹配的则显示null
外连接查询结果 = 内连接查询结果 + 主表中有而从表中没有的记录
2.左外连接,left join左边的为主表
右外连接,right join右边的为主表
3.左外和右外交换两个表的顺序可以实现同样的效果
4.全外连接 = 内连接的结果+表1中有但表二中没有的+表二中有但表1没有的
引入:查询男朋友不在boy表中的女生名
-- 左外连接
SELECT
b.`name`,
bo.*
FROM
beauty b
LEFT OUTER JOIN boys bo ON b.boyfriend_id = bo.id
WHERE
bo.id IS NULL;
-- 右外连接
SELECT
b.`name`,
bo.*
FROM
boys bo
RIGHT OUTER JOIN beauty b ON b.boyfriend_id = bo.id
WHERE
bo.id IS NULL;
案例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;
交叉连接
cross join 笛卡尔乘积
|
|
练习
-- 1.查询编号>3的女神的男朋友信息,如果有则详细列出,没有用null填充
SELECT
b.id,b.`name`,bo.boyName
from beauty b
LEFT OUTER JOIN boys bo
ON b.boyfriend_id = bo.id
WHERE b.id >3;
-- 2.查询哪个城市没有部门
SELECT
city,d.*
FROM locations l
LEFT OUTER JOIN departments d
on l.location_id = d.location_id
WHERE d.department_id is null;
-- 3.查询部门名为SAL或IT的员工信息
SELECT
e.*,department_name
FROM employees e
RIGHT OUTER JOIN departments d
on e.department_id = d.department_id
WHERE department_name in ('SAL','IT');
子查询
进阶7:子查询
含义:
出现在其他语句中的select语句,称为子查询或内查询
外部的查询语句,称为主查询或外查询
分类:
按子查询出现的位置:
select后面 :仅仅支持标量子查询
from后面:支持表子查询
where或having :标量子查询、列子查询、行子查询
exist后面(相关子查询):表子查询
按结果集的行列数不同:
标量子查询(结果集只有一行一列)
列子查询(结果集只有一列多行)
行子查询(结果集有一行多列)
表子查询(结果集一般为多行多列)
一、where或having :
1.标量子查询
2.列子查询
3.行子查询
特点:
1.子查询放在小括号内
2.子查询一般放在条件的右侧
3.标量子查询,一般搭配单行操作符使用 > < = >= <=
列子查询,一般搭配多行操作符使用,返回多行
in/not in 等于列表中的任意一个
ANY|SOME 和子查询返回的某一个值比较 a>any 只要大于里面任意一个 a>min()
ALL 和子查询返回的所有值比较 a>all 大于里面所有 a>all
any 任意、some、all所有
4.子查询的执行优先于主查询执行,主查询的条件用到了子查询的结果
1.标量子查询
案例1.谁的工资比Abel高
SELECT
last_name,salary
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
);
2.列子查询(多行子查询)
案例:返回location_id是1400或1700的部门中的所有员工姓名
SELECT
last_name
FROM
employees
WHERE
department_id IN (
SELECT
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
salary
FROM
employees
WHERE
job_id = 'IT_PROG'
)
AND job_id <> 'IT_PROG';
或者
SELECT
employee_id,
last_name,
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
employee_id,
last_name,
job_id,
salary
FROM
employees
WHERE
salary < ALL (
SELECT
salary
FROM
employees
WHERE
job_id = 'IT_PROG'
)
AND job_id <> 'IT_PROG';
或者
SELECT
employee_id,
last_name,
job_id,
salary
FROM
employees
WHERE
salary < (
SELECT
min(salary)
FROM
employees
WHERE
job_id = 'IT_PROG'
)
AND job_id <> 'IT_PROG';
3.行子查询(结果集一行多列或多行多列)
案例:查询员工编号最小且工资最高的员工信息
SELECT
*
FROM
employees
WHERE
employee_id = (
SELECT
MIN(employee_id)
FROM
employees
)
AND salary = (
SELECT
max(salary)
FROM
employees
);
上述两个判断条件都是同样的运算符号,可用行子查询改写成
SELECT
*
FROM
employees
WHERE
(employee_id, salary) = (
SELECT
MIN(employee_id),
MAX(salary)
FROM
employees
);
写法具有局限性
二、select后面
案例:查询每个部门的员工个数
SELECT
d.*, (
SELECT
count(*)
FROM
employees e
WHERE
e.department_id = d.department_id
)
FROM
departments d;
三、from后面
案例(*):查询每个部门的平均工资的工资等级
SELECT av_dep.* ,grade_level
FROM
(
SELECT
avg(salary) ag,
department_id
FROM
employees
GROUP BY
department_id
)av_dep
INNER JOIN job_grades ON av_dep.ag BETWEEN lowest_sal
AND highest_sal;
四:exist后面(相关子查询)
语法:
select exists(完整的查询语句)结果:1或0 子查询的结果有没有值
案例1:查询有员工的部门名
SELECT
department_name
FROM
departments d
WHERE
EXISTS (
SELECT
*
FROM
employees e
WHERE
d.department_id = e.department_id
);
案例2:查询没有女朋友的男神信息
SELECT
bo.*
FROM
boys bo
WHERE
NOT EXISTS (
SELECT
b.boyfriend_id
FROM
beauty b
WHERE
bo.id = boyfriend_id
);
练习
-- 1.查询和Zlotkey相同部门的员工姓名和工资
SELECT
last_name,
salary
FROM
employees
WHERE
department_id = (
SELECT
department_id
FROM
employees
WHERE
last_name = 'Zlotkey'
);
-- 法二:
SELECT
e.last_name,
e.salary
FROM
employees e
JOIN employees m
WHERE
m.last_name = 'Zlotkey'
AND e.department_id = m.department_id;
-- 案例2:查询工资比平均工资高的员工的员工号,姓名和工资
SELECT
employee_id,
last_name,
salary
FROM
employees
WHERE
salary > (
SELECT
avg(salary)
FROM
employees
);
-- 案例3:查询各部门中工资比本部门平均工资高的员工的员工号,姓名和工资
SELECT
a.employee_id,
a.last_name,
a.salary
FROM
employees a
INNER JOIN (
SELECT
avg(salary) av,
department_id
FROM
employees
GROUP BY
department_id
) b ON a.department_id = b.department_id
WHERE
a.salary > b.av;
-- *案例4:查询和姓名中包含字母u的员工在相同部门的员工的员工号和姓名
SELECT
employee_id,
last_name
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
WHERE
department_id IN (
SELECT
department_id
FROM
departments
WHERE
location_id = 1700
);
-- 6.查询工资最低的员工信息
SELECT
last_name,
salary
FROM
employees
WHERE
salary = (
SELECT
MIN(salary)
FROM
employees
);
-- 7.查询平均工资最低的部门信息
SELECT
*
FROM
(
SELECT
avg(salary) ag,
department_id
FROM
employees
GROUP BY
department_id
) ag_dep
LEFT JOIN departments d ON (
d.department_id = ag_dep.department_id
)
ORDER BY
ag_dep.ag
LIMIT 1;
-- 各部门中,最高工资中最低的那个部门的最低工资
SELECT
MIN(salary),
department_id
FROM
employees
WHERE
department_id = (
SELECT
department_id
FROM
employees
GROUP BY
department_id
ORDER BY
MAX(salary)
LIMIT 1
);
-- 查询平均工资最高的部门的manager的详细信息:last_name,department_id,email,salary.(部门领导,非员工领导)
SELECT
last_name,
e.department_id,
email,
salary
FROM
employees e
LEFT JOIN departments d ON d.manager_id = e.employee_id
WHERE
d.department_id = (
SELECT
department_id
FROM
employees
GROUP BY
department_id
ORDER BY
AVG(salary) DESC
LIMIT 1
);
分页查询
进阶8:分页查询
应用场景:当要显示的数据一页显示不全,需要分页提交sql请求
语法:
select 查询列表
from 表
[join type] join 表2
on 连接条件
where 筛选条件
having 分组后的筛选
order by 排序后的字段】
limit offset,size;
offset:要显示条码的起始索引(起始索引从0开始)
size:要显示的条目个数
特点:
1.limit语句放在查询语句的最后
2.公式:要显示的页数 page,每页的条目数size
select 查询列表 from 表 limit(page-1)*size,size;
案例1:查询前五条员工信息
select * from employee LIMIT 0,5;
select * from employee LIMIT 5; offset从0开始可省
select * from employee LIMIT 10,15; 第11-25条
案例3:有奖金的员工信息,并且工资较高的前10名显示
SELECT
*
FROM
employees
WHERE
commission_pct IS NOT NULL
ORDER BY
salary DESC
LIMIT 10;
union联合查询
进阶9:联合查询
union 联合 合并:将多条查询语句的结果合并成一个结果
语法:
查询语句1
union
查询语句2
union
...
应用场景:
要查询的结果来自于多个表且多个表没有直接的连接关系,但查询的信息列一致
特点:
1.要求多条查询语句的查询列数一致
2.要求多条查询语句的查询的每一列的类型和顺序最好一致
3.union关键字默认去重,如果使用union all可以包含重复项
总结
语法:
select 查询列表 7
from 表1 别名 1
连接类型 join 表2 2
on 连接条件 3
where 筛选 4
group by 分组列表 5
having 筛选 6
order by 排序列表 8
limit 起始条目索引,条目数 9