关于mysql:MySQL数据库基础练习一

48次阅读

共计 9796 个字符，预计需要花费 25 分钟才能阅读完成。

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,score
where 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)

正文完
mysql
发表至： mysql
2020-11-16
 0
关于mysql:技术分享-MySQL级联复制下进行大表的字段扩容
关于mysql:存储函数一-创建存储函数
关于mysql:mysql左连接右连接内连接全连接
关于mysql:利用云原生数仓-Databend-构建-MySQL-的归档分析服务