SQL Server 高级应用收藏版

发布时间：2020-05-24 00:57:53 所属栏目：MsSql 来源：互联网

导读：SQL Server 高级应用收藏版

感兴趣的小伙伴，下面一起跟随脚本之家 jb51.cc的小编两巴掌来看看吧！

一. 建库,建表,加约束.
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,--指定日志物理文件最大容量,可选 
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,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中的所有全局变量都使用两个@标志作为前缀：

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带输入参数的存储过程

（编辑：安卓应用网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!

SQL Server 高级应用 收藏版

SQL Server 高级应用收藏版