实验6 数据检索

实验6 数据检索
【实验名称】数据检索
【实验目的】
 掌握select子句的使用方法
 掌握where子句筛选数据行的方法
 掌握order by子句排序查询结果的方法
 掌握统计函数的使用方法
 掌握数据行分组统计及对组进行筛选的方法
 掌握连接查询、子查询及集合运算的方法
【实验内容】
 练习课本的例题和实验课本中的实验5和实验6。
 完成课本P126页的上机作业。
 （选做）如何将笔记本电脑建立的数据库转移到机房电脑上。
实验过程（步骤）或程序代码：

  # 查询表的所有数据
use teaching;
select * from course;
select * from student;
select * from score;
 

查询指定列

select studentno,sname,phone from student;

为字段取别名

select studentno as '学号',sname as '姓名',
phone as '手机号',year(now())-year(birthdate) as '年龄'
from student
where year(birthdate)>2001;
 

使用谓语过滤记录

select distinct studentno,courseno
from score
where final > 95
order by studentno;
 

查询符合条件的数据

select studentno,sname,phone
from student
where entrance > 800;
 

带in关键字查询

select studentno,courseno ,daily,final
from score
where studentno in ('18135222201','18137221508','19123567897');

带between and的范围查询

select studentno,final
from score
where courseno = 'c05109' and daily between 80 and 95;

带like的字符匹配查询

select sname,birthdate, Email
from student
where sname like '何%' or sname like '韩%';

用is null查询空值

create table se_score as select * from score;
alter table se_score
add score float(3,1) null after courseno;

select studentno, courseno, score
from se_score
where score is null;
 

带and的多条件查询

select studentno ,courseno,daily,final
from score
where daily >=90 and final >= 85;
 

带or的多条件查询

select teacherno ,tname,major
from teacher
where department = '计算机学院' and (prof = '副教授' or prof = '教授');

用order by对结果排序

select studentno 学号,sname 姓名,entrance 入学成绩
from student
where entrance > 850 
order by entrance desc;
 
select courseno 课程号,daily * 0.2 + final * 0.8 as '总评' ,studentno 学号
from score
where daily * 0.2 + final * 0.8 > 90
order by courseno,daily * 0.2 + final * 0.8  desc;
 

group by 分组

select studentno 学号, round(avg(daily * 0.3 + final * 0.7),2) as '平均分'
from score
group by studentno;

group by 与group_concat()连用

select courseno 课程号,group_concat(studentno) 选课学生学号
from score
group by courseno;

group by 与having连用

select studentno 学号 ,sum(daily * 0.3 + final * 0.7) as '总分'
from score
where final >= 75
group by studentno
having count(*) >= 3
order by sum(daily * 0.3 + final * 0.7) desc;

用limit限制查询结果

select studentno ,sname,birthdate,phone
from student
order by entrance desc
limit 3;
 
select * from score
where final>85
order by daily asc
limit 2,5;

聚合函数查询

count

select count(studentno) as '18级学生数'
from student
where substring(studentno,1,2) = '18';
 

sum && avg

select studentno 学号 , sum(final) 总分,avg(final) 平均分
from score
group by studentno
having sum(final)>2
order by studentno;

max && min

select max(final) 最高分,min(final) 最低分,max(final) - min(final) as 分差
from score
where (courseno = 'c05109');
 

group by与with roll up

select courseno 课程号,avg(final) 课程期末平均分
from score
group by courseno with rollup;
 

内连接

select student.studentno ,sname,final
from student inner join score
on student.studentno = score.studentno
where score.courseno = 'c05109';
 
select student.studentno ,sname,final
from student,score
where student.studentno = score.studentno
and score.courseno = 'c05109';

外连接

交叉连接

select student.studentno ,sname,score.*
from student cross join score;
 

连接多个表

select student.studentno,sname,cname,final,round(period/16,1)
from score join student on student.studentno = score.studentno
join course on score.courseno = course.courseno
where substring(student.studentno,1,2) = '18';
 
create table student01 as select studentno,sname,phone from teaching.student;
select studentno,sname,phone from student01
where phone like '%131%'
union
select studentno,sname,phone from teaching.student
where phone like '%132%';

利用子查询做表达式and

select studentno,sname,entrance,(select avg(entrance) from student) 平均成绩,entrance - (select avg(entrance) from student) 分差
from student
where studentno = '18125121107';

利用子查询生成派生表

select TT.studentno 学号,TT.courseno 课程号,TT.final * 0.8 + TT.daily *0.2 总评
from (select * from score where final > 85) as TT
where TT.final * 0.8 + TT.daily *0.2 > 90;

where 语句中的子查询

select studentno,courseno,final
from score as a
where final<(select avg(final) from score as b where a.courseno = b.courseno group by courseno);
 

带in关键字的子查询

select studentno,sname,phone,Email
from student
where studentno in (select studentno from score where final>93);

带exist

select studentno,sname,birthdate,phone
from student
where exists(select * from student where birthdate > '2001-12-12');

对比较运算进行限制的子查询

select student.studentno,sname,phone,final
from score inner join student
on score.studentno = student.studentno
where final > all
(select final from score where courseno = 'c05109');
 
create database mysqltest;

利用子查询插入数据

use teaching;
create table mysqltest.student02
(select * from student where birthdate > '2001-12-31');

利用子查询更新数据

update score
set final = final * 1.05
where studentno in (
select studentno from student where entrance < 800);

查询以特殊字符或字符串开头的记录

select studentno,sname,birthdate,phone
from student
where sname regexp '^赵';

查询以特定的字符或字符串结尾的记录

select studentno,sname,phone,Email
from student
where phone regexp '5$';
 

用符号.来替代字符串中的任意一个字符

select studentno,sname,phone
from student
where sname regexp '^赵..江$';

匹配指定字符串

select studentno,sname,phone,Email
from student
where phone regexp '131|132';

实验环境与器材：
实验过程（步骤）或程序代码：

实验五

基本句子的使用

select studentno 学号,sname 姓名,phone 电话,year(now())-year(birthdate) 年龄 
from student 
where birthdate < '2003-01-02';

在score表中查询期中考试成绩高于90分的学生的学号和课程号，并按照学号排序

select distinct studentno,courseno
from score
where daily>90
order by studentno;
 

查询学号为(18122221324,18137221508,18137221508)学生的课程号，平时成绩，期末成绩

select courseno,daily,final
from score
where studentno in(18122221324,18137221508,18137221508);

查询选修学号为c08123的学生的学号和期末成绩，并要求平时成绩在85~100分

select studentno,final 
from score
where courseno = 'c08123' and daily between 85 and 100;

在student表中查询所有姓赵的同学的姓名，生日和Email:

select sname,birthdate,Email
from student
where sname like '赵%';

在score表中显示期中成绩高于85分,期末成绩高于90分的学生的学号，课程号和成绩

select studentno,courseno,daily,final
from score
where daily >= 85 and final >= 90;

查询计算机专业为软件工程或网络技术的教师的教师号和职称

select teacherno,tname,prof
from teacher
where department = '计算机学院'
and (major = '软件工程' or major = '网络技术');

排序，分组和限定记录的查询

在student表中输出高于800分的学生的学号，姓名，电话和入学成绩，并按照入学成绩降序输出

select studentno,sname,phone,entrance
from student
where entrance > 800
order by entrance desc;

在score表中查询总评成绩大于85分的学生的学号，课程号，电话和总评成绩，并按照课程号的升序，载按总评成绩的降序排序

select courseno,daily * 0.2 + final * 0.8 as '总评',studentno
from score where daily * 0.2 + final * 0.8 > 85
order by courseno,daily * 0.2 + final * 0.8 desc;
 

利用group by 子句对score表数据分组，显示每个学生的学号和平均总评成绩

select studentno,round(avg(daily * 0.3 + final * 0.7),2) as avg1
from score
group by studentno;
 

利用group by关键字和group_concat()函数对score表中的courseno字段进行

分组查询。可以查看该学生选修的课程号

select studentno 学号,group_concat(courseno) 选课学生
from score
group by studentno;
 

查询选课在2门以上且各门课程期末成绩均高于85分的学生的学号及其总成绩 查询结果按总成绩降序排序

select studentno 学号,sum(daily * 0.3 +final * 0.7) as '总分'
from score
where final >= 85
group by studentno
having count(*) >= 2
order by sum(daily * 0.3 +final * 0.7) desc;

查询score表中期末成绩final高于90分的成绩，按照平时成绩daily进行升序排序 从序号1开始，查询3条记录

select * from score
where final>90
order by daily asc
limit 1,3;

聚合函数的应用

查询score表中学生的期末总成绩大于275的学生学号，总成绩及平时成绩

select studentno 学号,sum(final) 总分,avg(final) 平均分
from score
group by studentno
having sum(final) > 275
order by studentno;
 

查询选修课程号为c05103的学生的期末最高分，最低分以及之间相差的分数

select max(final) '最高分',min(final) '最低分',max(final) - min(final) as '分差'
from score
where(courseno = 'c05103');
 

查询score表中每个学生期末平均值和所有成绩的平均值

select studentno '学号',avg(final) '平均分'
from score
group by studentno with rollup;

实验六

查询选修课程号为c06106的学生的学号、姓名、平时成绩和期末成绩：

select student.studentno,sname,final
from student inner join score
on student.studentno = score.studentno
where score.courseno = 'c06108';

利用左连接方式查询学生的学姓名、平时成绩和期末成绩

select student.studentno,sname,daily,final
from student left join score
on student.studentno = score.studentno;

使用右外连接查询教师的排课情况

select teacher.teacherno,tname,major,courseno
from teacher right join teach_course
on teacher.teacherno = teach_course.teacherno;

查询19级学生的学号、姓名、课程名、期末成绩及学分

select student.studentno,sname,cname,final,round(period/16,1)
from score join student on student.studentno = score.studentno
           join course  on score.courseno = course.courseno
           where left(student.studentno,2) = '19';
            

查询期末成绩高于90，总评成绩高于85的学生的学号，课程号

select TT.studentno 学号,TT.courseno 课程号,TT.final * 0.8 + TT.daily *0.2 总评
from (select * from score where final > 90) as TT
where TT.final * 0.8 + TT.daily *0.2 > 85;
 

查询期末成绩比选修该课程平均期末成绩低的学生的学号，课程和期末成绩

select studentno,courseno,final
from score as a
where final < (select avg(final) from score as b where a.courseno = b.courseno group by courseno);
 

获取期末成绩高于90分的学生的学号、姓名、电话和Email

select studentno,sname,phone,Email
from student
where studentno in (select studentno from score where final > 90);

查找score表中所有比c05103课程期末成绩都高的学号、姓名、电话和期末成绩

select student.studentno,sname,phone,final
from score inner join student
on score.studentno = student.studentno
where final>all(select final from score where courseno ='c05103');

将student表中1999年以后出生的学生记录添加到stud表中：

insert into stud
(select * from student where birthdate >= '1999-12-31');

查询student表中电话号码尾数为8的学生的部分信息

select studentno,sname,phone,Email
from student
where phone regexp '8$';

实验结果与分析：

成 绩：
教师签名：
月 日