数据库高级

/*************************************************************************************/
/*************************************************************************************/
/******************************sql server数据库高级复习********************************/
/*************************************************************************************/


--第二章 

--1.变量
--DECLARE @变量名 变量类型

DECLARE @ID INT
SET @ID=12
PRINT @ID

--SELECT @ID FROM TB WHERE ....


--2.全局变量
PRINT @@ERROR        --返回上一条sql语句执行错误号
PRINT @@IDENTITY    --返回最后插入的标识值得系统函数


--3.逻辑控制语句
/*
IF<条件表达式>
    BEGIN
        <代码块>
    END
ELSE
    BEGIN
        <代码块>
    END    


WHILE<条件表达式>
    BEGIN
        <代码块>
    END

CASE
    WHEN <条件表达式> THEN <运算式>
    WHEN <条件表达式> THEN <运算式>
    WHEN <条件表达式> THEN <运算式>
    ELSE ''
END
*/
--第三章 高级查询---

--1、子查询
--1.1 什么是子查询
--可以用在增删改查语句中的查询，也可叫内部查询或嵌套查询

--1.2为什么要使用子查询
--降低SQL复杂度，增强可读性

--1.3子查询的三种用法
--子查询作为where条件(保证返回值是唯一的)
--查询马文才的成绩
select * from stuexam where stuno = (select stuno from stuinfo where stuname= '马文才')

--子查询作为表使用
--查询笔试成绩大于80分的学员信息
select * from stuinfo s1 left join (select * from stuexam where writtenExam > 80) s2
on s1.stuno = s2.stuno

--子查询作为列使用（不好理解）
--查询笔试成绩大于80分的学员信息
select *,(select writtenexam from stuexam s2 where writtenExam > 80 and s1.stuno = s2.stuno) as 分数 
from stuinfo s1

---第三章 SQL高级查询（第二次课）---

--Exists 和 Not Exists
if exists (select * from sys.databases where name='studentDB')
    drop database studentDB

--查询机试及格的学生信息
select * from stuexam where not exists (select * from stuexam where labExam >= 120)

--some any all
--必须跟子查询
--some any 一些（只要比最小的大）
--all 全部（比最大的都大）

--0 1 2 3 4 5 6 7 8 9
--2 4 6 8
--3 4 5 6 7 8 9
--9 

select * from stuinfo
--查询出比“李斯文”和“梅超风”年龄大的有那些
select * from stuinfo where stuage > some(select stuage from stuinfo where stuname = '李斯文' or stuname ='梅超风')
--查询出比“李斯文”和“梅超风”年龄都大
select * from stuinfo where stuage > all(select stuage from stuinfo where stuname = '李斯文' or stuname ='梅超风')

------------------我是分割线-----------------------------

--聚合技术
--聚合函数(avg、sum、count)
--compute 和 compute by 用一个查询可以得到两个结果（一个明细一个汇总）
select * from stuinfo
--查询班长所有的成绩及平均分
select * from stuexam
select avg(labexam) from stuexam
--compute
select * from stuexam compute avg(labexam)
--compute by(可以根据分组后的结果再进行汇总)
--查询所有人的平均年龄
select * from stuinfo order by stuage compute avg(stuage) by stuage
select * from stuinfo order by stuaddress compute avg(stuage) by stuaddress


------------------我是分割线-----------------------------
--排序函数
--查询结果进行排序,可以明确具体名次
--三个排序函数
--1 Row_Number() 没有并列，不跳空  1 2 3 4
--2 Rank()       有并列，有跳空    1 2 2 4 
--3 Dense_Rank() 有并列，不跳空    1 2 2 3

--语法：
select Row_Number() over(order by 分数 desc) as 别名,列1,列2 from stuexam

--查询分数表的机试排名及个人信息
select row_number() over(order by labexam desc) as 排名,stuname,labexam from stuinfo as s1,stuexam as s2 where s1.stuno = s2.stuno
select rank() over(order by labexam desc) as 排名,stuname,labexam from stuinfo as s1,stuexam as s2 where s1.stuno = s2.stuno
select dense_rank() over(order by labexam desc) as 排名,stuname,labexam from stuinfo as s1,stuexam as s2 where s1.stuno = s2.stuno


--公式表达式(临时表)
--语法：with 临时表的名字(查询的字段)
--      as
--     (select 语句)

with test(stuno,stuname,labexam)
as
(
    select s1.stuno,stuname,labexam from stuinfo as s1,stuexam as s2 where s1.stuno = s2.stuno
)
select * from test
go

--视图
create view test
as
select s1.stuno,stuname,labexam from stuinfo as s1,stuexam as s2 where s1.stuno = s2.stuno
go
select * from test


-----------第四章 索引和视图----------------

--数据库对象：索引、视图、事务、游标、存储过程、触发器

--准备工作
create database Test
go

use Test
go

create table stuinfo
(
    stuid int,
    stuname varchar(20)
)

declare @num int
set @num = 1
while(@num < 1000001)
begin
    set @num = @num + 1
    insert into stuinfo values(@num,'eric')
end

select count(*) from stuinfo


---索引
--1、什么是索引
--索引相当于给表中的数据编个号，方便查询，提高查询速度

--2、索引的分类
--聚集索引和非聚集索引
--聚集索引,只有一个，而且一定是主键
--非聚集索引，至多249


--3、创建索引
--手动和半手动（写sql语句）

--查询一次数据
select * from stuinfo where stuid = 9999

--语法
create  clustered --聚集索引
index IX_stuid1
on stuinfo(stuid)
--with fillfactor = 0-100 --填充因子

create table stuexam
(
    examid int primary key,
    score int
)

--非聚集索引
--可以在非主键字段
create nonclustered --非聚集索引
index IX_stuname
on stuinfo(stuname)

select * from stuinfo where stuname = 'eric' and stuid = 9999

--使用索引（必须在条件语句中使用这个字段）
--显式使用
select * from stuinfo with(index=IX_stuname) where stuname = 'eric' and stuid = 9999
--隐式使用
select * from stuinfo where stuname = 'eric' and stuid = 9999

--删除索引
if exists(select * from sysindexes where name = 'IX_stuname')
    drop index stuinfo.IX_stuname
go
create nonclustered --非聚集索引
index IX_stuname
on stuinfo(stuname)

--索引只在查询时起作用

--------------------------我是分割线---------------------------

--视图
--1、什么是视图
--虚拟表，对原表数据没有影响

--2、视图的优点
--娜姐 学生信息电话、住址等信息
--eric 学生的成绩

select * from stuscore

--delete from stusocre
drop view stuscore

--2.1 按需所取，只放某个用户关心的数据，简化表的结构
--2.2 保护原表中的数据（数据信息，保护表名）

--3、视图的创建语法
create view 视图名
as
查询语句
go


--4、加密视图
create view stuscore
as
select stuname,stuage,stuaddress from stuinfo,stuexam where stuinfo.stuno = stuexam.stuno
go
create view stuscore1
as
select stuname,stuage,stuaddress from stuinfo,stuexam where stuinfo.stuno = stuexam.stuno
go

--查询当前数据下所有视图的信息
select * from information_schema.views

--加密
create view stuscore2
with encryption
as
select stuname,stuage,stuaddress from stuinfo,stuexam where stuinfo.stuno = stuexam.stuno
go

---第六章 存储过程

--优点：
--1、减少网络流通量（传输存储过程的名字）
--2、执行速度快（预编译）
--3、安全(权限)
--4、相互隔离（锁）


--存储过程分类
--不带参数

--带参数
--带输入参数
create proc proc_test
@id int = 1,
@name varchar(20)
as
sql语句
go

--带默认值的存储过程(调用的时候有区别，有默认值，调用该存储过程参数可以省略)
SELECT * from stuinfo

create proc proc_insertStuinfo
@stuno varchar(10),
@stuname varchar(10),
@stuage int = 18,
@stuadd varchar(20) = '湖北武汉'
as
insert into stuinfo values(@stuno,@stuname,@stuage,@stuadd)
go

--调用存储过程
exec proc_insertStuinfo 's666','eric'
exec proc_insertStuinfo 's777','lily',@stuadd = '北京'

select * from stuinfo

--带输出参数
--output
--输出马文才的机试成绩
create proc proc_score
@name varchar(20),
@score int output
as
select @score=labExam from stuinfo as t1,stuexam as t2 where t1.stuno = t2.stuno and stuname = @name
go

--调用存储过程
declare @score123 int
exec proc_score '马文才',@score123 output --输出必须在调用是要标明output关键字
print '马文才的成绩是'+convert(varchar(20),@score123)

--Raiserror(自定错误) --try catch
--语法
create proc proc_math
@num1 int,
@num2 int = 0
as
    if(@num2 = 0)
        begin
            raiserror('亲，除法的除数不能为0',15,1)--catch
            return --返回
        end
    declare @result int
    set @result = @num1 / @num2    
go

@@error

exec proc_math 100

create proc proc_trans1
@trnsmoney int
as
    if(@trnsmoney > 30000)
        begin
            raiserror('亲，金额太大，请分批处理',15,1)--catch
            return --返回
        end    
go

exec proc_trans1 50000


create proc pro_stu1
@num int output
as
create table tab
(
    num int
)
go

select rand()*100000

-----第七章 触发器---

--1、什么是触发器？
--特殊的存储过程，事务，被动执行的sql语句

--2、为什么使用触发器?
--一般用在增删修的动作之后，还要自动做的sql指令

--3、触发器的分类

--DML 数据操作语言 Data Mandatory Language
--select insert update delete 

--DDL 数据定义语言 Data Define Language
--create drop 

--DCL 数据控制语言 Data Control Language
--grant revoke

--创建添加触发器
create trigger tri_first
on stuinfo --创建触发动作表
for  insert--delete insert update(三选一) --创建什么触发器
as
--触发动作
declare @stuno varchar(20)
select @stuno = stuno from inserted
insert into stuexam values(@stuno,0,0)
go

--测试触发器
insert into stuinfo values ('s2508','mike','18','上海')


--创建添加触发器2
--往商品记录表中添加一条记录，修改库存
create trigger  tri_goodinfo
on goodinfo
for insert 
as
--触发动作
declare @id int
select @id = goodid from inserted
update 库存表 set 库存数量 = 库存数量 -1  where 商品id = @id
go

--删除触发器
create trigger tri_stuinfo_delete
on stuinfo 
for delete
as
declare @no varchar(10)
select @no = stuno from deleted 
delete from stuexam where stuno =@no 
go

--测试删除触发器
delete from stuinfo where stuno = 's2508'

--修改触发器
create trigger tri_stuinfo_update
on stuinfo
for update
as
    declare @no varchar(20)
    select @no = stuno from deleted 
    insert into stuexam values(@no,0,0)
go

--测试
update stuinfo set stuname = 'eric' where stuname = 'lily'

--修改触发器2
create trigger tri_stuexam_update
on stuexam
for update
as
    declare @before int
    declare @after int
    select @before = labexam from deleted
    select @after = labexam from inserted
    print '亲，修改完成，修改前的分数为：'+convert(varchar(10),@before)
    print '亲，修改完成，修改后的分数为：'+convert(varchar(10),@after)    
go

--测试
update stuexam set labexam = 59 where labexam =90 and stuno = 's2506'

 

转载于:https://www.cnblogs.com/gx-143/p/5920489.html