SQL Sever 复习.doc

第 62 页 共 62 页SQL Sever 2005 复习SQL语句一数据查询1.抽取特定属性(1)定制显示及排序(2)通配符(3)抽取来自表顶部的结果(top关键字)(4)抽取不重复的结果(distinct关键字)2.使用函数定制结果集(1)字符串函数(2)日期函数(3)属性函数(4)次序函数(5)系统函数二.汇总和分组数据1. 通过集合函数总结数据(1)avg返回数字表达式的平均值所有的或者不同的(2)count返回表达式中的唯一的数据(3)min返回表达式的最小值(4)sum返回数之表达式的总数2.分组数据(1)group by(2)compute 和compute by3.pivot关键字查询三.使用连接和子查询查询数据1. 使用连接查询数据(1)使用内连接(2)使用外连接(3)使用交叉连接(4)使用等值连接(5)使用自连接2. 使用子查询查询数据(1)使用操作符和in,exists关键字(2)使用修改的比较操作符(3)使用集合函数显示(4)使用嵌入子查询(5)使用相关子查询四.管理数据库和表1.管理数据库(1)系统数据库(2)识别数据库文件(3)创建用户自定义数据库(4)管理用户自定义数据库2.管理表(1)管理表(2)数据完整性(3)创建约束(4)应用规则(5)使用用户定义数据类型五.在表中操纵数据1.使用DML语句操纵数据(1)在表中添加数据(2)从现有表中复制数据到新表(3)更新表(4)从表中删除数据(5)删除表中全部记录2.操纵XML数据(1)在表中存储XML数据(2)从表中抽取XML数据六.实现索引,视图和全文搜索1.创建和管理索引(1)索引介绍(2)创建索引(3)管理索引2.创建和管理视图(1)创建视图(2)通过视图修改数据时的限制(3)索引视图(4)管理视图3.实现全文搜索(1)配置全文搜索(2)使用全文搜索搜索数据七.实现存储过程和函数1.实现批处理(1)创建批处理的指导方针(2)使用结构(3)处理错误和异常2.实现存储过程3.创建带参数的存储过程(1)从存储过程返回值(2)从另一个过程调用一个过程4.实现函数(1)创建UDFs(2)创建标量函数(3)创建表值函数八.使用触发器1.实现和识别触发器(1)DML触发器(2)DDL触发器2.创建触发器(1)创建删除触发器(2)创建更新触发器(3)创建后触发器3.管理触发器(1)改变触发器(2)删除触发器(3)重命名触发器4.实现事务(1)事务属性(2)回滚事务(3)锁(4) SQL Sever中的锁*游标SQL语句数据定义语言(DDL) 用于定义数据库，数据类型，结构和数据上的约束create:用于创建新数据库对象，如表，索引，存储过程及函数alter:用于修改数据库对象drop:删除对象数据库操纵语言(DML) 用于数据库中对象的操作insert:在表中插入新数据update:修改表中现存记录delete:删除表中数据数据控制语言(DCL) 控制数据库中对象的访问grant:对指定的用户授予访问权利revoke:阻止某些用户的访问数据库查询语言(DQL) 从数据库中查询对象select:从数据库中以不同的方式，格式选择数据的命令一 数据查询 (使用AdventureWorks数据库)抽取特定属性定制显示及排序抽取职位是Tool Designer，支付率大于19的雇员编号，以编号升序排列，并以下面的格式显示 雇员编号支付率Select EmployeeID雇员编号，Rate支付率 from HumanResources.Employee where Title=Tool Designer and Rate19 order by EmployeeID asc通配符通配符描述%表示任何字符可是0个或更多_表示单个字符在指定范围内的任何字符不在指定范围内的任一字符SQL Sever 所提供的这些字符可用于关键字从部门表中抽取以Pro开始的名称值记录Select *from HumanResources.Department where Name like Pro%从部门表中抽取部门名称是五个字符长度,并且以Sal开头,而第四个字符在e,o中选择,第五个字符为任何字符Select * from HumanResources.Department where Name like Sale,o_抽取来自表顶部的记录,使用top关键字从员工表中抽取前3个记录,其中HireDate应该大于等于1198,并且小于等于123198,记录应该基于SickleaveHours列以升序显示Select top 3 * from HumanResources.Employee where HireDate =1198 and HireDate row_number 函数SQL查询使用row_number函数显示列的顺序数：select EmployeeID，Rate,row_number（）over(order by Rate dese) as rank from HumanResources.EmployeeHistory显示结果P2.322 rank函数rank函数返回基于指定规则的结果集的每行的顺序：select EmployeeID,Rate,rank() over (order by rate desc) as rank from HumanResources.EmployeePayHistory显示结果P2.333 dense_rank函数此函数用于连续的顺序值需要基于特定的规则被给出的地方，例如你想基于一年内产品的销售排列，若A，B产品销售额相同，两个都指定相同的等级，销售额次序的下一个产品将指定为下一个顺序值Select EmployeeID,Rate,dense_rank() over (order by Rate desc) as rank from HumanResources.EmployeePayHistory显示结果P2.34*这三种函数显示结果的不同之处在于rank列的输出排序使用系统函数函数定义host_id( )返回客户进程的当前主进程ID号host_name( )返回客户进程当前主机的名称db_id(db_name)返回数据库标识号Db_name(db_id返回数据库名称Object_id(objname)返回数据库对象ID号Object_name(obj_id)返回数据库对象名称User_id(name_in_db)返回相应于用户名称的数据库标识号User_name(user_id)返回根据数据标识号的用户名Suser_id(login_name)返回相应于用户登陆名的登陆标识(ID)号Suser_sid（login_name）返回相应于用户登陆名的安全标识号（SID）Suser_sname(server_user_id )返回相当于安全标识号的登陆名称SQL Sever 2005 中也提供转换函数将一个表达式的数据类型转换为另一种数据类型，使用convert函数语法：Convert (datatype (length),expression style)Datatype 只能是系统定义数据类型Length 是可选的char,varchar或二进制数据类型参数Expression 是任何有效的要被转换到的另一种数据类型的表达式Style 是在转换数据类型到字符数据类型的时候表示日期的方法例：显示来自员工表的title和hrie date对于它，需将日期数据的雇用到字符数据类型，然后以yy.mm.dd的格式显示Select Title,convert(char(10),HireDate，2)Hire Date from HumanResources.EmployeeAdventure Works,Inc的管理层想浏览一个报表，它显示employeeID,designation和作为市场经理或市场专员的员工的年龄，数据将以大写显示Select EmployeeID,upper(Title)as Designation,datediff(yy,BirthDate,getdates)as Age from HumanResources.Employee where Title=Marketing Manager or Title=Marketing Specialist二汇总和分组数据数据的汇总包含有助于在广度层次进行数据分析的集合集，SQL Sever 系统提供集合函数来产生汇总数据1通过集合函数总结数据(1)avg返回数字表达式的平均值所有的或者不同的从EmployeePayHistory 表中的Rates列抽取平均值并且用用户自定义的标题Select avg(Rate) Average Rate from HumanResources.EmployeePayHistory(2)count返回表达式中的唯一的数据（或所有的，或者不同的）从EmployeePayHistory 表中使用用户自定义标题抽取唯一比率值：Select count (distinct Rate)unique Rate from HumanResources.EmployeePayHistory Count 函数也接收（*）作为它的参数，但是它计算查询返回的行数（3）min返回表达式的最小值：从EmployeePayHistory 表中使用用户自定义标题抽取最小比率值：Select min(Rate)Minmum Ratefrom HumanResources.EmployeePayHistory (4)sum返回数之表达式的总数，或者所有，或者不同的从EmployeePayHistory表中使用用户自定义的标题抽取所有唯一比率值的总和Select sum（distinct Rate ）sum from HumanResources.EmployeePayHistory2.分组数据在SQL Sever 2005可通过使用group by,compute,compute by,pivot从句分组数据(1)group_by group byhaving从句与 selectwhere从句相同,group by从句收集满足条件的数据,并且汇总它到一个表达式以产生单一的一组值,having从句消除所有不满足条件的这些组查询抽取所有职务和雇员的平均空闲小时,当空闲小时大于30并且分组平均值大于55的时候：select Title，avg（VacatoinHours）Average Vacatoin Hours from HumanResources.Employee where VacationHours30 group by Title having avg(VacatoinHours)55group by中的 all关键字被用于显示所有组,包括那些不包含在where从句中的如果不使用 all ,group by 从句不会显示没满足条件的行的分组,如果使用all,即使在分组中没有满足搜索条件group by也将显示所有行从员工职务中抽取记录,他们被where从句的条件删除:Select Title, VacationHours= sum(VacatoinHours) from HumanResources.Employee where Title in (Recruiter,Stocker,Design Engineer) group by all Title(2)compute 和compute_by Compute从句被用于产生汇总行,compute by从句被用来以数据分组计算结果集中的汇总值,数据被分组的列在by关键字之后被提到Group by从句用来产生分组的汇总报告，并且不产生结果集中的个别表行，而 compute从句被用于控制中断的总结报告程序，它产生结果集中的详细信息Select Title，VacationHoursAverage VacationHours，SickLeaveHoursAverage SickLeave Hours from HumanResources.Employee where Title inn (Recruiter,Stocker) order by Title,VacationHours,SickLeaveHours compute avg(VacationHours),avg(SickLeaveHours) by Title输出结果P244使用computer by 从句计算Designation 列中的每个值的VacationHours 和 SickLeaveHours 小计.computer 从句计算VacationHours 和 SickLeaveHours 总和Select Title,Total VacationHours=VacationHours,SickLeaveHours Total SickLeaveHours from HumanResources.Employee where Title in (Recruiter,Stocker) order by Title,VacationHours,SicLeaveHours computer sum (VacationHours),sum(SickLeaveHours)3.pivotPivot操作符被用于将列集转换为值,pivot通过将来自列的唯一值以表达式的形式转换为输出中的多个列来轮换表达式.如果有输出中的多个列来轮换表达式.如果有输出的需要它将在剩下的列值上完成集合.显示某些员工下的购买定单的数量,并且包含提供商,Select VendorID ,164 as Emp1,198as Emp2,223 as Emp3,231 as Emp4,233 as EmpsFrom (select PurchaseOrderID ,EmpoyeeID ,VendorID from Purchasing.PurchaseHeader)pPivot(count (Purchase OrderID) for EmployeeID in (164,198,223,231,233)as pvt order by VendorID*注:1.computer 子句中所用到的列必须出现在select 表中三.使用连接和子查询查询数据1.使用连接查询数据SQL Sever 允许使用连接,连接允许在单一结果集中从相关的表中浏览数据,可以基于共同属性连接多个表依赖于浏览多个表的数据的需求,可以应用不同类的连接,有内连接,外连接交叉连接,等值连接或字连接.(1)使用内连接内连接在公共的列上使用比较操作符从表中抽取数据,当内连接被使用的时候仅满足公共列中的连接条件的值的行被显示,内连接取交集.两个表中不满足条件的行不被显示内连接是默认连接,因此可以通过使用join关键字来应用内连接,而且还可以用inner join关键字显示Employee表的每个员工的EmployeeID ,Title和EmployeePayHistory表的Rate和PayFrequency列select e.EmployeeID,e.Title,eph.Rate,eph.PayFrequency from HumanResources.Employee e join HumanResources.EmployeePayHistory eph on e.EmployeeID=eph.EmployeeID从Employee表中抽取所有支付率大于40的员工的 employeeid 和designation这个比较是以公共属性 EmployeeID,将 Employee表和 EmployeePayHiatory表连接后在 Rate列上完成的Select e.EmployeeIDEmployeeID,e.Titledesination from HumaanResources.Employee e inner join HumanResources.EmployeePayHistory eph on e.EmployeeID=eph.EmployeeeID where eph.Rate40(2)使用外连接外连接显示包含来自一个表中所有行和来自另一个表中匹配行的结果集,例如:在表A和表B上创建了外连接,结果中将会显示表A中所有记录,和来自表B中那些公共列保持条件为真的记录外连接若没找到匹配记录的相关表的列为null外连接有3种类型:左连接右连接完全连接1 使用左连接左连接返回left outer join关键字左侧指定的表的所有行和右侧指定的表的匹配的行,对于来自左侧的表的行,在右侧指定的表中没有发现匹配的行,那么来自右侧指定的表中获得数据的列中将显示null值SpecialOfferProduction表中包含一系列特价产品,SalesOrderDetail表存储所有销售事务的详细情况,AdventureWorks,Inc的用户需要浏览没有事务交易的特价产品的ProductIDSelect p.ProductID,p1.SalesOrderID,p1.UnitPrice from Sales.SpecialOfferProduct p left outer join Sales.SalesOrderDtail p1 on p.ProductID=p1.ProductID where SaleesOrderID is null左连接显示左表中所有记录与右表相匹配的行列2使用右连接右连接返回right outer join关键字右边指定的表中的所有记录和来自左边指定的表中的匹配行显示右表所有记录与左表相匹配的记录JoCandiidate表中存储所有职位候选人的详细信息,需抽取所有候选人的列表需要找到哪个候选人已经被 AdventureWorks,Inc雇用Select e.Title,d.JobCandidateID from HumanResources.Employee e right outer join HumanResources.JobCandidate d on e.EmployeeID=d.EmployeeID3使用完全外连接完全外连接是左,右连接的组合,此连接返回来自两个表中所有匹配和非配置的行,匹配记录只显示一次,在非配置行的情况下,对于数据不可用的列将显示null值产生一个显示所有员工最高学历的详细情况的列表select e.EmployeeID,e.EmployeeName ,ed.EmployeeEducaationCode,ed.Education from HumanResources.Employee e full outer join HumanResources.Education ed on e.EmployeeEducationCode = ed.EmployeeEducationCode(3)使用交叉连接交叉连接也被称笛卡儿积,在两个表中的每行与另一个表中的每行连接结果集中行的数量是第一个表中行的数量的乘积,即:如果表A有10行,表B有5行,那么表A的10行与表B的5行相连接,结果集将包含50行若已经存在ComputerDetails表中保存了计算机的配置和价格的详情,商店也销售外设,并且AddonDetails表中存储了这些外设的详情,为了确定一台计算机和所有外设的总价格:select c.CompDescription,a.AddOnDescription,c.Prince+a.Prince Total Cost from ComputerDetails c cross join AddOnDetails a(4)使用等值连接等值连接使用外键连接表，但是被用于显示两个表的所有列，所有连接的表的公共列被显示，与内连接的区别有两个方面，一方面输出内容不同，另一方面连接符号不同，等值连接符号只能是=,内连接符号是=,!=,使用公共列EmployeeID在EmployeeDepartmentHistory,Employee和Department表之间应用相等连接的例子select * from HumanResources.EmpoyeeDepartmentHistory d join HumanResources.Employee e on d.EmployeeID=e.EmployeeID join HumanResources.Department p on p.DepartmentID=d.departmentID(5)使用自连接在一个自连接中,一个表与自己连接,结果在一个表中的一行与同一个表中的另一行相关,在自连接中,表名在查询中使用两次,因此为了区别同一个表的两个实例表被给出两个别名Employee表与自己连接以显示EmployeeID属性和所有职务为经理的员工的职务:Select a.EmployeeId,a.Title Employee Designation,a.ManagerID,b.TitleManager Designation from HumanResources.Employee a join HumanResources.Employee b on a.ManagerID=b.EmployeeID2使用子查询查询数据子查询是一个SQL语句被用于另外一个SQL语句中，子查询被嵌入到select,insert,update和delete语句的where或having从句中,代表查询的查询被称为外部查询,代表子查询的查询被称为内部查询.数据库引擎首先执行内部查询,返回结果给外部查询以计算结果.(1) 使用操作符和in,exists关键字1子查询可以返回一个或多个值,操作符的返回值是单列单值型显示EmployeeID为46的员工部门的名称select Name from HumanResources.Department where DepartmentID=(select DepartmentID from HumanResources.EmployeeDepartmentHistory where EmployeeID=46 and EndDate is null)2使用in关键字子查询返回多于一个值,即返回值是单列多值型从Employee表中抽取所有住在Bothell的员工的EmployeeID属性,需使用一个查询获得包含词Bothell的所有地址的AddressID,然后再从Employee表中获得EmployeeIDselect EmployeeID from HumanResources.EmployeeAddress where AddressID in(select AddressID from Person.Address where City=Bothell3exists从句可以检查记录是否存在.exists关键字总是返回一个ture或false值Exists关键字不在任何列名,常量或其他表达式前面,并且它在内部查询的select列表中包含星号(*)查询以显示一个包含和所有在任何时候在市场部门工作的员工的EmployeeID和Title,其中市场部门的DepartmentID是4select EmloyeeID,Title from HumanResources.Employee where exists(select * from HumanResources.EmployeeDepartmentHistory where EmployeeID=HumanReources.Employee.EmployeeID and DepartmentID=4)注:子查询必须使用括号包围并且不能使用order by或compte by子句(2)使用修改的比较操作符使用子查询的时候,你可以使用=,和all大于列表中最大值column_nameall(10,20,30)表示大于30all小于列表中最小值c_nall(10,20,30)表示小于30all不等于列表中所有值c_nall(10,20,30)表示不等于10,20和30any大于列表中的最小值c_nany(10,20,30)表示大于10any小于列表中的最大值c_nany(10,20,30)表示小于30any不等于列表中的某一值c_n(10,20,30)不等于10或20或30=any等于列表中的任何值c_n=any(10,20,30)等于10或20或30显示所有休假小时大于职务为Recruiter的员工的休假小时的Title和EmployeeID:select EmployeeID,Title from HumanResources.Employee where VacationHours all(select VacationHours from HumanResorces.Emloyee where Title=Recruiter)(3)使用集合函数显示那些休假小时大于职务为市场助理的平均值的员工的EmployeeID:select EmployeeID from HumanResources.Employee where VacationHoiurrs(select avg(VacationHours) from HumanResources.Employee wher Title=Marketing Assistant)(4)使用嵌入子查询子查询可以包含一个或多个子查询,子查询依赖另一个查询的结果的时候被使用,它同样依赖于另一个子查询结果浏览一个电子邮件地址为的员工的DepartmentIDselect DepartmentID from HumanResources.EmployeeDepsrtmentHistory where EmployeeID=(select EmployeeID from HumanResources.Employee where ContactID=(select ContactID from Person.Contact where EmailAddress=)(5)使用相关子查询相关子查询被定义为依赖于它评估的外部查询的查询,在相关子查询中where从句引用在from从句的表中,这表示内部查询为外部查询指定的表的每行进行求值查询显示员工的ID,职务和所有休假小时大于他们职务的平均休假小时的员工花费在休假的小时数:select EmployeeID,Title from HumanResources.Employee where EmployeeID in (select EmployeeID from HumanResources.EmployeePayHistory where Rate40)四.管理数据和表1.管理数据库(1)系统数据库系统数据库是在SQL Server2005的每个实例中都存在的标准数据库,这些数据库包含一个特殊的表集,它们被用于存储服务器端特定的配置,其它数据库的模板,而且这些数据库包含一个需要用来查询数据库的临时存储区SQL Server2005中包含如下数据库:mastertempdbmodelmsdbresource1 master数据库记录所有器特定的配置信息,包括认证用户,数据库系统配置和远程服务器,而且它记录了全部实例的原数据,此数据库包含控制SQL Server运行的关键数据,它也存储在SQL Server的初始化信息,因此,如果master数据库不可用, SQL Server数据库引擎将不能启动,建议不要在master数据库上给任何用户授权,更新master数据库的备份以反映数据库中的变化也是很重要的,因为master数据库记录所有的其他数据库的存在和其他数据库文件的位置,master数据库是SQL Server系统数据库的主数据库2tempdb数据库tempdb数据库是一个临时数据库,它保存所有临时表和存储过程,它自动被服务器使用来解析大型或嵌入的查询或显示结果给户之前排序数据group by,order by和distinct从句产生的所有临时的表和结果都被存储在tempdb数据库中,不应将数据库在此数据库中,因为这个数据库在SQL Server每次启动的时候被重建,这将导致保存的数据丢失3model数据库model数据库作为新数据库的模板或原型,不论数据库何时被创建, model数据库的内容被复制到新数据库.model数据库可以为各种在数据定义语言(DDL)语句中指定的参数设置默认值以创建数据库对象,如果想让每个新数据库包含特定的数据库对象,你可以在model数据库加入对象,添加对象后无论何时创建一个新的数据库,对象都会添加到新数据库中4msdb数据库msdb数据库支持SQL Server代理, SQL Server代理是一个计划SQL Server周期活动的工具,例如备份和数据库邮件,msdb数据库包含任务计划,异常处理,警告管理和SQL Server执行服务需要的系统操作符信息,msdb数据库包含一些系统定义表,它们是特定于数据库的5资源数据库资源数据库是一个只读数据库,包含所有系统对象,例如系统定义的过程和SQL Server 2005包含的视图,资源数据库不包含用户数据或用户原数据(2)识别数据库文件1主数据文件:主数据文件包含数据库对象,主文件可以被用于系统表和对象,而第二数据文件被用于存储用户数据和对象,主数据文件的扩展名为.mdf.ndf.ldf（3）创建用户自定义数据库create database Country on( name=country1, filename=e:country1,mdf, size=3mb, maxsize=100mb, filegrowth=2mb),(name=country2,filename=e:country2.ndf,size=3mb,maxsize=100mb,filegrowth=2mb)log on (name=country_log,filename=e:country_log.ldf,size=3mb,maxsize=100mb,filegrowth=10%)(4)管理用户自定义数据库1查询数据库Sp_helpdb country2重命名Sp_renamedb country,address3增加文件Alter database Country Add file(or add log file)( name=country1, filename=e:country3,ndf, size=3mb, maxsize=100mb, filegrowth=2mb)4 修改文件alter database Country modify file( name=country3, filename=e: country3,ndf, size=5mb, maxsize=200mb, filegrowth=2mb)注：在修改文件时名称不能改变，最大存储空间容易从小变大，不容易从大变小5删除数据文件Remove database country6删除数据库Drop database country2.管理表（1）管理表1创建表dbo.作为表的前缀,是默认模式create table Student(StudentID int not null,StudentName char(30) not null,StudentAge int not null,Course char(30) not null)新建表的另一种方式是在已经打开的数据库表上右击新建表2增加字段alter table Studentadd Address varchar(50) not null3修改字段alter table Student alter column Course char(30) null4删除字段alter table Studentdrop column Course6查询表Sp_help table Student(2)数据完整性数据完整性是指维护数据的一致性和完整性,数据完整性被增强以确保数据库的数据是准确的,一致的和可靠的1实体完整性:确保每行都可以被一个称为主键的属性唯一确定,主键列包含所有行中的唯一值,而且这个列不能为null2域完整性:确保仅在列中保存值的有效范围,它可以通过限制数据的类型,值的范围和数据的格式来执行3引用完整性:确保外键的值与相应主键的值相匹配4用户定义完整性:指用户指定的一系列规则(3)创建约束1主键约束一张表中只能创建一个主键,但是主键可以不止一个,主键所在的列不能为null alter table Studentadd constraint pkStudentName primary key(StudentID,StudentName)2外键约束一张表中可创建多个外键 alter table Studentadd constraint fkStudentName foreign key (StudentName) references StudentDetail(StudentName)3检查约束alter table Studentadd constraint chkStudentAge check(StudentAge bet