MS SqlServer数据库及应用.ppt

SQL2000数据库系统及其应用,演讲内容,SQL语言基础初步认识SQL2000SQL2000数据库开发应用结束,SQL语言基础,SQL基本数据类型和基本操作基本数据类型字符类型、二进制类型、数值类型、逻辑类型、日期/时间类型等等逻辑表达式=、=、and、Not、or等基本SQL语句建表语句createtable表名（列名列属性，列名列属性)插入语句insertinto表名(列名1，列名2)values(值1，值2)查询语句select*|列名,列名,from表名where条件表达式修改语句update表名set列名1=表达式1，列名2=表达式2，where条件表达式删除数据行语句deletefrom表名where条件表达式清空表语句truncatetable表名删除表语句droptable表名1，表名2，,SQL语言基础,Transact-SQL语句Top关键字返回用户指定数量Selecttopinteger|topintegerpercent列名1，列名2,from表名Distinct关键字从查询结果集中去掉重复的行返回Text、ntext、image类型字段不能使用该关键字Selectdistinct*|列名1，列名2，from表名,SQL语言基础,Transact-SQL语句使用计算列Transact-SQL允许直接在SELECT语句中使用计算列Select姓名+(+学号+),数学成绩+语文成绩from学生成绩表常用函数集合函数AVG、SUM、MAX、MIN、COUNT等字符串函数LEN、LTRIM、RTRIM、LEFT、RIGHT、SUBSTRING、UPPER、LOWER、REPLACE等日期函数GETDATE（）、DAY（DATE）、MONTH（DATE）、YEAR（DATE）、datediff(datepart,date_expr1,date_expr2)等其它函数ABS()、CEILING()、FLOOR（）、CONVERT(数据类型，表达式)、CAST(表达式AS数据类型),SQL语言基础,Transact-SQL语句使用orderby子句升序例：selectname,pricefrombookorderbypriceasc降序例：selectname,pricefrombookorderbypricedesc使用WHERE子句使用算术、逻辑表达式使用BETWEEN关键字，例：select书号，价格from书价表where价格BETWEEN$15AND$20使用IN关键字，例：select书号，价格from书价表where价格in($12,$25,$23,$16),SQL语言基础,Transact-SQL语句使用Groupby子句例：selectname,avg(price)frombookGroupbyname使用HAVING关键字筛选结果集例:selectname,avg(price)frombookGroupbynamehavingavg(price)20使用ALL关键字,显示所有被groupby子句分类的数据集包括,不满足where子句查询要求的记录例:selectname,avg(price)frombookwherenamein(“书名1”,”书名2”)Groupbyallname使用CUBE关键字产生多维立方体的数据结果集,实际上就是数据列交叉组合所产生的结果集例:select课程,性别,SUM(成绩)AS总成绩from成绩表groupby课程,性别WITHCUBE使用ROLLUP关键字,类似CUBE,但它压缩掉无意义的行例：select课程,性别,SUM(成绩)AS总成绩from成绩表groupby课程,性别WITHROLLUP,SQL语言基础,Transact-SQL语句使用COMPUTE和COMPUTEBY子句类似GROUPBY，但其可以产生多个结果集COMPUTE，返回结果追加一行所有记录的统计数据例：select类型，价格，预付款from商品表orderby类型computeSUM(价格),SUM(预付款)COMPUTEBY，返回结果针对每一个分类，进行统计计算例：select类型，价格，预付款from商品表orderby类型computeSUM(价格),SUM(预付款)BY类型,SQL语言基础,Transact-SQL语句使用LIKE子句通配符的使用%：表示0至n个任意字符_:表示单个的任意字符:表示方括号里列出的任意一个字符：任意一个没有在方括号里列出的字符例：selectnamefromauthorswherenamelike7_2234d%转义字符的使用关键字ESCAPE用来规定转义字符例：select列名1from例表1where列名1likeMxyzxESCAPEM,SQL语言基础,Transact-SQL语句多表联接内联接，包括相等联接和自然联接。内联接是用比较运算符比较要联接列的值的联接，仅显示两个联接表中的匹配行的联接例1：select表A.col1,表B.col2from表A,表Bwhere表B.col1=表A.col1例2：selectA.col1,B.col2from表AasAINNERJOIN表BasBON表B.col1=表A.col1,SQL语言基础,Transact-SQL语句多表联接外联接左向外联接，包括第一个命名表中的所有行。不包括右表中的不匹配行,使用关键字LEFTOUTERJOINON例：SELECTB.col1字段1,B.col2字段2,P.col2字段3FROM表BASBLEFTOUTERJOIN表PASPONP.col1=B.col3右向外联接,包括第二个命名表中的所有行。不包括左表中的不匹配行,使用关键字RIGHTOUTERJOINON例：SELECTB.col1字段1,B.col2字段2,P.col2字段3FROM表BASBRIGHTOUTERJOIN表PASPONP.col1=B.col3完整外部联接,包括所有联接表中的所有行，不论它们是否匹配,使用关键字FULLOUTERJOINON例：SELECTB.col1字段1,B.col2字段2,P.col2字段3FROM表BASBFULLOUTERJOIN表PASPONP.col1=B.col3,SQL语言基础,Transact-SQL语句多表联接交叉联接，也称作笛卡尔积。两个表中每两个可能成对的行占一行关键字：CROSSJOIN例：selectA.col1,B.col2from表AasACROSSJOIN表BasB,SQL语言基础,Transact-SQL语句嵌套查询使用IN和NOTIN关键字：select作品代码，作者代码from作品表where作者代码NOTIN（select作者代码from作者表where作者国籍=中国）使用EXISTS和NOTEXISTS关键字：select作品代码，作者代码from作品表whereNOTEXISTS（select*from作者表where作者表.作者代码=作品表.作者代码AND作者国籍=中国）列清单里使用嵌套查询：select作品名称，(select作者姓名from作者表where作者表.作者代码=作品表.作者代码)as作者姓名from作品表灵活使用嵌套查询：select作者姓名,作者年龄from作者表where作者年龄（selectavg(作者年龄）from作者表),初步认识SQL2000,SQL2000概述关系型数据库，数据按表进行组织和管理数据库对象：表格、视图、存储过程、触发器（数据完整性）、用户、角色等查询和执行语言Transact-SQL,初步认识SQL2000,安装SQL2000数据库远程安装要求本地和远程计算机操作系统均为MSWINDOWSNT要求本地和远程计算机使用相同芯片要求本地使用账号须具有远程计算机管理员级权限本地安装仅安装客户端工具安装数据库服务器和客户端工具设定数据库系统管理员密码,初步认识SQL2000,SQL2000数据库组件数据库服务器数据库服务器管理器全文检索组件事件查看器客户端网络实用工具SQLSERVER网络实用工具企业管理器查询分析器,SQL2000数据库开发应用,管理SQLSERVER服务器管理SQLSERVER服务器组顶层组（TopLevelGroup）次级组（Sub-group）连接和注册SQLSERVER服务器使用SQLSERVER网络实用工具配置服务器端(网络连接参数：TCP/IP、命名管道等)使用客户端网络实用工具配置客户端（服务器别名、连接参数）使用企业管理器的注册服务器向导注册服务器断开和恢复同服务器的连接删除服务器,SQL2000数据库开发应用,管理SQLSERVER服务器配置SQLSERVER服务器编辑SQLSERVER服务器注册属性修改身份验证其他注册属性配置SQLSERVER服务器属性选项卡常规选项卡：SQLSERVER版本信息、服务器配置信息、自启动策略内存选项卡：动态分配内存或是固定使用内存处理器选项卡：配置多处理器情况下运行方式安全性选项卡：登陆和启动服务器的身份验证配置连接选项卡：数据库连接方面信息的配置数据库选项卡：对数据库索引、备份和恢复等属性进行配置、服务器选项卡：选择默认语言、决定服务器是否支持触发器的嵌套设置，配置对对2000年问题的支持等属性ActiveDirectory:将服务器添加到AD中，AD提供存储基于网络的实体信息的空间复制选项卡：分布式数据管理使用服务器管理器启动和关闭SQLSERVER服务,SQL2000数据库开发应用,SQLServer数据库管理创建数据库用企业管理器创建数据库使用Transact-SQL创建数据库CREATEDATABASEdatabase_nameONPRIMARY(NAME=操作系统文件的逻辑文件名,FILENAME=包含文件路径的操作系统文件的实际名字,SIZE=文件的初始长度,MAXSIZE=最大长度或UNLIMITED,FILEGROWTH=增长速度)LOGON(日志文件的定义),SQL2000数据库开发应用,SQLServer数据库管理查看和修改数据库信息用企业管理器查看和修改数据库信息使用Transact-SQL查看和修改数据库信息查看数据库信息USEDATABASE_NAMEGOsp_helpdbDATABASE_NAMESp_databases数据库更名Sp_renamedbold_name,new_name修改数据库大小ALTERDATABASEdatabase_nameADDFILE(文件定义)|REMOVEFILEFILE_NAME|MODIFYFILE(文件定义)删除数据库使用企业管理器使用Transact-SQL:DROPDATABASEdatabase_name,n,SQL2000数据库开发应用,表的管理和使用用企业管理器进行表的创建、查看、修改和删除使用Transact-SQL进行表的创建、查看、修改和删除创建表：CREATETABLEtable_name(列定义)查看表信息：Sp_helptable_name重命名：Sp_renameold_tablename,new_tablename,SQL2000数据库开发应用,使用视图视图的优点分割数据，简化用户浏览简化拥护操作提供自动的安全保护功能为数据库重构提供一定的逻辑独立性视图的创建和删除创建视图：CREATEVIEWview_name列名1,ASSELECTSTATEMENTWITHCHECKOPTION删除视图：Dropviewview_name,SQL2000数据库开发应用,使用视图修改视图的定义使用企业管理器使用Transact-SQL提供的ALTERVIEW语句视图的创建和删除创建视图：CREATEVIEWview_name列名1,ASSELECTSTATEMENTWITHCHECKOPTION删除视图：Dropviewview_name,SQL2000数据库开发应用,使用视图对视图进行加密WITHENCRYPTION关键字例:CREATEVIEWtitle_viewWITHENCRYPTIONASSELECT*FROMtitles使用视图加强数据安全对不同用户授予不同的用户使用许可权通过SELECT子句限制用户对某些底层基本表列的访问通过WHERE子句限制用户对某些底层基本表行的访问,SQL2000数据库开发应用,使用视图对视图进行加密WITHENCRYPTION关键字例:CREATEVIEWtitle_viewWITHENCRYPTIONASSELECT*FROMtitles使用视图加强数据安全对不同用户授予不同的用户使用许可权通过SELECT子句限制用户对某些底层基本表列的访问通过WHERE子句限制用户对某些底层基本表行的访问,SQL2000数据库开发应用,数据完整性实体完整性实施:Primarykey,Uniquekey值域完整性实施:Defaultcheckforeign参照完整性实施:checkforeign用户自定义的完整性实施:Rule,Triggers,Procedure,SQL2000数据库开发应用,使用规则创建和删除规则使用企业管理器创建和删除规则Transact-SQL语句:CREATERULErule_nameASrange=$100ANDrange$200CREATERULErule_nameASlistIN(12,23)CREATERULErule_nameASvalueLIKE_-%0-9绑定规则Sp_bindrulerule_name,”table_name.column_name”Sp_bindrulerule_name“user_defind_datatype”“futureonly_flag”解除绑定Sp_unbindrule“table_name.column_name”Sp_unbindrule“user_defind_datatype”“futureonly_flag”,SQL2000数据库开发应用,SQLServer编程结构程序注释单行注释语句:“-”多行注释语句：使用/*和*/括起来批处理结束标志:GO使用变量局部变量:DECLAREvarible_namedatatype,n全局变量：反映服务器当前活动状态，系统给出的，用户不参与定义，IDENTITY,ROWCOUNT,ERROR,CONNECTIONS等变量赋值：SETvarible_name=valueSELECTvarible_name=value,SQL2000数据库开发应用,SQLServer编程结构设置查询属性SETNOCOUNTON|OFF：属性设置为ON，执行完一次SQL指令后，将不会返回查询所涉及的数据行的数目；设置为OFF则反之，默认值为OFF。SETNOEXECON|OFF：属性设置为ON，只编译不执行查询；设置为OFF以后编译并执行查询SETPARSEONLYON|OFF：属性设置为ON，只进行语法分析，不进行编译和执行；设置为OFF，既进行语法分析，又进行编译和执行SETROWCOUNT整型常量|整型变量返回不超过指定数量的记录，值为0时返回所有的记录,SQL2000数据库开发应用,SQLServer编程结构控制结构IFlogical_expressionexpressions1ELSEexpressions2BEGINENDWHILElogical_expressionBEGINexpressionsBREAKCONTINUEENDCASEWHENlogical_expressionTHENresult_expressionnELSEresult_expressionENDRETURNinteger_expression,SQL2000数据库开发应用,SQLServer编程结构游标声明游标：DECLARE游标名INSENSITIVESCROLLCURSORFORSELECT语句FORREADONLY|UPDATEOF列名1，列名2打开游标：OPENcursor_name关闭游标：CLOSEcursor_name释放游标：DEALLOCATEcursor_name使用游标取数：FETCHNEXT|PRIOR|FIRST|LAST|ABSOLUTEn|nvar|RELATIVEn|nvarFROMcursor_nameINTOvarible,n利用游标修改数据:UPDATEtable_nameSETcolumn_name=expression,nWHERECURRENTOFcursor_nameFETCH_STATUS全局变量：返回被FETCH语句执行的最后游标的状态0FETCH语句成功；-1FETCH语句失败或此行不在结果集中；-2被提取的行不存在。,SQL2000数据库开发应用,SQLServer编程结构事务控制语句开始一个事务单元：BEGINTRANSACTIONtransaction_name提交一个事务单元：COMMITTRANSACTIONtransaction_name回滚一个事务单元：ROLLBACKTRANSACTIONtransaction_name设置保存点：SAVETRANSACTIONsavepoint_name,SQL2000数据库开发应用,使用存储过程存储过程的优点执行速度快：创建时已进行了语法检查和性能优化，执行时不必再重复这些步骤。经一次调用驻留内存，提高执行速度模块化的程序设计：对应用程序具有独立性，减少网络通信量：调用时只需一个语句保证系统的安全性：用户不具有直接访问数据表数据的权限，只被赋予了对若干存储过程的访问权限,SQL2000数据库开发应用,使用存储过程创建存储过程：CREATEPROCEDUREprocedure_nameparameterdata_type)=defaultOUTPUT,nWITHRECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTIONASsql_statementnRECOMPILE:执行完存储过程不在高速缓存里保存存储过程的备份，每次执行都重新编译和优化管理存储过程使用企业管理器查看存储过程信息sp_helptext|sp_helpprocedure_name查看存储过程引用表的情况：sp_dependsprocedure_name,SQL2000数据库开发应用,使用存储过程执行存储过程：若调用存储过程的语句为批处理的第一句，可直接使用存储过程名，否则应该使用EXECUTE或EXEC关键字，例：usedb_nameEXECprocedure_name删除存储过程:DROPPROCEDUREproceduren修改存储过程:ALTERPROCEDUREprocedure_name.,SQL2000数据库开发应用,触发器的应用触发器是一种特殊的存储过程,它与表格紧密相连,可以看作表格定义的一部分创建触发器：CREATETRIGGERtrigger_nameONtable_nameWITHENCRYPTIONFORDELETE,INSERT,UPDATENOTFORREPLACATIONASsql_statementnRETURNinteger_expression管理触发器:sp_helptriggertable_name,type删除触发器:DROPTRIGGERtriggern修改触发器:ALTERTRIGGERtrigger_name.,SQL2000数据库开发应用,管理SQLServer的安全性SQLServer的安全机制分为四个等级客户机操作系统的安全性SQLServer的登陆安全性数据库的使用安全性数据库对象的是使用安全性用户通过一个等级的验证达到下一个安全等级,SQL2000数据库开发应用,SQLServer的安全性SQLServer标准登录模式创建账户语法:Sp_addloginloginame=login,passwd=passworddefdb=database,deflanguage=language,sid=sid,encryptopt=encryption_optionsid:用户唯一标示符encryptopt:是否对存储在系统表里的密码进行加密,其取值:NULL默认选项,加密;skip_encryption不加密;skip_encryption_old不加密,提供的密码已使用较早版本进行加密,SQL2000数据库开发应用,SQLServer的安全性SQLServer标准登录模式特殊账户SA拥有最高的管理权限,默认任何用户数据库的主任修改和删除帐户修改默认数据库:Sp_defaultdblogin_namedatabase_name修改默认语言:2p_defaultlanguagelogin_nameFrench修改账户密码:Sp_password“old_password”,”new_password”,”login_name”删除指定账户:Sp_droploginlogin_name撤回刚才新建账户:Sp_revokeloginloginame=login,SQL2000数据库开发应用,SQLServer的安全性SQLServer集成登录模式创建登录用户映射账户:Sp_grantloginloginame=login撤消账户映射:sp_revokeloginloginame=login阻止账户连接数据库服务器:sp_denyloginloginame=login使用企业管理器创建登录用户,SQL2000数据库开发应用,SQLServer的安全性SQLServer数据库安全性添加数据库用户Sp_grantdbaccessloginame=login,name_in_db=name_in_dbname_in_db账户在该数据库下的用户名删除数据库用户语法：sp_revokedbaccessname_in_db=name特殊的数据库用户Dbo：对应于登录账户SA，是数据库拥有者，存在于每个数据库，拥有最高权力Guest：可以使任何已经登录到数据库服务器的用户都可以访问数据库，sp_grantdbaccessguest使用企业管理器管理数据库用户,SQL2000数据库开发应用,SQLServer的安全性SQLServer角色服务器角色浏览固定服务器角色:sp_helpsvrole分配角色:sp_addsvrrolemember登录账户role_name使用企业管理器进行服务器角色管理数据库角色浏览固定数据库角色：sp_helpdbfixedrole分配角色：sp_addrolememberdb_owner,name撤销角色：sp_droprolememberdb_owner,name创建角色：sp_addrolerolename=role,ownername=owner删除角色：sp_droprolerole_name使用企业管理器管理数据库角色,SQL2000数据库开发应用,SQLServer的安全性SQLServer许可许可概述对象许可：数据库对象拥有者授予许可给指定用户，命令许可：授予执行某些Transact-SQL命令的许可许可内容：INSERT、UPDATE、DELETE、SELECT、REFERENCES，ALLpermissions、EXECUTE授予许可授予对象许可：GRANTALLPRIVILEGES|permission,ncolumn,.n)ONtable|view|ONtable|viewColumn,.n)|ONstored_procedureTOsecurity_account,nWITHGRANTOPTION授予命令许可：GRANTALL|statement,nTOsecuity_account,nWITHGRANTOPTION：定义是否给予用户授予该许可给别的用户的权力,SQL2000数据库开发应用,SQLServer的安全性SQLServer许可撤销许可撤销对象许可：REVOKEGRANTOPTIONFORALLPRIVILEGES|ONtable|view|ONtable|view(column,.n)|stored_procedureTO|FROMsecurity_account,.nCASCADE撤销命令许可：REVOKEALL|STATEMENT,nFROMsecutity_account,nCASCADE:对应于WITHGRANTOPTION,同时撤销该用户授予给其他用户的权力拒绝访问对象许可：DENYALLPRIVILEGES|permission,.n(column,.nONtable|view|ONtable|viewcolumn,.n)|ONstored_procedure|extended_procedureTO