sql查询语句总结练习

╰+哭是因爲堅強的太久メ 2021-11-26 13:14 431阅读 0赞

### 首先创建表格‘dept’和‘emp’并插入数据记录。 ###

代码：

/*
    Navicat MySQL Data Transfer
    
    Source Server         : localhost_3306
    Source Server Version : 50527
    Source Host           : localhost:3306
    Source Database       : wr_day01_mdb1
    
    Target Server Type    : MYSQL
    Target Server Version : 50527
    File Encoding         : 65001
    
    Date: 2019-07-24 08:00:31
    */
    
    SET FOREIGN_KEY_CHECKS=0;
    
    -- ----------------------------
    -- Table structure for `dept`
    -- ----------------------------
    DROP TABLE IF EXISTS `dept`;
    CREATE TABLE `dept` (
      `deptno` varchar(255) NOT NULL DEFAULT '',
      `dname` varchar(255) DEFAULT NULL,
      `loc` varchar(255) DEFAULT NULL,
      PRIMARY KEY (`deptno`),
      KEY `deptno` (`deptno`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    
    -- ----------------------------
    -- Records of dept
    -- ----------------------------
    INSERT INTO `dept` VALUES ('10', '教学部', '济南');
    INSERT INTO `dept` VALUES ('20', '实训中心', '莱芜');
    INSERT INTO `dept` VALUES ('30', '就业部', '济南');
    INSERT INTO `dept` VALUES ('40', '市场部', '济南');
    INSERT INTO `dept` VALUES ('50', '总经理办公室', '北京');
    
    -- ----------------------------
    -- Table structure for `emp`
    -- ----------------------------
    DROP TABLE IF EXISTS `emp`;
    CREATE TABLE `emp` (
      `empno` varchar(20) NOT NULL,
      `ename` varchar(20) NOT NULL,
      `job` varchar(20) DEFAULT NULL,
      `mgr` varchar(20) DEFAULT NULL,
      `hiredate` date DEFAULT NULL,
      `sal` int(11) DEFAULT NULL,
      `comm` int(11) DEFAULT NULL,
      `deptno` varchar(20) DEFAULT NULL,
      PRIMARY KEY (`empno`),
      KEY `deptno` (`deptno`),
      CONSTRAINT `deptno` FOREIGN KEY (`deptno`) REFERENCES `dept` (`deptno`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    
    -- ----------------------------
    -- Records of emp
    -- ----------------------------
    INSERT INTO `emp` VALUES ('1001', '李军', '总经理', null, '2018-07-01', '15000', '2000', '50');
    INSERT INTO `emp` VALUES ('1002', '赵旭', '班主任', '1003', '2018-07-02', '3000', '2000', '20');
    INSERT INTO `emp` VALUES ('1003', '李萌', '实训中心主任', '1001', '2018-06-22', '4000', '2000', '20');
    INSERT INTO `emp` VALUES ('1004', '刘枫', '教学总监', '1001', '2018-07-10', '12000', '2000', '10');
    INSERT INTO `emp` VALUES ('1005', '王涛', '嵌入式讲师', '1004', '2018-07-10', '5000', '2000', '10');

![20190724080444657.png][]

![201907240805478.png][]

###  接下来时sql查询语句的练习题目： ###

注：读者可根据具体题目修改数据库数据内容，以便查询出相应的结果集。

1.列出至少有一个员工的所有部门。

SELECT dname FROM dept where deptno IN (SELECT DISTINCT deptno FROM emp)

2.列出薪金比“王涛”多的所有员工。

SELECT \* FROM emp WHERE sal > (SELECT sal FROM emp WHERE ename = '王涛')

3.列出所有员工的姓名及其直接上级的姓名。

SELECT e.ename '员工',IFNULL(b.ename,'boss') '上司' FROM  emp e LEFT JOIN emp b ON e.mgr = b.empno

4.列出受雇日期早于其直接上级的所有员工。

SELECT e.\* FROM emp e LEFT JOIN emp b ON e.mgr = b.empno WHERE e.hiredate < b.hiredate

5.列出部门名称和这些部门的员工信息，包括那些没有员工的部门。

SELECT dept.dname,emp.\* FROM dept LEFT JOIN emp ON dept.deptno = emp.deptno

6.列出所有job为“CLERK”（办事员）的姓名及其部门名称。

SELECT ename,dname FROM emp INNER JOIN dept ON emp.deptno = dept.deptno WHERE job = 'CLERK'

7.列出最低薪金大于1500的各种工作。

SELECT job FROM emp GROUP BY job HAVING MIN(sal)>1500

8.列出在部门“教学部”工作的员工的姓名，假定不知道教学部的部门编号。

SELECT ename FROM emp WHERE deptno = (SELECT deptno FROM dept WHERE dname = '教学部')

9.列出薪金高于公司平均薪金的所有员工。

SELECT \* FROM emp WHERE sal > (SELECT AVG(sal) FROM emp)

10.列出与“刘枫”从事相同工作的所有员工。

SELECT \* FROM emp WHERE job = (SELECT job FROM emp WHERE ename = '刘枫')

11.列出薪金等于部门10中员工的薪金的所有员工的姓名和薪金。

SELECT ename,sal FROM emp WHERE sal = ANY(SELECT sal FROM emp WHERE deptno = '10')

12.列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金。

SELECT ename,sal FROM emp WHERE sal > ALL(SELECT sal FROM emp WHERE deptno = '30')

13.列出在每个部门工作的员工数量、平均工资和平均服务期限。

SELECT dname,COUNT(empno),AVG(sal),AVG( TIMESTAMPDIFF(DAY,hiredate,NOW())) FROM dept LEFT JOIN emp ON dept.deptno = emp.deptno GROUP BY dept.deptno

14.列出所有员工的姓名、部门名称和工资。

SELECT ename,dname,sal FROM emp INNER JOIN dept ON emp.deptno = dept.deptno

15.列出从事同一种工作但属于不同部门的员工的一种组合。

SELECT e1.ename,e1.job,e1.deptno,e2.ename,e2.job,e2.deptno FROM emp e1 CROSS JOIN emp e2 WHERE e1.job = e2.job AND e1.deptno != e2.deptno LIMIT 1

16.列出所有部门的详细信息和部门人数。

SELECT DISTINCT dept.deptno,dname,loc,COUNT(empno)'部门人数' FROM dept LEFT JOIN emp ON dept.deptno = emp.deptno GROUP BY dept.deptno

17.列出各种工作的最低工资。

SELECT job,MIN(sal) FROM emp GROUP BY job

18.列出各个部门的MANAGER（经理）的最低薪金（job为MANAGER）。

SELECT dept.dname,MIN(sal)'该部门经理的最低薪 资'  FROM dept LEFT JOIN (SELECT \* FROM emp WHERE job = '经理') m on dept.deptno = m.deptno GROUP BY dept.deptno

19.列出所有员工的年工资，按年薪从低到高排序。

SELECT ename,(sal\*12) FROM emp ORDER BY (sal\*12) DESC

### sql语句如果有误误，望读者指出，谢谢。 ###

[20190724080444657.png]: /images/20211126/926151fe266046f1ab980ffee35fc963.png
[201907240805478.png]: /images/20211126/4b98365d76174a06949d387c1580a56d.png