@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
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/44455.html
標籤：MySQL