1. 多表连接
查找所有员工的last_name和first_name以及对应的dept_name,也包括暂时没有分配部门的员工
CREATE TABLE `departments` (
`dept_no` char(4) NOT NULL,
`dept_name` varchar(40) NOT NULL,
PRIMARY KEY (`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`));
输入描述:
无
输出描述:
last_name | first_name | dept_name |
---|---|---|
Facello | Georgi | Marketing |
省略 | 省略 | 省略 |
Sluis | Mary | NULL |
思路:
注意点:多表连接时,下一次连接是在上一次连接完成后进行!
本题思路为运用两次LEFT JOIN连接嵌套
1、第一次LEFT JOIN连接employees表与dept_emp表,得到所有员工的last_name和first_name以及对应的dept_no,也包括暂时没有分配部门的员工
2、第二次LEFT JOIN连接上表与departments表,即连接dept_no与dept_name,得到所有员工的last_name和first_name以及对应的dept_name,也包括暂时没有分配部门的员工
SELECT a.last_name,a.first_name,c.dept_name FROM employees a
LEFT JOIN dept_emp b ON a.emp_no=b.emp_no
LEFT JOIN departments c ON b.dept_no=c.dept_no
2.通过LIMIT关键字进行最(大/小)k值筛选
LIMIT a OFFSET b=LIMIT b,a 表示从下标为b开始(第一行下标为0),向后显示a个数
题目描述
查找员工编号emp_no为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`));
输入描述:
无
输出描述:
growth |
---|
28841 |
SELECT ((SELECT salary FROM salaries where emp_no='10001' ORDER BY to_date DESC LIMIT 0,1)-
(SELECT salary FROM salaries where emp_no='10001' ORDER BY to_date LIMIT 0,1)) growth
注意:sql显示运算结果 SELECT 运算 AS 列名(不需要FROM)
3. 对子查询结果进行运算
题目描述
查找所有员工自入职以来的薪水涨幅情况,给出员工编号emp_no以及其对应的薪水涨幅growth,并按照growth进行升序
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`));
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 | growth |
---|---|
10011 | 0 |
省略 | 省略 |
10010 | 54496 |
10004 | 34003 |
方法一:对两个子查询结果运算
--多个子查询做运算
SELECT a.emp_no,(a.salary-b.salary) growth FROM
(SELECT e.emp_no,s.salary FROM employees e INNER JOIN salaries s ON e.emp_no=s.emp_no AND s.to_date='9999-01-01') a
LEFT JOIN
(SELECT e.emp_no,s.salary FROM employees e INNER JOIN salaries s ON e.emp_no=s.emp_no AND s.from_date=e.hire_date) b
ON a.emp_no=b.emp_no
ORDER BY growth
方法二:做两次连接操作
--做两次连接操作
SELECT e.emp_no,(s1.salary-s2.salary) growth FROM
employees e INNER JOIN salaries s1 ON e.emp_no=s1.emp_no AND s1.to_date='9999-01-01'
INNER JOIN salaries s2 ON e.emp_no=s2.emp_no AND s2.from_date=e.hire_date
ORDER BY growth
4.复用一张表进行比较查询完成1-N排序(排序通过count实现)
题目描述
对所有员工的当前(to_date='9999-01-01')薪水按照salary进行按照1-N的排名,相同salary并列且按照emp_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 | rank |
---|---|---|
10005 | 94692 | 1 |
10009 | 94409 | 2 |
10010 | 94409 | 2 |
10001 | 88958 | 3 |
10007 | 88070 | 4 |
10004 | 74057 | 5 |
10002 | 72527 | 6 |
10003 | 43311 | 7 |
10006 | 43311 | 7 |
10011 | 25828 | 8 |
--复用同一张表 计算比当前salary高的个数即为排名
SELECT s1.emp_no,s1.salary,COUNT(DISTINCT s2.salary) rank FROM salaries s1, salaries s2 WHERE s1.to_date='9999-01-01' AND s2.to_date='9999-01-01'
AND s1.salary<=s2.salary
GROUP BY s1.emp_no
ORDER BY rank,s1.emp_no
5.利用左连接后is null或者not in关键字进行数据筛选
题目描述
获取所有非manager员工当前的薪水情况,给出dept_no、emp_no以及salary ,当前表示to_date='9999-01-01'
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`));
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`));
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`));
输入描述:
无
输出描述:
dept_no | emp_no | salary |
---|---|---|
d001 | 10001 | 88958 |
d004 | 10003 | 43311 |
d005 | 10007 | 88070 |
d006 | 10009 | 95409 |
--NOT IN 关键字
SELECT de.dept_no,de.emp_no,s.salary FROM
dept_emp de LEFT JOIN salaries s ON de.emp_no=s.emp_no WHERE s.to_date='9999-01-01'
AND de.emp_no NOT IN (SELECT emp_no FROM dept_manager)
--left join 后判断null is null
SELECT d1.dept_no,d1.emp_no,(SELECT salary FROM salaries WHERE emp_no=d1.emp_no AND to_date='9999-01-01') salary FROM
dept_emp d1 LEFT JOIN dept_manager d2 ON d1.emp_no=d2.emp_no WHERE d1.to_date='9999-01-01'
AND d2.dept_no is null
6.多表连接时,WHERE条件一律写到所有表完成连接操作后
题目描述
获取员工其当前的薪水比其manager当前薪水还高的相关信息,当前表示to_date='9999-01-01',
结果第一列给出员工的emp_no,
第二列给出其manager的manager_no,
第三列给出该员工当前的薪水emp_salary,
第四列给该员工对应的manager当前的薪水manager_salary
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`));
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 | manager_no | emp_salary | manager_salary |
---|---|---|---|
10001 | 10002 | 88958 | 72527 |
10009 | 10010 | 95409 | 94409 |
SELECT de.emp_no,dm.emp_no manager_no,s1.salary emp_salary,s2.salary manager_salary FROM
dept_emp de LEFT JOIN dept_manager dm ON de.dept_no=dm.dept_no
LEFT JOIN salaries s1 ON de.emp_no=s1.emp_no
LEFT JOIN salaries s2 ON dm.emp_no=s2.emp_no
--多表连接时,WHERE条件一律写到所有表完成连接操作后
WHERE de.to_date='9999-01-01' AND dm.to_date='9999-01-01'
AND s1.to_date='9999-01-01' AND s2.to_date='9999-01-01'
AND s1.salary>s2.salary
--多表连接
SELECT fm.title,fm.description FROM
film fm INNER JOIN film_category fc ON fm.film_id=fc.film_id
INNER JOIN category c ON fc.category_id=c.category_id
WHERE c.name='Action'
--子查询
SELECT a.title ,a.description FROM
category c,
(SELECT fm.title,fm.description,fc.category_id FROM film fm,film_category fc WHERE fm.film_id=fc.film_id) a
WHERE c.category_id=a.category_id AND c.name='Action'
7.多连接查询+子查询
题目描述
film表
字段 | 说明 |
film_id | 电影id |
title | 电影名称 |
description | 电影描述信息 |
CREATE TABLE IF NOT EXISTS film (
film_id smallint(5) NOT NULL DEFAULT '0',
title varchar(255) NOT NULL,
description text,
PRIMARY KEY (film_id));
category表
字段 | 说明 |
category_id | 电影分类id |
name | 电影分类名称 |
last_update | 电影分类最后更新时间 |
CREATE TABLE category (
category_id tinyint(3) NOT NULL ,
name varchar(25) NOT NULL, `last_update` timestamp,
PRIMARY KEY ( category_id ));
film_category表
字段 | 说明 |
film_id | 电影id |
category_id | 电影分类id |
last_update | 电影id和分类id对应关系的最后更新时间 |
CREATE TABLE film_category (
film_id smallint(5) NOT NULL,
category_id tinyint(3) NOT NULL, `last_update` timestamp);
查找描述信息中包括robot的电影对应的分类名称以及电影数目,而且还需要该分类对应电影数量>=5部
注意:模糊查询 LIKE 关键字 %:任意0个或者多个 _:1个
--模糊查询%
SELECT c.name,COUNT(f.film_id) amount FROM
film f INNER JOIN film_category fc ON f.film_id=fc.film_id
INNER JOIN (SELECT category_id FROM film_category GROUP BY category_id HAVING COUNT(film_id)>=5) cc ON cc.category_id=fc.category_id
INNER JOIN category c ON c.category_id=cc.category_id
WHERE f.description LIKE '%robot%'
GROUP BY c.name
8.数据库添加索引
题目描述
针对salaries表emp_no字段创建索引idx_emp_no,查询emp_no为10005, 使用强制索引。
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 INDEX idx_emp ON salaries(emp_no);
--sqlLite 强制索引访问
SELECT * FROM salaries INDEXED BY idx_emp_no WHERE emp_no=10005
--mysql
SELECT * FROM salaries FORCE INDEX idx_emp_no WHERE emp_no=10005
9.更新数据库的值
题目描述
将所有获取奖金的员工当前的薪水增加10%。
create table emp_bonus(
emp_no int not null,
recevied datetime not null,
btype smallint not null);
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`));
UPDATE salaries SET salary=1.1*salary WHERE emp_no IN
(SELECT s.emp_no FROM salaries s INNER JOIN emp_bonus b
ON s.emp_no=b.emp_no AND s.to_date='9999-01-01')
10.SQL 中的LENGTH() SUBSTR()函数
注意:
--substr(字符串,起始位置,长度)
--起始位置:截取的子串的起始位置(注意:字符串的第一个字符的索引是1)。值为正时从字符串开始位置 开始计数,值为负时从字符串结尾位置开始计数。
--长度:截取子串的长度
--substr(字符串,起始位置,长度)
--起始位置:截取的子串的起始位置(注意:字符串的第一个字符的索引是1)。值为正时从字符串开始位置 开始计数,值为负时从字符串结尾位置开始计数。
--长度:截取子串的长度
SELECT first_name FROM employees ORDER BY SUBSTR(first_name,-2,2)
11.查找排除当前最大、最小salary之后的员工的平均工资avg_salary
注意:不能写作 salary!=MAX(salary) 必须通过SELECT MAX() FROM 返回结果 因为不可以在where语句里面直接使用聚合函数 要用子查询包裹聚合函数
--不能写作 salary!=MAX(salary) 必须通过SELECT MAX() FROM 返回结果 因为不可以在where语句里面直接使用聚合函数 要用子查询包裹聚合函数
SELECT AVG(salary) avg_salary FROM salaries WHERE salary!=(SELECT MAX(salary) FROM salaries)
AND salary!=(SELECT MIN(salary) FROM salaries)
AND to_date = '9999-01-01'
12.使用含有关键字exists查找未分配具体部门的员工的所有信息。
题目描述
使用含有关键字exists查找未分配具体部门的员工的所有信息。
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`));
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`));
输出格式:
emp_no | birth_date | first_name | last_name | gender | hire_date |
---|---|---|---|---|---|
10011 | 1953-11-07 | Mary | Sluis | F | 1990-01-22 |
Exists的用法:
exists对外表用loop逐条查询,每次查询都会查看exists的条件语句,当 exists里的条件语句能够返回记录行时(无论记录行是的多少,只要能返回
),条件就为真,返回当前loop到的这条记录;反之如果exists里的条 件语句不能返回记录行,则当前loop到的这条记录被丢弃,exists的条件就像一个bool条件,当能返回结果集则为true,不能返回结果集则为 false。
(上面太长了,balabala,看下面^_^)总的来说,如果外表有n条记录,那么exists查询就是将这n条记录逐条取出,然后判断n遍exists条件
如果使用:
1 2 |
|
那么,Exists都会从外表employees里面逐条比对,如,第一条的emp_no = '10001',那么
Exists判断:
1 |
|
可想而知,一定存在不等于10001的结果集。那么上面的查询语句其实也就等效于:
1 |
|
SELECT * FROM employees WHERE NOT EXISTS
(SELECT emp_no FROM dept_emp WHERE employees.emp_no=emp_no)
13.统计salary的累计和running_total
题目描述
按照salary的累计和running_total,其中running_total为前两个员工的salary累计和,其他以此类推。 具体结果如下Demo展示。。
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 | running_total |
---|---|---|
10001 | 88958 | 88958 |
10002 | 72527 | 161485 |
10003 | 43311 | 204796 |
10004 | 74057 | 278853 |
10005 | 94692 | 373545 |
10006 | 43311 | 416856 |
10007 | 88070 | 504926 |
10009 | 95409 | 600335 |
10010 | 94409 | 694744 |
10011 | 25828 | 720572 |
--用子查询复用一张表
SELECT s1.emp_no,s1.salary,(SELECT SUM(s2.salary) FROM salaries s2 WHERE s2.emp_no<=s1.emp_no AND s2.to_date = '9999-01-01') running_total
FROM salaries s1
WHERE s1.to_date = '9999-01-01' ORDER BY s1.emp_no
14.对于employees表中,给出奇数行的first_name
题目描述
对于employees表中,给出奇数行的first_name
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`));
输出格式:
first_name |
---|
Georgi |
Chirstian |
Anneke |
Tzvetan |
Saniya |
Mary |
--首先题目的叙述有问题,导致理解有误,输出的数据与参考答案不同。先给出正确的题目叙述:【对于employees表,在对first_name进行排名后,选出奇数排名对应的first_name】。
--复用一张表
SELECT s1.first_name FROM employees s1 WHERE (SELECT COUNT(*) FROM employees s2 WHERE s2.first_name<=s1.first_name)%2==1
15.获取有奖金的员工相关信息。
题目描述
获取有奖金的员工相关信息。
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`));
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 emp_bonus(
emp_no int not null,
recevied datetime not null,
btype smallint not null);
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、first_name、last_name、奖金类型btype、对应的当前薪水情况salary以及奖金金额bonus。 bonus类型btype为1其奖金为薪水salary的10%,btype为2其奖金为薪水的20%,其他类型均为薪水的30%。 当前薪水表示to_date='9999-01-01'
输出格式:
emp_no | first_name | last_name | btype | salary | bonus |
---|---|---|---|---|---|
10001 | Georgi | Facello | 1 | 88958 | 8895.8 |
10002 | Bezalel | Simmel | 2 | 72527 | 14505.4 |
10003 | Parto | Bamford | 3 | 43311 | 12993.3 |
10004 | Chirstian | Koblick | 1 | 74057 | 7405.7 |
主要掌握
CASE 变量
WHEN 变量取值 THEN 执行操作
WHEN 变量取值 THEN 执行操作
...
ELSE 执行操作
END
SELECT e.emp_no,e.first_name,e.last_name,eb.btype,s.salary,(CASE eb.btype
WHEN 1 THEN s.salary*0.1
WHEN 2 THEN s.salary*0.2
ELSE s.salary*0.3
END
) bonus FROM
employees e INNER JOIN salaries s ON e.emp_no=s.emp_no
INNER JOIN emp_bonus eb ON e.emp_no=eb.emp_no
WHERE s.to_date='9999-01-01'