目录
2021/2/1 基础查询
2021/2/2 条件查询
2021/2/4 排序查询
进阶一:基础查询
# 进阶一:基础查询
/*
语法:SELECT 查询列表 FROM 表名;
特点:
1、查询的结果集 是一个虚拟表
2、SELECT 类似于System.out.println(打印内容);
SELECT 后面跟的查询列表,可以有多个部分组成,中间用逗号隔开
例如:SELECT 字段1, 字段2, 表达式 FROM 表
System.out.println()的打印内容,只能有一个
3、执行顺序
SELECT fiest_name FROM employees222;
① FROM 子句
② SELECT 子句
4、查询列表可以是:字段、表达式、常量、函数等
*/
# 一、查询常量
SELECT 100 ;
# 二、查询表达式
SELECT 100%3;
# 三、查询单个字段
SELECT last_name FROM employees;
# 四、查询多个字段
SELECT last_name, email, employee_id FROM employees;
# 五、查询所有字段
SELECT * FROM employees;
SELECT
`last_name`,
`first_name`,
`email`,
`salary`,
`manager_id`
FROM
`employees` ;
-- 快捷键F12可快速对齐
# 六、查询函数(调用函数,获取返回值)
SELECT DATABASE();
SELECT VERSION();
SELECT USER();
# 七、起别名
# 方式一:使用AS关键字
SELECT USER() AS 用户名;
SELECT USER() AS '用户名';
SELECT USER() AS "用户名";
SELECT fiest_name AS '姓 名' FROM employees;
# 方式二:使用空格
SELECT USER() 用户名;
SELECT USER() '用户名';
SELECT USER() "用户名";
SELECT fiest_name '姓 名' FROM employees;
# 八、
-- 需求:查询 first_name 和last_name 拼接成的全名,最终起别名为:姓 名
# 方案1: 使用 + pass
SELECT first_name, last_name AS '姓 名'
FROM employees;
# 方案2:使用contact拼接函数
SELECT CONCAT(first_name, last_name) AS '姓名'
FROM employees;
--mysql 中 + 的作用:
# 加法运算:
--1、两个操作数都是数值型
--2、其中一个操作数为字符型
-- 将字符型数据强制转换成数值型,如果无法转换,则直接当做0处理
--3、其中一个操作数为null
null + null ===》 null
null + 100 ====》 null
# 九、DISTINCT的使用
--需求:查询员工涉及到的部门编号有哪些
SELECT DISTINCT department_id FROM employees;
# 十、查看表的结构
DESC employees;
SHOW COLUMNS FROM employees;
-- IFNULL(表达式1, 表达式2)
--表达式1:可能为null的字段或表达式
--表达式2:如果表达式1为null,则最终结果显示的值
--功能:如果表达式1为null,则显示表达式2,否则显示表达式1
进阶二:条件查询
/*
语法:
SELECT 查询列表
FROM 表名
WHERE 筛选条件
--执行顺序:FROM --> WHERE --> SELECT
特点:
1、按条件表达式筛选
关系运算符: < > >= <= = <> 可使用不等号,但不建议
2、按逻辑表达式筛选
逻辑运算符: and or not
3、模糊查询
LIKE IN BETWEEN AND IS NULL
*/
# 一、按关系表达式筛选
# 案例1:查询部门编号不是100的员工信息
SELECT *
FROM employees
WHERE department_id <> 100;
# 案例2:查询工资<15000的姓名、工资
SELECT last_name, salary
FROM employees
WHERE salary < 15000;
# 二、按逻辑表达式筛选
# 案例1:查询部门编号不是50-100之间员工姓名、部门编号、邮箱
# 方式1
SELECT `last_name`,`department_id`,`email`
FROM `employees`
WHERE `department_id` > 100 OR `department_id` < 50;
# 方式2
SELECT `last_name`,`department_id`,`email`
FROM `employees`
WHERE NOT(`department_id` <= 50 OR `department_id` >= 100);
# 案例2:查询奖金率>0.03 或者 员工编号在60-110之间的员工信息
SELECT *
FROM `employees`
WHERE `commission_pct` > 0.03
OR(`department_id` >= 60 AND `department_id` <= 100);
# 三、模糊查询
# 1、LIKE
/*
功能:一般和通配符搭配使用,对字符型数据进行部分匹配查询
常见的通配符:
_任意单个字符
%任意多个字符 0-多个
*/
# 案例1:查询姓名中包含字符a的员工信息
SELECT *
FROM `employees`
WHERE `last_name` LIKE '%a%';
# 案例2:查询姓名中最后一个字符为e的员工信息
SELECT *
FROM `employees`
WHERE `last_name` LIKE '%e';
# 案例3:查询姓名中第一个字符为e的员工信息
SELECT *
FROM `employees`
WHERE `last_name` LIKE 'e%';
# 案例4:查询姓名中第三个字符为x的员工信息
SELECT *
FROM `employees`
WHERE `last_name` LIKE '__x%';
# 案例5:查询姓名中第二个字符为_的员工信息
SELECT *
FROM `employees`
WHERE `last_name` LIKE '_\_%';
SELECT *
FROM `employees`
WHERE `last_name` LIKE '_$_%' ESCAPE '$';
# 2、IN
/*
功能:查询某字段的值是否属于指定的列表之内
a IN(常量值1,常量值2,常量值3,...)
a NOT IN (常量值1,常量值2,常量值3,...)
*/
# 案例1:查询部门编号是30/50/90的员工名、部门编号
# 方式1
SELECT `last_name`,`department_id`
FROM `employees`
WHERE `department_id` IN (30, 50, 90);
# 方式2
SELECT `last_name`,`department_id`
FROM `employees`
WHERE `department_id` = 30
OR `department_id` = 50
OR `department_id` = 90;
# 案例2:查询工种编号不是ST_CLERK或IT_PROG的员工信息
# 方式1
SELECT *
FROM `employees`
WHERE `job_id` NOT IN ('SH_CLERK','IT_PROG');
--非数值型的需要用''
# 方式2
SELECT *
FROM `employees`
WHERE NOT(`job_id` = 'SH_CLERK'
OR `job_id` = 'IT_PROG');
# 3、BETWEEN AND
/*
功能:判断某个字段的值是否介于xx之间
BETWEEN AND/NOT BETWEEN AND
*/
# 案例1:查询部门编号是30-90之间的部门编号、员工姓名
# 方式1
SELECT `department_id`,`last_name`
FROM `employees`
WHERE `department_id` BETWEEN 30 AND 90;
# 方式2
SELECT `department_id`,`last_name`
FROM `employees`
WHERE `department_id` >= 30 AND `department_id` <= 90;
# 案例2:查询年薪不是100000-200000之间的员工姓名、工资、年薪
SELECT
`last_name`,
`salary`,
`salary` * 12*(1+ IFNULL(`commission_pct`, 0)) 年薪
FROM
`employees`
WHERE `salary` * 12*(1+ IFNULL(`commission_pct`, 0)) NOT BETWEEN 100000
AND 200000
# 4、IS NULL/ IS NOT NULL
= 只能判断普通的内容
IS 只能判断NULL值
<=> 安全等于,既能判断普通内容,又能判断NULL值
# 案例1:查询没有奖金的员工信息
SELECT *
FROM `employees`
WHERE `commission_pct` IS NULL;
# 案例2:查询有奖金的员工信息
SELECT *
FROM `employees`
WHERE `commission_pct` IS NOT NULL;
条件查询 案例讲解
# 1、查询工资大于12000的员工姓名和工资
SELECT
`last_name`,
`salary`
FROM
`employees`
WHERE `salary` > 12000 ;
# 2、查询员工号为176的员工的姓名和部门号和年薪
SELECT
`last_name`,
`department_id`,
`salary` * 12 * (1+ IFNULL(`commission_pct`, 0)) 年薪
FROM
`employees`
WHERE `employee_id` = 176
# 3、选择工资不在5000到12000的员工的姓名和工资
SELECT
`last_name`,
`salary`
FROM
`employees`
WHERE `salary` NOT BETWEEN 5000
AND 12000 ;
# 4、选择在20或50号部门工作的员工姓名和部门号
SELECT
`last_name`,
`department_id`
FROM
`employees`
WHERE `department_id` = 20
OR `department_id` = 50 ;
# 5、选择公司中没有管理者的员工姓名及job_id
SELECT
`last_name`,
`job_id`
FROM
`employees`
WHERE `manager_id` IS NULL ;
# 6、选择公司中没有奖金的员工姓名,工资和奖金级别
SELECT
`last_name`,
`salary`,
`commission_pct`
FROM
`employees`
WHERE `commission_pct` IS NOT NULL ;
# 7、选择员工姓名的第三个字母是a的员工姓名
SELECT
`last_name`
FROM
`employees`
WHERE `last_name` LIKE '__a%';
# 8、选择姓名中有字母a和e的员工姓名
SELECT
`last_name`
FROM
`employees`
WHERE `last_name` LIKE '%a%'
AND `last_name` LIKE '%e%';
# 9、显示出表employees表中first_name以‘e’结尾的员工信息
SELECT
*
FROM
`employees`
WHERE `first_name` LIKE '%e';
# 10、显示出来employees部门编号在80-100之间的姓名、职位
SELECT
`last_name`,
`job_id`
FROM
`employees`
WHERE `department_id` BETWEEN 80
AND 100 ;
# 11、显示出表employees的manager_id是100,101,110的员工姓名、职位
SELECT
`last_name`,
`job_id`
FROM
`employees`
WHERE `manager_id` IN (100, 101, 110);
进阶三:排序查询
/*
语法:
SELECT 查询列表
FROM 表名
WHERE 筛选列表
ORDER BY 排序列表
执行顺序:
FROM --> WHERE --> SELECT --> ORDER BY
特点:
1、排序列表可以是单个字段、多个字段、表达式、函数、列数、以及以上的组合
2、升序,ASC 默认行为
降序,DESC
*/
# 一、按单个字段排序
# 案例1:将员工编号 > 120 的员工信息进行工资的升序
SELECT *
FROM employees
WHERE employee_id > 120
ORDER BY salary ASC;
# 案例2:将员工编号 > 120 的员工信息进行工资的降序
SELECT *
FROM employees
WHERE employee_id > 120
ORDER BY salary DESC;
# 二、按表达式排序
# 案例1:对有奖金的员工,按年薪降序
SELECT *, salary * 12 * ( 1 + IFNULL(commission_pct,0)) 年薪
FROM employees
WHERE commission_pct IS NOT NULL
ORDER BY salary * 12 * (1 + IFNULL(commission_pct,0)) DESC;
# 三、按别名排序
# 案例1:对有奖金的员工,按年薪降序
SELECT *, salary * 12 * ( 1 + IFNULL(commission_pct,0)) 年薪
FROM employees
WHERE commission_pct IS NOT NULL
ORDER BY 年薪 DESC;
# 四、按函数的结果排序
# 案例1:按姓名的字数长度进行升序
SELECT LENGTH(last_name), last_name
FROM employees
ORDER BY LENGTH(last_name);
# 五、按多个字段排序
# 案例1:查询员工的姓名、工资、部门编号,先按工资升序,再按部门编号降序
SELECT last_name, salary, department_id
FROM employees
ORDER BY salary ASC, department_id DESC; --可以一直排序
# 六、补充:按列序排序
SELECT *
FROM employees
ORDER BY 2;
SELECT *
FROM employees
ORDER BY first_name;
进阶四:常见函数
/*
函数:类似于Java中学过的‘方法’
为了解决某个问题,将编写的一系列的命令集合封装在一起,对外仅仅暴露方法名,供外部调用
1、自定义方法(函数)
2、调用方法(函数) 叫什么(函数名)/ 干什么(函数功能)
常见函数:
字符函数
数学函数
日期函数
流程控制函数
*/
# 一、字符函数
# 1、CONCAT 拼接字符
SELECT CONCAT('hello', first_name, last_name) 备注 FROM employees;
# 2、LENGTH 获取字节长度
SELECT LENGTH('hello,郭襄');
# 3、CHAR_LENGTH 获取字符个数
SELECT CHAR_LENGTH('hello,郭襄');
# 4、SUBSTRING 截取子串
--注意:起始索引从1开始
--SUBSTR(str,起始索引,截取的字符长度)
--SUBSTR(str,起始索引)
SELECT SUBSTRING('张三丰爱上了郭襄', 1, 3);
# 案例:
部门编号是30,工资显示为2倍
部门编号是50,工资显示为3倍
部门编号是60,工资显示为4倍
显示 部门编号,新工资,旧工资
SELECT department_id, salary,
CASE department_id
WHEN 30 THEN salary * 2
WHEN 30 THEN salary * 2
WHEN 30 THEN salary * 2
ELSE salary
END newSalary
FROM employees
# 情况2:类似于多重IF语句,实现区间判断
# 案例:
如果工资>20000,显示级别A
如果工资>15000,显示级别B
如果工资>10000,显示级别C
SELECT department_id, salary,
CASE department_id
WHEN salary > 20000 THEN 'A'
WHEN salary > 15000 THEN 'B'
WHEN salary > 10000 THEN 'C'
ELSE 'D'
END grade
FROM employees
常用函数案例分析
进阶五:分组函数
/*
说明:分组函数往往用于实现将一组数据进行统计计算,最终得到一个值,
又称为聚合函数或统计函数
分组函数清单:
SUM(字段名): 求和
AVG(字段名): 求平均数
MAX(字段名): 求最大值
MIN(字段名): 求最小值
COUNT(字段名): 计算非空字段的个数
*/
# 案例1:查询员工信息表中,所有员工的工资和、工资平均值、最低工资、最高工资、有工资的个数
SELECT
SUM(`salary`) 工资和,
AVG(`salary`) 工资平均值,
MAX(`salary`) 最大值,
MIN(`salary`) 最小值
COUNT(`salary`)
FROM `employees`
# 案例2:添加筛选条件
# 查询emp表中记录数
SELECT COUNT(employee_id) FROM employees;
# 查询emp表中有佣金的人数:
SELECT COUNT(`salary`)
FROM `employees`
# 查询emp表中月薪大于2500的人数:
SELECT COUNT(`salary`)
FROM `employees`
WHERE `salary` > 2500;
# 查询有领导的人数:
--COUNT本身就已经去重了
SELECT COUNT(`employee_id`)
FROM `employees`;
## COUNT的补充介绍
# 1、统计结果集的行数,推荐使用COUNT(*)
SELECT COUNT(*) FROM employees; --专门用于统计结果集的行数
SELECT COUNT(*) FROM employees WHERE department_id = 30;
SELECT COUNT(1) FROM employees;
SELECT COUNT(1) FROM employees WHERE department_id = 30;
# 2、搭配DISTINCT实现去重的统计
# 需求:查询有员工的部门个数
SELECT COUNT(DISTINCT department_id)
FROM employees;
## 思考:每个部门的总工资、平均工资?
SELECT
`department_id`,
SUM(`salary`),
AVG(`salary`)
FROM
`employees`
GROUP BY `department_id`;
进阶六:分组查询
/*
语法:
SELECT 查询列表
FROM 表名
WHERE 筛选条件
GROUP BY 分组列表
HAVING 分组后查询;
执行顺序:
FROM -- WHERE -- GROUP BY -- HAVING -- SELECT -- ORDER BY
特点:
查询列表往往是 分组函数和被分组的字段
分组查询中的筛选分为两类
筛选的基表 使用的关键词 位置
-分组前筛选 原始表 WHERE GROUP BY 前
-分组后筛选 分组后的结果集 HAVING GROUP BY 后
-- WHERE —— GROUP BY —— HAVING
问题:分组函数做条件只可能放在HAVING后面
*/
# 1)简单的分组
# 案例1:查询每个工种的员工平均工资
SELECT AVG(`salary`),`job_id`
FROM `employees`
GROUP BY `job_id`;
# 案例2:每个领导手下人数
SELECT COUNT(`employee_id`),`manager_id`
FROM `employees`
WHERE `manager_id` IS NOT NULL
GROUP BY `manager_id`;
# 2)可以实现分组前的筛选
# 案例1:查询邮箱中包含a字符的 每个部门的最高工资
SELECT `department_id`, MAX(`salary`)
FROM `employees`
WHERE `email` LIKE '%a%'
GROUP BY `department_id`;
# 案例2:查询每个领导手下有奖金的员工的平均工资
SELECT `manager_id`, AVG(`salary`)
FROM `employees`
WHERE `commission_pct` IS NOT NULL
GROUP BY `manager_id`;
# 3)可以实现分组后的筛选
# 案例1:查询哪个部门的员工个数>5
--首先查询每个部门的员工个数
--再在刚才结果的基础上,筛选哪个部门的员工个数>5
SELECT `department_id`, COUNT(`employee_id`)
FROM `employees`
GROUP BY `department_id`
HAVING COUNT(`employee_id`) > 5 ;
# 案例2:每个工种有奖金的员工的最高工资>12000的工种编号和最高工资
SELECT
`job_id`,
MAX(`salary`)
FROM
`employees`
WHERE `commission_pct` IS NOT NULL
GROUP BY `job_id`
HAVING MAX(`salary`) > 12000 ;
# 案例3:领导编号>102的每个领导手下的最低工资大于5000的领导编号和最低工资
--查询每个领导手下员工的最低工资
--筛选以上结果
SELECT
`manager_id`,
MIN(`salary`)
FROM
`employees`
WHERE `manager_id` > 102
GROUP BY `manager_id`
HAVING MIN(`salary`) > 5000 ;
# 4)可以实现排序
# 案例:查询没有奖金的员工的最高工资>6000的工种编号和最高工资,按最高工资升序
--分析1:按工种分组,查询每个工种有奖金的员工的最高工资
SELECT MAX(`salary`) 最高工资,`job_id`
FROM `employees`
WHERE `commission_pct` IS NULL
GROUP BY `job_id`;
--分析2:筛选刚才的结果,看哪个最高工资>6000
SELECT MAX(`salary`) 最高工资,`job_id`
FROM `employees`
WHERE `commission_pct` IS NULL
GROUP BY `job_id`;
HAVING MAX(`salary`) > 6000;
--分析3:按最高工资排序
SELECT MAX(`salary`) 最高工资,`job_id`
FROM `employees`
WHERE `commission_pct` IS NULL
GROUP BY `job_id`;
HAVING MAX(`salary`) > 6000;
ORDER BY MAX(`salary`) ASC;
# 5)按多个字段分组
# 案例:查询每个工种每个部门的最低工资,并按最低工资降序
--提示:工种和部门都一样,才是一组
SELECT MIN(`salary`) 最低工资,`job_id`,`department_id`
FROM `employees`
GROUP BY `job_id`,`department_id`
ORDER BY MIN(`salary`) DESC;
进阶七:连接查询
/*
含义:又称多表查询,当查询的字段来自于多个表时,就会用到连接查询
笛卡尔乘积现象:表1 有m行,表2有n行,结果 = m*n 行
发生原因:没有有效的连接条件
如何避免:添加有效的连接条件
分类:
按年代分类:
SQL92标准 仅仅支持内连接
SQL99标准【推荐】:支持内连接+外连接(左外+右外)+交叉连接
按功能分类:
内连接:等值连接/非等值连接/自连接
外连接:左外连接/右外连接/全外连接
交叉连接
*/
SELECT * FROM beauty;
SELECT * FROM boys;
SELECT NAME, boyName FROM boys, beauty
WHERE beauty.`boyfriend_id` = boys.`id`;
#------------------------------SQL92语法---------------------------
# 一、内连接
/*
语法:
SELECT 查询列表
FROM 表1 别名,表2 别名
WHERE 连接条件
AND 筛选条件
GROUP BY 分组列表
HAVING 分组后筛选
ORDER BY 排序列表
执行顺序:
FROM -- WHERE -- AND -- GROUP BY -- HAVING -- SELECT -- ORDER BY
*/
# 一)等值连接
/*
语法:
SELECT 查询列表
FROM 表名1 别名1, 表名2 别名2,...
WHERE 等值连接的连接条件
特点:
1、为了解决多表中的字段名重名问题,往往为表起别名,提高语义性
2、表的顺序无要求
3、n表连接,至少需要n-1个连接条件
4、可以搭配前面介绍的所有子句使用,比如排序、分组、筛选
*/
# ①简单的两表连接
# 案例:查询员工名和部门名
SELECT `last_name`,`department_name`
FROM `departments` dp ,`employees` ep
WHERE dp.`department_id`=ep.`department_id`
# ②添加筛选条件
# 案例1:查询部门编号>100的部门名和所在的城市名
SELECT `department_name`,`city`
FROM `departments` dp ,`locations` lc
WHERE dp.`location_id` = lc.`location_id`
AND dp.`department_id` > 100;
# 案例2:查询有奖金的员工名、部门名
SELECT `last_name`,`department_name`
FROM `employees` ep, `departments` dp
WHERE dp.`department_id` = ep.`department_id`
AND `commission_pct` IS NOT NULL;
# 案例3:查询城市名中第二个字符为o的部门名和城市名
SELECT `department_name`, `city`
FROM `departments` dp ,`locations` lc
WHERE dp.`location_id` = lc.`location_id`
AND city LIKE '_o%';
# ③添加分组+筛选
# 案例1:查询每个城市的部门个数
SELECT city, COUNT(*) 部门个数
FROM `locations` lc, `departments` dp
WHERE lc.`location_id` = dp.`location_id`
GROUP BY city;
# 案例2:查询有奖金的每个部门的部门名和部门的领导编号和该部门的最低工资
SELECT `department_name`, dp.`manager_id`,MIN(`salary`)
FROM `departments` dp, `employees` ep
WHERE dp.`department_id` = ep.`department_id`
AND `commission_pct` IS NOT NULL
GROUP BY dp.`department_id`;
# 案例3:查询部门中员工个数>10的部门名
SELECT `department_name`,COUNT(*)
FROM `departments` dp, `employees` ep
WHERE dp.`department_id` = ep.`department_id`
GROUP BY dp.`department_id`
HAVING COUNT(*) > 10;
# ④添加分组+筛选+排序
# 案例1:查询部门中员工个数>10的部门名,并按部门名降序
SELECT `department_name`,COUNT(*)
FROM `departments` dp, `employees` ep
WHERE dp.`department_id` = ep.`department_id`
GROUP BY dp.`department_id`
HAVING COUNT(*) > 10
ORDER BY `department_name` DESC;
# 案例2:查询每个工种的员工个数和工种名,并按个数降序
SELECT `job_title`,COUNT(*)
FROM `jobs`, `employees` ep
WHERE jobs.`job_id` = ep.`job_id`
GROUP BY `job_title`
ORDER BY COUNT(*) DESC;
# ⑤三表连接
# 案例:查询员工名、部门名、城市名
SELECT `last_name`, `department_name`, `city`
FROM `employees` ep, `departments` dp, `locations` lc
WHERE ep.`department_id` = dp.`department_id`
AND dp.`location_id` = lc.`location_id`
# 二)非等值连接
# 案例1:查询员工的工资和工资级别
SELECT salary, grade_level
FROM employees e, job_grades g
WHERE salary BETWEEN g.lowest_sal AND g.highest_sal
# 三)自连接
# 案例:查询员工名和上级的名称
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
#-----------------------------SQL99语法-------------------------------
# 一、内连接
/*
语法:
SELECT 查询列表
FROM 表1 别名
【INNER】 JOIN 表2 别名
ON 连接条件
WHERE 筛选条件
GROUP BY 分组列表
HAVING 分组后筛选
ORDER BY 排序列表
执行顺序:
FROM -- WHERE -- AND -- GROUP BY -- HAVING -- SELECT -- ORDER BY
SQL92和SQL99的区别:
SQL99,使用JOIN关键字代替了之前的逗号,并且将连接条件和筛选条件进行了分离,提高阅读性!!!
SQL92
*/
# 一)等值连接
# ①简单的两表连接
# 案例:查询员工名和部门名
SELECT `last_name`,`department_name`
FROM `departments` d INNER JOIN `employees` e
ON d.`department_id` = e.`department_id`;
# ②添加筛选条件
# 案例1:查询部门编号>100的部门名和所在的城市名
SELECT
`department_name`,
`city`
FROM
`locations` l
JOIN `departments` d
ON l.`location_id` = d.`location_id`
WHERE d.`department_id` > 100 ;
# ③添加分组+筛选
#案例1:查询每个城市的部门个数
SELECT
city,
COUNT(*) 部门个数
FROM
`locations` l
JOIN `departments` d
ON l.`location_id` = d.`location_id`
GROUP BY l.`city`
# ④添加分组+筛选+排序
# 案例1:查询部门中员工个数>10的部门名,并按员工个数降序
SELECT
`department_name`,
COUNT(*) 员工数
FROM
`departments` d
JOIN `employees` e
ON d.`department_id` = e.`department_id`
GROUP BY d.`department_id`
HAVING COUNT(*) > 10
ORDER BY 员工数 DESC;
# 二)非等值连接
# 案例:查询部门编号在10-90之间的员工的工资级别,并按级别进行分组
SELECT COUNT(*) 个数, grade
FROM employees e
JOIN sal_grade g
ON e.salary BETWEEN g.min_salary AND g.max_salary
WHERE e.department_id BETWEEN 10 AND 90
GROUP BY g.grade;
# 三)自连接
# 案例:查询员工名和对应的领导名
SELECT
e.`last_name`,
l.`last_name`
FROM
`employees` e
JOIN `employees` l
ON e.`manager_id` = l.`employee_id`
# 二、外连接
/*
说明:查询结果为主表中所有的纪录,如果从表有匹配项,则显示匹配项;如果从表没有匹配项,则显示Null
应用场景:一般用于查询主表中有但从表没有的纪录
特点:
1、外连接分主从表,两表的顺序不能任意调换
2、左连接的话,左边为主表
右连接的话,右边为主表
语法:
SELECT 查询列表
FROM 表1 别名
LEFT/RIGHT/FULL 【OUTER】 JOIN 表2 别名
ON 连接条件
WHERE 筛选条件
*/
USE girls
# 案例1:查询所有女神纪录,以及对应的男神名,如果没有对应的男神,则显示为null
# 左连接
SELECT b1.`name`, b2.`boyName`
FROM `beauty` b1
LEFT JOIN `boys` b2
ON b1.`boyfriend_id` = b2.`id`;
# 案例2:查询哪个女神没有男朋友
SELECT b1.`name`, b2.`boyName`
FROM `beauty` b1
LEFT JOIN `boys` b2
ON b1.`boyfriend_id` = b2.`id`
WHERE b2.`id` IS NULL;
# 案例3:查询哪个部门没有员工,并显示其部门编号和部门名
SELECT
d.`department_id`,
d.`department_name`
FROM
`departments` d
LEFT JOIN `employees` e
ON d.`department_id` = e.`department_id`
WHERE e.department_id IS NULL
# 外连接查询案例
# 一、查询编号>3的女神的男朋友信息,如果有则列出详细,如果没有,用null填充
SELECT b1.id, b1.`name`, b2.`boyName`
FROM `beauty` b1
LEFT JOIN `boys` b2
ON b1.`boyfriend_id` = b2.`id`
WHERE b1.`id` > 3
# 二、查询哪个城市没有部门
SELECT l.`city`
FROM `locations` l
LEFT JOIN `departments` d
ON l.`location_id` = d.`location_id`
WHERE d.`department_id` IS NULL
# 三、查询部门名为SAL或IT的员工信息
SELECT d.*
FROM `employees` e
RIGHT JOIN `departments` d
ON e.`department_id` = d.`department_id`
WHERE d.`department_name` = 'SAL'
OR d.`department_name` = 'IT'
连接查询辨析
进阶八:子查询
/*
说明:当一个查询语句中又嵌套了另一个完整的SELECT语句,则被嵌套的SELECT语句称为子查询或内查询,外面的SELECT语句称为主查询或外查询
分类:
按子查询出现的位置进行分类:
1、SELECT后面
要求:子查询的结果为单行单列(标量子查询)
2、FROM后面
要求:子查询的结果可以为多行多列
3、WHERE或HAVING后面⭐
要求:子查询的结果必须为单列
多行子查询
单行子查询
4、EXISTS后面
要求:子查询结果必须为单列(相关子查询)
特点:
1、子查询放在条件中,要求必须放在条件的右侧
2、子查询一般放在小括号中
3、子查询的执行优先于主查询
4、单行子查询对应了 单行操作符:> < >= <= <>
多行子查询对应了 多行操作符 any/some all in
*/
# 一、where或having后面
# 1、标量子查询(单行子查询)
# 2、列子查询(多行子查询)
# 3、行子查询(多列多行)
# 1、标量子查询
# 案例1:谁的工资比Abel高?
--1) 查询Abel的工资
SELECT salary
FROM employees
WHERE last_name = 'Abel';
--2) 查询员工的信息,满足salary > 1)结果
SELECT
*
FROM
employees
WHERE salary >
(SELECT
salary
FROM
employees
WHERE last_name = 'Abel');
# 案例2:返回job_id与141号员工相同,salary比143号员工多的 员工姓名,job_id 和工资
--1)查询141号员工的job_id
SELECT `job_id`
FROM `employees`
WHERE `employee_id` = 141;
--2) 查询143号员工的salary
SELECT `salary`
FROM `employees`
WHERE `employee_id` = 143;
--3) 查询员工的姓名,job_id 和工资,要求job_id = 1)且salary > 2)
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
--1) 查询公司的最低工资
SELECT MIN(`salary`)
FROM `employees`;
--2)查询last_name,job_id,salary,要求salary = 1)
SELECT `last_name`, `job_id`, `salary`
FROM `employees`
WHERE `salary` = (SELECT MIN(`salary`)
FROM `employees`);
# 案例4:查询最低工资大于50号部门最低工资的部门id和其最低工资
--1)查询50号部门的最低工资
SELECT MIN(`salary`)
FROM `employees`
WHERE `department_id` = 50;
--2)查询每个部门的最低工资
SELECT MIN(`salary`)
FROM `employees`
GROUP BY `department_id`;
--3)在 2)基础上筛选,满足MIN(salary) > 1)
SELECT `department_id`,MIN(`salary`)
FROM `employees`
GROUP BY `department_id`
HAVING MIN(`salary`) > (SELECT MIN(`salary`)
FROM `employees`);
# 2、列子查询(多行子查询)
/*
返回多行
使用多行比较操作符
-IN/NOT IN 等于列表中的任意一个
-ANY/SOME 和子查询返回的某一个值比较 类似于MIN()
-ALL 和子查询返回的所有值比较 类似于MAX()
*/
# 案例1:返回location_id是1400或1700的部门中的所有员工姓名
--1)查询location_id是1400或1700的部门编号
SELECT `department_id`
FROM `departments`
WHERE `location_id` IN(1400,1700);
--2)查询员工姓名,要求部门号是1)列表中的某一个
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
--1)查询job_id为‘IT_PROG’部门任一工资
SELECT DISTINCT(`salary`)
FROM `employees`
WHERE `job_id` = 'IT_PROG';
--2) 查询员工号、姓名、job_id以及salary, salary < 1)的任意一个
SELECT `employee_id`,`last_name`,`job_id`,`salary`
FROM `employees`
WHERE `salary` < ANY(SELECT DISTINCT(`salary`)
FROM `employees`)
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 DISTINCT(`salary`)
FROM `employees`)
AND job_id <> 'IT_PROG';
# 3、行子查询(结果集一行多列或多行多列)
# 案例:查询员工编号最小并且工资最高的员工信息
--1)编号最小
SELECT MIN(`employee_id`)
FROM `employees`;
--2)工资最高
SELECT MAX(`salary`)
FROM `employees`;
--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后面
# 案例:查询部门编号是50的员工个数
SELECT
(SELECT
COUNT(*)
FROM
`employees`
WHERE `department_id` = 50)
个数
# 三、放在from后面
# 案例:查询每个部门的平均工资的工资级别
--1)查询每个部门的平均工资
SELECT AVG(`salary`),`department_id`
FROM `employees`
GROUP BY `department_id`
--2)将1)和sal_grade两表连接查询
SELECT dep_ag.department_id, dep_ag.ag, g.grade
FROM sal_grade g
JOIN (SELECT AVG(`salary`),`department_id`
FROM `employees`
GROUP BY `department_id`) dep_ag
ON dep_ag.ag BETWEEN g.min_salary AND g.max_salary
# 四、放在exists后面
# 案例1:查询有无名字叫‘Abel’的员工信息
SELECT EXISTS(
SELECT *
FROM `employees`
WHERE `last_name` = 'Abel')
有无Abel
子查询案例分析
# 案例1:查询和Zlotkey相同部门的员工姓名和工资
--1)查询zlotkey的部门
SELECT `department_id`,`last_name`
FROM `employees`
WHERE `last_name` = 'Zlotkey';
--2)查询部门号=1)的员工姓名和工资
SELECT `last_name`,`salary`
FROM `employees`
WHERE `department_id` = (
SELECT `department_id`
FROM `employees`
WHERE `last_name` = 'Zlotkey');
# 案例2:查询工资比公司平均工资高的员工的员工号,姓名和工资
--1)查询公司的平均工资
SELECT AVG(`salary`)
FROM `employees`;
--2)查询工资>1)的员工号、姓名和工资
SELECT `employee_id`, `last_name`, `salary`
FROM `employees`
WHERE `salary` > (
SELECT AVG(`salary`)
FROM `employees`);
# 案例3:查询各部门中工资比本部门平均工资高的员工的员工号,姓名和工资
--1)查询各部门的平均工资
SELECT `department_id`,AVG(`salary`)
FROM `employees`
GROUP BY `department_id`;
--2)连接1)结果集和employees表,进行筛选
SELECT `employee_id`,`last_name`,`salary`,e.`department_id`
FROM `employees` e
INNER JOIN (
SELECT `department_id`,AVG(`salary`) ag
FROM `employees`
GROUP BY `department_id`) ag_dep
ON e.`department_id` = ag_dep.department_id
WHERE salary > ag_dep.ag;
# 案例4:查询和姓名中包含字母u的员工在相同部门的员工的员工号和姓名
--1) 查询姓名中包含字母u的员工
SELECT DISTINCT `department_id`
FROM `employees`
WHERE `last_name` LIKE '%u%';
--2) 在1)基础之上,在相同部门的员工的员工号和姓名
SELECT `employee_id`,`last_name`
FROM `employees` e
WHERE `department_id` IN(
SELECT DISTINCT `department_id`
FROM `employees`
WHERE `last_name` LIKE '%u%')
AND `last_name` NOT LIKE '%u%';
# 案例5:查询在部门的location_id为1700的部门工作的员工的员工号
--1) 查询location_id为1700的部门编号
SELECT DISTINCT `department_id`
FROM `departments`
WHERE `location_id` = 1700;
--2) 查询在部门号在1)中的员工的员工号
SELECT `employee_id`
FROM `employees`
WHERE `department_id` IN (
SELECT DISTINCT `department_id`
FROM `departments`
WHERE `location_id` = 1700);
# 案例6:查询管理者是K_ing的员工姓名和工资
--1)查询姓名为K_ing的员工姓名和工资
SELECT `employee_id`
FROM `employees`
WHERE `last_name`= 'k_ing'
--2)查询哪个员工的manager_id = 1)
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显示为一列,列名为 姓 名
--1)查询最高工资
SELECT MAX(`salary`)
FROM `employees`
--2)查询工资=1)的姓 名
## CONCAT起别名时需要加‘’
SELECT CONCAT(`first_name`,`last_name`) '姓 名'
FROM `employees`
WHERE `salary` = (
SELECT MAX(`salary`)
FROM `employees`);
进阶九:分页查询
/*
应用场景:当页面上的数据,一页显示不全,则需要分页显示
分页查询的SQL命令请求数据库服务器-->服务器响应查询到的多条数据-->前台页面
语法:
SELECT 查询列表
FROM 表1 别名
JOIN 表2 别名
ON 连接条件
WHERE 筛选条件
GROUP BY 分组
HAVING 分组后筛选
ORDER BY 排序列表
LIMIT 起始条目索引,显示的条目数
执行顺序:
FROM -- JOIN -- ON -- WHERE -- GROUP BY -- HAVING -- SELECT -- LIMIT
特点:
1、起始条目索引如果不写,默认是0
2、limit后面支持两个参数
参数1:显示的起始条目索引
参数2:条目数
公式:
假如要显示的页数是page,每页显示的条目数为size
SELECT *
FROM employees
LIMIT (page-1) * size, size
*/
# 案例1:查询员工信息表的前5条
SELECT * FROM employees LIMIT 0,5
# 等价
SELECT * FROM employees LIMIT 5
# 案例2:查询有奖金的,且工资较高的第11名到第20名
SELECT *
FROM `employees`
WHERE `commission_pct` IS NOT NULL
ORDER BY `salary` DESC
LIMIT 10,10
进阶十:union联合查询
/*
说明:当查询结果来自于多张表,但多张表之间没有关联,这个时候往往使用联合查询,也称为union查询
语法:
SELECT 查询列表 FROM 表1 WHERE 筛选条件
UNION
SELECT 查询列表 FROM 表1 WHERE 筛选条件
特点:
1、多条待联合的查询语句的查询列数必须一致,查询类型、字段意义最好一致
2、union实现去重查询
union all实现全部查询
*/
# 案例1:显示所有国家的年龄>20岁的用户信息
SELECT * FROM chinese WHERE age > 20 UNION
SELECT * FROM usa WHERE uage > 20;
# 案例2:查询所有国家的用户姓名和年龄
SELECT uname,uage FROM usa
UNION
SELECT `name`,age FROM chinese
# 案例3:union自动去重 / union all可以支持重复项
SELECT 1 ,'邓伦'
UNION ALL
SELECT 1 ,'邓伦'
UNION ALL
SELECT 1 ,'邓伦'
UNION ALL
SELECT 1 ,'邓伦'