HONGWEI's Blog

MySQL 教程:多表查询

数据库 156

创建数据库并插入数据

表一,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

本文链接: https://blog.zhwei.cn/article/mysql-jiao-cheng-duo-biao-cha-xun
版权声明: 本博客所有文章除特别声明外,均遵循 CC 4.0 BY-NC-SA 版权协议。转载请附上原文链接!