测试用例五+数据库

create database db_test charset=utf8

use db_test

create table major(
mid int primary key,
mname varchar(255)
)

create table student(
sid int primary key,
sname varchar(255),
score int,
joindate varchar(255),
smid int
)

insert into major values(50,‘PHP’)

insert into student values(1003,‘刘能’,‘87’,‘2018-09-12’,50),(1004,‘范冰冰’,‘89’,‘2016-11-20’,10)

select sname,score from student

select sid,sname,joindate from student where score < 80

select sname,score,joindate from student where joindate like ‘2018%’

select * from student where smid=10 and score < 80

select * from student where sname like ‘刘%’

select avg(score) from student

select mid,max(score) from student s inner join major m on s.smid = m.mid group by m.mname

select sname,mname from student s inner join major m on s.smid =m.mid

select max(score) from student s where s.smid in(select mid from major) group by s.smid

select mid,mname,count(smid) from major m left join student s on s.smid = m.mid group by smid