"Websites" 表中的 "id" 列指向 "access_log" 表中的字段 "site_id"。上面这两个表是通过 "site_id" 列联系起来的。摘自菜鸟教程
1.SQL SELECT TOP, LIMIT,ROWNUM子句
SELECT TOP 子句用于规定要返回的记录的数目。并非所有的数据库系统都支持SELECT TOP语句,MYSQL支持LIMIT语句来选取指定的条数数据,ORACLE可以使用ROWNUM来选取。
SQL Server语法
SELECT TOP number|percent column_name(s) FROM table_name;
MySQL语法
SELECT column_name(s) FROM table_name LIMIT number;
SELECT * FROM Persons LIMIT 5;
Oracle语法
SELECT column_name(s) FROM table_name WHERE ROWNUM <= number;
SELECT * FROM Persons WHERE ROWNUM <= 5;
MySQL SELECT LIMIT 实例:从 "Websites" 表中选取头两条记录
SELECT * FROM Websites WHERE LIMIT 2;
SQL SELECT TOP PERCENT 实例:还可以使用百分比作为参数。
从 websites 表中选取前面百分之 50 的记录:
SELECT TOP 50 PERCENT * FROM Websites;
2.SQL LIKE操作符
LIKE操作符用于在WHERE子句中搜索列中的指定模式。
SELECT column_name(s) FROM TABLE_NAME WHERE COLUMN_NAME LIKE PATTERN;
SQL LIKE 操作符实例:选取 name 以字母 "G" 开始的所有客户:
SELECT * FROM Websites WHERE name LIKE 'G%';
选取 name 以字母 "k" 结尾的所有客户:
SELECT * FROM Websites WHERE name LIKE '%K';
选取 name 包含模式 "oo" 的所有客户:
SELECT * FROM Websites WHERE name LIKE '%oo%';
通过使用 NOT 关键字,您可以选取不匹配模式的记录。
下面的 SQL 语句选取 name 不包含模式 "oo" 的所有客户:
SELECT * FROM Websites WHERE name NOT LIKE '%oo%';
3.SQL IN 操作符
IN操作符运行在WHERE子句中规定多个值
SELECT COLUMN_NAME(S) FROM TABLE_NAME WHERE COLUMN_NAME IN (value1, value2, value3,...);
IN 操作符实例:选取 name 为 "Google" 或 "菜鸟教程" 的所有网站:
SELECT * FROM Websites WHERE name IN ('Google','菜鸟教程');
4.SQL BETWEEN操作符
BETWEEN操作符用于选取介于两个之间的数值范围内的值,这些值可以是数值,文本或日期。
SELECT COLUMN_NAME(S) FROM TABLE_NAME WHERE COLUNM_NAME BETWEEN value1 AND value2;
BETWEEN 操作符实例:选取 alexa 介于 1 和 20 之间的所有网站:
SELECT * FROM Websites WHERE alexa BETWEEN 1 AND 20;
带有 IN 的 BETWEEN 操作符实例:选取 alexa 介于 1 和 20 之间但 country 不为 USA 和 IND 的所有网站:
SELECT * FROM Websites WHERE (alexa BETWEEN 1 AND 20) AND country NOT IN ('USA','IND');
带有文本值的 BETWEEN 操作符实例:选取 name 以介于 'A' 和 'H' 之间字母开始的所有网站:
SELECT * FROM Websites WHERE name BETWEEN 'A' AND 'H';
选取 name 不介于 'A' 和 'H' 之间字母开始的所有网站:
SELECT * FROM Websites WHERE name NOT BETWEEN 'A' AND 'H';
5.SQL JOIN
join 用于把来自两个或多个表的行结合起来,基于这些表之间的共同字段。
INNER JOIN:如果表中有至少一个匹配,则返回行
SELECT COLUMN_NAME(S) FROM TABLE1 INNER JOIN TABLE2 ON TABLE1.COLUMN_NAME = TABLE2.COLUMN_NAME;
将返回所有网站的访问记录
SELECT Websites.id, Websites.name, access_log.count, access_log.date FROM Websites INNER JOIN access_log ON Websites.id=access_log.site_id ORDER BY access_log.count;
LEFT JOIN: 即使右表中没有匹配,也从左表中返回所有的行;LEFT JOIN 称为 LEFT OUTER JOIN;LEFT JOIN 关键字从左表(Websites)返回所有的行,即使右表(access_log)中没有匹配。
SELECT COLUMN_NAME(S) FROM TABLE1 LEFT JOIN TABLE2 ON TABLE1.COLUMN_NAME = TABLE2.COLUNM_NAME;
SELECT COLUMN_NAME(S) FROM TABLE1 LEFT OUTER JOIN TABLE2 ON TABLE1.COLUMN_NAME=TABLE2.COLUMN_NAME;
将返回所有网站及他们的访问量(如果有的话)。
以下实例中我们把 Websites 作为左表,access_log 作为右表:
SELECT Websites.name, access_log.count, access_log.date FROM Websites LEFT JOIN access_log ON Websites.id=access_log.site_id ORDER BY access_log.count DESC;
RIGHT JOIN:即使左表中没有匹配,也从右表中返回所有的行;从右表(table2)返回所有的行,即使左表(table1)中没有匹配。如果左表中没有匹配,则结果为 NULL.
SELECT COLUMN_NAME(S) FROM TABLE1 RIGHT JOIN TABLE2 ON TABLE1.COLUMN_NAME = TABLE2.COLUMN_NAME;
SELECT COLUMN_NAME(S) FROM TABLE1 RIGHT OUTER JOIN TABLE2 ON TABLE1.COLUMN_NAME = TABLE2.COLUMN_NAME;
FULL JOIN:只要其中一个表中存在匹配,则返回行。MySQL中不支持 FULL OUTER JOIN
FULL OUTER JOIN 关键字只要左表(table1)和右表(table2)其中一个表中存在匹配,则返回行.
FULL OUTER JOIN 关键字结合了 LEFT JOIN 和 RIGHT JOIN 的结果。
SELECT COLUMN_NAME(S) FROM TABLE1 FULL OUTER JOIN TABLE2 ON TABLE1.COLUMN_NAEM = TABLE2.COLUMN_NAME;
6.SQL UNION操作符
UNION操作符用于合并两个或多个SELECT语句的结果集。UNION内部的每个SELECT语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时,每个SELECT语句中的列的顺序必须相同。
SELECT COLUMN_NAME(S) FROM TABLE1 UNION SELECT COLUMN_NAME(S) FROM TABLE2;
默认地,UNION操作符选取不同的值,如果允许重复的值,使用UNION ALL
SELECT COLUMN_NAME(S) FROM TABLE1 UNION ALL SELECT COLUMN_NAME(S) FROM TABLE2;
UNION结果集中的列名总是等于UNION中第一个SELECT语句中的列名。
SQL UNION 实例:从 "Websites" 和 "apps" 表中选取所有不同的country(只有不同的值):
SELECT country FROM Websites UNION SELECT country FROM apps ORDER BY country;
SQL UNION ALL 实例:从 "Websites" 和 "apps" 表中选取所有的country(也有重复的值):
SELECT country FROM Websites UNION ALL SELECT country FROM apps ORDER BY country;
带有 WHERE 的 SQL UNION ALL :从 "Websites" 和 "apps" 表中选取所有的中国(CN)的数据(也有重复的值):
SELECT country, name FROM Websites WHERE country='CN'
UNION ALL
SELECT country, app_name FROM apps WHERE country='CN'
ORDER BY country;
7.SQL SELECT INTO
SELECT INTO 语句从一个表复制数据,然后把数据插入到另一个新表中。MySQL 数据库不支持 SELECT ... INTO 语句,但支持 INSERT INTO ... SELECT 。
复制所有的列插入到新表中:
SELECT * INTO NEWTABLE [IN externaldb] FROM TABLE1;
复制希望的列插入到新表中:
SELECT COLUMN_NAME(S) INTO NEWTABLE [IN externaldb] FROM TABLE1;
创建 Websites 的备份复件:
SELECT * INTO WEBSITESBACKUP FROM Websites;
只复制一些列插入到新表中:
SELECT name, url INTO WEBSITESBACKUP FROM Websites;
只复制中国的网站插入到新表中:
SELECT * INTO WEBSITESBACKUP FROM Websites WHERE country='CN';
复制多个表中的数据插入到新表中:
SELECT Websites.name, access_log.count, access_log.date INTO WEBSITESBACKUP FROM Websites
LEFT JOIN access_log ON Websites.id=access_log.site_id;
8.SQL INSERT INTO SELECT
INSERT INTO SELECT 语句从一个表复制数据然后将数据插入到一个已存在的表中。目标表中任何已存在的行都不会受影响。
从一个表中复制所有的列插入到另一个已存在的表中;
INSERT INTO TABLE2 SELECT * FROM TABLE1;
只复制希望的列插入到另一个已存在的表中;
INSERT INTO TABLE2 (COLUMN_NAME(S)) SELECT COLUMN_NAME(S) FROM TABLE1;
复制 "apps" 中的数据插入到 "Websites" 中:
INSERT INTO Websites (name, country) SELECT app_name, country FROM apps;
只复 id=1 的数据到 "Websites" 中:
INSET INTO Websites (name, country) SELECT app_name, country FROM apps WHERE id=1;
9.SQL 约束
SQL约束用于规定表中的数据规则。如果存在违反约束的数据行为,行为会被约束终止。
NOT NULL - 指示某列不能存储 NULL 值。
强制 "ID" 列、 "LastName" 列以及 "FirstName" 列不接受 NULL 值:
CREATE TABLE Persons(
ID int NOT NULL,
Lastname varchar(255) NOT NULL,
Firstname varchar(255) NOT NULL,
Age int
);
添加 NOT NULL 约束
在一个已创建的表的 "Age" 字段中添加 NOT NULL 约束
ALTER TABLE Persons MODIFY Age int NOT NULL;
删除 NOT NULL 约束
在一个已创建的表的 "Age" 字段中删除 NOT NULL 约束
ALTER TABLE Persons MODIFY Age int NULL;
UNIQUE - 保证某列的每行必须有唯一的值。UNIQUE 和 PRIMARY KEY 约束均为列或列集合提供了唯一性的保证。PRIMARY KEY 约束拥有自动定义的 UNIQUE 约束。每个表可以有多个 UNIQUE 约束,但是每个表只能有一个 PRIMARY KEY 约束。
在 "Persons" 表创建时在 "P_Id" 列上创建 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 约束
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 约束
ALTER TABLE Persons ADD UNIQUE (P_Id)
当表已被创建时,如需命名 UNIQUE 约束,并定义多个列的 UNIQUE 约束
ALTER TABLE Persons ADD CONSTRAINT uc_PersonID UNIQUE (P_Id,LastName)
如需撤销 UNIQUE 约束
MYSQL:
ALTER TABLE PersonsDROP INDEX uc_PersonID
SQL Server / Oracle / MS Access:
ALTER TABLE Persons DROP CONSTRAINT uc_PersonID
PRIMARY KEY - NOT NULL 和 UNIQUE 的结合。确保某列(或两个列多个列的结合)有唯一标识,有助于更容易更快速地找到表中的一个特定的记录。
主键必须包含唯一的值。
主键列不能包含 NULL 值。
每个表都应该有一个主键,并且每个表只能有一个主键。
在 "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 语法:
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)
)
当表已被创建时,如需在 "P_Id" 列创建 PRIMARY KEY 约束,请使用下面的 SQL:
ALTER TABLE Persons
ADD PRIMARY KEY (P_Id)
如需命名 PRIMARY KEY 约束,并定义多个列的 PRIMARY KEY 约束,请使用下面的 SQL 语法:
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 - 保证一个表中的数据匹配另一个表中的值的参照完整性。
FOREIGN KEY 约束用于预防破坏表之间连接的行为。
FOREIGN KEY 约束也能防止非法数据插入外键列,因为它必须是它指向的那个表中的值之一
在 "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 语法:
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)
)
ALTER TABLE 时的 SQL FOREIGN KEY 约束
当 "Orders" 表已被创建时,如需在 "P_Id" 列创建 FOREIGN KEY 约束,请使用下面的 SQL:
ALTER TABLE Orders
ADD FOREIGN KEY (P_Id)
REFERENCES Persons(P_Id)
如需命名 FOREIGN KEY 约束,并定义多个列的 FOREIGN KEY 约束,请使用下面的 SQL 语法:
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 - 保证列中的值符合指定的条件。
如果对单个列定义 CHECK 约束,那么该列只允许特定的值。
如果对一个表定义 CHECK 约束,那么此约束会基于行中其他列的值在特定的列中对值进行限制。
在 "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 语法:
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')
)
ALTER TABLE 时的 SQL CHECK 约束
当表已被创建时,如需在 "P_Id" 列创建 CHECK 约束,请使用下面的 SQL:
ALTER TABLE Persons
ADD CHECK (P_Id>0)
如需命名 CHECK 约束,并定义多个列的 CHECK 约束,请使用下面的 SQL 语法:
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 - 规定没有给列赋值时的默认值。
在 "Persons" 表创建时在 "City" 列上创建 DEFAULT 约束:
CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255) DEFAULT 'Sandnes'
)
10.SQL CREATE INDEX
CREATE INDEX 语句用于在表中创建索引。
在不读取整个表的情况下,索引使数据库应用程序可以更快地查找数据。
SQL CREATE INDEX 语法
在表上创建一个简单的索引。允许使用重复的值:
CREATE INDEX index_name ON table_name (column_name)
SQL CREATE UNIQUE INDEX 语法
在表上创建一个唯一的索引。不允许使用重复的值:唯一的索引意味着两个行不能拥有相同的索引值。
CREATE UNIQUE INDEX index_name ON table_name (column_name)
CREATE INDEX 实例:在 "Persons" 表的 "LastName" 列上创建一个名为 "PIndex" 的索引:
CREATE INDEX PIndex ON Persons (LastName)
如果您希望索引不止一个列,您可以在括号中列出这些列的名称,用逗号隔开:
CREATE INDEX PIndex ON Persons (LastName, FirstName)