数据库，连接查询

CREATE TABLE fruits
(
f_id    char(10)         NOT NULL,
s_id    INT            NOT NULL,
f_name  char(255)      NOT NULL,
f_price decimal(8,2)      NOT NULL,
PRIMARY KEY(f_id) 
);

INSERT INTO fruits (f_id, s_id, f_name, f_price)
     VALUES('a1', 101,'apple',5.2),
     ('b1',101,'blackberry', 10.2),
     ('bs1',102,'orange', 11.2),
     ('bs2',105,'melon',8.2),
     ('t1',102,'banana', 10.3),
     ('t2',102,'grape', 5.3),
     ('o2',103,'coconut', 9.2),
     ('c0',101,'cherry', 3.2),
     ('a2',103, 'apricot',2.2),
     ('l2',104,'lemon', 6.4),
     ('b2',104,'berry', 7.6),
     ('m1',106,'mango', 15.6),
     ('m2',105,'xbabay', 2.6),
     ('t4',107,'xbababa', 3.6),
     ('m3',105,'xxtt', 11.6),
     ('b5',107,'xxxx', 3.6);
         
         
 #创建供应商表
 CREATE TABLE suppliers
(
  s_id      int      NOT NULL AUTO_INCREMENT,
  s_name    char(50) NOT NULL,
  s_city    char(50) NULL,
  s_zip     char(10) NULL,
  s_call    CHAR(50) NOT NULL,
  PRIMARY KEY (s_id)
) ;
INSERT INTO suppliers(s_id, s_name,s_city,  s_zip, s_call)
VALUES(101,'FastFruit Inc.','Tianjin','300000','48075'),
(102,'LT Supplies','Chongqing','400000','44333'),
(103,'ACME','Shanghai','200000','90046'),
(104,'FNK Inc.','Zhongshan','528437','11111'),
(105,'Good Set','Taiyuang','030000', '22222'),
(106,'Just Eat Ours','Beijing','010', '45678'),
(107,'DK Inc.','Zhengzhou','450000', '33332');

SELECT f_id,s_id,f_name,f_price from fruits;

#连接查询，从多个表查询相关的数据
#查询水果名称和供应商的名称，价格
SELECT f_name,s_name from fruits,suppliers where fruits.s_id=suppliers.s_id;

#内连接 INNER JOIN,速度快，不会遗漏连接条件(??)
SELECT fruits.s_id,f_name,s_name from fruits INNER JOIN suppliers ON fruits.s_id=suppliers.s_id;

#查询‘apple’的供应商信息
SELECT suppliers.s_id,s_name,s_city,s_zip,s_call from suppliers INNER JOIN fruits ON suppliers.s_id=fruits.s_id 
    AND fruits.f_name='apple';
        
#利用别名多表查询，查询‘apple’的供应商信息
#
SELECT suppliers.s_id,s_name,s_city,s_zip,s_call from suppliers  as s INNER JOIN fruits as f ON s.s_id=f.s_id
    AND f.f_name='apple';# ??? todo
#SELECT suppliers.s_id,s_name,s_city,s_zip,s_call from suppliers INNER JOIN fruits ON suppliers.s_id=fruits.s_id
#    AND fruits.f_name='apple';

#订单表
CREATE TABLE orders
(
  o_num  int      NOT NULL AUTO_INCREMENT,
  o_date datetime NOT NULL,
  c_id   int      NOT NULL,
  PRIMARY KEY (o_num)
) ;

INSERT INTO orders(o_num, o_date, c_id)
VALUES(30001, '2008-09-01', 10001),
(30002, '2008-09-12', 10003),
(30003, '2008-09-30', 10004),
(30004, '2008-10-03', 10005),
(30005, '2008-10-08', 10001);

#顾客表
CREATE TABLE customers
(
  c_id      int       NOT NULL AUTO_INCREMENT,
  c_name    char(50)  NOT NULL,
  c_address char(50)  NULL,
  c_city    char(50)  NULL,
  c_zip     char(10)  NULL,
  c_contact char(50)  NULL,
  c_email   char(255) NULL,
  PRIMARY KEY (c_id)
);

INSERT INTO customers(c_id, c_name, c_address, c_city, 
c_zip,  c_contact, c_email) 
VALUES(10001, 'RedHook', '200 Street ', 'Tianjin', 
 '300000',  'LiMing', 'LMing@163.com'),
(10002, 'Stars', '333 Fromage Lane',
 'Dalian', '116000',  'Zhangbo','Jerry@hotmail.com'),
(10003, 'Netbhood', '1 Sunny Place', 'Qingdao',  '266000',
 'LuoCong', NULL),
(10004, 'JOTO', '829 Riverside Drive', 'Haikou', 
 '570000',  'YangShan', 'sam@hotmail.com');
 
 
 SELECT * from customers;
 
 SELECT * from orders;
 
 #查询所有的顾客信息和他的订单信息(内连接)
 SELECT customers.c_id,c_name,c_address,c_city,c_contact,c_email,o_num,O_date FROM customers INNER JOIN orders ON
    orders.c_id=customers.c_id;
  #查询所有的顾客信息和他的订单信息，包括没有订单的顾客 （左连接,左表的所有信息都包含）
SELECT customers.c_id,c_name,c_address,c_city,c_contact,c_email,o_num,O_date FROM customers LEFT JOIN orders ON
    orders.c_id=customers.c_id;
 
 #内连接（只包含符合连接条件的记录）和外连接(左连接，右连接。包含符合条件的记录和不符合条件的记录）
 
 #在customers,orders表中查询customers表中id为10001的客户订单信息
 SELECT o_num,o_date,customers.c_id from customers INNER JOIN orders ON customers.c_id=orders.c_id 
     AND orders.c_id=10001;