数据库04——MySQL中查询

系统：win7 64位

接上一篇数据库03——MySQL中约束

1. 准备数据

1. 1 准备数据库

create database selectTest;

1.2 准备数据表

//学生表
学生号
学生姓名
学生性别
学生出生年月
班级

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 teacher(
	tno VARCHAR(20) primary key,
	tname VARCHAR(20) not null,
	tsex VARCHAR(10) not null,
	tbirthday datetime,
	prof VARCHAR(20) not null,
	depart VARCHAR(20) not null
);

//课程表
课程号
课程名
教师

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

//成绩表
学号
课程号
成绩

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

1.3 准备数据表中数据

insert into student values('1001','张雪','女','1998-10-02','1806');
insert into student values('1002','张白','男','1997-08-08','1806');
insert into student values('1003','王三','男','1995-04-25','1804');
insert into student values('1004','李斯','女','1994-05-02','1806');
insert into student values('1005','王柳','女','1996-12-20','1804');

insert into teacher values('885','王军','男','1976-06-15','教授','数据系');
insert into teacher values('886','李红','女','1976-09-29','副教授','计算机系');
insert into teacher values('887','六霜','女','1972-05-12','教授','计算机系');
insert into teacher values('888','池昌','女','1972-05-12','教授','计算机系');
insert into teacher values('889','孙桦','女','1982-04-30','副教授','计算机系');

insert into course values('3-101','计算机概论','888');
insert into course values('3-102','数据分析','887');
insert into course values('3-103','计算机算法','888');
insert into course values('3-104','数字电路','886');

insert into score values('1001','3-102','86');
insert into score values('1001','3-101','88');
insert into score values('1002','3-104','86');
insert into score values('1002','3-103','80');
insert into score values('1003','3-102','97');
insert into score values('1003','3-101','93');
insert into score values('1004','3-102','70');
insert into score values('1005','3-104','69');
insert into score values('1005','3-102','93');

2. 练习

1. 查询student表的所有记录

select * from student;

• *代表所有字段
  

  ---

2. 查询student表的所有记录sname、ssex、class列

select sname,ssex,class from student;

---

3. 查询教师所有的单位，即不重复的depart列

select distinct depart from teacher;

• 不重复：distinct
  

  ---

4. 查询score表中成绩在80-90之间的所有记录

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

• between 80 and 90是闭区间
  

---

5. 查询score表中成绩为88、86的记录

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

• and是且，or是或
  

---

6. 查询student表中1806班或者性别为女的同学记录

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

---

7. 以class降序查询student表中的所有记录

select * from student order by class desc;

• order by:排序 desc:降序 asc;升序 （默认升序）
  

---

8. 以cno升序、degree降序查询score表的所有记录

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

---

9. 查询1804班的学生人数

select count(*) from student where class='1804';

• 括号不能丢，且count与(*)间不能有空格

---

10. 查询score表中最高分学生的学号与课程号（子查询或者排序）

select sno,cno from score where degree=(select max(degree) from score);
select sno,cno from score order by degree desc limit 0,1;

• limit 0,1:从第0个数据开始，只取第一个
  

11. 查询每门课的平均成绩

select cno,avg(degree) from score group by cno;

• avg(degree):平均数
• group by cno：按cno分组，不加这个就是将所有成绩的平均数
  

12. 查询score表中至少有2名学生选修的并以3开头的课程的平均分数

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

• having count(cno)>3:至少有两名学生
• cno like ‘3%’:模糊查询，cno以3开头

13. 查询分数大于70，小于90 的sno列

select sno,degree from score where degree>70 and degree <90;

14. 查询所有学生的sname、cno、degree列

sname在 student表中，cno、degree在score表中

select sname,cno,degree from score,student 
where student.sno=score.sno;

• 两个表联合查询
  

15. 查询所有学生的sno、cname和degree列

select sno,cname,degree from score,course 
where score.cno=course.cno;

• 将两个表中共同的字段=连接后，就可以联合查询了
  

16. 查询所有学生的sname、cname、cno和degree列

select sname,cname,degree,score.sno from score,course,student 
where score.cno=course.cno and student.sno=score.sno;

• 表名没有顺序，字段顺序即生成表格表头顺序
• 两个表连接之间用and
• 有重复字段的需要指定 score.sno
• 将score.sno换个字段名字 score.sno as score_sno

select sname,cname,degree,score.sno as score_sno from score,course,student 
where score.cno=course.cno and student.sno=score.sno;

17. 查询1806班学生每门课的平均分

select cno,avg(degree)
from score
where cno in (select sno from student where class='1806')
group by cno;

select sno from student where class =‘1806’;//找到1806班的学生