首页 / MYSQL / mysql 相关练习题
mysql 相关练习题
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了mysql 相关练习题,小编现在分享给大家,供广大互联网技能从业者学习和参考。文章包含6513字,纯文字阅读大概需要10分钟。
内容图文
![mysql 相关练习题](/upload/InfoBanner/zyjiaocheng/908/8048f169b7574be480ec2d3335b6d5c5.jpg)
/* 自己查询自己 把一张表看成是两张表。 表的设计。 SELECT * FROM depart; SELECT d1. NAME '部门', d2. NAME '分部门' FROM depart d1 INNER JOIN depart d2 ON d1.id = d2.did; SELECT d1. NAME, d2. NAME FROM depart d1 INNER JOIN depart d2 ON d1.did = d2.id; SELECT s.name,s.age,g.gname ,z.id from student s INNER JOIN grade g INNER JOIN zhongjian z ON z.tid=z.gid; */ -- 学生表 create table students( sno VARCHAR(3) not NULL, sname VARCHAR(4) not NULL, ssex VARCHAR(2) not NULL, sbirthday datetime, class VARCHAR(5)) -- CREATE table courses( cno VARCHAR(5) not null, cname varchar(10) not null, tno VARCHAR(10) not null) CREATE table scores ( sno VARCHAR(3) not null, cno VARCHAR(5) not null, degree NUMERIC(10,1) not null) -- 老师表 create table teachers( tno VARCHAR(3) not null, tname VARCHAR(4) not null, tsex VARCHAR(2) not null, tbirthday datetime not null,prof varchar(6), depart VARCHAR (10) not null) insert into students (sno,sname,ssex,sbirthday,class)VALUES(108,'曾华','男','1977-09-01',95033); insert into students (sno,sname,ssex,sbirthday,class)VALUES(105,'匡明','男','1975-10-02',95031); insert into students (sno,sname,ssex,sbirthday,class)VALUES(107,'王丽','女','1976-01-23',95033); insert into students (sno,sname,ssex,sbirthday,class)VALUES(107,'李军','男','1976-01-23',95033); insert into students (sno,sname,ssex,sbirthday,class)VALUES(107,'王芳','女','1975-02-10',95031); insert into students (sno,sname,ssex,sbirthday,class)VALUES(107,'陆军','男','1974-06-03',95031); insert into courses(cno ,cname,tno)VALUES('3-105','计算机导论',825); insert into courses(cno ,cname,tno)VALUES('3-105','计算机导论',825); insert into courses(cno ,cname,tno)VALUES('3-105','计算机导论',825); insert into courses(cno ,cname,tno)VALUES('3-105','计算机导论',825); insert into scores(sno ,cno,degree)VALUES (103,'3-245',86); insert into scores(sno ,cno,degree)VALUES (103,'3-245',86); insert into scores(sno ,cno,degree)VALUES (103,'3-245',86); insert into scores(sno ,cno,degree)VALUES (103,'3-245',86); insert into scores(sno ,cno,degree)VALUES (103,'3-245',86); insert into scores(sno ,cno,degree)VALUES (103,'3-245',86); insert into scores(sno ,cno,degree)VALUES (103,'3-245',86); insert into scores(sno ,cno,degree)VALUES (103,'3-245',86); insert into scores(sno ,cno,degree)VALUES (103,'3-245',86); insert into scores(sno ,cno,degree)VALUES (103,'3-245',86); insert into scores(sno ,cno,degree)VALUES (103,'3-245',86); insert into scores(sno ,cno,degree)VALUES (103,'3-245',86); insert into teachers(tno,tname,tsex,tbirthday,prof,depart) VALUES(804,'李晨','男','1958-12-02','副教授','计算机系'); -- 2791693327 select * from students; select * from courses; select * from scores; select * from teachers; -- 1.查询student表中的所有记录的sname ssex, class 列? SELECT sname, ssex,class from students; -- 2.查询教师所有的单位 既不重复的depart 列? 去重 关键字 distinct SELECT DISTINCT depart from teachers; -- 3.查询student 表的所有记录? SELECT * from students; -- 4.查询score表中成绩在60 到 80之间的所以记录? between SELECT * from scores where degree BETWEEN 60 and 80; -- 5.查询score中成绩 为85 86 88 的记录? in ()关键字 select * from scores WHERE degree in(85,86,88); -- 6. 查询students 表中 95031 班 或者 性别为女 的同学记录?or 关键字 SELECT * from students WHERE class='95031' or ssex='女'; -- 7.以class 降序查询 students 表的所以记录? 关键字 ORDER BY DESC 降序! SELECT * from students ORDER BY class DESC; -- 8.以 con 升序,degree降序查询 score 表的所有记录。order by 默认状态下是 升序 select * from scores ORDER BY sno, degree DESC; -- 9.查询‘95031’ 班的学生人数 分组查询 SELECT COUNT(expr) AS ‘名字’ from 表 WHERE 条件(xx=xx;)! SELECT COUNT(1) AS '95031班级的学生数量' from students where class='95031'; -- 10.查询score 表中的最高分的学生学号和课程号。分数降序查询 由高到低 分组时只取第一页!(联想子查询 ,一个查询的结果是另一个查询的条件) -- 1.先查询score表中 的最高分,然后由此得到 最高分的学生学号和课程号。 SELECT cno,sno from scores ORDER BY degree DESC LIMIT 1;-- 这种简单快捷 SELECT max(degree) from scores;-- 利用聚合函数 max(列名)获取最大值 SELECT cno,sno from scores where degree=(SELECT max(degree) from scores); -- 这种相对麻烦。 -- 简单的聚合函数 最大值 最小值 平均值 求和值 SELECT max(class) from students; select avg(class) from students; SELECT min(class) from students; SELECT sum(class) from students; SELECT COUNT(class)FROM students;-- 对表中数据的的个数求和。 -- 11.查询3-105 号课程的平均分 SELECT avg(degree) from scores where cno='3-105'; -- 12.查score表 至少有5名学生选修的并以3开头的平均分数 LIKE ‘x%’ 模糊查询 -- 结果集是分组查询后,再次进行筛选,不能使用where, 分组后再次过滤,关键字 having SELECT cno,avg(degree) FROM scores WHERE cno like '3%' GROUP BY cno HAVING COUNT(sno)>=5; SELECT cno,avg(degree) FROM scores WHERE cno like '3%' GROUP BY cno HAVING count(sno)>=5; -- 13.在表scors 查询最低分大于70,最高分小于90的sno列 分组查询 group by 列名 -- 结果集是分组查询后,再次进行筛选,不能使用where, 分组后再次过滤,关键字 having SELECT sno FROM scores GROUP BY sno HAVING max(degree)<90 and min(degree)>70 LIMIT 5; -- 14.查询所有学生的sname cno degree 列 INNER JOIN 显示内连接 on 是限制条件 SELECT sname,cno,degree FROM students INNER JOIN scores ON(students.sno=scores.sno)ORDER BY sname; SELECT sname,cno,degree FROM students INNER JOIN scores on(students.sno=scores.sno)ORDER BY sname; SELECT sname,cno,degree from students inner JOIN scores on (students.sno=scores.sno) order by sname; -- 15.查询所有学生的sname cname 和 degree列。 三张表 一起查。 INNER JOIN 用两次 限制条件是 外键 SELECT sname,cname,degree FROM students INNER JOIN scores ON(students.sno=scores.sno)INNER JOIN courses ON(scores.cno=courses.cno)ORDER BY sname; SELECT sname,cname,degree FROM students INNER JOIN scores ON(students.sno=scores.sno)INNER JOIN courses ON(scores.cno=courses.cno)ORDER BY sname; -- 16.查询所有学生的sno cname degeree 列 排序查询 ORDER BY 关键字 默认是升序 ASC 降序 DESC SELECT sno,cname,degree FROM scores INNER JOIN courses ON(courses.cno=scores.cno)ORDER BY sno DESC; SELECT sno,cname,degree FROM scores INNER JOIN courses ON(courses.cno=scores.cno)ORDER BY sno; SELECT sno,cname,degree FROM scores INNER JOIN courses ON(courses.cno=scores.cno)ORDER BY sno DESC; -- 17.查询 ‘95033’班所选课程的的平均分。分组查询 GROUP BY 被分组的列名 -- 必须跟随聚合函数 SELECT cname,avg(degree) from students INNER JOIN scores ON(students.sno=scores.sno)INNER JOIN courses ON(courses.cno=scores.cno)WHERE class='95033' GROUP BY courses.cno ORDER BY cname DESC; SELECT cname,avg(degree) FROM students INNER JOIN scores ON(students.sno=scores.sno)INNER JOIN courses ON(courses.cno=scores.cno) WHERE class='95033' GROUP BY courses.cno ORDER BY cname desc;
内容总结
以上是互联网集市为您收集整理的mysql 相关练习题全部内容,希望文章能够帮你解决mysql 相关练习题所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错,欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明:本文内容由互联网用户自发贡献,该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 gblab@vip.qq.com 举报,一经查实,本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。