- ########################
- # 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)
- ) ENGINE=InnoDB;
- #####################
- # 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)
- ) ENGINE=InnoDB;
- #######################
- # 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)
- ) ENGINE=InnoDB;
- ######################
- # 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)
- ) ENGINE=InnoDB;
- ###########################
- # Create productnotes table
- ###########################
- CREATE TABLE productnotes
- (
- note_id int NOT NULL AUTO_INCREMENT,
- prod_id char(10) NOT NULL,
- 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';
③内连接
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，如需转载请自行联系原作者
