发帖
雷达卡
-- 条件查询
-- 查询基本工资大于等于2000小于等于3000的员工信息
select * from emp where sal between 3000 and 5000;
select * from emp where sal>=3000 and sal<=5000;
select * from emp where sal<=3000 or sal>=5000;
-- 查询10号部门和20号部门中sal低于2000的员工信息
select * from emp where (deptid=10 or deptid=20) and sal<=4000;
-- 练习：查询salesman的所属部门：姓名,职位,所在部门
select * from emp;
select ename,job,deptid from emp where job=\"manager\";
-- 空值查询
-- 查询mgr为空的记录
select * from emp;
select * from emp where mnager is null;#所有比较符不能对空值做判断，比如=null
select * from emp where mnager is not null;
-- 练习：查询comm不为空的记录
-- 模糊查询: select 字段1[,字段2,…] from 表名 where 字符串字段[ not] like 通配符,只能用于字符串数据的查询
#百分号（%）通配符：匹配多个字符;
#下划线（_）通配符：匹配一个字符
-- 查询姓名以a开头的员工信息
select * from emp where ename like \"a%\" ;
-- 查询姓名中包含a的员工信息
select * from emp where ename like \"%c%\" ;
select * from emp where ename like \"%k\" ;
-- 查询姓名中第二个字符为a的员工信息
select * from emp where ename like \"_c%\";
-- 练习：查询员工姓名中不包含s的员工信息
select * from emp where ename not like \"%s%\" ;
-- 查询结果排序:select 字段1[,字段2,…] from 表名 order by 字段1[ 排序方向,字段2 排序方向,…]:asc升序，desc降序（没有指定排序方向时，默认是asc升序
-- 单字段排序：查询所有员工信息按sal降序显示
select * from emp order by sal desc;
select * from emp order by sal asc;
-- 多字段排序：查询所有员工信息按deptno升序、sal降序显示:字段间用“，”隔开
select * from emp order by deptid asc, sal desc;
select * from emp where job=\"manager\" order by deptid asc, sal desc;
-- 限制查询结果数量:限制查询结果数量：select 字段1[,字段2,…] from 表名 limit [偏移量,] 行数;limit接受一个或两个数字参数，参数必须是一个整数常量;
#偏移量可以省略
-- 查询基本工资最高的前5位员工
select * from emp order by sal desc limit 5;
-- 查询基本工资第5到7名的员工
select * from emp order by sal desc limit 4,3 ;#查询基本工资在5-7名的员工，所以从第一行开始偏移量为4行到了第5行，然后取5-7行，行数为3
-- 练习：查询最后入职的5位员工
select * from emp order by hiredate desc limit 5;
-- 聚合运算
-- 查询emp表中员工总数、最高工资、最低工资、平均工资及工资总和
select count(*) as 员工总数,max(sal) as 最高工资, min(sal) as 最低工资, avg(sal) as 平均工资, sum(sal) as 工资总和 from emp;
-- 分组查询
-- 查询各部门的平均工资:select 字段1[,字段2,…] from 表名[ where 查询条件] group by 分组字段1[,分组字段2,…];
select deptid,avg(sal) as 平均工资
from emp
group by deptid
;
-- 查询各部门不同职位的平均工资(多字段分组)
select job,deptid,avg(sal) as 平均工资
from emp
group by deptid,job
;
-- 练习：查询各部门的员工数
select deptid,count(*)
from emp
group by deptid
;
-- 练习：查询各部门不同职位的人数count(*)，括号里面可以为*，也可以是其他字段，count只统计非空值
select deptid,job,count(*) as 人数
from emp
group by deptid,job
order by deptid
;
-- 分组后筛选：select 字段1[,字段2,…] from 表名[ where 查询条件][ group by 分组字段1[,分组字段2,…]] having 筛选条件;
-- 查询各部门clerk的平均工资
select deptid,job,avg(sal) as 平均工资
from emp
group by deptid,job
having job=\"clerk\"
;
select deptid,job,avg(sal) as 平均工资
from emp
where job=\"clerk\"
group by deptid,job
having avg(sal)>1000
;
-- 查询平均工资大于2000的部门,where不能对聚合函数进行筛选，因为语句执行顺序，where执行在聚合计算之前，
#所以用having计算(对分组结果计算),having也支持运算符筛选
select avg(sal) as 平均工资
from emp
where 平均工资>2000
group by deptid
;
-- 多表连接查询
create table t1(key1 char,v1 int);
create table t2(key2 char,v2 int);
insert into t1 values(\'a\',1),(\'a\',2),(\'b\',3),(\'c\',4),(\'a\',13);
insert into t2 values(\'b\',10),(\'b\',11),(\'a\',12),(\'a\',13),(\'e\',14);
select * from t1;
select * from t2;
-- 内连接：select 字段1[,…] from 表1[ inner] join 表2 on 表1.key=表2.key;
select * from t1 inner join t2 on t1.key1=t2.key2;
-- 左连接:select 字段1[,…] from 表1 left join 表2 on 表1.key=表2.key;
select * from t1 left join t2 on t1.key1=t2.key2 ;
-- 右连接
select * from t1 right join t2 on t1.key1=t2.key2 ;
select * from t2 left join t1 on t1.key1=t2.key2 ;
-- 合并查询
-- union去重:select 字段1[,字段2,…] from 表名 union select 字段1[,字段2,…] from 表名;被合并的结果集的字段列数、顺序和数据类型必须完全一致,即2个表纵向合并
select * from t1 union select * from t2 ;
-- union all不去重: select 字段1[,字段2,…] from 表名 union all select 字段1[,字段2,…] from 表名;
select * from t1 union all select * from t2 ;
#左反连接：左表中关键字段匹配不到的数据
select * from t1 left join t2 on t1.key1=t2.key2
where t2.key2 is null;
#右反连接:右表中关键字段匹配不到的数据
select * from t2 left join t1 on t1.key1=t2.key2
where t1.key1 is null;
select * from t1 right join t2 on t1.key1=t2.key2
where t1.key1 is null;
-- 多表查询练习
select * from emp order by sal;
create table salgrade (grade int,losal int ,hisal int);
desc salgrade;
insert into salgrade value (1,1500,2500),(2,2501,3500),(3,3501,4500),(4,4501,9900);
select * from emp;
select * from dept;
select * from salgrade;
-- 查询每位员工的ename,deptname,sal
select ename,deptname,sal
from emp
left join dept
on emp.deptid=dept.deptid;
-- 查询各地区的员工数（统计每个地区，没有员工计为0）
select loc,count(empid)
from dept
left join emp
on dept.deptid=emp.deptid
group by loc
;
-- 查询manager的姓名、所属部门名称和入职日期：ename,dname,job,hiredate(内连接/笛卡尔积连接)
#笛卡尔积是对表中的每一条记录都匹配上另外一个表的记录，不管数据是否正确
select job,ename,deptname,hiredate
from emp
left join dept
on emp.deptid=dept.deptid
where job=\"manager\"
;
#笛卡尔积连接：效率更低
select job,ename,deptname,hiredate
from emp,dept
where emp.deptid=dept.deptid
and job=\"manager\"
;
select *
from emp,dept;
-- 查询每位员工的工资等级；empno,ename,sal,grade(不等值连接)
show tables;
select empid,ename,sal,grade
from emp
left join salgrade
on sal between losal and hisal;
-- 查询各工资等级的员工数
select grade,count(empid)
from emp
left join salgrade
on sal between losal and hisal
group by grade;
-- 查询所有员工姓名及其直属领导姓名（自连接：通过别名，将同一张表视为多张表）
select a1.ename as 员工姓名,b1.ename as 直属领导
from emp as a1
left join emp as b1
on a1.mnager=b1.empid;
-- 查询入职日期早于其直属领导的员工：empno,ename,dname
select a1.empid,a1.ename,a1.hiredate as 员工入职日期,a2.hiredate as 领导入职日期,dept.deptname
from emp as a1
left join emp as a2 on a1.mnager=a2.empid
left join dept on a1.deptid=dept.deptid
where a1.hiredate<a2.hiredate ;
-- 子查询
-- 标量子查询：返回的结果是一个数据（单行单列）
-- 查询基本工资高于公司平均工资的员工信息
select * from emp where sal>avg(sal);#where 里面不能用聚合函数
select avg(sal) from emp;#作为下面where语句的筛选条件
select * from emp where sal>(select avg(sal) from emp);
-- 练习：查询和jones同一个领导的员工：empno,ename,job,mgr
select mnager from emp where ename=\"jones\";#员工为jones的领导的工号
select empid,ename,job,mnager
from emp where mnager=(select mnager from emp where ename=\"jones\");
-- 行子查询:返回的结果是一行(单行多列
-- 查询和scott同部门同职位的员工：empno,ename,job,deptno
select job,deptid from emp where ename=\"scott\" ;
select * from emp order by deptid;
select * from emp where (job,deptid)=(select job,deptid from emp where ename=\"scott\") and ename!=\"scott\";
-- 列子查询：返回的结果是一列(多行单列):any 大于一组数里面的一个即可，可以用in
-- 查询普通员工的工资等级：empno,ename,sal,grade
select distinct mnager from emp where mnager is not null;
select *
from emp
left join salgrade
on sal between losal and hisal
where empid not in (select distinct mnager from emp where mnager is not null);
-- 练习：查询员工数不少于4人的部门的所有员工：empno,ename,deptno
select count(empid),deptid from emp
group by deptid
having count(empid)>=4;
select empid,ename,deptid from emp where deptid in
(select deptid from emp
group by deptid
having count(empid)>=4);
-- 查询基本工资高于30号部门任意员工的员工信息
-- 查询基本工资高于30号部门所有员工的员工信息
select sal from emp where deptid=20;
select *
from emp
where sal>all(select sal from emp where deptid=20);
#或者大于20号部门的最高工资
select *
from emp
where sal>all(select max(sal) from emp where deptid=20);
-- from子查询
-- 查询各部门最高工资的员工：empno,ename,sal,deptno
select * from emp order by deptid,sal desc;
select max(sal) as 最高工资,deptid from emp group by deptid;
select *
from emp
left join (select max(sal) as 最高工资,deptid from emp group by deptid) as t1
on emp.deptid=t1.deptid
where sal=最高工资;
京公网安备 11010802022788号
