学习MS-SQL Sever 的一些笔记总结

创建通用存储过程：

删除

Create PROCEDURE [dbo].[deltable]

    @tabelname nvarchar(100),

@condition nvarchar(200)

AS

BEGIN

declare @sql nvarchar(500)

set @sql='delete from '+@tabelname+' where '+@condition

exec(@sql)

END

查询

Create PROCEDURE [dbo].[seltable]

@tablename nvarchar(100),

@cols nvarchar(100),

@condition nvarchar(200)

AS

BEGIN

declare @sql nvarchar(200)

set @sql='select '+@cols+' from '+@tablename+' '+@condition

exec(@sql) END

修改

Create PROCEDURE [dbo].[uptable]

 @tablename nvarchar(100),

 @cols nvarchar(100),

 @condition nvarchar(100)

 as

BEGIN

declare @sql nvarchar(500)

set @sql='update '+@tablename +' set '+@cols +' where '+@condition

exec(@sql)

 

End

 

-----------------------------------------------------------------------------------------------

 

某些聚合函数：

函数stdevp(运算式);返回运算式中所有数据的总体样本标准差；

Stdev():返回所有数据的总体总体标准差

 运算式可为：字段名，运算式，函数

--------------------------------------------------------------------------------------

 

建表三范式：

一、列的原子性。

二、表的事件唯一性。就是表讲同一件事情。

三、表内字段与主键有关联。

 

sql = "SELECT @@Identity FROM Users";  // 查询新增加的记录的标识号

 

-----------------------------------------------------------------------------------------------

 

建库

CREATE DATABASE TEST 

ON PRIMARY 

(

NAME='TESTDB_DATA',--主数据文件的逻辑名

FILENAME='H:\Tset_Data\TESTDB_DATA.mdf',--主数据文件的物理名

SIZE=5MB,--主数据文件的初始大小

MAXSIZE=100MB,--主数据文件增长的最大值

FILEGROWTH=15%--主数据文件的增长率。

)

LOG ON 

(

NAME='TEST_LOG', --主数据文件的逻辑名

FILENAME='H:\Test_Data\TEST_LOG.ldf', --主数据文件的物理名

SIZE=2MB, --主数据文件的初始大小

FILEGROWTH=1MB--主数据文件的增长率。

)

GO

 

有次数据文件的创建：

CREATE DATABASE EM

ON PRIMARY

(

NAME='EM_DATA',

FILENAME='H:\Test_Data\EM_DATA.MDF',

SIZE=10,

MAXSIZE=100,

FILEGROWTH=1

),

(

NAME='EM_DATA2',

FILENAME='H:\Test_Data\EM_DATA2.NDF',

SIZE=10,

MAXSIZE=100,

FILEGROWTH=1

)

LOG ON

(

NAME='EM_LOG',

FILENAME='H:\Test_Data\EM_LOG.LDF',

SIZE=10,

FILEGROWTH=10%

),

(

NAME='EM_LOG2',

FILENAME='H:\Test_Data\EM_LOG2.LDF',

SIZE=10,

FILEGROWTH=10%

)

GO

 

-----------------------------------------------------------------------------------------------

 

检查是否已经存在已知数据库：

use master

go

if EXISTS(select * from sysdatabases where name = 'EM')

drop database EM   --存在就删除。

 

---------------------------------------------------------------------------------------------

 

建表：

use TEST

if exists(select * from sysobjects where name='base')

drop table base

if exists(select * from sysobjects where name='base2')

drop table base2

 

create table base2

(

id int 

primary key(id)

)

create table base

(

Id int identity(1,1),

name varchar(20),

sex char(2) default('男'),

stuID numeric(18,0), --  身份证号

primary key(Id),

check(sex='男' or sex='女'),

foreign key(Id) references  base2(id),

)

------------------------------------------------------------------------

------单独添加约束

alter table base2

add constraint key1 

primary key(id) --主键约束

alter table base2

add constraint key2 

unique(stuid) ---唯一约束

alter table base2

add constraint key3

default('成都') for adress  ----默认约束

alter table base2

add constraint key4

foreign key(id) references base(Id)  ---外键约束

alter table base2

add constraint key5

check(age between 12 and 30) --检查约束

go

 

-----------------------------------------------------------------------------------------------

 

删除约束

alter table base2

drop constraint FK_defaultadress

 

----------------------------------------------------------------------------------------------- 

 

增加登陆账户

Widows:

Exec sp_grantlogin ‘yuming\zhanghu’

SQL sever 

exec sp_addlogin 'ning','12345'

exec sp_addlogin 'ning','12345','TEST'

 

----------------------------------------------------------------------------------------------- 

 

创建数据库用户

Exec sp_grantdbaccess ‘登陆账户名’,’数据库用户名’

数据库用户名可以不写，默认为登陆账户，即数据库用户默认和嘟噜账户同名。

 

Guest账户

可以访问没有设定数据库用户名的数据库。

 

分配权限

 Grant select,insert,update ON stuinfo TO zhangsan

Grant create table TO zhangsan

 

 

//分支语句

select writenExam,级别=

 case

 when writenExam<60 then '不及格'

 when writenExam>=85 then '优秀'

 when writenExam between 60 and 70 then '及格'

 when writenExam between 71 and 84 then '良好'

 end

 from chengji

 

//根据每个人的平均成绩分等级

select writenExam,labExam,pingjun = (writenExam+LabExam)/2 ,

 级别=

 case

 when (writenExam+LabExam)/2<60 then '不及格'

 when (writenExam+LabExam)/2>=85 then '优秀'

 when (writenExam+LabExam)/2 between 60 and 70 then '及格'

 when (writenExam+LabExam)/2 between 71 and 84 then '良好'

 end

 from chengji

 

 //建立中间临时表再分等级

 if exists(select * from sysobjects where name='test')

drop table test

select  writenExam,labExam,pingjun=(writenExam+LabExam)/2

into test

from chengji

select writenExam,labExam,pingjun ,

 级别=

 case

 when pingjun <60 then '不及格'

 when pingjun  >=85 then '优秀'

 when pingjun between 60 and 70 then '及格'

 when pingjun  between 71 and 84 then '良好'

 end

 from test

 

 

根据条件循环加分

declare @pinnjun int

select @pinnjun=AVG(LabExam) from chengji

 

while(@pinnjun<85)

begin

-- 根据LabExam分支加分

update chengji set LabExam=

case

when LabExam<60  then LabExam+5

when LabExam between 60 and 69 then LabExam+3

when LabExam between 70 and 79 then LabExam+2

when LabExam between 80 and 89 then LabExam+1

else LabExam

end

--更新平均成绩

select @pinnjun=AVG(LabExam) from chengji

if @pinnjun>85 break

end

go

 

----------------------------------------------------------------------------------------------

 

事务:

declare @error int

set @error=0

begin transaction up

update bank set currentMoney=currentMoney-99 where customerName='张三'

set @error=@error+@@ERROR

update bank set currentMoney=currentMoney+999 where customerName='李四'

set @error=@error+@@ERROR

if (@error<>0)

begin

print('提交失败')

rollback transaction up

end

else

begin

print('成功提交')

commit transaction up

end

select * from bank

 

----------------------------------------------------------------------------------------------- 

 

----创建索引

----fillfactor:该值指示索引页填满的空间所占的百分比.

----unique 唯索引

----clustered 聚集索引

----nonclustered 非聚集索引

create clustered index index_name

on test(labExam)

with fillfactor = 80

删除索引

drop index test.index_name

 

 ----------------------------------------------------------------------------------------------

 

----创建视图

if exists(select * from sysobjects where name = 'view2')

drop view view2

go

create view view2

as

select chengji.ExanNo as 学员编号,

       chengji.LabExam as 机试成绩 ,

       chengji.writenExam as 笔试成绩,

       (chengji.LabExam+chengji.writenExam)/2 as 平均成绩

from chengji

go

select * from view2