学步园

--------------------创建文件包
exec sp_configure'show advanced options',1
RECONFIGURE
exec sp_configure'xp_cmdshell',1
RECONFIGURE
exec xp_cmdshell 'md d:\project'
go
use master 
go
--创建bbsDB数据库
if exists(select name from sysdatabases where name='bbsDB')
	drop database bbsDB
go
create database bbsDB
on
(
	name='bbsDB',
	filename='d:\project\bbsBD.mdf',
	size=10mb,
	filegrowth=15%
)
log on
(
	name='bbsDB_log',
	filename='d:\project\bbsBD_log.ldf',
	size=3mb,
	maxsize=20mb,
	filegrowth=15%
)
--创建 bbsUsers 表
use bbsDB
go
if exists(select name from sysobjects where name='bbsUsers')
	drop table bbsUsers
go
create table bbsUsers
(
	UID int identity(1,1) not null, --用户编号
	Uname varchar(15) not null,		--用户名
	Upassword varchar(10) not null,	--密码
	Uemail varchar(20),				--邮箱
	Usex bit	not null,			--性别
	Uclass int ,					--用户等级
	Uremark varchar(20),			--备注信息
	UregDate datetime  not null,	--注册日期
	Ustate int,						--状态
	Upoint int						--用户积分
)
go
--主键
use bbsDB
go
if exists(select name from sysobjects where name='PK_UID')
	alter table bbsUsers 
	drop constraint PK_UID
go
alter table bbsUsers
add constraint PK_UID primary key (UID)
go
--默认密码值  888888
if exists(select name from sysobjects where name='DF_Upassword')
	alter table bbsUsers
	drop constraint DF_Upassword
go
alter table bbsUsers
add constraint DF_Upassword default (888888) for Upassword 
go
--密码约束  必须大于6个字符
if exists(select name from sysobjects where name='CK_Upassword')
	alter table bbsUsers
	drop constraint CK_Upassword
go
alter table bbsUsers
add constraint CK_Upassword check(Len(Upassword)>=6)
GO

--默认日期  截取当前时间 getdate() 方法
if exists(select name from sysobjects where name='DF_UregDate')
	alter table bbsUsers
	drop constraint DF_UregDate
go
alter table bbsUsers
add constraint DF_UregDate default (getdate()) for UregDate
go
--邮箱数据约束
if exists(select name from sysobjects where name='CK_Uemail')
	alter table bbsUsers 
	drop constraint CK_Uemail
go
alter table bbsUsers
add constraint CK_Uemail  check (Uemail like '%@%')
go
--版块编号，外键，引用bbsSection 表的主键 SID
use bbsDB
if exists (select name from sysobjects where name='FK_TsID')
	alter table bbsToic
	drop constraint FK_TsID
go
alter table bbsTopic
add constraint FK_TsID foreign key (TsID) references bbsSection(SID)
go
--唯一约束
if exists (select name from sysobjects where name='UQ_stuID')
	alter table bbsToic
	drop constraint UQ_stuID
go
alert table bbsTopic
add constraint UQ_stuID unique (stuID)


------------------------------------------------------------------------
第一曲：添加SQL登录用户

	use master
	--go
	--if exists(select * from syslogins where name='zhouxuan')
	--	drop loin 'zhouxuan'
	--go
	exec  sp_addlogin 'zhouxuan' ,'密码'
	go

第二曲:分配指定数据库

	use 需要分配的数据库
	go 
	exec sp_grantdbaccess '分配的用户' [,'你的登录账号（必须有权限）'] 

第三曲：分配权限
 
	grant 权限 [on 表名] to 数据库用户
	权限：insert ,delete, update, select, create table
-------------------------------------------------------------
局部变量：
	声明：
		declare @name varchar(8)
		declare @seat int 
		不可   declare @name ,@seat int
	赋值：
		set @name=value
		select @name = name from . where.
		不可 +=
	两种赋值的区别：
		1.select 可以同时对多变量送至
		2.set 必须保证查询语句返回单行单列
		  select当结果中返回多行时，自动将最后一行此字段的知送给变量
		3.set当查询语句一行记录都没有返回，为变量送空值。
		  select 当查询语句一行记录都没有返回，保持原值
	declare @stuSeat int
	set @stuSeat =10
	select @stuSeat=stuseat from stuinfo where stuname='李文'
	print @stuSeat
	
全局变量
	
	@@error 最后一个T-SQL语句的错误号
	@@identity 最后一次插入的标示值
	@@language  当前使用的语言的名称
	@@max_connections 可以创建的同时连接的最大数目
	@@rowcount 受上一个SQL语句影响的行数
	@@servername 本地服务器名称
	@@servicename 该技术机上的sql服务的名称
	@@timeticks 当前技术机上每刻度的微秒数
	@@version sqlserver的版本信息
	set nocount [on\off ]不显示/显示  
	distinct 取消重复项  SELECT DISTINCT ProductID FROM Production.ProductInventory
	日期部分  	缩写  
	年份 		yy、yyyy 
 	季度 		qq、q 
 	月份 		mm、m 
 	每年的某一日 	dy、y 
 	日期 		dd、d 
 	星期 		wk、ww
	工作日		dw
	小时 		hh 
	分钟 		mi、n 
	秒 		ss、s 
 	毫秒 		ms 
	dateadd(dd,3,需要添加的时间)  添加时间   
	datediff(dd,当前时间,其他时间) 比较两个时间的间隔
	datepat(ss,getdate())   返回表示指定日期的指定日期部分的整数
输出语句：
	print 局部变量或字符串
	select 局部变量 as 自定义列名
	
控制语句：
	if(@sumError>0)
  		begin
		 	print'交易失败'
  		end
	else 
  		begin
    			print'交易成功'
 		end

	
	while(1=1)
	{
		begin
			if(@n>0)
				update
			else
				break
	}
	

	select 是否通过=case
			when writtenExam>=60 and LabExam>=60 then '通过'
			else '未通过'
			end
	from stuInfo where id=1
	
GO 关键字标志着批处理的结果
----------------------------------------------------------------------------------
数据转换
convert(varchar(6),@send)
convert(varchar(15),getdate(),111) 108 109 转换时间格式

-----------------------------------------------------------------------------------
子查询 in \not in
	select * from stuinfo where stuno in(select stuno stuMarks where writtenExam>60)

-----------------------------------------------------------------------------------
生成新表并保存数据
if exists(select * from sysobjects where name='newTable')
	drop table newTable
go
select ....into newTable ...
-------------------------------------------------------------------------------------
事务：
	事务是作为单个逻辑工作单元执行的一系列操作，要么都执行，要么都不执行
	
	4个属性：
		1.原子性：事务是一个完整的操作，事务的各种元素是不可分的。
		2.一致性：当事务完成时，数据处于一致状态
		3.隔离性：对数据进行修改的所以并发事务是彼此隔离的
		4.持久性：事务完成后，它对系统的影响是永久性的，该修改即使出现系统故障，也
			保持一致
	具体操作例子

	use bankDB
	go
	    begin tran --开始事务
		declare @sumError int
		set @sumError=0
		update bank set currentMoney=currentMoney+500 where customerName='张三'
		set @sumError=@sumError+@@error
		declare @ka char(10)
		select @ka=cardID from bank where customerName='张三'
		insert into transInfo(cardID, transType, transMoney, transDate)values(@ka,'存入',500,Getdate())
		set @sumError=@sumError+@@error
		if(@sumError>0)
  			begin
   			   	rollback tran --回滚事务
   	 			print'存钱失败，请确认输入无异常'
 	 		end
		else
  			begin
   	 			commit tran  --提交事务
    				print '存钱成功，请查询余额'
  	     end
	 go
  
	自动提交事务：这是SQLServer的默认模式，它将每单条的SQL语句为一个事务，如果执行成功，
		则自动提交，如果错误，则自动回滚
	
	set implicit transactions on/off 隐式事务


-------------------------------------------------------------------------------------------
索引：
	索引：是SQLServer编排数据的内部方法，为SQLServer提供一个方法来编排查询数据的路径
	索引的作用：使数据库程序无须对整个表进行是扫描，就可以在其中找到所需要的数据
	索引页:数据库中储存索引的数据页，索引页存放检索数据行的关键字页以及该数据行的地址指针
	唯一索引：唯一索引不允许两行具有相同的索引值
		提示：创建了唯一约束，将自动创建唯一索引，尽管唯一索引有助于找到信息，但为了
			获得聚佳性能，建议使用主键约束
	主键索引：主键，唯一标示
	索引分为：
		1、聚集索引：包括主键	
			表中各行的物理顺序与键值的逻辑（索引）顺序相同，表只能包含一个聚集索引
			例如：汉语字典默认按拼音排序，拼音字母a b c d e...z 就是索引的逻辑顺序
			      而页码1.2.3.4 就是物理顺序
		2、非聚集索引：
			表中各行的物理顺序与键值的逻辑顺序不匹配，聚集索引比非聚集索引有更快速
			的数据访问速度
			例如：按笔画排序的索引就是非聚集索引，"1"画的字对应的页码可能比"3"画的
			      字对应的页码大
	创建使用索引
	
	use stuDB
	go
	if exists(select name from sysindexes where name='IX_stuMarke_writtenExam')
		where name='IX_stuMarke_writtenExam'
		drop index stuMarks.IX_stuMarke_writtenExam   --删除索引
	go
	---------创建非聚集索引，填充因子30%
	create nonclustered index IX_stuMarke_writtenExam on stuMarks(writtenExam)
		with fillfactor=30    ---填充因子%
	go
	
	---------创建时可选择 是否唯一  是否为聚集非聚集
	[unique] [clustered | nonclustered]
	
	使用索引
	select * from stuMarks	with (index(IX_stuMarke_writtenExam)) 
		where writtenExam between 60 and 90 
----------------------------------------------------------------------------------------
SQL语句创建视图
	use studb
	go
	if exists(select * from sysobjects where name='view_name')
		drop view view_name
	go
	create view view_name
		as 
		<select 语句>
	go
	select * from view_name

--------------------------------------------------------------------------------------
常用的系统存储过程
	sp_databases	  列出服务器上的所有数据库
	sp_helpdb	  报告有关指定数据库或所有数据库的信息
	sp_renamedb	  更改数据库名称
	sp_tables 	  返回当前环境下可查询的对象的列表
	sp_defaultdb '用户','数据库'  登陆时显示的默认数据库
	sp_columns  	  返回某个表列的信息
	sp_help		  查找某个表的所有信息
	sp_helpconstraint 查看某个表的约束
	sp_helpindex   	  查看某个表的索引
	sp_stored_procedures 列出当前环境中的所有存储过程
	sp_password	  添加或修改登陆用户的密码
	sp_helptext	  显示默认值，未加密的存储过程，发器或视图的实际文件
	
	
	调用
	exec sp_databases	 ---列出服务器上的所有数据库
	exec sp_renamedb 'db1','db2' 	 ---把数据库名db1改为db2
	use  stuDB
	go
	exec sp_tables --返回当前环境下可查询的对象的列表
	exec sp_columns stuInfo  --查看表stuInfo中的列的信息
	exec sp_help  stuInfo	--查看表 stuInfo的所有信息
	exec sp_helpconstraint stuInfo  --查看表stuInfo的约束
	exec sp_helpindex stuMarks  ---查看表stuMarks的索引
	exec sp_helptext 'view_stuInfo_stuMarks'  --查看视图的语句文本
	exec sp_stored_procedures   --返回当前数据库中的存储过程列表
	调用DOS命令
	exec sp_configure'show advanced options',1
	RECONFIGURE
	exec sp_configure'xp_cmdshell',1
	RECONFIGURE
	exec xp_cmdshell 'md d:\project'
-----------------------------------------------------------------------------------------
存储过程：
	use stuDB
	go
	if exists(select * from sysobjects where name='proc_stu')
		drop procedure proc_stu
	go
	
	create procedure proc_stu
		@writtenpass int, --输入参数：考试及格线
		@labPass int,	--输入
		@name varchar(50) output --- 设置外面接收的参数
	   as 
		print '-----------------------'
		
	go
	
	---调用存储过程------------------------------
	declare @test varchar(50)
	exec proc_stu 60,55,@test output
	--或者
	exec proc_stu @labPass=55,@writtenpass=60，@name=@test output
	注意：
		1.初始化化参数为空就直接  ..=null
	          判断是否为空就  if .. is null
	     	2.注意传参
	参数
	
错误处理
	raiserror('及格线错误,请指定0-100之间的分数，统计退出',16,1)        1-16
	错误的严重级别大于10，将自动设置系统全局变量 @error 为非零