常用sql语句总结

SELECT
SELECT
列名称 FROM
表名称
SELECT
LastName,FirstName FROM Persons
SELECT * FROM Persons
SELECT DISTINCT
列名称 FROM
表名称
SELECT DISTINCT Company FROM Orders

WHERE
SELECT
列名称 FROM
表名称 WHERE 列 运算符 值
SELECT * FROM Persons WHERE City='Beijing'
SELECT * FROM Persons WHERE Year>1965
SELECT
列 FROM 表 WHERE 列 LIKE
模式
SELECT * FROM Persons WHERE FirstName LIKE 'B%'
SELECT * FROM Persons WHERE FirstName LIKE '%s'
SELECT * FROM Persons WHERE FirstName LIKE '%eo%'

INSERT INTO
INSERT INTO
表名称 VALUES (值1, 值2,....)
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')

Update
UPDATE
表名称 SET
列名称 = 新值 WHERE
列名称 = 某值
UPDATE Person SET FirstName = 'Fred' WHERE LastName = 'Wilson'
UPDATE Person SET Address = 'Zhongshan 23', City = 'Nanjing'
WHERE LastName = 'Wilson'

DELETE
DELETE FROM
表名称 WHERE
列名称 = 值
DELETE FROM Person WHERE LastName = 'Wilson'
DELETE FROM table_name

ORDER BY
SELECT Company, OrderNumber FROM Orders ORDER BY Company
SELECT Company, OrderNumber FROM Orders ORDER BY Company, OrderNumber
SELECT Company, OrderNumber FROM Orders ORDER BY Company DESC
SELECT Company, OrderNumber FROM Orders ORDER BY Company DESC, OrderNumber ASC

AND & OR
SELECT * FROM Persons WHERE FirstName='Thomas' AND LastName='Carter'
SELECT * FROM Persons WHERE firstname='Thomas' OR lastname='Carter'
SELECT * FROM Persons WHERE (FirstName='Thomas' OR FirstName='William')
AND LastName='Carter'

IN
SELECT
列名称 FROM
表名称 WHERE
列名称 IN (值1,值2,..)
SELECT * FROM Persons WHERE LastName IN ('Adams','Gates')

BETWEEN ... AND
SELECT
列名称 FROM
表名称 WHERE
列名称 BETWEEN 值1 AND 值2
SELECT * FROM Persons WHERE LastName BETWEEN 'Adams' AND 'Carter'
SELECT * FROM Persons WHERE LastName NOT BETWEEN 'Adams' AND 'Carter'

Alias
SELECT
列名称 AS
列的别名 FROM
表名称
SELECT
列名称 FROM
表名称 AS
表的别名
SELECT LastName AS Family, FirstName AS Name FROM Persons
SELECT LastName, FirstName FROM Persons AS Employees

Join
SELECT Employees.Name, Orders.Product FROM Employees, Orders
WHERE Employees.Employee_ID = Orders.Employee_ID

SELECT Employees.Name
FROM Employees, Orders
WHERE Employees.Employee_ID = Orders.Employee_ID
AND Orders.Product = 'Printer'

SELECT
字段1, 字段2, 字段3
FROM
第一个表
INNER JOIN
第二个表
ON
第一个表.keyfield = 第二个表.foreign_keyfield

SELECT Employees.Name, Orders.Product
FROM Employees
INNER JOIN Orders
ON Employees.Employee_ID=Orders.Employee_ID

SELECT field1, field2, field3
FROM first_table
LEFT JOIN second_table
ON first_table.keyfield = second_table.foreign_keyfield

SELECT Employees.Name, Orders.Product
FROM Employees
LEFT JOIN Orders
ON Employees.Employee_ID=Orders.Employee_ID

SELECT field1, field2, field3
FROM first_table
RIGHT JOIN second_table
ON first_table.keyfield = second_table.foreign_keyfield

SELECT Employees.Name, Orders.Product
FROM Employees
RIGHT JOIN Orders
ON Employees.Employee_ID=Orders.Employee_ID

SELECT Employees.Name
FROM Employees
INNER JOIN Orders
ON Employees.Employee_ID=Orders.Employee_ID
WHERE Orders.Product = 'Printer'

UNION
SQL Statement 1
UNION
SQL Statement 2


SELECT E_Name FROM Employees_China
UNION
SELECT E_Name FROM Employees_USA

SQL Statement 1
UNION ALL
SQL Statement 2

CREATE
CREATE DATABASE
数据库名称
CREATE TABLE
表名称
(
列名称1 数据类型,
列名称2 数据类型,
.......
)

CREATE TABLE Person
(
LastName varchar,
FirstName varchar,
Address varchar,
Age int
)


CREATE TABLE Person
(
LastName varchar(30),
FirstName varchar,
Address varchar,
Age int(3)
)

CREATE UNIQUE INDEX
索引名称
ON
表名称 (列名称)

CREATE INDEX
索引名称
ON
表名称 (列名称)

CREATE INDEX PersonIndex
ON Person (LastName)

CREATE INDEX PersonIndex
ON Person (LastName DESC)

CREATE INDEX PersonIndex
ON Person (LastName, FirstName)

DROP
DROP INDEX index_name ON table_name
DROP TABLE
表名称
DROP DATABASE
数据库名称
TRUNCATE TABLE
表名称

ALTER TABLE
ALTER TABLE
表名称 ADD
列名称 数据类型
ALTER TABLE
表名称 DROP COLUMN
列名称
ALTER TABLE Person ADD City varchar(30)
ALTER TABLE Person DROP COLUMN Address

FUNCTIONS
SELECT function(列) FROM 表

GROUP BY 以及 HAVING
SELECT column,SUM(column) FROM table GROUP BY column
SELECT Company,SUM(Amount) FROM Sales
GROUP BY Company

SELECT column,SUM(column) FROM table
GROUP BY column
HAVING SUM(column) condition value

SELECT Company,SUM(Amount) FROM Sales
GROUP BY Company
HAVING SUM(Amount)>10000

SELECT INTO
SELECT column_name(s) INTO newtable [IN externaldatabase] FROM source
SELECT * INTO Persons_backup FROM Persons
SELECT LastName,Firstname INTO Persons_backup
FROM Persons
WHERE City='Sandnes'

CREATE VIEW
CREATE VIEW
视图名称 AS SELECT
列名称 FROM
表名称 WHERE
条件
CREATE VIEW [Current Product List] AS
SELECT ProductID,ProductName
FROM Products
WHERE Discontinued=No

CREATE VIEW [Products Above Average Price] AS
SELECT ProductName,UnitPrice
FROM Products
WHERE UnitPrice>(SELECT AVG(UnitPrice) FROM Products)

SELECT * FROM [Current Product List]

CREATE VIEW [Category Sales For 1997] AS
SELECT DISTINCT CategoryName,Sum(ProductSales) AS CategorySales
FROM [Product Sales for 1997]
GROUP BY CategoryName

SELECT * FROM [Category Sales For 1997]
WHERE CategoryName='Beverages'





[ 本帖最后由 xing393939 于 2009-4-14 17:50 编辑 ]

顶！

收藏了，谢谢！