MySQL 教程：多表查询

创建数据库并插入数据

表一，fruits，水果

# 创建表，数据类型请自行查询
CREATE TABLE fruits (
  id INT NOT NULL,
  sid INT NOT NULL,
  NAME CHAR(255) NOT NULL,
  price DECIMAL (8, 2) NOT NULL,
  PRIMARY KEY (id)
);

# 表中插入数据
INSERT INTO fruits
VALUES
  ('1', 101, 'apple', 5.2),
  ('2', 101, 'blackberry', 10.2),
  ('3', 102, 'orange', 11.2),
  ('4', 105, 'melon', 8.2),
  ('5', 102, 'banana', 10.3),
  ('6', 102, 'grape', 5.3),
  ('7', 103, 'coconut', 9.2),
  ('8', 101, 'cherry', 3.2),
  ('9', 103, 'apricot', 2.2),
  ('10', 104, 'lemon', 6.4),
  ('11', 104, 'berry', 7.6),
  ('12', 106, 'mango', 15.6);
  ('13', 110, 'HHHHHH', 12.6);

表二，suppliers，供应商

CREATE TABLE suppliers (
  sid INT NOT NULL,
  sName CHAR(50) NOT NULL,
  city CHAR(50) NULL,
  zip CHAR(10) NULL,
  scall CHAR(50) NOT NULL,
  PRIMARY KEY (sid)
);

INSERT INTO suppliers
VALUES
  (
    101,
    'Supplies A',
    'Tianjin',
    '400000',
    '18075'
  ),
  (
    102,
    'Supplies B',
    'Chongqing',
    '400000',
    '44333'
  ),
  (
    103,
    'Supplies C',
    'Shanghai',
    '400000',
    '90046'
  ),
  (
    104,
    'Supplies D',
    'Zhongshan',
    '400000',
    '11111'
  ),
  (
    105,
    'Supplies E',
    'Taiyuang',
    '400000',
    '22222'
  ),
  (
    106,
    'Supplies F',
    'Beijing',
    '400000',
    '45678'
  ),
  (
    107,
    'Supplies G',
    'Zhengzhou',
    '400000',
    '33332'
  );

表 3，顾客

CREATE TABLE people (
  id INT NOT NULL,
  NAME VARCHAR(30),
  num INT NOT NULL,
  CITY VARCHAR(50),
  PRIMARY KEY (id)
);

INSERT INTO people
VALUES
  ('1', 'A', 23, 'Shanghai'),
  ('2', 'B', 5, 'TIANJIN'),
  ('3', 'C', 3, 'BEIJING'),
  ('4', 'D', 11, 'NANCHANG'),
  ('5', 'E', 56, 'NANJING'),
  ('6', 'F', 15, 'JIANGXI'),
  ('7', 'G', 23, 'SHENZHEN'),
  ('8', 'H', 56, 'WUHAN'),
  ('9', 'I', 76, 'GUANZHOU'),
  ('10', 'G', 34, 'LIAONING');

普通双表查询

查询水果的供应商编码、名字即对应的水果名称和价格

SELECT f.SID,S.SNAME,F.name,F.price FROM fruits AS f ,suppliers AS S WHERE F.SID =S.SID;

SID    SNAME    name    price
101    Supplies A    apple    5.20
101    Supplies A    blackberry    10.20
102    Supplies B    orange    11.20
105    Supplies E    melon    8.20
102    Supplies B    banana    10.30
102    Supplies B    grape    5.30
103    Supplies C    coconut    9.20
101    Supplies A    cherry    3.20
103    Supplies C    apricot    2.20
104    Supplies D    lemon    6.40
104    Supplies D    berry    7.60
106    Supplies F    mango    15.60

内连接，两个表的公共部分

格式：表名 (INNER) JOIN 表名 ON 连接条件

双表内连接查询

查询水果的供应商编码、名字、城市即对应的水果名称和价格

SELECT f.SID,S.SNAME,F.name,F.price,s.city FROM fruits AS f INNER JOIN suppliers AS S ON F.SID =S.SID;

SELECT f.SID,S.SNAME,F.name,F.price,s.city FROM fruits AS f JOIN suppliers AS S ON F.SID =S.SID;

SID    SNAME    name    price    city
101    Supplies A    apple    5.20    Tianjin
101    Supplies A    blackberry    10.20    Tianjin
102    Supplies B    orange    11.20    Chongqing
105    Supplies E    melon    8.20    Taiyuang
102    Supplies B    banana    10.30    Chongqing
102    Supplies B    grape    5.30    Chongqing
103    Supplies C    coconut    9.20    Shanghai
101    Supplies A    cherry    3.20    Tianjin
103    Supplies C    apricot    2.20    Shanghai
104    Supplies D    lemon    6.40    Zhongshan
104    Supplies D    berry    7.60    Zhongshan
106    Supplies F    mango    15.60    Beijing

发现和上面普通查询一样

自连接查询，即双表是同一张表

查询供应 id 为 2 的水果供应商提供的其他水果名称

SELECT f2.id,F2.name,f2.sid FROM fruits AS f1 JOIN fruits AS F2 ON f1.sid = f2.sid AND f1.id = 2;

id    name    sid
1    apple    101
2    blackberry    101
8    cherry    101

查询条件为表 1，查询字段为表 2

通过子句查询进行内链接

SELECT fruits.id,fruits.name,fruits.sid FROM fruits WHERE fruits.SID = (SELECT fruits.SID FROM fruits WHERE fruits.ID = 2);

id    name    sid
1    apple    101
2    blackberry    101
8    cherry    101

外连接

左外连接，左表全部和左右表公共部分集合

格式： 表名 LEFT (OUTER) JOIN 表名 ON 条件

# SUPPLIERS 为左表，显示全部内容
# FRUITS 为右表，显示与左表公共部分
# 右表其他内容显示为空，NULL

SELECT s.sid,s.sname,f.id,f.name FROM suppliers AS s LEFT JOIN fruits AS f ON f.sid = s.sid ;

sid    sname    id    name
101    Supplies A    1    apple
101    Supplies A    2    blackberry
102    Supplies B    3    orange
105    Supplies E    4    melon
102    Supplies B    5    banana
102    Supplies B    6    grape
103    Supplies C    7    coconut
101    Supplies A    8    cherry
103    Supplies C    9    apricot
104    Supplies D    10    lemon
104    Supplies D    11    berry
106    Supplies F    12    mango
107    Supplies G    NULL  NULL

右外连接，右表全部和左右表公共部分集合

与左外连接相似,就是全部显示右表及公共部分

格式： 表名 RIGHT (OUTER) JOIN 表名 ON 条件

# SUPPLIERS 为左表，显示与右表公共部分
# FRUITS 为右表，显示全部内容
# 左表其他内容显示为空，NULL

SELECT s.sid,s.sname,f.id,f.name FROM suppliers AS s RIGHT JOIN fruits AS f ON f.sid = s.sid ;

sid    sname    id    name
101    Supplies A    1    apple
101    Supplies A    2    blackberry
102    Supplies B    3    orange
105    Supplies E    4    melon
102    Supplies B    5    banana
102    Supplies B    6    grape
103    Supplies C    7    coconut
101    Supplies A    8    cherry
103    Supplies C    9    apricot
104    Supplies D    10    lemon
104    Supplies D    11    berry
106    Supplies F    12    mango
NULL  NULL    13    HHHHHH

三表查询

• 表一和表二sid关联
• 表一和表三id关联

查询供应商 Supplies B 供应的水果顾客购买量

SELECT
  s.city,
  s.sName,
  f.name,
  p.NAME,
  p.num
FROM
  fruits AS F
  JOIN suppliers AS s
    ON f.sid = s.sid
    AND s.sName = 'Supplies B'
  LEFT JOIN people AS p
    ON f.id = p.id
ORDER BY p.num DESC;

city    sName    name    NAME    num
Chongqing    Supplies B    banana    E    56
Chongqing    Supplies B    grape    F    15
Chongqing    Supplies B    orange    C    3