SQL常见语句大全

----------------------------------------------------------基本--------------------------------------------------------------

1、SELECT（选取）

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

2、INSERT（插入）

    INSERT INTO Persons VALUES ('Gates', 'Bill', 'Xuanwumen 10', 'Beijing')

3、DELETE（删除）

    DELETE FROM Person WHERE LastName = 'Wilson'

4、UPDATE（修改）

    UPDATE Person SET FirstName = 'Fred' WHERE LastName = 'Wilson'

----------------------------------------------------------高级--------------------------------------------------------------

1、TOP（返回记录数目）

    SELECT TOP 50 PERCENT * FROM Persons

2、LIKE（通配符）

    SELECT * FROM Persons WHERE City NOT LIKE '%lon%'

3、AS（指定别名）

    SELECT po.OrderID, p.LastName, p.FirstName FROM Persons AS p, Product_OrdersAS po WHERE p.LastName='Adams' AND p.FirstName='John'

4、JOIN（连接，从两个或更多的表中获取结果）

    SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo FROM Persons, Orders WHERE Persons.Id_P = Orders.Id_P

    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 Persons.LastName, Persons.FirstName, Orders.OrderNo FROM Persons FULL JOIN Orders ON Persons.Id_P=Orders.Id_P ORDER BY Persons.LastName

5、UNION（合并多个SELECT语句的结果）

    SELECT E_Name FROM Employees_China UNION SELECT E_Name FROM Employees_USA

6、SELECT INTO（复制表）

    SELECT *INTO Persons_backup FROM Persons

7、PRIMARY_KEY、FOREIGN_KEY（主键、外键）

    CREATE TABLE Orders

    (

       O_Id int NOT NULL PRIMARY KEY,

       OrderNo int NOT NULL,

       Id_P int FOREIGN KEY REFERENCES Persons(Id_P)

    )

8、CREATE（创建数据库、表、索引）

    CREATE DATABASE my_db

    CREATE TABLE Persons(Id_P int,LastName varchar(255),FirstName varchar(255),Address varchar(255),City varchar(255))

    CREATE INDEX PersonIndex ON Person (LastName)

9、DROP（删除数据库、表、索引）

    DROP DATABASE m_db

    DROP TABLE Persons

    DROP INDEX PersonIndex ON Person

10、ALTER（添加、删除、修改列）

    ALTER TABLE Persons ADD Birthday date

    ALTER TABLE Persons DROP Birthday date

    ALTER TABLE Persons ALTER COLUMN Birthday year

11、ORDER BY（排序）

    SELECT * FROM Persons ORDER BY FirstName DESC

----------------------------------------------------------函数--------------------------------------------------------------

1、AVG（均值）

    SELECT Customer FROM Orders WHERE OrderPrice>(SELECT AVG(OrderPrice) FROM Orders)

2、MAX、MIN（最大、小值）

    SELECT MAX(OrderPrice) AS LargestOrderPrice FROM Orders

3、FIRST、LAST（第一个、最后一个）

    SELECT FIRST(OrderPrice) AS FirstOrderPrice FROM Orders

4、COUNT（行数）

    SELECT COUNT(Customer) AS CustomerNilsen FROM OrdersWHERE Customer='Carter'

5、GROUP BY（根据一个或多个列对结果集进行分组）

    SELECT Customer,OrderDate,SUM(OrderPrice) FROM Orders GROUP BY Customer,OrderDate

6、HAVING（条件选择，因为WHERE 关键字无法与合计函数一起使用）

    SELECT Customer,SUM(OrderPrice) FROM Orders WHERE Customer='Bush' OR Customer='Adams'GROUP BY Customer HAVING SUM(OrderPrice)>1500

7、LACSE、UCASE（大小写）

    SELECT LCASE(LastName) as LastName,FirstName FROM Persons

8、LEN（文本段中值的长度）

    SELECT LEN(City) as LengthOfCity FROM Persons

9、MID（从文本字段中提取子字符串）

    SELECT MID(City,1,3) as SmallCity FROM Persons

10、ROUND（四舍五入）

    SELECT ProductName, ROUND(UnitPrice,0) as UnitPrice FROM Products

11、NOW（当前时间）

    SELECT ProductName, UnitPrice, Now() as PerDate FROM Products

    SELECT ProductName, UnitPrice, FORMAT(Now(),'YYYY-MM-DD') as PerDateFROM Products