python技术面试题(七)--SQL语句

小闫同学啊

发布于 2019-07-18 14:51:35

6250

发布于 2019-07-18 14:51:35

文章被收录于专栏：小闫笔记小闫笔记

正文共： 6454 字 5 图预计阅读时间： 17 分钟

我们后面查询用到的表：

mysql> select * from t_score;
+------+--------------+-----------+--------+
| c_id | c_student_id | c_english | c_math |
+------+--------------+-----------+--------+
|    1 |            1 |      60.5 |     99 |
|    2 |            2 |      65.5 |     60 |
|    3 |            3 |      70.5 |     88 |
|    4 |            4 |      60.5 |     77 |
|    5 |            5 |      60.5 |     89 |
|    6 |            6 |        90 |     93 |
|    7 |            7 |        80 |     99 |
|    8 |            8 |        88 |     99 |
|    9 |            9 |        77 |     60 |
|   10 |           10 |        75 |     86 |
|   11 |           11 |        60 |     60 |
|   12 |           12 |        88 |     99 |
|   13 |           13 |        77 |     59 |
|   14 |           14 |      NULL |     59 |
|   15 |           15 |        60 |   NULL |
+------+--------------+-----------+--------+

1.单表查询

1.mysql中的分页查询。

语法：

select * from 表名 limit (page-1)*count,count;

page指的是页码，count指的是每页显示的条数。

# 每页3条数据，查询第三页的数据，(3-1)*3=6.
mysql> select * from t_score limit 6,3;
+------+--------------+-----------+--------+
| c_id | c_student_id | c_english | c_math |
+------+--------------+-----------+--------+
|    7 |            7 |        80 |     99 |
|    8 |            8 |        88 |     99 |
|    9 |            9 |        77 |     60 |
+------+--------------+-----------+--------+

2.求和：

# 求数学学科的总成绩
mysql> select sum(c_math) from t_score;
+-------------+
| sum(c_math) |
+-------------+
|        1127 |
+-------------+

3.求平均：

# 求数学学科的平均成绩
mysql> select avg(c_math) from t_score;
+-------------+
| avg(c_math) |
+-------------+
|        80.5 |
+-------------+

4.求最大最小值：

# 找到数学最高分
mysql> select max(c_math) from t_score;
+-------------+
| max(c_math) |
+-------------+
|          99 |
+-------------+
# 找到数学最低分
mysql> select min(c_math) from t_score;
+-------------+
| min(c_math) |
+-------------+
|          59 |
+-------------+

5.统计记录总数：

# 统计参加数学考试的人有多少
mysql> select count(*) from t_score;
+----------+
| count(*) |
+----------+
|       15 |
+----------+

6.分组：

group_by后面的字段名要和select后面的字段名相同，否则会报错。

# 从成绩表中取出数学成绩进行分组
mysql> select c_math from t_score group by c_math;
+--------+
| c_math |
+--------+
|   NULL |
|     59 |
|     60 |
|     77 |
|     86 |
|     88 |
|     89 |
|     93 |
|     99 |
+--------+

7.根据分组结果，使用group_concat()来获取分组中指定字段的集合

# 根据数据成绩进行分组，获取每个分数中学生的编号
mysql> select c_math,group_concat(c_student_id) from t_score group by c_math;
+--------+----------------------------+
| c_math | group_concat(c_student_id) |
+--------+----------------------------+
|   NULL | 15                         |
|     59 | 13,14                      |
|     60 | 2,9,11                     |
|     77 | 4                          |
|     86 | 10                         |
|     88 | 3                          |
|     89 | 5                          |
|     93 | 6                          |
|     99 | 1,7,8,12                   |
+--------+----------------------------+

8.分组和聚合函数的使用

# 根据性别进行分组，求出每组同学的最大年龄、最小年龄、年龄总和、平均年龄、人数
mysql> select c_gender,max(c_age),min(c_age),sum(c_age),avg(c_age),count(*) from t_student group by c_gender;
+----------+------------+------------+------------+------------+----------+
| c_gender | max(c_age) | min(c_age) | sum(c_age) | avg(c_age) | count(*) |
+----------+------------+------------+------------+------------+----------+
| 男       |         99 |         15 |       1084 |    47.1304 |       26 |
| 女       |         88 |         11 |        239 |    39.8333 |        7 |
+----------+------------+------------+------------+------------+----------+

9.having条件语句的使用。

# 从学生表中以性别进行分组，然后选出女生分组，并展示小组中所有的名字
mysql> select c_gender,group_concat(c_name) from t_student group by c_gender having c_gender='女';
+----------+-----------------------------------------------------------+
| c_gender | group_concat(c_name)                                       |
+----------+-----------------------------------------------------------+
| 女       | 小龙女,白骨精,扈三娘,孙二娘,赵敏,嫦娥,孙                         |
+----------+-----------------------------------------------------------+

2.多表查询

# 学生表中保存了学生的信息和所在班级的ID，班级表中保存了班级的信息。 查询学生姓名和对应的班级
mysql> select t_student.c_name,t_class.c_name from t_student,t_class where t_student.c_class_id=t_class.c_id;
+-----------+-------------------------------------+
| c_name    | c_name                              |
+-----------+-------------------------------------+
| 孙德龙    | 软件工程18级一班                        |
| 塔大      | 软件工程18级二班                        |
| 宋江      | 计算机科学与技术18级一班                 |
| 武松      | 计算机科学与技术18级二班                 |
| 孙二娘    | 网络工程18级一班                        |
| 扈三娘    | 网络工程18级二班                        |
| 鲁智深    | 软件工程18级一班                        |
| 林冲      | 软件工程18级二班                       |
| 阮小七    | 计算机科学与技术18级一班                 |
| 阮小五    | 计算机科学与技术18级二班                 |
| 阮小二    | 网络工程18级一班                       |
| 白骨精    | 网络工程18级二班                       |
| 孙悟空    | 软件工程18级一班                       |
| 猪八戒    | 软件工程18级二班                       |
| 沙和尚    | 计算机科学与技术18级一班                 |
| 唐三奘    | 计算机科学与技术18级二班                 |
| 哪吒      | 网络工程18级一班                       |
| 嫦娥      | 网络工程18级二班                       |
| 杨过      | 软件工程18级一班                       |
| 郭靖      | 软件工程18级二班                       |
| 洪七公    | 计算机科学与技术18级一班                 |
| 欧阳锋    | 计算机科学与技术18级二班                 |
| 黄药师    | 网络工程18级一班                       |
| 小龙女    | 网络工程18级二班                       |
| 孙%       | 软件工程18级一班                      |
| 张无忌    | 软件工程18级二班                       |
| 张翠山    | 计算机科学与技术18级一班                 |
| 张三丰    | 计算机科学与技术18级二班                 |
| 宋青书    | 网络工程18级一班                        |
| 赵敏      | 网络工程18级二班                       |
| 孙        | 计算机科学与技术18级一班                 |
| 孙子      | 计算机科学与技术18级一班                 |
| 孙        | 网络工程18级一班                       |
+-----------+-------------------------------------+

1.内连接查询

语法：

select * from 表1 inner join 表2 on 表1.列 运算符 表2.列

连接时必须指定连接条件，用on指定。如果无条件，那么会出现笛卡尔积。

#  查询学生姓名和对应的班级
mysql> select ts.c_name,tc.c_name from t_student as ts inner join t_class tc on ts.c_class_id=tc.c_id;
.....结果同上一个结果.......

上面的as代表的是为表起别名，也可以不写空格隔开。

2.左连接查询

语法：

select * from 表1 left join 表2 on 表1.列 运算符 表2.列

查询的结果为根据左表中的数据进行连接，如果右表中没有满足条件的记录，则连接空值。

mysql> select ts.c_name,tc.c_name from t_student as ts left join t_class tc on ts.c_class_id=tc.c_id;
+--------------+-------------------------------------+
| c_name       | c_name                              |
+--------------+-------------------------------------+
| 孙德龙       | 软件工程18级一班                        |
| 塔大         | 软件工程18级二班                       |
| 宋江         | 计算机科学与技术18级一班                |
| 武松         | 计算机科学与技术18级二班                |
| 孙二娘       | 网络工程18级一班                       |
| 扈三娘       | 网络工程18级二班                       |
| 鲁智深       | 软件工程18级一班                       |
| 林冲         | 软件工程18级二班                      |
| 阮小七       | 计算机科学与技术18级一班                |
| 阮小五       | 计算机科学与技术18级二班                |
| 阮小二       | 网络工程18级一班                       |
| 白骨精       | 网络工程18级二班                       |
| 孙悟空       | 软件工程18级一班                       |
| 猪八戒       | 软件工程18级二班                       |
| 沙和尚       | 计算机科学与技术18级一班                 |
| 唐三奘       | 计算机科学与技术18级二班                 |
| 哪吒         | 网络工程18级一班                       |
| 嫦娥         | 网络工程18级二班                       |
| 杨过         | 软件工程18级一班                       |
| 郭靖         | 软件工程18级二班                       |
| 洪七公       | 计算机科学与技术18级一班                 |
| 欧阳锋       | 计算机科学与技术18级二班                 |
| 黄药师       | 网络工程18级一班                       |
| 小龙女       | 网络工程18级二班                        |
| 孙%          | 软件工程18级一班                       |
| 张无忌       | 软件工程18级二班                        |
| 张翠山       | 计算机科学与技术18级一班                 |
| 张三丰       | 计算机科学与技术18级二班                 |
| 宋青书       | 网络工程18级一班                        |
| 赵敏         | 网络工程18级二班                       |
| 孙           | 计算机科学与技术18级一班                 |
| 孙子         | 计算机科学与技术18级一班                 |
| 孙           | 网络工程18级一班                       |
| 小闫笔记     | NULL                                  |
+--------------+-------------------------------------+

3.子查询

语法：

select * from 表1 where 条件 运算符 (select查询)

子查询是单独可以执行的一条SQL语句，它作为主查询的条件或者数据源嵌套在主查询中。

3.1标量子查询（子查询返回的结果是一个数据(一行一列)）

# 查询班级中年龄大于平均年龄的学生信息
mysql> select * from t_student where c_age > (select avg(c_age) from t_student);
# 因为数据太多，为了展示效果，我们查询指定的一些字段
mysql> select c_id,c_name,c_gender,c_address from t_student where c_age > (select avg(c_age) from t_student);
+------+-----------+----------+-----------------------------+
| c_id | c_name    | c_gender | c_address                   |
+------+-----------+----------+-----------------------------+
|    7 | 鲁智深    | 男       | 北京市西城区西直门          |
|   15 | 沙和尚    | 男       | 北京市西城区西直门          |
|   16 | 唐三奘    | 男       | 北京市西城区西直门          |
|   18 | 嫦娥      | 女       | 北京市昌平霍营              |
|   19 | 杨过      | 男       | 北京市西城区西直门          |
|   20 | 郭靖      | 男       | 北京市西城区西直门          |
|   21 | 洪七公    | 男       | 北京市西城区西直门          |
|   22 | 欧阳锋    | 男       | 北京市西城区西直门          |
|   25 | 孙%       | 男       | 北京市西城区西直门          |
|   29 | 宋青书    | 男       | 北京市西城区西直门          |
|   30 | 赵敏      | 女       | 北京市昌平霍营              |
+------+-----------+----------+-----------------------------+

3.2列级子查询（子查询返回的结果是一列(一列多行)）

# 主查询 where 条件 in (列子查询)
# 查询出所有学生所在班级的班级名称
mysql> select c_name from t_class where c_id in (select c_class_id from t_student);
+-------------------------------------+
| c_name                              |
+-------------------------------------+
| 软件工程18级一班                    |
| 软件工程18级二班                    |
| 计算机科学与技术18级一班            |
| 计算机科学与技术18级二班            |
| 网络工程18级一班                    |
| 网络工程18级二班                    |
+-------------------------------------+

3.3行级子查询（子查询返回的结果是一行(一行多列)）

# 主查询 where (字段1,2,...) = (行子查询) 
# 查询班级年龄最大，所在班号最小的学生
mysql> select c_id,c_name,c_gender,c_address from t_student where(c_age,c_class_id) = (select max(c_age),min(c_class_id) from t_student);
+------+-----------+----------+-----------------------------+
| c_id | c_name    | c_gender | c_address                   |
+------+-----------+----------+-----------------------------+
|    7 | 鲁智深     | 男        | 北京市西城区西直门             |
|   25 | 孙%       | 男        | 北京市西城区西直门             |
+------+-----------+----------+-----------------------------+

4.自连接查询。