D:\MYsql\data 里的 my.ini 是配置文件,可以改动,改动后要重新启动
MySQL 的启动和停止
dos窗口管理员打开, ** **
**net stop MySQL80 停止 **
net start MySQL80 启动 (MySQL80 是我的MySQL的用户名)
MySQL的登录与退出
登录 mysql -h主机名 -P端口号 -u用户名 -p密码
- MySQL 8.0 Command Line Client 登陆,直接输入密码,缺点是只能是root用户
- dos窗口,管理员打开, (远程登录)mysql -h localhost -P 3306 -u root -p 后输入密码。
其中, -h 主机host , -P 端口号 -u 用户名 -p 密码
- mysql -h localhost -P 3306 -u root -p密码
密码显示出,要求-p 后无空格
- mysql -u root -p (本机登录)
退出 exit 或ctrl+z
MySQL的常见命令
- 查看当前所有的数据库 show databases;
- 打开指定的库 ** use 库名;**
- 查看当前库的所有表 ** show tables;**
- 查看其它库的所有表 ** show tables from 库名;**
- 创建表 create table 表名{
列名 列类型,
列名 列类型,
…
};
- 查看当前处于哪一个库 select database();
- 查看表结构 ** desc 表名;**
- 查看表中的数据 select * from 表名;
- 更新stuinfo表里的id==1的name为lilei (**stuinfo表在world 库里面 **) ** **
update stuinfo set name=‘lilei’ where id=1;
- 删除表中id==1的数据 delete from 表名 where id=1;
- 查看服务器的版本
方式一:登录到mysql服务器 ** select version();**
方式二: 没有登录到mysql服务器 mysql --version 或 mysql -V
-
图形化查看mysql是否登录
可修改是否开机自启动;
-
SQL的语法规范
- 不去分大小写,但关键字大写,表名、列名小写
- 每条命令最好用分号结尾
- 每条命令根据需要,可以缩进蹲、或换行
- 注释:
单行注释: **#**注释文字
单行注释: –空格注释文字
多行注释: / 注释文字 /
- 运行 myemployees.sql 脚本文件,得到的数据库
数据库四张表里的一些信息
DQL语言(在SQLyog中操作)
进阶1: 基础查询
- 语法
select 查询列表 from 表名;
类似于:printf(打印的东西);
特点:
- 查询列表可以是 : 表中的字段,常量值, 表达式, 函数
- 查询的结果是一个虚拟的表格
- 打开启用的数据库
**USE myemployees;**
1.查询表中的单个字段
SELECT
hiredate
FROM
employees ;
表中有一个或多个列 ,列又称为“字段”
2. 查询表中的多个字段
SELECT
last_name,
salary,
email
FROM
employees ;
3.查询表中的所有字段
(1)* 只能按照表中的顺序
SELECT
*
FROM
employees ;
(2) 按照你的顺序打印出来
SELECT
first_name, # ` ` 是着重号 ,代表是一个字段名,区分字段与关键字,可以去掉
employee_id,
last_name,
phone_number,
email,
job_id,
salary,
commission_pct,
manager_id,
department_id,
hiredate
FROM
employees ;
**
**是着重号 ,代表是一个字段名,区分字段与关键字,可以去掉
格式化 Ctrl+F12 或 F12
4. 查询常量值
**SELECT 100;**
**SELECT 'john';**
字符型和日期型的常量值必须用单引号 ‘’ 引起来 ,数值型不需要
5. 查询表达式
**SELECT 100%99;**
6. 查询函数
**SELECT VERSION();**
** (MySQL的版本号)**
7. 起别名
SELECT 100%98 AS 结果;
SELECT
last_name AS 姓,
first_name AS 名
FROM
employees ;
SELECT
last_name 姓,
first_name 名
FROM
employees ;
**SELECT salary AS "out put" FROM employees; **
(如果别名中有特殊符号,eg:空格,# ,最好加上双引号)
8. 去重
**SELECT DISTINCT department_id FROM employees;**
** 在要查的表名前面加上 DISTINCT**
9. +号的作用
运算符,两个操作数都为数值型
(1)**SELECT 100+90; **
两个数都为数值型,则作加法运算
(2)**SELECT '123'+10;**
其中一方为字符型,试图将字符型转化为数值型
如果转化成功,则继续做加法运算
如果转化失败,则将字符型数值转化为 0
(3)**SELECT null+10;**
** 只要其中一方为 null ,则结果为 null**
10. concat(str1,str2, …) 拼接
SELECT
CONCAT(last_name, first_name) AS 姓名
FROM
employees ;
11. IFNULL(expr1,expr2); expr1 为可能为null 的表的名字, expr2 为如果expr1 为 null ,则 返回的结果为 expr2
进阶2: 条件查询
SELECT 执行顺序
查询列表 (3)
FROM
表名 (1)
WHERE 筛选条件 ; (2)
1. 分类
(1) 按条件表达式筛选
**条件运算符: > < = (等于) <>(不等于) != >= <= **
(2) 按逻辑表达式筛选
逻辑运算符:
**&& || ! **
and or not
(3) 模糊查询
like
between and
in
is null 或 is not null
2. 按条件表达式筛选
案例一: 查询工资>12000的员工信息
SELECT
*
FROM
employees
WHERE salary > 12000 ;
案例二: 查询部门编号不等于90号的员工名字和部门编号
SELECT
CONCAT(last_name, ' ', first_name) AS 姓名,
department_id AS 部门编号
FROM
employees
WHERE department_id <> 90 ;
3. 按逻辑表达式筛选
案例一: 查询工资z在10000到20000之间的员工名、工资以及资金
SELECT
first_name AS 员工名,
salary AS 工资,
IFNULL(commission_pct, 0) AS 奖金
FROM
employees
WHERE salary >= 10000 AND salary <= 20000 ;
案例二: 查询部门编号不是在90到110之间,或者工资高于15000的员工信息
SELECT
*
FROM
employees
WHERE NOT (
department_id <= 110
AND department_id >= 90
)
OR salary >= 15000 ;
SELECT
*
FROM
employees
WHERE department_id > 110
OR department_id < 90
OR salary >= 15000 ;
4. 模糊查询
(1)like (像什么)
特点:(1) 一般与通配符搭配使用
**通配符: **
% 任意多个字符,包含0个字符
_ 任意单个字符
案例一:查询员工名中包含a的员工信息
SELECT
*
FROM
employees
WHERE last_name LIKE '%a%' ;
案例二:查询员工名中第二个字符、第五个字符为a的员工名和工资
SELECT
last_name,
salary
FROM
employees
WHERE last_name LIKE '_a__a%' ;
案例三:查询员工中第三个字符为 _ 的员工名
SELECT
last_name
FROM
employees
WHERE last_name LIKE '_@_%' ESCAPE '@' ;
SELECT
last_name
FROM
employees
WHERE last_name LIKE '_\_%' ;
LIKE 值里有一些符号(eg: _ )当做普通符号使用, 则就可以使用 转义的方式 , ESCAPE ‘str’ --> 这里的str 就是转义符号
5. between and (在什么与什么之间)
案例一:查询员工编号在100到120之间的员工信息
SELECT
*
FROM
employees
WHERE employee_id BETWEEN 100
AND 120 ;
SELECT
*
FROM
employees
WHERE employee_id >= 100
AND employee_id <= 120 ;
特点:
(1) 包含两个临界值[a,b]
(2) 两个临界值不要调换顺序,[a,b] ,大于等于a ,小于等于b
6. in
(1) 含义: 判断某个字段的值是否属于 in 列表中的某一项
(2) 特点:in 列表的值类型必须一致或兼容(in列表中不能出现通配符 % 和 _ )
(3) 案例:查询员工的工种编号是 IT_PROG 、AD_VP、AD_PRES 中的一个员工名或工种编号
SELECT
last_name,
job_id
FROM
employees
WHERE job_id IN ('AD_VP', 'IT_PROG', 'AD_PRES') ;
SELECT
last_name,
job_id
FROM
employees
WHERE job_id = 'AD_VP'
OR job_id = 'IT_PROG'
OR jOb_ID = 'AD_PRES' ;
7. is null 和 is not null
(1) = 或 <> 不能用于判断null 值 ,
(2) is null 或 is not null 可以判断 null 值
(3) 案例一:查询没有奖金的员工名和奖金率
SELECT
last_name,
commission_pct
FROM
employees
WHERE commission_pct IS NULL ;
8. 安全等于 <=>
(1) 含义:判断是否等于,如果等于,返回true
(2) 案例一:查询没有奖金的员工名和奖金率
SELECT
last_name,
commission_pct
FROM
employees
WHERE commission_pct <=> NULL ;
(3) 案例二:查询工资等于12000的员工信息
SELECT
*
FROM
employees
WHERE salary <=> 12000 ;
9. is null 与 安全等于<=> 的 对比
is null : 仅仅可以判断null值,可读性较高
<=> : 既可以判断null值,又可以判断普通的数值, 可读性较低
10. isnull(expr) 函数
功能:判断某字段或表达式是否为null , 如果是为null,返回1 ;否则返回 0
11. 经典面试题
试问,SELECT * FROM employees; (1)和
SELECT * FROM employees WHERE commission_pct LIKE ‘%%’ AND last_name LIKE ‘%%’; (2)
结果是否一样?说明原因。
答:不一样,commission_pct 和 last_name 中有null值,(2)中判断的是没有null值得列表, (1)zh中包含了null值得列表。
假设, SELECT * FROM employees WHERE commission_pct LIKE ‘%%’ OR last_name LIKE ‘%%’; (2)
(2)OR 了 employees 表中的所有列表(所有列表中总归有不为null的),那么(1)(2)结果一样。
进阶3: 排序查询
1. 语法
select 查询列表 (3)
from 表 (1)
【where 筛选条件】 (2)
order by 排序列表【asc|desc】 (4)
这里的(1)(2)(3)(4)是执行的顺序
2. 特点:
(1)asc 代表的是升序,desc代表的是降序 ;如果不写,默认为升序
(2)order by 子句中可以支持单个字段、多个字段、表达式、函数、别名
(3)order by 子句一般放在查询语句的最后面,limit 子句除外
案例1:查询员工信息,要求工资从高到低
SELECT
*
FROM
employees
ORDER BY salary DESC ;
案列2:查询部门编号>=90的员工信息,按入职时间的先后顺序排列【按筛选添加筛选条件】
SELECT
*
FROM
employees
WHERE department_id >= 90
ORDER BY hiredate ASC ;
案例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:按姓名的长度显示员工的姓名和工资【按函数排序】
LENGTH(str) 返回str的长度
SELECT
LENGTH(last_name) AS 字节长度,
last_name,
salary
FROM
employees
ORDER BY LENGTH(last_name) DESC ;
案例6:查询员工信息,要求先按工资升序,再按员工编号降序【按多个字段排序】
SELECT
*
FROM
employees
ORDER BY salary ASC,
employee_id DESC ;
整体上是按工资的升序,如果工资中有相同的几个,则这几个按员工编号排序
3. 测试题
题1: 查询员工的姓名和部门号和年薪,按年薪降序,按姓名升序
SELECT
last_name,
department_id,
salary * 12 * (1+ IFNULL(commission_pct, 0)) AS 年薪
FROM
employees
ORDER BY 年薪 DESC,
last_name ASC ;
题2: 选择工资不在8000到17000的员工的姓名和工资,按工资降序
SELECT
last_name,
salary
FROM
employees
WHERE NOT (salary BETWEEN 8000
AND 17000)
ORDER BY salary DESC ;
SELECT
last_name,
salary
FROM
employees
WHERE salary NOT BETWEEN 8000
AND 17000
ORDER BY salary DESC ;
题3:查询邮箱中包含e的员工信息,并按照邮箱的字节数降序、再按照部门号升序
SELECT
*
FROM
employees
WHERE email LIKE '%e%'
ORDER BY LENGTH(email) DESC,
department_id ASC ;
进阶4: 常见函数
一、 概念:将一组逻辑语句封装在方法体中,对外暴露方法名
(1)调用:select 函数名(实参列表)【from 表】
二、分类:
(1)单行函数(传一个参数,返回一个值;给一组值,返回一组值),eg: concat , length , ifnull …
(2)分组函数(传一组参数,返回一个值)
功能:做统计使用,又称为统计函数,聚合函数,组函数
三、单行函数
(一)、字符函数
1. length(str); 获取参数值的字节个数
在MySQL 8.0 客户端中 一个汉字 = 3 byte
SELECT LENGTH('john');
SELECT LENGTH('张三丰hahaha');
**SHOW VARIABLES LIKE '%char%'; **
Variable_name Value | Value |
---|---|
character_set_client | utf8mb3 |
character_set_connection | utf8mb3 |
character_set_database | gb2312 |
character_set_filesystem | binary |
character_set_results | utf8mb3 |
character_set_server | utf8mb4 |
character_set_system | utf8mb3 |
character_sets_dir | D:\\MYsql\\share\\charsets\\ |
2. concat(str1,str2, …); 拼接字符串
SELECT
CONCAT(last_name, '_', 'first_name') AS 姓名
FROM
employees ;
3. upper (大写) 、lower(小写)
SELECT UPPER('jilo');
SELECT LOWER('JYTREW');
(1)示7例:将姓变大写,名变小写,然后拼接
SELECT
CONCAT(
UPPER(last_name),
'_',
LOWER(first_name)
)
FROM
employees ;
4. substr , substring
(1)注意:索引从1开始
(2)substr(str,pos); 截取从指定索引后面所有字符
SELECT
SUBSTR(
'李莫愁爱上了陆展元',
7
) AS out_put ;
(3)substr(str,pos,len); 截取从指定索引开始的指定长度len的字符串
SELECT
SUBSTR(
'李莫愁爱上了陆展元',
1,
3
) AS out_put ;
(4)案例:姓名中首字符大写,其他字符小写,然后用 _ 拼接 ,显示出来
SELECT
CONCAT(
UPPER(SUBSTR(last_name, 1, 1)),
'_',
LOWER(SUBSTR(last_name, 2))
) AS 姓名
FROM
employees ;
5. instr(str,substr); 返回子串第一次出现的索引,如果找不到则返回0
SELECT
INSTR(
'杨不殷六侠悔爱上了殷六侠',
'殷六a侠'
) AS out_put ;
SELECT
INSTR(
'杨不悔爱上了殷六侠',
'殷六侠'
) AS out_put ;
6. trim 去掉字符串前后的空格
SELECT TRIM(' 张翠山 ') AS out_put;
SELECT LENGTH(TRIM(' 张翠山 ')) AS out_put;
(1)去掉字符串前后的a
SELECT
TRIM(
'a' FROM 'aaaaaaaaaaa张aaaaaaa翠山aaaaaaaaaa'
) AS out_put ;
7. lpad(str,len,padstr); 用指定的字符实现左填充指定长度
SELECT
LPAD('殷素素', 10, '*') AS out_put ;
(1)如果字符串超过指定长度,则从右边删去超过的字符,不填充
SELECT
LPAD('殷素素', 2, '*') AS out_put ;
8. rpad(str,len,padstr); 用指定的字符实现右填充指定长度
SELECT RPAD('殷素素',12,'ab') AS out_put;
(1)如果字符串超过指定长度,则从右边删去超过的字符,不填充
SELECT RPAD('殷小素',2,'ab') AS out_put;
9. replace(str,from_str,to_str); 用to_str替换str中的from_str
SELECT
REPLACE(
'张无忌爱上了周芷若',
'周芷若',
'赵敏'
) AS out_put ;
SELECT
REPLACE(
'张无忌周芷若周芷若爱周芷若上了周芷若',
'周芷若',
'赵敏'
) AS out_put ;
(二)、数学函数
1. round 四舍五入(取绝对值四舍五入后,加表示正负的符号)
**(1)round(X) **
SELECT ROUND(-1.499);
(2)round(X,D) 四舍五入,小数点后保留D位小数
SELECT ROUND(1.567,2);
2. ceil 向上取整,返回>=该参数的最小整数
SELECT CEIL(2.05);
3. floor 向下取整,返回<=该参数的最大整数
SELECT FLOOR(1.76);
4. truncate(X,D) 截断,只取小数点后的D位小数
SELECT TRUNCATE(1.69,1);
5. mod 取余,
mod(a,b) = a - a/b * b eg: mod(-10,-3) = -10 - (-10/-3)*-3 = -1
SELECT MOD(8,6);
(三)、日期函数
1. now 返回系统当前日期+时间
SELECT NOW();
2. curdate 返回系统当前日期,不包含时间
SELECT CURDATE();
3. curtime 返回当前时间,不包含日期
SELECT CURTIME();
4. 可以获取指定的部分,年,月,日,小时,分钟,秒
(1)获取当前系统的年份
SELECT YEAR(NOW());
(2)获取给定日期的年份
SELECT YEAR('2002-3-7');
(3)获取employees表中入职时间的年份
SELECT YEAR(hiredate) AS 年 FROM employees;
(4)获取当前系统的月份
SELECT MONTH(NOW()) AS 月;
(5)获取当前系统的月份,并按照英文写
SELECT MONTHNAME(NOW()) AS 月;
5. str_to_date(str,format) 将字符通过指定格式转化为日期
SELECT STR_TO_DATE('2002-3-7','%Y-%c-%d') AS out_put;
前面字符中的年月日之间的间隔是怎样的,后面格式format中保持一致
(1) 查询入职日期为2000-9-9 的员工信息
SELECT * FROM employees WHERE hiredate = '2000-9-9';
SELECT * FROM employees WHERE hiredate = STR_TO_DATE('9-9 2000','%c-%d %Y');
6. date_format(date,format) 将日期转化为字符
(1)将当前系统的日期转化为 XX年XX月XX日
SELECT DATE_FORMAT(NOW(),'%y年%m月%d日') AS out_put;
(2)查询有奖金的员工名和入职日期(xx月/xx日 xx年)
SELECT
last_name,
DATE_FORMAT(hiredate, '%m月/%d日 %y年') AS 入职时期
FROM
employees
WHERE commission_pct IS NOT NULL ;
%Y | 四位的年份 |
---|---|
%y | 2位的年份 |
%m | 月份(01,02,…11,12) |
%c | 月份(1,2,…11,12) |
%d | 日(01,02,…) |
%H | 小时(24小时制) |
%h | 小时(12小时制) |
%i | 分钟(00,01,…59) |
%s | 秒(00,01,…59) |
(四)、其他函数
1. select version(); 查看MySQL的版本号
2. select database(); 查看当前所在的数据库
3. select user(); 查看当前的用户
4. md5(‘字符’); 返回该字符的md5加密形式
5. sha1(‘字符’); 返回该字符的密码形式
(五)、流程控制函数
1. if(expr1,expr2,expr3):如果expr1为true,则返回expr2,否则返回expr3(if … else 的效果)
(1)如果10>5,则返回结果 大 ;否则,返回 小
SELECT IF(10>5,'大','小');
(2)查询员工姓名和奖金,并标出又没有奖金
SELECT
last_name,
commission_pct,
IF(
commission_pct IS NULL,
'没奖金',
'有奖金'
) AS out_put
FROM
employees ;
2. case函数
(1)case函数的使用一: switch case 的效果
switch (变量或表达式){
case 常量1: 语句1; break;
case 常量2: 语句2; break;
...
default: 语句n; break;
}
case 要判断的字段或表达式,
when 常量1 then 要显示的 值1 或 语句1;
when 常量2 then 要显示的 值2 或 语句2;
...
else 要显示的 值n 或 语句n;
end
语句后面要加分号 ;
(2)案例1:查询员工的工资,要求
部门号=30,显示的工资为1.1倍
部门号=40,显示的工资为1.2倍
部门号=50,显示的工资为1.3倍
其他部门,显示的工资为原工资
SELECT salary AS 原始工资,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;
(3)case函数的使用二: 类似于 多重if
if(条件1){
语句1;
} else if(条件2){
语句2;
}
...
else {
语句n;
}
case
when 条件1 then 要显示的 值1 或 语句1;
when 条件2 then 要显示的 值2 或 语句2;
...
else 要显示的 值n 或 语句n;
end
(4)案例2:查询员工的工资情况
如果工资>20000,显示A级别
如果工资>15000,显示B级别
如果工资>10000,显示C级别
否则,显示D级别
SELECT
salary AS 工资,
CASE
WHEN salary > 20000
THEN 'A'
WHEN salary > 15000
THEN 'B'
WHEN salary > 10000
THEN 'C'
ELSE 'D'
END AS 工资级别
FROM
employees ;
case函数块,if函数块,可以看作一个字段
(5)案例3:使用 case-when,按照下面的条件:
job | Grade |
---|---|
AD_PRES | A |
ST_MAN | B |
IT_PROC | C |
SA_REP | D |
ST_CLERK | E |
产生下面的结果
last_name | job_id | Grade |
---|---|---|
king | AD_PRES | A |
SELECT
last_name,job_id,
CASE
job_id
WHEN 'AD_PRES' THEN 'A'
WHEN 'ST_MAN' THEN 'B'
WHEN 'IT_PROC' THEN 'C'
WHEN 'SA_REP' THEN 'D'
WHEN 'ST_CLERK' THEN 'E'
END AS Grade
FROM
employees
WHERE job_id = 'AD_PRES';
四、分组函数
1. 功能:用于统计使用,又称为聚合函数或统计函数或组函数
2. 分类:sum(求和),avg(平均值),max(最大值),min(最小值),count(计算个数)
SELECT SUM(salary) FROM employees;
SELECT MAX(salary) FROM employees;
SELECT MIN(salary) FROM employees;
SELECT AVG(salary) FROM employees;
SELECT COUNT(salary) FROM employees;
SELECT
SUM(salary) AS 和,
MAX(salary) AS 最大值,
MIN(salary) AS 最小值,
ROUND(AVG(salary),2) AS 平均值,
COUNT(salary) AS 个数
FROM
employees ;
3. 特点
(1)sum,avg一般用于处理数值型;max,min,count 可以处理任何类型
(2)都忽略null值
SELECT
MAX(commission_pct),
MIN(commission_pct)
FROM
employees ;
SELECT
SUM(commission_pct),
AVG(commission_pct),
SUM(commission_pct) / 35,
SUM(commission_pct) / 107
FROM
employees ;
(3)可以和 distinct 搭配实现去重的运算
SELECT SUM(DISTINCT salary),SUM(salary) FROM employees;
SELECT AVG(DISTINCT salary),AVG(salary) FROM employees;
SELECT MAX(DISTINCT salary),MAX(salary) FROM employees;
SELECT MIN(DISTINCT salary),MIN(salary) FROM employees;
SELECT COUNT(DISTINCT salary),COUNT(salary) FROM employees;
4. count函数
**(1)**一般使用 count() **统计结果集的行数,(**count(),所有字段只要有一行不为null,就+1)
(2)**SELECT COUNT(1) FROM employees; **
count(常量值) 在所有字段旁边加了一列 常量 ,计算这个常量的数量,即所有字段的总行数
效率:在INNODB引擎下,count(*) 和 count(1) 的效率差不多,比 count(字段名) 要高一些
5. datediff(expr,expr2) 日期差,expr 大日期 ,expr2 小日期
**SELECT DATEDIFF('2023-2-14','2022-10-3');**
** **
(1)案例:查询员工表中的最大入职时间和最小入职时间的相差天数
SELECT DATEDIFF(MAX(hiredate),MIN(hiredate)) FROM employees;
进阶5: 分组查询
一、语法
select 分组函数,列(要求出现在 group by 的后面) (5)
from 表 (1)
【where 筛选条件】 (2)
group by 分组的列表 (3)
【having 分组后的筛选】 (4)
【order by 子句】 (6)
**注意:**查询列表必须特殊,要求是 分组函数 和 group by 后出现的字段
二、简单的分组查询
(1)查询每个工种的最高工资(group by 后面的列表就是工种)
SELECT
MAX(salary),
job_id
FROM
employees
GROUP BY job_id ;
(2)查询每个位置上的部门个数
SELECT
COUNT(*),
location_id
FROM
departments
GROUP BY location_id ;
三、添加分组前筛选条件(筛选条件包含的字段来自于from 表里的,用 where 筛选条件,在from后,group by 前面)
案例1:查询邮箱中包含a字符的,每个工资的平均工资
SELECT
AVG(salary),
department_id
FROM
employees
WHERE email LIKE '%a%'
GROUP BY department_id ;
案例2:查询有奖金的每个领导手下的最高工资
SELECT
MAX(salary) AS 最高工资,
manager_id
FROM
employees
WHERE commission_pct IS NOT NULL
GROUP BY manager_id ;
四、添加分组后的筛选条件(筛选条件包含的字段来自于分组后 group by 的结果,用 having ,在group by 后面)
案例1:查询哪个部门的员工个数>2
(1)查询每个部门的员工数
SELECT
COUNT(*),
department_id
FROM
employees
GROUP BY department_id ;
(2)根据(1)的结果进行筛选,查询哪个部门的员工个数>2
SELECT
COUNT(*) AS 数量,
department_id
FROM
employees
GROUP BY department_id
HAVING COUNT(*) > 2 ;
案例2:查询每个工种有奖金的员工的最高工资>12000的工种编号和最高工资
(1)查询每个工种有奖金的员工的最高工资
SELECT
MAX(salary),
job_id
FROM
employees
WHERE commission_pct IS NOT NULL
GROUP BY job_id ;
(2)根据(1)的结果继续筛选,最高工资>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的领导编号是哪个,以及最低工资
(1)查询每个领导手下的员工最低工资
SELECT
MIN(salary),
manager_id
FROM
employees
GROUP BY manager_id ;
(2)添加筛选条件: 编号 > 102
SELECT
MIN(salary),
manager_id
FROM
employees
WHERE manager_id > 102
GROUP BY manager_id
(3)添加筛选条件:最低工资 > 5000
SELECT
MIN(salary),
manager_id
FROM
employees
WHERE manager_id > 102
GROUP BY manager_id
HAVING MIN(salary) >5000;
五、特点
|
| 数据源 | 位置 | 关键字 |
| — | — | — | — |
| 分组前筛选 | 原始表 | group by 子句的前面 | where |
| 分组后筛选 | 分组后的结果集 | group by 子句的后面 | having |
(1)分组函数做条件肯定放在having 子句中
(2)能用分组前筛选的,就优先考虑使用前筛选
(3)group by 子句支持单个字段分组,多个字段分组(多个字段之间用逗号隔开,没有顺序要求),表达式或函数(用的较少)
(4)也可以添加排序(排序放在整个分组查询的最后)
六、按表达式或函数分组
1. 案例:按员工姓名的长度分组,查询每一组的员工个数,筛选员工个数>5的有哪些
(1)查询每个长度的员工个数
SELECT
LENGTH(last_name) AS len_name,
COUNT(*)
FROM
employees
GROUP BY LENGTH(Last_name);
(2)添加筛选条件
SELECT
LENGTH(last_name) AS len_name,
COUNT(*)
FROM
employees
GROUP BY LENGTH(Last_name)
HAVING COUNT(*)>5;
2. group by 和 having 后面 可以用 别名 (from 表 as 别名,那么其他地方就应该用 这个别名,因为from 表 是第一个执行)
SELECT
LENGTH(last_name) AS len_name,
COUNT(*) AS 个数
FROM
employees
GROUP BY len_name
HAVING 个数>5;
七、按照多个字段分组
案例1:查询每个部门每个工种的员工的平均工资
SELECT
AVG(salary),
department_id,
job_id
FROM
employees
GROUP BY department_id,
job_id ;
八、添加排序
案例1: 查询每个部门每个工种的员工的平均工资,并按平均工资的高低显示
SELECT
AVG(salary),
department_id,
job_id
FROM
employees
GROUP BY department_id,
job_id
ORDER BY AVG(salary) DESC ;
案例2:查询每个部门编号不为null,每个工种的员工的平均工资,且平均工资>10000,按平均工资的高低显示
SELECT
AVG(salary),
department_id,
job_id
FROM
employees
WHERE department_id IS NOT NULL
GROUP BY department_id,
job_id
HAVING AVG(salary) > 10000
ORDER BY AVG(salary) DESC ;
九、案例
(1)案例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)案例2:查询员工最高工资和最低工资的差距(DIFFERENCE)
SELECT
(MAX(salary) - MIN(salary)) AS DOFFERENCE
FROM
employees ;
(3)案例3:查询各个管理者手下员工的最低工资,其中最低工资不能低于6000,没有管理者的员工不计算于内
SELECT
MIN(salary),
manager_id
FROM
employees
WHERE manager_id IS NOT NULL
GROUP BY manager_id
HAVING MIN(salary) >= 6000 ;
(4)案例4:查询所有部门的编号,员工数量和工资平均值,并按平均工资降序
SELECT
department_id,
COUNT(*),
AVG(salary)
FROM
employees
GROUP BY department_id
ORDER BY AVG(salary) DESC ;
(5)案例5:选择具有各个job_id的员工人数
SELECT
COUNT(*),
job_id
FROM
employees
GROUP BY job_id ;
进阶6:连接查询(多表查询)
一、含义:当查询的字段来自于多个表时,就会用到连接查询
1. 笛卡尔乘积现象:表1 有m行 ,表2 有n行 ,结果= m*n 行
发生条件:没有有效的连接条件
如何避免:添加有效的连接条件
用一个表去匹配另一个表,当连接条件的值相等式,才筛选出来
二、分类
按年代分类 | 按功能分类 |
---|---|
SQL92 标准:仅仅支持内连接 | 内连接:等值连接+非等值连接+自连接 |
SQL99标准【推荐】:支持内连接+外连接(左外连接和右外连接)+交叉连接 | 外连接:左外连接+右外连接+全外连接 |
交叉连接 |
三、SQL92标准
(一)、等值连接
1. 特点
(1)多表等值连接的结果是多表的交集的部分
(2)n表连接,至少需要 n-1 个连接条件
(3)多表的顺序没有要求
(4)可以搭配前面介绍的所有子句使用,比如 排序, 分组 ,筛选
案例1: 查询女神名对应的男神名
SELECT
NAME,
boyName
FROM
boys,
beauty
WHERE beauty.boyfriend_id = boys.id ;
案例2: 查询员工名和对应的部门名
SELECT
last_name,
department_name
FROM
employees,
departments
WHERE departments.`department_id` = employees.`department_id` ;
2. 为表起别名
案例3:查询员工名,工种号,工种名
SELECT
last_name,
employees.job_id,
job_title
FROM
employees,
jobs
WHERE employees.`job_id` = jobs.`job_id` ;
SELECT
last_name,
e.job_id,
job_title
FROM
employees AS e,
jobs
WHERE e.`job_id` = jobs.`job_id` ;
特点:
(1)提高了语句的简洁度
(2)区分多个重名的字段
注意:如果为表起了别名,则查询的字段就不能使用 原来的表名 去 限定
3. 两个表的顺序可以调换
案例4: 查询员工名,工种号,工种名
SELECT
e.last_name,
e.job_id,
job_title
FROM
jobs,
employees AS e
WHERE e.`job_id` = jobs.`job_id` ;
4. 可以加上筛选
案例5:查询有奖金的员工名,部门号
SELECT
last_name,
department_name,
commission_pct
FROM
employees,
departments
WHERE departments.department_id = employees.`department_id`
AND employees.`commission_pct` IS NOT NULL ;
案例6:查询城市名中第二个字符为o的部门名和城市名
SELECT
department_name,
city
FROM
departments,
locations
WHERE locations.location_id = departments.`location_id`
AND city LIKE '_o%' ;
5. 可以加分组
案例7:查询每个城市的部门个数
SELECT
COUNT(*) 个数,
city
FROM
departments,
locations
WHERE departments.`location_id` = locations.`location_id`
GROUP BY city ;
案例8:查询有奖金的每个部门的部门名和部门的领导编号和该部分的最低工资
SELECT
department_name,
employees.manager_id,
MIN(salary)
FROM
departments,
employees
WHERE departments.`department_id` = employees.`department_id`
AND commission_pct IS NOT NULL
GROUP BY department_name,departments.`manager_id` ;
6. 可以加排序
案例9:查询每个工种的工种名和员工的个数,并且按照员工个数降序
SELECT
job_title,
COUNT(*)
FROM
jobs,
employees
WHERE jobs.`job_id` = employees.`job_id`
GROUP BY job_title
ORDER BY COUNT(*) DESC ;
SELECT
job_title,
COUNT(*)
FROM
jobs AS j,
employees AS e
WHERE j.`job_id` = e.`job_id`
GROUP BY job_title
ORDER BY COUNT(*) DESC ;
7. 三表连接
案例10:查询员工名,部门名和所在的城市
SELECT
last_name,
department_name,
city
FROM
employees,
departments,
locations
WHERE departments.`department_id` = employees.`department_id`
AND departments.`location_id` = locations.`location_id` ;
案例11:查询员工名,部门名和所在的以s开头的城市,并将城市名按照降序排
SELECT
last_name,
department_name,
city
FROM
employees,
departments,
locations
WHERE departments.`department_id` = employees.`department_id`
AND departments.`location_id` = locations.`location_id`
AND city LIKE 's%'
ORDER BY department_name DESC ;
(二)、非等值连接
案例1:查询员工的工资和工资级别
SELECT
salary,
grade_level
FROM
employees,
job_grades
WHERE salary BETWEEN lowest_sal
AND highest_sal
(三)、自连接(把原本的表当作二张表或更多张表使用)(自己连接自己)
案例1:查询员工名和上级名
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` ;
(六)、测试题
案例1:显示所有员工的姓名,部门号和部门名称
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
d.department_id,
e.job_id,
d.location_id
FROM
departments d,
employees e
WHERE d.`department_id` = e.`department_id`
AND d.department_id = 90 ;
案例3:选择所有有奖金的员工的 last_name,department_name,location_id,city
SELECT
e.last_name,
d.department_name,
l.location_id,
l.city,
e.`commission_pct`
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
e.last_name,
e.job_id,
e.department_id,
d.department_name,
l.`city`
FROM
employees e,
departments d,
locations l
WHERE e.`department_id` = d.`department_id`
AND d.`location_id` = l.`location_id`
AND l.`city` = 'Toronto' ;
案例5:查询每个工种,每个部门的部门名,工种名和最低工资
SELECT
j.job_id,
e.department_id,
d.department_name,
j.job_title,
MIN(salary)
FROM
departments d,
employees e,
jobs j
WHERE d.`department_id` = e.`department_id`
AND j.`job_id` = e.`job_id`
GROUP BY job_id,
department_id ;
案例6:查询每个国家下的部门个数大于2的国家编号
SELECT
country_id,
COUNT(*) 部门数
FROM
locations l,
departments d
WHERE l.`location_id` = d.`location_id`
GROUP BY country_id
HAVING COUNT(*) > 2 ;
案例7:选择指定员工的姓名,员工名,以及他的管理者的姓名和员工号,结果类似于下面的格式
employees | Emp# | manager | Mgr# |
---|---|---|---|
kochhar | 101 | king | 100 |
SELECT
e1.last_name AS "employees",
e1.employee_id AS "Emp#",
e2.last_name AS manager,
e2.employee_id AS "Mgr#"
FROM
employees e1,
employees e2
WHERE e1.`manager_id` = e2.`employee_id`
AND e1.`employee_id` = 101 ;
四、SQL99标准
(一)、语法
select 查询列表
from 表1 别名 【连接类型】
join 表2 别名
on 连接条件
【where 筛选条件】
【group by 分组】
【having 筛选条件】
【order by 排序列表】
分类:
内连接: inner
外连接:
左外连接:left 【outer】
右外连接:rigth 【outer】
全外连接:full 【outer】
交叉连接: cross
(二)、内连接
1. 语法
select 查询列表
from 表1 别名
inner join 表2 别名
on 连接条件
2. 等值连接
案例一:查询员工名,部门名
SELECT
last_name,
department_name
FROM
employees e
INNER JOIN departments d
ON e.`department_id` = d.`department_id` ;
ELECT
last_name,
department_name
FROM
departments d
INNER JOIN employees e
ON e.`department_id` = d.`department_id` ;
案例二:查询名字中包含e的员工名和工种名(筛选)
SELECT
last_name,
job_title
FROM
employees e
INNER JOIN jobs j
ON e.`job_id` = j.`job_id`
WHERE last_name LIKE '%e%' ;
案例三:查询部门个数>3的城市名和部门个数(分组+筛选)
(1) 查询每个城市的部门个数
(2) 在(1)结果上筛选满足条件的
SELECT
city,
COUNT(*) 部门个数
FROM
departments d
INNER JOIN locations l
ON d.`location_id` = l.`location_id`
GROUP BY city
HAVING COUNT(*) > 3 ;
案例四:查询哪个部门的员工个数>3的部门名和员工个数,并按个数降序(添加排序)
(1) 查询每个部门的员工个数
SELECT
COUNT(*) 员工个数,
department_name
FROM
employees e
INNER JOIN departments d
ON e.`department_id` = d.`department_id`
GROUP BY department_name ;
(2) 在(1)的结果上筛选员工个数>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 ;
案例五:查询员工名,部门名,工种名,并按部门名降序(添加三表连接)
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 ;
(1) 多表(>2)连接的时候,前后表要有连接条件(表有顺序)
(2) from 表1 别名
inner join 表2 别名 on 连接条件1
inner join 表3 别名 on 连接条件2
…
等值连接的特点:
(1) 可以添加排序,分组,筛选
(2) inner 可以忽略
(3) 筛选条件放在where 后面 ,连接条件放在 on 后面 ,提高分离性 ,便于阅读
(4) inner join 连接 和 SQL92 语法中的等值连接效果是一样的,都是查询多表的交集
3. 非等值连接
案例1: 查询员工的工资等级
SELECT
salary,
grade_level
FROM
employees e
INNER JOIN job_grades j
ON e.`salary` BETWEEN j.`lowest_sal`
AND j.`highest_sal` ;
案例2: 查询工资级别的个数>20的个数,并且按工资级别降序
SELECT
COUNT(*),
grade_level
FROM
employees e
INNER JOIN job_grades j
ON e.`salary` BETWEEN j.`lowest_sal`
AND j.`highest_sal`
GROUP BY grade_level
HAVING COUNT(*) > 20
ORDER BY grade_level DESC ;
4. 自连接(把原本的表当作二张表或更多张表使用)(自己连接自己)
案例1:查询员工的名字,上级的名字
SELECT
e.last_name AS employees,
e.employee_id AS "Emp#",
m.last_name AS manager,
m.employee_id AS "Mge#"
FROM
employees e
INNER JOIN employees m
ON e.`manager_id` = m.`employee_id` ;
案例2: 查询姓名中包含k的员工名字,上级的名字
SELECT
e.last_name AS employees,
e.employee_id AS "Emp#",
m.last_name AS manager,
m.employee_id AS "Mge#"
FROM
employees e
INNER JOIN employees m
ON e.`manager_id` = m.`employee_id`
WHERE e.last_name LIKE '%k%';
(三)、外连接(外连接的结果=内连接的结果 + 主表有,从表没有(从表为null))
1. 应用场景:用于查询一个表中有,另一个表没有的记录
2. 特点
(1) 外连接的查询结果为主表中的所有记录
如果从表中有和主表匹配的,则显示匹配的值
如果从表中没有主表匹配的,则显示null值
(2) 左外连接, left join 左边的是 主表
右外连接, right join 右边的是 主表
(3) 左外和右外 交换两个表的顺序,(主表和从表不会改变),可以实现同样的效果
3. 主表:你要查询的信息主要来自于哪个表,则就是主表
4. 案例
(1)案例1:查询男朋友 不在男神表的女神名
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 ;
(2) 案例2:查询哪个部门没有员工
SELECT
d.department_name,
d.department_id
FROM
departments d
LEFT OUTER JOIN employees e
ON d.`department_id` = e.`department_id`
WHERE e.`employee_id` IS NULL ;
SELECT
d.department_name,
d.department_id
FROM
employees e
RIGHT OUTER JOIN departments d
ON d.`department_id` = e.`department_id`
WHERE e.`employee_id` IS NULL ;
5. 全外连接 = 内连接的结果 + 表1中有但表2中没有的 + 表2中有但表1中没有
(四)、交叉连接(使用99语法标准实现笛卡尔乘积)
SELECT *
FROM beauty b
CROSS JOIN boys bo;
结果是48行, beauty 表匹配 boys 表的每一行
(五)、SQL92 和 SQL 99
功能: sql 99 支持更多
可读性:sql99 实现连接条件和筛选条件的分离,可读性较高
(六)、测试题
**案例1:**查询编号 >3 的女神的男朋友信息,如果有则列出详细,如果没有,用null填充(主表是beauty,从表是boys)
SELECT
b.`name`,
bo.*
FROM
beauty b
LEFT OUTER JOIN boys bo
ON bo.`id` = b.`boyfriend_id`
WHERE b.`id` > 3 ;
**案例2:**查询哪个城市没有部门(locations为主表,departments为从表,让从表的主键为null)
SELECT
city,
d.`department_id`
FROM
locations l
LEFT OUTER JOIN departments d
ON l.`location_id` = d.`location_id`
WHERE d.department_id IS NULL ;
**案例3:**查询部门名为SAL或IT的员工信息( departments为主表,employees为从表,(因为有些部门没有员工))
SELECT
e.*,
d.`department_name`
FROM
employees e
RIGHT OUTER JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE d.`department_name` = 'SAL'
OR d.`department_name` = 'IT' ;
进阶7: 子查询
一、含义
出现在其他语句(删除,添加,查询)中的select 语句,称为 内查询或子查询;外部的语句可以是select , insert , update ,delete 等语句,一般为查询语句,称为主查询 或 外查询
1. 分类
(1)按子查询出现的位置:
select 后面: 仅仅支持标量子查询
from 后面: 支持 表子查询
where 或 having 后面:⭐
标量子查询(一行) ✅
**列子查询(多行) ✅ **
行子查询(多列多行)
**exists **后面(相关子查询):表子查询
(2)按结果集的行列数不同分类:
标量子查询(结果集只有一行一列)
列子查询(结果集只有一列多行)
行子查询(结果集只有一行多列,<多列多行 也可以>)
表子查询(结果集一般为多行多列)
二、where 或 having 后面的子查询
(一)、分类:
- 标量子查询(单行子查询)
- 列子查询(多行子查询)
- 行子查询(多列多列子查询)
特点:
- 子查询放在小括号内
- 子查询一般放在条件的右侧
- 标量子查询,一般搭配着单行操作符使用 ** > < >= <= = <>**
- 列子查询,一般搭配着多行操作符使用,** in any/some all**
- 子查询的执行优先于主查询执行,主查询的条件用到了子查询的结果
(二)、标量子查询(单行子查询)
a. 案例
- 谁的工资比 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') ;
- 返回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 salary >
(SELECT
salary
FROM
employees
WHERE employee_id = 143)
AND job_id =
(SELECT
job_id
FROM
employees
WHERE employee_id = 141) ;
- 返回公司工资最少的员工的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) ;
- 查询最低工资大于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
WHERE department_id = 50);
(三)、列子查询(多行子查询)
- 返回多行
- 使用多行操作符
操作符 | 含义 |
---|---|
in / not in | 等于列表中的任意一个 |
any /some | 和子查询返回的某一个值比较 |
**all ** | 和子查询返回的所有值比较 |
a. 案例
- 返回location_id 是1400或1700的部门中的所有员工姓名
(1)查询location_id是1400或1700的部门编号
SELECT DISTINCT
department_id
FROM
departments
WHERE location_id IN (1400, 1700) ;
(2)查询员工姓名,要求部门号是(1)列表中的某一个
SELECT
last_name
FROM
employees
WHERE department_id IN
(SELECT DISTINCT
department_id
FROM
departments
WHERE location_id IN (1400, 1700));
SELECT
last_name
FROM
employees
WHERE department_id =ANY
(SELECT DISTINCT
department_id
FROM
departments
WHERE location_id IN (1400, 1700));
- 返回其他工种中比job_id为 ‘IT_PRPC’ 工种任一工资低的员工的员工号,姓名,job_id,以及salary
(1)查询job_id为 ‘IT_PRPC’ 工种的任一工资
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 < SOME
(SELECT
DISTINCT salary
FROM
employees
WHERE job_id = 'IT_PROG') AND job_id <> 'IT_PROG';
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' ;
- 返回其他工种中比job_id为 ‘IT_PRPC’ 工种所有工资低的员工的员工号,姓名,job_id,以及salary
SELECT
employee_id,
last_name,
job_id,
salary
FROM
employees
WHERE salary < ALL
(SELECT DISTINCT
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' ;
(四)、行子查询(结果集为一行多列或多行多列)
案例:查询员工编号最小并且工资最高的员工信息(可能会不存在)
(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 后面(仅仅支持标量子查询,即一行一列)
案例:查询每个部门的员工个数
SELECT *,(
SELECT COUNT(*)
FROM employees e
WHERE e.department_id=d.`department_id`
) 个数
FROM departments d;
案例:查询员工号=102的部门名
SELECT (
SELECT d.department_name
FROM departments d
INNER JOIN employees e
ON e.department_id = d.department_id
WHERE e.employee_id = 102
) 部门名;
SELECT
department_name 部门号
FROM
departments d
WHERE d.`department_id` =
(SELECT
department_id
FROM
employees
WHERE employee_id = 102) ;
四、from 后面
1. 注意:将子查询结果充当一张表,要求必须起别名
2. 案例:查询每个部门的平均工资的工资等级
(1)查询每个部门的平均工资
SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id;
(2) 连接(1)的结果集和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 g.lowest_sal AND g.highest_sal ;
五、exists 后面 (相关子查询)(还没弄懂)
(一)、语法
**exists (完整的查询语句)**
结果: 1 或 0
(二)、案例
- 查询有员工的部门名
SELECT DISTINCT department_name
FROM employees e
INNER JOIN departments d
ON e.`department_id`=d.`department_id`;
SELECT department_name
FROM departments d
WHERE EXISTS (
SELECT *
FROM employees e,departments d
WHERE d.`department_id`=e.`department_id`
);
SELECT department_name
FROM departments d
WHERE d.`department_id` IN (
SELECT department_id
FROM employees e
);
- 查询没有女朋友的男神信息
SELECT bo.*
FROM boys bo
WHERE bo.id NOT IN (
SELECT boyfriend_id
FROM beauty
);
SELECT bo.*
FROM boys bo
WHERE NOT EXISTS (
SELECT boyfriend_id
FROM beauty b
WHERE bo.`id`=b.`boyfriend_id`
);
(三)、exists(子查询) ,这里子查询一般为连接查询
exists(查询语句),判断子查询的结果有没有值,结果为0或1 (相当于 bool 类型)
六、测试题
- 查询和 zlotkey 相同部门的员工姓名和工资
SELECT last_name,salary
FROM employees e
WHERE e.`department_id` = (
SELECT department_id
FROM employees
WHERE last_name = 'Zlotkey'
);
- 查询工资比公司平均工资高的员工的员工号,姓名和工资
(1) 公司的平均工资
SELECT AVG(salary)
FROM employees;
(2) 查询比(1)高的员工号,姓名,工资
SELECT employee_id,last_name,salary
FROM employees
WHERE salary > (
SELECT AVG(salary)
FROM employees
);
- 查询各部门中工资比本部门平均工资高的员工的员工号,姓名和工资
(1) 每个部门的平均工资
SELECT department_id,AVG(salary)
FROM employees
GROUP BY department_id;
(2) 每个部门比本部门平均工资高的员工号,姓名,工资
SELECT employee_id,last_name,salary
FROM employees e
INNER JOIN (
SELECT department_id,AVG(salary) avgg
FROM employees
GROUP BY department_id) AS avg_dep
ON e.department_id IN (avg_dep.department_id)
WHERE e.salary >avg_dep.avgg;
SELECT employee_id,last_name,salary,e.`department_id`
FROM employees e
INNER JOIN (
SELECT department_id,AVG(salary) avgg
FROM employees
GROUP BY department_id) AS avg_dep
ON e.department_id =avg_dep.department_id
WHERE e.salary >avg_dep.avgg;
- 查询和姓名中含字母u的员工在相同部门的员工的员工号和姓名
(1) 查询姓名中包含u的员工的部门
SELECT DISTINCT department_id
FROM employees e
WHERE e.last_name LIKE '%u%';
(2) 查询和(1)任一部门号相同的员工的员工号和姓名
SELECT e2.employee_id,e2.last_name
FROM employees e2
WHERE e2.department_id IN (
SELECT DISTINCT department_id
FROM employees e
WHERE e.last_name LIKE '%u%'
);
- 查询在部门的location_id=1700的部门工作的员工的员工号
(1) 查询location_id = 1700 的部门号
SELECT department_id
FROM departments d
WHERE d.`location_id` = 1700;
(2) 查询部门号在(1)中的员工的员工号
SELECT employee_id
FROM employees e
WHERE e.department_id IN (
SELECT department_id
FROM departments d
WHERE d.`location_id` = 1700
);
SELECT employee_id
FROM employees e
WHERE e.department_id =ANY (
SELECT department_id
FROM departments d
WHERE d.`location_id` = 1700
);
- 查询管理者是k_ing 的员工姓名和工资
(1) 查询姓名为k_ing的员工号
SELECT employee_id
FROM employees e
WHERE last_name = 'k_ing'
(2) 查询manger_id = (1)的员工姓名和工资
SELECT e2.last_name,e2.salary
FROM employees e2
WHERE e2.manager_id IN (
SELECT employee_id
FROM employees e
WHERE last_name = 'k_ing'
);
- 查询工资最高的员工的姓名,要求first_name和last_name显示为一列,列明为 姓.名
(1) 查询最高的工资
SELECT MAX(salary)
FROM employees;
(2)查询工资等于 (1)的员工的姓名
SELECT CONCAT(first_name,last_name) AS "姓.名"
FROM employees e
WHERE salary = (
SELECT MAX(salary)
FROM employees
);
七、测试题
- 查询工资最低的员工信息:last_name,salary
(1) 查询最低的工资
SELECT MIN(salary) FROM employees
(2) 查询工资=(1)的last_name,salary
SELECT last_name,salary
FROM employees e
WHERE e.salary = (
SELECT MIN(salary) FROM employees
);
- 查询平均工资最低的部门信息
方式一
(1) 查询每个部门的平均工资
SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id
(2) 最低的平均工资
SELECT MIN(avgg)
FROM (
SELECT AVG(salary) avgg
FROM employees
GROUP BY department_id
) avgg_dep
(3) 平均工资=(2)的department_id
SELECT avg_dep.department_id
FROM (
SELECT AVG(salary) avggg,department_id
FROM employees
GROUP BY department_id
) avg_dep
WHERE avg_dep.avggg = (
SELECT MIN(avgg)
FROM (
SELECT AVG(salary) avgg
FROM employees
GROUP BY department_id
) avgg_dep
)
SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary) = (
SELECT MIN(avgg)
FROM (
SELECT AVG(salary) avgg
FROM employees
GROUP BY department_id
) avgg_dep
)
(4) 部门编号=(3)的信息
SELECT *
FROM departments d
WHERE d.department_id = (
SELECT avg_dep.department_id
FROM (
SELECT AVG(salary) avggg,department_id
FROM employees
GROUP BY department_id
) avg_dep
WHERE avg_dep.avggg = (
SELECT MIN(avgg)
FROM (
SELECT AVG(salary) avgg
FROM employees
GROUP BY department_id
) avgg_dep
)
);
SELECT *
FROM departments d
WHERE d.department_id = (
SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary) = (
SELECT MIN(avgg)
FROM (
SELECT AVG(salary) avgg
FROM employees
GROUP BY department_id
) avgg_dep
)
);
方式二
(1)查询各部门的平均工资,并按平均工资升序排列,使用分页查询,显示第一行(即最低平均工资对应的部门号)
SELECT department_id
FROM employees
GROUP BY department_id
ORDER BY AVG(salary) ASC
LIMIT 0,1
(2)部门号=(1)的部门信息
SELECT d.*
FROM departments d
WHERE d.department_id = (
SELECT department_id
FROM employees
GROUP BY department_id
ORDER BY AVG(salary) ASC
LIMIT 0,1
);
- 查询平均工资最低的部门信息和该部门的平均工资
(1)查询各部门的平均工资,并按平均工资升序排列,使用分页查询,显示第一行(即最低平均工资对应的部门号)
SELECT department_id ,AVG(salary)
FROM employees
GROUP BY department_id
ORDER BY AVG(salary) ASC
LIMIT 0,1
(2)部门号=(1)的部门号的部门信息
SELECT d.*
FROM departments d
WHERE d.department_id = (
SELECT department_id
FROM employees
GROUP BY department_id
ORDER BY AVG(salary) ASC
LIMIT 0,1
);
(3) 连接(1)(2)
SELECT de_min.*,min_sa.ag 平均工资
FROM (
SELECT department_id ,AVG(salary) ag
FROM employees
GROUP BY department_id
ORDER BY AVG(salary) ASC
LIMIT 0,1
) min_sa
INNER JOIN (
SELECT d.*
FROM departments d
WHERE d.department_id = (
SELECT department_id
FROM employees
GROUP BY department_id
ORDER BY AVG(salary) ASC
LIMIT 0,1
)
) de_min
ON de_min.department_id = min_sa.department_id;
- 查询平均工资最高的job信息
(1)查询平均工资最高的job_id
SELECT job_id
FROM employees
GROUP BY job_id
ORDER BY AVG(salary) DESC
LIMIT 0,1
(2)工种号=(1)的信息
SELECT j.*
FROM jobs j
WHERE j.job_id = (
SELECT job_id
FROM employees
GROUP BY job_id
ORDER BY AVG(salary) DESC
LIMIT 0,1
);
- 查询平均工资高于公司的平均工资的部门有哪些?
(1) 查询公司的平均工资
SELECT AVG(salary) FROM employees
(2) 查询每个部门的平均工资
SELECT AVG(salary) ag,department_id d_id
FROM employees
GROUP BY department_id
(3) 在(2)的基础上,查询部门平均工资>(1)的部门
SELECT de_sal.d_id
FROM (
SELECT AVG(salary) ag,department_id d_id
FROM employees
GROUP BY department_id
) de_sal
WHERE de_sal.ag >(
SELECT AVG(salary) FROM employees
);
SELECT department_id d_id
FROM employees
GROUP BY department_id
HAVING AVG(salary) > (
SELECT AVG(salary) FROM employees
);
- 查询出公司中所有的manager信息
(1)查询所有的manager_id(不为null)
SELECT DISTINCT manager_id FROM employees
WHERE manager_id IS NOT NULL
(2) 查询员工号 = (1)的信息
SELECT e.* FROM employees e
WHERE e.employee_id IN (
SELECT DISTINCT manager_id FROM employees
WHERE manager_id IS NOT NULL
);
- 各个部门中 最高工资中最低的那个部门 的最低工资是多少
(1) 查询每个部门的最高工资
SELECT MAX(salary) max_sa,department_id FROM employees
GROUP BY department_id
(2)查询(1)中的最小的工资对应的部门号
SELECT department_id
FROM employees
GROUP BY department_id
ORDER BY MAX(salary) ASC
LIMIT 0,1
(3)查询每个部门的最低工资
SELECT MIN(salary ),department_id
FROM employees
GROUP BY department_id
(4) 查询(3)中department_id=(2)的信息
SELECT min_sal.*
FROM (
SELECT MIN(salary ) ag_min,department_id
FROM employees
GROUP BY department_id
) min_sal
INNER JOIN (
SELECT department_id
FROM employees
GROUP BY department_id
ORDER BY MAX(salary) ASC
LIMIT 0,1
) max_min_dep
ON max_min_dep.department_id IN (min_sal.department_id )
SELECT MIN(salary)
FROM employees e
WHERE e.department_id = (
SELECT department_id
FROM employees
GROUP BY department_id
ORDER BY MAX(salary) ASC
LIMIT 0,1
)
- 查询平均工资最高的部门的 manager 的详细信息:last_name, department_id, email ,salasry
(1) 查询每个部门的平均工资
SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id
(2)在(1)上最高的工资对应的部门号
SELECT department_id
FROM employees
GROUP BY department_id
ORDER BY AVG(salary) DESC
LIMIT 0,1
(3)将employees 和 departments 连接查询,筛选条件是(2)
部门的领导只有一个,员工的领导有多个
SELECT last_name,d.department_id,email,salary
FROM employees e
INNER 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 0,1
);
进阶8:分页查询
一、语法
1. 应用场景:当要显示的数据,一页显示不全,需要分页提交sql请求
执行顺序
select 查询列表 (7)
from 表1 (1)
【【连接类型】join 表2 (2)
on 连接条件 (3)
where 筛选条件 (4)
group by 分组字段 (5)
having 分组后的筛选 (6)
order by 排序的字段】 (8)
limit offset,size; (9)
offset : 要显示条目的起始索引(起始索引从0开始)
size : 要显示的条目
offset 的值 从0(第一条信息)开始,可以省略
limit 语法排在最后, 执行在 最后
(5)(6)都为虚拟的表,(7)将最后的表显示出来后,(8)排序在显示的表的基础上进行
二、案例
(一)、查询前5条员工信息
SELECT * FROM employees LIMIT 0,5;
(二)、 查询有奖金的员工信息,并且工资较高的前10名显示出来
SELECT
*
FROM
employees
WHERE commission_pct IS NOT NULL
ORDER BY salary DESC
LIMIT 0, 10 ;
- 用子查询
(1) 有奖金的员工信息
SELECT * FROM employees WHERE commission_pct IS NOT NULL
(2) 在(1)基础上,按工资排名
SELECT *
FROM (SELECT * FROM employees WHERE commission_pct IS NOT NULL) sall
ORDER BY salary DESC
(3) 取前10名
SELECT *
FROM (
SELECT *
FROM (SELECT * FROM employees WHERE commission_pct IS NOT NULL) sall
ORDER BY salary DESC
) high_sall
LIMIT 0,10;
(三)、查询第11条-第25条的员工信息
SELECT * FROM employees LIMIT 10,15;
三、特点
1. limit 语句放在查询语句的最后
2. 公式
要显示的页数 page , 每页的条目数 size
select 查询列表
from 表
limit (page-1)*size , size ;
size = 10
第1页 | 0 |
---|---|
第2页 | 10 |
第3页 | 20 |