浅析SQL Server的分页方式 ISNULL与COALESCE性能比较

前言

SQL SELECT 语句

上一节我们讲解了数据类型以及字符串中几个需要注意的地方,这节我们继续讲讲字符串行数同时也讲其他内容和穿插的内容,简短的内容,深入的讲解。(可参看文章《详解SQL
Server中的数据类型》)

一、查询SQL SELECT 语法

分页方式

(1)SELECT 列名称 FROM 表名称

在SQL 2005或者SQL
2008中我们是利用ROW_NUMBER开窗函数来进行分页的,关于开窗函数,我们在SQL进阶中会详细讲讲。如下:

(2)SELECT *  FROM 表名称

USE TSQL2012GODECLARE @StartRow INTDECLARE @EndRow INTSET @StartRow = 31SET @EndRow = 40SELECT [address], [city], [region]FROM (SELECT [SC].[address], [SC].[city], [SC].[region],ROW_NUMBER() OVER(ORDER BY [SC].[address], [SC].[city],[SC].[custid]) AS RowNumberFROM Sales.Customers SC) SaleCustomerWHERE RowNumber  @StartRow AND RowNumber  @EndRowORDER BY [address], [city], [region];

注释:SQL 语句对大小写不敏感。SELECT 等效于 select。

上面代码想必就不需要我解释了,同时利用视图也可以进行分页

=======================================================

WITH SaleCustomer AS(SELECT [SC].[address], [SC].[city], [SC].[region],ROW_NUMBER() OVER(ORDER BY [SC].[address], [SC].[city],[SC].[custid]) AS RowNumberFROM Sales.Customers SC)SELECT [address], [city], [region]FROM SaleCustomerWHERE RowNumber  @StartRow AND RowNumber  @EndRowORDER BY [address], [city], [region]GO

二、查询去重 SQL SELECT DISTINCT

下面我们来看看这二者利用SQL查询语句和视图有没有性能上的差异呢?来,检验就是。

在表中,可能会包含重复值。这并不成问题,不过,有时您也许希望仅仅列出不同(distinct)的值。关键词
DISTINCT 用于返回唯一不同的值

从这里我们可以看出,二者在性能开销上并没有什么不同,大部分情况下应该是一样的。但是在SQL
2011之后版本则出现新的语法来实现分页,估计我们依然还是利用的ROW_NUMBER,可能是为了能够兼容SQL版本到2005,利用OFFSET-FETCH来进行筛选过滤,它是基于SQL
2011之后才有。上述我们是取从31到40之间的数据,如果用OFFSET-FETCH实现,我们看这个函数字面意思就能知道跳过多少数据然后抓取多少数据,所以我们需要跳过前面30条数据,需要取接下来10条数据。

(1)语法:SELECT DISTINCT 列名称 FROM 表名称

USE TSQL2012GODECLARE @PageSize INT = 10, @PageIndex INT = 3SELECT *FROM Sales.CustomersORDER BY custidOFFSET @PageIndex * @PageSize ROWSFETCH NEXT 10 ROWS ONLYGO

===========================================================

如果对SQL版本要求不低的话,利用OFFSET-FETCH来实现简直爽爆了。

三、SQL WHERE:WHERE 子句**

COALESCE compare to ISNULL

如需有条件地从表中选取数据,可将 WHERE 子句添加到 SELECT 语句

上一节我们讲了讲一些字符串函数,其中漏掉了一个字符串函数即COALESCE,这个函数在SQL
2008+上才有,其中还有其他几个类似对字符串函数的处理,我们一起来看下。msdn对其定义为:按顺序计算变量并返回最初不等于
NULL 的第一个表达式的当前值。返回数据类型优先级最高的 expression
的数据类型。 如果所有表达式都不可为 Null,则结果的类型也不可为
Null。如果所有参数均为 NULL,则 COALESCE 返回 NULL。至少应有一个
Null值为NULL 类型。说白了就是对NULL的处理。我们简单来看下一个例子。

(1)语法:SELECT 列名称 FROM 表名称 WHERE  列 运算符 值

USE TSQL2012GOSELECT custid, country, region, city, country + COALESCE(N''+ region, N'') + N',' + city AS locationFROM Sales.Customers

(2)下面的运算符可在 WHERE 子句中使用:

上述我们可以看到,我们通过COALESCE函数来对NULL用空字符串来代替进行处理。SQL
2012也引入了CONCAT函数来接收一个要连接的输入列表并自动以空字符串替换NULL,上述同样可以用CONCAT函数来代替。

注释:在某些版本的 SQL 中,操作符 <> 可以写为 !=

USE TSQL2012GOSELECT custid, country, region, city,country + CONCAT(country,N''+ region, N',' + city) AS locationFROM Sales.Customers

=========================================================

同时我们看到下图知道,CONCAT函数参数至少要有两个:

四、SQL AND & OR

msdn对CONCAT函数解释为:CONCAT
采用可变数量的字符串参数,并将它们串联成单个字符串。
它需要至少两个输入值;否则将引发错误。
所有参数都隐式转换为字符串类型,然后串联在一起。 Null
值被隐式转换为空字符串。 如果所有参数都为 Null,则返回 varchar(1)
类型的空字符串。 隐式转换为字符串的过程遵循现有的数据类型转换规则。

AND 和 OR 运算符用于基于一个以上的条件对记录进行过滤

我们继续回到COALESCE函数,主要看看它与ISNULL函数的区别。

AND 和 OR 运算符

COALESCE与ISNULL函数探讨

AND 和 OR 可在 WHERE 子语句中把两个或多个条件结合起来。

可能有些人认为ISNULL比COALESCE函数更快,或者有人认为ISNULL和COALESCE函数是等同,更有人认为应该倾向于使用COALESCE函数,因为它是
ANSI SQL标准函数。认为归认为,那么两者到底有何不同呢,我们一起来看下。

如果第一个条件和第二个条件都成立,则 AND 运算符显示一条记录。

COALESCE和ISNULL处理数据类型优先不同

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

COALESCE函数决定类型输出基于数据类型优先,所以如下在处理INT时,DATETIME优先级高于INT。

========================================================

DECLARE @int INT, @datetime DATETIME;SELECT COALESCE(@datetime, 0);SELECT COALESCE(@int, CURRENT_TIMESTAMP);

五、SQL ORDER BY

而对于ISNULL函数,数据类型不受数据类型优先影响,而是通过函数参数列表第一项影响,ISNULL在于交换而COALESCE在于所有参数查询的合并。

     ORDER BY 语句用于对结果集进行排序

DECLARE @int INT, @datetime DATETIME;SELECT ISNULL(@datetime, 0);

ORDER BY 语句

我们看看进行如下操作会如何

(1)ORDER BY 语句用于根据指定的列对结果集进行排序。

DECLARE @int INT, @datetime DATETIME;SELECT ISNULL(@int, CURRENT_TIMESTAMP);

ORDER BY 语句默认按照升序对记录进行排序。

此时会出现无法将DATETIME转换为INT

(2)如果您希望按照降序对记录进行排序,可以使用 DESC 关键字。

此时我们需要显式进行如下转换才行

=============================================================

DECLARE @int INT, @datetime DATETIME;SELECT ISNULL(@int, CONVERT(INT,CURRENT_TIMESTAMP));SELECT ISNULL(@int, CAST(CURRENT_TIMESTAMP AS INT));

六、INSERT INTO

ISNULL会造成数据丢失

INSERT INTO 表名称 VALUES (值1, 值2,….)

我们再来看二者的对比的例子

我们也可以指定所要插入数据的列:

DECLARE @c5 VARCHAR(5);SELECT 'COALESCE', COALESCE(@c5, 'Jeffcky Wang')UNION ALLSELECT 'ISNULL', ISNULL(@c5, 'Jeffcky Wang');

(1)INSERT INTO table_name (列1, 列2,…) VALUES (值1, 值2,….)

上述我们定义字符串变量长度为5,而利用ISNULL字符串却被截取了,在这里我们可以认为ISNULL会导致数据的丢失而非出错。为什么会出现这样的结果呢?上述我们已经讲过ISNULL受第一个参数影响,其长度定义为5,所以只能为5,这是会进行截取,而COALESCE函数着眼于检测所有元素,此时为12所以会完全进行返回。我们通过运行如下就可以看出。

====================================================

DECLARE @c5 VARCHAR(5);SELECT c = COALESCE(@c5, 'Jeffcky Wang'), i = ISNULL(@c5, 'Jeffcky Wang')INTO dbo.TestISNULL_COALESCESELECT name, t = TYPE_NAME(system_type_id), max_length, is_nullable FROM sys.columns WHERE [object_id] = OBJECT_ID('dbo.TestISNULL_COALESCE');

七、SQL UPDATE

我们看到上述COALESCE合并的结果是可空的而ISNULL不是,有一点点不同。

Update 语句

COALESCE对列计算时需要持久化

Update 语句用于修改表中的数据。

接下来我们看看二者最大的不同,我们通过计算列并且在其上面创建主键或者非空约束,看看ISNULL和COALESCE的区别

(1)语法:UPDATE 表名称 SET列名称 = 新值  WHERE 列名称 = 某值

CREATE TABLE dbo.CreateISNULL( a INT, b AS ISNULL(a, 15) PRIMARY KEY);

============================================================

我们再来看看COALESCE函数来计算列

八、SQL DELETE

CREATE TABLE dbo.CreateCOALESCE( a INT, b AS COALESCE(a, 15) PRIMARY KEY);

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

很明显我们需要对列进行持久化,通过添加PERSISTED关键字,如下即可。

语法

CREATE TABLE dbo.CreateCOALESCE( a INT, b AS COALESCE(a, 15) PERSISTED PRIMARY KEY);

(1)DELETEF  ROM 表名称 WHERE列名称 = 值

我们再来看看一个二者的不同

============================================================

DECLARE @c CHAR(10);SELECT 'x' + COALESCE(@c, '') + 'y';SELECT 'x' + ISNULL(@c, '') + 'y';

九、SQL TOP

我们到这里其实我们可以稍微概括下二者的区别:ISNULL着重于替换,而COALESCE着重于合并。COALESCE显示忽略了NULL并用空字符串填充并压缩,而ISNULL对NULL会用空字符串填充但不会压缩。

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

COALESCE函数支持超过两个参数

对于拥有数千条记录的大型表来说,TOP 子句是非常有用的。

对于多个参数输入,ISNULL函数需要嵌套调用,而COALESCE能够处理任何数量,至于上限不知,所以对于多个参数使用COALESCE更加,如下使用多个参数输入。

注释:并非所有的数据库系统都支持
TOP 子句。

SELECT COALESCE(a, b, c, d, e, f, g) FROM dbo.table;

(1)SQL Server 的语法:

而对于ISNULL,我们需要这样做

SELECT  TOP number|percent  column_name(s)  FROM  table_name

SELECT ISNULL(a, ISNULL(b, ISNULL(c, ISNULL(d, ISNULL(e, ISNULL(f, g)))))) FROM dbo.table;

=================================================================

二者最终执行时和利用CASE一样

十、LIKE 操作符

CASE WHEN [tempdb].[dbo].[table].[a] IS NOT NULL THEN [tempdb].[dbo].[table].[a] ELSE CASE WHEN [tempdb].[dbo].[table].[b] IS NOT NULL THEN [tempdb].[dbo].[table].[b] ELSE CASE WHEN [tempdb].[dbo].[table].[c] IS NOT NULL THEN [tempdb].[dbo].[table].[c] ELSE CASE WHEN [tempdb].[dbo].[table].[d] IS NOT NULL THEN [tempdb].[dbo].[table].[d] ELSE CASE WHEN [tempdb].[dbo].[table].[e] IS NOT NULL THEN [tempdb].[dbo].[table].[e] ELSE CASE WHEN [tempdb].[dbo].[table].[f] IS NOT NULL THEN [tempdb].[dbo].[table].[f] ELSE [tempdb].[dbo].[table].[g] END END END END END END

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

COALESCE和ISNULL二者性能比较

SELECT column_name(s)

我们来运行如下查询

FROM table_name

DBCC DROPCLEANBUFFERS;DECLARE @a VARCHAR(5), -- = 'str_a', -- this line changed per test @b VARCHAR(5), -- = 'str_b', -- this line changed per test @v VARCHAR(5), @x INT = 0, @time DATETIME2(7) = SYSDATETIME();WHILE @x = 500000BEGIN SET @v = COALESCE(@a, @b); --COALESCE SET @x += 1;ENDSELECT DATEDIFF(MILLISECOND, @time, SYSDATETIME());GODBCC DROPCLEANBUFFERS;DECLARE @a VARCHAR(5), -- = 'str_a', -- this line changed per test @b VARCHAR(5), -- = 'str_b', -- this line changed per test @v VARCHAR(5), @x INT = 0, @time DATETIME2(7) = SYSDATETIME();WHILE @x = 500000BEGIN SET @v = ISNULL(@a, @b); --ISNULL SET @x += 1;ENDSELECT DATEDIFF(MILLISECOND, @time, SYSDATETIME());

WHERE column_name LIKE pattern

我们有查询四个场景:两个参数都为NULL第一个参数为NULL第二个参数为NULL两个参数都为NULL。每个场景测试十次,最终得出如下结果

================================================

从上看出二者性能并未有什么太大差异,我们不需要太担心了吧,当然上述场景并未完全覆盖,至少还是能说明一部分。上述我们得到的结果查看的执行时间,现在我们再来看看二者查询执行计划。

十一、SQL 通配符

SELECT COALESCE((SELECT MAX(index_id) FROM sys.indexes WHERE [object_id] = t.[object_id]), 0) FROM sys.tables AS t;SELECT ISNULL((SELECT MAX(index_id) FROM sys.indexes WHERE [object_id] = t.[object_id]), 0) FROM sys.tables AS t;

在搜索数据库中的数据时,SQL 通配符可以替代一个或多个字符。

上述可能不太准确,还和硬件配置有关,也有可能COALESCE的性能差与ISNULL。二者性能应该是没什么很大差异。

SQL 通配符必须与 LIKE 运算符一起使用。

ISNULL和自然语言描述不一致

在 SQL 中,可使用以下通配符:

为何是和自然语言描述不一致呢?也就是说我们当判断某个值为NULL会做什么,不为NULL再做什么,这时用查询语言SQL描述如下:

==============================================================

IF ISNULL(something) -- do something

十二、SQL IN

我们用自然语言角度来看,翻译为如果something为NULL我们做什么,这个时候是不一致的。因为在SQL
Server中没有布尔值类型,上述我们只能进行如下转换

IN 操作符

IF something IS NULL -- do something-- orIF ISNULL(something, NULL) IS NULL -- do something-- orIF ISNULL(something, '') = '' -- do something

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

利用GUID看看奇葩的ISNULL

SELECT column_name(s)

在本节介绍之前我们再来看看一个例子,从而颠覆你的想法,让你发狂。

FROM table_name

SELECT ISNULL(NEWID(), 'JeffckyWang') AS Col1

WHERE column_name IN (value1,value2,…)

这样看是没问题,我们将其插入到表中,再看对其列的描述

========================================

SELECT ISNULL(NEWID(), 'JeffckyWang') AS Col1INTO dbo.IsNullExample2;EXEC sp_help 'dbo.IsNullExample2';

十三、SQL  BETWEEN

表中数据确实存在,但是对列的描述是可空的。

BETWEEN 操作符在 WHERE
子句中使用,作用是选取介于两个值之间的数据范围。

总结

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

上述重点讲述了COALESCE和ISNULL函数区别之处,通过本节的讲述二者的场景和区别,我们是不是应该有了一点想法,到底是该用COALESCE还是ISNULL呢?大部分情况下还是利用COALESCE为好,一是此函数是作为SQL标准函数,第二个相对于ISNULL它可以支持更多参数,而ISNULL则需要嵌套,而对于ISNULL难道就没有应用场景了吗,当然有在查询数据时判断数据是否为NULL,这种情况下利用ISNULL,例如,如下

(1)语法:

SELECT ISNULL(argument, '') INTO dbo.IsNullExample;

SELECT column_name(s)   FROM table_name

本文关于ISNULL和COALESCE的比较参考文章:Deciding between COALESCE and
ISNULL in SQL
Server。本节我们到此结束,简短的内容,深入的理解,我们下节再会,good
night!

WHERE column_name    BETWEEN value1 AND value2

以上就是本文的全部内容,希望本文的内容对大家的学习或者工作能带来一定的帮助,如果有疑问大家可以留言交流,同时也希望多多支持脚本之家!

=============================================

十四、SQL Alias(别名)

(1)表的 SQL Alias 语法:

SELECT column_name(s)   FROM table_name    AS alias_name

(2)列的 SQL Alias 语法

SELECT column_name AS alias_name   FROM table_name

==================================================

十五、SQL JOIN

SQL join 用于根据两个或多个表中的列之间的关系,从这些表中查询数据。

不同的 SQL JOIN

除了我们在上面的例子中使用的 INNER
JOIN(内连接),我们还可以使用其他几种连接。

下面列出了您可以使用的 JOIN 类型,以及它们之间的差异。

JOIN: 如果表中有至少一个匹配,则返回行

LEFT JOIN: 即使右表中没有匹配,也从左表返回所有的行

RIGHT JOIN: 即使左表中没有匹配,也从右表返回所有的行

FULL JOIN: 只要其中一个表中存在匹配,就返回行

=====================================================

十六、SQL INNER JOIN

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

(1)语法:SELECT column_name(s)

FROM table_name1

INNER JOIN table_name2

ON table_name1.column_name=table_name2.column_name

注释:INNER JOIN 与 JOIN 是相同的。

============================================

十七、SQL LEFT JOIN

LEFT JOIN 关键字会从左表 (table_name1) 那里返回所有的行,即使在右表
(table_name2) 中没有匹配的行。

(1)LEFT JOIN 关键字语法

SELECT column_name(s)

FROM table_name1

LEFT JOIN table_name2

ON table_name1.column_name=table_name2.column_name

注释:在某些数据库中, LEFT JOIN 称为 LEFT OUTER JOIN。

==========================================================

十八、SQL RIGHT JOIN

RIGHT JOIN 关键字会右表 (table_name2) 那里返回所有的行,即使在左表
(table_name1) 中没有匹配的行。

(1)RIGHT JOIN 关键字语法

SELECT column_name(s)

FROM table_name1

RIGHT JOIN table_name2

ON table_name1.column_name=table_name2.column_name

注释:在某些数据库中, RIGHT JOIN 称为 RIGHT OUTER JOIN。

====================================================

十九、SQL FULL JOIN

只要其中某个表存在匹配,FULL JOIN 关键字就会返回行。

(1)FULL JOIN 关键字语法

SELECT column_name(s)

FROM table_name1

FULL JOIN table_name2

ON table_name1.column_name=table_name2.column_name

注释:在某些数据库中, FULL JOIN 称为 FULL OUTER JOIN。

===================================================

二十、SQL UNION 和 UNION ALL

SQL UNION 操作符

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

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

(1)SQL UNION 语法

SELECT column_name(s) FROM table_name1

UNION

SELECT column_name(s) FROM table_name2

注释:默认地,UNION 操作符选取不同的值。如果允许重复的值,请使用
UNION ALL

(2)SQL UNION ALL 语法:

SELECT column_name(s) FROM table_name1

UNION ALL

SELECT column_name(s) FROM table_name2

另外,UNION 结果集中的列名总是等于 UNION 中第一个 SELECT
语句中的列名。

二十一、SQL SELECT INTO

SQL SELECT INTO 语句可用于创建表的备份复件。

SELECT INTO 语句

SELECT INTO 语句从一个表中选取数据,然后把数据插入另一个表中。

SELECT INTO 语句常用于创建表的备份复件或者用于对记录进行存档。

二十二、SQL CREATE DATABASE

CREATE DATABASE 用于创建数据库。

SQL CREATE DATABASE 语法

CREATE DATABASE database_name

二十三、CREATE TABLE 语句

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

SQL CREATE TABLE 语法

CREATE TABLE 表名称

(

列名称1 数据类型,

列名称2 数据类型,

列名称3 数据类型,

….

)

数据类型(data_type)规定了列可容纳何种数据类型。下面的表格包含了SQL中最常用的数据类型:

====================================================================

二十四、SQL 约束

约束用于限制加入表的数据的类型。 可以在创建表时规定约束(通过 CREATE
TABLE 语句),或者在表创建之后也可以(通过 ALTER TABLE 语句)。

我们将主要探讨以下几种约束:

NOT NULL

UNIQUE

PRIMARY KEY

FOREIGN KEY

CHECK

DEFAULT

(1)SQL NOT NULL 约束

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

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

下面的 SQL 语句强制 “Id_P” 列和 “LastName” 列不接受 NULL 值:

CREATE TABLE Persons

(

Id_P int NOT NULL,

LastName varchar(255) NOT NULL,

FirstName varchar(255),

Address varchar(255),

City varchar(255)

)

(2)SQL UNIQUE 约束

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

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

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

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

(3)SQL PRIMARY KEY 约束

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

主键必须包含唯一的值。

主键列不能包含 NULL 值。

每个表都应该有一个主键,并且每个表只能有一个主键。

(4)SQL FOREIGN KEY 约束 

一个表中的 FOREIGN KEY 指向另一个表中的 PRIMARY KEY。

让我们通过一个例子来解释外键。请看下面两个表:

“Persons” 表:

请注意,”Orders” 中的 “Id_P” 列指向 “Persons” 表中的 “Id_P” 列。

“Persons” 表中的 “Id_P” 列是 “Persons” 表中的 PRIMARY KEY。

“Orders” 表中的 “Id_P” 列是 “Orders” 表中的 FOREIGN KEY。

FOREIGN KEY 约束用于预防破坏表之间连接的动作。

FOREIGN KEY
约束也能防止非法数据插入外键列,因为它必须是它指向的那个表中的值之一。

(5)SQL CHECK 约束

CHECK 约束用于限制列中的值的范围。

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

如果对一个表定义 CHECK 约束,那么此约束会在特定的列中对值进行限制。

(6)SQL DEFAULT 约束

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

如果没有规定其他的值,那么会将默认值添加到所有的新记录。

SQL DEFAULT Constraint on CREATE TABLE

下面的 SQL 在 “Persons” 表创建时为 “City” 列创建 DEFAULT 约束:

My SQL / SQL Server / Oracle / MS Access:

CREATE TABLE Persons

(

Id_P int NOT NULL,

LastName varchar(255) NOT NULL,

FirstName varchar(255),

Address varchar(255),

City varchar(255) DEFAULT ‘Sandnes’

)

通过使用类似 GETDATE() 这样的函数,DEFAULT 约束也可以用于插入系统值:

CREATE TABLE Orders

(

Id_O int NOT NULL,

OrderNo int NOT NULL,

Id_P int,

OrderDate date DEFAULT GETDATE()

)

SQL CREATE INDEX

CREATE INDEX 语句用于在表中创建索引。

在不读取整个表的情况下,索引使数据库应用程序可以更快地查找数据。

索引

您可以在表中创建索引,以便更加快速高效地查询数据。

用户无法看到索引,它们只能被用来加速搜索/查询。

注释:更新一个包含索引的表需要比更新一个没有索引的表更多的时间,这是由于索引本身也需要更新。因此,理想的做法是仅仅在常常被搜索的列(以及表)上面创建索引。

SQL CREATE INDEX 语法

在表上创建一个简单的索引。允许使用重复的值:

CREATE INDEX index_name

ON table_name (column_name)

注释:”column_name” 规定需要索引的列。

SQL CREATE UNIQUE INDEX 语法

在表上创建一个唯一的索引。唯一的索引意味着两个行不能拥有相同的索引值。

CREATE UNIQUE INDEX index_name

ON table_name (column_name)

SQL 撤销索引、表以及数据库

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

SQL DROP INDEX 语句

我们可以使用 DROP INDEX 命令删除表格中的索引。

用于 Microsoft SQLJet (以及 Microsoft Access) 的语法:

DROP INDEX index_name ON table_name

用于 MS SQL Server 的语法:

DROP INDEX table_name.index_name

用于 IBM DB2 和 Oracle 语法:

DROP INDEX index_name

用于 MySQL 的语法:

ALTER TABLE table_name DROP INDEX index_name

SQL ALTER TABLE

ALTER TABLE 语句

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

SQL ALTER TABLE 语法

如需在表中添加列,请使用下列语法:

ALTER TABLE table_name

ADD column_name datatype

要删除表中的列,请使用下列语法:

ALTER TABLE table_name

DROP COLUMN column_name

注释:某些数据库系统不允许这种在数据库表中删除列的方式 (DROP COLUMN
column_name)。

要改变表中列的数据类型,请使用下列语法:

ALTER TABLE table_name

ALTER COLUMN column_name datatype

SQL AUTO INCREMENT

Auto-increment 会在新记录插入表中时生成一个唯一的数字。

AUTO INCREMENT 字段

我们通常希望在每次插入新记录时,自动地创建主键字段的值。

我们可以在表中创建一个 auto-increment 字段。

SQL VIEW

视图是可视化的表。

本章讲解如何创建、更新和删除视图。

SQL CREATE VIEW 语句

什么是视图?

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

视图包含行和列,就像一个真实的表。视图中的字段就是来自一个或多个数据库中的真实的表中的字段。我们可以向视图添加
SQL 函数、WHERE 以及 JOIN
语句,我们也可以提交数据,就像这些来自于某个单一的表。

注释:数据库的设计和结构不会受到视图中的函数、where 或 join 语句的影响。

SQL Date

SQL 日期

当我们处理日期时,最难的任务恐怕是确保所插入的日期的格式,与数据库中日期列的格式相匹配。

只要数据包含的只是日期部分,运行查询就不会出问题。但是,如果涉及时间,情况就有点复杂了。

在讨论日期查询的复杂性之前,我们先来看看最重要的内建日期处理函数。

MySQL Date 函数

下面的表格列出了 MySQL 中最重要的内建日期函数:

函数 描述

NOW() 返回当前的日期和时间

CURDATE() 返回当前的日期

CURTIME() 返回当前的时间

DATE() 提取日期或日期/时间表达式的日期部分

EXTRACT() 返回日期/时间按的单独部分

DATE_ADD() 给日期添加指定的时间间隔

DATE_SUB() 从日期减去指定的时间间隔

DATEDIFF() 返回两个日期之间的天数

DATE_FORMAT() 用不同的格式显示日期/时间

SQL Server Date 函数

下面的表格列出了 SQL Server 中最重要的内建日期函数:

函数 描述

GETDATE() 返回当前日期和时间

DATEPART() 返回日期/时间的单独部分

DATEADD() 在日期中添加或减去指定的时间间隔

DATEDIFF() 返回两个日期之间的时间

CONVERT() 用不同的格式显示日期/时间

SQL Date 数据类型

MySQL 使用下列数据类型在数据库中存储日期或日期/时间值:

DATE – 格式 YYYY-MM-DD

DATETIME – 格式: YYYY-MM-DD HH:MM:SS

TIMESTAMP – 格式: YYYY-MM-DD HH:MM:SS

YEAR – 格式 YYYY 或 YY

SQL Server 使用下列数据类型在数据库中存储日期或日期/时间值:

DATE – 格式 YYYY-MM-DD

DATETIME – 格式: YYYY-MM-DD HH:MM:SS

SMALLDATETIME – 格式: YYYY-MM-DD HH:MM:SS

TIMESTAMP – 格式: 唯一的数字

SQL 日期处理

如果不涉及时间部分,那么我们可以轻松地比较两个日期!

假设我们有下面这个 “Orders” 表:

OrderId ProductName OrderDate

1 computer 2008-12-26

2 printer 2008-12-26

3 electrograph 2008-11-12

4 telephone 2008-10-19

现在,我们希望从上表中选取 OrderDate 为 “2008-12-26” 的记录。

我们使用如下 SELECT 语句:

SELECT * FROM Orders WHERE OrderDate=’2008-12-26′

结果集:

OrderId ProductName OrderDate

1 computer 2008-12-26

3 electrograph 2008-12-26

现在假设 “Orders” 类似这样(请注意 “OrderDate” 列中的时间部分):

OrderId ProductName OrderDate

1 computer 2008-12-26 16:23:55

2 printer 2008-12-26 10:45:26

3 electrograph 2008-11-12 14:12:08

4 telephone 2008-10-19 12:56:10

如果我们使用上面的 SELECT 语句:

SELECT * FROM Orders WHERE OrderDate=’2008-12-26′

那么我们得不到结果。这是由于该查询不含有时间部分的日期。

SQL NULL

NULL 值是遗漏的未知数据。

默认地,表的列可以存放 NULL 值。

本章讲解 IS NULL 和 IS NOT NULL 操作符。

SQL NULL

MySQL 也拥有类似 ISNULL() 的函数。不过它的工作方式与微软的 ISNULL()
函数有点不同。

在 MySQL 中,我们可以使用 IFNULL() 函数,就像这样:

SELECT ProductName,UnitPrice*(UnitsInStock+IFNULL(UnitsOnOrder,0))

FROM Products

或者我们可以使用 COALESCE() 函数,就像这样:

SELECT ProductName,UnitPrice*(UnitsInStock+COALESCE(UnitsOnOrder,0))

FROM Products

MySQL 也拥有类似 ISNULL() 的函数。不过它的工作方式与微软的 ISNULL()
函数有点不同。

在 MySQL 中,我们可以使用 IFNULL() 函数,就像这样:

SELECT ProductName,UnitPrice*(UnitsInStock+IFNULL(UnitsOnOrder,0))

FROM Products

或者我们可以使用 COALESCE() 函数,就像这样:

SELECT ProductName,UnitPrice*(UnitsInStock+COALESCE(UnitsOnOrder,0))

FROM Products

SQL 数据类型

MySQL 也拥有类似 ISNULL() 的函数。不过它的工作方式与微软的 ISNULL()
函数有点不同。

在 MySQL 中,我们可以使用 IFNULL() 函数,就像这样:

SELECT ProductName,UnitPrice*(UnitsInStock+IFNULL(UnitsOnOrder,0))

FROM Products

或者我们可以使用 COALESCE() 函数,就像这样:

SELECT ProductName,UnitPrice*(UnitsInStock+COALESCE(UnitsOnOrder,0))

FROM Products

Microsoft Access、MySQL 以及 SQL Server 所使用的数据类型和范围。

Microsoft Access 数据类型

数据类型 描述 存储

Text 用于文本或文本与数字的组合。最多 255 个字符。

Memo Memo 用于更大数量的文本。最多存储 65,536 个字符。注释:无法对 memo
字段进行排序。不过它们是可搜索的。

Byte 允许 0 到 255 的数字。 1 字节

Integer 允许介于 -32,768 到 32,767 之间的数字。 2 字节

Long 允许介于 -2,147,483,648 与 2,147,483,647 之间的全部数字 4 字节

Single 单精度浮点。处理大多数小数。 4 字节

Double 双精度浮点。处理大多数小数。 8 字节

Currency 用于货币。支持 15 位的元,外加 4
位小数。提示:您可以选择使用哪个国家的货币。 8 字节

AutoNumber AutoNumber 字段自动为每条记录分配数字,通常从 1 开始。 4 字节

Date/Time 用于日期和时间 8 字节

Yes/No 逻辑字段,可以显示为 Yes/No、True/False 或
On/Off。在代码中,使用常量 True 和 False (等价于 1 和 0)注释:Yes/No
字段中不允许 Null 值 1 比特

Ole Object 可以存储图片、音频、视频或其他 BLOBs (Binary Large OBjects)
最多 1GB

Hyperlink 包含指向其他文件的链接,包括网页。

Lookup Wizard 允许你创建一个可从下列列表中进行选择的选项列表。 4 字节

MySQL 数据类型

在 MySQL 中,有三种主要的类型:文本、数字和日期/时间类型。

Text 类型:

数据类型 描述

CHAR(size)
保存固定长度的字符串(可包含字母、数字以及特殊字符)。在括号中指定字符串的长度。最多
255 个字符。

VARCHAR(size)
保存可变长度的字符串(可包含字母、数字以及特殊字符)。在括号中指定字符串的最大长度。最多
255 个字符。注释:如果值的长度大于 255,则被转换为 TEXT 类型。

TINYTEXT 存放最大长度为 255 个字符的字符串。

TEXT 存放最大长度为 65,535 个字符的字符串。

BLOB 用于 BLOBs (Binary Large OBjects)。存放最多 65,535 字节的数据。

MEDIUMTEXT 存放最大长度为 16,777,215 个字符的字符串。

MEDIUMBLOB 用于 BLOBs (Binary Large OBjects)。存放最多 16,777,215
字节的数据。

LONGTEXT 存放最大长度为 4,294,967,295 个字符的字符串。

LONGBLOB 用于 BLOBs (Binary Large OBjects)。存放最多 4,294,967,295
字节的数据。

ENUM(x,y,z,etc.) 允许你输入可能值的列表。可以在 ENUM 列表中列出最大
65535
个值。如果列表中不存在插入的值,则插入空值。注释:这些值是按照你输入的顺序存储的。可以按照此格式输入可能的值:ENUM(‘X’,’Y’,’Z’)

SET 与 ENUM 类似,SET 最多只能包含 64 个列表项,不过 SET
可存储一个以上的值。

Number 类型:

数据类型 描述

TINYINT(size) -128 到 127 常规。0 到 255
无符号*。在括号中规定最大位数。

SMALLINT(size) -32768 到 32767 常规。0 到 65535
无符号*。在括号中规定最大位数。

MEDIUMINT(size) -8388608 到 8388607 普通。0 to 16777215
无符号*。在括号中规定最大位数。

INT(size) -2147483648 到 2147483647 常规。0 到 4294967295
无符号*。在括号中规定最大位数。

BIGINT(size) -9223372036854775808 到 9223372036854775807 常规。0 到
18446744073709551615 无符号*。在括号中规定最大位数。

FLOAT(size,d) 带有浮动小数点的小数字。在括号中规定最大位数。在 d
参数中规定小数点右侧的最大位数。

DOUBLE(size,d) 带有浮动小数点的大数字。在括号中规定最大位数。在 d
参数中规定小数点右侧的最大位数。

DECIMAL(size,d) 作为字符串存储的 DOUBLE 类型,允许固定的小数点。

* 这些整数类型拥有额外的选项
UNSIGNED。通常,整数可以是负数或正数。如果添加 UNSIGNED
属性,那么范围将从 0 开始,而不是某个负数。

Date 类型:

数据类型 描述

DATE() 日期。格式:YYYY-MM-DD注释:支持的范围是从 ‘1000-01-01’ 到
‘9999-12-31’

DATETIME() *日期和时间的组合。格式:YYYY-MM-DD
HH:MM:SS注释:支持的范围是从 ‘1000-01-01 00:00:00’ 到 ‘9999-12-31
23:59:59’

TIMESTAMP() *时间戳。TIMESTAMP 值使用 Unix 纪元(‘1970-01-01 00:00:00’
UTC) 至今的描述来存储。格式:YYYY-MM-DD HH:MM:SS注释:支持的范围是从
‘1970-01-01 00:00:01’ UTC 到 ‘2038-01-09 03:14:07’ UTC

TIME() 时间。格式:HH:MM:SS 注释:支持的范围是从 ‘-838:59:59’ 到
‘838:59:59’

YEAR() 2 位或 4 位格式的年。注释:4 位格式所允许的值:1901 到 2155。2
位格式所允许的值:70 到 69,表示从 1970 到 2069。

* 即便 DATETIME 和 TIMESTAMP 返回相同的格式,它们的工作方式很不同。在
INSERT 或 UPDATE 查询中,TIMESTAMP
自动把自身设置为当前的日期和时间。TIMESTAMP 也接受不同的格式,比如
YYYYMMDDHHMMSS、YYMMDDHHMMSS、YYYYMMDD 或 YYMMDD。

SQL Server 数据类型

Character 字符串:

数据类型 描述 存储

char(n) 固定长度的字符串。最多 8,000 个字符。 n

varchar(n) 可变长度的字符串。最多 8,000 个字符。

varchar(max) 可变长度的字符串。最多 1,073,741,824 个字符。

text 可变长度的字符串。最多 2GB 字符数据。

Unicode 字符串:

数据类型 描述 存储

nchar(n) 固定长度的 Unicode 数据。最多 4,000 个字符。

nvarchar(n) 可变长度的 Unicode 数据。最多 4,000 个字符。

nvarchar(max) 可变长度的 Unicode 数据。最多 536,870,912 个字符。

ntext 可变长度的 Unicode 数据。最多 2GB 字符数据。

Binary 类型:

数据类型 描述 存储

bit 允许 0、1 或 NULL

binary(n) 固定长度的二进制数据。最多 8,000 字节。

varbinary(n) 可变长度的二进制数据。最多 8,000 字节。

varbinary(max) 可变长度的二进制数据。最多 2GB 字节。

image 可变长度的二进制数据。最多 2GB。

Number 类型:

数据类型 描述 存储

tinyint 允许从 0 到 255 的所有数字。 1 字节

smallint 允许从 -32,768 到 32,767 的所有数字。 2 字节

int 允许从 -2,147,483,648 到 2,147,483,647 的所有数字。 4 字节

bigint 允许介于 -9,223,372,036,854,775,808 和 9,223,372,036,854,775,807
之间的所有数字。 8 字节

decimal(p,s) 固定精度和比例的数字。允许从 -10^38 +1 到 10^38 -1
之间的数字。p 参数指示可以存储的最大位数(小数点左侧和右侧)。p 必须是 1
到 38 之间的值。默认是 18。s 参数指示小数点右侧存储的最大位数。s 必须是
0 到 p 之间的值。默认是 0。 5-17 字节

numeric(p,s) 固定精度和比例的数字。允许从 -10^38 +1 到 10^38 -1
之间的数字。p 参数指示可以存储的最大位数(小数点左侧和右侧)。p 必须是 1
到 38 之间的值。默认是 18。s 参数指示小数点右侧存储的最大位数。s 必须是
0 到 p 之间的值。默认是 0。 5-17 字节

smallmoney 介于 -214,748.3648 和 214,748.3647 之间的货币数据。 4 字节

money 介于 -922,337,203,685,477.5808 和 922,337,203,685,477.5807
之间的货币数据。 8 字节

float(n) 从 -1.79E + 308 到 1.79E + 308 的浮动精度数字数据。 参数 n
指示该字段保存 4 字节还是 8 字节。float(24) 保存 4 字节,而 float(53)
保存 8 字节。n 的默认值是 53。 4 或 8 字节

real 从 -3.40E + 38 到 3.40E + 38 的浮动精度数字数据。 4 字节

Date 类型:

数据类型 描述 存储

datetime 从 1753 年 1 月 1 日 到 9999 年 12 月 31 日,精度为 3.33 毫秒。
8 bytes

datetime2 从 1753 年 1 月 1 日 到 9999 年 12 月 31 日,精度为 100 纳秒。
6-8 bytes

smalldatetime 从 1900 年 1 月 1 日 到 2079 年 6 月 6 日,精度为 1 分钟。
4 bytes

date 仅存储日期。从 0001 年 1 月 1 日 到 9999 年 12 月 31 日。 3 bytes

time 仅存储时间。精度为 100 纳秒。 3-5 bytes

datetimeoffset 与 datetime2 相同,外加时区偏移。 8-10 bytes

timestamp 存储唯一的数字,每当创建或修改某行时,该数字会更新。timestamp
基于内部时钟,不对应真实时间。每个表只能有一个 timestamp 变量。

其他数据类型:

数据类型 描述

sql_variant 存储最多 8,000 字节不同数据类型的数据,除了 text、ntext
以及 timestamp。

uniqueidentifier 存储全局标识符 (GUID)。

xml 存储 XML 格式化数据。最多 2GB。

cursor 存储对用于数据库操作的指针的引用。

table 存储结果集,供稍后处理。

MySQL 也拥有类似 ISNULL() 的函数。不过它的工作方式与微软的 ISNULL()
函数有点不同。

在 MySQL 中,我们可以使用 IFNULL() 函数,就像这样:

SELECT ProductName,UnitPrice*(UnitsInStock+IFNULL(UnitsOnOrder,0))

FROM Products

或者我们可以使用 COALESCE() 函数,就像这样:

SELECT ProductName,UnitPrice*(UnitsInStock+COALESCE(UnitsOnOrder,0))

FROM Products

SQL 服务器 – RDBMS

MySQL 也拥有类似 ISNULL() 的函数。不过它的工作方式与微软的 ISNULL()
函数有点不同。

在 MySQL 中,我们可以使用 IFNULL() 函数,就像这样:

SELECT ProductName,UnitPrice*(UnitsInStock+IFNULL(UnitsOnOrder,0))

FROM Products

或者我们可以使用 COALESCE() 函数,就像这样:

SELECT ProductName,UnitPrice*(UnitsInStock+COALESCE(UnitsOnOrder,0))

FROM Products

现代的 SQL 服务器构建在 RDBMS 之上。

DBMS – 数据库管理系统(Database Management System)

数据库管理系统是一种可以访问数据库中数据的计算机程序。

DBMS 使我们有能力在数据库中提取、修改或者存贮信息。

不同的 DBMS 提供不同的函数供查询、提交以及修改数据。

RDBMS – 关系数据库管理系统(Relational Database Management System)

关系数据库管理系统 (RDBMS)
也是一种数据库管理系统,其数据库是根据数据间的关系来组织和访问数据的。

20 世纪 70 年代初,IBM 公司发明了 RDBMS。

RDBMS 是 SQL 的基础,也是所有现代数据库系统诸如 Oracle、SQL Server、IBM
DB2、Sybase、MySQL 以及 Microsoft Access 的基础。

相关文章