[MySQL]2-15 宣告變數及其應用

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來做排序的動作。

以上是內部變數宣告的應用，其實會這樣用的SQL不多見，只是提供個方式可以這樣做而已

---

接下來介紹外部宣告
外部宣告意即會在主SQL外做額外的SET @zzzxxxyyy宣告的動作，以下將使用範例解說。

SET @A = '測試';
SELECT user.*, @A
FROM user;
上下兩句是相同的，因為是額外的語句做宣告所以可以直接使用 = 號
SET @A := '測試';
SELECT user.*, @A
FROM user;

結果︰@A可以接受到範例中的測試字樣，但其實可以接受任何值

內部宣告沒給初值會錯，外部宣告也會錯，但值得注意的是，內部一錯整句SQL都會錯導致無法執行，但外部宣告的情況將會演變成宣告式是錯的，底下的查詢卻正常運行，而該變數的值就會變成NULL
SET @A;
SELECT user.*, @A
FROM user;

結果︰變數@A沒有得到任何值，皆顯示NULL

其它文章

[MySQL]2-14 UNION ALL 及 UNION 使用比較(附加FULL JOIN)

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

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

※特別注意︰要執行UNION [ALL]聯集查詢，上下兩個查詢所得的欄位數及欄位屬性必須相同

使用UNION ALL，會將上下兩個查詢的結果全部都列出來。
SELECT *
FROM   user_order
             LEFT JOIN
             user
             ON
             user.id = user_order.user_id
UNION ALL
SELECT *
FROM   user_order
             RIGHT JOIN
             user
             ON
             user.id = user_order.user_id

結果︰兩種查詢結果都會逐一呈現

使用UNION，當上下兩結果集合併時，將會把相同的結果排除重複，呈現不重複的資料結果
SELECT *
FROM   user_order
             LEFT JOIN
             user
             ON
             user.id = user_order.user_id
UNION
SELECT *
FROM   user_order
             RIGHT JOIN
             user
             ON
             user.id = user_order.user_id

結果:只呈現出兩種查詢不重複的資料結果

上方所示UNION語句範例，同時具有FULL OUTER JOIN的功能，但MySQL並不支援FULL OUTER JOIN，所以如果有需要使用到FULL OUTER JOIN的人可以使用上方所示的語法去做

其它文章

[MySQL]2-13 DISTINCT vs GROUP BY 的差異比較

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


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

結果︰

使用GROUP BY，必須放在WHERE區塊之後ORDER BY之前，如下列語句所示位置，其後跟隨撰寫者想要去除重複資料的欄位名稱，代表的意義是後面所跟的所有欄位都必須相同才能算重複的資料，下列語句結果會與上方DISTINCT的結果相同，因為兩個所限制的欄位都相同。
SELECT      id, user_id, order_date, order_status
FROM         user_order
GROUP BY id, user_id, order_date, order_status
ORDER BY id

結果︰

使用GROUP BY，本次所限制的欄位與上兩個語句不同，當user_id, order_status兩欄位相同就算重複的資料，結果就會與上方語句不同了。
SELECT      id, user_id, order_date, order_status
FROM         user_order
GROUP BY user_id, order_status
ORDER BY id

結果︰

總結︰DISTINCT會與要查找的欄位交互影響，有時候不是那麼好用，GROUP BY則沒有此限制，且GROUP BY 在對於有建立INDEX的TABLE查找的效率較佳，但DISTINCT在對於沒有INDEX的TABLE效率較佳，端看撰寫者所需來選用。

---

額外補充DISTINCT的特別用法
可以搭配COUNT()函式，針對某一個欄位做DISTINCT的計數，語句如下所示，表示計數不重複的user_id有多少個，計數不重複的order_status有多少個。
SELECT id, COUNT(DISTINCT user_id), order_date, COUNT(DISTINCT order_status)
FROM    user_order

結果︰因為使用了COUNT()函式所以只會出現一筆資料，故這個方法只適合計次的SELECT

其它文章

[MySQL]2-12使用COUNT及GROUP BY

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

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;

結果：

其它文章

[MySQL]2-11使用SUM及GROUP BY

使用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;

結果：

其它文章

[MySQL]2-10使用MAX及MIN及GROUP BY

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

結果：

找出最貴的商品價格
SELECT MAX(p.price)
FROM products_table AS p
結果：

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

結果：

找出最便宜的商品價格
SELECT MIN(p.price)
FROM products_table AS p
結果：

其它文章

[MySQL]2-9使用子查詢

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

位置	Select區間	From區間	Where區間
意義	一個子查詢回傳資料給母查詢當作一個欄位使用，只能回傳一筆單欄資料，此時的where條件能與母查詢的from區塊比對。	當作一個templatetable，可以給select、where直接使用，也可以與join系列聯合使用。	當作一種條件比對資料，透過in、exist、=等運算子來做條件限制

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

#假如這是記錄使用者的訂單
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 ;
結果：

使用在FROM後的JOIN子查詢
SELECT 
              user_order_A.id,
              user_order_A.name,
              products_table.price
FROM   user_order_A
             JOIN (SELECT
                                      SUM(products_table.price),
                                      products_table.order_id ,
                                      products_table.price
                         FROM products_table
                        GROUP BY products_table.order_id
                      )AS products_table
             ON(user_order_A.id= products_table.order_id)
GROUP BY user_order_A.id;
結果：

使用在WHERE後的子查詢條件資料
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

WHERE

               user_order_A.name IN (SELECT name FROM user WHERE user.id > 0)
結果︰

其它文章

[MySQL]2-8表格連接用法(NATURAL JOIN)

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

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

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

使用NATURAL JOIN，資料會只會出現叉集後的結果，並且相同的欄位名稱只會出現一次
語句︰
SELECT *
FROM occupation AS occ
            NATURAL JOIN
            user_order AS uo;

結果︰自動關聯user_id欄位，並只出現叉集後結果

使用NATURAL JOIN並指定個別欄位，相同欄位名稱不會自動出現，但依然可以指定
語句︰
SELECT occ.id,
               occ.user_id,
               occ.occupation,
               occ.salary,
               uo.id,
               uo.user_id,
               uo.order_date,
               uo.order_status
FROM occupation AS occ
            NATURAL JOIN
            user_order AS uo

結果︰依照指定查出特定欄位

除了上方所述一般使用之外，NATURAL JOIN還可以加入LEFT or RIGHT來做左右關聯

使用NATRUAL LEFT JOIN
語句︰
SELECT *
FROM occupation AS occ
            NATURAL LEFT JOIN
            user_order AS uo;

結果︰以occupation為主關聯user_order，並將相同名稱的欄位併成一個

使用NATRUAL RIGHT JOIN
語句︰
SELECT *
FROM occupation AS occ
            NATURAL RIGHT JOIN
            user_order AS uo;

結果︰以user_order為主關聯occupation，並將相同名稱的欄位併成一個

總結︰如果確定兩個TABLE有相同名稱的欄位(最好只有一個相同欄位)，並且確知其資料的關聯性，而你想要的資料結果是叉集，則可使用NATRUAL JOIN。

\

其它文章

[MySQL]2-7表格連接用法(STRAIGHT_JOIN)

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

使用STRAIGHT_JOIN之後，結果相同，但執行的順序不同
SELECT *
FROM occupation
         STRAIGHT_JOIN user ON occupation.user_id = user.id
         STRAIGHT_JOIN user_order ON user_order.user_id = user.id
結果

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

執行順序 occupation → user→ user_order

總結︰未使用STRAIGHT_JOIN使用其他JOIN MySQL會自己找尋已經有INDEX的關聯尋找先後順序做關聯表通常這會是系統最佳效能了，使用STRAIGHT_JOIN會依照撰寫者所寫的順序去JOIN，效能部份就必須撰寫者自行調效。簡而言之，如果撰寫者能把握最有效率的JOIN順序與條件，那麼就可以使用STRAIGHT_JOIN，反之則讓系統自行處理，切勿濫用。

其它文章

[MySQL]2-6 表格連接用法(RIGHT JOIN)

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);
結果：

其它文章

[MySQL]2-5 表格連接用法(LEFT JOIN)

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

我們將user table insert 一筆資料，是沒有occupation關連。
INSERT INTO user (id,username,account_number)
VALUES (5,'Mr. Jack','Jack123');

SELECT *
FROM user AS u
     INNER JOIN occupation AS o
     ON(u.id = o.user_id);
如果使用INNER JOIN，因為該資料沒有關聯到occupation，所以不會出顯剛新增的user資料。

必須使用LEFT JOIN

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

其它文章

[MySQL]2-4 表格連接用法(Cartesian Joins)

Cartesian Joins 笛卡兒聯接

SELECT *
FROM 表格A ,表格B
將表格所有可能聯接，全都資料聯接。
如果要限制，可以在WHERE加條件。

例：
SELECT *
FROM user AS u, occupation AS o

結果：

例：
SELECT *
FROM user AS u, occupation AS o
WHERE u.id = o.user_id
結果：

具有相同效果的JOIN語句是 CROSS JOIN
此JOIN不需要ON的條件限制，與table1,table2 效果相同
例︰

SELECT *
FROM user AS u CROSS JOIN occupation AS o

SELECT *
FROM user AS u CROSS JOIN occupation AS o
WHERE u.id = o.user_id

其它文章

[MySQL]2-3 表格連接用法(JOIN)

SELECT *
FROM 表格A INNER JOIN 表格B
ON(表格A.欄位 = 表格B.欄位 )
注意

INNER JOIN

及JOIN 是一樣的

例：
SELECT *
FROM user AS u
     INNER JOIN occupation AS o
     ON(u.id = o.user_id);
等同
SELECT *
FROM user AS u
           JOIN occupation AS o
           ON(u.id = o.user_id);


結果：

其它文章

[MySQL]2-2使用LIKE 查詢資料用法

LIKE

在 LIKE 前面加上 NOT ,NOT LIKE表示与 LIKE 相反的意思。
LIKE 通常配 % 一起使用，
% 表示不比對，全部都要。

MYSQL LIKE是不分大小寫的，以下的查詢會查出一樣的。
查詢名字是mr 開頭的。
SELECT * FROM user AS u WHERE u.username LIKE 'mr%';
SELECT * FROM user AS u WHERE u.username LIKE 'Mr%';
SELECT * FROM user AS u WHERE u.username LIKE 'MR%';
但使用BINARY 二進制編碼，就可以區分大小寫了
例：
SELECT * FROM user AS u WHERE BINARY  u.username LIKE 'mr%';
SELECT * FROM user AS u WHERE BINARY  u.username LIKE 'Mr%';
SELECT * FROM user AS u WHERE BINARY  u.username LIKE 'MR%';

SELECT 'abc' LIKE 'ABC';
-> 1
SELECT 'abc' LIKE BINARY 'ABC';
> 0

也可以在CREATE TABLE 中就指定好
CREATE TABLE user (
id bigint(20) NOT NULL AUTO_INCREMENT COMMENT '這是PK_ID',
username varchar(20) BINARY NOT NULL DEFAULT 'ERIC',
...
)

其它文章

[MySQL]2-1 表格的關連及限制用法_PK_FK_UK_INDEX

表格的關連及限制用法_PK_FK_UK_INDEX
例：
建立user table
CREATE TABLE user (
      id bigint(20) NOT NULL AUTO_INCREMENT COMMENT '這是PK_ID',
      username  varchar(20) NOT NULL DEFAULT '名字',
      account_number varchar(20) NOT NULL COMMENT '帳號',
      password varchar(255) NOT NULL DEFAULT '123' COMMENT '密碼預設為123',
      PRIMARY KEY(id),
      UNIQUE KEY account_number (account_number)
)AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

新增user table 資料
INSERT INTO user (id,username,account_number)
VALUES  (1,'Mr. Wu','wu123'),
                (2,'Mr. Wang','wang1'),
              (3,'Mr. Lin','lin'),
                (4,'Mr. ERIC','eric');

建立 user 的職業occupation
CREATE TABLE occupation (
      id bigint(20)    NOT NULL AUTO_INCREMENT ,
      occupation CHAR(20) NOT NULL COMMENT '職業',
      salary int(20)   NOT NULL DEFAULT 0,
      user_id bigint(20)   NOT NULL,
      PRIMARY KEY (id),
      FOREIGN KEY fk_occupation(user_id) REFERENCES user(id)
)AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

occupation table 資料
INSERT INTO occupation (occupation,salary,user_id)
VALUES ('軍人',40000,1),
                ('老師',45000,2),
                ('商人',98000,3),
              ('敗家',-35000,4),
                ('業務',25000,2);

說明：
1、PRIMARY KEY 主鍵設定
PRIMARY KEY  是設定此表格欄位值獨一無二的值
PRIMARY KEY (欄位名稱)
例：在user table 及 occupation table 的主鍵名稱是id
PRIMARY KEY (id)

2、FOREIGN KEY 外來鍵
FOREIGN KEY 是設定此表格有參考(關連)到其它表格的值
FOREIGN KEY 建立FK的名稱且是資料庫唯一的名稱(欄位名稱)
            REFERENCES 參考表格(欄位名稱)
例：在occupation table 有個欄位user_id參考 user table id
FOREIGN KEY fk_occupation(user_id) REFERENCES user(id)

3、UNIQUE KEY 唯一鍵
UNIQUE KEY 是設定表格欄位值不重複的值
UNIQUE KEY 建立名稱(欄位名稱)
例：在user table 有個帳號的欄位account_number不可以重複值
UNIQUE KEY account_number (account_number)

4、NOT NULL
設定不可以有null值

5、AUTO_INCREMENT
是自動產生主鍵值
最後要加起始值
AUTO_INCREMENT=1

6、COMMENT '這是PK_ID'
說明或註解

7、DEFAULT
設定初值，
如果你沒有insert 此欄位，會自動補初值。

8、DEFAULT CHARSET=utf8
設定編碼為utf8

表格的關連及限制用法INDEX

INDEX|KEY} [index_name] [index_type] (index_col_name,...)

CREATE TABLE user (
      id  bigint(20)  NOT NULL AUTO_INCREMENT COMMENT '這是PK_ID',
      username   varchar(20)  NOT NULL DEFAULT '名字',
      account_number  varchar(20)  NOT NULL  COMMENT '帳號',
      password  varchar(255)  NOT NULL DEFAULT '123'  COMMENT '密碼預設為123',
      PRIMARY KEY(id),
      UNIQUE KEY account_number (account_number),
      KEY(username)
)AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;


PK 預設就有INDEX了。

參考資料

其它文章