MySQL 查询练习
1、创建表
1.1 创建表
– 1、学生表
– student
– 学号 姓名 性别 出生年月日 所在班级
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
`sno` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
`sname` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
`ssex` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
`sbirthday` datetime(0) NULL DEFAULT NULL,
`class` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
PRIMARY KEY (`sno`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
– 2、课程表
– course
– 课程号 课程名称 教师编号
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course` (
`cno` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
`cname` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
`tno` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
PRIMARY KEY (`cno`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
– 3、成绩表
– score
– 学号 课程号 成绩
DROP TABLE IF EXISTS `score`;
CREATE TABLE `score` (
`sno` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
`cno` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
`degree` decimal(10, 0) NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
– 4、 教师表
– teacher
– 教师编号 教师名字 教师性别 出生年月日 职称 所在部门
DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher` (
`tno` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
`tname` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
`tsex` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
`tbirthday` datetime(0) NULL DEFAULT NULL,
`prof` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
`depart` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
PRIMARY KEY (`tno`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
1.2 创建表截图
图表截图：
2、 往数据表中添加数据
2.1 添加学生信息
INSERT INTO `student` VALUES ('101', '张三', '男', '1995-01-23 00:00:00', '95033');
INSERT INTO `student` VALUES ('102', '李四', '男', '1995-08-13 00:00:00', '95031');
INSERT INTO `student` VALUES ('103', '王丽', '女', '1976-01-23 00:00:00', '95033');
INSERT INTO `student` VALUES ('104', '李军', '男', '1976-02-20 00:00:00', '95033');
INSERT INTO `student` VALUES ('105', '王芳', '女', '1975-02-10 00:00:00', '95031');
INSERT INTO `student` VALUES ('106', '陆军', '男', '1974-06-03 00:00:00', '95031');
INSERT INTO `student` VALUES ('107', '王尼玛', '男', '1976-02-20 00:00:00', '95033');
INSERT INTO `student` VALUES ('108', '张全蛋', '男', '1975-02-10 00:00:00', '95031');
INSERT INTO `student` VALUES ('109', '赵铁柱', '男', '1974-06-03 00:00:00', '95031');
INSERT INTO `student` VALUES ('110', '张飞', '男', '1974-06-03 00:00:00', '95038');
2.2 添加教师表信息
INSERT INTO `teacher` VALUES ('804', '李诚', '男', '1958-12-02 00:00:00', '副教授', '计算机系');
INSERT INTO `teacher` VALUES ('825', '王萍', '女', '1972-05-05 00:00:00', '助教', '计算机系');
INSERT INTO `teacher` VALUES ('831', '刘冰', '女', '1977-08-14 00:00:00', '助教', '电子工程系');
INSERT INTO `teacher` VALUES ('856', '张旭', '男', '1969-03-12 00:00:00', '讲师', '电子工程系');
2.3 添加课程表
select * from course
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');
2.4 添加成绩
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);
3 查询操作
– 1.查询student表中所有的记录
select * from student;
– 2.查询student表中所有记录的sname,ssex和class列
select sname,ssex,class from student
– 3.查询教师所有的单位但是不重复的depart列
select distinct depart from teacher;
– 4.查询score表中成绩在60-80之间所有的记录(degree)
select * from score;
update score set degree='75' where sno='105' and cno='3-245';
select * from score where degree between 60 and 80;
select * from score where degree>=60 and degree<=80;
– 5.查询score表中成绩为85, 86, 或者88的记录(sc_degree)
select * from score where degree in(85,86,88);
– 6.查询student表中’95031’班或者性别为’女’的同学记录
select * from student;
select * from student where ssex='女' or class='95031';
– 7.以class降序查询student表中所有的记录
select * from student order by class desc
– 8.以cno升序、degree降序查询score表中所有的数据
select * from score order by cno asc,degree desc;
– 9.查询’95031’班的学生人数
select * from student;
select count(*) from student where class='95031';
– 10.查询score表中的最高分数的学生号和课程号.(子查询或者排序)
select * from score;
select sno,cno from score where degree=(select max(degree) from score);
– 11.查询每门课的平均成绩
select * from course;
select * from score;
select cno,avg(degree) from score where cno='3-105';
select cno,avg(degree) from score group by cno
– 12,查询score表中至少有2名学生选修的,并且以3开头的课程的平均分
select * from score;
select cno,avg(degree),count(*) from score group by cno having count(cno)>=2 and cno like '3%';
– 13.查询分数大于70但是小于90的sno列:
select * from score;
select sno,degree from score where degree>70 and degree<90;
select * from student;
select * from teacher;
select * from course;
– 14.查询所有的学生 sname , cno, degree列
select s.sname,sc.cno,sc.degree from student s,score sc where s.sno=sc.sno;
select s.sname,sc.cno,sc.degree from student s left join score sc on s.sno=sc.sno;
– 15.查询所有学生的sno, cname, degree列
select sc.sno,c.cname,sc.degree from score sc,course c where sc.cno=c.cno;
– 16.查询所有的学生 sname , cname, degree列
select sname,cname,degree from student,course,score
where score.sno=student.sno and score.cno=course.cno;
– 17.查询班级是’95031’班学生每门课的平均分
select * from student where class='95031';
select cno,avg(degree) from score,student where score.sno=student.sno and class='95031' group by cno;
– 18.查询选修"3-105"课程的成绩高于’109’号同学’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 max(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 tno from course where cno=(select cno from score group by cno having count(*)>5);
select tname from teacher where tno=(select tno from course where cno=(select cno from score group by cno having count(*)>5));
– 23.查询95033班和95031班全体学生的记录
select * from student;
insert into student values('110','张飞','男','1974-06-03','95038');
select * from student where class in ('95038','95031');
– 24.查询存在有85分以上成绩的课程cno
select cno from score where degree>85 group by cno;
– 25.查出所有’计算机系’ 教师所教课程的成绩表
select tno from teacher where depart='计算机系';
select cno 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.查询’计算机系’与’电子工程系’ 不同职称的教师的tname和prof
select prof from teacher where depart='电子工程系';
select tname,prof from teacher where depart='计算机系' and prof not in (select prof from teacher where depart='电子工程系') #查询
union -- 求并集
select tname,prof from teacher where depart='电子工程系' and prof not in (select prof from teacher where depart='计算机系');
– 27、查询选修编号为"3-105"课程且成绩至少高于选修编号为’3-245’同学的cno,sno和degree,并且按照degree从高到地次序排序
select degree from score where cno='3-245';
select cno,sno,degree 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
select degree from score where cno='3-245';
select cno,sno,degree from score where cno='3-105' and degree>all(select degree from score where cno='3-245');
– 29. 查询所有教师和同学的 name ,sex, birthday
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 avg(degree) from score group by cno;
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(ssex)>=2;
– 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的值
select sbirthday from student;
select max(sbirthday),min(sbirthday) from student;
– 37.以班级号和年龄从大到小的顺序查询student表中的全部记录
select * from student order by class desc,sbirthday;
– 38.查询"男"教师 及其所上的课
select * from course where tno in (select tno from teacher where tsex='男');
– 39.查询最高分同学的sno、cno和degree;
select * from score where degree=(select max(degree) from score);
– 40. 查询和"李军"同性别的所有同学的sname
select ssex from student where 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 cno from course where cname='计算机导论';
select * from score where cno=(select cno from course where cname='计算机导论');
select score.* from score,student where ssex='男' and cno=(select cno from course where cname='计算机导论') and score.sno=student.sno;
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 * FROM GRADE;
select sno,cno,grade from score,grade where degree between low and upp;
4 进阶:显示学生名字和课程名称
4.1 – 创建两个表
-- person表
#id,name,cardId
create table person(
id int,
name varchar(20),
cardId int
);
-- card表
-- id, 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);
select * from person;
select * from card;
– 1、inner join 查询(内连接)：内连接查询其实就是两张表中的数据通过某个字段相对，查出相关记录的数据
select * from person inner join card on person.cardId=card.id;
-- 等价于
select * from person join card on person.cardId=card.id;
-- 也等价于
select * from person,card where person.cardId=card.id;
#内连接查询其实就是两张表中的数据通过某个字段相对，查出相关记录的数据
– 2、left join (左外连接)
select * from person left join card on person.cardId=card.id;
-- 等价于
select * from person left outer join card on person.cardId=card.id;
#左外连接，会把左边表里面的所有数据取出来，而右边中的数据，如果有相等的，就会显示出来；
#如果没有，就会补 NULL
– 3、right join (右外连接)
select * from person right join card on person.cardId=card.id;
-- 等价于
select * from person right outer join card on person.cardId=card.id;
#右外连接，会把右边表里面的所有数据取出来，而左边边中的数据，如果有相等的，就会显示出来；
#如果没有，就会补 NULL
select * from person;
select * from card;
– 4、full join (全外连接)
-- MySQL不支持 full join
select * from person full join card on person.cardId=card.id;
-- MySQL全外连接通过 左连接union右链接 实现
select * from person left join card on person.cardId=card.id
union
select * from person right join card on person.cardId=card.id;
请关注公众号，有惊喜哦！
扫一扫
944
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
