DQL语言

14

关系结构数据库是以表格形式进行数据存储,由“行”和“列”组成。

执行数据库查询时,返回的结果集是一张虚拟表。

基础查询

一、语法

select 列名 from 表名;

二、特点

1、查询列表可以是字段、常量、表达式、函数,也可以是多个
2、查询结果是一个虚拟表
3、生成环境下优先使用列名查询

三、示例

1、查询单个字段
select 字段名 from 表名;

2、查询多个字段
select 字段名,字段名 from 表名;

3、查询所有字段
select * from 表名
注意:* 的方式需转换成全列名,效率低,可读性差。

4、查询常量
select 常量值;	
SELECT 100; SELECT 'join';
注意:字符型和日期型的常量值必须用单引号引起来,数值型不需要

5、查询函数
select 函数名(实参列表);	
SELECT VERSION();

6、查询表达式
select 100/1234;

7、起别名
①as	 ②空格
select employee_id as 编号,first_name as 名字,salary as 工资 from t_employees;
select employee_id  编号,first_name  名字,salary  工资 from t_employees;

8、去重
select distinct 字段名 from 表名;

9、+( - * / %)
作用:做加法运算等算术运算
select 数值+数值; 直接运算
select 字符+数值;先试图将字符转换成数值,如果转换成功,则继续运算;否则转换成0,再做运算
select null+值;结果都为null

10、【补充】concat函数
功能:拼接字符
select concat(字符1,字符2,字符3,...);
SELECT CONCAT('a','b','c') AS 结果 FROM employees;

11、【补充】ifnull函数
功能:判断某字段或表达式是否为null,如果为null 返回指定的值,否则返回原本的值
select ifnull(commission_pct,0) from employees;

12、【补充】isnull函数
功能:判断某字段或表达式是否为null,如果是,则返回1,否则返回0

条件查询

一、语法
select 查询列表 from 表名 where 筛选条件

二、筛选条件的分类

1、简单条件运算符
> < = 
<> != 
>= <=  
<=>安全等于
注意: = 既可以判断,又可以赋值
2、逻辑运算符
&& and
|| or
!  not
3、模糊查询
like:一般搭配通配符使用,可以判断字符型或数值型
通配符:%任意多个字符,_任意单个字符
注意:模糊查询只能和like关键字结合使用。
4. like、between and、in、is null、is not null
注意:between and要小值在前,大值在后,反之,得不到正确结果。
注意:in的查询效率较低,可通过多条件拼接。

is null /is not null:用于判断null值   not位置:放在is后面或者放在字段前面
SELECT last_name,commission_pct FROM employees WHERE commission_pct IS NULL; 
SELECT last_name,commission_pct FROM employees WHERE commission_pct IS NOT NULL;

is null 与  <=>
		普通类型的数值	null值	     可读性
is null		×			√		√
<=>		√			√		×

#3.1 查询工资等于11000的员工信息
select employee_id,first_name,salary from t_employees where salary=11000;
#3.2 逻辑判断,查询工资等于11000的员工信息并且奖金等于0.30
select employee_id,first_name,salary, commission_pct from t_employees where salary=11000 and commission_pct=0.30;
#3.3 查询工资小于等于10000 并且大于等于6000的员工信息
select employee_id,first_name,salary from t_employees where salary >= 6000 and salary<=10000;
#3.4 查询工资小于等于10000 并且大于等于6000的员工信息 使用between and
select employee_id,first_name,salary from t_employees where salary between 10000 and 6000 ;
#3.5 查询奖金为null,NULL 值判断  not位置:放在is后面或者放在字段前面
select employee_id,first_name,salary,commission_pct from t_employees where  commission_pct  is not   null;
#3.6 查询员工的部门id为 枚举查询( IN (值1,值2,值3 ) )
select employee_id,first_name,salary,department_id from  t_employees where department_id=90 or department_id=60 or department_id=30;
#3.7 查询员工姓名以‘L’开头 模糊查询(LIKE 通配符)
# % 实现求余 实现模糊查询 表示任意多个字符
select  employee_id,first_name,salary from t_employees where first_name like 'L%';
#3.8 查询员工姓名以‘L’开头 四个字母 模糊查询(LIKE 通配符)
# _ 表示一个字符
select  employee_id,first_name,salary from t_employees where first_name like 'L___';

排序查询

一、语法
select  列名  from 表 where 筛选条件 order by 排序列 【asc/desc】

二、特点
1、asc :升序,如果不写默认升序
     desc:降序
2、排序列表支持 单个字段、多个字段、函数、表达式、别名
3、order by的位置一般放在查询语句的最后(除limit语句之外)

select employee_id,first_name,salary from t_employees order by salary desc, employee_id asc;

常见函数

一、概述
功能:类似于java中的方法
好处:提高重用性和隐藏实现细节
调用:select 函数名(实参列表);

二、单行函数
1、字符函数
concat:连接	SELECT CONCAT(last_name,'_',first_name) 姓名 FROM employees;
substr:截取子串	SELECT SUBSTR('吴刚伐桂在天上',1,2) out_put;  SELECT SUBSTR('吴刚伐桂在天上',4) out_put;
upper:变大写	SELECT UPPER('ton');
lower:变小写	SELECT LOWER('ton');
replace:替换	SELECT REPLACE('莉莉伊万斯的青梅竹马是詹姆','詹姆','斯内普') AS out_put;
length:获取字节长度	SELECT LENGTH('鬼谷子qwe');
trim:去前后空格	SELECT LENGTH(TRIM('    霍山    ')) AS out_put;
instr:获取子串第一次出现的索引	,找不到返回0	SELECT INSTR('MySQL技术进阶','技术') AS out_put;
lpad:用指定的字符实现左填充指定长度	SELECT LPAD('梅林',8,'+') AS out_put;
rpad:用指定的字符实现右填充指定长度	SELECT RPAD('梅林',5,'&') AS out_put;
经验:执行字符串函数查询,会自动生成一张虚拟表(一行一列)。

2、数学函数
round:四舍五入 SELECT ROUND(1.45); SELECT ROUND(1.567,2); 
ceil:向上取整,返回>=该参数的最小整数 SELECT CEIL(1.005); SELECT CEIL(-1.002); 
floor:向下取整,返回<=该参数的最大整数 SELECT FLOOR(-9.99); 
truncate:截断 SELECT TRUNCATE(1.65,1); 
mod:取余 SELECT MOD(10,3); 
rand:获取随机数,返回0-1之间的小数 SELECT RAND();

3、日期函数
now:返回当前日期+时间	SELECT NOW();
year:返回年	SELECT YEAR(NOW());
month:返回月	SELECT MONTH(NOW());
day:返回日	SELECT DAY(NOW());
date_format:将日期转换成字符	SELECT DATE_FORMAT('2020/6/6','%Y年%m月%d日') AS out_put;
curdate:返回当前日期	SELECT CURDATE();
str_to_date:将字符转换成日期	SELECT STR_TO_DATE('2020-5-13','%Y-%c-%d') AS out_put;
curtime:返回当前时间	SELECT CURTIME();
hour:小时
minute:分钟
second:秒
datediff:返回两个日期相差的天数	SELECT DATEDIFF('2020/06/30','2020/06/21');
monthname:以英文形式返回月	SELECT MONTHNAME(NOW());
adddate: 增加天数    select ADDDATE('2021-4-6',200);
经验:执行时间函数查询,会自动生成一张虚拟表(一行一列)。

4、其他函数
version 当前数据库服务器的版本	SELECT VERSION();
database 当前打开的数据库	SELECT DATABASE();
user当前用户	SELECT USER();
password('字符'):返回该字符的密码形式	SELECT PASSWORD('a');
md5('字符'):返回该字符的md5加密形式	SELECT MD5('a');

5、流程控制函数
①if(条件表达式,表达式1,表达式2):如果条件表达式成立,返回表达式1,否则返回表达式2
SELECT IF(10<5,'大','小');

②case情况1
case 变量或表达式或字段
    when 常量1 then 值1或语句
    when 常量2 then 值2或语句
     ...
    else 值n或语句
end

SELECT salary 原始工资,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;


③case情况2 类似于多重if
case 
       when 条件1 then 值1
       when 条件2 then 值2
       ...
       else 值n
end

SELECT salary, 
CASE 
       WHEN salary>20000 THEN 'A' 
       WHEN salary>15000 THEN 'B' 
       WHEN salary>10000 THEN 'C' 
       ELSE 'D' 
END AS 工资等级 
FROM employees;

三、分组函数 用作统计使用
又称为聚合函数或统计函数或组函数。
select 聚合函数(列名) from 表名。
1、分类
max 最大值
min 最小值
sum 和
avg 平均值
count 计算个数
经验:执行聚合函数查询,会自动生成一张虚拟表(一行一列)。

SELECT SUM(salary) 和,ROUND(AVG(salary),2) 平均,MAX(salary) 最高,MIN(salary) 最低,COUNT(salary) 个数 FROM employees;

2、特点
①语法:select max(字段) from 表名;
②支持的类型:sum和avg一般用于处理数值型,max、min、count可以处理任何数据类型
③以上分组函数都忽略null
④都可以搭配distinct使用,实现去重的统计:select sum(distinct 字段) from 表;
⑤count函数
count(字段):统计该字段非空值的个数
count(*):统计结果集的行数

案例:查询每个部门的员工个数
1 xx    10
2 dd    20
3 mm    20
4 aa    40
5 hh    40
count(1):统计结果集的行数
效率上:
MyISAM存储引擎,count(*)最高
InnoDB存储引擎,count(*)和count(1)效率>count(字段)

⑥ 和分组函数一同查询的字段,要求是group by后出现的字段

分组查询

一、语法
select 分组函数,分组后的字段
from 表
【where 筛选条件】
group by 分组的字段 以其中一个字段的值来分组
【having 分组后的筛选】
【order by 排序列表】

和分组函数一同查询的字段,要求是group by后出现的字段
group by子句支持单个字段分组,多个字段分组(多个字段之间用逗号隔开没有顺序要求),表达式或函数(使用较少)

二、特点
		使用关键字		筛选的表	          位置
分组前筛选	where		原始表		group by的前面
分组后筛选	having		分组后的结果	group by 的后面

注意:分组查询中,select显示的列只能是分组依据列,或者聚合函数列,不能出现其他列。
注意:HAVING是对分组之后的数据做过滤。

查询每个工种的最高工资(将相同的job_id的员工分到一组,MAX(salary)获取小组中工资最高的作为该属性的值)
SELECT MAX(salary),job_id FROM employees  GROUP BY job_id;

查询每个部门每个工种的员工的平均工资,按平均工资的高低查询
SELECT AVG(salary),department_id,job_id 
FROM employees 
GROUP BY department_id,job_id 
ORDER BY AVG(salary) DESC;

添加筛选过滤条件
SELECT COUNT(*) c,LENGTH(last_name) len_name  FROM employees GROUP BY len_name HAVING c>5;

MySQL的基本架构

连接查询

一、含义
当查询中涉及到了多个表的字段,需要使用多表连接
select 字段1,字段2  from 表1,表2,...;
笛卡尔乘积:当查询多个表时,没有添加有效的连接条件,导致多个表所有行实现完全连接。
如何解决:添加有效的连接条件。

二、分类
按年代分类:
	sql92:
		等值
		非等值
		自连接
		也支持一部分外连接(用于oracle、sqlserver,mysql不支持)

	sql99【推荐使用】
		内连接
			等值
			非等值
			自连接
		外连接
			左外
			右外
			全外(mysql不支持)
		交叉连接
			
三、SQL92语法
1、等值连接
语法:
	select 查询列表
	from 表1 别名,表2 别名
	where 表1.key=表2.key
	【and 筛选条件】
	【group by 分组字段】
	【having 分组后的筛选】
	【order by 排序字段】

特点:
	① 一般为表起别名
	②多表的顺序可以调换
	③n表连接至少需要n-1个连接条件
	④等值连接的结果是多表的交集部分

//查询员工名、工种号、工种名
SELECT e.last_name,e.job_id,j.job_title 
FROM jobs j,employees e 
WHERE e.`job_id`=j.`job_id`;

2、非等值连接
语法:
	select 查询列表
	from 表1 别名,表2 别名
	where 非等值的连接条件
	【and 筛选条件】
	【group by 分组字段】
	【having 分组后的筛选】
	【order by 排序字段】

//查询员工的工资和工资级别
SELECT salary,grade_level 
FROM employees e,job_grades g 
WHERE e.salary BETWEEN g.`lowest_sal` AND g.`highest_sal` 
AND g.`grade_level`='A';

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`;

四、SQL99语法
内连接(★):inner 
外连接 
* 左外(★):left 【outer】 
* 右外(★):right 【outer】 
* 全外:full【outer】 
交叉连接:cross

1、内连接
语法:
select 查询列表
from 表1 别名【inner】 
join 表2 别名 
on 连接条件
where 筛选条件
group by 分组列表
having 分组后的筛选
order by 排序列表
limit 子句;

特点:
①表的顺序可以调换
②内连接的结果=多表的交集
③n表连接至少需要n-1个连接条件

分类:
等值连接	
SELECT last_name,department_name FROM departments d INNER JOIN employees e ON e.`department_id` = d.`department_id`;
非等值连接 
SELECT salary,grade_level FROM employees e INNER JOIN job_grades g ON e.`salary` BETWEEN g.`lowest_sal` AND g.`highest_sal`;
自连接	
SELECT e.last_name,m.last_name FROM employees e INNER JOIN employees m ON e.`manager_id`= m.`employee_id`;

2、外连接 用于查询一个表中有,另一个表没有的记录
语法:
select 查询列表
from 表1 别名 left|right|full【outer】 
join 表2 别名 
on 连接条件
where 筛选条件
group by 分组列表
having 分组后的筛选
order by 排序列表
limit 子句;

特点:
①查询的结果=主表中所有的行,如果从表和它匹配的将显示匹配行,如果从表没有匹配的则显示null
②left join 左边的就是主表
  right join 右边的就是主表
  full join 两边都是主表
③一般用于查询除了交集部分的剩余的不匹配的行
全外连接=内连接的结果+表1中有但表2没有的+表2中有但表1没有的

3、交叉连接
语法:
select 查询列表
from 表1 别名
cross join 表2 别名;
特点:
类似于笛卡尔乘积

案例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; 
全外 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对比
功能:sql99支持的较多
可读性:sql99实现连接条件和筛选条件的分离,可读性较高
#3连接查询

#3.0补充:交叉连接(笛卡尔积),问题:出现重复数据
#传统写法
select * from t_employees ,t_departments;
#标准写法
select * from t_employees CROSS join t_departments;

#3.1内连接查询(去掉重复的交叉连接)
#查询所有员工的部门信息
#传统写法
select * from t_departments as d, t_employees as e  where e.DEPARTMENT_ID=d.DEPARTMENT_ID;
#标准写法
select * from t_employees as e INNER JOIN t_departments as d on e.DEPARTMENT_ID=d.DEPARTMENT_ID;

#3.2外连接
#左外连接查询:以左表为主表,主表中数据全部查询出来,用从表去匹配,如果找到显示,没有找到填充null
#查询没有分配部门的员工
select * from t_employees as e left outer join t_departments  as d 
on e.DEPARTMENT_ID=d.DEPARTMENT_ID
where d.DEPARTMENT_ID is null;
#右外连接查询:以右表为主表,主表中数据全部查询出来,用从表去匹配,如果找到显示,没有找到填充null
#查询没有员工的部门
SELECT * from t_employees as e right outer join t_departments as d
on e.DEPARTMENT_ID=d.DEPARTMENT_ID
where e.EMPLOYEE_ID is null;

子查询

一、含义
嵌套在其他语句内部的select语句称为子查询或内查询,
外面的语句可以是insert、update、delete、select等,一般select作为外面语句较多
外面如果为select语句,则此语句称为外查询或主查询

子查询的执行优先于主查询执行,主查询的条件用到了子查询的结果

二、分类
1、按出现位置
select后面:
		仅仅支持标量子查询
from后面:
		表子查询
where或having后面:
		标量子查询
		列子查询
		行子查询
exists后面:
		标量子查询
		列子查询
		行子查询
		表子查询

2、按结果集的行列
标量子查询(单行子查询):结果集为一行一列
列子查询(多行子查询):结果集为多行一列
行子查询:结果集为多行多列
表子查询:结果集为多行多列

三、示例
where或having后面
1、标量子查询
案例:查询最低工资的员工姓名和工资
①最低工资
select min(salary) from employees
②查询员工的姓名和工资,要求工资=①
select last_name,salary
from employees
where salary=(
	select min(salary) from employees
);

2、列子查询
可以使用ANY或ALL关键字
案例:查询所有是领导的员工姓名
①查询所有员工的 manager_id
select manager_id from employees
②查询姓名,employee_id属于①列表的一个
select last_name
from employees
where employee_id in(
	select manager_id
	from employees
);

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;


三、from后面 将子查询结果充当一张表,要求必须起别名
查询每个部门的平均工资的工资等级 
①查询每个部门的平均工资 
SELECT AVG(salary),department_id FROM employees GROUP BY department_id; SELECT * FROM job_grades;
 ②连接①的结果集和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 */
查询有员工的部门名 
#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` );
#1 子查询
#1.1 子查询作为条件判断 (一行一列)
#查询工资大于bruce的员工信息
#第一步先查询bruce的工资
select salary from t_employees where first_name='bruce';
#第二步查询工资大于6000的员工
select * from t_employees where salary>6000;
#合并
select * from t_employees where salary>(select salary from t_employees where first_name='bruce');
#1.2作为枚举查询条件 (多行一列)
#查询与姓为'King'同一部门的员工信息:
select department_id from t_employees where last_name='king';
#查询同部门的员工信息
select * from t_employees where DEPARTMENT_ID in(80,90);
#合并
select * from t_employees where DEPARTMENT_ID in(select department_id from t_employees where last_name='king');
#当子查询结果集形式为多行单列时还可以使用ANY或ALL关键字。
#查询查询 60 部门所有人的工资
select salary from t_employees where DEPARTMENT_ID=60;
#查询大于60 部门所有人的工资的员工
select * from t_employees where SALARY>9000;
#合并
#大于最大工资
select * from t_employees where SALARY> ALL(select salary from t_employees where DEPARTMENT_ID=60);
#大于最小工资
select * from t_employees where SALARY> ANY(select salary from t_employees where DEPARTMENT_ID=60);
#1.3 作为一张临时表 (多行多列)
#查询员工表中工资排名前 5 名的员工信息:
#第一步查询所有人的id,姓名,工资
select employee_id,first_name,salary from t_employees order by SALARY desc;
#把上一个结果作为临时表查询
select name,salary from (select employee_id,first_name as name,salary from t_employees order by SALARY desc) as temp limit 0,5;

分页查询

一、应用场景
当要查询的条目数太多,一页显示不全,需要分页提交sql请求。

二、语法
	select 查询列表
	from 表
	【join type join 表2
	on 连接条件
	where 筛选条件
	group by 分组字段
	having 分组后的筛选
	order by 排序的字段】
	limit offset,size;
注意
	offset要显示条目的起始索引(起始索引从0开始)代表第一行
	size 要显示的条目个数

公式:
假如要显示的页数为page,每一页条目数为size
select 查询列表
from 表
limit (page-1)*size,size;

查询前五条员工信息 
SELECT * FROM employees LIMIT 0,5; 
SELECT * FROM employees LIMIT 5;

查询第11条——第25条 
SELECT * FROM employees LIMIT 10,15;

合并查询

一、含义
union:合并、联合,将多次查询结果合并成一个结果

二、语法
查询语句1
union 【all】
查询语句2
union 【all】
...;

三、意义
1、将一条比较复杂的查询语句拆分成多条语句
2、适用于查询多个表的时候,查询的列基本是一致

四、特点
1、要求多条查询语句的查询列数必须一致
2、要求多条查询语句的查询的每一列的类型和顺序最好一致
3、union 去重,union all包含重复项

查询部门编号>90或邮箱包含a的员工信息 
SELECT * FROM employees WHERE email LIKE '%a%' OR department_id>90; 

SELECT * FROM employees WHERE email LIKE '%a%' 
UNION 
SELECT * FROM employees WHERE department_id>90;
#合并查询 union
#要求:1 列数相同 2 类型可以不同
#UNION连接的结果集会去除重复的记录。
select * from tab1 UNION SELECT* from tab2;
#UNION ALL 包含重复数据
select * from tab1 UNION ALL SELECT* from tab2;

查询总结

语法:
select 查询列表    ⑦
from 表1 别名       ①
连接类型 join 表2   ②
on 连接条件         ③
where 筛选          ④
group by 分组列表   ⑤
having 筛选         ⑥
order by排序列表    ⑧
limit 起始条目索引,条目数;  ⑨