二、SQL顺序
SQL语句的书写顺序
SELECT DISTINCT
<select_list> -- 选择的字段
FROM <left_table> -- 连接的左表表名
<join_type> JOIN <right_table> -- 连接类型 连接的右表表名
ON <Join_condition> -- 表连接的等式条件
WHERE <where_condition> -- 表取数筛选条件
GROUP BY <group_by_list> -- 分组字段
Having <having_condition> -- 分组后统计值
ORDER BY <order_by_condition> -- 排序顺序
LIMIT <limit_number> -- 最终显示数据的条数
举个栗子:正式员工数大于10人的部分 按照人数倒序显示显示前10行的部门、部门人数
SELECT department,COUNT(DISTINCT employee_id) AS cnt
FROM emoployee_table
WHERE type='正式员工'
GROUP BY department
HAVING cnt>10
ORDER BY cnt DESC -- 默认升序 倒序的话可以使用DESC
LIMIT 10;
Q1:where后面的条件与having后面的条件有啥不一样?
A1:where是直接取表的部分数据的条件筛选,比如取正式员工,是在Group By之前执行的操作;Having后面的条件是针对衍生计算的新字段进行的条件筛选,是GROUP BY执行完后在进行筛选,看下面的执行顺序,与书写顺序是不一致的。
SQL语句的执行顺序
FROM <left_table>
ON <join_condition>
<join_type> JOIN <right_table>
WHERE <where_condition>
GROUP BY <group_by_list>
HAVING <having_condition>
SELECT
DISTINCT <select_list>
ORDER BY <order_by_condition>
LIMIT <limit_number>
三、SQL的七种连接
表A和表B进行连接
1.左连接
{ x ∣ x ∈ A } \{x|x\in{A} \} {x∣x∈A}
SELECT <select_list>
FROM A
LEFT JOIN B
ON A.Key=B.Key
简单例子可以参考:https://blog.csdn.net/zhaihuilin0986/article/details/88688733
2. 右连接
{ x ∣ x ∈ B } \{x|x\in{B} \} {x∣x∈B}
SELECT <select_list>
FROM A
RIGHT JOIN B
ON A.Key=B.Key
3.内连接
{ x ∣ x ∈ A ∩ B } \{x|x\in{A\cap B}\} {x∣x∈A∩B}
SELECT <select_list>
FROM A
INNER JOIN B
ON A.Key=B.Key
4.全连接
{ x ∣ x ∈ A ∪ B } \{x|x\in{A\cup B}\} {x∣x∈A∪B}
SELECT <select_list>
FROM A
FULL OUTER JOIN B
ON A.Key=B.Key
MySQL不支持上述语法,可以借助UNION
SELECT <select_list>
FROM A
LEFT JOIN B
ON A.Key=B.Key
UNION
SELECT <select_list>
FROM A
RIGHT JOIN B
ON A.Key=B.Key
5.左异
{ x ∣ x ∈ A & x ∉ B } \{x|x\in{A} \ \& \ x \notin B \} {x∣x∈A & x∈/B}
SELECT <select_list>
FROM A
LEFT JOIN B
WHERE B.Key IS NULL
6.右异
{ x ∣ x ∈ B & x ∉ A } \{x|x\in{B} \ \& \ x \notin A \} {x∣x∈B & x∈/A}
SELECT <select_list>
FROM A
RIGHT JOIN B
WHERE A.Key IS NULL
7.各自独有并
{ x ∣ x ∈ A ∪ B − A ∩ B } \{x|x\in{A\cup B-A\cap B} \} {x∣x∈A∪B−A∩B}
SELECT <select_list>
FROM A
FULLER OUTER JOIN B
ON A.Key=B.Key
WHERE A.Key IS NULL
OR B.Key IS NULL
四、LeetCode SQL
175. Combine Two Tables
很基础的两表左连接
SELECT FirstName,LastName,City,State FROM Person LEFT JOIN Address on Person.PersonId=Address.PersonId
176. Second Highest Salary
将不同工资水平降序后选择第二个即可,LIMIT的使用最关键
SELECT
(SELECT DISTINCT
Salary
FROM Employee
ORDER BY Salary DESC
LIMIT 1 OFFSET 1) AS SecondHighestSalary
这里用于分页的limit的用法:
limit X,Y表示跳过前X条数据(X是表记录索引号,索引号从0开始计数,从第X+1条开始读取),读取Y个数据
limit5,5表示从第6条数据开始读取,共读取5条,即第6-10条数据(索引号为5-9)
limit X offset Y表示跳过前Y个数据,从Y+1开始读取X条数据。
177. Nth Highest Salary
使用函数
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
SET N = N-1;
RETURN (
select distinct Salary from Employee group by Salary order by Salary desc limit 1 offset N
);
END
SELECT s.Score, r.Rank
FROM Scores AS s
LEFT JOIN
(SELECT @n := @n + 1 AS Rank, a.Score FROM (SELECT DISTINCT Score FROM Scores ORDER BY Score DESC) a, (SELECT @n := 0) b) AS r
on s.Score=r.Score
ORDER BY Rank;
180. Consecutive Numbers
三表自身连接
SELECT DISTINCT l1.Num as ConsecutiveNums
FROM
Logs l1,
Logs l2,
logs l3
WHERE
l1.Id=l2.Id-1
AND l2.Id=l3.Id-1
AND l1.Num=l2.Num
AND l2.Num=l3.Num
;
181. Employees Earning More Than Their Managers
SELECT
a.Name AS 'Employee'
FROM
Employee AS a,
Employee AS b
WHERE
a.ManagerId = b.Id
AND a.Salary > b.Salary
;
SELECT Email FROM Person group by Email having count(Email)>1;
183. Customers Who Never Order
SELECT customers.name as Customers
FROM customers
WHERE customers.id
NOT IN (SELECT customerid FROM orders);
184. Department Highest Salary
select Department.name AS 'Department',
Employee.name AS 'Employee',
Salary
from Employee join department on Employee.DepartmentId=Department.Id
where (Employee.DepartmentId,Salary) IN
(
SELECT DepartmentId,MAX(Salary)
FROM Employee GROUP BY DepartmentId
);
185. Department Top Three Salaries
SELECT
d.name as "Department",
e1.Name as "Employee",
e1.Salary as "Salary"
FROM
Employee e1
JOIN Employee e2 JOIN Department d
WHERE
e1.DepartmentID=e2.DepartmentId
AND e1.Salary<=e2.Salary AND d.id=e2.DepartmentId
GROUP BY d.name,e1.id
HAVING COUNT(DISTINCT(e2.Salary))<=3
ORDER BY d.name,salary desc
196. Delete Duplicate Emails
我的思路:按照邮件地址分组找出各组最小的id(临时表),然后将原表中id不属于临时表的删除
delete from person
where id not in
(select min(id) as id
from person
group by email) ;
但这个会报错You can’t specify target table ‘person’ for update in FROM clause,解决方法是再多加一层select查询
delete from person
where id not in
(select id
from (
select min(id) as id
from person
group by email) as t1);
197. Rising Temperature
这个使用to_days函数将日期类型转为实数来判断相近两天
两表自连接
select w1.id from weather w1, weather w2
where to_days(w1.RecordDate) = to_days (w2.RecordDate) + 1
and w1.Temperature > w2.Temperature;
SELECT
Request_at AS Day,
CONVERT(
SUM( CASE WHEN Trips.status LIKE 'cancelled_by_%' THEN 1 ELSE 0 END)/COUNT(*),
DECIMAL(10,2)
) AS 'Cancellation Rate'
FROM
Trips
WHERE
Client_Id not in (SELECT Users_Id from Users WHERE Users.Banned='Yes')
AND
Driver_Id not in (SELECT Users_Id from Users WHERE Users.Banned='Yes')
AND
Request_at BETWEEN '2013-10-01' AND '2013-10-03'
GROUP BY Request_at
SELECT name,population,area
FROM World
WHERE area>3000000 OR population>25000000
SELECT id,movie,description,rating
FROM cinema
WHERE description <> 'boring' and id%2=1
ORDER BY rating DESC
626. Exchange Seats
逻辑判断
语法一:
CASE expression
WHEN value1 THEN returnvalue1
WHEN value2 THEN returnvalue2
WHEN value3 THEN returnvalue3
……
ELSE defaultreturnvalue
END
语法二:
CASE
WHEN condition1 THEN returnvalue1
WHEN condition 2 THEN returnvalue2
WHEN condition 3 THEN returnvalue3
……
ELSE defaultreturnvalue
END
语法三:
if(exp1,exp2,exp3)
该问题核心代码
CASE
WHEN MOD(id, 2) != 0 AND seat_counts.counts != id THEN id + 1
WHEN MOD(id, 2) != 0 AND seat_counts.counts = id THEN id
ELSE id - 1
END
详细代码:
SELECT
(CASE
WHEN MOD(id, 2) != 0 AND seat_counts.counts != id THEN id + 1
WHEN MOD(id, 2) != 0 AND seat_counts.counts = id THEN id
ELSE id - 1
END) AS id,
student
FROM
seat,
(SELECT
COUNT(*) AS counts
FROM
seat) AS seat_counts
ORDER BY id ASC;
select *
from employees
where hire_date=(
select max(hire_date)
from employees)
还有一种解法看起来不太科学
SELECT *
FROM employees
ORDER BY hire_date DESC
limit 1
SELECT *
FROM employees
WHERE hire_date=(SELECT DISTINCT(hire_date)
FROM employees
ORDER BY hire_date DESC
limit 2,1)
3.查找各个部门当前(to_date=‘9999-01-01’)领导当前薪水详情以及其对应部门编号dept_no
SELECT S.*,D.dept_no
FROM salaries S
LEFT JOIN dept_manager D
on D.emp_no=S.emp_no
WHERE S.to_date="9999-01-01" and D.to_date="9999-01-01"
4.查找所有已经分配部门的员工的last_name和first_name
SELECT E.last_name,E.first_name,D.dept_no
FROM employees E
inner JOIN dept_emp D
ON D.emp_no=E.emp_no
5.查找所有已经分配部门的员工的last_name和first_name
SELECT E.last_name,E.first_name,D.dept_no
FROM employees E
inner JOIN dept_emp D
ON D.emp_no=E.emp_no
6.查找所有员工的last_name和first_name以及对应部门编号dept_no,也包括展示没有分配具体部门的员工
SELECT E.last_name,E.first_name,D.dept_no
FROM employees E
LEFT JOIN dept_emp D
ON D.emp_no=E.emp_no
[7.查找薪水涨幅超过15次的员工号emp_no以及其对应的涨幅次数t]
SELECT emp_no, COUNT(emp_no) AS t FROM salaries
GROUP BY emp_no HAVING t > 15
[8.找出所有员工当前(to_date=‘9999-01-01’)具体的薪水salary情况,对于相同的薪水只显示一次,并按照逆序显示]
SELECT DISTINCT salary
FROM salaries
WHERE to_date='9999-01-01'
ORDER BY salary DESC
[9.获取所有部门当前manager的当前薪水情况,给出dept_no, emp_no以及salary,当前表示to_date=‘9999-01-01’]
SELECT D.dept_no,D.emp_no,S.salary
FROM dept_manager AS D
INNER JOIN salaries AS S
ON D.emp_no=S.emp_no and S.to_date=D.to_date
WHERE D.to_date="9999-01-01"
[10.获取所有非manager的员工emp_no]
SELECT E.emp_no
FROM employees E
WHERE E.emp_no NOT IN
(
SELECT emp_no
FROM dept_manager D
)
[11.获取所有员工当前的manager,如果当前的manager是自己的话结果不显示,当前表示to_date=‘9999-01-01’。结果第一列给出当前员工的emp_no,第二列给出其manager对应的manager_no。]
SELECT E.emp_no,D.emp_no AS manager_no
FROM dept_emp AS E INNER JOIN dept_manager AS D
ON E.dept_no=D.dept_no
WHERE D.to_date='9999-01-01' and E.to_date='9999-01-01' and E.emp_no<>D.emp_no
[12.获取所有部门中当前员工薪水最高的相关信息,给出dept_no, emp_no以及其对应的salary]
SELECT D.dept_no,S.emp_no,MAX(S.salary) AS salary
FROM salaries S
INNER JOIN dept_emp D
on S.emp_no=D.emp_no
WHERE D.to_date="9999-01-01" and S.to_date="9999-01-01"
GROUP BY D.dept_no
[13.从titles表获取按照title进行分组,每组个数大于等于2,给出title以及对应的数目t。]
SELECT title,COUNT(*) AS t
FROM titles
GROUP BY title
HAVING t>=2
[14.从titles表获取按照title进行分组,每组个数大于等于2,给出title以及对应的数目t。注意对于重复的emp_no进行忽略。]
SELECT title,COUNT(DISTINCT emp_no) AS t
FROM titles
GROUP BY title
HAVING t>=2
[15.查找employees表所有emp_no为奇数,且last_name不为Mary的员工信息,并按照hire_date逆序排列]
SELECT *
FROM employees
WHERE last_name<>"Mary" and emp_no%2=1
ORDER BY hire_date DESC
[16.统计出当前各个title类型对应的员工当前(to_date=‘9999-01-01’)薪水对应的平均工资。结果给出title以及平均工资avg。]
SELECT T.title AS title,AVG(S.salary) AS avg
FROM salaries S
INNER JOIN titles T
ON S.emp_no=T.emp_no
WHERE S.to_date="9999-01-01" AND T.to_date="9999-01-01"
GROUP BY T.title
[17.获取当前(to_date=‘9999-01-01’)薪水第二多的员工的emp_no以及其对应的薪水salary]
select emp_no,salary
from salaries
where to_date="9999-01-01" and salary=
( select distinct S.salary from salaries S order by S.salary desc limit 1,1)
[18.查找当前薪水(to_date=‘9999-01-01’)排名第二多的员工编号emp_no、薪水salary、last_name以及first_name,不准使用order by]
SELECT E.emp_no,MAX(S.salary) AS salary,E.last_name,E.first_name
FROM employees E
INNER JOIN salaries S
on E.emp_no=S.emp_no
WHERE S.to_date='9999-01-01'
and S.salary NOT IN (SELECT MAX(salary) FROM salaries WHERE to_date='9999-01-01')
[19.查找所有员工的last_name和first_name以及对应的dept_name,也包括暂时没有分配部门的员工]
SELECT E.last_name,E.first_name,D.dept_name
FROM employees E
Left JOIN dept_emp DE
ON E.emp_no=DE.emp_no
LEFT JOIN departments D
ON D.dept_no=DE.dept_no
[20.查找员工编号emp_no为10001其自入职以来的薪水salary涨幅值growth]
SELECT MAX(salary)-MIN(salary) AS growth
FROM salaries
WHERE emp_no=10001
[21.查找所有员工自入职以来的薪水涨幅情况,给出员工编号emp_no以及其对应的薪水涨幅growth,并按照growth进行升序]
SELECT StartSalary.emp_no,EndSalary.salary-StartSalary.salary AS growth
FROM
(SELECT E.emp_no AS emp_no,S.salary As salary
FROM employees E
INNER JOIN salaries S
on E.emp_no=S.emp_no and E.hire_date=S.from_date) AS StartSalary
INNER JOIN
(SELECT E.emp_no AS emp_no,S.salary AS salary
FROM employees E
INNER JOIN salaries S
on E.emp_no=S.emp_no and S.to_date = '9999-01-01') AS EndSalary
on StartSalary.emp_no=EndSalary.emp_no
ORDER BY growth
[22.统计各个部门对应员工涨幅的次数总和,给出部门编码dept_no、部门名称dept_name以及次数sum]
SELECT DE.dept_no,D.dept_name,COUNT(S.salary) AS sum
FROM salaries S
LEFT JOIN dept_emp DE
ON S.emp_no=DE.emp_no
INNER JOIN departments D
on D.dept_no=DE.dept_no
GROUP BY D.dept_no
[23.对所有员工的当前(to_date=‘9999-01-01’)薪水按照salary进行按照1-N的排名,相同salary并列且按照emp_no升序排列]
select S1.emp_no,S1.salary,count(distinct S2.salary) rank
from salaries S1, salaries S2
where S1.salary <= S2.salary and S1.to_date = '9999-01-01' and S2.to_date = '9999-01-01'
group by S1.emp_no
order by rank;
[24.获取所有非manager员工当前的薪水情况,给出dept_no、emp_no以及salary ,当前表示to_date=‘9999-01-01’]
SELECT DE.dept_no,E.emp_no,S.salary
FROM employees E
LEFT JOIN salaries S
on S.emp_no=E.emp_no
LEFT JOIN dept_emp DE
on DE.emp_no=S.emp_no
WHERE S.to_date='9999-01-01' AND DE.to_date='9999-01-01'
AND E.emp_no not in (SELECT DM.emp_no FROM dept_manager DM WHERE DM.to_date='9999-01-01')
[25,获取员工其当前的薪水比其manager当前薪水还高的相关信息,当前表示to_date=‘9999-01-01’,结果第一列给出员工的emp_no,第二列给出其manager的manager_no,第三列给出该员工当前的薪水emp_salary,第四列给该员工对应的manager当前的薪水manager_salary]
SELECT S1.emp_no,DM.emp_no AS manager_no,S1.salary AS emp_salary,S2.salary AS manager_salary
FROM salaries S1,dept_emp DE,dept_manager DM,salaries S2
WHERE S1.emp_no=DE.emp_no and DE.dept_no=DM.dept_no
and DM.emp_no=S2.emp_no and S1.salary>s2.salary
and S1.to_date='9999-01-01' and DE.to_date='9999-01-01' and DM.to_date='9999-01-01'
and S2.to_date='9999-01-01'
[26,汇总各个部门当前员工的title类型的分配数目,结果给出部门编号dept_no、dept_name、其当前员工所有的title以及该类型title对应的数目count]
SELECT D.dept_no,D.dept_name,T.title,COUNT(T.title)
FROM departments D,dept_emp DE,titles T
WHERE D.dept_no=DE.dept_no AND DE.emp_no=T.emp_no
and DE.to_date='9999-01-01' and T.to_date='9999-01-01'
GROUP BY DE.dept_no, T.title
[27,给出每个员工每年薪水涨幅超过5000的员工编号emp_no、薪水变更开始日期from_date以及薪水涨幅值salary_growth,并按照salary_growth逆序排列。]
SELECT s2.emp_no,s2.from_date,(s2.salary - s1.salary) AS salary_growth
FROM salaries AS s1,salaries AS s2
WHERE s1.emp_no=s2.emp_no
AND salary_growth > 5000
AND strftime('%Y',s2.to_date) - strftime('%Y',s1.to_date) = 1
ORDER BY salary_growth DESC;
[28.查找描述信息中包括robot的电影对应的分类名称以及电影数目,而且还需要该分类对应电影数量>=5部]
这个问题需要注意的是先要筛选出电影数大于等于5的电影品类id,再
SELECT C.name,COUNT(FC.film_id) AS t
FROM film F
INNER JOIN film_category FC ON F.film_id=FC.film_id
INNER JOIN category C ON C.category_id=FC.category_id
WHERE F.description like '%robot%' and FC.category_id in
(SELECT category_id
FROM film_category
GROUP BY category_id
HAVING COUNT(film_id)>=5)
GROUP BY FC.category_id #筛选电影数大于5的电影品类的品类id
[29.使用join查询方式找出没有分类的电影id以及名称]
SELECT F.film_id,F.title
FROM film F
WHERE F.film_id not in
(
SELECT F1.film_id
FROM film F1
INNER JOIN film_category FC ON F1.film_id=Fc.film_id
)
[30.使用子查询的方式找出属于Action分类的所有电影对应的title,description]
SELECT F.title,F.description
FROM film F
WHERE F.film_id in
(SELECT F1.film_id
FROM film F1
INNER JOIN film_category FC on F1.film_id=FC.film_id
INNER JOIN category C ON C.category_id=FC.category_id
WHERE C.name='Action'
)
[31.获取select * from employees对应的执行计划]
explain select * from employees;
[32.将employees表的所有员工的last_name和first_name拼接起来作为Name,中间以一个空格区分]
SELECT last_name || " "|| first_name
AS Name
FROM employees
[33.创建一个actor表]
CREATE TABLE IF NOT EXISTS actor(
actor_id smallint(5) NOT NULL,
first_name varchar(45) NOT NULL,
last_name varchar(45) NOT NULL,
last_update timestamp NOT NULL default (datetime('now','localtime')),
PRIMARY KEY (actor_id)
);
[34.对于表actor批量插入数据]
insert into actor(actor_id,first_name,last_name,last_update)
values (1,"PENELOPE","GUINESS","2006-02-15 12:34:33"),(2,"NICK","WAHLBERG","2006-02-15 12:34:33")
[35.对于表actor批量插入如下数据,如果数据已经存在,请忽略,不使用replace操作]
INSERT or IGNORE INTO actor
VALUES ('3','ED','CHASE','2006-02-15 12:34:33');
[36.创建一个actor_name表,将actor表中的所有first_name以及last_name导入该表]
CREATE TABLE actor_name (
first_name varchar(45) NOT NULL,
last_name varchar(45) NOT NULL
);
INSERT INTO actor_name SELECT first_name, last_name FROM actor;
[37.针对如下表actor结构创建索引:
CREATE TABLE IF NOT EXISTS actor (
actor_id smallint(5) NOT NULL PRIMARY KEY,
first_name varchar(45) NOT NULL,
last_name varchar(45) NOT NULL,
last_update timestamp NOT NULL DEFAULT (datetime(‘now’,‘localtime’)))
对first_name创建唯一索引uniq_idx_firstname,对last_name创建普通索引idx_lastname]
create unique index uniq_idx_firstname on actor(first_name);
create index idx_lastname on actor(last_name);
[38.针对actor表创建视图actor_name_view,只包含first_name以及last_name两列,并对这两列重新命名,first_name为first_name_v,last_name修改为last_name_v:
CREATE TABLE IF NOT EXISTS actor (
actor_id smallint(5) NOT NULL PRIMARY KEY,
first_name varchar(45) NOT NULL,
last_name varchar(45) NOT NULL,
last_update timestamp NOT NULL DEFAULT (datetime(‘now’,‘localtime’)))]
create view actor_name_view (first_name_v,last_name_v) as
select first_name,last_name from actor
[39.针对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_no on salaries(emp_no);]
select * from salaries
indexed by idx_emp_no
where emp_no = '10005'
[40.存在actor表,包含如下列信息:
CREATE TABLE IF NOT EXISTS actor (
actor_id smallint(5) NOT NULL PRIMARY KEY,
first_name varchar(45) NOT NULL,
last_name varchar(45) NOT NULL,
last_update timestamp NOT NULL DEFAULT (datetime(‘now’,‘localtime’)));
现在在last_update后面新增加一列名字为create_date, 类型为datetime, NOT NULL,默认值为’0000 00:00:00’]
ALTER TABLE actor
ADD COLUMN create_date datetime NOT NULL DEFAULT '0000-00-00 00:00:00';
[41.构造一个触发器audit_log,在向employees_test表中插入一条数据的时候,触发插入相关的数据到audit中。
CREATE TABLE employees_test(
ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(50),
SALARY REAL
);
CREATE TABLE audit(
EMP_no INT NOT NULL,
NAME TEXT NOT NULL
);]
CREATE TRIGGER audit_log AFTER INSERT ON employees_test
BEGIN
INSERT INTO audit VALUES (NEW.ID, NEW.NAME);
END;
[42.删除emp_no重复的记录,只保留最小的id对应的记录。
CREATE TABLE IF NOT EXISTS titles_test (
id int(11) not null primary key,
emp_no int(11) NOT NULL,
title varchar(50) NOT NULL,
from_date date NOT NULL,
to_date date DEFAULT NULL);
insert into titles_test values (‘1’, ‘10001’, ‘Senior Engineer’, ‘1986-06-26’, ‘9999-01-01’),
(‘2’, ‘10002’, ‘Staff’, ‘1996-08-03’, ‘9999-01-01’),
(‘3’, ‘10003’, ‘Senior Engineer’, ‘1995-12-03’, ‘9999-01-01’),
(‘4’, ‘10004’, ‘Senior Engineer’, ‘1995-12-03’, ‘9999-01-01’),
(‘5’, ‘10001’, ‘Senior Engineer’, ‘1986-06-26’, ‘9999-01-01’),
(‘6’, ‘10002’, ‘Staff’, ‘1996-08-03’, ‘9999-01-01’),
(‘7’, ‘10003’, ‘Senior Engineer’, ‘1995-12-03’, ‘9999-01-01’);]
delete from titles_test
where id not in (select min(id) from titles_test)
[43.将所有to_date为9999-01-01的全部更新为NULL,且 from_date更新为2001-01-01。
CREATE TABLE IF NOT EXISTS titles_test (
id int(11) not null primary key,
emp_no int(11) NOT NULL,
title varchar(50) NOT NULL,
from_date date NOT NULL,
to_date date DEFAULT NULL);
insert into titles_test values (‘1’, ‘10001’, ‘Senior Engineer’, ‘1986-06-26’, ‘9999-01-01’),
(‘2’, ‘10002’, ‘Staff’, ‘1996-08-03’, ‘9999-01-01’),
(‘3’, ‘10003’, ‘Senior Engineer’, ‘1995-12-03’, ‘9999-01-01’),
(‘4’, ‘10004’, ‘Senior Engineer’, ‘1995-12-03’, ‘9999-01-01’),
(‘5’, ‘10001’, ‘Senior Engineer’, ‘1986-06-26’, ‘9999-01-01’),
(‘6’, ‘10002’, ‘Staff’, ‘1996-08-03’, ‘9999-01-01’),
(‘7’, ‘10003’, ‘Senior Engineer’, ‘1995-12-03’, ‘9999-01-01’);]
update titles_test set to_date=null,from_date='2001-01-01' where to_date='9999-01-01';
[44.将id=5以及emp_no=10001的行数据替换成id=5以及emp_no=10005,其他数据保持不变,使用replace实现。
CREATE TABLE IF NOT EXISTS titles_test (
id int(11) not null primary key,
emp_no int(11) NOT NULL,
title varchar(50) NOT NULL,
from_date date NOT NULL,
to_date date DEFAULT NULL);
]
REPLACE INTO titles_test VALUES (5, 10005, 'Senior Engineer', '1986-06-26', '9999-01-01')
[45.将titles_test表名修改为titles_2017。
CREATE TABLE IF NOT EXISTS titles_test (
id int(11) not null primary key,
emp_no int(11) NOT NULL,
title varchar(50) NOT NULL,
from_date date NOT NULL,
to_date date DEFAULT NULL);]
alter table titles_test rename to titles_2017
[46.在audit表上创建外键约束,其emp_no对应employees_test表的主键id。
CREATE TABLE employees_test(
ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(50),
SALARY REAL
);
CREATE TABLE audit(
EMP_no INT NOT NULL,
create_date datetime NOT NULL
);]
DROP TABLE audit;
CREATE TABLE audit(
EMP_no INT NOT NULL,
create_date datetime NOT NULL,
FOREIGN KEY(emp_no) REFERENCES employees_test(id));
[47.存在如下的视图:
create view emp_v as select * from employees where emp_no >10005;
如何获取emp_v和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
));]
SELECT em.* FROM employees AS em, emp_v AS ev WHERE em.emp_no = ev.emp_no
[48.将所有获取奖金的员工当前的薪水增加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 = salary*1.1
where emp_no in (select emp_no from emp_bonus) -- 子查询得到获取奖金的员工id
[49.针对库中的所有表生成select count(*)对应的SQL语句]
select "select count(*) from "||name|| ";" as cnts from sqlite_master
where type='table'
[50.将employees表中的所有员工的last_name和first_name通过(’)连接起来。]
SELECT last_name || "'"|| first_name
AS Name
FROM employees
[51.查找字符串’10,A,B’ 中逗号’,'出现的次数cnt。]
select length('10,A,B') -length(replace('10,A,B',",",""))
[52.获取Employees中的first_name,查询按照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
));]
SELECT first_name FROM employees
order by substr(first_name,length(first_name)-1)
[53.按照dept_no进行汇总,属于同一个部门的emp_no按照逗号进行连接,结果给出dept_no以及连接出的结果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
));]
SELECT dept_no, group_concat(emp_no) AS employees
FROM dept_emp GROUP BY dept_no;
[54.查找排除当前最大、最小salary之后的员工的平均工资avg_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
));]
SELECT AVG(salary) AS avg_salary
FROM salaries
WHERE salary not in(SELECT MIN(salary) FROM salaries )
AND salary not in (SELECT MAX(salary) FROM salaries )
AND to_date = '9999-01-01'
[55.分页查询employees表,每5行一页,返回第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
));]
SELECT *
FROM employees
LIMIT 5,5
[56.获取所有员工的emp_no、部门编号dept_no以及对应的bonus类型btype和recevied,没有分配具体的员工不显示]
select e.emp_no,de.dept_no,eb.btype,eb.recevied
from employees E
inner join dept_emp DE on E.emp_no=DE.emp_no
left join emp_bonus EB on E.emp_no=EB.emp_no
[57.使用含有关键字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
));]
SELECT *
FROM employees E1
WHERE E1.emp_no not in (
SELECT E.emp_no
FROM employees E
INNER JOIN dept_emp DE
WHERE E.emp_no=DE.emp_no
)
[58.存在如下的视图:
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关键字。]
SELECT em.* FROM employees AS em, emp_v AS ev WHERE em.emp_no = ev.emp_no
[59.获取有奖金的员工相关信息。
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’]
SELECT e.emp_no, e.first_name, e.last_name, b.btype, s.salary,
(CASE b.btype
WHEN 1 THEN s.salary * 0.1
WHEN 2 THEN s.salary * 0.2
ELSE s.salary * 0.3 END) AS bonus
FROM employees AS e INNER JOIN emp_bonus AS b ON e.emp_no = b.emp_no
INNER JOIN salaries AS s ON e.emp_no = s.emp_no AND s.to_date = '9999-01-01'
SELECT e.emp_no, e.first_name, e.last_name, b.btype, s.salary,
(s.salary * b.btype / 10.0) AS bonus
FROM employees AS e INNER JOIN emp_bonus AS b ON e.emp_no = b.emp_no
INNER JOIN salaries AS s ON e.emp_no = s.emp_no AND s.to_date = '9999-01-01'
[60.按照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
));]
select a.emp_no, a.salary, sum(b.salary)
from salaries as a, salaries as b
where b.emp_no <= a.emp_no
and a.to_date = '9999-01-01'
and b.to_date = '9999-01-01'
group by a.emp_no
order by a.emp_no asc
[61.对于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
));]
SELECT e1.first_name FROM
employees e1
WHERE
(SELECT count(*) FROM employees e2
WHERE e1.first_name <=e2.first_name)%2=1;