MySQL单表查询练习（条件_模糊_分组_聚合_排序）

练习所用数据表

• 部门表
CREATE TABLE DEPT(
 DEPTNO INT PRIMARY KEY, – 部门编号
 DNAME VARCHAR(14) , – 部门名称
 LOC VARCHAR(13) – 部门地址
 ) ;INSERT INTO DEPT VALUES (10,‘ACCOUNTING’,‘NEW YORK’);
 INSERT INTO DEPT VALUES (20,‘RESEARCH’,‘DALLAS’);
 INSERT INTO DEPT VALUES (30,‘SALES’,‘CHICAGO’);
 INSERT INTO DEPT VALUES (40,‘OPERATIONS’,‘BOSTON’);• 员工表
CREATE TABLE EMP
 (
 EMPNO INT PRIMARY KEY, – 员工编号
 ENAME VARCHAR(10), – 员工名称
 JOB VARCHAR(9), – 工作
 MGR DOUBLE, – 直属领导编号
 HIREDATE DATE, – 入职时间
 SAL DOUBLE, – 工资
 COMM DOUBLE, – 奖金
 DEPTNO INT, – 部门号
 FOREIGN KEY(DEPTNO) REFERENCES DEPT(DEPTNO));
 INSERT INTO EMP VALUES
 (7369,‘SMITH’,‘CLERK’,7902,‘1980-12-17’,800,NULL,20);
 INSERT INTO EMP VALUES
 (7499,‘ALLEN’,‘SALESMAN’,7698,‘1981-02-20’,1600,300,30);
 INSERT INTO EMP VALUES
 (7521,‘WARD’,‘SALESMAN’,7698,‘1981-02-22’,1250,500,30);
 INSERT INTO EMP VALUES
 (7566,‘JONES’,‘MANAGER’,7839,‘1981-04-02’,2975,NULL,20);
 INSERT INTO EMP VALUES
 (7654,‘MARTIN’,‘SALESMAN’,7698,‘1981-09-28’,1250,1400,30);
 INSERT INTO EMP VALUES
 (7698,‘BLAKE’,‘MANAGER’,7839,‘1981-05-01’,2850,NULL,30);
 INSERT INTO EMP VALUES
 (7782,‘CLARK’,‘MANAGER’,7839,‘1981-06-09’,2450,NULL,10);
 INSERT INTO EMP VALUES
 (7788,‘SCOTT’,‘ANALYST’,7566,‘1987-07-13’,3000,NULL,20);
 INSERT INTO EMP VALUES
 (7839,‘KING’,‘PRESIDENT’,NULL,‘1981-11-17’,5000,NULL,10);
 INSERT INTO EMP VALUES
 (7844,‘TURNER’,‘SALESMAN’,7698,‘1981-09-08’,1500,0,30);
 INSERT INTO EMP VALUES
 (7876,‘ADAMS’,‘CLERK’,7788,‘1987-07-13’,1100,NULL,20);
 INSERT INTO EMP VALUES
 (7900,‘JAMES’,‘CLERK’,7698,‘1981-12-03’,950,NULL,30);
 INSERT INTO EMP VALUES
 (7902,‘FORD’,‘ANALYST’,7566,‘1981-12-03’,3000,NULL,20);
 INSERT INTO EMP VALUES
 (7934,‘MILLER’,‘CLERK’,7782,‘1982-01-23’,1300,NULL,10);• 工资等级表
CREATE TABLE SALGRADE
 ( GRADE INT, – 工资等级
 LOSAL DOUBLE, – 最低工资
 HISAL DOUBLE ); – 最高工资
 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);

习题&答案

使用emp表进行查询

• 查询出部门编号为30的员工

SELECT * FROM emp WHERE DEPTNO =30;

• 查询所有销售员的姓名、编号、部门编号

SELECT ENAME, EMPNO, DEPTNO FROM emp WHERE JOB='SALESMAN';

• 找出奖金高于工资的员工

SELECT * FROM emp WHERE COMM>SAL;

• 找出奖金高于工资60%的员工

SELECT * FROM emp WHERE COMM>SAL*0.6;

• 找出部门编号为10的所有经理， 和部门编号为20的所有销售员的详细资料

SELECT * FROM emp WHERE (DEPTNO=10 AND JOB='MANAGER') OR (DEPTNO=20 and JOB='SALESMAN');

• 找出部门编号为10的所有经理， 和部门编号为20的所有销售员,还有既不是经理也不是销售员但工资大于或等于2000的所有员工的详细资料

SELECT * FROM emp WHERE (DEPTNO=10 AND JOB='MANAGER') OR (DEPTNO=20 AND JOB='SALESMAN') OR (NOT JOB IN('MANAGER', 'SALESMAN')AND SAL>2000);

• 无奖金或奖金低于100的员工

SELECT * FROM emp WHERE COMM<100 OR COMM IS NULL;

• 查询名字为五个字符的员工

SELECT * FROM emp WHERE ENAME LIKE '_____';

• 查询1981年入职的员工

SELECT * FROM emp WHERE HIREDATE LIKE '1981%';

• 查询所有员工的详细信息，使用编号升序排列

SELECT * FROM EMP ORDER BY EMPNO ASC;

• 查询所有员工的详细信息，用工资降序排序， 如果工资相同则使用入职日期升序排序

SELECT * FROM emp ORDER BY SAL DESC,HIREDATE ASC;

• 查询每个部门的平均工资

SELECT DEPTNO,AVG(SAL) FROM emp GROUP BY DEPTNO;

• 查询每个部门的员工数量

SELECT DEPTNO, COUNT(*) FROM emp GROUP BY DEPTNO;

• 查询每种工作的最高工资、最低工资、人数,并按照人数升序排列，若人数相同则按照最低工资降序排列

SELECT JOB, MAX(SAL),MIN(SAL), COUNT(SAL) FROM emp GROUP BY JOB ORDER BY COUNT(SAL), MIN(SAL) DESC;