数据库 SQL实战练习

一觉醒来想练练SQL，顺便归纳总结下题目

1.on和where的区别

看下面两个例子

数据库表都是dept_emp部门表和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`));

1.查找所有已经分配部门的员工的last_name和first_name

select employees.last_name, first_name, dept_emp.dept_no 
from dept_emp inner join employees where 
dept_emp.emp_no = employees.emp_no;

2.查找所有员工的last_name和first_name以及对应部门编号dept_no，也包括展示没有分配具体部门的员工

select last_name,first_name,dept_no from employees 
left join dept_emp on dept_emp.emp_no = employees.emp_no;

两个表连接时用on，在使用left join时，on和where条件的区别如下：

1. on条件是在生成临时表时使用的条件，它不管on中的条件是否为真，都会返回左边表中的记录。
2. where条件是在临时表生成好后，再对临时表进行过滤的条件。这时已经没有left join的含义（必须返回左边表的记录）了，条件不为真的就全部过滤掉。

2. distinct和group by 的性能

薪水表

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`));

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

一看到这道题第一反应就是使用distinct

select distinct salary from salaries  where to_date='9999-01-01' order by salary desc

也确实是通过调试了，但是没有考虑到数据量很大且重复不太多的情况下使用distinct效率会很低

对于distinct,groupby的性能：

• 数据量非常巨大时候，比如1000万中有300W重复数据，这时候的distinct的效率略好于group by；
• 对于相对重复量较小的数据量比如1000万中1万的重复量，用group by的性能会远优于distinct

因此考虑使用group by

select salary from salaries  where to_date='9999-01-01' group by salary order by salary desc;

3.not in 和 not exists

职员表和部门经理表

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`)); 

获取所有非manager的员工emp_no

一般都会想到下面的写法

select emp_no from employees where emp_no not in (
    select emp_no from dept_manager
)

这里需要注意not in的一些特殊情况：

• 如果子查询中返回的任意一条记录包含空值，则查询不返回任何记录
• 而且not in 会对内外表进行全表扫描，没有用到索引

因此我们一般都会使用not exists代替not in，因为not exists子查询仍然会用到索引，所以无论那个表大，not exists都会比not in 快

select em.emp_no from employees em where not exists(
    select distinct dm.emp_no from dept_manager dm where em.emp_no=dm.emp_no
)

这里还有一种方式就是使用连接取空值的方法

SELECT emp_no FROM (
    SELECT * FROM employees LEFT JOIN dept_manager
ON employees.emp_no = dept_manager.emp_no
)WHERE dept_no IS NULL