MySQL- - -第二天
2021/5/24 19:28:52
本文主要是介绍MySQL- - -第二天,对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!
第二天
1、关于查询结果集的去重
注意:原表数据不会被修改,只是查询结果去重。
去重需要使用一个关键字:distinct
mysql> select distinct job from emp;
distinct只能出现在所有字段的最前方。
// 这样编写是错误的,语法错误。
mysql> select ename,distinct job from emp;
// distinct出现在job,deptno两个字段之前,表示两个字段联合起来去重。
mysql> select distinct job,deptno from emp;
统计一下工作岗位的数量?
select count(distinct job) from emp; +---------------------+ | count(distinct job) | +---------------------+ | 5 | +---------------------+
2、连接查询
2.1、什么是连接查询?
从一张表中单独查询,称为单表查询。 emp表和dept表联合起来查询数据,从emp表中取员工名字,从dept表中取部门名字。 这种跨表查询,多张表联合起来查询数据,被称为连接查询。
2.2、连接查询的分类?
根据语法的年代分类: SQL92:1992年的时候出现的语法 SQL99:1999年的时候出现的语法 我们这里重点学习SQL99.(这个过程中简单演示一个SQL92的例子) 根据表连接的方式分类: 内连接: 等值连接 非等值连接 自连接 外连接: 左外连接(左连接) 右外连接(右连接) 全连接(不讲)
2.3、当两张表进行连接查询时,没有任何条件的限制会发生什么现象?(笛卡尔积现象)
案例:查询每个员工所在部门名称? 两张表连接没有任何条件限制: select ename,dname from emp, dept; +--------+------------+ | ename | dname | +--------+------------+ | SMITH | ACCOUNTING | | SMITH | RESEARCH | | SMITH | SALES | | SMITH | OPERATIONS | | ALLEN | ACCOUNTING | | ALLEN | RESEARCH | | ALLEN | SALES | | ALLEN | OPERATIONS | ... 56 rows in set (0.00 sec) 14 * 4 = 56 当两张表进行连接查询,没有任何条件限制的时候,最终查询结果条数,是 两张表条数的乘积,这种现象被称为:笛卡尔积现象。(笛卡尔发现的,这是 一个数学现象。)
关于表的别名:(把上面56条记录的语句别名修饰)
select e.ename,d.dname from emp e,dept d;
表的别名有什么好处?
第一:执行效率高; 第二:可读性好。
2.4、怎么避免笛卡尔积现象?(加筛选条件)
连接时加条件,满足这个条件的记录被筛选出来! select e.ename,d.dname from emp, dept where e.deptno =d.deptno;
思考:最终查询的结果条数是14条,但是匹配的过程中,匹配的次数减少了吗?
还是56次,只不过进行了四选一。次数没有减少(只是显示出需要的)。
注意:通过笛卡尔积现象得出,表的连接次数越多效率越低,尽量避免表的 连接次数。
2.5、内连接之等值连接。(条件是等量关系)
案例:查询每个员工所在部门名称,显示员工名和部门名?
emp e和dept d表进行连接。条件是:e.deptno = d.deptno
SQL92语法: select e.ename,d.dname from emp e, dept d where e.deptno = d.deptno;
sql92的缺点:结构不清晰,表的连接条件,和后期进一步筛选的条件,都放到了where后面。
SQL99语法:
select ... from a join b on a和b的连接条件 where 筛选条件
sql99优点:表连接的条件是独立的,连接之后,如果还需要进一步筛选,再往后继续添加where
SQL99语法: select e.ename,d.dname from emp e join dept d on e.deptno = d.deptno;
//inner可以省略(带着inner可读性更好!!!一眼就能看出来是内连接)
select e.ename,d.dname from emp e inner join dept d on e.deptno = d.deptno; // 条件是等量关系,所以被称为等值连接。
2.6、内连接之非等值连接(连接条件中的关系时非等量关系- - - 范围)
案例:找出每个员工的薪资等级,要求显示员工名、薪资、薪资等级?
select e.ename, e.sal, s.grade from emp e join salgrade s on e.sal between s.losal and s.hisal; // 条件不是一个等量关系,称为非等值连接。
2.7、内连接之自连接(一张表看做两张表,自己连自己)
案例:查询员工的上级领导,要求显示员工名和对应的领导名?
(下面的写法是内连接- - - 输出不包含KING)
select a.ename as '员工名', b.ename as '领导名' from emp a join emp b on a.mgr = b.empno; //员工的领导编号 = 领导的员工编号
2.8、外连接(主、副之分)
什么是外连接,和内连接的区别?
内连接:
假设A和B表进行连接,使用内连接的话,凡是A表和B表能够匹配上的记录查询出来,这就是内连接。A、B两张表没有主副之分,两张表时平等的。
外连接:(主表数据无条件的全部查询出来)
假设A和B表进行连接,使用外连接的话,A、B两张表中有一张是主表,一张表是副表,主要查询主表中的数据,捎带着查询副表,当副表中的数据没有和主表中的数据匹配上,副表自动模拟出NULL与之匹配。
外连接的分类?
左外连接(左连接):表示左边的这张表是主表。
右外连接(右连接):表示右边的这张表是主表。
(左连接有右连接的写法,右连接有左连接的写法)
案例:查询员工的上级领导,要求显示员工名和对应的领导名?
(要包含KING- - - 故使用外连接 [主查员工,副查上级领导,即使没有上级领导,也要输出KING])
select a.ename '员工',b.ename '领导' from emp a left outer join emp b on a.mgr = b.empno;
left代表:(right同理)
将join关键字左边的这张表看成主表,主要是为了将这张表的数据全部查询出来,捎带着关联查询右边的表。
outer可以省略,可读性高
思考:外连接的查询结果条数一定是 >= 内连接的查询结果条数?
正确。
案例:查询哪个部门没有员工?
select d.* from emp e right outer join dept d on e.deptno = d.deptno where e.empno is null;
2.9、三张表,四张表怎么连接?
语法: select ... from a join b on a和b的连接条件 join c on a和c的连接条件 right join d on a和d的连接条件 一条SQL中内连接和外连接可以混合。都可以出现!
案例:找出每一个员工的部门名称以及工资等级,要求显示员工名、部门名、薪资、薪资等级?
select e.ename,e.sal,d.dname,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,e.sal,d.dname,s.grade,l.ename 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 l on e.mgr = l.empno;
3、子查询
3.1、什么是子查询?
select语句中嵌套select语句,被嵌套的select语句称为子查询。
3.2、子查询都可以出现在哪里呢?
select ..(select). from ..(select). where ..(select).
3.3、where子句中的子查询
案例:找出比最低工资高的员工姓名和工资?
select ename,sal from emp where sal > min(sal);
ERROR 1111 (HY000): Invalid use of group function
where子句中不能直接使用分组函数。
实现思路:
第一步:查询最低工资是多少 select min(sal) from emp; +----------+ | min(sal) | +----------+ | 800.00 | +----------+ 第二步:找出>800的 select ename,sal from emp where sal > 800; 第三步:合并 select ename,sal from emp where sal > (select min(sal) from emp);
3.4、from(跟的是表)子句中的子查询
注意:from后面的子查询,可以将子查询的查询结果当做一张临时表。(技巧)
案例:找出每个岗位的平均工资的薪资等级。
第一步:找出每个岗位的平均工资(按照岗位分组求平均值) select job,avg(sal) from emp group by job; +-----------+-------------+ | job | avgsal | +-----------+-------------+ | ANALYST | 3000.000000 | | CLERK | 1037.500000 | | MANAGER | 2758.333333 | | PRESIDENT | 5000.000000 | | SALESMAN | 1400.000000 | +-----------+-------------+t表 第二步:克服心理障碍,把以上的查询结果就当做一张真实存在的表t mysql> select * from salgrade; s表 +-------+-------+-------+ | GRADE | LOSAL | HISAL | +-------+-------+-------+ | 1 | 700 | 1200 | | 2 | 1201 | 1400 | | 3 | 1401 | 2000 | | 4 | 2001 | 3000 | | 5 | 3001 | 9999 | +-------+-------+-------+ t表和s表进行表连接,条件:t表avg(sal) between s.losal and s.hisal; select t.*, s.grade from (select job,avg(sal) as avgsal from emp group by job) t join salgrade s on t.avgsal between s.losal and s.hisal; +-----------+-------------+-------+ | job | avgsal | grade | +-----------+-------------+-------+ | CLERK | 1037.500000 | 1 | | SALESMAN | 1400.000000 | 2 | | ANALYST | 3000.000000 | 4 | | MANAGER | 2758.333333 | 4 | | PRESIDENT | 5000.000000 | 5 | +-----------+-------------+-------+
3.5、select后面出现的子查询(这个内容不需要掌握,了解即可!!!)
案例:找出每个员工的部门名称,要求显示员工名,部门名?
select e.ename,e.deptno,(select d.dname from dept d where e.deptno = d.deptno) as dname from emp e;
4、union合并查询结果集(可以将查询结果集相加)
案例:查询工作岗位是MANAGER和SALESMAN的员工?
第一种 select ename,job from emp where job = 'MANAGER' or job = 'SALESMAN'; 第二种 select ename,job from emp where job in('MANAGER','SALESMAN'); 第三种 select ename,job from emp where job = 'MANAGER' union select ename,job from emp where job = 'SALESMAN';
union的效率要高一些。对于表连接来说,每连接一次新表,
则匹配的次数满足笛卡尔积,成倍的翻。。。
但是union可以减少匹配的次数。在减少匹配次数的情况下,
还可以完成两个结果集的拼接。
a 连接 b 连接 c a 10条记录 b 10条记录 c 10条记录 匹配次数是:1000 a 连接 b一个结果:10 * 10 --> 100次 a 连接 c一个结果:10 * 10 --> 100次 使用union的话是:100次 + 100次 = 200次。(union把乘法变成了加法运算)
union在使用的时候有注意事项吗?
//错误的:union在进行结果集合并的时候,要求两个结果集的列数相同。
select ename,job from emp where job = 'MANAGER' union select ename from emp where job = 'SALESMAN';
5、limit(非常重要,分页查询靠它mysql特有)
5.1、limit作用:将查询结果集的一部分取出来。通常使用在分页查询当中。
5.2、limit怎么用呢?(startIndex是起始下标,length是长度)
完整用法:limit startIndex, length startIndex是起始下标,length是长度。 起始下标从0开始。 **缺省用法:limit 5; 这是取前5.** 案例:按照薪资降序,取出排名在前5名的员工? select ename,sal from emp order by sal desc limit 5; //取前5 select ename,sal from emp order by sal desc limit 0,5; +-------+---------+ | ename | sal | +-------+---------+ | KING | 5000.00 | | SCOTT | 3000.00 | | FORD | 3000.00 | | JONES | 2975.00 | | BLAKE | 2850.00 | +-------+---------+
5.3、注意:mysql当中limit在order by之后执行(limit是sql语句最后执行的一个环节)!!!!!!
select 5 ... from 1 ... where 2 ... group by 3 ... having 4 ... order by 6 ... limit 7 ...
5.4、取出工资排名在[3-5]名的员工?
select ename,sal from emp order by sal desc limit 2, 3; 2表示起始位置从下标2开始,就是第三条记录。 3表示长度。 +-------+---------+ | ename | sal | +-------+---------+ | FORD | 3000.00 | | JONES | 2975.00 | | BLAKE | 2850.00 | +-------+---------+
5.5、分页
每页显示3条记录 第1页:limit 0,3 [0 1 2] 第2页:limit 3,3 [3 4 5] 第3页:limit 6,3 [6 7 8] 第4页:limit 9,3 [9 10 11] 每页显示pageSize条记录 第pageNo页:limit (pageNo - 1) * pageSize , pageSize public static void main(String[] args){ // 用户提交过来一个页码,以及每页显示的记录条数 int pageNo = 5; //第5页 int pageSize = 10; //每页显示10条 int startIndex = (pageNo - 1) * pageSize; String sql = "select ...limit " + startIndex + ", " + pageSize; }
记公式:
limit (pageNo-1)*pageSize , pageSize
这篇关于MySQL- - -第二天的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!
- 2024-05-29阿里 Canal 实时同步 MySQL 增量数据至 ClickHouse 数据库
- 2024-05-24在Linux下管理MySQL的大小写敏感性
- 2024-04-26MySQL查出时间比实际晚8小时的解决方案
- 2024-04-01JPA不识别MySQL的枚举类型
- 2024-03-30mysql数据库表卡死解决方法
- 2024-03-15MySQL多数据源笔记5-ShardingJDBC实战
- 2024-03-11natural join mysql
- 2024-03-11关于VS2017,VS2015 中利用 EF使用Mysql 不显示数据源问题解决方案
- 2024-02-26mysql 阿里云xb后缀备份文件恢复-icode9专业技术文章分享
- 2024-02-22docker mysql 5.7