-
1
2
3SELECT p.FirstName, p.LastName, a.City, a.State
FROM Person AS p LEFT JOIN Address AS a
ON p.PersonId = a.PersonId;- 左外连接:左表中满足条件的记录能够都查询到,即使右表中没有与之匹配的行
-
1
2
3
4
5SELECT
(SELECT DISTINCT(Salary)
FROM Employee
ORDER BY Salary desc
LIMIT 1,1) as SecondHighestSalary;- 关键字顺序:
SELECT
->FROM
->WHERE
->GROUP BY
->HAVING
->ORDER BY
->LIMIT
- 使用
DISTINCT
筛选重复的查询结果 - 为保证查询结果为空时返回
NULL
,需要在外边再包装一层SELECT
LIMIT
可以进行范围搜索
- 关键字顺序:
-
1
2
3
4
5
6
7CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
set N = N-1;
RETURN (
SELECT(SELECT DISTINCT(Salary) FROM Employee ORDER BY Salary DESC LIMIT N, 1)
);
END- 函数注重返回值,返回值只能是一个值,不能是集合
LIMIT
进行范围查询- 包装
SELECT
返回NULL
-
1
2
3
4
5SELECT s1.Score,COUNT(DISTINCT(s2.Score)) rank
FROM Scores s1,Scores s2
WHERE s1.Score <= s2.Score
GROUP BY s1.Id -- 此处运行环境不同可能会报错
ORDER BY s1.Score DESC;1
2
3
4SELECT s1.Score,
(SELECT COUNT(DISTINCT(s2.Score)) FROM Scores s2 WHERE s2.Score > s1.Score)+1 AS rank
FROM Scores s1
ORDER BY s1.Score DESC;- 利用
DISTINCT
去重分数相等的个数 - 自连接对同一张表的数据进行比较
- 利用
-
1
2
3
4SELECT DISTINCT(l1.Num) AS ConsecutiveNums
FROM Logs l1, Logs l2, Logs l3
WHERE l1.Num = l2.Num AND l2.Num = l3.Num
AND l1.Id = l2.Id-1 AND l2.Id = l3.Id-1;- 利用
Id
进行三表自连接查询
- 利用
-
1
2
3
4SELECT e1.Name Employee
FROM Employee e1,Employee e2
WHERE e1.ManagerId = e2.Id
AND e1.Salary > e2.Salary; -
1
2
3
4SELECT Email
FROM Person
GROUP BY Email
HAVING COUNT(*) > 1;GROUP BY
计算分组内记录个数
1
2
3SELECT DISTINCT(p1.Email) AS Email
FROM Person p1, Person p2
WHERE p1.Id != p2.Id AND p1.Email = p2.Email; -
1
2
3SELECT Name AS Customers
FROM Customers
WHERE Id NOT IN (SELECT DISTINCT(CustomerId) FROM Orders); -
1
2
3
4
5-- 当同一部门内最大工资不是一个时,只能查询到一个
SELECT d.Name AS Department,ANY_VALUE(e.Name) AS Employee,MAX(e.Salary)
FROM Employee e, Department d
WHERE e.DepartmentId = d.Id
GROUP BY Department;- MySql 5.7 要求在使用
GRUOP BY
时,SELECT
内的非函数查询必须包含在GROUP BY
中,否则报错sql_mode=only_full_group_by
,可以通过使用ANY_VALUE()
解决
1
2
3
4
5
6SELECT dep.Name AS Department,e.Name AS Employee, e.Salary
FROM Employee e, (SELECT d.Id, ANY_VALUE(d.Name) AS Name, MAX(e.Salary) AS m
FROM Employee e, Department d
WHERE e.DepartmentId = d.Id
GROUP BY Id) AS dep
WHERE e.DepartmentId = dep.Id AND e.Salary = dep.m; - MySql 5.7 要求在使用
-
1
2
3
4
5
6
7
8
9
10
11SELECT P2.Name AS Department,P3.Name AS Employee,P3.Salary AS Salary
FROM Employee AS P3
INNER JOIN Department AS P2
ON P2.Id = P3.DepartmentId
WHERE (
SELECT COUNT(DISTINCT Salary)
FROM Employee AS P4
WHERE P3.DepartmentId = P4.DepartmentId
AND P4.Salary >= P3.Salary
) <= 3
ORDER BY DepartmentId,Salary DESC; -
1
2
3
4
5
6
7DELETE FROM Person
WHERE Id NOT IN (
SELECT Id FROM
(SELECT MIN(p.Id) AS Id
FROM Person p
GROUP BY Email) pe
);- 不嵌套一层
SELECT
会报错:You can't specify target table 'Person' for update in FROM clause
- 不嵌套一层
-
1
2
3SELECT w1.Id
FROM Weather w1, Weather w2
WHERE DATEDIFF(w1.RecordDate, w2.RecordDate)=1 AND w1.Temperature > w2.Temperature;使用
DATEDIFF`函数求救两个日期的差集DATE
类型不能直接加减,比如“2019-04-30”+1会变成“20190431”
-
1
2
3
4
5
6SELECT t.request_at Day,(
round(count(if(status != 'completed', status, null)) / count(status), 2)
) AS 'Cancellation Rate'
FROM Users u INNER JOIN Trips t ON u.Users_id = t.Client_Id AND u.banned != 'Yes'
WHERE t.Request_at >= '2013-10-01' AND t.Request_at <= '2013-10-03'
GROUP BY t.Request_at; -
1
2
3SELECT name, population, area
FROM World
WHERE (area > 3000000 OR population > 25000000);OR
用括号括起来
1
2
3
4
5
6
7SELECT name, population, area
FROM World
WHERE area > 3000000
UNION
SELECT name, population, area
FROM World
WHERE population > 25000000UNION
:并集操作,不包括重复行,同时进行默认排序UNION ALL
:并集操作,包括重复行,不进行排序
-
1
2
3
4SELECT class
FROM courses
GROUP BY class
HAVING COUNT(DISTINCT(student)) >= 5;- 考虑重复记录
-
1
2
3
4
5
6
7
8SELECT DISTINCT a.*
FROM stadium a,stadium b,stadium c
WHERE a.people>=100 AND b.people>=100 AND c.people>=100
AND (
(a.id = b.id-1 AND b.id = c.id -1) OR
(a.id = b.id-1 AND a.id = c.id +1) OR
(a.id = b.id+1 AND b.id = c.id +1)
) ORDER BY a.id;- 注意如果只一种情况,会导致最后两个满足条件的记录查询不到
-
1
2
3
4SELECT *
FROM cinema
WHERE description != 'boring' AND id%2 = 1
ORDER BY rating DESC; -
1
2
3
4
5
6
7SELECT (CASE
WHEN MOD(id,2) = 1 AND id = (SELECT COUNT(*) FROM seat) THEN id
WHEN MOD(id,2) = 1 THEN id+1
ElSE id-1
END) AS id, student
FROM seat
ORDER BY id; -
1
UPDATE salary SET sex = IF(sex='m','f','m');
1
2UPDATE salary
SET sex = CHAR ( ASCII(sex) ^ ASCII( 'm' ) ^ ASCII( 'f' ) );