1. ######################## 
2. # Create customers table 
3. ######################## 
4. CREATE TABLE customers 
5. ( 
6.   cust_id      int       NOT NULL AUTO_INCREMENT, 
7.   cust_name    char(50)  NOT NULL , 
8.   cust_address char(50)  NULL , 
9.   cust_city    char(50)  NULL , 
10.   cust_state   char(5)   NULL , 
11.   cust_zip     char(10)  NULL , 
12.   cust_country char(50)  NULL , 
13.   cust_contact char(50)  NULL , 
14.   cust_email   char(255) NULL , 
15.   PRIMARY KEY (cust_id) 
16. ) ENGINE=InnoDB; 
17.  
18. ######################### 
19. # Create orderitems table 
20. ######################### 
21. CREATE TABLE orderitems 
22. ( 
23.   order_num  int          NOT NULL , 
24.   order_item int          NOT NULL , 
25.   prod_id    char(10)     NOT NULL , 
26.   quantity   int          NOT NULL , 
27.   item_price decimal(8,2) NOT NULL , 
28.   PRIMARY KEY (order_num, order_item) 
29. ) ENGINE=InnoDB; 
30.  
31.  
32. ##################### 
33. # Create orders table 
34. ##################### 
35. CREATE TABLE orders 
36. ( 
37.   order_num  int      NOT NULL AUTO_INCREMENT, 
38.   order_date datetime NOT NULL , 
39.   cust_id    int      NOT NULL , 
40.   PRIMARY KEY (order_num) 
41. ) ENGINE=InnoDB; 
42.  
43. ####################### 
44. # Create products table 
45. ####################### 
46. CREATE TABLE products 
47. ( 
48.   prod_id    char(10)      NOT NULL, 
49.   vend_id    int           NOT NULL , 
50.   prod_name  char(255)     NOT NULL , 
51.   prod_price decimal(8,2)  NOT NULL , 
52.   prod_desc  text          NULL , 
53.   PRIMARY KEY(prod_id) 
54. ) ENGINE=InnoDB; 
55.  
56. ###################### 
57. # Create vendors table 
58. ###################### 
59. CREATE TABLE vendors 
60. ( 
61.   vend_id      int      NOT NULL AUTO_INCREMENT, 
62.   vend_name    char(50) NOT NULL , 
63.   vend_address char(50) NULL , 
64.   vend_city    char(50) NULL , 
65.   vend_state   char(5)  NULL , 
66.   vend_zip     char(10) NULL , 
67.   vend_country char(50) NULL , 
68.   PRIMARY KEY (vend_id) 
69. ) ENGINE=InnoDB; 
70.  
71. ########################### 
72. # Create productnotes table 
73. ########################### 
74. CREATE TABLE productnotes 
75. ( 
76.   note_id    int           NOT NULL AUTO_INCREMENT, 
77.   prod_id    char(10)      NOT NULL, 
78.   note_date datetime       NOT NULL, 
79.   note_text  text          NULL , 
80.   PRIMARY KEY(note_id), 
81.   FULLTEXT(note_text) 
82. ) ENGINE=MyISAM; 
83.  
84.  
85. ##################### 
86. # Define foreign keys 
87. ##################### 
88. ALTER TABLE orderitems ADD CONSTRAINT fk_orderitems_orders FOREIGN KEY (order_num) REFERENCES orders (order_num); 
89. ALTER TABLE orderitems ADD CONSTRAINT fk_orderitems_products FOREIGN KEY (prod_id) REFERENCES products (prod_id); 
90. ALTER TABLE orders ADD CONSTRAINT fk_orders_customers FOREIGN KEY (cust_id) REFERENCES customers (cust_id); 
91. 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';

 

③内连接

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

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

left join orders

on customers.cust_id=orders.cust_id

group by customers.cust_id;

 

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