1.join连接查询

通常情况下DBA不建议使用JOIN查询，如果无法避免则首要问题就是选择合适的驱动表。

left join(左联接) ：返回包括左表中的所有记录和右表中联结字段相等的记录。

right join(右联接) ：返回包括右表中的所有记录和左表中联结字段相等的记录。

inner join | join(等值连接)：只返回两个表中联结字段相等的行。

SELECT * FROM Person p1 INNER JOIN Person p2 ON p1.Email = p2.Email //等价于 SELECT * FROM Person p1, Person p2 WHERE p1.Email = p2.Email

全连接Full Join：Mysql不支持。

嵌套语句中，子查询只返回一个字段，则父查询可以如下：忽略关键字 from

SELECT IFNULL((SELECT Salary FROM Employee ORDER BY Salary desc LIMIT 1,1),NULL) AS SecondHighestSalary

UNION ALL & UNION

相同点：连接前后的select语句，其返回的字段名可以不一致但是字段个数必须一致。特例，如果返回全部字段则对应的两个表的字段个数必须一致。

不同点：UNION会自动对返回的字段进行去重。例如，返回字段Email的结果会进行去重。

1、第二高薪水

编写一个 SQL 查询，获取 Employee 表中第二高的薪水（Salary） 。

+----+--------+
| Id | Salary |
+----+--------+
| 1  | 100    |
| 2  | 200    |
| 3  | 300    |
+----+--------+
例如上述 Employee 表，SQL查询应该返回 200 作为第二高的薪水。如果不存在第二高的薪水，那么查询应返回 null。

+---------------------+
| SecondHighestSalary |
+---------------------+
| 200                 |
+---------------------+

Coding

解法一：

SELECT
    (SELECT DISTINCT
            Salary
        FROM
            Employee
        ORDER BY Salary DESC
        LIMIT 1 OFFSET 1) AS SecondHighestSalary
;

解法二：

SELECT
    IFNULL(
      (SELECT DISTINCT Salary
       FROM Employee
       ORDER BY Salary DESC
        LIMIT 1 OFFSET 1),
    NULL) AS SecondHighestSalary

Anaylaze

如果单单只用：

(SELECT DISTINCT
            Salary
        FROM
            Employee
        ORDER BY Salary DESC
        LIMIT 1 OFFSET 1)

则不会把结果为null的情况显示出来，所以给它外面再加一层。

2、超过经理收入的员工

Employee 表包含所有员工，他们的经理也属于员工。每个员工都有一个 Id，此外还有一列对应员工的经理的 Id。

+----+-------+--------+-----------+
| Id | Name  | Salary | ManagerId |
+----+-------+--------+-----------+
| 1  | Joe   | 70000  | 3         |
| 2  | Henry | 80000  | 4         |
| 3  | Sam   | 60000  | NULL      |
| 4  | Max   | 90000  | NULL      |
+----+-------+--------+-----------+
给定 Employee 表，编写一个 SQL 查询，该查询可以获取收入超过他们经理的员工的姓名。在上面的表格中，Joe 是唯一一个收入超过他的经理的员工。

+----------+
| Employee |
+----------+
| Joe      |
+----------+

Coding

# Write your MySQL query statement below
select e1.Name as Employee  from Employee e1
 join Employee e2 
on e1.ManagerId=e2.id and e1.Salary>e2.Salary

不能用left join 因为答案要求是确定的。

3、查找重复的电子邮箱

编写一个 SQL 查询，查找 Person 表中所有重复的电子邮箱。

示例：

+----+---------+
| Id | Email   |
+----+---------+
| 1  | a@b.com |
| 2  | c@d.com |
| 3  | a@b.com |
+----+---------+
根据以上输入，你的查询应返回以下结果：

+---------+
| Email   |
+---------+
| a@b.com |
+---------+
说明：所有电子邮箱都是小写字母。

Coding

解法一：

1. 先执行：
select Email, count(Email) as num
from Person
group by Email;
得到：
| Email   | num |
|---------|-----|
| a@b.com | 2   |
| c@d.com | 1   |
2. 总结
select Email from
(
  select Email, count(Email) as num
  from Person
  group by Email
) as statistic
where num > 1
;

解法二：

# Write your MySQL query statement below
select Email 
from Person 
group by Email 
having count(Email)>1;

4、删除重复的电子邮箱

编写一个 SQL 查询，来删除 Person 表中所有重复的电子邮箱，重复的邮箱里只保留 Id 最小的那个。

+----+------------------+
| Id | Email            |
+----+------------------+
| 1  | john@example.com |
| 2  | bob@example.com  |
| 3  | john@example.com |
+----+------------------+

Id 是这个表的主键。例如，在运行你的查询语句之后，上面的 Person 表应返回以下几行:

+----+------------------+
| Id | Email            |
+----+------------------+
| 1  | john@example.com |
| 2  | bob@example.com  |
+----+------------------+

Coding

# Write your MySQL query statement below
delete p1 from Person p1, Person p2
where p1.Email = p2.Email and p1.Id>p2.Id

5、locate

判断某个字段是否包含某个字符。大于0表示查询存在特定字符的行。等于0表示过滤掉存在特定字符的行。

SELECT * FROM csdn.ods_my_favorite_rt where source = '' and LOCATE("html",url) > 0;
SELECT * FROM csdn.ods_my_favorite_rt where source = '' and LOCATE("html",url) = 0;

SQL面试题

1.join连接查询

1、第二高薪水

Coding

Anaylaze

2、超过经理收入的员工

Coding

3、查找重复的电子邮箱

Coding

4、删除重复的电子邮箱

Coding

5、locate