MySql基本查詢、串連查詢、子查詢、正則表達查詢講解，mysql講解

MySql基本查詢、串連查詢、子查詢、正則表達查詢講解，mysql講解

 林炳文Evankaka原創作品。轉載請註明出處http://blog.csdn.net/evankaka

        查詢資料指從資料庫中擷取所需要的資料。查詢資料是資料庫操作中最常用，也是最重要的操作。使用者可以根據自己對資料的需求，使用不同的查詢方式。通過不同的查詢方式，可以獲得不同的資料。MySQL中是使用SELECT語句來查詢資料的。在這一章中將講解的內容包括。
1、查詢語句的基本文法
2、在單表上查詢資料
3、使用彙總函式查詢資料
4、多表上聯集查詢
5、子查詢
6、合并查詢結果
7、為表和欄位取別名
8、使用Regex查詢

什麼是查詢？

怎麼查的？

資料的準備如下：

create table STUDENT(STU_ID int primary KEY,STU_NAME char(10) not null,STU_AGE smallint unsigned  not null,STU_SEX char(2) not null);insert  into STUDENT values(2001,'小王',13,'男');insert  into STUDENT values(2002,'明明',12,'男');insert  into STUDENT values(2003,'紅紅',14,'女');insert  into STUDENT values(2004,'小花',13,'女');insert  into STUDENT values(2005,'天兒',15,'男');insert  into STUDENT values(2006,'阿獵',13,'女');insert  into STUDENT values(2007,'阿貓',16,'男');insert  into STUDENT values(2008,'阿狗',17,'男');insert  into STUDENT values(2009,'黑子',14,'男');insert  into STUDENT values(2010,'小玉',13,'女');insert  into STUDENT values(2011,'頭頭',13,'女');insert  into STUDENT values(2012,'冰冰',14,'女');insert  into STUDENT values(2013,'美麗',13,'女');insert  into STUDENT values(2014,'神樂',12,'男');insert  into STUDENT values(2015,'天五',13,'男');insert  into STUDENT values(2016,'小三',11,'男');insert  into STUDENT values(2017,'阿張',13,'男');insert  into STUDENT values(2018,'阿傑',13,'男');insert  into STUDENT values(2019,'阿寶',13,'女');insert  into STUDENT values(2020,'大王',14,'男');

然後這是學產生績表，其中定義了外鍵約束

create table GRADE(STU_ID INT NOT NULL,STU_SCORE INT,foreign key(STU_ID) references STUDENT(STU_ID));insert  into GRADE values(2001,90);insert  into GRADE values(2002,89);insert  into GRADE values(2003,67);insert  into GRADE values(2004,78);insert  into GRADE values(2005,89);insert  into GRADE values(2006,78);insert  into GRADE values(2007,99);insert  into GRADE values(2008,87);insert  into GRADE values(2009,70);insert  into GRADE values(2010,71);insert  into GRADE values(2011,56);insert  into GRADE values(2012,85);insert  into GRADE values(2013,65);insert  into GRADE values(2014,66);insert  into GRADE values(2015,77);insert  into GRADE values(2016,79);insert  into GRADE values(2017,82);insert  into GRADE values(2018,88);insert  into GRADE values(2019,NULL);insert  into GRADE values(2020,NULL);

一、查詢語句的基本文法

查詢資料是指從資料庫中的資料表或視圖中擷取所需要的資料，在MySQL中，可以使用SELECT語句來查詢資料。根據查詢條件的不同，資料庫系統會找到不同的資料。

SELECT語句的基本文法格式如下：

SELECT 屬性列表       FROM 表名或視圖列表       [WHERE 條件運算式1]       [GROUP BY 屬性名稱1 [HAVING 條件運算式2]]       [ORDER BY 屬性名稱2 [ASC|DESC]]

• 屬性列表：表示需要查詢的欄位名。
• 表名或視圖列表：表示即將進行資料查詢的資料表或者視圖，表或視圖可以有多個。
• 條件運算式1：設定查詢的條件。
• 屬性名稱1：表示按該欄位中的資料進行分組。
• 條件運算式2：表示滿足該運算式的資料才能輸出。
• 屬性2：表示按該欄位中的資料進行排序，排序方式由ASC或DESC參數指定。
• ASC：表示按升序的順序進行排序。即表示值按照從小到大的順序排列。這是預設參數。
• DESC：表示按降序的順序進行排序。即表示值按照從大到小的順序排列。

如果有WHERE子句，就按照“條件運算式1”指定的條件進行查詢；如果沒有WHERE子句，就查詢所有記錄。

如果有GROUP BY子句，就按照“屬性名稱1”指定的欄位進行分組；如果GROUP BY子句後面帶著HAVING關鍵字，那麼只有滿足“條件運算式2”中指定的條件的記錄才能夠輸出。GROUP BY子句通常和COUNT()、SUM()等彙總函式一起使用。

如果有ORDER BY子句，就按照“屬性名稱2”指定的欄位進行排序。排序方式由ASC或DESC參數指定。預設的排序方式為ASC。

二、在單表上查詢資料2.1、查詢所有欄位

select * from STUDENT;

 

2.2、按條件查詢

 （1） 比較子      > ,  < ,=  , != （< >）,>=   ,   <= 

select * from STUDENT where STU_AGE>13;

 in(v1,v2..vn)  ，符合v1,v2,,,vn才能被查出
IN關鍵字可以判斷某個欄位的值是否在指定的集合中。如果欄位的值在集合中，則滿足查詢條件，該紀錄將被查詢出來。如果不在集合中，則不滿足查詢條件。其文法規則如下：[ NOT ] IN ( 元素1, 元素2, …, 元素n )

select * from STUDENT where STU_AGE in(11,12);

  between v1 and v2    在v1至v2之間（包含v1,v2）
BETWEEN AND關鍵字可以判讀某個欄位的值是否在指定的範圍內。如果欄位的值在指定範圍內，則滿足查詢條件，該紀錄將被查詢出來。如果不在指定範圍內，則不滿足查詢條件。其文法規則如下：
[ NOT ] BETWEEN 取值1 AND 取值2

select * from STUDENT where STU_AGE between 13 and 15;

（2）邏輯運算子 not ( ! )  邏輯非

select * from STUDENT where STU_AGE NOT IN(13,14,16);

or ( || )    邏輯或
OR關鍵字也可以用來聯合多個條件進行查詢，但是與AND關鍵字不同。使用OR關鍵字時，只要滿足這幾個查詢條件的其中一個，這樣的記錄將會被查詢出來。如果不滿足這些查詢條件中的任何一個，這樣的記錄將被排除掉。OR關鍵字的文法規則如下：
條件運算式1 OR 條件運算式2 [ …OR 條件運算式n ]
其中，OR可以用來串連兩個條件運算式。而且，可以同時使用多個OR關鍵字，這樣可以串連更多的條件運算式。

select * from STUDENT where STU_ID<2005 OR STU_ID>2015;

 and ( && )  邏輯與
AND關鍵字可以用來聯合多個條件進行查詢。使用AND關鍵字時，只有同時滿足所有查詢條件的記錄會被查詢出來。如果不滿足這些查詢條件的其中一個，這樣的記錄將被排除掉。AND關鍵字的文法規則如下：
條件運算式1 AND 條件運算式2 [ … AND 條件運算式n ]
其中，AND可以串連兩個條件運算式。而且，可以同時使用多個AND關鍵字，這樣可以串連更多的條件運算式。

（3）模糊查詢 like 像
LIKE關鍵字可以匹配字串是否相等。如果欄位的值與指定的字串相匹配，則滿足查詢條件，該紀錄將被查詢出來。如果與指定的字串不匹配，則不滿足查詢條件。其文法規則如下：[ NOT ] LIKE '字串'
“NOT”選擇性參數，加上 NOT表示與指定的字串不匹配時滿足條件；“字串”表示指定用來匹配的字串，該字串必須加單引號或雙引號。

萬用字元:  %  任一字元

select * from STUDENT where STU_NAME LIKE '%王';

表示匹配任何以王結尾的

select * from STUDENT where STU_NAME LIKE '阿%';

表示匹配任何以阿開頭的

 _  單個字元比如說插入

insert  into STUDENT values(2021,'天下無鏡',14,'男');

然後

select * from STUDENT where STU_NAME LIKE '_下_';

查詢的結果為空白

但是如果下後面加兩個_符號

select * from STUDENT where STU_NAME LIKE '_下__';

查詢結果不為空白

“字串”參數的值可以是一個完整的字串，也可以是包含百分比符號(%)或者底線(_)的通配字元。二者有很大區別
“%”可以代表任意長度的字串，長度可以為0;
“_”只能表示單個字元。
如果要匹配姓張且名字只有兩個字的人的記錄，“張”字後面必須要有兩個“_”符號。因為一個漢字是兩個字元，而一個“_”符號只能代表一個字元。
（4）空值查詢

IS NULL關鍵字可以用來判斷欄位的值是否為空白值（NULL）。如果欄位的值是空值，則滿足查詢條件，該記錄將被查詢出來。如果欄位的值不是空值，則不滿足查詢條件。其文法規則如下：
IS [ NOT ] NULL
其中，“NOT”是選擇性參數，加上NOT表示欄位不是空值時滿足條件。
IS NULL是一個整體，不能將IS換成”=”.

三、使用彙總函式查詢資料3.1、group by 分組

如下：

select * from STUDENT group by STU_SEX;

不加條件，那麼就只取每個分組的第一條。

如果想看分組的內容，可以加groub_concat

select STU_SEX,group_concat(STU_NAME) from STUDENT group by STU_SEX;

3.2、一般情況下group需與統計函數（彙總函式）一起使用才有意義

 先準備一些資料：

create table EMPLOYEES(EMP_NAME CHAR(10) NOT NULL,EMP_SALARY INT unsigned NOT NULL,EMP_DEP CHAR(10) NOT NULL);insert into EMPLOYEES values('小王',5000,'銷售部');insert into EMPLOYEES values('阿小王',6000,'銷售部');insert into EMPLOYEES values('工是不',7000,'銷售部');insert into EMPLOYEES values('人人樂',3000,'資源部');insert into EMPLOYEES values('滿頭大',4000,'資源部');insert into EMPLOYEES values('天生一家',5500,'資源部');insert into EMPLOYEES values('小花',14500,'資源部');insert into EMPLOYEES values('大玉',15000,'研發部');insert into EMPLOYEES values('條條',12000,'研發部');insert into EMPLOYEES values('笨笨',13000,'研發部');insert into EMPLOYEES values('我是天才',15000,'研發部');insert into EMPLOYEES values('無語了',6000,'審計部');insert into EMPLOYEES values('什麼人',5000,'審計部');insert into EMPLOYEES values('不知道',4000,'審計部');

mysql中的五種統計函數：
（1）max：求最大值
求每個部門的最高工資：

select EMP_NAME,EMP_DEP,max(EMP_SALARY) from EMPLOYEES group by EMP_DEP;

（2）min：求最小值

求每個部門的最仰工資：

select EMP_NAME,EMP_DEP,min(EMP_SALARY) from EMPLOYEES group by EMP_DEP;

 （3）sum：求總數和

求每個部門的工資總和：

select EMP_DEP,sum(EMP_SALARY) from EMPLOYEES group by EMP_DEP

（4）avg：求平均值

求每個部門的工資平均值

select EMP_DEP,avg(EMP_SALARY) from EMPLOYEES group by EMP_DEP;

（5）count：求總行數
求每個部門工資大於一定金額的人數

select EMP_DEP,count(*) from EMPLOYEES where EMP_SALARY>=500 group by EMP_DEP;

3.3、帶條件的groub by 欄位 having，利用HAVING語句過濾分組資料

having 子句的作用是篩選滿足條件的組，即在分組之後過濾資料，條件中經常包含聚組函數，使用having 條件顯示特定的組，也可以使用多個分組標準進行分組。
having 子句被限制子已經在SELECT語句中定義的列和彙總運算式上。通常，你需要通過在HAVING子句中重複彙總函式運算式來引用彙總值，就如你在SELECT語句中做的那樣。

select EMP_DEP,avg(EMP_SALARY),group_concat(EMP_NAME)from EMPLOYEES  group by EMP_DEP HAVING  avg(EMP_SALARY) >=6000;

尋找平均工資大於6000的部門，並把部門裡的人全部列出來

四、多表上聯集查詢

多表上聯集查詢分為內串連查詢和外串連查詢

(1)隱式內串連查詢

select STUDENT.STU_ID,STUDENT.STU_NAME,STUDENT.STU_AGE,STUDENT.STU_SEX,GRADE.STU_SCORE from STUDENT,GRADE WHERE STUDENT.STU_ID=GRADE.STU_ID AND GRADE.STU_SCORE >=90;

尋找大於90分的學生資訊：

（2）顯式內串連查詢

select STUDENT.STU_ID,STUDENT.STU_NAME,STUDENT.STU_AGE,STUDENT.STU_SEX,GRADE.STU_SCORE from STUDENT inner join GRADE on STUDENT.STU_ID=GRADE.STU_ID AND GRADE.STU_SCORE >=90;

用法：select .... from 表1 inner join 表2 on 條件運算式

（3）外串連查詢

left join.左串連查詢。

用法 ：select .... from 表1 left join 表2 on 條件運算式

意思是表1查出來的資料不能為null，但是其對應表2的資料可以為null

select STUDENT.STU_ID,STUDENT.STU_NAME,STUDENT.STU_AGE,STUDENT.STU_SEX,GRADE.STU_SCORE from STUDENT left join GRADE on STUDENT.STU_ID=GRADE.STU_ID;

right join就是相反的了，用法相同

用left join的時候，left join操作符左側表裡的資訊都會被查詢出來，右側表裡沒有的記錄會填空(NULL).right join亦然；inner join的時候則只有條件合適的才會顯示出來

full join()
完整外部聯結返回左表和右表中的所有行。當某行在另一個表中沒有匹配行時，則另一個表的挑選清單列包含空值。如果表之間有匹配行，則整個結果集行包含基表的資料
值。
      僅當至少有一個同屬於兩表的行符合聯結條件時，內聯結才返回行。內聯結消除與另一個表中的任何行不匹配的行。而外聯結會返回 FROM 子句中提到的至少一個表或
視圖的所有行，只要這些行符合任何 WHERE 或 HAVING 搜尋條件。將檢索通過左向外聯結引用的左表的所有行，以及通過右向外聯結引用的右表的所有行。完整外
部聯結中兩個表的所有行都將返回。

五、子查詢

以一個查詢select的結果作為另一個查詢的條件

文法：select * from 表1 wher 條件1（select ..from 表2 where 條件2）

1、與In結合

select * from STUDENT where STU_ID IN(select STU_ID from GRADE where STU_SCORE>85);

尋找大於85分的學生資訊

2、與EXISTS結合

EXISTS和NOT EXISTS操作符只測試某個子查詢是否返回了資料行。如果是，EXISTS將是true，NOT EXISTS將是false。

select * from STUDENT where  EXISTS (select STU_ID from GRADE where STU_SCORE>=100);

如果有學產生績大於100，才查詢所有的學生資訊

3、ALL、ANY和SOME子查詢

any和all的操作符常見用法是結合一個相對比較操作符對一個資料列子查詢的結果進行測試。它們測試比較值是否與子查詢所返回的全部或一部分值匹配。比方說，如果比較值小於或等於子查詢所返回的每一個值，<=all將是true，只要比較值小於或等於子查詢所返回的任何一個值，<=any將是true。some是any的一個同義字。

select STU_ID from GRADE where STU_SCORE <67;

只要學號大於上面的任意一個就顯示出來：

select * from STUDENT where STU_ID >= any (select STU_ID from GRADE where STU_SCORE <67);

六、合并查詢結果

合并查詢結果是將多個SELECT語句的查詢結果合并到一起。因為某種情況下，需要將幾個SELECT語句查詢出來的結果合并起來顯示。
使用UNION關鍵字時，資料庫系統會將所有的查詢結果合并到一起，然後去除掉相同的記錄。而UNION ALL關鍵字則只是簡單的合并到一起。其文法規則如下：

SELECT語句1UNION | UNION ALLSELECT語句2UNION | UNION ALL ….SELECT語句n ;

七、排序與取數7.1、order by

（1） order by price //預設升序排列
（2）order by price desc //降序排列
（3）order by price asc //升序排列，與預設一樣
（4）order by rand() //隨機排列，效率不高

select * from GRADE where STU_SCORE >80 order by STU_SCORE;

預設是按升序的，

也可以這麼寫

select * from GRADE where STU_SCORE >80 order by STU_SCORE ASC;

結果如下：

如果想換成降序的：

select * from GRADE where STU_SCORE >80 order by STU_SCORE desc;

7.2、limit

limit [offset,] N
offset 位移量，可選，不寫則相當於limit 0,N
N 取出條目

取分數最高的前5條

select * from GRADE order by STU_SCORE desc limit 5;

取分數最低的前5條

select * from GRADE order by STU_SCORE asc limit 5;

取分數排名在10-15之間的5條

select * from GRADE order by STU_SCORE desc limit 10,5

八、為表和欄位取別名

使用AS來命名列

select STU_ID as '學號',STU_SCORE as '分數' from GRADE;

當表的名稱特別長時，在查詢中直接使用表名很不方便。這時可以為表取一個別名。用這個別名來代替表的名稱。
MySQL中為表取別名的基本形式如下：
表名 表的別名

select S.STU_ID,S.STU_NAME,S.STU_AGE,S.STU_SEX,G.STU_SCORE from STUDENT S,GRADE G WHERE S.STU_ID=G.STU_ID AND G.STU_SCORE >=90;

九、使用Regex查詢

        Regex是用某種模式去匹配一類字串的一個方式。例如，使用Regex可以查詢出包含A、B、C其中任一字母的字串。Regex的查詢能力比通配字元的查詢能力更強大，而且更加的靈活。Regex可以應用於非常複雜查詢。
MySQL中，使用REGEXP關鍵字來匹配查詢Regex。其基本形式如下：
屬性名稱 REGEXP '匹配方式'

在使用前先插入一些資料：

insert  into STUDENT values(2022,'12wef',13,'男');insert  into STUDENT values(2023,'faf_23',13,'男');insert  into STUDENT values(2024,'fafa',13,'女');insert  into STUDENT values(2025,'ooop',14,'男');insert  into STUDENT values(2026,'23oop',14,'男');insert  into STUDENT values(2027,'woop89',14,'男');insert  into STUDENT values(2028,'abcdd',11,'男');

（1）使用字元“^”可以匹配以特定字元或字串開頭的記錄。

查詢所有以阿頭的

select * from STUDENT where STU_NAME REGEXP  '^阿';

以數字開頭

select * from STUDENT where STU_NAME REGEXP '^[0-9]';

（2）使用字元“$”可以匹配以特定字元或字串結尾的記錄

以數字結尾

select * from STUDENT where STU_NAME REGEXP '[0-9]$';

（3）用Regex來查詢時，可以用“.”來替代字串中的任意一個字元。

select * from STUDENT where STU_NAME REGEXP '^w....[0-9]$';

以w開頭,以數字結束，中間有4個

（4）使用方括弧（[]）可以將需要查詢字元組成一個字元集。只要記錄中包含方括弧中的任一字元，該記錄將會被查詢出來。
例如，通過“[abc]”可以查詢包含a、b、c這三個字母中任何一個的記錄。

使用方括弧可以指定集合的區間。
“[a-z]”表示從a-z的所有字母;
“[0-9]”表示從0-9的所有數字;
“[a-z0-9]”表示包含所有的小寫字母和數字。
“[a-zA-Z]”表示匹配所有字母。

select * from STUDENT where STU_NAME REGEXP '[0-9a-z]';

查詢所有包含有數字和小寫字母的

使用“[^字元集合]”可以匹配指定字元以外的字元

（5）｛｝表示出現的次數

Regex中，“字串{M}”表示字串連續出現M次；“字串{M,N}”表示字串聯連續出現至少M次，最多N次。例如，“ab{2}”表示字串“ab”連續出現兩次。“ab{2,4}”表示字串“ab”連續出現至少兩次，最多四次。

o出現2次

select * from STUDENT where STU_NAME REGEXP 'o{2}';

（6）+表示到少出現一次

fa至少出現一次

select * from STUDENT where STU_NAME REGEXP '(fa)+';

      

 注意： 

       Regex可以匹配字串。當表中的記錄包含這個字串時，就可以將該記錄查詢出來。如果指定多個字串時，需要用符號“|”隔開。只要匹配這些字串中的任意一個即可。每個字串與”|”之間不能有空格。因為，查詢過程中，資料庫系統會將空格也當作一個字元。這樣就查詢不出想要的結果。
Regex中，“*”和“+”都可以匹配多個該符號之前的字元。但是，“+”至少表示一個字元，而“*”可以表示零個字元。

著作權聲明：本文為博主林炳文Evankaka原創文章，未經博主允許不得轉載。