SQL系列

最新推荐文章于 2024-05-23 18:05:50 发布

To_be_thinking

最新推荐文章于 2024-05-23 18:05:50 发布

阅读量366

点赞数

分类专栏：大数据开发

本文链接：https://blog.csdn.net/To_be_to_thought/article/details/90614050

版权

大数据开发专栏收录该内容

1 篇文章 0 订阅

订阅专栏

二、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\in{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\in{B} \}$

SELECT <select_list>
FROM A
RIGHT JOIN B
ON A.Key=B.Key

3.内连接

$\{x|x\in{A\cap B}\}$

SELECT <select_list>
FROM A
INNER JOIN B
ON A.Key=B.Key

4.全连接

$\{x|x\in{A\cup 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\in{A} \ \& \ x \notin B \}$

SELECT <select_list>
FROM A
LEFT JOIN B
WHERE B.Key IS NULL

6.右异

$\{x|x\in{B} \ \& \ x \notin A \}$

SELECT <select_list>
FROM A
RIGHT JOIN B
WHERE A.Key IS NULL

7.各自独有并

$\{x|x\in{A\cup B-A\cap 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

178. Rank Scores

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
;

182. Duplicate Emails

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;

262. Trips and Users

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

595. Big Countries

SELECT name,population,area
FROM World
WHERE area>3000000 OR population>25000000

620. Not Boring Movies

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;

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

select *
from employees
where hire_date=(
    select max(hire_date)
    from employees)

还有一种解法看起来不太科学

SELECT *
FROM employees
ORDER BY hire_date DESC
limit 1

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

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

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;

To_be_thinking

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
SQL系列

175. Combine Two Tables176. Second Highest Salary177. Nth Highest Salary178. Rank Scores180. Consecutive Numbers181. Employees Earning More Than Their Managers182. Duplicate Emails183. Customer...
复制链接

扫一扫

专栏目录