SQL连载(三)----SQL语法

这一篇文章主要讲解一些SQL的一些基本语法:

1、SQL 的语法并不按照语法顺序执行

SQL 语句有一个让大部分人都感到困惑的特性，就是：SQL 语句的执行顺序跟其语句的语法顺序并不一致。SQL 语句的语法顺序是：

• SELECT[DISTINCT]
• FROM
• WHERE
• GROUP BY
• HAVING
• UNION
• ORDER BY

为了方便理解，上面并没有把所有的 SQL 语法结构都列出来，但是已经足以说明 SQL 语句的语法顺序和其执行顺序完全不一样，就以上述语句为例，其执行顺序为：

• FROM
• WHERE
• GROUP BY
• HAVING
• SELECT
• DISTINCT
• UNION
• ORDER BY

关于 SQL 语句的执行顺序，有三个值得我们注意的地方：

1、 FROM 才是 SQL 语句执行的第一步，并非 SELECT 。数据库在执行 SQL 语句的第一步是将数据从硬盘加载到数据缓冲区中，以便对这些数据进行操作。

2、 SELECT 是在大部分语句执行了之后才执行的，严格的说是在 FROM 和 GROUP BY 之后执行的。理解这一点是非常重要的，这就是你不能在 WHERE 中使用在 SELECT 中设定别名的字段作为判断条件的原因。

SELECT A.x + A.y AS zFROM AWHERE z = 10 -- z 在此处不可用，因为SELECT是最后执行的语句！

如果你想重用别名z，你有两个选择。要么就重新写一遍 z 所代表的表达式：

SELECT A.x + A.y AS zFROM AWHERE (A.x + A.y) = 10

3、 无论在语法上还是在执行顺序上， UNION 总是排在在 ORDER BY 之前。很多人认为每个 UNION 段都能使用 ORDER BY 排序，但是根据 SQL 语言标准和各个数据库 SQL 的执行差异来看，这并不是真的。尽管某些数据库允许 SQL 语句对子查询（subqueries）或者派生表（derived tables）进行排序，但是这并不说明这个排序在 UNION 操作过后仍保持排序后的顺序。

2、SQL中的注释

Oracle
REM单行注释
-- 单行注释
/*多行注释*/ 


SQL Server
-- 单行注释
/*多行注释*/ 


MySQL
#单行注释
-- 单行注释（特别注意，-- 后有个空格！！！）
/*多行注释*/ 


总结：
/*多行注释*/ 和-- 单行注释都差不多，MySQL要注意。
非标准的#和REM最好还是不要用了
为了兼容-- 后面最好都加空格

3、常用的CURD操作

(1)、增

A、创建数据库 Create Database

用途：建立新的数据库。

语法：

CREATE DATABASE database_name

B、创建表 Create Table

用途：建立新的表。

语法：

CREATE TABLE table_name (column_name1 data_type, column_name2 data_type, .......)

C、新增表数据 Insert Into

用途：在表中插入新行。

语法：

插入一行数据：

INSERT INTO table_nameVALUES (value1, value2,....)

插入一行数据在指定的字段上：

INSERT INTO table_name (column1, column2,...)VALUES (value1, value2,....)

实例：

"Persons" 表中的原始数据：

LastNameFirstNameAddressCityPettersenKariStorgt 20Stavanger

运行下面的 SQL 插入一行数据：

INSERT INTO Persons VALUES ('Hetland', 'Camilla', 'Hagabakka 24', 'Sandnes')

插入后 "Persons" 表中的数据为：

LastNameFirstNameAddressCityPettersenKariStorgt 20StavangerHetlandCamillaHagabakka 24Sandnes

运行下面的 SQL 插入一行数据在指定的字段上：

INSERT INTO Persons (LastName, Address)VALUES ('Rasmussen', 'Storgt 67')

插入后 "Persons" 表中的数据为：

LastNameFirstNameAddressCityPettersenKariStorgt 20StavangerHetlandCamillaHagabakka 24SandnesRasmussen Storgt 67 

D、创建索引

语法：

CREATE [索引类型] INDEX 索引名称ON 表名(列名)WITH FILLFACTOR = 填充因子值0~100GO

实例：

USE 库名GOIF EXISTS (SELECT * FROM SYSINDEXES WHERE NAME='IX_TEST_TNAME')--检测是否已经存在IX_TEST_TNAME索引DROP INDEX TEST.IX_TEST_TNAME--如果存在则删除--创建索引CREATE NONCLUSTERED INDEX IX_TEST_TNAME --创建一个非聚集索引ON TEST(TNAME) --为TEST表的TNAME字段创建索引WITH FILLFACTOR = 30 --填充因子为30%GO

查询:

SELECT * FROM TEST(INDEX = IX_TEST_TNAME) WHERE TNAME = 'A' --指定按‘IX_TEST_TNAME’索引查询

总结：

1.什么是索引：数据库中的索引是某个表中一列或多列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。

2.分类：

唯一索引(UNIQUE)：不允许两行具有相同的索引值（创建了唯一约束，系统将自动创建唯一索引）
主键索引：主键索引要求主键中的每个值是唯一的，（创建主键自动创建主键索引）
聚集索引(CLUSTERED)：表中各行的物理顺序与键值的逻辑（索引）顺序相同，表中只能包含一个聚集索引，主键列默认为聚集索引
非聚集索引(NONCLUSTERED)：表中各行的物理顺序与键值的逻辑（索引）顺序不匹配，表中可以有249个非聚集索引

3.创建索引的标准：用于频繁搜索的列；用于对数据进行排序的列

注意：如果表中仅有几行，或列中只包含几个不同的值，不推荐创建索引，因为SQL Server 在小型表中用索引搜索数据所花的时间比逐行搜索更长。

(2)、删

A、删除数据库 Drop Database

用途：移除原有的数据库。

语法：

DROP DATABASE database_name

B、删除表中的数据 Delete

用途：删除表中的数据。

语法：

DELETE FROM table_name WHERE column_name = some_value

实例：

"Person" 表中的原始数据：

LastNameFirstNameAddressCityNilsenFredKirkegt 56StavangerRasmussenNinaStien 12Stavanger

删除 Person 表中 LastName 为 "Rasmussen" 的数据：

DELETE FROM Person WHERE LastName = 'Rasmussen'

执行删除语句后 "Person" 表中的数据为：

LastNameFirstNameAddressCityNilsenFredKirkegt 56Stavanger

D、删除索引 DROP INDEX

用途：删除表中的索引。

语法：

DROP INDEX table_name on index_name

(3)、改

A、改变数据库表中的内容 Update

用途：更新表中原有数据。

语法：

UPDATE table_name SET column_name = new_valueWHERE column_name = some_value

实例：

"Person" 表中的原始数据：

LastNameFirstNameAddressCityNilsenFredKirkegt 56StavangerRasmussen Storgt 67 

运行下面的 SQL 将 Person 表中 LastName 字段为 "Rasmussen" 的 FirstName 更新为 "Nina"：

UPDATE Person SET FirstName = 'Nina'WHERE LastName = 'Rasmussen'

更新后 "Person" 表中的数据为：

LastNameFirstNameAddressCityNilsenFredKirkegt 56StavangerRasmussenNinaStorgt 67

同样的，用 UPDATE 语句也可以同时更新多个字段：

UPDATE PersonSET Address = 'Stien 12', City = 'Stavanger'WHERE LastName = 'Rasmussen'

更新后 "Person" 表中的数据为：

LastNameFirstNameAddressCityNilsenFredKirkegt 56StavangerRasmussenNinaStien 12Stavanger

B、修改表 Alter Table

用途：在已经存在的表中增加或者移除字段。

语法：

ALTER TABLE table_name ADD column_name datatypeALTER TABLE table_name DROP COLUMN column_name

注意：某些数据库管理系统不允许移除表中的字段。

实例：

"Person" 表中的原始数据：

LastNameFirstNameAddressPettersenKariStorgt 20

在 Person 表中增加一个名为 City 的字段：

ALTER TABLE Person ADD City varchar(30)

增加后表中数据如下：

LastNameFirstNameAddressCityPettersenKariStorgt 20

移除 Person 表中原有的 Address 字段：

ALTER TABLE Person DROP COLUMN Address

移除后表中数据如下：

LastNameFirstNameCityPettersenKari

(4)、查

A、简单查询

1.基本查询

2.Where(条件)
3.BETWEEN
4.LIKE
5.AND
6.OR 
7.IN
8.NOT 否定
9.ORDER BY(排序)
10.AS(Alias)
11.Distinct
12.MAX/MIN
13.SUM
14.AVG
15.COUNT
16.GROUP BY
17.HAVING
18.TOP
19.Case语句

B、子查询

   子查询本质上是嵌套进其他SELECT,UPDATE,INSERT,DELETE语句的一个被限制的SELECT语句,在子查询中，只有下面几个子句可以使用

1.SELECT子句(必须)
2.FROM子句(必选)
3.WHERE子句(可选)
4.GROUP BY(可选)
5.HAVING(可选)
6.ORDER BY(只有在TOP关键字被使用时才可用) 

C、连接(联合)查询

基本语法与方法

　　SQL-92标准所定义的FROM子句的连接语法格式为：

FROM join_table join_type join_table   [ON (join_condition)] 

其中join_table指出参与连接操作的表名，连接可以对同一个表操作，也可以对多表操作，对同一个表操作的连接又称做自连接。

join_type 指出连接类型，可分为三种：内连接、外连接和交叉连接。内连接(INNER JOIN)使用比较运算符进行表间某(些)列数据的比较操作，并列出这些表中与连接条件相匹配的数据行。根据所使用的比较方式不同。


　　1、内连接又分为等值连接、自然连接和不等连接三种。 


　　2、外连接分为左外连接(LEFT OUTER JOIN或LEFT JOIN)、右外连接(RIGHT OUTER JOIN或RIGHT JOIN)和全外连接(FULL OUTER JOIN或FULL JOIN)三种。与内连接不同的是，外连接不只列出与连接条件相匹配的行，而是列出左表(左外连接时)、右表(右外连接时)或两个表(全外连接时)中所有符合搜索条件的数据行。 


　　3、交叉连接(CROSS JOIN)没有WHERE 子句，它返回连接表中所有数据行的笛卡尔积，其结果集合中的数据行数等于第一个表中符合查询条件的数据行数乘以第二个表中符合查询条件的数据行数。 


　　连接操作中的ON (join_condition) 子句指出连接条件，它由被连接表中的列和比较运算符、逻辑运算符等构成。 


　　无论哪种连接都不能对text、ntext和image数据类型列进行直接连接，但可以对这三种列进行间接连接。例如：

SELECT p1.pub_id,p2.pub_id,p1.pr_info 　　FROM pub_info AS p1 INNER JOIN pub_info AS p2 　　ON DATALENGTH(p1.pr_info)=DATALENGTH(p2.pr_info)

一、内连接(INNER JOIN)

先来看下表:

IdCard表:

User表:

对应的SQL文件为:

CREATE TABLE `idcard` (  `id` int(11) NOT NULL,  `IdNum` varchar(255) DEFAULT NULL,  PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;CREATE TABLE `user` (  `id` int(11) NOT NULL,  `name` varchar(255) DEFAULT NULL,  `age` int(11) DEFAULT NULL,  `card_id` int(11) DEFAULT NULL,  PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;

内连接查询操作列出与连接条件匹配的数据行，它使用比较运算符比较被连接列的列值。内连接分三种：

1、等值连接：在连接条件中使用等于号(=)运算符比较被连接列的列值，其查询结果中列出被连接表中的所有列，包括其中的重复列。 

例如：列出idcard和user表中相对应的数据

select * from idcard as i INNER JOIN User as u ON i.id=u.card_id

等价于：SELECT * FROM idcard AS i ,User AS u WHERE i.id=u.card_id

查询结果：

2、不等连接： 在连接条件使用除等于运算符以外的其它比较运算符比较被连接的列的列值。这些运算符包括>、>=、<=、<、!>、!<和<>。 

例如：  

SELECT * FROM idcard AS i INNER JOIN User AS u ON i.id > u.card_id

查询结果：

3、自然连接：在连接条件中使用等于(=)运算符比较被连接列的列值，但它使用选择列表指出查询结果集合中所包括的列，并删除连接表中的重复列。 

例如：SELECT * FROM idcard  NATRUAL JOIN user 

查询结果与等值连接一样，只是去掉了重复的id那一列。

二、外连接(outer join)

内连接时，返回查询结果集合中的仅是符合查询条件( WHERE 搜索条件或 HAVING 条件)和连接条件的行。而采用外连接时，它返回到查询结果集合中的不仅包含符合连接条件的行，而且还包括左表(左外连接时)、右表(右外连接时)或两个边接表(全外连接)中的所有数据行。

1、左外连接(left outer join)

左外连接会返回左表的所有记录和右表中匹配记录的组合(如果右表中无匹配记录, 来自于右表的所有列的值设为 NULL). 如果左表的一行在右表中存在多个匹配行, 那么左表的行会复制和右表匹配行一样的数量, 并进行组合生成连接结果

例如：

SELECT *  FROM  idcard LEFT OUTER JOIN user ON idcard.id = user.card_id

查询结果：

2、右外连接(right outer join)

右外连接, 亦简称右连接, 它与左外连接完全类似, 只不过是作连接的表的顺序相反而已.右连接操作返回右表的所有行和这些行在左表中匹配的行(没有匹配的, 来源于左表的列值设为 NULL).

例如：

SELECT * FROM idcard RIGHT OUTER JOIN user ON idcard.id= user.card_id

等价于：

SELECT * FROM idcard LEFT OUTER JOIN user ON idcard.id= user.card_id

查询结果：

实际上显式的右连接很少使用, 因为它总是可以被替换成左连接--换换表的位置就可以了，所以上面两条语句是等价的。

3、全连接(full outer join)

全连接是左右外连接的并集. 连接表包含被连接的表的所有记录, 如果缺少匹配的记录, 即以 NULL 填充.。一些数据库系统(如 MySQL)并不直接支持全连接, 但它们可以通过左右外连接的并集(参: union)来模拟实现

例如：SELECT * FROM  idcard FULL OUTER JOIN user ON idcard.id=user.idcard

Mysql不支持上面的语句所以采用Union来实现:

SELECT * FROM idcardLEFT JOIN user ON idcard.id = user.card_idUNIONSELECT * FROM idcardRIGHT JOIN user ON idcard.id = user.card_id

查询结果：

三、自然连接(Natural join)

自然连接(Natural join)是一种特殊的等值连接，它要求两个关系中进行比较的分量必须是相同的属性组，两表中的所有名称相同的列都将被比较，并且在结果中把重复的属性列去掉，结果表中两表中名称相同的列只出现一次.。而等值连接并不去掉重复的属性列。(sqlserver 不支持 自然连接)

例如：SELECT * FROM idcard  NATRUAL JOIN user

查询结果与等值连接一样，只是去掉了重复的id那一列。

四、交叉连接(Cross join)

交叉连接(cross join)，又称笛卡尔连接(cartesian join)或叉乘(Product)，它是所有类型的内连接的基础。把表视为行记录的集合，交叉连接即返回这两个集合的笛卡尔积，返回到结果集合中的数据行数等于第一个表中符合查询条件的数据行数乘以第二个表中符合查询条件的数据行数。这其实等价于内连接的链接条件为"永真"，或连接条件不存在.。
    

如果 A 和 B 是两个集合，它们的交叉连接就记为: A × B.

例如： SELECT * FROM idcard CROSS JOIN user

等价于：SELECT * FROM idcard,user

查询结果:

E、集合查询

为了合并多个SELECT语句的结果，可以使用集合操作，实现操作的并、交、差。集合操作符包括UNION、NUION ALL、INTERSECT和MINUS。

1、并操作【union】的嵌套查询

语句范例 ：(select 部门编号 from 雇员表)  union  (select 部门编号 from 部门表)

说明：并操作就是集合中并集的概念。属于集合A或集合B的元素的总和就是并集。

注：该操作的嵌套查询要求属性具有相同的定义，包括类型和取值范围。

2、交操作【intersect】的嵌套查询 

语句范例 ：(select 部门编号 from 雇员表)  intersect(select 部门编号 from 部门表)

说明：   交操作就是集合中交集的概念。属于集合A且属于集合B的元素的总和就是交集。

注：该操作的嵌套查询要求属性具有相同的定义，包括类型和取值范围。

3、差操作【minus】的嵌套查询

语句范例 ：(select 部门编号 from 部门表)  minus(select 部门编号 from 雇员表)

说明：差操作就是集合中差集的概念。属于集合A(前一个孒查询结果) 且不属于集合B(后一个孒查询结果)的元素的总和就是差集。

注：该操作的嵌套查询要求属性具有相同的定义，包括类型和取值范围。

F、分页查询

不同的数据库，SQL写法都不一样，下面做一汇总：

 数据库

 分页查询语句

 说明

 MySQL

   "QUERY_SQL limit ?,?"           使用limit关键字，第一个"?"是起始行号，
  第二个"?"是返回条目数

 Oracle

 SELECT * FROM 
 ( SELECT A.*, ROWNUM RN  FROM  
 (QUERY_SQL ) A  WHERE ROWNUM 
 <= ？) WHERE RN >= ？

 结合rownum关键字，利用嵌套三层select 
 语句实现。第一个"?"表示终止行号，
  第二个"?"表示其实行号

 sql server

 尚无通用语句 可使用top n来返回前n条记录或使用存储过程

 DB2

 假设查询语句：select t1.* from t1 order
 by t1.id； 分页语句可为：
 "select * from ( select rownumber() over 
  (order by t1.id) as row_, t1.* from t1 
  order by t1.id) as temp_ where row_ 
  between ?+1 and ?"  返回两个"?"之间的记录

 InterBase

 “QUERY_SQL row ? to ?”  返回两个"?"之间的记录 PostgreSQL “QUERY_SQL limit ? offset ?”  第一个"?"为起始行号，第二个"?"代表
  返回记录数


SQL Server分页SQL写法比较多,以下是常见的几种方法:

方法1： 适用于 SQL Server 2000/2005

SELECT TOP 页大小 *FROM table1WHERE id NOT IN          (          SELECT TOP 页大小*(页数-1) id FROM table1 ORDER BY id          )ORDER BY id

方法2：

适用于 SQL Server 2000/2005 

--顺序写法：SELECT TOP 页大小 *FROM table1WHERE id >=(SELECT ISNULL(MAX(id),0) FROM (SELECT TOP 页大小*(页数-1)+1 id FROM table1 ORDER BY id) A)ORDER BY id--降序写法：SELECT TOP 页大小 *FROM table1WHERE id <=(SELECT ISNULL(MIN(id),0) FROM (SELECT TOP 页大小*(页数-1)+1 id FROM table1 ORDER BY id Desc) A)ORDER BY id Desc

方法3：

适用于 SQL Server 2005

SELECT TOP 页大小 * FROM         (         SELECT ROW_NUMBER() OVER (ORDER BY id) AS RowNumber,* FROM table1        ) AWHERE RowNumber > 页大小*(页数-1)

说明:

页大小：每页的行数；

页数：第几页。使用时，请把“页大小”和“页大小*(页数-1)”替换成数字。

通过SQL 查询分析器，显示比较：结论是:

分页方案二：(利用ID大于多少和SELECT TOP分页）效率最高，需要拼接SQL语句
分页方案一：(利用Not In和SELECT TOP分页)   效率次之，需要拼接SQL语句
分页方案三：(利用SQL的游标存储过程分页)    效率最差，但是最为通用