mysql数据查询（重点）

mysql数据查询

一：简单的查询语句（DQL）

　　语法格式：select 字段名1，字段名2，字段名3... from 表名;

　　提示：

　　　　1.任何一条sql语句以“；”结尾的。

　　　　2.sql语句不区分大小写。

　　　　3.sql语句中想要出现中文，必须把中文单引号括起来。

　　　　4.想要把修改后的数据重命名，则不用逗号，而是用as

　　举例：

select ename,sal*12 from emp;/select ename,empno from emp;
重命名：select ename,sal*12 as '年薪' from emp;

 

二：条件查询：

　　语法格式：select 字段，字段。。。 from 表明 where 条件 and/or 条件；//and交，or并。优先级直接用括号表示

举例：

查询对应工资的员工：select ename from emp where sal >= 5000;
查询对应员工的工资：select sal from emp where ename = 'zhangsan';

 

注意：

　　1.当需要查询含有null的数据的时候，需要用is来判断是不是null

select sal from emp where sal is null;

　　2.特殊查询字句：in；（查询多个的时候，等同于or）

select enamel，job from emp where job in('salesman','manger');

 

三：模糊查询：eg：找出名字中含有某字符或字符串时候用

注意：%：代表任意多个字符，_代表任意一个字符

select ename from emp where ename like '%o%';//找出含有o的名字

select ename from emp where ename like '_A%';//找出第二个字符是A的名字

select ename from emp where ename like '%\_%'//使用转义字符，将_无意义

 

四：数据排序：（升序和降序）

　　语法格式：select 字符段，字符段 from 表名 order by 需要排序的数据列

　　默认为升序，asc表示升序，desc表示降序。：

按照工资升序/降序，找出员工名和薪资

select ename,sal from order by sal asc;//升序排列
select ename,sal from order by sal desc;//降序排列

 

按照工资降序排列，当工资相同的时候再按照名字的升序排列(按照顺序判断排序相关性的重要性)

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

综上所述：执行顺序：select from where order by

 

五：分组函数：count计数，sum求和，avg平均值，max最大值。min最小值,ifnull判断是否为null的一个函数，先分组，再取最大值

select sum(sal) from emp;//工资总和
select max(sal) from emp;//最高工资
select min(sal) from emp;//最低工资
select avg(sal)  from emp;//平均工资
select count(*) from emp;//找出总人数
select count(sal) from emp;//找出总人数
select ename,(sal+ifnull(comm,0)）*12 as yearsal from emp;//如果是null的话，将其看成是0，然后再相加，否则含有null的式子全部看成是null
select xx from emp group by job;//要求查询到每一组的最高或者最低,先通过group 不用先分组，再查询最高和最低。注意：where在group by执行前就执行完成，之后再分组

 

　　分组函数的过滤顺序：

　　　　1.select ，2.from，3.where，4.group by，5.having，6.order by

　　子查询：select嵌套语句：

　　　　select ename,sal from emp where sal>(select avg(sal) from emp);

　　找出每个工作岗位的最高薪资：

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

　　找出每个部门不同工作岗位的最高薪资：(去寻找有几个变量，这里面有两个)

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

注意：当一条语句中有group by的话，select后面只能跟分组函数和参加分组的字段

 

 

六：having的使用：（处理使用情况：分完组后算出来的数据）

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

select max(sal),depno from emp group by deptno having max(sal)>2900;//效率低下
select max(sal),depno from emp where sal>2900 group by deptno;效率高,能使用where就是用where，不能用再使用having

 

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

select deptno,avg(sal) from emp group by depno having avg(sal)>2900

 

七：去除查询重复记录：distinct必须出现在最前方，表示后面字段联合去重。

　　查询工作岗位（不出现重复的工作岗位）

　　　　select distinct job from emp;

 

八：连接查询：

　　在实际开发中，一个业务对应多张表，比如：学生和班级，起码两张表

分类：

　　内连接：

　　　　1.等值连接：（等量关系）

查询每个员工的部门名称，要求显示员工名和部门名（当然也可以用92语法简单式子，但是用join和on把连接条件单独分割开来）

select
    字段，字段
from
    A表
join
    B表
on
    链接相等的条件
where
    筛选条件

　　　　2.非等值连接：(不是等量关系)

找出每个员工的薪资等级，显示员工名，工资，工资等级

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

　　　　3.自连接（一张表看成两张表，自己连接自己）可用等值和非等值连接

找出每个员工的上级领导，要求显示员工名和对应的领导。(员工的领导表=领导表的员工编号)

select
    a.ename,b.ename
from 
    emp a
inner join 
    emp b
on 
    a.mgr=b.empno;

 

　　外连接：

　　　　定义：内连接的两张表时平等的，外连接的两张表分主表和副表。主要查询主表，捎带查询副表，如果副表上面没有匹配主表，则生成NULL与之匹配

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

　　左外连接（左连接）：左边的表时主表

select 
    a.ename '员工'，b.ename'领导'
from 
    emp b
left join//left outer join 
    emp a
on
    a.mgr=b.empno;

　　右外连接（右连接）：右边的表时主表

select 
    a.ename '员工'，b.ename'领导'
from 
    emp b
left join//left outer join 
    emp a
on
    a.mgr=b.empno;

 

eg：查询不是null的所有员工的工资的员工所有信息

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

 

　　全连接:不常用

　　笛卡尔乘积现象：如果两张表连接查询的时候，没有限制要求，则会出现相乘个数据。

　　　　select ename,dname from emp,dept;

　　避免笛卡尔积现象：加条件避免

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

　　给表起一个别名，来区分字段在哪个表里面

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

 

九：三张以上表连接查询

语法：

select 
    xxxxx
from
    A
join 
    B
join
    C

　　A与B表连接的结果再与C连接

eg：找出每一个员工的部门名称，工资等级

select 
    e.ename,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;

 

eg:找出每一个员工的部门名称，工资等级，以及上级领导

select 
    e.ename,d.dname,s.grade,e1.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 e1
on 
    e.mgr=e1.empno;

 

十：子查询：

　　select语句嵌套select语句

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

 

　　where后面嵌套：

找出高于平均薪资的员工信息

select * from emp where sal>(select avg(sal) from emp);

 

　　from后面嵌套

找出每个部门平均薪水的薪资等级

第一步：
    找出每个部门平均薪水，按部门编号分组
select deptno,avg(sal) as avgsal from emp group by deptno;
​
第二步：
    将以上的查询结果当做临时表t，让t表和salgrade s表连接
select 
    t.*,s.grade
from
    (select deptno,avg(sal) as avgsal from emp group by deptno) as t
join
    salgrade s
on
    t.avfsal between s.losal and s.hisal;//这个条件意思是在字段之间（最低薪资和最高薪资之间

​

　　select后面嵌套

找出每个员工所在的部门名称，要求显示员工名 和部门名

select
    e.ename,
    (select d.dname from dept d where e.deptno=d.deptno) as dname
from
    emp e;

 

十一：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='mamager'
union
select ename,job from emp where job='salesman';

 

十二：limit（重点中的重点，分页查询就是它）

　特点：

• lmit是mysql特有的

• limit 取结果集中的部分数据，这是他的作用

语法机制：

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

案例：去除工资排名前五的员工

select ename,sal from emp order by sal desc limit 0,5;
select ename,sal from emp order by sal desc limit 5;

select所有环节

select
    ...
from
    ...
where
    ...
group by
    ...
having
    ...
order by
    ...
limit
    ...

 

十三：通用的标准分页sql（百度搜索每页显示10条记录）

每页显示3条记录：

• 第一页：0，3

• 第二页：3，3

• 第三页：6，3

• 第四页：9，3

• 第五页：12，3

每页显示pageSize（每页显示多少记录）条记录：

第pageNo页：（pageNo-1）*pageSize，pageSize