SQL-Advance

》打开外围配置代码

--让sqlserver可以执行'xp_cmdshell'
EXEC sp_configure 'show advanced options', 1
GO
-- To update the currently configured value for advanced options.
RECONFIGURE
GO
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1
GO
-- To update the currently configured value for this feature.
RECONFIGURE
GO

》调用Dos命令创建文件夹
exec xp_cmdshell 'mkdir(md) 本地磁盘:\\文件夹'
》调用Dos命令查看文件夹
exec xp_cmdshell 'dir 本地磁盘:\\文件夹'

》建立数据库

--在数据库（master）中检查要建立数据库是否存在
use master
go
if exists (select * from sysdatabases where name ='数据库名')
drop database 数据库名
--建立数据库
create database 数据库名

on primary --主数据文件
(
name= '数据库名_data', --数据库的物理名
fileName='本地磁盘:\\文件夹\数据库名_data.mdf',--数据的逻辑名
size = 3mb,--数据库起始大小
filegrowth = 10%--数据的增长率
)
,--次数据库文件
(
--数据库的物理名
name= '数据库名1_data',
--数据的逻辑名
fileName='本地磁盘:\\文件夹\数据库名_data.ndf',
--数据库起始大小
size = 3mb,
--数据的增长
filegrowth = 10%,
)
log on
(
name= '数据库名_log', --数据库的物理名
fileName='本地磁盘:\\文件夹\数据库名_data.ldf',--数据的逻辑名
size = 3mb,--数据库起始大小
filegrowth = 10%,--数据的增长率
)
go


--设置是否显示影响行数
set nocount on

--切换数据库
use 数据库名
GO
--在(sysobjects)中查找是否存在表(shuInfo)
if exists(select * from sysobjects where name = '表名')
drop table 表名

go
--建立表

create table 表名
(
--列名 数据类型 特征（如非空，约束）
stuName varchar(20) not null, -- 学生姓名 
stuNo varchar(20) not null, --学生学好可在后面直接建立约束  如（stuNo varchar(20) primary key check(stuNo like 's253__')  ）
stuSex varchar(1) not null ,--学生性别
stuAge smallint not null, -- 年龄
stuSeat int identity(1,1) not null, -- 学生座位   可在后面直接建立约束  如（stuSeat int identity(1,1) ）
stuAddress varchar(50) --学生住址 ， 可在后面直接建立约束  如（stuAddress varchar(50)  default('住址不详') ）
)
go
---建约束

--主键约束
alter table stuInfo
  add constraint pk_stuNo  primary key (stuNo)
--检查约束
alter table stuInfo
add constraint ck_stuAge check(stuAge between 15 and 40)
--默认约束
alter table stuInfo
add constraint df_stuAddress default('住址不详') for stuAddress
--唯一约束
alter table stuInfo
  add constraint uq_stuNo unique (stuNo)
--外键约束....假设stuMarks（学员成绩表）表中stuNo 引用 主表stuInfo表stuNo
alter table stuMarks
add constraint fk_stuNo foreign key (stuNo) references stuInfo(stuNo)

/*删除constraint语法*/
alter table stuInfo
  drop constraint <约束名称>
/* 建立用户*/
--1
use master
go
exec sp_addlogin '账号','密码' --sql用户 ,在系统数据库下建立
--2
use stuDB
go
exec sp_grantdbaccess '账号','数据库用户'--建立数据库用户，在要操作的数据库下建立
--3
grant 权限 on stuInfo to 数据库用户 --分贝权限


--删除登录用户
1-window exec sp_revokelogin
2-Sql    exec sp_droplogin
--删除数据库用户
revokedbaccess


--给表中添加新列：
alter table 表名
add 列名 数据类型
--删除列：
alter table 表名
drop column 列名

注意：查找的数值必须转换为字符串输出
convert(varchar(字符串长度),变量)

/*局部变量*/
--声明变量
declare @变量名 类型

--变量赋值
set @变量名 = 值 -- 静态

select @变量名=列 from  stuInfo where stuNo='S25300' -- 动态赋值（保证查询的结果只有一个值不然结果不准确）

/*全局变量*/
@@error  -- sql 的错误号码
@@identity --sql 最后插入的标识列
@@language --sql 当前语言
@@max_connections --sql 可以创建的最大连接
@@servername --sql 本地服务器的名称
@@servicename --该计算机上的服务名称
@@timeticks --当前计算机上的每刻度的微妙数
@@trancount -- 当前打开事物数
@@version --sql 版本信息

--变量叠加：
declare @sum int
declare @count int
set @sum=0
set @count=0
while(1=1)
begin
set @count=@count+1
set @sum=@sum+@count
if(@count>=10)
break
end
print 'sum:='+Convert(varchar(20),@sum)


/*if——else*/--条件结构
if (条件)
begin
--语句1
--语句2
end
else
begin
--语句1
--语句2
end


/*while循环*/
while(条件)
begin
--语句
end

/*case语句分支*/
case
when 条件1 then 结果1
when 条件2 then 结果2
when 条件3 then 结果3
else 结果
end

--事务的属性-ACID
》A—atomicity   原子性
》C—consistency 一致性
》I—isolation   隔离性         
》D—durability  永久性

调试(逐步)：print 变量名(输出变量值)

--创建事务：
》开始事务-begin transaction
》提交事务-commit transaction
》回滚(撤销)事务-rollback transaction
--（sql中有三种事务 1.显示事务 2.隐士事务 3.自动事务）
--在t-sql开始前开启事务
begin transaction --开始事务

--使用@@error跟踪错误号码，判断是否有错误 如果有就回滚事务，没有则提交事务
commit transaction --提交事务
rollback transaction --回滚事务


set implicit_transactions on --开启隐士事务
set implicit_transactions off --关闭隐士事务

--唯一索引
create unique index_new on stuInfo(id)  with(fillfactor = 30)           --with为可选 其中fillfactor为填充因子数子为百分比
--非聚集索引
create nonclustered index_new on stuInfo (id) with (fillfactor = 30)--with为可选 其中fillfactor为填充因子数子为百分比
--聚集索引
create clustered index_new on stuInfo (id) with (fillfactor = 30)--with为可选 其中fillfactor为填充因子数子为百分比

》创建索引：
if exists(select * from sysindexes where name='ix_**)
drop index 表名.ix_**
create [unique][clustered|nonclustered]index index_name
on table _name (column_name[,column_name]...)
[with fillfactor=x]
--fillfactor:填充因子  X:为1-100的值
*****按索引查询
select * from 表名 with(index=索引名) [where<条件>]
*****创建视图：
if exists(select * from sysobjects where name='view_name')
drop view view_name
create view view_name  as   <select语句>
*****使用视图查询
select * from 视图名

/*存储方法*/ 可有返回值  返回值只能是int型数据且只有1个（返回值和输出参数的区别）
if exists (select *from sysobjects where name = 'proc_new')
drop procedure proc_new
go

create procedure proc_new
--输出参数（@count int=0 output, ）
--输入参数（@stuName varchar(20) ,@stuid int ）
as
--sql语句
go


exec proc_new  --传出参数是可显示传参 ， 可隐士传参 ，output参数传入是后面跟上output-在调用前声明变量