数据库之SQL基本语法

查询语言(query language)：操作或获取数据的语言:

• 
  
• 关系代数是操作语义（operational semantics）
• 关系演算是说明性语义（declarative semantics）

SQL有两个子语言：

• 
  
• DDL：data definition language 数据定义语言:
  创建表格
  注意约束
  注意建表的顺序
  
• DML：data manipulate language数据操作语言:
  

WHERE

SELECT * FROM Customers
WHERE Country='Mexico';

AND/OR

SELECT * FROM Customers
WHERE Country='Germany'
AND City='Berlin';

SELECT * FROM Customers
WHERE City='Berlin'
OR City='München';

SELECT * FROM Customers
WHERE Country='Germany'
AND (City='Berlin' OR City='München');

ORDER BY

降序排列（升序为ASC）
SELECT * FROM Customers
ORDER BY Country DESC;

INSERT INTO VALUES

INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country)
VALUES ('Cardinal','Tom B. Erichsen','Skagen 21','Stavanger','4006','Norway');

插入指定列：
INSERT INTO Customers (CustomerName, City, Country)
VALUES ('Cardinal', 'Stavanger', 'Norway');

也可以直接输入所有数据，默认对应表格中的数据：
INSERT INTO VALUES ('Cardinal','Tom B. Erichsen','Skagen 21','Stavanger','4006','Norway');

UPDATE

注意where语句的添加
UPDATE Customers
SET ContactName='Alfred Schmidt', City='Hamburg'
WHERE CustomerName='Alfreds Futterkiste';

DELETE

DELETE FROM Customers
WHERE CustomerName='Alfreds Futterkiste' AND ContactName='Maria Anders';

TOP

选择头两行数据
SELECT TOP 2 * FROM Customers;
选择前50%
SELECT TOP 50 PERCENT * FROM Customers;

LIKE

选取 City 以字母 "s" 开始的：
SELECT * FROM Customers
WHERE City LIKE 's%';

选取 City 以字母 "s" 结尾的：
SELECT * FROM Customers
WHERE City LIKE '%s';

选取 Country 包含模式 "land" 的：
SELECT * FROM Customers
WHERE Country LIKE '%land%';

选取 Country 不包含模式 "land" 的：
SELECT * FROM Customers
WHERE Country NOT LIKE '%land%';

通配符

选取 City 以一个任意字符开始，然后是 "erlin" 的：
SELECT * FROM Customers
WHERE City LIKE '_erlin';

选取 City 以 "L" 开始，然后是一个任意字符，然后是 "n"，然后是一个任意字符，然后是 "on" 的：
SELECT * FROM Customers
WHERE City LIKE 'L_n_on';

选取 City 以 "b"、"s" 或 "p" 开始的：
SELECT * FROM Customers
WHERE City LIKE '[bsp]%';

选取 City 以 "a"、"b" 或 "c" 开始的：
SELECT * FROM Customers
WHERE City LIKE '[a-c]%';

选取 City 不以 "b"、"s" 或 "p" 开始的：
SELECT * FROM Customers
WHERE City LIKE '[!bsp]%';

IN

选取 City 为 "Paris" 或 "London" 的:
SELECT * FROM Customers
WHERE City IN ('Paris','London');

BETWEEN

SELECT * FROM Products
WHERE Price BETWEEN 10 AND 20;

选取 OrderDate 介于 '04-July-1996' 和 '09-July-1996' 之间的所有订单：
SELECT * FROM Orders
WHERE OrderDate BETWEEN #07/04/1996# AND #07/09/1996#;

别名（AS）

SELECT o.OrderID, o.OrderDate, c.CustomerName
FROM Customers AS c, Orders AS o
WHERE c.CustomerName='Alfreds Futterkiste';

INNER JOIN

两个表有相同属性且当前属性值相同则连接起来：
SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate
FROM Orders
INNER JOIN Customers
ON Orders.CustomerID=Customers.CustomerID;

UNION/UNION ALL

将两个表中的E_name列连接到同一列，并且会自动去重：
SELECT E_Name FROM Employees_China
UNION
SELECT E_Name FROM Employees_USA

与UNION不同的就是不会去重：
SELECT E_Name FROM Employees_China
UNION ALL
SELECT E_Name FROM Employees_USA

———-参考w3cschool

http://www.runoob.com/sql/sql-join.html