查询练习
创建表
-
学生表
student
学号
姓名
性别
出生日期
班级
create table student( sno varchar(20) primary key, sname varchar(20) not null, ssex varchar(10)not null, sbirthday datetime, class varchar(20)); -
课程表
Course
课程号
课程名称
教师编号
create table course( cno varchar(20) primary key, cname varchar(20) not null, tno varchar(20) not null, foreign key(tno) references teacher(tno)); -
成绩表
score
学号
课程号
成绩
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) ); -
教师表
teacher
编号
名字
性别
出生日期
职称
部门
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);
插入数据
#添加学生信息
insert into student values('101','曾华','男','1977-09-01','95033');
insert into student values('102','匡明','男','1975-10-02','95031');
insert into student values('103','王丽','女','1976-01-23','95033');
insert into student values('104','李军','男','1976-02-20','95033');
insert into student values('105','王芳','女','1975-02-10','95031');
insert into student values('106','陆君','男','1974-06-03','95031');
insert into student values('107','王尼玛','男','1976-01-23','95033');
insert into student values('108','张全蛋','男','1976-02-20', '95033');
insert into student values('109','赵铁柱','男','1975-02-10','95031');
#添加教师信息
insert into teacher values('804','李诚','男','1958-12-02','副教授','计算机系');
insert into teacher values('856','张旭','男','1969-03-12','讲师','电子工程系');
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("103", '3-245','86');
insert into score values("105", '3-245', '75');
insert into score values('109', '3-245', '68');
insert into score values('103', "3-105", '92');
insert into score values('105', '3-105', "88");
insert into score values('109', '3-105',"76");
insert into score values("103", '6-166','85');
insert into score values('105', '6-166','79');
insert into score values('109', '6-166','81');
查询练习
#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表中成绩在60到80之间的所有记录。
#查询区间 between....and......
select * from score where degree between 60 and 80;
#或
select * from score where degree > 60 and degree<80;#运算符表示
#5、查询score表中成绩为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降序查询student表的所有记录。
#升序降序
select * from student order by class desc;#降序
select * from student order by class; #默认升序
select * from student order by class asc;#升序
#8、以cno升序、degree降序查询score表的所有记录。#先以cno为升序，遇见相同的异score为降序
select * from score order by cno asc,degree desc;
#9、查询“95031"班的学生人数。
# 统计 count
select count(*) from student where class="95031";
#10、查询score表中的最高分的学生学号和课程号。(子查询或者排序)
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;#limit 0表示从哪开始，1表示查几条
查询练习
#11.查询每门课的平均成绩
select * from course;
#avg 求平均值
select avg(degree) from score where cno ="3-105";#一门课程
select cno,avg(degree) from score group by cno;#先利用group进行分组
#12.查询score表中至少有2名学生选修的并以3开头的课程的平均分数
select cno,avg(degree),count(*) from score group by cno having count(cno)>=2 and cno like "3%";#like%3以3为开头的
#13.查询分数大于70，小于90的sno列
select sno,degree from score where degree>70 and degree<90;
#或者
select sno,degree from score where degree between 70 and 90;
#14.查询所有学生的sname、cno、degree列
#示例一：进行分开查询，其中sno在两个表中是存在交集的
select sno,sname from student;
+-----+--------+
| sno | sname |
+-----+--------+
| 101 | 曾华 |
| 102 | 匡明 |
| 103 | 王丽 |
| 104 | 李军 |
| 105 | 王芳 |
| 106 | 陆君 |
| 107 | 王尼玛 |
| 108 | 张全蛋 |
| 109 | 赵铁柱 |
select sno,cno,degree from score;
+-----+-------+--------+
| sno | cno | degree |
+-----+-------+--------+
| 103 | 3-105 | 92 |
| 103 | 3-245 | 86 |
| 103 | 6-166 | 85 |
| 105 | 3-105 | 88 |
| 105 | 3-245 | 75 |
| 105 | 6-166 | 79 |
| 109 | 3-105 | 76 |
| 109 | 3-245 | 68 |
| 109 | 6-166 | 81 |
+-----+-------+--------+
#示例二，将两个查询进行合并
select sname,cno,degree from student,score where student.sno=score.sno;
+--------+-------+--------+
| sname | cno | degree |
+--------+-------+--------+
| 王丽 | 3-105 | 92 |
| 王丽 | 3-245 | 86 |
| 王丽 | 6-166 | 85 |
| 王芳 | 3-105 | 88 |
| 王芳 | 3-245 | 75 |
| 王芳 | 6-166 | 79 |
| 赵铁柱 | 3-105 | 76 |
| 赵铁柱 | 3-245 | 68 |
| 赵铁柱 | 6-166 | 81 |
+--------+-------+--------+
#多表查询
#15.查询所有学生的sno，cname,和degree列。
#示例一先分别表示
select cno,cname from course;
+-------+------------+
| cno | cname |
+-------+------------+
| 3-105 | 计算机导论 |
| 3-245 | 操作系统 |
| 6-166 | 数字电路 |
| 9-888 | 高等数学 |
select cno,sno,degree from score;
+-------+-----+--------+
| cno | sno | degree |
+-------+-----+--------+
| 3-105 | 103 | 92 |
| 3-245 | 103 | 86 |
| 6-166 | 103 | 85 |
| 3-105 | 105 | 88 |
| 3-245 | 105 | 75 |
| 6-166 | 105 | 79 |
| 3-105 | 109 | 76 |
| 3-245 | 109 | 68 |
| 6-166 | 109 | 81 |
+-------+-----+--------+
#示例二
select sno,cname,degree from course,score where course.cno = score.cno;
#16.三联表查询
#查询所有学生的sname、cname和degree列
select sno,cno,degree from score;
+-----+-------+--------+
| sno | cno | degree |
+-----+-------+--------+
| 103 | 3-105 | 92 |
| 103 | 3-245 | 86 |
| 103 | 6-166 | 85 |
| 105 | 3-105 | 88 |
| 105 | 3-245 | 75 |
| 105 | 6-166 | 79 |
| 109 | 3-105 | 76 |
| 109 | 3-245 | 68 |
| 109 | 6-166 | 81 |
select sname,sno from student;
+--------+-----+
| sname | sno |
+--------+-----+
| 曾华 | 101 |
| 匡明 | 102 |
| 王丽 | 103 |
| 李军 | 104 |
| 王芳 | 105 |
| 陆君 | 106 |
| 王尼玛 | 107 |
| 张全蛋 | 108 |
| 赵铁柱 | 109 |
+--------+-----+
select cname,cno from course;
+------------+-------+
| cname | cno |
+------------+-------+
| 计算机导论 | 3-105 |
| 操作系统 | 3-245 |
| 数字电路 | 6-166 |
| 高等数学 | 9-888 |
+------------+-------+
select sname,cname,degree from score,student,course where course.cno=score.cno and student.sno=score.sno;
+--------+------------+--------+
| sname | cname | degree |
+--------+------------+--------+
| 王丽 | 计算机导论 | 92 |
| 王丽 | 操作系统 | 86 |
| 王丽 | 数字电路 | 85 |
| 王芳 | 计算机导论 | 88 |
| 王芳 | 操作系统 | 75 |
| 王芳 | 数字电路 | 79 |
| 赵铁柱 | 计算机导论 | 76 |
| 赵铁柱 | 操作系统 | 68 |
| 赵铁柱 | 数字电路 | 81 |
+--------+------------+--------+
#我TM就是个天才
select sname,cname,degree ,student.sno as stu_sno,course.cno as cou_cno from score,student,course where course.cno=score.cno and student.sno=score.sno;#student.sno as 语句需要学习
子查询
#17.子查询加分组求平均分
#查询“95031”班学生每门课的平均分
#示例一，先进行拆分的表示
select * from student where class="95031";
mysql> select * from score where sno in(select sno from student where class="95031");#此处的in是非常值得学习的，筛出来的必须是同一样的
mysql> select cno,avg(degree) from score where sno in(select sno from student where class="95031") group by cno;#此处的group用的也非常棒
#TM逻辑鬼才啊，反正就是抽丝剥茧，反复练习，看好最小范围
#18.子查询
#查询选修“3-105”课程的成绩高于“109”号同学“3-105”成绩的所有同学的记录。
select degree from score where sno="109" and cno="3-105";
select * from score where cno = "3-105" and degree>(select degree from score where sno="109" and cno="3-105");
#19.查询成绩高于学号为“109”、课程号为“3-105”的成绩的所有记录
select degree from score where sno="109" and cno="3-105";
select * from score where degree>(select degree from score where sno="109" and cno="3-105");
#20.查询学号为108、101的同学出生的所有学生的sno、sname和sbirthday列。
select * from student where sno="108" or sno ="101";
#或者
select * from student where sno in("108",101);
#只查年份
select year(sbirthday) from student where sno in("108",101);
#进一步进行查询
select * from student where year(sbirthday) in (select year(sbirthday) from student where sno in (108,101));
#21.查询“张旭”教师任课的学生成绩
select * from teacher where tname="张旭";#第一步
mysql> select cno from course where tno=( select tno from teacher where tname="张旭");#第二步
mysql> select sno,degree from score where cno=(select cno from course where tno=( select tno from teacher where tname="张旭"));#第三步
#22.查询选修课程的同学多于2人的教师的姓名（计数）
#示例一自我研发
select cno from score group by cno having count(*)>2;#第一步先找出选修多于2人的课程号
select tno from course where cno=(select cno from score group by cno having count(*)>2);#第二步找出联系
select tname from teacher where tno in( select tno from course where cno in (select cno from score group by cno having count(*)>2));#第三步
#23.查询95033班和95031班全体学生的记录
slect * from student where class in("95033","95031");#我感觉这是在侮辱我智商
#24.查询存在85分以上成绩的课程Cno;
select cno,degree from score where degree>85;
#25.查询出“计算机系”教师所教课程的成绩表
select tno from teacher where depart="计算机系";#第一步先找出计算机系的
select cno from course where tno in (select tno from teacher where depart="计算机系");#第二步找出其中连接的关键词
select degree,cno from score where cno in ( select cno from course where tno in (select tno from teacher where depart="计算机系"));#最后得出最终的结果
#26.查询“计算机系”与“电子工程系”不同职称的tname和port，审题有困难 not in/union
select prof from teacher where depart="电子工程系";
select * from teacher where depart="计算机系" and prof not in(select prof from teacher where depart="电子工程系") union select * from teacher where depart="电子工程系" and prof not in(select prof from teacher where depart="计算机系");#not in 和union用的挺好的
#27 查询选修编号为“3-105”课程且成绩至少高于选修编号为“3-245”的同学的Cno、Sno和Degree,并按degree从高到低排序。any的用法以及排序的用法。
select * from score where cno ="3-105" and degree > any(select degree from score where cno = "3-245")order by degree desc;
#28.查询选修编号为“3-105”且成绩高于选修编号为“3-245”课程的同学的Cno、sno和degree.
#且，all的用法
mysql> select * from score where cno ="3-105" and degree > all(select degree from score where cno = "3-245");
#29.查询所有教师和同学的name、sex、和birthday
#union以及as的应用
select sname,ssex,sbirthday from student;#先找出学生的
select tname,tsex,tbirthday from teacher;#在找出老师的
select sname,ssex,sbirthday from student union select tname,tsex,tbirthday from teacher;#利用union进行合并
select sname as name,ssex as sex,sbirthday as birthday from student union select tname,tsex,tbirthday from teacher;#利用as对名称进行修改
#30.查询所用“女”教师和“女”同学的name,sex,birthday
select sname as name,ssex as sex,sbirthday as birthday from student where ssex="女" union select tname,tsex,tbirthday from teacher where tsex="女";
#复制表数据做条件查询
#31.查询成绩比该课程平均成绩低的同学此的成绩表
select * from score a where degree<(select avg(degree) from score b where a.cno=b.cno);
#这个a和b用的妙啊，
#？？？where a.cno=b.cno不太明白
#32.查询所有任课老师的Tname和depart
select tname,depart from teacher where tno in (select tno from course);