查询练习

1. 查询所有记录

select * from student；

2.查询指定的字段

select sname,ssex,class from student;

字段名：sname，ssex，class
mysql> select sname,ssex,class from student;
±----------±-----±------+
| sname | ssex | class |
±----------±-----±------+
| 李军 | 男 | 95033 |
| 王尼玛 | 男 | 95031 |
| 陆君 | 男 | 95031 |
| 张全蛋 | 男 | 95031 |
| 匡明 | 男 | 95031 |
| 赵铁柱 | 男 | 95031 |
| 王丽 | 女 | 95033 |
| 曾华 | 男 | 95033 |
| 王芳 | 女 | 95031 |
±----------±-----±------+
9 rows in set (0.00 sec)

3.排除重复

select distinct depart from teacher;

distinct ：去重
mysql> select distinct depart from teacher;
±----------------+
| depart |
±----------------+
| 计算机系 |
| 电子工程系 |
±----------------+
2 rows in set (0.02 sec)

4.查询区间值（60-80）

方法1：

select * from score where degree between 60 and 80；

方法2
运算符比较

select * from score where degree > 60 and degree < 80; 

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.查询特定的的值（85,86,88）

select * from score where degree in(85,86,88);

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.查询表中‘95031’班或性别为女的记录

select * from student where class='95031' or ssex='女';

±----±----------±-----±--------------------±------+
| sno | sname | ssex | sbirthday | class |
±----±----------±-----±--------------------±------+
| 102 | 王尼玛 | 男 | 1974-06-03 00:00:00 | 95031 |
| 103 | 陆君 | 男 | 1974-06-03 00:00:00 | 95031 |
| 104 | 张全蛋 | 男 | 1974-06-03 00:00:00 | 95031 |
| 105 | 匡明 | 男 | 1975-10-02 00:00:00 | 95031 |
| 106 | 赵铁柱 | 男 | 1974-06-03 00:00:00 | 95031 |
| 107 | 王丽 | 女 | 1976-01-23 00:00:00 | 95033 |
| 109 | 王芳 | 女 | 1975-02-10 00:00:00 | 95031 |
±----±----------±-----±--------------------±------+
7 rows in set (0.01 sec)

7.升序，降序

1.升序

select *from student order by class asc;

asc 升序，可不写。
mysql> select *from student order by class asc;
±----±----------±-----±--------------------±------+
| sno | sname | ssex | sbirthday | class |
±----±----------±-----±--------------------±------+
| 102 | 王尼玛 | 男 | 1974-06-03 00:00:00 | 95031 |
| 103 | 陆君 | 男 | 1974-06-03 00:00:00 | 95031 |
| 104 | 张全蛋 | 男 | 1974-06-03 00:00:00 | 95031 |
| 105 | 匡明 | 男 | 1975-10-02 00:00:00 | 95031 |
| 106 | 赵铁柱 | 男 | 1974-06-03 00:00:00 | 95031 |
| 109 | 王芳 | 女 | 1975-02-10 00:00:00 | 95031 |
| 101 | 李军 | 男 | 1976-00-20 00:00:00 | 95033 |
| 107 | 王丽 | 女 | 1976-01-23 00:00:00 | 95033 |
| 108 | 曾华 | 男 | 1977-09-01 00:00:00 | 95033 |
±----±----------±-----±--------------------±------+
9 rows in set (0.00 sec)

2.降序

select * from student by class desc;

desc降序
mysql> select *from student order by class desc;
±----±----------±-----±--------------------±------+
| sno | sname | ssex | sbirthday | class |
±----±----------±-----±--------------------±------+
| 101 | 李军 | 男 | 1976-00-20 00:00:00 | 95033 |
| 107 | 王丽 | 女 | 1976-01-23 00:00:00 | 95033 |
| 108 | 曾华 | 男 | 1977-09-01 00:00:00 | 95033 |
| 102 | 王尼玛 | 男 | 1974-06-03 00:00:00 | 95031 |
| 103 | 陆君 | 男 | 1974-06-03 00:00:00 | 95031 |
| 104 | 张全蛋 | 男 | 1974-06-03 00:00:00 | 95031 |
| 105 | 匡明 | 男 | 1975-10-02 00:00:00 | 95031 |
| 106 | 赵铁柱 | 男 | 1974-06-03 00:00:00 | 95031 |
| 109 | 王芳 | 女 | 1975-02-10 00:00:00 | 95031 |
±----±----------±-----±--------------------±------+
9 rows in set (0.00 sec)

8.以cno升序、degree降序

select * from score order by cno asc,degree desc;

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.统计班的人数

select count(*) from student where class='95031';

mysql> select count() from student where class=‘95031’;
±---------+
| count() |
±---------+
| 6 |
±---------+
1 row in set (0.00 sec)

10.最高分

select sno,cno from score where degree=(select max(degree) from 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)

1.找最高分

select max(degree) from score;

2.找最高分的 sno 和 cno

select sno,cno from score where degree=(select max(degree) from score);

3.排序

select sno,cno degree from score order by degree;

±----±-------+
| sno | degree |
±----±-------+
| 103 | 3-105 |
| 105 | 3-105 |
| 109 | 3-105 |
| 103 | 3-245 |
| 105 | 3-245 |
| 109 | 3-245 |
| 103 | 6-166 |
| 105 | 6-166 |
| 109 | 6-166 |
±----±-------+

select sno,cno degree from score order by degree limit 0,1;

±----±-------+
| sno | degree |
±----±-------+
| 103 | 3-105 |
±----±-------+
limit 后的第一位数字 “0” 表示开始的位置， 第二位数字 ‘1’ 表示多少条数据

11.查询每门课的平均成绩

使用：avg()

select avg(degree) from score where cno='3-105';
select avg(degree) from score where cno='3-245';
select avg(degree) from score where cno='6-166';
select avg(degree) from score where cno='3-105';

±------------+
| avg(degree) |
±------------+
| 85.3333 |
±------------+

一行代码实现上述几条代码的效果
分组：group by

select avg(degree) from score group by cno;

±------------+
| avg(degree) |
±------------+
| 85.3333 |
| 76.3333 |
| 81.6667 |
±------------+

12.查询score表中至少有2名学生选修的并以3开头的课程平均数

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 |
±------±------------±---------+

count(*) 出现的次数
cno like ‘3%’ 使用通配符在 cno 字段筛选 3 开头的数据
count（cno）>=2 判断出现两次次以上的数据

17.查询分数大于70，小于90的sno列