SQLServer高级培训课件

资源描述

《SQLServer高级培训课件》由会员分享，可在线阅读，更多相关《SQLServer高级培训课件（171页珍藏版）》请在金锄头文库上搜索。

1、SQL SERVERSQL SERVER高级培训高级培训高级培训高级培训主讲人主讲人: :郑维勇郑维勇SQLServer高级培训数据库的概述数据库对象表、视图、存储过程、触发器、默认值、规则、索引、用户。 SQL Server数据库的版本个人版标准版开发版企业版 Transact-SQL语言主要的4个组成部份数据定义语言DDL数据操作语言DML数据控制语言DCL共它语言元素SQLServer高级培训Transact-SQL 数据定义语言DDL用来创建数据库、数据库对象和定义列。包括：CREATE,ALTER,DROP。数据操纵语言DML用来操纵数据库的命令。包括：SELECT,INSE

2、RT,UPDATE,DELET。数据控制语言DCL用来控制数据库组件的存取许可、存取权限等的命令包括：GRANT,DENY,REVOKE。SQLServer高级培训管理SQL Server服务启动 SQL Server服务Net start mssqlserver 暂停 SQL Server服务Net pause mssqlserver 停止 SQL Server服务Net stop mssqlserverSQLServer高级培训SQL Server服务SQL Server 2000的各种服务和功能说明服务器名称文件名文件名服务说明服务说明MSSQLSERVERMSSQLSERVERSq

3、lserver.exeSqlserver.exe它负责数据存取、安全配置、事务它负责数据存取、安全配置、事务管理等管理等(Binnsqlserver.exe)(Binnsqlserver.exe)SQLSERVERAGENTSQLSERVERAGENTSqlagent.exeSqlagent.exe负责调度定期执行的活动以及通知负责调度定期执行的活动以及通知系统管理员服务器所发生的问题系统管理员服务器所发生的问题(Binnsqlagent.ext)(Binnsqlagent.ext)Microsoft SearchMicrosoft SearchMssearch.exeMssearch.exe

4、提供数据库内全文检索的功能提供数据库内全文检索的功能Distributed Transaction Distributed Transaction Coordinator(MSDTC) )Msdtc.exeMsdtc.exe是一个事务管理器，它允许客户端是一个事务管理器，它允许客户端应用程序在一个事务中包含多个不应用程序在一个事务中包含多个不同的数据源同的数据源SQLServer高级培训SQL Server服务器的注册与删除1、通过控制台目录2、使用Transact-sql sp_addserver server= server ,local= local ,duplicate_ok= dup

5、licate_okEgsp_addserver localSQLServer高级培训配置链接服务器在SQL Server 2000中,允许SQL Server用户访问基于SQL Server的服务器以外的数据。通过配置链接服务器可以实现访问其他服务器上的OLE DB数据源。链接服务器是对OLE DB数据源的抽象，是OLE DB提供程序和连接属性的来源。在链接服务器的边接过程中，为了使该连接有效，需要在链接服务器之间创建登录映射。链接服务器登录映射可以通过系统存储过程sp_addlinkedsrvlogin添加，相应地，链接服务器登录映射可以通过系统存储过程sp_droplinkedsrv

6、login删除。SQLServer高级培训配置链接服务器添加键接服务器Sp_addlinkedserver server= server,srvproduct= product_name,provider=provider_name,datasrc=data_source,location=location,provstr=provider_string,catalog=catalogEg:exec sp_addlinkedserver server=exam2,provider=Microsoft.Jet.OLEDB.4.0,srvproduct=,datasrc=d:db2.mdbsp_a

7、ddlinkedserver 链接服务器名, 产品名, Microsoft.Jet.OLEDB.4.0, Access数据库路径及文件名sp_addlinkedserver Anna, Access 2000, Microsoft.Jet.OLEDB.4.0, D:db2.mdbSQLServer高级培训配置链接服务器数据源数据源提供者名称提供者名称Product_nameProduct_nameProvider_nameProvider_nameData_sourceData_sourceSQL ServerSQL ServerSQL Server OLESQL Server OLEDB P

8、roviderDB ProviderSQL ServerSQL ServerSQL ServerSQL ServerSQL ServerSQL Server主机主机网络名称网络名称AccessAccessMicrosoft OLE DB Microsoft OLE DB provider for Jetprovider for Jet任意名称任意名称Microsoft.Jet.OLMicrosoft.Jet.OLE DB.4.0E DB.4.0AccessAccess文件路径文件路径和文件名称和文件名称ExcelExcelMicrosoft OLE DB Microsoft OLE DB pr

9、ovider for Jetprovider for Jet任意名称任意名称Microsoft.Jet.OLMicrosoft.Jet.OLE DB.4.0E DB.4.0ExcelExcel文件路径和文件路径和文件名称文件名称SQLServer高级培训配置链接服务器创建链接服务器登录通过系统存储过程sp_addlinkedsrvlogin可以创建或更新本地SQL Server实例上的登录与链接服务器上远程登录之间的映射。其语法为：Sp_addlinkedsrvlogin rmtsrvname=rmtsrvname,useself= useself,location=location,rmtu

10、ser=rmtuser,rmtpassword=rmtpassword其中，参数:(1)rmtsrvname= rmtsrvname:是应用登录映射的链接服务器名称(2)useself=useself:决定用于连接到远程服务器的登录名称，默认设置为ture.true值指定SQL Server验证的登录使用其自己的凭据以连接到rmtsrvname,而忽略rmtuser和rmtpassword参数(3)locallogin=locallogin:是本地服务器上的登录,默认设置为Null.如果为null,则指定应用于所有连接到rmtsrvname的本地登录若不为null则locallogin可以是S

11、QL Server登录或windows NT用户(4)rmtuser=rmtuser:当useself为false时,用来连接rmtsrvname的用户名,默认设置为null.SQLServer高级培训配置链接服务器创建一个映射，使得所有到本地服务器的登录都使用其各自的用户凭据连接到链接服务器examExec sp_addlinkedsrvlogin exam或Exec sp_addlinkedsrvlogin exam,true使用指定的用户和密码将所有本地登录连接到链接服务器Exec sp_addlinkedsrvlogin exam,false,null,user,password创建一

12、个映射，使得所有到本地服务器的登录都使用相同的登录user和密码password连接到链接服务器exam,不使用登录和密码Exec sp_addlinkedsrvlogin exam,falseSQLServer高级培训配置链接服务器数据访问从SQL Server 2000中访问OLE DB数据源的方法有两种：通过特殊名称以及通过链接服务器进行数据访问。在通过特殊名称访问OLE DB数据源的方法中，可以通过函数openrowset和opendatasource来进行。其语法:Openrowset (provider_name,datasource;user_id;password|provid

13、er_string,catalog.schema.object|query)Eg:SELECT * FROM OPENROWSET(Microsoft.Jet.OLEDB.4.0, d:db2.mdb; admin;, bbs)SQLServer高级培训查询分析器如何进入查询分析器？SQLServer高级培训SQL语言基础(变量与常量)定义和使用局部变量定义局部变量Declare variable_name datatype ,.nDeclare sex char(2)为局部变量赋值为局部变量赋值一般有两种,一种是使用select语句，另一种是使用Set语句Select variable_na

14、me=expressionfrom table_name,n where clausedeclare bookname varchar(30) select bookname = vcprint booknameGOSQLServer高级培训SQL语言基础(变量与常量)定义和使用全局变量全局变量是SQL SERVER系统内部事先定义好的变量，不用用户参与定义，对用户而言是只读的。identity:返回最近一次插入的identity列的数值,返回值是numericprocid:返回当前存储过程的ID标识version:返回当前SQL Server服务器安装日期、版本以及处理器的类型languag

15、e:返回当前使用的语言名servername:返回当前SQL SERVER服务器名称SQLServer高级培训SQL语言基础(变量与常量) 使用日期和时间常量SQL 提供的日期/时间数据类型可以存储日期和时间的组给数据。SQL 规定日期、时间和时间间隔的常量值被指定为字符中常量。SQLServer高级培训SQL语言基础(数据类型) 常见的数据类型1.整型(int,smallint,tinyint,bigint)2.浮点数据类型(real,float,decimal)3.二进制数据类型(binary,varbinary)4.逻辑数据类型5.字符型数据类型(char(n),nchar(n),var

16、char(n),nvarchar(n)SQLServer高级培训SQL语言基础(复合语句)定义复合语句(beginend)declare A int,B int,C intset A=20set B=30begin set C=A set A=B set B=Cendprint Aprint BSQLServer高级培训SQL语言基础(条件判断语句)使用if语句进行条件判断If命令行|程序块declare x intset x=-100if x0print x是一个负数SQLServer高级培训新建数据库新建数据库Create database命令Create database databas

17、e_nameon ,.n ,.nlog on,.ncollate collation_namefor load|for attachSQLServer高级培训新建数据库新建数据库create database student on(name=学生管理数据库,f:datastudent.mdf,size=1MB,MAXSIZE=50MB,)log on(name=学生管理日志,f:datastudent.ldf,size=1MB,MAXSIZE=50MB,)SQLServer高级培训修改数据库修改数据库 ALTER DATABASEALTER DATABASE命令命令alter database

18、 studentmodify file(name=学生管理数据库,maxsize=unlimited)SQLServer高级培训删除数据库删除数据库 Drop DatabaseDrop Database命令命令SQLServer高级培训新建表新建表 Create table Create table 命令命令Create table table_name( column_name data_type null|not null primary key|unique,)SQLServer高级培训新建表新建表use studentgocreate table stu_info(stu_id int

19、 not null,stu_name varchar(10),gender bit,major varchar(20),address varchar(100)SQLServer高级培训修改表修改表( (增加字段增加字段) ) 增加字段增加字段Alter table table_nameAlter table table_nameAdd column_name data_typeAdd column_name data_typeEg: alter table stu_info add memo textSQLServer高级培训修改表修改表( (增加主键增加主键) ) Alter table

20、add primary key(column_name)Alter table add primary key(column_name) Eg: Eg: alter table stu_info add primary key(stu_id) alter table stu_info add primary key(stu_id)SQLServer高级培训修改表修改表( (更改字段更改字段) ) 更改字段属性更改字段属性Alter table table_nameAlter table table_nameAlter column column_name new_data_typeAlter

21、column column_name new_data_typeEg:alter table stu_info alter column stu_name varchar(12)SQLServer高级培训修改表修改表( (删除字段删除字段) ) Alter table table_nameAlter table table_nameDrop column column_nameDrop column column_nameEg:alter table stu drop column memoSQLServer高级培训表的重命名表的重命名 Exec sp_rename old_table,new

22、_tableExec sp_rename old_table,new_tableEg:exec sp_rename stu_info,stuSQLServer高级培训表的删除表的删除Drop table table_nameDrop table table_nameSQLServer高级培训显示数据库信息显示SQL SERVER数据库中所有数据库详细信息Select * from sysdatabasesSQLServer高级培训显示系统/用户表显示数据库中所有表的详细信息use stuselect * from sysobjects 显示数据库中系统表的详细信息use stuselect

23、 * from sysobjects where xtype=s 显示数据库中用户表的详细信息use stuselect * from sysobjects where xtype=uSQLServer高级培训显示表中的字段名称及属性显示表中的字段名称Select col_name(object_id(tbl_stu,3) 显示表中的字段长度Select col_length(tbl_stu,stu_id) 显示表中结构Sp_help tbl_stuSQLServer高级培训SelectSelect语句语句( (列的查询列的查询) ) 单列查询Select field_name from t

24、able_name 多列查询Select field_name1,field_name2,. from table_name 查询所有列Select * from table_name 使用列名Select field_name as new_name from table_nameSQLServer高级培训SelectSelect语句语句( (列的计算列的计算) ) 简单列计算复杂列计算执行算术组合运算连接列值Select 图书名称,(单价 -进价)as 利润 from tb_xsb02Select sum(数量)as 总数量,sum(数量)%2 剩余 from tb_xsb02Sel

25、ect name 姓名,(math+english+chinese)总分,(math+english+chinese)/3 平均分 from tb_score02Select (名字+( )+姓氏) 姓名 from tb_abstu02SQLServer高级培训SelectSelect语句语句(Where(Where条件条件) ) 检索单行值不匹配检索Select * from tb_stu02 where 学号=ID001Select * from tb_stu02 where 学号ID001SQLServer高级培训SelectSelect语句语句( (区间查询区间查询) ) 利用符号进

26、行区间查询利用Bewteen进行区间查询Select * from tb_abstu02 where 年龄 18 and 年龄 90 and 英语成绩 90 利用OR运算符连接where子句Select 学生姓名,数学成绩,音乐成绩,英语成绩from tb_StuScore03where 数学成绩 90 or 英语成绩 90SQLServer高级培训SelectSelect语句语句(IN(IN运算符运算符) )Select 教师姓名,系别,年龄,性别from tb_TeacherInfo03where 系别 in(计算机系,英语系,生物系)SQLServer高级培训SelectSelect语

27、句语句(NOT(NOT运算符运算符) )Select 房间号,房间类型,价格,房态,配置from tb_kf03where (房态 NOT IN (入住)and (not(价格 between 80 and 150)SQLServer高级培训SelectSelect语句语句(like(like运算符运算符) ) 使用%匹配字符Like %mr%-包含字符mr的任何文本Like mr%-以字符mr开头的任何文本Like %mr-以字符mr结尾的任何文本select 考生课程,考生人数,有效时间 from tb_StuExam where 考生课程 like 计算机%SQLServer高级培训Se

28、lectSelect语句语句(like(like运算符运算符) ) 使用_匹配任意单个字符Select 学生编号,学生姓名,年龄,所在学院 from tb_StuInfo03 where 学生编号 like 20070101_SQLServer高级培训SelectSelect语句语句(like(like运算符运算符) ) 使用匹配某一范围的字符a-m表示a到m的所有字符0-9表示0到9的所有数字Select 学生编号,学生姓名,性别,年龄,所学专业from tb_Student05where 年龄 like 2-2_SQLServer高级培训SelectSelect语句语句(like(like

29、运算符运算符) ) 使用匹配非某一范围的字符Select 学生编号,学生姓名,性别,年龄,所学专业from tb_Student05where 年龄 like 2-2_语句Select * from stu where name like MC 功能？SQLServer高级培训SelectSelect语句语句( (数据排序数据排序) ) 升序排序Select * from tb_employee06 order by 奖金降序排序Select * from tb_employee06 order by 奖金 desc 列出前n条记录Select top n * from tbl_stu 列出

30、前n-m条记录(mn)Select top n+1 * from (select top m * from tbl_question) tbl_question order by id descSQLServer高级培训SelectSelect语句语句( (数据排序数据排序) ) 列出n%条记录Select top 10 percent * from tbl_stu 随机列出n条记录Select top 5 * from tbl_question order by newid() 不显示重复值Select distinct stu_name from tbl_stuSQLServer高级培训S

31、electSelect语句语句( (汉字排序汉字排序) ) 按姓氏笔画排序select * from tb_stu06 order by 姓名 collate chinese_prc_stroke_cs_as_ks_ws 按音序排序select * from tb_stu06 order by 姓名collate chinese_prc_cs_asSQLServer高级培训SelectSelect语句语句( (动态排序动态排序) ) CASECASE条件语句的语法格式条件语句的语法格式1.1.简单简单CASECASE函数函数Case input_expressionCase input_exp

32、ressionWhen when_expression then result_expressionWhen when_expression then result_expression.n.nElse else_result_expressionElse else_result_expressionENDEND2.CASE2.CASE搜索函数搜索函数Case Case When boolean_expression then result_expressionWhen boolean_expression then result_expression.n.nELSE else_result_

33、expressionELSE else_result_expressionENDENDSQLServer高级培训SelectSelect语句语句( (动态排序动态排序) )Select 学生编号,学生名称,所在班级,学生成绩= case when 学生成绩 is null then 暂无成绩！ when 学生成绩 is not null then 有成绩！end from tb_StuXueSheng03SQLServer高级培训SelectSelect语句语句( (动态排序动态排序) )select rq,sum(case when shengfu=胜 then 1 else 0 end)

34、胜,sum(case when shengfu=负then 1 else 0 end) 负from matchgroup by rqSQLServer高级培训SelectSelect语句语句( (操作字符串操作字符串) ) 截取字符串Select stu_id,substring(stu_name,1,4),age from tbl_stuSelect stu_id,left(stu_name,1),age from tbl_stuSelect stu_id,Right(stu_name,1),age from tbl_stu 去除尾部空格Select 学号,rtrim (学号)as 去空格后

35、,姓名,rtrim(姓名)as 去空格后,专业,rtrim(专业)as 去空格后from tb_stu04SQLServer高级培训SelectSelect语句语句( (操作字符串操作字符串) ) 使用”+”连接字符串select 书名,类别,(书名+ 的类别为 + 类别) as 说明from tb_book04SQLServer高级培训SelectSelect语句语句( (操作字符串操作字符串) )使用stuff函数插入字符串Stuff(character_expression,start,length,character_expression)Select 学号,姓名,stuff(学号,2

36、,2,200800) as 新学号from tb_stu04获取字符在字符串中出现的次数Select len(apples is in the apple tree)- len(replace (apples is in the apple tree,p,)/len(p) as afrom tb_name04SQLServer高级培训SelectSelect语句语句( (操作字符串操作字符串) ) 替换字符串使用replace函数将字符串中某一字符替换成另一字符Eg:select replace(dsfsdfsfdsf*fd,d,*)as 替换后字符串大小写转换Uper(character_

37、expression)Lower(character_expression)select upper(firstname)as up_firstname,lower(lastname )as low_lastnamefrom tb_name04SQLServer高级培训Select语句(格式化字符串) 在字符串查询中,可以将字符串格式化为需要的格式,以增加结果集的可读性。 charindex函数Charindex函数用于返回字符串中指定表达式的起始位置语法：Charindex(expression1,expression2,start_location)Eg: select 电话号码 as 提

38、取前, left (电话号码,charindex(-,电话号码)-1)as 区号, substring(电话号码,charindex(-,电话号码)+1, len(电话号码)-charindex(-,电话号码)as 号码 from tb_tel04SQLServer高级培训Select语句(格式化字符串) 用“”代替过长的字符串显示select str1 , case when len(str1)10 then left(str1,10)+. else str1 end as news_strfrom tb_tb04 求07/08级各有多少学生?select sum(case when lef

39、t(stu_id,2)=07 then 1 else 0 end) 07级人数, sum(case when left(stu_id,2)=08 then 1 else 0 end) 08级人数from tbl_stuSQLServer高级培训Select语句(聚合函数) SUM函数(汇总)SELECT SUM(数量) AS 总数量,SUM(单价) AS 总金额 FROM tb_xsb07 AVG函数(平均)SELECT AVG(语文) AS 语文 ,AVG(代数) AS 代数 ,AVG(几何) AS 几何 ,AVG(英语) AS 英语 FROM tb_stuAchievement07 MAX

40、函数(最大值)SELECT MAX(销售额) AS 最高销售额 FROM tb_seller07 MIN函数(最小值)SELECT MIN(销售额) AS 最低销售额 FROM tb_seller07SQLServer高级培训Select语句(聚合函数) COUNT函数(总计记录条数)SELECT COUNT(*) AS 日销售数量 from tb_bookSell07 where 日期=2005-10-6SQLServer高级培训Select语句(分组统计) GROUP BY子句创建简单分组SELECT 商品类别 FROM tb_goods07 GROUP BY 商品类别 GROUP BY子

41、句创建多列组合列SELECT 书名,出版社 FROM tb_bookSell07 GROUP BY 书名,出版社 GROUP BY对统计结果进行排序SELECT 学生编号,学生姓名,sum(高数+外语+计算机文化基础+马经+数据库管理+数据结构+软件工程) AS 总成绩 FROM tb_StuMark07 GROUP BY 学生编号 ,学生姓名ORDER BY 总成绩 DESCSQLServer高级培训Select语句(分组统计) Having子句SELECT 书名,出版社,SUM(金额) AS 总计金额FROM tb_BookSell07GROUP BY 书名,出版社HAVING SUM(金

42、额)500 Having子句与WHERE子句的联合使用Select class_name,count(*) as 人数from tbl_stu,tbl_classwhere tbl_stu.class_id=tbl_class.class_idgroup by class_nameSQLServer高级培训Select语句(分组统计) 在分组查询中使用CUBE运算符cube运算符的主要作用是自动对Group By子句中列出的字段进行分组汇总运算。Select 班级,性别,avg(语文) 语文平均成绩,avg(代数) 代数平均成绩,avg(几何) 几何平均成绩,avg(英语)英语平均成绩 fro

43、m tb_stuAchievement07group by 班级,性别with cubeSQLServer高级培训Select语句(分组统计) 在分组查询中使用ROLLUPselect 班级,性别,avg(语文) 语文平均成绩,avg(代数) 代数平均成绩,avg(几何) 几何平均成绩,avg(英语)英语平均成绩 from tb_stuAchievement07group by 班级,性别with rollupSQLServer高级培训Select语句(分组统计) 使用COMPUTE子句在同一结果集中显示明细和汇总computeAVG|COUNT|MAX|MIN|STDEV|STDEVP |V

44、AR|VARP|SUM (expression),.nBy expression,nselect * from tb_pay07order by 所属部门compute avg(工资)SELECT *FROM tb_pay07 ORDER BY 所属部门COMPUTE SUM(工资) BY 所属部门SQLServer高级培训Select语句(使用子查询) 简单子查询SELECT 姓名,工资,所属部门,(SELECT AVG(工资) FROM tb_apay08) AS 平均工资, (工资-( SELECT AVG(工资) FROM tb_apay08) AS 与平均工资的差额FROM tb_a

45、pay08 为子查询命名SELECT * FROM (SELECT 挂账单位,SUM(欠款金额) AS 金额 FROM tb_agzmx08 GROUP BY 挂账单位)tb1ORDER BY 金额 ASCSQLServer高级培训Select语句(使用子查询) 在子查询中使用聚合函数SELECT 姓名,工资,所属部门FROM tb_apay08WHERE 工资 ( SELECT AVG(工资) FROM tb_apay08 ) 使用子查询更新数据UPDATE tb_aMesell08 SET 药品名称=润洁WHERE 药品名称 IN (SELECT 药品名称 FROM tb_aMesell0

46、8 WHERE 药品编号=YB004)SQLServer高级培训Select语句(使用子查询) 使用子查询删除数据DELETE tb_aMesell08WHERE 药品名称 IN ( SELECT 药品名称 FROM tb_aMesell08 WHERE 药品编号=YB001)SQLServer高级培训Select语句(使用子查询) 在嵌套中使用EXISTS谓词SELECT 学生姓名,所在学院,家庭住址FROM tb_aStuInfo08 IWHERE EXISTS ( SELECT 学生姓名 FROM tb_aStuMark08 M WHERE M.学生姓名=I.学生姓名 AND 外语 80

47、)select 学生姓名,所在学院,家庭住址 from tb_aStuInfo08where 学生编号 in( select 学生编号 from tb_aStuMark08 where 外语80)在一些情况下,只要子查询返回一个真值和假值.只考虑是否满足谓词条件,数据内容本身并不重要.SQLServer高级培训Select语句(日期查询) 使用between and 查询某一时间段内的数据select * from tb_student06where 入校时间 between 01.09.01 and 01.09.05 使用关系运算符查询某一时间段内的数据select 姓名,入校时间 from

48、 tb_student06where 入校时间 01.09.01 and 入校时间 01.09.05 使用in运算符查询几个时间的数据SELECT 书名,出版日期 FROM tb_booksell06 where 出版日期 in(2005-1-12,2005-1-25)SQLServer高级培训Select语句(日期查询) 查询某一月份的数据要实现按月查询数据可以使用日期函数month(). 语法：month(date)select 书名,出版日期 from tb_bookSell06where month(出版日期)=1 order by 出版日期查询今天是周几select datena

49、me(dw,getdate() as 今天是周几Datename(interval,date)SQLServer高级培训Select语句(日期查询) datename()函数中的intervalyyyy:返回指定时间的年份Q:返回指定时间的季度M:返回指定时间的月份D:返回指定时间是本月的第几天DY:返回指定时间是本年的第几天W:返回指定日期是本周的周几Ww:返回指定日期是一年中的第几周select datename(yyyy,getdate() as 年份select datename(m,getdate() as 月份select datename(q,getdate()as 季度sele

50、ct datename(w,getdate() as 今天是周几select datename(ww,getdate() as 第几周select datename(d,getdate() as 本月的第几天select datename(dy,getdate() as 本年的第几天SQLServer高级培训Select语句(日期查询) 查询两个日期之间的天数Datediff函数用于返回跨两个指定日期的日期与时间边界数其语法格式如下:Datediff(datepart,startdate,enddate)yyyy:返回年份差mm:返回月份差D:返回日差select datediff(yyyy,

51、2007-10-1,2008-10-1) as 年份差select datediff(mm,2007-10-1,2008-10-1) as 月份差select datediff(d,2007-10-1,2007-12-1) as 日差SQLServer高级培训Select语句(日期查询) 查询3个月后的日期Dateadd函数用于在向指定日期加上一段时间的基础上,返回新的datetime值.语法格式如下:Dateadd(datepart,number,date)select dateadd(mm,3,getdate() as DAY NUMBER 获取某月的第一天select dateADD(m

52、m,datediff(mm,0,2007-12-20),0)as DAY_NUMBERSQLServer高级培训SelectSelect语句语句复制表结构Select * into destion_table from source_table where 11 批量插入数据Insert into table1 (field1,field2,field3 ) select field1,field2,field3from table2SQLServer高级培训多表查询笛卡尔乘积查询笛卡尔乘积查询实际上就是两表之间的交叉联接实现查询的方式。SELECT tb_userInfo09.*,tb_

53、profession09.* FROM tb_userInfo09 CROSS JOIN tb_profession09 在多表联接中设置联接条件SELECT 学生姓名,学生性别,学生年龄,课程名称,老师姓名 FROM tb_student09,tb_course09,tb_teacher09WHERE tb_student09.自动编号=tb_course09.编号 and tb_course09.自动编号=tb_teacher09.编号SQLServer高级培训多表查询使用表别名SELECT 学生姓名,学生性别,学生年龄,课程名称,老师姓名 FROM tb_student09 AS a,

54、tb_course09 AS b,tb_teacher09 AS cWHERE a.自动编号=b.编号 and b.自动编号=c.编号在多表联接中返回某个表的所有列名SELECT tb_student09.*,课程名称FROM tb_student09,tb_course09WHERE tb_student09.自动编号=tb_course09.编号SQLServer高级培训多表查询(内联接) 等值联接等值联接是指在联接条件中使用等号运算符比较被联接列的值,在查询结果中列出本联接表中的所有列,包括其中的重复列.语法：Select fieldlistFrom table1INNER JOIN

55、table2On table1.column=table2.columnSELECT tb_course09.课程名称,tb_teacher09.老师姓名FROM tb_course09 INNER JOIN tb_teacher09 ON tb_course09.自动编号=tb_teacher09.编号SQLServer高级培训多表查询(内联接) 不等联接不等联接是指在联接条件中使用等于运算符以外的其它比较运算符比较被联接的值SELECT tb_course09.自动编号,tb_course09.课程名称,tb_teacher09.编号,tb_teacher09.老师姓名FROM tb_co

56、urse09 INNER JOIN tb_teacher09 ON tb_course09.自动编号tb_teacher09.编号SQLServer高级培训多表查询(内联接) 自然联接自然连接是一种特殊的联接，它只在两个表有相同名称的列且列的含义相似时才能使用，并将在同名列上进行相等联接自然联接是指在联接条件中使用等号运算符比较被联接列的值，但它使用选择列表指出查询结果集合中所包括的列，并删除联接表中重复列SELECT a.*,b.实发合计 FROM tb_mMRem09 AS a INNER JOIN tb_nMRpay09 AS b on a.人员编号=b.人员编号WHERE b.工资月份

57、=3Select a.*,b.实发合计 from tb_mMRem09 a, tb_nMRpay09 b where a.人员编号=b.人员编号 and b.工资月份=3SQLServer高级培训多表查询(外联接) 左外联接(left outer join)SELECT a.部门编号,a.部门名称,a.负责人, b.人员编号,b.人员姓名,b.部门名称, b.学历,b.技术职称FROM tb_nMRde09 a LEFT OUTER JOIN tb_mMRem09 b ON a.部门名称=b.部门名称SQLServer高级培训多表查询(外联接) 右外联接(right outer join)SE

58、LECT *FROM tb_nMRde09 a RIGHT OUTER JOIN tb_nMRpay09 b ON a.部门名称=b.部门名称WHERE b.工资月份=10SQLServer高级培训多表查询(外联接) 完全联接(full join)SELECT *FROM tb_student09 a FULL JOIN tb_course09 b ON a.自动编号=b.编号 SQLServer高级培训多表查询(自联接)SELECT a.员工姓名,a.基本工资,b.员工姓名,b.基本工资FROM tb_pay09 a INNER JOIN tb_pay09 b ON a.基本工资b.基本工S

59、QLServer高级培训多表查询(静态交叉表)SELECT 员工姓名, SUM( CASE 所在部门 WHEN 食品部 THEN 销售业绩 ELSE NULL END) AS 食品部业绩, SUM( CASE 所在部门 WHEN 家电部 THEN 销售业绩 ELSE NULL END) AS 家电部业绩FROM tb_sell09 GROUP BY 员工姓名SQLServer高级培训多表查询(组合查询) 使用UNION运算符组合两个表的数据SELECT 学生编号 AS 编号 , 学生姓名 AS 姓名FROM tb_StuMark07 WHERE 高数85UNIONSELECT 编号 ,姓名 F

60、ROM tb_stuScore09 WHERE 高数85 使用UNION ALL保留重复行SELECT * FROM tb_nemployee09 UNION ALL SELECT * FROM tb_nemployee09SQLServer高级培训INSERT语句INSERT语句语法:INSERT INTO VALUES(column value,.,lastcolumn value)SQLServer高级培训INSERT语句插入单行数据INSERT INTO tb_em11 VALUES (WY003,王三,女,1981-01-03,本科,长春理工大学,长春市春城大街) 特定字段数据插入

61、INSERT INTO tb_em11 (员工编号,员工姓名,员工家庭住址) VALUES (WY004,王三,长春市自由大街) NULL值的插入INSERT INTO tb_em11 VALUES (NULL,NULL,NULL,1981-01-04,本科,东北师范大学,NULL)SQLServer高级培训INSERT语句插入日期数据INSERT INTO tb_goods11 VALUES (LZW003,明日数码相机,数码设备,500万像素,50,Getdate() 使用UNION ALL语句批量插入数据INSERT tb_employee11 SELECT WY005,王五,男,19

62、81-04-05,专科,长春工程学院,长春市大经路UNION ALL SELECT WY006,王六,女,1982-04-05,专科,长春中医学院,长春市大马路UNION ALL SELECT WY007,王七,男,1985-04-05,专科,长春工程学院,长春市解放大路SQLServer高级培训INSERT语句使用INSERT SELECT语句批量插入数据INSERT INTO tb_employee11SELECT * FROM tb_em11 创建数据表之后插入数据CREATE TABLE tb_sell11(编号 CHAR(20),产品名称 CHAR(50),销售单价 MONEY)G

63、OINSERT INTO tb_sell11 VALUES (XS001,办公用品,60)SQLServer高级培训UPDATE语句UPDATESET=.,=WHERESQLServer高级培训UPDATE语句 UPDATE语句更新列值UPDATE tb_Student12SET 学号=LEFT(学号,2)+1+RIGHT(学号,4) WHERE 专业名=计算机利用子查询更新多行的值UPDATE tb_Grade12SET 成绩=成绩+10WHERE学号 IN (SELECT 学号 FROM tb_Student12 WHERE 专业名=计算机)AND 课程号=01SQLServer高级培训

64、UPDATE语句使用CASE表达式更新列值 update tb_case12 set 消费水平 = case when 商品价格2000 then 昂贵 when (商品价格=800) and (商品价格=2000) then 一般 end 只更新存在于其它表中的数据update tb_update12 set 浮动工资=200 where 职工姓名 in (select 姓名 from tb_personnel12)SQLServer高级培训DELETE语句DELETE FROM WHERESQLServer高级培训DELETE语句删除单条数据DELETE FROM tb_Student

65、12 where 姓名=孙秀斌删除多行数据DELETE FROM TB_GRADE12 WHERE 课程号=01 删除所有数据DELETE FROM tb_Student12 删除部分重复的行Delete from tb_deleteAll12 where 编号 not in(SELECT MAX(编号) AS 编号 FROM tb_deleteAll12 GROUP BY 姓名)SQLServer高级培训DELETE语句使用子查询删除数据DELETE FROM tb_Test12 WHERE 学号 IN ( SELECT 学号 FROM tb_Grade12 WHERE tb_Test1

66、2.学号=tb_Grade12.学号 AND tb_Test12.课程号=tb_Grade12.课程号 )SQLServer高级培训使用视图基本知识点视图是一种常见的数据库对象,可以把它看成是虚拟表或者存储在数据库中的查询,它为查看和存取数据提供了另一种途径。视图的主要用途是:限制用户访问敏感的数据帮助用户执行复杂的SQL查询创建视图语法格式:Create view view_name(column,n)with encryptionAs Select_statementwith check optionSQLServer高级培训使用视图使用视图过滤不想要的数据Use db_sqlgocr

67、eate view v_wareas select 编号,商品名称,售价from tb_ware14goselect * from v_ware 使用视图与计算数据Use db_sqlgocreate view v_ware2(商品名称, 利润)as select 商品名称,(售价-进价)as 利润from tb_ware14goselect * from v_ware2 SQLServer高级培训使用视图使用视图简化复杂的连接use db_sqlgocreate view v_employeeas select tb_employee14.员工编号,tb_employee14.员工姓名,t

68、b_laborage14.薪资编号,tb_laborage14.基本工资,tb_job14.请假天数from tb_employee14 left join tb_job14on tb_employee14.员工编号 = tb_job14.员工编号left join tb_laborage14on tb_laborage14.员工编号 = tb_job14.员工编号go select * from v_employeeSQLServer高级培训使用视图修改视图语法格式:ALTER VIEW view_name(column,.n)with encryptionAs Select_statem

69、entwith check optionSQLServer高级培训使用视图use db_sqlgocreate view v_employeeasselect tb_employee14.员工编号员工编号,tb_employee14.员工姓名员工姓名,tb_laborage14.基本工资基本工资,tb_job14.请假天数请假天数from tb_employee14 left join tb_job14on tb_employee14.员工编号员工编号 = tb_job14.员工编号员工编号left join tb_laborage14on tb_laborage14.员工编号员工编号 = t

70、b_job14.员工编号员工编号goalter view v_employeeasselect tb_employee14.员工编号员工编号,tb_employee14.员工姓名员工姓名,tb_laborage14.基本工资基本工资,tb_job14.请假天数请假天数from tb_employee14 left join tb_job14on tb_employee14.员工编号员工编号 = tb_job14.员工编号员工编号left join tb_laborage14on tb_laborage14.员工编号员工编号 = tb_job14.员工编号员工编号where tb_laborag

71、e14.基本工资基本工资 1000SQLServer高级培训使用视图通过视图添加数据use db_sqlgoinsert into v_ware(商品名称,售价) values (笔记本,9856) 通过视图修改数据use db_sqlgoupdate v_ware set 售价 = 8644 where 编号= 6 通过视图删除数据use db_sqldelete v_ware2 where 商品名称 = 笔记本SQLServer高级培训使用视图删除视图use db_sqldrop view v_wareSQLServer高级培训存储过程基本知识点存储过程是预编译SQL语句的集合,这些

72、语句存储在一个名称下并作为一个单元来处理.存储过程代替了传统的逐条执行SQL语句的方式一个存储过程中可以包含查询、插入、删除、更新等操作的一系列SQL语句，当这个存储过程被调用执行时，这些操作也会同时执行。SQLServer高级培训存储过程使用create procedure语句创建存储过程Create procedure proc_person15As Select 人员姓名,英文姓名,家庭住址From tb_person15goSQLServer高级培训存储过程创建带参数的存储过程create procedure proc2personid int,personname varchar

73、(20)asselect * from tb_person15where 人员编号=personid and人员姓名=personnamego proc2 3100423,尚思go SQLServer高级培训存储过程CREATE PROCEDURE insertQuestion (user_name varchar(50),project_name varchar(50),qtype_id tinyint,prior_level tinyint,error_step varchar(100),machine varchar(50),title varchar(50),description t

74、ext, varchar(60),due_time varchar(10)asinsert into tbl_question (user_name,project_name,qtype_id,prior_level,error_step,machine,title,description,) values (user_name,project_name,qtype_id,prior_level,error_step,machine,title,description,)declare id intset id=identityinsert into tbl_status (question_

75、id,submit_user,status) values(id,user_name,提交)GOSQLServer高级培训存储过程删除存储过程use db_SqlGO-判断存储过程pro_prc15是否存在，如果存在将它删掉if exists(select name from sysobjects where name=pro_prc15and type=p)drop proc pro_prc15 -删除存储过程goSQLServer高级培训存储过程重新命名存储过程if exists(select name from sysobjects where name=pro_person15 a

76、nd type=p)drop proc pro_per15 gouse db_SQLgocreate procedure pro_Ren15asselect * from tb_Ren15 where 家庭住址=吉林省goexec sp_rename pro_Ren15,pro_pRen15goSQLServer高级培训函数(标量函数) 创建标量函数创建标量函数创建标量函数创建标量函数标量函数是指函数体包含一条或多条标量函数是指函数体包含一条或多条标量函数是指函数体包含一条或多条标量函数是指函数体包含一条或多条SQLSQL语句，这些语句以语句，这些语句以语句，这些语句以语句，这些语句以beg

77、inbegin开始，并以开始，并以开始，并以开始，并以endend结束。创建用户自定义函数用结束。创建用户自定义函数用结束。创建用户自定义函数用结束。创建用户自定义函数用create functioncreate function语语语语句，而创建标量函数需要句，而创建标量函数需要句，而创建标量函数需要句，而创建标量函数需要create functioncreate function与与与与beginendbeginend一起一起一起一起配合使用。配合使用。配合使用。配合使用。创建标量函数语法创建标量函数语法创建标量函数语法创建标量函数语法 create function create fu

78、nction 名称名称名称名称 ( (参数名称参数名称参数名称参数名称参数类型参数类型参数类型参数类型=默认值默认值默认值默认值,n),n) returns returns 返回值类型返回值类型返回值类型返回值类型 with encryption with encryption as as begin begin 函数体函数体函数体函数体 return return 函数返回值函数返回值函数返回值函数返回值 end endSQLServer高级培训函数(标量函数)Eg:create function valid_id(stu_id char(7)returns bitas begin dec

79、lare returnvalue bit declare count int set count=(select count(*) from tbl_stu where stu_id=stu_id) if count0 set returnvalue=1 else set returnvalue=0 return returnvalueend SQLServer高级培训函数( 标量函数)Eg:create function dept_name(name varchar(10)returns varchar(15)as begin declare dept_name varchar(15) se

80、t dept_name=(select 所属部门 from tb_apay08 where 姓名=name) return dept_nameend SQLServer高级培训函数( 标量函数)Eg:create function show_price(goods_name varchar(20)returns floatas begin declare price float set price=(select 进价 from tb_ware14 where 商品名称=goods_name) return priceendSQLServer高级培训函数( 标量函数)Eg:create fun

81、ction goods_time(id int)returns datetimeas begin declare goods_time datetime set goods_time=(select 进货日期 from tb_ware14 where 编号=id) return goods_timeendSQLServer高级培训函数( 单语句表值型函数) 单语句表值函数又称内联表值函数，这类型函数以表的形式返回一个值单语句表值函数又称内联表值函数，这类型函数以表的形式返回一个值单语句表值函数又称内联表值函数，这类型函数以表的形式返回一个值单语句表值函数又称内联表值函数，这类型函数以表的形式返

82、回一个值, ,相当于一个参数化的视图。相当于一个参数化的视图。相当于一个参数化的视图。相当于一个参数化的视图。创建单语句表值型函数语法创建单语句表值型函数语法create function create function 名称名称(参数名称参数名称参数类型参数类型=默认值默认值,n),n)returns tablereturns tablewith encryptionwith encryptionasasreturn return （selectselect语句）语句）SQLServer高级培训函数( 单语句表值函数)Eg:create function show_stu(tick cha

83、r(2)returns tableasreturn (select * from tbl_stu where tick=tick)SQLServer高级培训函数(多语句表值型函数)多语句表值型函数是标量函数和单语句函数的结合体，该函数返回的是一个表，多语句表值型函数是标量函数和单语句函数的结合体，该函数返回的是一个表，可以进行多次查询。可以进行多次查询。create function create function 名称名称(参数名称参数名称参数类型参数类型=默认值默认值,n),n)returns returns 局部变量局部变量tabletablewith encryptionwith e

84、ncryptionasasbeginbegin函数体函数体return return 函数返回值函数返回值endendSQLServer高级培训函数(多语句表值型函数)create function show_ginfo(id int)returns returntable table(商品名称 varchar(20),售价 float,备注 varchar(20)as begin declare goods_name varchar(20),price float declare i int set i=(select count(*) from tb_ware14 where 编号=id)

85、 if i0 insert into returntable(商品名称,售价) select 商品名称,售价 from tb_ware14 where 编号=id else insert into returntable(备注) values(没有该商品信息) returnend SQLServer高级培训游标游标提供一种从表中检索数据并进行操作的灵活手段,游标主要用在服务器上,处理由客户端发给服务器的SQL 语句,或是批处理、存储过程、触发器中的数据处理请求一个完整的游标由5部分组成,并且这5个部分应符合下面的顺序:(1)声明游标(2)打开游标(3)从一个游标中查找信息(4)关闭游标(5)释

86、放游标语法:Declare cursor_nameinsensitive|scroll cursorFor select_statementForRead only|updateof column name,.nSQLServer高级培训游标(定义游标)Eg:declare cur_ware cursor for -声明游标select 商品名称,进货日期,进价,售价 from tb_ware14open cur_ware -打开游标declare curware cursor -创建游标变量set curware=cur_ware -为游标变量赋值 fetch next from curwa

87、re -读取游标中的值勤 close cur_ware -关闭游标SQLServer高级培训游标(读取游标中数据)declare emp_cursor cursorfor select 姓名,性别 from tb_emp06open emp_cursordeclare name varchar(8),sex char(2)fetch next from emp_cursor into name,sexprint 姓名:+name+性别:+sexfetch next from emp_cursor into name,sexprint 姓名:+name+性别:+sexClose emp_curs

89、ct 编号,姓名,性别,所属部门From tb_employeeOpen readcursorFetch next from readcursorWhile fetch_status=0 begin fetch next from readcursor endSQLServer高级培训游标(关闭/释放)语法:Close Global cursor_name|cursor_variable_nameDeallocate Global cursor_name|cursor_variable_nameSQLServer高级培训使用触发器触发器是一种特殊类型的存储过程,是为响应数据操作语言事件或数据

90、定义语言事件而执行的存储过程.当用户对表进行相应操作时,触发器将自动执行.触发器可以基于表创建,也可以基于视图创建. 触发器是数据库独立的对象.当一个事件发生时,触发器自动隐式运行.但触发器不能接收参数.SQL支持3种类型的触发器:Insert、Update、Delete.当向表插入数据、更新数据、删除数据时，触发器就被调用。创建触发器的语法:Create trigger trigger_nameOn table|viewwith encryption for|after|instead ofinsert,update,delete not for replication as if upda

91、te(column) and|orupdate(column) .n Sql_statement.nSQLServer高级培训使用触发器触发器的优点触发器是自动的:它们在对表的数据作了任何修改(比如手工输入或者应用程序采取的操作)之后立即被激活。触发器可以通过数据库中的相关表进行层叠更改。触发器可以强制限制，这些限制比用 CHECK 约束所定义的更复杂。 SQLServer高级培训使用触发器创建Insert触发器use db_sqlgocreate trigger tri_employeeon tb_employee17after insertasprint(欢迎加入本公司！)goin

92、sert into tb_employee17 values (08008,鲍艳,男,开发部,070716)SQLServer高级培训使用触发器创建Update触发器use db_sqlgocreate trigger tri_employeeon tb_employee17after insertasprint(欢迎加入本公司！)goinsert into tb_employee17 values (08008,鲍艳,男,开发部,070716)SQLServer高级培训使用触发器创建Delete触发器create trigger tri_dele_laborageon tb_labora

93、ge17with encryption after deleteasprint(数据删除成功)godelete from tb_laborage17 where 编号 = 5SQLServer高级培训删除触发器使用触发器drop trigger tri_laborageSQLServer高级培训索引索引在对大量数据进行查询时,可以应用到索引技术.索引是一种特殊类型的数数据库对象,它保存着数据表中一列或多列的排序结果,有效地使用索引可以提高数据的查询效率。语法:Create unique|clustered|nonclustered index index_nameOn table|view

94、(column|ASC|DESC,n)on 参数说明:uniqueclustered|nonclustered:指定创建索引的类型，参数依次为唯一索引、聚集索引和非聚集索引。当省略unique选项时，创建非唯一索引，省略nonclustered选项时，建立唯一聚集索引。Index_name:索引名。索引名在表或视图中必须唯一，但在数据库中不必唯一,索引名必须遵守标识符规则使用索引的目的如下:(1)强调唯一:在pk列或unique列上自动创建唯一索引(2)提高查询速度:当查询使用索引时,速度会大幅度的提高SQLServer高级培训索引主键主键主键主键聚集索引聚集索引聚集索引聚集索引用途用途

95、强制表的实体完整性强制表的实体完整性对数据行的排序，方便查对数据行的排序，方便查询用询用一个表多少个一个表多少个一个表最多一个主键一个表最多一个主键一个表最多一个聚集索引一个表最多一个聚集索引是否允许多个是否允许多个字段来定义字段来定义一个主键可以多个字段来定义一个主键可以多个字段来定义一个索引可以多个字段来一个索引可以多个字段来定义定义是否允许是否允许 nullnull 数据行出现数据行出现如果要创建的数据列中数据存在如果要创建的数据列中数据存在nullnull，无法建立主键。，无法建立主键。没有限制建立聚集索引的没有限制建立聚集索引的列一定必须列一定必须 not nullno

96、t null 主键vs 聚集索引SQLServer高级培训索引使用unique关键字创建唯一索引唯一索引(unique)是不允许在两行中创建相同的索引值,unique索引可以拥有一行或者多行.Create unique index Ind_user20On tb_user20(用户编号)创建简单的非簇索引Create index ind_score20 on tb_score20(学生编号)使用clustered 关键字创建簇索引簇索引改变了表中数据存放的物理位置。在带有簇索引的表中，行是以索引顺序存放的，即簇索引中的键字值进行排序。语法:Create clustered index ind_

97、nameOn table_nameWith ignore_dup_keySQLServer高级培训索引删除索引语法:Drop index table.index|view.index,.nSQLServer高级培训事务处理事务是由一系列语句构成的逻辑单元。事务和存储过程等批处理有一定的相似之处，通常都是为了完成一定业务逻辑而将一条或多条语句“封装”起来，使它们与其它语句之间出现一个逻辑上的边界，并形成相对独立的一个工作单元。SQL Server通过事务控制语句,把SQL Server语句集合分组后,形成独立的逻辑工作单元。1.事务处理控制语句BEGIN TRANSACTIONCOMMIT TR

98、ANSACTIONROLLBACKUP TRANSACTION在SQL Server中，对事务的管理包含3个方面:事务控制语句。使用户或者程序员能把一系列Transact-SQL语句作为逻辑上的一个语句锁机制(locking)。封锁正被一个事务修改的数据，防止其它用户访问到“不一致”的数据事务日志(Transaction Log)。使事务具有可恢复性。SQLServer高级培训事务处理begin transaction -开始 update tb_apay08 set 工资=工资*1.05 where 所属部门=文档部 update tb_apay08 set 工资=工资*1.1 where

99、所属部门=设计部 update tb_apay08 set 工资=工资*1.08 where 所属部门=ASP部commit transaction -提交SQLServer高级培训事务处理事务的隐式提交事务的隐式提交在给出begin transaction语名之后，即显式方式，sql server服务器能依次完成所有的sql语句，在任何时间，也可以退出整个事务处理，或者提交该事务处理。而sql server的隐式提交，即没有明确给出begin transaction语句，则sqlServer是按照每个sql语句都是一个事务进行执行./*隐含的begin transaction */upd

100、ate tb_apay08 set 工资=工资*1.05 where 所属部门=文档部/*隐含的commit transaction*/*隐含的begin transaction */ update tb_apay08 set 工资=工资*1.1 where 所属部门=设计部/*隐含的commit transaction*/*隐含的begin transaction */ update tb_apay08 set 工资=工资*1.08 where 所属部门=ASP部/*隐含的commit transaction*/SQLServer高级培训事务处理回滚事务回滚事务begin transacti

101、on update_data update tb_customers03 set 邮编=100006 where 顾客编号=1000rollback transaction update_data -回滚事务 select * from tb_customers03SQLServer高级培训事务处理锁锁锁是数据库中的一个非常重要的概念，它主要用于多用户环境下保证数据库完整性和一致性。我们知道，多个用户能够同时操纵同一个数据库中的数据，会发生数据不一致现象。即如果没有锁定且多个用户同时访问一个数据库，则当他们的事务同时使用相同的数据时可能会发生问题。这些问题包括:丢失更新、脏读、不可重复读和幻

102、觉读。1.当两个或多个事务选择同一行，然后基于最初选定的值更新该行时，会发生丢失更新问题。每个事务都不知道其它事务的存在。最后的更新将重写由其它事务所做的更新，这将导致数据丢失。 2. 脏读就是指当一个事务正在访问数据，并且对数据进行了修改，而这种修改还没有提交到数据库中，这时，另外一个事务也访问这个数据，然后使用了这个数据。因为这个数据是还没有提交的数据，那么另外一个事务读到的这个数据是脏数据，依据脏数据所做的操作可能是不正确的。 3.不可重复读是指在一个事务内，多次读同一数据。在这个事务还没有结束时，另外一个事务也访问该同一数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改，

103、那么第一个事务两次读到的的数据可能是不一样的。这样就发生了在一个事务内两次读到的数据是不一样的，因此称为是不可重复读。 SQLServer高级培训事务处理4.幻觉读是指当事务不是独立执行时发生的一种现象，例如第一个事务对一个表中的数据进行了修改，这种修改涉及到表中的全部数据行。同时，第二个事务也修改这个表中的数据，这种修改是向表中插入一行新数据。那么，以后就会发生操作第一个事务的用户发现表中还有没有修改的数据行，就好象发生了幻觉一样。 SQLServer高级培训事务处理封锁封锁封锁是数据库中的一个重要技术。在事务执行期间，比如进行更新操作时，数据库可能出现暂时的不一致，利用封锁技术就能够有效地

104、防止其他事务读不一致性的数据。其它事务必须等到此事务解锁(unlock)之后才能访问该数据。可封锁的对象:字段、记录、表和数据库等SQL Server封锁的方式有3种1.共享锁(Share lock)2.排它锁(Exclusive lock)3.更新锁(Update lock)SQLServer高级培训事务处理死锁死锁当两个事务Trans1和Trans2在下列状态时，将产生死锁Trans1:存取数据项X和YTrans2:存取数据项Y和X 如果事务Trans1封锁了数据项X,事务Trans2封锁了数据项Y,则Trans1等待Trans2释放Y上的锁,Trans2等待Trans1释放X上的锁。因此

105、，Trans1和Trans2都无限地等待对方打开锁住的数据项，即死锁发生。为尽可能避免死锁的发生，用户应该遵循以下原则:在所有的事务中都按同一顺序来访问各个表尽可能利用存储过程来完成一个事务。事务应该尽量小且应尽快提交尽量避免人工输入操作出现在事务中SQLServer高级培训数据库安全与维护(分离/附加数据) 分离数据库Sp_detach_dbdbname=dbname,skipchecks=skipchecks返回代码值：0( 成功)或1(失败) 附加数据库Sp_attach_dbdbname=dbname,.16返回代码值：0( 成功)或1(失败)sp_attach_db dbname=d

106、b_mrsoftfdw,=D:Datadb_mrsoftfdw_Data.MDF,=D:Datadb_mrsoftfdw_Log.LDFSQLServer高级培训数据库安全与维护(导入/导出数据) 将SQL SERVER数据库导出到Excel数据表中将SQL SERVER数据库导出到Access数据表中将SQL SERVER数据库导出到文本文件中将Excel数据导到SQL SERVER数据库中将Access数据导到SQL SERVER数据库中将文本文件中的数据导到SQL SERVER数据库中SQLServer高级培训SQL Server的安全性设置安全级别在SQL Server 2

107、000中工作时,用户要经过两个安全性阶段:身份验证(安全用户认证)和授权(访问许可确认)。身份验证模式:windows身份验证模式、混合模式配置windows认证模式配置混合认证模式SQLServer高级培训SQL Server的安全性数据库用户简介数据库用户用来指出哪一个人可以访问哪一个数据库。在一个数据库中，用户ID 惟一标识一个用户，用户对数据的访问权限以及对数据库对象的所有关系都是通过用户账号来控制的，用户账号总是基于数据库的，即两个不同数据库中可以有两个相同的用户账号。同一登录ID可以与不同数据库的同一个用户ID(或者不同的用户ID)关联,但同一登录ID不能与同一数据库的多个

108、用户ID关联.同一个数据库中,一个登录ID只能关联一个用户ID。在数据库中，用户账号与登录账号是两个不同的概念。一个合法的登录账号只表明该账号通过了NT 认证或SQL Server 认证，但不能表明其可以对数据库数据和数据对象进行某种或某些操作，所以一个登录账号总是与一个或多个数据库用户账号（这些账号必须分别存在相异的数据库中）相对应，这样才可以访问数据库。例如，登录账号sa 自动与每一个数据库用户dbo 相关联。通常而言，数据库用户账号总是与某一登录账号相关联，但有一个例外，那就是guest用户SQLServer高级培训SQL Server的安全性创建登录用户使用sp_addlogin创建

109、登录用户语法:exec sp_addlogin 登录用户,登录密码,默认数据库,默认语言Eg: exec sp_addlogin loginame=mr passwd=111 使用sp_password修改登录用户密码语法:sp_passwordold=old_password,new=new_password,loginame=loginEg: exec sp_password old=111,new=11,loginame=mr 使用sp_helplogins查看用户登录信息SQLServer高级培训SQL Server的安全性创建数据库用户使用sp_adduser为当前数据库中的新用户

110、添加安全账户语法:Sp_adduser loginame= login ,name_in_db=user ,grpname=group 参数 loginame = login 用户的登录名称。没有默认值。login 必须是现有 SQL Server登录或 Microsoft Windows NT用户。 name_in_db = user 新用户的名称。其默认值为 NULL。如果没有指定 user，则用户的名称默认为 login 名称。指定 user 即为新用户在数据库中给予一个不同于 SQL Server 上的登录ID 的名称。 grpname = group 组或角色，新用户自动地成为其成

111、员。默认值为 NULL。group 必须是当前数据库中有效的组或角色。SQLServer高级培训SQL Server的安全性角色管理当几个用户共同完成一个任务时，管理员可以将他们集中到一个称为“角色”的单元中。角色是数据库访问许可的管理单位，其成员继承角色所拥有的访问许可。 SQL Server 2000可以将争色分为固定服务器角色、固定数据库角色、用户自定义数据库角色。固定服务器角色(作用域在服务器范围内它们存在于数据库之外) 固定服务器角色是SQL Server在安装时就创建好的用于分配服务器及管理权限的实体。 exec sp_helpsrvrole -浏览固定服务器角色SQLSe

112、rver高级培训SQL Server的安全性固定数据库角色(在数据库级别定义，并在每个数据库中都存在) 固定数据库角色是在每个数据库中都创建的预定义组。exec sp_helpdbfixedrole -浏览固定数据库角色固定数据库角色固定数据库角色角色名称角色名称描述描述db_ownerdb_owner在数据库中有全部权限在数据库中有全部权限Db_accessadminDb_accessadmin可以添加或删除用户可以添加或删除用户IDIDDb_securityadminDb_securityadmin可以管理全部权限、对象所有权、角色和角色成员资格可以管理全部权限、对象所有权、角色和角色成员

113、资格Db_ddladminDb_ddladmin可以发出可以发出ALL DDLALL DDLDb_backupoperatorDb_backupoperator可以发出可以发出DBCCDBCC、CHECKPOINTCHECKPOINT和和BACKUPBACKUP语句语句Db_datareaderDb_datareader可以选择数据库内任何用户表中的所有数据可以选择数据库内任何用户表中的所有数据Db_datawriterDb_datawriter可以更改数据库内任何用户表中的所有数据可以更改数据库内任何用户表中的所有数据db_denydatareaderdb_denydatareader不能选

114、择数据库内任何用户表中的任何数据不能选择数据库内任何用户表中的任何数据Db_denydatawriterDb_denydatawriter不能更改数据库内任何用户表中的任何数据不能更改数据库内任何用户表中的任何数据SQLServer高级培训SQL Server的安全性用户自定义数据库角色用户的需求通常并不总是能够很容易地通过一个固定数据库角色来实现，或者用户可能没有管理windows NT用户和组的权限。所以，需要一个用户自定义数据库角色。用户自定义数据库角色有两种类型：标准角色、应用程序角色。Exec sp_addrole test -用户自定义角色Exec sp_droprole tes

115、t -删除数据库角色使用sp_addrole创建数据库角色语法:sp_addrole rolename= role,oewnername=ownerSQLServer高级培训SQL Server的安全性使用GRANT语句授予角色权限语法:GRANT ALL|statement.nTo security_account.n参数说明:ALL:表示授予所有可用的权限Statement:create database、create default、create function、 create procedure、create rule、create table、create view、 back

116、up database、backup logTo:指定安全账户列表Eg:Grant create table to zwySQLServer高级培训SQL Server的安全性使用REVOKE语句取消角色创建数据表的权限语法:REVOKE ALL|statement,nFrom security_account,.nEg:Revoke create table from zwySQLServer高级培训SQL Server的安全性权限管理对于用户来说,权限是指其登录到SQL Server之后,数据库用户账号决定该用户可以进行的更改数据库定义或访问数据的活动.在SQL Server中包括3种

117、类型的权限,即对象权限、语句权限和暗示权限。1、对象权限:处理数据或执行过程。涉及的操作如下:Select、insert、update和delete语句权限，它们可以应用到整个表或视图中Select 和update语句权限，它们可以有选择性地应用到表或视图中的单个列上Select权限，它们可以应用到用户定义函数Insert和delete语句权限，它们会影响整行，因此只可以应用到表或视图中Execute语句权限：它们可以影响存储过程和函数2、语句权限:创建数据库或数据库中的项目。涉及的操作如下:Create databaseCreate view|procedureCreate rule|def

119、n user_defined_functionTo security_account,.nwith grant optionas group |roleEg:grant select,insert on tb_goods to zwy with grant optionSQLServer高级培训SQL Server的安全性使用Revoke语句取消角色对指定数据表的操作权限语法:Revoke grant option forall privileges|permission,.n (column,.n) on table|view |ontable|view(column,n) |onstor

120、ed_procedure|extended_procedure |on user_defined_function to|from security_account,.n cascade as group|roleEg:Revoke select,insert on tb_goods from zwy cascadeSQLServer高级培训数据库备份与恢复为什么要备份数据库?系统故障事务故障介质故障SQL Server的备份与桌面数据库的差异?SQL Server VS Access、Visual FoxroSQLServer高级培训数据库备份与恢复数据备份方式1、完全数据库备份完全数据

121、库备份是对所有数据库操作和事务日志中的事务进行备份,可以用作系统失败时恢复数据库的基础。2、差异备份差异备份只记录自上次数据库备份后更改的数据。差异备份比数据库完全备份小而且备份速度快，因此，可以更经常地备份，经常备份将减少丢失数据的危险，使用差异备份将数据库还原到其完成时的那一点。3、事务日志备份事务日志备份是对数据库发生的事务进行备份。它可以在相应的数据库基础上，尽可能恢复最新的数据库记录。4、数据库文件和文件组备分可以选择对数据库文件或文件组进行备份。在数据库文件和文件组备份中，可以指定需要备份的文件，最多指定16个文件或文件组。SQLServer高级培训数据库备份与恢复恢复模型简单模

122、型使用简单恢复模型可以将数据库恢复到上次备份的即时点。不过，无法将数据库还原到故障点或特定的即时点。完全恢复完全恢复模型提供最大的灵活性，可将数据库恢复到故障点状态。完全恢复模型使用数据库备份和事务日志提供对媒体故障的完全防范。大容量日志记录恢复模型大容量日志记录恢复模型允许大容量日志记录操作。大容量日志记录恢复模型提供对媒体故障的防范，并对某些大规模或大容量复制操作提供最佳性能和最少日志使用空间。SQLServer高级培训数据库备份与恢复创建备份设备创建备份时，必须先创建或选择存放备份数据的备份设备。备份设备是用来存储数据库、事务日志或文件和文件组备份的存储介质，可以是硬盘、磁盘或其

123、它物理设备等。当使用磁盘时，SQL Server允许将本地主机硬盘和远程主机上的硬盘作为备份设备。磁盘备份设备磁带备份设备物理和逻辑设备创建备份设备的方法“企业管理器”中创建“查询分析器”中创建(sp_addumpdevice语句创建备份设备)Sp_addumpdevice语法:Sp_addumpdevice device_type,logical_name,physical_nameEg: Exec sp_addumpdevice disk,my_device,d:my_device.bakSQLServer高级培训数据库备份与恢复删除备份设备“企业管理”中删除“查询分析器“中删除(sp

124、_dropdevice语句删除设备) Sp_dropdevice语法:sp_dropdevice logicalname=deviceEg:sp_dropdevice my_deviceSQLServer高级培训数据库备份与恢复 SQL Server数据库备份在“企业管理器”中备份在“查询分析器”中备份1、备份整个数据库Backup database database_name|database_name_varTo ,nwith,password=password|password_variable,format|noformat,init|noinit,name=backup_set_na

125、me|backup_set_name_var,noskip|skip,stats=percentageEg:Backup database pubs to disk=c:a.bakbackup database northwind to device1-完全备份Backup database stu to device1 with differential -差异备份SQLServer高级培训数据库备份与恢复2、备份事务日志Backup log database_name|database_name_var to ,.nwith,password=password|password_varia

126、ble,format|noformat,init|noinit,name=backup_set_name|backup_set_name_var,no_truncate,norecovery|standby=undo_,noskip|skip,stats=percentageEg:backup log northwind to mydevice1 with initSQLServer高级培训数据库备份与恢复备份文件和文件组 Backup Database database,.fTo backup_device,.nWITH with_options,.o;Sp_addumpdevice di

127、sk,mrdata,E:mrdatabasemrfdw.bakGoRestore from mrdataGoBackup database db_mrsoftFile=db_mrsoft_data,primaryTo mrdatagoSQLServer高级培训数据库备份与恢复数据库恢复简介Restore headeronly该语句在特定的备份设备上检索所有备份的首部信息.其语法为:Restore headeronlyFrom with,password=password|password_variable:= logical_backup_device_name|logical_backup

129、ysical_backup_device_name|physical_backup_device_name_varEg:Restore from deviceSQLServer高级培训数据库备份与恢复Restore verifyonly该语句验证备份但不还原备份.检查备份集是否完整以及所有卷是否都可读.其语法为:Restore verifyonlyFrom ,.nwith ,loadhistory ,password=password|password_variable:=logical_backup_device_name|logical_backup_device_name_var|dis

130、k|tape= physical_backup_device_name|physical_backup_device_name_varEg:Restore verifyonly from device1SQLServer高级培训数据库备份与恢复数据库恢复使用restore命令恢复数据库Restore Database database_name|database_name_varfrom backup_device.,.nwith ,norecovery|recovery|standby=undo_ ,replace使用restore命令恢复事务日志Restore log database_

131、name|database_name_varfrom ,.nwith ,norecovery|recovery|standby=undo_ ,replaceSQLServer高级培训数据库备份与恢复使用restore命令恢复文件或文件组Restore databasedatabase_name|database_name_varfrom ,.nwith ,norecovery|recovery|standby=undo_ ,replaceEg:Restore database db_mrsoftFile=db_mrsoft_data,primaryFrom mrdatagoSQLServer高

132、级培训附录select tbl_stuAttendance.stu_id,stu_name,count(tbl_stuAttendance.stu_id) as 旷课次数from tbl_stuAttendance,tbl_stu where Attendance_id=3 and tbl_stuAttendance.stu_id=tbl_stu.stu_idgroup by tbl_stuAttendance.stu_id,stu_nameorder by 旷课次数select tbl_stuAttendance.stu_id,stu_name,count(tbl_stuAttendance

133、.stu_id) as 旷课次数,class_name from tbl_stuAttendance,tbl_stu,tbl_class where Attendance_id=3 and tbl_stuAttendance.stu_id=tbl_stu.stu_id and tbl_stu.class_id=tbl_class.class_idgroup by tbl_stuAttendance.stu_id,stu_name,class_nameorder by 旷课次数 descselect stu_id,count(stu_id) as 旷课次数from tbl_stuAttendan

134、ce where attendance_id=3 group by stu_idhaving count(stu_id)5order by 旷课次数SQLServer高级培训附录select class_name,count(stu_id) as 学生人数from tbl_stu,tbl_classwhere tbl_stu.class_id=tbl_class.class_idgroup by class_nameselect stu_id,count(stu_id) as 出现的次数from tbl_stugroup by stu_idhaving count(stu_id)1select

135、 class_day,count(*) as 上课人数from tbl_stu where class_day=w3 and room_id=305group by class_dayselect class_day,count(*) as 上课人数人数from tbl_stu group by class_dayorder by class_daySQLServer高级培训附录select class_day,room_id,count(*) as 上课人数人数from tbl_stu group by class_day,room_idorder by class_day,room_idSQLServer高级培训

展开阅读全文

SQLServer高级培训课件

最新文档