高级sql

/*cmd命令*/
exec xp_cmdshell 'md d:\bank' -- 创建bank文件夹
exec xp_cmdshell 'dir d:\bank' -- 查看bank文件夹
exec xp_cmdshell 'rd d:\bank' -- 删除bank文件夹

/*高级数据库操作*/
--建立数据库

--在数据库（master）中检查要建立数据库是否存在
use master
go
if exists (select * from sysdatabases where name ='stuNo')
begin
drop database stuNo
end
--建立stuNo数据库
create database stuNo
on primary --主数据文件
(
name= 'stuNo_data', --数据库的物理名
fileName='e:\stuNo_data.mdf',--数据的逻辑名
size = 3mb,--数据库起始大小
filegrowth = 10%--数据的增长率
)
,--次数据库文件
(
name= 'stuNo_datas', --数据库的物理名
fileName='e:\stuNo_datas.ndf',--数据的逻辑名
size = 3mb,--数据库起始大小
filegrowth = 10%,--数据的增长
)
log on
(
name= 'stuNo_datas', --数据库的物理名
fileName='e:\stuNo_datas.ldf',--数据的逻辑名
size = 3mb,--数据库起始大小
filegrowth = 10%,--数据的增长率
)
go


/*建立表*/

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

--建立表 stuInfo

create table stuInfo
(
--列名 数据类型 特征（如非空，约束）
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('住址不详') ）
)


--给表中添加列
/*alter table stuInfo
add 列名 类型 not null*/
--移除列
/*alter table stuInfo
drop column 列名*/


--从新给 stuInfo建立constraint(约束) 这样的好处是知道 constaritn 的名称

--主键约束
alter table stuInfo --改变表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 数据库用户 --分贝权限


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

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

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

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

/*输出语句*/
print '想说什么就说了' --静态

select '想说的话' as '列明' --动态


/*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 结果3
end


/*子查询*/

--子查询1
select*from stuInfo where id = (select Mid from stuMarks where 条件)
--子查询2（举例说明）
if exists (select *from sysdatabases where name = 'stuDB')
begin
drop database stuDB
end
go
create database stuDB
go

use stuDB
go
if exists (select *from sysobjects where name = 'stuInfo')
begin
drop table stuInfo
end
create table stuInfo
(
id int identity (1,1),
stuName varchar(20) not null,
stuAge int not null,
stuSex varchar(2) not null
)
go
if exists (select *from sysobjects where name = 'stuMarks')
begin
drop table stuMarks
end
go
create table stuMarks
(
Mid int not null,
WE int ,
LE int
)
go
insert stuInfo values('陈康',10,'女')
insert stuMarks values(1,90,90)

--思路（实例）
select 姓名=(select stuName from stuInfo where id = 1) ,mid from stuMarks


/*使用 exists 查询相关信息是否存在*/

--如
if exists (select * from sysdatabases where name = 'stuDB')
begin
drop database stuDB--查出数据则返回true
end

--使用 not exists 查询相关信息不存在
if not exists(select * from sysdatabases where name = 'stuDB')
begin
print '系统数据表中没有stuDB 数据库'
end
else
begin
drop database stuDB
end

/*使用 in 查询在什么范围内的数据*/
--一般使用在 子查询中
select * from stuInfo where id in (select *from stuMarks)
--使用 not in 查询不在什么范围内的数据
select * from stuInfo where id not in (select * from stuMarks)


/*事务*/ --（sql中有三种事务 1.显示事务 2.隐士事务 3.自动事务）
--在t-sql开始前开启事务
begin transaction --开始事务

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


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


/*索引*/
if exists(select * from sysindexes where name= 'index_new')
begin
drop index index_new
end
go

--唯一索引
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 sysobjects where name = 'view_new')
drop view view_new
go

create view view_new
as
sql查询语句 --（查询结果在一张表上）

/*存储方法*/ 可有返回值 返回值只能是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-在调用前声明变量