create database StudScore_DB1 --数据库创建语句
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) --数据库日志文件增长速度
alter database StudScore_DB2
add file(
name=StudScore_DB2_Data2, --数据库次要数据文件逻辑名称
filename='D:\StudScore_DB2_Data2.ndf', --数据库次要数据文件物理文件名
size=5MB , --数据库次要数据文件初始大小
maxsize=100MB, --数据库次要数据文件最大尺寸
filegrowth=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) --数据库日志文件增长速度
drop database StudScore_DB2 --删除学生成绩管理数据库语句
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
)
create table ClassInfo
(
ClassID varchar(10) primary key, --主键约束
ClassName varchar(50) not null,
ClassDesc varchar(100) null
)
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 --建立外键关系
)
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)
--建立复合主键
)
create table StuEnro11Info
(
Seq_ID INT IDENTITY (100001,1), --报名序号初值为100001,步长为1(自动编号)
StudNo varchar(15) Primary Key,
StudName varchar(30) not null
)
alter table StudScoreInfo add Seq_ID int Identity(1001,1)
alter table StudScoreInfo drop constraint PK_S_C
alter table StudScoreInfo add constraint PK_T_C primary key(StudNo,CourseID)
alter table StudScoreInfo drop column Seq_ID
drop table StudScoreInfo
insert into ClassInfo
(ClassID,ClassName,ClassDesc)
values
('20000704','计算机2000','计算机怎样')
insert into ClassInfo
(ClassName,ClassID)
values
('20000704','计算机2000')
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'
delete from ClassInfo
where ClassID='20000704'
truncate table StudScoreInfo
select StudNo ,StudName ,StudSex,StudBirthDay ,ClassID from StudInfo
--还可用符号*来选取表的全部列
select *from StudInfo
select StudNo ,StudName ,ClassID from StudInfo
select StudNo+StudName ,StudName,StudSex,StudName,ClassID from StudInfo
--3.25在查询学生信息表(StudScoreInfo)中使用计算列
--3.26查询学生信息表(StudInfo)中不重复的性别记录
select distinct StudSex from StudInfo
select distinct StudName,StudSex from StudInfo
select top 10* from StudInfo
select top 10* from StudScoreInfo
where StudNo='20050319001' order by StudScore desc
select top 20 percent * from StudScoreInfo where StudNo='20050319001'
select StudNo as 学号,姓名=StudName,ClassID 班级编号 from StudInfo
select StudNo+StudName as 学号姓名,性别='学生性别:'+StudSex from StudInfo
select StudNo as 学号,StudName 姓名,出生日期=StudBirthDay
into StudInfoBack
from StudInfo
select top 10 StudNo as 学号,StudName 姓名,StudSex as 性别
into ChineseStudInfo
from 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
select * from StudScoreInfo where StudScore>70
select * from StudScoreInfo where StudScore>=90
select * from StudInfo where StudNo ='20050319001'
select * from StudInfo where StudNo >'20050319001'
select * from StudInfo where StudBirthDay >='1985/01/01'
select * from StudInfo where StudSex<>'男'
select * from StudScoreInfo where StudScore>=60 and StudScore<=70
select * from StudInfo where StudNo ='20050319002' or StudNo ='99070405'
select * from StudInfo where not StudSex='男'
select * from StudScoreInfo where StudNo='20050319001' and StudScore>=80
select * from StudScoreInfo where StudNo='20050319001' and StudScore>=90 and StudScore<=100
select * from StudScoreInfo where not StudScore>80 and StudScore<=90
select * from StudScoreInfo where not StudScore>80 or StudScore<=90
select * from StudScoreInfo where not (StudScore>80 or StudScore<=90)
select * from StudScoreInfo where StudScore between 70 and 80
select * from StudScoreInfo where StudScore not between 70 and 80
select * from StudScoreInfo where StudNo='20050319001' and StudScore between 90 and 100
select * from StudScoreInfo where StudScore between 60 and 70 or StudScore between 80 and 90
select * from StudInfo where StudNo in('20050319002' ,'99070405')
select * from StudInfo where StudNo not in('20050319002' ,'99070405')
select * from StudInfo where StudName like '胡%'
select * from StudInfo where StudName like '%文%'
--3.58查询姓名中第二个字为"丽"字的学生基本信息
select * from StudInfo where StudName like '_丽%'
select * from ClassInfo where ClassDesc is NULL
select * from ClassInfo where ClassDesc is not NULL
select AVG (StudScore) FROM StudScoreInfo
select AVG (StudScore) FROM StudScoreInfo where StudNo ='20050319001'
select COUNT (*) from StudScoreInfo where StudNo ='20050319001'
select AVG (StudScore) as AvgScore,CourseCount=COUNT (*)
from StudScoreInfo where StudNo ='20050319001'
select SUM (StudScore),MAX (StudScore),MIN(StudScore),
AVG(StudScore),COUNT(*),SUM(StudScore)/COUNT (*)AvgScore
from StudScoreInfo where StudNo ='20050319001'
select StudNo,AVG (StudScore)AvgScore from StudScoreInfo group by StudNo
select StudNo,CAST(AVG (StudScore)AS numeric(4,1))AvgScore
from StudScoreInfo group by StudNo
select StudNo,COUNT (*)CourseCount from StudScoreInfo group by StudNo
select StudNo,COUNT (*)CourseCount, CAST(AVG (StudScore)AS numeric(4,1))AvgScore
from StudScoreInfo group by StudNo
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
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
select StudNo,SUM (StudScore) AS SumScore,COUNT (*)CourseCount,
CAST(AVG (StudScore)AS numeric(4,1))AvgScore
from StudScoreInfo where StudScore>=80
group by StudNo
select StudNo,AVG (StudScore)as AvgScore
from StudScoreInfo where StudScore<60
group by StudNo
HAVING COUNT (*) >=10
select *from StudScoreInfo where StudNo ='20050319001'
order by StudScore DESC
select * from StudScoreInfo where StudNo ='20050319001'
order by StudScore DESC,CourseID ASC
select StudNo ,AVG (StudScore)as 平均分
from StudScoreInfo
group by StudNo
order by 平均分 DESC