這個練習跟以下的練習有關
http://www.mysql.tw/2013/04/blog-post.html
這個練習資料庫的建立，以及簡單的用PHP來連結資料庫，然後做資料新增與顯示。
http://www.mysql.tw/2013/04/blog-post_18.html
這個練習直接使用SQL指令的SELECT來篩選需要的資料。
以下是練習的步驟:
(1) 為了後續的練習，我們必須先加上有意義的資料到資料表中，所以先把舊資料刪除了。
假設你的資料庫是newdb
//使用newdbname資料庫 ~ 紅色請依實際狀況修改
use newdbname;
//顯示裡面有哪些資料表
show tables;
//刪除舊的資料
delete from customer;
delete from order_body;
delete from order_head;
delete from product;
//插入有意義的資料 ~ customer
insert into customer(cus_id, cus_name, cus_address, cus_no) values (1, 'John', 'Yunlin', '001');
insert into customer(cus_id, cus_name, cus_address, cus_no) values (2, 'Mary', 'Taipei', '002');
insert into customer(cus_id, cus_name, cus_address, cus_no) values (3, 'Tim', 'Taichung', '003');
insert into customer(cus_id, cus_name, cus_address, cus_no) values (4, 'Helen', 'Tainan', '004');
insert into customer(cus_id, cus_name, cus_address, cus_no) values (5, 'Jim', 'Kaohsiung', '005');
//看看插入的資料
Select * from customer;
//插入有意義的資料 ~ product
insert into product (prod_id, prod_name, prod_price, prod_inventory, prod_no) values (1,'Pen', 25, 1000, '001');
insert into product (prod_id, prod_name, prod_price, prod_inventory, prod_no) values (2,'Pencil', 10, 2000, '002');
insert into product (prod_id, prod_name, prod_price, prod_inventory, prod_no) values (3,'Pencil Box', 50, 500, '003');
insert into product (prod_id, prod_name, prod_price, prod_inventory, prod_no) values (4,'Notebook', 30, 1500, '004');
insert into product (prod_id, prod_name, prod_price, prod_inventory, prod_no) values (5,'Eraser', 15, 25000, '005');
//看看插入的資料
Select * from product;
//假設要改單筆資料 ~ 修改客戶id是1的客戶，把名字改為 Johnson
update customer set cus_name='Johnson' where cus_id=1;
語法
UPDATE table_name SET 欄位1=欄位1的值, 欄位2=欄位2的值 .... WHERE 條件;
//插入有意義的資料 ~ order_head, order_body
insert into order_head (ord_id, ord_cus_id, ord_date, ord_date_delivery) values (1, 1, '2017/01/01', '2017/01/07');
insert into order_head (ord_id, ord_cus_id, ord_date, ord_date_delivery) values (2, 1, '2017/01/05', '2017/01/12');
insert into order_head (ord_id, ord_cus_id, ord_date, ord_date_delivery) values (3, 2, '2017/01/06', '2017/01/10');
insert into order_head (ord_id, ord_cus_id, ord_date, ord_date_delivery) values (4, 3, '2017/02/01', '2017/02/12');
insert into order_head (ord_id, ord_cus_id, ord_date, ord_date_delivery) values (5, 4, '2017/02/03', '2017/02/07');
insert into order_head (ord_id, ord_cus_id, ord_date, ord_date_delivery) values (6, 5, '2017/02/02', '2017/02/20');
insert into order_body (ordb_id, ordb_ord_id, ordb_prod_id, ordb_amount) values (1,1,1,5);
insert into order_body (ordb_id, ordb_ord_id, ordb_prod_id, ordb_amount) values (2,1,2,10);
insert into order_body (ordb_id, ordb_ord_id, ordb_prod_id, ordb_amount) values (3,1,3,15);
insert into order_body (ordb_id, ordb_ord_id, ordb_prod_id, ordb_amount) values (4,2,2,20);
insert into order_body (ordb_id, ordb_ord_id, ordb_prod_id, ordb_amount) values (5,2,4,12);
insert into order_body (ordb_id, ordb_ord_id, ordb_prod_id, ordb_amount) values (6,3,2,22);
insert into order_body (ordb_id, ordb_ord_id, ordb_prod_id, ordb_amount) values (7,4,5,11);
insert into order_body (ordb_id, ordb_ord_id, ordb_prod_id, ordb_amount) values (8,5,3,26);
insert into order_body (ordb_id, ordb_ord_id, ordb_prod_id, ordb_amount) values (9,6,1,30);
insert into order_body (ordb_id, ordb_ord_id, ordb_prod_id, ordb_amount) values (10,6,2,70);
insert into order_body (ordb_id, ordb_ord_id, ordb_prod_id, ordb_amount) values (11,6,5,200);
//看看插入的資料
select * from order_head;
select * from order_body;
以下就可以來進行SELECT的指令練習了。
SELECT的基本語法如下~ 依照給予的條件，抓出資料。
SELECT * | {[DISTINCT, DISTINCTROW] field_list}
FROM table_list
WHERE conditions
GROUP BY {field_list} [ASC,DESC]
HAVING conditions
ORDER BY {field_list} [ASC,DESC]
(1) 列出客戶資料表 (customer)中的所有客戶代號、客戶名稱。
SELECT cus_no, cus_name FROM customer;
(2) 找到客戶代號為001的客戶地址。
SELECT cus_address FROM customer WHERE cus_no='001';
(3) 找到客戶名稱中存在"i"字串的所有客戶地址。
SELECT cus_address FROM customer WHERE cus_name like '%i%';
(4) 找到客戶名稱中以"m"字串結尾的所有客戶地址。
SELECT cus_address FROM customer WHERE cus_name like '%m';
(5) 以訂單中的產品id為羣組，得到訂單中各產品的訂購總額。
透過GROUP BY找某類型的總額~ SELECT "欄位1", SUM("欄位2") FROM "表格名" GROUP BY "欄位1"
SELECT ordb_prod_id, SUM(ordb_amount) FROM order_body
GROUP BY ordb_prod_id;
(6) 以訂單中的產品id為羣組，得到訂單中各產品的訂購總額。但是只列出總額>100的資料。
透過HAVING限定函數條件
SELECT "欄位1", SUM("欄位2") FROM "表格名" GROUP BY "欄位1" HAVING (函數條件)
如果被 SELECT 的只有函數欄， 那就不需要 GROUP BY 子句。
SELECT ordb_prod_id, SUM(ordb_amount) FROM order_body
GROUP BY ordb_prod_id
HAVING SUM(ordb_amount) > 100;
(7) 透過ORDER BY做排序
SELECT ordb_prod_id, SUM(ordb_amount) FROM order_body
GROUP BY ordb_prod_id
ORDER BY SUM(ordb_amount) DESC; //大到小排序
SELECT ordb_prod_id, SUM(ordb_amount) FROM order_body
GROUP BY ordb_prod_id
ORDER BY SUM(ordb_amount) ASC; //小到大排序, 這是預設的排序方式
(8) 使用JOIN ~ 找到訂購產品編號是1的客戶編號
SELECT ord_cus_id
FROM order_head
JOIN order_body
ON order_head.ord_id=order_body.ordb_ord_id
WHERE order_body.ordb_prod_id=1;
跟以下有何不同?
SELECT ord_cus_id FROM order_head, order_bodyWHERE order_head.ord_id=order_body.ordb_ord_id
AND order_body.ordb_prod_id=1;
(9) 以上得到了客戶編號，但是如果想要找到訂購產品編號是1的客戶姓名呢?
因為客戶姓名在customer表單上，所以變成要參考三個表單 : customer, order_head, order_body
SELECT cus_name FROM customer, order_head, order_body
WHERE order_head.ord_id=order_body.ordb_ord_id
AND order_body.ordb_prod_id=1
AND cus_id=ord_cus_id;
執行結果如下
SELECT更多參考
http://www.mysql.tw/2013/05/select.html
http://www.mysql.tw/2014/05/sql-select.html
SELECT & JOIN更多參考
){kind=link}
0 留言