程式開發學習之路

MySQL的變數長相為@zzzxxxyyy，小老鼠+你想取的名稱，整串@zzzxxxyyy為一個變數。
宣告分兩種，其一外在宣告，其二內在宣告

首先介紹內在宣告，以下將舉例講解︰
先新增測試假資料
CREATE TABLE heyf_t10 (
empid INT ,
deptid INT ,
salary DECIMAL(10, 2)
);
INSERT INTO heyf_t10 VALUES
(1,10,5500.00),
(2,10,4500.00),
(3,20,1900.00),
(4,20,4800.00),
(5,40,6500.00),
(6,40,14500.00),
(7,40,44500.00),
(8,50,6500.00),
(9,50,7500.00);


接著，開始對這些假資料做操作，下方SQL中(SELECT @rownum := 0, @pdept := NULL , @rank := 0) AS a ，這裡就是做初步的變數宣告，而此變數宣告必須給初值，給值的符號為 := 中間不可空格，先宣告後就可以使用了。
SELECT
         empid,
         deptid,
         salary,
         rank
FROM
         (SELECT
                  heyf_tmp.empid,
                  heyf_tmp.deptid,
                  heyf_tmp.salary,
                  @rownum := @rownum+1 ,
                  /*上句意義是，變數@rownum每歷經一筆資料都加1*/
                  IF(@pdept = heyf_tmp.deptid, @rank := @rank+1, @rank := 1) AS rank,
                  /*上句意義是，變數@pdept 如果等於deptid值則@rank+1，否則@rank 給值 1*/
                  @pdept := heyf_tmp.deptid
                  /*上句意義，變數@pdept給值deptid*/
         FROM
                  (SELECT @rownum := 0, @pdept := NULL , @rank := 0) AS a ,
                  (SELECT
                           empid,
                           deptid,
                           salary
                  FROM
                           heyf_t10
                  ORDER BY
                           deptid ASC ,salary DESC
                  ) heyf_tmp
         ) AS result

結果︰先依照deptid分組，再依照salary來做排序的動作。

UNION ALL 與UNION 是MySQL能使用的聯集查詢，兩種語法相同但是效果不同，以下將會舉例介紹。

語法︰
<SELECT查詢一>
UNION [ALL]
<SELECT查詢二>

DISTINCT 及GROUP BY 都是用來做去除重複資料的關鍵字，但用法不同，以下將針對這兩種做比較介紹


使用DISTINCT，必須放在SELECT區塊內第一個欄位的前方，如下列語句所示位置，其代表的意義是後面所跟的所有欄位都必須相同才能算重複的資料，換言之如果確認欲查出的欄位必須同時去除重複資料才能使用這個。
SELECT      DISTINCT id, user_id, order_date, order_status
FROM         user_order
ORDER BY id

結果︰

計算出每筆訂單有幾筆商品

SELECT 
      user_order_A.id,
      user_order_A.name,
      COUNT(user_order_A.id)
FROM  user_order_A
      JOIN products_table
        ON(user_order_A.id= products_table.order_id)
GROUP BY user_order_A.id;

結果：

使用SUM及GROUP BY

SUM是總合，將此欄位加總
GROUP BY 是群組，也可以說是分組。
GROUP BY 可以配合SUM ，COUNT。

此查詢將同一訂單編號，的金額加總。

SELECT 
              user_order_A.id,
              user_order_A.name,
              SUM(products_table.price)AS price
FROM user_order_A
            JOIN products_table
            ON(user_order_A.id= products_table.order_id)
GROUP BY user_order_A.id;

使用MAX
找出每個訂單裡的最貴的商品價格
SELECT MAX(p.price),p.order_id,p.product_name
FROM products_table ASp
GROUP BY p.order_id;

子查詢，意即查詢中還有另一個查詢，子查詢放置的位置可以在select、from、where中，有其個別用法及意義。


個別用法將在底下一一介紹

#假如這是記錄使用者的訂單
drop table user_order_A;
CREATE TABLE user_order_A (
id bigint(20) NOT NULL AUTO_INCREMENT COMMENT '訂單ID',
name varchar(20) NOT NULL COMMENT '誰的訂單',
PRIMARY KEY(id)
)AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
#假如有A、B、C三位使用者下單
insert into user_order_A(id,name) values (1, 'A');
insert into user_order_A(id,name) values (2, 'B');
insert into user_order_A(id,name) values (3, 'C');
insert into user_order_A(id,name) values (4, 'A');
insert into user_order_A(id,name) values (5, 'B');
insert into user_order_A(id,name) values (6, 'C');

#假如這是記錄每訂單的項目價錢
drop table products_table;
CREATE TABLE products_table (
  order_id bigint(20) NOT NULL COMMENT '關連訂單id',
  product_name varchar(255) NOT NULL,
  price bigint(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
insert into products_table(order_id,product_name,price) values (1, "商品1", 20);
insert into products_table(order_id,product_name,price) values (3, "商品2", 10);
insert into products_table(order_id,product_name,price) values (2, "商品3", 50);
insert into products_table(order_id,product_name,price) values (5, "商品4", 10);
insert into products_table(order_id,product_name,price) values (5, "商品5", 10);
insert into products_table(order_id,product_name,price) values (6, "商品6", 80);
insert into products_table(order_id,product_name,price) values (1, "商品7", 10);
insert into products_table(order_id,product_name,price) values (2, "商品8", 10);
insert into products_table(order_id,product_name,price) values (3, "商品9", 40);
insert into products_table(order_id,product_name,price) values (2, "商品2", 10);
insert into products_table(order_id,product_name,price) values (4, "商品1", 20);

#使用子查詢select底下
SELECT user_order_A.id,
               user_order_A.name,
               (SELECT 
                              SUM(products_table.price)
               FROM
                              products_table
               WHERE user_order_A.id = products_table.order_id
               ) AS price
FROM
               user_order_A ;
結果：

NATURAL JOIN 自動依據相同名稱的欄位來做聯集，方式與INNER JOIN相同，結果略為不同

使用一般JOIN
語句︰
SELECT *
FROM occupation AS occ
            JOIN
                    user_order AS uo
            ON
                    occ.user_id = uo.user_id

結果︰所有有對應上的欄位都會出現

 STRAIGHT_JOIN 強迫MySQL使用我們的順序去做JOIN，其JOIN結果跟INNER JOIN相同

首先先CREATE新關聯TABLE以及INSERT資料

CREATE TABLE user_order (
id bigint(20) NOT NULL AUTO_INCREMENT ,
user_id varchar(20) NOT NULL ,
order_date datetime NOT NULL,
order_status int(20) NOT NULL DEFAULT 0 COMMENT '0未確認訂單，1待付款，2已結案',
PRIMARY KEY(id),
FOREIGN KEY fk_user_order(user_id) REFERENCES user(id)
)AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

INSERT INTO user_order (user_id, order_date, order_status )
         VALUES (1, '2011-05-21', 2),
                           (2, '2011-06-25', 2),
                           (3, '2011-09-13', 1),
                           (4, '2011-12-25', 0);

一般JOIN情況如下
SELECT *
FROM occupation
         JOIN user ON occupation.user_id = user.id
         JOIN user_order ON user_order.user_id = user.id

結果


使用EXPLAIN 來看執行順序
EXPLAIN SELECT *
FROM occupation
         JOIN user ON occupation.user_id = user.id
         JOIN user_order ON user_order.user_id = user.id
執行順序 user_order → occupation→ user

RIGHT JOIN是將右邊表格全顯示，意即以右邊表格的資料為主關聯左邊表格，若左邊表格有右邊表格的對應則顯示對應資料，若左邊沒右邊的資料則顯示null。

我們將occupation table insert 一筆資料，是沒有與user關連。
INSERT INTO occupation (occupation,salary,user_id)
VALUES ('程式設計師',40000,7);


SELECT *
FROM user AS u
     LEFT JOIN occupation AS o
     ON(u.id = o.user_id);

如果使用INNER JOIN 及 LEFT JOIN，因新資料尚未有關連所以不會出顯剛新增的資料。

必須使用RIGHT JOIN
SELECT *
FROM user AS u
     RIGHT JOIN occupation AS o
     ON(u.id = o.user_id);
結果：