MySql教程

mysql 练习20道题

本文主要是介绍mysql 练习20道题,对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!

准备:建表,插入数据

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

这篇关于mysql 练习20道题的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!