转-SqlServer脚本学习
--创建数据库
create database HaiTianSystem
create database 海天学院
go--用于批量处理中的分段执行,常用的就是分割符
--删除数据库
drop database HaiTianSystem
go
--表结构的建立，删除，修改
--创建表
create table ht_user
(
学生号 int primary key,--设置主键pk
姓名 char(10) not null,--不允许为空
性别 char(2) not null check(性别='男' or 性别='女'),--检查约束
出生日期 datetime check(出生日期>='1998-12-31'),
年龄 int check(年龄>18 and 年龄<55)
)
--删除表
drop table ht_user
go
---修改表
--增加列
alter table ht_user add 电话 nchar(20)
alter table ht_user add 手机 nchar(11) not null
--删除列
alter table ht_user drop column 手机
alter table ht_user drop column 邮箱
go
/*
2010 -07-13 14:00
*/
--唯一约束,表示列存储的值不允许相同
alter table ht_user add 邮箱 nvarchar(50) unique
go
---GuID全局唯一标识符,用于主键，数据库的同步，目录名，产品ID
alter table ht_user add 全局唯一标识 varchar(100) default NewID()--default 表示默认值 newid表示GUID
go
----数据的插入
select * from ht_user
go
insert into ht_user(学生号,姓名,性别 )values(3,'陈乾','男')
go
insert into ht_user values(3,'徐兴新','男')
go
---更新表的内容
update ht_user set 姓名='张凯' where 学生号='1'
go
update ht_user set 姓名='杨旗' where 性别='男'
go
---删除
delete from ht_user where 学生号='1'
go
--清空表的全部内容
delete from ht_user
go
-----select查询
--查询表中所有内容
select * from 商品表1
go
--查询分类名称,按照列名来查询
select 分类名 from 商品表1
go
--去掉重复的值表示列中的内容有重复的,过滤掉多余的,保留唯一值
select distinct 分类名 from 商品表1
go
---运算符大于使用
select * from 商品表1 where 单价>'2000'
go
--聚合函数的使用 count
select COUNT(*) from 商品表1 where 单价<'2000'
--count表示根据where条件来统计结果的总计数,多少列满足条件,常用与数据的分页和用户的登录,判读是否有值
--特别注意点:count(*)这种写法从此以后不要出现,耗费性能,在检索数据的时候需要遍历每个列的内容
--改进方式就是count(列名)此处的列名最好是主键,加速检索的效率,速度有很大的提高
select COUNT(商品代号) 统计, 分类名 as 商品名称 from 商品表1 where 单价<'2000'
--列的别名 在查询的列后面空格一个 跟着写别名,另一种就是使用关键字as后面跟着别名
go
--select语句用用到聚合函数需要使用group by 进行分组
--在查询中进行计算
select 商品代号,分类名,单价,数量,单价*数量 as 价值 from 商品表1
go
--从商品表1中查询出不同分类名的种数
select COUNT(distinct(分类名)) as 分类种数 from 商品表1
go
--按照商品代号进行2个表的联合
select sb1.*,sb2.* from 商品表1 as sb1 ,商品表2 as sb2 where sb1.商品代号=sb2.商品代号
go
---所有商品的最大值,最小值,平均数量及数量总和
select MAX(数量) as 最大数量,MIN(数量) as 最小数量,AVG(数量) as 平均数量,SUM(数量) as 总和
from 商品表1
go
--查询出商品的最高价值,最低价值,和总价值
select max(单价*数量) as 最高价值,min(单价*数量) as 最低价值,sum(单价*数量)as总价值 from 商品表1
go
---where条件
select * from 商品表1 where 商品代号='KTQ-12' or 分类名='空调器'
go
---联合2个表查询商品产地是无锡,或者南京的产品所有信息
select sb1.*,sb2.* from 商品表1 as sb1 ,商品表2 as sb2 where sb1.商品代号=sb2.商品代号
and(sb2.产地='无锡'or sb2.产地='南京')
go
--中间连接
select sb1.*,sb2.* from 商品表1 as sb1 inner join 商品表2 as sb2 on sb1.商品代号=sb2.商品代号
go
--左连接
select sb1.*,sb2.* from 商品表1 as sb1 left join 商品表2 as sb2 on sb1.商品代号=sb2.商品代号
go
--有连接
select sb1.*,sb2.* from 商品表1 as sb1 right join 商品表2 as sb2 on sb1.商品代号=sb2.商品代号
go
--专门比较式
--all,语法<列名> <比较符> all(<子查询>),当子查询的结果中的每一个值都满足所给的条件时,次比较式的值是真,否则为假
---子查询就是要给完整的sql语句,用于嵌套在比较式中时候用
select * from 商品表1 where 单价>all(select 单价 from 商品表1 where 分类名='电冰箱')
go
---该语句从商品表1中查询出单价比分类名为'电冰箱'的所有商品的单价都高的商品,只要子查询的条件不满足就不显示任何结果
---any 同some一样的用法,只要子查询中的任何一个条件满足即可
select sb1.*,sb2.* from 商品表1 as sb1 inner join 商品表2 as sb2 on sb1.商品代号=sb2.商品代号 where sb2.产地= some(select 产地 from 商品表2 where 品牌='小天鹅')
go
---between 在两者之间的值
select * from 商品表1 where 单价 between 1500 and 2500
go
---exists
--语法：not[]exists(<子查询>)
--此格式中的<子查询>的结果至少存在一个元素时，表明查询结果非空，此判断是真，否则为假
--也就是只要子查询中有结果返回就是真的，为空就是假
select * from 商品表1 where exists(select 分类名 from 商品表1 where
单价>1500)
go
--in 指定的列名查询的值只要包含在子查询中，就为真
<> [not] in{(常量表)|(子查询)}
select * from 商品表1 where 分类名 in('电视机','洗衣机')
go
--like 用于模糊查询,列名和查询的值相匹配时，条件为真，
--特别注意：使用的频率非常高，常用于网站的站内搜索和关键字的匹配，也可以用于数据的检索
--常用的用法有6种，
select * from 商品表1 where 商品代号 like'%b%'
go
--order by 用于结果的排序，asc正排序，desc到排序，默认是asc
select * from 商品表1 order by 商品代号 desc
go
--group by 用于聚合函数中的分组,通常和聚合函数中一起使用,用于分组统计
select 分类名,COUNT(分类名) as 产品个数 from 商品表1 group by 分类名
go
--having 用于group by分组以后再进行结果的筛选
select 分类名,COUNT(分类名) as 产品个数 from 商品表1 group by 分类名 having COUNT(分类名)>=2
go
---临时表的使用
--根据查询的结果自动建立一个表，通常作为临时表，根据需要建立和删除
select sb1.*,sb2.产地,sb2.品牌 into 查询结果临时表 from 商品表1 as sb1 inner join 商品表2 as sb2 on sb1.商品代号=sb2.商品代号 where sb2.产地= some(select 产地 from 商品表2 where 品牌='小天鹅')
go
--查询临时表
select * from 查询结果临时表
go
---建立视图
create view 商品视图表(商品代号,商品名称,商品数量,商品产地) as select sb1.商品代号,sb1.分类名 ,sb1.数量,sb2.产地 from 商品表1 as sb1 inner join 商品表2 as sb2 on sb1.商品代号=sb2.商品代号
go
--查询视图
select * from 商品视图表
go
--修改视图内容用update，对行的内容进行修改
update 商品视图表 set 商品数量=商品数量+10 where 商品名称='电冰箱'
go
--修改视图定义用alterd，对视图的结构进行修改，重新调整列
alter view 商品视图表(商品代号,商品名称,商品数量,商品产地) as select sb1.商品代号,sb1.分类名 ,sb1.数量,sb2.产地 from 商品表1 as sb1 inner join 商品表2 as sb2 on sb1.商品代号=sb2.商品代号 where sb2.产地= some(select 产地 from 商品表2 where 品牌='小天鹅')
go
============2010 07-14 10:20
--变量
declare @uname char(10),@uage int,@utel char(10)
set @uname='海天'
select @uage='14',@utel='0531-88676689'
print '你的姓名：'+ @uname
go
---全局变量
select @@VERSION--版本信息
go
select @@Servername--服务器名称
go
--字符串函数
select left('haitiansystem',2),substring('haitiansystem',3,4),upper('haitiansystem')
go
select getdate()as 当前日期 ,datepart(month,getdate())as 月份
go
---四舍五入
select round(16.2828,3)
go
---系统函数
select DB_NAME()--当前使用数据库名称
go
--begin end
begin
select * from 商品表1
update 商品表2 set 品牌='小鸭' where 产地='青岛'
end
go
select * from 商品表2
go
--if esle
declare @shop_avg int
select @shop_avg=avg(单价)from 商品表1 where 分类名='电视机'
print '电视机的平均价格:'
print @shop_avg
if @shop_avg>2500
print '产品价格太高'
else
print '产品价格合适'
go
--if exists
if exists(select 分类名 from 商品表1 where 数量>20)
print '有此产品'
else
print '无此产品'
--case when
use 商品库 ---使用哪个数据库
go
select 商品代号,单价,数量 ,描述=
case
when(单价>=2200) then '价格过高'
when(单价>=1000) then '价格合适'
when(单价<1000) then '价格太低'
end
from 商品表1 order by 商品代号
--while 1+2。。。+100的和
declare @i int,@sumall int--声明变量
select @i=1,@sumall=0--变量赋值
while @i<=100 --条件判断
begin
select @sumall=@sumall+@i --循环累加
select @i=@i+1 --循环加1，到满足while的条件为止
end
print '总和是：'
print @sumall
go
---晚上11点执行商品库的备份
WAITFOR TIME '11:00:00'
BACKUP Databse 商品库 to disk='d:\商品库.bak'
go
----goto
declare @i int,@sumall int--声明变量
select @i=1,@sumall=0--变量赋值
Label_1: --跳转目标标识符
select @sumall=@sumall+@i --循环累加
select @i=@i+1 --循环加1，到满足while的条件为止
while @i<=100 --条件判断
goto Label_1--goto跳转语句
print '总和是：'+@sumall
print @sumall
----
declare @flm char(10),@cd char(10),@jg decimal
select @flm=商品表1.分类名,@cd=商品表2.产地,@jg=商品表1.单价 from 商品表1 inner join 商品表2 on 商品表1.商品代号=商品表2.商品代号 where 商品表1.商品代号='DBX-135'
print @flm
print @cd
print @jg
go
---定义存储过程 输入3个数比较大小
create proc up_maxnum
--定义参数
@a int,
@b int,
@c int
as
begin
declare @max int --定义局部变量
set @max=@a
if @b>@max
set @max=@b
if @c>@max
set @max=@c
print '最大数是:'+convert(varchar,@max)
end
-- 执行存储过程
execute up_maxnum 68,34,346
go
--修改存储过程的名称
sp_rename up_maxnum,up_maxnumber
go
--查看存储过程
sp_help up_maxnumber
go
--查看存储过程的源码
sp_helptext up_maxnumber
go
--加密存储过程
create proc test with encryption
as
select * from 商品表1
--
sp_help test
go
sp_helptext test
go
--删除存储过程
drop proc test
go
===2010 07-15 14：30
CREATE TRIGGER safety
ON DATABASE
FOR DROP_TABLE
AS
PRINT '禁止删除表信息'
ROLLBACK ;
---
use 商品库
go
begin tran
update 商品表1 set 单价=单价+2000
delete from 商品表2 where 商品代号='DBX-135'
commit tran--结束一个事务
rollback tran --发生错误执行回滚,保证数据的完整性
--
select * from 商品表1
go
--实例：使用@@Error返回值来控制回滚
--设有表a,有字段num (int),name (varchar(20))；表b,有字段num (int),Add (varchar(50))。以下是一个存储过程，使用了@@Error的返回值来控件事件回滚：
CREATE PROCEDURE [dbo].[TranTest] --定义存储过程
@num int,@name varchar(20),@Add varchar(20) --声明参数
AS
DECLARE @ErrorA int,@ErrorB int --定义变量
begin transaction aa --定义事物名称
insert into a values(@num,@name) --要执行的sql语句
Set @ErrorA = @@error --返回最后执行的sql语句的错误代码
insert into b values(@num,@Add) --要执行的sql语句
Set @ErrorB = @@error
if @ErrorA>0 or @ErrorB>0 --判断是否有错返回
begin
Rollback transaction aa --如果有错误返回到事物定义以前，不执行此事物
end
else
commit transaction aa --无错误，执行此事物之内的sql语句块
GO
---------------
create trigger tri_table_safe
on database
for drop_table
as
begin
print'有用户试图删除表!'
rollback --撤销用户删除
end
-------inserted触发器
create trigger tri_student_insert
on student
for insert
as
begin
declare @xm char(10)
select @xm=sname from insterted
print @xm
end
--执行返回
insert into student (sno,sname)values('20100717','HaiTianCollege')
go
---delete触发器
create trigger trig_student_delete
on student
for delete
as
begin
declare @xm char(10)
select @xm=sname fom deleted --取得当前删除行信息
print @xm
update student set spass='10'
end
--执行删除
delete from student where sname='马超'
go
---声明游标
declare cur_student cursor
for
select * from student
for update
---游标的使用
现在成绩表中增加一个flag字段
--定一个游标
declare @score int ---定义变量
declare cur_score cursor --声明游标名称为cur_score
for
select grade from sc ---定义游标结果集的select查询语句
for
update of flag --标识部分可读写游标 ，对flag字段进行修改
go
--使用游标，并针对成绩表进行修改
open cur_score --打开局部游标
declare @score int ---定义变量
fetch next from cur_score into @score --读取游标的第一条记录并赋值到变量中
while @@FETCH_STATUS=0 --循环读取游标中的记录并返回游标的状态是否为成功
begin
if @score>=90 --通过变量的值来判断成绩是否成立
update sc set flag='优秀' --成立更新表中的列
where CURRENT of cur_score --即时执行当前的游标
if @score>=80 and @score<90
update sc set flag='良好'
where current of cur_score
if @score>=70 and @score<80
update sc set flag='中等'
where current of cur_score
if @score>=60 and @score<70
update sc set flag='及格'
where current of cur_score
if @score<60
update sc set flag='不及格'
where current of cur_score
fetch next from cur_score into @score --执行判断成功以后在继续读取下一跳记录
end
--关闭游标.close 语句关闭游标，但不释放游标占用的数据结构，可以使用open命令再次打开
close cur_score
--释放游标 就是删除游标的数据结构，不能在用open打开
deallocate cur_score
go
--查看使用游标
select * from sc
