数据库系统原理与应用教程(071)—— MySQL 练习题:操作题 110-120(十五):综合练习
110、限定查询的记录数(1)
员工表:employees,表结构和数据如下:
/*
drop table if exists `employees` ;
CREATE TABLE `employees` (
`emp_no` int(11) NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` char(1) NOT NULL,
`hire_date` date NOT NULL,
PRIMARY KEY (`emp_no`));
INSERT INTO employees VALUES(10001,'1953-09-02','Georgi','Facello','M','1986-06-26');
INSERT INTO employees VALUES(10002,'1964-06-02','Bezalel','Simmel','F','1985-11-21');
INSERT INTO employees VALUES(10003,'1959-12-03','Parto','Bamford','M','1986-08-28');
INSERT INTO employees VALUES(10004,'1954-05-01','Chirstian','Koblick','M','1986-12-01');
INSERT INTO employees VALUES(10005,'1955-01-21','Kyoichi','Maliniak','M','1989-09-12');
INSERT INTO employees VALUES(10006,'1953-04-20','Anneke','Preusig','F','1989-06-02');
INSERT INTO employees VALUES(10007,'1957-05-23','Tzvetan','Zielinski','F','1989-02-10');
INSERT INTO employees VALUES(10008,'1958-02-19','Saniya','Kalloufi','M','1994-09-15');
INSERT INTO employees VALUES(10009,'1952-04-19','Sumant','Peac','F','1985-02-18');
INSERT INTO employees VALUES(10010,'1963-06-01','Duangkaew','Piveteau','F','1989-08-24');
INSERT INTO employees VALUES(10011,'1953-11-07','Mary','Sluis','F','1990-01-22');
*/
mysql> select * from employees;
+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date |
+--------+------------+------------+-----------+--------+------------+
| 10001 | 1953-09-02 | Georgi | Facello | M | 1986-06-26 |
| 10002 | 1964-06-02 | Bezalel | Simmel | F | 1985-11-21 |
| 10003 | 1959-12-03 | Parto | Bamford | M | 1986-08-28 |
| 10004 | 1954-05-01 | Chirstian | Koblick | M | 1986-12-01 |
| 10005 | 1955-01-21 | Kyoichi | Maliniak | M | 1989-09-12 |
| 10006 | 1953-04-20 | Anneke | Preusig | F | 1989-06-02 |
| 10007 | 1957-05-23 | Tzvetan | Zielinski | F | 1989-02-10 |
| 10008 | 1958-02-19 | Saniya | Kalloufi | M | 1994-09-15 |
| 10009 | 1952-04-19 | Sumant | Peac | F | 1985-02-18 |
| 10010 | 1963-06-01 | Duangkaew | Piveteau | F | 1989-08-24 |
| 10011 | 1953-11-07 | Mary | Sluis | F | 1990-01-22 |
+--------+------------+------------+-----------+--------+------------+
11 rows in set (0.00 sec)
【问题】查询最晚入职员工的所有信息,查询结果如下:
emp_no | birth_date | first_name | last_name | gender | hire_date |
10008 | 1958-02-19 | Saniya | Kalloufi | M | 1994-09-15 |
员工表:employees,表结构和数据如下:
解答:
mysql> select * from employees order by hire_date desc limit 1;
+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date |
+--------+------------+------------+-----------+--------+------------+
| 10008 | 1958-02-19 | Saniya | Kalloufi | M | 1994-09-15 |
+--------+------------+------------+-----------+--------+------------+
1 row in set (0.00 sec)
111、限定查询的记录数(2)
员工表:employees,表结构和数据如下:
/*
drop table if exists `employees` ;
CREATE TABLE `employees` (
`emp_no` int(11) NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` char(1) NOT NULL,
`hire_date` date NOT NULL,
PRIMARY KEY (`emp_no`));
INSERT INTO employees VALUES(10001,'1953-09-02','Georgi','Facello','M','1986-06-26');
INSERT INTO employees VALUES(10002,'1964-06-02','Bezalel','Simmel','F','1985-11-21');
INSERT INTO employees VALUES(10003,'1959-12-03','Parto','Bamford','M','1986-08-28');
INSERT INTO employees VALUES(10004,'1954-05-01','Chirstian','Koblick','M','1986-12-01');
INSERT INTO employees VALUES(10005,'1955-01-21','Kyoichi','Maliniak','M','1989-09-12');
INSERT INTO employees VALUES(10006,'1953-04-20','Anneke','Preusig','F','1989-06-02');
INSERT INTO employees VALUES(10007,'1957-05-23','Tzvetan','Zielinski','F','1989-02-10');
INSERT INTO employees VALUES(10008,'1958-02-19','Saniya','Kalloufi','M','1994-09-15');
INSERT INTO employees VALUES(10009,'1952-04-19','Sumant','Peac','F','1985-02-18');
INSERT INTO employees VALUES(10010,'1963-06-01','Duangkaew','Piveteau','F','1989-08-24');
INSERT INTO employees VALUES(10011,'1953-11-07','Mary','Sluis','F','1990-01-22');
*/
mysql> select * from employees;
+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date |
+--------+------------+------------+-----------+--------+------------+
| 10001 | 1953-09-02 | Georgi | Facello | M | 1986-06-26 |
| 10002 | 1964-06-02 | Bezalel | Simmel | F | 1985-11-21 |
| 10003 | 1959-12-03 | Parto | Bamford | M | 1986-08-28 |
| 10004 | 1954-05-01 | Chirstian | Koblick | M | 1986-12-01 |
| 10005 | 1955-01-21 | Kyoichi | Maliniak | M | 1989-09-12 |
| 10006 | 1953-04-20 | Anneke | Preusig | F | 1989-06-02 |
| 10007 | 1957-05-23 | Tzvetan | Zielinski | F | 1989-02-10 |
| 10008 | 1958-02-19 | Saniya | Kalloufi | M | 1994-09-15 |
| 10009 | 1952-04-19 | Sumant | Peac | F | 1985-02-18 |
| 10010 | 1963-06-01 | Duangkaew | Piveteau | F | 1989-08-24 |
| 10011 | 1953-11-07 | Mary | Sluis | F | 1990-01-22 |
+--------+------------+------------+-----------+--------+------------+
11 rows in set (0.00 sec)
【问题】查询入职时间排名倒数第三(如果第三名有重复,则全部取出)的员工所有信息,查询结果如下:
emp_no | birth_date | first_name | last_name | gender | hire_date |
10001 | 1953-09-02 | Georgi | Facello | M | 1986-06-26 |
解答:
/*
select * from employees where emp_no in
(select emp_no from (select emp_no from employees order by hire_date limit 2,1) a);
*/
mysql> select * from employees where emp_no in
-> (select emp_no from (select emp_no from employees order by hire_date limit 2,1) a);
+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date |
+--------+------------+------------+-----------+--------+------------+
| 10001 | 1953-09-02 | Georgi | Facello | M | 1986-06-26 |
+--------+------------+------------+-----------+--------+------------+
1 row in set (0.00 sec)
112、连接查询(1)
该题目使用的表和数据如下:
/*
drop table if exists `salaries` ;
drop table if exists `dept_manager` ;
CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`));
CREATE TABLE `dept_manager` (
`dept_no` char(4) NOT NULL,
`emp_no` int(11) NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`));
INSERT INTO dept_manager VALUES('d001',10002,'9999-01-01');
INSERT INTO dept_manager VALUES('d002',10006,'9999-01-01');
INSERT INTO dept_manager VALUES('d003',10005,'9999-01-01');
INSERT INTO dept_manager VALUES('d004',10004,'9999-01-01');
INSERT INTO salaries VALUES(10001,88958,'2002-06-22','9999-01-01');
INSERT INTO salaries VALUES(10002,72527,'2001-08-02','9999-01-01');
INSERT INTO salaries VALUES(10003,43311,'2001-12-01','9999-01-01');
INSERT INTO salaries VALUES(10004,74057,'2001-11-27','9999-01-01');
INSERT INTO salaries VALUES(10005,94692,'2001-09-09','9999-01-01');
INSERT INTO salaries VALUES(10006,43311,'2001-08-02','9999-01-01');
INSERT INTO salaries VALUES(10007,88070,'2002-02-07','9999-01-01');
*/
薪水表:salaries,表中数据如下:
mysql> select * from salaries;
+--------+--------+------------+------------+
| emp_no | salary | from_date | to_date |
+--------+--------+------------+------------+
| 10001 | 88958 | 2002-06-22 | 9999-01-01 |
| 10002 | 72527 | 2001-08-02 | 9999-01-01 |
| 10003 | 43311 | 2001-12-01 | 9999-01-01 |
| 10004 | 74057 | 2001-11-27 | 9999-01-01 |
| 10005 | 94692 | 2001-09-09 | 9999-01-01 |
| 10006 | 43311 | 2001-08-02 | 9999-01-01 |
| 10007 | 88070 | 2002-02-07 | 9999-01-01 |
+--------+--------+------------+------------+
7 rows in set (0.00 sec)
部门表:dept_manager,表中数据如下:
mysql> select * from dept_manager;
+---------+--------+------------+
| dept_no | emp_no | to_date |
+---------+--------+------------+
| d001 | 10002 | 9999-01-01 |
| d004 | 10004 | 9999-01-01 |
| d003 | 10005 | 9999-01-01 |
| d002 | 10006 | 9999-01-01 |
+---------+--------+------------+
4 rows in set (0.00 sec)
【问题】查询各个部门当前领导的薪水详情以及其对应的部门编号dept_no,输出结果以 salaries.emp_no 升序排序,查询结果如下:
emp_no | salary | from_date | to_date | dept_no |
10002 | 72527 | 2001-08-02 | 9999-01-01 | d001 |
10004 | 74057 | 2001-11-27 | 9999-01-01 | d004 |
10005 | 94692 | 2001-09-09 | 9999-01-01 | d003 |
10006 | 43311 | 2001-08-02 | 9999-01-01 | d002 |
解答:
/*
select s.emp_no, s.salary, s.from_date, s.to_date, d.dept_no
from dept_manager d join salaries s on d.emp_no = s.emp_no
order by s.emp_no;
*/
mysql> select s.emp_no, s.salary, s.from_date, s.to_date, d.dept_no
-> from dept_manager d join salaries s on d.emp_no = s.emp_no
-> order by s.emp_no;
+--------+--------+------------+------------+---------+
| emp_no | salary | from_date | to_date | dept_no |
+--------+--------+------------+------------+---------+
| 10002 | 72527 | 2001-08-02 | 9999-01-01 | d001 |
| 10004 | 74057 | 2001-11-27 | 9999-01-01 | d004 |
| 10005 | 94692 | 2001-09-09 | 9999-01-01 | d003 |
| 10006 | 43311 | 2001-08-02 | 9999-01-01 | d002 |
+--------+--------+------------+------------+---------+
4 rows in set (0.00 sec)
113、连接查询(2)
该题目使用的表和数据如下:
/*
drop table if exists `dept_emp` ;
drop table if exists `employees` ;
CREATE TABLE `dept_emp` (
`emp_no` int(11) NOT NULL,
`dept_no` char(4) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`));
CREATE TABLE `employees` (
`emp_no` int(11) NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` char(1) NOT NULL,
`hire_date` date NOT NULL,
PRIMARY KEY (`emp_no`));
INSERT INTO dept_emp VALUES(10001,'d001','1986-06-26','9999-01-01');
INSERT INTO dept_emp VALUES(10002,'d002','1996-08-03','9999-01-01');
INSERT INTO employees VALUES(10001,'1953-09-02','Georgi','Facello','M','1986-06-26');
INSERT INTO employees VALUES(10002,'1964-06-02','Bezalel','Simmel','F','1985-11-21');
INSERT INTO employees VALUES(10003,'1959-12-03','Parto','Bamford','M','1986-08-28');
INSERT INTO employees VALUES(10004,'1954-05-01','Chirstian','Koblick','M','1986-12-01');
*/
员工表:employees,表中数据如下:
mysql> select * from employees;
+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date |
+--------+------------+------------+-----------+--------+------------+
| 10001 | 1953-09-02 | Georgi | Facello | M | 1986-06-26 |
| 10002 | 1964-06-02 | Bezalel | Simmel | F | 1985-11-21 |
| 10003 | 1959-12-03 | Parto | Bamford | M | 1986-08-28 |
| 10004 | 1954-05-01 | Chirstian | Koblick | M | 1986-12-01 |
+--------+------------+------------+-----------+--------+------------+
4 rows in set (0.00 sec)
部门表:dept_emp,表中数据如下:
mysql> select * from dept_emp;
+--------+---------+------------+------------+
| emp_no | dept_no | from_date | to_date |
+--------+---------+------------+------------+
| 10001 | d001 | 1986-06-26 | 9999-01-01 |
| 10002 | d002 | 1996-08-03 | 9999-01-01 |
+--------+---------+------------+------------+
2 rows in set (0.00 sec)
【问题】查询所有已经分配部门的员工的 last_name 和 first_name 以及 dept_no,未分配部门的员工不显示,查询结果如下:
last_name | first_name | dept_no |
Facello | Georgi | d001 |
Simmel | Bezalel | d002 |
解答:
/*
select e.last_name, e.first_name, d.dept_no
from employees e join dept_emp d on e.emp_no = d.emp_no;
*/
mysql> select e.last_name, e.first_name, d.dept_no
-> from employees e join dept_emp d on e.emp_no = d.emp_no;
+-----------+------------+---------+
| last_name | first_name | dept_no |
+-----------+------------+---------+
| Facello | Georgi | d001 |
| Simmel | Bezalel | d002 |
+-----------+------------+---------+
2 rows in set (0.00 sec)
114、连接查询(3)
该题目使用的表和数据如下:
/*
drop table if exists `dept_emp` ;
drop table if exists `employees` ;
CREATE TABLE `dept_emp` (
`emp_no` int(11) NOT NULL,
`dept_no` char(4) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`));
CREATE TABLE `employees` (
`emp_no` int(11) NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` char(1) NOT NULL,
`hire_date` date NOT NULL,
PRIMARY KEY (`emp_no`));
INSERT INTO dept_emp VALUES(10001,'d001','1986-06-26','9999-01-01');
INSERT INTO dept_emp VALUES(10002,'d002','1996-08-03','9999-01-01');
INSERT INTO employees VALUES(10001,'1953-09-02','Georgi','Facello','M','1986-06-26');
INSERT INTO employees VALUES(10002,'1964-06-02','Bezalel','Simmel','F','1985-11-21');
INSERT INTO employees VALUES(10003,'1959-12-03','Parto','Bamford','M','1986-08-28');
INSERT INTO employees VALUES(10004,'1954-05-01','Chirstian','Koblick','M','1986-12-01');
*/
员工表:employees,表中数据如下:
mysql> select * from employees;
+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date |
+--------+------------+------------+-----------+--------+------------+
| 10001 | 1953-09-02 | Georgi | Facello | M | 1986-06-26 |
| 10002 | 1964-06-02 | Bezalel | Simmel | F | 1985-11-21 |
| 10003 | 1959-12-03 | Parto | Bamford | M | 1986-08-28 |
| 10004 | 1954-05-01 | Chirstian | Koblick | M | 1986-12-01 |
+--------+------------+------------+-----------+--------+------------+
4 rows in set (0.00 sec)
部门表:dept_emp,表中数据如下:
mysql> select * from dept_emp;
+--------+---------+------------+------------+
| emp_no | dept_no | from_date | to_date |
+--------+---------+------------+------------+
| 10001 | d001 | 1986-06-26 | 9999-01-01 |
| 10002 | d002 | 1996-08-03 | 9999-01-01 |
+--------+---------+------------+------------+
2 rows in set (0.00 sec)
【问题】查询所有已经分配部门的员工(包括暂时没有分配具体部门的员工)的 last_name 和 first_name 以及 dept_no,查询结果如下:
last_name | first_name | dept_no |
Facello | Georgi | d001 |
Simmel | Bezalel | d002 |
Bamford | Parto | NULL |
Koblick | Chirstian | NULL |
/*
select e.last_name, e.first_name, d.dept_no
from employees e left join dept_emp d on e.emp_no = d.emp_no;
*/
mysql> select e.last_name, e.first_name, d.dept_no
-> from employees e left join dept_emp d on e.emp_no = d.emp_no;
+-----------+------------+---------+
| last_name | first_name | dept_no |
+-----------+------------+---------+
| Facello | Georgi | d001 |
| Simmel | Bezalel | d002 |
| Bamford | Parto | NULL |
| Koblick | Chirstian | NULL |
+-----------+------------+---------+
4 rows in set (0.00 sec)
115、分组查询
该题目使用的表和数据如下:
/*
drop table if exists `salaries` ;
CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`));
INSERT INTO salaries VALUES(10001,60117,'1986-06-26','1987-06-26');
INSERT INTO salaries VALUES(10001,62102,'1987-06-26','1988-06-25');
INSERT INTO salaries VALUES(10001,66074,'1988-06-25','1989-06-25');
INSERT INTO salaries VALUES(10001,66596,'1989-06-25','1990-06-25');
INSERT INTO salaries VALUES(10001,66961,'1990-06-25','1991-06-25');
INSERT INTO salaries VALUES(10001,71046,'1991-06-25','1992-06-24');
INSERT INTO salaries VALUES(10001,74333,'1992-06-24','1993-06-24');
INSERT INTO salaries VALUES(10001,75286,'1993-06-24','1994-06-24');
INSERT INTO salaries VALUES(10001,75994,'1994-06-24','1995-06-24');
INSERT INTO salaries VALUES(10001,76884,'1995-06-24','1996-06-23');
INSERT INTO salaries VALUES(10001,80013,'1996-06-23','1997-06-23');
INSERT INTO salaries VALUES(10001,81025,'1997-06-23','1998-06-23');
INSERT INTO salaries VALUES(10001,81097,'1998-06-23','1999-06-23');
INSERT INTO salaries VALUES(10001,84917,'1999-06-23','2000-06-22');
INSERT INTO salaries VALUES(10001,85112,'2000-06-22','2001-06-22');
INSERT INTO salaries VALUES(10001,85097,'2001-06-22','2002-06-22');
INSERT INTO salaries VALUES(10001,88958,'2002-06-22','9999-01-01');
INSERT INTO salaries VALUES(10002,72527,'1996-08-03','1997-08-03');
*/
薪水表:salaries,表中数据如下:
mysql> select * from salaries;
+--------+--------+------------+------------+
| emp_no | salary | from_date | to_date |
+--------+--------+------------+------------+
| 10001 | 60117 | 1986-06-26 | 1987-06-26 |
| 10001 | 62102 | 1987-06-26 | 1988-06-25 |
| 10001 | 66074 | 1988-06-25 | 1989-06-25 |
| 10001 | 66596 | 1989-06-25 | 1990-06-25 |
| 10001 | 66961 | 1990-06-25 | 1991-06-25 |
| 10001 | 71046 | 1991-06-25 | 1992-06-24 |
| 10001 | 74333 | 1992-06-24 | 1993-06-24 |
| 10001 | 75286 | 1993-06-24 | 1994-06-24 |
| 10001 | 75994 | 1994-06-24 | 1995-06-24 |
| 10001 | 76884 | 1995-06-24 | 1996-06-23 |
| 10001 | 80013 | 1996-06-23 | 1997-06-23 |
| 10001 | 81025 | 1997-06-23 | 1998-06-23 |
| 10001 | 81097 | 1998-06-23 | 1999-06-23 |
| 10001 | 84917 | 1999-06-23 | 2000-06-22 |
| 10001 | 85112 | 2000-06-22 | 2001-06-22 |
| 10001 | 85097 | 2001-06-22 | 2002-06-22 |
| 10001 | 88958 | 2002-06-22 | 9999-01-01 |
| 10002 | 72527 | 1996-08-03 | 1997-08-03 |
+--------+--------+------------+------------+
18 rows in set (0.00 sec)
【问题】查询薪水记录超过 15 条的员工号 emp_no 以及其对应的记录次数t,查询结果如下:
emp_no | t |
10001 | 17 |
解答:
/*
select emp_no, count(*) t
from salaries group by emp_no having t > 15;
*/
mysql> select emp_no, count(*) t
-> from salaries group by emp_no having t > 15;
+--------+----+
| emp_no | t |
+--------+----+
| 10001 | 17 |
+--------+----+
1 row in set (0.03 sec)
116、消除重复元组
该题目使用的表和数据如下:
/*
drop table if exists `salaries` ;
CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`));
INSERT INTO salaries VALUES(10001,72527,'2002-06-22','9999-01-01');
INSERT INTO salaries VALUES(10002,72527,'2001-08-02','9999-01-01');
INSERT INTO salaries VALUES(10003,43311,'2001-12-01','9999-01-01');
*/
薪水表:salaries,表中数据如下:
mysql> select * from salaries;
+--------+--------+------------+------------+
| emp_no | salary | from_date | to_date |
+--------+--------+------------+------------+
| 10001 | 72527 | 2002-06-22 | 9999-01-01 |
| 10002 | 72527 | 2001-08-02 | 9999-01-01 |
| 10003 | 43311 | 2001-12-01 | 9999-01-01 |
+--------+--------+------------+------------+
3 rows in set (0.00 sec)
【问题】查询所有员工具体的薪水(salary)情况(相同的薪水只显示一次),并按照薪水降序显示,查询结果如下:
salary |
72527 |
43311 |
解答:
mysql> select distinct salary from salaries order by salary desc;
+--------+
| salary |
+--------+
| 72527 |
| 43311 |
+--------+
2 rows in set (0.00 sec)
117、子查询(1)
该题目使用的表和数据如下:
/*
drop table if exists `dept_manager` ;
drop table if exists `employees` ;
CREATE TABLE `dept_manager` (
`dept_no` char(4) NOT NULL,
`emp_no` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`));
CREATE TABLE `employees` (
`emp_no` int(11) NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` char(1) NOT NULL,
`hire_date` date NOT NULL,
PRIMARY KEY (`emp_no`));
INSERT INTO dept_manager VALUES('d001',10002,'1996-08-03','9999-01-01');
INSERT INTO dept_manager VALUES('d002',10003,'1990-08-05','9999-01-01');
INSERT INTO employees VALUES(10001,'1953-09-02','Georgi','Facello','M','1986-06-26');
INSERT INTO employees VALUES(10002,'1964-06-02','Bezalel','Simmel','F','1985-11-21');
INSERT INTO employees VALUES(10003,'1959-12-03','Parto','Bamford','M','1986-08-28');
*/
员工表:employees,表中数据如下:
mysql> select * from employees;
+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date |
+--------+------------+------------+-----------+--------+------------+
| 10001 | 1953-09-02 | Georgi | Facello | M | 1986-06-26 |
| 10002 | 1964-06-02 | Bezalel | Simmel | F | 1985-11-21 |
| 10003 | 1959-12-03 | Parto | Bamford | M | 1986-08-28 |
+--------+------------+------------+-----------+--------+------------+
3 rows in set (0.00 sec)
部门领导表:dept_manager,表中数据如下:
mysql> select * from dept_manager;
+---------+--------+------------+------------+
| dept_no | emp_no | from_date | to_date |
+---------+--------+------------+------------+
| d001 | 10002 | 1996-08-03 | 9999-01-01 |
| d002 | 10003 | 1990-08-05 | 9999-01-01 |
+---------+--------+------------+------------+
2 rows in set (0.00 sec)
【问题】查询所有非部门领导的员工emp_no,查询结果如下:
emp_no |
10001 |
解答:
mysql> select emp_no from employees where emp_no not in (select emp_no from dept_manager);
+--------+
| emp_no |
+--------+
| 10001 |
+--------+
1 row in set (0.08 sec)
118、连接查询
该题目使用的表和数据如下:
/*
drop table if exists `dept_emp` ;
drop table if exists `dept_manager` ;
CREATE TABLE `dept_emp` (
`emp_no` int(11) NOT NULL,
`dept_no` char(4) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`));
CREATE TABLE `dept_manager` (
`dept_no` char(4) NOT NULL,
`emp_no` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`));
INSERT INTO dept_emp VALUES(10001,'d001','1986-06-26','9999-01-01');
INSERT INTO dept_emp VALUES(10002,'d001','1996-08-03','9999-01-01');
INSERT INTO dept_emp VALUES(10003,'d002','1995-12-03','9999-01-01');
INSERT INTO dept_manager VALUES('d001',10002,'1996-08-03','9999-01-01');
INSERT INTO dept_manager VALUES('d002',10003,'1990-08-05','9999-01-01');
*/
员工表;dept_emp,表中数据如下:
mysql> select * from dept_emp;
+--------+---------+------------+------------+
| emp_no | dept_no | from_date | to_date |
+--------+---------+------------+------------+
| 10001 | d001 | 1986-06-26 | 9999-01-01 |
| 10002 | d001 | 1996-08-03 | 9999-01-01 |
| 10003 | d002 | 1995-12-03 | 9999-01-01 |
+--------+---------+------------+------------+
3 rows in set (0.00 sec)
部门经理表:dept_manager,表中数据如下:
mysql> select * from dept_manager;
+---------+--------+------------+------------+
| dept_no | emp_no | from_date | to_date |
+---------+--------+------------+------------+
| d001 | 10002 | 1996-08-03 | 9999-01-01 |
| d002 | 10003 | 1990-08-05 | 9999-01-01 |
+---------+--------+------------+------------+
2 rows in set (0.00 sec)
【问题】获取所有的员工和员工对应的经理,如果员工本身是经理的话则不显示,查询结果如下:
emp_no | manager |
10001 | 10002 |
解答:
/*
select de.emp_no, dm.emp_no manager
from dept_emp de join dept_manager dm on de.dept_no = dm.dept_no
where de.emp_no not in (select emp_no from dept_manager);
*/
mysql> select de.emp_no, dm.emp_no manager
-> from dept_emp de join dept_manager dm on de.dept_no = dm.dept_no
-> where de.emp_no not in (select emp_no from dept_manager);
+--------+---------+
| emp_no | manager |
+--------+---------+
| 10001 | 10002 |
+--------+---------+
1 row in set (0.00 sec)
119、分组查询/子查询
该题目使用的表和数据如下:
/*
drop table if exists `dept_emp` ;
drop table if exists `salaries` ;
CREATE TABLE `dept_emp` (
`emp_no` int(11) NOT NULL,
`dept_no` char(4) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`));
CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`));
INSERT INTO dept_emp VALUES(10001,'d001','1986-06-26','9999-01-01');
INSERT INTO dept_emp VALUES(10002,'d001','1996-08-03','9999-01-01');
INSERT INTO dept_emp VALUES(10003,'d002','1996-08-03','9999-01-01');
INSERT INTO salaries VALUES(10001,88958,'2002-06-22','9999-01-01');
INSERT INTO salaries VALUES(10002,72527,'2001-08-02','9999-01-01');
INSERT INTO salaries VALUES(10003,92527,'2001-08-02','9999-01-01');
*/
员工表:dept_emp,表中数据如下:
mysql> select * from dept_emp;
+--------+---------+------------+------------+
| emp_no | dept_no | from_date | to_date |
+--------+---------+------------+------------+
| 10001 | d001 | 1986-06-26 | 9999-01-01 |
| 10002 | d001 | 1996-08-03 | 9999-01-01 |
| 10003 | d002 | 1996-08-03 | 9999-01-01 |
+--------+---------+------------+------------+
3 rows in set (0.00 sec)
薪水表:salaries,表中数据如下:
mysql> select * from salaries;
+--------+--------+------------+------------+
| emp_no | salary | from_date | to_date |
+--------+--------+------------+------------+
| 10001 | 88958 | 2002-06-22 | 9999-01-01 |
| 10002 | 72527 | 2001-08-02 | 9999-01-01 |
| 10003 | 92527 | 2001-08-02 | 9999-01-01 |
+--------+--------+------------+------------+
3 rows in set (0.00 sec)
【问题】查询每个部门中当前员工薪水最高的相关信息,显示:dept_no,emp_no 以及其对应的 salary,按照部门编号dept_no 升序排列,查询结果如下:
dept_no | emp_no | maxSalary |
d001 | 10001 | 88958 |
d002 | 10003 | 92527 |
解答:
/*
select a.dept_no, emp_no, a.maxSalary
from salaries s join
(select d.dept_no, max(salary) maxSalary
from salaries s join dept_emp d on s.emp_no = d.emp_no
group by d.dept_no) a on s.salary = a.maxSalary
order by a.dept_no;
*/
mysql> select a.dept_no, emp_no, a.maxSalary
-> from salaries s join
-> (select d.dept_no, max(salary) maxSalary
-> from salaries s join dept_emp d on s.emp_no = d.emp_no
-> group by d.dept_no) a on s.salary = a.maxSalary
-> order by a.dept_no;
+---------+--------+-----------+
| dept_no | emp_no | maxSalary |
+---------+--------+-----------+
| d001 | 10001 | 88958 |
| d002 | 10003 | 92527 |
+---------+--------+-----------+
2 rows in set (0.00 sec)
120、构造查询条件
该题目使用的表和数据如下:
/*
drop table if exists `employees` ;
CREATE TABLE `employees` (
`emp_no` int(11) NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` char(1) NOT NULL,
`hire_date` date NOT NULL,
PRIMARY KEY (`emp_no`));
INSERT INTO employees VALUES(10001,'1953-09-02','Georgi','Facello','M','1986-06-26');
INSERT INTO employees VALUES(10002,'1964-06-02','Bezalel','Simmel','F','1985-11-21');
INSERT INTO employees VALUES(10003,'1959-12-03','Bezalel','Mary','M','1986-08-28');
INSERT INTO employees VALUES(10004,'1954-05-01','Chirstian','Koblick','M','1986-12-01');
INSERT INTO employees VALUES(10005,'1953-11-07','Mary','Sluis','F','1990-01-22');
*/
员工表:employees,表中数据如下:
mysql> select * from employees;
+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date |
+--------+------------+------------+-----------+--------+------------+
| 10001 | 1953-09-02 | Georgi | Facello | M | 1986-06-26 |
| 10002 | 1964-06-02 | Bezalel | Simmel | F | 1985-11-21 |
| 10003 | 1959-12-03 | Bezalel | Mary | M | 1986-08-28 |
| 10004 | 1954-05-01 | Chirstian | Koblick | M | 1986-12-01 |
| 10005 | 1953-11-07 | Mary | Sluis | F | 1990-01-22 |
+--------+------------+------------+-----------+--------+------------+
5 rows in set (0.00 sec)
【问题】查询 employees 表所有 emp_no 为奇数,且 last_name 不为 Mary 的员工信息,并按照 hire_date 降序排列,查询结果如下:
emp_no | birth_date | first_name | last_name | gender | hire_date |
10005 | 1953-11-07 | Mary | Sluis | F | 1990-01-22 |
10001 | 1953-09-02 | Georgi | Facello | M | 1986-06-26 |
解答:
/*
select * from employees
where mod(emp_no, 2) = 1 and last_name <> 'Mary'
order by hire_date desc;
*/
mysql> select * from employees
-> where mod(emp_no, 2) = 1 and last_name <> 'Mary'
-> order by hire_date desc;
+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date |
+--------+------------+------------+-----------+--------+------------+
| 10005 | 1953-11-07 | Mary | Sluis | F | 1990-01-22 |
| 10001 | 1953-09-02 | Georgi | Facello | M | 1986-06-26 |
+--------+------------+------------+-----------+--------+------------+
2 rows in set (0.02 sec)