實作練習 (再續)


這個練習跟以下的練習有關

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_body
WHERE 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更多參考


留言

這個網誌中的熱門文章

如何使用EXCEL連接MYSQL

關聯模式的五大鍵 Super key、Candidate Key、Primary Key、Alternate Key、Foreign Key

SELECT SQL語法總整理