sql学习笔记
部分内容摘自 剑指offer
- 题目描述
查找薪水涨幅超过15次的员工号emp_no以及其对应的涨幅次数t
CREATE TABLEsalaries
(
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
));
题目分析: 题干中出现涨幅超过15次(下面sql语句用number>15表示,这儿把员工初始工资也算作一次涨幅),所以要用到count统计emp_no出现次数的方法count统计数据出现次数的方法。其次借助此题帮助我们理解group by 与order by的区别,order by 用在排序即desc 或asc; group by就是分组,举个例子好说点,group by 单位名称
这样的运行结果就是以“单位名称”为分类标志统计各单位的职工人数和工资总额。
这样可以更好的分下类,更好看一些。
还有就是为什么没有用where而是用的having,记住下面的两句话就好了。
WHERE语句在GROUP BY语句之前;SQL会在分组之前计算WHERE语句。
HAVING语句在GROUP BY语句之后;SQL会在分组之后计算HAVING语句。
于是我们可以这样写:
select emp_no, count(emp_no) as number
from salaries
group by emp_no
having number>15;
- 题干描述:查找所有员工入职时候的薪水情况,给出emp_no以及salary, 并按照emp_no进行逆序
CREATE TABLEemployees
(
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
));
CREATE TABLEsalaries
(
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
));
此题有四个知识点:
1、由于员工的涨薪问题,导致emp_no不唯一,即有多条emp_no记录。因此有限制条件salaries.emp_no=employees.emp_no
2、根据题意注意到 salaries.from_date 和 employees.hire_date 的值应该要相等,因此有限制条件 e.hire_date = s.from_date
3、根据题意要按照 emp_no 值逆序排列,因此最后要加上 ORDER BY e.emp_no DESC
4、这里可将employees 直接AS 为e,同理salaries AS为s
select e.emp_no,s.salary
from employees e,salaries s
where e.emp_no=s.emp_no
and e.hire_date=s.from_date
order by e.emp_no desc;
3、题目描述
查找所有员工的last_name和first_name以及对应部门编号dept_no,也包括展示没有分配具体部门的员工
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
));
select employees.last_name,first_name,dept_emp.dept_no
from employees
left join dept_emp
on employees.emp_no=dept_emp.emp_no;
题目分析:此题我先附上sql语句,大家可以对照sql语句看。
这里用到了left join(普及:lef join 会读取左表的全部内容不管右表有无数据,right相反,二inner join 则读取左右两表数据的交集) 即结果对于左表(即表employees)结果全部予以显示,题目要求!(所以以后遇到题干说道包括…就可以用到left join)
注意on与where有什么区别,两个表连接时用on,在使用left jion时,on和where条件的区别如下:
1、 on条件是在生成临时表时使用的条件(确保了emp_no的唯一性?),它不管on中的条件是否为真,都会返回左边表中的记录。
2、where条件是在临时表生成好后,再对临时表进行过滤的条件。这时已经没有left join的含义(必须返回左边表的记录)了,条件不为真的就全部过滤掉。
4、题目描述:找出所有员工当前(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
));
分析:此题用到了group by(重复率低时性能优于distinct)作为薪水只显示一次的限制条件,此外group by(分组)使用时在where之后,在order by之前。
select salary
from salaries
where to_date='9999-01-01'
group by salary order by salary desc;