學習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
</div>來自：http://www.cnblogs.com/zhangning/archive/2012/02/02/Hard_Ning.html
jopen 
hubeinana