mysql总结5→多表查询

######################## 

# Create customers table 

CREATE TABLE customers 

( 

  cust_id      int       NOT NULL AUTO_INCREMENT, 

  cust_name    char(50)  NOT NULL , 

  cust_address char(50)  NULL , 

  cust_city    char(50)  NULL , 

  cust_state   char(5)   NULL , 

  cust_zip     char(10)  NULL , 

  cust_country char(50)  NULL , 

  cust_contact char(50)  NULL , 

  cust_email   char(255) NULL , 

  PRIMARY KEY (cust_id) 

) ENGINE=InnoDB; 

######################### 

# Create orderitems table 

CREATE TABLE orderitems 

  order_num  int          NOT NULL , 

  order_item int          NOT NULL , 

  prod_id    char(10)     NOT NULL , 

  quantity   int          NOT NULL , 

  item_price decimal(8,2) NOT NULL , 

  PRIMARY KEY (order_num, order_item) 

##################### 

# Create orders table 

CREATE TABLE orders 

  order_num  int      NOT NULL AUTO_INCREMENT, 

  order_date datetime NOT NULL , 

  cust_id    int      NOT NULL , 

  PRIMARY KEY (order_num) 

####################### 

# Create products table 

CREATE TABLE products 

  prod_id    char(10)      NOT NULL, 

  vend_id    int           NOT NULL , 

  prod_name  char(255)     NOT NULL , 

  prod_price decimal(8,2)  NOT NULL , 

  prod_desc  text          NULL , 

  PRIMARY KEY(prod_id) 

###################### 

# Create vendors table 

CREATE TABLE vendors 

  vend_id      int      NOT NULL AUTO_INCREMENT, 

  vend_name    char(50) NOT NULL , 

  vend_address char(50) NULL , 

  vend_city    char(50) NULL , 

  vend_state   char(5)  NULL , 

  vend_zip     char(10) NULL , 

  vend_country char(50) NULL , 

  PRIMARY KEY (vend_id) 

########################### 

# Create productnotes table 

CREATE TABLE productnotes 

  note_id    int           NOT NULL AUTO_INCREMENT, 

  note_date datetime       NOT NULL, 

  note_text  text          NULL , 

  PRIMARY KEY(note_id), 

  FULLTEXT(note_text) 

) ENGINE=MyISAM; 

# Define foreign keys 

ALTER TABLE orderitems ADD CONSTRAINT fk_orderitems_orders FOREIGN KEY (order_num) REFERENCES orders (order_num); 

ALTER TABLE orderitems ADD CONSTRAINT fk_orderitems_products FOREIGN KEY (prod_id) REFERENCES products (prod_id); 

ALTER TABLE orders ADD CONSTRAINT fk_orders_customers FOREIGN KEY (cust_id) REFERENCES customers (cust_id); 

ALTER TABLE products ADD CONSTRAINT fk_products_vendors FOREIGN KEY (vend_id) REFERENCES vendors (vend_id); 

1.子查询：子查询指的是嵌套在查询中的查询；

eg1.获取订购商品编号为TNT2的客户名：

（普通的方式为：）

①从订单详情表中获取订单编号：

select order_num from orderitems where prod_id='TNT2';

②根据订单编号获取下订单的客户ID：

select cust_id from orders where order_num in ('2005','2007');

③根据客户ID获取客户的姓名：

select cust_name from customers where cust_id in ('10001','10004');

把上面的查询换成子查询为：③(②(①))

eg2.获取每个客户下的订单的是数量：

select cust_name,(select count(*) from orders where orders.cust_id = customers.cust_id) from customers;

2.连接查询

<1>.内连接（inner join）：又称等值连接。（等值连接中注意使用where条件，不使用where条件将会产生笛卡尔集。）

eg1.

等值连接：

select prod_id,products.vend_id,prod_name 

from products,vendors 

where products.vend_id=vendors.vend_id;

内连接：(...inner join ... on ...)

select prod_id,products.vend_id,prod_name

from products inner join vendors

on products.vend_id = vendors.vend_id;

eg2.

获取订购商品编号为TNT2的客户号：

select cust_name from customers,orders,orderitems

where customers.cust_id=orders.cust_id

and orders.order_num=orderitems.order_num

and orderitems.prod_id='TNT2';

内连接：

select customers.cust_id,cust_name from customers 

inner jion orders on customers.cust_id=orders.cust_id

inner jion orderitems on orders.order_num=orderitems.order_num

where orderitems.prod_id='TNT2';

注意事项：尽量不要连接不需要的表，连接的表越多效能越低！！

eg3.

获得生产DTNTR商品的供应商生产的其他的产品：

①子查询（推荐）

select prod_id,vend_id,prod_name

from products

where vend_id=(select vend_id from products where prod_id='DTNTR'); 

②自连接：（懂的）

select p1.prod_id,p1.vend_id,p1.prod_name

from products as p1,products as p2

where p1.vend_id = p2.vend_id

and p2.prod_id='DTNTR';

③内连接

from products as p1

inner join products as p2

on p1.vend_id=p2.vend_id

where p2.prod_id='DTNTR';

<2>.外连接：

概念性说明：

(1.)内连接：两个表存在主外键关系时，常用内连接查询。

(2.)左外连接：结果包括左表的所有行，如果左表的行在表中没有匹配的行，则为空值。

（3.）左外连接：（left outer jion） → outer可以省略。

eg1.一个学生表t_student;一个成绩表t_score,里面存一个sid引用的是学生表的外键。

select name,score from t_student 

left join t_score 

on t_score.sid=t_student.id;

eg2.获取所有用户id及下的订单编号，包含未下的订单用户：（典型性）

select customers.cust_id,orders.order_num

from custtomers

left join orders

on customers.cust_id=orders.cust_id;

eg3.查询所有客户及每个客户所下的订单数：

select customers.cust_name,count(orders.order_num)

from customers

on customers.cust_id=orders.cust_id

group by customers.cust_id;

      本文转自韩立伟 51CTO博客，原文链接：http://blog.51cto.com/hanchaohan/924585，如需转载请自行联系原作者