--创建数据库
Create database StudentDB
on primary
(
    name='StudentDB',
    filename='G:\数据库\三级数据库学习\data\StudentDb.mdf',
    size=3mb,
    maxsize=unlimited,
    filegrowth=1mb
),
(
    name='StuDB1',
    filename='G:\数据库\三级数据库学习\data\StuDB1.ndf',
    size=3mb,
    maxsize=unlimited,
    filegrowth=1mb
),
(
    name='StuDB2',
    filename='G:\数据库\三级数据库学习\data\StuDB2.ndf',
    size=3mb,
    maxsize=unlimited,
    filegrowth=1mb
)

log on
(
    name='StudentDB_log',
    filename='G:\数据库\三级数据库学习\data\StudentDB_log.ldf',
    size=1mb,
    maxsize=1gb,
    filegrowth=10%
)

--修改数据文件
alter database [StudentDB]
modify file
(name=StudentDB,size=5mb)

--修改数据库：添加一个辅助数据文件到新的文件组
alter database Studentdb
add filegroup GData
go
alter database Studentdb
add file
(
name='StuDB3',
filename='F:\数据库\data\StuDB3.ndf'
)
to filegroup Gdata

--删除数据库的辅助数据文件StuDB3
alter database Studentdb
remove file Studb3

--删除数据库,"drop"删除数据库中的对象
drop database StudentDB

--分离数据库
exec sp_detach_db StudentDB

--附加数据库
create database StudentDB
on primary
(
name='StuDB3',
filename='F:\数据库\data\StudentDB.mdf'
)
for attach

--创建架构
use StudentDB
go
create schema my

--删除架构
drop schema my

Use master
--exists判断()里面的语句是否返回值，如果有值就返回True
if exists( select * from sysdatabases where name='TestSchool')
--删除数据库
drop database TestSchool

go
--开启外围服务配置,xp_cmdshell
execute sp_configure 'show advanced options',1
RECONFIGURE
execute sp_configure 'xp_cmdshell',1
RECONFIGURE

go
--自动创建文件夹，调用存储过程，让其帮助创建一个文件夹
execute xp_cmdshell 'mkdir G:\数据库\Mydir'

--使用语句创建数据库
create database TestSchool
on primary--在那个文件组上创建，默认是主文件组上创建主数据文件,可以省略
(
name='TestSchool_data',
size=3mb,
filegrowth=10%,
maxsize=100mb,
filename='G:\数据库\Mydir\TestSchool_data.mdf'
)

log on
(
name='TestSchool_log',
size=3mb,
filegrowth=10%,
maxsize=100mb,--日志文件一般不限制最大容量
filename='G:\数据库\Mydir\TestSchool_log.ldf'
)

use StudentDB
--创建数据表 create table StudentDB.my.Tb_Stu_Info
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)
)

/*修改数据表[dbo].[Tb_Stu_Info]，增加名为Stestd lie ,
数据类型为int,不为空。*/
alter table [dbo].[Tb_Stu_Info]
add stest int not null

--修改stest列的数据类型
alter table [dbo].[Tb_Stu_Info]
alter column stest varchar(20)

--删除stest列
alter table [dbo].[Tb_Stu_Info]
drop column stest

alter table 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]

--创建分区表
/*在数据表中，依据表StudentInfo的StdInfoYear列来创建分区函数YearOrderPartFunc,
边界值为2007和2009*/
--首先创建分区函数
create partition function YearOrderPartFunc(int)
as range left for values('2007','2009')
--其次创建分区方案
create partition scheme YearOrderPartScheme
as partition YearOrderPartFunc
to([primary],[primary],[primary])--to(分别对应的是文件组）

--最后依据分区方案创建表
create table Tb_StudentInfo
(
StdInfoID int identity(1,1) not null,
StdName varchar(20) not null,
StdYear int not null
)
on YearOrderPartScheme(StdYear)

-----------------------------------------------------------------
--查看所属分区
select *,$partition.YearOrderPartFunc(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,TeachInfoKnowl
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

use master
create database StuSetDB
on primary
(
name='StuSetDB',
filename='G:\数据库\三级数据库学习\WorkSpace\StuSetDB.mdf',
size=3mb,
maxsize=unlimited,
filegrowth=1mb
)
log on
(
name='StuSetDB_log',
filename='G:\数据库\三级数据库学习\WorkSpace\StuSetDB_log.ldf',
size=1mb,
maxsize=1gb,
filegrowth=10%
)

--删除数据库
exec sp_detach_db StuSetDB
drop database StuSetDB

use StuSetDB
create table AdminUser
(
AdminUserID int not null primary key,
AdminUserName varchar(50) not null,
AdminUserPwd varbinary(120) not null,
AdminLoginTimer datetime not null
)


--课程类型
/*  1 专业基础课
    2 专业课
    3 公共课
*/
create table CourseType
(
CourseTypeID int not null primary key,
CourseTypeName varchar(50) not null
)

--课程信息表
create table CourseInfo--正确设置
(
CourseInfoID int not null primary key,
CourseTypeID int not null foreign key references CourseType(CourseTypeID),
CourseInfoCode char(8) not null,
CourseInfoName varchar(50) not null,
CourseInfoProj varchar(max) not null,
CourseInfoRstPer int,
CourseInfoPraPer int,
CourseInfoHotHrs int ,
CourseInfoCreHor int,
CourseInfoRMK varchar(max)
)

/* 老师类型
1    专职
2    兼职
3    外聘
*/
create table TeachType--正确设置
(
TeachTypeID int primary key,
TeachTypeName varchar(20)
)

/* 系部
    1    001    信息工程系    32    10
    2    002    数字自动化工程系    26    11
    3    003    计算机工程系    32    13
    4    004    经济管理系    48    15
    5    005    外语系    28    10
    6    006    人文科学系    36    12
    7    007    会计系    27    9
    8    008    继续教育部    15    6
*/
create table DepInfo
(
DepInfoID int not null primary key,
DepInfoCode char(10),
DepInfoName varchar(50),
DepInfoPreO int,
DepInfoTeach int
)

/* 老师信息表
    1    3    1    030026    李静    女    研究生    硕士    计算机应用    副教授    毕业于...

*/
create table TeachInfo--正确设置
(
TeachInfoID int not null primary key identity(1,1),
DepInfoID int not null foreign key references DepInfo(DepInfoID),
TeachTypeID int not null foreign key references TeachType(TeachTypeID),
TeachInfoNum int not null,
TeachInfoName varchar(20) not null,
TeachInfoSex varchar(10) not null,
TeachInfoknowl varchar(20),
TeachInfoDeg varchar(20),
TeachInfoSpecil varchar(50),
TeachInfoTilte varchar(50),
TeachInfoRMK varchar(50)
)



/* 专业
    1    3    590109    图形图像制作    电子信息大类    以“***”    在广告，传媒...
    3    2    580202    电气自动化技术    制造大类    本专业以“**”    在太阳能光伏...
    5    3    590108    软件技术    电子信息大类    以教务管理系统...    在软件相关....
    7    3    5901022    网络技术    电子信息大类
    8    3    590101    计算机应用技术    电子信息大类    培养系统掌握..    主要从事..
    9    3    590110    动漫设计与制作    电子信息大类    培养动漫....    与.....
    10    3    590208    信息安全    电子信息大类    培养能适应....    在电子行业...
    11    1    590202    应用电子技术    电子信息大类    培养掌握...    适应.....
    12    1    590201    电子信息工程    电子信息大类    通过...    主要.....
    13    2    580110    计算机辅助设计    电子信息大类    培养从事...    主要在...
    14    2    520104    汽车运用技术    电子信息大类    培养掌握...    主要在品牌...
    15    4    620204    会计电算化    财经大类    培养...    会计电算化作为....
    16    4    620302    经济信息管理    财经大类    一企业经营...    随着信息化...
*/
create table SpecilInfo
(
SpecilInfoID int not null primary key,
DepInfoID int not null foreign key references DepInfo(DepInfoID),
SpecilInfoCode char(10),
SpecilInfoName varchar(50),
SpecilInfoSubject varchar(20),
SpecilInfoAm varchar(100),
SpecilInfoProOne varchar(100)
)

--班级信息表
create table ClassInfo--正确设置，老师ID教程没有设置外键约束
(
ClassInfoID int not  null primary key,
SpilInfoID int not null foreign key references dbo.ClassInfo(ClassInfoID),
ClassInfoCode char(10) not null,
ClassInfoName varchar(50) not null,
TechInfoID int not null foreign key references TeachInfo(TeachInfoID),
ClassInfoSum int ,
ClassInfoRMK varchar(max)
)

/* 老师课程表
    5    1    4
    1    1    5
    2    1    6
    4    3    7
    6    3    20
    7    3    26
    3    5    7
    8    5    8
    9    5    13
    20    11    10
    21    11    24
    23    12    24
    24    12    26
    25    13    10
    26    13    25
    10    15    16
    11    15    17
    12    15    20
    13    16    28
    14    16    31
    15    17    7
    16    17    11
    17    17    15
    18    19    27
*/
create table TeachCourse--正确设置
(
TeachCourseID int not null primary key identity(1,1),
TeachInfoId int not null foreign key references TeachInfo(TeachInfoID),
CourseInfoId int not null foreign key references CourseInfo(CourseInfoID)
)

/*选课信息
    1    1    2    78    0    0    0    null
    5    2    2    56    0    0    0    null
    10    5    5    99    0    0    0        
    11    1    5    90    0    0    0    
    12    2    5    40    0    0    0
    13    3    6    88    10    0    0
    14    5    6    45    0    0    0
    15    1    6    67    0    0    0
    16    2    6    99    0    0    0
    17    6    38    45    0    0    0
    18    7    38    89    0    0    0
    19    6    45    0    0    0    0
    20    4    49    0    10    0    0
    22    6    50    0    0    0    0
    23    7    50    0    0    0    0
    25    4    52    0    10    0    0
    26    7    52    0    0    0    0
    26    7    52    0    0    0    0
    27    4    54    0    10    0    0
    28    7    54    0    0    0    0
    29    3    17    0    10    0    0
*/
create table StudentCourse--正确设置
(
StudentCourseID int not null primary key identity(1,1),
TeachCourseID int not null foreign key references dbo.TeachCourse(TeachCourseID),
StudentInfoID int not null,
StudentCourseUsuGrd numeric(18,0),
StudentCourseExamGrd numeric(18,0),
StudentCourseTermGrd numeric(18,0),
StudentCourseFlag int,
TeachEvaluation varchar(max)
)

/*学生信息表
    2    31    03080110    刘莉    男    129456123    1990-01-01 00:00:00    汉    139086000    123@126.com    2008
    3    31    03080404    张琳    男    
*/
create table StudentInfo --正确设置，教程没有设置主键和外键
(
StudentInfoID int not null primary key,
ClassInfoID int not null foreign key references ClassInfo(ClassInfoID),
StudentInfoNum char(8) not null,
StudentInfoName varchar(50) not null,
StudentInfoSex varchar(2) not null,
StudentInfoCard varchar(18),
StudentInfoBrth dateTime,
StudentInfoNatns varchar(20),
StudentInfoTel varchar(30),
StudentInfoEmail varchar(30),
StudentInfoYear int
)
-----------------以上为数据库和表格的初始化





--insert语句
insert [dbo].[StudentCourse]
values(1,50,0,0,0,null,null)
--insert 插入多条
insert [dbo].[StudentCourse]
values(2,50,0,0,0,null,null),
(3,50,0,0,0,null,null),
(4,50,0,0,0,null,null),
(5,50,0,0,0,null,null)

--insert 插入部分数据
insert [dbo].[TeachCourse](TeachInfoID,CourseInfoID)
values(18,17)

--插入其它表的数据
--将学生信息表StudentInfo中女同学的学号、性别、身份证号码、家庭地址、联系电话插入到新表StudentInfo1
create table StudentInfo1
(
StudentInfoNum varchar(20) not null,
StudentInfoName varchar(30) not null,
StudentInfoSex varchar(2) not null,
StudentInfoCard varchar(18) ,
StudentInfoAddress varchar(60),
StudentInfoTel varchar(30)
)

insert into dbo.StudentInfo1
select StudentInfoNum,StudentInfoName,StudentInfoSex,StudentInfoCard,
StudentInfoNatns,StudentInfoTel
from dbo.StudentInfo
where StudentInfoSex='女'

--update 语句  更新
update dbo.StudentInfo
set StudentInfoName='李婵林',
    StudentInfoTel='15858095197',
    StudentInfoEmail='15858095197@qq.com'
where StudentInfoName='李婵'

--更新兼职老师的TeachInfoRMK
update dbo.TeachInfo
set TeachInfoRMK='每周上课不超过10节'
from TeachInfo a join TeachType b
on a.TeachTypeID=b.TeachTypeID
where TeachTypeName='兼职'

--将“网页设计”课程考试成绩不及格的在原分数基础上加5分
update StudentCourse
set StudentCourseExamGrd+=5
where StudentCourseExamGrd<60 and TeachCourseID in
(
select TeachCourseID from TeachCourse
where CourseInfoID=(select CourseInfoID from CourseInfo
                    where CourseInfoName='网页设计')
)

--delete  删除行
delete StudentCourse
where StudentCourseID=50 and TeachCourseID=4

delete StudentCourse
from StudentCourse a join StudentInfo b
on a.StudentCourseID=b.StudentInfoID
where StudentInfoName='刘莉'

--删除表中的所有数据
delete StudentInfo1

--truncate也可以删除表中的所有数据，效率更高，使用的系统资源更少
truncate table StudentInfo1


--查询语句
--1.查询院系信息表的所有信息
select * from DepInfo

--2查询教师信息表中的教师姓名。专业。和职称
select TeachInfoName,TeachInfoSpecil,TeachInfoTilte
from TeachInfo

--3、查询所有老师从事的专业
select TeachInfoSpecil from TeachInfo

--4、查询所有老师从事的专业消除重复
select distinct TeachInfoSpecil from TeachInfo

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

--6、查询班级信息表中的前百分之二十的记录的班级代号，班级名称。
select top 20 percent ClassInfoCode,ClassInfoName 
from ClassInfo

--7、查询课程信息表中课程名称，理论学时，实践学时
select CourseInfoName,CourseInfoRstPer,CourseInfoPraPer
from CourseInfo

--8、查询课程信息表中课程名称，理论学时，实践学时，总学时
select CourseInfoName,CourseInfoRstPer,CourseInfoPraPer,
        CourseInfoRstPer+CourseInfoPraPer
from CourseInfo

--9、查询课程信息表中课程名称，理论学时，实践学时，总学时，给每一列取名
select CourseInfoName as 课程名称,CourseInfoRstPer as 理论学时,CourseInfoPraPer as 实践学时,
        CourseInfoRstPer+CourseInfoPraPer as 总学时
from CourseInfo

--方法二  
select 课程名称=CourseInfoName,理论学时=CourseInfoRstPer,实践学时=CourseInfoPraPer,
        总学时=CourseInfoRstPer+CourseInfoPraPer
from CourseInfo

--11、查询某年以后出生的学生的学号、姓名、性别和出生日期，结果按年龄从小到大排序
--默认为 asc 升序， 降序desc
select StudentInfoNum,StudentInfoName,StudentInfoSex,StudentInfoBrth
from StudentInfo
where StudentInfoBrth>='1990-1-1'
order by StudentInfoBrth desc

--查询出生日期在1990-1-1之后的女生
select * from StudentInfo
where StudentInfoBrth > '1990-1-1' and StudentInfoSex='女'

--查询所有姓张的学生信息
select * from StudentInfo
where StudentInfoName like '张%'

--查询入学年份在2007到2009之间的学生信息
select * from StudentInfo
where StudentInfoYear between 2007 and 2009

select * from StudentInfo
where StudentInfoYear >= 2007 and StudentInfoYear <=2009

select * from StudentInfo
where StudentInfoYear in ( 2007 ,2008,2009)

--12、查询某年以后出生的学生的学号、姓名、性别和出生日期，结果按年龄从小到大排序
--当年龄相同时，按先女生后男生排序
select StudentInfoNum,StudentInfoName,StudentInfoSex,StudentInfoBrth
from StudentInfo
where StudentInfoBrth >='1990-1-1'
order by StudentInfoBrth desc,StudentInfoSex desc

--13、聚合函数    统计学生、教师的总人数  
--统计系部教学编制的总数、最大、最小及平均值
select COUNT(*) from StudentInfo
select COUNT(*) from TeachInfo
select  SUM(DepInfoPreO) as 总数,
        MAX(DepInfoPreO) as 最大值,
        MIN(DepInfoPreO) as 最小值,
        AVG(DepInfoPreO) as 平均值
        from DepInfo

--分组：
--14、查询教师表中有各种学历的人数
select TeachInfoknowl,COUNT(TeachInfoKnowl) as 数量
from TeachInfo
group by TeachInfoknowl

--15、统计1990年以后出生的男生和女生各多少人
select StudentInfoSex,COUNT(*)
from StudentInfo
where StudentInfoBrth>'1990-1-1'
group by StudentInfoSex

--16、显示所有学生的相关信息，并汇总学生总人数
select StudentInfoName,StudentInfoSex,StudentInfoNatns
from StudentInfo
compute COUNT(StudentInfoName)

--17、显示所有学生的相关信息，并按学生所属民族分类汇总
--分类汇总需要先排序
select StudentInfoName,StudentInfoSex,StudentInfoNatns
from StudentInfo
order by StudentInfoNatns
compute COUNT(StudentInfoNatns) by StudentInfoNatns



--连接查询
--内连接返回所有满足条件的结果，左连接左边的表不满足条件的也返回
--完全外连接左右表中不满足条件的都返回
--1、查询每个教师的基本信息及教师类别（内连接）
select  TeachInfoName,TeachInfoSex,TeachInfoTilte,TeachTypeName,
        TeachInfo.TeachTypeID
        from TeachInfo inner join TeachType
        on TeachInfo.TeachTypeID=TeachType.TeachTypeID
--使用别名
select  TeachInfoName,TeachInfoSex,TeachInfoTilte,TeachTypeName,
        a.TeachTypeID
        from TeachInfo a join TeachType b
        on a.TeachTypeID=b.TeachTypeID

--2、查询每位老师的授课情况，不管老师有没有授课都要包括其情况（左外连接）
select TeachInfoNum,TeachInfoName,TeachInfoSex,TeachInfoTilte,
        CourseInfoID
        from TeachInfo a left join TeachCourse b
        on a.TeachInfoID=b.TeachInfoID

--3。查询所有老师授课课程的选修情况 （右外连接） 
select StudentCourseID,StudentInfoID,TeachInfoID,CourseInfoID
from StudentCourse a right join TeachCourse b
on a.TeachCourseID=b.TeachCourseID

--4、使用完全外连接查询数据库中每位老师的授课情况
select TeachInfoNum,TeachInfoName,TeachInfoSex,TeachInfoTilte,
    TeachCourseID,CourseInfoID
    from TeachCourse a full join TeachInfo b
    on a.TeachInfoId=b.TeachInfoID

--5、查询所有女生的所在班级信息
select StudentInfoName,StudentInfoSex,ClassInfoName
from StudentInfo a join ClassInfo b
on a.ClassInfoID=b.ClassInfoID
where StudentInfoSex='女'

--6、查询学生所属的班级信息和专业信息   注意ClassInfo需要写在中间位置
select StudentInfoName,SpecilInfoName,ClassInfoName
from StudentInfo a join ClassInfo b
on a.ClassInfoID=b.ClassInfoID
join SpecilInfo c
on c.SpecilInfoID=b.SpilInfoID

--7、查询学生选课的课程数量由高到低排序
select StudentInfoName, COUNT(b.StudentInfoID) 课程门数
from StudentInfo a join StudentCourse b
on a.StudentInfoID=b.StudentInfoID
group by StudentInfoName
order by 课程门数 desc

--8、查看计算机工程系的教师授课课程的选修情况
select TeachInfoName,DepInfoName,COUNT(StudentInfoID)
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

--9、查询每个学生的选课情况，列出学生的姓名、课程名称、教师名
select StudentInfoName,CourseInfoName,TeachInfoName
from StudentInfo a join StudentCourse b
    on a.StudentInfoID=b.StudentInfoID
    join TeachCourse c
    on b.TeachCourseID=c.TeachCourseID
    join CourseInfo d
    on c.CourseInfoId=d.CourseInfoID
    join TeachInfo e
    on c.TeachInfoId=e.TeachInfoID

--10、利用并运算查询老师和学生的姓名
select StudentInfoName 姓名 from StudentInfo
union
select TeachInfoName from TeachInfo

--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、查找比所有课程类别ID为1的课程总学时都要高的课程
select CourseInfoName,CourseTypeID
from CourseInfo
where CourseInfoHotHrs > all
    (select CourseInfoHotHrs from CourseInfo
    where CourseTypeID=1)

--2、查询数据库中所有已获‘副教授’职称的女教师的姓名、性别、学历、专业和职称
select * from (
select TeachInfoName,TeachInfoSex,TeachInfoknowl,TeachInfoSpecil,TeachInfoTilte
from TeachInfo
where TeachInfoTilte='副教授') as t
where t.TeachInfoSex='女'

--3、查找开设了课程ID为7的课程的老师的相关信息
--首先查询课程ID为7的课程有哪些老师授课
--其次查询上一个不收里面相关老师的信息
select  TeachInfoName,TeachInfoSex,TeachInfoknowl,TeachInfoSpecil,TeachInfoTilte
from TeachInfo
where TeachInfoID in (select TeachInfoID from TeachCourse
where CourseInfoId=7)

--4、查找数据库中选修了7号课程的学生学号、姓名和联系电话
/*首先查询教授7号课程的老师信息，然后查询学生选课是否选了7号课程的老师*/
select StudentInfoNum,StudentInfoName,StudentInfoTel
from StudentInfo
where exists(
select * from StudentCourse
where StudentInfoID=StudentInfo.StudentInfoID
and exists(
select * from TeachCourse
where CourseInfoId=7 and 
TeachCourseID=StudentCourse.TeachCourseID))

--5、查询与“刘莉”同班同学的信息
select * from StudentInfo
where ClassInfoID=(
select ClassInfoID from StudentInfo
where StudentInfoName='刘莉')

--6、查询课程考试不及格的学生的姓名、性别
select StudentInfoName,StudentInfoSex
from StudentInfo
where StudentInfoID in (
select StudentInfoID from StudentCourse
where StudentCourseUsuGrd<60)

--7、查询课程考试不及格的学生的姓名和课程名
select 姓名=(select StudentInfoName from StudentInfo
            where StudentInfoID=a.StudentInfoID),
        课程名=(select CourseInfoName from CourseInfo
            where CourseInfoID in(
            select CourseInfoID from TeachCourse
            where TeachCourseID=a.TeachCourseID))
from StudentCourse a
where StudentCourseUsuGrd<60

--8、查询网页设计课程不及格的学生姓名。性别和电话
select StudentInfoName,StudentInfoSex,StudentInfoTel
from StudentInfo
where StudentInfoID in (
select StudentInfoID from StudentCourse
where StudentCourseUsuGrd<60 and TeachCourseID in(
select TeachCourseID from TeachCourse
where CourseInfoId=(
select CourseInfoID from courseInfo
where CourseInfoName='网页设计')))




--存储过程
--查询学生ID为6的学生姓名和已选课程门数，当选课门数在3门以上，输出“XX，已经完成了选课”
--否则输出“XX，还需选课
declare @sname varchar(50),@num int
select @sname=StudentInfoName from StudentInfo
where StudentInfoID=6
set @num=(select COUNT(*) from StudentCourse
        group by StudentInfoID
        having StudentInfoID=6)
if @num>=3
    begin
        print @sname + '，你已经完成了选课！'
    end
else
    print @sname + '，你还需要继续选课！'

--2、简单case结构：查询学生信息表中学生的性别，若是女生显示“female”,否则显示“male”
select StudentInfoName , StudentInfoSex,性别=case
                                            when StudentInfoSex='男' then 'male'
                                            when StudentInfoSex='女' 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

--4、定义一个存储过程，用于查询数据库中所有教师的姓名、性别、学历、职称和所教授的课程名称
--procedure可以简写为proc
create procedure ShowTeaCourse
as
select TeachInfoName,TeachInfoSex,TeachInfoknowl,TeachInfoSpecil,TeachInfoTilte,CourseInfoName
from TeachInfo a join TeachCourse b
on a.TeachInfoID=b.TeachInfoId
join CourseInfo c
on b.CourseInfoId=c.CourseInfoID

--5、执行存储过程的三种方式
execute ShowTeaCourse
exec dbo.ShowTeaCourse
dbo.ShowTeaCourse

    


--4、创建多语句表值函数，用来查询指定教师任教的课程名称。实践课时、理论课时和总课时
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='朱志奇'

create function FSelCourse(@teachName varchar(20))
returns @tech_Course table
(
课程名称 varchar(80),
理论学时 int,
实践学时 int,
总学时 int
)
as 
begin
    insert @tech_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、游标的使用
--1.声明游标；2.打开游标；3.提取数据；4.关闭游标；5.释放游标
--对学生信息表，定义一个查询“汉族，性别男”的学生姓名、性别、民族的游标，并输出游标结果
--声明相关变量
declare @sname varchar(20), @ssex varchar(5),@snatns varchar(5)
--声明游标
declare student_cursor cursor for
select StudentInfoName,StudentInfoSex,StudentInfoNatns 
from StudentInfo
where StudentInfoNatns='汉' and StudentInfoSex='男'
--打开游标
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 [moon-PC\Teacher]--创建windows身份验证的用户
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，密码改为“sql10o123”
alter login Teachers
with password='sql10o123'
old_password='sql@123%'

--4、禁用名为“Teachers”的登陆
alter login Teachers enable--启用
alter login Teachers disable--禁用
drop login Teachers--删除


--用户管理：
--6、创建名为 teachUser的登录名，在数据库中，创建用户 teach 与teachUser登录名对应
create login teachUser
with password ='123',
default_database=[master]
go
use StudentDB
go
create user teach
for login teachUser

--7、修改数据库中创建的用户teach 将名称改为teach2
alter user teach
with name=teach2

--8、删除数据库中用户teach
drop user teach


--用户权限管理
--9、在数据库中，创建用户teacher,对应登录名为teachers,并将表teachinfo的select权限授予teacher
create login teachers
with password='123',
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对表teachinfo的select权限
revoke select on TeachInfo to teacher

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

--13、将SQL Server登录名 teachers 从sysadmin 固定服务器角色中删除
exec sp_dropsrvrolemember 'teachers','sysadmin'

--14、创建一个新的登陆test ,将其映射到数据库的用户test，设置数据库角色的成员，使之对该数据库中的数据只有只读的权限
create login test
with password ='123',
default_database=[master]
go
use StudentDB
go
create user test
for login test
go
exec sp_addrolemember 'db_datareader' ,'test'

--15、将数据库中的用户test从db_datareader角色中删除
exec sp_dropsrvrolemember  'db_datareader' ,'test'

--16、将数据库中的添加角色MyTeacher,并将用户teacher1（对应登录名teach1）和teacher2（对应登陆名teach2）添加到该角色中，
--赋予该角色在教师信息表Teachinfo上有插入、修改和删除的全检，拒绝该角色创建表的权限
create login teach1
with password='123',
default_database=[master]
go 
create login teach2
with password='123',
default_database=[master]
go
use StudentDB
go
create user Teacher1
for login teach1
go 
create user Teacher2
for login teach2
go
create role MyTeacher--创建角色
go
exec sp_addrolemember 'Myteacher','Teacher1'--添加角色
go
exec sp_addrolemember 'Myteacher','Teacher2'--添加角色
go
grant insert,update,delete on TeachInfo to Myteacher
with grant option
go
deny create table to Myteacher

--https://zhuanlan.zhihu.com/p/72856084
use SchoolDB
--新增学生
IF OBJECT_ID (N'PROC_INSERT_StudentInfo', N'P') IS NOT NULL
    DROP PROCEDURE  PROC_INSERT_StudentInfo;
GO
CREATE PROCEDURE  PROC_INSERT_StudentInfo
    @StudentName  VARCHAR(50),
    @StudentAge int,
    @StudentSex varchar(2)
AS 
    INSERT INTO SchoolDB.dbo.StudentInfo([Name],[Age],[Sex])
    VALUES(@StudentName,@StudentAge,@StudentSex)
    SELECT SCOPE_IDENTITY() AS [StudentId]
GO

--修改
IF OBJECT_ID (N'PROC_UPDATE_StudentInfo', N'P') IS NOT NULL
    DROP PROCEDURE  PROC_UPDATE_StudentInfo;
GO
CREATE PROCEDURE  PROC_UPDATE_StudentInfo
    @StudentId int,
    @StudentName  VARCHAR(50),
    @StudentAge int,
    @StudentSex varchar(2)
AS 
    UPdaTE SchoolDB.dbo.StudentInfo
    SET Name=@StudentName,Age=@StudentAge,Sex=@StudentSex
    where StudentId=@StudentId
GO

--删除
IF OBJECT_ID (N'PROC_Delete_StudentInfo', N'P') IS NOT NULL
    DROP PROCEDURE  PROC_Delete_StudentInfo;
GO
CREATE PROCEDURE  PROC_Delete_StudentInfo
    @StudentId  int
AS 
    delete from SchoolDB.dbo.StudentInfo where StudentId=@StudentId;
GO