MySQL数据库多表查询

MySQL数据库多表查询

-Author: bavdu

-Email: bavduer@163.com

-Gtihub: https://github.com/bavdu

---

多表查询方法

SELECT field1, field2,...fieldN FROM table_name1, table_name2...[WHERE condition1 [AND [OR]] condition2.....]

mysql> create database Book_Class character set=utf8 collate=utf8_bin;
Query OK, 1 row affected (0.01 sec)

mysql> use Book_Class;
Database changed
mysql> create table Book_Price (
    -> id bigint not null primary key,
    -> bookname varchar(2000),
    -> bookprice bigint,
    -> author varchar(2000),
    -> typeid bigint)
    -> charset=utf8;

mysql> create table Book_Type (
    -> id bigint not null primary key,
    -> book_type_name varchar(2000))
    -> charset=utf8;
Query OK, 0 rows affected (0.03 sec)
                                                  
mysql> create table Price_devel (
    -> id bigint not null primary key,
    -> pricelevel bigint,
    -> price bigint,
    -> description varchar(2000))
    -> charset=utf8;
Query OK, 0 rows affected (0.01 sec)

##插入数据
mysql> insert into Book_Price (id,bookname,bookprice,author,typeid) values(1,"《Jane Eyre》",49.00,"Tom",2);
Query OK, 1 row affected (0.01 sec)

mysql> insert into Book_Price (id,bookname,bookprice,author,typeid) values(2,"《Cranford》",99.00,"Jack",2);
Query OK, 1 row affected (0.00 sec)

mysql> insert into Book_Price (id,bookname,bookprice,author,typeid) values(3,"《Black Beauty》",79.00,"Bob",2);
Query OK, 1 row affected (0.01 sec)

mysql> insert into Book_Price (id,bookname,bookprice,author,typeid) values(4,"《Desert Mountain Sea》",199.00,"Jerry",1);
Query OK, 1 row affected (0.01 sec)

mysql> insert into Book_Price (id,bookname,bookprice,author,typeid) values(5,"《Far from the Madding Crowd》",19.00,"Bavdu",3);
Query OK, 1 row affected (0.01 sec)

mysql> insert into Book_Price (id,bookname,bookprice,author,typeid) values(6,"《The Railway Children》",32.00,"Mr Liu",2);
Query OK, 1 row affected (0.00 sec)

mysql> insert into Book_Price (id,bookname,bookprice,author,typeid) values(7,"《William Shakespeare》",17.00,"Mr Wang",3);
Query OK, 1 row affected (0.00 sec)

mysql> insert into Book_Price (id,bookname,bookprice,author,typeid) values(8,"《Agatha Christie》",289.00,"Mr Yang",1);
Query OK, 1 row affected (0.00 sec)
                                                                 
mysql> insert into Book_Type (id,book_type_name) values(1,"COMPUTER TYPE");
Query OK, 1 row affected (0.01 sec)

mysql> insert into Book_Type (id,book_type_name) values(2,"HISTORY TYPE");
Query OK, 1 row affected (0.01 sec)

mysql> insert into Book_Type (id,book_type_name) values(3,"EDUCATION TYPE");
Query OK, 1 row affected (0.00 sec)
                                                                 
mysql> insert into Price_devel (id,pricelevel,price,description) values(1,1,100,"昂贵");
Query OK, 1 row affected (0.01 sec)

mysql> insert into Price_devel (id,pricelevel,price,description) values(2,2,20,"中等");
Query OK, 1 row affected (0.00 sec)

mysql> insert into Price_devel (id,pricelevel,price,description) values(3,3,19,"便宜");
Query OK, 1 row affected (0.01 sec)

mysql> select * from Book_Price;
+----+----------------------------------+-----------+---------+--------+
| id | bookname                         | bookprice | author  | typeid |
+----+----------------------------------+-----------+---------+--------+
|  1 | 《Jane Eyre》                    |        49 | Tom     |      2 |
|  2 | 《Cranford》                     |        99 | Jack    |      2 |
|  3 | 《Black Beauty》                 |        79 | Bob     |      2 |
|  4 | 《Desert Mountain Sea》          |       199 | Jerry   |      1 |
|  5 | 《Far from the Madding Crowd》   |        19 | Bavdu   |      3 |
|  6 | 《The Railway Children》         |        32 | Mr Liu  |      2 |
|  7 | 《William Shakespeare》          |        17 | Mr Wang |      3 |
|  8 | 《Agatha Christie》              |       289 | Mr Yang |      1 |
+----+----------------------------------+-----------+---------+--------+
8 rows in set (0.00 sec)

mysql> select * from Book_Type;
+----+----------------+
| id | book_type_name |
+----+----------------+
|  1 | COMPUTER TYPE  |
|  2 | HISTORY TYPE   |
|  3 | EDUCATION TYPE |
+----+----------------+
3 rows in set (0.00 sec)

mysql> select * from Price_devel;
+----+-----------+-------+-------------+
| id | picelevel | price | description |
+----+-----------+-------+-------------+
|  1 |         1 |   100 | 昂贵        |
|  2 |         2 |    80 | 中等        |
|  3 |         3 |    19 | 便宜        |
+----+-----------+-------+-------------+
3 rows in set (0.00 sec)

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-yyJoJZJw-1587733275072)(/Users/chaoliu/Documents/Cloud_Class/1.3_Linux_database_manager/picture/%E5%A4%9A%E8%A1%A801.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Pfhd7m4G-1587733275076)(/Users/chaoliu/Documents/Cloud_Class/1.3_Linux_database_manager/picture/%E5%A4%9A%E8%A1%A802.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-5aurFe7W-1587733275078)(/Users/chaoliu/Documents/Cloud_Class/1.3_Linux_database_manager/picture/%E5%A4%9A%E8%A1%A803.png)]

知识点1: 将数据库压缩文件导入数据库中

同学们,在生产环境上数据库中的初始数据都是通过DBA写出的sql脚本来解决数据结构问题的.也就是说运维人员在对项目进行上线的时候,不单单是对于项目所使用框架的设计和部署,最重要的数据也是需要我们在项目上线之前导入到数据库中的.(由讲师提供符合生产环境的数据库压缩文件,可参考https://github.com/bavdu/mysqlproduct)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

mysql>
mysql> create database classicmodels;
Query OK, 1 row affected (0.00 sec)

mysql> exit
Bye
[root@mysql ~]# ls
mysql-5.7.26-bin.tar.xz  mysqlsampledatabase.zip
[root@mysql ~]# unzip mysqlsampledatabase.zip
Archive:  mysqlsampledatabase.zip
  inflating: mysqlsampledatabase.sql
[root@mysql ~]# ls
mysql-5.7.26-bin.tar.xz  mysqlsampledatabase.sql  mysqlsampledatabase.zip

##将数据库压缩文件导入到数据库中.
[root@mysql ~]# mysql -uroot -p"Cloud1903..0515" classicmodels < mysqlsampledatabase.sql

#####classicmodels数据库简单介绍

• 客户(Customers):存储客户的数据
• 产品(Products):存储比例模型车列表
• 产品线(ProductLines):存储产品系列类别列表
• 订单(Orders):存储客户下达的销售订单
• 订单详细信息(OrderDetails):存储每个销售订单的销售项目信息
• 付款(Payments):存储客户根据其帐户进行的付款
• 员工(Employees):存储所有员工信息以及组织结构,例如谁向谁报告
• 办公室(Offices):存储销售办公室数据

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-T3VyfpXX-1587733275080)(http://www.mysqltutorial.org/wp-content/uploads/2009/12/MySQL-Sample-Database-Schema.png)]

知识点2: 多表查询中的嵌套查询

执行查询时子查询首先运行并返回结果集,然后此结果集用作外部查询的输入

需求:查询在美国办事处的人员名单
mysql> select lastName,firstName from employees where officeCode in 
(select officeCode from offices where country="USA");
+-----------+-----------+
| lastName  | firstName |
+-----------+-----------+
| Murphy    | Diane     |
| Patterson | Mary      |
| Firrelli  | Jeff      |
| Bow       | Anthony   |
| Jennings  | Leslie    |
| Thompson  | Leslie    |
| Firrelli  | Julie     |
| Patterson | Steve     |
| Tseng     | Foon Yue  |
| Vanauf    | George    |
+-----------+-----------+
10 rows in set (0.00 sec)

知识点3: 多表查询中的内键连接

mysql> select 字段01,字段02 from 表01 join 表02 on 表02.f1=表01.f1 join 表03 on 表03.f1=表01.f1 where 表03.f1=限定条件 and 表02.f1 >= 40;

需求: 登记在美国办公室的用户名单
mysql> select contactFirstName,contactLastName from customers join employees on customers.salesRepEmployeeNumber=employees.employeeNumber where employees.officeCode in (select officeCode from offices where country="USA");

+------------------+-----------------+
| contactFirstName | contactLastName |
+------------------+-----------------+
| Susan            | Nelson          |
| Julie            | Murphy          |
| Juri             | Hashimoto       |
| Julie            | Brown           |
| Sue              | Frick           |
| Sue              | Taylor          |
| Jean             | King            |
| Julie            | Young           |
| Mary             | Young           |
| Valarie          | Thompson        |
| Brian            | Chandler        |
| Steve            | Thompson        |
| Jerry            | Tseng           |
| Miguel           | Barajas         |
| Wing             | Huang           |
| Francisca        | Cervantes       |
| Allen            | Nelson          |
。。。。。
。。。。。
。。。。。
。。。。。
。。。。。
。。。。。