MySql 简单的查询操作
emp表如下:
dept表如下:
- 查询每个部门下的工作种类。(部门编号deptno + job组合在一起是不重复的。)
select distinct deptno,job from emp1
- 员工转正后,月薪上调500元,请查询出所有员工转正后的月薪
select sal + 500 as 转正后月薪 from emp1
- 查询员工姓名,员工的工资,以及员工的年薪
select ename as 姓名,sal as 工资,sal * 12 as 年薪 from emp1
- 查询员工的年收入
select sal * 12 + ifnull(comm,0) as 年收入 from emp1
- 员工试用期6个月,转正后月薪上调20%,请查询出所有员工工作第一年的年薪所得(不考虑奖金部分,年薪的试用期6个月的月薪+转正后6个月的月薪)
select sal*6+sal*1.2*6 from emp;
- 员工试用期6个月,转正后月薪上调20%,请查询出所有员工工作第一年的所有收入(需考虑奖金部分),要求显示列标题为员工姓名,工资收入,奖金收入,总收入
select
ename 员工姓名,
sal*6+sal*1.2*6 工资收入,
ifnull(comm,0)*12奖金收入,
sal*6+sal*1.2*6+ ifnull(comm,0)*12 总收入
from emp;
- 查询所有经理的编号
select mgr 经理编号 from emp1 where job = "manager"
- 查询职位为SALESMAN的员工编号、职位、入职日期
select empno 员工编号,job 职位,hiredate 入职日期 from emp1 where job = "SALESMAN"
- 查询1985年12月31日之前入职的员工姓名及入职日期
select ename 员工姓名,hiredate 入职日期 from emp1 where hiredate < "1985-12-31"
- 查询部门编号不在10部门的员工姓名、部门编号
select ename 员工姓名,deptno 部门编号 from emp1 where deptno != 10
- 查询20,30部门的员工
select * from emp1 where deptno in(20,30)
- 查询job 为 CLERK|SALESMAN|MANAGER 的员工
select * from emp1 where job in('CLERK','SALESMAN','MANAGER')
- 查询除了10、20号部门以外的员工姓名、部门编号
select ename 员工姓名,deptno 部门编号 from emp1 where deptno not in (10,20)
- 查询名字以 "S"开头的员工(S后面可以有0个字符,也可以有多个字符)
select * from emp1 where ename like 's%'
- 查询名字中包含 "S"的员工
select * from emp1 where ename like '%s%'
- 查询员工名字第二个字符是"O"的员工
select * from emp1 where ename like '_o%'
- 查询名字中带"_"的员工
select * from emp1 where ename like '%\_%'
- 查询名字中不包括’W’的员工
select * from emp1 where ename not like '%w%'
- 查询部门编号为10或者20的员工姓名,部门编号
select ename 员工姓名,deptno 部门编号 from emp1 where deptno = 10 or deptno = 20
- 查询经理编号为7902, 7566, 7788的员工姓名,经理编号
select ename 员工姓名,mgr 经理编号 from emp1 where mgr = 7902 or mgr = 7566 or mgr = 7788
- 查询20部门,并且job是’CLERK’的员工
select * from emp1 where deptno = 20 and job = "clerk"
- 查询20,或 30部门的员工
select * from emp1 where deptno = 20 or deptno = 30
- 查询工资既不等于2000也不等于3000的员工
select * from emp1 where sal != 2000 and sal != 3000
- 工资在[3000,5000] 范围内的员工
select * from emp1 where sal + ifnull(comm,0) between 3000 and 5000
- 收入不在在3000到5000的员工 not between … and …
select * from emp1 where sal not between 3000 and 5000
- 查询月薪在3000到5000的员工姓名,月薪
select * from emp1 where sal between 3000 and 5000
- 查询工资不在 2000~3000内的员工
select * from emp1 where sal not between 2000 and 3000
- 按照员工的部门编号升序(asc可省略)排
select * from emp1 order by deptno asc
- 按照员工的部门编号降序(desc)排
select * from emp1 order by deptno desc
- 查询员工信息,按照部门编号升序排,在部门内部按照工资升序排
select * from emp1 order by deptno,sal
- 查询员工信息,按照部门编号降序排,在部门内部按照工资升序排
select * from emp1 order by deptno desc,sal asc
- 根据部门编号统计平均工资。
select deptno as 部门编号,avg(sal) as 平均工资
from emp1
group by deptno
- 统计各部门下不同职位的平均工资
select job as 职位,avg(sal) as 平均工资
from emp1
group by job
- 求员工数大于2的部门编号和部门人数
select deptno as 部门编号,count(*) as 部门人数
from emp1
group by deptno
having count(*) < 2
- 查询每个部门最高工资大于2900的部门编号,最高工资
select deptno as 部门编号,max(sal) as 最高工资
from emp1
group by deptno
having max(sal) > 2900
- 求各部门工作不是SALESMAN的员工的工资总和,要求统计工资和大于1000
select deptno as 部门编号,sum(sal) as 工资总和
from emp1
where job != "salesman"
group by deptno
having sum(sal) > 1000
- 求各部门中工资大于2000的员工的部门编号,平均工资
select deptno as 部门编号,avg(sal) as 平均工资
from emp1
where sal > 2000
group by deptno
select age,count(*)
from stu
where sex='男'
group by age
having count(*) > 3
- 查询入职日期最早的前5名员工姓名,入职日期
select ename as 员工姓名 ,hiredate as 入职日期
from emp1
order by hiredate asc
limit 5
- 查询工作在30号部门并且入职日期最早的前2名员工姓名,入职日期
select ename as 员工姓名 ,hiredate as 入职日期
from emp1
where deptno = 30
order by hiredate asc
limit 2
- 查询奖金为null的员工
select *
from emp1
where comm is null
- 查询奖金不为null的员工
select *
from emp1
where comm is not null
- 写一个查询,分别计算100.456 四舍五入到小数点后第2位,第1位,整数位的值
select round(100.456,2),round(100.456,1),round(100.456,0) from emp1
- 写一个查询,分别计算100.456 从小数点后第2位,第1位,整数位截断的值
select truncate(100.456,2),truncate(100.456,1),truncate(100.456,0) from emp1
- 求I love china的长度
select length("i love china")
- 把”I”, “love” “China”拼接在一起
select concat("i","love","China")
- 把ename , “_”, job 拼接在一起
select concat_ws("_",ename,job) from emp1
- 把员工的名字都转成小写
select lower(ename) from emp1
- 把”5000”有“*”左填充到8位,****5000
select lpad("5000",8,"*")
- “ abc def ”去左右空格
select trim(" abc def ")
- 截取”I love china” 的前三位字符
select substr("i love chine",1,3)
- 求系统当前时间
select sysdate() as 系统当前时间;
- 查询2020-1-18到现在的日期差
select datediff(curdate(),"2020-01-18") as 日期差;
- 查询3月以前的日期
select DATE_SUB(SYSDATE(),interval 3 month) as 三个月以前的日期;
- 查询当前日期的年月日时分秒
select TIME(SYSDATE()) as 时分秒,day(SYSDATE()) as 日,month(SYSDATE()) as 月,year(sysdate()) as 年;
- 查询当前日期在一年,一月一周中是第几天
select DAYOFYEAR(NOW()),DAYOFMONTH(NOW()), DAYOFWEEK(NOW());
- 日期格式化 %Y-%m-%d %H:%i:%m Y: 4位年y:2位年 H:24小时 h:12小时
select date_format(SYSDATE(),'%Y-%m-%d %H:%i:%m') as 格式化后的日期;
- 查询服务器当前时间
select NOW() as 服务器时间;
- 查询部门10,20的员工截止到2000年1月1日,工作了多少个月,入职的月份
select datediff('2000-01-01',hiredate),
MONTH(hiredate) as 入职月份
from emp1
where deptno in(10,20)
- 如果员工试用期6个月,查询职位不是MANAGER的员工姓名,入职日期,转正日期,入职日期是第多少月,第多少周
select
ename as 员工姓名,
hiredate as 入职时间,
DATE_ADD(hiredate,INTERVAL 6 month) as 转正日期,
EXTRACT(month FROM hiredate) as 入职第多少个月,
EXTRACT(week FROM hiredate) as 入职第多少个周
from emp1
where job != "manager"
- 写一个查询,用首字母大写,其它字母小写显示雇员的 ename,显示名字的长度,并给每列一个适当的标签,条件是满足所有雇员名字的开始字母是J、A 或 M 的雇员,并对查询结果按雇员的ename升序排序。(提示:使用length、substring)
select
concat(upper(left(ename,1)),lower(right(ename,length(ename) - 1))) as 首字母大写的员工姓名,
length(ename) as 名字长度
from emp1
where ename like 'J%' or ename like'A%' or ename like 'M%'
order by ename asc
- 查询员工姓名中中包含大写或小写字母A的员工姓名
select ename
from emp1
where ename like '%a%'
- 显示所有员工的姓名,用a替换所有"A"
select replace(ename,"A","a") from emp1
- 查询部门编号为10或20,入职日期在81年5月1日之后,并且姓名中包含大写字母A的员工姓名,员工姓名长度
select
ename as 员工姓名,
length(ename)
from emp1
where deptno in(10,20) and
ename like '%A%' and
hiredate > "1981-05-01"
- 查询每个职工的编号,姓名,工资。要求将查询到的数据按照一定的格式合并成一个字符串
前10位:编号,不足部分用填充,左对齐
中间10位:姓名,不足部分用填充,左对齐
后10位:工资,不足部分用*填充,右对齐
select
concat(rpad(empno,10,'*'),rpad(ename,10,'*'),lpad(sal,10,'*'))
from emp1
- 查询(工资超过2000并且职位是MANAGER),或(职位是SALESMAN)的员工姓名、职位、工资
select
ename as 姓名,
job as 职位,
sal as 工资
from emp1
where
(sal > 2000 and job = "manager") or (job = "salesman")
- 查询工资超过2000并且职位是 MANAGER或SALESMAN的员工姓名、职位、工资
select
ename as 姓名,
job as 职位,
sal as 工资
from emp1
where
sal > 2000 and (job in("manager","salesman"))
- 查询(部门在10或者20),并且(工资在3000到5000)之间的员工姓名、部门、工资
select
ename as 姓名,
job as 职位,
sal as 工资
from emp1
where
deptno in (10,20) and (sal between 3000 and 5000)
- 查询入职日期在81年后,并且职位不是SALES开头的员工姓名、入职日期、职位
select
ename as 姓名,
job as 职位,
hiredate as 入职日期
from emp1
where hiredate > "1981-12-31" and job not like 'SALES%'
- 查询职位为SALESMAN或MANAGER,部门编号为10或者20,姓名包含A的员工姓名、职位、部门编号
select
ename as 姓名,
job as 职位,
deptno as 部门编号
from emp1
where (job in('salesman','manager')) and (deptno in (10,20)) and ename like '%A%'
- 查询部门在20或30的员工姓名,部门编号,并按照工资升序排序
select
ename as 姓名,
deptno as 部门编号
from emp1
where deptno in (20,30)
order by sal asc
- 查询工资在2000-3000之间,部门不在10号的员工姓名,部门编号,工资,并按照部门升序,工资降序排序
select
ename as 姓名,
deptno as 部门编号,
sal as 工资
from emp1
where sal between 2000 and 3000 and deptno != 10
order by deptno asc,sal desc
- 查询入职日期在82年至83年之间,职位以SALES或者MAN开头的员工姓名,入职日期,职位,并按照入职日期降序排序
select
ename as 姓名,
hiredate as 入职时间,
job as 职位
from emp1
where hiredate between "1982-12-31" and "1983-12-31" and job like 'SALES%' or job like 'MAN%'
order by hiredate desc
- 按照每页显示5条记录,分别查询第1页,第2页,第3页信息,要求显示员工姓名、入职日期、部门编号
select
ename as 姓名,
hiredate as 入职日期,
deptno as 部门编号
from emp1
limit 0,5
select
ename as 姓名,
hiredate as 入职日期,
deptno as 部门编号
from emp1
limit 5,10
select
ename as 姓名,
hiredate as 入职日期,
deptno as 部门编号
from emp1
limit 10,15
- 查询入职时间在1982-7-9之后,且不从事SALESMAN工作的员工姓名、入职时间、职位
select
ename as 姓名,
hiredate as 入职日期,
job as 职位
from emp1
where hiredate > "1982-07-09" and job !="salesman"
- 查询员工姓名的第三个字母是A的员工姓名
select
ename as 姓名
from emp1
where ename like '__A%'
---------------------------
select
ename as 姓名
from emp1
where substring(ename,3,1) = 'A'
- 查询部门号为30号员工的信息,先按工资降序排序,再按姓名升序排序
select *
from emp1
where deptno = 30
order by sal desc,ename asc
- 查询没有上级的员工(经理号为空)的员工姓名
select
ename as 姓名
from emp1
where mgr is null
- 查询工资大于等于4500并且部门为10或者20的员工的姓名、工资、部门编号
select
ename as 姓名,
sal as 工资,
deptno as 部门编号
from emp1
where sal >= 4500 and deptno in(10,20)
- 求各部门中职位是salesman 的最高工资
select
deptno,max(sal)
from emp1
where job = "salesman"
group by deptno
- 求81年以后入职的不同岗位的最低工资
select
job,min(sal)
from emp1
where hiredate > "1981-12-31"
group by job
- 平均工资大于2000的部门编号和平均工资
select
deptno,avg(sal)
from emp1
group by deptno
having avg(sal) > 2000
- 查询部门20的员工,每个月的工资总和及平均工资
select
deptno,
sum(sal),
avg(sal)
from emp1
where deptno = 20
group by deptno
- 查询部门20的员工,每个月的收入总和与平均收入
select
deptno as 部门编号,
sum(sal + ifnull(comm,0)) as 收入总和,
avg(sal + ifnull(comm,0)) as 平均收入
from emp1
where deptno = 20
group by deptno
- 查询工作在30的员工人数,最高工资及最低工资
select
deptno,count(*),
max(sal),
min(sal)
from emp1
where deptno = 30
group by deptno
- 查询员工表中一共有几种岗位类型
select
count(distinct job) as 岗位类型
from emp1
- 查询员工工资小于2000显示高新,不然显示一般
select
sal,if(sal > 2000,"高薪","一般")
from emp1
- 如果为NULL显示空值
select
ifnull(comm,"空值")
from emp1
select
ename,job,
case job
when 'clerk' then '保洁'
when 'manager' then '管理员'
when 'presdent' then '高管'
else '销售'
end
from emp1
多表查询
- 写一个查询,显示所有员工姓名,部门编号,部门名称
select
ename,dname,e.deptno,d.deptno
from emp1 e,dept1 d
where e.deptno = d.deptno
- 写一个查询,显示所有工作在CHICAGO并且奖金不为空的员工姓名,工作地点,奖金
select
ename as 员工姓名,d.loc as 工作地点,ifnull(comm,0)
from emp1 e,dept1 d
where e.deptno = d.deptno and d.loc = "chicago" and comm is not null
- 写一个查询,显示所有姓名中含有A字符的员工姓名,工作地点
select
ename as 员工姓名,d.loc as 工作地点
from emp1 e,dept1 d
where e.deptno = d.deptno and ename like '%a%'
- 写一个查询,显示所有姓名中含有A字符的员工姓名,工作地点
select ename , loc
from emp1 e inner join dept1 d
on e.deptno = d.deptno
where ename like '%a%'
- 建表操作
create table if not exists salgrade(
grade int primary key auto_increment ,
losal int,
hisal int
);
insert into salgrade values (1, 700, 1200);
insert into salgrade values (2, 1201, 1400);
insert into salgrade values (3, 1401, 2000);
insert into salgrade values (4, 2001, 3000);
insert into salgrade values (5, 3001, 9999);
- 非等值连接表
-- sql92
select *
from emp1 e,salgrade s
where e.sal between s.losal and s.hisal
-- sql99
select *
from emp1 e inner join salgrade s
on e.sal and s.hisal
- 查询学员和班级信息,要求显示所有学生信息
select *
from t_stu t_s left join t_class t_c
on t_s.c_id = t_c.c_id
- 统计每个班级人数
select c_name,count(s_id)
from t_class left join t_stu
on t_stu.c_id = t_class.c_id
group by c_name
- 获取没有员工的部门
select
d.deptno
from dept1 d left join emp1 e
on d.deptno = e.deptno
group by empno
having count(empno) = 0;
select
e.*
from dept1 d right join emp1 e
on d.deptno = e.deptno
group by empno
having count(e.deptno) = 0;
- 没有部门员工
select e.*
from emp1 e left join dept1 d
on d.deptno = e.deptno
where e.deptno is null
- 查找员工姓名以及员工直接领导姓名
select e.ename,l.ename
from emp1 e inner join emp1 l
on e.empno = l.mgr
- 查询所有工作在NEW YORK和CHICAGO的员工姓名,员工编号,以及他们的经理姓名,经理编号
select
e.ename as 员工姓名,
e.empno as 员工编号,
l.ename as 经理姓名,
l.empno as 经理编号
from emp1 e inner join dept1 d on e.deptno = d.deptno
inner join emp1 l on e.mgr = l.empno
where d.loc in ('new york','chicago');
下面三个题的参考表:
- 查询BaiBaiHe的高数成绩
select
s.degree
from Student s inner join Score sc on s.sno = sc.sno
inner join course c on sc.cno = c.cno
where s.sname = "baibaihe" and c.cname = "高数"
- 黄磊老师任课的考试成绩
select
s.degree
from Teacher t inner join Course c on t.tno = c.tno
inner join Score s on c.cno = s.cno
where t.tname = "黄磊老师"
- 成绩大等于80分的所有学生姓名,课程名称和任课老师的名字
select
s.sname,
c.cname,
t.tname
from Student s inner join Score sc on s.sno = sc.sno
inner join course c on sc.cno = c.cno
inner join teacher t on c.tno = t.tno
where degree > 80;
- 显示员工SMITH的姓名,部门名称,直接上级名称
select
e.ename,
d.dname,
l.ename
from emp1 e inner join dept1 d on e.deptno = d.deptno
inner join emp1 l on e.mgr = l.empno
where e.ename = "Smith"
- 显示员工姓名,部门名称,工资,工资级别,要求工资级别大于4级。
select
e.ename,
d.dname,
sal,
s.grade
from emp1 e inner join salgrade s on sal between s.losal and s.hisal
inner join dept1 d on e.deptno = d.deptno
where s.grade > 4
- 显示员工KING和FORD管理的员工姓名及其经理姓名。
select
e.ename as 员工,
l.ename as 被管理的员工,
k.ename as 经理姓名
from emp1 e inner join emp1 l on e.empno = l.mgr
left join emp1 k on e.mgr = k.empno
where e.ename in ("king","ford");
- 显示员工姓名,参加工作时间,经理名,参加工作时间,要求参加时间比经理早。
select
e.ename,
e.hiredate,
l.ename,
l.hiredate
from emp1 e inner join emp1 l on e.mgr = l.empno
where e.hiredate < l.hiredate
- 写一个查询,显示所有员工姓名,部门编号,部门名称。
select
e.ename,
e.deptno,
d.dname
from emp1 e left join dept1 d on e.deptno = d.deptno
- 写一个查询,显示所有工作在CHICAGO并且奖金不为空的员工姓名,工作地点,奖金
select
e.ename,
d.loc,
e.comm
from emp1 e inner join dept1 d on e.deptno = d.deptno
where d.loc = "chicago" and comm is not null
- 写一个查询,显示所有姓名中含有A字符的员工姓名,工作地点。
select
e.ename,
d.loc
from emp1 e inner join dept1 d on e.deptno = d.deptno
where e.ename like '%a%'
- 找出早于12年前受雇的员工.
select *
from emp1
where hiredate < DATE_SUB(SYSDATE(),INTERVAL 12 YEAR)
- 以首字母大写的方式显示所有员工的姓名.
select
concat(upper(left(ename,1)),lower(right(ename,length(ename) - 1)))
from emp1
- 显示正好为5个字符的员工的姓名.
select *
from emp1
where length(ename) = 5
- 显示不带有"R"的员工的姓名.
select
ename
from emp1
where ename not like '%r%'
- 显示所有员工姓名的前三个字符.
select
left(ename,3)
from emp1
- 显示所有员工的姓名,用a替换所有"A"
select
replace(ename,'a','A')
from emp1
- 显示满10年服务年限的员工的姓名和受雇日期.
select
ename,
hiredate
from emp1
where floor(DATEDIFF(SYSDATE(),hiredate) / 365) = 10
- 显示员工的详细资料,按姓名排序.
select *
from emp1 e left join dept1 d on e.deptno = d.deptno
order by e.ename asc
- 显示员工的姓名和受雇日期,根据其服务年限,将最老的员工排在最前面.
select
ename,
hiredate
from emp1
order by hiredate asc
- 显示所有员工的姓名、工作和薪金,按工作的降序排序,若工作相同则按薪金排序
select
ename,
job,
sal
from emp1
order by job desc ,sal desc
- 显示所有员工的姓名、加入公司的年份和月份,按受雇日期所在月排序,若月份相同则将最早年份的员工排在最前面.
select
ename,
EXTRACT(year FROM hiredate),
EXTRACT(month FROM hiredate)
from emp1
order by EXTRACT(month FROM hiredate) asc ,EXTRACT(year FROM hiredate) asc
- 显示在一个月为30天的情况所有员工的日薪金,忽略余数
select
truncate(sal / 30,0)
from emp1
- 找出在(任何年份的)2月受聘的所有员工
select *
from emp1
where EXTRACT(month FROM hiredate) = 2
- 对于每个员工,显示其加入公司的天数.
select
DATEDIFF(SYSDATE(),hiredate)
from emp1
- 显示姓名字段的任何位置包含"A"的所有员工的姓名.
select
ename
from emp1
where ename like '%a%'
- 以年月日的方式显示所有员工的服务年限. (大概) 一年365天,一月30天
select
floor(DATEDIFF(SYSDATE(),hiredate) / 365),
floor(DATEDIFF(SYSDATE(),hiredate) / 30),
floor(DATEDIFF(SYSDATE(),hiredate))
from emp1
- 工作在chicago地点的所有员工
select
*
from emp1
where deptno = (select deptno from dept1 where loc = "chicago")
- 查找部门平均工资大于部门10的平均工资
select
deptno,avg(sal)
from
emp1
group by deptno
having avg(sal) > (select avg(sal) from emp1 where deptno = 10)
- 1.查询入职日期最早的员工姓名,入职日期
select
ename 员工姓名,
hiredate 入职日期
from emp1
where hiredate = (select min(hiredate) from emp1)
- 2.查询工资比SMITH工资高并且工作地点在CHICAGO的员工姓名,工资,部门名称
select
ename 员工姓名,
sal 工资,
dname 部门名称
from emp1 e inner join dept1 d on e.deptno = d.deptno
where (sal > (select sal from emp1 where ename = "smith"))
and (loc = (select loc from dept1 where loc = "chicago"))
- 3.查询入职日期比20部门入职日期最早的员工还要早的员工姓名,入职日期
select
hiredate 入职日期,
ename 员工姓名
from emp1
group by deptno
having deptno = 20 and hiredate < min(hiredate)
- 4.查询部门人数大于所有部门平均人数的的部门编号,部门名称,部门人数
select
e.deptno 部门编号,
dname 部门名称,
count(*) 部门总人数
from emp1 e inner join dept1 d on e.deptno = d.deptno
group by e.deptno
having count(e.ename) > (select count(deptno) from emp1) / (select count(deptno) from dept1)
- 查询入职日期比10部门任意一个员工早的员工姓名、入职日期,不包括10部门员工
select
ename 员工姓名,
hiredate 入职时间
from emp1
where hiredate < any(select hiredate from emp1 where deptno = 10);
- 查询入职日期比10部门任意一个员工晚的员工姓名、入职日期,不包括10部门员工
select
ename 员工姓名,
hiredate 入职日期
from emp1
where hiredate > all(select hiredate from emp1 where deptno = 10) and deptno != 10;
- 查询职位和10部门任意一个员工职位相同的员工姓名,职位,不包括10部门员工
select
ename 员工姓名,
job 职位
from emp1
where job = any(select job from emp1 where deptno = 10) and deptno != 10;
- 查询含有下属的领导
select
*
from emp1
where empno in(select distinct mgr from emp1 )
- 每个部门的平均工资
select
deptno 部门编号,
avg(sal) 平均工资
from emp1
group by deptno
- 查询每个部门的编号,名称,平均工资
select
e.deptno 部门编号,
dname 部门名称,
avg(sal) 平均工资
from emp1 e inner join dept1 d on e.deptno = d.deptno
group by e.deptno
- 查询每个部门每个岗位的工资总和。
select
d.deptno,
dname,
job,sum(sal)
from emp e,dept d where e.deptno=d.deptno
group by d.deptno,dname,job;
- 求各部门中工资大于2000的员工的部门编号,部门名称,平均工资
select
e.deptno 部门编号,
dname 部门名称,
avg(sal)
from emp1 e inner join dept1 d on e.deptno = d.deptno
where sal > 2000
group by e.deptno
- 求各部门中职位是salesman 的最高工资
select
deptno 部门编号,
max(sal)
from emp1
where job = "salesman"
group by deptno
- 求81年以后入职的不同岗位的最低工资
select
job,
min(sal)
from emp1
where hiredate > "1981-12-31"
group by job
- 查询每个部门最高工资大于2900的部门编号,最高工资
select
deptno,
max(sal)
from emp
group by deptno
having max(sal)>2900;
- 求各部门工作不是SALESMAN的员工的工资总和,要求统计工资和大于1000
select
deptno,
sum(sal)
from emp1
where job != "salesman"
group by deptno
having sum(sal) > 1000
- 求各部门中工资大于2000的员工的部门编号,部门名称,平均工资
select
d.deptno,
dname,
ifnull(avgsal,0)
from dept1 d
left join (select
deptno,
avg(sal) avgsal
from emp1
where sal > 2000 and deptno is not null
group by deptno) t on d.deptno = t.deptno
- 求员工数大于2的部门编号,部门名称和部门人数
select
count(*) 部门人数,
e.deptno,
dname
from emp1 e inner join dept1 d on e.deptno = d.deptno
group by e.deptno
having count(*) > 2
- 查询每个部门的部门编号,部门名称,部门人数,最高工资,最低工资,工资总和,平均工资
select
e.deptno 部门编号,
dname 部门名称,
count(*) 部门人数,
max(sal) 最高工资,
min(sal) 最低工资,
sum(sal) 工资总和,
avg(sal) 平均工资
from emp1 e
inner join dept1 d
on e.deptno = d.deptno
group by e.deptno
- 查询每个部门,每个岗位的部门编号,部门名称,岗位名称,部门人数,最高工资,最低工资,工资总和,平均工资
select
e.deptno 部门编号,
job,
dname 部门名称,
count(*) 部门人数,
max(sal) 最高工资,
min(sal) 最低工资,
sum(sal) 工资总和,
avg(sal) 平均工资
from emp1 e
inner join dept1 d
on e.deptno = d.deptno
group by e.deptno,job
- 查询部门平均工资大于2000,且人数大于2的部门编号,部门名称,部门人数,部门平均工资,并按照部门人数升序排序
select
e.deptno 部门编号,
dname 部门名称,
count(*) 部门人数,
avg(sal) 平均工资
from emp1 e
inner join dept1 d
on e.deptno = d.deptno
group by e.deptno
having count(*) > 2 and avg(sal) > 2000
order by count(*) asc
- 查找没有学生的班级信息
- 学生,班级,两个表查询。班级信息都保留
select
c.*
from t_class c left join t_stu s on c.c_id = s.c_id
where s.s_id is null
- 查找没有班级的学生信息。
select
s.*
from t_class c right join t_stu s on c.c_id = s.c_id
where c.c_id is null
- 查询每个班级的人数
select
c_id,
count(*)
from t_stu
group by c_id
- 查询班级人数大于2的班级和人数
select
c_id,
count(*)
from t_stu
where c_id is not null
group by c_id
having count(*) > 2
- 查询班级平均年龄,要求平均年龄在20岁以上。
select
c_id,
avg(s_age)
from t_stu
where c_id is not null
group by c_id
having avg(s_age) > 20
- 查询员工以及员工所在的部门
select
*
from emp1 e inner join dept1 d on e.deptno = d.deptno;
- 求工作地点在DALLAS的员工信息和部门名称。
select
e.*,
dname
from emp1 e inner join dept1 d on e.deptno = d.deptno
where loc = "dallas"
- 查询工资大于3000的员工和他所在的部门名称。
select
e.*,
dname
from emp1 e inner join dept1 d on e.deptno = d.deptno
where sal > 3000
- 查询在RESEARCH部门工作的员工信息
select
*
from emp1 e inner join dept1 d on e.deptno = d.deptno
where dname = "research"
- 查询工作种类是 MANAGER的员工编号,员工姓名和部门信息。
select
e.empno,
e.ename,
d.*
from emp1 e inner join dept1 d on e.deptno = d.deptno
where job = "manager"
- 选择部门30中的所有员工.
select
*
from emp1
where deptno = 30
- 列出所有办事员(CLERK)的姓名,编号和部门编号
select ename,empno,deptno
from emp1
where job = "clerk"
- 找出佣金(comm)高于薪金的员工.
select *
from emp1
where ifnull(comm,0) > sal
- 找出佣金高于薪金的60%的员工.
select *
from emp1
where ifnull(comm,0) > sal * 0.6
- 找出部门10中所有经理(MANAGER)和部门20中所有办事员(CLERK)的详细资料.
select *
from emp1
where (deptno = 10 and job = "manager") or (deptno = 20 and job = "clerk")
- 找出部门10中所有经理(MANAGER), 部门20中所有办事员(CLERK), 既不是经理又不是办事员但其薪金大于或等于2000的所有员工的详细资料.
select *
from emp1
where (deptno = 10 and job = "manager") or (deptno = 20 and job = "clerk") or (job != "manager" and sal >=2000)
- 找出收取佣金的员工的不同工作.
select
job
from emp1
where comm is not null and comm != 0
- 找出不收取佣金或收取的佣金低于100的员工.
select
*
from emp1
where comm is null and comm != 0 or ifnull(comm,0) < 100
- 查询部门平均工资在2500元以上的部门名称及平均工资。
select
dname,
avg(sal)
from emp1 e inner join dept1 d on e.deptno = d.deptno
group by e.deptno
having avg(sal) > 2500
- 查询员工岗位中不是以“SA”开头并且平均工资在2500元以上的岗位及平均工资,并按平均工资降序排序。
select
job,
avg(sal)
from emp1
where job not like 'SA%'
group by job
having avg(sal) > 2500
- 查询部门人数在2人以上的部门名称、最低工资、最高工资,并对求得的工资进行四舍五入到整数位。
select
count(*),
dname,
round(min(sal)),
round(max(sal))
from emp1 e inner join dept1 d on e.deptno = d.deptno
group by e.deptno
having count(*) > 2
- 查询岗位不为SALESMAN,工资和大于等于2500的岗位及每种岗位的工资和。
select
job,
sum(sal)
from emp1
where job != "salesman"
group by job
having sum(sal) >= 2500
- 显示经理号码和经理姓名,这个经理所管理员工的最低工资,没有经理的KING也要显示,不包括最低工资小于3000的,按最低工资由高到低排序。
select
e.mgr,
e.ename,
min(l.sal)
from emp1 e inner join emp1 l on e.empno = l.mgr
group by e.empno
having min(l.sal) < 3000
order by min(l.sal) asc
- 查询工资高于编号为7782的员工工资,并且和7369号员工从事相同工作的员工的编号、姓名及工资。
select
empno,
ename,
sal
from emp1
where sal > (select sal from emp1 where empno = 7782) and job =(select job from emp1 where empno = 7369)
- 查询工资最高的员工姓名和工资。
select
ename,
sal
from emp1
where sal = (select max(sal) from emp1)
- 查询部门最低工资高于10号部门最低工资的部门的编号、名称及部门最低工资。
select
e.deptno,
dname,
min(sal)
from emp1 e inner join dept1 d on e.deptno = d.deptno
where (select min(sal)from emp1) > (select min(sal) from emp1 e where e.deptno = 10 group by e.deptno)
group by e.deptno
- 查询员工工资为其部门最低工资的员工的编号和姓名及工资。
select
empno,
ename,
sal
from emp1
where sal in(select min(sal) from emp1 group by deptno)
- 显示经理是KING的员工姓名,工资。
select
ename,
sal
from emp1
where mgr = (select empno from emp1 where ename = "king")
- 显示比员工SMITH参加工作时间晚的员工姓名,工资,参加工作时间。
select
ename,
sal,
hiredate
from emp1
where hiredate < (select hiredate from emp1 where ename = "smith")
- 使用子查询的方式查询哪些职员在NEW YORK工作。
select
*
from emp1
where deptno in (select deptno from dept1 where loc = "new york")
- 写一个查询显示和员工SMITH工作在同一个部门的员工姓名,雇用日期,查询结果中排除SMITH。
select
ename,
hiredate
from emp1
where deptno in (select deptno from emp1 where ename = "smith") and ename != "smith"
- 写一个查询显示其工资比全体职员平均工资高的员工编号、姓名。
select
empno,
ename
from emp1
where sal < (select avg(sal) from emp1)
- 显示部门名称和人数
select
dname,
count(*)
from emp1 e inner join dept1 d on e.deptno = d.deptno
group by e.deptno
- 显示每个部门的最高工资的员工
select
deptno,
max(sal)
from emp1
group by deptno
- 显示出和员工号7369部门相同的员工姓名,工资
select
ename,
sal
from emp1
where deptno = (select deptno from emp1 where empno = 7369)
- 显示出和姓名中包含“W”的员工相同部门的员工姓名
select
ename,
sal
from emp1
where deptno = (select deptno from emp1 where ename like '%w%')
- 查找出部门10和部门20中,工资最高的员工的员工名字,部门名字,部门位置
select
dname,
ename,
loc,
sal
from emp1 e inner join dept1 d on e.deptno = d.deptno
where sal in (select max(sal) from emp1 where deptno in (10,20) group by deptno)
- 找出部门人数最多的部门中的第二到第五个人
select count(*)
from emp1
group by deptno
order by count(*) asc
select
*
from (select cdcdsds ro,x.* from( select count(*),deptno from emp1 group by deptno order by count(*) desc)) x
where) x) between 2 and 5
- 分组统计各部门下工资>500的员工的平均工资
select
avg(sal)
from emp1
where sal > 500
group by deptno
- 算出部门30中得到最多奖金的员工奖金
select
max(ifnull(comm,0))
from emp1
where deptno = 30
- 算出每个职位的员工数和最低工资
select
job,
count(*),
min(sal)
from emp1
group by job
- 列出员工表中每个部门的员工数(员工数必须大于3),和部门名称
select
count(*),
dname
from emp1 e inner join dept1 d on e.deptno = d.deptno
group by e.deptno
having count(*) > 3
- 列出所有员工的姓名和其上级的姓名
select
e.ename,
l.ename
from emp1 e inner join emp1 l on e.mgr = l.empno
- 得到平均工资大于2000的工作职种
select
job
from emp1
group by job
having avg(sal) > 2000
- 按部门统计员工数,员工人数越来越少排列,查出的第二名到第五名部门信息(列出部门名字,部门位置)
select
d.*
from emp1 e right join dept1 d on e.deptno = d.deptno
group by e.deptno
order by count(*) desc
limit 1,4
*** 复制emp表为empdemo,(数据和emp表一致)***
- 修改1001的工资为2500 ,部门编号为20
update table empdemo set sal = 2500 , deptno = 20 where empno = 1001
- 修改李四的入职时间为今天的日期
update table empdemo set hiredate = CURDATE() where ename = "李四"
- 修改王五的领导编号为1001 , 工资为2000
update table empdemo set mgr = 1001,sal = 1000 where ename = "王五"
- 把部门30所有人的工资提高30%
update table empdemo set sal * 1.3 where deptno = 30
- 删除员工编号是1001的员工信息
delete from empdemo where empno = 1001
- 删除姓名是王五的信息
delete from empdemo where ename = "王五"
- 删除部门编号是20的所有人的信息
delete from empdemo where deptno = 20
- 清除empdemo的所有数据
delete from empdemo
truncate from empdemo