SQL 基本语句
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
?
