SQL server高级应用 收藏版
程序员文章站
2023-12-01 08:35:04
一. 建库,建表,加约束. 1.1建库 复制代码 代码如下: use master go if exists (select * from sysdatabases whe...
一. 建库,建表,加约束.
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中的所有全局变量都使用两个@标志作为前缀：
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：必须是批处理中的第一条语句，并且只能应用到一个表中。
触发器只能在当前的数据库中创建，不过触发器可以引用当前数据库的外部对象。
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：必须是批处理中的第一条语句，并且只能应用到一个表中。
触发器只能在当前的数据库中创建，不过触发器可以引用当前数据库的外部对象。
上一篇： MySql是否需要commit详解