第4章TSQL语言1.ppt

1、1,第三章,SQL Server数据管理,2,回顾,数据完整性包括了实体完整性、引用完整性、域完整性和自定义完整性 创建数据库表的过程实际上就是实施完整性约束的过程 创建表包括选择字段名称、数据类型、定义是否为空、设置默认值、主键和关系、检查约束等 表中没有合适的列作为主键，可以创建标识列,3,目标,理解T-SQL及其作用 理解变量和标识符 使用SQL Server中的逻辑表达式和运算符 使用T-SQL查询表中数据 使用T-SQL向表中插入数据 使用T-SQL更新表中的数据 使用T-SQL删除表中的数据 T-SQL函数,4,4.1SQL概述,WHAT(SQL是什么？) Structured Q

2、uery Language：结构化查询语言 WHY(为何要使用SQL？) 难道仅仅使用企业管理器操作SQL Server数据库? 应用程序如何与数据库打交道？ WHEN(何时使用?) 对SQL Server执行所有的操作都可以 程序中的增删改查 HOW(怎么使用?) ,5,一、SQL和T-SQL,T-SQL = Transact-SQL T-SQL是SQL的加强版 对功能进行了扩充：如变量说明、流程控制、功能函数 思考：ORACLE数据库支持标准SQL，在SQL Server中编写的T-SQL语句他也支持吗？,6,二、T-SQL的组成,DML（数据操作语言） 查询、插入、删除和修改数据库中的数

3、据； SELECT、INSERT、 UPDATE 、DELETE等； DCL（数据控制语言） 用来控制存取许可、存取权限等； GRANT、REVOKE 等； DDL（数据定义语言） 用来建立数据库、数据库对象和定义其列 CREATE TABLE 、DROP TABLE 等 变量说明、流程控制、功能函数 定义变量、判断、分支、循环结构等 日期函数、数学函数、字符函数、系统函数等,7,讲述T-SQL之前,8,4.2语言的基本知识,4.2.1 变量和标识符 1、局部变量：一般在一个批处理中被声明、定义、赋值和引用，批处理结束后，在批处理中使用的局部变量也就消失了， 2、全局变量：是用来记录SQL S

4、erver服务器活动状态的一组数据，它已经被预先定义，用户只可以使用，不可以重新定义和赋值,9,一、局部变量,1、局部变量定义： 格式：DECLARE (） , 说明： ：局部变量的名称。必须以开头,遵循SQLServer的标识符和对象的命名规范，而且名字不能使用保留字。 (）：为局部变量指定的数据类型。可以是除text,ntext或者image的所有系统数据类型或用户自定义数据类型. 可以声明多个变量，用逗号间隔。 变量初值是NULL,例：DECLARE STUDENT_COUNT INT,CLASS_NAME VARCHAR(10),10,2、局部变量赋值和显示,1)局部变量赋值： 格式1

5、：SET = 格式2:或SELECT = 2)查询变量 格式：SELECT ,SET STUDENT_COUNT=10 SELECT CLASS_NAME=08计应,SELECT STUDENT_COUNT,CLASS_NAME GO,注意： 1、若赋值长度超过变量声明的长度，超过的部分被截去 2、可用SELECT语句从表中查询出的结果给局部变量赋值；但要保证查询出的值的惟一性。 3、局部变量与值的数据类型不匹配时，值隐式转化为局部变量的数据类型；,11,二、全局变量,全局变量是SQLServer系统所提供并赋值的变量。 用户不能建立和修改全局变量 全局变量的名字以开头。 大多数全局变量的值是

6、报告本次SQLServer启动后发生的系统活动。通常应该将全局变量的值赋给在同一个批中的局部变量，以便保存和处理。 SQLServer提供的全局变量分为两类： 1)与SQLServer连接有关的全局变量。如：rowcount表示受最近一个语句影响的行数。 2)关于系统内部信息有关的全局变量。如：version表示SQLServer的版本号。 查询全局变量：SELECT rowcount,12,常用全局变量,全局变量反映的是服务器当前的状态，SQLServer2000提供了30多个全局变量，下面介绍几个常用的全局变量： connections：返回今天试图连接到本服务器的连接的数目。 rowco

7、unt：返回上一条T-SQL语句影响到的数据行数。 error：返回上一条T-SQL语句执行后的错误号。 procid：返回当前存储过程的ID标识。 remserver：返回登录记录中远程服务器的名字。 spid：返回当前服务器进程的ID标识。 version：返回当前SQLServer服务器的版本、处理器类型等。,13,三、标识符,标识符用来定义服务器、数据库、数据库对象和变量等的名称。分为常规标识符和分隔标识符 1、常规标识符 标识符规则： 第一个字符可必须是以Unicode标准定义的字母、_、#、汉字。 后续字符必须是以Unicode标准定义的字母、_、#、$、_、数字、汉字。 不能是保

8、留字。不能有空格和其他字符 长度为1128之间,14,2、分隔标识符,对不符合标识符规则的标识符必须进行分隔。符合标识符规则的可分隔也可不分隔 Sql server中T-SQL分隔的方法： 非法标识符 如my table “非法标识符” 如“my table” 非法标识符包括：，-，！，%，and ,&,.,(,),等 注意：用“”做分隔符前,须执行执行 SET QUOTED_IDENTIFIER ON,15,4.2.2 T-SQL中的运算符,运算符是一种指定运算性质的符号，用来指定要在一个或多个表达式中执行的操作。运算符有优先级之分。 在SQLServer2000中，有如下几类运算符： 一元

9、运算符：+（正）、-（负）、（按位取反，只对整数操作） 算术运算符 :包括加(十)、减()、乘(*)、除()和取模() 字符串连接运算符 :+ 比较运算符: =,=,!(非），!(不大于） 逻辑运算符 ：not ,and,or 赋值运算符 ：= 优先级：括号-按位取反-正负-乘、除、取模-加、减、连接符-比较符-非-与-或-赋值,16,比较运算符,与C语言中的一样，很难写错,17,逻辑表达式,NOT (付款方式 = 信用卡) OR (信用卡 阳光卡),18,4.2.3通配符,1、邮箱的约束表达式：student_email like % 2、学号第一个字符必须g、h、i、j、k的表达式： st

10、udent_id like g-h% 3、学生第二个字符不是1、2、3，第一个字符不是g、j的表达式：student_id like gh1-3%,19,通配符使用说明,通常与LIKE关键字一起来使用 可以用在检查约束中使用LIKE 在后面的查询语句中还会经常使用到,思考：电话号码中区号是0731的表达式怎么写？,20,4.3 T_SQL操作语言,T-SQL操作语言包括 1、查询select 2、插入insert 3、更新update 4、删除delete,21,4.3.1查询,客户程序,SQL SERVER,SELECT * FROM SALES,查询产生一个虚拟表，看到的是表形式显示的结果

11、，但结果并不真正存储，每次执行查询只是现从数据表中提取数据，并按照表的形式显示出来,22,查询原理,23,思考,既然查询得到的“结果集”的结构类似于一张表，那么可以在“结果集”上继续进行查询吗？,24,1、 T-SQL语言查询数据,SELECT INTO TABLE|CURSOR FROM WHERE ORDER BY ASC或DESC,例：查询学生表中1999/1/1入学的学生的姓名和住址，并按学号排列,25,课堂练习：,查询学生表中男生的姓名和住址，并按学号排列,select student_id,student_name,home_addrfrom student where sex=1

12、 order by student_id DESC,26,数据查询-基础,查询全部的行和列,查询部分行,27,数据查询-列名,使用AS来命名列,使用=来命名列,28,数据查询-复制,例：将teacher表中所有记录的姓名,电话,住址，邮编复制到一个新表teatongxunlu中,并创建一个新字段email SELECT teacher_name,telephone,home_addr,postalcode INTO teatongxunlu FROM teacher,29,数据查询-限制行数,限制固定行数,返回百分之多少行,30,数据查询-空行常量列,查询空行,使用常量列,思考：非空行怎么查？

13、,31,数据查询-排序,升/降序排列,32,数据查询-多列排序,按多列排序,思考：排序中的字段，可以使用表达式吗？ 如果不可以，请说明原因； 如果可以，请举例说明；,33,课堂练习,1、将book表记录复制到一个新表bookinfo,结构与book相同。 2、查询bookinfo表中东南大学出版社的所有记录的book_name,author,price,要求列名用中文显示。并且按price降序排列 3、bookinfo表添加两列，数量（book_num,int)，并输入每条记录的数量 4、查询bookinfo表中price0记录的book_name,总价（=price*book_num),34

14、,2、 T-SQL语言插入记录,一、插入一条记录格式： INSERT INTO (列名列表) VALUES 举例：在student表中插入一条记录 INSERT INTO student (student_id,student_name,sex,birth,class_id,entrance_date,home_addr) VALUES(G9940212,刘宁,1,1988-12-11, g99402,1999-09-01,湖南财专) 注意：字符型和日期型值插入时要加入单引号。,35,注意事项1:,注意事项1:每次插入一行数据，不可能只插入半行或者几列数据，因此，插入的数据是否有效将按照整行的

15、完整性的要求来检验；,36,注意事项2:,注意事项2:每个数据值的数据类型、精度和小数位数必须与相应的列匹配；,37,注意事项3,注意事项3:不能为标识列指定值，因为它的数字是自动增长的；,38,注意事项4,注意事项4:如果在设计表的时候就指定了某列不允许为空，则必须插入数据；,39,注意事项5,注意事项5:插入的数据项，要求符合检查约束的要求,INSERT INTO student (student_id,student_name,sex,birth,class_id,entrance_date,email) VALUES(G9940212,刘宁,1,1988-12-11, g99402,1

16、999-09-01,gch),40,注意事项6,注意事项6:具有缺省值的列，可以使用DEFAULT（缺省）关键字来代替插入的数值,INSERT INTO student (student_id,student_name,sex,birth,class_id,entrance_date,home_addr) VALUES(G9940212,刘宁,default,1988-12-11, g99402,1999-09-01,湖南财专),41,二、插入多行数据,INSERT INTO (列名) SELECT FROM where ,42,成批插入数据,使用SELECT子句插人数据时应该注意以下几点：

17、使用SELECT子句插人数据时，table_name和table_list可以是相同的表，也可以是不同的表。 要插入的数据表table_name必须已经存在，也就是说，不能向不存在的表中插人数据。 要插人数据的表的列和SELECT子句中列的数量和顺序必须相同， 列的数据类型也要相同，如果不相同，SQLServer也要能自动转化。,43,3、用T-SQL语言更新数据行,UPDATE SET WHERE ,44,课堂练习,1、用insert命令在teatongxunlu表中添加一条新记录，姓名为 李明 ,其他字段自己填写 2、将教师表中所有记录添加到teatongxunlu,45,例 将上例中插入

18、的一行数据中住址为湖南财专的记录，改为“湖南长沙枫林路39号”。,USE jwgl GO UPDATE student SET home_addr=湖南长沙枫林路39号 WHERE home_addr=湖南财专 注意：UPDATE只能在一张表上操作，并且更新后的数据必须满足表原先的约束条件，否则，数据更新将不会成功。,46,成批修改,将student_course表中course_id=dep04_d001均加1分 UPDATE student_course SET grade=grade+1 WHERE course_id=dep04_d001,47,课堂练习,1、在教师通讯录teatong

19、xunlu表中，将姓名为李明的记录的电话号码改 3、在教师通讯录teatongxunlu表中，将邮编为210036全改为410205,48,4、T-SQL语言删除数据,如果表中的数据不再需要时，可以将其删除，以释放存储空间。对表中数据的删除是用DELETE语句实现的。 DELETE语句的语法形式是： DELETE FROM WHERE 说明：若不加WHERE子句，将会删除表中的所有的记录，所以使用时应特别小心。,49,例 将学号为G9940212插入的数据从student表中删除掉。,USE jwgl GO DELETE FROM teacher WHERE stud

20、ent_id=g9940212,50,删除数据行 3-1,DELETE FROM WHERE ,51,删除数据行 3-2,思考：以下的删除语句可以执行吗？,52,课堂练习,1、在教师通讯录中删除姓名为李明的记录 2、删除教师通讯录中邮编为210002的记录,53,4.4SQL Server中的函数,相当于C语言中的内部函数,字符串函数 日期函数 数学函数 系统函数,54,字符串函数,55,日期函数,56,数学函数,57,系统函数,58,案例分析1要求,某公司印了一批充值卡，卡的密码是随机生成的，现在出现这个问题： 卡里面的“O和0”（哦和零）“i和1”（哎和一），用户反映说看不清楚，公司决定，

21、把存储在数据库中的密码中所有的“哦”都改成“零”，把所有的“i”都改成“1”； 请编写SQL语句实现以上要求； 数据库表名：Card；密码字段名：PassWord；,59,案例分析1分析,这是更新语句，需要使用UPDATE语句； 因为牵涉到字符串的替换，需要使用到SQL Server中的函数Replace；,60,案例分析1T-SQL,两行SQL语句,一行SQL语句,61,案例分析2要求,在数据库表中有以下字符数据，如： 13-1、13-2、13-3、13-10、13-100、13-108、13-18、13-11、13-15、14-1、14-2 现在希望通过SQL语句进行排序，并且首先要按照前半部分的数字进行排序，然后再按照后半部分的数字进行排需，输出要排成这样： 13-1、13-2、13-3、13-10、13-11、13-15、13-18、13-100、