MySQL查询二

最新推荐文章于 2023-10-23 19:18:17 发布

lonnie long

最新推荐文章于 2023-10-23 19:18:17 发布

阅读量1.2k

点赞数 7

文章标签： mysql 数据库 database

本文链接：https://blog.csdn.net/qq_53048695/article/details/121298579

版权

学生表

科目表

成绩表

数据修改与删除

将地址是西安市的更新为西安科技二路
将S1001的email修改为空的字符串
将第二门课的名字更新为 java基础，课时为60 ，班级是二年级
将S1001 ，课程编号为 2 的成绩提高 5分
将 S1004 ,课程编号为3 的成绩更新为60，考试时间更新为 2015-10-10
经核实 S1004 课程编号为2 的缺考
将计算机网络课程删除掉

-- 学生表（student）
CREATE TABLE student(
studentNo VARCHAR(10) PRIMARY KEY ,
studentName VARCHAR(10) NOT NULL,
loginPassword VARCHAR(50) NOT NULL,
sex CHAR(3) DEFAULT '男',
phone VARCHAR(50) UNIQUE,
address VARCHAR(50) NOT NULL,
born date,
email VARCHAR(50),
gradeId CHAR(3) 
)
SELECT * FROM student;
-- 科目表(subject)
CREATE TABLE subjects(
subjectId INT PRIMARY KEY auto_increment,
subjectName VARCHAR(50) NOT NULL,
classHour CHAR(3) NOT NULL,
gradeId CHAR(3)
);
-- 成绩表（score）
CREATE TABLE score(
id INT PRIMARY KEY auto_increment,
studentNo VARCHAR(10),
subjectId INT,
studentScore CHAR(3) NOT NULL,
examDate date
);
DESC score;

SHOW CREATE TABLE score;
-- 添加外键
ALTER TABLE score ADD CONSTRAINT fk_score_student FOREIGN KEY (studentNo) REFERENCES student(studentNo);
ALTER TABLE score ADD CONSTRAINT fk_score_subjects FOREIGN KEY (subjectId) REFERENCES subjects(subjectId);
-- student中添加数据
INSERT INTO student VALUES('s1001','张三','zhangsan',DEFAULT,'13022225555','宿舍','1994-01-01','(NULL)',1);
INSERT INTO student VALUES('s1002','李四','lisi',DEFAULT,'13266669999','宿舍','1991-07-07','(NULL)',1);
INSERT INTO student VALUES('s1003','张丽','123456','女','13099999999','宿舍','1992-05-06','(NULL)',1);
INSERT INTO student VALUES('s1004','王磊','123456',DEFAULT,'15066668888','西安','1991-06-07','(NULL)',1);
INSERT INTO student VALUES('s1005','张丹','zhangdan','女','15036998888','宿舍','1992-06-07','(NULL)',1);
INSERT INTO student VALUES('s1006','李亮','123456',DEFAULT,'15022226669','西安市雁塔区','1993-12-01','liliang@126.com',1);
INSERT INTO student VALUES('s1007','李丹','123456','女','1503699965','宿舍','1993-11-11','20161201141947@126com',1);
INSERT INTO student VALUES('s1008','王亮','123456',DEFAULT,'15022223333','西安市','1993-12-02','(NULL)',2);
INSERT INTO student VALUES('s1009','赵龙','123456',DEFAULT,'13022229999','西安市','1992-06-07','(NULL)',2);
INSERT INTO student VALUES('s1010','徐丹','123456','女','15899996666','宿舍','1993-05-06','(NULL)',2);
-- subjects 添加数据
INSERT INTO subjects VALUES(1,'MySQL深入',65, 1);
INSERT INTO subjects VALUES(2,'C语言面向过程',45, 1);
INSERT INTO subjects VALUES(3,'计算机基本原理',70, 1);
INSERT INTO subjects VALUES(4,'毛邓概论',61, 1);
INSERT INTO subjects VALUES(5,'英语',55, 1);
INSERT INTO subjects VALUES(6,'jsp',40, 2);
INSERT INTO subjects VALUES(7,'数据结构',60, 2);
INSERT INTO subjects VALUES(8,'oracel',65, 2);
INSERT INTO subjects VALUES(9,'计算机网络',50, 2);
-- score 添加数据
INSERT INTO score VALUES(1,'s1001',1,80,'2015-07-01');
INSERT INTO score VALUES(2,'s1002',1,40,'2015-07-01');
INSERT INTO score VALUES(3,'s1001',2,10,'2015-07-01');
INSERT INTO score VALUES(4,'s1002',2,20,'2015-07-01');
INSERT INTO score VALUES(5,'s1003',1,60,'2015-07-01');
INSERT INTO score VALUES(6,'s1001',3,82,'2015-07-03');
INSERT INTO score VALUES(7,'s1001',4,90,'2015-07-03');
INSERT INTO score VALUES(8,'s1001',5,75,'2015-07-01');
INSERT INTO score VALUES(9,'s1002',3,65,'2015-07-03');
INSERT INTO score VALUES(10,'s1002',4,35,'2015-07-03');
INSERT INTO score VALUES(11,'s1002',5,87,'2015-07-01');
INSERT INTO score VALUES(12,'s1003',2,65,'2015-07-01');
INSERT INTO score VALUES(13,'s1003',3,45,'2015-07-03');
INSERT INTO score VALUES(14,'s1003',4,92,'2015-07-03');
INSERT INTO score VALUES(15,'s1003',5,55,'2015-07-01');
INSERT INTO score VALUES(16,'s1004',4,65,'2015-07-01');
INSERT INTO score VALUES(17,'s1004',2,85,'2015-07-01');
INSERT INTO score VALUES(18,'s1004',3,45,'2015-07-03');
SELECT * FROM score;
SELECT * from student;
SELECT * from subjects;
-- 数据修改与删除
-- 1.	将地址是西安市的更新为 西安科技二路
UPDATE student set address = '西安科技二路' WHERE address = '西安市';
-- 2.	将S1001的email修改为空的字符串
UPDATE student SET email = '空的字符串' WHERE studentNo = 's1001';
-- 3.	将第二门课的名字更新为 java基础，课时为60 ，班级是二年级
UPDATE subjects SET subjectName = 'java基础',classHour = 65, gradeId = 2 WHERE subjectId = 2;
-- 4.	将S1001 ，课程编号为 2 的成绩 提高 5分
UPDATE score SET studentScore = studentScore + 5 WHERE studentNo = 's1001' AND subjectId = 2;
-- 5.	将 S1004 ,课程编号为3 的成绩更新为60，考试时间更新为 2015-10-10
UPDATE score SET studentScore = 60, examDate = '2015-10-10' WHERE studentNo = 's1004' AND subjectId = 3;
-- 6.	经核实 S1004 课程编号为2 的 缺考
UPDATE score SET studentScore = '缺考' WHERE studentNo = 's1004' AND subjectId = 2;
-- 7.	将计算机网络课程删除掉
-- 删除指定行的指定数据
-- update 表名 set 字段=replace(字段,'要删除的串','') where 条件
UPDATE subjects SET subjectName = replace(subjectName,'计算机网络','');

数据查询：

1、查询全部一年级的学生信息。

2、查询全部二年级的学生的姓名和电话。

3、查询全部一年级女同学的信息。

4、查询课时超过60的科目信息。

5、查询二年级的科目名称

6、查询二年级男同学的姓名和住址。

7、查询无电子邮件的学生姓名和年级信息。

8、查询出生日期在1992年之后的男学生姓名和年级信息。

9、参加了日期为2015年7月3日的“计算机基本原理”科目考试的成绩信息

10、按照出生日期查询一年级的学生信息。

11、按成绩由高到低的次序查询参加编号为1的科目考试信息。

12、查询2015年7月1日参加“MySQL深入”考试的前2名学员成绩信息。

13、查询课时最多的科目名称及课时。

14、查询年龄最小的学生所在的年级及姓名。

15、查询考试的最低分出现在哪个科目

16、查询学号为“s1001”的学生参加过的所有考试信息，并按照时间前后次序显示。

17、查询年龄超过25周岁的学生信息。

18、查询1月份过生日的学生信息

19、查询今天过生日的学生姓名及所在年级。

20、新生入学，为其分配一个Email地址，规则如下：S1+当前日期 +@bd.com

21、查询住址为“雁塔区”的学生姓名、电话、住址

22、查询名称中含有“计算机”字样科目名称、学时及所属年级，并按年级由低到高显示。

23、查询电话中含有以“130”开头的学生姓名，住址和电话。

24、查询姓“赵”的学号、姓名和住址。

25、统计一年级女生的总人数。

26、查询李四总成绩

27、学号为s1003的学生所有课总成绩

28、学号为s1003的学生考试的平均分。

29、查询一年级的科目“Mysql”的最高分、最低分、平均分。

30、查询每个年级的总学时数，并按照升序排列。

31、查询每个参加考试的学员的平均分。（Group by 学号）

32、查询每门课程的平均分，并按照降序排列。（group by 课程）

33、查询每个学生参加的所有考试的总分，并按照降序排列。（group by 学号）

34、查询一年级的平均年龄。

35、查询每个年级西安地区的学生人数。

36、查询参加考试的学生中，平均分及格的学生记录，并按照平均成绩降序排列

37、查询参加考试至少一次考试不及格的学生学号、姓名、不及格次数。

38、查询学生姓名、所属年级名称及联系电话。

39、查询年级编号为1的科目名称、年级名称及学时。

40、查询参加科目编号为1的考试的学生姓名、分数、考试日期。

41、查询学号为s1001的学生参加的考试科目名称、分数、考试日期。

42、查询所有科目的参考信息（某些科目可能还没有被考试过）

43、查询没有被考过的科目信息。

-- 数据查询
-- 1、	查询全部一年级的学生信息。
SELECT * FROM student WHERE gradeId = 1;
-- 2、	查询全部二年级的学生的姓名和电话。
SELECT studentName,phone FROM student WHERE gradeId = 2;
-- 3、	查询全部一年级女同学的信息。
SELECT * FROM student WHERE gradeId = 1 AND sex = '女';
-- 4、	查询课时超过60的科目信息。
SELECT * FROM subjects WHERE classHour >= 60;
-- 5、	查询二年级的科目名称
SELECT subjectName FROM subjects WHERE gradeId = 2;
-- 6、	查询二年级男同学的姓名和住址。
SELECT studentName,address FROM student WHERE gradeId = 2 AND sex = '男';
-- 7、	查询无电子邮件的学生姓名和年级信息。
SELECT studentName,gradeId FROM student WHERE email = '(NULL)';
-- 8、	查询出生日期在1992年之后的男学生姓名和年级信息  ?
SELECT studentName,gradeId FROM student WHERE born >'1992-1-1'
-- 9、	参加了日期为2015年7月3日的“计算机基本原理”科目考试的成绩信息  ?
SELECT subjectId,studentScore FROM score  WHERE examDate = '2015-7-3' AND (SELECT subjectName  from subjects WHERE subjectName = '计算机组成原理')
-- 10、	按照出生日期查询一年级的学生信息
SELECT * FROM student WHERE born != '' AND gradeId = 1;
-- 11、	按成绩由高到低的次序查询参加编号为1的科目考试信息
SELECT studentScore FROM score where subjectId = (SELECT subjectName from subjects where subjectId = 1 ) ORDER BY studentScore desc;
SELECT subjectName from subjects where subjectId = 1 
-- 12、   查询2015年7月1日参加“MySQL深入”考试的前2名学员成绩信息。
select * from score where examDate = '2015-07-01' and subjectId = (SELECT subjectId from subjects where subjectName = 'MySQL深入') order by studentScore desc limit 2;
-- 13、   查询课时最多的科目名称及课时。
select subjectName,classHour from subjects order by classHour desc limit 1
-- 14、   查询年龄最小的学生所在的年级及姓名。
select gradeId,studentName from student order by born desc limit 1
-- 15、   查询考试的最低分出现在哪个科目
SELECT subjectName FROM subjects where subjectId = (SELECT  subjectId FROM score ORDER BY studentScore limit 1)
-- 16、   查询学号为“s1001”的学生参加过的所有考试信息，并按照时间前后次序显示。
select * from score where studentNo = 's1001' order by examDate
-- 17、   查询年龄超过25周岁的学生信息。
select * from Student where (year(curdate()) - substring(born from 1 for 4)) > 25
-- 18、   查询1月份过生日的学生信息
select * from Student where (substring(born from 6 for 6)) = 1;
-- 19、   查询今天过生日的学生姓名及所在年级。
select studentName,gradeId from Student where (substring(born from 6 for 7)) = month(curtime()) and (substring(born from 9 for 10)) = day(curtime());
-- 20、   新生入学，为其分配一个Email地址，规则如下：S1+当前日期 [+@bd.com](mailto:+@bd.com)

-- 21、   查询住址为“雁塔区”的学生姓名、电话、住址
select studentName,phone,address from Student where address like '%雁塔区%';
-- 22、   查询名称中含有“计算机”字样科目名称、学时及所属年级，并按年级由低到高显示。
select * from subjects where subjectName like '计算机%';
-- 23、   查询电话中含有以“130”开头的学生姓名，住址和电话。
select studentName,address,phone from Student where phone like '130%';
-- 24、   查询姓“赵”的学号、姓名和住址。
select studentNo,studentName,address from Student where studentName like '赵%'
-- 25、   统计一年级女生的总人数。
select count(sex) from Student where sex = '女';
-- 26、   查询李四总成绩
select sum(studentScore) from score  where studentNo = (select studentNo from Student where studentName = '李四');
-- 27、   学号为s1003的学生所有课总成绩
select sum(studentScore) from score where studentNo = 's1003';
-- 28、   学号为s1003的学生考试的平均分。
select avg(studentScore) from score where studentNo = 's1003';
-- 29、   查询一年级的科目“Mysql”的最高分、最低分、平均分。
select max(studentScore),min(studentScore),avg(studentScore) from score where subjectId = (select subjectId from subjects ;#升序排列。
select gradeid,sum(classhour) from subjects group by gradeId order by sum(classhour)
-- 31、   查询每个参加考试的学员的平均分。（Group by 学号）
select studentNo,avg(studentScore) from score group by studentNo;
-- 32、   查询每门课程的平均分，并按照降序排列。（group by 课程）
select subjectId,avg(studentScore) from score group by subjectId order by avg(studentScore) desc;
-- 33、   查询每个学生参加的所有考试的总分，并按照降序排列。（group by 学号）
select studentNo,sum(studentScore) from score group by studentNo order by avg(studentScore) desc;
-- 34、   查询一年级的平均年龄。
select gradeId,avg((year(curdate())-substring(born from 1 for 4))) from student;
-- 35、   查询每个年级西安地区的学生人数。
select gradeId,count(1) from student where address like '%西安%' group by gradeId;
-- 36、   查询参加考试的学生中，平均分及格的学生记录，并按照平均成绩降序排列
select studentNo,avg(studentScore) from score group by studentNo having avg(studentScore)>=60 order by avg(studentScore);
-- 37、   查询参加考试至少一次考试不及格的学生学号、姓名、不及格次数。
select studentNo,(select s.studentName from Student s where s.studentNo=r.studentNo )studentName ,count(studentScore) from score r where studentScore<60 group by studentNo having count(studentScore)>=1;
-- 38、   查询学生姓名、所属年级名称及联系电话。
select studentName,gradeId,phone from Student
-- 39、   查询年级编号为1的科目名称、年级名称及学时。
select subjectName,gradeId,classhour from subjects  where gradeId=1;
-- 40、   查询参加科目编号为1的考试的学生姓名、分数、考试日期。
select  ( select s.studentname from Student s where s.studentno=r.studentno ), r.studentScore,r.examdate from score r where subjectid=1;
-- 41、   查询学号为s1001的学生参加的考试科目名称、分数、考试日期。
select subjectname,studentScore,examDate from score,student,subjects WHERE score.subjectId=`subjectForm`.subjectId and score.studentNo=student.studentNo and student.StudentNo='s1001'
-- 42、   查询所有科目的参考信息（某些科目可能还没有被考试过）
select j.subjectName as "科目名称", r.studentNo as"学号", r.studentScore as "分数" from subjects as j left outer join score as r on j.subjectid=r.subjectid;
-- 43、   查询没有被考过的科目信息。
select j.subjectName as "科目名称" from score as r right outer join subjects as j on j.subjectId=r.subjectId where r.studentScore is null and r.studentNo is null;

lonnie long

关注

7
点赞
踩
14

收藏

觉得还不错? 一键收藏
1
评论
MySQL查询二

学生表科目表成绩表数据修改与删除将地址是西安市的更新为西安科技二路将S1001的email修改为空的字符串将第二门课的名字更新为 java基础，课时为60 ，班级是二年级将S1001 ，课程编号为 2 的成绩提高 5分将 S1004 ,课程编号为3 的成绩更新为60，考试时间更新为 2015-10-10经核实 S1004 课程编号为2 的缺考将计算机网络课程删除掉-- 学生表（student）CREATE TABLE student(st
复制链接

扫一扫