数据库操作练习1

1.查找最晚入职员工的所有信息

题目描述

查找最晚入职员工的所有信息
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`));

 

emp_no	birth_date	first_name	last_name	gender	hire_date
10008	1958-02-19	Saniya	Kalloufi	M	1994-09-15

Sql1:

SELECT * FROM employees ORDER BY hire_date DESC LIMIT 1;

LIMIT m,n : 表示从第m+1条开始，取n条数据；
LIMIT n ： 表示从第0条开始，取n条数据，是limit(0,n)的缩写。
本题limit 0,1 表示从第（0+1）条数据开始，取一条数据，即取出最晚入职员工。

Sql2:

SELECT * FROM employees WHERE hire_date=(SELECT MAX(hire_date) FROM employees);

上述查询为子查询,先找出 hire_date 字段的最大值，再把该值当成  employees 表的 hire_date 查询条件。 从employees中选出最大的值

2.查找入职员工时间排名倒数第三的员工所有信息

题目描述

查找入职员工时间排名倒数第三的员工所有信息
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`));

 

emp_no	birth_date	first_name	last_name	gender	hire_date
10005	1955-01-21	Kyoichi	Maliniak	M	1989-09-12

Sql1:

SELECT * FROM employees ORDER BY hire_date DESC LIMIT 2,1;

Sql2:

网友说可能有多个，而且要去重于是：

SELECT * FROM employees WHERE hire_date=(SELECT DISTINCT hire_date FROM employees ORDER BY hire_date DESC LIMIT 2,1);

Sql3:

先取倒数第三个，然后排序（升序）取第一个

SELECT * FROM (SELECT * FROM employees order by hire_date DESC LIMIT 3) ORDER BY hire_date LIMIT 1;

3. 查找各个部门当前领导当前薪水详情以及其对应部门编号dept_no

题目描述

查找各个部门当前(to_date='9999-01-01')领导当前薪水详情以及其对应部门编号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`));
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`));

 

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
10010	94409	2001-11-23	9999-01-01	d006

SELECT salaries.emp_no, salaries.salary, salaries.from_date, salaries.to_date, dept_manager.dept_no
		FROM salaries INNER JOIN dept_manager ON dept_manager.emp_no=salaries.emp_no where salaries.to_date='9999-01-01'
		AND dept_manager.to_date='9999-01-01'

4.查找员工编号emp_now为10001其自入职以来的薪水salary涨幅值growth

题目描述

查找员工编号emp_now为10001其自入职以来的薪水salary涨幅值growth
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`));

sql1:

SELECT MAX(salary) - MIN(salary) FROM salaries WHERE emp_no = '10001'

sql2:

SELECT ((SELECT salary FROM salaries WHERE emp_no = '10001' ORDER BY salary DESC LIMIT 1)
- (SELECT salary FROM salaries WHERE emp_no = '10001' ORDER BY salary LIMIT 1)) AS growth

5.查找employees表所有emp_no为奇数

题目描述

查找employees表所有emp_no为奇数，且last_name不为Mary的员工信息，并按照hire_date逆序排列
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`));

emp_no	birth_date	first_name	last_name	gender	hire_date
10011	1953-11-07	Mary	Sluis	F	1990-01-22
10005	1955-01-21	Kyoichi	Maliniak	M	1989-09-12
10007	1957-05-23	Tzvetan	Zielinski	F	1989-02-10
10003	1959-12-03	Parto	Bamford	M	1986-08-28
10001	1953-09-02	Georgi	Facello	M	1986-06-26
10009	1952-04-19	Sumant	Peac	F	1985-02-18

sql1:

SELECT * FROM employees WHERE emp_no %2 == 1 AND last_name <> 'Mary' ORDER BY 
hire_date DESC 

6. 找出所有员工当前具体的薪水salary情况

题目描述

找出所有员工当前(to_date='9999-01-01')具体的薪水salary情况，对于相同的薪水只显示一次,并按照逆序显示
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`));

salary
94692
94409
88958
88070
74057
72527
59755
43311
25828

sql1:

SELECT DISTINCT salary FROM salaries WHERE to_date='9999-01-01' ORDER BY salary DESC

sql2:

SELECT salary FROM salaries WHERE to_date='9999-01-01' GROUP BY salary ORDER BY 
salary DESC 

性能比较：

对于distinct,group by的性能。
数据量非常巨大时候，比如1000万中有300W重复数据，这时候的distinct的效率略好于group by；
对于相对重复量较小的数据量比如1000万中1万的重复量，用groupby的性能会远优于distnct。
简书上的一篇博客说的不错，大家可以穿送过去看一看传送门，mysql获取当前时间 : now()
sqlServer获取当前时间: getDate()(sql2参考牛客网友回答)

对于distinct与group by的使用:

1、当对系统的性能高并数据量大时使用group by

2、当对系统的性能不高时使用数据量少时两者皆可

3、尽量使用group by

7. 从titles表获取按照title进行分组

题目描述

从titles表获取按照title进行分组，每组个数大于等于2，给出title以及对应的数目t。
CREATE TABLE IF NOT EXISTS "titles" (
`emp_no` int(11) NOT NULL,
`title` varchar(50) NOT NULL,
`from_date` date NOT NULL,
`to_date` date DEFAULT NULL);

title	t
Assistant Engineer	2
Engineer	4
省略	省略
Staff	3

sql1:

SELECT title,count(title) AS t FROM titles GROUP BY title HAVING COUNT(title) >=2

8. 从titles表获取按照title进行分组，注意对于重复的emp_no进行忽略。

题目描述

从titles表获取按照title进行分组，每组个数大于等于2，给出title以及对应的数目t。
注意对于重复的emp_no进行忽略。
CREATE TABLE IF NOT EXISTS "titles" (
`emp_no` int(11) NOT NULL,
`title` varchar(50) NOT NULL,
`from_date` date NOT NULL,
`to_date` date DEFAULT NULL);

title	t
Assistant Engineer	2
Engineer	3
省略	省略
Staff	3

sql1:

SELECT ts.title,count(ts.title) AS t FROM (SELECT DISTINCT emp_no  ,title FROM titles ) ts
GROUP BY ts.title HAVING COUNT(ts.title) >=2

 sql2:

SELECT title, COUNT(DISTINCT emp_no) AS t FROM titles 
GROUP BY title HAVING t>=2

    先用GROUP BY title将表格以title分组，再用COUNT(DISTINCT emp_no)（注意：这里注意distinct 作用于后面所有字段）可以统计同一title值且不包含重复emp_no值的记录条数,题目要输出每个title的个数为t和titile，故用AS语句将COUNT(DISTINCT emp_no)的值转换为t,由于WHERE后不可跟COUNT()函数，故用HAVING语句来限定t>=2的条件：

9. 获取当前薪水第二多的员工的emp_no以及其对应的薪水salary

题目描述

获取当前（to_date='9999-01-01'）薪水第二多的员工的emp_no以及其对应的薪水salary
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`));

emp_no	salary
10009	94409

sql1 : 查找出to_date='9999-01-01'处的emp_no和salary两列，然后以salary 按照降序排列，从第二个开始取，取一个

   

SELECT emp_no, salary FROM salaries WHERE to_date='9999-01-01'
ORDER BY salary DESC LIMIT 1,1

sql2: 先选出to_date='9999-01-01'处小于max(salary)的剩下的一波，再从剩下的一波中找出最大值

SELECT emp_no, MAX(salary) FROM salaries WHERE salary < (SELECT MAX(salary) FROM salaries
WHERE to_date='9999-01-01')

sql3: 为了防止钱多的人可能存在多个，因此：

SELECT emp_no , salary FROM salaries WHERE salary = (SELECT salary FROM salaries
ORDER BY salary DESC LIMIT 1,1)

sql4:

SELECT emp_no ,MAX(salary) FROM salaries WHERE to_date='9999-01-01' AND
salary != (SELECT MAX(salary) FROM salaries)

 10.获取employees中的行数据，且这些行也存在于emp_v中

题目描述

存在如下的视图：
create view emp_v as select * from employees where emp_no >10005;
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`));
获取employees中的行数据，且这些行也存在于emp_v中。注意不能使用intersect关键字。
输出格式:

emp_no	birth_date	first_name	last_name	gender	hire_date
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

sql1：用where选出两者相等的记录

SELECT em.* FROM employees AS em, emp_v AS ev WHERE em.emp_no = ev.emp_no

sql2: 直接从employees 输出记录

SELECT * FROM employees WHERE emp_no >10005

sql3: 直接从emp_v输出记录

SELECT * FROM emp_v