+-------------+---------+ | Column Name | Type | +-------------+---------+ | name | varchar | | continent | varchar | | area | int | | population | int | | gdp | int | +-------------+---------+ name 是这张表的主键。 这张表的每一行提供：国家名称、所属大陆、面积、人口和 GDP 值。
面积至少为 300 万平方公里（即，3000000 km2）
或者
人口至少为 2500 万（即 25000000）
编写一个 SQL 查询以报告 大国 的国家名称、人口和面积。
1
大国 => where满足相关条件即可
1 2 3
SELECT name, population, area FROM World WHERE area >= 3000000 or population >= 25000000;
1 2 3 4
| name | population | area | | ----------- | ---------- | ------- | | Afghanistan | 25500100 | 652230 | | Algeria | 37100000 | 2381741 |
#case when多条件判断 CASE WHEN 列名=条件值1 THEN 选项1 WHEN 列名=条件值2 THEN 选项2 ELSE 默认值 END
#MOD取余 MOD(a,b) #a/b的余数
#返回原字符串中指定数目的字符 LEFT(name, 1) #返回name的第一个字符
#IF判断，真返回v1，假返回v2 IF(expr, V1, V2)
CASE WHEN
1 2 3 4 5 6 7 8 9
SELECT employee_id, (CASE WHEN MOD(employee_id,2) != 0 AND LEFT(name, 1) != 'M' THEN salary WHEN MOD(employee_id,2) = 0 OR LEFT(name, 1) = 'M' THEN 0 END) bonus FROM Employees ORDER BY employee_id;
IF
1 2 3 4
SELECT employee_id, IF(employee_id%2 != 0 AND name NOT LIKE('M%'),salary,0) bonus FROM Employees ORDER BY employee_id;
regexp
1 2 3 4 5 6
SELECT employee_id, (CASE WHEN employee_id%2 = 1 AND name NOT REGEXP('^M') THEN salary ELSE 0 END) bonus FROM Employees ORDER BY employee_id;
+-------------+----------+ | Column Name | Type | +-------------+----------+ | id | int | | name | varchar | | sex | ENUM | | salary | int | +-------------+----------+ id 是这个表的主键。 sex 这一列的值是 ENUM 类型，只能从 ('m', 'f') 中取。 本表包含公司雇员的信息。
DELETE p1 FROM Person p1, Person p2 WHERE p1.email = p2.email AND p1.id > p2.id;
NOT IN子查询
1 2 3 4 5 6 7 8 9
DELETE FROM Person WHERE id NOT IN( SELECT id FROM( SELECT min(id) id FROM Person GROUP BY email )p );
ROW_NUMBER()开窗函数
1
1 2 3 4 5 6 7 8 9 10
DELETE FROM PERSON WHERE id IN( SELECT id FROM( SELECT id,email, ROW_NUMBER() OVER(PARTITION BY email ORDER BY id) AS id_order FROM Person )p WHERE id_order > 1 );
+----------------+---------+ | Column Name | Type | +----------------+---------+ | user_id | int | | name | varchar | +----------------+---------+ user_id 是该表的主键。 该表包含用户的 ID 和名字。名字仅由小写和大写字符组成。
编写一个 SQL 查询来修复(查询)名字，使得只有第一个字符是大写的，其余都是小写的。
返回按 user_id 排序的结果表。
CONCAT一下
1 2 3 4
SELECT user_id, CONCAT(UPPER(LEFT(name,1)),LOWER(SUBSTRING(name,2))) AS name FROM Users ORDER BY user_id;
1 2 3 4
| user_id | name | | ------- | ----- | | 1 | Alice | | 2 | Bob |
SELECT * FROM Patients WHERE conditions REGEXP '^DIAB1| DIAB1';
LIKE同理
1 2 3
SELECT * FROM Patients WHERE conditions LIKE 'DIAB1%' OR conditions LIKE '% DIAB1%'
4.组合查询&指定选取
1965.丢失信息的雇员
表: Employees
1 2 3 4 5 6 7 8
+-------------+---------+ | Column Name | Type | +-------------+---------+ | employee_id | int | | name | varchar | +-------------+---------+ employee_id 是这个表的主键。 每一行表示雇员的id 和他的姓名。
表: Salaries
1 2 3 4 5 6 7 8
+-------------+---------+ | Column Name | Type | +-------------+---------+ | employee_id | int | | salary | int | +-------------+---------+ employee_id is 这个表的主键。 每一行表示雇员的id 和他的薪水。
SELECT L.employee_id FROM employees L LEFT JOIN Salaries R ON L.employee_id = R.employee_id WHERE R.salary IS NULL UNION SELECT L.employee_id FROM Salaries L LEFT JOIN employees R ON L.employee_id = R.employee_id WHERE R.name IS NULL ORDER BY employee_id;
分别进行两次左连接找出缺失的，最后UNION并起来
1795.每个产品在不同商店的价格
表：Products
1 2 3 4 5 6 7 8 9 10 11
+-------------+---------+ | Column Name | Type | +-------------+---------+ | product_id | int | | store1 | int | | store2 | int | | store3 | int | +-------------+---------+ 这张表的主键是product_id（产品Id）。 每行存储了这一产品在不同商店store1, store2, store3的价格。 如果这一产品在商店里没有出售，则值将为null。
SELECT product_id, 'store1' store, store1 price FROM Products WHERE store1 IS NOT NULL UNION ALL SELECT product_id, 'store2' store, store2 price FROM Products WHERE store2 IS NOT NULL UNION ALL SELECT product_id,'store3' store, store3 price FROM Products WHERE store3 IS NOT NULL;
SELECT id, 'Root' Type FROM tree WHERE p_id IS NULL UNION SELECT id,'Leaf' Type FROM tree WHERE id NOT IN( SELECT DISTINCT p_id FROM tree WHERE p_id iS NOT NULL ) AND p_id IS NOT NULL UNION SELECT id, 'Inner' Type FROM tree WHERE id IN( SELECT DISTINCT p_id FROM tree WHERE p_id IS NOT NULL ) AND p_id IS NOT NULL ORDER BY id;
CASE WHEN 流程控制
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17
SELECT id 'id', CASE WHEN tree.id IN ( SELECT T.id FROM tree T WHERE T.p_id IS NULL ) THEN 'Root' WHEN tree.id IN( SELECT T.p_id FROM tree T ) THEN 'Inner' ELSE 'Leaf' END Type FROM tree ORDER BY 'id'
IF与CASE WHEN同理
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17
SELECT id 'id', CASE WHEN tree.id IN ( SELECT T.id FROM tree T WHERE T.p_id IS NULL ) THEN 'Root' WHEN tree.id IN( SELECT T.p_id FROM tree T ) THEN 'Inner' ELSE 'Leaf' END Type FROM tree ORDER BY 'id'
176.第二高的薪水
Employee 表：
1 2 3 4 5 6 7 8
+-------------+------+ | Column Name | Type | +-------------+------+ | id | int | | salary | int | +-------------+------+ id 是这个表的主键。 表的每一行包含员工的工资信息。
输入: Person表: +----------+----------+-----------+ | personId | lastName | firstName | +----------+----------+-----------+ | 1 | Wang | Allen | | 2 | Alice | Bob | +----------+----------+-----------+ Address表: +-----------+----------+---------------+------------+ | addressId | personId | city | state | +-----------+----------+---------------+------------+ | 1 | 2 | New York City | New York | | 2 | 3 | Leetcode | California | +-----------+----------+---------------+------------+ 输出: +-----------+----------+---------------+----------+ | firstName | lastName | city | state | +-----------+----------+---------------+----------+ | Allen | Wang | Null | Null | | Bob | Alice | New York City | New York | +-----------+----------+---------------+----------+ 解释: 地址表中没有 personId = 1 的地址，所以它们的城市和州返回 null。 addressId = 1 包含了 personId = 2 的地址信息。
1 2 3 4
SELECT firstName, lastName, city, state FROM Person LEFT JOIN Address ON Person.personId = Address.personId;
1 2 3 4 5
## INNER JOIN 取交集 OUTER JOIN left join, right join, full outer join都是外连接，可能会出现null
1581. 进店却未进行过交易的顾客
表：Visits
1 2 3 4 5 6 7 8
+-------------+---------+ | Column Name | Type | +-------------+---------+ | visit_id | int | | customer_id | int | +-------------+---------+ visit_id 是该表的主键。 该表包含有关光临过购物中心的顾客的信息。
表：Transactions
1 2 3 4 5 6 7 8 9
+----------------+---------+ | Column Name | Type | +----------------+---------+ | transaction_id | int | | visit_id | int | | amount | int | +----------------+---------+ transaction_id 是此表的主键。 此表包含 visit_id 期间进行的交易的信息。
有一些顾客可能光顾了购物中心但没有进行交易。请你编写一个 SQL 查询，来查找这些顾客的 ID ，以及他们只光顾不交易的次数。
SELECT V.customer_id, count(*) count_no_trans FROM Visits V LEFT JOIN Transactions T ON V.visit_id = T.visit_id WHERE amount IS NULL GROUP BY customer_id;