MySQL查询练习
作业1:
//创建表DEPT
mysql> CREATE TABLE DEPT(
-> DEPTNO int (2) primary key,DNAME varchar(14),LOC varchar(13));
//创建表EMP
create table EMP( EMPNO int(4) primary key, ENAME varchar(10), JOB varchar(9), MGR int(4), HIREDATE date, SAL int(7), COMM int(7), DEPTNO int(2) references DEPT(DEPTNO)) default charset='utf8mb4';
//向DEPT表添加数据:
mysql> INSERT INTO DEPT VALUES
-> (10,'ACCOUNTING','NEW YORK');
Query OK, 1 row affected (0.01 sec)
mysql> INSERT INTO DEPT VALUES (20,'RESEARCH','DALLAS');
Query OK, 1 row affected (0.01 sec)
mysql> INSERT INTO DEPT VALUES
-> (30,'SALES','CHICAGO');
Query OK, 1 row affected (0.01 sec)
mysql> INSERT INTO DEPT VALUES
-> (40,'OPERATIONS','BOSTON');
Query OK, 1 row affected (0.01 sec)
//向表EMP添加数据:
mysql> INSERT INTO EMP VALUES (7369,'SMITH','CLERK',7902,'1980--12-17',800,NULL,20);
mysql> INSERT INTO EMP VALUES (7499,'ALLEN','SALESMAN',7689,'1981-2-20',1600,300,30);
mysql> INSERT INTO EMP VALUES
-> (7521,'WARD','SALESMAN',7698,'1981-2-22',1250,500,30);
Query OK, 1 row affected (0.01 sec)
mysql> INSERT INTO EMP VALUES
-> (7566,'JONES','MANAGER',7839,'1981-4-2',2975,NULL,20);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO EMP VALUES
-> (7654,'MARTIN','SALESMAN',7698,'1981-9-28',1250,1400,30);
Query OK, 1 row affected (0.01 sec)
mysql> INSERT INTO EMP VALUES (7698,'BLAKE','MANAGER',7839,'1981-5-1',2850,NULL,30);
Query OK, 1 row affected (0.01 sec)
mysql> INSERT INTO EMP VALUES
-> (7782,'CLARK','MANAGER',7839,'1981-6-9',2450,NULL,10);
Query OK, 1 row affected (0.01 sec)
mysql> INSERT INTO EMP VALUES
-> (7788,'SCOTT','ANALYST',7566,'1987-7-13',3000,NULL,20);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO EMP VALUES
-> (7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO EMP VALUES
-> (7844,'TURNER','SALESMAN',7698,'1981-9-8',1500,0,30);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO EMP VALUES
-> (7876,'ADAMS','CLERK',7788,'1987-7-13',1100,NULL,20);
Query OK, 1 row affected (0.01 sec)
mysql> INSERT INTO EMP VALUES
-> (7900,'JAMES','CLERK',7698,'1981-12-3',950,NULL,30);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO EMP VALUES (7902,'FORD','ANALYST',7566,'1981-12-3',3000,NULL,20);
Query OK, 1 row affected (0.01 sec)
mysql> INSERT INTO EMP VALUES
-> (7934,'MILLER','CLERK',7782,'1982-1-23',1300,NULL,10);
Query OK, 1 row affected (0.01 sec)
//创建BONUS表:
mysql> CREATE TABLE BONUS(
-> ENAME varchar(10),
-> JOB varchar(9),
-> SAL int,
-> COMM int);
Query OK, 0 rows affected (0.01 sec)
//创建SALGRADE表:
mysql> CREATE TABLE SALGRADE (
-> GRADE int,
-> LOSAL INT,
-> HISAL INT);
Query OK, 0 rows affected (0.01 sec)
//向SALGRADE表添加数据:
mysql> INSERT INTO SALGRADE VALUES (1,700,1200);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO SALGRADE VALUES (2,1201,1400);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO SALGRADE VALUES (3,1401,2000);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO SALGRADE VALUES (4,2001,3000);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO SALGRADE VALUES (5,3001,9999);
Query OK, 1 row affected (0.01 sec)
SQL语句练习题一:
1、选择部门30中的所有员工;
mysql> select * from EMP where DEPTNO=30;
2、列出所有办事员(CLERK)的姓名,编号和部门编号;
mysql> select EMPNO,ENAME,DEPTNO from EMP where JOB=‘CLERK’;
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-bPLfNm1H-1672885818755)(null)]
3、找出奖金高于工资的员工;
mysql> select MAX(SAL) as MAX_SALARY from EMP;
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-JIkmdTWn-1672885818625)(null)]
4、找出奖金高于工资的60%的员工;
mysql> select ENAME from EMP where SAL*0.6<=(select MAX(HISAL) from SALGRADE);
5、找出部门10中的所有经理(MANAGER)和部门20中所有的办事员(CLERK)的详细资料;
mysql> select * from EMP where DEPTNO=10 and JOB=‘MANAGER’ or DEPTNO=20 and JOB=‘CLERK’;
6、找出部门10中所有的经理(MANAGER),部门20中所有办事员(CLERK),既不是经理又不是办事员但其工资大于或等于2000的所有员工的详细资料;
mysql> select * from EMP where SAL>=2000 and DEPTNO=10 and JOB not in(‘MANAGER’) or DEPTNO=20 and JOB not in(‘CLERK’,‘MANAGER’);
7、找出收取奖金的员工的不同工作;
mysql> select JOB,ENAME FROM EMP WHERE COMM!=NULL;
8、找出不收取奖金或者收取的奖金低于100的员工;
mysql> select ENAME from EMP where COMM=NULL or COMM<=100;
9、找出各月倒数第三天受雇的所有员工;
mysql> select ENAME from EMP where DAY(HIREDATE)=27;
10、找出早于12年前受雇的员工;
mysql> select * from EMP where DATE_SUB(now(),interval 12 year);
11、以首字母大写的方式显示所有员工的姓名;
mysql> select CONCAT(SUBSTRING(ENAME,1,1),LOWER(SUBSTRING(ENAME,2))) from EMP;
12、显示正好为5个字符的员工的姓名;
mysql> select ENAME from EMP where LENGTH(ENAME)=5;
13、显示不带有“R”的员工姓名;
mysql> select ENAME FROM EMP WHERE ENAME NOT IN (‘R’);
14、显示所有员工姓名的前三个字符;
mysql> select SUBSTRING(ENAME,1,3) from EMP;
15、显示所有员工的姓名,用“a”替换所有的“A”;
mysql> select REPLACE(ENAME,‘A’,‘a’) from EMP;
16、显示满10年服务年限的员工的姓名和受雇日期;
mysql> select HIREDATE,ENAME from EMP where date_sub(now(),interval 10 year);
17、显示员工的详细资料,按姓名排序;
mysql> select * from EMP order by ENAME;
18、显示员工的姓名和受雇日期,根据其服务年限,将最老的员工排在最前面;
mysql> select HIREDATE,ENAME from EMP where date_sub(now(),interval 10 year)>=10 order by HIREDATE ;
19、显示所有员工的姓名、工作和工资,按工作的降序排序,若工作相同则按工资排序;
mysql> select ENAME,JOB,SAL from EMP order by JOB DESC , SAL ASC;
20、显示所有员工姓名、加入公司的年份和月份,按受雇日期所在月排序,若月份相同则将最早年份的员工排在最前面;
mysql> select ENAME,YEAR(HIREDATE) as YEARS,MONTH(HIREDATE) as MONTHS from EMP order by YEARS ASC,MONTHS ASC;
21、显示在一个月为30天的情况所有员工的日工资,忽略余数;
mysql> select ENAME,SAL/30 as 日工资 from EMP;
22、找出(任何年份的)2月受聘的所有员工;
mysql> select ENAME ,HIREDATE from EMP WHERE MONTH(HIREDATE)=2 ;
23、对于每个员工,显示其加入公司的天数;
mysql> select ENAME,DATEDIFF(now(),HIREDATE) as 入职天数 from EMP;
24、显示姓名字段的任何位置包含“A”的所有员工的姓名;
mysql> select ENAME from EMP where ENAME like’%A%';
25、以年月日的方式显示所有员工的服务年限;
mysql> select ENAME,HIREDATE AS 入职时间,DATEDIFF(now(),HIREDATE) as 已入职天数 FROM EMP;
作业2:
1、列出至少有一个员工的所有部门编号、名称,并统计出这些部门的平均工资、最低工资、最高工资
mysql> select DEPTNO,AVG(SAL),MIN(SAL),MAX(SAL) from EMP where DEPTNO=20;
2、列出薪金比SMITH或者ALLEN多的所有员工的编号、姓名、部门名称、其领导姓名
mysql> select EMPNO,ENAME,JOB,MGR from EMP where SAL<= (select SAL from EMP where ENAME=‘SMITH’) or SAL>=(selectSAL FROM EMP where ENAME=‘ALLEN’);
3、列出所有员工的编号、姓名及其直接上级的编号、姓名,显示的结果按领导年工资的降序排列
select e.ENAME,e.EMPNO,e.MGR,m.ENAME as bossName from EMP e left join EMP m on e.MGR=m.EMPNO order by e.SAL DESC;
4、列出雇佣日期早于其直接上级的所有员工的编号、姓名、部门名称、部门位置、部门人数
SELECT E.EMPNO,E.ENAME,D.DNAME as 部门名称 ,D.LOC as Loction ,COUNT(E.ENAME) as 部门人数 from EMP E ,EMP M,DEPT D where E.MGR=M.EMPNO AND E.HIR
EDATE < M.HIREDATE and E.DEPTNO=D.DEPTNO group by E.EMPNO;
5、列出部门名称和这些部门的员工信息(数量、平均工资),同时列出那些没有员工的部门
mysql> select *,AVG(e.SAL) ,(select count(e.ENAME) from EMP group by e.DEPTNO) from EMP e right join DEPT d on e.DEPTNO=d.DEPTNO group by e.EMPNO,d.DEPTNO;
6、列出所有CLERK的姓名及其部门名称,部门的人数,工资等级
mysql> select e.ENAME,d.DNAME,count(*) ,g.GRADE from SALGRADE g, EMP e join DEPT d on e.DEPTNO=d.DEPTNO where JOB=‘CLERK’ group by e.ENAME,d.DNAME,g.GRADE;
7、列出最低薪金大于1500的各种工作及从事此工作的全部雇员人数及所在部门名称、位置、平均工资
mysql> select *,d.DNAME,d.LOC,AVG(SAL) from EMP,DEPT d where SAL>=1500 group by EMPNO,d.DEPTNO;
8、列出在部门SALES工作的员工的姓名、基本工资、雇佣日期、部门名称,假定不知道该部门的部门编号
mysql> select e.ENAME,e.SAL,e.HIREDATE ,d.DNAME from EMP e,DEPT d where d.DNAME=‘SALES’;
9、列出薪金高于公司平均薪金的所有员工,所在部门,上级领导,公司的工资等级
select distinct e.ENAME,m.ENAME as boosName ,d.DNAME as 部门,g.GRADE as 工资等级 fr
om EMP e,EMP m,DEPT d,SALGRADE g where e.SAL>(select AVG(SAL) from EMP) and e.MGR=m.EMPNO;
10、列出与SCOTT从事相同工作的所有员工及部门名称,部门人数
mysql> select ENAME ,DNAME,count(ENAME) from EMP inner join DEPT on EMP.DEPTNO=DEPT.DEPTNO where JOB=(select JOB from EMP where ENAME=‘SCOTT’) group by EMPNO;
11、列出公司各个工资等级雇员的数量、平均工资
mysql> select e.ENAME,AVG(e.SAL),count(*) FROM EMP e join EMP m on
e.DEPTNO=m.DEPTNO group by e.ENAME;
12、列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金、部门名称
mysql> select e.SAL ,e.ENAME,d.DNAME from EMP e ,DEPT d where SAL>(select MAX(SAL) from EMP where DEPTNO=30) and e.DEPTNO=d.DEPTNO;
13、列出在每个部门工作的员工数量、平均工资和平均服务期限
mysql> select count(ENAME),AVG(SAL),AVG(DATEDIFF(now(),HIREDATE)) from EMP
group by DEPTNO;
14、列出所有员工的姓名、部门名称和工资
mysql> select e.ENAME,e.SAL,d.DNAME from EMP e ,DEPT d where e.DEPTNO=d.DE
PTNO;
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Roo5lDT9-1672885867824)(null)]
15、列出所有部门的详细信息和部门人数
mysql> select d.* ,e.ENAME ,count(1) from DEPT d,EMP e where e.DEPTNO=d.DEPTNO group by d.DEPTNO,e.EMPNO;
16、列出各种工作的最低工资及从事此工作的雇员姓名
mysql> select MIN(e.SAL),m.ENAME,e.JOB from EMP e,EMP m where e.JOB=m.JOB group by e.JOB,m.ENAME ;
17、列出各个部门的MANAGER的最低薪金、姓名、部门名称、部门人数
mysql> select MIN(e.SAL),e.ENAME,d.DNAME,count(*) from EMP e join DEPT d on e.DEPTNO=d.DEPTNO where JOB=‘MANAGER’ group by e.ENAME,d.DNAME;
18、列出所有员工的年工资,所在部门名称,按年薪从低到高排序
mysql> select e.ENAME,e.SAL*12,d.DNAME from EMP e,DEPT d where e.DEPTNO=d.DEPTNO group by
ENAME,SAL,DNAME;
19、列出某个员工的上级主管及所在部门名称,并要求出这些主管中的薪水超过3000
mysql> select SAL from EMP where SAL > 3000 and ENAME=‘( select m.ENAME as BossNAME from
EMP e left join EMP m on m.EMPNO=e.MGR where e.EN)’;
20、求出部门名称中带‘S’字符的部门员工的工资合计、部门人数
mysql> select SUM(e.SAL),d.DNAME ,count(*) from EMP e join DEPT d on e.DEPTNO=d.DEPTNO where d.DNAME like ‘%S%’ group by d.DNAME;
注:
以上均为自我练习若有误请联系更正