准备:建表,插入数据
drop table if exists emp;
create table if not exists emp(
empno int primary key auto_increment,
ename varchar(10) not null,
job varchar(20),
salary double(8,2),
mgr int(10),
bonus double(6,2),
hiredate date,
deptno int(10)
);
insert into emp values(null,'mary','sales',7000,6,1000,'2014-1-1',10);
insert into emp values(null,'lily','sales',6000,1,800,'2014-5-1',10);
insert into emp values(null,'tom','sales',5000,1,4000,'2014-3-1',10);
insert into emp values(null,'james','account',8000,6,null,'2014-2-1',20);
insert into emp values(null,'scott','teaching',8000,6,3000,'2014-1-20',30);
insert into emp values(null,'tom','BOSS',38000,null,null,'2013-1-20',30);
insert into emp values(null,'kitty','teaching',7000,5,700,'2014-5-20',30);
insert into emp values(null,'kitty','teaching',6000,5,500,'2014-6-20',30);
insert into emp values(null,'green','analyst',15000,6,1000,'2014-2-20',40);
insert into emp values(null,'brown','analyst',12000,9,7000,'2014-4-20',40);
insert into emp values(null,'danis','department',3000,6,800,'2014-3-4',50);
insert into emp values(null,'brown','department',1800,11,600,'2014-4-20',50);
insert into emp values(null,'smith','department',1200,11,500,'2014-5-20',50);
#1:查询EMP表的全部职工的EMPNO、ENAME和JOB。
SELECT
empno,ename,job
FROM
emp
#2:查询EMP表的全部职工的EMPNO、ENAME和JOB,按salary升序次序排列。
#按照某个字段进行排序,不需要将其显示,一定要确认查询的表中存在这个字段
SELECT
empno,ename,job
FROM
emp
ORDER
BY
salary
#3:查询EMP表的全部列,列的次序为:JOB,SALARY,ENAME,EMPNO,MGR,HIREDATE,BONUS,DEPTNO,查询结果按年薪降序。
SELECT
job,salary,ename,mgr,hiredate,bonus,deptno
FROM
emp
ORDER
BY
salary
DESC
#4:列出EMP表中的不同的JOB名称
SELECT
job
FROM
emp
GROUP
BY
job
#5:查询在部门10中工作,其工资高于6000的职工信息
SELECT
*
FROM
emp
WHERE
deptno=10
AND
salary>6000
#6:列出其JOB为BOSS或ANALYST的职工名及职工号
SELECT
ename,empno
FROM
emp
WHERE
job=
"BOSS"
OR
job=
"analyst"
#7:列出工资在5500至20000之间的职工名字、职工号。使用两种方式实现
(1)
SELECT
ename,empno
FROM
emp
WHERE
salary
BETWEEN
5500
AND
20000
(2)
SELECT
ename,empno
FROM
emp
WHERE
salary>=5500
AND
salary<=20000
#8:查出工资为6k的人员所在的部门名称和地址。
SELECT
deptno,adrress
FROM
emp
WHERE
salary=6000
#9:查出以W开头或以S结尾的职工名的职工信息。
SELECT
job,salary,ename,mgr,hiredate,bonus,deptno,adrress
FROM
emp
WHERE
ename
LIKE
"w%"
OR
ename
LIKE
"%s"
#10:查出以k开头或以y结尾的职工信息和部门信息。
SELECT
job,salary,ename,mgr,hiredate,bonus,deptno,adrress
FROM
emp
WHERE
ename
LIKE
"k%"
OR
ename
LIKE
"%y"
#11:列出没有奖金的职工信息。
SELECT
*
FROM
emp
WHERE
bonus
IS
NULL
查询奖金为
NULL
的时候要用
IS
NULL
#12:查询工资最高的人的信息以及部门信息
SELECT
*
FROM
emp
ORDER
BY
salary
DESC
LIMIT 1
#13:查询每个部门的平均工资,部门名称,部门编号,并按照平均工资倒序排列
SELECT
deptno,job,
AVG
(salary)
FROM
emp
GROUP
BY
deptno
ORDER
BY
AVG
(salary)
DESC
#14:查询每个部门的平均工资,按照部门编号,部门名称,平均薪资顺序显示,低于5000的不要求显示,并按照平均工资倒序排列
SELECT
deptno,job,
AVG
(salary)
FROM
emp a
GROUP
BY
deptno
HAVING
AVG
(salary)>=5000
ORDER
BY
AVG
(salary)
#15:查询‘lily’的领导信息
SELECT
*
FROM
emp
WHERE
empno
IN
(
SELECT
mgr
FROM
emp
WHERE
ename=
"lily"
)
16:查詢在梵蒂冈办公的人员信息
SELECT
*
FROM
emp
WHERE
adrress=
"梵蒂冈"
#17:列出所有部门的详细信息和部门人数
SELECT
COUNT
(deptno),deptno
FROM
emp
GROUP
BY
deptno
SELECT
COUNT
(1),deptno
FROM
emp
GROUP
BY
deptno
#18:查询出所有薪水在
'james'
之上的所有人员信息,并按照工资降序排列
SELECT
*
FROM
emp
WHERE
salary>(
SELECT
salary
FROM
emp
WHERE
ename=
"james"
)
ORDER
BY
salary
DESC
#19:查询出所有有奖金的人员信息,并按照奖金的多少进行降序排列
SELECT
*
FROM
emp
WHERE
bonus
IS
NOT
NULL
ORDER
BY
bonus
DESC
#20:查出emp表中所有部门的最高薪水和最低薪水,部门编号为10的部门不显示。
(1)
SELECT
deptno,
MAX
(salary),
MIN
(salary)
FROM
emp
WHERE
deptno <> 10
GROUP
BY
deptno
(2)
SELECT
deptno,
MAX
(salary),
MIN
(salary)
FROM
emp
GROUP
BY
deptno
HAVING
deptno <> 10
#21:查询所有mary下属的信息和工作地点,并且按照他们的工资降序排列
SELECT
*
FROM
emp
WHERE
mgr
IN
(
SELECT
empno
FROM
emp
WHERE
ename=
"mary"
)
ORDER
BY
salary
DESC