DQL语言
关系结构数据库是以表格形式进行数据存储,由“行”和“列”组成。
执行数据库查询时,返回的结果集是一张虚拟表。
基础查询
一、语法
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 起始条目索引,条目数; ⑨