数据库SQL:一张学生成绩表score,部分内容如下:name course grade 张三操作系统67 张三数据结构86 李四软件工程89 用一条SQL语句查询出每]课都大于80分的学生姓名。
时间: 2024-06-12 13:06:51 浏览: 10
SELECT name FROM score WHERE course IN (SELECT DISTINCT course FROM score WHERE grade > 80) GROUP BY name HAVING COUNT(*) = (SELECT COUNT(DISTINCT course) FROM score WHERE grade > 80)
相关问题
用SQL写学生成绩管理系统
好的,以下是一个简单的学生成绩管理系统的SQL设计示例:
首先,我们需要创建两个表:学生信息表和成绩表。
1. 学生信息表:
```sql
CREATE TABLE student_info (
id INT PRIMARY KEY AUTO_INCREMENT, -- 学生ID,自增长
name VARCHAR(50) NOT NULL, -- 学生姓名
gender VARCHAR(10) NOT NULL, -- 学生性别
class_id INT NOT NULL -- 班级ID,外键关联到班级信息表
);
```
2. 成绩表:
```sql
CREATE TABLE grade (
id INT PRIMARY KEY AUTO_INCREMENT, -- 成绩ID,自增长
student_id INT NOT NULL, -- 学生ID,外键关联到学生信息表
course_name VARCHAR(50) NOT NULL, -- 课程名称
score INT NOT NULL -- 成绩
);
```
接下来,我们需要向这两个表中插入一些数据,以便于后续的查询和操作。
1. 向学生信息表中插入数据:
```sql
INSERT INTO student_info (name, gender, class_id) VALUES
('张三', '男', 1),
('李四', '女', 1),
('王五', '男', 2),
('赵六', '女', 2);
```
2. 向成绩表中插入数据:
```sql
INSERT INTO grade (student_id, course_name, score) VALUES
(1, '语文', 90),
(1, '数学', 80),
(1, '英语', 70),
(2, '语文', 85),
(2, '数学', 95),
(2, '英语', 75),
(3, '语文', 80),
(3, '数学', 90),
(3, '英语', 85),
(4, '语文', 95),
(4, '数学', 80),
(4, '英语', 90);
```
接下来,我们可以进行一些基本的查询和操作。
1. 查询某个学生的所有成绩:
```sql
SELECT course_name, score FROM grade WHERE student_id = 1;
```
2. 查询某门课程的平均分、最高分、最低分:
```sql
SELECT AVG(score) AS avg_score, MAX(score) AS max_score, MIN(score) AS min_score FROM grade WHERE course_name = '语文';
```
3. 修改某个学生的成绩:
```sql
UPDATE grade SET score = 95 WHERE student_id = 1 AND course_name = '数学';
```
4. 删除某个学生的成绩:
```sql
DELETE FROM grade WHERE student_id = 1;
```
以上是一个简单的学生成绩管理系统的SQL设计示例,您可以根据实际需求进行修改和完善。如果您需要更加详细和专业的设计方案,建议您咨询相关的数据库工程师或团队。
在一个成绩管理系统数据库GradeDB中,定义如下关系表: STUDENT(SID,SName,Age,Sex) GRADE(SID,CID,Score, Note) COURSE(CID,CName,Teacher) (1)编写SQL程序创建数据库及其关系表,并插入20个学生的2门课程(“数据库原理及应用”、“数据结构与算法”)成绩数据; (2)GradeLOG表的信息包括user操作用户、SID学号、CID课程号、changetime修改时间、oldscore修改前成绩、newscore修改后成绩。 创建GradeLOG表。 编写触发器程序实现GRADE表数据的插入、删除、修改在日志表(GradeLOG)记录相关操作数据,如操作用户、学号、课程号、修改时间、修改前成绩、修改后成绩。 (3)编写存储过程程序实现统计各课程不及格学生人数,并在屏幕输出;
(1)创建数据库及其关系表,并插入数据:
```sql
CREATE DATABASE GradeDB;
USE GradeDB;
CREATE TABLE STUDENT (
SID CHAR(10) PRIMARY KEY,
SName VARCHAR(20) NOT NULL,
Age INT NOT NULL,
Sex CHAR(2) NOT NULL
);
CREATE TABLE COURSE (
CID CHAR(10) PRIMARY KEY,
CName VARCHAR(20) NOT NULL,
Teacher VARCHAR(20) NOT NULL
);
CREATE TABLE GRADE (
SID CHAR(10) REFERENCES STUDENT(SID),
CID CHAR(10) REFERENCES COURSE(CID),
Score INT NOT NULL,
Note VARCHAR(50),
PRIMARY KEY (SID, CID)
);
INSERT INTO STUDENT VALUES ('200001', '张三', 19, '男');
INSERT INTO STUDENT VALUES ('200002', '李四', 20, '女');
INSERT INTO STUDENT VALUES ('200003', '王五', 18, '男');
INSERT INTO STUDENT VALUES ('200004', '赵六', 21, '女');
INSERT INTO STUDENT VALUES ('200005', '刘七', 20, '男');
INSERT INTO STUDENT VALUES ('200006', '陈八', 19, '女');
INSERT INTO STUDENT VALUES ('200007', '张九', 18, '男');
INSERT INTO STUDENT VALUES ('200008', '李十', 21, '女');
INSERT INTO STUDENT VALUES ('200009', '王麻子', 20, '男');
INSERT INTO STUDENT VALUES ('200010', '赵大', 19, '女');
INSERT INTO STUDENT VALUES ('200011', '刘二', 18, '男');
INSERT INTO STUDENT VALUES ('200012', '陈三', 21, '女');
INSERT INTO STUDENT VALUES ('200013', '张四', 20, '男');
INSERT INTO STUDENT VALUES ('200014', '李五', 19, '女');
INSERT INTO STUDENT VALUES ('200015', '王六', 18, '男');
INSERT INTO STUDENT VALUES ('200016', '赵七', 21, '女');
INSERT INTO STUDENT VALUES ('200017', '刘八', 20, '男');
INSERT INTO STUDENT VALUES ('200018', '陈九', 19, '女');
INSERT INTO STUDENT VALUES ('200019', '张十', 18, '男');
INSERT INTO STUDENT VALUES ('200020', '李一', 21, '女');
INSERT INTO COURSE VALUES ('C001', '数据库原理及应用', '张三');
INSERT INTO COURSE VALUES ('C002', '数据结构与算法', '李四');
INSERT INTO GRADE VALUES ('200001', 'C001', 80, '优秀');
INSERT INTO GRADE VALUES ('200001', 'C002', 75, '良好');
INSERT INTO GRADE VALUES ('200002', 'C001', 65, '及格');
INSERT INTO GRADE VALUES ('200002', 'C002', 70, '良好');
INSERT INTO GRADE VALUES ('200003', 'C001', 55, '不及格');
INSERT INTO GRADE VALUES ('200003', 'C002', 60, '及格');
INSERT INTO GRADE VALUES ('200004', 'C001', 90, '优秀');
INSERT INTO GRADE VALUES ('200004', 'C002', 85, '优秀');
INSERT INTO GRADE VALUES ('200005', 'C001', 70, '良好');
INSERT INTO GRADE VALUES ('200005', 'C002', 75, '良好');
INSERT INTO GRADE VALUES ('200006', 'C001', 80, '优秀');
INSERT INTO GRADE VALUES ('200006', 'C002', 85, '优秀');
INSERT INTO GRADE VALUES ('200007', 'C001', 60, '及格');
INSERT INTO GRADE VALUES ('200007', 'C002', 65, '及格');
INSERT INTO GRADE VALUES ('200008', 'C001', 75, '良好');
INSERT INTO GRADE VALUES ('200008', 'C002', 80, '优秀');
INSERT INTO GRADE VALUES ('200009', 'C001', 55, '不及格');
INSERT INTO GRADE VALUES ('200009', 'C002', 60, '及格');
INSERT INTO GRADE VALUES ('200010', 'C001', 70, '良好');
INSERT INTO GRADE VALUES ('200010', 'C002', 75, '良好');
```
(2)创建GradeLOG表,并编写触发器程序实现GRADE表数据的插入、删除、修改在日志表记录相关操作数据:
```sql
CREATE TABLE GradeLOG (
user VARCHAR(20),
SID CHAR(10),
CID CHAR(10),
changetime DATETIME,
oldscore INT,
newscore INT
);
DELIMITER $$
CREATE TRIGGER log_insert
AFTER INSERT ON GRADE
FOR EACH ROW
BEGIN
INSERT INTO GradeLOG VALUES (USER(), NEW.SID, NEW.CID, NOW(), NULL, NEW.Score);
END$$
CREATE TRIGGER log_delete
AFTER DELETE ON GRADE
FOR EACH ROW
BEGIN
INSERT INTO GradeLOG VALUES (USER(), OLD.SID, OLD.CID, NOW(), OLD.Score, NULL);
END$$
CREATE TRIGGER log_update
AFTER UPDATE ON GRADE
FOR EACH ROW
BEGIN
INSERT INTO GradeLOG VALUES (USER(), NEW.SID, NEW.CID, NOW(), OLD.Score, NEW.Score);
END$$
DELIMITER ;
-- 测试触发器
DELETE FROM GRADE WHERE SID = '200001' AND CID = 'C001';
UPDATE GRADE SET Score = 90 WHERE SID = '200002' AND CID = 'C002';
```
(3)编写存储过程程序实现统计各课程不及格学生人数,并在屏幕输出:
```sql
DELIMITER $$
CREATE PROCEDURE count_failed_students()
BEGIN
DECLARE c_name VARCHAR(20);
DECLARE c_id CHAR(10);
DECLARE n INT;
DECLARE done INT DEFAULT FALSE;
DECLARE cur1 CURSOR FOR SELECT CName, CID FROM COURSE;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
OPEN cur1;
read_loop: LOOP
FETCH cur1 INTO c_name, c_id;
IF done THEN
LEAVE read_loop;
END IF;
SELECT COUNT(*) INTO n FROM GRADE WHERE CID = c_id AND Score < 60;
SELECT CONCAT(c_name, '不及格人数为', n) AS Result;
END LOOP;
CLOSE cur1;
END$$
DELIMITER ;
-- 执行存储过程
CALL count_failed_students();
```
以上代码是一个完整的MySQL数据库程序,可以创建数据库、关系表、插入数据、创建触发器和存储过程,实现对学生成绩的管理。