MySQL实验五：设计性实验

实验五：设计性实验

Student 表的内容

字段名	字段描述	数据类型	主键	外键	非空	唯一	自增
num	学号	INT(10)	是	否	是	是	否
name	姓名	VARCHAR(20)	否	否	是	否	否
sex	性别	VARCHAR(4)	否	否	是	否	否
birthday	出生年份	DATETIME	否	否	否	否	否
bumen	院系	VARCHAR(20)	否	否	是	否	否
address	家庭住址	VARCHAR(50)	否	否	否	否	否

score 表的内容

字段名	字段描述	数据类型	主键	外键	非空	唯一	自增
id	编号	INT(10)	是	否	是	是	否
c_name	课程名	VARCHAR(20)	否	否	否	否	否
stu_id	学号	INT(10)	否	是	是	否	否
grade	成绩	INT(10)	否	否	否	否	否

初始化数据

创建数据库语句：

create database if not exists schoolinfo;
use schoolinfo;

创建表语句：

create table student(
	num int(10) primary key not null unique comment '学号',
    name varchar(20) not null comment '姓名',
    sex varchar(4) not null comment '性别',
    birthday datetime comment '出生年月日',
    bumen varchar(20) not null comment '院系',
    address varchar(50) comment '家庭住址'
)charset=utf8;

create table score(
	id int(10) primary key not null unique comment '编号',
    c_name varchar(20) comment '课程名',
    stu_id int(10) not null comment '学号',
    grade int(10) comment '成绩',
    foreign key (stu_id) references student(num)
)charset=utf8;

插入数据语句：

insert into student values
(901,'张军','男','1985-04-02','计算机系','北京市海淀区'),
(902,'张超','男','1986-06-04','中文系','北京市昌平区'),
(903,'张美','女','1990-07-09','中文系','湖南省永州市'),
(904,'李五一','男','1990-12-12','英语系','辽宁省阜新市'),
(905,'王芳','女','1991-11-11','英语系','福建省厦门市'),
(906,'王桂','男','1988-10-10','计算机系','湖南省衡阳市');

insert into score values
(1,'计算机',901,98),
(2,'英语',901,80),
(3,'计算机',902,65),
(4,'中文',902,88),
(5,'中文',903,95),
(6,'计算机',904,70),
(7,'英语',904,92),
(8,'英语',905,94),
(9,'计算机',906,90),
(10,'英语',906,85);

按下列要求写出操作语句

1. 查询 student 表的所有记录。

• 方法一：用”*“。

  select * from student;

• 方法二：列出所有的列名。

  select num,name,sex,birthday,bumen,address from student;

2. 查询 student 表的第二条到第四条记录。

   select * from student limit 1,3;
   #在 student 表中搜索全部记录 从第2条开始向后3条。

3. 从 student 表查询所有学生的学号、姓名和院系的信息。

   select num,name,bumen from student;

4. 查询计算机系和英语系的学生的信息。

• 方法一：使用 IN 关键字

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

• 方法二：使用 OR 关键字

  select * from student where bumen='计算机系' or bumen='英语系';

5. 从 student 表中查询年龄为 18 到 22 岁的学生的信息。

• 方法一：使用 BETWEEN AND 关键字来查询

  select * from student where date_format(now(),'%y') - year(birthday) between 18 and 20;
  #data_format()函数，参数为(data，格式)参数,返回date经所传格式格式化后的数据。
  #now()函数，无参数，返回当前年月日时间。
  #year()函数，参数类型为(date)，返回所传参数的年份。

• 方式二：使用 AND 关键字和比较运算符。

  select * from student where date_format(now(),'%Y')-year(birthday)>18 and date_format(now(),'%Y')-year(birthday)<20;
  #data_format()函数，参数为(data，格式)参数（格式参数类似C语言）,返回date经所传格式格式化后的数据。
  #经测试发现格式里的%Y和%y不一样，Y表示完整年份，y表示年份的后两位
  #即如果现在输入：
  select date_format(now(),'%Y');
  #结果为2021，如果输入：
  select date_format(now(),'%y');
  #结果为21
  #now()函数，无参数，返回当前年月日时间。

6. student 表中查询每个院系有多少人，为统计的人数列取别名 sum_of_bumen。

   select bumen,count(num) as sum_of_bumen from student group by bumen;

7. 从 score 表中查询每个科目的最高分。

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

8. 查询李五一的考试科目（c_name）和考试成绩（grade）。

   select c_name,grade from score where stu_id=(select num from student where name='李五一');

9. 用连接查询的方式查询所有学生的信息和考试信息。

   select * from student join score on student.num=score.stu_id;

10. 计算每个学生的总成绩（需显示学生姓名）。

    select name,sum(grade) from student join score on student.num=score.stu_id group by name;

11. 计算每个考试科目的平均成绩。

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

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

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

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

    select * from student where num in (select stu_id from score  where c_name='计算机') and num in(select stu_id from score  where c_name='英语');

14. 将计算机成绩按从高到低进行排序。

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

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

    select num from student union (select stu_id from score);

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

    select name,bumen,c_name,grade from student join score on student.num=score.stu_id where name like'张%' or name like'王%';

17. 查询都是湖南的同学的姓名、年龄、院系、考试科目和成绩。

    select name,date_format(now(),'%Y') - year(birthday) as age,bumen,c_name,grade from student join score on student.num=score.stu_id where address like'湖南%';