MySQL(查询练习)

数据准备

学生表

create table student(
	sno varchar(20) primary key,
	sname varchar(20) not null,
	ssex varchar(10) not null,
	sbirthday datetime,
	class varchar(20)
);

课程表

create table course(
	cno varchar(20) primary key,
	cname varchar(20) not null,
	tno varchar(20) not null,
	foreign key(tno) references teacher(tno)
);

教师表

create table teacher(
	tno varchar(20) primary key,
	tname varchar(20) not null,
	tsex varchar(20) not null,
	tbirthday datetime,
	prof varchar(20) not null,
	depart varchar(20) not null
);

成绩表

create table score(
	sno varchar(20) ,
	cno varchar(20) not null,
	degree decimal,
	foreign key(sno) references student(sno),
	foreign key(cno) references course(cno),
	primary key(sno,cno)
	
);

添加数据

--学生表
insert into student values('101','曾华','男','1977-09-01','95033');
insert into student values('102','匡明','男','1975-06-03','95031');
insert into student values('103','王丽','女','1976-09-01','95033');
insert into student values('104','王尼玛','男','1974-03-03','95031');
insert into student values('105','张全蛋','男','1976-02-01','95031');
insert into student values('106','赵铁柱','男','1975-11-06','95033');
--教师表
insert into teacher values('804','李成','男','1958-09-06','副教授','计算机系');
insert into teacher values('856','张旭','男','1969-04-23','讲师','电子工程系');
insert into teacher values('825','王萍','女','1972-05-05','助教','计算机系');
insert into teacher values('831','刘冰','女','1977-08-14','助教','电子工程系');
--课程表
insert into course values('3-105','计算机导论','825');
insert into course values('3-245','操作系统','804');
insert into course values('6-166','数字电路','856');
insert into course values('9-888','高等数学','831')
--成绩表
insert into score values('104','3-245','86');
insert into score values('105','3-245','76');
insert into score values('106','3-245','82');
insert into score values('104','3-105','91');
insert into score values('105','3-105','81');
insert into score values('106','3-105','79');
insert into score values('104','6-166','92');
insert into score values('105','6-166','84');
insert into score values('106','6-166','73');
insert into score values('104','9-888','98');
insert into score values('105','9-888','85');
insert into score values('106','9-888','77');

查询练习

1.查询student表所有记录

select *from student;

2.查询student表特定字段

select name,ssex,class from student;

3.查询教师所有单位即不重复的depart列
关键字distinct排除重复

select distinct depart from teacher;

4.查询score中成绩在60-80的
查询区间between … and …
使用运算符 < 和 >

select * from score where degree between 60 and 80;
select * from score where degree > 60 and degree<80;

5.查询成绩为85,86和88的，表示或者关系查询 关键字in

select *  from score where degree in (85,86,88);

6.查询student中"95031"班或者性别为"女"的同学记录，不同字段的或者关系查询 关键字or

select *  from student where class='95031' or ssex='女';

7.以class降序查询class表的记录 升序(默认)降序(desc) order by 关键字

select * from student order by class desc;

8.以cno升序，degree降序查询score表 升序降序共同查询

select * from score order by cno asc,degree desc;

9.统计’95031’班的人数，统计 count关键字

select * from student where class='95031';

10.统计score表中的最高分的学生号和课程号
子查询 先挑选出最高分，再用where找出最高分的学号和课程号

select sno,cno from score where degree=(select max(degree) from score);

--排序的做法
select sno,cno,degree from score order by degree desc limit 0,1
--0表示从第0条才是,1表示查询一条

11.查询每门课的平均成绩 avg,group by分组(同属某一类)

select avg(degree) from score where cno='3-105';
select cno,avg(degree) from score  group by cno;

12.查询score中至少有两名学生选修的并且以3开头的平均分数
1. WHERE 条件后面是不能跟聚合函数的，因为WHERE执行顺序大于聚合函数，如果需要用聚合函数作为过滤条件则用HAVING
2. HAVING通常是对分组以后的数据进行筛选，所以一般都是在使用GROUP BY 或者聚合函数后使用，而WHERE是在分组前对数据进行过滤
至少有两名：'count’计数
以三开头：like '3%'模糊查询

select cno,avg(degree),count(*) from score  group by cno having count(cno)>=2 and cno like'3%';