1. select
SELECT LastName,FirstName FROM Persons;
SELECT * FROM Persons;
2. where
SELECT * FROM Persons WHERE City='Beijing';
SELECT * FROM Persons WHERE FirstName='Thomas' AND LastName='Carter'
SELECT * FROM Persons WHERE (FirstName='Thomas' OR FirstName='William') AND LastName='Carter'
3. order by
SELECT Company, OrderNumber FROM Orders ORDER BY Company;//默认升序
SELECT Company, OrderNumber FROM Orders ORDER BY Company DESC, OrderNumber ASC
4. insert
INSERT INTO table_name (列1, 列2,...) VALUES (值1, 值2,....)
INSERT INTO Persons VALUES ('Gates', 'Bill', 'Xuanwumen 10', 'Beijing')；
INSERT INTO Persons (LastName, Address) VALUES ('Wilson', 'Champs-Elysees')；
5. update
UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值
UPDATE Person SET FirstName = 'Fred' WHERE LastName = 'Wilson'；
UPDATE Person SET Address = 'Zhongshan 23', City = 'Nanjing' WHERE LastName = 'Wilson';
6. delete
DELETE FROM 表名称 WHERE 列名称 = 值
DELETE FROM Person WHERE LastName = 'Wilson';
可以在不删除表的情况下删除所有的行。这意味着表的结构、属性和索引都是完整的：
DELETE FROM table_name;
7. limit
SELECT column_name(s) FROM table_name LIMIT number
8. like
SELECT column_name(s) FROM table_name WHERE column_name LIKE pattern
// N开头
SELECT * FROM Persons WHERE City LIKE 'N%'；
// g结尾
SELECT * FROM Persons WHERE City LIKE '%g'；
// 包含lon
SELECT * FROM Persons WHERE City LIKE '%lon%'；
// NOT组合
SELECT * FROM Persons WHERE City NOT LIKE '%lon%'
// 第一个字母之后是eorge
SELECT * FROM Persons WHERE FirstName LIKE '_eorge'；
// 以 "C" 开头，然后是一个任意字符，然后是 "r"，然后是任意字符，然后是 "er"
SELECT * FROM Persons WHERE LastName LIKE 'C_r_er'
// [charlist] 选取居住的城市以 "A" 或 "L" 或 "N" 开头的人
SELECT * FROM Persons WHERE City LIKE '[ALN]%'；
// 选取居住的城市不以 "A" 或 "L" 或 "N" 开头的人
SELECT * FROM Persons WHERE City LIKE '[!ALN]%'；
9. in
// 选取姓氏为 Adams 和 Carter 的人
SELECT * FROM Persons WHERE LastName IN ('Adams','Carter')；
10.between
// 如需以字母顺序显示介于 "Adams"（包括）和 "Carter"（不包括）之间的人，请使用下面的 SQL：
SELECT * FROM Persons WHERE LastName BETWEEN 'Adams' AND 'Carter'；
// 如需使用上面的例子显示范围之外的人，请使用 NOT 操作符
SELECT * FROM Persons WHERE LastName NOT BETWEEN 'Adams' AND 'Carter'；
11.join
// 引用两个表
SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo FROM Persons, Orders
WHERE Persons.Id_P = Orders.Id_P；
// INNER JOIN
SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo FROM Persons INNER JOIN
Orders ON Persons.Id_P = Orders.Id_P ORDER BY Persons.LastName；
12.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 ORDER BY Persons.LastName;
13.LEFT JOIN
// LEFT JOIN 关键字会从左表 (table_name1) 那里返回所有的行，即使在右表 (table_name2) 中没有
匹配的行。
SELECT column_name(s) FROM table_name1 LEFT JOIN table_name2 ON
table_name1.column_name=table_name2.column_name;
14.RIGHT JOIN
// RIGHT JOIN 关键字会右表 (table_name2) 那里返回所有的行，即使在左表 (table_name1) 中没有
匹配的行。
SELECT column_name(s) FROM table_name1 RIGHT JOIN table_name2 ON
table_name1.column_name=table_name2.column_name
15.FULL JOIN
只要其中某个表存在匹配，FULL JOIN 关键字就会返回行。
SELECT column_name(s) FROM table_name1 FULL JOIN table_name2 ON
table_name1.column_name=table_name2.column_name
// SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo FROM Persons FULL JOIN
Orders ON Persons.Id_P=Orders.Id_P ORDER BY Persons.LastName;
posted on
