第 3 章 关系数据库标准语言 SQL

1. 第3章 关系数据库标准语言SQL

2. 3.1 SQL语言的基本概念与特点 3.1.1 语言的发展及标准化 • 在70年代初，E.F.Codd首先提出了关系模型。70年代中期，IBM公司在研制 SYSTEM R关系数据库管理系统中研制了SQL语言，最早的SQL语言（叫SEQUEL2）是在1976 年 11 月的IBM Journal of R&D上公布的。 • 1979年ORACLE公司首先提供商用的SQL，IBM公司在DB2 和SQL/DS数据库系统中也实现了SQL。 • 1986年10月，美国ANSI采用SQL作为关系数据库管理系统的标准语言(ANSI X3. 135-1986），后为国际标准化组织（ISO）采纳为国际标准。

3. 1989年，美国ANSI采纳在ANSI X3.135-1989报告中定义的关系数据库管理系统的SQL标准语言，称为ANSI SQL 89。 • 1992年，ISO又推出了SQL92标准，也称为SQL2。 • 目前SQL99(也称为SQL3)在起草中，增加了面向对象的功能。 • 结构化查询语言SQL(Structured Query Language)是一种介于关系代数与关系演算之间的语言，其功能包括查询、操纵、定义和控制四个方面，是一个通用的、功能极强的关系数据库语言。目前已成为关系数据库的标准语言，广泛应用于各种数据库。

4. SQL 视图1 视图2 外模式 基本表3 模式 基本表4 基本表2 基本表1 存储文件1 存储文件2 内模式 一、关系数据库三级模式结构 3.1.2 SQL语言的基本概念

5. 外模式对应于视图（View）和部分基本表（Base Table）; • 模式对应于基本表; • 内模式对应于存储文件。 • 基本表是本身独立存在的表，在SQL中一个关系就对应一个表。一些基本表对应一个存储文件，一个表可以有若干索引，索引也存放在存储文件中。 • 视图是从基本表或其他视图中导出的表，它本身不独立存储在数据库中，也就是说数据库中只存放视图的定义而不存放视图对应的数据，这些数据仍存放在导出视图的基本表中，因此视图是一个虚表。 • 存储文件的物理结构及存储方式等组成了关系数据库的内模式。存储文件的物理结构及存储方式等不同数据库管理系统往往是不同的，一般也是不公开的。 • 视图和基本表是SQL语言的主要操作对象，用户可以用SQL语言对视图和基本表进行各种操作。在用户眼中，视图和基本表都是关系表，而存储文件对用户是透明的。

6. 3.1.3 SQL语言的主要特点 SQL集数据查询、数据操纵、数据定义 和数据控制功能于一体，主要特点包括： 1、综合统一 SQL集数据定义语言DDL、数据操纵语言DML、数据控制语言DCL的功能于一体，语言风格统一，可以独立完成数据库生命周期中的全部活动，包括： • 定义关系模式，插入数据，建立数据库; • 对数据库中的数据进行查询和更新; • 数据库重构和维护; • 数据库安全性、完整性控制。

7. 2、高度非过程化 非关系数据模型的数据操纵语言是“面向过程”的，用“过程化”语言完成某项请求，必须指定存储路径。 SQL进行数据操作，只要提出“做什么”，而无须指明“怎么做”，因此无需了解存储路径。存储路径的选择以及SQL的操作过程由系统自动完成。这样可以减轻用户的负担，也提高了数据独立性。 3、面向集合的操作方式 非关系数据模型得采用提面向记录的操作方式，操作对象是一条记录。 SQL采用集合操作方式，不仅操作对象、查找结果可以是元组的集合，而且一次插入、删除、更新操作的对象也可以是元组的集合。

8. 4、以同一种语法结构提供多种使用方式 SQL既是独立的语言，又是嵌入式语言。 作为独立的语言，它能够独立地用于联机交互的使用方式，用户可以在终端键盘上直接键入SQL命令对数据库进行操作; 作为嵌入式语言，SQL语句能够嵌入到高级语言（如C、C++、Java）程序中，供程序员设计程序时使用。 而在两种不同的使用方式下，SQL的语法结构基本上是一致的。 5、语言简洁，易学易用 SQL功能极强，完成核心功能只用了9个动词，接近英语口语，所以容易学习，易于使用。 数据查询数据定义数据操纵数据控制 SELECTCREATE DROP ALTERINSERT UPDATE DELETEGRANT REVOKE

9. 3．2 数据定义 3.3.1 字段数据类型 所谓数据类型就是以数据的表现方式和存储方式来划分的数据的种类。在SQL Server 中每个变量、参数、表达式等都有数据类型。

10. 整数数据类型 1. INT （INTEGER） 说明： (1)取值范围：-2 31（-2 147483648） ~2 31 -1 (2147 483647)之间的所有正负整数。 (2)存储大小： 4个字节，其中1位表示整数值的正负号，其它31 位表示整数值的长度和大小。 例： declare @a int --定义一个整型局部变量a select @a=-12345 --为该局部变量赋值为-12345 select @a --在屏幕上输出该局部变量的值 go --整个程序段结束

11. 2. SMALLINT 说明： （1）取值范围：-2 15（-32768） ~2 15 -1（32767）之间的所有正负整数。 （2）存储大小：2个字节，其中1位表示整数的正负号，其它15位表示整数值的长度和大小

12. 3. TINYINT 说明： （1）取值范围：0 ~255 之间的所有正整数。 （2）存储大小：1 个字节 例： declare @b tinyint set @b=12345 select @b go 结果：出错

13. 4. BIGINT 说明： （1）取值范围：-263（-9223372036854775807） ~263-1（ 9223372036854775807）之间的所有正负整数。 （2）存储大小：每个BIGINT 类型的数据占用8个字节的存储空间。

14. 浮点数据类型 (一)近似数值类型：指在其范围内不是所有的数都能精确表示 • REAL 数据类型: REAL数据类型以指数形式表示时可精确到第7 位小数， (1)取值范围：从-3.40E -38 到3.40E +38。 (2)存储大小：占用4 个字节的存储空间。 (3)若以小数点表示，则可精确到小数点后第（8-整数位）位； (4)当整数位达到8位时，系统自动以指数形式表示

15. 2. FLOAT： 说明： (1)取值范围：可精确到第15 位小数，其范围为从-1.79E -308 到1.79E +308。 (2)存储大小：占用8 个字节的存储空间。 (3)当整数位数达到17位时，系统自动以指数形式表示。

16. （二）精确小数： 1. DECIMAL 说明： (1)取值范围：存储从-1038-1到1038-1的固定精度和范围的数值型数据； (2)存储大小：2~17个字节不等； (3)可用格式： • DECIMAL[（p，[s]）]，P指范围是小数点左右所能存储的数字的总位数，不包括小数点； • S是精度是小数点右边存储的数字的位数，缺省为0。 例如：decimal(15，5)，表示共有15 位数，其中整数10 位，小数5位。

17. DECIMAL数据占用的字节数取决于P中的整数位数 P中的整数位数 字节数 介于 1~9位 占5个字节 10~19位 占9个字节 20~28位 占13个字节 29~38位 占17个字节

18. 2. NUMERIC NUMERIC数据类型与DECIMAL数据类型完全相同。

19. 二进制数据类型 1 .   BINARY：固定长度的二进制数据类型。 （1）格式：BINARY（ n）， n 表示数据的长度，取值为1 到8000 ，必须指定BINARY 类型数据的大小。 （2）存储大小：占用n+4个字节的存储空间。 注：输入数据时必须在数据前加上字符“0X”作为二进制标识,且数据输入时为十六进制数字，若输入的数据过长将会截掉其超出部分，若输入的数据位数为奇数，则会在符号“0X ”后添加一个0。

20. 例： declare @a binary(5) set @a=0xabc select @a --定义局部变量a为二进制数据，长度为5 --为a赋值为0xabc --值为0x0abc000000

21. 2 .VARBINARY： • 可变长度的二进制数据类型 • (1)格式：VARBINARY（n）。 n 的取值也为1 到8000， • (2)存储大小：实际数值长度+4个字节

22. 逻辑数据类型BIT （1）存储大小：占用1 个字节的存储空间； （2）取值范围：0 或1 ，如果输入0 或1以外的值，将被视为1 （3）说明：BIT 类型不能定义为NULL 值（所谓NULL 值是指空值或无意义的值）。 例：declare @a bit --定义局部变量a为BIT型 set @a=2 --为a赋值为2 select @a --输出结果为1

23. 字符数据类型 1. CHAR： 固定长度的非UNICODE字符 • （1）定义形式：CHAR[ （n） ]； • （2）存储大小：字符串中每个字符和符号占一个字节的存储空间，汉字占2个字节的空间n 表示所有字符占的总存储空间，n 的取值为1 ~8000， 即可容纳8000 个ANSI 字符。 • （3） 说明：省略N系统默认值为1， 若输入数据的字符数小于n，则系统自动在其后添加空格来填满设定好的空间。若输入的数据过长，将会截掉其超出部分。

24. 例2:declare @a char（5） set @a='123' select @a 输出结果为： 123空格空格 例1:declare @a char set @a='123' select @a 输出结果为： 1

25. 例3:declare @a char(2) set @a=‘1中国' select @a select datalength(@a) --返回@a所占用的字节长度 输出结果： 1 --输出字符1未输出‘中’字的左半部分 2 --变量长度为2个字节 例4：若将长度改为3、4、7，该题输出结果和长度是什么？

26. 2 . VARCHAR： 可变长度的非Unicode字符 (1)定义形式：VARCHAR [ （n） ]，n 的取值为1 到8000 (2)存储大小：实际数值长度，若输入数据的字符数小于n ，则系统不会在其后添加空格来填充空间。

27. 例:declare @a varchar(2) set @a=‘1中国' select @a select datalength(@a) --返回@a所占用的字节长度 输出结果： 1 --输出字符1未输出‘中’字的左半部分 1 --变量长度为屏幕显示的实际字节，1个字节 考虑：若长度改为3、4、7，则输出结果和长度是什么？

28. . NCHAR： 固定长度的UNICODE字符 (1)定义形式：NCHAR[ （n） ]。 (2)存储大小：字符串中每个字符、符号和汉字均占两个字节的存储空间，n表示总存储空间，取值为1~4000。 (3)说明：(字符中，英文字符只需要一个字节存储就足够了，但汉字众多，需要两个字节存储，英文与汉字同时存在时容易造成混乱)Unicode字符集就是为了解决字符集这种不兼容的问题而产生的，它所有的字符都用两个字节表示，即英文字符也是用两个字节表示。

29. 例:declare @a Nchar(2) set @a=‘1中国' select @a select datalength(@a) --返回@a所占用的字节长度 输出结果： 1中 --输出字符1和‘中’字 4 --变量长度为2个字节 考虑：若将长度改为3、4、7，该题输出结果和长度是什么？

30. . NVARCHAR: 可变长度的UNICODE字符 (1)定义形式：NVARCHAR [ （n） ]。 它与VARCHAR 类型相似。不同的是，NVARCHAR数据类型采用UNICODE 标准字符集（Character Set）， n 的取值为1 到4000。 （2） 存储大小：实际数值长度,超出N的部分截去，不足N的部分不补空格。

31. 例:declare @a Nvarchar(2) set @a=‘1中国' select @a select datalength(@a) --返回@a所占用的字节长度 输出结果： 1中 --输出字符1和‘中’字 4 --变量长度为2个字节 考虑：若将长度改为3、4、7，该题输出结果和长度是什么？

32. 文本和图形数据类型 1、TEXT：用于存储大量文本数据 (1)容量：理论上为1~231–1(2147483647)个字节，实际应用时需要视硬盘的存储空间而定。 (2)说明：SQL SERVER存储此类数据时，将TEXT 和IMAGE 类型的数据直接存放到表的数据行中，而不是存放到不同的数据页中。 这就减少了用于存储TEXT 和IMAGE 类型的空间，并相应减少了磁盘处理这类数据的I/O 数量。

33. 2 、NTEXT NTEXT数据类型与TEXT类型相似，不同的是NTEXT 类型采用UNICODE 标准字符集(Character Set), 因此其理论容量为230-1(1, 073, 741, 823)个字节。

34. 3、 IMAGE IMAGE数据类型用于存储大量的二进制数据Binary Data。 其理论容量为2 31 -1(2,147,483,647)个字节。 通常用来存储图形等OLE （Object Linking and Embedding，对象连接和嵌入）对象，该类型不指定长度，可用来输入任何二进制数据。在输入数据时必须在数据前加上字符“0X”作为二进制标识

35. 日期和时间数据类型 1. DATETIME (1）日期范围：从1753 年1 月1 日至9999 年12 月31 日的日期； (2) 存储大小：每个数值要求8 个字节的存储空间。 (3) 说明：如果省略了日期部分，则系统将1900 年1 月1 日作为日期缺省值。

36. 例：declare @a datetime --定义类型 set @a=‘1989-2-23’ --为变量a赋值 select @a select datalength(@a) go 输出结果： 1989-02-23 00:00:00.000 8 --长度为8个字节

37. 2. SMALLDATETIME (1) 日期范围：从1900年1月1日到2079年6月6日的日期和时间数据，精确到分钟； (2) 存储大小：使用4 个字节存储数据。 (3) 日期输入格式：允许用斜杠（/）、连接符（-）和小数点（.）作为用数字表示的年、月、日之间的分隔符。 如： YMD：2000/6/22 2000-6-22 2000.6.22 MDY：3/5/2000 3-5-2000 3.5.2000 DMY：31/12/1999 31-12-1999 31.12.2000

38. （2）时间输入格式： 顺序为“小时、分钟、秒、毫秒”，中间用冒号“：”隔开，秒和毫秒之间可用小数点“.”隔开，隔开后的第一位数字代表十分之一秒，第二位数字代表百分之一秒，第三位数字代表千分之一秒。Am表示午前12小时 、PM表示午后12小时，默认情况为AM。AM 与PM 均不区分大小写。如： 3:5:7.2pm---下午3 时5 分7 秒200 毫秒 10:23:5.123Am---上午10 时23 分5 秒123 毫秒

39. 例：declare @a smalldatetime --定义数据类型 set @a=‘11:30 pM’ --输出下午时间 select @a --输出a go 输出结果： 1900-01-01 23:30:00

40. 货币数据类型 货币数据类型用于存储货币值。在使用货币数据类型时，应在数据前加上货币符号，系统才能辨识其为哪国的货币，如果不加货币符号，则默认为“￥”

41. 1 MONEY (1)精确值：货币单位的万分之一，即4 位小数 (2) 取值范围：-2 63（-922337203685477.5808 ~2 63 -1（+922337203685477.5807）。 (3)存储大上：使用8个字节存储。

42. 2. SMALLMONEY 类似于MONEY 类型，存储的货币值范围较小. (1)取值:从-214,748.3648到+214,748.3647， (2)存储空间:4 个字节

43. 特定数据类型 1 TIMESTAMP提供数据库范围内的惟一值，此类型相当于BINARY8或VARBINARY（8），但当它所定义的列在更新或插入数据行时，此列的值会被自动更新，一个计数值将自动地添加到此TIMESTAMP数据列中。每个数据库表中只能有一个TIMESTAMP数据列。如果建立一个名为“TIMESTAMP”的列，则该列的类型将被自动设为TIMESTAMP数据类型

44. UNIQUEIDENTIFIER 存储一个16 位的二进制数字。此数字称为（GUIDGlobally Unique Identifier ，即全球惟一鉴别号）。 此数字由SQLServer 的NEWID函数产生的全球惟一的编码，在全球各地的计算机经由此函数产生的数字不会相同

45. 7.11 局部变量与全局变量 局部变量 局部变量是用户定义的变量，作用范围仅在程序内部。在程序中通常用来储存从表中查询到的数据，或当作程序执行过程中暂存变量使用。，而且必须先用DECLARE命令说明后才可使用。 1. 格式：DECLARE @变量名变量类型[,@变量名变量类型…]

46. 2.说明： （1）局部变量必须以“@”开头，遵循命名规范，且不能使用保留字，不能和全局变量同名； (2) 变量类型可以是系统数据类型，也可以是用户自定义的数据类型； (3)未赋值前，变量值为null。

47. 3. 给变量赋值格式： 必须使用SELECT或SET命令来设定变量的值，其语法如下： 格式1：SELECT @局部变量1=变量值1 [,@局部变量2=变量值2] /*可为多个变量赋值，中间用逗号隔开*/ 格式2：SET @局部变量=变量值 /*一次只能为一个变量赋值 */

48. 4.赋值的注意事项： (1)若赋值长度超出声明长度则自动截去超出长度； (2)可以用select语句将从表中查询出的结果给局部变量赋值； (3)若变量值和局部变量类型不匹配，则系统隐式的将被赋值转化为局部变量的类型

49. 5.变量的显示： select @局部变量名 例：声明一个长度为10 个字符的变量“id”,赋值并将值显示在屏幕上。 declare @id char（10） select @id=‘10010001’ select @id go 结果为10010001空格空格

50. 全局变量 全局变量是SQL Server系统内部使用的变量，其作用范围并不局限于某一程序，而是任何程序均可随时调用全局变量。 用户只能调用全局变量，而不能定义和赋值给全局变量。全局变量通常存储一些SQL Server的配置设定值和统计数据。用户可在程序中用全局变量来测试系统的设定值或Transact-SQL命令执行后的状态值。 如，@@rowcount 功能是存储前一条命令影响到的记录总数。