SQL教程

1、基础

演示数据库，下面是选自 “Websites” 表的数据：

SELECT 语句用于从数据库中选取数据。

SELECT name,country FROM Websites;

SELECT DISTINCT 语句用于返回唯一不同的值。

SELECT DISTINCT country FROM Websites;

WHERE 子句用于提取那些满足指定条件的记录。

SELECT * FROM Websites WHERE country='CN';

如果第一个条件和第二个条件都成立，则 AND 运算符显示一条记录。
如果第一个条件和第二个条件中只要有一个成立，则 OR 运算符显示一条记录。

SELECT * FROM Websites WHERE country='CN' AND alexa > 50;
SELECT * FROM Websites  WHERE country='USA'  OR country='CN';
SELECT * FROM Websites WHERE alexa > 15 AND (country='CN' OR country='USA');

ORDER BY 关键字用于对结果集按照一个列或者多个列进行排序。
ORDER BY 关键字默认按照升序对记录进行排序。如果需要按照降序对记录进行排序，您可以使用 DESC 关键字。

SELECT * FROM Websites ORDER BY alexa DESC;
SELECT * FROM Websites ORDER BY country,alexa;

INSERT INTO 语句用于向表中插入新记录。

INSERT INTO Websites (name, url, alexa, country) VALUES ('百度','https://www.baidu.com/','4','CN');

UPDATE 语句用于更新表中已存在的记录。

UPDATE Websites SET alexa='5000', country='USA' WHERE name='菜鸟教程';

DELETE 语句用于删除表中的行。

DELETE FROM Websites WHERE name='Facebook' AND country='USA';

2、高级教程

SELECT TOP 子句用于规定要返回的记录的数目。

MySQL: SELECT * FROM Persons LIMIT 5;

Oracle: SELECT * FROM Persons WHERE ROWNUM <=5;

LIKE 操作符用于在 WHERE 子句中搜索列中的指定模式。

SELECT * FROM Websites WHERE name LIKE 'G%';

SELECT * FROM Websites WHERE name LIKE '%oo%';

SELECT * FROM Websites WHERE name NOT LIKE '%oo%';

在 SQL 中，通配符与 SQL LIKE 操作符一起使用。

SQL 通配符用于搜索表中的数据。
在 SQL 中，可使用以下通配符：

// 选取 name 以 "G"、"F" 或 "s" 开始的所有网站
SELECT * FROM Websites WHERE name REGEXP '^[GFs]';

// 选取 name 以 A 到 H 字母开头的网站
SELECT * FROM Websites WHERE name REGEXP '^[A-H]';

// 选取 name 不以 A 到 H 字母开头的网站
SELECT * FROM Websites WHERE name REGEXP '^[^A-H]';

IN 操作符允许您在 WHERE 子句中规定多个值。

// 选取 name 为 "Google" 或 "菜鸟教程" 的所有网站
SELECT * FROM Websites WHERE name IN ('Google','菜鸟教程');

BETWEEN 操作符用于选取介于两个值之间的数据范围内的值。

// 选取 alexa 介于 1 和 20 之间的所有网站
SELECT * FROM Websites WHERE alexa BETWEEN 1 AND 20; 

SELECT * FROM Websites WHERE alexa NOT BETWEEN 1 AND 20;

// 选取 alexa 介于 1 和 20 之间但 country 不为 USA 和 IND 的所有网站
SELECT * FROM Websites WHERE (alexa BETWEEN 1 AND 20) AND country NOT IN ('USA', 'IND');

通过使用 SQL，可以为表名称或列名称指定别名

演示数据库

// SQL 语句指定了两个别名，一个是 name 列的别名，一个是 country 列的别名
SELECT name AS n, country AS c FROM Websites; 

// 把三个列（url、alexa 和 country）结合在一起，并创建一个名为 "site_info" 的别名
SELECT name, CONCAT(url, ', ', alexa, ', ', country) AS site_info FROM Websites; 

SELECT w.name, w.url, a.count, a.date  FROM Websites AS w, access_log AS a  WHERE a.site_id=w.id and w.name="菜鸟教程";

SQL join 用于把来自两个或多个表的行结合起来。

演示数据库

请注意，“Websites” 表中的 “id” 列指向 “access_log” 表中的字段 “site_id”。上面这两个表是通过 “site_id” 列联系起来的。
然后，如果我们运行下面的 SQL 语句（包含 INNER JOIN）：

SELECT Websites.id, Websites.name, access_log.count, access_log.date
FROM Websites
INNER JOIN access_log
ON Websites.id=access_log.site_id;

不同的 SQL JOIN
在我们继续讲解实例之前，我们先列出您可以使用的不同的 SQL JOIN 类型：

• INNER JOIN：如果表中有至少一个匹配，则返回行
• LEFT JOIN：即使右表中没有匹配，也从左表返回所有的行
• RIGHT JOIN：即使左表中没有匹配，也从右表返回所有的行
• FULL JOIN：只要其中一个表中存在匹配，则返回行

UNION 操作符用于合并两个或多个 SELECT 语句的结果集。

请注意，UNION 内部的每个 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每个 SELECT 语句中的列的顺序必须相同。

SELECT country FROM Websites
UNION
SELECT country FROM apps
ORDER BY country;

执行以上 SQL 输出结果如下：

SELECT INTO 语句从一个表复制数据，然后把数据插入到另一个新表中

// 创建 Websites 的备份复件：
SELECT * INTO WebsitesBackup2016 FROM Websites;

// 只复制一些列插入到新表中：
SELECT name, url INTO WebsitesBackup2016 FROM Websites;

// 只复制中国的网站插入到新表中：
SELECT * INTO WebsitesBackup2016 FROM Websites WHERE country='CN';

// 复制多个表中的数据插入到新表中：
SELECT Websites.name, access_log.count, access_log.date INTO WebsitesBackup2016 FROM Websites
LEFT JOIN access_log
ON Websites.id=access_log.site_id;

INSERT INTO SELECT 语句从一个表复制数据，然后把数据插入到一个已存在的表中

// 复制 "apps" 中的数据插入到 "Websites" 中：
INSERT INTO Websites (name, country) SELECT app_name, country FROM apps;

// 只复 id=1 的数据到 "Websites" 中：
INSERT INTO Websites (name, country) SELECT app_name, country FROM apps  WHERE id=1;

CREATE DATABASE 语句用于创建数据库

// 下面的 SQL 语句创建一个名为 "my_db" 的数据库
CREATE DATABASE my_db;

CREATE TABLE 语句用于创建数据库中的表

表由行和列组成，每个表都必须有个表名

• column_name 参数规定表中列的名称。
• data_type 参数规定列的数据类型（例如 varchar、integer、decimal、date 等等）。
• size 参数规定表中列的最大长度。

// 创建一个名为 "Persons" 的表，包含五列：PersonID、LastName、FirstName、Address 和 City
CREATE TABLE Persons
(
PersonID int,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255)
);

SQL 约束用于规定表中的数据规则

如果存在违反约束的数据行为，行为会被约束终止。
约束可以在创建表时规定（通过 CREATE TABLE 语句），或者在表创建之后规定（通过 ALTER TABLE 语句）。

// PRIMARY KEY 约束的实例
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)  //PRIMARY KEY约束
)
CREATE TABLE Persons
(
    Id_P int NOT NULL PRIMARY KEY,   //PRIMARY KEY约束
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Address varchar(255),
    City varchar(255)
)

NOT NULL 约束强制列不接受 NULL 值

// 下面的 SQL 强制 "ID" 列、 "LastName" 列以及 "FirstName" 列不接受 NULL 值：
CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255) NOT NULL,
    Age int
);

// 在一个已创建的表的 "Age" 字段中添加 NOT NULL 约束如下所示：
ALTER TABLE Persons MODIFY Age int NOT NULL;

// 在一个已创建的表的 "Age" 字段中删除 NOT NULL 约束如下所示：
ALTER TABLE Persons MODIFY Age int NULL;

UNIQUE 约束唯一标识数据库表中的每条记录

// MySQL：
CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
UNIQUE (P_Id)
)

// SQL Server / Oracle / MS Access：
CREATE TABLE Persons
(
P_Id int NOT NULL UNIQUE,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)

// 如需命名 UNIQUE 约束，并定义多个列的 UNIQUE 约束，请使用下面的 SQL 语法：
CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT uc_PersonID UNIQUE (P_Id,LastName)
)

// 当表已被创建时，如需在 "P_Id" 列创建 UNIQUE 约束，请使用下面的 SQL：
ALTER TABLE Persons
ADD UNIQUE (P_Id)

// 如需命名 UNIQUE 约束，并定义多个列的 UNIQUE 约束，请使用下面的 SQL 语法：
ALTER TABLE Persons
ADD CONSTRAINT uc_PersonID UNIQUE (P_Id,LastName)

// 如需撤销 UNIQUE 约束，请使用下面的 SQL：

// MySQL：
ALTER TABLE Persons
DROP INDEX uc_PersonID

// SQL Server / Oracle / MS Access：
ALTER TABLE Persons
DROP CONSTRAINT uc_PersonID

PRIMARY KEY 约束唯一标识数据库表中的每条记录。
主键必须包含唯一的值。
主键列不能包含 NULL 值。
每个表都应该有一个主键，并且每个表只能有一个主键。

// 下面的 SQL 在 "Persons" 表创建时在 "P_Id" 列上创建 PRIMARY KEY 约束：
// MySQL：
CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
PRIMARY KEY (P_Id)
)

// SQL Server / Oracle / MS Access：
CREATE TABLE Persons
(
P_Id int NOT NULL PRIMARY KEY,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)


// 如需命名 PRIMARY KEY 约束，并定义多个列的 PRIMARY KEY 约束，请使用下面的 SQL 语法：
// MySQL / SQL Server / Oracle / MS Access：
CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName)
)
// 注释：在上面的实例中，只有一个主键 PRIMARY KEY（pk_PersonID）。然而，pk_PersonID 的值是由两个列（P_Id 和 LastName）组成的。


// 当表已被创建时，如需在 "P_Id" 列创建 PRIMARY KEY 约束，请使用下面的 SQL：
// MySQL / SQL Server / Oracle / MS Access：
ALTER TABLE Persons
ADD PRIMARY KEY (P_Id)


// 如需命名 PRIMARY KEY 约束，并定义多个列的 PRIMARY KEY 约束，请使用下面的 SQL 语法：
// MySQL / SQL Server / Oracle / MS Access：
ALTER TABLE Persons
ADD CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName)


// 需撤销 PRIMARY KEY 约束，请使用下面的 SQL：
// MySQL：
ALTER TABLE Persons DROP PRIMARY KEY
// SQL Server / Oracle / MS Access：
ALTER TABLE Persons DROP CONSTRAINT pk_PersonID

一个表中的 FOREIGN KEY 指向另一个表中的 UNIQUE KEY(唯一约束的键)

// 下面的 SQL 在 "Orders" 表创建时在 "P_Id" 列上创建 FOREIGN KEY 约束：
// MySQL：
CREATE TABLE Orders
(
O_Id int NOT NULL,
OrderNo int NOT NULL,
P_Id int,
PRIMARY KEY (O_Id),
FOREIGN KEY (P_Id) REFERENCES Persons(P_Id)
)


// SQL Server / Oracle / MS Access：
CREATE TABLE Orders
(
O_Id int NOT NULL PRIMARY KEY,
OrderNo int NOT NULL,
P_Id int FOREIGN KEY REFERENCES Persons(P_Id)
)


// 如需命名 FOREIGN KEY 约束，并定义多个列的 FOREIGN KEY 约束，请使用下面的SQL 语法：
// MySQL / SQL Server / Oracle / MS Access：
CREATE TABLE Orders
(
O_Id int NOT NULL,
OrderNo int NOT NULL,
P_Id int,
PRIMARY KEY (O_Id),
CONSTRAINT fk_PerOrders FOREIGN KEY (P_Id)
REFERENCES Persons(P_Id)
)


// 当 "Orders" 表已被创建时，如需在 "P_Id" 列创建 FOREIGN KEY 约束，请使用下面的 SQL：
// MySQL / SQL Server / Oracle / MS Access：
ALTER TABLE Orders
ADD FOREIGN KEY (P_Id)
REFERENCES Persons(P_Id)


// 如需命名 FOREIGN KEY 约束，并定义多个列的 FOREIGN KEY 约束，请使用下面的 SQL 语法：
// MySQL / SQL Server / Oracle / MS Access：
ALTER TABLE Orders
ADD CONSTRAINT fk_PerOrders
FOREIGN KEY (P_Id)
REFERENCES Persons(P_Id)


// 如需撤销 FOREIGN KEY 约束，请使用下面的 SQL：
// MySQL：
ALTER TABLE Orders
DROP FOREIGN KEY fk_PerOrders


// SQL Server / Oracle / MS Access：
ALTER TABLE Orders
DROP CONSTRAINT fk_PerOrders

如果对单个列定义 CHECK 约束，那么该列只允许特定的值。

// 下面的 SQL 在 "Persons" 表创建时在 "P_Id" 列上创建 CHECK 约束。CHECK 约束规定 "P_Id" 列必须只包含大于 0 的整数。
// MySQL：
CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CHECK (P_Id>0)
)


// SQL Server / Oracle / MS Access：
CREATE TABLE Persons
(
P_Id int NOT NULL CHECK (P_Id>0),
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)


// 如需命名 CHECK 约束，并定义多个列的 CHECK 约束，请使用下面的 SQL 语法：
// MySQL / SQL Server / Oracle / MS Access：
CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT chk_Person CHECK  (P_Id>0 AND City='Sandnes')
)


// 当表已被创建时，如需在 "P_Id" 列创建 CHECK 约束，请使用下面的 SQL：
// MySQL / SQL Server / Oracle / MS Access:
ALTER TABLE Persons
ADD CHECK (P_Id>0)


// 如需命名 CHECK 约束，并定义多个列的 CHECK 约束，请使用下面的 SQL 语法：
// MySQL / SQL Server / Oracle / MS Access：
ALTER TABLE Persons
ADD CONSTRAINT chk_Person CHECK (P_Id>0 AND City='Sandnes')


// 如需撤销 CHECK 约束，请使用下面的 SQL：
// SQL Server / Oracle / MS Access：
ALTER TABLE Persons
DROP CONSTRAINT chk_Person


// MySQL：
ALTER TABLE Persons
DROP CHECK chk_Person

DEFAULT 约束用于向列中插入默认值。

CREATE TABLE 时的 SQL DEFAULT 约束

// 下面的 SQL 在 "Persons" 表创建时在 "City" 列上创建 DEFAULT 约束：
// My SQL / SQL Server / Oracle / MS Access：
CREATE TABLE Persons
(
    P_Id int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Address varchar(255),
    City varchar(255) DEFAULT 'Sandnes'
)


// 通过使用类似 GETDATE() 这样的函数，DEFAULT 约束也可以用于插入系统值：
CREATE TABLE Orders
(
    O_Id int NOT NULL,
    OrderNo int NOT NULL,
    P_Id int,
    OrderDate date DEFAULT GETDATE()
)

当表已被创建时，如需在 “City” 列创建 DEFAULT 约束，请使用下面的 SQL：

// MySQL：
ALTER TABLE Persons
ALTER City SET DEFAULT 'SANDNES'


// SQL Server / MS Access：
ALTER TABLE Persons
ADD CONSTRAINT ab_c DEFAULT 'SANDNES' for City


// Oracle：
ALTER TABLE Persons
MODIFY City DEFAULT 'SANDNES'

如需撤销 DEFAULT 约束，请使用下面的 SQL：

// MySQL：
ALTER TABLE Persons
ALTER City DROP DEFAULT


// SQL Server / Oracle / MS Access：
ALTER TABLE Persons
ALTER COLUMN City DROP DEFAULT

CREATE INDEX 语句用于在表中创建索引。
在不读取整个表的情况下，索引使数据库应用程序可以更快地查找数据。

// SQL CREATE INDEX 语法
// 在表上创建一个简单的索引。允许使用重复的值：
CREATE INDEX index_name
ON table_name (column_name)

// SQL CREATE UNIQUE INDEX 语法
// 在表上创建一个唯一的索引。不允许使用重复的值：唯一的索引意味着两个行不能拥有相同的索引值。Creates a // unique index on a table. Duplicate values are not allowed:
CREATE UNIQUE INDEX index_name
ON table_name (column_name)

// 下面的 SQL 语句在 "Persons" 表的 "LastName" 列上创建一个名为 "PIndex" 的索引：
CREATE INDEX PIndex
ON Persons (LastName)

// 如果您希望索引不止一个列，您可以在括号中列出这些列的名称，用逗号隔开：
CREATE INDEX PIndex
ON Persons (LastName, FirstName)

通过使用 DROP 语句，可以轻松地删除索引、表和数据库。

// 用于 MS Access 的 DROP INDEX 语法：
DROP INDEX index_name ON table_name

// 用于 MS SQL Server 的 DROP INDEX 语法：
DROP INDEX table_name.index_name

// 用于 MySQL 的 DROP INDEX 语法：
ALTER TABLE table_name DROP INDEX index_name



// DROP TABLE 语句用于删除表。
DROP TABLE table_name



// DROP DATABASE 语句用于删除数据库。
DROP DATABASE database_name



// 如果我们仅仅需要删除表内的数据，但并不删除表本身，那么我们该如何做呢？
// 请使用 TRUNCATE TABLE 语句：
TRUNCATE TABLE table_name

ALTER TABLE 语句用于在已有的表中添加、删除或修改列。

// 现在，我们想在 "Persons" 表中添加一个名为 "DateOfBirth" 的列。
// 我们使用下面的 SQL 语句：
ALTER TABLE Persons
ADD DateOfBirth date


// 现在，我们想要改变 "Persons" 表中 "DateOfBirth" 列的数据类型。
// 我们使用下面的 SQL 语句：
ALTER TABLE Persons
ALTER COLUMN DateOfBirth year


// 接下来，我们想要删除 "Person" 表中的 "DateOfBirth" 列。
// 我们使用下面的 SQL 语句：
ALTER TABLE Persons
DROP COLUMN DateOfBirth

AUTO INCREMENT在每次插入新记录时，自动地创建主键字段的值

下面的 SQL 语句把 “Persons” 表中的 “ID” 列定义为 auto-increment 主键字段：

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

MySQL 使用 AUTO_INCREMENT 关键字来执行 auto-increment 任务。
默认地，AUTO_INCREMENT 的开始值是 1，每条新记录递增 1。
要让 AUTO_INCREMENT 序列以其他的值起始，请使用下面的 SQL 语法：

ALTER TABLE Persons AUTO_INCREMENT=100

要在 “Persons” 表中插入新记录，我们不必为 “ID” 列规定值（会自动添加一个唯一的值）：

INSERT INTO Persons (FirstName,LastName)
VALUES ('Lars','Monsen')

上面的 SQL 语句会在 “Persons” 表中插入一条新记录。“ID” 列会被赋予一个唯一的值。“FirstName” 列会被设置为 “Lars”，“LastName” 列会被设置为 “Monsen”。

CREATE VIEW 语句在 SQL 中，视图是基于 SQL 语句的结果集的可视化的表。

视图 “Current Product List” 会从 “Products” 表列出所有正在使用的产品（未停产的产品）。这个视图使用下面的 SQL 创建：

CREATE VIEW [Current Product List] AS
SELECT ProductID,ProductName
FROM Products
WHERE Discontinued=No

我们可以像这样查询上面这个视图：

SELECT * FROM [Current Product List]

Northwind 样本数据库的另一个视图会选取 “Products” 表中所有单位价格高于平均单位价格的产品：

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

我们可以像这样查询上面这个视图：

SELECT * FROM [Products Above Average Price]

更新视图
现在，我们希望向 “Current Product List” 视图添加 “Category” 列。我们将通过下列 SQL 更新视图：

CREATE VIEW [Current Product List] AS
SELECT ProductID,ProductName,Category
FROM Products
WHERE Discontinued=No

Date 函数

CREATE TABLE Orders
(
OrderId int NOT NULL,
ProductName varchar(50) NOT NULL,
OrderDate datetime NOT NULL DEFAULT NOW(),
PRIMARY KEY (OrderId)
)

NULL 值

// 我们如何仅仅选取在 "Address" 列中带有 NULL 值的记录呢？
// 我们必须使用 IS NULL 操作符：
SELECT LastName,FirstName,Address FROM Persons
WHERE Address IS NULL


// 我们如何仅仅选取在 "Address" 列中不带有 NULL 值的记录呢？
// 我们必须使用 IS NOT NULL 操作符：
SELECT LastName,FirstName,Address FROM Persons
WHERE Address IS NOT NULL

NULL 函数

SELECT ProductName,UnitPrice*(UnitsInStock+UnitsOnOrder)
FROM Products

在上面的实例中，如果有 “UnitsOnOrder” 值是 NULL，那么结果是 NULL。

SELECT ProductName,UnitPrice*(UnitsInStock+ISNULL(UnitsOnOrder,0))
FROM Products

上面，如果 “UnitsOnOrder” 是 NULL，则不会影响计算，因为如果值是 NULL 则 ISNULL() 返回 0：

持续更新中…