0409MySQL实战

20190408

1、从不订购的客户（简单）

某网站包含两个表，Customers 表和 Orders 表。编写一个 SQL 查询，找出所有从不订购任何东西的客户。

--创建表插入值
CREATE TABLE Customers(
	Id int(10) primary key,
	Name VARCHAR(10)
);
INSERT INTO Customers(Id,Name)
VALUES(1,"Joe");
INSERT INTO Customers(Id,Name)
VALUES(2,"Henry");
INSERT INTO Customers(Id,Name)
VALUES(3,"Sam");
INSERT INTO Customers(Id,Name)
VALUES(4,"Max");
+----+-------+
| Id | Name  |
+----+-------+
|  1 | Joe   |
|  2 | Henry |
|  3 | Sam   |
|  4 | Max   |
+----+-------+
4 rows in set (0.00 sec)
CREATE TABLE Orders(
	Id int(10),
	CustomerId int(10) 
);
INSERT INTO Orders(Id,CustomerId)
VALUES(1,3);
INSERT INTO Orders(Id,CustomerId)
VALUES(2,1);
+------+------------+
| Id   | CustomerId |
+------+------------+
|    1 |          3 |
|    2 |          1 |
+------+------------+
2 rows in set (0.00 sec)
--思路：查找出Customers.Id不在(not in)Orders.CustomerId
SELECT Customers.Name as Customers FROM Customers
WHERE Customers.Id not in (select CustomerId from Orders);
+-----------+
| Customers |
+-----------+
| Henry     |
| Max       |
+-----------+
2 rows in set (0.00 sec)

2、第N高的薪水（中等）

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

CREATE TABLE Employee(
	Id INT(10),
	Salary INT(10)
);
INSERT INTO Employee(Id,Salary)
VALUES(1,100);
INSERT INTO Employee(Id,Salary)
VALUES(2,200);
INSERT INTO Employee(Id,Salary)
VALUES(3,300);
+------+--------+
| Id   | Salary |
+------+--------+
|    1 |    100 |
|