数据库系统概论第三章学习通作业(建表、查询、修改、删除、视图)

最新推荐文章于 2025-04-17 10:04:57 发布

Java+每天进步亿点点

最新推荐文章于 2025-04-17 10:04:57 发布

阅读量2.5k

点赞数 2

文章标签：学习 sql 数据库 mysql

本文链接：https://blog.youkuaiyun.com/qq_43280993/article/details/123513041

版权

设有3个关系：

S(S#,SNAME,SEX,AGE)

SC(S#,C#,GRADE)

C(C#,CNAME,TEACHER)

试用SQL语句完成下列大题1~4的任务：

用数据定义语言DDL定义上述三个表，请为每个属性定义适当的数据类型，宽度(和精度)，每个表上的主键、外键以及完整性约束。

1，把这个sql文件导入本地，桌面建一个XX.sql文件，用记事本打开，把这个复制进去。然后用语句导入到自己的本地数据库，这里用的是mysql，Navicat

/*
Navicat MySQL Data Transfer
Date: 2022-03-15 21:34:47
*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for `c`
-- ----------------------------
DROP TABLE IF EXISTS `c`;
CREATE TABLE `c` (
  `c` int(11) NOT NULL,
  `cname` varchar(255) DEFAULT NULL,
  `teacher` varchar(255) NOT NULL,
  PRIMARY KEY (`c`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of c
-- ----------------------------
INSERT INTO `c` VALUES ('1', 'java', 'LIU');
INSERT INTO `c` VALUES ('2', 'python', 'LI');
INSERT INTO `c` VALUES ('3', 'html', 'XU');

-- ----------------------------
-- Table structure for `s`
-- ----------------------------
DROP TABLE IF EXISTS `s`;
CREATE TABLE `s` (
  `s` int(11) NOT NULL AUTO_INCREMENT,
  `sname` varchar(255) NOT NULL,
  `sex` varchar(10) NOT NULL,
  `age` int(11) NOT NULL,
  PRIMARY KEY (`s`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of s
-- ----------------------------
INSERT INTO `s` VALUES ('1', 'WANG', 'male', '20');
INSERT INTO `s` VALUES ('2', 'ZHANGYU', 'male', '24');
INSERT INTO `s` VALUES ('3', 'ZHAO', 'female', '18');
INSERT INTO `s` VALUES ('4', 'YI', 'male', '25');
INSERT INTO `s` VALUES ('5', 'FANG', 'female', '21');
INSERT INTO `s` VALUES ('6', 'ZHANGFRI', 'female', '18');

-- ----------------------------
-- Table structure for `sc`
-- ----------------------------
DROP TABLE IF EXISTS `sc`;
CREATE TABLE `sc` (
  `s` int(11) NOT NULL,
  `c` int(11) DEFAULT NULL,
  `grade` float DEFAULT NULL,
  KEY `s` (`s`),
  KEY `c` (`c`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of sc
-- ----------------------------
INSERT INTO `sc` VALUES ('1', '1', '80');
INSERT INTO `sc` VALUES ('1', '2', '88');
INSERT INTO `sc` VALUES ('2', '1', '79');
INSERT INTO `sc` VALUES ('2', '2', '81');
INSERT INTO `sc` VALUES ('3', '3', '86');
INSERT INTO `sc` VALUES ('4', '2', '85');
INSERT INTO `sc` VALUES ('4', '3', '87');
INSERT INTO `sc` VALUES ('5', '1', '95');
INSERT INTO `sc` VALUES ('3', '1', '82');
INSERT INTO `sc` VALUES ('1', '3', '77');
INSERT INTO `sc` VALUES ('4', '1', '66');
INSERT INTO `sc` VALUES ('6', '1', '78');

在1题的基础是，用SELECT语句完成下列查询。

（1）检索LIU老师所授课程的课程号和课程名；

（2）检索年龄大于23的男同学的学号和姓名；

（3）检索学号为S3的学生所学课程的课程名与任课老师；

（4）检索至少选修LIU 老师所授课程中一门课程的女学生的姓名；

（5）检索WANG同学不学课程的课程号；

（6）检索至少选修2门课的学生的学号；

（7）检索全部学生都选修的课程的课程号和课程名；

（8）检索选修课程包含LIU老师所授全部课程的学生的学号；

（9）检索所有姓张的学生；

（10）查询每个学生的平均成绩；

（11）查询平均成绩大于等于80分的学生的学号和姓名。

（12）查询平均成绩第一名的学生的学号和姓名。

1、select c,cname from C where teacher='LIU';
	+---+-------+
	| c | cname |
	+---+-------+
	| 1 | java  |
	+---+-------+
2、 select s,sname,sex,age from S where sex='male' and age>23;
	+---+-------+------+-----+
	| s | sname | sex  | age |
	+---+-------+------+-----+
	| 2 | ZHANG | male |  24 |
	| 4 | YI    | male |  25 |
	+---+-------+------+-----+
3、 select cname,teacher from SC join C on SC.c=C.c and SC.s=3;
	+-------+---------+
	| cname | teacher |
	+-------+---------+
	| java  | LIU     |
	| html  | XU      |
	+-------+---------+
4、select distinct sname,sex,cname from S,SC,C where sex='female' and teacher='LIU';
	+-------+--------+-------+
	| sname | sex    | cname |
	+-------+--------+-------+
	| ZHAO  | female | java  |
	| FANG  | female | java  |
	+-------+--------+-------+
5、select c from C where not exists(select * from S,SC where S.s=SC.s and SC.c=C.c and sname='WANG');
	+---+
	| c |
	+---+
	| 3 |
	+---+
6、 select s from SC group by s having count(*)>1;
	+---+
	| s |
	+---+
	| 1 |
	| 2 |
	| 3 |
	| 4 |
	+---+
7、select c,cname from C where not exists(select * from S where not exists(select * from SC where s=S.s and c=C.c));
	+---+-------+
	| c | cname |
	+---+-------+
	| 1 | java  |
	+---+-------+
8、select  distinct s,c,grade from SC x where not exists(select * from C where teacher='LIU' and not exists(select * from SC y where y.s=x.s and x.c=c.c));
	+---+------+-------+
	| s | c    | grade |
	+---+------+-------+
	| 1 |    1 |    80 |
	| 2 |    1 |    79 |
	| 5 |    1 |    95 |
	| 3 |    1 |    82 |
	| 4 |    1 |    66 |
	| 6 |    1 |    78 |
+---+------+-------+
9、 select sname from S where sname like 'ZHANG%';
	+----------+
	| sname    |
	+----------+
	| ZHANGYU  |
	| ZHANGFRI |
	+----------+
10、select avg(grade),s from SC group by s;
	+-------------------+---+
	| avg(grade)        | s |
	+-------------------+---+
	| 81.66666666666667 | 1 |
	|                80 | 2 |
	|                84 | 3 |
	| 79.33333333333333 | 4 |
	|                95 | 5 |
	|                78 | 6 |
	+-------------------+---+
11、select S.s,sname,avg(grade) from S join SC on S.s=SC.s group by s having avg(grade)>=80;
	+---+---------+-------------------+
	| s | sname   | avg(grade)        |
	+---+---------+-------------------+
	| 1 | WANG    | 81.66666666666667 |
	| 2 | ZHANGYU |                80 |
	| 3 | ZHAO    |                84 |
	| 5 | FANG    |                95 |
	+---+---------+-------------------+
12、select avg(grade),SC.s,sname from SC,S where SC.s=S.s group by s limit 1;
	+-------------------+---+-------+
	| avg(grade)        | s | sname |
	+-------------------+---+-------+
	| 81.66666666666667 | 1 | WANG  |
	+-------------------+---+-------+

（1）请创建选修LIU老师所带课程的学生视图，视图包括：学号、姓名、性别、课程号、课程名、成绩。

（2）创建所有男生的视图。

（3）创建所有学生的选课视图，视图包括：学号、姓名、性别、课程号、课程名、成绩。

（4）创建学生平均成绩的视图，视图包括：学号、姓名和平均成绩。

1、create view LIU_student as select sf.Sno,sf.SNAME,sf.SEX,kc.Cno,kc.CNAME,cj.grade from s sf join sc cj on sf.Sno=cj.Sno join c kc on kc.Cno=cj.Cno where kc.teacher='LIU'

2、create view LIU_student as select sf.Sno,sf.SNAME,sf.SEX,kc.Cno,kc.CNAME,cj.grade from s sf join sc cj on sf.Sno=cj.Sno join c kc on kc.Cno=cj.Cno where sf.SEX='男'

3、create view LIU_student as select sf.Sno,sf.SNAME,sf.SEX,kc.Cno,kc.CNAME,cj.grade from s sf join sc cj on sf.Sno=cj.Sno join c kc on kc.Cno=cj.Cno 

4、create view avg_student as select sf.Sno,sf.SNAME,ROUND(avg(cj.grade)) from s sf LEFT join sc cj on sf.Sno=cj.Sno GROUP BY sf.Sno

整理的自己的作业题，有不合适的语句欢迎同学们一起交流一下。一起进步。