MySQL练习

最新推荐文章于 2023-05-15 16:02:01 发布

原创最新推荐文章于 2023-05-15 16:02:01 发布 · 432 阅读

2 ·

CC 4.0 BY-SA版权

文章标签：

#mysql

数据库专栏收录该内容

18 篇文章

订阅专栏

创建“emp”表并插入数据：

CREATE TABLE `emp` (
  `EMPNO` INT(4) NOT NULL,
  `ENAME` VARCHAR(10) DEFAULT NULL,
  `JOB` VARCHAR(9) DEFAULT NULL,
  `MGR` VARCHAR(10) DEFAULT NULL,
  `HIREDATE` DATE DEFAULT NULL,
  `SAL` INT(7) DEFAULT NULL,
  `COMM` INT(7) DEFAULT NULL,
  `DEPTNO` INT(2) DEFAULT NULL,
  PRIMARY KEY (`EMPNO`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;


INSERT INTO `emp`(`EMPNO`,`ENAME`,`JOB`,`MGR`,`HIREDATE`,`SAL`,`COMM`,`DEPTNO`) VALUES
('7369','SMITH','CLERK','7902','1980-12-17','800',NULL,'20'),
('7499','ALLEN','SALESMAN','7698','1981-02-20','1600','300','30'),
('7521','WARD','SALESMAN','7698','1981-02-22','1250','500','30'),
('7566','JONES','MANAGER','7839','1981-04-02','2975',NULL,'20'),
('7654','MARTIN','SALESMAN','7698','1981-09-28','1250','1400','30'),
('7698','BLAKE','MANAGER','7839','1981-05-01','2850',NULL,'30'),
('7782','CLARK','MANAGER','7839','1981-06-09','2450',NULL,'10'),
('7788','SCOTT','ANALYST','7566','1987-04-19','3000',NULL,'20'),
('7839','KING','PRESIDENT',NULL,'1981-11-17','5000',NULL,'10'),
('7844','TURNER','SALESMAN','7698','1981-09-08','1500','0','30'),
('7876','ADAMS','CLERK','7788','1987-05-23','1100',NULL,'20'),
('7900','JAMES','CLERK','7698','1981-12-03','950',NULL,'30'),
('7902','FORD','ANALYST','7566','1981-12-03','3000',NULL,'20'),
('7934','MILLER','CLERK','7782','1982-01-23','1300',NULL,'10');

创建“dept”表并插入数据：

CREATE TABLE `dept` (
  `DEPTNO` INT(2) NOT NULL,
  `DNAME` VARCHAR(14) DEFAULT NULL,
  `LOC` VARCHAR(13) DEFAULT NULL,
  PRIMARY KEY (`DEPTNO`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;


INSERT INTO `dept`(`DEPTNO`,`DNAME`,`LOC`) VALUES
('10','ACCOUNTING','NEW YORK'),
('20','RESEARCH','DALLAS'),
('30','SALES','CHICAGO'),
('40','OPERATIONS','BOSTON');

查询以下内容：

1．列出至少有4个员工的所有部门编号和名称。

SELECT DEPTNO ,COUNT(*) c
FROM emp
GROUP BY DEPTNO HAVING c>4; 
-- 整合
SELECT d.* FROM dept d JOIN (SELECT DEPTNO,COUNT(1) c FROM emp GROUP BY DEPTNO HAVING c>4) n  ON d.DEPTNO=n.DEPTNO;

在这里插入图片描述

2．列出薪金比“SMITH”多的所有员工。

SELECT * FROM emp WHERE SAL >(SELECT SAL FROM emp WHERE ENAME='SMITH');

在这里插入图片描述

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

SELECT e.ENAME '员工',m.ENAME '经理' FROM emp e JOIN emp m ON e.MGR=m.EMPNO;

在这里插入图片描述

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

SELECT  e.EMPNO,e.ENAME,d.DNAME
FROM emp e JOIN emp m ON e.MGR = m.EMPNO JOIN dept d ON e.DEPTNO = d.DEPTNO
WHERE e.HIREDATE < m.HIREDATE;

在这里插入图片描述

5．列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门。

SELECT e.*,d.DNAME
FROM emp e RIGHT JOIN dept d ON e.DEPTNO = d.DEPTNO;

在这里插入图片描述

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

SELECT DEPTNO 
FROM emp
GROUP BY DEPTNO;
-- 整合
SELECT e.ENAME,d.DNAME
FROM emp e RIGHT JOIN dept d ON e.DEPTNO=d.DEPTNO JOIN (SELECT DEPTNO FROM emp GROUP BY DEPTNO) n ON e.DEPTNO=n.DEPTNO
WHERE e.JOB='CLERK';

在这里插入图片描述

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

SELECT JOB
FROM emp GROUP BY JOB HAVING MIN(SAL) > 1500;

在这里插入图片描述

8．列出在部门“SALES”（销售部）工作的员工的姓名，假定不知道销售部的部门编号。

SELECT e.ENAME
FROM emp e WHERE e.DEPTNO=(SELECT DEPTNO FROM dept WHERE DNAME='SALES');

在这里插入图片描述

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

SELECT e.* FROM emp e WHERE e.SAL >(SELECT AVG(SAL) FROM emp);

在这里插入图片描述

10．列出与“SCOTT”从事相同工作的所有员工。

SELECT DEPTNO
FROM emp GROUP BY DEPTNO;
-- 整合
SELECT e.*,d.DNAME
FROM emp e,dept d ,(SELECT DEPTNO FROM emp GROUP BY DEPTNO) n WHERE e.DEPTNO=d.DEPTNO AND e.DEPTNO=n.DEPTNO AND e.JOB=(SELECT JOB FROM emp WHERE ENAME='SCOTT');

在这里插入图片描述

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

SELECT e.ENAME,e.SAL
FROM emp e JOIN dept d ON e.DEPTNO=d.DEPTNO WHERE SAL = ALL(SELECT SAL FROM emp WHERE DEPTNO=30);

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

SELECT e.ENAME,e.SAL
FROM emp e JOIN dept d ON e.DEPTNO=d.DEPTNO WHERE SAL > ALL(SELECT SAL FROM emp WHERE DEPTNO=30);

在这里插入图片描述

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

SELECT DEPTNO,COUNT(EMPNO),AVG(SAL),AVG(DATEDIFF(NOW(),HIREDATE)/365)
FROM emp GROUP BY DEPTNO;

在这里插入图片描述

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

SELECT e.ENAME,d.DNAME,e.SAL
FROM emp e JOIN dept d ON e.DEPTNO=d.DEPTNO;

在这里插入图片描述

15．列出所有部门的详细信息和部门人数。

SELECT DEPTNO ,COUNT(1) c FROM emp GROUP BY DEPTNO;
-- 整合
SELECT d.*,n.c
FROM dept d LEFT JOIN (SELECT DEPTNO ,COUNT(1) c FROM emp GROUP BY DEPTNO) n ON d.DEPTNO=n.DEPTNO;

在这里插入图片描述

16．列出各种工作的最低工资。

SELECT * FROM emp WHERE SAL IN (SELECT MIN(SAL) FROM emp GROUP BY JOB);

在这里插入图片描述

17．列出各个部门的MANAGER（经理）的最低薪金。

SELECT DEPTNO,MIN(SAL) min_sal
FROM emp WHERE JOB='MANAGER' GROUP BY DEPTNO;

在这里插入图片描述

18．列出所有员工的年工资,按年薪从低到高排序。

SELECT e.SAL + IFNULL(COMM,0) '年薪',d.DNAME
FROM emp e JOIN dept d ON e.DEPTNO=d.DEPTNO ORDER BY '年薪';

在这里插入图片描述

思考：列出每个部门薪水前两名最高的人员名称以及薪水

SELECT * 
FROM emp e1 WHERE 2>(SELECT COUNT(*) FROM emp e2 WHERE e1.DEPTNO=e2.DEPTNO AND e1.SAL<e2.SAL) ORDER BY SAL DESC;

在这里插入图片描述