/***********创建数据库************/
use master
go
--创建数据库
if exists (SELECT * from sysdatabases where name='Students')
drop database Students
go
create database Students
go
--使用Students 数据库
use Students
go
--创建学生信息表
IF EXISTS (SELECT * from sysobjects where name='StuInfo')
DROP TABLE StuInfo
go
CREATE TABLE StuInfo
(
StuID int identity primary key, --学号
StuName varchar(10) not null, --姓名
StuSex varchar(2) not null, --性别
ClassID varchar(10) --班级编号
)
go
--创建学生成绩表
IF EXISTS (SELECT * FROM SYSOBJECTS WHERE NAME='StuMark')
DROP TABLE StuMarks
CREATE TABLE StuMarks
(
ExamNO int identity primary key ,
StuID int references stuinfo(stuid),
Subject varchar(20),
Score int check(Score <= 100 and Score >= 0)
)
go
/***************** 添加数据 *****************/
INSERT INTO StuInfo (StuName, StuSex, ClassID)
SELECT '张三','女', 1 UNION
SELECT '李四','男', 1 UNION
SELECT '王五','男', 1 UNION
SELECT '赵六','女', 2 UNION
SELECT '钱七','女', 2
INSERT INTO StuMarks (StuID, Subject, Score)
SELECT 1, 'Java', 80 UNION
SELECT 1, 'SQL', 82 UNION
SELECT 1, 'HTML', 85 UNION
SELECT 2, 'Java', 81 UNION
SELECT 2, 'SQL', 60 UNION
SELECT 2, 'HTML', 70 UNION
SELECT 3, 'Java', 90 UNION
SELECT 3, 'SQL', 85 UNION
SELECT 3, 'HTML', 70 UNION
SELECT 4, 'Java', 61 UNION
SELECT 4, 'SQL', 68 UNION
SELECT 5, 'Java', 81 UNION
SELECT 5, 'SQL', 65 UNION
SELECT 5, 'HTML', 90
select * from stuinfo
select * from stumarks
select *
from stuinfo a,stumark b
where a.stuid = b.stuid
select *
from stuinfo a inner join stumark b
on a.stuid = b.stuid
declare @@num int
set @@num = 1
select @@num
select @@error
go
select @@error
--如果没有错误那么@@error的值是0
--输出print是以文本 select以表格的形式
--select 关心在结果集中出现的列
--from关心表名(数据源) 数据源应该提供所有需要的数据
--where关心在结果集中出现的行
--子查询在数据源中
select *
from stuinfo a,(select stuid from stumarks where score > 80) b
where a.stuid = b.stuid and stuname='李四'-- and score >= 82
select *
from (select stuid,stuname from stuinfo where stuname = '李四') a,(select stuid,score from stumarks where score > 80) b
where a.stuid = b.stuid --and stuname='李四'
select stuname,score
from (select stuid,stuname from stuinfo where stuname = '李四') a,(select stuid,score from stumarks where score > 80) b
where a.stuid = b.stuid
--找某个指定姓名及以前的所有学员信息
--使用变量
declare @name varchar(50)
set @name = '钱七'
declare @num int
select @num = stuid from stuinfo where stuname = @name
select * from stuinfo where stuid <= @num
--使用子查询
declare @name varchar(50)
set @name = '钱七'
select *
from stuinfo a,(select stuid from stuinfo where stuname = @name) b
where a.stuid <= b.stuid
--使用子查询2
declare @name varchar(50)
set @name = '张三'
select *
from stuinfo
where stuid <= (select stuid from stuinfo where stuname = @name)
--查询所有HTML成绩，如果没有成绩的学员赋null
select *
from stuinfo a ,stumarks b
where a.stuid = b.stuid and b.subject='HTML'
select *
from stuinfo a left join stumarks b
on a.stuid = b.stuid
where b.subject = 'HTML'
select *
from stuinfo a left join (select * from stumarks where subject='HTML') b
on a.stuid = b.stuid
select *
from stuinfo a ,(select * from stumarks where subject='HTML') b
where a.stuid = b.stuid and b.subject='HTML'
--将子查询中提取出来的数据与原始数据每一数据一一对应
select a.*,(select score from stumarks b where a.stuid = b.stuid and subject='HTML')
from stuinfo a
select *
from stuinfo
where stuid in(select stuid from stuinfo where stuid > 7)
select stuid
from stuinfo a
where stuid not in(select stuid from stumarks where subject='HTML')
select *
from stuinfo
where stuid in(1,3,5)
select *
from stuinfo
where stuid > all(select stuid from stuinfo )
select *
from stumarks
compute avg(score)
select subject,avg(score)
from stumarks
group by subject
compute avg(avg(score))
select *,rank() over(order by score)
from stumarks
select *,dense_rank() over(order by score)
from stumarks
select *,row_number() over(order by score)
from stumarks
select top 5 *
from stumarks
where examno >= 10
order by examno
--通式 索引---------------------------------------------------
if exists(select * from sys.indexes where [name] = 'mytest')
drop index mytest
go
---nonclustered 非聚集索引 clustered uniqued
create nonclustered index mytest
on stuinfo(stuname)
--getdate()函数是SQL提供用于获取时间
declare @f_date datetime
set @f_date = getdate()
--select @f_date
select * from stuinfo with(index=mytest)
declare @b_date datetime
set @b_date = getdate()
--select @b_date
select @b_date - @f_date
declare @f_date_1 datetime
set @f_date_1 = getdate()
--select @f_date
select * from stuinfo
declare @b_date_1 datetime
set @b_date_1 = getdate()
--select @b_date
select @b_date_1 - @f_date_1
--create database test
--当需要的内容是一张表中的一部分的时候
--当需要的内容是多张表中的各一部分的时候
select * from view_1
--创建视图
if exists(select * from sys.views where [name] = 'myview')
drop view myview
go
create view myview
as
--放置所有能够正常运行的SQL的查询
select * from stuinfo
go
select * from sys.views
if exists(select * from sys.views where [name] = 'myview1')
drop view myview1
go
create view myview1
as
select a.stuid,a.stuname,b.subject,b.score
from stuinfo a,stumarks b
where a.stuid = b.stuid
go
select * from myview1
--对一个数据混乱的表添加一个排序列并可以抽取其中任意需要的数据
--测试ROW_NUMBER
select a.stuname,b.subject,b.score,row_number() over(order by score) as myid
from stuinfo a,stumarks b
where a.stuid = b.stuid
if exists(select * from sys.views where [name] = 'myview2')
drop view myview2
go
create view myview2
as
select a.stuname,b.subject,b.score,row_number() over(order by score) as myid
from stuinfo a,stumarks b
where a.stuid = b.stuid
go
select *
from myview2
where myid > 5
--视图在深层次的意义是对表进行封装，只给你看到你应该看到的
if exists(select * from sys.views where [name] = 'myview3')
drop view myview3
go
create view myview3
as
select * from stuinfo
go
--在某种情况下可以通过视图修改原始数据
insert myview3 values('aaa','男',2)
select * from myview3
select * from stuinfo
--
if exists(select * from sys.views where [name] = 'myview4')
drop view myview4
go
create view myview4
as
select stuname,stusex from stuinfo
go
select * from myview4
insert myview4 values('bbb','女')
select * from stuinfo
--当视图牵扯到多张表的时候(列列表)是不允许进行修改的
if exists(select * from sys.views where [name] = 'myview5')
drop view myview5
go
create view myview5
as
select stuname,subject,score
from stuinfo a ,stumarks b
where a.stuid = b.stuid
go
select * from myview5
insert myview5 values('ccc','HTML',23)
-----
if exists(select * from sys.views where [name] = 'myview6')
drop view myview6
go
create view myview6
as
select a.*
from stuinfo a ,stumarks b
where a.stuid = b.stuid
go
select * from myview6
insert myview6 values('sdfklj','kk',1)
--
if exists(select * from sys.views where [name] = 'myview7')
drop view myview7
go
create view myview7
as
select distinct a.*
from stuinfo a ,stumarks b
where a.stuid = b.stuid
go
select * from myview7
insert myview7 values('sdfklj','kk',1)
--
if exists(select * from sys.views where [name] = 'myview8')
drop view myview8
go
create view myview8
as
select b.stuid 编号,stuname 姓名,stusex 性别,classid 班级编号
from stuinfo a ,stumarks b
where a.stuid = b.stuid
go
select * from myview8
insert myview8 values('sdfklj','kk',1)
--索引和视图
--为什么使用索引 在针对大数据量的时候的查询的速度
--聚集，非聚集，唯一
--为什么使用视图
--事务和游标
--事务
IF EXISTS (SELECT * from sysobjects where name='bank')
DROP TABLE bank
go
CREATE TABLE bank
(
username varchar(50),
moneys money check(moneys >= 1)
)
go
insert bank values('aaa',1000)
insert bank values('bbb',1)
update bank set moneys = moneys - 1000 where username = 'aaa'
--约束冲突的上面这一行本身就执行过，而且进行过反向操作
update bank set moneys = moneys + 1000 where username = 'bbb'
select * from bank
--事务完成：一起执行完 一起回滚
begin tran test --打开事务的起点
declare @num_error int,@num int,@uname varchar(50),@uname1 varchar(50)
set @num_error = 0 --用来记录是否出现错误
set @num = 1000
set @uname = 'aaa'
set @uname1 = 'bbb'
--在使用事务的时候多半结合全局变量@error
update bank set moneys = moneys - @num where username = @uname
set @num_error = @num_error + @@error --通过全局变量记录错误
update bank set moneys = moneys + @num where username = @uname1
set @num_error = @num_error + @@error
if(@num_error <> 0)--根据前面的记录判断如何处理
begin
print 'error'
rollback --当出现了错误就回滚
end
else
begin
print 'ok'
commit --当没有错误的时候就提交
end
--1.开始事务
--2.记录可能出现的错误
--3.根据记录判断是否提交或回滚事务
begin tran t1
insert bank values('ccc',1000)
save tran t2
insert bank values('ddd',1000)
save tran t3
insert bank values('eee',1000)
rollback tran t3
commit tran t2
select @@trancount
--
if exists(select * from sys.syscursors where cursor_name ='mycursor')
deallocate mycursor
go
declare mycursor
cursor scroll--默认是只向前
for select * from stuinfo
open mycursor
declare @id int,@names varchar(50),@sex varchar(50),@cid int
fetch first from mycursor into @id,@names,@sex,@cid
while(@@fetch_status = 0)
begin
if(@sex = '男')
begin
set @sex = '*'
end
--print @names+@sex
fetch next from mycursor into @id,@names,@sex,@cid
end
close mycursor
select * from sys.syscursors
--1.创建游标
--2.打开
--3.定义需要的变量 要求和游标中的变量列表在数目，顺序和数据类型上一致
--4.需要的操作
-- @@fetch_status 如果不等于0 就是到了结尾
-- 记得每次操作要将游标的指针向下或向上移动
--5.关闭
--一张表没有主建，也没有identity现在需要提取其中的第3-6行 (X row_number)
declare mycursor
cursor scroll
for
select * from stuinfo
go
open mycursor
declare @id int,@name varchar(50),@sex varchar(50),@classid int
fetch first from mycursor into @id,@name,@sex,@classid
while(@@fetch_status = 0)
begin
print convert(varchar(50),@id)+@name
fetch next from mycursor into @id,@name,@sex,@classid
end
close mycursor
create view myview
as
select stuname,subject,score
from stuinfo a,stumarks b
where a.stuid = b.stuid
go
--存储过程
--类似Java中的方法，将一些功能打包运行(功能模块)
--隐藏底层信息
select * from sp_databases
--exec专门用来执行存储过程
exec sp_databases
exec sp_tables
--自定义
-- drop proc myproc
if exists( select * from sys.procedures where [name]='myproc')
drop proc myproc
go
create proc myproc
as
begin
select * from stuinfo
end
go
select * from sys.procedures
--create？
if exists( select * from sys.procedures where [name]='myproc1')
drop proc myproc1
go
create proc myproc1
as
begin
create table test
(
id int
)
end
go
exec myproc1
select * from test
--参数
if exists( select * from sys.procedures where [name]='myproc2')
drop proc myproc2
go
create proc myproc2
--参数列表
@id int
as
begin
select * from stuinfo where stuid = @id
end
go
exec myproc2 3
--存储过程可以将对底层的操作隐藏
--存储过程在第一次运行的时候会有一个编译的过程，所以理论上来讲当后续运行的时候
--运行速度要快(针对没有将内容进行存储过程打包)
--如果直接运行这些语句会占用资源
--多个参数
if exists( select * from sys.procedures where [name]='myproc3')
drop proc myproc3
go
create proc myproc3
--参数列表
@id int,
@name varchar(50)
as
begin
select * from stuinfo where stuid = @id and stuname = @name
end
go
exec myproc3 3,'王五'
exec myproc3 @name='王五',@id=3
--关于默认值
if exists( select * from sys.procedures where [name]='myproc4')
drop proc myproc4
go
create proc myproc4
--参数列表
@name varchar(50),
@sex varchar(4) = '男',--设置默认值
@classid int
as
begin
insert into stuinfo values(@name,@sex,@classid)
end
go
exec myproc4 'aa',1
--默认值的问题
exec myproc4 'aa',default,1
exec myproc4 @name='aa',@classid=1
if exists( select * from sys.procedures where [name]='myproc5')
drop proc myproc5
go
create proc myproc5
--参数列表
@name varchar(50),
@classid int,
@sex varchar(4) = '男'--设置默认值
as
begin
insert into stuinfo values(@name,@sex,@classid)
end
go
exec myproc5 'aa',1
--返回值
if exists( select * from sys.procedures where [name]='myproc6')
drop proc myproc6
go
create proc myproc6
--参数列表
@id int,
@name varchar(50),
@num int output,
@sex varchar(4) = '男'--设置默认值
as
begin
--输出查找到的对应的学院的classid
--如果需要返回值那么直接像使用变量一样
select @num = classid from stuinfo where and
--select classid from stuinfo where and
end
go
declare @num_out int
declare @name varchar(50)
set @name = '李四'
exec myproc6 1,@name,@num_out output
select @num_out
--存储过程的参数可以使用变量进行输入
--存储过程的输出参数在接收其传回的数据的时候一定要加上关键字output，如果没加逻辑没错
--得不到内容
select * from stuinfo
--
if exists( select * from sys.procedures where [name]='myproc7')
drop proc myproc7
go
create proc myproc7
--参数列表
int,
@num int output,
@name varchar(50),
@sex varchar(4) = '男'--设置默认值
as
begin
--输出查找到的对应的学院的classid
--如果需要返回值那么直接像使用变量一样
select @num = classid from stuinfo where and
--select classid from stuinfo where and
end
go
declare @num_out int
set @num_out = 1
declare @name varchar(50)
set @name = '李四'
exec myproc7 @num_out output,@name
select @num_out
--存储过程的输出参数既可以作为输入变量使用同时可以作为输出变量使用
--
delete from stuinfo where stuid = 1
declare @num int
set @num = ?
exec myproc8 @num
--出现问题的原因：因为存在主外键 当删除主键方的数据的时候可能外键数据存在就会触发约束
if exists( select * from sys.procedures where [name]='myproc8')
drop proc myproc8
go
create proc myproc8
--参数列表
int,
@num int
as
begin
--看外键表是否存在相应的数据
delete from stumarks where stuid = @num
delete from stuinfo where stuid = @num
end
go
delete from stuinfo where stuid = 1
select * from stumarks
exec myproc8 1
--在刚才的内容上添加：当删除外键表信息的时候为了保存相应的数据将被删除的数据(外键)备份
--备份表的表名自行自定
--只要数据
--在实现删除之前：
--1.判断需要的表是否存在 如果不存在 创建 如果存在 (删除X)
--2.得到要删除的数据 (游标)
--3.将得到的数据添加到对应的表中
--4.删除外键和主键表中的数据
if exists( select * from sys.procedures where [name]='myproc9')
drop proc myproc9
go
create proc myproc9
--参数列表
@num int
as
begin
if not exists(select * from sys.objects where [name] = 'mytest')
begin
create table mytest
(
subject varchar(50),
score float
)
end
if exists(select * from sys.syscursors where [cursor_name] = 'mycursor_mytest')
deallocate mycursor_mytest
declare mycursor_mytest
cursor scroll
for select subject,score from stumarks where stuid = @num
open mycursor_mytest
declare @sub varchar(50),@score float
fetch first from mycursor_mytest into @sub,@score
while(@@fetch_status = 0)
begin
print @sub+' '+convert(varchar(50),@score)
insert mytest values(@sub,@score)
fetch next from mycursor_mytest into @sub,@score
end
--看外键表是否存在相应的数据
--delete from stumarks where stuid = @num
--delete from stuinfo where stuid = @num
end
go
exec myproc9 2
--drop table mytest
select * from mytest
-----------------------------------------------------------------------------
if exists( select * from sys.procedures where [name]='myproc8')
drop proc myproc8
go
create proc myproc8
--参数列表
@num varchar(50) = '*'
as
begin
--看外键表是否存在相应的数据
if(@num = '*')
begin
select * from stuinfo
end
else
begin
declare @coun int
select @coun = count(*) from stuinfo where
if(@coun > 0)
begin
--有
end
else
begin
--没有
end
end
end
go
--触发器：特殊的存储过程
--触发器只有SQL管理系统可以调用，当预定的操作发生时有系统调用(地雷)
--触发器的分类
--触发器分为前触，后触
--触发器可以针对表和视图
--库存
if exists(select * from sys.objects where [name]='cun')
drop table cun
go
create table cun
(
cid int,
num int
)
go
--物品
if exists(select * from sys.objects where [name]='obj')
drop table obj
go
create table obj
(
oid int,
names varchar(50)
)
go
--销售
if exists(select * from sys.objects where [name]='o')
drop table o
go
create table o
(
ooid int,
snum int
)
insert into obj values(1,'mp3')
insert into cun values(1,50)
insert into o values(1,3)
select * from obj
select * from cun
select * from o
--在向某张表插入数据的同时能够进行关联的操作
create proc mytest
@id int,
@num int
as
begin
declare @num_cun int
select @num_cun = num from cun where cid = @id
update cun set num = @num_cun - @num where cid = @id
insert into o values(@id,@num)
end
go
exec mytest 1,3
--当希望在做增，删，改操作(修改数据)的时候同时完成一些关联的操作的时候可以使用触发器
--触发器有两张非常重要的表inserted deleted 多半有需要的数据
--只有在触发器触发的条件下才有这两张表
--inserted表放的新插入的数据
--deleted表放的删除的数据
--如果做insert: inserted 有 deleted 没有
--如果做delete: inserted 没有 deleted 有
--如果做update: inserted 有(新数据) deleted 有(被删除的数据)
if exists(select * from sys.triggers where [name] = 'mytri')
drop trigger mytri
go
create trigger mytri
on o
for insert
as
begin
select * from inserted
select * from deleted
end
go
--exec mytri--错
--delete
if exists(select * from sys.triggers where [name] = 'mytri1')
drop trigger mytri1
go
create trigger mytri1
on o
for delete
as
begin
select * from inserted
select * from deleted
end
go
--update
if exists(select * from sys.triggers where [name] = 'mytri2')
drop trigger mytri2
go
create trigger mytri2
on o
for update
as
begin
select * from inserted
select * from deleted
end
go
update o set snum=5 where ooid=1
delete from o where ooid=1
select * from o
select * from sys.triggers
--完成上面存储过程的操作
if exists(select * from sys.triggers where [name] = 'mytri4')
drop trigger mytri4
go
create trigger mytri4
on o
for insert
as
begin
declare @id int,@num int
select @id=ooid,@num=snum from inserted--ooid,snum
declare @num_cun int
select @num_cun = num from cun where cid = @id
--print convert(varchar(50),@num_cun)+' '+convert(varchar(50),@num)+' '+convert(varchar(50),@id)
update cun set num = @num_cun - @num where cid = @id
--insert into o values(@id,@num)
end
go
--我们现在使用的是后触所以这个操作(相当于)已经完成
if exists( select * from sys.procedures where [name]='myproc8')
drop proc myproc8
go
create proc myproc8
--参数列表
int,
@num int
as
begin
--看外键表是否存在相应的数据
delete from stumarks where stuid = @num
delete from stuinfo where stuid = @num
end
go
delete from stuinfo where stuid = 1
select * from stumarks
exec myproc8 1
--使用触发器完成上面存储过程在删除主键的时候删除外键而不触发外键约束
if exists(select * from sys.triggers where [name] = 'mytri5')
drop trigger mytri5
go
create trigger mytri5
on stuinfo
for delete
as
begin
--print 'a'
declare @num int
select @num=stuid from deleted
delete from stumarks where stuid = @num
delete from stuinfo where stuid = @num
end
go
--使用前置触发器
--前置触发器相当于替换了当前的操作
if exists(select * from sys.triggers where [name] = 'mytri6')
drop trigger mytri6
go
create trigger mytri6
on stuinfo
instead of delete
as
begin
--print 'a'
declare @num int
select @num=stuid from deleted
delete from stumarks where stuid = @num
delete from stuinfo where stuid = @num
--已经通过了改触发器的区间理论上来讲不会再触发当前触发器
end
go
delete from stuinfo where stuid = 2
select * from stuinfo
select * from stumarks
--前置触发器在某个操作上理论上来讲只能放一个
if exists(select * from sys.triggers where [name] = 'mytri7')
drop trigger mytri7
go
create trigger mytri7
on stuinfo
instead of delete
as
begin
print 'a'
end
go
--后置触发器是可以链接
if exists(select * from sys.triggers where [name] = 'mytri8')
drop trigger mytri8
go
create trigger mytri8
on stuinfo
for delete
as
begin
print 'a'
end
go
if exists(select * from sys.triggers where [name] = 'mytri9')
drop trigger mytri9
go
create trigger mytri9
on stuinfo
for delete
as
begin
print 'b'
end
go
if exists(select * from sys.views where [name]='myview')
drop view myview
go
create view myview
as
select * from stuinfo
go
select * from myview
--后触不能用于视图，所以应该使用前置
if exists(select * from sys.triggers where [name] = 'mytri10')
drop trigger mytri10
go
create trigger mytri10
on myview
instead of insert
as
begin
print 'b'
end
go
insert myview(stuname,stusex,classid) values('aa','a',1)
insert into stuinfo values('hsc','男',1)
if exists(select * from sys.triggers where [name] = 'del')
drop trigger del
go
create trigger del
on stuinfo
for insert --update delete
as
begin
delete from stuinfo where stuId >5 --from delete
end
go
if exists(select * from sys.triggers where [name] = 'up')
drop trigger up
go
create trigger up
on stuinfo
for delete
as
begin
select * from stuinfo
-- delete from stuinfo where stuId >5 --from delete
end
go
delete from stuinfo where stuid=4
if exists(select * from sys.triggers where [name] = 'updates')
drop trigger updates
go
create trigger updates
on stuinfo
instead of update --instead of前触 先触发执行里面的语句 没执行sql语句 for后触 先执行操作 再执行触发器的语句
as
begin
select * from stuinfo
-- delete from stuinfo where stuId >5 --from delete
end
go
update stuinfo set stuname='黄少成123' where stuid=7
select * from stuinfo
select * from stumarks
exec sp_helptext 'del' --查看触发器的
306
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
