SQL
大小写不敏感
-
选列
SELECT 列名称 FROM 表名称
-
选所有列
SELECT * FROM 表名称
-
选列并去除列中的重复值
SELECT DISTINCT 列名称 FROM 表名称
-
选择条件
SELECT 列名称 FROM 表名称 WHERE 列 运算符 值
例:
// 1 SELECT S.sname FROM Sailors S, Reserves R WHERE S.sid=R.sid AND R.bid=102 // 2 SELECT * FROM Persons WHERE City='Beijing'
-
选择结果排序
// 先根据字母排序,字母相同的情况下再根据数字排序 SELECT Company, OrderNumber FROM Orders ORDER BY Company, OrderNumber // DESC逆序,ASC顺序 SELECT Company, OrderNumber FROM Orders ORDER BY Company DESC, OrderNumber ASC
-
插入行
INSERT INTO 表名称 VALUES (值1, 值2,....)
例:
INSERT INTO Persons VALUES ('Gates', 'Bill', 'Xuanwumen 10', 'Beijing')
-
插入行(指定列)
// 插入时未指定的列为空 INSERT INTO table_name (列1, 列2,...) VALUES (值1, 值2,....)
例:
INSERT INTO Persons (LastName, Address) VALUES ('Wilson', 'Champs-Elysees')
-
修改数据
UPDATE 表名称 SET 列名称 = 新值, 列名称 = 新值, ... WHERE 列名称 = 某值
例:
UPDATE Person SET FirstName = 'Fred' WHERE LastName = 'Wilson'
-
删除行
// 若无第三行,则删除整个表 DELETE FROM 表名称 WHERE 列名称 = 值
例:
DELETE FROM Person WHERE LastName = 'Wilson'
-
LIKE
SELECT column_name(s) // s: 指定输出行的数目 FROM table_name WHERE column_name LIKE pattern
例:
SELECT * FROM Persons WHERE City LIKE 'N%' // N%: 以N开头 // %g: 以g结尾 // %con%: 包含con
-
通配符
通配符 描述 % 代表零个或多个字符 - 仅替代一个字符 [charlist] 字符列中的任何单一字符 [^charlist]或者[!charlist] 不在字符列中的任何单一字符 例:
// % N%: 以N开头 %g: 以g结尾 %con%: 包含con // - C_r_er: 1356字母分别是crer, 24字母任意 // [charlist] [ALN]: 选取的对象中含有A或L或N // [^charlist]或者[!charlist] [!ALN]: 选取的对象中不含有A和L和N
-
IN: 规定多个值或嵌套
// 规定多个值 SELECT column_name(s) FROM table_name WHERE column_name IN (value1,value2,...) // 嵌套 SELECT 列名称 FROM 表名称 WHERE 列 (NOT) IN (SELECT 列名称 FROM 表名称 WHERE 列 运算符 值)
例:
// 规定多个值 SELECT * FROM Persons WHERE LastName IN ('Adams','Carter') // 嵌套 SELECT S.sname FROM Sailors S WHERE S.sid IN (SELECT R.sid FROM Reserves R WHERE R.bid=103)
-
BETWEEN: 选取介于两个值之间的数据范围(是否包含前后有数据库系统决定)
SELECT column_name(s) FROM table_name WHERE column_name BETWEEN value1 AND value2
例:
// 以字母顺序显示介于 "Adams" 和 "Carter" 之间的人 SELECT * FROM Persons WHERE LastName BETWEEN 'Adams' AND 'Carter'
-
AS: 别名
// 表 SELECT column_name(s) FROM table_name AS alias_name // 列(得到的列将会因此改名) SELECT column_name AS alias_name FROM table_name
例:
SELECT po.OrderID, p.LastName, p.FirstName FROM Persons AS p, Product_Orders AS po WHERE p.LastName='Adams' AND p.FirstName='John'
-
INNER JOIN: 仅返回与限定符匹配的行(连接)
SELECT column_name(s) FROM table_name1 INNER JOIN table_name2 ON table_name1.column_name=table_name2.column_name
例:
SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo FROM Persons INNER JOIN Orders ON Persons.Id_P=Orders.Id_P
-
LEFT OUTER JOIN: 返回所有匹配的行,以及左侧表中所有不匹配的行
-
RIGHT OUTER JOIN: 返回所有匹配的行,以及右侧表中所有不匹配的行
-
FULL OUTER JOIN: 返回所有匹配的行,以及左右侧表中所有不匹配的行
-
UNIONl: 合并两个或多个SELECT语句的结果集
// UNION:不重复 // UNION ALL: 可重复 SELECT 列名称 FROM 表名称 WHERE 列 运算符 值 UNION SELECT 列名称 FROM 表名称 WHERE 列 运算符 值
例:
SELECT R.sid FROM Boats B, Reserves R WHERE R.bid=B.bid AND B.color=‘red’ UNION SELECT R.sid FROM Boats B, Reserves R WHERE R.bid=B.bid AND B.color=‘green’
-
INTERSECT: 嵌套选择
SELECT 列名称 FROM 表名称 WHERE 列 运算符 值 INTERSECT SELECT 列名称 FROM 表名称 WHERE 列 运算符 值
例:
// 既预定了红船又预定了绿船之人 SELECT S.sid FROM Sailors S, Boats B, Reserves R WHERE S.sid=R.sid AND R.bid=B.bid AND B.color=‘red’ INTERSECT SELECT S.sid FROM Sailors S, Boats B, Reserves R WHERE S.sid=R.sid AND R.bid=B.bid AND B.color=‘green’
-
EXCEPT: 除去
SELECT 列名称 FROM 表名称 WHERE 列 运算符 值 EXCEPT SELECT 列名称 FROM 表名称 WHERE 列 运算符 值
例:
SELECT S.sid FROM Sailors S EXCEPT SELECT S.sid FROM Sailors S, Reserves R WHERE S.sid=R.sid
-
EXISTS
用法与结果同IN一致。
-
op(>=<) ANY
= ANY : 用法与结果同IN一致。 > ANY : >min < ANY : <max
例:
// S.rating > min(S2.rating) SELECT * FROM Sailors S WHERE S.rating > ANY (SELECT S2.rating FROM Sailors S2 WHERE S2.sname=‘Horatio’)
-
op(>=<) ALL
> ALL : >max < ALL : <min
例:
// S.rating > max(S2.rating) SELECT * FROM Sailors S WHERE S.rating > ALL (SELECT S2.rating FROM Sailors S2 WHERE S2.sname=‘Horatio’)
-
MAX()
例:
// 找年龄最大 SELECT S.sname, S.age FROM Sailors S WHERE S.age = (SELECT MAX (S2.age) FROM Sailors S2)
-
GROUP BY
SELECT column_name, aggregate_function(column_name) FROM table_name WHERE column_name operator value GROUP BY column_name
例:
// 查找并求出每个客户的总金额 SELECT Customer,SUM(OrderPrice) FROM Orders GROUP BY Customer // 根据客户分组
-
COUNT(): 返回匹配指定条件的行数
SELECT COUNT(column_name) FROM table_name
例:
// 返回表中的记录数 SELECT COUNT(*) FROM table_name // 计算 "Carter" 的出现次数 SELECT COUNT(Customer) AS CustomerNilsen FROM Orders WHERE Customer='Carter'
-
HAVING:
SELECT column_name, aggregate_function(column_name) FROM table_name WHERE column_name operator value GROUP BY column_name HAVING aggregate_function(column_name) operator value
例:
// 查找客户 "Bush" 或 "Adams" 拥有超过 1500 的订单总金额 SELECT Customer,SUM(OrderPrice) FROM Orders WHERE Customer='Bush' OR Customer='Adams' GROUP BY Customer HAVING SUM(OrderPrice)>1500