MySql（二）

MySql

MySql版本：MySQL 8.0

select…from…where基本子句的使用。

①在student表中查询出生日期在2003年以前的学生的学号、姓名、电话和年龄。

mysql> select studentno 学号,sname 姓名,

-> phone 电话,year(now())-year(birthdate) 年龄

-> from student

-> where birthdate<‘2003-01-01’;

② 在score表中查询期中成绩中有高于90的学生的学号和课程号,并按照学号排序。

mysql> select distinct studentno,courseno

-> from score

-> where daily>90

-> order by studentno;

③ 查询学号分别为的18122221324、18137221508和18137221508的学生学号、课程号、平时成绩和期末成绩。

mysql> select studentno,courseno ,daily ,final

-> from score

-> where studentno in(‘18122221324’,‘18137221508’,‘18137221508’);

④查询选修课程号为c05109 的学生学号和期末成绩，并且要求平时成绩在85到100之间。

mysql> select studentno, final

-> from score

-> where courseno=‘c05109’ and daily between 85 and 100;

⑤在student表中显示所有姓许的学生的姓名、生日和Email。

mysql> select sname, birthdate, Email

-> from student

-> where sname like ‘许%’;

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

mysql> select studentno,courseno,daily,final

-> from score

-> where daily >=85 and final >= 90;

⑦ 查询计算机学院专业为"软件工程"或"网络技术"的教师的教师号、姓名和职称。

mysql> select teacherno,tname, prof

-> from teacher

-> where department=‘计算机学院’

-> and (major ='软件工程’or major =‘网络技术’);

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

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

mysql> select studentno ,sname ,phone,entrance

-> from student

-> where entrance>800

-> order by entrance desc;

tips： desc为降序。

②在score表中查询总评成绩大于85分的学生的学号、课程号和总评成绩，并先按照课程号的升序、再按照总评成绩的降序排列。总评成绩计算公式如下：

总评成绩=daily0.2+final0.8

mysql> select courseno,daily 0.2+ final0.8 as ‘总评’,studentno

-> from score

-> where daily 0.2+ final0.8>85

-> order by courseno, daily 0.2+ final0.8 desc;

③利用group by子句对score表数据分组，显示每个学生的学号和平均总评成绩。总评成绩计算公式如下：

总评成绩=daily0.3+final0.7

mysql> select studentno , round(avg(daily0.3+final0.7),2) as avg1

-> from score

-> group by studentno;

④使用group by关键字和group_concat()函数对score表中的courseno字段进行分组查询。可以查看选学该学生选修的课程号。

mysql> select studentno 学号,group_concat(courseno) 选课学生

-> from score

-> group by studentno ;

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

mysql> select studentno 学号,sum(daily0.3+final0.7) as ‘总分’

-> from score

-> where final>=85

-> group by studentno

-> having count(*)>=2

-> order by sum(daily0.3+final0.7) desc;

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

mysql> select * from score

-> where final>90

-> order by daily asc

-> limit 1,3;

（3）聚合函数的应用。

①查询score表中学生的期末总成绩大于75分的学生学号、总成绩及平均成绩。

mysql> select studentno 学号, sum(final) 总分, avg(final)平均分

-> from score

-> group by studentno

-> having sum(final)>75

-> order by studentno;

②查询选修课程号为c05109号课程的期末最高分、最低分及之间相差的分数。

mysql> select max(final) 最高分, min(final) 最低分,

-> max(final)- min(final) as 分差

-> from score

-> where (courseno = ‘c05109’);

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

mysql> select studentno 学号,avg(final) 平均分

-> from score

-> group by studentno with rollup;

（4）多表连接

① 查询选修课程号为c05109的学生的学号、姓名和期末成绩。

mysql> select student.studentno,sname,final

-> from student inner join score

-> on student.studentno= score.studentno

-> where score.courseno = ‘c05109’;

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

mysql> select student.studentno,sname,daily,final

-> from student left join score

-> on student.studentno=score.studentno;

③利用右外连接方式查询教师的排课情况。

mysql> select teacher.teacherno,tname, major, courseno

-> from teacher right join teach_course

-> on teacher.teacherno = teach_course.teacherno;

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

mysql> 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分的学生的学号、课程号和总评成绩。

mysql> select TT.studentno 学号 ,TT.courseno 课程号 ,

-> TT.final0.8+TT.daily0.2 总评

-> from (select * from score where final>90) as TT

-> where TT.final0.8+TT.daily0.2>85;

（5）子查询

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

mysql> 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。

mysql> select studentno,sname,phone,Email

-> from student

-> where studentno in ( select studentno

-> from score

-> where final>90);

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

mysql> 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表中。

mysql> insert into stud

-> ( select * from student

-> where birthdate>=‘1999-12-31’);

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

mysql> select studentno, sname, phone, Email

-> from student

-> where phone regexp ‘8$’;

（6）创建和管理视图

①在teacher表上创建一个简单的视图，视图名称为v_teacher。

mysql> create view v_teacher

-> as select * from teacher;

mysql> select * from v_teacher;

②在student表、course表和score表上创建一个名为stu_score的视图。视图中保留19级的女生的学号、姓名、电话、课程名和期末成绩。

mysql> create view stu_score

-> as select student.studentno, sname, phone, cname,final

-> from score join student on student.studentno=score. studentno

-> join course on course.courseno=score.courseno

-> where sex=‘女’ and left(student.studentno,2)= ‘19’;

mysql> select * from stu_score;

③创建视图v_teach，统计材料学院的教师中的不是教授或副教授的教师号、教师名和专业。

mysql> create view v_teach

-> as select teacherno, tname, major

-> from teach_view1

-> where prof not like ‘%教授’ and department=‘材料学院’;

④查看视图定义情况。

mysql>show create view stu_score;

⑤修改视图v_teach，统计材料学院的教师中的教授和副教授的教师号、教师名、和专业，并在视图名后面指明视图列名称。

mysql> alter view v_teach (教师号,教师名,专业)

-> as select teacherno, tname, major

-> from teach_view1

-> where prof like ‘%教授’ and department=‘材料学院’ ;

mysql> select * from v_teach;

⑥ 删除视图v_teach命令：

mysql> drop view v_teach;

执行命令之前：


执行命令之后：

（7）利用视图修改表数据。

① 创建视图view_avg，统计各门课程平均成绩，并按课程名称升序排列。

mysql> create view view_avg

-> as select cname 课程名, avg(final) 平均成绩

-> from score join course on score.courseno= course.courseno

-> group by cname asc;

mysql> select * from view_avg;

②通过视图v_teacher，对基表teacher进行插入、更新和删除数据的操作。

mysql> insert into v_teacher (teacherno,tname,major,prof,department)

-> values (‘t07027’ , ‘陶颐年’ , ‘合金技术’ , ‘副教授’ , ‘材料学院’ );

mysql> update v_teacher set prof = ‘副教授’ where teacherno = ‘111’;

mysql> delete from v_teacher where teacherno = ‘t07027’;

③ 视图stu_score依赖于源表student、course和score等3张表，包括studentno、sname、 phone、cname和final等5个字段，通过stu_score修改基本表student中的学号为19120000111的电话号码。

mysql> update stu_score set phone=‘133123456777’

-> where studentno =‘19120000111’;

mysql> select studentno,sname, phone from student

-> where studentno =‘19126113307’ ;

tips： stu_score是针对19级的视图，验证能否修改18级数据。

mysql> update stu_score set phone=‘135123456777’

-> where studentno =‘18125121107’;

④ 修改v_teacher的视图定义，添加with check option选项。

mysql> alter view v_teacher

-> as

-> select * from teacher

-> where department = ‘计算机学院’

-> with check option ;