MySQL练习题

题目背景：

建表语句

提示：这里需要自己先创建数据库或者选择已有数据库

下面是建表和插入语句

CREATE TABLEstudent (

stu_idINT(10) PRIMARY KEY,

stu_nameVARCHAR(20)NOT NULL,

sexVARCHAR(2),

birthYEAR,

departmentVARCHAR(20),

addrVARCHAR(50)

);

INSERT INTO student VALUES(901,'张飞', '男',1985,'计算机系', '河北省涿州市'),

(902,'关羽', '男',1986,'中文系', '山西省运城市'),

(903,'貂蝉', '女',1990,'中文系', '山西省忻州县'),

(904,'刘备', '男',1990,'英语系', '河北省涿州市'),

(905,'小乔', '女',1991,'英语系', '安徽省潜山市'),

(906,'赵云', '男',1988,'计算机系', '河北省正定市');

CREATE TABLEscore (

score_idINT(10) PRIMARY KEYAUTO_INCREMENT ,

stu_idINT(10) NOT NULL,

c_nameVARCHAR(20) ,

gradeINT(10)

);

INSERT INTO score(stu_id,c_name,grade) VALUES(901, '计算机',98),

(901, '英语', 80),(902, '计算机',65),(902, '中文',88),

(903, '中文',95),(904, '计算机',70),(904, '英语',92),

(905, '英语',94),(906, '计算机',90),(906, '英语',85);

题目及答案：

1.从student表中查询计算机系和英语系学生的信息。

select * from student where department in(‘IT’,‘english’);

2.从student表中查询年龄25~33岁的学生信息。

select * from student where (year(curdate())-year(birth))between 25 and 33;

3.在student表中统计每个院系各有几个学生。

select department,count(stud_id) as count,group_concat(stu_name) as members from student group by department;

4.查询每个院系学生中的最高分。

select max(big.grade),big.department from ( select * from student inner join score where student.stud_id=score.stu_id ) as big group by big.department;
select max(grade),department from student inner join score on student.stud_id=score.stu_id group by student.department;

5.查询学生貂蝉的考试科目(c_name)和考试成绩(grade)

select c_name,grade from score,student where student.stud_id=score.stu_id and student.stu_name=‘diaochan’;

6.查询计算机成绩低于95的学生信息。

select student.stud_id,student.stu_name from student,score where student.stud_id=score.stu_id and score.c_name=‘IT’ and score.grade<95;

7.查询同时参加计算机和英语考试的学生的信息。

select student.stud_id,student.stu_name from student,score where score.stu_id in(select stu_id from score where c_name=‘IT’) and score.c_name=‘english’;

8.从student表和score表中分别查询出学生的学号，然后合并查询结果。

select student.stud_id,score.stu_id from student,score where student.stud_id=score.stu_id;
select student.stud_id,score.stu_id from student inner join score on student.stud_id=score.stu_id ;

9.查询姓张或者姓王的同学的个人信息、院系和考试科目及成绩。

select * from student inner join score where student.stud_id=score.stu_id and student.stu_name like ‘张%’ or student.stu_name like ‘王%’;

10.查询山西省的学生的姓名、出生年份、院系、考试科目及成绩。

select student.stu_name,student.birth,student.department,score.c_name,score.grade from student inner join score where student.stud_id=score.stu_id and student.addr like ‘山西%’;

---

题目详解：

参考其他博客：https://blog.csdn.net/weixin_39793564/article/details/114345904