Mysql查询练习

查询数据准备

-- 查询练习数据准备 -- 学生表 -- Student -- 学号 -- 姓名 -- 性别 -- 出生年月日 -- 所在班级 CREATE TABLE Student( sno VARCHAR(20) PRIMARY KEY, sname VARCHAR(20) not null, ssex VARCHAR(10) not null, sbirthday datetime, class VARCHAR(20) ); -- 教师表 -- 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 ); -- 课程表 -- 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) ); -- 添加数据 -- 学生表数据 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-02-20','95033'); INSERT INTO student VALUES('108','张全蛋','男','1975-02-10','95031'); INSERT INTO student VALUES('109','赵铁柱','男','1974-06-03','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列 -- distinct 排除重复 select distinct depart from teacher; -- 4.查询score表中成绩在60-80之间所有的记录(degree) select * from score where degree between 60 and 80; select * from score where degree>60 and degree<80; -- 5.查询score表中成绩为85, 86, 或者88的记录(degree) select * from score where degree in(85,86,88); -- 6.查询student表中'95031'班或者性别为'女'的同学记录 select * from student where class='95031' or ssex='女'; -- 7.以class降序查询student表中所有的记录 --升序asc(默认)，降序desc select * from student order by class desc; -- 8.以cno升序.degree降序插叙score表中所有的数据 select * from score order by cno asc,degree desc; -- 9.查询'95031'班的学生人数 --统计 count select count(*) from student where class='95031'; -- 10.查询score表中的最高分数的学生号和课程号.(子查询或者排序) -- (1)找到最高分 -- (2)找最高分sno和cno select sno,cno from score where degree=(select max(degree) from score); -- 排序做法 -- limit (从多少开始),(查多少条) select sno,cno from score order by degree desc limit 0,1; -- 11.查询每门课的平均成绩 -- avg() -- select avg(degree) from score where cno='3-105'; -- group by分组 select cno,avg(degree) from score group by cno; -- 12.查询score表中至少有2名学生选修的,并且以3开头的课程的平均分 select cno,avg(degree),count(*) from score group by cno having count(cno)>=2 and cno like '3%'; -- 13.查询分数大于70但是小于90的sno列 select sno,degree from score where degree>70 and degree<90; -- between 其实是大于等于和小于等于 select sno,degree from score where degree between 70 and 90; -- 14.查询所有的学生 sname , cno, degree列 -- select sno,sname from student; -- select sno,cno,degree from score; select sname,cno,degree from student,score where student.sno = score.sno; -- 15.查询所有学生的sno, cname, degree列 -- select cno,cname from course; -- select cno,sno,degree from score; select sno,cname,degree from course,score where course.cno = score.cno; -- 16.查询所有的学生 sname , cname, degree列 -- sname -> student -- cname -> scoure -- degree -> score select sname,cname,degree from student,course,score where student.sno=score.sno and course.cno = score.cno; -- 17.查询班级是'95031'班学生每门课的平均分 -- select sno from student where class='95031'; -- select * from score where sno in(select sno from student where class='95031'); select cno,avg(degree) from score where sno in(select sno from student where class='95031') group by cno; -- 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 * from score where degree>(select degree from score where sno='109' and cno='3-105'); -- 20.查询所有学号为108、101的同学同年出生的所有学生的sno,sname和sbirthday -- select year(sbirthday) from student where sno in (108,101); select sno,sname,sbirthday from student where year(sbirthday) in (select year(sbirthday) from student where sno in (108,101)); -- 21.查询 "张旭" 教师任课的学生的成绩 -- select tno from teacher where tname='张旭'; -- select cno from course where tno=(select tno from teacher where tname='张旭'); select * from score where cno=(select cno from course where tno=(select tno from teacher where tname='张旭')); -- 22.查询选修课程的同学人数多于 5 人的教师姓名 -- 为了效果,添加数据: INSERT INTO score VALUES('101','3-105','90'); INSERT INTO score VALUES('102','3-105','91'); INSERT INTO score VALUES('104','3-105','89'); -- select cno from score group by cno having count(*)>5; select tname from teacher where tno in(select tno from course where cno in(select cno from score group by cno having count(*)>5)); -- 23.查询95033班和95031班全体学生的记录 -- 添加数据 INSERT INTO student VALUES('110','张飞','男','1974-06-03','95038'); select * from student where class in('95031','95033'); -- 24.查询存在85分以上成绩的课程cno select cno from score where degree>85 group by cno; -- 25.查出所有'计算机系' 教师所教课程的成绩表 -- select * from teacher where depart='计算机系'; -- select * from course where tno in(select tno from teacher where depart='计算机系'); select * from score where cno in (select cno from course where tno in(select tno from teacher where depart='计算机系')); -- 26.查询'计算机系'与'电子工程系' 不同职称的教师的name和prof -- union求并集 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='计算机系'); -- 27, 查询选修编号为"3-105"课程且成绩至少高于选修编号为'3-245'同学的cno,sno和degree,并且按照degree从高到地次序排序 -- any 任意一个,>min也可以 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 就是所有，>max也可以 select * from score where cno='3-105' and degree>all(select degree from score where cno='3-245'); -- 29. 查询所有教师和同学的 name ,sex, birthday -- as取别名，union取并集(这里第二排默认用第一排别名) select tname as name,tsex as sex,tbirthday as birthday from teacher union select sname,ssex,sbirthday from student; -- 30.查询所有'女'教师和'女'学生的name,sex,birthday select tname as name,tsex as sex,tbirthday as birthday from teacher where tsex = '女' union select sname,ssex,sbirthday from student where ssex = '女'; -- 31.查询成绩比该课程平均成绩低的同学的成绩表 select * from score a where degree< (select avg(degree) from score b where a.cno=b.cno); -- 32.查询所有任课教师的tname 和 depart(课程表中安排了课程) select tname,depart from teacher where tno in(select tno from course); -- 33.查出至少有2名男生的班号 select class from student where ssex='男' group by class having count(*)>1; -- 34.查询student 表中 不姓"王"的同学的记录 select * from student where sname not like '王%'; -- 35. 查询student 中每个学生的姓名和年龄(当前时间 - 出生年份) -- 当前年份select year(now()); select sname,year(now())-year(sbirthday) as '年龄' from student; -- 36. 查询student中最大和最小的 sbirthday的值 -- max与min函数 select max(sbirthday) as 'MAX',min(sbirthday) as 'MIN' from student; -- 37.以班级号和年龄从大到小的顺序查询student表中的全部记录 select * from student order by class desc,sbirthday asc; -- 38.查询"男"教师 及其所上的课 -- select * from teacher where tsex = '男'; select * from course where tno in (select tno from teacher where tsex = '男'); -- 39.查询最高分同学的sno cno 和 degree; -- select max(degree) from score; select * from score where degree =(select max(degree) from score); -- 40. 查询和"李军"同性别的所有同学的sname select sname from student where ssex= (select ssex from student where sname='李军'); -- 41.查询和"李军"同性别并且同班的所有同学的sname select sname from student where ssex= (select ssex from student where sname='李军') and class=(select class from student where sname='李军'); -- 42. 查询所有选修'计算机导论'课程的'男'同学的成绩表 -- select * from student where ssex = '男'; -- select * from course where cname ='计算机导论'; select * from score where cno=(select cno from course where cname ='计算机导论') and sno in(select sno from student where ssex = '男'); -- 43. 假设使用了以下命令建立了一个grade表 CREATE TABLE grade( low INT(3), upp INT(3), grade CHAR(1) ); INSERT INTO grade VALUES(90,100,'A'); INSERT INTO grade VALUES(80,89,'B'); INSERT INTO grade VALUES(70,79,'c'); INSERT INTO grade VALUES(60,69,'D'); INSERT INTO grade VALUES(0,59,'E'); -- 查询所有同学的sno , cno 和grade列 select sno,cno,grade from score,grade where degree between low and upp; -- 连接查询 -- person表 id ,name ,cardId CREATE TABLE person( id int, name VARCHAR(20), cardId int ); -- card表 card,name CREATE TABLE card( id int, name VARCHAR(20) ); INSERT INTO card VALUES (1,'饭卡'); INSERT INTO card VALUES (2,'建行卡'); INSERT INTO card VALUES (3,'农行卡'); INSERT INTO card VALUES (4,'工商卡'); INSERT INTO card VALUES (5,'邮政卡'); INSERT INTO person VALUES (1,'张三',1); INSERT INTO person VALUES (2,'李四',3); INSERT INTO person VALUES (3,'王五',6); -- 内连接(两张表中数据通过某个字段相等查询出相关记录数据) -- inner join 或者 join select * from person inner join card on person.cardId = card.id; -- 外连接 -- 1.左连接 left join 或者 left outer join -- 左边表里面的所有数据取出来，右边表数据如果有则显示，没有则显示NULL select * from person left join card on person.cardId = card.id; -- 2.右连接 right join 或者 right outer join -- 右边表里面的所有数据取出来，右边表数据如果有则显示，没有则显示NULL select * from person right join card on person.cardId = card.id; -- 3.完全外连接 full join 或者 full outer join(mysql不支持full join) -- mysql实现就是左右连接取并集 select * from person left join card on person.cardId = card.id union select * from person right join card on person.cardId = card.id;