SQL 高级教程

1.SQL SELECT TOP 子句

SELECT TOP 子句用于规定要返回的记录的数目。对于拥有数千条记录的大型表来说，是非常有用的。

注意:并非所有的数据库系统都支持 SELECT TOP 语句。 MySQL 支持 LIMIT 语句来选取指定的条数数据， Oracle 可以使用 ROWNUM 来选取。

a.SQL Server / MS Access 语法

SELECT TOP number|percent column_name(s)

FROM table_name;

b.MySQL 语法

SELECT  column_name(s)

FROM table_name

LIMIT number;

c.Oracle语法

SELECT column_name(s)

FROM table_name

WHERE ROWNUM<=number;

b1:选取头两条记录

SELECT　＊　 FROM  Websites LIMIT 2;

######################################################

2.SQL SELECT TOP PERCENT实例

在 Microsoft SQL Server 中还可以使用百分比作为参数。

下面的 SQL 语句从 websites 表中选取前面百分之 50 的记录：

SELECT　TOP 50 PERCENT * FROM Websites;

#######################################################

3.SQL LIKE 操作符

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

语法：

SELECT column_name(s)

FROM table_name

WHERE  coulumn_name LIKE pattern;

a.选取name以字母“G”开始的所有客户

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

提示："%" 符号用于在模式的前后定义通配符（缺省字母）。

b.选取name以字母"K"结尾的所有客户

SELECT　*　FROM　Ｗebsites WHERE  name LIKE '%K'  ;

c.选取name包含"oo"的所有客户

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

'%a'    //以a结尾的数据

'a%'    //以a开头的数据

'%a%'    //含有a的数据

‘_a_’    //三位且中间字母是a的

'_a'    //两位且结尾字母是a的

'a_'    //两位且开头字母是a的

#######################################################

4.SQL  通配符

通配符可用于替代字符串中的任何其他字符。在 SQL 中，通配符与 SQL LIKE 操作符一起使用。SQL 通配符用于搜索表中的数据。

a1.下面的 SQL 语句选取 url 以字母 "https" 开始的所有网站：

SELECT * FROM Websites WHERE url LIKE 'https%';

a2.下面的 SQL 语句选取 url 包含模式 "oo" 的所有网站：

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

b1.选取 name 以一个任意字符开始，然后是 "oogle" 的所有客户：

SELECT * FROM Websites WHERE  name LIKE  '_oogle';

b2.下面的 SQL 语句选取 name 以 "G" 开始，然后是一个任意字符，然后是 "o"，然后是一个任意字符，然后是 "le" 的所有网站

SELECT * FROM Websites WHERE name LIKE 'G_o_le';

c.MySQL 中使用 REGEXP 或 NOT REGEXP 运算符 (或 RLIKE 和 NOT RLIKE) 来操作正则表达式。

c1.下面的 SQL 语句选取 name 以 "G"、"F" 或 "s" 开始的所有网站：

SELECT * FROM Websites WHERE name REFEXP '^[GFs]';

c2.下面的 SQL 语句选取 name 以 A 到 H 字母开头的网站：

SELECT * FROM WHERE name REGEXP '^[A_H]';

c3.下面的 SQL 语句选取 name 不以 A 到 H 字母开头的网站：

SELECT * FROM Websites WHERE name  REFEXP '^[^A_H]';

d.

首先说下LIKE命令都涉及到的通配符：

% 替代一个或多个字符

_ 仅替代一个字符

[charlist] 字符列中的任何单一字符

[^charlist]或者[!charlist] 不在字符列中的任何单一字符

其中搭配以上通配符可以让LIKE命令实现多种技巧：

1、LIKE'Mc%' 将搜索以字母 Mc 开头的所有字符串（如 McBadden）。

2、LIKE'%inger' 将搜索以字母 inger 结尾的所有字符串（如 Ringer、Stringer）。

3、LIKE'%en%' 将搜索在任何位置包含字母 en 的所有字符串（如 Bennet、Green、McBadden）。

4、LIKE'_heryl' 将搜索以字母 heryl 结尾的所有六个字母的名称（如 Cheryl、Sheryl）。

5、LIKE'[CK]ars[eo]n' 将搜索下列字符串：Carsen、Karsen、Carson 和 Karson（如 Carson）。

6、LIKE'[M-Z]inger' 将搜索以字符串 inger 结尾、以从 M 到 Z 的任何单个字母开头的所有名称（如 Ringer）。

7、LIKE'M[^c]%' 将搜索以字母 M 开头，并且第二个字母不是 c 的所有名称（如MacFeather）。

#######################################################

5.SQL  IN 操作符

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

语法：

SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1,value2,...);

a.下面的 SQL 语句选取 name 为 "Google" 或 "菜鸟教程" 的所有网站：

SELECT  * FROM  Websites WHERE  name IN('Google','菜鸟教程')；

b.IN 与 = 的异同

•  相同点：均在WHERE中使用作为筛选条件之一、均是等于的含义
•  不同点：IN可以规定多个值，等于规定一个值

IN

SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1,value2,...);

=

SELECT column_name(s)
FROM table_name
WHERE column_name=value1;

#######################################################

6.SQL  BETWEEN 操作符

BETWEEN 操作符用于选取介于两个值之间的数据范围内的值。这些值可以是数值、文本或者日期。

语法：

SELECT  column_name(s)
FROM  table_name
WHERE  column_name BETWEEN  value1 AND  value2;

a.下面的 SQL 语句选取 alexa 介于 1 和 20 之间的所有网站：

SELECT   *  FROM Websites WHERE alexa BETWEEN 1 AND 20;

b.如需显示不在上面实例范围内的网站，请使用 NOT BETWEEN：

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

c.下面的 SQL 语句选取alexa介于 1 和 20 之间但 country 不为 USA 和 IND 的所有网站：

SELECT   *  FROM Websites WHERE (alexa  not BETWEEN 1 AND 20) AND NOT country IN ('USA',"IND');

d.下面的 SQL 语句选取 name 以介于 'A' 和 'H' 之间字母开始的所有网站：

SELECT   *  FROM Websites WHERE name BETWEEN 'A' AND 'H';

c.带有日期值的 BETWEEN 操作符实例-下面的 SQL 语句选取 date 介于 '2016-05-10' 和 '2016-05-14' 之间的所有访问记录：

SELECT * FROM  acess _log

WHERE date BETWEEN '2016-05-10' AND '2016-05-14';

d.请注意，在不同的数据库中，BETWEEN 操作符会产生不同的结果！

在某些数据库中，BETWEEN 选取介于两个值之间但不包括两个测试值的字段。
在某些数据库中，BETWEEN 选取介于两个值之间且包括两个测试值的字段。
在某些数据库中，BETWEEN 选取介于两个值之间且包括第一个测试值但不包括最后一个测试值的字段。

因此，请检查您的数据库是如何处理 BETWEEN 操作符！

#######################################################

7.SQL 别名

通过使用 SQL，可以为表名称或列名称指定别名。基本上，创建别名是为了让列名称的可读性更强。

列别名语法：

SELECT column_name  AS   alias_name 

FROM table_name;

表别名语法：

SELECT column_name(s)

FROM table_name  AS alias_name; 

a. 下面的 SQL 语句指定了两个别名，一个是 name 列的别名，一个是 country 列的别名。提示：如果列名称包含空格，要求使用双引号或方括号：

SELECT name AS  n,country AS  c

FROM  Websites;

b.把三个列（url、alexa 和 country）结合在一起，并创建一个名为 "site_info" 的别名：

SELECT name,CONCAT( url ,',',alexa,',',country)  AS   site_info ;

c.下面的 SQL 语句选取 "菜鸟教程" 的所访问记录。我们使用 "Websites" 和 "access_log" 表，并分别为它们指定表别名 "w" 和 "a"（通过使用别名让 SQL 更简短）：

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="菜鸟教程";

d.不带别名的相同的 SQL 语句：

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

 

#######################################################

8.SQL  连接（JOIN)

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

SQL JOIN 子句用于把来自两个或多个表的行结合起来，基于这些表之间的共同字段。

最常见的 JOIN 类型：SQL INNER JOIN（简单的 JOIN）。 SQL INNER JOIN 从多个表中返回满足 JOIN 条件的所有行。

请注意，"Websites" 表中的 "id" 列指向 "access_log" 表中的字段 "site_id"。上面这两个表是通过 "site_id" 列联系起来的。

a.然后，如果我们运行下面的 SQL 语句（包含 INNER JOIN）：

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

b.不同的 SQL JOIN

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

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

c.首先，连接的结果可以在逻辑上看作是由SELECT语句指定的列组成的新表。

左连接与右连接的左右指的是以两张表中的哪一张为基准，它们都是外连接。

外连接就好像是为非基准表添加了一行全为空值的万能行，用来与基准表中找不到匹配的行进行匹配。假设两个没有空值的表进行左连接，左表是基准表，左表的所有行都出现在结果中，右表则可能因为无法与基准表匹配而出现是空值的字段。

这部分主要涉及的是表连接的逻辑问题，教程主讲语法的话这部分确实需要自己再去搜资料

#######################################################

9.SQL   INNER JOIN关键字

INNER JOIN 关键字在表中存在至少一个匹配时返回行。

语法：

SELECT column_name(s)

FROM table1

INNER JOIN table2

ON table1.column_name=table2.column_name;

或者

SELECT column_name(s)

FROM table1

JOIN table2

ON table1.column_name=table2.column_name;

a.下面的 SQL 语句将返回所有网站的访问记录：

SELECT  Websites.name,access_log.count,access_log.date

FROM Websites

INNER JOIN Websites.id=access_log.site.id

ORDER BY access_log.count;

注释：INNER JOIN 关键字在表中存在至少一个匹配时返回行。如果 "Websites" 表中的行在 "access_log" 中没有匹配，则不会列出这些行。

#######################################################

10.SQL  LEFT JOIN 关键字

LEFT JOIN 关键字从左表（table1）返回所有的行，即使右表（table2）中没有匹配。如果右表中没有匹配，则结果为 NULL。

语法：

SELECT column_name(s)

FROM table1

LEFT JOIN table2

ON table1.column_name=table2.column;

或者：

SELECT column_name(s)

FROM table1

LEFT OUTER JOIN table2

ON table1.column_name=table2.column;

a.下面的 SQL 语句将返回所有网站及他们的访问量（如果有的话）。以下实例中我们把 Websites 作为左表，access_log 作为右表：

SELECT Websites.name,access_log.count,assess_log.data

FROM  Websites

LEFT JOIN access_log

ON  Websites.id=access.log.site.id

ORDER BY access_log.count DESC;

注释：LEFT JOIN 关键字从左表（Websites）返回所有的行，即使右表（access_log）中没有匹配。

#######################################################

11.SQL  RIGHT JOIN 关键字

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;

a.下面的 SQL 语句将返回网站的访问记录。以下实例中我们把 access_log 作为左表，Websites 作为右表：

SELECT Websites.name,access_log.count,access_log.date

FROM access_log

RIGHT JOIN  Websites

ON  access_log.site_id=Websites.id

ORDER  BY access_log.count DESC;

注释：RIGHT JOIN 关键字从右表（Websites）返回所有的行，即使左表（access_log）中没有匹配。

#######################################################

12.SQL  FULL OUTER JOIN 关键字

FULL OUTER JOIN 关键字只要左表（table1）和右表（table2）其中一个表中存在匹配，则返回行.

FULL OUTER JOIN 关键字结合了 LEFT JOIN 和 RIGHT JOIN 的结果。

语法：

SELECT column_name

FROM  table1

FULL OUTER JOIN table2

ON  table1.column_name=table2.column_name

a.下面的 SQL 语句选取所有网站访问记录。MySQL中不支持 FULL OUTER JOIN，你可以在 SQL Server 测试以下实例。

SELECT Websites.name, access_log.count, access_log.date
FROM Websites
FULL OUTER JOIN access_log
ON Websites.id=access_log.site_id
ORDER BY access_log.count DESC;

注释： FULL OUTER JOIN 关键字返回左表（Websites）和右表（access_log）中所有的行。如果 "Websites" 表中的行在 "access_log" 中没有匹配或者 "access_log" 表中的行在 "Websites" 表中没有匹配，也会列出这些行。
#######################################################

13.SQL UNION  操作符

SQL 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 语句中的列名。

a.下面的 SQL 语句从 "Websites" 和 "apps" 表中选取所有不同的country（只有不同的值）：

SELECT country FROM Websites

UNION 

SELECT country FROM apps

ORDER BY country;

注释： UNION 不能用于列出两个表中所有的country。如果一些网站和APP来自同一个国家，每个国家只会列出一次。UNION 只会选取不同的值。请使用 UNION ALL 来选取重复的值！

使用UNION命令时需要注意，只能在最后使用一个ORDER BY命令，是将两个查询结果合在一起之后，再进行排序！绝对不能写两个ORDER BY命令。

另外，在使用ORDER BY排序时，注意两个结果的别名保持一致，使用别名排序很方便。当然也可以使用列数。

#######################################################

14.SQL SELECT INTO 语句

通过 SQL，您可以从一个表复制信息到另一个表。SELECT INTO 语句从一个表复制数据，然后把数据插入到另一个新表中。

语句：复制所有的列到新表

SELECRT *
INTO newtable [IN externaldb]
FROM table1;

只复制希望的列插入到新表

SELECRT column_name
INTO newtable [IN externaldb]
FROM table1;

a.创建Websites 的备份文件

SELECT *
INTO WebsitesBackup2016
FROM Websites;

b.只复制希望的列插入到新表

SELECT  name,url
INTO WebsitesBackup2016
FROM Websites;

c.只复制中国的网站插入到新表

SELECT *
INTO WebsitesBackup2016
FROM Websites 
WHERE country='CN';

d.复制多个表中的数据插入到新表中：

SELECRT  Websites.name, access_log.count, access_log.date
INTO Websites2016
FROM Websites
LEFT JOIN access_log
ON Websites.id=access_log.site.id

e. 提示： SELECT INTO 语句可用于通过另一种模式创建一个新的空表。只需要添加促使查询没有数据返回的 WHERE 子句即可：

SELECT *
INTO  newtable
FROM  table1
WHERE 1=0;

#######################################################

15.SQL  INSERT INTO SELECT 语句

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

语法：

我们可以从一个表中复制所有的列插入到另一个已存在的表中：

INSETRT　INTO　table2

SELECT * FROM   table1

或者我们可以只复制希望的列插入到另一个已存在的表中：

INSERT  INTO table2  (column_name(s))

SELECT column_name(s)  FROM table1;

#######################################################

16.SQL  CREAT DATABASE 语句-(用于创建数据库）

语法：

CREATE DATABASE dbname;

#######################################################

17.SQL  CREATE TABLE 语句-（用于创建数据库中的表）

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

语法：

CERATE TABLE table_name

(

 column_name1  data_type(size),

 column_name2 data_type(size),

 column_name3 data_type(size),

......

);

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

size 参数规定表中列的最大长度。

a.现在我们想要创建一个名为 "Persons" 的表，包含五列：PersonID、LastName、FirstName、Address 和 City。

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

#######################################################

18.SQL  约束（Constraints)

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

如果存在违反约束的数据行为，行为会被约束终止。

约束可以在创建表时规定（通过 CREATE TABLE 语句），或者在表创建之后规定（通过 ALTER TABLE 语句）。

语法：

CREATE  TABLE  table_name
(
column_name1 data_type(size)  constraints_name,
column_name2 data_type(size)  constraints_name,
column_name3 data_type(size)  constraints_name,

....
);

在 SQL 中，我们有如下约束：

• a.NOT NULL - 指示某列不能存储 NULL 值。

  NOT NULL 约束强制列不接受 NULL 值。NOT NULL 约束强制字段始终包含值。这意味着，如果不向字段添加值，就无法插入新记录或者更新记录。

• 下面的 SQL 强制 "P_Id" 列和 "LastName" 列不接受 NULL 值：

  CREATE TABLE Persons
  (
  P_Id int NOT NULL,
  LastName varchar(255) NOT NULL,
  FirstName varchar(255),
  Address varchar(255),
  City varchar(255)
  )
  
  
• b1.UNIQUE - 保证某列的每行必须有唯一的值。

• UNIQUE 和 PRIMARY KEY 约束均为列或列集合提供了唯一性的保证。

  PRIMARY KEY 约束拥有自动定义的 UNIQUE 约束。

  请注意，每个表可以有多个 UNIQUE 约束，但是每个表只能有一个 PRIMARY KEY 约束。

  b2.MySQL / SQL Server / Oracle / MS Access：

  ALTER TABLE Persons
  ADD UNIQUE (P_Id)

  b3.如需命名 UNIQUE 约束，并定义多个列的 UNIQUE 约束，请使用下面的 SQL 语法：

  MySQL / SQL Server / Oracle / MS Access：

  ALTER TABLE Persons
  ADD CONSTRAINT uc_PersonID UNIQUE (P_Id,LastName)
  b4.撤销 UNIQUE 约束

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

  MySQL：

  ALTER TABLE Persons
  DROP INDEX uc_PersonID

  SQL Server / Oracle / MS Access：

  ALTER TABLE Persons
  DROP CONSTRAINT uc_PersonID

• 当表已被创建时，如需在 "P_Id" 列创建 UNIQUE 约束，请使用下面的 SQL：

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

• PRIMARY KEY - NOT NULL 和 UNIQUE 的结合。确保某列（或两个列多个列的结合）有唯一标识，有助于更容易更快速地找到表中的一个特定的记录。
• FOREIGN KEY - 保证一个表中的数据匹配另一个表中的值的参照完整性。
• CHECK - 保证列中的值符合指定的条件。
• DEFAULT - 规定没有给列赋值时的默认值。

#######################################################

19.SQL   CREATE INDEX 语句

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)

注释：用于创建索引的语法在不同的数据库中不一样。因此，检查您的数据库中创建索引的语法。

---

CREATE INDEX 实例

下面的 SQL 语句在 "Persons" 表的 "LastName" 列上创建一个名为 "PIndex" 的索引：

CREATE INDEX PIndex
ON Persons (LastName)

如果您希望索引不止一个列，您可以在括号中列出这些列的名称，用逗号隔开：

CREATE INDEX PIndex
ON Persons (LastName, FirstName)

#######################################################

20.SQL  撤销索引。撤销表以及撤销数据库

DROP INDEX 语句

DROP INDEX 语句用于删除表中的索引。

用于 MS Access 的 DROP INDEX 语法：

DROP INDEX index_name ON table_name

用于 MS SQL Server 的 DROP INDEX 语法：

DROP INDEX table_name.index_name

用于 DB2/Oracle 的 DROP INDEX 语法：

DROP INDEX index_name

用于 MySQL 的 DROP INDEX 语法：

ALTER TABLE table_name DROP INDEX index_name

---

DROP TABLE 语句

DROP TABLE 语句用于删除表。

DROP TABLE table_name

---

DROP DATABASE 语句

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

DROP DATABASE database_name

---

TRUNCATE TABLE 语句

如果我们仅仅需要删除表内的数据，但并不删除表本身，那么我们该如何做呢？

请使用 TRUNCATE TABLE 语句：

TRUNCATE TABLE table_name