3、DQL (data query language)
3.1基础查询
3.1.1、查询表中的单个字段
SELECT
last_name
FROM
employees;
3.1.2、查询表中的多个字段
SELECT
last_name,
email,
salary
FROM
employees ;
3.1.3、查询表中的所有字段
SELECT
*
FROM
employees ;
3.1.4、查询常量值 表达式 函数
SELECT 100;
SELECT 100*98;
SELECT version();
3.1.5、起别名
SELECT
100%98 AS '结果'; --便于理解,如果要查询的字段有重名的情况,使用别名可以区分
SELECT last_name 姓; --AS 可以省略用空格代替
3.1.6、去重
SELECT DISTINCT --使用distinct去重
`department_id`
FROM
employees ;
3.1.7、关于 + /concat
**例:**查询员工的first_name和last_name,并连接成一个字段
SELECT
CONCAT(first_name, last_name) AS 姓名
FROM
employees ;
/*使用函数concat 连接
sql中,+只能运算*/
3.2、条件查询
SELECT
查询列表
FROM
表名
WHERE
筛选条件;
3.2.1、按条件表达式筛选
**条件运算符:> < = !=(<>) >= <= **
例1:查询工资>12000的员工信息
SELECT
*
FROM
employees
WHERE salary > 12000 ;
例2:查询部门编号不等于90的员工名和部门编号
SELECT
first_name,
department_id
FROM
employees
WHERE department_id <> 90 ;
3.2.2、 按逻辑表达式筛选
&& || ! and or not
例1:查询工资10000-20000之间的员工名,工资,奖金
SELECT
first_name,
salary,
commission_pct
FROM
employees
WHERE salary > 10000
AND salary < 20000 ;
案例2:查询部门编号不在90-110或者工资高于15000的员工信息
SELECT
*
FROM
employees
WHERE department_id < 90 || department_id > 110 || salary > 15000 ;
3.2.3、模糊查询
like between and in is null
like 案例1:查询员工名中包含a 的员工信息
SELECT
*
FROM
employees
WHERE last_name LIKE '%a%' ;
/*一般和通配符搭配使用
%-->任意多个字符 包含0个
_ -->任意单个字符
escape '$'-->转义*/
案例2:查询员工名字中第三个字符为e 第五个字符为a 的员工名和工资
SELECT
`first_name`,
`salary`
FROM
employees
WHERE `first_name` LIKE '__e_a%' ;
between/and (包含临界值)
案例:查询员工编导在100-120之间的员工信息
SELECT
*
FROM
employees
WHERE `employee_id` BETWEEN 100 AND 200 ;
in
案例:查询员工的工种编号 为IT_PROT, AD_VP,AD_PRES中的一个的 员工名和工种编号
SELECT
`first_name`,
`job_id`
FROM
employees
WHERE `job_id` IN ('IT_PROT', 'AD_VP', 'AD_PRES') ;
is null(=运算符,不能判断null值)
案例:查询没有奖金的员工名和奖金率
SELECT
`first_name`,
`commission_pct`
FROM
employees
WHERE `commission_pct` IS NULL ;
安全等于 <=>可以判断null值 也可以判断普通类型,但是可读性较低 不推荐
3.3排序查询
SELECT
*
FROM
table
WHERE
条件 ORDER BY 排序列表 [asc/desc]
案例:查询员工信息,要求工资从高到底排序
SELECT
*
FROM
employees
ORDER BY salary DESC ;
3.4、常见函数
调用:select 函数名(实参列表) 【from 表】;
3.4.1 单行函数
-
字符函数
length() —> 获取参数值的字节个数
concat() —> 拼接字符串
select concat(last_name,'_',firstname) from employees;
upper/lower —> 大小写
select concat(upper(last_name),'_',lower(first_name)) as 姓名 from employees;
substring —> 截取
sql索引都是从1开始的
select substr('123456',4) out_put;--从4开始到结束的所有字符 select substr('123456',1,3) out_put;-- 从1 开始截取, 字符长度为3
instr —> 查找第一次出现的位置 返回索引,如果不存在,返回0
select instr('1234567','1') as out_put;
trim —> 去前后空格
—>去掉前后自定义字符
select trim(' 123 ') as out_put; select trim('a' from 'aaaaa123aaaaaaa') as out_put;
lpad —> 用指定字符实现左填充指定长度
rpad —>右填充
select lpad('123',10,'*') as out_put; select rpad('123',10,'*') as out_put;
replace —> 替换
select replace('张无忌喜欢周芷若','周芷若','赵敏') as out_put;
-
数学函数
round —>四舍五入
select round(1.65); select round(1.658,2);--小数点后保留两位
ceil —>向上取整,返回大于等于该参数的最小整数
select ceil(1.65);-- 2 select ceil(1.00);-- 1
floor —>向下取整
select floor(1.65);-- 1
truncate —> 截断
select truncate(1.65,1)-- 1.6
mod —>取余
a-a/b*b
select mod(10,3);-- 1
-
日期函数
now 返回当前系统日期+时间
select now();
curdate 返回当前日期,不包含时间
select curdate();
curtime 当前时间 不包含日期
获取指定部分,年 月 日 小时 分钟 秒
select year(now()); select month(now()); select monthname(now());
str_to_date 将日期格式的字符,转换成指定格式的日期
date_formate 将日期转换成字符
str_to_date('9-13-2020','%m-%d-%Y') date_formate('2018/6/6','%Y年%m月%d日')
1 %Y 四位的年份 2 %y 两位年份 3 %m 月份 01 02 4 %c 月份 1 2 5 %d 日 01 02 6 %H 小时 24H 7 %h 小时 12H 8 %i 分钟 00 01 9 %s 秒 01 02
-
流程控制函数
if函数:if-else效果
select if(10>5,'大','小');
case函数:
1 switch case 效果
case 要判断的字段或者表达式 when 常量1 then 要显示的值1或语句1 when 常量2 then 要显示的值2或语句2 ... else 要显示的值n 或语句 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;
2.类似于多重if
case when 条件1 then 显示的值/表达式 when 条件2 then 显示的值/表达式 when 条件2 then 显示的值/表达式 ... else 显示的值/表达式 end
案例:查询工资,如果>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;
3.4.2 分组函数 (用作统计使用)
包括 sum 求和, avg 平均值,max,min,count 计算个数
-
简单使用
select sum(salary) from employees; SELECT AVG(salary) from employees; SELECT MAX(salary) from employees; SELECT MIN(salary) from employees; SELECT COUNT(salary) from employees; SELECT SUM(salary),round(AVG(salary),2) from employees;
-
参数支持哪些类型
- sum,avg 处理数值型
- max, min,count 处理任何类型
-
是否忽略null
-
和distinct搭配(去重)
SELECT SUM(DISTINCT salary),SUM(salary) FROM employees;
-
count函数详细介绍
select count(*) from employees;--行数
-
和分组函数一起查询的字段有限制
select avg(salary),employee_id from employees; --后面查询employee_id 的部分没有意义。
SELECT DATEDIFF(MAX(hiredate),MIN(hiredate)) AS difference FROM employees;
--最大 和 最小入职天数 相差多少天
SELECT COUNT(*) FROM employees WHERE department_id =90;
--部门ID 为90的员工个数
3.5 分组查询
分组查询的筛选条件分为两类,分组前筛选/分组后筛选(数据源不同)
select 分组函数 ,列(要求出现在group by后面)
from 表
where 筛选条件
group by 分组的列表
order by asc/desc;
3.5.1 添加分组前筛选
查询每个工种的最高工资
SELECT
MAX(salary),
job_id
FROM
employees
GROUP BY job_id ;
查询邮箱中包含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 ;
3.5.2 添加分组后筛选
查询哪个部门的员工个数大于2
SELECT
COUNT(*),
department_id
FROM
employees
GROUP BY department_Id
HAVING COUNT(*) > 2 ;
查询每个工种有奖金的员工的最高工资>12000 的工种编号和最高工资
SELECT
MAX(salary),
job_id
FROM
employees
WHERE commission_pct IS NOT NULL
GROUP BY job_id
HAVING MAX(salary) > 12000 ;
3.5.3 按表达式或函数分组
按员工姓名的长度分组,查询每一组员工的个数,筛选员工个数>5 的有哪些
SELECT
COUNT(*),
LENGTH(last_name)
FROM
employees
GROUP BY LENGTH(last_name)
HAVING COUNT(*)>5;
3.5.4 按多个字段分组
查询每个部门 每个工种的员工的平均工资
SELECT
AVG(salary),
department_id,
job_id
FROM
employees
GROUP BY department_id,
job_id ;
3.5.5 添加排序
**查询每个部门 每个工种的员工的平均工资 并排序 **
SELECT
AVG(salary),
department_id,
job_id
FROM
employees
GROUP BY department_id,
job_id
ORDER BY AVG(salary) ASC ;
3.6 连接查询(多表查询)
3.6.1笛卡尔乘积
表1有M行,表2有N行,结果M*N行
发生原因: 没有有效的连接条件
添加有效的连接条件就可以避免笛卡尔乘积
SELECT
`name`,
`boyName`
FROM
beauty,
boys
WHERE beauty.boyfriend_id = boys.id ;
3.6.2 分类
sql92标准(支持内连接)
sql99标准(推荐)
内连接(等值连接,非等值连接,自连接)、外连接(左外连接,右外连接,全外连接)、交叉连接
3.6.2.1 sql92标准— 等值连接
查询女生名和对应男生名
SELECT
`name`,
`boyName`
FROM
beauty,
boys
WHERE beauty.boyfriend_id = boys.id ;
查询员工名和对应的部门名
SELECT
last_name,
department_name
FROM
employees,
departments
WHERE departments.`department_id` = employees.`department_id` ;
查询有奖金的员工名和对应的部门名
SELECT
last_name,
department_name
FROM
employees,
departments
WHERE departments.`department_id` = employees.`department_id`
AND employees.commission_pct IS NOT NULL;
多表等值连接为多表的交集部分/N个表连接 至少需要N-1个连接条件/顺序没有要求
3.6.2.2 sql92— 非等值连接
查询员工的工资和工资级别
SELECT
salary,
grade_level
FROM
employees e,
job_grades g
WHERE salary BETWEEN g.`lowest_sal`
AND g.`highest_sal` ;
3.6.2.3 sql92—自连接
查询员工名和上级的名称
SELECT
e.`employee_id`,
e.`last_name`,
m.`employee_id`,
m.`last_name`
FROM
employees e,
employees m
WHERE e.`employee_id` = m.`employee_id` ;
3.6.2.4 sql99
语法:
select 查询列表
from 表1 别名 连接类型
join 表二 别名
on 连接条件
/*连接类型:
内连接:inner
外连接:左外:left (outer)
右外: right (outer)
全外: full (outer)
交叉类型:cross*/
3.6.2.5 sql99 —内连接
select 查询列表
from 表1 别名 inner
join 表2 别名
on 连接条件
分类:等值,非等值,自连接
等值
--1.查询员工名和部门名
SELECT
last_name,
department_name
FROM
employees e
INNER JOIN departments d
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 last_name LIKE '%e%' ;
/*3.查询部门个数>3的 城市名,和部门个数(分组+筛选)*/
SELECT
COUNT(*),
city 部门个数
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
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`
GROUP BY job_title
ORDER BY `job_title` ASC ;
非等值连接
/*查询员工的工资级别*/
SELECT
salary,
grade_level
FROM
employees e
INNER JOIN job_grades g
ON e.`salary` BETWEEN lowest_sal
AND highest_sal ;
/*查询工资级别的个数>20的个数,并按工资级别排序*/
SELECT
COUNT(*),
salary,
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 ASC ;
自连接
/*查询员工的名字 和上级的名字*/
SELECT
e.last_name,
m.last_name
FROM
employees e
INNER JOIN employees m
ON e.`manager_id` = m.`employee_id` ;
3.6.2.6 sql99—左右外连接
/*查询没有男朋友的女生名*/
SELECT
b.`name`
FROM
beauty b
LEFT OUTER JOIN boys bo
ON b.`boyfriend_id` = bo.`id`
WHERE bo.`boyName` IS NULL ;
/*查询 哪个部门没有员工*/
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
3.7 子查询
出现在其他语句中的select语句,成为子查询或内查询
外部的查询语句,成为主查询或者外查询
分类:按出现位置分类{select后面,from后面,where/having后面,exists后面}
按结果集的行列数不同{标量子查询(结果集只有一行一列)、列子查询(结果集一列多行)、行子查询(结果集一行多列)、
表子查询(多行多列)}
select后面只支持 标量子查询
3.7.1 where后的标量子查询使用
1.子查询都在小括号内,
2.一般放在条件右侧,
3.标量子查询 一般搭配单行操作符使用(> < = <> >= <=)
4.列子查询,一般搭配多行操作符(IN ANY/SOME ALL)
**标量子查询(单行子查询) where **
/*谁的工资比Abel高*/
SELECT
*
FROM
employees
WHERE
salary>(SELECT
salary
FROM
employees
WHERE last_name = 'Abel') ;
/*返回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) ;
/*返回工资最少的员工的last_name,job_id,salary*/
SELECT
last_name,
job_id,
salary
FROM
employees
WHERE salary =
(SELECT
MIN(salary)
FROM
employees) ;
子查询的having子句
/*查询最低工资 大于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);
3.7.2 where 后的列子查询(多行子查询)
返回多行,使用多行比较操作符(IN/NOT IN 等于列表中的任意一个、ANY/SOME 和子查询返回的某一个值比较、ALL 和子查询返回的所有值比较)
/*返回location_id是1400或1700的部门中,所有员工姓名*/
SELECT
last_name
FROM
employees
WHERE department_id IN
(SELECT DISTINCT
department_id
FROM
departments
WHERE location_id IN (1400, 1700));
/*返回其他部门中比job_id为'IT_PROG'部门任意工资低的员工的员工号,姓名,job_id和salary*/
SELECT
last_name,
employee_id,
job_id,
salary
FROM
employees
WHERE salary < ANY
(SELECT
salary
FROM
employees
WHERE job_id = 'IT_PROG')
AND job_id <> 'IT_PROG' ;
/*返回其他部门中比job_id为'IT_PROG'部门所有工资低的员工的员工号,姓名,job_id和salary*/
SELECT
last_name,
employee_id,
job_id,
salary
FROM
employees
WHERE salary < ALL
(SELECT
salary
FROM
employees
WHERE job_id = 'IT_PROG')
AND job_id <> 'IT_PROG' ;
3.7.3 where后的行子查询(多行多列)
/*查询员工编号最小并且工资最高的*/
SELECT
*
FROM
employees
WHERE employee_id =
(SELECT
MIN(employee_id)
FROM
employees)
AND salary =
(SELECT
MAX(salary)
FROM
employees) ;
3.7.4 select 后的子查询
/*查询每个部门的员工人数*/
SELECT
d.*,
(SELECT
COUNT(*)
FROM
employees e
WHERE e.department_id = d.`department_id`) 个数
FROM
departments d ;
/*查询员工号=102的部门名*/
SELECT (
SELECT department_name
FROM departments d
INNER JOIN employees e
ON d.department_id=e.department_id
WHERE e.employee_id=102
) 部门;
-----------------------------