SQL 相关知识点

SQL 相关知识点

数据库的创建

数据库的分离与附加

当需要把数据库从一个位置复制到另外的地点时，比如从学校的机房复制到寝室的个人电脑中，需要分离和附加数据库

# 创建数据库
create database StudentDB_New
on primary
(
	name='StudentDB',
	filename='C:\Study File\DATA1\StudentDB.mdf',
	size=3mb,
	maxsize=unlimited,
	filegrowth=1mb
),
(
	name='StuDB1',
	filename='C:\Study File\DATA2\StuDB1.ndf',
	size=3mb,
	maxsize=unlimited,
	filegrowth=1mb
),
(
	name='StuDB2',
	filename='C:\Study File\DATA2\StuDB2.ndf',
	size=3mb,
	maxsize=unlimited,
	filegrowth=1mb
)
log on
(
	name='StudentDB_log',
	filename='C:\Study File\DATA1\StudentDB_log.ldf',
	size=1mb,
	maxsize=1gb,
	filegrowth=10%
)

# 修改数据库
alter database StudentDB_New 
modify file
(
	name=StudentDB,
	size=5mb
)

# 修改数据库：添加一个辅助数据文件到新文件组，先创建文件组再创建文件
alter database StudentDB_New
add filegroup GData
go
alter database StudentDB_New
add file
(
	name='StuDB3',
	filename='C:\Study File\DATA2\StuDB3.ndf'
)
to filegroup GData

# 删除数据库的辅助数据文件StuDB3
alter database StudentDB_New
remove file StuDB3

# 删除数据库
drop database # -

# 分离数据库，不分离的话无法复制转移
exec sp_detach_db StudentDB_New

# 附加数据库，地址可以写拷贝到的新的位置
create database StudentDB_New
on primary
(
    name='StudentDB',
    filename='C:\Study File\DATA1\StudentDB.mdf'
)
for attach

数据库的分离和附加区别与数据库的删除：数据库的删除就是彻底把物理文件删除掉，像恢复都不可能，但数据库分离只是切掉了数据库和服务器的联系，还是可以再移植回去

创建架构

1. 架构的概念
   1. 架构是指包含图表、视图、过程的容器
   2. 位于数据库内部，而数据库位于服务器内部
   3. 特定架构中的每个安全对象都必须有唯一的名称
   4. 架构中安全对象的完全指定名称：server.database.schema.object (数据库服务器名.数据库名.架构名.对象名)

# 创建架构
use StudentDB_New
go
create schema my

# 删除架构
drop schema my

创建及维护数据表、创建分区表与视图

1. 数据表的创建与维护

use StudentDB_New
# 创建数据表
create table Tb_Stu_Info
(
	Stu_No varchar(20) primary key not null,
	Stu_Name nvarchar(10), 
	Stu_Sex nvarchar(6),
	Stu_Birthday date,
	Stu_Address nvarchar (200)
)

2. 数据表的修改

# 修改数表据Tb_Stu_Info, 增加名为STEST的列，数据类型为int，不为空
alter table dbo.Tb_Stu_Info
add stest int not null

# 修改STEST列的数据类型
alter table tb_stu_info
alter column stest varchar(20)

# 删除stest列
alter table dbo.Tb_Stu_Info
drop column stest
# 删除多列
alter table dbo.Tb_Stu_Info
drop column Stu_Birthday, Stu_Address

# 新建列
alter table dbo.Tb_Stu_Info
add Stu_Birthday date,
    Stu_Address nvarchar(200)

# 删掉数据表
drop table dbo.Tb_Stu_Info

创建分区表

1. 创建分区表的步骤

   1. 创建分区函数：告诉SQL如何将数据进行分区
   2. 创建分区方案：告诉SQL将已分区的数据放在哪个文件组中
   3. 创建表

2. 创建分区表的意义

分区是要把一个表数据拆分为若干子集合，也就是把把一个数据文件拆分到多个数据文件中，然而这些文件的存放可以依托一个文件组或这多个文件组，由于多个文件组可以提高数据库的访问并发量，还可以把不同的分区配置到不同的磁盘中提高效率，所以创建时建议分区跟文件组个数相同。

分区表的表数据按照你指定的规则分放到不同的文件里，把一个大的数据文件拆分成多个小的文件，还可以把这些小文件放在不同的磁盘下由多个 cpu 进行处理，这样文件的大小随着拆分而减小，还得到硬件系统的加强，自然对我们处理数据大大有利。所以大数据量的数据表，对分区的需要还是有必要的，因为它可以提高 select 效率，还可以对历史数据进行区分存档等。

3. 创建分区表

# 创建分区表
# 在stuseldb中，依据表 studentinfo 的 stdinfoyear 列来创建分区函数YearOrderPartFunc，边界值为2007和2009

# 首先创建分区函数，可以把 as 翻译成“依据，按照”
create partition function YearOrderPartFunc1(int)
as range left for values('2007', '2009')
# 其次创建分区方案
create partition scheme YearOrderPartScheme
as partition YearOrderPartFunc1
to([primary], fgroup2,fgroup3) 
# 最后依据分区方案创建表
create table Tb_StudentInfo
(
    StdInfoID int identity(1,1) not null,
    StdName varchar(20) not null,
    StdYear int not null
)
on YearOrderPartScheme(StdYear)
# int identity(1,1)表示从1开始递增，每次增加1

# 查看数据所在分区
select *, $partition.YearOrderPartFunc1(StdYear)
as '所属分区'
from dbo.Tb_StudentInfo

创建视图

视图与表不同，视图是一个虚表，即视图所对应的数据不进行实际的存储，数据库中只存储视图的定义，对视图的数据进行操作时，系统根据视图的定义去操作与视图相关联的基本表

# 创建视图，查询每门课程的授课老师
create view View_TeacherCourse
(课程名称,老师姓名)
as 
select CourseInfoName,TeachInfoName
from dbo.CourseInfo a join dbo.TeachCourse b
join dbo.TeachInfo c
on b.TeachCourseID=c.TeachInfoID
on a.CourseInfoID=b.CourseInfoID 

select * from dbo.View_TeacherCourse

# 查看视图的定义
sp_helptext View_TeacherCourse

# 修改视图
alter view View_TeacherCourse
(课程名称,老师姓名,职称)
as 
select CourseInfoName,TeachInfoName,TeachInfoKnow1
from dbo.CourseInfo a join dbo.TeachCourse b
join dbo.TeachInfo c
on b.TeachCourseID=c.TeachInfoID
on a.CourseInfoID=b.CourseInfoID 

# 删除视图
drop view View_TeacherCourse

创建表的约束、创建索引

1. 约束：约束是 SQL Sever 提供的自动保持数据库完整性的一种方法。有五种约束类型
   1. 主关键字约束（Primary Key Constraint）
   2. 外关键字约束（Foreign Key Constraint）
   3. 唯一性约束（Unique Constraint）
   4. 检查约束（Check Constraint）
   5. 默认约束（default Constraint）

# Not NULL 约束
create table StudentInfo
(
	StuInfoID int identity(1,1) not null,
	StuName varchar(20) not null,
	StuAge int 
)

# default 约束
# 创建班级信息表，系部默认为“软件学院”，班级人数默认“0”.
# 创建表的时候来定义约束
create table ClassInfo
(
	ClassInfoID int not null,
	ClassInDep varchar(20) not null default('软件学院'),
	ClassInfoCount int not null default(0)
)

# 修改表的时候来定义约束：
# 给班级编号增加默认值“1001”
alter table ClassInfo
add constraint df_classInfoID default(1001) for ClassInfoID

# unique 约束
# 字段值取值唯一，允许NULL值（只能出现一次）
create table StudentInfo2
(
	StdID int not null,
	StdInfoNum varchar(20) not null unique,
	StdInfoCard varchar(18)
)

alter table StudentInfo2
add constraint uq_card unique(StdInfoCard)

# Check 约束 (score between 0 and 100)
create table StudentScore
(
	StdID int not null,
	CourseID int not null,
	Score numeric check(Score>=0 and Score<=100),
	CourseStartData date not null
)

alter table StudentScore
add constraint ck_StartDate check(CourseStartDate>='2014-2-1'and CourseStartDate<='2014-2-8')

# parimary key 约束: 取值范围唯一且不允许NULL
create table StudentInfo3
(
	StuInfoID int primary key not null,
	StuName varchar(20) 
)

alter table StudentInfo3
add constraint pk_ID primary key(StuInfoID)

# 复合主键
create table studentCourse
(
	StdID int not null,
	CourID int not null,
	Score numeric not null,
	constraint pk_StdID_CourID primary key(StdID,CourID)
)

# foreign key 约束
create table studentInfo4
(
	StdID int primary key not null,
	StdName varchar(20) not null,
	ClassInfoID int foreign key references ClassInfo(ClassInfoID)
)

alter table studentInfo4
add constraint fk_ID foreign key(classInfoID) references ClassInfo(ClassInfoID)

外键约束：在新表的项里填的值一定要是在关联的表中的项里面有的值

# 删除约束
alter table ClassInfo
drop constraint DF__ClassInfo__Class__4E88ABD4

2. 索引

创建主键约束的时候会自动创建聚集索引，创建聚集索引后，表里面的顺序会自动排列：

# 创建索引，desc表示降序
create clustered index pk_StuInfoID
on StudentInfo(StuInfoID desc)

# 重新生成索引
alter index pk_StuInfoID
on StudentInfo rebuild

# 删除索引
drop index pk_StuInfoID
on StudentInfo

利用T-SQL语句插入、更新、删除记录

1. insert 语句的使用

   1. 插入多条语句

      # insert 语句，可以一次插入多条语句
      insert dbo.ClassInfo
      values(1,'A',10),
      (2,'B',12),
      (3,'C',14)
      
      insert dbo.ClassInfo(ClassInfoID,ClassInfoCount)
      values(18,17)
      

   2. 插入其他表的数据，将 A 表中所有女同学的学号、姓名、性别、身份证号码、家庭地址及联系电话插入到新创建的 B 表中

      # 插入其他表的数据
      create table StudentInfo1
      (
      StdInfoNum varchar(20) not null,
      StdInfoName varchar(20) not null,
      StdInFoSex varchar(2) not null,
      StdInfoCard varchar(20) not null,
      StdInfoAddress varchar(60) not null,
      StdInfoTel varchar(30) null
      )
      
      insert into dbo.StudentInfo1
      select StdInfoNum,StdInfoName,StdInFoSex,StdInfoCard,
      StdInfoAddress,StdInfoTel
      from dbo.StudentInfo
      where StdInfoSex='女'
      

2. update 语句的使用

# update语句，一定要带上where语句，不然会对所有数据进行修改
update dbo.StudentInfo
set StdInfoName='LK',
	StdInfoTel='18824260208',
	StdInfoEmail='jld@gmail.com'
where StdInfoname='蓝贝斯'

use StudentDB
go
update dbo.ClassInfo
set ClassInfoCount=100
where ClassInDep='音乐系'

# 修改 TeachInfo 表的备注列 TeachInfoRMK，为所有的兼职教师设置为每周上课不超过 10 课时
use StuSe1DB
go
update dbo.TeachInfo
set TeachInfoRMK='每周上课不超过10节'
from dbo.TeachInfo a join dbo.TeachType b
on a.TeachTypeID=b.TeachTypeID
where TeachTypeName='兼职'

3. delete 语句的使用
   1. 简单的数据修改
   2. 根据其他表的数据进行修改
   3. 基于TRUNCATETABLE命令删除表数据

# 简单的删除行：放弃选修的朱老师的《网页设计》课程，改成李竞老师的《广告设计课程》
# delete 语句
delete dbo.StudentCourse
where StdInfoID=50 and TeachCourseID=4

# 删除StudentCourse表中，刘立同学的选课信息
delete dbo.StudentCourse
from dbo.StudentCourse a join dbo.StudentInfo b
on a.StdInfoID=b.StdInfoID
where StdInfoName='刘立'

# 删除表中所有数据
use StudentDB_New
go 
alter table dbo.studentInfo4
drop constraint fk_ID
go
delete dbo.ClassInfo

# truncate 删除效率更高
truncate table dbo.StudentInfo

查询表单数据

1. 选择列
   1. 查询所有的列
   2. 选择指定的列
   3. 计算列值

use StudentDB_New
# 1.查询院系信息表的所有信息
select * from dbo.DepInfo

# 2.查询教师信息表中的教师的姓名、专业和职称
select TeachInfoName, TeachInfoSpec, TeachInfoTilte
from dbo.TeachInfo

# 3.查询所有老师从事的专业
select TeachInfoSpec from dbo.TeachInfo

# 4.查询所有老师从事的专业,消除重复
select distinct TeachInfoSpec from dbo.TeachInfo

# 5.查询班级信息表中的前8条记录的班级代号，班级名称
select top 8 ClassInfoCode,ClassInfoName from dbo.ClassInfo

# 6.查询班级信息表中的前百分之二十的记录的班级代号和班级名称
select top 20 percent ClassInfoCode, ClassInfoName 
from dbo.ClassInfo

# 7.查询课程信息表中课程名称，理论学时，实践学时
select CourseInfoName, CourseInfoRstPer,CourseInfoPraper
from dbo.CourseInfo

# 8.查询课程信息表中课程名称，理论学时，实践学时,总学时（计算列值）
select CourseInfoName, CourseInfoRstPer,CourseInfoPraper,
	   CourseInfoRstPer+CourseInfoPraper
from dbo.CourseInfo

# 9.查询课程信息表中课程名称，理论学时，实践学时,总学时（计算列值），
# 给每一列起一个中文别名
select CourseInfoName as 课程名称, 
	   CourseInfoRstPer as 理论学时,
	   CourseInfoPraper as 实践学时,
	   CourseInfoRstPer+CourseInfoPraper as 总学时
from dbo.CourseInfo

# 10.以另外的方式取别名
select 课程名称=CourseInfoName,
       理论学时=CourseInfoRstPer,
       实践学时=CourseInfoPraper,
       总学时=CourseInfoRstPer+CourseInfoPraper
from dbo.CourseInfo

2. 过滤查询结果集

3. 选择行

   1. 使用比较运算符
   2. 使用逻辑运算符
   3. 使用LIKE运算符
   4. 使用BETWEEN AND运算符
   5. 使用IS NULL运算符
   6. 使用IN运算符

# 12.查询某年以后出生的学生学号姓名、性别和出生日期，
# 结果按照年龄从小到大排序
# 排序默认升序ASC，降序是DESC
select stdinfonum,stdinfoname,stdinfobirthd
from dbo.studentinfo
where stdinfobirthd >= '1990-1-1'
order by stdinfobirthd desc

# 查询出生日期在1990-1-1之后的女生。
select * from studentinfo
where stdinfobirthd >'1990-1-1' and stdinfosex='女'

# 查询所有姓张的学生的信息
# like可以做模糊查询
select * from studentinfo
where stdinfoname like '张%'

# 查询入学年份在2007到2009之间的学生信息。
select * from studnetinfo
where stdinfoyear between 2007 and 2009
# 或者写
where stdinfoyear in (2007,2008,2009)

-12. 查询某年以后出生的学生的学号姓名、性别和出生日期，结果按照年龄排序
# 当年龄相同时，按先女生后男生排序
select stdinfonum,stdinfoname,stdinfobirthd
from dbo.studentinfo
where stdinfobirthd >= '1990-1-1'
order by stdinfobirthd desc, stdinfosex desc

# 13. 聚合函数
# 查询班级信息表的所有信息
# 统计学生，教师的总人数
# 统计系部教学编制的总数、最大、最小及平均值
select count(*) from studentinfo
select count(*) from teacherinfo
select sum(depinpreoftech) as 总数,
	   max(depinpreoftech) as 最大值,
	   min(depinpreoftech) as 最小值,
	   avg(depinpreoftech) as 平均值
from depinfo

# 14. 查询教师表中有各种学历的人数
select teachinfoknowl,count(teachinfoknowl) as 数量
from teachoinfo
group by teachinfoknowl
# 以上这一步很重要，使用聚合函数的时候一定要select后面全是聚合函数
# 不能既有字段又有聚合函数，如果一定有，后面一定要接上group by

# 15. 统计1990年以后出生的男女学生各有多少人
select teachinfosex, count(*)
from studentinfo
where stdinfobirthd>'1990-1-1'
group by stdinfosex

4. 数据排序、数据分组与汇总

# 16.显示所有学生的相关信息，并汇总学生总人数
select stdinfoname,stdinfosex,stdinfonatns
from studentinfo
compute count(stdinfoname)

# 17.显示所有学生的相关信息，并按照学生所属民族分类汇总
select stdinfoname,stdinfosex,stdinfonatns
from studentinfo
order by stdinfonatns
compute count(stdinfonatns) by stdinfonatns

5. INTO 子句

连接查询多表数据

1. 内连接和外连接
   1. 内连接：使用比较运算符比较两个表共有的字段列，返回满足条件的记录行
   2. 左外连接：包括左表中不满足条件的行（null值也返回，左表中所有的信息返回）
   3. 右外连接：包括右表红不满足条件的行
   4. 完全外连接：包括左表和右表中不满足条件的行

# 1.查看每个教师的基本信息及教师类别（内连接）
select teachinfoname,teachinfosex,teachtypetilte 
       teachtypename
       from teachinfo a inner join teachtype b
       on a.teachtypeid=b.teachtypeid

# 查看每位教师的授课情况，不管教师有没有授课都要包括其情况（左外连接）
select teachinfonum,teachinfosex,teachtypetilte,courseinfoid
       from teachinfo a left join teachcourse b
       on a.teachinfoid=b.teachinfoid  
       
# 查询所有老师授课课程的选课情况（右外连接）     
select studcourseid,stdinfoid,teachinfoid,courseinfoid
from studentcourse a right join teachcourse b
on a.teachcourseid=b.teachcourseid    

 # 使用完全外连接查看stuseldb数据库中每位老师的授课情况
 select a,b,c
 from teachcourse a full join teachinfo b
 on a.teachinfoid=b.teachinfoid
 
 # 5.查询所有女生的班级信息
 select name,sex,class
 from classinfo a join studentinfo b
 on a.classinfoid=b.classinfoid
 where stdinfosex='女'
 
 # 6.查询学生所属的班级信息和专业信息（三表关联）
 select a,b,c
 from studentinfo a join classinfo b
 on a.classinfoid=b.classinfoid
 join specilinfo c
 on b.spilinfoid=c.spilinfoid

3. 查询结果的并、交、差运算

# 7.查询选课程最多的学生由高到低排序
 select stdinfoname,count(b.stdinfoid) 课程门数
 from studentinfo a join studentcourse b
 on a.stdinfoid=b.stdinfoid
 group by stdinfoname
 order by 课程门数 desc
 
 # 查看计算机工程系的老师授课的选修情况（4表）
 select teachinfoname, depinfoname,count(stdinfoid)
 from depinfo a join teachinfo b
 on a.depinfoid=b.depinfoid
 join teachcourse c
 on b.teachinfoid=c.teachinfoid
 join studentcourse d
 on c.teachcourseid=d.teachcourseid
 where depinfoname='计算机工程系'
 group by teachinfoname depinfoname
 
 # 10.利用并运算查询学生和老师的姓名
 select teachinfoname from teachinfo
 union
 select stdinfoname from studentinfo
 
 # 11.利用交运算查询彭欢老师和朱志奇老师教授的同一门课程
 # 列出课程名称
 select courseinfoname
 from courseinfo a join teachcourse b
 on a.courseinfoid=b.courseinfoid
 join teachinfo c
 on b.teachinfoid=c.teachinfoid
 where teachinfoname='彭欢'
 intersect
 select courseinfoname
 from courseinfo a join teachcourse b
 on a.courseinfoid=b.courseinfoid
 join teachinfo c
 on b.teachinfoid=c.teachinfoid
 where teachinfoname='朱志气'
 
 # 12. 利用差运算查询由彭欢老师授课而没有由朱志奇老师教授的课程
 # 列出课程名称
 select courseinfoname
 from courseinfo a join teachcourse b
 on a.courseinfoid=b.courseinfoid
 join teachinfo c
 on b.teachinfoid=c.teachinfoid
 where teachinfoname='彭欢'
 except
 select courseinfoname
 from courseinfo a join teachcourse b
 on a.courseinfoid=b.courseinfoid
 join teachinfo c
 on b.teachinfoid=c.teachinfoid
 where teachinfoname='朱'

子查询，其他的一些查询功能

1. 子查询
   1. 子查询用作派生表
   2. 子查询用作表达式
      1. 使用比较运算符的子查询
      2. 使用 ALL \ SOME 或 ANY 的子查询
      3. 使用 IN 或 NOT IN 的子查询
      4. 使用 EXISTS 和 NOT EXISTS 的子查询

# 子查询用作派生表
# 1.查找比所有课程类别ID为1的 课程总学时都要高的课程
select courseinfoname.coursetypeid
from courseinfo
where courseinfohothrs > all
    (select courseinfohothrs from courseinfo
    where coursetypeid=1)
# 2.查询stuseldb数据库中所有已获副教授职称的女教师的姓名、性别、学历、专业和职称。
select * from (
    select teachinfoname,teachinfosex,teachinfoknowl,teachinfotilte
    from teachinfo
    where teachinfotilte='副教授'
) as t
where t.teachinfosex='女'

# 子查询用作表达式
# 3.查找开设了课程ID为7的课程的教师的相关信息
# 首先查询课程id为7的课程由那些教师授课
select teachinfoid from teachcourse
where courseinfoid=7
# 其次查询上一个步骤里老师的信息
select teachinfoid,teachinfoname,teachinfosex,teachinfoknowl,teachinfospec
from teachinfo
where teachinfoid in (select teachinfoid from teachcourse
where courseinfoid=7)

# 4.查找数据库库中选修了7号课程的学生学号、姓名和联系电话。
# 子查询的select语句只能来自一个表，和连接查询区别一下
select stdinfonum,stdinfoname,stdinfotel
from studentinfo
where exists(
    select * from studentcourse
    where stdinfoid=studentinfo.stdinfoid
    and exists (
        select * from teachcourse
        where courseinfoid=7 and 
        teachcourseid=studentcourse.teachcourseid
    )
)

EXISTS用于检查子查询是否至少会返回一行数据，
该子查询实际上并不返回任何数据，而是返回True or False
一种通俗的理解为：将外表查询的每一行，带入内查询作为检验，如果内查询
返回的结果取非空值，则EXISTS子句返回true，这一行可以作为外查询的结果行
否则不能作为结果

1. 首先执行一次外部查询、
2. 对于外部查询中的每一行分别执行一次子查询，而且每次执行子查询时都会
引用外部查询中当前行的值

# 查询图像0801班的学生信息
select * from studentinfo
where classinfoid=(select classinfoid from classinfo
where classinfoname='图像0801')
# 都可以实现，但推荐用子查询，连接查询的代价更高
select * from studentinfo a
join classinfo b
on a.classinfoid=b.classinfoid
where classinfoname='图像0801'

# 7.查询课程考试不及格的学生的姓名和课程名（来自多表）
select 姓名=(select stdinfoname from studentinfo
            where stdinfoid=a.stdinfoid),
            课程名=(select courseinfoname from courseinfo
            where courseinfoid in (
            select courseinfoid from teachcourse
            where teachcourseid=a.teachcourseid))
from studentcourse a 
where studcourseusugrd<60

2. 开窗函数及公用表表达式

SQL编程-存储过程

1. 创建存储过程
   1. T-AQL 程序的流程控制

# 查询stdinfoid为6的学生的姓名和已选课程门数，当选课程门数在3门以上
# 输出“xx，已完成选课”，否则输出“cxx，还需选课”
declare @sname varchar(50),@num int
select @sname=stdinfoname from studentinfo
where stdinfoid=6
print @sname
set @num=(select count(*) from studentcourse
    group by stdinfoid
    having stdinfoid=6)
# where 子句与having短语的区别在于作用对象不同。where作用于基本表或视图
# having作用于组0    
if @num>=3
    begin
        print @sname+',你已经完成了选课'
    end    
else 
        print @sname+',你还需继续选课'    

#  简单CASE结构：查询studentinfo表中学生的性别
# 若是女生显示female，否则显示male      
select stdinfoname,stdinfosex,性别=case
                        when stdinfosex='男' then 'male'
                        when stdinfosex='女' then 'female'
                        end
   from studentinfo             
   
# 3.循环结构：计算1-100内能被3整除的最大数
declare @x int=100
while(@x>=1)
    begin
        if(@x % 3=0)
        break
        set @x-=1
    end
print @x    

# 创建和执行存储过程    
# 定义一个存储过程，用于查询stuseldb数据库中所有教师的
# 姓名、性别、学历、职称和所授课的课程名称
create procedure showteacourse
as
select teachinfoname
from teachinfo a join teachcourse b
on a.teachinfoid=b.teachinfoid
# 执行存储过程,直接选择程序名字
execute showteacourse  

2. 参数化存储过程
   1. 带输入参数的存储过程
   2. 带输出参数的存储过程
   3. 带 return 返回数据

# 带参数的存储过程：
# 定义一个存储过程，用于查询数据库中某一位老师的姓名、性别、学历和课
# 的课程名称
# 输入参数
create proc showteainfobyname
@teachername varchar(30)
as
select teachinfoname,teachinfosex,teachinfoknowl
from teachinfo a join teachcourse b
on  a.teachinfoid=b.teachinfoid
where teachinfoname=@teachername


# 执行带输入参数的存储过程
showteainfobyname '李静'

# 9.定义一个存储过程在教师授课表中增加教师开设课程的记录
/*如李范老师（教师ID为16）开设C语言课程（26）的记录*/
# 输出参数
create proc addteacourse
@teainfoid int, @courseinfoid int,
@teacourseid int output
as
insert teachcourse
values(@teainfoid,@courseinfoid)
# 设置输出参数，用来存储自动增长的编号
Set @teacourseid=scope_identity()

# 执行
declare @teaqcourid int
exec addteacourse 17,26,@teacourid output
select @teacourid

select * from teachcourse where teachinfoid=17
and courseinfoid=26

3. 修改和删除存储过程

# 修改存储过程
/*6.修改存储过程，用于查询数据库中所有副教授职称的教师的
姓名、性别、学历、职称和所授课的课程名称*/
alter procedure showteacourse
as

# 7.删除存储过程
drop procedure showteacourse

SQL 编程-触发器

1. DML 触发器
   1. after 触发器：执行 insert、update、delete 之后触发
   2. instead of 触发器：不执行 insert、update、delete，而是执行触发器本身定义的操作

/* DML触发器
1. 创建insert触发器，如果向教师信息表teachinfo中插入记录时，检查该记录的院系编号在
院系信息表depinfo中是否存在，如果不存在，则不允许插入
*/ 
# inserted，逻辑表，存放插入之后的记录
create trigger trig_insertteachinfo
on teachinfo

after insert
as
begin transaction
if exist (select * from inserted a
where a.depinfoid not in(select depinfo from depinfo))
begin
    raiserror('数据一致性验证！'，19，1)
    rollback transaction
end
else
    commit transaction    

/* 
2. 创建 update 触发器，当班级信息表 classinfo 中的主键 classinfoid 进行
修改时，必须对学生表中相应的 classinfoid 也进行修改
*/
# 更新分成2步骤：先删除原数据，再插入新数据
# deleted，逻辑表，存放删除之后的记录(原表)   
create trigger trig_updateclass
on classinfo
after update
as
if update(classinfoid)
begin
    update studentinfo
    set classinfoid=(select classinfoid from inserted)
    where classinfoid=(select classinfoid from deleted)
end   

# 测试
update classinfo
set classinfoid=444 where classinfoid=4   

/*
3. 创建 delete 触发器，当删除学生信息表 studentinfo 中已退学的学生资料
时，自动删除学生选课表 studentcourse 中此学生相应的所有信息
*/
create studentinfo
after delete
as
begin
    declare @stdinfoid int
    select @stdinfoid=deleted.stdinfoid from deleted
    deleted studentcourse
    where stdinfoid =@stdinfoid
end 

# 测试
delete studentinfo
where stdinfoid=2

/*
4. 创建 instead of 触发器，限制不允许删除院系信息表 depinfo 中已存在
的院系记录
*/
create trigger trig_deletedep
on depinfo
instead of delete
as
begin
    declare @delcount int
    select @delcount=count(*) from deleted
    if @delcount>0
    begin
        raiserror('不能删除院系表中的任何记录！'，10，1)
        rollback transaction
    end
end
# 测试
delete depinfo

# 5.修改及删除触发器
drop trigger name

alter trigger name  

2. DDL 触发器
   1. DDL 触发器在 create、alter、drop 及其它 DDL 语句上操作
   2. DDL 触发器不能作为 instead of 触发器使用

# DDL触发器
/*
7. 定义DDL触发器来防止数据库stuseldb中的任一
表被修改或删除
*/
create trigger trig_studeldbsafe
on database
for drop_table, alter_table
as
print '你不能删除或修改studeldb中任何对象'
rollback

# 8. 删除DDL触发器
drop trigger trig_stuseldbsafe
on database 

/*
6.创建触发器，当在学生信息表中插入或删除记录时，班级
信息表classinfo中的班级人数进行实时更新
*/
create trigger trig_sudinfoclassnum
on studentinfo
after insert,delete
as
if(exists (select * from inserted))
begin
    update classinfo
    set classinfosum+=1
    where classinfoid=(select classinfoid from inserted)
end
if (exists (select * from deleted))
begin
    update classinfo
    set classinfosum-=1
    where classinfoid in (select classinfoid from deleted)
end

SQL编程-用户定义函数与游标

1. 系统内置函数
   1. 转换函数
   2. 数学函数
   3. 字符串函数：使 用字符串函数查询学神信息表姓王的学生的学号、姓名和性别
   4. 日期时间函数：查询信息表中年龄在20-25岁的学生学号姓名性别和年龄

# 转换函数
select cast('11.43' as float),
       convert(varchar(20),getdate(),112) 
# 数学函数
select geiling(5.6),floor(5.6) 
select abs(-30)

# 字符串函数
# 学生信息表王的学生的学号姓名性别
select stdinfonum,stdinfoname,stdinfosex
from studentinfo
where substring(stdinfoname,1,1)='王'
# 也可以用模糊搜索where stdinfoname like '王%' 

# 日期函数：查询学生信息表中年龄在20-25岁的学生学号等 
select * from(
select stdinfonum,stdinfoname,stdinfosex,
datediff(year,stdinfobirthd,getdate()) age
from studentinfo) as temp
where age between 20 and 25     

2. 自定义用户函数

   1. 创建和调用标量函数：返回一个值

      # 用户自定义函数
      # 2. 创建标量函数 ftitlesum，统计指定院系的教师人数
      create function ftitlesum(@depname varchar(20))
      returns int
      as
      begin
          declare @num int
          select @num=count(*) from teachinfo
          where depinfoid=(select depinfoid from
          depinfo where 
          depinfoname=@depname)
          return @num
      end
      
      # 调用
      declare @name varchar(20)='信息工程系'
      select ftitlesum(@name)
      

   2. 创建和调用表值函数：返回一个表

      # 3. 创建内联表值函数fselteach，用来查询指定院系的教师信息
      select * from teachinfo
      where depinfoid=(select depinfo from
                      depinfo where
                      Depinfoname='信息工程系')
      
      create function fselteach(@depname varchar(20))
      returns table
      as
      return (select teachinfonum,teachinfoname,teachinfoknowl
      from teachinfo
      where depinfoid=(select depinfoid from depinfo
                      where depinfoname=@depname))
      
      # 调用
      declare @d varachar(20)='信息工程系'
      select * from fselteach(@d)                              
      
      # 4.创建多语句表值函数fselcourse，用来查询指定教师任
      # 教的课程名称、实践课时、理论课时和总课时                                                                                            
      create function fselcourse(@teachname varchar(20))
      returns @teach_course table
      (
      课程名称 varchar(80),
      理论学时 int,
      实践学时 int,
      总学时 int
      )
      as
      begin
          insert into @teach_course
          select courseinfoname,courseinforstper,courseinfopraper
          courseinforstper+courseinfopraper
      from courseinfo c join teachinfo a 
      join teachcourse b
      on a.teachinfoid=b.courseinfoid
      on c.teachinfoname=@teachname
      return     
      end  
      
      # 调用
      select * from fselcourse('朱志奇')
      

内联表值函数和多语句表值函数的区别：

• 内联表值函数，RETURNS 子句只包含关键字 table。不必定义返回变量的格式，因为它由 RETURN 子句中的 SELECT 语句 的结果集的格式设置。
• 内联表值函数的 函数体 不用 BEGIN 和 END 分隔。
• 内联表值函数的 RETURN 子句在括号中包含单个 SELECT 语句。SELECT 语句的结果集构成函数所返回的表。其使用的 SELECT 语句与视图中使用的 SELECT 语句受到相同的限制。

3. 游标
   1. 声明游标
   2. 打开游标
   3. 提取数据
   4. 关闭游标
   5. 释放游标

/*
5. 游标的使用
对studentinfo表，定义一个查询汉族，性别男生的学生姓名、性别
民族的游标，并输出游标结果
*/
# 申明相关变量
declare @sname varchar(20),
        @ssex varchar(5),
        @snatuns varchar(5)
# 申明游标        
declare student_cursor cursor for
select stdinfoname,stdinfosex,stdinfonatns
from studentinfo
where stdinfonatns='汉' and stdinfosex='男' 
# 打开游标
open student_cursor
# 提取数据
fetch next from student_cursor into @sname,@ssex,@snatuns
# 用来判断游标提取状态的全局变量@@fetch_status
while @@fetch_status=0
begin
    print '学生姓名'+@sanme+'性别'+@ssex+'民族'+@snatns
    fetch next from student_cursor into @sname,@ssex,@snatns
end  
# 关闭游标
close student_cursor
# 释放游标
deallocate student_cursor  

安全管理、数据库的备份和恢复

1. 数据库的安全管理
   1. 登录管理
   2. 用户权限管理
   3. 角色管理

# 1.将windows账户中的用户‘teacher’添加到SQL sever登录中
# 默认数据为master
create login moon-PC\Teacher
from windows
with default_database=[master]

# 2.创建名为teachers的SQL登录，密码为sq1@123%
# 默认数据库为master，强制实施密码策略
create login teachers
with password='sq1@123%'
with default_database=[master],
check__expiration=on
check_policy=on

# 3.修改SQL登录teachers，密码改为‘123’
alter login teachers
with password='123'
old_password='sq1@123%'

# 4.禁用teachers登录
# 启用
alter login teachers enable
# 禁用
alter login teachers disable

# 5.删除teachers登录名
drop login teachers

# 用户管理
# 6.创建名为teachuser的登录名，在数据库中，创建用户teach1与teachuser
# 登录名对应
create login teachuser
with password='123',
default_database=[master]
go
use stuseldb
go
create user teach1
for login teachuser

# 7.修改在数据库中创建的用户teach1
# 将其名称改为teach2
alter user teach1
with name=teach2

# 8.删除stuseldb数据库中用户teach2
drop user teach2

# 用户权限管理
# 9.在数据库库中，创建用户teacher，其对应登录名为teachers
# 并将表teachinfo的select权限授予teacher
create login teachers
with password='sq1@123%'
with default_database=[master],
check__expiration=on
check_policy=on

create user teacher
for login teachers
grant select on teachinfo to teacher

# 10. 拒绝用户teacher查看adminuser表的权限
deny select on adminuser to teacher
# 11.撤销用户teacher对表techinfo的select权限
revoke select on techinfo to teacher

# 角色管理
# 12.将SQL登录名teacher添加到sysadmin固定服务器角色中
# 使其可以在数据库服务器上执行任何操作
exec sp_addsrvrolemember 'teachers','sysadmin'
exec sp_dropsrvrolemember 'teachers','sysadmin'

2. 数据库的备份与恢复

das1