SQL学习笔记3：WHERE子句与JOIN连接全解析 + LeetCode实战
基础知识点
1.AND / OR 语句
SELECT column1, column2, …
FROM table_name
WHERE condition1 AND condition2 AND condition3 …;
或者两者进行结合
SELECT * FROM Customers
WHERE Country = ‘Spain’ AND (CustomerName LIKE ‘G%’ OR CustomerName LIKE ‘R%’);
将AND和OR相结合
2.NOT
SELECT column1, column2, …
FROM table_name
WHERE NOT condition;
not可以接where后面的多种表现
3.（NOT）NULL
SELECT column_names
FROM table_name
WHERE column_name IS NULL;
SELECT column_names
FROM table_name
WHERE column_name IS NOT NULL;
4.DELETE
删除特定条件的记录
DELETE FROM table_name WHERE condition;
删除所有记录但是不删除表
DELETE FROM table_name;
重要考点——JOIN
1.（inner）join
两者
SELECT Products.ProductID, Products.ProductName, Categories.CategoryName
FROM Products
INNER JOIN Categories ON Products.CategoryID = Categories.CategoryID;
三者
SELECT Orders.OrderID, Customers.CustomerName, Shippers.ShipperName
FROM ((Orders
INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID)
INNER JOIN Shippers ON Orders.ShipperID = Shippers.ShipperID);
2.LEFT JOIN/RIGHT JOIN
这两者是一样的，只是那个表完全，看之前的图片就可以知道区别
SELECT Customers.CustomerName, Orders.OrderID
FROM Customers（左）
LEFT JOIN Orders（右） ON Customers.CustomerID = Orders.CustomerID
ORDER BY Customers.CustomerName;
3. FULL OUTER JOIN
SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
FULL OUTER JOIN Orders ON Customers.CustomerID=Orders.CustomerID
ORDER BY Customers.CustomerName;
4. 自链接self join
SELECT column_name(s)
FROM table1 T1, table1 T2
WHERE condition;
T1， T2是同一个表的别名
SELECT A.CustomerName AS CustomerName1, B.CustomerName AS CustomerName2, A.City
FROM Customers A, Customers B
WHERE A.CustomerID <> B.CustomerID
AND A.City = B.City
ORDER BY A.City;
LeetCodeSQL 重要50题重点习题（考点：查询+连接 难度:简单）
1.1757可回收且低脂的产品
# Write your MySQL query statement below
SELECT product_id
FROM Products
WHERE low_fats='Y' and recyclable='Y';
2.584寻找用户推荐人
# Write your MySQL query statement below
SELECT name
FROM Customer
WHERE referee_id<>2 OR referee_id IS NULL;
3. 1378使用唯一标识符来代替员工ID
# Write your MySQL query statement below
SELECT EmployeeUNI.unique_id, Employees.name
FROM EmployeeUNI
RIGHT JOIN Employees
ON EmployeeUNI.id=Employees.id;
4.1068产品销售分析I
# Write your MySQL query statement below
SELECT Product.product_name, Sales.year, Sales.price
FROM Product
RIGHT JOIN Sales
ON Product.product_id=Sales.product_id;
