数据库语句实践
一. 条件筛选
1. 大的国家
编写解决方案找出大国的国家名称、人口和面积(面积300万、人口2500万)
SELECT name, population, area FROM world WHERE area >= 3000000 OR population >= 25000000 #mysql
world[(world['area'] >= 3000000)|(world['population'] >= 25000000)][['name','population','area']] #pandas
2. 可回收且低脂的产品
编写解决方案找出既是低脂又是可回收的产品编号
SELECT product_id FROM Products WHERE low_fats = 'Y' AND recyclable = 'Y' #mysql
world[(world['area'] >= 3000000)|(world['population'] >= 25000000)][['name','population','area']] #pandas
3. 从不订购的客户
找出所有从不点任何东西的顾客
select customers.name as 'Customers'
from customers
where customers.id not in
(
select customerid from orders
)
mysql左连接
SELECT name AS 'Customers'
FROM Customers LEFT JOIN Orders ON Customers.Id = Orders.CustomerId
WHERE Orders.CustomerId IS NULL
4. 文章浏览
询出所有浏览过自己文章的作者
SELECT DISTINCT author_id AS id
FROM Views WHERE author_id = viewer_id
ORDER BY id
二.字符串函数
1. 无效的推文
推文长度大于15无效
SELECT tweet_id FROM tweets WHERE CHAR_LENGTH(content) > 15
2. 计算特殊奖学金
如果一个雇员的id 是奇数并且他的名字不是以 'M' 开头，那么他的奖金是他工资的 100% ，否则奖金为0
SELECT employee_id,
IF(employee_id % 2 = 1 AND name NOT REGEXP '^M', salary, 0) AS bonus
FROM employees
ORDER BY employee_id
3. 恢复表中的名字
修复名字，使得只有第一个字符是大写的，其余都是小写的
SELECT user_id, CONCAT(UPPER(SUBSTRING(name, 1, 1)), LOWER(SUBSTRING(name, 2))) AS name
FROM Users
ORDER BY user_id;
4. 查找拥有有效邮箱的用户
有效的邮箱包含符合下列条件的前缀名和域名：
前缀名是包含字母（大写或小写）、数字、下划线 '_'、句点 '.' 和/或横杠 '-' 的字符串。前缀名必须以字母开头。
域名是 '@leetcode.com'
SELECT user_id, name, mail
FROM Users
WHERE mail REGEXP '^[a-zA-Z][a-zA-Z0-9_.-]*\\@leetcode\\.com$'; #必须以字母开头
5. 患某种疾病的患者
查询患有 I 类糖尿病的患者 ID （patient_id）、患者姓名（patient_name）以及其患有的所有疾病代码（conditions）
SELECT patient_id, patient_name, conditions
FROM Patients
WHERE conditions REGEXP '\\bDIAB1.*';
三. 数据操作
1. 第N高的薪水
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
DECLARE M INT;
SET M = N-1;
RETURN (
SELECT DISTINCT salary
FROM Employee
ORDER BY salary DESC
LIMIT M, 1
);
END
2. 第二高的薪水
SELECT
(SELECT DISTINCT Salary FROM Employee
ORDER BY Salary DESC
LIMIT 1 OFFSET 1) AS SecondHighestSalary
3. 部门工资最高的员工
查找出每个部门中薪资最高的员工
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
)
;
4. 分数排名
由高到底，分数相同排名相同，排名数字连续
SELECT
S.score,
DENSE_RANK() OVER (
ORDER BY
S.score DESC
) AS 'rank'
FROM
Scores S;
5. 删除重复的电子邮箱
只保留一个具有最小 id 的唯一电子邮件
DELETE p1 FROM Person p1,
Person p2
WHERE
p1.Email = p2.Email AND p1.Id > p2.Id
6. 每个产品在不同商店的价格
重构表，使得商店名变成属性
SELECT product_id, 'store1' AS store, store1 AS price
FROM Products WHERE store1 IS NOT NULL
UNION
SELECT product_id, 'store2' AS store, store2 AS price
FROM Products WHERE store2 IS NOT NULL
UNION
SELECT product_id, 'store3' AS store, store3 AS price
FROM Products WHERE store3 IS NOT NULL
四. 数据统计
1. 富有客户的数量
查询至少有一个订单的金额严格大于 500 的客户的数量
SELECT COUNT(DISTINCT customer_id) AS rich_count
FROM Store WHERE amount > 500
2. 即时实物配送
如果顾客期望的配送日期和下单日期相同，则该订单称为 「即时订单」，否则称为「计划订单」。
查询即时订单所占的百分比，保留两位小数。
SELECT ROUND(100 * AVG(order_date = customer_pref_delivery_date), 2) AS immediate_percentage FROM Delivery;
3. 按分类统计薪水
按薪水高低分类统计数量
SELECT 'Low Salary' AS category,
SUM(CASE WHEN income < 20000 THEN 1 ELSE 0 END) AS accounts_count FROM Accounts
UNION
SELECT 'Average Salary' category,
SUM(CASE WHEN income >= 20000 AND income <= 50000 THEN 1 ELSE 0 END) AS accounts_count FROM Accounts
UNION
SELECT 'High Salary' category,
SUM(CASE WHEN income > 50000 THEN 1 ELSE 0 END) AS accounts_count FROM Accounts
五. 数据分组
1. 查询每个员工花费的总时间
计算每个员工每天花费的总时间
SELECT event_day AS day, emp_id, SUM(out_time - in_time) AS total_time
FROM Employees GROUP BY event_day, emp_id;
2. 游戏玩法分析
查询每位玩家第一次登陆平台的日期
SELECT player_id, MIN(event_date) AS first_login
FROM Activity A GROUP BY A.player_id;
3. 每位教师所教授的科目种类的数量
查询每位老师在大学里教授的科目种类的数量
SELECT teacher_id, COUNT(DISTINCT subject_id) AS cnt
FROM Teacher GROUP BY teacher_id;
4. 超过5名学生的课
SELECT class FROM courses
GROUP BY class HAVING COUNT(student) >= 5
5. 订单最多的客户
SELECT customer_number FROM orders
GROUP BY customer_number
ORDER BY COUNT(order_number) DESC
LIMIT 1
6. 按日期分组销售产品
查询每个日期、销售的不同产品的数量及其名称。
SELECT sell_date, COUNT(DISTINCT(product)) AS num_sold,
GROUP_CONCAT(DISTINCT product ORDER BY product SEPARATOR ',') AS products
FROM Activities GROUP BY sell_date
ORDER BY sell_date ASC
7. 每天的领导和合伙人
对于每一个 date_id 和 make_name，返回不同的 lead_id 以及不同的 partner_id 的数量
SELECT date_id, make_name,
COUNT(DISTINCT lead_id) AS unique_leads,
COUNT(DISTINCT partner_id) AS unique_partners
FROM DailySales
GROUP BY date_id, make_name;
六. 数据合并
1. 查询合作过至少三次的演员和导演的 id 对
SELECT actor_id, director_id
FROM ActorDirector
GROUP BY actor_id, director_id
HAVING COUNT(*) >= 3;
2. 使用唯一标识码替换员工
SELECT unique_id, name FROM Employees LEFT JOIN EmployeeUNI
ON Employees.id = EmployeeUNI.id;
3. 学生们参加各科测试的次数
SELECT
s.student_id, s.student_name, sub.subject_name, IFNULL(grouped.attended_exams, 0) AS attended_exams
FROM
Students s CROSS JOIN Subjects sub
LEFT JOIN (
SELECT student_id, subject_name, COUNT(*) AS attended_exams
FROM Examinations
GROUP BY student_id, subject_name
)
grouped ON s.student_id = grouped.student_id AND sub.subject_name = grouped.subject_name
ORDER BY s.student_id, sub.subject_name;
4. 至少有5名直接下属的经理
SELECT name FROM employee
WHERE id IN (
SELECT managerId FROM employee GROUP BY managerId HAVING COUNT(*) >= 5
);
5. 销售员
查询没有任何与名为 “RED” 的公司相关的订单的所有销售人员的姓名。
SELECT s.name FROM salesperson s
WHERE
s.sales_id NOT IN (
SELECT o.sales_id FROM orders o LEFT JOIN company c ON o.com_id = c.com_id
WHERE c.name = 'RED')
