SQL92语法连接查询测试和复习
SQL92语法连接查询测试
#1.显示所有员工的姓名,部门号和部门名称。
USE myemployees ;
SELECT
last_name,
d.department_id,
department_name
FROM
employees e,
departments d
WHERE e.`department_id` = d.`department_id` ;
#2.查询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 ;
#3. 选择所有有奖金的员工的
last_name,
department_name,
location_id,
city
SELECT
last_name,
department_name,
l.location_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 ;
#4.选择city在Toronto工作的员工的
last_name,
job_id,
department_id,
department_name
SELECT
last_name,
job_id,
d.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' ;
#5.查询每个工种、每个部门的部门名、工种名和最低工资
SELECT
department_name,
job_title,
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 department_name,
job_title ;
#6.查询每个国家下的部门个数大于2的国家编号
SELECT
country_id,
COUNT(*) 部门个数
FROM
departments d,
locations l
WHERE d.`location_id` = l.`location_id`
GROUP BY country_id
HAVING 部门个数 > 2 ;
#7、选择指定员工的姓名,员工号,以及他的管理者的姓名和员工号,结果类似于下面的格式
employees Emp # manager Mgr#
kochhar 101 king 100
SELECT
e.last_name employees,
e.employee_id "Emp#",
m.last_name manager,
m.employee_id "Mgr#"
FROM
employees e,
employees m
WHERE e.manager_id = m.employee_id
AND e.last_name = 'kochhar' ;
复习
day2的学习内容
DQL语言学习
进阶6:连接查询(接day2)
连接查询详解
#二、sql99语法
/*
语法:
select 查询列表
from 表1 别名 【连接类型】
join 表2 别名
on 连接条件
【where 筛选条件】
【group by 分组】
【having 筛选条件】
【order by 排序列表】
分类:
内连接(★):inner
外连接
左外(★):left 【outer】
右外(★):right 【outer】
全外:full【outer】
交叉连接:cross
*/
#一)内连接
/*
语法:
select 查询列表
from 表1 别名
inner join 表2 别名
on 连接条件;
分类:
等值
非等值
自连接
特点:
①添加排序、分组、筛选
②inner可以省略
③筛选条件放在where后面,连接条件放在on后面,提高分离性,便于阅读
④inner join连接和sql92语法中的等值连接效果是一样的,都是查询多表的交集
*/
#1、等值连接
#案例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的城市名和部门个数,(添加分组+筛选)
#①查询每个城市的部门个数
#②在①结果上筛选满足条件的
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
#② 在①结果上筛选员工个数>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%' ;
#二、外连接
/*
应用场景:用于查询一个表中有,另一个表没有的记录
特点:
1、外连接的查询结果为主表中的所有记录
如果从表中有和它匹配的,则显示匹配的值
如果从表中没有和它匹配的,则显示null
外连接查询结果=内连接结果+主表中有而从表没有的记录
2、左外连接,left join左边的是主表
右外连接,right join右边的是主表
3、左外和右外交换两个表的顺序,可以实现同样的效果
4、全外连接=内连接的结果+表1中有但表2没有的+表2中有但表1没有的
*/
#引入:查询男朋友不在男神表的的女神名
SELECT
*
FROM
beauty ;
SELECT
*
FROM
boys ;
#左外连接
SELECT
b.*,
bo.*
FROM
boys bo
LEFT OUTER JOIN beauty b
ON b.`boyfriend_id` = bo.`id`
WHERE b.`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 ;
#右外
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不支持
USE girls ;
SELECT
b.*,
bo.*
FROM
beauty b FULL
OUTER JOIN boys bo
ON b.`boyfriend_id` = bo.id ;
#三)交叉连接(笛卡尔乘积)
SELECT
b.*,
bo.*
FROM
beauty b
CROSS JOIN boys bo ;
#sql92和sql99 pk
/*
功能:sql99支持的较多
可读性:sql99实现连接条件和筛选条件的分离,可读性较高
*/
连接查询测试
#一、查询编号>3的女神的男朋友信息,如果有则列出详细,如果没有,用null填充
SELECT
b.id,
b.name,
bo.*
FROM
beauty b
LEFT OUTER JOIN boys bo
ON b.`boyfriend_id` = bo.`id`
WHERE b.`id` > 3 ;
#二、查询哪个城市没有部门
SELECT
city
FROM
departments d
RIGHT
OUTER JOIN locations l
ON d.`location_id` = l.`location_id`
WHERE d.`department_id` IS NULL ;
#三、查询部门名为SAL或IT的员工信息
SELECT
e.*,
d.department_name,
d.`department_id`
FROM
departments d
LEFT JOIN employees e
ON d.`department_id` = e.`department_id`
WHERE d.`department_name` IN ('SAL', 'IT') ;
进阶7:子查询
/*
含义:
出现在其他语句中的select语句,称为子查询或内查询
外部的查询语句,称为主查询或外查询
分类:
按子查询出现的位置:
select后面:
仅仅支持标量子查询
from后面:
支持表子查询
where或having后面:★
标量子查询(单行) √
列子查询 (多行) √
行子查询
exists后面(相关子查询)
表子查询
按结果集的行列数不同:
标量子查询(结果集只有一行一列)
列子查询(结果集只有一列多行)
行子查询(结果集有一行多列)
表子查询(结果集一般为多行多列)
*/
#一、where或having后面
/*
1、标量子查询(单行子查询)
2、列子查询(多行子查询)
3、行子查询(多列多行)
特点:
①子查询放在小括号内
②子查询一般放在条件的右侧
③标量子查询,一般搭配着单行操作符使用
> < >= <= = <>
列子查询,一般搭配着多行操作符使用
in、any/some、all
④子查询的执行优先于主查询执行,主查询的条件用到了子查询的结果
*/
子查询详解
#1.标量子查询★
#案例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) ;
#非法使用标量子查询
SELECT
MIN(salary),
department_id
FROM
employees
GROUP BY department_id
HAVING MIN(salary) >
(SELECT
salary
FROM
employees
WHERE department_id = 250) ;
#2.列子查询(多行子查询)★
#案例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 IN
(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' ;
#3、行子查询(结果集一行多列或多行多列)
#案例:查询员工编号最小并且工资最高的员工信息
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
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`) ;
#案例2:查询没有女朋友的男神信息
#in
SELECT
bo.*
FROM
boys bo
WHERE bo.id NOT IN
(SELECT
boyfriend_id
FROM
beauty) ;
#exists
SELECT
bo.*
FROM
boys bo
WHERE NOT EXISTS
(SELECT
boyfriend_id
FROM
beauty b
WHERE bo.`id` = b.`boyfriend_id`) ;
子查询测试
#1. 查询和Zlotkey相同部门的员工姓名和工资
#①查询Zlotkey的部门
SELECT
department_id
FROM
employees
WHERE last_name = 'Zlotkey' ;
#②查询部门号=①的姓名和工资
SELECT
last_name,
salary
FROM
employees
WHERE department_id =
(SELECT
department_id
FROM
employees
WHERE last_name = 'Zlotkey') ;
#2.查询工资比公司平均工资高的员工的员工号,姓名和工资。
#①查询平均工资
SELECT
AVG(salary)
FROM
employees ;
#②查询工资>①的员工号,姓名和工资。
SELECT
last_name,
employee_id,
salary
FROM
employees
WHERE salary >
(SELECT
AVG(salary)
FROM
employees) ;
#3.查询各部门中工资比本部门平均工资高的员工的员工号, 姓名和工资
#①查询各部门的平均工资
SELECT
AVG(salary),
department_id
FROM
employees
GROUP BY department_id ;
#②连接①结果集和employees表,进行筛选
SELECT
employee_id,
last_name,
salary,
e.department_id
FROM
employees e
INNER JOIN
(SELECT
AVG(salary) ag,
department_id
FROM
employees
GROUP BY department_id) ag_dep
ON e.department_id = ag_dep.department_id
WHERE salary > ag_dep.ag ;
#4. 查询和姓名中包含字母u的员工在相同部门的员工的员工号和姓名
#①查询姓名中包含字母u的员工的部门
SELECT DISTINCT
department_id
FROM
employees
WHERE last_name LIKE '%u%' ;
#②查询部门号=①中的任意一个的员工号和姓名
SELECT
last_name,
employee_id
FROM
employees
WHERE department_id IN
(SELECT DISTINCT
department_id
FROM
employees
WHERE last_name LIKE '%u%') ;
#5. 查询在部门的location_id为1700的部门工作的员工的员工号
#①查询location_id为1700的部门
SELECT DISTINCT
department_id
FROM
departments
WHERE location_id = 1700 ;
#②查询部门号=①中的任意一个的员工号
SELECT
employee_id
FROM
employees
WHERE department_id = ANY
(SELECT DISTINCT
department_id
FROM
departments
WHERE location_id = 1700) ;
#6.查询管理者是King的员工姓名和工资
#①查询姓名为king的员工编号
SELECT
employee_id
FROM
employees
WHERE last_name = 'K_ing' ;
#②查询哪个员工的manager_id = ①
SELECT
last_name,
salary
FROM
employees
WHERE manager_id IN
(SELECT
employee_id
FROM
employees
WHERE last_name = 'K_ing') ;
#7.查询工资最高的员工的姓名,要求first_name和last_name显示为一列,列名为 姓.名
#①查询最高工资
SELECT
MAX(salary)
FROM
employees ;
#②查询工资=①的姓.名
SELECT
CONCAT(first_name, last_name) "姓.名"
FROM
employees
WHERE salary =
(SELECT
MAX(salary)
FROM
employees) ;
多行操作符 | 含义 |
---|---|
in/not in | 等于列表中的任意一个 |
any|some | 和子查询返回的某一个值比较 |
all | 和子查询返回的所有值比较 |
进阶8:分页查询
/*
应用场景:当要显示的数据,一页显示不全,需要分页提交sql请求
语法:
select 查询列表
from 表
【join type 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;
size=10
page
1 0
2 10
3 20
*/
分页查询详解
#案例1:查询前五条员工信息
SELECT
*
FROM
employees
LIMIT 0, 5 ;
SELECT
*
FROM
employees
LIMIT 5 ;
#案例2:查询第11条——第25条
SELECT
*
FROM
employees
LIMIT 10, 15 ;
#案例3:有奖金的员工信息,并且工资较高的前10名显示出来
SELECT
*
FROM
employees
WHERE commission_pct IS NOT NULL
ORDER BY salary DESC
LIMIT 10 ;