创建通用存储过程：
删除
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