数据库学习笔记

创建数据库
create database studentDB1
on primary
(
name = ‘StudentDB1’,
filename = ‘D:\data\StudentDB1.mdf’,
size = 3mb,–初始大小
maxsize = unlimited,–最大大小
filegrowth = 1mb
),
(
name = ‘StuDB1’,
filename = ‘D:\data\StuDB1.ndf’,
size = 3mb,
maxsize = unlimited,
filegrowth = 1mb
),
(
name = ‘StuDB2’,
filename = ‘D:\data\StuDB2.ndf’,
size = 3mb,
maxsize = unlimited,
filegrowth = 1mb
)
log on
(
name = ‘StudentDB1_log’,
filename = ‘D:\data\StudentDB1log.ldf’,
size = 1mb,
maxsize = 1gb,
filegrowth = 10%
)
–修改数据库
–1.改变主数据文件的初始大小
alter database StudentDB1
modify file
(name = StudentDB1,
size = 5mb)
–2.在该数据库中，创建名为“GData”的文件组，为数据库添加辅助数据文件StuDB3,文件建立在“D:\data”目录下，将该文件添加到Gdata文件组中
–注意，应该是先创建文件组，再创建文件
alter database studentDB1
add filegroup gdata
go
alter database studentDB1
add file
(
name = ‘StuDB3’,
filename = ‘d:\data\studb3.ndf’
)
to filegroup Gdata
–3.删除数据库的辅助数据文件
alter database studentDB1
remove file StuDB3
–删除数据库
drop database StudentDB
–4.分离数据库，只有在分离后数据库才能完成拷贝操作，进行数据库分离操作的时候，确保该数据库没有被使用，采用use [其他数据库名]
exec sp_detach_db studentDB1
–5.附加数据库，将.mdf文件附加到数据库服务器
create database studentdb2
on primary
(
name = ‘studentdb1’,
filename = ‘C:\Users\rensh\Desktop\studentdb1.mdf’
)
for attach
use studentdb
exec sp_detach_db studentDB1
–数据库的分离与附加，就是将数据库文件和数据库服务器的联系切断和关联的操作。

–创建架构
create schema my
–删除架构
drop schema my

表相关操作
use studentdb
create schema my
–1.创建数据表
create table studentdb.my.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)
)
–修改数据表，增加列名为stest,数据类型为int,不为空
alter table my.tb_stu_info
add stest int not null
–修改stest列的数据类型
alter table my.tb_stu_info
alter column stest varchar(20)
–删除列
alter table my.tb_stu_info
drop column stest
–删除多列
alter table my.tb_stu_info
drop column stu_birthday,stu_address
–添加多列
alter table my.tb_stu_info
add stu_birthday date,
stu_address nvarchar(200)
–删除数据表
drop table my.tb_stu_info
create table dbo.stu_info
(
stu_no varchar(20) primary key not null,
stu_sex varchar(20),
stu_address varchar(100),
stu_year varchar(20)
)
–2.创建分区表，依据stu_info中的stu_year列创建分区函数yearorderpartfunc,边界值为和
–首先创建分区函数
create partition function yearorderpartfunc(int)
as range left for values(‘2007’,‘2009’)
–3.创建表的约束和索引
–not null 约束
create table student_info
(
stuid int identity(1,1) not null, --identity代表自动增长，初始值为1，增量为1
stuname varchar(20) not null,
stuage int
)
–缺省约束，需要给某一个表定一些默认的值
–创建班级信息表，所在的系部默认为“软件学院”，班级人数默认为
–创建表的时候定义约束
create table classinfo
(
classinfoid int not null,
classindep varchar(20) not null default(‘软件学院’),
classinfocount int not null default(0)
)
–修改表的时候定义约束
–给班级的编号增加默认值为“”
alter table dbo.classinfo
add constraint df_classinfoid default(1001) for classinfoid
–唯一约束，代表所有字段的取值必须是唯一的，可以允许null
create table studentinfo2
(
stdid int not null,
stdinfonum varchar(20) not null unique,
stdinfocard varchar(18)
)
–修改表定义的时候创建约束
alter table studentinfo2
add constraint ix_card unique(stdinfocard)
–check 约束，检查数据是否合法
create table studentscore
(
stdid int not null,
courseid int not null,
score numeric check(score>=0 and score<=100),
coursestartdate date not null
)
–另一种写法
create table studentscore2
(
stdid int not null,
courseid int not null,
score numeric check(score between 0 and 100),
coursestartdate date not null
)
alter table studentscore2
add constraint ck_startdate check(coursestartdate>=‘2014-2-1’
and coursestartdate<=‘2014-2-28’)
–主键约束primary key约束（主键约束与唯一约束的区别：唯一约束可以允许出现一个null，但是主键约束不能出现null）
create table studentinfo11
(
stuinfoid int not null primary key,
stuname varchar(20)
)
–另一种添加主键的方式
alter table studentinfo3
add constraint pk_ID primary key(stuinfoid)

–添加复合主键
create table sutdentcou1
(
stdid int not null,
courid int not null,
score numeric not null,
constraint pk_stdid_courid primary key(stdid,courid)
)
–外键约束，foreign key
use studentdb
create table studentinfo4
(
stdid int primary key not null,
stdname varchar(20) not null,
classid int foreign key references dbo.classinfo(classinfoid)
)
–删除约束
alter table studentinfo4
drop constraint FK__studentin__class__286302EC
alter table studentinfo4
add constraint fk_id foreign key(classid) references classinfo(classinfoid)
–创建索引
create clustered index pk_stdid
on dbo.student_info(stuid desc)
–重新生成索引
alter index pk_stdid
on student_info rebuild
–删除索引
drop index pk_stdid
on student_info

增删改语句
–insert语句
use studentdb
insert dbo.stu_info values(1,1,2,2)
–插入多条数据
insert dbo.stu_info
values(2,1,2,3),
(3,1,2,4),
(4,1,2,5)
–针对某一部分的列进行插入
insert dbo.stu_info(stu_no,stu_sex)
values(5,1),
(6,2)
插入其他表的数据，将studentinfo表中所有女同学的学号，姓名，性别，身份证号，家庭地址及联系电话插入到新创建的studentinfo1表中
insert into dbo.studentinfo1
select stdinfonum,stdinfoname,stdinfosex,stdinfocard,stdinfonatns,stdinfotel
from dbo.studentinfo
where stdinfosex=‘女’
–数据修改，update
/*将studentinfo表中的李婵同学的曾用名改为“李婵林”，联系电话改为“123456”，电子邮箱改为“lichan8229@163.com”
update dbo.studentinfo
set stdinfoname = ‘李婵林’,
stdinfotel = ‘123456’,
stdinfoemail = ‘lichan8229@163.com’
where stdinfoname = ‘李婵’
*/

/*修改teachinfo表的备注列teachinfoRMK，为所有的兼职教师设置为‘每周上课不超过课时’，老师的类型在teachtype里面（兼职代表teachinfo里面typeid=2）。
use stuseIDB
go
update dbo.teachinfo
set teachinfoRMK=‘每周上课不超过节’
from dbo.teachinfo a join dbo.teachtype b --建立连接查询
on a.teachtypeid = b.teachtypeid
where teachtypename = ‘兼职’
*/

/*将“网页设计”课程考试成绩不合格的在原分数的基础上加分
update dbo.studentcourse
set stucourseexamgrd+=5
where studcourseexamgrd<60 and teachcourseid in
(
select teachcourseid from dbi.teachcourse
where courseinfoid = (select courseinfoid from dbo.courseinfo
where courseinfoname = ‘网页设计’)
)
*/

/*删除语句，由于宋思同学改变想法，改成选修李静老师的《广告设计》课程，放弃选修朱志奇老师的《网页设计》课程，所以他原来选修的《网页设计》课程的记录就应该从studentcourse表中删除。
–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
go
delete dbo.classinfo
*/

/* --另一个删除表的数据
truncate table dbo.studentinfo
*/

查询语句
/*1.查询院系信息表的所有信息
select * from dbo.depinfo

2.查询教师信息表中驾驶的姓名，专业，职称
select name,sepc,title from dbo.teachinfo

3.查询所有教师从事的专业
select sepc from dbo.teachinfo

4.查询所有教师从事的专业，消除重复
select distinc sepc from dbo.teachinfo

5.查询班级信息表的前条记录的班级代号，班级名称
select top 8 classinfocode,classinfoname from dbo.classinfo

6.查询班级信息表中前百分之二十记录的班级代号，班级名称
select top 20 percent classinfocode,classinfoname from dbo.classinfo

7.查询课程信息表中课程的名称，理论学时，实践学时，总学时,并且给每一列起一个别名
select courseinfoname as 课程名称,
courseinforstper as 理论学时,
courseinfopraper as 实践学时,
courseinforstper+courseinfopraper as 总学时
from dbo.courseinfo

8.另一种方式取别名，+用在字符串时，代表连接操作，若是数字类型的字符串与int型的做+操作，会先将字符串转化成int，再做计算
select 课程名称=courseinfoname ,
理论学时=courseinforstper,
实践学时=courseinfopraper,
总学时=courseinforstper+courseinfopraper
from dbo.courseinfo

9.查询年月号以后出生的学生的学号，姓名，性别，出生日期，结果按照年龄从小到大排列(默认升序asc，降序desc)
select stdinfonum,stdinfoname,stdinfobirthD
from dbo.studentinfo
where stdinfobirthD >=‘1990-1-1’
order by stdinfobirthD asc

–查询出生日期在-1-1之后的女生
select * from dbo.studentinfo
where stdinfobirthD>‘1990-1-1’ and stdinfosex=‘女’

–查询所有姓张的学生的信息
select * from dbo.studentinfo
where stdinfoname like ‘张%’

–查询入学年份在到之间的学生信息
select * from dbo.studentinfo
where stdinfoyear between 2007 and 2009

select * from dbo.studentinfo
where stdinfoyear in (2007,2008,2009)

10.查询年月号以后出生的学生的学号，姓名，性别，出生日期，结果按照年龄从小到大排列.当年龄相同的时候，按照先女生后男生排序
select stdinfonum,stdinfoname,stdinfosex,stdinfobirthD
from dbo.studentinfo
where stdinfobirthD>‘1990-1-1’
order by stdinfobirthD desc,stdinfosex desc

11.聚合函数
–查询班级信息表中的所有信息，统计学生，教师的总人数
–统计系部教学编制的总数，最大，最小及平均值
select COUNT() from dbo.studentinfo
select COUNT() from dbo.teachinfo
select SUM(depinfopreoftech) as 总数
MAX(depinfopreoftech) as 最大值
MIN(deoinfopreoftech) as 最小值
AVG(depinfopreoftech) as 平均值
from dbo.depinfo

12.分组：
–查询教师表中各种学历的人数,当普通列和聚合函数同时查询时，必须使用分组
select teachinfoknow,COUNT(teachinfoknow) as 数量
from dbo.teachinfo
group by teachinfoknow

–统计年以后出生的男女学生各有多少人
select stdinfosex,COUNT(*)
from dbo.studentinfo
where stdinfobirthD>‘1990-1-1’
group by stdinfosex

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

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

13. 连接查询
    –查看每个教师的基本信息及教师的类别（来自于多个表，内连接）
    select teachinfoname,teachinfosex,teachinfotitle,
    teachtypename,teachinfo.teachtypeid
    from dbo.teachinfo inner join dbo.teachtype
    on teachinfo.teachtypeid = teachtype.teachtypeid

–采用别名的方式
select teachinfoname,teachinfosex,teachinfotitle,
teachtypename,a.teachtypeid
from dbo.teachinfo a inner join dbo.teachtype b
on a.teachtypeid = b.teachtypeid

–查看每位教师的授课情况，不管教师有没有授课都要包括其情况（左外连接,外连接可以将左边的表的null值进行查询，比如会出现courseinfoid为空值的情况）
左外连接：包括左表中不满足条件的行
右外连接：包括右表中不满足条件的行
完全外连接：包括左表和右表中不满足条件的行

select teachinfonum.teachinfoname,teachnifosex,teachinfotitle,
courseinfoid
from dbo.teachinfo a left join dbo.teachcourse b
on a.teachinfoid=b.teachinfoid

–查看所有教师授课课程的选修情况（右外连接）
select studentcourseid,stdinfoid,teachinfoid,courseinfoid
from dbo.studentcourse a right join dbo.teachcourse b
on a.teachcourseid=b.teachcourseid

–使用完全外连接查看stuseldb数据库中每位教师的授课情况
select teachinfonum,teachinfoname,teachinfosex,teachinfotitle,
teachcourseid,courseinfoid
from dbo.teachcourse a full join dbo.teachinfo b
on a.teachinfoid=b.teachinfoid

–查询所有女生的所在班级信息
select stdinfoname,stdinfosex,classinfoid
from dbo.classinfo a join dbo.studentinfo b
on a.classinfoid=b.classinfoid
wherestdinfosex=‘女’

–查询学生所属的班级信息和专业信息，学生信息在dbo.studentinfo,班级信息在dbo.classinfo,专业信息在dbo.specilinfo,班级信息表作为关联过渡。
select stdinfoname,spilinfoname，classinfoname
from dbo.studentinfo a join dbo.classinfo b
on a.classinfoid=b.classinfoid
join dbo.specilinfo c
on b.spilinfoid=c.spilinfoid

–查询选课程最多的学生由高到低的排序(注意：一般查询和聚合函数一起使用，需要将前面出现的普通列进行group by操作)
select stdinfoname,COUNT(b.stdinfoid) 课程门数
from dbo.studentinfo a join dbo.studentcourse b
on a.stdinfoid=b.stdinfoid
group by stdinfoname
order by 课程门数desc

–查询计算机工程系的教师的授课课程的选修情况(来自于四个表的数据)
select teachinfoname,depinfoname,COUNT(stdinfoid)
from dbo.dbo.depinfo a join dbo.teachinfo b
on a.depinfoid=b.depinfoid
join dbo.teachinfocourse c
on b.teachinfoid=c.teachinfoid
join dbo.studentcourse d
on c.teachcourseid=d.teachcourseid
where depinfoname=‘计算机工程系’
gorup by teachinfoname,depinfoname

–查看每个学生的选课情况，列出学生的姓名，课程名称，教师名
select stdinfoname,courseinfoname,teachinfoname
from dbo.studentinfo a join studentcourse b
on a.stdinfoid=b.stdinfoid
join dbo.teachcourse c
on b.teachcourseid=c.teachcourseid
join dbo.courseinfo d
on c.courseinfoid=d.courseinfocode
join dbo.teachinfo ae
on c.teachinfoid=e.teachinfoid --有错误

14.交并差运算,必须是相容字段
–利用并运算查询学生和老师的姓名,union将两个结果集合并成一个结果集（必须是可以兼容的字段），列名为第一个结果集的列名为准
select teachinfoname from dbo.teachinfo
union
select stdinfoname from dbo.studentinfo

–利用交运算查询彭欢老师和朱志奇老师教授的同一门课程，列出课程名，intersect将两个结果集进行交运算
select courseinfoname
from dbo.courseinfo a join dbo.teachcourse b
on a.courseinfoid=b.courseinfoid
join dbo.teachinfo c
on b.teachinfoid=c.teachinfoid
where teachinfoname=‘彭欢’
intersect
select courseinfoname
from dbo.courseinfo a join dbo.teachcourse b
on a.courseinfoid=b.courseinfoid
join dbo.teachinfo c
on b.teachinfoid=c.teachinfoid
where teachinfoname=‘朱志奇’

利用差运算查询由彭欢老师授课而没有朱志奇老师授课的教程，列出课程名称,用except
select courseinfoname
from dbo.courseinfo a join dbo.teachcourse b
on a.courseinfoid=b.courseinfoid
join dbo.teachinfo c
on b.teachinfoid=c.teachinfoid
where teachinfoname=‘彭欢’
except
select courseinfoname
from dbo.courseinfo a join dbo.teachcourse b
on a.courseinfoid=b.courseinfoid
join dbo.teachinfo c
on b.teachinfoid=c.teachinfoid
where teachinfoname=‘朱志奇’

子查询用作派生表
/*
–1.查询比所有课程类别id为的课程的学时都要高的课程，all表示大于最大或者小于最小，any表示大于最小或者小于最大（大于或者小于任何一个都可以）
select courseinfoname,coursetypeid
from dbo.courseinfo
where courseinfohothrs > all
(select courseinfohothrs from dbo.courseinfo
where coursetypeid=1)

–2.查询stuseldb数据库中所有已获得“副教授”职称的女教师的姓名，性别，学历，专业和职称（先找出所有的副教授，然后从结果集中筛选女教师）。
select * from(
select teachinfoname,teachinfosex,teachinfoknowl,
teachinfospec,teachinfotitle
from dbo.teachinfo
where teachinfotitle=‘副教授’) as t --将子查询的结果集取别名
where t.teachinfosex=‘女’

–子查询用作表达式

1.查找开设了课程id为的课程的教师的相关信息，

–首先查询课程id为的课程由哪些老师授课
select teachinfoid from dbo.teachcourse
where courseinfoid=7
–其次查询上一个步骤里相关老师的信息
select teachinfoname,teachinfosex,teachinfoknowl,
teachinfospec,teachinfotitle
from dbo.teachinfo
where teachinfoid in (select teachinfoid from dbo.teachcourse
where courseinfoid=7)

2.查找stuseldb数据库中选修了号课程(有老师上课且有学生选课)的学生学号，姓名和联系电话，
注意连接和子查询不同，子查询的结果只能来自于一个表的数据，连接是将多个表进行合并
–首先查询号课程由哪位老师上课
select * from dbo.studentcourse
where stdinfoid=dbo.studentinfo.stdinfoid
and exists(
select * from dbo.teachcourse
where courseinfoid=7 and
teachcourseid=dbo.studentcourse.teachcourseid)

–然后查学号，姓名，联系电话
select stdinfonum,stdinfoname,stdinfotel
from dbo.studentinfo
where exists(
select * from dbo.studentcourse
where stdinfoid=dbo.studentinfo.stdinfoid
and exists(
select * from dbo.teachcourse
where courseinfoid=7 and
teachcourseid=dbo.studentcourse.teachcourseid)
)

3.查询图像班的学生信息
select * from dbo.studentinfo
where classinfoid=(select classinfoid from dbo.classinfo
where classinfoname=‘图像’)

若采用连接查询，代价更高，性能上用子查询更好
select *from studentinfo a
join classinfo b
on a.classinfoid=b.classinfoid
where classinfoname=‘图像’

4.查询与“刘丽”同班同学的信息
–首先查询刘所在的班级编号
select classinfoid from dbo.studentinfo
where stdinfoname=‘刘丽’
–然后查询同班同学的信息
select * from dbo.studentinfo
where classinfoid=(
select classinfoid from dbo.studentinfo
where stdinfoname=‘刘丽’
)

5.查询课程考试不及格的学生的姓名，性别
–首先查询不及格学生的学号，然后查询姓名，性别
select stdinfoname,stdinfosex from dbo.studentinfo
where stdinfoid in(
select stdinfoid from dbo.studentcourse
where studcourseusugrd<60)

6.查询考试不及格的学生的姓名和课程名
select 姓名=(stdinfoname from dbo.studentinfo a
where stdinfoid=a.stdinfoid)，
课程名=(select courseinfoname from dbo.courseinfo
where course.infoid in(
select courseinfoid from dbo.teachcourse
where teachcourseid=a.teachcourseid
))
from dbo.studentcourse a
where studcourseusugrd<60

若采用连接查询
select stdinfoname,courseinfoname,studcourseusucrd
from dbo.courseinfo a join dbo.teachcourse b
on a.courseinfoid=b.courseinfoid
join dbo.studentcourse c
on b.teachcourseid=c.teachcourseid
join dbo.studentinfo d
on c.stdinfoid=d.stdinfoid
where studcourseusugrd<60

7.查询网页设计课程不及格的学生姓名，性别和联系电话
查询哪些老师开了网页设计这门课,因为有不同的老师都开了网页设计这门课程
select teachcourseid from dbo.teachcourse
where courseinfoid=(
select courseinfoid from dbo.courseinfo
where courseinfoname=‘网页设计’
)
然后再查询成绩低于分的学生
select stdinfoid from studentcourse
where studcourseusugrd<60
and teachcourseid in(
select teachcourseid from dbo.teachcourse
where courseinfoid=(
select courseinfoid from dbo.courseinfo
where courseinfoname=‘网页设计’
)
)
最后查询学生的姓名，性别和联系电话
select stdinfoname,stdinfosex,stdinfotel
from dbo.studentinfo
where stdinfoid in(
select stdinfoid from studentcourse
where studcourseusugrd<60
and teachcourseid in(
select teachcourseid from dbo.teachcourse
where courseinfoid=(
select courseinfoid from dbo.courseinfo
where courseinfoname=‘网页设计’
)
)
)

数据库存储过程以及流程控制

一.流程控制
1.if else结构：查询stdinfoid为的学生的姓名和已选课程门数，当选课门数在门以上，输出“xx，已经完成了选课”，
否则输出“xx，还需要选课”
declare @sname varchar(50),@num int
select @sname=stdinfoname from dbo.studentinfo
where stdinfoid=6
set @num=(select COUNT(*) from dbo.studentcourse
group by stdinfoid
having stdinfoid=6) --使用having语句对分组结果进行限制
if @num>=3
begin
print @sname+’,你已经完成了选课’
end
else
print @sname+’,你还需要继续选课’

2.简单case结构：查询studentinfo表中学生的性别，
若女生显示“female”,否者显示“male”.

select stdinfoname,stdinfosex,性别=case
when stdinfosex=‘男’ then ‘male’
when stdinfosex=‘女’ then ‘female’
end
from dbo.studentinfo

3.循环结构：计算-100内可以被整除的最大数
declare @x int=100
while(@x>=1)
begin
if(@x % 3=0) --%代表对求模
break
set @x-=1
end
print @x

二.存储过程的创建与执行
1.定义一个存储过程，用于查询stuseldb数据库中所有教师的
姓名，性别，学历，职称和所讲授课的课程名称

定义一个存储过程,procedure可以简写成proc
create procedure showteacourse
as
select teachinfoname,teachinfosex,teachinfoknowl,
teachinfititle,courseinfoname
from dbo.teachinfo a join dbo.teachcourse b
on a.teachinfoid=b.teachinfoid
join dbo.courseinfo c
on b.courseinfoid=c.courseinfoid

执行存储过程
直接：
shouteacourse
但是如果在批处理语句中，就会报错，比如：
select * from dbo.courseinfo
go
showteacourse
就会出错

所以通常采用：
execute showteacourse
或者
exec dbo.showteacourse

2.修改存储过程：修改前面存储过程，将所有的老师改为所有副教授职称的教师

alter procedure showteacourse
as
select teachinfoname,teachinfosex,teachinfoknowl,
teachinfititle,courseinfoname
from dbo.teachinfo a join dbo.teachcourse b
on a.teachinfoid=b.teachinfoid
join dbo.courseinfo c
on b.courseinfoid=c.courseinfoid
where teachinfotitle=‘副教授’

3.删除存储过程
drop procedure showteacourse
drop proc showteacourse

–带参数的存储过程：
4.定义一个存储过程，用于查询stuseldb数据库中，某一位教师的姓名，性别，学历，职称和所教授课程的课程名称
create proc showteainfobyname
@teachername varchar(30)
as
select teachinfoname,teachinfosex,teachinfoknowl,
teachinfotitle,courseinfoname
from dbo.teachinfo a join teachcourse b
on a.teachinfoid=b.teachinfoid
join dbo.courseinfoid c
on b.courseinfoid=c.courseinfoid
where teachinfoname=@teachername

执行带输入参数的存储过程
dbo.showteainfobyname ‘李静’

–带输出参数的存储过程
5.定义一个存储过程，在教师授课表中增加教师开设课程的记录
比如：
李凡老师（教师信息id为）开设《c语言》课程（课程id为）的记录

create proc addteacurse
@teainfoid int,@courseinfoid int,@teacourseid int output --代表该参数是一个输出参数
as
insert dbo.teachcourse
values(@teainfoid,@courseinfoid)
–设置输出参数，用来存储自动增长的编号
set @teacourseid=scope_identity() --系统函数，获取自动增长的编号

–执行
declare @teacourid int --一定要声明输出参数
exec dbo.addteacourse 17,26,@teacourid output

–然后使用select显示输出参数的值
select @teacourid

触发器
–DML触发器
1.创建insert触发器，如果向教师信息表teachinfo中插入记录时，检查该记录的院系编号在院系信息表depinfo
中是否存在，如果不存在，则不允许插入
AFTER 触发器，在执行insert,update,delete之后触发
INSTEAD OF 触发器，不执行insert,update,delete,而是执行触发器本身定义的操作
–inserted，逻辑表，存放的是插入之后的记录
create trigger trig_insertTeachinfo
on dbo.Teachinfo
after insert
as
begin transaction
if exists(select * from inserted a
where a.depinfoid not in(
select depinfoid from dbo.depinfo))
begin
raiseerror(‘数据一致性验证’，,1)
rollback transaction
end
else
commit transaction
2.创建update触发器，当班级信息表classinfo中的主键classinfoid进行修改时，
必须对学生表中相应的classinfoid也进行修改

–更新分成两步：先删除原数据，再插入新数据
–deleted:逻辑表，存放删除之后的记录

create trigger trig_updateclass
on dbo.classinfo
after update
as
if update(classinfoid)
begin
update dbo.studentinfo
set classinfoid=(select classinfoid from inserted) --需要修改的新的值从inserted表中来
where classinfoid=(select classinfoid from deleted) --update语句需要条件，获取原来的classinfoid
end
3.delete触发器，当删除学生信息表studentinfo
中已退学的学生资料时，自动删除学生选课表studentcourse中此学生相应的所有信息

create trigger trig_delestudent
on dbo.studentinfo --针对的是学生信息表，进行delete操作,after之后接触发后的动作
after delete
as
begin
declare @stdinfoid int
select @stdinfoid = deleted.stdinfoid from deleted --选择被删除的dbo.studentinfo表里面的stdinfoid字段
delete dbo.studentcourse
where stdinfoid=@stdinfoid
end
4.创建instead of 触发器，
限制不允许删除院系信息表depinfo中已经存在的院系记录
create trigger trig_deletedep
on dbo.depinfo
instead of delete
as
begin
declare @delcount int
select @delcount = count(*) from deleted --depinfo表中所有的被删除的记录的数量
if @delcount>0
begin
raiserror(‘不能删除院系表中的任何记录！’，,1)
rollback transaction --不小心做了删除记录后，将对应的事物回滚。如果不回滚，触发器依然可以执行，但是会显示删除动作的结果（数据表的内容依旧不会变化，显示几行受影响）
end
end

5.修改和删除触发器
drop trigger trig_deletedep

alter trigger trig_deletedep
on dbo.depinfo
instead of delete
as
begin
declare @delcount int
select @delcount = count(*) from deleted --depinfo表中所有的被删除的记录的数量
if @delcount>0
begin
raiserror(‘不能删除院系表中的任何记录！’，,1)
rollback transaction
print ‘执行成功’
end
end
–DDL触发器
DDL触发器在create,alter,drop以及其他DDL语句上操作，不能作为instead of触发器使用

1.定义DDL触发器，防止数据库stuseldb中的任一表被修改或者删除

create trigger trigger_stuseldbsafe
on database --现在已经在stuseldb数据库下进行操作
for drop_table,alter_table
as
print ‘不能进行删除或者修改数据库中任何对象’
rollback
–删除DDL触发器
drop trigger trig_stuseldbsafe
on database

2.创建触发器，当在学生信息表中插入或者删除记录时，
班级信息表classinfo中的班级人数进行实时更新

create trigger tri_studinfoclassnum
on dbo.studentinfo
after insert,delete
as
if(exists (select * from inserted)) --如果是在进行插入操作
begin
update dbo.classinfo
set classinfosum+=1
where classinfoid=(select classinfoid from inserted)
end
if(exists (select * from deleted)) --如果是在进行删除操作
begin
update dbo.classinfo
set classinfosum-=1
where classinfoid=(select classinfoid from inserted)
ene

系统内置函数
一.转换函数
select cast(‘11.43’ as float),
convert(varchar(20),getdate(),111)
–字符串函数：使用字符串函数查询
–学生信息表姓王的学生的学号，姓名和性别
select stdinfonum,stdinfoname,stdinfosex
from dbo.studentinfo
where SUBSTRING(stdinfoname,1,1)=‘王’ --substring表示从stdinfoname的第一个位置开始，取一个字符
还有一种方式：
select stdinfonum,stdinfoname,stdinfosex
from dbo.studentinfo
where stdinfoname=‘王%’
–日期函数：查询学生信息表中年龄在-25岁
的学生的学号，姓名，性别和年龄。
select DATEADD(“month”,1,GETDATE) --将现在的日期增加一个月显示
–首先查询出所有的年龄
select stdinfonum,stdinfoname,stdinfosex,
DATEDIFF(YEAR,stdinfobirthd,getdate()) age --用系统日期和当前日期最差，计算年龄，然后使用别名为age
from dbo.studentinfo
–然后查询年龄在-25岁的学生，采用子查询
select * from(
select stdinfonum,stdinfoname,stdinfosex,
DATEDIFF(YEAR,stdinfobirthd,getdate()) age
from dbo.studentinfo
) as temp
where age between 20 and 25
二.自定义用户函数
–创建标量值函数FTitleSum,统计指定院系的教师人数
create function FTitleSum(@depname varchar(20))
returns int
as
begin
declare @num int
select @num=count(*) from dbo.teachinfo
where depinfo=(select depinfoid from
dbo.depinfo where
depinfoname=@depname)
return @num
end
调用
delare @name varchar(20)=‘信息工程系’
select dbo.FTitleSum(@name)
–表值函数：内联表值函数，多语句表值函数
1.创建内联表值函数FSslTeach,用来查询指定院系的教师信息
select语句只能返回结果集，表值函数可以返回表一样的数据,标量函数只能返回一个结果
create function FSslTeach(@depname varcahr(20))
returns table
as
return select teachinfonum,teachinfoname,teachinfoknowl,
teachinfotitle from dbo.teachinfo
where depinfoid=(select depinfoid from dbo.depinfo
where depinfoname=@depname))
调用
declare @d varchar(20)=‘信息工程系’
select * from FSslTeach(@d)
2.创建多语句表值函数FSelCourse,用来查询指定教师任教的课程名称，实践课时，理论课时和总课时
select courseinfoname,courseinforstper,courseinfopraper,
courseinforstper+courseinfopraper
from dbo.courseinfo c join dbo.teachinfo a
join dbo.teachcourse b
on a.teachinfoid=b.teachinfoid
on c.courseinfoid=b.courseinfoid
where teachinfoname=‘朱志奇’

若采用多语句表值函数（内联表值函数只能有一个sql语句，多语句表值函数可以包含很多的sql语句）：
create function FSelCourse(@teachname varchar(20))
returns @teach_course table
(
课程名称varchar(80),
理论学时int,
实践学时int,
总学时int
)
as
begin
insert teach_course
select courseinfoname,courseinforstper,courseinfopraper,
courseinforstper+courseinfopraper
from dbo.courseinfo c join dbo.teachinfo a
join dbo.teachcourse b
on a.teachinfoid=b.teachinfoid
on c.courseinfoid=b.courseinfoid
where teachinfoname=’@teachname’
return
end

调用
select * from FSelCourse(‘朱志奇’)

三.游标
5个步骤：声明游标，打开游标，提取数据，关闭游标，释放游标

对studeninfo表，定义一个查询“汉族，男生”的学生姓名，性别，民族的游标，并输出游标的结果
declare @sname varchar(20),
@ssex varchar(5),
@snatns varchar(5)
declare student_cursor cursor for
select stdinfoname,stdinfosex,stdinfonatns
from dbo.studentinfo
where stdinfoname = ‘汉’ and stdinfosex=‘男’
打开游标
open student_cursor
提取数据
fetch next from student_cursor into @sname,@ssex,@snatns
while @@FETCH_STATUS=0
begin
print ‘学生姓名：’+@sname+‘学生性别：’+@ssex+‘民族：’+@snatns
fetch next from student_cursor into @sname,@ssex,@snatns
end
关闭游标
close student_cursor
释放游标
deallocate student_cursor

数据库安全性

1.将windows账户中的用户“teacher”添加到SQL Server登录中，默认数据库为“master”
create login [GYRP\teacher]
from windows
with default_database=[master]
2.创建名为“teachers”的SQL登录，登录密码为“sql@123%”,
默认数据库为“master”,强制实施密码策略
create login teachers
with password=‘sql@123%’,
default_database=[master],
check_expiration=on,
check_policy=on
3.修改SQL登录teachers,密码改为“sqlo0123”
alter login teachers
with password=‘sqlo0123’
old_password=‘sql@123%’
4.禁用teachers用户登录
启用
alter login teachers enable
禁用
alter login teachers disable
5.删除“teachers”登录名
drop login teachers