实验四 单表查询

（一）实验目的

1、掌握数据库基础知识、数据库和表的基本操作

2、掌握单表中数据的增删改查。

（二）实验要求

请大家将执行结果以图片形式完成实验报告，并在最后给出自己经过实验得到的总结。

（三）实验学时

2学时

（四）实验内容

已知，有一个学生表student和一个分数表score，请按要求对这两个表进行操作。student表和score分数表的表结构分别如表1-1和表1-2所示。

表1-1 student表结构

字段名

数据类型

主键

非空

唯一

自增

字段描述

stu_id

INT(10)

是

是

是

是

学号

stu_name

VARCHAR(20)

否

是

否

否

姓名

sex

VARCHAR(2)

否

否

否

否

性别

birth

YEAR

否

否

否

否

出生年份

department

VARCHAR(20)

否

是

否

否

院系

addr

VARCHAR(50)

否

否

否

否

家庭住址

表1-2 score表结构

字段名

数据类型

主键

非空

唯一

自增

字段描述

score_id

INT(10)

是

是

是

是

编号

stu_id

INT(10)

否

是

否

否

学号

c_name

VARCHAR(20)

否

否

否

否

课程名

grade

INT(10)

否

否

否

否

分数

表1-1和表1-2中分别对student表和score表中字段的数据类型、主键、非空、唯一约束、自增和字段描述做了说明，其中这两个表中都有stu_id字段，用于表示学号，该字段使student表和score表实现了关联。

（1）启动MySQL服务器。

mysql -uroot -proot

（2）登录MySQL服务器。

mysql -uroot -proot

（3）创建数据库。

create database exp03

（4）切换当前数据库。

use exp03

（5）创建学生表student，插入6条记录。

stu_id

stu_name

sex

birth

department

addr

901

张飞

m

1985

cmp

hb

902

关羽

m

1986

chi

sx

903

貂蝉

f

1990

chi

sx

904

刘备

m

1990

eng

hb

905

小乔

 f

1991

eng

ah

906

赵云

m

1988

eng

hb

create table student
(
stu_id int(10) primary key not null unique auto_increment comment '学号',
stu_name varchar(20) not null comment '姓名',
sex varchar(2) comment '性别',
birth year comment '出生年份',
department varchar(20) not null comment '院系',
addr varchar(50) comment '家庭住址'
)
insert into student values
(901,'张飞','m',1985,'cmp','hb'),
(902,'关羽','m',1986,'chi','sx'),
(903,'貂蝉','f',1990,'chi','sx'),
(904,'刘备','m',1990,'eng','hb'),
(905,'小乔','f',1991,'eng','ah'),
(906,'赵云','m',1988,'eng','hb')

（6）创建分数表score，插入10条记录。

score_id

stu_id

c_name

grade

1

901

cmp

98

2

901

eng

80

3

902

cmp

65

4

902

chi

88

5

903

chi

95

6

904

cmp

70

7

904

eng

92

8

905

eng

94

9

906

cmp

90

10

906

eng

85

create table score
(
score_id int(10) primary key not null unique auto_increment comment '编号',
stu_id int(10) not null comment '学号',
c_name varchar(20) comment '课程名',
grade int(10) comment '分数'
)
insert into score(stu_id,c_name,grade) 
values
(901,'cmp',98),
(901,'eng',80),
(902,'cmp',65),
(902,'chi',88),
(903,'chi',95),
(904,'cmp',70),
(904,'eng',92),
(905,'eng',94),
(906,'cmp',90),
(906,'eng',85)

（7）查询全体学生的学号和姓名

select stu_id,stu_name from student

（8）查询全体学生的详细记录

select * from student

（9）查询英语系的学生姓名、年龄、系别

select stu_name,birth,department from student
where department='eng'

（10）查询所有选修过课程的学生学号（不重复）

select distinct stu_id from student

（11）查询考试90分以上的学生学号（不重复）

select distinct stu_id from score
where grade>=90

（12）查询不是英语系、计算机系的学生性别、年龄、系别

select sex,birth,department from student
where department not in('eng','cmp')

（13）查询出生于1980到1989年的学生学号、姓名、系别、出生日期；

select stu_name,sex,department,birth from student
where birth between 1980 and 1989

（14）查询姓张的学生情况

select * from student
where stu_name like '%张%'

（15）查询姓刘或姓赵的学生情况

select * from student
where stu_name like '%刘%' or stu_name like '%赵%'

（16）查询姓刘且名字为两个字的学生情况

select * from student
where stu_name like '刘_'

（17）查询1990年以后出生的学生姓名。

select stu_name from student
where birth>=1990

（18）查询英语系男同学的学生姓名。

select stu_name from student
where department='eng' and sex='m'

（19）查询女同学或者来自sx的男同学的学生姓名、性别及家庭住址。

select stu_name,sex,addr from student
where sex='f' or (sex='m' and addr='sx')

（20）查询成绩在90到95之间的课程名称及学生学号。

select c_name,stu_id from score
where grade between 90 and 95

（21）查询英语系中河北籍学生的人数。

select count(*) from student 
where department='eng' and addr='hb'

（22）统计年龄在34岁以下的男、女性的数量。

select count(*) from student
where 2023-birth<34 group by sex

（23）查询山西学生的最大年龄，最小年龄及山西学生的总人数

select max(2023-birth),min(2023-birth),count(*) from student
where addr='sx'

（24）查询学生语文成绩的总和。

select sum(grade) from score 
where c_name='chi'

（25）查询计算机课程的最高成绩和最低成绩。

select max(grade),min(grade) from score 
where c_name='cmp'

（26）查询学生英语成绩的平均值。

select avg(grade) from score
where c_name='eng'

（27）查询每位学生的平均成绩。

select stu_id,avg(grade) from score group by stu_id

（28）查询选修每门课程的人数

select c_name,count(*) from score group by c_name

（29）将每门课程的成绩由高到低排序

select c_name,grade from score order by c_name,grade desc

（30）查询每门课程的最高成绩，且按降序排列。

select c_name,max(grade) from score group by c_name order by max(grade) desc

（31）查询每门课程的平均成绩，且按降序排列。

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

（32）查询每位学生的最低成绩，且查询结果按其成绩的降序排列。

select stu_id,min(grade) from score group by stu_id order by min(grade)
desc

（33）用LENGTH(str)函数查询student表中stu_id为905的stu_name字段的字符长度

select length(stu_name) from student where stu_id='905'

（34）用CONCAT(s1,s2,……)函数将student表中stu_id为905的stu_namer字段中数据与“：”和“a beautiful girl”字符串连接起来

select concat(stu_name,':','a beautiful girl') from student where stu_id=905

(35) 查询每门课程选课学生的stu_id。

select c_name,stu_id from score group by c_name,stu_id