java mysql 多表查询_mysql总结5→多表查询

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

# Createcustomerstable

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

CREATETABLEcustomers

(

cust_id      intNOTNULLAUTO_INCREMENT,

cust_name    char(50)NOTNULL,

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,

PRIMARYKEY(cust_id)

) ENGINE=InnoDB;

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

# Createorderitemstable

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

CREATETABLEorderitems

(

order_num  intNOTNULL,

order_item intNOTNULL,

prod_id    char(10)NOTNULL,

quantity   intNOTNULL,

item_price decimal(8,2)NOTNULL,

PRIMARYKEY(order_num, order_item)

) ENGINE=InnoDB;

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

# Createorderstable

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

CREATETABLEorders

(

order_num  intNOTNULLAUTO_INCREMENT,

order_date datetime NOTNULL,

cust_id    intNOTNULL,

PRIMARYKEY(order_num)

) ENGINE=InnoDB;

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

# Createproductstable

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

CREATETABLEproducts

(

prod_id    char(10)NOTNULL,

vend_id    intNOTNULL,

prod_name  char(255)NOTNULL,

prod_price decimal(8,2)NOTNULL,

prod_desc  text          NULL,

PRIMARYKEY(prod_id)

) ENGINE=InnoDB;

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

# Createvendorstable

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

CREATETABLEvendors

(

vend_id      intNOTNULLAUTO_INCREMENT,

vend_name    char(50)NOTNULL,

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,

PRIMARYKEY(vend_id)

) ENGINE=InnoDB;

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

# Createproductnotestable

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

CREATETABLEproductnotes

(

note_id    intNOTNULLAUTO_INCREMENT,

prod_id    char(10)NOTNULL,

note_date datetime       NOTNULL,

note_text  text          NULL,

PRIMARYKEY(note_id),

FULLTEXT(note_text)

) ENGINE=MyISAM;

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

# Define foreignkeys

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

ALTERTABLEorderitemsADDCONSTRAINTfk_orderitems_ordersFOREIGNKEY(order_num)REFERENCESorders (order_num);

ALTERTABLEorderitemsADDCONSTRAINTfk_orderitems_productsFOREIGNKEY(prod_id)REFERENCESproducts (prod_id);

ALTERTABLEordersADDCONSTRAINTfk_orders_customersFOREIGNKEY(cust_id)REFERENCEScustomers (cust_id);

ALTERTABLEproductsADDCONSTRAINTfk_products_vendorsFOREIGNKEY(vend_id)REFERENCESvendors (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

onproducts.vend_id = vendors.vend_id;

eg2.

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

等值连接：

select cust_name from customers,orders,orderitems

wherecustomers.cust_id=orders.cust_id

andorders.order_num=orderitems.order_num

andorderitems.prod_id='TNT2';

内连接：

select customers.cust_id,cust_name from customers

inner jionorders on customers.cust_id=orders.cust_id

inner jionorderitems on orders.order_num=orderitems.order_num

whereorderitems.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';

③内连接

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

from products as p1

inner joinproducts as p2

onp1.vend_id=p2.vend_id

wherep2.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

oncustomers.cust_id=orders.cust_id;

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

select customers.cust_name,count(orders.order_num)

from customers

left join orders

on customers.cust_id=orders.cust_id

group by customers.cust_id;