1、Char(1)课程号(唯一)Cname课程名称Credit学分create table Course(课程号 char(1) primary key,课程名称 varchar(20) not null,学分 smallintClass班级表班级号(唯一)Speciality班级所在专业InyearChar(4)入校年份NumberInteger班级人数Monitor班长学号use GradeManager create table Class(班级号 char(5) primary key,专业 varchar(20) not null,入学年份 char(4) not null,班级人数 i
2、nteger ,班长学号 char(7)Grade成绩表学号课程号GmarkDecimal(4,1)成绩create table Grade(学号 char(7) not null,课程号 char(1) not null,成绩 decimal(4,1)图2-4 成绩管理数据库中的表结构2000101李勇男20003112000102刘诗晨女192000103王一鸣003122000104张婷婷212001101李勇敏013112001102贾向东222001103陈宝玉2001104张逸凡1数据库42离散数学3管理信息系统操作系统5数据结构6数据处理7C语言计算机软件200089计算机应用6
3、82001989288867855656654837080907656 图2-5 成绩管理数据库中的数据加入完整性约束说明,如图3-6所示,请用SQL语句完成图中约束的说明。完整性约束主码男或女,默认为男大于14,小于65外部码,级联alter table Studentadd constraint PK_Studentprimary key (学号)-添加主键约束add constraint DF_sex default (男) for 性别-添加默认值,默认性别为男add constraint ck_xingbie check (性别= or 性别=女-添加check约束,指定性别输入为男
4、或女alter table studentadd constraint ck_nianling check (年龄 between 14 and 65);-添加check约束,指定年龄输入范围是1465add constraint FK_class_student foreign key (班级)References Class (班级号)-添加外键约束on delete cascade-指定为级联删除 1、2、3、4、5、6之一alter table Courseadd constraint PK_Course primary key (课程号)-为课程号添加主键add constraint
5、 ck_Course check (学分=1or 学分=2 or 学分=3456-添加check约束,指定学分只能为1,2,3,4,5,6班级号大于1,小于100外部码alter table classadd constraint CK_banjirenshu check (班级人数1 and 班级人数=0 and 成绩=100)-添加check约束,指定成绩范围为0100 图3-6 加了约束说明后的表结构2. 针对以上四个表,用SQL语言完成以下各项操作:1 给学生表增加一属性Nation(民族),数据类型为Varchar(20);use GradeManageradd 民族 varchar
6、(20) null-使用add可以添加新列2 删除学生表中新增的属性Nation;drop column 民族 -使用drop删除列3 向成绩表中插入记录(”2001110”,”3”,80);go insert into Student values (2001110,李四2001311-添加该记录是为了能使下一条记录顺利添加,因为grade表的【学号】字段列上含外键约束,该外键参照 Student表的主键字段列【学号】,为了保证数据的完整性,Student表中因含有记录的一行数据。insert Grade80-使用insert into添加一条记录4 修改学号为”2001110”的学生的成绩
7、为70分;update Grade set 成绩=70where 学号 =2001110-使用update修改一条记录5 删除学号为”2001110”的学生的成绩记录;delete from Grade where 学号 = 2001110-使用delete语句删除一条记录3. 针对以上四个表,用SQL语言完成以下各项查询:1.找出所有被学生选修了的课程号;select distinct 课程号 from Grade -从grade表中查询出所用被学生选修的课程号(grade表为成绩表,被选修的课程必须有成绩,所以从该表中查询),distinct关键字用来删除重复行。 2.找出01311班女学
8、生的个人信息; use GradeManagerselect *from Student where 班级=01311 and 性别=3.找出01311班、01312班的学生姓名、性别、出生日期、当前时间;select 姓名,性别,出生日期=YEAR(GETDATE()-年龄,YEAR(GETDATE() as 前时间from Studentwhere 班级=01311 or 班级=01312- YEAR(GETDATE()函数用来获得当前的年份。4.找出所有姓李的学生的个人信息;where 姓名 like 李%-使用like进行模糊查询;语法为:匹配表达式 not like 匹配模式串 ;用
9、于匹配模式串的通配符有:% 百分号 代表零个或多个任意字符串;- 下划线 代表一个任意字符,双字节字符应对应两个下划线,如汉字; 方括号 代表方括号中列出的或属于指定范围内的任何单一字符; 括在方括号中的 代表不在方括号中列出或不属于指定范围内的任何单一字符;5.找出学生李勇所在班级的学生人数; use GradeManager select 班级人数 from Class inner join Student on Student.学号=class.班长学号where 姓名=李勇 go -使用 inner join关键字实现内连接,内连接为系统默认的,所以inner 可以省略; 格式:fro
10、m inner join on 可拆分为:表名1.字段列名1 表名2.字段列名2 运算符有:= , != , , , ; 根据分,又可以分为:等值连接和非等值连接 select 班级人数from Class,Studentwhere Student.学号 = Class.班长学号 and 姓名= -不使用 join 关键字实现内连接, where (select 年龄 from Student where 姓名=) and 年龄25 go4 找出选修了课程操作系统的学生学号和姓名;select 学号,姓名 from student where 学号 in(select 课程号 from Cou
11、rse where 课程名称=)5 找出所有没有选修1号课程的学生姓名;select 姓名 from student where 学号 not in(select 学号 from grade where 课程号=6 找出选修了全部课程的学生姓名。select 姓名 from student where 学号 in(select 学号 from grade group by 学号having count(distinct 学号) in(select count(课程号) from course)-group by 语句根据一列或多列的值对查询数据集进行分组;Having 子句用来对生成的各个组进
12、行条件筛选,只对满足筛选条件的那些组进行统计与汇总;having子句如果与group by 子句同时出现,它只能出现在group by 子句之后;如果脱离了group by 子句,having 子句的行为与where子句一样,但用法有所限制,要求having子句中出现的所用列必须同时出现在select语句的中。Where子句与having子句的区别如下:1,where子句作用的对象为数据表或视图,从中选择满足条件的记录,having子句作用于查询数据集,从中选出满足条件的组。2.having子句中的可以使用聚合函数,而where子句中的=3)6. 针对以上四个表,用SQL语言完成以下各项操作:1 对每个班,求学生的平均年龄,并把结果存入数据库;select AVG(年龄) as 平均年龄into studnet_avg_agegroup by 班级-into子句用来将查询结果数据集保存到指定名称的数据表中;2 将01311班的全体学生的成绩置零;set 成绩=0 where 学号 in(select 学号 from S
copyright@ 2008-2022 冰豆网网站版权所有
经营许可证编号:鄂ICP备2022015515号-1