mysql查询练习

最新推荐文章于 2024-07-23 07:27:56 发布

原创最新推荐文章于 2024-07-23 07:27:56 发布 · 524 阅读

0 ·

CC 4.0 BY-SA版权

本文提供了几个关于MySQL查询的示例，包括根据特定分数查询记录、使用OR条件筛选、按特定字段排序、查找最高分、平均分计算以及多条件组合查询等。通过这些例子，可以了解和提升MySQL查询能力。

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)