《MS SqlServer数据库及应用》由会员分享，可在线阅读，更多相关《MS SqlServer数据库及应用（52页珍藏版）》请在金锄头文库上搜索。
1、SQL2000数据库系统及其应用演讲内容SQL语言基础初步认识SQL 2000SQL2000数据库开发应用结束SQL语言基础SQL基本数据类型和基本操作基本数据类型字符类型、二进制类型、数值类型、逻辑类型、日期/时间类型等等逻辑表达式=、=、 、 、and、Not、or等基本SQL语句建表语句create table 表名（列名 列属性，列名 列属性)插入语句insert into 表名 (列名1，列名2) values (值1，值2)查询语句select *|列名,列名,from 表名 where 条件表达式修改语句update 表名 set 列名1=表达式1，列名2=表达式2，where
2、条件表达式删除数据行语句delete from 表名 where 条件表达式清空表语句truncate table 表名删除表语句drop table 表名1，表名2，SQL语言基础Transact-SQL语句Top关键字返回用户指定数量Select top integer | top integer percent 列名1，列名2, from 表名Distinct关键字从查询结果集中去掉重复的行返回Text、ntext、image类型字段不能使用该关键字Select distinct *|列名1，列名2，from 表名SQL语言基础Transact-SQL语句使用计算列Transact-SQ
3、L允许直接在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 数据类型)
4、SQL语言基础Transact-SQL语句使用order by子句升序例：select name,price from book order by price asc降序例：select name,price from book order by price desc使用WHERE子句使用算术、逻辑表达式使用BETWEEN关键字，例：select 书号，价格from书价表where价格 BETWEEN $15 AND $20使用IN关键字，例：select 书号，价格 from 书价表 where 价格 in ($12,$25,$23,$16)SQL语言基础Transact-SQL语句使用Gr
5、oup by子句例：select name,avg(price) from book Group by name使用HAVING关键字筛选结果集例: select name,avg(price) from book Group by name having avg(price)20使用ALL关键字,显示所有被group by子句分类的数据集包括,不满足where子句查询要求的记录例: select name,avg(price) from book where name in (“书名1”,”书名2”)Group by all name 使用CUBE关键字产生多维立方体的数据结果集,实际上就是
6、数据列交叉组合所产生的结果集例:select 课程,性别,SUM(成绩) AS 总成绩 from 成绩表 group by 课程,性别 WITH CUBE 使用ROLLUP关键字,类似CUBE,但它压缩掉无意义的行例：select 课程,性别,SUM(成绩) AS 总成绩 from 成绩表 group by 课程,性别 WITH ROLLUP SQL语言基础Transact-SQL语句使用COMPUTE和COMPUTE BY子句类似GROUP BY ，但其可以产生多个结果集COMPUTE，返回结果追加一行所有记录的统计数据例：select 类型，价格，预付款from 商品表 order by
7、类型 compute SUM(价格),SUM(预付款)COMPUTE BY，返回结果针对每一个分类，进行统计计算例： select 类型，价格，预付款from 商品表 order by 类型 compute SUM(价格),SUM(预付款) BY 类型SQL语言基础Transact-SQL语句使用LIKE子句通配符的使用%：表示0至n个任意字符_:表示单个的任意字符:表示方括号里列出的任意一个字符：任意一个没有在方括号里列出的字符例：select name from authors where name like 7_2234d%转义字符的使用关键字ESCAPE用来规定转义字符例：select
8、 列名1 from 例表1 where 列名1 like Mxyzx ESCAPE MSQL语言基础Transact-SQL语句多表联接内联接，包括相等联接和自然联接。内联接是用比较运算符比较要联接列的值的联接，仅显示两个联接表中的匹配行的联接例1：select 表A.col1,表B.col2 from 表A,表B where 表B.col1=表A.col1例2： select A.col1,B.col2 from 表A as A INNER JOIN 表B as B ON 表B.col1=表A.col1SQL语言基础Transact-SQL语句多表联接外联接左向外联接，包括第一个命名表中的所
9、有行。不包括右表中的不匹配行 ,使用关键字LEFT OUTERJOIN ON例：SELECT B.col1 字段1,B.col2 字段2 ,P.col2 字段3 FROM 表B AS B LEFT OUTER JOIN 表P AS P ON P.col1 = B.col3右向外联接,包括第二个命名表中的所有行。不包括左表中的不匹配行 ,使用关键字RIGHT OUTERJOIN ON例：SELECT B.col1 字段1,B.col2 字段2 ,P.col2 字段3 FROM 表B AS B RIGHT OUTER JOIN 表P AS P ON P.col1 = B.col3完整外部联接,包括
10、所有联接表中的所有行，不论它们是否匹配,使用关键字FULL OUTERJOIN ON例：SELECT B.col1 字段1,B.col2 字段2 ,P.col2 字段3 FROM 表B AS B FULL OUTER JOIN 表P AS P ON P.col1 = B.col3SQL语言基础Transact-SQL语句多表联接交叉联接，也称作笛卡尔积 。两个表中每两个可能成对的行占一行 关键字：CROSS JOIN 例： select A.col1,B.col2 from 表A as A CROSS JOIN 表B as BSQL语言基础Transact-SQL语句嵌套查询使用IN和NOT
11、IN关键字：select 作品代码，作者代码from 作品表 where 作者代码 NOT IN （select 作者代码 from 作者表 where 作者国籍=中国）使用EXISTS和NOT EXISTS关键字：select 作品代码，作者代码from 作品表 where NOT EXISTS （select * from 作者表 where 作者表.作者代码=作品表.作者代码AND 作者国籍=中国）列清单里使用嵌套查询：select 作品名称，(select 作者姓名 from 作者表 where 作者表.作者代码=作品表.作者代码) as 作者姓名 from 作品表 灵活使用嵌套查询：
12、 select 作者姓名,作者年龄 from 作者表 where 作者年龄 （select avg(作者年龄） from 作者表)初步认识SQL2000SQL2000概述关系型数据库，数据按表进行组织和管理数据库对象：表格、视图、存储过程、触发器（数据完整性）、用户、角色等查询和执行语言Transact-SQL初步认识SQL2000安装SQL2000数据库远程安装要求本地和远程计算机操作系统均为MS WINDOWS NT要求本地和远程计算机使用相同芯片要求本地使用账号须具有远程计算机管理员级权限本地安装仅安装客户端工具安装数据库服务器和客户端工具设定数据库系统管理员密码初步认识SQL2000S
13、QL2000数据库组件数据库服务器数据库服务器管理器全文检索组件事件查看器客户端网络实用工具SQL SERVER网络实用工具企业管理器查询分析器SQL2000数据库开发应用管理SQL SERVER 服务器管理SQL SERVER 服务器组顶层组（Top Level Group）次级组（Sub-group）连接和注册SQL SERVER服务器使用SQLSERVER网络实用工具配置服务器端(网络连接参数：TCP/IP、命名管道等)使用客户端网络实用工具配置客户端（服务器别名、连接参数）使用企业管理器的注册服务器向导注册服务器断开和恢复同服务器的连接删除服务器SQL2000数据库开发应用管理SQL
14、SERVER 服务器配置SQL SERVER 服务器编辑SQLSERVER服务器注册属性修改身份验证其他注册属性配置SQLSERVER服务器属性选项卡常规选项卡：SQL SERVER版本信息、服务器配置信息、自启动策略内存选项卡：动态分配内存或是固定使用内存处理器选项卡：配置多处理器情况下运行方式安全性选项卡：登陆和启动服务器的身份验证配置连接选项卡：数据库连接方面信息的配置数据库选项卡：对数据库索引、备份和恢复等属性进行配置、服务器选项卡：选择默认语言、决定服务器是否支持触发器的嵌套设置，配置对对2000年问题的支持等属性Active Directory:将服务器添加到AD中，AD提供存储基
15、于网络的实体信息的空间复制选项卡：分布式数据管理使用服务器管理器启动和关闭SQL SERVER服务SQL2000数据库开发应用SQL Server数据库管理创建数据库用企业管理器创建数据库使用Transact-SQL创建数据库CREATE DATABASE database_name ON PRIMARY(NAME=操作系统文件的逻辑文件名,FILENAME=包含文件路径的操作系统文件的实际名字,SIZE=文件的初始长度,MAXSIZE=最大长度或UNLIMITED,FILEGROWTH=增长速度)LOG ON (日志文件的定义) SQL2000数据库开发应用SQL Server数据库管理查看
16、和修改数据库信息用企业管理器查看和修改数据库信息使用Transact-SQL查看和修改数据库信息查看数据库信息USE DATABASE_NAME GO sp_helpdb DATABASE_NAMESp_databases 数据库更名Sp_renamedb old_name,new_name修改数据库大小ALTER DATABASE database_name ADD FILE (文件定义)|REMOVE FILE FILE_NAME|MODIFY FILE (文件定义)删除数据库使用企业管理器使用Transact-SQL:DROP DATABASE database_name,n SQL20
17、00数据库开发应用表的管理和使用用企业管理器进行表的创建、查看、修改和删除使用Transact-SQL进行表的创建、查看、修改和删除创建表：CREATE TABLE table_name (列定义)查看表信息：Sp_help table_name重命名：Sp_rename old_tablename,new_tablenameSQL2000数据库开发应用使用视图视图的优点分割数据，简化用户浏览简化拥护操作提供自动的安全保护功能为数据库重构提供一定的逻辑独立性视图的创建和删除创建视图：CREATE VIEW view_name 列名1,AS SELECT STATEMENT WITH CHECK
18、 OPTION删除视图：Drop view view_nameSQL2000数据库开发应用使用视图修改视图的定义使用企业管理器使用Transact-SQL提供的ALTER VIEW语句视图的创建和删除创建视图：CREATE VIEW view_name 列名1,AS SELECT STATEMENT WITH CHECK OPTION删除视图：Drop view view_nameSQL2000数据库开发应用使用视图对视图进行加密WITH ENCRYPTION关键字例:CREATE VIEW title_view WITH ENCRYPTION AS SELECT * FROM titles使
19、用视图加强数据安全对不同用户授予不同的用户使用许可权通过SELECT子句限制用户对某些底层基本表列的访问通过WHERE子句限制用户对某些底层基本表行的访问SQL2000数据库开发应用使用视图对视图进行加密WITH ENCRYPTION关键字例:CREATE VIEW title_view WITH ENCRYPTION AS SELECT * FROM titles使用视图加强数据安全对不同用户授予不同的用户使用许可权通过SELECT子句限制用户对某些底层基本表列的访问通过WHERE子句限制用户对某些底层基本表行的访问SQL2000数据库开发应用数据完整性实体完整性实施:Primary key
20、 ,Unique key值域完整性实施: Default check foreign参照完整性实施: check foreign用户自定义的完整性实施: Rule,Triggers,ProcedureSQL2000数据库开发应用使用规则创建和删除规则使用企业管理器创建和删除规则Transact-SQL语句:CREATE RULE rule_name AS range =$100 AND range$200CREATE RULE rule_name AS list IN (12,23)CREATE RULE rule_name AS value LIKE _-%0-9绑定规则Sp_bindrul
21、e rule_name,”table_name.column_name”Sp_bindrule rule_name “user_defind_datatype” “futureonly_flag”解除绑定Sp_unbindrule “table_name.column_name”Sp_unbindrule “user_defind_datatype” “futureonly_flag”SQL2000数据库开发应用SQL Server 编程结构程序注释单行注释语句:“-”多行注释语句：使用/* 和*/括起来批处理结束标志: GO使用变量局部变量: DECLARE varible_name dat
22、atype ,n全局变量：反映服务器当前活动状态，系统给出的，用户不参与定义，IDENTITY,ROWCOUNT,ERROR,CONNECTIONS等变量赋值：SET varible_name=valueSELECT varible_name=valueSQL2000数据库开发应用SQL Server 编程结构设置查询属性SET NOCOUNTON|OFF：属性设置为ON，执行完一次SQL指令后，将不会返回查询所涉及的数据行的数目；设置为OFF则反之，默认值为OFF。SET NOEXECON|OFF：属性设置为ON，只编译不执行查询；设置为OFF以后编译并执行查询SET PARSEONLY O
23、N|OFF：属性设置为ON，只进行语法分析，不进行编译和执行；设置为OFF，既进行语法分析，又进行编译和执行SET ROWCOUNT 整型常量|整型变量返回不超过指定数量的记录，值为0时返回所有的记录SQL2000数据库开发应用SQL Server 编程结构控制结构IF logical_expression expressions1 ELSE expressions2BEGINENDWHILE logical_expression BEGIN expressions BREAK CONTINUE ENDCASE WHEN logical_expression THEN result_expre
24、ssionn ELSE result_expression ENDRETURN integer_expressionSQL2000数据库开发应用SQL Server 编程结构游标声明游标：DECLARE 游标名INSENSITIVE SCROLL CURSOR FOR SELECT 语句 FOR READ ONLY | UPDATE OF 列名1，列名2打开游标：OPEN cursor_name关闭游标：CLOSE cursor_name释放游标：DEALLOCATE cursor_name使用游标取数：FETCH NEXT|PRIOR|FIRST|LAST|ABSOLUTEn|nvar|RE
25、LATIVEn|nvarFROM cursor_name INTO varible,n利用游标修改数据:UPDATE table_name SET column_name=expression,n WHERE CURRENT OF cursor_nameFETCH_STATUS全局变量：返回被 FETCH 语句执行的最后游标的状态 0FETCH 语句成功 ；-1FETCH 语句失败或此行不在结果集中；-2被提取的行不存在。 SQL2000数据库开发应用SQL Server 编程结构事务控制语句开始一个事务单元：BEGIN TRANSACTIONtransaction_name提交一个事务单元：
26、COMMIT TRANSACTION transaction_name回滚一个事务单元：ROLLBACK TRANSACTION transaction_name设置保存点：SAVE TRANSACTION savepoint_nameSQL2000数据库开发应用使用存储过程存储过程的优点执行速度快：创建时已进行了语法检查和性能优化，执行时不必再重复这些步骤。经一次调用驻留内存，提高执行速度模块化的程序设计：对应用程序具有独立性，减少网络通信量：调用时只需一个语句保证系统的安全性：用户不具有直接访问数据表数据的权限，只被赋予了对若干存储过程的访问权限SQL2000数据库开发应用使用存储过程创建
27、存储过程：CREATE PROCEDURE procedure_nameparameter data_type) =defaultOUTPUT,n WITH RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTIONAS sql_statementnRECOMPILE:执行完存储过程不在高速缓存里保存存储过程的备份，每次执行都重新编译和优化管理存储过程使用企业管理器查看存储过程信息sp_helptext|sp_help procedure_name查看存储过程引用表的情况：sp_depends procedure_nameSQL2000数据库开发应用使用存储过程执行存储
28、过程：若调用存储过程的语句为批处理的第一句，可直接使用存储过程名，否则应该使用EXECUTE或EXEC关键字，例：use db_name EXEC procedure_name删除存储过程:DROP PROCEDURE proceduren修改存储过程:ALTER PROCEDURE procedure_name .SQL2000数据库开发应用触发器的应用触发器是一种特殊的存储过程,它与表格紧密相连,可以看作表格定义的一部分创建触发器：CREATE TRIGGER trigger_name ON table_name WITH ENCRYPTIONFOR DELETE,INSERT,UPDAT
29、ENOT FOR REPLACATIONAS sql_statementnRETURN integer_expression管理触发器:sp_helptrigger table_name,type删除触发器:DROP TRIGGER triggern修改触发器:ALTER TRIGGER trigger_name .SQL2000数据库开发应用管理SQL Server 的安全性SQL Server 的安全机制分为四个等级客户机操作系统的安全性SQL Server的登陆安全性数据库的使用安全性数据库对象的是使用安全性用户通过一个等级的验证达到下一个安全等级SQL2000数据库开发应用SQL Se
30、rver 的安全性SQL Server标准登录模式创建账户语法:Sp_addlogin loginame = login,passwd = passworddefdb = database,deflanguage = language,sid = sid,encryptopt = encryption_optionsid:用户唯一标示符encryptopt:是否对存储在系统表里的密码进行加密,其取值:NULL 默认选项,加密;skip_encryption 不加密;skip_encryption_old 不加密,提供的密码已使用较早版本进行加密SQL2000数据库开发应用SQL Server
31、的安全性SQL Server标准登录模式特殊账户SA拥有最高的管理权限,默认任何用户数据库的主任修改和删除帐户修改默认数据库:Sp_defaultdb login_name database_name修改默认语言:2p_defaultlanguage login_name French修改账户密码:Sp_password “old_password”,”new_password”,”login_name”删除指定账户:Sp_droplogin login_name撤回刚才新建账户:Sp_revokelogin loginame= loginSQL2000数据库开发应用SQL Server 的安
32、全性SQL Server集成登录模式创建登录用户映射账户:Sp_grantlogin loginame = login撤消账户映射:sp_revokelogin loginame= login阻止账户连接数据库服务器:sp_denylogin loginame= login使用企业管理器创建登录用户SQL2000数据库开发应用SQL Server 的安全性SQL Server数据库安全性添加数据库用户Sp_grantdbaccess loginame = login ,name_in_db= name_in_dbname_in_db 账户在该数据库下的用户名删除数据库用户语法：sp_revok
33、edbaccess name_in_db = name特殊的数据库用户Dbo：对应于登录账户SA，是数据库拥有者，存在于每个数据库，拥有最高权力Guest：可以使任何已经登录到数据库服务器的用户都可以访问数据库，sp_grantdbaccess guest使用企业管理器管理数据库用户SQL2000数据库开发应用SQL Server 的安全性SQL Server角色服务器角色浏览固定服务器角色:sp_helpsvrole分配角色:sp_addsvrrolemember 登录账户 role_name使用企业管理器进行服务器角色管理数据库角色浏览固定数据库角色：sp_helpdbfixedrole分
34、配角色：sp_addrolemember db_owner,name撤销角色：sp_droprolemember db_owner,name创建角色：sp_addrole rolename= role ,ownername= owner删除角色：sp_droprole role_name使用企业管理器管理数据库角色SQL2000数据库开发应用SQL Server 的安全性SQL Server许可许可概述对象许可：数据库对象拥有者授予许可给指定用户，命令许可：授予执行某些Transact-SQL命令的许可许可内容：INSERT、UPDATE、DELETE、SELECT、REFERENCES，AL
35、L permissions、EXECUTE授予许可授予对象许可：GRANTALL PRIVILEGES|permission,ncolumn,.n) ON table |view|ONtable|viewColumn,.n)|ONstored_procedure TO security_account,nWITH GRANT OPTION授予命令许可：GRANTALL|statement,n TO secuity_account,nWITH GRANT OPTION：定义是否给予用户授予该许可给别的用户的权力SQL2000数据库开发应用SQL Server 的安全性SQL Server许可撤销
36、许可撤销对象许可：REVOKEGRANTOPTIONFORALLPRIVILEGES|ONtable|view|ONtable|view(column,.n)|stored_procedureTO|FROMsecurity_account,.nCASCADE撤销命令许可：REVOKEALL |STATEMENT,nFROM secutity_account,nCASCADE:对应于WITH GRANT OPTION ,同时撤销该用户授予给其他用户的权力拒绝访问对象许可：DENYALLPRIVILEGES|permission,.n(column,.n ON table | view|ONtab
37、le |viewcolumn,.n)|ONstored_procedure |extended_procedureTO security_account,nCASCADE命令许可：DENYALL|statement,n TO security_account,.n使用企业管理器管理许可SQL2000数据库开发应用SQL Server 数据备份与恢复SQL Server的备份策略备份数据库不备份事务日志,操作和规划简单,恢复时只需一步操作即可恢复到数据库备份操作结束时的状态备份数据库和事务日志在备份数据库的同时,备份事务日志,可以将数据库恢复到意外发生前的状态增量备份在原有备份的基础备份数据库更
38、新的内容,提高操作速度,减少备份时间其它策略针对文件和文件组手工备份数据库文件SQL2000数据库开发应用SQL Server 数据备份与恢复执行SQL Server的备份命名备份名称选择备份方案添加备份设备设定重写方案制订备份的调度计划SQL2000数据库开发应用SQL Server 数据备份与恢复执行SQL Server的恢复选择还原类型选择恢复设备也可利用Transact-SQL语句完成数据库恢复SQL2000数据库开发应用SQL Server 数据传输服务DTS (Data Transformation Service)功能数据的导入和导出多种数据源之间数据传输转换数据格式实现一列或多
39、列数据的统计计算,格式变换传输传输数据库对象实现同类数据源之间存储过程、约束等数据库对象的传递SQL2000数据库开发应用SQL Server 数据传输服务DTS 工具DTS DesignerDTS包的图形化编辑和执行工具数据转换服务导入/导出向导数据传输服务的简易工具命令提示实用工具Dtswiz 命令行工具，启动DTS 导入导出向导，默认的安装路径为 x:Program FilesMicrosoft SQL Server80ToolsBinnDtsrun 命令行工具，执行DTS包，默认的安装路径为 x:Program FilesMicrosoft SQL SQL2000数据库开发应用SQL
40、Server 自动化管理执行元素作业(Job)操作者(Operator)警报(Alert) 使用SQL Mail创建操作者设置警报创建作业SQL2000数据库开发应用SQL Server 分布式数据管理SQL Server复制技术快照复制,最初配置复制时,自动同步所有目标数据库数据,以后每次更新只发送数据的改变快照事务复制,连续监视出版服务器事务日志的改变.,发布修改表数据的语句,不发送实际的数据合并复制,多个站点独立于出版服务器而修改数据,然后与出版服务器同步将改变的数据发布到其他站点. SQL Server复制的基本元素出版服务器订阅服务器分发服务器出版物文章使用复制技术配置出版服务器创建出版物设计订阅演讲结束报告完毕谢谢大家的支持和鼓励！
