SQL server高级应用

 

SQL server高级应用

一. 建库,建表,加约束.

1.1建库

use master

go

if exists (select * from sysdatabases where name=’MyDatabase’)—判断master数据库sysdatagbases表中是否存在将要创建的数据库名

drop database MyDatabase—如果sysdatabases表中存在该数据库名,则将它删除

go

exec xp_cmdshell ‘md D:/MyDatabases’—利用存储过程创建一个文件夹用于存储数据物理文件(数据文件，日志文件),DOS命令(mkdir=md)

go

create database MyDatabase—创建数据库

on

(

  name=’MyDatabase_data’,--指定逻辑文件名

  filename=’D:/MyDatabases/MyDatabase_data.mdf’,--指定物理文件名

  size=5mb,--初始大小

  maxsize=50mb,--指定物理文件最大容量,可选

  filegrowth=20%--增长量

)

log on

(

  name=’MyDatabase_log’,--指定逻辑日志文件名

  filename=’D:/MyDatabases/MyDatabase_log.ldf’,--指定日志物理文件名

  size=5mb,--初始大小

  maxsize=50mb,--指定日志物理文件最大容量,可选

  filegrowth=20%--增长量

)

go

use MyDatabase

go

1.2建表.

If exists (select * from sysobjects where name=’Mytable’)

drop table Mytable

go

create table Mytable

(

  ID int not null identity(1,1) primary key,--标识种子1,标识增量1,设该列为主键

  name nchar(10) not null,--不可为空

  degree numeric(18,0)—身份证,numeric(18,0)代表18位数字,小数位数为0

)

Go

1.3加约束.

Alter table stuInfo add constraint PK_stuNo primary key(stuNo)—主键

alter table stuMarks add constraint FK_stuNo foreign key(stuNo) references stuInfo(stuNo)—外键

alter table stuInfo add constraint UQ_stuID unique(stuID)—唯一约束

alter table stuInfo add constraint df_stuAddress default(‘地址不详’) for stuAddres—默认约束

alter table stuMarks add constraint ck_stuAge check(stuAge between 15 and 40)—检查约束

alter table stuMarks drop constraint ck_stuAge—删除约束

1.4帐户管理.

1.4.1 创建登录帐户.

exec sp_grantlogin 'jbtraining/s26301' --windows用户为jbtraining/s26301,jbtraining 表示域

exec sp_addlogin 'admin','0533131'--SQL登录帐户,帐户: 'admin',密码:0533131.

1.4.2 创建数据库用户.

exec sp_grantdbaccess 'jbtraining/s26301','s26301dbuser'--s26301dbuser为数据库用户名

exec sp_grantdbaccess 'admin', 's26301dbuser'--s26301dbuser为数据库名

1.4.3 向数据库用户授权.

/*为s26301dbuser分配对表mytable的select,insert,updata,delete权限*/

grant select,insert,update,delete on mytable to s26301dbuser

/*为s26301dbuser用户分配创建表的权限

grant create table to s26301dbuser

二.T-SQL编程

2.1变量.

局部变量的名称必须以标记@作为前缀：

Declare @name varchar(8)--声明变量。

Declare @name varchar(8)=value--初始值。

Set @name=value-- 赋值。

Select @name=value--赋值。

全局变量

SQL server中的所有全局变量都使用两个@标志作为前缀：

变量	含义
@@error	最后一个T-SQL错误的错误号
@@identity	最后一次插入的标识值
@@language	当前使用的语言名称
@@max_connections	可以创建同时连接的最大数目
@@rowcount	受上一个SQL语句影响的行数
@@servername	本地服务器的名称
@@servicename	该计算机上的SQL服务的名称
@@timeticks	当前计算机上每刻度的微秒数
@@transcount	当前连接打开的事务数
@@version	SQL server的版本信息

2.2输出语句.

Print局部变量或字符串,以字符串形式打印数据 。

Select 局部变量AS自定义列名，以表格形式打印数据。

2.3逻辑控制语句.

2.3.1 .If-else

If(表达式)

Begin

语句1

语句2

End

Else

Begin

语句1

语句2

End

2.3.2 .case

Case

   When 条件一 then 结果二

   When 条件二 then 结果二

End

三．高级查询

3.1子查询.

Select … from 表1 where 字段1>(子查询)

3.2IN 和NOT IN子查询

Select … from 表1 where 字段一 NOT IN (子查询)

Select … from 表2 where 字段二 IN (子查询)

3.3Exists和Not Exists子查询

If exists(子查询)

语句

If not exists(子查询)

语句

四．事务，索引和视图.

4.1事务

²        开始事务:begin transaction

²        提交事务:commit transaction

²        回滚事务:rollback transaction

事务分类

²        显式事务:用begin transaction 明确指定事务的开始。

²        隐性事务:通过设置set implicit_transactions on语句,将陷性事务模式设置为打开。当以隐性事务操作时，SQL server将在提交或回滚事务后扑克动启动新事务。无法描述事务的开始，只需提交或回滚每个事务.

²        自动提交事务:这是SQL server的默认模式，它将每条单独的T-SQL语句视为一个事务。如果成功执行，则自动提交。如果错误，则自动回滚。

示例:

Begin transaction--开始事务

Declare @errorsum int

Set @errorsum=0

Update ……………………….

Set @errorsum=@errorsum+@@error

Update………………………..

Set @errorsum=@errorsum+@@error

If @errorsum<>0

Begin

   Rollback transaction--回滚事务

End

Else

Begin

   Commit transaction--提交事务

End

Go

4.2索引

索引是SQL server编排数据的内部方法。它为SQL server提供一种方法来编排查询数据的路由

索引页：

数据库中的存储索引的数据页。索引页存放检索数据行的关键字页以及该数据行的地址指针。索引页类似于汉语字典中按拼音或笔画排序的目录页。

唯一索引：

唯一索引不允许两行具有相同的索引值.

主键索引：

在数据库关系图中为表定义一个主键将自动创建主键索引，主键索引是唯一索引的特殊类型。主键索引要求主键中的每个值是唯一的。当在查询中使用主键索引时，它还允许快速访问数据。

聚集索引：

在聚集索引中，表中各行的物理顺序与键值的逻辑（索引）顺序相同。表只能包含一个聚集索引。

创建索引

If exists (select name from sysindexes where name=’myindex’)

Drop index 表名.myindex

Create nonclustered index myindex

on

student(id) with fillfactor=30

go

myindex为索引名,with fillfactor=30,指定填充因子为30%

使用索引

Select * from stumarks (myindex) where writtenExam between 60 and 90

Stumarks为表名,myindex为索引名,writtenexam为列名,between 60 and 90 指定查询出writtenexam字段60至90之间的值

建立索引的条件

Ø         该列用于频繁搜索

Ø         该列用于对数据进行排序

禁止使用索引的情况

Ø         列中仅包含几个不同的值

Ø         表中数据仅包含几行,为小型表创建索引可能不太划算，因为SQL server在索引中搜索数据所花的时间比在表中逐行搜索所花的时间更长

4.3视图

视图的用处

Ø         筛选表中的行

Ø         防止未经许可的用户访问敏感数据

Ø         降低数据库的复杂程度

Ø         将多个物理数据表抽象为一个逻辑数据表

使用视图的好处

ü         对最终用户的好处

l         结果更容易理解。创建视图时，可以将列名改为有意义的名称，使用户更容易理解列所代表的内容。在视图中修改列名不会影响基表的列名

l         获得数据更容易。很多人对SQL不太了解，因此对他们来说创建对多个表的复杂查询很困难。因而可以通过创建视图来方便用户访问多个表中的数据。

ü         对开发人员的好处

l         限制数据检索更容易。开发人员有时需要隐藏某些行或列中的信息。通过使用视图，用户可以灵活地访问他们需要的数据，同时保证同一个表或其他表的其他数据库的安全性。要实现这一目标，可以在创建视图时将要对用户保密码的列排除在外。

l         维护应用程序更方便。调试视图比调试查询更容易。跟踪视图中过程的各个步骤中的错误更为容易，这是因为所有的步骤都是视图的组成部分。

创建视图

If exists (select * from sysobjects where name-‘myview’)

Drop view myview

Go

Create view myview

As

Select 姓名=stuName,学员=sutInfo from stuInfo left join stuMarks

on stuInfo.stuNO=stuMarks.stuNo

Go

使用视图

Select * from myview

五．存储过程

5．1系统存储过程

常用系统存储过程

Sp_datadases        列出服务器上的所有数据库

Sp_helpdb          报告有关指定数据库或所有数据库的信息

Sp_renamedb        更改数据库的名称

Sp_tables           返回当前环境下可查询的对象的列表

Sp_columns         返回某个表列的信息

Sp_help            查看某个表的所有信息

Sq_helpconstraint    查看某个表的约束

Sq_helpindex        查看某个表的索引

Sq_stored_procedures 列出当前环境中的所有存储过程

Sp_password        添加或修改登录帐户的密码

Sp_helptext         显示默认值、未加密的存储过程、用户定义的存储过程、触发器或视图的实际文本

5．2自定义存储过程

Ø         不带参存储过程

Ø         带输入参数的存储过程

Ø         带输出参数的存储过程

5．2．1不带参数的存储过程

Create proc procedureName

As

  SQL 语句

Go

调用语法

Exec procedureName

5．2．2带输入参数的存储过程

Create proc procedureName

@number int =默认值,

@n varchar(20)

As

  SQL 语句

Go

调用语法:

Exec procedureName 200,’lyh’

Exec procedureName @n=’lyh’

5．2．3带输出参数的存储过程

Create proc procedureName

@number int output,

@name char(20)

As

  SQL 语句

  Set @number=1000

Go

调用语法

Declare @dd int

Exec procedureName @dd output,’lyh’

六.SQL server触发器

什么是触发器:

  触发器是在对表进行插入，更新或删除操作时自动执行的存储过程。

触发器的类别

INSERT触发器：当向表中插入数据时触发，自动执行触发器所定义的SQL语句。

UPDATE触发器：当更新表中某列、多列时触发，自动执行触发器所定义的SQL语句。

DELETE触发器：当删除表中记录时触发，自动执行触发器所定义的SQL语句。

Deleted表：用于存储DELETE和UPDATE语句所影响的行的副本，即在DELETED表中临时保存了被删除或被更新前的记录行。在执行DELETE或UPDATE语句 ，行从触发器表中删除，并传输到DELETED表中。由此我们可以从DELETED表中检查删除的数据行是否能删除。如果不能，就可以回滚撤销此操作，因为触发器本身就是一个特殊的事务单元。

Inserted表：用于存储INSERT和UPDATE语句所影响的行的副本，即在inserted表中临时保存了被插入或被更新后的记录行。在执行INSERT或UPDATE语句时，新加行被同时添加到INSERT表和触发器表中。由此我们可以从INSERTED检查插入数据是否满足业务需求。如果不满足，就可以向用户报告错误消息，并回滚撤销操作。

定义触发器

Create trigger trigger_name

On tablae_name

[with encryption]

For(insert,update,delete)

As

SQL 语句

Go

Trigger_name:是触发器的名称。触发器名称必须符合标识符规则，并且在数据库中必须唯一，。可以选择是否指定触发器所有者名称。

Table_name:是在其上执行触发器的表或视图

With encryption：加密syscomments表中包含create trigger语句文本的条目。使用with encryption可防止将触发器作为SQLserver复制的部分发布.

Create trigger：必须是批处理中的第一条语句，并且只能应用到一个表中。

触发器只能在当前的数据库中创建，不过触发器可以引用当前数据库的外部对象。