矿大数据库应用

1、n T-SQL语言简介语言简介n 数据类型数据类型 系统数据类型和用户定义数据类型系统数据类型和用户定义数据类型n 变量、运算符、函数变量、运算符、函数 运算符运算符 函数函数n 批处理和流程控制批处理和流程控制 批和脚本批和脚本 流程控制流程控制1l1970年年6月月，埃德加，埃德加考特考特(Edgar Frank Codd)在在Communications of ACM 上发表了大型共享数据库数据的上发表了大型共享数据库数据的关系模型一文。首次明确而清晰地为数据库系统提出了一关系模型一文。首次明确而清晰地为数据库系统提出了一种崭新的模型，种崭新的模型， 即关系模型。即关系模型。l1970

2、年以后，考特继续致力于完善与发展关系理论。年以后，考特继续致力于完善与发展关系理论。l1972 年，他提出了关系代数和关系演算的概念，年，他提出了关系代数和关系演算的概念， 定义了关系定义了关系的并、交、投影、选择、连接等各种基本运算，为的并、交、投影、选择、连接等各种基本运算，为SQL语言语言的形成和发展奠定了理论基础。的形成和发展奠定了理论基础。l1979年，年，SQL(Structure Query Language)在商业数据库中成在商业数据库中成功得到了应用。功得到了应用。2l1986年，年，ANSI正式发表了编号为正式发表了编号为X3.135-1986的的SQL标准，标准，并且在并

3、且在1987年获得了年获得了ISO组织的认可，被命名为组织的认可，被命名为ISO9075-1987。后来这个标准在。后来这个标准在1992、1999、2001、2003年等不断年等不断地得到了扩充和完善。地得到了扩充和完善。l从从SQL语言的历史来看，语言的历史来看，T-SQL语言与语言与SQL语言并不完全语言并不完全等同等同。不同的数据库供应商一方面采纳了。不同的数据库供应商一方面采纳了SQL语言作为自语言作为自己数据库的操作语言，另一方面又对己数据库的操作语言，另一方面又对SQL语言进行了不同语言进行了不同程度的扩展。这种扩展的主要原因是不同的数据库供应商程度的扩展。这种扩展的主要原因是不

4、同的数据库供应商为了达到特殊目的和实现新的功能，不得不对标准的为了达到特殊目的和实现新的功能，不得不对标准的SQL语言进行扩展，而这些扩展往往又是语言进行扩展，而这些扩展往往又是SQL标准的下一个版标准的下一个版本的主要实践来源。本的主要实践来源。3T-SQL语句语句相关例子相关例子数据操作语句数据操作语句SELECT、INSERT、DELETE、UPDATE数据定义语句数据定义语句CREATE TABLE、DROP TABLE、ALTER TABLECREATE VIEW、DROP VIEWCREATE INDEX、DROP INDEXCREATE PROCEDURE、ALTER PROCE

5、DURE、DROP PROCEDURECREATE TRIGGER、ALTER TRIGGER、DROP TRIGGER数据控制语句数据控制语句GRANT、DENY、REVOKE附加的语言元素附加的语言元素BEGIN TRANSACTION/COMMIT、ROLLBACK、SET TRANSACTIONDECLARE、OPEN、FETCH、CLOSE、EXECUTE4l每条每条SQL 语句均由一个动词（语句均由一个动词（Verb）开始，该动词描述）开始，该动词描述语句要产生的动作。图语句要产生的动作。图4-1 中的动词中的动词SELECT 后紧接着一后紧接着一个或多个子句，个或多个子句， 子句

6、中给出了被动词作用的数据或提供子句中给出了被动词作用的数据或提供动词动作的详细信息。动词动作的详细信息。图图2-1 SQL SERVER语句的结构语句的结构5qSELECT语句是语句是SQL Server中最基本和最重要的语句之中最基本和最重要的语句之一。一。 SELECT语句是执行全部命令的基础。语句是执行全部命令的基础。q所谓查询就是对所谓查询就是对SQL Server发出一个数据请求，数据查发出一个数据请求，数据查询语句只是提出要询语句只是提出要“查询什么查询什么”和和“从什么地方查询从什么地方查询”，那那“怎么查询怎么查询”则由数据库管理系统则由数据库管理系统DBMS来完成。来完成。q

7、SELECT语句的简单用法语句的简单用法： SELECT FROM WHERE 678l程序代码中不执行的文本字符串，用于对代码进程序代码中不执行的文本字符串，用于对代码进行说明或暂时禁用正在进行诊断的部分语句。一行说明或暂时禁用正在进行诊断的部分语句。一般地，注释主要描述程序名称、作者名称、变量般地，注释主要描述程序名称、作者名称、变量说明、代码更改日期、算法描述等。说明、代码更改日期、算法描述等。l两种注释方式两种注释方式l双连字符双连字符(-)l正斜杠星号字符对正斜杠星号字符对(/*/)910q 数据类型：数据所代表信息的类型，它关系到数据类型：数据所代表信息的类型，它关系到系统在内存或

8、磁盘上开辟相应的存储空间。系统在内存或磁盘上开辟相应的存储空间。qSQL Server 2008提供了提供了33种数据类型，分为数种数据类型，分为数字数据类型、字符数据类型、日期和时间数据类字数据类型、字符数据类型、日期和时间数据类型、二进制数据类型以及其他数据类型。型、二进制数据类型以及其他数据类型。q需要使用数据类型的对象包括表中的列、视图需要使用数据类型的对象包括表中的列、视图中的列、定义的局部变量、存储过程中的参数、中的列、定义的局部变量、存储过程中的参数、函数及存储过程的返回值等。函数及存储过程的返回值等。11q最常用的数据类型之一，主要用来存储整数值，可直接进最常用的数据类型之一，

9、主要用来存储整数值，可直接进行数据运算。行数据运算。 q int： 4字节，取值范围为字节，取值范围为-231（-2147483648）231-1（2147483647） 。 q Smallint： 2字节，取值范围为字节，取值范围为-215（-32768）215-1 （32768）。）。q Tinyint： 1字节，取值范围为字节，取值范围为0255 。 q Bigint： 8字节，取值范围为字节，取值范围为-263（-9 223 372 036 854 775 808 ）263（9 223 372 036 854 775 807）。适用于存储）。适用于存储长度超过长度超过int范围的整型数

10、据范围的整型数据 。q Bit： 1字节，两种取值：字节，两种取值：0和和1。一般用于保存用来表示。一般用于保存用来表示逻辑值的数据。例如，是否会员，逻辑值的数据。例如，是否会员， 是否是新消息等。是否是新消息等。12q用于存储十进制小数。用于存储十进制小数。q Real：取值范围为：取值范围为-3.40E+383.40E+38（4字节）字节） 。 q Float ( n ) ： n 为用于存储为用于存储 尾数的位数，尾数的位数， n1,53 ，默认值为默认值为 53。范围从。范围从-1.79E-308到到1.79E+308，（，（4或或8字节）。字节）。 n如不指定如不指定float的长度，

11、会被存储在的长度，会被存储在8个字节中。当指定长个字节中。当指定长度为度为124间数值时，则实际上定义了一个间数值时，则实际上定义了一个real数据类型。数据类型。n如果如果 1=n=24，则将，则将 n 视为视为 24。如果。如果 25=n类类型型”，单击，单击“新建新建”“用户定用户定义数据类型义数据类型”菜单项，在弹出的菜单项，在弹出的“用户定义数据类型属性用户定义数据类型属性”对话对话框中，输入新建数据类型的名称，框中，输入新建数据类型的名称，选择系统数据类型；更改此数据选择系统数据类型；更改此数据类型可存储的最大数据长度，选类型可存储的最大数据长度，选择择“允许空值允许空值”。25q

12、利用系统存储过程创建利用系统存储过程创建q语法：语法：sp_addtype type_name , phystype(length)|(precision,scale),null | not null | identityqtype_name，用户定义的数据类型的名字；，用户定义的数据类型的名字；qPhystype，基于的系统数据类型，可包括长度、精度、标，基于的系统数据类型，可包括长度、精度、标度；度；qnull | not null | identity，用户自定义数据类型的性质，分，用户自定义数据类型的性质，分别为允许空值、不允许为空值、具有标识列性质。别为允许空值、不允许为空值、具有标

13、识列性质。q提醒：后续版本的提醒：后续版本的 Microsoft SQL Server 将删除该功能。将删除该功能。 2627q右击右击“用户定义数据类型用户定义数据类型”，单击，单击“删除删除”，再单击，再单击“确确定定” 。q也可用系统存储过程来查看、重命名、删除用户定义的数也可用系统存储过程来查看、重命名、删除用户定义的数据类型。据类型。 qsp_help type_name查看用户自定义数据类型的特征；查看用户自定义数据类型的特征；qsp_rename type_name给用户自定义数据类型重新命名；给用户自定义数据类型重新命名；qsp_droptype type_name删除一个已经

14、定义且未被使用的用删除一个已经定义且未被使用的用户自定义数据类型。户自定义数据类型。2829p 变量变量 局部变量和全局变量局部变量和全局变量p 常量常量p 运算符运算符 运算符的类别运算符的类别 运算符的优先级别运算符的优先级别 p 系统函数系统函数 数学函数、字符串函数数学函数、字符串函数 日期和时间函数日期和时间函数 转换函数转换函数 系统函数系统函数 聚合函数聚合函数 30q局部局部变量：由用户根据需要定义的变量：由用户根据需要定义的变量变量，使用范围，使用范围只局限于某一个语句或过程体内。只局限于某一个语句或过程体内。q主要用于保存临时数据或由存储过程返回的结果。主要用于保存临时数据

15、或由存储过程返回的结果。q作用域是其被声明时所在批处理。作用域是其被声明时所在批处理。q必须先用必须先用DECLARE语句定义后才可以使用。语句定义后才可以使用。31l定义语法形式：定义语法形式： DECLARE local_variable AS data_type | = value | cursor_variable_name CURSOR ,.n l局部变量不能是局部变量不能是 text，ntext 或或 image 数据类型。数据类型。l声明局部变量之后，会将其设为声明局部变量之后，会将其设为NULL，如设定新值，须，如设定新值，须用用SELECT或者或者SET命令。其语法形式为：命

16、令。其语法形式为： SET local_variable = expression 或或 SELECT local_variable = expression ,.n l其中，参数其中，参数expression是任何有效的是任何有效的SQL Server表达式。表达式。32l例例2.4，声明一个长度为，声明一个长度为9个字符的变量个字符的变量city，并赋值，并赋值“Welcome to changsha”。DECLARE city CHAR(9)SELECT city = Welcome to changshaSELECT cityl建议：当执行简单变量赋值时，使用建议：当执行简单变量赋值时

17、，使用 SET语句；当基于语句；当基于查询进行变量赋值时，使用查询进行变量赋值时，使用SELECT语句。语句。33q由由SQL SERVER预先定义并负责维护，主要用于保存系统预先定义并负责维护，主要用于保存系统的某些参数值和性能统计数据，作用域覆盖所有程序。的某些参数值和性能统计数据，作用域覆盖所有程序。q全局变量都以标记符全局变量都以标记符“”开头。开头。q分类，一是与分类，一是与SQL Server连接有关的全局变量，如连接有关的全局变量，如rowcount表示受最近一个语句影响的行数；二是关于系表示受最近一个语句影响的行数；二是关于系统内部信息有关的全局变量，如统内部信息有关的全局变量

18、，如version表示表示SQL Server的版本号。的版本号。qIDENTITY：返回最后一条：返回最后一条Insert语句执行后的标识值。语句执行后的标识值。q注意：局部变量的名称不能与全局变量的名称相同，否则注意：局部变量的名称不能与全局变量的名称相同，否则会在应用程序中出现不可预测的结果。会在应用程序中出现不可预测的结果。34例例2.7返回当前用户进程的会话返回当前用户进程的会话 ID、登录名和用户名。、登录名和用户名。 SELECT SPID AS ID, SYSTEM_USER AS Login Name, USER AS User Name35l常量：程序运行中值不变的量，也称

19、为文字值或常量：程序运行中值不变的量，也称为文字值或标量值标量值 。l常量格式取决于其所表示的值的数据类型。例，常量格式取决于其所表示的值的数据类型。例，This is a book. 、May 1, 2006、98321等。对于等。对于字符常量或时间日期型常量，需用单引号引起来。字符常量或时间日期型常量，需用单引号引起来。l分类：字符串常量、二进制常量分类：字符串常量、二进制常量 、bit常量常量 、datetime常量常量 、integer常量、常量、 decimal常量、常量、 float 和和 real 常量、常量、 money常量、常量、uniqueidentifier常量等。常量等

20、。36主要有六大类：算术运算符、赋值运算符、主要有六大类：算术运算符、赋值运算符、位运算符、比较运算符、逻辑运算符以及字符串位运算符、比较运算符、逻辑运算符以及字符串连接运算符连接运算符(参见参见P29)。运算符的优先级别运算符的优先级别 37Microsoft SQL Server 2008系统提供了许多系统提供了许多内置函数，这些函数可以完成许多特殊的操作，内置函数，这些函数可以完成许多特殊的操作，增强了系统的功能，提高了系统的易用性。增强了系统的功能，提高了系统的易用性。 383940例例2.8 先使用先使用SUBSTRING函数从字符串中获取指定的子字符串，函数从字符串中获取指定的子字

21、符串，然后使用然后使用LOWER函数、函数、UPPER函数对子字符串进行转换，函数对子字符串进行转换，且把且把UPPER函数嵌套在函数嵌套在LOWER函数内或把函数内或把LOWER函数嵌函数嵌套在套在UPPER内。内。DECLARE string_lower_upper varchar(40)SET string_lower_upper = Information Model Creation Issues.SELECT LOWER(SUBSTRING(string_lower_upper, 1, 11) AS Lower, UPPER(SUBSTRING(string_lower_upper

22、, 13, 5) AS Upper, LOWER(UPPER(SUBSTRING(string_lower_upper, 19, 8) As LowerUpper, UPPER(LOWER(SUBSTRING(string_lower_upper, 28, 7) As UpperLower41 用于对日期和时间数据进行各种不同的处理和运算，并返用于对日期和时间数据进行各种不同的处理和运算，并返回一个字符串、数字值或日期和时间值。回一个字符串、数字值或日期和时间值。 42 SQL Server能够自动处理某些数据类型的转换。例如，能够自动处理某些数据类型的转换。例如，char和和varchar、

23、smallint和和int，可以将自动转换（隐式转，可以将自动转换（隐式转换）。但无法自动转换或者自动转换的结果不符合预期结换）。但无法自动转换或者自动转换的结果不符合预期结果的，就需要使用转换函数做显式转换。果的，就需要使用转换函数做显式转换。 43ldata_type 作为目标的系统提供数据类型。作为目标的系统提供数据类型。llength nchar、nvarchar、char、varchar、binary 或或 varbinary 数据类型的可选参数。对于数据类型的可选参数。对于 CONVERT，如果未指定，如果未指定 length，则默认为，则默认为 30 个字符。个字符。lstyle

24、 用于将用于将 datetime 或或 smalldatetime 数据转换为字符数据数据转换为字符数据（nchar、nvarchar、char、varchar、nchar 或或 nvarchar 数据类型）的日期格式的样式；或用于将数据类型）的日期格式的样式；或用于将 float、real、money 或或 smallmoney 数据转换为字符数据（数据转换为字符数据（nchar、nvarchar、char、varchar、nchar 或或 nvarchar 数据类型）数据类型）的字符串格式的样式。如果的字符串格式的样式。如果 style 为为 NULL，则返回的结，则返回的结果也为果也为

25、NULL。444546USE AdventureWorks;GOSELECT The list price is + CAST(ListPrice AS varchar(12) AS ListPriceFROM Production.ProductWHERE ListPrice BETWEEN 350.00 AND 400.00;GO47 用于返回系统、用户、数据库的信息。用户得到信息后，用于返回系统、用户、数据库的信息。用户得到信息后，可以使用语句进行相关的操作。可以使用语句进行相关的操作。 4849 针对整个或者一个表列或几个表列进行数据汇总，常用针对整个或者一个表列或几个表列进行数据汇总

26、，常用来计算来计算SELECT语句查询结果集的统计值。如求一个结果语句查询结果集的统计值。如求一个结果集的最大值、最小值、平均值和所有元素和等。集的最大值、最小值、平均值和所有元素和等。 聚合函数聚合函数AVG、COUNT、MIN、MAX和和SUM是针对是针对表列进行运算，并回单一的值，其中表列进行运算，并回单一的值，其中COUNT、MIN和和MAX可用于各种数据类型，但可用于各种数据类型，但AVG及及SUM则仅适用于数则仅适用于数值类型。值类型。 50程序是由批、注释、变量、改变执行顺序的控制语句、程序是由批、注释、变量、改变执行顺序的控制语句、错误和消息的处理等成分组成的。错误和消息的处理

27、等成分组成的。 批（处理）批（处理）批是作为一个逻辑单元的一组批是作为一个逻辑单元的一组T-SQL语句。语句。一个批中的所有语句被组合成一个执行计划。因此一个批中的所有语句被组合成一个执行计划。因此对所有语句一起进行语法分析且通过语法验证，否则对所有语句一起进行语法分析且通过语法验证，否则将不执行任何一条语句。将不执行任何一条语句。但如果运行时发生错误，那么任何发生在运行时错但如果运行时发生错误，那么任何发生在运行时错误之前的语句仍然有效。除非是未提交事务的一部分。误之前的语句仍然有效。除非是未提交事务的一部分。51qGO：向：向 SQL Server 实用工具发出一批实用工具发出一批 Tra

28、nsact-SQL 语句结束的信号。语句结束的信号。 q两个两个GO之间的之间的T-SQL语句作为一个批。在一个语句作为一个批。在一个批中可包含一条或多条批中可包含一条或多条T-SQL语句，成为一个语语句，成为一个语句组。这样的语句组从应用程序一次性地发送到句组。这样的语句组从应用程序一次性地发送到SQL Server服务器进行执行。服务器进行执行。q使用批的限制（使用批的限制（P35）q脚本：一系列顺序提交的批。脚本：一系列顺序提交的批。 52/*例例2.10 由三个批处理组成的脚本。由三个批处理组成的脚本。*/USE studentGOCREATE VIEW teacher_info_vi

29、ewASSELECT teacher_id, name, tech_title, salary FROM teacher_infoGOSELECT * FROM teacher_info_viewGO脚本脚本5354qBEGINEND用来设定一程序块用来设定一程序块q IFELSE用来判断当某一条件成立时执行某段用来判断当某一条件成立时执行某段程序，条件不成立时执行另一段程序。程序，条件不成立时执行另一段程序。 q CASE语句为多分支语句语句为多分支语句q WHILECONTINUEBREAK循环语句循环语句流程控制流程控制551. BEGINEND语句语句语法格式：语法格式：BEGIN E

30、NDl用来设定一个程序块，将在用来设定一个程序块，将在BEGIN END 内的所内的所有程序视为一个单元执行。有程序视为一个单元执行。l经常在条件语句如经常在条件语句如IF ELSE 中使用，在中使用，在BEGIN END 中可嵌套另外的中可嵌套另外的BEGIN END 来定义另一程来定义另一程序块。序块。56流程控制流程控制(示例示例2.11 )572. IFELSE语句语句l语法形式：语法形式： IF Boolean_expression sql_statement | statement_block ELSE sql_statement | statement_block lELSE子句

31、是可选的。子句是可选的。l允许嵌套使用允许嵌套使用IFELSE语句，而且嵌套层数语句，而且嵌套层数没有限制。没有限制。58流程控制流程控制(示例示例2.12 )593. CASE语句语句l作用：计算多个条件式，并将其中一个符合条件作用：计算多个条件式，并将其中一个符合条件的结果表达式返回。的结果表达式返回。l两种使用形式两种使用形式l简单式简单式 CASE结构：将某个表达式与一组简单结构：将某个表达式与一组简单表达式进行比较以确定结果。表达式进行比较以确定结果。l搜索式搜索式CASE结构：结构： 计算一组布尔表达式以确计算一组布尔表达式以确定结果。定结果。l共同点：两种格式都支持可选的共同点：

32、两种格式都支持可选的 ELSE 参数。参数。 60流程控制流程控制(示例示例2.13 )61流程控制流程控制(示例示例2.14 )l例例2.14 从从stud_grade表中查询所有同学考试成绩情况，凡表中查询所有同学考试成绩情况，凡成绩为空者输出成绩为空者输出“未考未考”、小于、小于60分输出分输出“不及格不及格”、60分至分至70分输出分输出“及格及格”、70分至分至90分输出分输出“良好良好”、大于、大于或等于或等于90分时输出分时输出“优秀优秀”。SELECT stud_id,name,score=CASE WHEN grade IS NULL THEN 未考未考 WHEN grade

33、=60 AND grade=70 AND grade=90 THEN 优秀优秀 ENDFROM stud_grade624. WHILECONTINUEBREAK语句语句WHILE Boolean_expression sql_statement | statement_block BREAK sql_statement | statement_block CONTINUE sql_statement | statement_block l在设定的条件成立时会重复执行命令行或程序块。在设定的条件成立时会重复执行命令行或程序块。lCONTINUE 可让程序跳过本次循环，其后的语句回到下可让程序跳

34、过本次循环，其后的语句回到下次次WHILE 循环的第一行命令。循环的第一行命令。lBREAK 命令则让程序完全跳出循环，结束执行命令则让程序完全跳出循环，结束执行WHILE 语句。语句。63declare x int, y int, nums intselect x = 0, y=1set nums=0while y 100beginif(y%3=0)beginset x=x+y;set nums=nums+1endset y=y+1endprint str(x)+,+str(nums)例例2.15 计算计算100内能被内能被3整除的数的个数及总和整除的数的个数及总和64q WAITFOR语句用来暂时停止程序执行语句用来暂时停止程序执行q GOTO语句用来改变程序执行的流程语句用来改变程序执行的流程q RETURN语句用于结束当前程序语句用于结束当前程序(所在的批、存所在的批、存储过程和触发器储过程和触发器)的执行，返回到上一个调用它的的执行，返回到上一个调用它的程序。程序。q PRINT语句的作用是在屏幕上显示用户信息。语句的作用是在屏幕上显示用户信息。q RAISER