2020python练习——SQL查询

@2020.5.8

 

题目：

https://www.cnblogs.com/linhaifeng/articles/7267596.html#_label5

 

1、数据准备

（1）将数据文件 init.sql 放在MySQL的安装目录文件 C:\MySQL\mysql-5.6.48-winx64\data

（2）启动MySQL服务，启动客户端，连接服务端

 

 

（3）导入数据文件

mysql> create database db1;
mysql> use db1;
mysql> source /root/init.sql

 

Microsoft Windows [版本 10.0.18362.1016]
(c) 2019 Microsoft Corporation。保留所有权利。

C:\WINDOWS\system32>mysql -uroot -p
Enter password: ***
ERROR 2003 (HY000): Can't connect to MySQL server on 'localhost' (10061)

C:\WINDOWS\system32>net start mysql
MySQL 服务正在启动 .
MySQL 服务已经启动成功。


C:\WINDOWS\system32>mysql -uroot -p
Enter password: ***
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.48 MySQL Community Server (GPL)

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| bms_info           |
| db99               |
| mysql              |
| performance_schema |
| test               |
+--------------------+
6 rows in set (0.02 sec)

mysql> create database db907P;
Query OK, 1 row affected (0.01 sec)

mysql> use db907P;
Database changed

mysql> source C:/MySQL/mysql-5.6.48-winx64/data/init.sql;
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.06 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.05 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.03 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 47 rows affected (0.00 sec)
Records: 47  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.04 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 16 rows affected (0.00 sec)
Records: 16  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.15 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> show tables;
+------------------+
| Tables_in_db907p |
+------------------+
| class            |
| course           |
| score            |
| student          |
| teacher          |
+------------------+
5 rows in set (0.01 sec)

mysql>
cmd客户端-详细指令

 

2、解题啦

先明确表结构：

mysql> show tables;
+------------------+
| Tables_in_db907p |
+------------------+
| class            |
| course           |
| score            |
| student          |
| teacher          |
+------------------+
5 rows in set (0.01 sec)

mysql> select * from class;
+-----+--------------+
| cid | caption      |
+-----+--------------+
|   1 | 三年二班     |
|   2 | 三年三班     |
|   3 | 一年二班     |
|   4 | 二年九班     |
+-----+--------------+
4 rows in set (0.01 sec)

mysql> select * from course;
+-----+--------+------------+
| cid | cname  | teacher_id |
+-----+--------+------------+
|   1 | 生物   |          1 |
|   2 | 物理   |          2 |
|   3 | 体育   |          3 |
|   4 | 美术   |          2 |
+-----+--------+------------+
4 rows in set (0.00 sec)

mysql> select * from score;
+-----+------------+-----------+-----+
| sid | student_id | course_id | num |
+-----+------------+-----------+-----+
|   1 |          1 |         1 |  10 |
|   2 |          1 |         2 |   9 |
|   5 |          1 |         4 |  66 |
|   6 |          2 |         1 |   8 |
|   8 |          2 |         3 |  68 |
|   9 |          2 |         4 |  99 |
|  10 |          3 |         1 |  77 |
|  11 |          3 |         2 |  66 |
|  12 |          3 |         3 |  87 |
|  13 |          3 |         4 |  99 |
|  14 |          4 |         1 |  79 |
|  15 |          4 |         2 |  11 |
|  16 |          4 |         3 |  67 |
|  17 |          4 |         4 | 100 |
|  18 |          5 |         1 |  79 |
|  19 |          5 |         2 |  11 |
|  20 |          5 |         3 |  67 |
|  21 |          5 |         4 | 100 |
|  22 |          6 |         1 |   9 |
|  23 |          6 |         2 | 100 |
|  24 |          6 |         3 |  67 |
|  25 |          6 |         4 | 100 |
|  26 |          7 |         1 |   9 |
|  27 |          7 |         2 | 100 |
|  28 |          7 |         3 |  67 |
|  29 |          7 |         4 |  88 |
|  30 |          8 |         1 |   9 |
|  31 |          8 |         2 | 100 |
|  32 |          8 |         3 |  67 |
|  33 |          8 |         4 |  88 |
|  34 |          9 |         1 |  91 |
|  35 |          9 |         2 |  88 |
|  36 |          9 |         3 |  67 |
|  37 |          9 |         4 |  22 |
|  38 |         10 |         1 |  90 |
|  39 |         10 |         2 |  77 |
|  40 |         10 |         3 |  43 |
|  41 |         10 |         4 |  87 |
|  42 |         11 |         1 |  90 |
|  43 |         11 |         2 |  77 |
|  44 |         11 |         3 |  43 |
|  45 |         11 |         4 |  87 |
|  46 |         12 |         1 |  90 |
|  47 |         12 |         2 |  77 |
|  48 |         12 |         3 |  43 |
|  49 |         12 |         4 |  87 |
|  52 |         13 |         3 |  87 |
+-----+------------+-----------+-----+
47 rows in set (0.00 sec)

mysql> select * from teacher;
+-----+-----------------+
| tid | tname           |
+-----+-----------------+
|   1 | 张磊老师        |
|   2 | 李平老师        |
|   3 | 刘海燕老师      |
|   4 | 朱云海老师      |
|   5 | 李杰老师        |
+-----+-----------------+
5 rows in set (0.00 sec)

mysql> select * from student;
+-----+--------+----------+--------+
| sid | gender | class_id | sname  |
+-----+--------+----------+--------+
|   1 | 男     |        1 | 理解   |
|   2 | 女     |        1 | 钢蛋   |
|   3 | 男     |        1 | 张三   |
|   4 | 男     |        1 | 张一   |
|   5 | 女     |        1 | 张二   |
|   6 | 男     |        1 | 张四   |
|   7 | 女     |        2 | 铁锤   |
|   8 | 男     |        2 | 李三   |
|   9 | 男     |        2 | 李一   |
|  10 | 女     |        2 | 李二   |
|  11 | 男     |        2 | 李四   |
|  12 | 女     |        3 | 如花   |
|  13 | 男     |        3 | 刘三   |
|  14 | 男     |        3 | 刘一   |
|  15 | 女     |        3 | 刘二   |
|  16 | 男     |        3 | 刘四   |
+-----+--------+----------+--------+
16 rows in set (0.00 sec)

mysql>

 

 详细查询：

 

 

 

 

参考阅读：

https://www.cnblogs.com/linhaifeng/articles/7267596.html#_label5

https://www.cnblogs.com/Dominic-Ji/p/10875493.html

https://www.cnblogs.com/wupeiqi/articles/5748496.html

posted @ 2020-05-08 16:39  bigorangecc  阅读(483)  评论(0)  编辑  收藏  举报