Mysql数据库(六）：详细，对新手非常友好

友好"/>

Mysql数据库(六）：详细，对新手非常友好

新建一个数据库

• 创建一张表suppliers,并插入数据
  

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');

• 查询之前，先来看一下两个表的结构
  
  

1.内连接

• 在fruits表和suppliers表之间使用内连接查询

SELECT suppliers.s_id, s_name,f_name, f_price FROM fruits ,suppliers WHERE fruits.s_id = suppliers.s_id;

• 使用 inner join 语法进行内连接查询

SELECT suppliers.s_id, s_name,f_name, f_price
FROM fruits
INNER JOIN suppliers ON fruits.s_id = suppliers.s_id;

再创建一张表orders

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

向表插入数据

2.左连接

SELECT customers.c_id, orders.o_num
FROM customers LEFT OUTER JOIN orders
ON customers.c_id = orders.c_id;

3.右连接

SELECT customers.c_id, orders.o_num
from customers RIGHT OUTER JOIN orders
ON customers.c_id = orders.c_id;

4.复合条件连接查询

SELECT customers.c_id, orders.o_num
FROM customers INNER JOIN orders
ON customers.c_id = orders.c_id AND customers.c_id = 10001;
SELECT suppliers.s_id, s_name,f_name, f_price
FROM fruits INNER JOIN suppliers
ON fruits.s_id = suppliers.s_id
ORDER BY fruits.s_id;

• 在fruits表和suppliers表之间使用内连接查询，查询之前，查看两个表的结构

SELECT suppliers.s_id, s_name,f_name, f_price
FROM fruits ,suppliers
WHERE fruits.s_id = suppliers.s_id;

• 在fruits表和suppliers表之间使用INNER JOIN语法进行内连接查询

SELECT suppliers.s_id, s_name,f_name, f_price
FROM fruits INNER JOIN suppliers
ON fruits.s_id = suppliers.s_id;

• 查询供应f_id='a1’的水果供应商提供的其他水果种类

SELECT f1.f_id, f1.f_name
FROM fruits AS f1, fruits AS f2
WHERE f1.s_id = f2.s_id AND f2.f_id = 'a1';

• 在customers表和orders表中，查询所有客户，包括没有订单的客户，SQL语法如下

SELECT customers.c_id, orders.o_num
FROM customers LEFT OUTER JOIN orders
ON customers.c_id = orders.c_id;

• 在customers表和orders表中，查询所有订单，包括没有客户的订单

SELECT customers.c_id, orders.o_num
from customers RIGHT OUTER JOIN orders
ON customers.c_id = orders.c_id;

• 在customers表和orders表中，使用INNER JOIN语法查询customers表中ID为10001、的客户的订单信息

SELECT customers.c_id, orders.o_num
FROM customers INNER JOIN orders
ON customers.c_id = orders.c_id AND customers.c_id = 10001;

• 在fruits表和suppliers表之间使用INNER JOIN语法进行内连接查询，并对查询结果排序

SELECT suppliers.s_id, s_name,f_name, f_price
FROM fruits INNER JOIN suppliers
ON fruits.s_id = suppliers.s_id
ORDER BY fruits.s_id;

下篇继续------------

回顾前五篇：

Mysql数据库(一）：详细，对新手非常友好

Mysql数据库(二）：详细，对新手非常友好

Mysql数据库(三）：详细，对新手非常友好

Mysql数据库(四）：详细，对新手非常友好

Mysql数据库(五）：详细，对新手非常友好