- List item 查询色score 中的成绩为87 ,86,88,的记录
select *from score where degree in(86,87,88);
mysql> select *from score where degree in(86,87,88);
+-----+-------+--------+
| sno | cno | degree |
+-----+-------+--------+
| 103 | 3-245 | 86 |
| 105 | 3-105 | 88 |
+-----+-------+--------+
2 rows in set (0.00 sec)
- 查询student中95031班或性别为女的同学记录。
select*from student where class=‘95031’ or sexx=‘F’
mysql> select*from student where class='95031' or sexx='F'
-> ;
+-----+------------+------+---------------------+--------+
| sno | sname | sexx | birthday | class |
+-----+------------+------+---------------------+--------+
| 101 | zenhua | F | 1977-09-01 00:00:00 | 95003 |
| 102 | kuangming | M | 1999-11-01 00:00:00 | 95031 |
| 104 | lijun | F | 1988-11-23 00:00:00 | 95033 |
| 106 | lujun | F | 1997-12-01 00:00:00 | 955033 |
| 107 | zhujun | F | 1737-09-08 00:00:00 | 955031 |
| 108 | zhujunqing | F | 1744-09-08 00:00:00 | 955033 |
| 109 | zhude | F | 1734-09-08 00:00:00 | 95033 |
+-----+------------+------+---------------------+--------+
7 rows in set (0.00 sec)
- 以cno升序 , degree 降许查询score中的记录
mysql> select *from score order by cno asc ,degree desc;
+-----+-------+--------+
| sno | cno | degree |
+-----+-------+--------+
| 103 | 3-105 | 92 |
| 105 | 3-105 | 88 |
| 102 | 3-105 | 79 |
| 109 | 3-105 | 76 |
| 108 | 3-105 | 70 |
| 103 | 3-245 | 86 |
| 105 | 3-245 | 75 |
| 109 | 3-245 | 68 |
| 102 | 3-245 | 9 |
+-----+-------+--------+
9 rows in set (0.00 sec)
```、
4. 查询95031班的人数, 统计count;
`mysql> select count(*) from student where class='95031';
+----------+
| count(*) |
+----------+
| 1 |
+----------+
1 row in set (0.00 sec)
`
5.查询score 中的最高分学生的学号和课程号;(排序i)
```bash
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)
mysql> select *from score;
+-----+-------+--------+
| sno | cno | degree |
+-----+-------+--------+
| 102 | 3-105 | 79 |
| 102 | 3-245 | 9 |
| 103 | 3-105 | 92 |
| 103 | 3-245 | 86 |
| 105 | 3-105 | 88 |
| 105 | 3-245 | 75 |
| 108 | 3-105 | 70 |
| 109 | 3-105 | 76 |
| 109 | 3-245 | 68 |
+-----+-------+--------+
9 rows in set (0.00 sec)
解释5号题目
1 找到最高分
select max(degree) from score;
2找到最高分的sno 和cno
select sno ,con from score where degree=(select max(degree) from score );
- 也可以用排序的方法找到最高分
首先把表按升序排列
mysql> seclect cno ,sno,degree from score order by degree
-> ;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'seclect cno ,sno,degree from score order by degree' at line 1
mysql> select cno,sno,degree from score order by degree;
+-------+-----+--------+
| cno | sno | degree |
+-------+-----+--------+
| 3-245 | 102 | 9 |
| 3-245 | 109 | 68 |
| 3-105 | 108 | 70 |
| 3-245 | 105 | 75 |
| 3-105 | 109 | 76 |
| 3-105 | 102 | 79 |
| 3-245 | 103 | 86 |
| 3-105 | 105 | 88 |
| 3-105 | 103 | 92 |
+-------+-----+--------+
9 rows in set (0.00 sec)
看到92在最下面所以采用到序
- 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)
#没懂第6. 计算每门可的平均成绩;
查询每门课
mysql> select *from course;
+-------+----------------+-----+
| cno | cname | tno |
+-------+----------------+-----+
| 3-105 | jisuanjidaolun | 825 |
| 3-245 | caozuo xitong | 804 |
| 9-888 | gaodengshuxue | 831 |
+-------+----------------+-----+
3 rows in set (0.00 sec)
求其中一门可的成绩
select avg(degree) from score where cno='3-105';
+-------------+
| avg(degree) |
+-------------+
| 81.0000 |
+-------------+
1 row in set (0.02 sec)
合并为一条语句;
group by 分组
mysql> select cno ,avg(degree) from score group by cno;
+-------+-------------+
| cno | avg(degree) |
+-------+-------------+
| 3-105 | 81.0000 |
| 3-245 | 59.5000 |
+-------+-------------+
2 rows in set (0.00 sec)
- 查询score表中至少有2名学生选修课的并且以三开头的课程平均分数;
这个比较复杂
like ‘3%’ :模糊搜索以三开头 ,%通配符表示所有
having count (cno )>=2 判断条件;
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 | 81.0000 | 5 |
| 3-245 | 59.5000 | 4 |
+-------+-------------+----------+
2 rows in set (0.00 sec)
- 查询分数大于 70小于90的sno的列;
mysql> select sno,degree from score where degree > 70 and degree<=90;
+-----+--------+
| sno | degree |
+-----+--------+
| 102 | 79 |
| 103 | 86 |
| 105 | 88 |
| 105 | 75 |
| 109 | 76 |
+-----+--------+
**错误**mysql> select sno,degree from score where between 70 and 90;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'between 70 and 90' at line 1
mysql> select sno,degree from score where degree between 70 and 90;
+-----+--------+
| sno | degree |
+-----+--------+
| 102 | 79 |
| 103 | 86 |
| 105 | 88 |
| 105 | 75 |
| 108 | 70 |
| 109 | 76 |
+-----+--------+
6 rows in set (0.00 sec)
多表查询
8.查询所有学生的sname ,cno 和degree列;
1
mysql> select sname from student;
+------------+
| sname |
+------------+
| zenhua |
| kuangming |
| wangli |
| lijun |
| wangfan |
| lujun |
| zhujun |
| zhujunqing |
| zhude |
+------------+
9 rows in set (0.00 sec)
mysql> select cno,degree from score;
+-------+--------+
| cno | degree |
+-------+--------+
| 3-105 | 79 |
| 3-245 | 9 |
| 3-105 | 92 |
| 3-245 | 86 |
| 3-105 | 88 |
| 3-245 | 75 |
| 3-105 | 70 |
| 3-105 | 76 |
| 3-245 | 68 |
+-------+--------+
9 rows in set (0.00 sec)
2
mysql> select cno,degree from score;
+-------+--------+
| cno | degree |
+-------+--------+
| 3-105 | 79 |
| 3-245 | 9 |
| 3-105 | 92 |
| 3-245 | 86 |
| 3-105 | 88 |
| 3-245 | 75 |
| 3-105 | 70 |
| 3-105 | 76 |
| 3-245 | 68 |
+-------+--------+
9 rows in set (0.00 sec)
mysql> select sno,cno,degree from score;
+-----+-------+--------+
| sno | cno | degree |
+-----+-------+--------+
| 102 | 3-105 | 79 |
| 102 | 3-245 | 9 |
| 103 | 3-105 | 92 |
| 103 | 3-245 | 86 |
| 105 | 3-105 | 88 |
| 105 | 3-245 | 75 |
| 108 | 3-105 | 70 |
| 109 | 3-105 | 76 |
| 109 | 3-245 | 68 |
+-----+-------+--------+
9 rows in set (0.00 sec)
最后三sname= cno;
错误:
mysql> select sname,cno,degree from score.sno=student.sno;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.sno' at line 1
mysql> select sname,cno,degree from score.sno=student.sno;
正确
mysql> select sname,cno,degree from student,score where score.sno=student.sno;
+------------+-------+--------+
| sname | cno | degree |
+------------+-------+--------+
| kuangming | 3-105 | 79 |
| kuangming | 3-245 | 9 |
| wangli | 3-105 | 92 |
| wangli | 3-245 | 86 |
| wangfan | 3-105 | 88 |
| wangfan | 3-245 | 75 |
| zhujunqing | 3-105 | 70 |
| zhude | 3-105 | 76 |
| zhude | 3-245 | 68 |
+------------+-------+--------+
9 rows in set (0.00 sec)