MySQL_查询汇总(一)
2022/4/28 2:12:40
本文主要是介绍MySQL_查询汇总(一),对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!
一、MySQL常用命令
完整看完就能掌握MySQL中大部分的查询问题了
-- 查看所有数据库 show databases; -- 选择使用哪个数据库 use <数据库名>; -- 创建数据库 create database <数据库名>; -- 查看所有数据表。(前提是要先选择使用哪个数据库) show tables; -- 查看表结构 desc <表名>; -- 导入数据(*.sql文件)注意路径中不要有中文 source <.sql文件路径> -- 查看MySQL版本号 select version(); -- 查看正在使用的数据库。 select database(); -- 中止sql语句的输入 \c -- 退出MySQL exit;
二、SQL分类
DQL:数据库查询语言
凡是带有select关键字的都是查询语句。
select 查
DML:数据库操作语言
凡是对表当中的数据进行增删改的都是DML。
insert 增
delete 删
update 改
DDL:数据定义语言
凡是带有create、drop、alter的都是DDL。DDL主要操作的是表的结构,不是表中的数据。
create 新建
drop 删除
alter 修改
TCL:事务控制语言
事务提交:commit
事务回滚:rollback
DCL:数据控制语言
授权:grant
撤销权限:revoke
三、查询语句
1.简单查询
select 和 from 都是关键字,字段名 和 表名 都是标识符。
对于SQL语句来说,是通用的。所有的SQL语句都以“;”结尾。SQL语句不区分大小写。
a>查询一个字段
select <字段名> from <表名>;
b>查询两个或多个字段
字段之间用“,”隔开。
select <字段名>,<字段名>,... from <表名>;
c>查询所有字段
- 可以把每个字段都写上。
- 可以使用“*”,代表全部。
在Java程序中不要写 "*" 号,因为它会先把星号转换为字段,这个过程也会占用一定的时间,所以效率比较低且可读性较差。
select * from <表名>;
d>给查询的列起别名
使用 as 关键字起别名。as 关键字可以省略。
注意:只是将显示的查询结果列名显示为别名,原表列名不变。记住:select语句是永远不会进行修改操作的。(只负责查询)
select deptno,dname as deptname from dept; -- 或 select deptno,dname deptname from dept;
问:假设起别名的时候,别名里面想要加空格,怎么办?————用 单引号 将别名括起来。
select deptno,dname 'dept name' from dept;
2.列参和数学运算
-- 列可以参与运算 select ename, sal*12 from emp; -- 也可以起别名,别名是中文就用单引号括起来 select ename, sal*12 as yearsal from emp;
3.条件查询
对表中数据进行条件筛选,将符合条件的数据取出来。
-- 语法格式 select <字段名> from <表名> where <条件>;
符号 | 含义 |
---|---|
= | 等于 |
<> 或 != | 不等于 |
< | 小于 |
<= | 小于等于 |
> | 大于 |
>= | 大于等于 |
between ... and ... | 两个值之间,等同于 >= and <= |
is null | 为空 |
is not null | 不为空 |
and | 并且 |
or | 或者 |
in | 包含 |
not | 取非,主要用在 is 或 in 中 |
like | 模糊查询(% 匹配任意个字符,_ 一个下划线只匹配一个字符) |
-- 查询工资等于800的数据 select empno, ename from emp where sql = 800; -- 查询工资不等于800的数据 select empno, ename from emp where sql != 800; -- 查询补助为null的数据(需要用is,不能用=) select empno, ename sal, comm from emp where comm is null; -- 查询工作岗位是manager和salesman的员工(in后面不是一个区间,而是具体的值) select empno, ename, job from emp where job in ('manager', 'salesman'); -- 找出名字中含有“O”的 select ename from emp where ename like '%O%'; -- 找出名字中以“T”结尾的 select ename from emp where ename like '%T'; -- 找出名字中第二个字母是“A”的 select ename from emp where ename like '_A%';
4.排序
a>单个字段排序
-- 正序排序(从小到大排序)。加不加asc都行,因为默认是升序。 select * from car_information order by car_price (asc); -- 倒序排序(从大到小排序) select * from car_information order by car_price desc;
b>多个字段排序
查询所有车辆,要求按照车辆价格升序,如果价格一样的话,再按照品牌升序排序。
-- car_price在前,起主导作用。只有当car_price相等的时候,才会考虑car_brand。 select * from car_information order by car_price, car_brand;
5.综合案例
找出价格在5到10的车辆信息,要求按照价格降序排列。
select car_brand, car_price from car_information where car_price between 5 and 10 order by car_price desc;
四、数据处理函数
数据处理函数又被称为单行处理函数,对数据是一行一行处理的。
特点:一个输入对应一个输出
函数 | 含义 |
---|---|
lower | 转换小写 |
upper | 转换大写 |
substr | 取子串 substr(被截取的字符串,起始下标,截取的长度) |
length | 取长度 |
trim | 去空格 |
str_to_date | 将字符串转换成日期 |
date_format | 格式化日期 |
format | 设置千分位 |
round | 四舍五入 |
rand() | 生成随机数 |
lfnull | 可以将null抓换为一个具体值 |
-- 例:转换小写 select lower(ename) as ename from emp;
五、分组函数
分组函数又被称为多行处理函数。
特点:输入多行,最终输出一行。
注意:分组函数在使用的时候必须先进行分组,然后才能用。如果没有对数据进行分组,默认整张表为一组。
函数 | 含义 |
---|---|
count | 计数 |
sum | 求和 |
avg | 平均值 |
max | 最大值 |
min | 最小值 |
-- 找出最高工资 select max(sal) from emp; -- 找出最低工资 select min(sal) from emp; -- 计算工资和 select sum(sal) from emp; -- 计算平均工资 select avg(sal) from emp; -- 计算员工数量 select count(ename) from emp;
分组函数需要注意的点
-
分组函数自动忽略null,你不需要对null进行处理。(null不是一个值,是什么也没有)
-
分组函数中的count(*)和count(具体字段)的区别:
count(*):统计表当中的总行数。(因为数据表中不存在全部字段都为null的数据)
count(具体字段):表示统计该字段下所有不为null的元素的总和。
-
分组函数不能直接使用在where子句中。
例:找出比最低工资高的员工信息。
select ename, sal from emp where sal > min(sal);
表面上看没问题,但是运行报错:错误使用分组函数。
看完 《分组查询》 才能明白。
-
所有的分组函数可以组合起来一起用。
select sum(sal), min(sal), max(sal), avg(sal), count(*) from emp;
六、分组查询(⭐⭐⭐⭐⭐)
在实际的应用中,可能有这样的需求,需要先进行分组,然后对每一组的数据进行操作。
这时候就需要用到分组查询了
select ... from ... group by ...
例:
计算每个部门的工资和?
计算每个工作岗位的平均薪资?
找出每个工作岗位的最高薪资?
关键字执行顺序
将之前的关键字全部组合在一起,来看一下他们的执行顺序:
select ... from ... where ... group by ... order by ...
顺序不能改变!!!
执行顺序是什么:
- from
- where
- group by
- select
- order by
一个大问题
为什么分组函数不能直接使用在where后面?
-- 报错 select ename, sal from emp where sal > min(sal); -- 成功 select sum(sal) from emp;
原因是:执行顺序。分组函数在使用的时候必须先进行分组,然后才能用。
从语法层面上看,where
的执行在 group by
前面,而 select
的执行在 group by
后面,select
的时候,group by
已经执行结束了。(group by没写不等于没有,没写表示整张表分为一组)
案例分析1
-
找出每个岗位的工资和。
select job, sum(sal) from emp group by job;
以上这个语句的执行顺序:
- 先从
emp
表中查询。 - 根据
job
字段进行分组 - 然后对每一组的数据进行
sum(sal)
- 先从
a>提问:
下面的语句能否执行:
select ename, job, sum(sal) from emp group by job;
答:以上语句在MySQL中可以执行,但没有意义。sum(sal)按照job只能分出5组,可是ename有14个数据,ename对应不了sum(sal)。
在MySQL中可以执行,但是在Oracle中会报错。Oracle的语法比MySQL严格。
重要结论:
在一条select语句当中,如果有group by语句的话,select后面只能跟:参加分组的字段、以及分组函数。其他的一律不能跟。
案例分析2
找出”每个部门,不同工作岗位“的最高薪资。
技巧:两个字段联合成一个字段看。(两个字段联合分组)
select deptno, job, max(sal) from emp group by deptno, job;
having子句
having可以对分完组之后的数据进一步过滤。
having
不能单独使用,having
不能代替 where
,having
必须和 group by
联合使用。
案例分析
找出每个部门最高薪资,要求显示最高薪资大于3000的。
-
找出每个部门最高薪资
按照部门编号分组,求每一组最大值。
select deptno, max(sal) from emp group by deptno;
-
要求显示最高薪资大于3000的
这题不能使用where,因为where的执行顺序在group by前面,所以只能使用having来过滤。
select deptno, max(sal) from emp group by deptno having max(sal) > 3000;
思考一个小问题
以上的sql语句执行效率是不是低?
答:是的,比较低,实际上可以这样考虑:先将大于3000的都找出来,然后再分组。
select deptno, max(sal) from emp where sal > 3000 group by deptno;
优化策略:
能够使用
where
过滤的,都尽量使用where
。
where
和having
优先选择where
。
大总结
-- 关键字只能按照这个顺序来,不能颠倒 select ... from ... where ... group by ... having ... order by ...
执行顺序:
- from
- where
- group by
- having
- select
- order by
最终案例
找出每个岗位的平均薪资,要求显示平均薪资大于1500的,除manager岗位之外,要求按照平均薪资降序排列。
select job, avg(sal) as avgsal from emp where job != 'manager' group by job having avg(sal) > 1500 order by avgsal desc;
七、查询结果去重
把查询结果去除重复记录,原表数据不会被修改,只是查询结果去重。
需要使用一个关键字:distinct
select distinct job from emp; -- 这样编写是错误的,语法错误。distinct只能出现在所有字段的最前方。 select ename, distinct job from emp; -- distinct出现在job和deptno两个字段之前,表示两个字段联合起来去重。 select distinct job, deptno from emp;
案例分析
统计一下工作岗位的数量?
select count(distinct job) from emp;
八、连接查询(⭐⭐⭐⭐⭐)
什么是连接查询:
从一张表中单独查询,称为单表查询。
从emp表和dept表联合起来查询数据,从emp表中取员工姓名,从dept表中取部门名字。这种跨表查询,多张表联合起来查询数据,叫做连接查询。
连接查询的分类:
根据语法的年代分类:
- SQL92:1992年的时候出现的语法(几乎废了)
- SQL99:1999年的时候出现的语法
根据表连接的方式分类:
- 内连接:
- 等值连接
- 非等值连接
- 自连接
- 外连接:
- 左外连接(左连接)
- 右外连接(右连接)
- 全连接(很少使用)
笛卡尔积现象(连接查询底层)
查询每个员工所在部门名称?
-
当两张表进行连接查询时,没有任何条件的限制会发生什么?
select ename, dname from emp, dept;
56 rows in set (0.00 sec)
当两张表进行连接查询,没有任何条件限制的时候,最终查询结果条数是两张表条数的乘积,这种现象被称为:笛卡尔积现象
怎么避免笛卡尔积现象呢?
连接时加条件,满足这个条件的记录被筛选出来
select ename, dname from emp, dept where emp.deptno = dept.deptno; ================================ -- 可以使用别名 -- 为什么select里可以使用from里起的别名,因为select的执行顺序在from之后。 select e.ename, d.dname from emp e, dept d where e.deptno = d.deptno;
思考:最终查询的结果条数是14条,但是匹配的过程中,匹配的次数减少了吗?
答:没有,还是56次,只不过进行了四选一。匹配次数没有减少。表连接越多,效率就越低。因为笛卡尔积现象,匹配次数会很多。
内连接
内连接的特点:完成能够匹配上这个条件的数据查询出来。
等值连接
案例:查询每个员工所在部门名称,显示员工名和部门名?(使用SQL99语法)
-- 92语法 select e.ename, d.dname from emp e, dept d where e.deptno = d.deptno; -- SQL92的缺点:结构不清晰,表的连接条件和后期进一步筛选的条件,都放在了where后面。 =========================== -- 99语法,inner join = join select e.ename, d.dname from emp e (inner) join dept d on e.deptno = d.deptno; -- 条件是等量关系,所以被称为等值连接。 -- SQL99的优点:表连接的条件是独立的,连接之后,如果还需要进一步筛选,再往后继续添加where条件。
条件是等量关系,所以被称为等值连接。
非等值连接
案例:找出每个员工的薪资等级,要求显示员工名、薪资、薪资等级。
select e.ename,e.sal,s.grade from emp e inner join salgrade s on e.sal between s.losal and s.hisal;
条件不是一个等量关系,称为非等值连接。
自连接
案例:查询员工的上级领导,要求显示员工名和对应的领导名。
select e.empno,e.ename,ee.ename mgrName from emp e inner join emp ee on e.mgr = ee.empno;
一张表看作两张表
外连接
右外连接
案例:显示员工和部门的关系
-- 内连接 select e.ename, d.dname from emp e join dept d on e.deptno = d.deptno; =================================== -- 右外连接 select e.ename, d.dname from emp e right (outer) join dept d on e.deptno = d.deptno;
right表示什么:表示将join关键字右边的这张表看成是主表,然后去左边的表中查询匹配项。(left同理)
内连接是把所有能够匹配上的数据查询出来,而外连接会把主表中所有数据都查询出来,无论是否有匹配项。
思考:外连接的查询结果条数一定是 >= 内连接的查询结果条数?
答:正确!
案例:查询每个员工的上级领导,要求显示所有员工的名字和领导名字
select a.ename '员工名',b.ename '领导名' from emp a left outer join emp b on a.mgr = b.empno;
多表连接
语法:
select ... from a join b on a和b的连接条件 left join c on a和c的连接条件 ...
一条sql语句中,内连接和外连接可以混合。都可以出现。
案例:找出每个员工的部门名称以及薪资等级,要求显示员工名、部门名、薪资、薪资等级。
select e.ename '员工名',d.dname '部门名',e.sal '薪资', s.grade '薪资等级' from emp e join dept d on e.deptno = d.deptno join salgrade s on e.sal between s.losal and s.hisal;
案例:找出每个员工的部门名称以及薪资等级,要求显示员工名、领导名、部门名、薪资、薪资等级。
select e.ename '员工名',ee.ename '领导名',d.dname '部门名',e.sal '薪资', s.grade '薪资等级' from emp e join dept d on e.deptno = d.deptno join salgrade s on e.sal between s.losal and s.hisal left join emp ee on e.mgr = ee.empno;
九、子查询
什么是子查询?
select语句中嵌套select语句,被嵌套的select语句称为子查询。
子查询都可以出现在哪里
select ..(select). from ..(select). where ..(select).
where中的子查询
案例:找出比最低工资高的员工姓名和工资?
-
查出最低工资是多少?
-- 第一步:查出最低工资是多少? select min(sal) from emp;
--> 800
-
找出工资 >800 的
-- 第二步:找出 >800(第一步求出的最低工资)的 select ename,sal from emp where sal > 800;
-
合并
-- 第三步:合并 select ename, sal from emp group by empno having sal > (select min(sal) from emp);
from中的子查询
注意:from后面的子查询,可以将子查询的查询结果当作一张临时表。
案例:找出每个岗位的平均工资的薪资等级。
-
计算每个岗位的平均工资。
select job, avg(sal) `avg` from emp group by job;
--> 将结果看作一张真实存在的临时表 t。
-
计算薪资等级,将t表和salgrade表进行连接,并且需要把子查询的结果起个别名。
select a.job, b.grade from (select job, avg(sal) `avg` from emp group by job) a left join salgrade b on a.avg between losal and hisal;
select后的子查询
脱裤子放屁的感觉。可以用连接来实现。
十、union合并查询结果集
案例:查询工作岗位是MANAGER或SALESMAN的员工。
select * from emp where job = 'MANAGER' or job = 'SALESMAN'; -- 或 select * from emp where job in ('MANAGER','SALESMAN');
union:
select * from emp where job = 'MANAGER' union select * from emp where job = 'SALESMAN';
union的效率要高一些。对于表连接来说,每连接一次新表,匹配的次数满足笛卡尔积,成倍的翻。。。
但是union可以减少匹配的次数。在减少匹配次数的情况下,还可以完成两个结果集的拼接。a 连接 b 连接 c
a:10条
b:10条
c:10条
匹配的次数就是:10 * 10 * 10 = 1000
a 连接 b 一个结果:10 * 10 = 100
b 连接 c 一个结果:10 * 10 = 100
使用union的话是:100 + 100 = 200 次 (把乘法变成了加法)
十一、limit(⭐⭐⭐⭐⭐)
limit是将查询结果集的一部分取出来,通常使用在分页查询中。
百度默认:一页显示10条记录
分页的作用是为了提高用户的体验,因为一次全部都查出来,用户体验差。可以一页一页翻页看。
案例:按照薪资降序,取出排名在前5名的员工。
select ename,sal from emp order by sal desc limit 0,5;
完整用法:limit startIndex,length
startIndex是起始下标,length是长度
缺省用法:limit length
length是长度,取前几条。
案例:取出工资排在[5-9]名的员工。
select ename, sal from emp order by sal desc limit 4,5;
分页
每页显示3条记录:
第1页:limit 0,3 [0 1 2]
第1页:limit 0,3 [3 4 5]
第1页:limit 0,3 [6 7 8]
第1页:limit 0,3 [9 10 11]
每页显示pageSize条记录:
第pageNo页:limit (pageNo-1)*pageSize,pageSize
再次总结执行顺序
- from
- where
- group by
- having
- select
- order by
- limit
这篇关于MySQL_查询汇总(一)的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!
- 2024-11-16MySQL资料:新手入门教程
- 2024-11-16MySQL资料:新手入门教程
- 2024-11-15MySQL教程:初学者必备的MySQL数据库入门指南
- 2024-11-15MySQL教程:初学者必看的MySQL入门指南
- 2024-11-04部署MySQL集群项目实战:新手入门教程
- 2024-11-04如何部署MySQL集群资料:新手入门指南
- 2024-11-02MySQL集群项目实战:新手入门指南
- 2024-11-02初学者指南:部署MySQL集群资料
- 2024-11-01部署MySQL集群教程:新手入门指南
- 2024-11-01如何部署MySQL集群:新手入门教程