一、MySQL進價查詢

1.1 單欄位排序

■ 使用ORDERBY語句來實現排序

■ 排序可針對一個或多個欄位

■ ASC:升序，預設排序方式【升序是從小到大】

■ DESC:降序【降序是從大到小】

■ ORDER BY的語法結構

■ ORDER BY後面跟欄位名

SELECT column1, column2,....FROM table_name ORDER BY column1,column2,...ASC|DESC;

mysql> select * from info order by score;
+----+----------+-------+------+
| id | name     | score | addr |
+----+----------+-------+------+
|  3 | oowooo   | 60.00 |      |
|  4 | oowo     | 60.00 |      |
|  2 | lisi     | 70.00 | NULL |
|  5 | wangwu   | 77.00 | NULL |
|  1 | zhangsan | 88.00 | NULL |
+----+----------+-------+------+
5 rows in set (0.00 sec)

mysql> select * from info order by score desc;
+----+----------+-------+------+
| id | name     | score | addr |
+----+----------+-------+------+
|  1 | zhangsan | 88.00 | NULL |
|  5 | wangwu   | 77.00 | NULL |
|  2 | lisi     | 70.00 | NULL |
|  3 | oowooo   | 60.00 |      |
|  4 | oowo     | 60.00 |      |
+----+----------+-------+------+
5 rows in set (0.00 sec)

1.2 多欄位排序

mysql> select id,hobby from info where 2=2 order by hobby desc,id desc;
+----+-------+
| id | hobby |
+----+-------+
|  5 |     3 |
|  4 |     2 |
|  3 |     2 |
|  2 |     2 |
|  1 |     1 |
+----+-------+
5 rows in set (0.00 sec)

###只有第一個欄位出現和第二欄位相同的情況下，第二欄位才有意義###

1.3 對結果進行分組-1

■ 使用GROUP BY語句來實現分組

■ 通常結合聚合函數一起使用

■ 可以按一個或多個欄位對結果進行分組

■ GROUP BY的語法結構

SELECT column_name, aggregate_function(column_name)FROM table_name WHERE column_name operator value GROUP BY column_name;

1.4 對結果進行分組-2

■ GROUP BY分組

###對info表的name進行統計在hobby欄位顯示統計個數###
mysql> select count(name),hobby from info group by hobby;
+-------------+-------+
| count(name) | hobby |
+-------------+-------+
|           1 |     1 |
|           3 |     2 |
|           1 |     3 |
+-------------+-------+
3 rows in set (0.00 sec)

■ GROUP BY結合ORDER BY

mysql> select count(name),hobby from info group by hobby order by count(name) desc;
+-------------+-------+
| count(name) | hobby |
+-------------+-------+
|           3 |     2 |
|           1 |     1 |
|           1 |     3 |
+-------------+-------+
3 rows in set (0.00 sec)

1.5 對結果進行分組

■ 只返回SELECT查詢結果的第一行或前幾行

■ 使用LIMIT語句限制條目

■ LIMIT語法結構

SELECT column1,column2,...FROM table_name LIMIT[offset,] number;
number：返回記錄行的最大數目
[offset,]：位置偏移量，從0開始

###檢視前三行###
mysql> select * from info limit 3;
+----+----------+-------+------+-------+
| id | name     | score | addr | hobby |
+----+----------+-------+------+-------+
|  1 | zhangsan | 88.00 | NULL |     1 |
|  2 | lisi     | 70.00 | NULL |     2 |
|  3 | oowooo   | 60.00 |      |     2 |
+----+----------+-------+------+-------+
3 rows in set (0.00 sec)

###檢視3-5行###
mysql> select * from info limit 2,3;
+----+--------+-------+------+-------+
| id | name   | score | addr | hobby |
+----+--------+-------+------+-------+
|  3 | oowooo | 60.00 |      |     2 |
|  4 | oowo   | 60.00 |      |     2 |
|  5 | wangwu | 77.00 | NULL |     3 |
+----+--------+-------+------+-------+
3 rows in set (0.00 sec)

1.6 設定別名

■ 使用AS語句設定別名，關鍵字AS可省略
■ 設定別名時，保證不能與庫中其他表或欄位名稱衝突
■ 別名的語法結構

SELECT column_name AS alias_name FROM table_name;
SELECT column_name(s)FROM table_name ASalias_name;

■ AS的用法

###給name和score設定別名###
mysql> select name as 姓名,score as 成績 from info;
+----------+--------+
| 姓名     | 成績   |
+----------+--------+
| zhangsan |  88.00 |
| lisi     |  70.00 |
| oowooo   |  60.00 |
| oowo     |  60.00 |
| wangwu   |  77.00 |
+----------+--------+
5 rows in set (0.00 sec)

###不加as語法也可以設定別名###
mysql> select name  姓名,score  成績 from info;
+----------+--------+
| 姓名     | 成績   |
+----------+--------+
| zhangsan |  88.00 |
| lisi     |  70.00 |
| oowooo   |  60.00 |
| oowo     |  60.00 |
| wangwu   |  77.00 |
+----------+--------+
5 rows in set (0.00 sec)

###給info表設定別名i，然後在name和score前面也要加i.使用，不加也可以使用###
mysql> select i.name as 姓名,i.score as 成績 from info as i;
+----------+--------+
| 姓名     | 成績   |
+----------+--------+
| zhangsan |  88.00 |
| lisi     |  70.00 |
| oowooo   |  60.00 |
| oowo     |  60.00 |
| wangwu   |  77.00 |
+----------+--------+
5 rows in set (0.00 sec)

###建立tmm新表，將info表的 score欄位>=80的資料放在新表tmm上###
mysql> create table tmm as select * from info where score >= 80;
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

注意：雖然把資料型別和表結構導過去了，但是表的約束沒有，像主鍵什麼的都沒有匯入過去！！！

1.7 萬用字元

■ 用於替換字串的部分字元

■ 通常配合LIKE一起使用，並協同WHERE完成查詢

■ 常用的萬用字元

● %表示零個、一個或多個

● _表示單個字元

###查詢z開頭的，%表示零個、一個或多個###
mysql> select * from info where name like 'z%';
+----+----------+-------+------+-------+
| id | name     | score | addr | hobby |
+----+----------+-------+------+-------+
|  1 | zhangsan | 88.00 | NULL |     1 |
+----+----------+-------+------+-------+
1 row in set (0.00 sec)

###_下劃線代表單個字元###
mysql> select * from info where name like '_i_i';
+----+------+-------+------+-------+
| id | name | score | addr | hobby |
+----+------+-------+------+-------+
|  2 | lisi | 70.00 | NULL |     2 |
+----+------+-------+------+-------+
1 row in set (0.00 sec)

1.8 子查詢

■ 也稱作內查詢或者巢狀查詢
■ 先於主查詢被執行，其結果將作為外層主查詢的條件
■ 在增刪改查中都可以使用子查詢
■ 支援多層巢狀
■ IN語句是用來判斷某個值是否在給定的結果集中

###先建一個num表，裡面只有id，在id欄位在新增一些資料###
mysql> select * from num;
+------+
| id   |
+------+
|    1 |
|    3 |
|    5 |
|    7 |
+------+
4 rows in set (0.00 sec)
###然後根據剛剛的info表和num表進行多表相連，按照num表的1、3、5、7顯示出info表的1、3、5、7行資料###
###後面輸出的結果賦予了前面的值###
mysql> select * from info where id in(select id from num);
+----+----------+-------+------+-------+
| id | name     | score | addr | hobby |
+----+----------+-------+------+-------+
|  1 | zhangsan | 88.00 | NULL |     1 |
|  3 | oowooo   | 60.00 |      |     2 |
|  5 | wangwu   | 77.00 | NULL |     3 |
+----+----------+-------+------+-------+
3 rows in set (0.00 sec)

###多層巢狀，從內部括號到外面匹配###
mysql> select * from info where id in(select id from num where name in(select name from num));
+----+----------+-------+------+-------+
| id | name     | score | addr | hobby |
+----+----------+-------+------+-------+
|  1 | zhangsan | 88.00 | NULL |     1 |
|  3 | oowooo   | 60.00 |      |     2 |
|  5 | wangwu   | 77.00 | NULL |     3 |
+----+----------+-------+------+-------+
3 rows in set (0.00 sec)

1.9 檢視

■ 資料庫中的虛擬表，這張虛擬表中不包含任何資料，只是做了資料對映；

###建立檢視v_score表###
mysql> create view v_score as select * from info where score >=80;

###檢視一下剛剛建立的檢視###
mysql> select * from v_score;
+----+----------+-------+
| id | name     | score |
+----+----------+-------+
|  1 | zhangsan | 88.00 |
+----+----------+-------+
1 row in set (0.00 sec)

mysql> show table status;  ###檢視檢視表的資訊

在這裡插入圖片描述

###這種被稱為臨時結果集，放在記憶體當中，重新啟動就會沒有###
mysql> select id,name from info;
+----+----------+
| id | name     |
+----+----------+
|  1 | zhangsan |
|  2 | lisi     |
|  3 | oowooo   |
|  4 | oowo     |
|  5 | wangwu   |
+----+----------+
5 rows in set (0.00 sec)

###要定義別名，比如下圖我定義了別名a，就可以使用了###
mysql> select a.id from (select id,name from info) a;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
|  5 |
+----+
5 rows in set (0.00 sec)

###count()代表統計，exists代表真或假###
mysql> select count(*) from info where exists (select * from info where name='zhangsan');
+----------+
| count(*) |
+----------+
|        5 |
+----------+
1 row in set (0.00 sec)

1.10 NULL值

■ 表示缺失的值
■ 與數位0或者空白(spaces）是不同的
■ 使用IS NULL或IS NOT NULL進行判斷
■ NULL值和空值的區別
● 空值長度為0，不佔空間;NULL值的長度為NULL，佔用空間
● IS NULL無法判斷空值
● 空值使用「="或者「<>"來處理
● COUNT()計算時，NULL會忽略，空值會加入計算

###查詢info表的addr欄位為null值的記錄###
mysql> select * from info where addr is null;
+----+----------+-------+------+-------+
| id | name     | score | addr | hobby |
+----+----------+-------+------+-------+
|  1 | zhangsan | 88.00 | NULL |     1 |
|  2 | lisi     | 70.00 | NULL |     2 |
|  5 | wangwu   | 77.00 | NULL |     3 |
+----+----------+-------+------+-------+
3 rows in set (0.00 sec)

1.11 正規表示式

■ 根據指定的匹配模式匹配記錄中符合要求的特殊字元
■ 使用REGEXP關鍵字指定匹配模式
■ 常用匹配模式

匹配模式	描述	範例
^	匹配文字的開始字元	‘^bd’ 匹配以 bd 開頭的字串
$	匹配文字的結束字元	‘qn$’ 匹配以 qn 結尾的字串
.	匹配任何單個字元	‘s.t’ 匹配任何s 和t 之間有一個字元的字串
*	匹配零個或多個在它前面的字元	‘fo*t’ 匹配 t 前面有任意個 o
+	匹配前面的字元 1 次或多次	‘hom+’ 匹配以 ho 開頭，後面至少一個m 的字串
字串	匹配包含指定的字串	‘clo’ 匹配含有 clo 的字串
p1	p2	匹配 p1 或 p2
[…]	匹配字元集合中的任意一個字元	‘[abc]’ 匹配 a 或者 b 或者 c
[^…]	匹配不在括號中的任何字元	‘[^ab]’ 匹配不包含 a 或者 b 的字串
{n}	匹配前面的字串 n 次	‘g{2}’ 匹配含有 2 個 g 的字串
{n,m}	匹配前面的字串至少 n 次，至多m 次	‘f{1,3}’ 匹配 f 最少 1 次，最多 3 次

查詢以z開頭的

mysql> select * from info where name regexp '^z';
+----+----------+-------+------+-------+
| id | name     | score | addr | hobby |
+----+----------+-------+------+-------+
|  1 | zhangsan | 88.00 | NULL |     1 |
+----+----------+-------+------+-------+
1 row in set (0.00 sec)

查詢以wu為結尾的

mysql> select * from info where name regexp 'wu$';
+----+--------+-------+------+-------+
| id | name   | score | addr | hobby |
+----+--------+-------+------+-------+
|  5 | wangwu | 77.00 | NULL |     3 |
+----+--------+-------+------+-------+
1 row in set (0.00 sec)

.代表任意字元，查詢zhang.an的記錄

mysql> select * from info where name regexp 'zhang.an';
+----+----------+-------+------+-------+
| id | name     | score | addr | hobby |
+----+----------+-------+------+-------+
|  1 | zhangsan | 88.00 | NULL |     1 |
+----+----------+-------+------+-------+
1 row in set (0.00 sec)

查詢oo任意個前面的字元，零次或多次

mysql> select * from info where name regexp 'oo*';
+----+--------+-------+------+-------+
| id | name   | score | addr | hobby |
+----+--------+-------+------+-------+
|  3 | oowooo | 60.00 |      |     2 |
|  4 | oowo   | 60.00 |      |     2 |
+----+--------+-------+------+-------+
2 rows in set (0.00 sec)

查詢ow前面字元至少一次

mysql> select * from info where name regexp 'ow+';
+----+--------+-------+------+-------+
| id | name   | score | addr | hobby |
+----+--------+-------+------+-------+
|  3 | oowooo | 60.00 |      |     2 |
|  4 | oowo   | 60.00 |      |     2 |
+----+--------+-------+------+-------+
2 rows in set (0.00 sec)

查詢z開頭的

mysql> select * from info where name regexp '^[z]';
+----+----------+-------+------+-------+
| id | name     | score | addr | hobby |
+----+----------+-------+------+-------+
|  1 | zhangsan | 88.00 | NULL |     1 |
+----+----------+-------+------+-------+
1 row in set (0.00 sec)

查詢不是z開頭的

mysql> select * from info where name regexp '^[^z]';
+----+--------+-------+------+-------+
| id | name   | score | addr | hobby |
+----+--------+-------+------+-------+
|  2 | lisi   | 70.00 | NULL |     2 |
|  3 | oowooo | 60.00 |      |     2 |
|  4 | oowo   | 60.00 |      |     2 |
|  5 | wangwu | 77.00 | NULL |     3 |
+----+--------+-------+------+-------+
4 rows in set (0.01 sec)

匹配兩個o

mysql> select * from info where name regexp 'oo{2}';
+----+--------+-------+------+-------+
| id | name   | score | addr | hobby |
+----+--------+-------+------+-------+
|  3 | oowooo | 60.00 |      |     2 |
+----+--------+-------+------+-------+
1 row in set (0.00 sec)

1.12 運運算元

■ 算數運運算元
MySQL 的運運算元用於對記錄中的欄位值進行運算。MySQL 的運運算元共有四種，分別是：算術運運算元、比較運運算元、邏輯運運算元和位運運算元。下面分別對這四種運運算元進行說明。

1.12.1 算數運運算元

MYSQL支援使用的運運算元號

運運算元	描述
+	加法
-	減法
*	乘法
/	除法
%	取餘數

mysql> select 2+3,3-2,3*4,8/2,9%2;
+-----+-----+-----+--------+------+
| 2+3 | 3-2 | 3*4 | 8/2    | 9%2  |
+-----+-----+-----+--------+------+
|   5 |   1 |  12 | 4.0000 |    1 |
+-----+-----+-----+--------+------+
1 row in set (0.00 sec)

1.12.2 比較運算

常用比較運運算元

1.12.2.1 等於運運算元

等號（=）是用來判斷數位、字串和表示式是否相等的，如果相等則返回 1，如果不相等則返回 0。如果比較的兩者有一個值是 NULL，則比較的結果就是 NULL。其中字元的比較是根據 ASCII 碼來判斷的，如果 ASCII 碼相等，則表示兩個字元相同；如果 ASCII 碼不相等，則表示兩個字元不相同。例如，等於運運算元在數位、字串和表示式上的使用，具體操作如下所示.

mysql> select 2=4,2='2','e'='e''r'=NULL;
+-----+-------+-----------------+
| 2=4 | 2='2' | 'e'='e''r'=NULL |
+-----+-------+-----------------+
|   0 |     1 |            NULL |
+-----+-------+-----------------+
1 row in set (0.00 sec)

1.12.2.2 不等於運運算元

不等於號有兩種寫法，分別是<>或者!=，用於針對數位、字串和表示式不相等的比較。如果不相等則返回 1，如果相等則返回 0，這點正好跟等於的返回值相反。需要注意的是不等於運運算元不能用於判斷 NULL。

mysql> mysqlt 'shuai'<>'chou',13!=17,NULL=NULL;
+-----------------+--------+-----------+
| 'shuai'<>'chou' | 13!=17 | NULL=NULL |
+-----------------+--------+-----------+
|               1 |      1 |      NULL |
+-----------------+--------+-----------+
1 row in set (0.00 sec)

從下面兩組字串比較可知，字串比的是字元的個數和順序

mysql> select 'abc'='abc';
+-------------+
| 'abc'='abc' |
+-------------+
|           1 |
+-------------+
1 row in set (0.00 sec)

mysql> select 'abc'='bca';
+-------------+
| 'abc'='bca' |
+-------------+
|           0 |
+-------------+
1 row in set (0.00 sec)

1.12.2.3 大於、大於等於、小於、小於等於運運算元

大於（>）運運算元用來判斷左側的運算元是否大於右側的運算元，若大於返回 1，否則返回 0，同樣不能用於判斷 NULL。
小於（<）運運算元用來判斷左側的運算元是否小於右側的運算元，若小於返回 1，否則返回 0，同樣不能用於判斷 NULL。
大於等於（>=）判斷左側的運算元是否大於等於右側的運算元，若大於等於返回 1，否則返回 0，不能用於判斷 NULL。
小於等於（<=）判斷左側的運算元是否小於等於右側的運算元，若小於等於返回 1，否則返回 0，不能用於判斷 NULL。
數值比較會自動轉換ASCII表的數值

我們需要大體記住幾個常用Dec（十進位制）的就行：

0是48

大寫A是65 B是66 依次往後推算

小寫a是97 b是9

mysql> mysql> select 5>3,'a'>'b',3>=4,(5+6)>=(3+2),4.4<3,1<2,'x'<='y',5<=5.5,'u'>=NULL;
+-----+---------+------+--------------+-------+-----+----------+--------+-----------+
| 5>3 | 'a'>'b' | 3>=4 | (5+6)>=(3+2) | 4.4<3 | 1<2 | 'x'<='y' | 5<=5.5 | 'u'>=NULL |
+-----+---------+------+--------------+-------+-----+----------+--------+-----------+
|   1 |       0 |    0 |            1 |     0 |   1 |        1 |      1 |      NULL |
+-----+---------+------+--------------+-------+-----+----------+--------+-----------+
1 row in set (0.00 sec)

一個有趣的發現，比較是或的關係，一旦開頭的b比a大，後面就不在比較

mysql> select 'abc'<'baa';
+-------------+
| 'abc'<'baa' |
+-------------+
|           1 |
+-------------+
1 row in set (0.00 sec)

1.12.2.4 IS NULL、IS NOT NULL

SNULL 判斷一個值是否為 NULL，如果為 NULL 返回 1，否則返回 0。
IS NOT NULL 判斷一個值是否不為 NULL，如果不為 NULL 返回 1，否則返回 0

mysql> select 2 is NULL,'d' is not NULL,NULL IS NULL;
+-----------+-----------------+--------------+
| 2 is NULL | 'd' is not NULL | NULL IS NULL |
+-----------+-----------------+--------------+
|         0 |               1 |            1 |
+-----------+-----------------+--------------+
1 row in set (0.00 sec)

1.12.2.5 BETWEEN AND

BETWEEN AND 比較運算通常用於判斷一個值是否落在某兩個值之間。例如，判斷某數位是否在另外兩個數位之間，也可以判斷某英文字母是否在另外兩個字母之間。

mysql> select 5 between 2 and 8,7 between 5 and 10,'d' between 'a' and 'z';
+-------------------+--------------------+-------------------------+
| 5 between 2 and 8 | 7 between 5 and 10 | 'd' between 'a' and 'z' |
+-------------------+--------------------+-------------------------+
|                 1 |                  1 |                       1 |
+-------------------+--------------------+-------------------------+
1 row in set (0.00 sec)

數位能否與字元比較？
mysql> select 5 between 2 and 8,7 between 5 and 10,8 between 'a' and 'z'; 
+-------------------+--------------------+-----------------------+
| 5 between 2 and 8 | 7 between 5 and 10 | 8 between 'a' and 'z' |
+-------------------+--------------------+-----------------------+
|                 1 |                  1 |                     0 |
+-------------------+--------------------+-----------------------+
1 row in set, 2 warnings (0.00 sec)
#數位不能與字元比較的，而且between是包含頭和尾的

理論+實驗：MySQL高階SQL語句

目錄