MySQL查询练习2

简单查询练习：

1.创建student和score表
2.为student表和score表增加记录
3.查询student表的所有记录
4.查询student表的第2条到4条记录
5.从student表查询所有学生的学号（id）、姓名（name）和院系（department）的信息
6.从student表中查询计算机系和英语系的学生的信息
7.从student表中查询年龄18~35岁的学生信息
8.查询每个院系有多少人
9.查询每个科目的最高分
10.查询李四的考试科目（c_name）和考试成绩（grade）
11.所有学生的信息和考试信息
12.计算每个学生的总成绩
13.计算每个考试科目的平均成绩
14.查询计算机成绩低于95的学生信息
15.查询同时参加计算机和英语考试的学生的信息
16.将计算机考试成绩按从高到低进行排序
17.查询姓张或者姓王的同学的姓名、院系和考试科目及成绩
18.查询都是湖南的学生的姓名、年龄、院系和考试科目及成绩
19.查询出每门课程都大于80分的学生姓名,类似于这个题目：查询所有成绩都及格的学生
20.查询平均分最高的科目名字
21.查询语文成绩比张三高的学生的信息
22.查询同时参加计算机和英语考试的学生的信息

1.创建student和score表

DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` 
(
`id` int(10) NOT NULL AUTO_INCREMENT,
`name` char(20) NOT NULL,
`sex` char(20) DEFAULT NULL,
`birth` year(4) DEFAULT NULL,
`department` char(10) DEFAULT NULL,
`address` char(10) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `score`;
CREATE TABLE `score` 
(
`id` int(10) NOT NULL AUTO_INCREMENT,
`stu_id` int(10) NOT NULL,
`c_name` char(20) DEFAULT NULL,
`grade` int(10) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `score_ibfk_1` (`stu_id`),
CONSTRAINT `score_ibfk_1` FOREIGN KEY (`stu_id`) REFERENCES `student` (`id`)
ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;

 2、为student表和score表增加记录

insert into `student`(`id`,`name`,`sex`,`birth`,`department`,`address`) values (1,'张老大','男',1985,'计算机系','北京市海淀区');
insert into `student`(`id`,`name`,`sex`,`birth`,`department`,`address`) values (2,'张老二','男',1986,'中文系','北京市昌平区');
insert into `student`(`id`,`name`,`sex`,`birth`,`department`,`address`) values (3,'张三','女',1990,'中文系','湖南省永州市');
insert into `student`(`id`,`name`,`sex`,`birth`,`department`,`address`) values (4,'李四','男',1990,'英语系','辽宁省阜新市');
insert into `student`(`id`,`name`,`sex`,`birth`,`department`,`address`) values (5,'王五','女',1991,'英语系','福建省厦门市');
insert into `student`(`id`,`name`,`sex`,`birth`,`department`,`address`) values (6,'王六','男',1988,'计算机系','湖南省衡阳市');

insert into `score`(`id`,`stu_id`,`c_name`,`grade`) values (1,1,'计算机',98);
insert into `score`(`id`,`stu_id`,`c_name`,`grade`) values (2,1,'英语',80);
insert into `score`(`id`,`stu_id`,`c_name`,`grade`) values (3,2,'计算机',65);
insert into `score`(`id`,`stu_id`,`c_name`,`grade`) values (4,2,'中文',88);
insert into `score`(`id`,`stu_id`,`c_name`,`grade`) values (5,3,'中文',95);
insert into `score`(`id`,`stu_id`,`c_name`,`grade`) values (6,3,'计算机',70);
insert into `score`(`id`,`stu_id`,`c_name`,`grade`) values (7,4,'计算机',70);
insert into `score`(`id`,`stu_id`,`c_name`,`grade`) values (8,4,'英语',92);
insert into `score`(`id`,`stu_id`,`c_name`,`grade`) values (9,5,'英语',94);
insert into `score`(`id`,`stu_id`,`c_name`,`grade`) values (10,5,'计算机',90);
insert into `score`(`id`,`stu_id`,`c_name`,`grade`) values (11,6,'计算机',90);
insert into `score`(`id`,`stu_id`,`c_name`,`grade`) values (12,6,'英语',85);

3.查询student表的所有记录：

select * 
from student;

查询结果

4.查询student表的第2条到4条记录

 select * 
from student limit 1,3;

查询结果

 5.从student表查询所有学生的学号（id）、姓名（name）和院系（department）的信息

select id,name,department 
from student;

查询结果

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

select * 
from student 
where department IN('计算机系','英语系');

查询结果

7.从student表中查询年龄在18~35岁的学生信息

select * 
from student 
where year(now())-birth>18 and year(now())-birth<35;

查询结果

8.查询每个院系有多少人

select department,count(department) 
from student 
group by department;

查询结果

9.查询每个科目的最高分

select c_name,max(grade) 
from score 
group by c_name;

查询结果

10.查询李四的考试科目（c_name）和考试成绩（grade）

select c_name,grade 
from score 
where stu_id=(select id from student where name='李四');

查询结果

11.所有学生的信息和考试信息

select * 
from student left outer join score on student.id=score.stu_id;

查询结果

12.计算每个学生的总成绩

select name,sum(grade) 
from student,score 
where student.id=score.stu_id group by name;

查询结果

13.计算每个考试科目的平均成绩 ，计算每个学生的平均成绩

select c_name,avg(grade) 
from score group by c_name 
union 
select s.name,avg(grade) from 
student as s inner join score as c on s.id=c.stu_id group by s.name;

查询结果

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

select * 
from student 
where id in(select stu_id from score where c_name='计算机'
 and grade<95);

查询结果

15.查询每个学科的平均成绩

select c_name,avg(grade) 
from score 
group by c_name;

查询结果

16.将计算机考试成绩降序排列

select grade 
from score 
where c_name='计算机' 
order by grade desc;

查询结果

17.查询姓张或者姓王的同学的姓名、院系和考试科目及成绩

select s.name,s.department,c.c_name,c.grade 
from student as s,score as c 
where s.id=c.stu_id and s.name regexp '^[张王]';

查询结果

18.查询都是湖南的学生的姓名、年龄、院系和考试科目及成绩

select s.name,year(now())-s.birth,s.department,c.c_name,c.grade
from student as s,score as c 
where s.id=c.stu_id and s.address regexp '湖南省';

查询结果

19.查询出每门课程都大于80分的学生姓名,类似于这个题目：查询所有成绩都及格的学生

select name 
from student inner join score on student.id = score.stu_id 
group by student.name 
having min(score.grade) > 80;

查询结果

20.查询平均分最高的科目名字

select c_name,avg(grade) 
from score 
group by c_name 
order by avg(grade) desc 
limit 1;

查询结果

21.查询语文成绩比张三高的学生的信息

提示: 先查询张三的语文成绩； 再查询语文成绩成绩大于a的学生信息；

select * 
from student as s inner join score as c on c.c_name='中文' and s.id=c.stu_id and c.grade > 
(
select c.grade 
from student as s inner join score as c on s.name='张三' 
and c.c_name='中文' and s.id=c.stu_id
);

查询结果

注意：表里没有学生语文成绩大于张三的语文成绩，所以查询结果为空。

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

select * 
from student as s 
inner join score as c1 on s.id = c1.stu_id and c1.c_name = '计算机' 
inner join score as c2 on s.id = c2.stu_id and c2.c_name = '英语';

查询结果