SQLserver高级编程,数据库基础编程

必赢365net手机版 2

1、数据库设计

 

数据库设计的重要性:

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

数据库设计的步骤:

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

 

E-R图:

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

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

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

必赢365net手机版 1

简单理解一下就可以了

必赢365net手机版 2

Ø Go批处理语句

数据库规范化:

用于同时执行多个语句

第一范式(1NF):

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

 

第二范式(2NF):

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

Ø 使用、切换数据库

第三范式(3NF):

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

use master
go

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

 

T-SQL创建数据库:

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

Ø 创建、删除数据库

案例:

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

 

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

方法1

T-SQL语句创建表:

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

–判断是否存在该数据库,存在就删除
if (exists (select * from sys.databases where name =
‘database_name’))
drop database database_name
go
–创建数据库,设置数据库文件、日志文件保存目录
create database database_name
on(
name = ‘database_name’,
filename = ‘c:datadatabase_name.mdf’
)
log on(
name = ‘database_name_log’,
filename = ‘c:datadatabase_name_log.ldf’
)
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
)

 

T-SQL创建约束:

主键约束:(primary key constraint):主键列数据唯一,并不为空,简称:PK

唯一约束:(unique constraint):保证该列不允许除夕重复值,简称:UQ

检查约束:(check
constraint):限制列中允许的取值以及多个列直接的关系,简称:CK

默认约束:(default constraint):设置某列的默认值,简称:DF

外键约束:(foreign key
constraint):用于在两个表之间建立关系,需要指定主从表,简称:FK

方法2(设置文件大小)、

T-SQL添加约束的语法格式:

alter table 表名

add constraint 约束名 约束类型
具体的约束说明

–判断是否存在该数据库,存在就删除
if (exists (select * from sys.databases where name =
‘database_name’))
drop database database_name
go
create database database_name
–默认就属于primary主文件组,可省略
on primary (
–数据文件的具体描述
name = ‘database_name_data’,        –主数据文件的逻辑名
fileName = ‘c:database_name_data.mdf’,     –主数据文件的物理名
size = 3MB,                  –主数据文件的初始大小
maxSize = 50MB,               –主数据文件增长的最大值
fileGrowth = 10%                –主数据文件的增长率
)
–日志文件的具体描述,各参数含义同上
log on (
name = ‘database_name_log’,
fileName = ‘c:database_name_log.ldf’,
size = 1MB,
fileGrowth = 1MB
)
go

T-SQL删除约束:

alter table 表名

drop constraint 约束名

 

案例:

--添加主键约束(将StuNo设为主键)
alter table StuInfo add constraint PK_StuNO primary key (StuNo)
go
--添加默认约束(性别默认为男)
alter table StuInfo Add constraint DF_StuSex DEFAULT ('男')for StuSex 
go
--添加检查约束(年龄必须为40之间)
alter table StuInfo Add constraint CK_StuAge check(StuAge>=18 and StuAge<=40)
go
--添加外键约束
alter table Exam Add constraint FK_StuNo FORELGN KEY (StuNo)references StuInfo(StuNo)
go

方法3(设置次数据文件)

(1)对表结构的操作

1、在表中添加一列

语法:alter table 表名

   add  添加的列名  数据类型

例子:在Student表中添加列Hobbies,类型为varchar,宽度:20

            alter  table  Student  

         add  Hobbies  varchar(20)

2、删除表中的一列

语法:alter  table  表名

      drop  column  列名

例子:删除Student表中的Hobbies列

      alter  table  Student

      drop  column  Hobbies

3、修改表中列的数据类型

语法:alter table 表名

      alter  column 列名  修改后的数据类型  not null

例子:修改Student中的Sex列为char型,宽度为2

      alter  table  Student

      alter column Sex char(2) nou null

if (exists (select * from sys.databases where name =
‘database_name’))
drop database database_name
go
create database database_name
–默认就属于primary主文件组,可省略
on primary (
–数据文件的具体描述
name = ‘database_name_data’, –主数据文件的逻辑名
fileName = ‘c:database_name_data.mdf’, –主数据文件的物理名
size = 3MB, –主数据文件的初始大小
maxSize = 50MB, –主数据文件增长的最大值
fileGrowth = 10% –主数据文件的增长率
),
–次数据文件的具体描述
(
–数据文件的具体描述
name = ‘database_name2_data’, –主数据文件的逻辑名
fileName = ‘c:database_name2_data.mdf’, –主数据文件的物理名
size = 2MB, –主数据文件的初始大小
maxSize = 50MB, –主数据文件增长的最大值
fileGrowth = 10% –主数据文件的增长率
)
–日志文件的具体描述,各参数含义同上
log on (
name = ‘database_name_log’,
fileName = ‘c:database_name_log.ldf’,
size = 1MB,
fileGrowth = 1MB
),
(
name = ‘database_name2_log’,
fileName = ‘c:database_name2_log.ldf’,
size = 1MB,
fileGrowth = 1MB
)
go

(2)添加约束

 1、添加主键约束
语法:alter  table 表名
              add  constraint   约束名  
 primary key(要设置主键的列名)
例子:
给Class表添加主键约束
if OBJECT_ID(‘PK_ClassId’) is not null
alter  table  Class
    drop  constraint  PK_ClassId
go
alter  table  Class
add  constraint  PK_ClassId  primary key(ClassId)

2、添加唯一约束
语法:alter table 表名
             add constraint 约束名 unique(要添加唯一约束的列名)
例子:
给信息表stuInfo中的姓名添加唯一约束
if OBJECT_ID(‘UQ_StuName’) is not null
alter  table  StuInfo
 drop  constraint  UQ_StuName
 go
 alter  table  StuInfo
 add  constraint  UQ_StuName
 unique(StuName)

3、添加默认约束
语法:alter table 表名
             add constraint  约束名  Default(默认值)  for 要添加默认值的列名
例子:
给stuInfo表中的Age列添加默认值为18
 if OBJECT_ID(‘DF_Age’) is not null
    alter  table  StuInfo
    drop  constraint  DF_Age
 go
 alter  table  stuInfo 
 add  constraint  DF_Age  Default(18) for Age

4、添加检查约束
语法:alter table 表名
           drop constraint 约束名
          check(列名>=0)
例子:
给笔试成绩添加一个约束,要求成绩必须在0-100之间
if OBJECT_ID(‘CK_WriteExam’) is not null
alter table Exam
drop constraint CK_WriteExam
go
alter table Exam
 add constraint CK_WriteExam
 check(WriteExam>=0 and WriteExam<=100)

5、外键约束
语法:alter table 表名1
       add constraint 约束名
       foreign key(外键约束名)
        references 表名2(外键约束名)
例子:
给班级表与学员信息表创建关系(外键约束)
if OBJECT_ID(‘FK_Class_StuInfo’) is not null
alter table  stuInfo
drop constraint Fk_Class_StuInfo
go
alter table stuInfo
add constraint Fk_Class_StuInfo
foreign key(ClassId)
references Class(ClassId)

–删除约束
Alter table 表名
Drop ConStraint 约束名

–删除表
Drop table 表名

 

 

(3)高级查询语法格式

–内连接
语法:
select 要查询的属性
from 表1 inner join 表2
on 表1.Id=表2.Id
where 要限制的条件(可以不要)

–左外连接
语法:
select 要查询的属性
from 表1 left outer join 表2
on 表1.id=表2.id

–右外连接
语法:
select 要查询的属性
from 表1 right outer join 表2
on 表1.id=表2.id

–全外连接
语法:
select 要查询的属性
from 表1 full outer join 表2
on 表1.id=表2.id

–交叉连接
语法:
select 要查询的属性
from 表1 Cross join 表2
where 条件

–自连接
select 要查询的属性
from 表1 , 表2
where 表1.id=表2.id

Ø 基本数据类型

(4)高级查询实例

if DB_ID(‘GoodSystem’) is not null
drop database GoodSystem
go
create database GoodSystem on –创建一个商品数据库
(
name=’GoodSystem’,
filename=’E:SQL第二章上机任务GoodSystem.mdf’
)
–打开数据库
use GoodSystem
–创建商品类型表GoodsType
if OBJECT_ID(‘GoodType’) is not null
drop table GoodType
go
create table GoodType
(
Tid int primary key,
Type varchar(20)
)
–创建商品信息表Goods
if OBJECT_ID(‘Goods’) is not null
drop table Goods
go
create table Goods
(
id int primary key,
Tid int ,
Name varchar(50),
Price money,
ProductionDate datetime,
Amount int
)
–给商品类型表GoodsType添加测试数据
insert GoodType select ‘1’,’家电’ union
select ‘2’,’电子’ union
select ‘3’,’食品’ union
select ‘4’,’生活用品’

–给商品信息表Goods添加测试数据
insert Goods select ‘1’,’1′,’冰箱’,’3344′,’2017-6-3′,’100′ union
select ‘2’,’1′,’电视’,’1777′,’2016-10-4′,’100′ union
select ‘3’,’1′,’微波炉’,’333′,’2017-2-26′,’100′ union
select ‘4’,’2′,’手机’,’4500′,’2017-5-7′,’100′ union
select ‘5’,’2′,’显示器’,’1777′,’2016-12-4′,’100′ union
select ‘6’,’2′,’主机’,’1500′,’2017-3-9′,’100′ union
select ‘7’,’3′,’老干妈’,’9′,’2017-7-6′,’100′ union
select ‘8’,’3′,’爽口榨菜’,’3.6′,’2017-6-8′,’100′

–查询商信息表中的商品名称,价钱,生产日期。
select Name 商品名称,Price 价钱,ProductionDate 生产日期
from Goods
–查询商品类型、商品名称、价钱、生产日期
select gt.Type 商品类型,gs.Name 商品名称 , gs.Price
商品价钱,gs.ProductionDate 生产日期
from GoodType gt inner join Goods gs
on gt.Tid=gs.Tid
–查询生产日期为2017的商品类型、商品名称、价钱、生产日期
select gt.Type 商品类型,gs.Name 商品名称 , gs.Price
价钱,gs.ProductionDate 生产日期
from GoodType gt inner join Goods gs
on gt.Tid=gs.Tid and year(gs.ProductionDate) like ‘2017%’
–查询商品类型为“电子”的商品类型、商品名称、价钱、生产日期,数据按价钱降序排列
select gt.Type 商品类型,gs.Name 商品名称,gs.Price 价钱
,gs.ProductionDate 生产日期
from GoodType gt inner join GoodS gs
on gt.Tid=gs.Tid and gt.Type=’电子’
order by Price desc

–统计每种商品类型的商品数量
select gt.Type 商品类型, COUNT(gs.Amount) 商品数量
from GoodType gt inner join Goods gs
on gt.Tid=gs.Tid
group by gt.Type

–查询所有商品类型对应的所有商品信息
select gt.Type 商品类型,gs.Name 商品名称 , gs.Price
商品价钱,gs.ProductionDate 生产日期
from GoodType gt left join Goods gs
on gt.Tid=gs.Tid

–查询价钱是333、1500、4500的商品信息
select Type 商品类型,Name 商品名称,Price 商品价钱,ProductionDate
生产日期
from GoodType gt ,Goods gs
where Price in(333,1500,4500)
and (gs.Tid=gt.Tid)

–查询没有对应商品信息的商品类型信息(类型编号,类型名称)
select Tid 编号,Type 类型 from GoodType
where Tid not in
(select Tid from Goods)

–查询所有商品的平均价钱
select AVG(Price) 平均价钱 from Goods

–查询价钱大于平均价钱的商品信息
select Type 商品类型,Name 商品名称,Price 商品价钱,ProductionDate
生产日期
from GoodType gt , Goods gs
where gs.Tid=gt.Tid
and Price>
(select AVG(Price) 平均价钱 from Goods)

–查询每种商品类型平均价钱超出总平均价钱的类型名称、平均价钱
select gt.Type 类型名称,AVG(Price) 价钱
from Goods gs,GoodType gt
where gs.Tid=gt.Tid
and
gs.Tid in
(select gs.Tid
from Goods
group by Tid
having AVG(Price)>
(select AVG(Price) 平均价钱 from Goods))
group by gt.Type
–查询所有商品总金额超20万的商品类型、名称、总金额
select Type 商品类型 ,Name 名称 ,Price*Amount 总金额
from GoodS gs , GoodType gt
where gs.Tid=gt.Tid
and Price*Amount>200000
–库存报警(低10)

–延迟到14:03:00 执行查询
waitfor time ’14:03:00′
select*from Goods
–延迟两秒执行操作
waitfor delay ’00:00:02′
select *from Goods

(5)变量函数

–常用的数学函数
–1、ABS 求绝对值 交易之前-交易之后
–2、POWER 求次方
select POWER(2,10),POWER(2.0000,0.5) –1kb 1024
select POWER(2.0000000,1.000000/3)
–3、求圆周率
select PI()
–4、Rount() –四舍五入函数
select ROUND(315.4567,2),Round(315.4567,0),Round(345.4567,-2)
–5、ceiling –取比原数大的整数
–6、floor –取比原数小的整数
select CEILING(3.00000000001),FLOOR(3.99999999)
–7、ASCII 返回一个字符的ASCII码值
select ASCII(‘A’)
–8、Rand() 返回一个0-1之间的随机数
–select nCHAR(214)+nCHAR(208)
select RAND()
select RAND(DATEPART(ss,GetDate())*2)–这样打变化频率小
go

–产生一个银行卡号,前8位为’6225 3800′ 后8位为随机数字,
–请用T-SQL编码完成
–select RAND()
declare @rand numeric(20,8)
select @rand=RAND()
print @rand
declare @s varchar(16)
set @s=SUBSTRING(STR(@rand,10,8),3,8)
print @s
set @s=’62253800’+@s
print ‘你的新银行卡号为:’+@s

–日期函数
–1、getdate():返回服务器上的当前时间
select GETDATE()
–2、datepart:返回一个日期的一部分值(整形)
–3、datename:返回一个日期的一部分值(字符串)
–返回一周的第几天(星期天是第一天)
select DATEPART(DW,GETDATE()),DATENAME(DW,GETDATE())
–返回一年的第几周
select DATEPART(WEEK,GETDATE()),DATENAME(WEEK,GETDATE())
–4、datediff 日期比较函数
–返回每个交易已经发生了多少天
select DATEDIFF(DD,transdate,GETDATE()) from TransInfo

–字符串函数
–1、LEN:返回一个字符串的字符数
select LEN(‘中国’),LEN(‘abc123!’)
select LEN(‘abc ‘),LEN(‘
abc’)–数据类型为varchar类型,会自动消除没字符连接的空格

–2、dataLength:返回一个字符串的字节数
select dataLength(‘中国’),dataLength(‘abc123!’)
select RIGHT(‘abcdef’,3),LEFT(‘abcdef’,3)
–4、substring:字符串截取函数
select SUBSTRING(‘abcdef’,2,3)–和C#不一样,下标从一开始
–5、charIndex:字符串查找函数
select charIndex(‘a’,’bcad’,1)
–6、upper:字母大写转换函数
–7、lower:字母小写转换函数
select UPPER(‘abc123’),LOWER(‘abCCC123中!’)
–8、space:产生空格函数
select len(‘abc’+SPACE(10) +’123′) ,’abc’+SPACE(10) +’123’
–len是测定总长度
–9、replicate:字符串重复函数
select REPLICATE(‘abc’,3)
–10、replace:字符替换函数
select REPLACE(‘11111111′,’1′,’o’)–将1替换为o
select REPLACE(‘o0o0o0o0000oo’,’0′,’o’),’00000000′–将0替换为o
select REPLACE( REPLACE(‘0o0oil0oillil10ol1′,’l’,’1′),’0′,’o’)
–11、stuff:字符替换函数
select STUFF(‘湖南武汉’,2,1,’北’)
select STUFF(‘中国长沙’,3,0,’湖南’)–输出中国湖南长沙
–12、ltrim和rtrim:去掉字符串左边或右边的全部空格
select len(ltrim(‘ a bc’))
–13、str:将数值转换为字符串函数
select STR(12345.65,8,2)
–14、char:将一个ASCII码值转换为一个字符
select CHAR(97),ASCII(‘a’)

 

(6)数据库变量格式

use StuDB
select*from StuInfo
select*from Exam
–总学生人数 参考人数 及格人数 未及格人数 及格率
declare @total int,@sum int,@pass int
select @total=COUNT(*) from StuInfo –统计总人数
select @sum=COUNT(*) from Exam –统计参考人数
select @pass=COUNT(*) from Exam
where WriteExam>=60 and LadExam>=60 –统计及格人数

select @total 总人数,@sum 参考人数,@pass 及格人数 ,
@total-@pass 未及格人数,CONVERT(varchar(20),
ceiling( @pass*1.0/@total*10000)/100)+’%’ 及格率

select*from StuInfo
–查看上一个错误的编号
select @@ERROR

select*from StuInfo
select*from Class
select @@IDENTITY
insert Class values(‘s149’)

–获取上一次SQL指令影响的命令行数
select @@ROWCOUNT

–判断删除是否成功
–1、直接删除
–2、先查询,然后再删除,再查询

–当前SQL服务器名称,当前服务名称
select @@SERVERNAME ,@@SERVICENAME

–显示当前打开的事务数
select @@TRANCOUNT

–显示当前服务器允许的最大连接数
select @@MAX_CONNECTIONS

–显示当前使用的语言
select @@LANGUAGE

print ‘当前服务器名称:’ +@@servername
print ‘当前服务名称:’ +@@servicename
print ‘错误编号:’ +convert(varchar(6), @@error)

–显示笔试平均成绩,再根据平均成绩显示相应信息
declare @avg float
select @avg=AVG(writeExam) from Exam
print ‘ ——成绩信息如下:——–‘
print’全校平均成绩:’+convert(varchar(20),@avg)
if @avg>=70
begin
print’成绩优秀!’
–显示前三名的学员信息
select top 3 StuName,si.StuId,WriteExam,LadExam
from StuInfo si,Exam e
where si.StuId=e.StuId
order by WriteExam desc –笔试降序
end
else
begin
print’成绩比较差!’
–显示后三名的学员信息
select top 3 StuName,si.StuId,WriteExam,LadExam
from StuInfo si,Exam e
where si.StuId=e.StuId
order by WriteExam asc –笔试降序
end

–对全班学员进行提分,保证每位同学的笔试成绩全部通过
while(1=1) –永真循环
begin
declare @count int –保存未通过的人数
–统计为通过的人数
select @count=Count(*) from Exam where writeExam<60
if (@count=0)
begin
break –终止循环
end
–进行加分
update Exam set WriteExam=100 where writeExam>=98
update Exam set WriteExam=WriteExam+2 where WriteExam<98

end
print’———-加分后的学员成绩如下:————–‘
select*from Exam

–显示学员笔试成绩的等级制
–90以上:优秀,80-90:良好,70-80:中等,60-69 :一般
–60以下:不及格
select ExamNo 考号,StuId 学号,WriteExam 笔试成绩 ,LadExam 机试成绩,
等级=
case
when writeExam>=90 then ‘优秀’
when writeExam>=80 then ‘良好’
when writeExam>=70 then ‘中等’
when writeExam>=60 then ‘一般’
else ‘不及格’
end
from Exam

–显示所有学员打的姓名,性别,年龄,笔试成绩,机试成绩
–没有成绩的学员显示缺考
select stuName,sex,Age,WriteExam=
case
when WriteExam IS null then ‘缺考’
else CONVERT(varchar(20),writeExam)
end,
LadExam=
case
when LadExam IS null then ‘缺考’
else CONVERT(varchar(20),ladexam)
end
from StuInfo si left join Exam e
on si.StuId=e.StuId

–未参加考试的学员成绩为0
select stuName ,sex ,ISNULL(age,18),
ISNULL(WriteExam,0),
ladexam=
case
when ladexam IS null then 0
else ladexam
end
from StuInfo si left join Exam e
on si.StuId=e.StuId

(7)索引、视图、事务

–创建索引
语法:
if exists(select*from sys.indexes
where name=’IX_stuinfo_AgeName’)
drop index stuinfo.IX_stuinfo_AgeName
go
create nonclustered index 索引名
on 表名(按某列升序或降序)
例子:
create nonclustered index IX_stuinfo_AgeName
on stuinfo(age,stuname desc)
备注:列名后加 desc 是降序的意思,不加默认升序
备注2:nonclustered表示创建非聚集索引
还有如:unique表示创建唯一性索引,clustered 表示创建聚集索引

–使用索引
语法:
select*from 有该索引的表名
with (index=索引名)
例子:
使用索引IX_stuinfo_AgeName 查询学员信息
select*From StuInfo
with(index=IX_Stuinfo_AgeName)

–索引的优点和缺点
优点:
1、加快访问速度
2、加强行的唯一性
缺点:
1、带索引的表在数据库中需要更多的存储空间
2、更新数据的命令需要更长的处理时间,因为它们需要对索引进行更新

–创建视图
语法:
create view 视图名
as
select 列名 from 表1,表2
where 表1.id =表2.id order by 条件
例子:
创建一个视图:获取学员的姓名、性别、年龄、笔试成绩、机试成绩、并且按笔试成绩降序排序
if exists(select*from sys.views
where name=’VW_Stu’)
drop view VW_Stu –有相同视图则删除原视图
go
create view VW_Stu
as
select top 100 stuName,Sex,Age,WriteExam,LadExam
from StuInfo si,Exam e
where si.StuId=e.StuId
order by WriteExam Desc
go
–视图的使用
select *from VW_Stu

–事务
事务的ACID属性
1、原子性
一个事务对数据库的所有操作,是一个不可分割的工作单元。这些操作要么全部执行,要么什么也不做。保证原子性是数据库系统本身的职责,由DBMS的事务管理子系统来实现。

2、一致性
一个事务独立执行的结果应保持数据库的一致性,即数据不会因为事务的执行而遭受破坏。确保单个事务的一致性是编写事务的应用程序员的职责。在系统运行时,由DBMS的完整性子系统执行任务。

3、隔离性
在多个事务并发执行时,系统应保证这些事务先后单独执行时的结果一样,此时称事务达到了隔离性的要求,也就是在多个并发事务执行时,保证执行结果是正确的,如同单用户环境一样。隔离性是由DBMS的并发控制子系统实现的。
4、持久性

语法:
–开启事务
begin transaction
declare @error int –定义变量,记录错误
set @error=0 –默认无错
Update bank set Blance=Blance+5000 where Bname=’join’
set @error=@errror+@@ERROR
Update bank set Blance=Blance-5000 where Bname=’jack’
set @error=@errror+@@ERROR
if(@error<>0) –如果错误号不为零,说明有操作出错
begin
raiserror(‘转账过程出错’,10,1)
rollback –回滚全部操作
end

else
begin
print ‘恭喜你,转账成功!’
commit –提交所有操作
end

实例:
–转账事务,转账900
begin tran
declare @err int=0 –声明一个变量,初值为0
update Bank set Cmoney=Cmoney-900 where Cname=’张三’
set @err=@err+@@ERROR
update Bank set Cmoney=Cmoney+900 where Cname=’李四’
set @err=@err+@@ERROR
if @err>0 –条件
begin
print’交易失败,事务回滚!’
rollback
end
else
begin
print’交易成功,事务提交’
commit tran
end

Ø 判断表或其他对象及列是否存在

(8)存储过程

–执行dos命令的存储过程CMDShell
–在d盘根目录下创建一个文件夹
execute xp_cmdshell ‘md D:DB’
–查看D盘下的所有信息
exec xp_cmdshell ‘dir d:’
–查看视图VW_Stu的源代码
exec sp_helptext ‘VW_Stu’
–查看一个表的索引
exec sp_helpindex ‘stuInfo’
–查看一个数据库中的存储过程
exec sp_stored_procedures
–进行数据库的逻辑名称改名(显示名称)
exec sp_renamedb BankDBs,BankDB–主文件名是不会变的
–查看当前数据库中的表和视图情况
exec sp_tables

–将网格显示改为文本格式显示

use StuDB
–创建一个存储过程
–显示机试和笔试的平均成绩,并且显示本次考试的成绩情况
–还要显示未通过的学员信息
if exists(select*from sys.procedures
where name=’ScoreCountl’)
drop procedure ScoreCountl
go
create procedure ScoreCountl
as
必赢365net手机版,declare @write decimal,@lab decimal
select @write=AVG(writeExam),@lab=AVG(LadExam)
from Exam
print ‘笔试成绩:’+convert(varchar(20),@write)
print ‘机试成绩:’+convert(varchar(20),@lab)
if @write>=70 and @lab>=70
print’本班考试成绩优秀!’
else
print’本班考试成绩一般!’

print’————————————————-‘
print’———-参加本次考试没有通过的学员名单———–‘
select stuname 姓名,si.StuId 学号,WriteExam 笔试成绩,LadExam 机试成绩
from StuInfo si,Exam e
where si.StuId=e.StuId and ( WriteExam<60 or LadExam<60)

go
exec ScoreCountl

–自由调控及格线
if exists(select*from sys.procedures
where name=’ScoreCount2′)
drop procedure ScoreCount2
go
create procedure ScoreCount2
@w decimal=60,–笔试及格线
@l decimal=60 –机试及格线,加了默认值60
as
declare @write decimal,@lab decimal
select @write=AVG(writeExam),@lab=AVG(LadExam)
from Exam
print ‘笔试成绩:’+convert(varchar(20),@write)
print ‘机试成绩:’+convert(varchar(20),@lab)
if @write>=70 and @lab>=70
print’本班考试成绩优秀!’
else
print’本班考试成绩一般!’

print’————————————————-‘
print’———-本次笔试及格线:’+convert(varchar(20),@w)
+’———-本次机试及格线:’+convert(varchar(20),@l)
print’———-参加本次考试没有通过的学员名单———–‘
select stuname 姓名,si.StuId 学号,WriteExam 笔试成绩,LadExam 机试成绩
from StuInfo si,Exam e
where si.StuId=e.StuId and ( WriteExam<@w or LadExam<@l)
go
exec ScoreCount2 @l=40,@w=60 –分别为机试和笔试的及格线
exec ScoreCount2 –有默认值的情况下

use GoodSystem
–创建一个存储过程:查询生产日期为某年的
–商品名称,类型,生产日期,库存
if exists(select*from sys.procedures
where name=’ScoreCount3′)
drop procedure ScoreCount3
go
create procedure ScoreCount3
@d decimal
as
select Name 商品名称,gt.Type 类型,ProductionDate 生产日期,Amount 库存
from GoodS gs,GoodType gt
where gs.Tid=gt.Tid and year(ProductionDate)=@d
–查询2017年的商品信息
go
exec ScoreCount3 2017

–创建一个存储过程:查询类型为‘食品’的商品信息
if exists(select*from sys.procedures
where name=’ScoreCount4′)
drop procedure ScoreCount4
go
create procedure ScoreCount4
@s varchar(20)
as
select Name 商品名称,gt.Type 类型,ProductionDate 生产日期,Amount 库存
from GoodS gs,GoodType gt
where gs.Tid=gt.Tid and gt.Type=@s

go
exec ScoreCount4 ‘食品’

–判断某个表或对象是否存在
if (exists (select * from sys.objects where name = ‘classes’))
print ‘Exist’;
go
if (exists (select * from sys.objects where object_id =
object_id(‘student’)))
print ‘Exist’;
go
if (object_id(‘student’, ‘U’) is not null)
print ‘Exist’;
go

(9)带输出参数的存储过程

–创建一个存储过程:添加一个学员成绩信息
if OBJECT_ID(‘InsertExam’) is not null
drop proc InsertExam
go
create proc InsertExam
@ExamNo int,
@stuid varchar(50),
@writeExam decimal(10,2),
@ladExam decimal(10,2),
@n int output
as
insert Exam(ExamNo,StuId,WriteExam,LadExam)
values(@ExamNo,@stuid,@writeExam,@ladExam)
select @n=@@ROWCOUNT

go
select*from Exam
–调用添加存储过程
declare @n int
exec InsertExam ‘8’,’1006′,70,80,@n output
if @n>0
print’添加成功’
else
print’添加失败’

use MySchool
select*from Teacher
–引用到C#案例MySchool_Proc
–添加教员
if OBJECT_ID(‘InsertTeacher’) is not null
drop proc InsertTeacher
go
create proc InsertTeacher
@LoginId varchar(50),
@LoginPwd varchar(50),
@TeacherName varchar(50),
@Sex varchar(50),
@UserStateId int,
@Birthday dateTime,
@n int output
as
insert Teacher(LoginId,LoginPwd,TeacherName,Sex,UserStateId,Birthday)
values(@LoginId,@LoginPwd,@TeacherName,@Sex,@UserStateId,@Birthday)
select @n=@@ROWCOUNT
go

–修改教员
if OBJECT_ID(‘UpdateTeacher’) is not null
drop proc UpdateTeacher
go
create proc UpdateTeacher
@LoginId varchar(50),
@LoginPwd varchar(50),
@TeacherName varchar(50),
@Sex varchar(50),
@UserStateId int,
@Birthday dateTime,
@n int output
as
update Teacher set LoginPwd=@LoginPwd, TeacherName=@TeacherName,
Sex=@Sex,UserStateId=@UserStateId,Birthday=@Birthday
where LoginId=@LoginId
select @n=@@ROWCOUNT
go
–删除教员
if OBJECT_ID(‘DeleteTeacher’) is not null
drop proc DeleteTeacher
go
create proc DeleteTeacher
@LoginId varchar(50),
@n int output
as
delete from Teacher where LoginId=@LoginId
select @n=@@ROWCOUNT
–自我理解
–添加、修改、删除存储过程都是为了应用到C#程序中可以更快的执行,且占系统运行内存不多。
–C#中的调用其实就是一个三层架构

–判断该列名是否存在,如果存在就删除
if (exists (select * from sys.columns where object_id =
object_id(‘student’) and name = ‘idCard’))
alter table student drop column idCard
go
if (exists (select * from information_schema.columns where table_name
= ‘student’ and column_name = ‘tel’))
alter table student drop column tel
go

 

Ø 创建、删除表

–判断是否存在当前table
if (exists (select * from sys.objects where name = ‘classes’))
drop table classes
go
create table classes(
id int primary key identity(1, 2),
name varchar(22) not null,
createDate datetime default getDate()
)
go
if (exists (select * from sys.objects where object_id =
object_id(‘student’)))
drop table student
go

–创建table
create table student(
id int identity(1, 1) not null,
name varchar(20),
age int,
sex bit,
cid int
)
go

 

Ø 给表添加字段、修改字段、删除字段

–添加字段
alter table student add address varchar(50) not null;
–修改字段
alter table student alter column address varchar(20);
–删除字段
alter table student drop column number;

–添加多个字段
alter table student
add address varchar(22),
tel varchar(11),
idCard varchar(3);

–判断该列名是否存在,如果存在就删除
if (exists (select * from sys.columns where object_id =
object_id(‘student’) and name = ‘idCard’))
alter table student drop column idCard
go
if (exists (select * from information_schema.columns where table_name
= ‘student’ and column_name = ‘tel’))
alter table student drop column tel
go

 

Ø 添加、删除约束

–添加新列、约束
alter table student
add number varchar(20) null constraint no_uk unique;
–增加主键
alter table student
add constraint pk_id primary key(id);
–添加外键约束
alter table student
add constraint fk_cid foreign key (cid) references classes(id)
go
–添加唯一约束
alter table student
add constraint name_uk unique(name);
–添加check约束
alter table student with nocheck
add constraint check_age check (age > 1);
alter table student
add constraint ck_age check (age >= 15 and age <= 50)
–添加默认约束
alter table student
add constraint sex_def default 1 for sex;
–添加一个包含默认值可以为空的列
alter table student
add createDate smalldatetime null
constraint createDate_def default getDate() with values;

—– 多个列、约束一起创建——–
alter table student add
/*添加id主键、自增*/
id int identity constraint id primary key,
/* 添加外键约束*/
number int null
constraint uNumber references classes(number),
/*默认约束*/
createDate decimal(3, 3)
constraint createDate default 2010-6-1
go

–删除约束
alter table student drop constraint no_uk;

 

Ø 插入数据

insert into classes(name) values(‘1班’);
insert into classes values(‘2班’, ‘2011-06-15’);

insert into student
select ‘bulise’ name, age, sex, cid
from student
where name = ‘tony’;

–多条记录同时插入
insert into student
select ‘jack’, 23, 1, 5 union
select ‘tom’, 24, 0, 3 union
select ‘wendy’, 25, 1, 3 union
select ‘tony’, 26, 0, 5;

 

Ø 查询、修改、删除数据

–查询数据
select * from student;
select id, ‘bulise’ name, age, sex, cid from student
where name = ‘tony’;
select *, (select max(age) from student) from student
where name = ‘tony’;

–修改数据
update student set name = ‘hoho’, sex = 1 where id = 1;

–删除数据(from可省略)
delete from student where id = 1;

 

Ø 备份数据、表

–备份、复制student表到stu
select * into stu from student;
select * into stu1 from (select * from stu) t;

 

Ø 利用存储过程查询表信息

–查询student相关信息
exec sp_help student;
exec sp_help classes;

 

 

 

转载自 

 

Leave a Comment.