MySQL查问练习一
1、查问student表中的所有记录；
mysql> select * from student;+-----+-----------+------+---------------------+-------+| sno | sname | ssex | sbirthday | class |+-----+-----------+------+---------------------+-------+| 101 | 曾华 | 男 | 1977-09-01 00:00:00 | 95033 || 102 | 匡明 | 男 | 1975-10-02 00:00:00 | 95031 || 103 | 王丽 | 女 | 1976-01-23 00:00:00 | 95033 || 104 | 李军 | 男 | 1976-02-20 00:00:00 | 95033 || 105 | 王芳 | 女 | 1975-02-10 00:00:00 | 95031 || 106 | 陆君 | 男 | 1974-06-03 00:00:00 | 95031 || 107 | 王尼玛 | 男 | 1974-06-03 00:00:00 | 95031 || 108 | 张全蛋 | 男 | 1974-06-03 00:00:00 | 95031 || 109 | 赵铁柱 | 男 | 1974-06-03 00:00:00 | 95031 |+-----+-----------+------+---------------------+-------+9 rows in set (0.00 sec)2、查问student表中的所有记录的sname，ssex，class列；
mysql> select sname, ssex, class from student;+-----------+------+-------+| sname | ssex | class |+-----------+------+-------+| 曾华 | 男 | 95033 || 匡明 | 男 | 95031 || 王丽 | 女 | 95033 || 李军 | 男 | 95033 || 王芳 | 女 | 95031 || 陆君 | 男 | 95031 || 王尼玛 | 男 | 95031 || 张全蛋 | 男 | 95031 || 赵铁柱 | 男 | 95031 |+-----------+------+-------+9 rows in set (0.00 sec)3、查问老师所有的单位即不反复的depart列；
关键字distinct排除反复。
mysql> select distinct depart from teacher;+-----------------+| depart |+-----------------+| 计算机系 || 电子工程系 |+-----------------+2 rows in set (0.01 sec)4、查问score表中问题在60到80之间的所有记录；
查问区间：
形式一：between...and...
mysql> select * from score where degree between 60 and 80;+-----+-------+--------+| sno | cno | degree |+-----+-------+--------+| 105 | 3-245 | 75 || 105 | 6-166 | 79 || 109 | 3-105 | 76 || 109 | 3-245 | 68 |+-----+-------+--------+4 rows in set (0.00 sec)形式二：间接应用运算符比拟；
mysql> select * from score where degree > 60 and degree < 80;+-----+-------+--------+| sno | cno | degree |+-----+-------+--------+| 105 | 3-245 | 75 || 105 | 6-166 | 79 || 109 | 3-105 | 76 || 109 | 3-245 | 68 |+-----+-------+--------+4 rows in set (0.00 sec)5、查问score表中问题为85，86或88的记录；
示意或着关系的查问；in关键字。
mysql> select * from score where degree in(85,86,88);+-----+-------+--------+| sno | cno | degree |+-----+-------+--------+| 103 | 3-245 | 86 || 103 | 6-166 | 85 || 105 | 3-105 | 88 |+-----+-------+--------+3 rows in set (0.00 sec)6、查问student表中“95031”班或者性别为女的记录；
关键字or，能够是不同的字段
mysql> select * from student where class='95031' or ssex='女';+-----+-----------+------+---------------------+-------+| sno | sname | ssex | sbirthday | class |+-----+-----------+------+---------------------+-------+| 102 | 匡明 | 男 | 1975-10-02 00:00:00 | 95031 || 103 | 王丽 | 女 | 1976-01-23 00:00:00 | 95033 || 105 | 王芳 | 女 | 1975-02-10 00:00:00 | 95031 || 106 | 陆君 | 男 | 1974-06-03 00:00:00 | 95031 || 107 | 王尼玛 | 男 | 1974-06-03 00:00:00 | 95031 || 108 | 张全蛋 | 男 | 1974-06-03 00:00:00 | 95031 || 109 | 赵铁柱 | 男 | 1974-06-03 00:00:00 | 95031 |+-----+-----------+------+---------------------+-------+7 rows in set (0.00 sec)7、以class降序查问student表的所有记录；
排序的关键字：order by...
降序：desc
升序：asc默认能够不写
mysql> select * from student order by class desc;+-----+-----------+------+---------------------+-------+| sno | sname | ssex | sbirthday | class |+-----+-----------+------+---------------------+-------+| 101 | 曾华 | 男 | 1977-09-01 00:00:00 | 95033 || 103 | 王丽 | 女 | 1976-01-23 00:00:00 | 95033 || 104 | 李军 | 男 | 1976-02-20 00:00:00 | 95033 || 102 | 匡明 | 男 | 1975-10-02 00:00:00 | 95031 || 105 | 王芳 | 女 | 1975-02-10 00:00:00 | 95031 || 106 | 陆君 | 男 | 1974-06-03 00:00:00 | 95031 || 107 | 王尼玛 | 男 | 1974-06-03 00:00:00 | 95031 || 108 | 张全蛋 | 男 | 1974-06-03 00:00:00 | 95031 || 109 | 赵铁柱 | 男 | 1974-06-03 00:00:00 | 95031 |+-----+-----------+------+---------------------+-------+9 rows in set (0.00 sec)8、以cno升序、degree降序查问score表中的所有记录；
以逗号隔开。
mysql> select * from score order by cno asc,degree desc;+-----+-------+--------+| sno | cno | degree |+-----+-------+--------+| 103 | 3-105 | 92 || 105 | 3-105 | 88 || 109 | 3-105 | 76 || 103 | 3-245 | 86 || 105 | 3-245 | 75 || 109 | 3-245 | 68 || 103 | 6-166 | 85 || 109 | 6-166 | 81 || 105 | 6-166 | 79 |+-----+-------+--------+9 rows in set (0.00 sec)9、查问’95031‘班的所有人数；
统计：count
mysql> select * from student;+-----+-----------+------+---------------------+-------+| sno | sname | ssex | sbirthday | class |+-----+-----------+------+---------------------+-------+| 101 | 曾华 | 男 | 1977-09-01 00:00:00 | 95033 || 102 | 匡明 | 男 | 1975-10-02 00:00:00 | 95031 || 103 | 王丽 | 女 | 1976-01-23 00:00:00 | 95033 || 104 | 李军 | 男 | 1976-02-20 00:00:00 | 95033 || 105 | 王芳 | 女 | 1975-02-10 00:00:00 | 95031 || 106 | 陆君 | 男 | 1974-06-03 00:00:00 | 95031 || 107 | 王尼玛 | 男 | 1974-06-03 00:00:00 | 95031 || 108 | 张全蛋 | 男 | 1974-06-03 00:00:00 | 95031 || 109 | 赵铁柱 | 男 | 1974-06-03 00:00:00 | 95031 |+-----+-----------+------+---------------------+-------+9 rows in set (0.00 sec)10、查问score表中的最高分的学生学号和课程号；
子查问或者排序；
复合语句。
mysql> select sno,cno from score where degree=(select max(degree) from score);+-----+-------+| sno | cno |+-----+-------+| 103 | 3-105 |+-----+-------+1 row in set (0.00 sec)步骤：
①、找到最高分
select max(degree) from score;②、找最高分的sno和cno
mysql> select sno,cno from score where degree=(select max(degree) from score);③、排序的做法：
存在肯定缺点，比方问题最高有多个分雷同。
mysql> select sno,cno,degree from score order by degree desc;+-----+-------+--------+| sno | cno | degree |+-----+-------+--------+| 103 | 3-105 | 92 || 105 | 3-105 | 88 || 103 | 3-245 | 86 || 103 | 6-166 | 85 || 109 | 6-166 | 81 || 105 | 6-166 | 79 || 109 | 3-105 | 76 || 105 | 3-245 | 75 || 109 | 3-245 | 68 |+-----+-------+--------+9 rows in set (0.00 sec)limit第一个数字示意从第几条开始，第二个数字示意查多少条。
mysql> select sno,cno,degree from score order by degree desc limit 0,1;+-----+-------+--------+| sno | cno | degree |+-----+-------+--------+| 103 | 3-105 | 92 |+-----+-------+--------+1 row in set (0.00 sec)11、查问每门课的均匀问题；
avg()
查问一门的均匀问题；
mysql> select avg(degree) from score where cno='3-105';+-------------+| avg(degree) |+-------------+| 85.3333 |+-------------+1 row in set (0.00 sec)一个语句中查问多门课程的均匀问题；...group by...分组
mysql> select cno,avg(degree) from score group by cno;+-------+-------------+| cno | avg(degree) |+-------+-------------+| 3-105 | 85.3333 || 3-245 | 76.3333 || 6-166 | 81.6667 |+-------+-------------+3 rows in set (0.00 sec) 12、查问score表中至多有两名学生选修的并以3结尾的课程的均匀问题
分组条件与含糊查问
mysql> select cno,avg(degree),count(*) from score group by cno having count(cno)>=2 and cno like '3%';+-------+-------------+----------+| cno | avg(degree) | count(*) |+-------+-------------+----------+| 3-105 | 85.3333 | 3 || 3-245 | 76.3333 | 3 |+-------+-------------+----------+2 rows in set (0.00 sec)分组条件：group by...having...
含糊查问：like
13、查问分数大于70小于80的sno列
范畴查问的两种形式
形式一：
mysql> select sno,degree from score where degree>70 and degree<90;+-----+--------+| sno | degree |+-----+--------+| 103 | 86 || 103 | 85 || 105 | 88 || 105 | 75 || 105 | 79 || 109 | 76 || 109 | 81 |+-----+--------+7 rows in set (0.00 s形式二：between...and...
mysql> select sno,degree from score where degree between 70 and 90;+-----+--------+| sno | degree |+-----+--------+| 103 | 86 || 103 | 85 || 105 | 88 || 105 | 75 || 105 | 79 || 109 | 76 || 109 | 81 |+-----+--------+7 rows in set (0.00 sec)14、查问所有学生的cno，sname和degree
多表查问
mysql> 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 |+-----------+-------+--------+9 rows in set (0.00 sec)15、查问所有学生的sno，cname和degree列
mysql> select sno,cname,degree from course,score where course.cno=score.cno;+-----+-----------------+--------+| sno | cname | degree |+-----+-----------------+--------+| 103 | 计算机导论 | 92 || 103 | 操作系统 | 86 || 103 | 数字电路 | 85 || 105 | 计算机导论 | 88 || 105 | 操作系统 | 75 || 105 | 数字电路 | 79 || 109 | 计算机导论 | 76 || 109 | 操作系统 | 68 || 109 | 数字电路 | 81 |+-----+-----------------+--------+9 rows in set (0.05 sec)16、所有学生的sname，cname，degree列
三表关联查问，通过独特字段的相等关系来分割在一起。
mysql> select sname,cname,degree from student,course,scorewhere student.sno=score.sno and course.cno = score.cno;+-----------+-----------------+--------+| sname | cname | degree |+-----------+-----------------+--------+| 王丽 | 计算机导论 | 92 || 王丽 | 操作系统 | 86 || 王丽 | 数字电路 | 85 || 王芳 | 计算机导论 | 88 || 王芳 | 操作系统 | 75 || 王芳 | 数字电路 | 79 || 赵铁柱 | 计算机导论 | 76 || 赵铁柱 | 操作系统 | 68 || 赵铁柱 | 数字电路 | 81 |+-----------+-----------------+--------+9 rows in set (0.00 sec)17、查问95031班学生每门课的平均分
子查问加分组求平均分
mysql> select cno,avg(degree) -> from score -> where sno in(select sno from student where class = '95031') -> group by cno;+-------+-------------+| cno | avg(degree) |+-------+-------------+| 3-105 | 82.0000 || 3-245 | 71.5000 || 6-166 | 80.0000 |+-------+-------------+3 rows in set (0.08 sec)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');mysql> select * from score where cno = '3-105'and degree >(select degree from score where sno='109'and cno ='3-105');+-----+-------+--------+| sno | cno | degree |+-----+-------+--------+| 103 | 3-105 | 92 || 105 | 3-105 | 88 |+-----+-------+--------+2 rows in set (0.00 sec)19查问问题高于学号109、课程号为3-105的问题的所有记录
mysql> SELECT * from score where degree > (SELECT degree FROM score where sno = '109' and cno = '3-105' );+-----+-------+--------+| sno | cno | degree |+-----+-------+--------+| 103 | 3-105 | 92 || 103 | 3-245 | 86 || 103 | 6-166 | 85 || 105 | 3-105 | 88 || 105 | 6-166 | 79 || 109 | 6-166 | 81 |+-----+-------+--------+6 rows in set (0.00 sec)20、查问和学号为108、101的同学同年出世的所有学生的sno、sname、和sbirthday列
year()+in关键字的子查问
mysql> SELECT sno,sname, sbirthday from student -> where YEAR(sbirthday) in (SELECT year(sbirthday) from student where sno in( 101,108));+-----+-----------+---------------------+| sno | sname | sbirthday |+-----+-----------+---------------------+| 101 | 曾华 | 1977-09-01 00:00:00 || 106 | 陆君 | 1974-06-03 00:00:00 || 107 | 王尼玛 | 1974-06-03 00:00:00 || 108 | 张全蛋 | 1974-06-03 00:00:00 || 109 | 赵铁柱 | 1974-06-03 00:00:00 |+-----+-----------+---------------------+5 rows in set (0.00 sec)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='张旭'));mysql> SELECT * FROM score WHERE cno =(SELECT cno from course where tno =(SELECT tno FROM teacher WHERE tname='张旭'));+-----+-------+--------+| sno | cno | degree |+-----+-------+--------+| 103 | 6-166 | 85 || 105 | 6-166 | 79 || 109 | 6-166 | 81 |+-----+-------+--------+3 rows in set (0.00 sec)22、查问选修某课程的同学人数多于5人的老师姓名
多表查问+子查问
步骤：① 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));mysql> SELECT tname from teacher WHERE tno = (select tno from course where cno=(SELECT cno from score GROUP BY cno HAVING count(*)>5));+--------+| tname |+--------+| 王萍 |+--------+1 row in set (0.00 sec)23、查问95033班95031班全体学生的记录
in示意或者关系
SELECT * from student WHERE class IN(95033,95031);24、查问存在问题有85分以上问题的课程cno
select cno from score where degree>85;mysql> SELECT cno,degree FROM score WHERE degree>85;+-------+--------+| cno | degree |+-------+--------+| 3-105 | 90 || 3-105 | 91 || 3-105 | 92 || 3-245 | 86 || 3-105 | 88 || 3-105 | 88 |+-------+--------+6 rows in set (0.00 sec)