SQL常用语句实例（摘抄自W3school）

SELECT * FROM Persons​

SELECT DISTINCT Company FROM Orders //查找唯一不同值

SELECT * FROM Persons WHERE City='Beijing'

and：

SELECT * FROM Persons WHERE FirstName='Thomas' AND LastName='Carter'

or:

SELECT * FROM Persons WHERE firstname='Thomas' OR lastname='Carter'

order by:分组

SELECT Company, OrderNumber FROM Orders ORDER BY Company

SELECT Company, OrderNumber FROM Orders ORDER BY Company DESC, OrderNumber ASC //以逆字母顺序显示公司名称，并以数字顺序显示顺序号

插入：

INSERT INTO Persons (LastName, Address) VALUES ('Wilson', 'Champs-Elysees') //在指定的列插入数据

新增/更新数据

UPDATE Person SET Address = 'Zhongshan 23', City = 'Nanjing' WHERE LastName = 'Wilson'//增加若干列

删除：

DELETE FROM Person WHERE LastName = 'Wilson' 

SELECT * FROM Persons LIMIT 5 //前五个

SELECT * FROM Persons WHERE City LIKE '%lon%' //模糊查询

SELECT * FROM Persons WHERE City NOT LIKE '%lon%'//不带‘lon’的数据

SELECT * FROM Persons WHERE FirstName LIKE '_eorge' //从上面的 "Persons" 表中选取名字的第一个字符之后是 "eorge" 的人

SELECT * FROM Persons WHERE City LIKE '[ALN]%' //从上面的 "Persons" 表中选取居住的城市以 "A" 或 "L" 或 "N" 开头的人

SELECT * FROM Persons WHERE City LIKE '[!ALN]%' //从上面的 "Persons" 表中选取居住的城市不以 "A" 或 "L" 或 "N" 开头的人

SELECT * FROM Persons WHERE LastName IN ('Adams','Carter') //从上表中选取姓氏为 Adams 和 Carter 的人

SELECT * FROM Persons WHERE LastName BETWEEN 'Adams' AND 'Carter' //以字母顺序显示介于 "Adams"（包括）和 "Carter"（不包括）之间的人

SELECT po.OrderID, p.LastName, p.FirstName FROM Persons AS p, Product_Orders AS po WHERE p.LastName='Adams' AND p.FirstName='John' //指别名

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo FROM Persons INNER JOIN Orders ON Persons.Id_P = Orders.Id_P ORDER BY Persons.LastName //内连接

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo FROM Persons LEFT JOIN Orders ON Persons.Id_P=Orders.Id_P ORDER BY Persons.LastName //列出所有的人，以及他们的定购 - 如果有的话

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo FROM Persons RIGHT JOIN Orders ON Persons.Id_P=Orders.Id_P ORDER BY Persons.LastName //列出所有的定单，以及定购它们的人 - 如果有的话

SELECT Customer FROM Orders WHERE OrderPrice>(SELECT AVG(OrderPrice) FROM Orders) //找到 OrderPrice 值高于 OrderPrice 平均值的客户

SELECT COUNT(Customer) AS CustomerNilsen FROM Orders WHERE Customer='Carter' //计算客户 "Carter" 的订单数(指定元素的数目)

SELECT COUNT(*) AS NumberOfOrders FROM Orders //总行数

SELECT COUNT(DISTINCT Customer) AS NumberOfCustomers FROM Orders //去重的行数

SELECT SUM(OrderPrice) AS OrderTotal FROM Orders //求orderprice的总数

SELECT Customer,SUM(OrderPrice) FROM Orders GROUP BY Customer //查找每个客户的总金额（总订单）

SELECT Customer,SUM(OrderPrice) FROM Orders WHERE Customer='Bush' OR Customer='Adams' GROUP BY Customer HAVING SUM(OrderPrice)>1500 //查找客户 "Bush" 或 "Adams" 拥有超过 1500 的订单总金额