SQL Server高级培训

SQLSERVER高级培训主讲人:郑维勇数据库的概述数据库对象表、视图、存储过程、触发器、默认值、规则、索引、用户。SQLServer数据库的版本•个人版•标准版•开发版•企业版Transact-SQL语言主要的4个组成部份•数据定义语言DDL•数据操作语言DML•数据控制语言DCL•共它语言元素Transact-SQL数据定义语言DDL用来创建数据库、数据库对象和定义列。包括：CREATE,ALTER,DROP。数据操纵语言DML用来操纵数据库的命令。包括：SELECT,INSERT,UPDATE,DELET。数据控制语言DCL用来控制数据库组件的存取许可、存取权限等的命令包括：GRANT,DENY,REVOKE。管理SQLServer服务启动SQLServer服务Netstartmssqlserver暂停SQLServer服务Netpausemssqlserver停止SQLServer服务NetstopmssqlserverSQLServer服务SQLServer2000的各种服务和功能说明服务器名称文件名服务说明MSSQLSERVERSqlserver.exe它负责数据存取、安全配置、事务管理等(Binn\sqlserver.exe)SQLSERVERAGENTSqlagent.exe负责调度定期执行的活动以及通知系统管理员服务器所发生的问题(Binn\sqlagent.ext)MicrosoftSearchMssearch.exe提供数据库内全文检索的功能DistributedTransactionCoordinator(MSDTC)Msdtc.exe是一个事务管理器，它允许客户端应用程序在一个事务中包含多个不同的数据源SQLServer服务器的注册与删除1、通过控制台目录2、使用Transact-sqlsp_addserver[@server=]‘server’[,[@local=]‘local’][,[@duplicate_ok=]’duplicate_ok’]Egsp_addserverlocal配置链接服务器在SQLServer2000中,允许SQLServer用户访问基于SQLServer的服务器以外的数据。通过配置链接服务器可以实现访问其他服务器上的OLEDB数据源。链接服务器是对OLEDB数据源的抽象，是OLEDB提供程序和连接属性的来源。在链接服务器的边接过程中，为了使该连接有效，需要在链接服务器之间创建登录映射。链接服务器登录映射可以通过系统存储过程sp_addlinkedsrvlogin添加，相应地，链接服务器登录映射可以通过系统存储过程sp_droplinkedsrvlogin删除。配置链接服务器添加键接服务器Sp_addlinkedserver[@server=]‘server’[,[@srvproduct=]’product_name’][,[@provider=]’provider_name’][,[@datasrc=]’data_source’][,[@location=]’location’][,[@provstr=]’provider_string’][,[@catalog=]’catalog’]Eg:execsp_addlinkedserver@server='exam2',@provider='Microsoft.Jet.OLEDB.4.0',@srvproduct='',@datasrc='d:\db2.mdb'sp_addlinkedserver'链接服务器名','产品名','Microsoft.Jet.OLEDB.4.0','Access数据库路径及文件名'sp_addlinkedserver'Anna','Access2000','Microsoft.Jet.OLEDB.4.0','D:\db2.mdb'配置链接服务器数据源提供者名称Product_nameProvider_nameData_sourceSQLServerSQLServerOLEDBProviderSQLServerSQLServerSQLServer主机网络名称AccessMicrosoftOLEDBproviderforJet任意名称Microsoft.Jet.OLEDB.4.0Access文件路径和文件名称ExcelMicrosoftOLEDBproviderforJet任意名称Microsoft.Jet.OLEDB.4.0Excel文件路径和文件名称配置链接服务器创建链接服务器登录通过系统存储过程sp_addlinkedsrvlogin可以创建或更新本地SQLServer实例上的登录与链接服务器上远程登录之间的映射。其语法为：Sp_addlinkedsrvlogin[@rmtsrvname=]’rmtsrvname’[,[@useself=]‘useself’][,[@location=]’location’][,[@rmtuser=]’rmtuser’][,[@rmtpassword=]’rmtpassword’]其中，参数:(1)[@rmtsrvname=]‘rmtsrvname’:是应用登录映射的链接服务器名称．(2)[@useself=]‘useself’:决定用于连接到远程服务器的登录名称，默认设置为ture.true值指定SQLServer验证的登录使用其自己的凭据以连接到rmtsrvname,而忽略rmtuser和rmtpassword参数．(3)[@locallogin=]’locallogin’:是本地服务器上的登录,默认设置为Null.如果为null,则指定应用于所有连接到rmtsrvname的本地登录．若不为null则locallogin可以是SQLServer登录或windowsNT用户(4)[@rmtuser=]’rmtuser’:当useself为false时,用来连接rmtsrvname的用户名,默认设置为null.配置链接服务器创建一个映射，使得所有到本地服务器的登录都使用其各自的用户凭据连接到链接服务器examExecsp_addlinkedsrvlogin‘exam’或Execsp_addlinkedsrvlogin‘exam’,’true’使用指定的用户和密码将所有本地登录连接到链接服务器Execsp_addlinkedsrvlogin‘exam’,’false’,null,’user’,’password’创建一个映射，使得所有到本地服务器的登录都使用相同的登录user和密码password连接到链接服务器exam,不使用登录和密码Execsp_addlinkedsrvlogin‘exam’,’false’配置链接服务器数据访问从SQLServer2000中访问OLEDB数据源的方法有两种：通过特殊名称以及通过链接服务器进行数据访问。在通过特殊名称访问OLEDB数据源的方法中，可以通过函数openrowset和opendatasource来进行。其语法:Openrowset(‘provider_name’,{‘datasource’;’user_id’;’password’|’provider_string’},{[catalog.][schema.]object|’query’})Eg:SELECT*FROMOPENROWSET('Microsoft.Jet.OLEDB.4.0','d:\db2.mdb';'admin';'',bbs)查询分析器•如何进入查询分析器？SQL语言基础(变量与常量)定义和使用局部变量定义局部变量Declare{@variable_namedatatype[,….n]}Declare@sexchar(2)为局部变量赋值为局部变量赋值一般有两种,一种是使用select语句，另一种是使用Set语句．Select@variable_name=expression[fromtable_name[,…n]whereclause]declare@booknamevarchar(30)select@bookname='vc'print@booknameGOSQL语言基础(变量与常量)定义和使用全局变量全局变量是SQLSERVER系统内部事先定义好的变量，不用用户参与定义，对用户而言是只读的。•@@identity:返回最近一次插入的identity列的数值,返回值是numeric•@@procid:返回当前存储过程的ID标识•@@version:返回当前SQLServer服务器安装日期、版本以及处理器的类型•@@language:返回当前使用的语言名•@@servername:返回当前SQLSERVER服务器名称SQL语言基础(变量与常量)使用日期和时间常量SQL提供的日期/时间数据类型可以存储日期和时间的组给数据。SQL规定日期、时间和时间间隔的常量值被指定为字符中常量。SQL语言基础(数据类型)常见的数据类型1.整型(int,smallint,tinyint,bigint)2.浮点数据类型(real,float,decimal)3.二进制数据类型(binary,varbinary)4.逻辑数据类型5.字符型数据类型(char(n),nchar(n),varchar(n),nvarchar(n))SQL语言基础(复合语句)定义复合语句(begin…end)declare@Aint,@Bint,@Cintset@A=20set@B=30beginset@C=@Aset@A=@Bset@B=@Cendprint@Aprint@BSQL语言基础(条件判断语句)使用if语句进行条件判断If条件表达式{命令行|程序块}declare@xintset@x=-100if@x0print'@x是一个负数'新建数据库•Createdatabase命令Createdatabasedatabase_name[on[filespec[,…..n]][,filegroup[,….n]]][logon{filespec[,….n]}][collatecollation_name][forload|forattach]新建数据库createdatabasestudenton(name='学生管理数据库',filename='f:\data\student.mdf',size=1MB,MAXSIZE=50MB,FILEGROWTH=5MB)logon(name='学生管理日志',filename='f:\data\student.ldf',size=1MB,MAXSIZE=50MB,FILEGROWTH=10%)修改数据库•ALTERDATABASE命令alterdatabasestudentmodifyfile(name='学生管理数据库',maxsize=unlimited)删除数据库•DropDatabase命令新建表•Createtable命令Createtabletable_name(column_namedata_type{[null|notnull][primarykey|unique]}[,………])新建表usestudentgocreatetablestu_info(stu_idintnotnull,stu_namevarchar(10),genderbit,majorvarchar(20),addressvarchar(100))修改表(增加字段)•增加字段Altertabletable_nameAddcolumn_namedata_typeEg:altertablestu_infoaddmemotext修改表(增加主键)•Altertableaddprimarykey(column_name)Eg:altertablestu_infoaddprimarykey(stu_id)修改表(更改字段)•更改字段属性Altertabletable_nameAltercolumncolumn_nam