SQL 基本语句

 

====== SQL基础 ======

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

INSERT INTO 表名称 VALUES (值1, 值2,....)
INSERT INTO table_name (列1, 列2,...) VALUES (值1, 值2,....)

UPDATE Person SET FirstName = 'Fred' WHERE LastName = 'Wilson'  // Person表名，FirstName,LastName列名

DELETE FROM Person WHERE LastName = 'Wilson'
DELETE FROM table_name    //删除所有
DELETE * FROM table_name   //删除所有

====== SQL高级 ======

SELECT TOP 2 * FROM Persons  // 从表中选前2个items
SELECT TOP 50 PERCENT * FROM Persons    // 从表中选前50%的items

SELECT * FROM Persons WHERE City LIKE '%g'  //从 "Persons" 表中选取居住在以 "g" 结尾的城市里的人
SELECT * FROM Persons WHERE City NOT LIKE '%lon%'
SELECT * FROM Persons WHERE City LIKE '[ALN]%'  // 从上面的 "Persons" 表中选取居住的城市以 "A" 或 "L" 或 "N" 开头的人
SELECT * FROM Persons WHERE City LIKE '[!ALN]%'  // 表中选取居住的城市不以 "A" 或 "L" 或 "N" 开头的人

SELECT * FROM Persons WHERE LastName IN ('Adams','Carter') // 从上表中选取姓氏为 Adams 和 Carter 的人

SELECT * FROM Persons WHERE LastName BETWEEN 'Adams' AND 'Carter' // 以字母顺序显示介于 "Adams"（包括）和 "Carter"（不包括）之间的人

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

//INNER JOIN 关键字在表中存在至少一个匹配时返回行。如果 "Persons" 中的行在 "Orders" 中没有匹配，就不会列出这些行
SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo FROM Persons INNER JOIN Orders ON Persons.Id_P = Orders.Id_P ORDER BY Persons.LastName

// LEFT JOIN 关键字会从左表 (Persons) 那里返回所有的行，即使在右表 (Orders) 中没有匹配的行。
SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo FROM Persons LEFT JOIN Orders ON Persons.Id_P=Orders.Id_P ORDER BY Persons.LastName

// RIGHT JOIN 关键字会从右表 (Orders) 那里返回所有的行，即使在左表 (Persons) 中没有匹配的行
SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo FROM Persons RIGHT JOIN Orders ON Persons.Id_P=Orders.Id_P ORDER BY Persons.LastName

// FULL JOIN 关键字会从左表 (Persons) 和右表 (Orders) 那里返回所有的行。如果 "Persons" 中的行在表 "Orders" 中没有匹配，或者如果 "Orders" 中的行在表 "Persons" 中没有匹配，这些行同样会列出。
SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo FROM Persons FULL JOIN Orders ON Persons.Id_P=Orders.Id_P ORDER BY Persons.LastName

// UNION 命令只会选取不同的值。 有两个名字相同的雇员，他们当中只有一个人被列出来
SELECT E_Name FROM Employees_China UNION SELECT E_Name FROM Employees_USA
// UNION ALL 列出所有item
SELECT E_Name FROM Employees_China UNION ALL SELECT E_Name FROM Employees_USA

SELECT * INTO Persons_backup FROM Persons   // 制作 "Persons" 表的备份复件
SELECT * INTO Persons IN 'Backup.mdb' FROM Persons  // IN 子句可用于向另一个数据库中拷贝表

== SQL函数 ==
SELECT AVG(OrderPrice<<列名>>) AS OrderAverage<<输出的新列名>> FROM Orders<<表名>>  // 算平均值

SELECT COUNT(Customer<<列名>>) AS CustomerNilsen<<输出的新列名>> FROM Orders<<表名>> WHERE Customer='Carter'   // 数个数

SELECT FIRST(OrderPrice) AS FirstOrderPrice FROM Orders  // 查找第一个值
SELECT LAST(OrderPrice) AS LastOrderPrice FROM Orders    // 查找最后一个值

SELECT MAX(OrderPrice) AS LargestOrderPrice FROM Orders  // 查找 "OrderPrice" 列的最大值
SELECT MIN(OrderPrice) AS LargestOrderPrice FROM Orders  // 查找 "OrderPrice" 列的最小值

SELECT SUM(OrderPrice) AS OrderTotal FROM Orders  // 查找 "OrderPrice" 字段的总数

SELECT Customer,SUM(OrderPrice) FROM Orders GROUP BY Customer  // 使用 GROUP BY 语句对客户进行组合
SELECT Customer,SUM(OrderPrice) FROM Orders GROUP BY Customer HAVING SUM(OrderPrice)<2000  // 查找订单总金额少于 2000 的客户

SELECT UCASE(LastName) as LastName,FirstName FROM Persons // 选取 "LastName" 和 "FirstName" 列的内容，然后把 "LastName" 列转换为大写
SELECT LCASE(LastName) as LastName,FirstName FROM Persons

SELECT MID(City,1,3) as SmallCity FROM Persons  //  从 "City" 列中提取前 3 个字符

SELECT LEN(City) as LengthOfCity FROM Persons  // 取得 "City" 列中值的长度

SELECT ProductName, ROUND(UnitPrice,0) as UnitPrice FROM Products  // 把名称和价格舍入为最接近的整数

SELECT ProductName<<列名>>, UnitPrice<<列名>>, Now() as PerDate<<输出的新列名>> FROM Products // 显示当天的日期所对应的名称和价格

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

====== SQL前期 ======

CREATE DATABASE my_db

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

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

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

CREATE TABLE Persons
(
Id_P int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
PRIMARY KEY (Id_P)
)

CREATE TABLE Persons
(
Id_P int NOT NULL PRIMARY KEY,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)

CREATE TABLE Persons
(
Id_P int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT uc_PersonID PRIMARY KEY (Id_P,LastName)
)

ALTER TABLE Persons ADD PRIMARY KEY (Id_P)

ALTER TABLE Persons ADD CONSTRAINT pk_PersonID PRIMARY KEY (Id_P,LastName)

ALTER TABLE Persons DROP PRIMARY KEY

ALTER TABLE Persons DROP CONSTRAINT pk_PersonID