15-sql复习

一、基础理论
1、数据管理发展阶段：人工管理、文件管理，数据库系统阶段。

2、SQL Server系统数据库master、msdb、model、tempdb
3、数据库文件分类：主数据文件、辅助/次数据文件、
4、DBA、DBS、DBMS、DBS、DB
5、约束：主键primary key、外键foreign key、检查check、默认default、非空not null
6、创建数据库create database 、创建数据表create table
7、distinct、asc/desc、sum/count/avg、group by、having
8、插入数据insert 、修改数据update、删除数据delete
9、视图view，视图中字段最多可以有1024个、视图定义加密with encryption
二、库表设计
1、创建一个数据库，名称为RJDataBase，数据文件和日志文件全部放在D盘“dbfile”中，主文件初始化大小为3M，增长方式为30％每次，最大值为50MB；次数据文件初始化大小为5MB，最大值15MB，增长方式为2MB每次；日志文件初始化大小为3MB，不限定大小，其他属性默认。

2、在数据库RJDataBase中创建班级表和学生表，表结构如下所示。

班级表名为“tbClass”
字段名数据类型长度约束含义
classID nchar 10 主键班级编号className nchar 20 非空班级名
memo nvarchar 50 备注
create table tbClass
(
classID nchar(10) primary key,
className nchar(20) not null,
memo nvarchar(50)
)
go
学生表名为“tbStudent”
字段名数据类型长度约束含义
stuD nchar 10 学号班级编号stuName nchar 20 非空姓名
sex nvarchar 50 默认“男”性别
height int 4 150-200 身高
dateBirth datetime 8 出生日期classID nchar 10 外键班级编号
memo nvarchar 50 备注
create table tbStudent
(
stuD nchar(10) foreign key ,
stuName nchar(10) not null,
sex nvarchar(50) default '男'，
height int(4) (height>150 and height<200),
dateBirth datetime(8),
classID nchar(10) constraint [fk_classID] foreign key ([classID]) references [tbclass] ([classID]), memo nvarchar(50)
)
三、数据更新
1、使用insert语句向班级表和学生表中各插入3条记录
2、使用update语句修改表中记录
3、使用delete语句删除一个班级记录以及该班级所包含的学生记录。

四、查询综合
数据源：StuSocietyDB
1、查询所有班级名称以“软件”开头的且性别为“男”的会员信息，结果如下所示：
select tbMember.classID as班级编号,className as班级名称,memberID as会员号,stuID as 学号,stuName as姓名
from dbo.tbMember
join dbo.tbClass
on tbClass.classID=tbMember.classID
where gender='男'and className like'软件%'
2、查询如下所示信息。

select societyName as社团名称,stuName as姓名,gender as性别,telephone as电话
from tbSociety
left join tbMember
on tbSociety.societyID=tbMember.societyID
3、查询如下所示信息
select tbSociety.societyName as社团名称,avg(activityFunds)as平均经费
from dbo.tbActivity
join dbo.tbSociety
on dbo.tbSociety.societyID=dbo.tbActivity.societyID
group by societyName
3-2，对上题进行修改，查询结果只显示平均经费700以上的
select tbSociety.societyName as社团名称,avg(activityFunds)as平均经费
from dbo.tbActivity
join dbo.tbSociety
on dbo.tbSociety.societyID=dbo.tbActivity.societyID
group by societyName
having avg(activityFunds)>700
4、查询如下所示信息
select tbSociety.societyID as社团编号,societyName as社团名称,a.会员编号,a.系部名称,a.班级,a.姓名
from dbo.tbSociety
join dbo.tbMember
on tbMember.societyID=tbSociety.societyID
left join(select memberID as会员编号,deptName as系部名称,className as班级,stuName as 姓名
from dbo.tbMember
join dbo.tbClass
on dbo.tbMember.classID=dbo.tbClass.classID
join dbo.tbDept
on dbo.tbDept.deptID=dbo.tbClass.deptID)as a
on tbMember.memberID=a.会员编号
5、使用子查询查询由代码天地组织的社团活动，结果如下所示
select activityName as活动名称,activityPlace as活动地点,activityFunds as活动经费
from tbActivity
where activityFunds=
(select activityFunds
from dbo.tbActivity
join dbo.tbSociety
on tbSociety.deleteFlag=tbActivity.deleteFlag
where societyName='代码天地'and activityFunds='1000')
6、查询系部名称以及系部包含的班级，结果取前10条，按系部名称升序，班级名称降序排序，结果如下所示。

select top 10 deptName as系部名称,className as班级名称
from dbo.tbDept
join dbo.tbClass
on tbClass.deptID=tbDept.deptID
order by deptName asc,className desc
7、创建一个视图，视图名为为“”，通过该视图可以查询语“select * from v_smember”查询出如下结果，并且对所定义的视图进行定义语句的加密处理。

use StuSocietyDB
go
create view v_smember
with encryption
as
select societyName,societyPurpose,stuName
from dbo.tbMember a join dbo.tbSociety b
on a.societyID=b.societyID
go
select*
from v_smember
8、查询出所有班级名称中含有“管理”的班级信息，查询内容包含班级编号，班级名称，系部编号。