数据库操作集合

--数据库的创建一定要在master数据库当中
use master
go
--判断在数据库中是否含有该数据库
--如果有，则删除
if exists(select * from sysdatabases where name='stuDB')
    drop database stuDB
go
--创建数据库
create database stuDB
on
(
    name='stuDB_mdb',--逻辑名称
    filename='e:\DB\stuDB_mdb.mdf',--文件物理路径
    --size=5,--初始大小，可以不写，默认为1MB
    maxsize=10,--增长最大限制，不写为无限
    filegrowth=1--增长率，可以是数据，可以是百分比
),
(
    name='stuDB_ndb',
    filename='e:\DB\stuDB_ndb.ndf'
)
log on
(
    name='stuDB_ldb',
    filename='e:\DB\stuDB_ldb.ldf',
    size=5,
    maxsize=10,
    filegrowth=1
)
go
/*
--添加数据文件
--alter database stuDB add file
--(
--    name='stuDB_ndb2',
--    filename='e:\db\stuDB_ndb2.ndf'
--)
go
--修改数据库名称
--alter database stuDB_T modify name=stuDB
go
--删除数据库文件
--alter database stuDB remove file stuDB_ndb2
--go
----修改数据文件的参数
--alter database stuDB modify file
--(    
--    name='stuDB_ndb',
--    --size=5,--初始大小，可以不写，默认为3MB
--    maxsize=10,--增长最大限制，不写为无限
--    filegrowth=50%--增长率，可以是数据，可以是百分比
--)*/
go
/*
--exec sp_detach_db 'stuDB'--分离数据库
go
--附加数据库
--exec sp_attach_db 'stuDB','e:\DB2\stuDB_mdb.mdf'
--                        ,'e:\DB2\stuDB_ndb.ndf'
--                        ,'e:\DB2\stuDB_ldb.ldf'
*/
go
--对数据库里面的数据进行操作
use stuDB
go
if exists(select * from sysobjects where name='stuInfo')
    drop table stuInfo
go
create table stuInfo
(
    stuNo int identity(1,1) primary key,--学号
    stuName varchar(10) not null,--姓名
    stuAge int not null,--年龄
    stuSex varchar(2) not null,--性别
    stuId char(18) not null,--身份证号码
    stuTel char(11),--电话号码
    stuAddress varchar(50) default('地址不详'),--家庭地址
    stuGroup int --学习小组组长id
)
go
--成绩表
if exists(select * from sysobjects where name='stuMarks')
    drop table stuMarks
go
create table stuMarks
(
    Mid int identity(1,1) primary key,
    stuNo int foreign key references stuInfo(stuNo),
    LabExam int,
    WritterExam int
)
go
--添加约束
--主键约束
--alter table stuInfo
-- add constraint PK_stuNo primary key(stuNo)
--唯一约束
--alter table stuInfo
-- add constraint UQ_stuId unique(stuId)
--检查约束
--alter table stuInfo
-- add constraint CK_stuAge check(stuAge>=15 and stuAge<=30)
--默认约束
alter table stuInfo
    add constraint DF_stuSex default('男')for stuSex
--外键约束
--alter table stuMarks
--    add constraint FK_stuNo foreign key(stuNo) references stuInfo(stuNo)
go
--删除约束
--alter table stuInfo
--    drop constraint DF__stuInfo__stuAddr__0519C6AF


go

--select * from stuInfo
    

insert into stuInfo (stuName,stuAge,stuSex,stuId,stuTel,stuAddress,stuGroup)
values('罗*',18,'男','430981111111111111','15999999999','广东湛江',1)
insert into stuInfo values('赖*',19,'男','430981111111111112','15999999998','江西赣州',1)
--批量插入，可以查询表格中的数据
insert into stuInfo 
select '向*',17,'男','430981111111111113','15999999997','四川成都',1
insert into stuInfo 
select '陈*',21,'女','430981111111111114','15999999995','湖南常德',4
insert into stuInfo 
select '虞*',18,'男','430981111111111115','15999999996','江西上饶',4
insert into stuInfo 
select '蓝*',19,'男','430981111111111116','15999999998','广东阳江',4
insert into stuInfo 
select '向*',17,'男','430981111111111117','15999999997','四川成都',1
go    
    insert into stuMarks values(1,80,68)
    insert into stuMarks values(2,50,90)
    insert into stuMarks values(3,70,40)
    insert into stuMarks values(4,80,80)
    insert into stuMarks values(5,70,80)
go
--查询并将数据插入一个新表，该表结构与原表一样，
--请注意，是重新创建一个新表
select * into stuInfoBack from stuInfo
go
select * from stuinfoBack
go
insert into stuinfoBack 
select stuName,stuAge,stuSex,stuId,stuTel,stuAddress,stuGroup from stuInfo

go
--修改数据
--upate
update stuInfo set stuSex='男',stuAge=15 where stuNo=1
go
--查询
select * from stuInfo
--查询前3条记录
select top 5 * from stuInfo
--查询百分比的记录
select top 5 percent * from stuInfo
--
select stuName+'_'+stuSex+'_'+stuAddress as new from stuInfo
--
select* from stuInfo order by stuAge,stuId desc
--分组统计
select stuGroup, COUNT(1) num 
from stuInfo group by stuGroup
having COUNT(1)>=3--having用作分组统计后的数据筛选
--
go
--联合查询
select * from stuInfo
select * from stuMarks
go
--最基本的联合查询
select I.stuName,stuSex,LabExam,WritterExam
 from stuInfo I,stuMarks M
 where I.stuNo=M.stuNo 
 and M.LabExam>=60 and M.WritterExam>=60
go
--查询所有参加了考试的同学的信息
select I.stuName,stuSex,LabExam,WritterExam
from stuInfo I inner join stuMarks M
on I.stuNo=M.stuNo
where M.LabExam>=60 and M.WritterExam>=60
go
--查询所有同学的信息，并且加上该同学的考试成绩

select I.stuName,stuSex,LabExam,WritterExam
from stuMarks M right join stuInfo I
on I.stuNo=M.stuNo
--全外连接
select I.stuName,stuSex,LabExam,WritterExam
from stuMarks M full outer join stuInfo I
on  I.stuNo=M.stuNo

--交叉连接
--数据行为两 表行数的乘积
select I.stuName,stuSex,LabExam,WritterExam
from stuMarks M cross join stuInfo I
--自连接
--查询学生的信息，以及该学生所在学习小组的组长名字
select I1.stuName,I1.stuSex,I2.stuName,I2.stuSex
 from stuInfo I1,stuInfo I2
 where I1.stuGroup=I2.stuNo
go
--联合查询，两表的字段类型必须一致
select * from stuInfo
union all
select * from stuInfoBack

go
select *
--,(select LabExam from stuMarks M where M.stuNo=I.stuNo) lab
from stuInfo I where stuNo not in (
    select stuNo from stuMarks 
    where LabExam>=60 and WritterExam>=60
)
go

select *
--,(select LabExam from stuMarks M where M.stuNo=I.stuNo) lab
from stuInfo I where exists (
    select * from stuMarks M where M.stuNo=I.stuNo
)
 
 go
 --any,some,all
--select * from stuInfo where stuAge>any(15,18,19)

 

go

--第五章 T-SQL编程

--声明局部变量
declare @name varchar(10)
declare @age int
--set赋值用于普通的变量赋值
set @name='华*'
set @age=20*2
--select用于查询表中的数据
select @name='麦*'

select @name=stuName from stuInfo --where stuNo=1

print @name

go
--查找相关岁数的同学的信息
declare @name varchar(10)
declare @age int
set @name='虞*'
select @age=stuAge from stuInfo
 where stuName=@name 
print @age
select * from stuInfo where 
    stuAge=@age+1 or stuAge=@age-1
go
--全局变量（系统变量@@）
--@@ERROR
select @@ERROR
--错误产生后的值
print 'aa'+@age
--@@identity
select @@identity
go
--IF...ElSE...
declare @age int

select @age=stuAge from stuInfo
 where stuName='虞*' 
if (@age>20)
--begin
    print '你成熟了'
--end
else
begin
    print '屁大点'
end
go
--if..else..的用法
declare @avg int
select @avg=avg(WritterExam) from stuMarks
if(@avg>70)
begin
print '全校成绩优秀'
select top 3*  from stuMarks order by WritterExam desc
end
else
begin
print '全校成绩较差'
select top 3*  from stuMarks order by WritterExam asc
end
go
--while
declare @len int
set @len=1
while(@len<=10)
begin
    print @len
    set @len=@len+1
    if(@len=5)
        break
end
go
--while实例
declare @count int
while (1=1)
begin
select @count=COUNT(1) from stuMarks where WritterExam<60
if(@count>0)
    update stuMarks set WritterExam=WritterExam+2-- where  WritterExam<60
else
    break
end
select * from stuMarks

/*
select * from stuInfo I
left join stuMarks M on I.stuNo=M.stuNo*/
go
/*case...when...then...else*/

--第一种用法，用于判断范围内的不确定值
select stuNo,LabExam,
    case 
    when LabExam<60 then '不及格'
    when LabExam<70 then '及格'
    when LabExam<80 then '一般'
    when LabExam<90 then '良好'
    else '优秀' 
    end Level
 from stuMarks
 --用于判断固定值
 select stuNo,LabExam,
    case LabExam
    when 50 then '不及格'
    when 70 then '及格'
    when 80 then '一般'
    when 90 then '良好'
    else '优秀' 
    end Level
 from stuMarks
 --
 update stuMarks set WritterExam=WritterExam+
        case when WritterExam<100 then 2
        else 0
        end
 go
 
 --waitfor   delay   time
 waitfor time '10:17:20'
 select * from stuInfo
 go
 --raiserror  错误显示
 raiserror('错了%d',18,1,50) with log
 --添加错误信息到系统表中
 sp_addmessage 50001,18,'错了','us_english'
 --如何调用已有的错误信息
 raiserror(50001,19,1) with log
 go
 select ABS(20),--绝对值
        ascii('A'),--求ASCII码
        RAND(),--0-1的随机数,一旦放入整型参数，则随机值只初始化一次，所有的值相等
        PI(),
        ROUND(50.688,1)
 --字符函数
 select 
 stuName,LEN(stuName),--求得字符串的长度，以字符数计量
 DATALENGTH(stuName),--求得字符串字节的长度，以字节为计量
 LEFT(stuName,2),--取得左边开始的2个字符
 RIGHT(stuName,3),--取得右边开始的3个字符
 SUBSTRING(stuName,1,2),--截取字符串，从第1个字符开始，取2个字符，
                        --如果第一个参数为0，代表第1个字符行为表现前数一位
 REPLICATE(stuName,2),--复制字符串，第二个参数为复制的个数
 REPLACE(stuName,'向','刘'),--替换字符串，第二个参数为被替换的字符，第三个是替换以后的字符
 CHAR(65),CHARINDEX('罗',stuName,1),--第一个参数是要找的字，第二个被查找的字符串值
 PATINDEX('罗%',stuName)--可以用通配符
 from stuInfo
 go
 --日期函数
 select GETDATE(),DATEPART(WEEKDAY,GETDATE()),
 DATEADD(DAY,-1,GETDATE())--日期的运算
 go
 --格式转换
 select CONVERT(varchar(20), GETDATE(),101)
 go
 --系统函数
 select USER,USER_ID('dbo'),DB_ID('stuDB'),OBJECT_ID('stuInfo')
 --if OBJECT_ID('stuInfo')<>null
    --drop table stuInfo
 go
 use master
 go
 select * from sysdatabases
 go
 --自定义函数
 --标量
 alter function fn_Test
 (
    @one int,@two int
 )
 returns int
 as
 begin
    return @one+@two
 end
 go
 alter function fn_Level(
    @LabExam int,--参数1，@名字,数据类型
    @WritterExam int
 )
 returns varchar(10)--返回值的数据类型
 as
 begin
    declare @avg int
    declare @level varchar(10)
    set @avg=(@LabExam+@WritterExam)/2
    
 select @level=
    case 
    when @avg<60 then '不及格'
    when @avg<70 then '及格'
    when @avg<80 then '一般'
    when @avg<90 then '良好'
    else '优秀' end
    return @level --最后必须要返回一个值
 end
 go
 select * from stuMarks
 --函数的使用可以结合数据的查询，并能将表格中的字段当成参数传递
 select dbo.fn_Test(LabExam,WritterExam),
    dbo.fn_Level(LabExam,WritterExam) level
  from stuMarks m
 go
 --表值函数
 create function fn_Table
 (@age int)
 returns table
 as
 return 
    select * from stuInfo where stuAge>@age
    
go
--表值函数的使用，作为一个数据源
select * from dbo.fn_Table(18)
go

--多语句表值函数的定义
create function fn_Table_2
(
    @age int,@sex varchar(2)
)
--首先字义一个变量，数据类型为table
--在table中字义该临时表的数据字段
returns @table table(stuNo int,stuName varchar(10),stuAddress varchar(50))
as
begin
    --将数据从表中取出插入到临时表中,数据字段的数据类型必须与临时表一致
    insert into @table
        select stuNo,stuName,stuAddress from stuInfo
        where stuAge>@age and stuSex=@sex
        
    return --最后返回，不需要写其它值
end
go
--使用的方法与内嵌表值函数的调用一致
select * from dbo.fn_Table_2(18,'女')
go

--第六章 视图、索引和事务
go
--索引
create unique index index_stuId
on stuInfo(stuId)

go
--重新生成索引
alter index index_stuAge on stuInfo rebuild
go

go
--删除索引，必须表名.索引
drop index stuInfo.index_stuId
drop index index_stuAge on stuInfo
go
--视图
select * from stuInfo
go
select * from view_Temp where stuAge>18
go
alter view view_Temp2
--with encryption  --加密当前的视图创建语句
--with schemabinding  --提示，如果你修改表中字段的结构，将会提出警告
as
SELECT dbo.stuInfo.stuNo AS 学号, stuName, stuAge, 
stuSex, LabExam, WritterExam
FROM  dbo.stuInfo INNER JOIN
   dbo.stuMarks ON dbo.stuInfo.stuNo = dbo.stuMarks.stuNo
go
select * from view_Temp2
go
alter view view_Temp3
as
select stuNo,stuName from stuInfo
go
select* from view_Temp3
go
--原则上是可以在视图上进行数据的增删改
--条件：不能引起基础表的错误
update view_Temp3 set stuName='罗*' where stuNo=1
go
--能否在视图的基础上再创建视图呢？
create view view_Temp4
as
select * from view_Temp3
go
select * from view_Temp4
go--with check option
create view view_Temp5
as
select * from stuInfo where stuName like '赖%'
with check option  --表示不能在视图上修改我的条件
go
update view_Temp5 set stuName='赖海不清'
go
--删除视图
drop view view_Temp5
go--
--查看视图的信息
exec sp_help view_Temp4
exec sp_helptext view_Temp4

go
/***************事务****************/
CREATE TABLE bank
(
    customerName CHAR(10),  --顾客姓名
    currentMoney MONEY     --当前余额
)
GO
ALTER TABLE bank
   ADD CONSTRAINT CK_currentMoney    
       CHECK(currentMoney>=1)
GO


INSERT INTO bank(customerName,currentMoney)    VALUES('张三',1000)
INSERT INTO bank(customerName,currentMoney)    VALUES('李四',1)
go
select * from bank
go
/*--转账测试：张三转账1000元给李四--*/
--我们可能会这样这样编写语句
--张三的账户少1000元，李四的账户多1000元
UPDATE bank SET currentMoney=currentMoney-1000 
     WHERE customerName='张三'
UPDATE bank SET currentMoney=currentMoney+1000 
     WHERE customerName='李四'
GO
--再次查看转账后的结果。 
SELECT * FROM bank
go

--开始创建事务
begin tran-- transaction  --tran
declare @error int --用于记录错误，请记住，是累积
UPDATE bank SET currentMoney=currentMoney-1000 
     WHERE customerName='张三'
set @error=@error+@@ERROR
UPDATE bank SET currentMoney=currentMoney+1000 
     WHERE customerName='李四'
set @error=@error+@@ERROR --累积
if (@error>0)--一旦出现错误，则变量大于0
begin
    --抛出错误，并且回滚数据
    raiserror('对不起，转帐出现错误，请联系柜台！',6,1)
    rollback-- transaction
end
else
begin
    print '转帐成功，请查询帐户余额！'
    --成功则提交
    commit
end
go
--关闭或找开自动提交
--记住，每条sql语句都是一个隐性事务，会自动提交
set implicit_transactions on-- off --
--on是不自动提交，需要手动写commit
--off是自动提交事务
go

/****************触发器*****************/

create table bank
(
    cardId char(9) primary key,    
    customerName CHAR(10),  --顾客姓名
    currentMoney MONEY  default(1) check(currentMoney>0)    --当前余额
)
go
create table transInfo
(
    cardId char(9) references bank(cardId) ,
    transType varchar(10) not null,
    transMoney MONEY not null
)
go

INSERT INTO bank(cardId,customerName,currentMoney)    VALUES('1001 0001','张三',1000)
INSERT INTO bank(cardId,customerName,currentMoney)    VALUES('1001 0002','李四',1)

go

alter trigger trig_Trans
on transInfo
for insert
as
    declare @money money
    declare @cardId char(9)

    select @cardId=cardId,@money=case transType when '支出' then -transMoney else transMoney end from inserted
    update bank set currentMoney=currentMoney+@money where cardId=@cardId

    if(@@error>0)
    begin
        raiserror('交易失败',5,1
        rollback
    end
    else
    begin
        print('交易成功')
        --commit
    end
go
create trigger trig_Bank
on bank
for update
as
    declare @newMoney money
    declare @oldMoney money
    declare @money money
    declare @cardId char(9)
    select @cardId=cardId,@newMoney=currentMoney from inserted
    select @oldMoney=currentMoney from deleted
    set @money= abs(@newMoney-@oldMoney)
    if(@money>20000)
    begin
        raiserror('每笔交易金额不能超过20000元，交易失败',6,1)
        print('交易金额：'+convert(varchar(20),@money))
        rollback
    end
    else
    begin
        print('交易成功！交易金额：'+convert(varchar(20),@money))
        print('帐号：'+convert(varchar(20),@cardId)+'帐户余额：'+convert(varchar(20),@newMoney))
    end
go

/************第七章 存储过程和触发器************/

--系统存储过程
exec sp_databases --列出所有的数据库
execute sp_renamedb 'stuNewDB','stuDB'
EXEC xp_cmdshell 'mkdir d:\bank', NO_OUTPUT


go
--用户自定义存储过程

--函数
create function fn_temp
(
    @name varchar(10)
)
returns int
as
begin
    return 10
end
go
create procedure proc_Temp
    @name varchar(10)
as
    select * from stuInfo
go
select dbo.fn_temp('a')
exec proc_Temp 'A'
go
--例题
alter proc proc_Level
    @num int output,
    @good int,
    @score int=60  --参数可以设置为默认值
as
    declare @labExam float
    declare @writterExam float
    select @labExam=AVG(LabExam)
    ,@writterExam=AVG(WritterExam) from stuMarks
    print('机试平均分是'+convert(varchar(10),@labExam))
    print('笔试平均分是'+convert(varchar(10),@writterExam))
    if(@labExam>=@good and @writterExam>=@good)
        print('全班成绩优秀')
    else
        print('继续努力')
    print('------------')
    print('参加了考试，但是不及格的同学有')
    
    select * from stuInfo I inner join stuMarks M
    on I.stuNo=M.stuNo
    where labExam<@score or writterExam<@score
    --统计未及格的人数    
    select @num=COUNT(*) from stuMarks 
    where labExam<@score or writterExam<@score
    
go
--存储过程的调用
exec proc_level 70,80
exec proc_Level @good=70,@score=80
exec proc_Level @good=80  --有默认值的，可以不带该参数
go
--调用output参数
declare @num int
exec proc_Level @num output,70,80
print '未及格人数：'+Convert(varchar(10),@num)
go

--触发器

go
create trigger trigger_Trans
on transInfo
for insert
as
    declare @money money
    declare @type varchar(10)
    declare @card varchar(10)
    select @card=cardId,@type=transType,@money=transMoney from inserted
    if(@type='支取')
    begin
        update bank set currentMoney=currentMoney-@money
        where cardId=@card
    end
    else
    begin
        update bank set currentMoney=currentMoney+@money
        where cardId=@card
    end

go


alter trigger trigger_Trans
on transInfo
for insert
as
    declare @money money
    declare @type varchar(10)
    declare @card varchar(10)
    select @card=cardId,@type=transType,
    @money=case transType 
            when '支取' then -transMoney 
            else transMoney end
     from inserted
    update bank set currentMoney=currentMoney+@money
    where cardId=@card    
go
alter trigger trigger_Bank
on bank
for update
as
begin
    declare @newMoney money
    declare @oldMoney money
    declare @money money
    --新数据放在inserted表中
    select @newMoney=currentMoney from inserted
    --旧数据放在deleted表中
    select @oldMoney=currentMoney from deleted
    set @money=abs(@newMoney-@oldMoney)
    if(@money>20000)
    begin
        raiserror('每日单笔交易额度不能超过2万!',6,1)
        print('交易金额为:'+convert(varchar(10),@money))
        rollback
    end
    else
    begin
        print('交易成功！交易金额为:'+convert(varchar(10),@money))
        print('帐户余额：'+convert(varchar(10),@newMoney))
    end
end
go
--删除帐户，判断，如果该帐户已有交易，则不能删除
alter trigger trigger_Delete
on bank
for delete
as
begin
    declare @cardId varchar(10)
    declare @count int
    select @cardId=cardId from deleted --删除的记录在deleted表中
    select @count =count(1) from transInfo where cardId=@cardId
    if(@count>0)
    begin
        raiserror('不能注销该帐户，因为已经有交易行为产生！',6,1)
        rollback
    end
    else
        print('已经注销该帐户，帐户名：'+@cardId)
end
go
insert into transInfo values('1001 0001','支取',200)
insert into transInfo values('1001 0002','存入',20000)
insert into transInfo values('1001 0002','支取',20001)
go
delete bank where cardId='1001 0003'

select * from bank
select * from transInfo
6+8=14
6-8=-2
6+-8=-2
INSERT INTO bank(cardId,customerName,currentMoney)    VALUES('1001 0003','王五',1000)