MySQL查询

最新推荐文章于 2024-10-14 17:54:13 发布

.Z.J.H.

最新推荐文章于 2024-10-14 17:54:13 发布

阅读量107

点赞数

文章标签： mysql

本文链接：https://blog.csdn.net/weixin_47208793/article/details/115904900

版权

MySQL查询

学习网址：https://www.bilibili.com/video/BV1fx411X7BD

文章目录

MySQL查询

0.0 emp表和dept表的创建

drop table if exists dept;
drop table if exists salgrade;
drop table if exists emp;
 
create table dept(
		deptno int(10) primary key,
		dname varchar(14),
		loc varchar(13)
		);
		
create table salgrade(
		grade int(11),
		losal int(11),
		hisal int(11)
		);
		
create table emp(
		empno int(4) primary key,
		ename varchar(10),
		job varchar(9),
		mgr int(4),
		hiredate date,
		sal double(7,2),
		comm double(7,2),
		deptno int(2)
		);
		
insert into dept(deptno,dname,loc) values(10,'ACCOUNTING','NEW YORK');
insert into dept(deptno,dname,loc) values(20,'RESEARCHING','DALLAS');
insert into dept(deptno,dname,loc) values(30,'SALES','CHICAGO');
insert into dept(deptno,dname,loc) values(40,'OPERATIONS','BOSTON');
 
insert into salgrade(grade,losal,hisal) values(1,700,1200);
insert into salgrade(grade,losal,hisal) values(2,1201,1400);
insert into salgrade(grade,losal,hisal) values(3,1401,2000);
insert into salgrade(grade,losal,hisal) values(4,2001,3000);
insert into salgrade(grade,losal,hisal) values(5,3001,5000);
 
insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno)
	values(7369,'SIMITH','CLERK',7902,'1980-12-17',800,null,20);
insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno)
	values(7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30);
insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno)
	values(7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30);
insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno)
	values(7566,'JONES','MANAGER',7839,'1981-04-02',2975,null,20);
insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno)
	values(7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30);
insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno)
	values(7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,null,30);
insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno)
	values(7782,'CLARK','MANAGER',7839,'1981-06-09',2450,null,10);
insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno)
	values(7788,'SCOTT','ANALYST',7566,'1987-04-19',3000,null,20);
insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno)
	values(7839,'KING','PRESIDENT',null,'1981-11-17',5000,null,10);
insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno)
	values(7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,null,30);
insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno)
	values(7876,'ADAMS','CLERK',7788,'1987-05-23',1100,null,20);
insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno)
	values(7900,'JAMES','CLERK',7698,'1981-12-03',950,null,30);
insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno)
	values(7902,'FORD','ANALYST',7566,'1981-12-03',3000,null,20);
insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno)
	values(7934,'MILLER','CLERK',7782,'1982-01-23',1300,null,10);

1.0 条件查询

条件查询

在这里插入图片描述

between…and…

找出工资在1100和3000之间的员工，包括1100和3000?

select ename ,sal from emp where sal >= 1100 and sal <= 3000;

select ename ,sal from emp where sal between 1100 and 3000;--between . . .and...是闭区间[1100 ～ 3000]

select ename ,sal from emp where sal between 3000 and 1100;--查询不到任何数据

between and在使用的时候必须左小右大。

between and除了可以使用在数字方面之外，还可以使用在字符串方面。

select ename from emp where ename between 'A' and 'c';--左闭右开
--+-------+
--| ename |
--+-------+
--  ATLEN
--  BTAKE
--  ADAMS
--+-------+

is null 和 is not null

找出哪些人没有津贴?

在数据库当中NULL不是一个值,代表什么也没有，为空。空不是一个值,不能用等号衡量。

必须使用is null或者is not null

select ename ,sal , comm from emp where comm is null;

select ename ,sal , comm from emp where comm = null;--错误写法

and 和 or 的优先级

and和or联合起来用:找出薪资大于1000的并且部门编号是20或30部门的员工。

and优先级大于or

select ename ,sal , deptno from emp where sal > 100o and deptno = 20 or deptno = 30;--错误的

select ename ,sal , deptno from emp where sal > 1000 and (deptno = 20 or deptno = 30);--正确的。

注意:当运算符的优先级不确定的时候加小括号。

in 和 not in

in等同于or:找出工作岗位是MANAGER和sA工ESMAN的员工?

select ename, job from emp where job = 'SATESMAN' or job = 'MANAGER ';

select ename ,job from emp where job in( ' SALESMAN','MANAGER');

not in ：不在这几个值当中

模糊查询 like

找出名字当中含有o的?

(在模糊查询当中，必须掌握两个特殊的符号，一个是%，一个是_ )

%代表任意多个字符,_代表任意1个字符。

查询%或者_用\转义

select ename from emp where ename like '%O%';

找出名字中第二个字母是A的?

select ename from emp where ename iike '_A%’;

排序

注意:默认是升序。怎么指定升序或者降序呢?

asc表示升序，desc表示降序。

select ename , sal from emp order by sal; --升序

select ename , sal from emp order by sal asc; --升序

select ename , sal from emp order by sal desc; --降序。

按照工资的降序排列，当工资相同的时候再按照名字的升序排列。

select ename , sal from emp order by sal desc;

select ename ,sal from emp order by sal desc , ename asc;

注意:

越靠前的字段越能起到主导作用。只有当前面的字段无法完成排序的时候，才会启用后面的字段。

order by 是最后执行的。

分组函数

count	取得记录数
sum	求和
avg	取平均
max	取最大值
min	取最小值

记住:所有的分组函数都是对"某一组“数据进行操作的。

分组函数一共5个。
分组函数还有另一个名字:多行处理函数。
多行处理函数的特点:输入多行，最终输出的结果是1行。
分组函数自动忽略NULL，不需要添加is not null。

count(*)与count(某个字段)区别

count(*): 不是统计某个字段中数据的个数，而是统计总记录条数。(和某个字段无关)

count (comm): 表示统计comm字段中不为NUIL的数据总数量。

单行处理函数

什么是单行处理函数?

输入一行,输出一行。

计算每个员工的年薪?

select ename , (sal+comm)*12 as yearsal from emp;
--出现计算结果为NULL的情况

重点:所有数据库都是这样规定的，只要有NULL参与的运算结果一定是NULL。

使用ifnull函数:

select ename , (sal+ifnull(comm,0))*12 as yearsal from emp;

ifnull()空处理函数?

ifnull(可能为NULL的数据,当做什么数据处理)

ifnull()属于单行处理函数。

select ename ,ifnull (comm ,0) as comm from emp;

group by和having

group by :按照某个字段或者某些字段进行分组。

having : having是对分组之后的数据进行再次过滤。（就是为了过滤分组后的数据而存在的——不可以单独的出现）

案例:找出每个工作岗位的最高薪资。

select max (sa1)from emp group by job ;

注意:

分组函数一般都会和group by联合使用，这也是为什么它被称为分组函数的原因。
并且任何一个分组函数(count sum avg max min）都是在group by语句执行结束之后才会执行的。
当一条sql语句没有group by的话，整张表的数据会自成一组。

select ename ,max(sal),job from emp group by job;

以上在mysql当中，查询结果是有的，但是结果没有意义（存在ename字段），在oracle数据库当中会报错。语法错误。oracle的语法规则比MysQL语法规则严谨。

记住一个规则:当一条语句中有group by的话，select后面只能跟分组函数和参与分组的字段。

多个字段联合起来一块分组

案例:找出每个部门不同工作岗位的最高薪资。

select depto,job,max(sal) from emp group by depto,job;

having与where的使用

能在where过滤的地方先过滤，这样效率比较高。

找出每个部门的最高薪资,要求显示薪资大于2900的数据。

--第一步:找出每个部门的最高薪资
select max (sal) ,deptno from emp group by deptno;

--第二步:找出薪资大于2900
select max(sal) , deptno from emp group by deptno having max(sal) > 2900;
--这种方式效率低。
select max(sal) ,deptno from emp where sal > 2900 group by deptno;
--效率较高，建议能够使用where过滤的尽量使用where过滤

找出每个部门的平均薪资，要求显示薪资大于2000的数据。

--第一步:找出每个部门的平均薪资
select deptno ,avg (sal) from emp group by deptno;

--第二步:要求显示薪资大于2000的数据
select deptno,avg(sal) from emp group by deptno having avg(sal)> 2000;

DQL查询语句顺序

select
	*				 5
from
	tablename		 1
where
	条件				2
group by
	字段				3
having
	条件				4
order by
	。。。			   6

distinct去重

distinct关键字去除重复数据。

select distinct job from emp;

--错误示范
select deptno, distinct job from emp;

记住：distinct只能出现在所有字段的最前面。

--deptno和job联合起来去重
select distinct deptno,job from emp;

结合分组函数使用：统计岗位数量？

select count(distinct job) from emp;

2.0 连接查询

连接查询

什么是连接查询?

在实际开发中，大部分的情况下都不是从单表中查询数据，一般都是多张表联合查询取出最终的结果。在实际开发中，一般一个业务都会对应多张表，比如:学生和班级，起码两张表。

连接查询的分类

1、根据语法出现的年代来划分的话,包括:

sQL92(一些老的DBA可能还在使用这种语法。DBA: DataBase Administrator，数据库管理员)
sQL99 (比较新的语法)

2、根据表的连接方式来划分,包括:

内连接:

等值连接非等值连接自连接
外连接:

左外连接(左连接)

右外连接(右连接)
全连接(这个不讲,很少用!)

连接查询原理以及笛卡尔积现象

在表的连接查询方面有一种现象被称为:笛卡尔积现象。(笛卡尔乘积现象)

案例:找出每一个员工的部门名称，要求显示员工名和部门名。

select ename,dname from emp,dept;
--ename和dname要联合起来一块显示，粘到一块。
--emp有14条记录，dept有4条记录，总共匹配次数为14*4=56次。

笛卡尔积现象

当两张表进行连接查询的时候，没有任何条件进行限制，最终的查询结果条数是两张表记录条数的乘积。

表的别名有什么好处?

select e.ename ,d. dname from emp e,dept d;

第一:执行效率高。
第二:可读性好。

怎么避免笛卡尔积现象?

当然是加条件进行过滤。

思考:避免了笛卡尔积现象，会减少记录的匹配次数吗?

不会,次数还是56次，只不过显示的是有效记录。

案例:找出每一个员工的部门名称，要求显示员工名和部门名。

select e.ename,d.dname
from emp e,dept d
where e.deptno = d.deptno
--SQL92,以后不用

内连接之等值连接

最大特点：条件是等量关系。

案例:找出每一个员工的部门名称，要求显示员工名和部门名。

--SQL92
select e.ename,d.dname
from emp e,dept d
where e.deptno = d.deptno

--SQL99（常用）
select 
	e.ename,d.dname
from 
	emp e
inner join --inner可以省略，带着inner可读性强
	dept d
on
	e.deptno = d.deptno --连接条件
where
	条件。。。。 --过滤条件

sQL99语法结构更清晰一些: 表的连接条件和后来的where条件分离了。

内连接之非等值连接

最大的特点是:连接条件中的关系是非等量关系。

案例: 找出每个员工的工资等级，要求显示员工名、工资、工资等级。

select 
	e.ename,e.sal,s.grade
from
	emp e
inner join
	salgrade s
on
	e.sal between s.losal and s.grade;

自连接

最大的特点是:一张表看做两张表。自己连接自己。

案例:找出每个员工的上级领导，要求显示员工名和对应的领导名。

在这里插入图片描述

select
	e1.ename , e2.ename as mgrname
from
	emp e1
inneer join
	emp e2
on 
	e1.mgr = e2.empno;
--KING没有领导,为NULL，不显示

外连接

什么是外连接,和内连接有什么区别?

内连接:

假设A和B表进行连接，使用内连接的话，凡是A表和B 表能够匹配上的记录查询出来，这就是内连接。AB两张表没有主副之分,两张表是平等的。

外连接:

假设A和B表进行连接，使用外连接的话，A两张表中有一张表是主表，一张表是副表，主要查询主表中的数据，捎带着查询副表，当副表中的数据没有和主表中的数据匹配上，副表自动模拟出NULL与之匹配。

外连接m的分类?

左外连接（(左连接):表示左边的这张表是主表。

右外连接右连接):表示右边的这张表是主表。

左连接有右连接的写法，右连接也会有对应的左连接的写法。

案例:找出每个员工的上级领导，要求显示员工名和对应的领导名。

--解决内连接KING数据丢失的问题
--左连接
select
	e1.ename , e2.ename as mgrname
from
	emp e1
left outer join --outer可省略
	emp e2
on 
	e1.mgr = e2.empno;

--右连接
select
	e1.ename , e2.ename as mgrname
from
	emp e2
right outer join
	emp e1
on 
	e1.mgr = e2.empno;

最大的特点：主表的数据无条件的全部查询出来。

案例：找出哪个部门没有员工？

select
	d.*
from
	emp e
right outer join
	dept d
on
	e.deptno = d.deptno
where
	e.empno is null;

多表连接查询

表示:A表和B表先进行表连接，连接之后A表继续和c表进行连接。

案例:找出每一个员工的部门名称以及工资等级。

select
	e.ename,d.dname,s.grade
from
	emp e
inner join
	dept d
on 
	e.deptno = d.deptno
inner join
	salgrade s
on	
	e.sal between s.losal and s.hisal;

案例:找出每一个员工的部门名称、工资等级、以及上级领导。

select
	e1.ename,d.dname,s.grade,e2.ename mgrname
from
	emp e1
on 
	e1.mgr = e2.empno
inner join
	dept d
on 
	e2.deptno = d.deptno
inner join
	salgrade s
on	
	e2.sal between s.losal and s.hisal
left outer join
	emp e2
on
	e1.mgr = e2.empno;

子查询

什么是子查询?子查询都可以出现在哪里?

select语句当中嵌套select语句，被嵌套的select语句是子查询。

子查询可以出现在哪里?

select
	.. (select) .
from
	.. (select) .
where
	.. (select) .

3.0 union(可以将查询结果集相加）

案例:找出工作岗位是sALESMAN和MANAGER的员工?

--第一种: 
select ename , job from emp where job = 'MANAGER' or job = 'SALESMAN' ;

--第二种:
 select ename , job from emp where job in ( 'MANAGER'，' SALESMAN') ;

--第三种: union
select ename ,job from emp where job = 'MANAGER'
union
select ename ,job from emp where job = 'SALESMAN ';

两张不相干的表中的数据拼接在一起显示?

select lename from emp
union
select dname from dept;

4.0 分页查询

limit

limit是mysql特有的，其他数据库中没有，不通用。(oracle中有一个相同的机制，叫做rownum)

limit取结果集中的部分数据,这时它的作用。

语法机制: limit startIndex, length

startIndex表示起始位置,从0开始，0表示第一条数据；length表示取几个

案例:取出工资前5名的员工（思路:降序取前5个)

select 
	*
from
	emp
group by
	sal desc
limit
	0, 5;

limit是sql语句最后执行的一个环节

通用的标准分页sql

每页显示3条记录:

第1页: 0, 3

第2页: 3, 3

第3页: 6, 3

第4页: 9, 3

第5页: 12, 3

每页显示pagesize条记录:

第pageNo页: (pageNo - 1) * pagesize, pagesize

pagesize是什么?是每页显示多少条记录

pageNo是什么?显示第几页

java代码 {
	int pageNo = 2; //页码是2
	int pagesize = 1o; //每页显示10条
	limit (pageNo - 1)* pagesize,pagesize
}

.Z.J.H.

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫