SQLserver高级编程

1、数据库设计

if exists(select name from sysobjects where name = ‘stuInfo’)
drop table stuInfo
go
create table stuInfo
(
stuName varchar(20) not null, –学员名称
stuNo char(6) not null, –学号
stuSex char(2) not null, –性别
stuAge int not null, –年龄
stuID numeric(18,0) not null,
–身份证号,numeric(18,0)表示18位数字,小数位数为0
stuSeat smallint identity(1,1),
–座位号:自动编号(标识列),从1开始自增
stuAddr text –住址
)
go

数据库设计的重要性:

减少冗余,提高性能、易维护

alter table stuInfo add constraint PK_stuID primary key(stuID);
alter table stuInfo add constraint UQ_stuSeat unique(stuSeat);
alter table stuInfo add constraint CK_stuSex check(stuSex

数据库设计的步骤:

1、收集信息、标识对象、标识属性、标识关系(一对一、一对多、多对一、多对多)

in(‘男’,’女’));

if exists(select name from sysobjects where name=’stuMarks’)
drop table stuMarks
go
create table stuMarks
(
examNo char(7) not null, –考号
stuNo char(6) not null, –学号
writtenExam int not null, –笔试成绩
labExam int not null –机试成绩
)
go

alter table stuMarks add constraint PK_examNo primary key(examNo);

E-R图:

属性:定义实体的性质、实体的特征

实体:数据项(属性)的集合

关联:实体之间相互连接的方式

网赌平台哪个信誉好 1

简单理解一下就可以了

网赌平台哪个信誉好 2

alter table stuMarks add constraint UQ_stuNo unique(stuNo);

select * from stuInfo;
insert into stuInfo
values(‘张秋丽’,’s25301′,’女’,18,412724198603151234,’北京海淀’);
insert into stuInfo
values(‘李斯文’,’s25303′,’男’,22,412724198603161234,’河南洛阳’);
insert into stuInfo
values(‘李文才’,’s25302′,’男’,31,412724198603171234,’陕西西安’);
insert into stuInfo
values(‘欧阳俊’,’s25304′,’男’,28,412724198603181234,’新疆喀什’);
insert into stuInfo
values(‘欧阳丹’,’s25305′,’女’,23,412724198603191234,’新疆库车’);

select * from stuMarks;
insert into stuMarks values(‘s27811′,’s25303’,80,58);
insert into stuMarks values(‘s27813′,’s25302’,50,90);
insert into stuMarks values(‘s27815′,’s25301’,65,0);
insert into stuMarks values(‘s27816′,’s25304’,77,82);
insert into stuMarks values(‘s27817′,’s25305’,70,92);


–局部变量声明/赋值/查询
declare @stuName varchar(20) –声明
set @stuName = ‘李文才’ –赋值@stuName
select * from stuInfo where stuName=@stuName; –查询
declare @stuSeat int
select @stuSeat = stuSeat from stuInfo where stuName=@stuName
–赋值@stuSeat
select * from stuInfo where (stuSeat = @stuSeat + 1) or (stuSeat =
@stuSeat – 1) –查询

数据库规范化:

go

–IF···ELSE···
–案例1:如果笔试成绩平均分大于70优秀,否则较差
declare @avgWrt float
select @avgWrt=AVG(writtenExam) from stuMarks
if(@avgWrt > 70)
begin
print ‘成绩优秀,平均成绩:’ + convert(varchar, @avgWrt)
select top 3 * from stuMarks order by writtenExam desc
end
else
begin
print ‘成绩较差,平均成绩:’ + convert(varchar, @avgWrt)
select top 3 * from stuMarks order by writtenExam asc

第一范式(1NF):

每列都应该是原子性的,五重复的域

end

–案例2:如果机试成绩平均分大于40优秀,否则较差
declare @avgLab float
select @avgLab=AVG(labExam) from stuMarks
if(@avgLab > 40)
begin
print ‘成绩优秀,平均成绩:’ + convert(varchar, @avgLab)
select max(labExam) from stuMarks
end
else
begin
print ‘成绩较差,平均成绩:’ + convert(varchar, @avgLab)
select min(labExam) from stuMarks
end

第二范式(2NF):

在第一范式的基础上属性完全依赖于主键

go

–while循环语句,可以使用 break 退出所在层的循环,continue
退出当次循环
select * from stuMarks where labExam<60
–案例1:labExam低于60分的,循环加到60分,每次加2分
declare @n int
while(1 = 1)
begin
select @n=count(*) from stuMarks where labExam < 60
if(@n > 0)
begin
update stuMarks set labExam = labExam + 2 where labExam < 60
end
else
begin
break
end
end
select * from stuMarks

第三范式(3NF):

第三范式要求各列与主键列直接相关

–update stuMarks set labExam=50 where labExam=60

select * from stuMarks where writtenExam<60
–案例2:writtenExam低于60分的,循环加到60分,每次加2分
declare @m int
while(1=1)
begin
select @m=count(*) from stuMarks where writtenExam < 60
if(@m > 0)
begin
update stuMarks set writtenExam = writtenExam + 2 where writtenExam <
60
end
else
break;
end
select * from stuMarks


–case多分支语句
–select col1,col2 =
— case
— when···col2 = condition1···then···
— when···col2 = condition2···then···
— else···
— end
–from tabName
select stuNo,writtenExam =
case
when writtenExam >= 90 then ‘A’
when writtenExam between 80 and 89 then ‘B’
when writtenExam between 70 and 79 then ‘C’
when writtenExam between 60 and 69 then ‘D’
else ‘E’
end
from stuMarks

T-SQL语句创建和管理数据库和表:

select * from stuMarks

select stuName,stuAge =
case
when stuAge >= 30 then ‘old’
when stuAge between 20 and 29 then ‘strong’
when stuAge between 10 and 19 then ‘young’
else ‘baby’
end
from stuInfo
select * from stuInfo


if exists(select name from sysobjects where name = ‘bankCounter’)
drop table bankCounter
go
create table bankCounter
(
cstName varchar(32) not null, –顾客姓名
countNo numeric(21,0) not null, –账户
curMoney money, –账户现金余额
time_stamp datetime –时间戳
)
go
alter table bankCounter add constraint PK_countNo primary
key(countNo);
alter table bankCounter add constraint CK_curMoney check(curMoney >
0) ;
go
insert into bankCounter
values(‘张三’,400006666668888889999,1000,’2012-08-09′);
insert into bankCounter
values(‘李四’,400006666668888889998,1001,’2013-08-06′);
go

T-SQL创建数据库:

if DB_ID('数据库名') is not null
drop database 数据库名
go
create database 数据库名
on
(
        name='数据库名',
        filename='物理数据库储存路径数据库文件'
)

select * from bankCounter

案例:

if DB_ID('Student')is not null
drop databese Student
go
create databese Student
on
(
    name='Student',
    finema='E:\第二学期\SQL\stuDB\Student.mdf'
)

 

数据文件参数 描述
name 数据库逻辑名称
filename 数据库物理文件名
size 数据文件初始化大小,单位默认为M
maxsize 数据文件可增长到最大值,单位默认阿M,不指定即无限大
filegrowth 数据库每次增长率,可以是百分比,默认单位M,0不增长

–事务处理:一种机制,一个操作序列,它将一组数据库操作指令作为一个整体执行,并一起向系统提交或者撤销,即

这组指令要不全部执行,要不全部不执行,有任何一个错误就要立刻回滚/撤销所有操作。
–begin transaction –开始事务
–commit transaction –提交事务
–rollback transaction –回滚(撤销)事务

–案例:在此例中,张三转账1000给李四,但因check约束curMoney必须大于零,报错,所以update(张三)的现金值没变,
— 但是,李四的现金却增多了1000
use StudentDemoDB
go
update bankCounter set curMoney = curMoney – 1000 where
cstName=’张三’;
update bankCounter set curMoney = curMoney + 1000 where
cstName=’李四’;
go
–还原数据
select * from bankCounter
update bankCounter set curMoney = curMoney – 1000 where cstName=’李四’;

————-用事务处理
begin transaction
use StudentDemoDB
go
declare @errorT int –定义局部变量记录错误次数
set @errorT = 0 –初始化
update bankCounter set curMoney = curMoney – 1000 where
cstName=’张三’;
set @errorT = @errorT + @@error
update bankCounter set curMoney = curMoney + 1000 where
cstName=’李四’;
set @errorT = @errorT + @@error

if(@errorT > 0)
begin
print ‘错误编码 #’ + convert(varchar,@errorT) + ‘!’
rollback transaction
end
else
begin
commit transaction
end
go

T-SQL语句创建表:

if object_ID('表名')is not null
drop table 表名
go
create table 表名
(
      字段1 数据类型 列的特性,
      字段2 数据类型 列的特性
)

select * from bankCounter

begin transaction
declare @error int
set @error=0
update bankCounter set curMoney = curMoney – 800 where cstName =
‘张三’
set @error = @error + @@ERROR
update bankCounter set curMoney = curMoney + 800 where cstName =
‘李四’
set @error = @error + @@ERROR

if(@error > 0)
begin
rollback transaction
end
else
begin
commit transaction
end
go
select * from bankCounter
go


–索引
–create [unique] [clustered|nonclustered] index index_name
–on table_name (column)
–[with
— fillfactor = x
–]
–说明
–unique:执行唯一索引,可选
–clustered/nonclustered:指定聚集索引还是非聚集索引,可选
–fillfactor:表示填充因子,制定一个0-100的值,该值指示索引页填满的空间所占的百分比

–案例1:成绩表stuMark中的writtenExam列经常被查询,为了加快查询速度,现在创建索引。因为成绩可能会重复,
–因此索引只能创建 非聚集索引。
use StudentDemoDB
go
if exists(select name from sysindexes where name =
‘IX_stuMarks_writtenExam’)
drop index stuMarks.IX_stuMarks_writtenExam
go
create nonclustered index IX_stuMarks_writtenExam
on stuMarks(writtenExam)
with fillfactor=30
go


–视图
–create view view_name
–as
–<select语句>
–案例:stuInfo/stuMarks的视图
if exists(select name from sysobjects where name=’view_stuMarksInfo’)
drop view view_stuMarksInfo
go
create view view_stuMarksInfo
as
select
a.stuName,a.stuNo,b.examNo,a.stuSex,a.stuAge,b.writtenExam,b.labExam
网赌平台哪个信誉好,from stuInfo a,stuMarks b where a.stuNo = b.stuNo
go

案例:

if object_ID('StuInfo')is not null
drop table StuInfo
go
create table StuInfo
(
      StuId int identity(1,1) primary key,
      StuName varchar(10) not null,
      StuSex varchar(2) not null,
      StuAge varchar(3) not null
)

select * from view_stuMarksInfo

–存储过程

–系统存储过程
exec sp_databases –列出服务器上所有的的数据库
exec sp_helpdb –列出所有数据库的信息
exec sp_helpdb StudentDemoDB –列出指定数据库的信息
exec sp_renamedb ‘A’,’B’ –将数据A的名称改为B
exec sp_tables –列出当前环境下可查询的对象的列表

exec sp_columns stuInfo –列出指定表的的所有列的信息
exec sp_help –列出当前环境下所有的对象信息(表名/约束名/视图)
exec sp_help stuInfo –返回指定表的所有信息(表名/字段/约束/)
exec sp_helpconstraint stuInfo –返回指定表的约束信息
exec sp_helpindex stuInfo –返回指定表的索引信息
exec sp_stored_procedures –返回当前环境中的所有存储过程
exec sp_helptext ‘view_stuMarksInfo’

You can leave a response, or trackback from your own site.

Leave a Reply

网站地图xml地图