数据库题---技能8

create database db_test charset=utf8;
use db_test;

专业表

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

学生表

create table student(
sid int,
sname varchar(255),
score float,
joindate DATE
)

查询学生表中所有学生记录

select * from student;

查询姓名为刘德华的学生信息

select * from student where sname=“刘德华”;

查询2018年入学的学生姓名、分数和入学时间

select sname,score,joindate from student where joindate between “2018-01-01” and “2018-12-31”;

查询专业编号为30并且分数高于80分的学生信息

select * from major m inner join student s on m.mid=s.smid where m.mid=30 and s.score > 80;

查询名字是3个字的所有学生信息

select * from student where sname like “___”;

查询前4个学生的编号、姓名和入学日期

select sid,sname,joindate from student limit 0,4;

查询各专业的最高成绩，显示专业编号和最高成绩

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

查询所有学生姓名及对应的专业名称

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

查询高于平均成绩的学生姓名、分数和入学日期

select sname,score,joindate from student where score > (select avg(score) from student);

用左外连接（专业表为左表），查询各专业的专业编号、专业名称、人数

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