sql学习笔记

来源：互联网发布：禁闭岛知乎编辑：程序博客网时间：2024/04/29 13:33

Code:

if exists (select * from sysdatabases where name = 'student')
drop database student --如果该数据库已经存在就删除
go
create database student sysdatabases
go
use student
go
if exists (select * from sysobjects where name = 'stuinfo')
drop table stuinfo --如果该表已经存在就删除
go
create table stuinfo
(
sid int primary key,
sname varchar(40) not null,
ssex varchar(20) not null
)
go
if exists (select *from sysobjects where name ='scoreinfo')
drop table scoreinfo
create table scoreinfo
(
id int primary key,
sid int references stuinfo(sid),--外键
score int not null
)
go
insert into stuinfo values(0001,'张三' ,'男')
insert into stuinfo values(0002,'李思','女')
go
insert into scoreinfo values(01,0001,85)
insert into scoreinfo values(02,0002,80)
go
--演示多表联结查询(必须有主外键关系)
use student
go
--(1)内联结查询--》特点：能取出公共字段的“共同值”
select * from stuinfo as stu inner join scoreinfo as sco on stu.sid=sco.sid
--(2) 左外联接 --》特点：能取出公共字段和“左表”的全部值
select *from stuinfo as stu left outer join scoreinfo as sco on stu.sid=sco.sid
--(3) 右外联接 --》特点：能取出公共字段和"右表"的全部值
select *from stuinfo as stu right outer join scoreinfo as sco on stu.sid=sco.sid
--演示子查询----查询出姓名为‘张三’的人的分数
select score from scoreinfo where sid=(select sid from stuinfo where sname='张三')
--演示排序函数
--集合运算：
--UNION和UNION ALL:并集
--UNION:把两张表合为一张表，去掉重复数据
--UNION ALL:把两张表合为一张表,不去掉重复数据
--注意:两张是同一个表
select * from stumarks where writtenexam >70
union
select * from stuinfo where writtenexam > 60
select * from stumarks where writtenexam > 70
union all
select * from stumarks where writtenexam > 60
--INTERSECT:交集
--把两张表合为一张表，去掉不重复数据，保留重复数据
select * from stumarks where writtenexam > 70
intersect
select * from stumarks where writtenexam > 60
--EXCEPT:差集
--把两张表相减,保留不重复的数据
--注意:前一个表比后一个表的数据要多才行
select * from stumarks where writtenexam > 60
except
select * from stumarks where writtenexam > 70
--带输入参数的存储过程
if object_id ('test','p') is not null
drop proc test --如果该存储过程已经存在就删除
go
create proc test
@a int ,@b int
as
select @a-@b
go
exec test @b=15, @a=20--执行存储过程（传参数）
--带输出参数的存储过程
if object_id ('demo','p') is not null
drop proc demo
go
create proc demo
@num1 int,@num2 int,@num3 int output
as
set @num3=@num1+@num2
go
declare @temp int --定义临时变量
exec demo @num1=20,@num2=15,@num3=@temp output
select @temp
--存储过程完成分页
if object_id('page','p') is not null
drop proc page
go
create proc page
--输入参数：当前页，每页显示的条数，表名，字段
@currentpage int=1,
@count int=10,
@tablename varchar(20),
@column varchar(20)
as
declare @sql nvarchar(1000)
set @sql = 'select top ' + str(@count)+' * from '+ @tablename + ' where '+ @column +' not in'+
('select top ' + str((@currentpage-1)*@count) + @column + ' from ' + @tablename)
exec(@sql)
go
exec page 2,3,'bookinfo','bookid'
--计算出总页数
总页数=（总条数（每页显示的条数-1））/每页显示的条数
--sql语句查询第N页数据（每页显示M条数据）
use bookshop
go
i=(n-1)*m
select top n * from bookinfo where bookid not in (select top i bookid from bookinfo )
--事务与游标
--模拟汇款操作，典型的事务举例(其实sql中的关键字go就是一个事务)
begin transaction tran_bank --transaction可以简写为tran
--定义一个记录错误的变量
declare @tran_error int
set @tran_error = 0
--在三毛的账户减去钱
update bank set currentMoney=currentMoney + 1000 where customerName ='三毛'
set @tran_error = @tran_erro + @@error
--在小毛的账户中增加钱
update bank set currentMoney=currentMoney -1000 where customerName='小毛'
set @tran_error =@tran_error +@@error
if @tran_error <> 0
begin
--执行出错，立即回滚事务
rollback transaction
print '转账失败，双方交易取消'
end
else
begin
--没有发现出错
commit transaction
print '转账成功，双方交易完成'
end
go
--视图与索引
--如果已经存在就删除
if exists (
select *from dbo.sysobject where name='view_stuInfo_stuMarks'
)
drop view view_stuInfo_stuMarks --删除视图的语法
go
create view view_stuInfo_stuMarks
as
--内联接查询语句
select * from stuinfo as stu inner join scoreinfo as sco on stu.sid=sco.sid
go
--查看视图
select *from view_stuInfo_stuMarks
--触发器
--insert触发器
--卡表
create table card
(
id int primary key,
card_name varchar(40),
banlance money
)
go
insert into card values(001,'张三',2000)
insert into card values(002,'李四',8000)
insert into card values(003,'王五',5000)
--交易表
create table translate
(
t_id int primary key,
card_id int,
t_date datetime,
t_type varchar(4),
salary money
)
insert into translate values(001,001,getdate(),'存',400)
insert into translate values(002,002,getdate(),'取',400)
insert into translate values(0004,003,getdate(),'存',400)
go
if object_id('ex') is not null
drop trigger ex
go
create trigger ex
on translate for insert
as
declare @id int
declare @c money
declare @type varchar(4)
--inserted逻辑表
select @id =card_id,@c=salary,@type=t_type from inserted
if @type='存'
begin
update card set banlance =banlance+@c where id=@id
end
else
begin
update card set banlance =banlance-@c where id=@id
end