【躍遷之路】SQL語句刻意練習（最後更新：2018.06.25）

weixin_34127717發表於2018-04-16
SQL

【躍遷之路】SQL語句刻意練習（最後更新：2018.06.25）

@(躍遷之路)專欄

叨叨兩句

  1. 技術的精進不能只是簡單的刷題，而應該是不斷的“刻意”練習
  2. 該系列改版後正式納入【躍遷之路】專欄，持續更新

刻意練習——MySQL

2018.04.02

題目描述

DROP TABLE IF EXISTS test1;
CREATE TABLE test1 (
id int(11) NOT NULL AUTO_INCREMENT,
username varchar(20) NOT NULL,
course varchar(20) NOT NULL,
score bigint(20) NOT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;

INSERT INTO test1 VALUES ('1', '張三', '數學', '34');
INSERT INTO test1 VALUES ('2', '張三', '語文', '44');
INSERT INTO test1 VALUES ('3', '張三', '英語', '54');
INSERT INTO test1 VALUES ('4', '李四', '數學', '134');
INSERT INTO test1 VALUES ('5', '李四', '語文', '144');
INSERT INTO test1 VALUES ('6', '李四', '英語', '154');
INSERT INTO test1 VALUES ('7', '王五', '數學', '234');
INSERT INTO test1 VALUES ('8', '王五', '語文', '244');
INSERT INTO test1 VALUES ('9', '王五', '英語', '254');

查出以下結果

法1

SELECT
    A.username,A.score as '數學',B.score as '語文',C.score as '英語'
FROM 
(select username,course,score from test1 where course = '數學') A,
(select username,course,score from test1 where course = '語文') B,
(select username,course,score from test1 where course = '英語') C
WHERE
    A.username = B.username
and B.username = C.username

法2【推薦】

select
    username,sum(case course when '數學' then score else 0 end ) as '數學',
sum(case course when '語文' then score else 0 end ) as '語文',
sum(case course when '英語' then score else 0 end ) as '英語'
FROM
    test1
group by username

2018.04.03

題目描述

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

2018.04.04

由於檢視 emp_v 的記錄是從 employees 中匯出的，所以要判斷兩者中相等的資料，只需要判斷emp_no相等即可。
方法一：用 WHERE 選取二者 emp_no 相等的記錄

SELECT em.* FROM employees AS em, emp_v AS ev WHERE em.emp_no = ev.emp_no
方法二：用 INTERSECT 關鍵字求 employees 和 emp_v 的交集
可參考：http://www.sqlite.org/lang_select.html

SELECT * FROM employees INTERSECT SELECT * FROM emp_v
方法三：仔細一想，emp_v的全部記錄均由 employees 匯出，因此可以投機取巧，直接輸出 emp_v 所有記錄

SELECT * FROM emp_v
【錯誤方法：】用以下方法直接輸出 *，會得到兩張表中符合條件的重複記錄，因此不合題意，必須在 * 前加表名作限定

SELECT * FROM employees, emp_v WHERE employees.emp_no = emp_v.emp_no

2018.04.05

題目描述
將所有獲取獎金的員工當前的薪水增加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 s.emp_no FROM salaries AS s INNER JOIN emp_bonus AS eb 
ON s.emp_no = eb.emp_no AND s.to_date = '9999-01-01')

2018.04.06

題目描述
針對庫中的所有表生成select count(*)對應的SQL語句
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 emp_bonus(
emp_no int not null,
recevied datetime not null,
btype smallint not null);
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));

輸出格式:
cnts
select count(*) from employees;
select count(*) from departments;
select count(*) from dept_emp;
select count(*) from dept_manager;
select count(*) from salaries;
select count(*) from titles;
select count(*) from emp_bonus;

本題主要有以下兩個關鍵點：
1、在 SQLite 系統表 sqlite_master 中可以獲得所有表的索引，其中欄位 name 是所有表的名字，而且對於自己建立的表而言，欄位 type 永遠是 'table'，詳情可參考：
http://blog.csdn.net/xingfeng0501/article/details/7804378

2、在 SQLite 中用 “||” 符號連線字串
SELECT "select count(*) from " || name || ";" AS cnts
FROM sqlite_master WHERE type = 'table'

3.mysql使用concat進行字串拼接

2018.04.07

題目描述
獲取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));

輸出格式：
first_name
Chirstian
Tzvetan
Bezalel
Duangkaew
Georgi
Kyoichi
Anneke
Sumant
Mary
Parto
Saniya

本題考查 substr(X,Y,Z) 或 substr(X,Y) 函式的使用。其中X是要擷取的字串。Y是字串的起始位置（注意第一個字元的位置為1，而不為0），取值範圍是±(1~length(X))，當Y等於length(X)時，則擷取最後一個字元；當Y等於負整數-n時，則從倒數第n個字元處擷取。Z是要擷取字串的長度，取值範圍是正整數，若Z省略，則從Y處一直擷取到字串末尾；若Z大於剩下的字串長度，也是擷取到字串末尾為止。

SELECT first_name FROM employees ORDER BY substr(first_name,length(first_name)-1) 

SELECT first_name FROM employees ORDER BY substr(first_name,-2)

2018.04.08

本題要用到SQLite的聚合函式group_concat(X,Y)，其中X是要連線的欄位，Y是連線時用的符號，可省略，預設為逗號。此函式必須與 GROUP BY 配合使用。此題以 dept_no 作為分組，將每個分組中不同的emp_no用逗號連線起來（即可省略Y）。可參考：
http://www.sqlite.org/lang_aggfunc.html#groupconcat
http://blog.csdn.net/langzxz/article/details/16807859

SELECT dept_no, group_concat(emp_no) AS employees
FROM dept_emp GROUP BY dept_no

2018.04.09

本題邏輯有問題，在挑選當前最大、最小salary時沒加 to_date = '9999-01-01' 作條件限制，導致挑選出來的是全表最大、最小salary，然後對除去這兩個salary再作條件限制 to_date = '9999-01-01' ，求平均薪水，此時求出的平均薪水與題目邏輯要求的不同。

SELECT AVG(salary) AS avg_salary FROM salaries 
WHERE to_date = '9999-01-01' 
AND salary NOT IN (SELECT MAX(salary) FROM salaries)
AND salary NOT IN (SELECT MIN(salary) FROM salaries)
正確的邏輯應如下所示，但在本題OJ系統中通不過：

SELECT AVG(salary) AS avg_salary FROM salaries 
WHERE to_date = '9999-01-01' 
AND salary NOT IN (SELECT MAX(salary) FROM salaries WHERE to_date = '9999-01-01')
AND salary NOT IN (SELECT MIN(salary) FROM salaries WHERE to_date = '9999-01-01')

2018.04.10

題目描述
分頁查詢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

2018.04.11

本題用 EXISTS 關鍵字的方法如下：意為在 employees 中挑選出令(SELECT emp_no FROM dept_emp WHERE emp_no = employees.emp_no)不成立的記錄，即當 employees.emp_no=10011時。反之，把NOT去掉，則輸出 employees.emp_no=10001~10010時的記錄。

SELECT * FROM employees WHERE NOT EXISTS 
(SELECT emp_no FROM dept_emp WHERE emp_no = employees.emp_no)

2018.4.12

select ev.* from emp_v ev,employees e where ev.emp_no = e.emp_no

本題主要考查 SQLite 中 CASE 表示式的用法。即當 btype = 1 時，得到 salary * 0.1；當 btype = 2 時，得到 salary * 0.2；其他情況得到 salary * 0.3。詳細用法請參考：
http://www.sqlite.org/lang_expr.html 中的【The CASE expression】
http://www.2cto.com/database/201202/120267.html 中的【條件表示式】

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'
其實觀察測試資料會發現 btype 只有1,2,3三種情況，即使不會 CASE 表示式，也能運用四則運算解出：（注意要除以10.0，如果除以10的話，結果的小數位會被捨去）

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'

2018.4.13

首先題目的敘述有問題，導致理解有誤，輸出的資料與參考答案不同。先給出正確的題目敘述：【對於employees表，在對first_name進行排名後，選出奇數排名對應的first_name】。
1、本題用到了三層 SELECT 查詢，為了便於理解，採用縮排方式分層，且最外層對應e1，最內層對應e3；
2、在e3層中，採用 COUNT() 函式對 e2.first_name 進行排名標號，即在給定 e2.first_name的情況下，不大於 e2.first_name 的 e3.first_name 的個數有多少，該個數剛好與 e2.first_name 的排名標號匹配，且將該值命名為 rowid；
/*注意：排名標號後並未排序，即[Bob, Carter, Amy]的排名是[2,3,1]，選取奇數排名後輸出[Carter, Amy]，所以可見參考答案中的first_name並未按字母大小排序*/
3、在e1層中，直接在限定條件 e1.rowid % 2 = 1 下，代表奇數行的 rowid，選取對應的 e1.first_name；
4、e2層則相當於連線e1層（選取表示層）與e3層（標號層）的橋樑。

SELECT e1.first_name FROM 
  (SELECT e2.first_name, 
    (SELECT COUNT(*) FROM employees AS e3 
     WHERE e3.first_name <= e2.first_name) 
   AS rowid FROM employees AS e2) AS e1
WHERE e1.rowid % 2 = 1

2018.4.14

本題的思路為複用 salaries 表進行子查詢，最後以 s1.emp_no 排序輸出求和結果。
1、輸出的第三個欄位，是由一個 SELECT 子查詢構成。將子查詢內複用的 salaries 表記為 s2，主查詢的 salaries 表記為 s1，當主查詢的 s1.emp_no 確定時，對子查詢中不大於 s1.emp_no 的 s2.emp_no 所對應的薪水求和
2、注意是對員工當前的薪水求和，所以在主查詢和子查詢內都要加限定條件 to_date = '9999-01-01'

SELECT s1.emp_no, s1.salary, 
(SELECT SUM(s2.salary) FROM salaries AS s2 
 WHERE s2.emp_no <= s1.emp_no AND s2.to_date = '9999-01-01') AS running_total 
FROM salaries AS s1 WHERE s1.to_date = '9999-01-01' ORDER BY s1.emp_no

1

2

1.用一條SQL 語句 查詢出每門課都大於80 分的學生姓名

name kecheng fenshu
張三 語文 81
張三 數學 75
李四 語文 76
李四 數學 90
王五 語文 81
王五 數學 100
王五 英語 90

法1: select distinct name from table where name not in (select distinct name from table where fenshu<=80)
思路是：
1.挑選出這張表中出現過分數小於80的人，然後從全部的人中，將這些人剔除出去，留下的就是分數都大於80的
2.分數都大於80的補集是分數出現過小於80

法2：select name from table group by name having min(fenshu)>80
思路是：分組後，挑選除最小分數依然大於80的

插入的三種方式

insert
insert ignore
replace

相關文章