数据库题---技能9

省份表

create table province(
pid int,
pname varchar(255)
)

学生表

create table student(
sid int,
sname varchar(255),
score float(4,2),
joindate date,
spid int
)

insert into province values(10,“河北”);
insert into province values(20,“黑龙江”);
insert into province values(30,“吉林”);
insert into province values(40,“辽宁”);
insert into province values(50,“内蒙古”);

insert into student values(1001,“张三”,95.5,“2018-03-12”,30);
insert into student values(1002,“李四”,93,“2017-09-18”,10);
insert into student values(1003,“赵六”,87,“2018-09-12”,50);
insert into student values(1004,“赵六1”,89,“2016-11-20”,10);
insert into student values(1005,“孙七”,75,“2017-06-30”,10);
insert into student values(1006,“熊大”,99.9,“2018-03-22”,20);
insert into student values(1007,“熊二”,65,“2018-09-12”,30);
insert into student values(1008,“光头强”,59,“2015-12-09”,50);

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

SELECT * FROM student;

查询姓名为李四的学生信息

SELECT * FROM student WHERE sname = ‘李四’;

查询2017年入学的学生学号，姓名，分数

SELECT * FROM student WHERE joindate LIKE ‘2017%’;

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

select * from province p inner join student s on p.pid=s.spid where p.pid=30 and s.score > 80;
#查询名字是3个字的所有学生信息
select * from student where sname like “___”;

查询学号从1004至1007学生的姓名和入学日期

select sname,joindate from student where sid between 1004 and 1007;

查询各省份的最高成绩，显示省份编号和最高成绩

select spid,max(score) from student group by spid;

查询所有学生姓名及对应的专省份

select sname,pname from province p inner join student s on p.pid=s.spid;

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

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

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

select pid,pname,count(spid) from province p left join student s on p.pid=s.spid group by pname;