数据库练习

--3.1创建学生成绩管理数据库(StudScore_DB1)

create database StudScore_DB1 --数据库创建语句

--3.2创建学生成绩管理数据库(StudScore_DB2),注意要求
create database StudScore_DB2
ON
(name=StudScore_DB2_Data1, --数据库主文件逻辑名称
filename='D:\StudScore_DB2_Data1.mdf', --数据库主文件物理文件名
size=10, --数据库主文件初始大小
maxsize=unlimited, ---数据库主文件最大尺寸无限大
filegrowth=10%) --数据库主文件增长速度
LOG ON
(name='StudScore_DB2_log1', --数据库日志文件逻辑名称
filename='D:\StudScore_DB2_log1.ldf', --数据库日志文件物理文件名
size=5MB, --数据库日志文件初始大小
maxsize=25MB, --数据库日志文件最大尺寸

filegrowth=1MB) --数据库日志文件增长速度

--3.3修改学生成绩管理数据库(StudScore_DB2)，添加5MB的次要数据文件
alter database StudScore_DB2
add file(
name=StudScore_DB2_Data2, --数据库次要数据文件逻辑名称
filename='D:\StudScore_DB2_Data2.ndf', --数据库次要数据文件物理文件名
size=5MB , --数据库次要数据文件初始大小
maxsize=100MB, --数据库次要数据文件最大尺寸
filegrowth=5MB --数据库次要数据文件增长速度

)

--3.4修改学生成绩管理数据库(StudScore_DB2)，添加两个5MB大小的日志文件
alter database StudScore_DB2
add log file
--添加的第一个5MB的日志文件
(name='StudScore_DB2_Log2', --数据库日志文件逻辑名称
filename='D:\StudScore_DB2_Log2.ldf', --数据库日志文件物理文件名
size=5MB, --数据库日志文件初始大小
maxsize=100MB, --数据库日志文件最大尺寸
filegrowth=5MB), --数据库日志文件增长速度
--添加的第二个5MB的日志文件
(name='StudScore_DB2_Log3', --数据库日志文件逻辑名称
filename='D:\StudScore_DB2_Log3.ldf', --数据库日志文件物理文件名
size=5MB, --数据库日志文件初始大小
maxsize=100MB, --数据库日志文件最大尺寸

filegrowth=5MB) --数据库日志文件增长速度

--3.5删除学生成绩管理数据库(StudScore_DB2)

drop database StudScore_DB2 --删除学生成绩管理数据库语句

--3.6创建学生信息表(StudInfo)
create table StudInfo
(
StudNo varchar(15) primary key, --设置StudNo为主键字段
StudName varchar(20) not null,
SyudSex Char(2) default '男' not null, --数据表中默认值设置
StudBirthDay datetime null,
ClassID varchar(10) not null

)

--3.7创建班级信息表(ClassInfo)
create table ClassInfo
(
ClassID varchar(10) primary key, --主键约束
ClassName varchar(50) not null,
ClassDesc varchar(100) null

)

--3.8创建带外键关系的学生信息表(StudInfo)
drop table StudInfo --删除前面的学生信息表(StudInfo)
create table StudInfo
(
StudNo varchar(15) primary key, --设置StudNo为主键字段
StudName varchar(20) not null,
SyudSex Char(2)not null, 
StudBirthDay datetime null,
ClassID varchar(10) Constraint FK_ClassID Foreign key references ClassInfo(ClassID)
not null --建立外键关系

)

--3.9创建带约束的学生成绩信息表(StudScoreInfo)
create table StudScoreInfo
(
StudNo varchar(15),
CourseID varchar(10),
StudScore Numeric(4,1) default 0 Check(StudScore>=0 AND StudScore<=100),
--使用Check约束学生成绩在0到100之间取值
Constraint PK_S_C Primary Key (StudNo,CourseID)
--建立复合主键

)

--3.10创建带标识列(IDENTITY)的学生报到信息表(StuEnro11Info)
create table StuEnro11Info
(
Seq_ID INT IDENTITY (100001,1), --报名序号初值为100001，步长为1(自动编号)
StudNo varchar(15) Primary Key,
StudName varchar(30) not null

)

--3.11修改学生成绩信息表(StudScoreInfo)增加自动编号新列

alter table StudScoreInfo add Seq_ID int Identity(1001,1)

--3.12修改学生成绩信息表(StudScoreInfo)删除主键(PK_S_C)

alter table StudScoreInfo drop constraint PK_S_C

--3.13修改学生成绩信息表(StudScoreInfo)将(StudNo,CourseID)设置为复合主键(PK_S_C)

alter table StudScoreInfo add constraint PK_T_C primary key(StudNo,CourseID)

--3.14修改学生成绩信息表(StudScoreInfo)删除自动编号列

alter table StudScoreInfo drop column Seq_ID

--3.15删除学生成绩信息表(StudScoreInfo)

drop table StudScoreInfo

--3.16使用insert语句为班级信息表（ClassInfo）添加新记录
insert into ClassInfo
(ClassID,ClassName,ClassDesc)
values

('20000704','计算机2000','计算机怎样')

--3.17使用insert语句为班级信息表（ClassInfo）添加新记录
insert into ClassInfo
(ClassName,ClassID)
values

('20000704','计算机2000')

--3.18使用insert语句为学生成绩信息表（StudScoreInfo）添加新记录
insert into StudScoreInfo
(StudNo,CourseID,StudScore)
values
('20000704001','A0101',80.5)
--3.19更新班级编号为“20000704”的班级名称为“计科2000级”、班级描述为空值
update ClassInfo
set ClassName='计科2000级',ClassDesc=null

where ClassID='20000704'

--3.20删除班级编号为“20000704”的班级信息
delete from ClassInfo

where ClassID='20000704'

--3.21删除学生成绩表（StudScoreInfo）所有记录

truncate table StudScoreInfo

--3.22查询学生信息表（StudInfo）所有记录
select StudNo ,StudName ,StudSex,StudBirthDay ,ClassID  from StudInfo
--还可用符号*来选取表的全部列

select *from StudInfo

--3.23查询学生信息表（StudInfo）部分列记录

select StudNo ,StudName ,ClassID  from StudInfo

--3.24在查询学生信息表（StudInfo）中使用连接列
select StudNo+StudName ,StudName,StudSex,StudName,ClassID  from StudInfo

--3.25在查询学生信息表（StudScoreInfo）中使用计算列

select StudNo,CourseID,StudScore+5,StudScore*0.8 from StudScoreInfo 
--3.26查询学生信息表（StudInfo）中不重复的性别记录

select distinct StudSex from StudInfo

--3.27查询学生信息表（StudInfo）中姓名和性别不重复的记录

select distinct StudName,StudSex from StudInfo

--3.28查询学生信息表（StudInfo）中前10条的记录

select top 10* from StudInfo

--3.29查询学号为20050319001成绩中最高的10门成绩
select top 10* from StudScoreInfo

where StudNo='20050319001' order by StudScore desc

--3.30查询学号为20050319001成绩中的20%条记录

select top 20 percent * from StudScoreInfo where StudNo='20050319001' 

--3.31查询学生信息表（StudInfo）学号、姓名、班级编号信息，并以中文字段名显示

select StudNo as 学号,姓名=StudName,ClassID 班级编号 from StudInfo

--3.32为字符串连接取别名

select StudNo+StudName as 学号姓名,性别='学生性别:'+StudSex from StudInfo

--3.33将学生信息表（StudInfo）中查询的部分字段结果存储新表
select StudNo as 学号,StudName 姓名,出生日期=StudBirthDay
into StudInfoBack

from StudInfo

--3.34选择学生信息表（StudInfo）前10条记录插入新表中
select top 10 StudNo as 学号,StudName 姓名,StudSex as 性别
into ChineseStudInfo

from StudInfo

--3.35使用表别名查询学生信息表（StudInfo）记录
select StudInfo.StudNo ,StudInfo.StudName from StudInfo
select s.StudNo,s.StudName from StudInfo as s

select s.StudNo 学号,s.StudName as 姓名,'班级编号'=ClassID FROM StudInfo  s

--3.36查询成绩大于70的学生成绩信息

select * from StudScoreInfo where StudScore>70

--3.37查询成绩90以上的学生成绩信息

select * from StudScoreInfo where StudScore>=90

--3.38查询学号为"20050319001"的学生信息

select * from StudInfo where StudNo ='20050319001'

--3.39查询学号大于"20050319001"的学生信息

select * from StudInfo where StudNo >'20050319001'

--3.40查询1985年1月1日以后出生的学生信息

select * from StudInfo where StudBirthDay >='1985/01/01'

--3.41查询性别不为"男"的学生信息

select * from StudInfo where StudSex<>'男'

--3.42查询学生成绩60到70之间的所有记录

select * from StudScoreInfo where StudScore>=60 and StudScore<=70

--3.43查询学号为20050319002或99070405的学生信息

select * from StudInfo where StudNo ='20050319002' or StudNo ='99070405'

--3.44查询性别为"女"的学生信息

select * from StudInfo where not StudSex='男'

--3.45查询学号为"20050319001",成绩80分以上的所有成绩记录

select * from StudScoreInfo where StudNo='20050319001' and StudScore>=80

--3.46查询学号为"20050319001",成绩在90到100之间的所有成绩记录

select * from StudScoreInfo where StudNo='20050319001' and StudScore>=90 and StudScore<=100

--3.47使用not和and运算符查询学生成绩在80到90之间的所有记录

select * from StudScoreInfo where not StudScore>80 and StudScore<=90

--3.48使用not和or运算符查询学生成绩表的所有记录

select * from StudScoreInfo where not StudScore>80 or StudScore<=90

--3.49括号优先,查询结果为空集

select * from StudScoreInfo where not (StudScore>80 or StudScore<=90)

--3.50查询学生成绩在70到80之间的学生成绩记录

select * from StudScoreInfo where StudScore between 70 and 80

--3.51查询学生成绩不在70到80之间的学生成绩记录

select * from StudScoreInfo where StudScore not between 70 and 80

--3.52查询学号为20050319001,成绩在[90,100]之间的所有记录

select * from StudScoreInfo where StudNo='20050319001' and StudScore between 90 and 100

--3.53查询学生成绩在[60,70]或者成绩在[80,90]的记录

select * from StudScoreInfo where  StudScore between 60 and 70 or StudScore between 80 and 90

--3.54查询学号为20050319002或99070405的学生基本信息

select * from StudInfo where StudNo in('20050319002' ,'99070405')

--3.55查询学号不为20050319002或99070405的学生基本信息

select * from StudInfo where StudNo not in('20050319002' ,'99070405')

--3.56查询姓名以"胡"字开头的学生基本信息

select * from StudInfo where StudName like '胡%'

--3.57查询姓名中包含"文"字的学生基本信息
select * from StudInfo where StudName like '%文%'

--3.58查询姓名中第二个字为"丽"字的学生基本信息

select * from StudInfo where StudName like '_丽%'

--3.59查询班级描述为空的班级信息

select * from ClassInfo where ClassDesc is NULL 

--3.60查询班级描述不为空的班级信息

select * from ClassInfo where ClassDesc is not NULL 

--3.61统计所有成绩平均分

select AVG (StudScore) FROM StudScoreInfo 

--3.62统计学号为"20050319001"学生成绩平均分

select AVG (StudScore) FROM StudScoreInfo where StudNo ='20050319001'

--3.63统计学号为"20050319001"学生成绩记录条数(即课程门数)

select COUNT (*) from  StudScoreInfo where StudNo ='20050319001'

--3.64统计学号为"20050319001"学生成绩平均分、课程门数并指定别名
select AVG (StudScore) as AvgScore,CourseCount=COUNT (*) 

from  StudScoreInfo where StudNo ='20050319001'

--3.65统计学号为"20050319001"学生成绩总分、最高分、最低分、平均分、课程门数
select SUM (StudScore),MAX (StudScore),MIN(StudScore),
AVG(StudScore),COUNT(*),SUM(StudScore)/COUNT (*)AvgScore

from  StudScoreInfo where StudNo ='20050319001'

--3.67统计各学生平均分

select StudNo,AVG (StudScore)AvgScore from StudScoreInfo group by StudNo 

--3.68统计各学生平均分,使用CAST函数保留小数位数
select StudNo,CAST(AVG (StudScore)AS numeric(4,1))AvgScore

 from StudScoreInfo group by StudNo 

 --3.69统计各学生所上的课程门数

 select StudNo,COUNT (*)CourseCount from  StudScoreInfo group by StudNo 

 --3.70统计各学生平均分和所上的课程门数
 select StudNo,COUNT (*)CourseCount, CAST(AVG (StudScore)AS numeric(4,1))AvgScore

 from  StudScoreInfo group by StudNo 

 --3.71统计各学生平均分、所上的课程门数、平均分和计算平均分
 select StudNo,SUM (StudScore) as SumScore,COUNT (*)CourseCount,
CAST(AVG (StudScore)AS numeric(4,1))AvgScore1,
SUM (StudScore)/COUNT (*)as AvgScore2

from  StudScoreInfo group by StudNo 

--3.72查询平均分80分以上的学生记录,使用HAVING子句
select StudNo,SUM (StudScore) AS SumScore,COUNT (*)CourseCount,
CAST(AVG (StudScore)AS numeric(4,1))AvgScore
from StudScoreInfo group by StudNo 

HAVING  AVG (StudScore)>=80

--3.73统计学生课程成绩为80分以上的学生平均分,使用WHERE子句
select StudNo,SUM (StudScore) AS SumScore,COUNT (*)CourseCount,
CAST(AVG (StudScore)AS numeric(4,1))AvgScore
from StudScoreInfo where StudScore>=80

group by StudNo 

--3.74统计重修10门以上的学生平均分信息,同时使用WHERE和HAVING子句
select StudNo,AVG (StudScore)as AvgScore
from StudScoreInfo where StudScore<60
group by StudNo 

HAVING COUNT (*) >=10

--3.75查询学号为"20050319001"的学生成绩记录,并按成绩高低排序
select *from StudScoreInfo where StudNo ='20050319001'

order by StudScore DESC

--3.76查询学号为"20050319001"的学生成绩记录,并按成绩高低排序,成绩相同的按课程编号升序排列
select * from StudScoreInfo where StudNo ='20050319001'

order by StudScore DESC,CourseID ASC

--3.77统计各学生平均分,并按平均分高低排序
select StudNo ,AVG (StudScore)as 平均分
from StudScoreInfo
group by StudNo 
order by 平均分 DESC