MySQL查询

MySQL查询语句

有三个表格,分别为student,major,result

1. 查询每个专业的学生人数

select count(*),majorid 
from student group by majorid;

2.查询参加考试的学生中，每个学生的平均分、最高分

select studentno,avg(score),max(score) 
from result group by studentno;

3.查询姓张的每个学生的最低分大于60的学号、姓名

select s.studentno,s.studentname
from student s
join result r on 
s.studentno=r.studentno
where studentname like '张%'
group by studentno
having min(score)>60;

4.查询每个专业生日在“1988-1-1”后的学生姓名、专业名称

select s.studentname,m.majorname
from student s
join major m on 
s.majorid=m.majorid
where datediff(borndate,'1988-1-1')>0;

5.查询每个专业的男生人数和女生人数分别是多少

select majorid,sex,count(*)
from student
group by majorid,sex
order by majorid,sex;

6.查询专业和张翠山一样的学生的最低分

Select min(score)
From result
Where studentno in (
Select studentno
From student
Where majorid = (select majorid from student 
where studentname ='张翠山'));

7.查询大于60分的学生的姓名、密码、专业名

Select studentname,loginpwd ,majorname 
From student s 
Join major m on s.majorid = m.majorid 
Join result r on s.studentno = r.studentno
Where r.score >60;

8.按邮箱位数分组，查询每组的学生个数

Select count(*), length(email)
From student 
Group by length (email);

9.查询学生名、专业名、分数

Select studentname,majorname,score 
From student s 
Join major m on s.majorid = m.majorid 
Join result r on s.studentno = r.studentno;

10.查询哪个专业没有学生，分别用左连接和右连接实现

Select m.majorid ,m.majorname,s.studentno 
From major m 
Left join student s on m.majorid = s.majorid 
Where s.studentno is null;

Select m.majorid , m.majorname , s.studentno
From student s
Right join  major m on m.majorid = s.majorid
Where s.studentno is null;

11.查询没有成绩的学生人数`

Select count(*)
From student s
left Join result r
On s.studentno = r.studentno
Where r.score is null;

create table `kcxxsj`(
`USER_KCDM` varchar(20) not null,
`KCMC` varchar(100) not null,
`jg_gh` int not null
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

load data infile 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/cjsj.csv' 
into table kcxxsj character set gb2312 
fields terminated by ',' optionally enclosed by '"' escaped by '"'
lines terminated by '\r\n'
ignore 1 lines;

create table `cjsj`(
`KCMC` varchar(100) not null,
`USER_KCDM` varchar(20) not null,
`KSCJ` int not null,
`KHFS` varchar(10) not null,
`KCLB1` varchar(10),
`xh` int not null
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

load data infile 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/cjsj.csv' 
into table cjsj character set gb2312 
fields terminated by ',' optionally enclosed by '"' escaped by '"'
lines terminated by '\r\n'
ignore 1 lines;

create table `jbxx`(
`XB` int not null,
`BJID` int not null,
`BJDM_USER` int not null,
`xh` int not null
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

load data infile 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/jbxx.csv' 
into table jbxx character set gb2312 
fields terminated by ',' optionally enclosed by '"' escaped by '"'
lines terminated by '\r\n'
ignore 1 lines;

create table `zyxysj`(
`USER_DM` int not null,
`ZWMC` varchar(100) not null,
`SSYXB_USERDM` int not null
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

load data infile 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/zyxysj.csv' 
into table zyxysj character set gb2312
fields terminated by ',' optionally enclosed by '"' escaped by '"'
lines terminated by '\r\n'
(`USER_DM`,  `ZWMC`, `SSYXB_USERDM` )