数据库TSQL程序设计基础PPT课件

1、4.1 SQL语言和T-SQL语言1.SQL语言简介 SQL( Structure Query Language)语言是关系型数据库标准应用语言。 SQL语言不同于C、C#这样的程序设计语言，只是数据库能识别的指令。 例如，在VB.NET程序中要得到SQL Server数据库表中的记录，可以在VB.NET程序中编写SQL查询语句，然后发送到数据库中。数据库根据查询的SQL语句进行查询，在把查询的结果返回给VB.NET程序。 第1页/共59页2.Transact- SQL语言简介 Transact- SQL(简写为T-SQL)是对标准SQL的扩展。 T-SQL语言主要组成部分 数据定义语言（DD

2、L，Data Definition Language） 数据操纵语言（DML，Data Manipularion Language） 数据控制语言（DCL，Data Control Language） 系统存储过程（System Stored Procedure） 附加的语言元素第2页/共59页数据定义语言DDL是指用来定义和管理数据库以及数据库中的各种对象的语句，这些语句包括CREATE创建 、ALTER修改和DROP删除等语句。数据操纵语言是指用来查询、添加、修改和删除数据库中数据的语句，这些语句包括SELECT检索数据 、INSERT添加记录 、UPDATE修改记录 、DELETE删除记

3、录等。数据控制语言（DCL）是用来设置或者更改数据库用户或角色权限的语句，这些语句包括GRANT授予权限 、REVOKE分配权限等语句。第3页/共59页其它语言元素1 注释2 变量3 运算符4 函数5 流程控制语句 第4页/共59页3.T-SQL特点功能强大:集DDL DML DCL于一体语言简洁，风格统一，易学易懂：SQL语言接近英语，只用几个英文单词的组合就能完成所有功能，因此初学者非常容易上手。既是自含式语言，又是嵌入式语言：作为自含式语言，SQL能够独立地用于联机交互的使用方式。 当作为嵌入式语言使用时，SQL语句几乎可以不加修改地嵌入到如VB、PB这样的前端开发平台上，利用前端工具的

4、计算能力和SQL的数据库操纵能力，可以快速地建立数据库应用程序。高度非过程化：用SQL语言进行数据操作，只要提出“做什么”，而无须知道“怎么做”，SQL语句的实现过程由系统自动完成。第5页/共59页4.2 T-SQL的标识符 标识符:由用户定义,SQL Server可识别的有意义的字符序列,通常用它们来表示SQL对象包括数据库、表、视图、属性名等名称 规则: 标识符的长度,通常在130个字符，不能是保留字 标识符第一个字任必须是字母、下划线、和#开头，从第二个开始还可以是数字、$符号 以、开头的是局部、全局变量 以#、#开头的是局部、全局临时对象 包含空格时，要用或引号括起第6页/共59页示例

5、 合法标识符：ABC、lili、VAR_X、#TBL_Y 非法标识符：1AB、FROM第7页/共59页在SQL语句语法格式中的一些约定符号：尖括号“”中的内容为实际语义。中括号“ ”中的内容为任选项。，. . .意思是“等等”，即前面的项可以重复。大括号“”与竖线“|”表明此处为选择项，在所列出的各项中仅需选择一项。 例如： A | B | C | D 意思是A、B、C、D中取其一。 SQL中的数据项（包括列项、表和视图）分隔符为“，”；其字符串常数的定界符用单引号“”表示。第8页/共59页在编写SQL语句时，遵从某种准则以提高语句的可读性，使其易于编辑，是很有好处的。 以下是一些通常的准则：

6、SQL语句对大小写不敏感 为了提高SQL语句的可读性，子句开头的关键字通常采用大写形式。SQL语句可写成一行或多行，习惯上每个子句占用一行。关键字不能在行与行之间分开，并且很少采用缩写形式。SQL语句的结束符为分号“；”，分号必须放在语句中的最后一个子句后面，但可以不在同一行。第9页/共59页4.3 T-SQL的数据类型 数据类型是指用于存储、检索、及解释数据值类型的一种定义，它决定了数据在计算机中的存储格式，在SQL Server中，通常指列、存储过程参数、局部变量的数据特征。 SQL Server中，对列的数据类型选择最为重要。 用户自定义数据类型第10页/共59页位类型数据 bit 位类

7、型只能存放0、1和NULL(空值)，一般用于逻辑判断。 位类型数据占1位二进制内存，如果一个数据表中有8个以下的位类型字段，系统用一个字节存储所有这些字段，超过8个不足16个用2个字节存放。 位类型数据输入任意的非0值时，都按1处理。第11页/共59页、数值型数据 SQL Server数值型数据共8种，整型4种，实型4种。整型数据类型的列一般存储的是计数值、数量或年龄等。1字节型整数 TinyInt 占1个字节固定长度内存，可存储0-255范围内的任意无符号整数。2短整型整数 smallInt 占2字节固定长度内存，最高位为符号位，可存储-3276832767（-215215-1）的任意整数。

8、第12页/共59页3基本整型整数 Int 或Integer 占4字节固定长度内存，高位为符号位，可存储-2147483648-2147483647（-231+231-1）范围内的任意整数。4长整型整数 Bigint 占8字节固定长度内存，高位为符号位，可存储-263到263-1范围内的任意整据。 注意：整型数据可以在较少字节里存储精确的整型数字，存储效率高，不可能出现小数的数据应尽量选用整数类型。 第13页/共59页5精确小数型数据 Numeric(p,s) p指定总位数（不含小数点），p的取值范围1p38。即最多可达38位有效数字，不使用指数的科学记数法表示，但取值范围必须在-1038到10

9、38-1之间。 s指定其中的小数位数，s的取值范围0sp。第14页/共59页numeric型数据在数据表里可带identity关键字（自动增长字段）。 numeric型数据所占的存储空间随精度的不同而不同，一般说来对应关系为: p的取值范围 数据所占内存字节数 1 - 9 5 10-19 9 20-28 13 29-38 17第15页/共59页6精确小数型数据 Decimal(p,s) 或 Dec(p,s) 该类型数据与Numeric(p,s)类型用法相同，所不同的是Decimal(p,s)不能用于数据表的identity字段。 第16页/共59页7近似值实型浮点数 real 占4字节固定长度

10、内存，最多7位有效数字，范围从+-1.1810-38到+-3.401038 。8可变精度实型浮点数 float(n) 当n的取值为1-24时，数据精度是7位有效数字，范围从-3.40E+38到1.79E+38，占4字节内存。 当n的取值为25-53时，精度是15位有效数字，范围从-1.79E+308到1.79E+308，占8字节内存。 实型浮点数常量可以直接使用科学记数法的指数形式书写。第17页/共59页货币型数据 SQL Server提供了Smallmoney和Money两种货币型数据。1短货币型 Smallmoney 该类型占4个字节固定长度的内存，实际是由2个2字节的整数构成，前2个字节

11、为货币值的整数部分，后2个字节为货币值的小数部分。货币值的范围从-214748.3648到+214748.3647，可以精确到万分之一货币单位。第18页/共59页2基本货币型 Money 该类型占8个字节固定长度的内存，由2个4字节的整数构成，前4个字节为货币值的整数部分，后4个字节表示货币值的小数部分。货币值的范围从-263到263-1，可精确到万分之一货币单位。 Money或Smallmoney类型的数值常量，应加货币符号$前缀，负数时加后缀$。 如：$222.222 , -333.333$ 第19页/共59页日期/时间型数据 SQL Server提供的日期/时间数据类型可存储日期和时间的

12、组合数据。以日期/时间类型存储日期或时间数据比字符型更简单，因为SQL Server提供了一系列专门处理日期和时间的函数来处理这类数据。若使用字符型存储日期和时间，计算机不能识别，也不能自动对这些数据按照日期和时间进行处理。 SQL Server提供了Smalldatetime和Datetime两种日期/时间的数据类型。第20页/共59页1短日期/时间型 Smalldatetime 占4个字节固定长度的内存，存放1900年1月1日到2079年6月6日的日期时间，可以精确到分。 2基本日期/时间型 Datetime 占8个字节固定长度的内存，存放1753年1月1日到9999年12月31日的日期时

13、间，可以精确到千分之一秒，即0.001s。第21页/共59页注意： 日期时间型常量与字符串常量相同必须使用单引号括起来。 SQL Server在用户没有指定小时以下精确的时间数据时，自动设置Datetime或Smalldatetime数据的时间为00:00:00。 数据库中默认的日期格式为“年-月-日”，输入时可使用年/月/日或年-月-日，也可以使用月/日/年、月-日-年、日/月/年或日-月-年。 如果使用日/月/年或日-月-年，系统不能区分时默认按“月-日-年”处理。第22页/共59页字符型数据 SQL Server提供了Char(n)、Varchar(n)和Text三种ASC码字符型数据。

14、1定长字符型 Char(n) 按n个字节的固定长度存放字符串，每个字符占用一个字节，长度范围1n8000； 若实际字符串长度小于n，则尾部填充空格按n个字节的字符串存储。第23页/共59页2变长字符型 Varchar(n) 按不超过n个字节的实际长度存放字符串，可指定最大长度为1n8000；若实际字符串长度小于n，则按字串实际长度存储，不填充空格。当存储的字符串长度不固定时，使用Varchar数据类型可以有效地节省空间。 3文本类型 Text Text类型存储的是可变长度的字符数据类型，最大长度为231-1字节2GB的数据。当存储超过8000字节时，可选择Text数据类型。第24页/共59页字

15、符型数据采用ASC码字符集，英文符号占1个字节，汉字占2个字节。字符型字符串常量必须使用单引号括起来。Char、Varchar数据类型若不指定长度，默认为1字节。若实际字符串长度超过指定的n个字节，用局部变量存储时只截取字符串前n个字节，其余字符舍掉。字符型字符串数据可以在企业管理器中直接输入到数据表中，但输入的字符串长度超过字段指定长度时系统不接受，也不会自动截取。第25页/共59页在用INSERT或UPDATE为数据表字符型字段输入、更新字符串时，如果超过n字节则系统提示“将截断字符串或二进制数据”并终止命令执行，不能保存该数据。如果列数据项的大小一致，则使用Char。如果列数据项的大小差

16、异相当大，则使用 Varcha。第26页/共59页【例4-1】字符型字符串abcdABCD我们学习共12个字符占16字节。 若定义数据类型为char(20)则存储为： abcdABCD我们学习 若定义数据类型为varchar(20)则按实际长度存储为： abcdABCD我们学习 若定义数据类型为char(10)或varchar(10)，则在局部变量中存储为：abcdABCD我 。存储到数据表字段中时，系统提示“将截断字符串或二进制数据”不能保存该数据。 第27页/共59页统一字符型数据 统一字符型也称为宽字符型，采用Unicode字符集，包括了世界上所有语言符号，一个英文符号或一个汉字都占用2

17、个字节的内存。前127个字符为ASC码字符。 SQL Server提供了Nchar(n)、Nvarchar(n)和Ntext三种统一字符型数据。1定长统一字符型 Nchar(n) 按n个字符的固定长度存放字符串，每个字符占用2个字节，长度范围1n4000；若实际字符个数小于最大长度n，则尾部填充空格按n个字符存储。第28页/共59页2变长统一字符型 Nvarchar(n) 按不超过n个字符的实际长度存放字符串，可指定最大字符数为1n4000；若实际字符个数小于n，则按字符串实际长度占用存储空间，不填充空格。 3统一字符文本类型 Ntext Ntext存储的是可变长度的双字节字符数据类型，最多可

18、以存储（230-1）/2个字符。第29页/共59页说明：统一字符型数据长度n为“字符”个数，所占字节数为2*n。统一字符型字符串常量必须使用单引号括起来。 Nchar、Nvarchar数据类型若不指定长度n，默认为1个字符（2个字节）。若实际字符串长度超过指定的n个字符，用局部变量存储时只截取字符串前n个字符，其余字符舍掉。第30页/共59页统一字符型数据可以在企业管理器中直接输入到数据表中，但输入的字符个数超过字段长度时则系统不接受。在用INSERT或UPDATE为数据表统一字符型字段输入、更新字符串时，若超过n个字符则系统提示“将截断字符串或二进制数据”并终止命令执行，不能保存该数据。第3

19、1页/共59页【例4-2】字符串abcdABCD我们学习作为统一字符型共12个字符占24字节。 若定义数据类型为Nchar(14)则存储为： abcdABCD我们学习 若定义数据类型为Nvarchar(14)则按实际字符数存储为：abcdABCD我们学习 若定义数据类型为Nchar(10)或Nvarchar(10)，则在局部变量中存储为： abcdABCD我们 存储到数据表字段中时，系统提示“将截断字符串或二进制数据”不能保存该数据。第32页/共59页、二进制数据 SQL Server用binary、varbinary 和image三种数据类型来存储二进制数据。二进制类型可用于存储声音图像等数

20、字类型的数据。1、定长二进制 binary(n) 按n个字节的固定长度存放二进制数据，最大长度为8K字节，即1n8000。 若实际数据不足n个字节，则在数据尾部加0补足n个字节。第33页/共59页2、变长二进制 varbinary(n) 按不超过n个字节的实际长度存放二进制数据，最大长度8K字节，即1n8000。 若实际数据不足n个字节，按实际长度存储数据，不补充加0； 3、图像二进制image 图像二进制为长度可变的二进制数据，可存储0231-1(2 147 483 647) 个字节。比如文本文档、Excel图表以及图像数据（包括.GIF、.BMP、.JPEG文件）等。 第34页/共59页注

21、意：二进制数据常量不允许加引号，默认用十进制书写，如1234、1011均视为十进制常量，如果使用十六进制则必须加0 x前缀。输出显示默认采用十六进制。若实际数据二进制长度超过指定的n个字节，用局部变量存储时只截取二进制的前n个字节，其余二进制位舍掉。数据库中二进制字段的数据不能在数据表中直接输入，即“无法编辑该单元”。第35页/共59页在用INSERT或UPDATE为数据表二进制字段输入、更新数据时，如果超过n字节，则系统提示“将截断字符串或二进制数据”并终止命令执行，不能保存该数据。如果列数据项的大小一致，则使用 binary，如果列数据项的大小差异相当大，则使用varbinary ，当列数

22、据条目超出 8000 字节时，请使用varbinary (max)。max 指示最大的存储大小为2311字节。第36页/共59页【例4-3】二进制数据1aa2bb3cc4共40位5个字节，可表示为0 x1aa2bb3cc4 若定义数据类型为binary(6)则数据后加0按6字节存储为：0 x1aa2bb3cc400 若定义数据类型为varbinary(6)则按实际数据存储为：0 x1aa2bb3cc4 若定义数据类型为binary(4)或varbinary(4)，则在局部变量中存储为：0 x1aa2bb3c。存储到数据表字段中时，系统提示“将截断字符串或二进制数据”不能保存该数据。第37页/共

23、59页图像、文本型数据的存储方式 存储文本、图像等大型数据时，可使用Text、Ntext和Image三种数据类型，这三种数据类型的数据量往往比较大，在SQL Server 7.0以前的版本中，文本和图像数据被存储在专门的页中，在数据行的相应位置只保存指向这些数据的指针。 SQL Server 2000提供了将小型文本或图像数据直接在行中存储的功能，不需要到另外的页中访问这些数据，使得读写文本和图像数据可以与读写普通字符串一样快。第38页/共59页 使用系统存储过程sp_tableoption可以指定文本或图像数据是否在表的行中存储。 语法格式： sp_tableoption 表名,text i

24、n row,TRUE|FALSE第39页/共59页说明： 当指定“TRUE”选项时，允许小型文本或图像数据直接在该表的行中存储，而且还可以指定文本或图像数据大小的上限值（24-7000字节），默认上限256字节。 当数据的大小不超过上限值而且同时数据行有足够空间时，文本和图像数据就会直接存储在行中。 若以上两个条件有一个不满足时，行中仍只存放指向数据存储位置的指针。第40页/共59页【例4-4】在当前数据库中创建数据表example，字段bin_1存放TEXT类型数据，bin_2存放NTEXT类型数据，bin_3存放Image类型数据。CREATE TABLE example(bin_1 te

25、xt,bin_2 ntext,bin_3 ntext Image)Go /* 以下语句指定不大于1000字节的文本或图像数据在行中存储 */sp_tableoption example, text in row, TRUEsp_tableoption example ,text in row, 1000 /* 以下语句指定Mytable表不在行中存储文本和图像数据 */sp_tableoption Mytable , text in row, FALSE第41页/共59页4.4 T-SQL的运算符与表达式 运算符是一些符号，它们能够用来执行算术运算、赋值运算、字符串连接以及在字段、常量和变量之

26、间进行比较。 在T-SQL中，运算符主要有以下6大类：算术运算符、赋值运算符、字符串串联运算符、比较运算符、逻辑运算符以及位运算符。第42页/共59页、算术运算符及表达式1、算术运算符： 算术运算符可以在两个表达式上执行数学运算，这两个表达式可以是数字数据类型分类的任何数据类型。 SQL标准中规定了5种用于表达式的符号：加号(+)、减号(-)、乘号(*) 、 除号(/)和取模(%) 。在SQL中，这5种符号的优先权与数学中的优先权相同，乘除的优先权高于加减的优先权，乘除具有相同的优先权，加减之间也具有相同的优先权。它们都可以在同一表达式中使用，具有相同优先权的符号按照从左到右的顺序来计算。第4

27、3页/共59页2、算术表达式 表达式是符号与运算符的组合，简单的表达式可以是一个常量、变量、列或标量函数，也可以用运算符将两个或更多的简单表达式连接起来组成复杂的表达式。 表达式在SQL查询中的应用，使SQL的查询操作有了更大的灵活性。表达式中可以包含的数据类型非常广泛，数据库中基本的数据类型，如数字数据类型、字符串数据类型、时间和日期数据类型等，都可以在表达式中使用。第44页/共59页运算符数据类型+整型浮点数精确数货币型-*/%整型第45页/共59页、赋值运算符及表达式 赋值运算符的作用是能够将数据值指派给特定的对象。 T-SQL 中只有一个赋值运算符，即等号(=)。第46页/共59页、字

28、符串连接运算符及表达式 在T-SQL中，字符串连接运算符只用“+”，而不能用“|”或“&”。 字符串表达式：字符串表达式1 + 字符串表达式2 字符串表达式的值仍是字符串。 如：ABC+EFG 则字符串表达式连接后的新字符串是：ABCEFG。 如：ABC+，+EFG 则连接后的新字符串是ABC，EFG。第47页/共59页、比较运算符及表达式 比较运算符用于比较两个表达式的大小或是否相同，其比较的结果是逻辑值，即TRUE(表示表达式的结果为真)、FALSE(表示表达式的结果为假) 。除了 text、ntext 或 image 数据类型的表达式外，比较运算符可以用于所有的表达式。第48页/共59页运算