SQL语言复习

前言

本文所有SQL语句均采用w3schools数据库，可在https://www.w3schools.com/sql/sql_exercises.asp进行练习。

---

SELECT

SELECT * FROM Customers;

SELECT City FROM Customers;

SELECT DISTINCT Country FROM Customers;

WHERE

SELECT * FROM Customers WHERE City = 'Berlin';

SELECT * FROM Customers WHERE NOT City = 'Berlin';

SELECT * FROM Customers WHERE CustomerID = 32;


SELECT * 
FROM Customers 
WHERE City = 'Berlin' AND PostalCode = 12209;


SELECT * 
FROM Customers
WHERE City = 'Berlin' OR City = 'London';

ORDER BY

SELECT * FROM Customers ORDER BY City;

SELECT * FROM Customers ORDER BY City DESC;

#先按国家排序，国家相同再按城市排序
SELECT * FROM Customers ORDER BY Country, City;

INSERT

INSERT INTO Customers ( CustomerName, Address, City, PostalCode, Country )
VALUES
	( 'Hekkan Burger', 'Gateveien 15', 'Sandnes', '4306', 'Norway' );

NULL

SELECT * FROM Customers WHERE PostalCode IS NULL;

SELECT * FROM Customers WHERE PostalCode IS NOT NULL;

UPDATE

UPDATE Customers SET City = 'Oslo';


UPDATE Customers SET City = 'Oslo' WHERE Country = 'Norway';


UPDATE Customers
SET City = 'Oslo', Country = 'Norway'
WHERE CustomerID = 32;

DELETE

DELETE FROM Customers WHERE Country = 'Norway';

DELETE FROM Customers;

Functions

SELECT MIN(Price) FROM Products;

SELECT MAX(Price) FROM Products;

SELECT COUNT(*) FROM Products WHERE Price = 18;

SELECT AVG(Price) FROM Products;

SELECT SUM(Price) FROM Products;

LIKE

SELECT * FROM Customers WHERE City LIKE 'a%';#a开头

SELECT * FROM Customers WHERE City LIKE '%a';#a结尾

SELECT * FROM Customers WHERE City LIKE '%a%';#包含a

SELECT * FROM Customers WHERE City LIKE 'a%b'#a开头b结尾;

SELECT * FROM Customers WHERE City NOT LIKE 'a%';

Wildcards(通配符)

SELECT * FROM Customers WHERE City LIKE '_a%';#第二个字母是a

#第一个字母是a或c或s
SELECT * FROM Customers WHERE City LIKE '[acs]%';

#第一个字母是a——f之间的任意字母
SELECT * FROM Customers WHERE City LIKE '[a-f]%';

#第一个字母不是a、c、f
SELECT * FROM Customers WHERE City LIKE '[!acf]%';

IN

SELECT * FROM Customers WHERE Country IN ('Norway','France');

SELECT * 
FROM Customers 
WHERE Country NOT IN ('Norway','France');

BETWEEN

SELECT * FROM Products WHERE Price BETWEEN 10 AND 20;

SELECT * FROM Products WHERE Price NOT BETWEEN 10 AND 20;

#按字母顺序介于'Geitost'和'Pavlova'之间
SELECT * FROM Products
WHERE ProductName 
BETWEEN 'Geitost' AND 'Pavlova';

ALIAS（起别名）

SELECT CustomerName, Address, PostalCode AS Pno FROM Customers;

SELECT * FROM Customers AS Consumers;

JOIN

SELECT * 
FROM Orders
	LEFT JOIN Customers 
		ON Orders.CustomerID = Customers.CustomerID;

SELECT * 
FROM Orders
	INNER JOIN Customers 
		ON Orders.CustomerID = Customers.CustomerID;

SELECT * 
FROM Orders
	RIGHT JOIN Customers 
		ON Orders.CustomerID = Customers.CustomerID;

GROUP BY

SELECT COUNT( CustomerID ), Country
FROM Customers
GROUP BY Country;

SELECT COUNT( CustomerID ), Country 
FROM Customers 
GROUP BY Country 
ORDER BY COUNT( CustomerID ) DESC;

Database

CREATE DATABASE testDB;

DROP DATABASE testDB;

CREATE TABLE Persons (
  PersonID INT, 
  LastName VARCHAR ( 255 ), 
  FirstName VARCHAR ( 255 ), 
  Address VARCHAR ( 255 ), 
  City VARCHAR ( 255 ) 
);

DROP TABLE Persons;

TRUNCATE TABLE Persons;#删除表中所有数据，但不删除表

ALTER TABLE Persons ADD Birthday DATE;#增加一列

ALTER TABLE Persons DROP COLUMN Birthday;#删除一列