数据库知识点涵盖总结题库及答案

 

测试数据：将sql复制到数据库生成库，表，数据。

create database if not exists testdb;
use testdb
drop table if exists student;
create table student (
  s_id int(11) not null,
  s_name varchar(20) default null,
  age int(11) default null,
  sex enum('男','女') default '男',
  primary key (s_id)
);
insert into student values (1,'张三',18,'男'),(2,'李四',19,'女'),(3,'王五',17,'男'),(4,'赵六',19,'女'),(5,'钱七',20,'男'),(6,'马八',16,'男'),(7,'杨九',30,'男'),(8,'牛十',15,'女');


drop table if exists sc;
create table sc (
  sc_id int(11) not null auto_increment,
  s_id int(255) default null,
  c_id int(11) default null,
  score decimal(4,1) default null,
  primary key (sc_id)
);
insert into sc values (1,1,1,75.0),(2,1,2,88.0),(3,1,3,92.0),(4,1,4,69.0),(5,2,1,89.0),(6,2,2,92.0),(7,2,3,98.0),(8,2,4,94.0),(9,3,1,73.0),(10,3,2,85.0),(11,3,3,55.0),(12,3,4,69.0),(13,4,1,93.0),(14,4,2,95.0),(15,4,3,98.0),(16,4,4,100.0),(17,5,1,92.0),(18,5,2,65.0),(19,5,3,68.0),(20,5,4,82.0),(21,6,1,64.0),(22,6,2,69.0),(23,6,3,62.0),(24,6,4,61.0);


drop table if exists course;
create table course (
  c_id int(11) not null,
  c_name varchar(20) default null,
  primary key (c_id)
);
insert into course values (1,'数学'),(2,'语文'),(3,'英语'),(4,'历史'),(5,'物理'),(6,'化学');
show tables;
select * from student;
select * from course;
select * from sc;

数据表说明：

学生表：student

字段	说明
s_id	学号
s_name	学生姓名
age	年龄
sex	性别

课程表：course

字段	说明
c_id	课程编号
c_name	课程名称

成绩表：sc

字段	说明
sc_id	成绩编号
s_id	学号
c_id	课程编号
score	成绩

 题目（按照要求写SQL查询语句）：

1. 查看学生表、课程表和成绩表的信息

学生表	课程表	成绩表

书写查询语句
select * from student;
select * from course;
select * from sc;

2.在学生表中，按照性别统计男女生的人数

 

书写查询语句

select sex,count(*) from student group by sex;

3.在学生表中，显示出年龄最大的学生信息（显示学生表所有列）

 

 

书写查询语句：
select * from student where age=(select max(age) from student);

4.连接学生表和成绩表，显示所有学生及其成绩（显示学生表和成绩表所有列）

 

书写查询语句：
select * from student s left join sc on s.s_id=sc.s_id;

5.连接课程表和成绩表，显示所有课程及其成绩（显示课程表和成绩表所有列）

 

书写查询语句：
select * from course c left join sc on c.c_id=sc.c_id;

6.在成绩表中按照s_id（学号）分组统计平均成绩（score）

书写查询语句：
select s_id,avg(score) from sc group by s_id;

 7.在成绩表中按照c_id（课程编号）分组统计课程平均成绩（score）

书写查询语句：
select c_id,avg(score) from sc group by c_id;

8.查询平均成绩大于85的所有学生，显示学号、姓名和平均成绩

书写查询语句：
select s.s_id,s.s_name,avg(score) from student s join sc on s.s_id=sc.s_id 
group by s.s_id,s.s_name having avg(score)>85;

 9.查询哪些同学每一门课的成绩都高于90分，显示学号，姓名

书写查询语句：
select s.s_id,s_name from student s join sc on s.s_id=sc.s_id group by s.s_id,
s.s_name having min(score)>90;

 10.查询哪些学生每门课的成绩仅仅在60分和70分的范围之间，显示学号，姓名

 

书写查询语句：
select s.s_id,s_name from student s join sc on s.s_id=sc.s_id group by s.s_id,
s.s_name having min(score)>=60 and max(score)<=70;

 成绩单（按照学生显示）

 

select s.s_id 学号,s_name 姓名,ifnull(group_concat(c_name,'(',lpad(score,5,' '),')' 
order by c.c_id separator '|'),'-') 成绩单 from student s left join 
sc on s.s_id=sc.s_id left join course c on sc.c_id=c.c_id group by s.s_id,s_name;

 成绩单（按照课程显示）

 

select c.c_id 课程号,c_name 课程名,ifnull(group_concat(s_name,'(',lpad(score,5,' '),')' 
order by s.s_id separator '|'),'-') 成绩单 from course c left join
 sc on c.c_id=sc.c_id left join student s on sc.s_id=s.s_id group by c.c_id,c_name;