天天看點
傳回

MySQL學習筆記（4）—— 資料查詢語言DQL一、查詢二、MySQL内置函數三、查詢結果排序與分頁四、GROUP BY與HAVING的使用五、GROUP_CONCAT函數的使用六、使用DISTINCT去重七、表連接配接（内連接配接、外連接配接、自連接配接）八、子查詢EXISTS和IN

文章目錄

  • 一、查詢
    • 1. 簡單查詢
    • 2. 條件查詢
      • （1）單條件查詢
      • （2）多條件查詢
      • （3）IN和LIKE的使用
  • 二、MySQL内置函數
    • 1. 函數now()
    • 2. 函數date_format()
    • 3. 聚合函數
    • 4. 函數ifnull()
    • 5. case when
  • 三、查詢結果排序與分頁
    • 1. 排序的應用場景
    • 2. order by的使用
    • 3. limit的使用
  • 四、GROUP BY與HAVING的使用
    • 1. 應用場景
    • 2. group by的使用
    • 3. having的使用
  • 五、GROUP_CONCAT函數的使用
    • 1. 應用場景
    • 2. group_concat的使用
  • 六、使用DISTINCT去重
  • 七、表連接配接（内連接配接、外連接配接、自連接配接）
    • 1. 什麼是表連接配接
    • 2. 表連接配接的幾種方式
    • 3. 各種表連接配接的差別
      • （1）内連接配接
      • （2）左連接配接
      • （3）自連接配接
    • 4. 示例
  • 八、子查詢EXISTS和IN
    • 1. 子查詢in
    • 2. 子查詢exists

一、查詢

1. 簡單查詢

  • 在SQL中，使用select語句來查詢資料。不同的關系資料庫，select文法會有細微差别
  • mysql中查詢文法 
    SELECT column_name1, column_name2 
FROM table_name 
[WHERE where_condition]
[GROUP BY {col_name | expr | position}, ... [WITH ROLLUP]] 
[HAVING where_condition]
[ORDER BY {col_name | expr | position} [ASC | DESC], ... [WITH ROLLUP]]
[LIMIT {[offset,] row_count | row_count OFFSET offset}]
  • 示例 
    # 查詢所有屬性/字段（列）
mysql> select * from contacts;
+----+------+-----------+
| id | name | phone     |
+----+------+-----------+
|  1 | 小明  | 123456789 |
|  3 | 李四  | 123151361 |
+----+------+-----------+
2 rows in set (0.00 sec)

# 查詢特定的屬性/字段（列）
mysql> select name,phone from contacts;
+------+-----------+
| name | phone     |
+------+-----------+
| 小明  | 123456789 |
| 李四  | 123151361 |
+------+-----------+
2 rows in set (0.00 sec)

# 帶where條件的查詢
mysql> select phone from contacts where name = '李四';
+-----------+
| phone     |
+-----------+
| 123151361 |
+-----------+
1 row in set (0.00 sec)

2. 條件查詢

  • 使用

    select

     中的

    where

     子句進行條件查詢
  • 在SQL中，

    insert

     、

    update

     、

    delete

     和

    select

     後面都能帶where子句，用于插入、修改、删除或查詢指定條件的記錄

（1）單條件查詢

  • SQL語句中使用where子句：

    SELECT 屬性名 FROM 表名 WHERE 屬性名 運算符 值
    MySQL學習筆記（4）—— 資料查詢語言DQL一、查詢二、MySQL内置函數三、查詢結果排序與分頁四、GROUP BY與HAVING的使用五、GROUP_CONCAT函數的使用六、使用DISTINCT去重七、表連接配接（内連接配接、外連接配接、自連接配接）八、子查詢EXISTS和IN

（2）多條件查詢

  • 在where子句中，使用

    and

     、

    or

     可以把兩個或多個過濾條件結合起來。
  • 文法：

    SELECT 屬性名 FROM 表名 WHERE condition1 AND condition2 OR condition3
  • 示例
    • 先建個測試表 
      create table employee(
	id int not null auto_increment primary key,
	name varchar(30) comment '姓名',
	sex varchar(1) comment '性别',
	salary int comment '薪資（元）'
)ENGINE=InnoDB default charset=utf8;

insert into employee(name,sex,salary) values('張三','男',5500);
insert into employee(name,sex,salary) values('李潔','女',4500);
insert into employee(name,sex,salary) values('李小梅','女',4200);
insert into employee(name,sex,salary) values('歐陽輝','男',7500);
insert into employee(name,sex,salary) values('李芳','女',8500);
insert into employee(name,sex,salary) values('張江','男',6800);
insert into employee(name,sex,salary) values('李四','男',12000);
insert into employee(name,sex,salary) values('王五','男',3600);
insert into employee(name,sex,salary) values('馬小龍','男',6000);
insert into employee(name,sex,salary) values('龍五','男',8000);
insert into employee(name,sex,salary) values('馮小芳','女',10000);
insert into employee(name,sex,salary) values('馬小龍','女',4000);
    • 開始查詢 
    mysql> select * from employee where sex != '男';
  +----+--------+------+--------+
  | id | name   | sex  | salary |
  +----+--------+------+--------+
  |  2 | 李潔   | 女   |   4500 |
  |  3 | 李小梅 | 女   |   4200 |
  |  5 | 李芳   | 女   |   8500 |
  | 11 | 馮小芳 | 女   |  10000 |
  | 12 | 馬小龍 | 女   |   4000 |
  +----+--------+------+--------+
  5 rows in set (0.00 sec)

  mysql> select * from employee where salary>10000;
  +----+------+------+--------+
  | id | name | sex  | salary |
  +----+------+------+--------+
  |  7 | 李四 | 男    |  12000 |
  +----+------+------+--------+
  1 row in set (0.00 sec)
  
  mysql> select * from employee where salary between 10000 and 12000;
  +----+--------+------+--------+
  | id | name   | sex  | salary |
  +----+--------+------+--------+
  |  7 | 李四   | 男    |  12000 |
  | 11 | 馮小芳 | 女    |  10000 |
  +----+--------+------+--------+
  2 rows in set (0.12 sec)
  
  mysql> select * from employee where sex='男' and salary between 10000 and 12000;
  +----+------+------+--------+
  | id | name | sex  | salary |
  +----+------+------+--------+
  |  7 | 李四 | 男   |  12000 |
  +----+------+------+--------+
  1 row in set (0.00 sec)

（3）IN和LIKE的使用

  • 運算符 IN 允許我們在 WHERE 子句中過濾某個字段的多個值
    • 文法：

      SELECT column_name FROM table_name WHERE column_name IN(value1, value2, …);
  • 在where子句中，有時候我們需要查詢包含xxx 字元串的所有記錄，這時就需要用到運算符like
    • 文法：

      SELECT column_name FROM table_name WHERE column_name LIKE ‘%value%’;
    • LIKE子句中的

      %

       類似于正規表達式中的

      *

       比對任意0個或多個字元
    • LIKE子句中的

      _

       比對任意單個字元
    • LIKE子句中如果沒有

      %

       和

      _

       ，就相當于運算符

      =

       的效果
    • 示例：找出姓張的人的資訊 
      mysql> select * from employee where name like '張%';
+----+------+------+--------+
| id | name | sex  | salary |
+----+------+------+--------+
|  1 | 張三  | 男   |   5500 |
|  5 | 張江  | 男   |   6800 |
+----+------+------+--------+
2 rows in set (0.00 sec)

二、MySQL内置函數

  • 我們通常說的MySQL函數指的是MySQL資料庫提供的内置函數，包括數學函數、字元串函數、日期和時間函數、聚合函數、條件判斷函數等，這些内置函數可以幫助使用者更友善地處理表中的資料，簡化使用者的操作
    MySQL學習筆記（4）—— 資料查詢語言DQL一、查詢二、MySQL内置函數三、查詢結果排序與分頁四、GROUP BY與HAVING的使用五、GROUP_CONCAT函數的使用六、使用DISTINCT去重七、表連接配接（内連接配接、外連接配接、自連接配接）八、子查詢EXISTS和IN
  • 部分簡單函數示例 
    mysql> select abs(-10);
+----------+
| abs(-10) |
+----------+
|       10 |
+----------+
1 row in set (0.03 sec)

mysql> select length('123456789');
+---------------------+
| length('123456789') |
+---------------------+
|                   9 |
+---------------------+
1 row in set (0.00 sec)

mysql> select length('123456789') from dual;	#從虛拟表dual中擷取，加不加沒差別
+---------------------+
| length('123456789') |
+---------------------+
|                   9 |
+---------------------+
1 row in set (0.00 sec)

1. 函數now()

  • now()用于傳回目前的日期和時間 
    mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2020-06-23 19:23:53 |
+---------------------+
1 row in set (0.10 sec)
  • 應用場景：在實際應用中，大多數業務表都會帶一個建立時間create_time字段，用于記錄每一條資料的産生時間。在向表中插入資料時，就可以在insert語句中使用now()函數

2. 函數date_format()

  • 函數

    date_format()

     用于以指定的格式顯示日期/時間 
    mysql> select date_format(now(),'%y/%m/%d %H:%i:%s');
+----------------------------------------+
| date_format(now(),'%y/%m/%d %H:%i:%s') |
+----------------------------------------+
| 20/06/23 19:26:41                      |
+----------------------------------------+
1 row in set (0.09 sec)
  • 應用場景：在實際應用中，一般會按照标準格式存儲日期/時間，如 2019-12-13 14:15:16 。在查詢使用資料時，往往會有不同的格式要求，這時就需要使用date_format()函數進行格式轉換

3. 聚合函數

  • 聚合函數是對一組值進行計算，并傳回單個值
  • 常用的5個聚合函數
    MySQL學習筆記（4）—— 資料查詢語言DQL一、查詢二、MySQL内置函數三、查詢結果排序與分頁四、GROUP BY與HAVING的使用五、GROUP_CONCAT函數的使用六、使用DISTINCT去重七、表連接配接（内連接配接、外連接配接、自連接配接）八、子查詢EXISTS和IN
  • 示例 
    mysql> select * from employee;
  +----+--------+------+--------+
  | id | name   | sex  | salary |
  +----+--------+------+--------+
  |  1 | 張三   | 男   |   5500 |
  |  2 | 李潔   | 女   |   4500 |
  |  3 | 李小梅 | 女   |   4200 |
  |  4 | 歐陽輝 | 男   |   7500 |
  |  5 | 李芳   | 女   |   8500 |
  |  6 | 張江   | 男   |   6800 |
  |  7 | 李四   | 男   |  12000 |
  |  8 | 王五   | 男   |   3600 |
  |  9 | 馬小龍 | 男   |   6000 |
  | 10 | 龍五   | 男   |   8000 |
  | 11 | 馮小芳 | 女   |  10000 |
  | 12 | 馬小龍 | 女   |   4000 |
  +----+--------+------+--------+
  12 rows in set (0.00 sec)
  
  mysql> select sum(salary) from employee;
  +-------------+
  | sum(salary) |
  +-------------+
  |       80600 |
  +-------------+
  1 row in set (0.10 sec)
  
  mysql> select avg(salary) from employee;
  +-------------+
  | avg(salary) |
  +-------------+
  |   6716.6667 |
  +-------------+
  1 row in set (0.00 sec)
  
  mysql> select count('女員工') from employee where sex='女';
  +-----------------+
  | count('女員工') |
  +-----------------+
  |               5 |
  +-----------------+
  1 row in set (0.00 sec)
  
  # 以下是一個錯誤示例，如果沒有min(salary)，會選出所有名字，
  # 這裡因為min(salary)隻有一個，是以通過限制name也隻顯示了一個。
  # 但這個其實不是薪資最少者的名字
  mysql> select name,min(salary) from employee;
  +------+-------------+
  | name | min(salary) |
  +------+-------------+
  | 張三  |        3600 |
  +------+-------------+
  1 row in set (0.10 sec)

  # 這是找出薪資最低員工的正确寫法，後面還會詳細說明
  mysql> select * from employee order by salary asc limit 1;
  +----+------+------+--------+
  | id | name | sex  | salary |
  +----+------+------+--------+
  |  8 | 王五 | 男   |   3600 |
  +----+------+------+--------+
  1 row in set (0.00 sec)

4. 函數ifnull()

  • 函數

    ifnull()

     用于處理NULL值

  • ifnull(v1,v2)

     ：如果 v1 的值不為 NULL，則傳回 v1，否則傳回 v2。 
    mysql> select ifnull(1/0,0);
+---------------+
| ifnull(1/0,0) |
+---------------+
|        0.0000 |
+---------------+
1 row in set (0.04 sec)

mysql> select ifnull(1,0);
+-------------+
| ifnull(1,0) |
+-------------+
|           1 |
+-------------+
1 row in set (0.00 sec)

5. case when

  • case when

     是流程控制語句，可以在SQL語句中使用case when來擷取更加準确和直接的結果
  • SQL中的case when類似于程式設計語言中的if else或者switch
  • 文法 
    #case when的文法有2種
CASE [col_name] WHEN [value1] THEN [result1]…ELSE [default] END
CASE WHEN [expr] THEN [result1]…ELSE [default] END
  • 示例 
    mysql> select
    -> id,name,
    -> case sex
    -> when '男' then 'F'
    -> when '女' then 'M'
    -> else 'UNKNOWN'
    -> end as sex_code,
    -> salary
    -> from employee;
    
+----+--------+----------+--------+
| id | name   | sex_code | salary |
+----+--------+----------+--------+
|  1 | 張三    | F        |   5500 |
|  2 | 李小梅  | M        |   4200 |
|  3 | 歐陽輝  | F        |   7500 |
|  4 | 李芳    | M        |   8500 |
|  5 | 張江    | F        |   6800 |
|  6 | 李四    | F        |  12000 |
|  7 | 王五    | F        |   3600 |
|  8 | 馬小龍   | F        |   6000 |
|  9 | 龍五    | F        |   8000 |
| 10 | 馮小芳   | M        |  10000 |
| 11 | 馬小龍   | M        |   4000 |
+----+--------+----------+--------+
11 rows in set (0.00 sec)

三、查詢結果排序與分頁

1. 排序的應用場景

  • 使用select選出資料後，往往還需要對資料進行一些處理
    • 學生按身高從高到低進行排列
    • 雙十一，某商城的商品交易量排行榜
    • 部落格中的文章按時間先後順序顯示

2. order by的使用

  • 在SQL中，使用

    order by

     對查詢結果集進行排序，可以按照一列或多列進行排序。
  • 文法 
    #order by文法
SELECT column_name1, column_name2
FROM table_name1, table_name2
ORDER BY column_name, column_name [ASC|DESC]

    • ASC

       表示按（字段）升序排列，

      DESC

       表示按降序排列
    • 預設情況下，對列按升序排列
  • 示例 
    mysql> select * from employee order by salary asc;
  +----+--------+------+--------+
  | id | name   | sex  | salary |
  +----+--------+------+--------+
  |  8 | 王五   | 男   |   3600 |
  | 12 | 馬小龍 | 女   |   4000 |
  |  3 | 李小梅 | 女   |   4200 |
  |  2 | 李潔   | 女   |   4500 |
  |  1 | 張三   | 男   |   5500 |
  |  9 | 馬小龍 | 男   |   6000 |
  |  6 | 張江   | 男   |   6800 |
  |  4 | 歐陽輝 | 男   |   7500 |
  | 10 | 龍五   | 男   |   8000 |
  |  5 | 李芳   | 女   |   8500 |
  | 11 | 馮小芳 | 女   |  10000 |
  |  7 | 李四   | 男   |  12000 |
  +----+--------+------+--------+
  12 rows in set (0.00 sec)

  mysql> select * from employee order by salary desc;
  +----+--------+------+--------+
  | id | name   | sex  | salary |
  +----+--------+------+--------+
  |  7 | 李四   | 男   |  12000 |
  | 11 | 馮小芳 | 女   |  10000 |
  |  5 | 李芳   | 女   |   8500 |
  | 10 | 龍五   | 男   |   8000 |
  |  4 | 歐陽輝 | 男   |   7500 |
  |  6 | 張江   | 男   |   6800 |
  |  9 | 馬小龍 | 男   |   6000 |
  |  1 | 張三   | 男   |   5500 |
  |  2 | 李潔   | 女   |   4500 |
  |  3 | 李小梅 | 女   |   4200 |
  | 12 | 馬小龍 | 女   |   4000 |
  |  8 | 王五   | 男   |   3600 |
  +----+--------+------+--------+
  12 rows in set (0.00 sec)

  # 兩個字段一起排
  mysql> select * from employee order by sex desc,salary desc;
  +----+--------+------+--------+
  | id | name   | sex  | salary |
  +----+--------+------+--------+
  |  7 | 李四   | 男   |  12000 |
  | 10 | 龍五   | 男   |   8000 |
  |  4 | 歐陽輝 | 男   |   7500 |
  |  6 | 張江   | 男   |   6800 |
  |  9 | 馬小龍 | 男   |   6000 |
  |  1 | 張三   | 男   |   5500 |
  |  8 | 王五   | 男   |   3600 |
  | 11 | 馮小芳 | 女   |  10000 |
  |  5 | 李芳   | 女   |   8500 |
  |  2 | 李潔   | 女   |   4500 |
  |  3 | 李小梅 | 女   |   4200 |
  | 12 | 馬小龍 | 女   |   4000 |
  +----+--------+------+--------+
  12 rows in set (0.00 sec)

3. limit的使用

  • 在

    SELECT

     語句中使用

    LIMIT

     子句來限制要傳回的記錄數，通常使用LIMIT實作分頁
  • 文法 
    #limit文法
SELECT column_name1, column_name2
FROM table_name1, table_name2
LIMIT [offset,] row_count

    • offset

       指定要傳回的第一行的偏移量。第一行（第一條記錄）的偏移量是0，而不是1

    • row_count

       指定要傳回的最大行數（傳回多少條記錄）
    • 例：

      LIMIT 0,20

       傳回最初20條記錄
  • 分頁公式

    • limit (page-1)\*row_count, row_count
  • 示例 
    mysql> select * from employee limit 3;
+----+--------+------+--------+
| id | name   | sex  | salary |
+----+--------+------+--------+
|  1 | 張三    | 男   |   5500 |
|  2 | 李小梅  | 女   |   4200 |
|  3 | 歐陽輝  | 男    |   7500 |
+----+--------+------+--------+
3 rows in set (0.00 sec)

mysql> select * from employee order by salary desc limit 0,5;
+----+--------+------+--------+
| id | name   | sex  | salary |
+----+--------+------+--------+
|  6 | 李四    | 男    |  12000 |
| 10 | 馮小芳  | 女    |  10000 |
|  4 | 李芳     | 女   |   8500 |
|  9 | 龍五     | 男   |   8000 |
|  3 | 歐陽輝   | 男   |   7500 |
+----+--------+------+--------+
5 rows in set (0.00 sec)

mysql> select * from employee order by salary desc limit 10,5;
+----+------+------+--------+
| id | name | sex  | salary |
+----+------+------+--------+
|  7 | 王五  | 男   |   3600 |
| 12 | 哈哈  | 男   |   NULL |
+----+------+------+--------+
2 rows in set (0.00 sec)

# 綜合order和limit，找出工資最多的人
mysql> select * from employee order by salary desc limit 1;
+----+------+------+--------+
| id | name | sex  | salary |
+----+------+------+--------+
|  6 | 李四  | 男   |  12000 |
+----+------+------+--------+
1 row in set (0.00 sec)

四、GROUP BY與HAVING的使用

1. 應用場景

  • 用

    select

     語句結合

    where

     查詢條件擷取需要的資料，但在實際的應用中，還會遇到下面這類需求
    • 公司想知道每個部門有多少名員工-----需按部分分組
    • 班主任想統計各科第一名的成績------需按科目分組
    • 某門店想掌握男、女性會員的人數及平均年齡-----需按年齡分組

2. group by的使用

  • 從字面上了解，

    group by

     表示根據某種規則對資料進行分組，它必須配合聚合函數進行使用，對資料進行分組後可以用聚合函數進行處理（count、sum、avg、max和min等）
  • 文法 
    #group by文法
SELECT column_name, aggregate_function(column_name)
FROM table_name
GROUP BY column_name

    • aggregate_function

       表示聚合函數

    • group by

       可以對一列或多列進行分組
  • 注意： 标準SQL中，select選出的，要麼是用于分組的元素/字段，要麼是聚合函數
  • 示例
    • 先準備資料 
      create table employee(
	id int not null auto_increment primary key,
	name varchar(30) comment '姓名',
	sex varchar(1) comment '性别',
	salary int comment '薪資（元）',
	dept varchar(30) comment '部門'
)ENGINE=InnoDB default charset=utf8;

insert into employee(name,sex,salary,dept) values('張三','男',5500,'A');
insert into employee(name,sex,salary,dept) values('李潔','女'，4500,'C');
insert into employee(name,sex,salary,dept) values('李小梅','女',4200,'A');
insert into employee(name,sex,salary,dept) values('歐陽輝','男',7500,'C');
insert into employee(name,sex,salary,dept) values('李芳','女',8500,'A');
insert into employee(name,sex,salary,dept) values('張江','男',6800,'A');
insert into employee(name,sex,salary,dept) values('李四','男',12000,'B');
insert into employee(name,sex,salary,dept) values('王五','男',3600,'B');
insert into employee(name,sex,salary,dept) values('馬小龍','男',6000,'A');
insert into employee(name,sex,salary,dept) values('龍五','男',8000,'C');
insert into employee(name,sex,salary,dept) values('馮小芳','女',10000,'C');
insert into employee(name,sex,salary,dept) values('馬小龍','女',4000,'A');
    • 使用group by分組，再用聚合函數處理 
      mysql> select sex,count(*) from employee group by sex;
+------+----------+
| sex  | count(*) |
+------+----------+
| 女    |        4 |
| 男    |        7 |
+------+----------+
2 rows in set (0.00 sec)

mysql> select dept,count(*) from employee group by dept;
+------+----------+
| dept | count(*) |
+------+----------+
| A    |        6 |
| B    |        3 |
| C    |        2 |
+------+----------+
3 rows in set (0.00 sec)

# 這裡sex字段不是用于分組的，非标準SQL寫法，不推薦
mysql> select sex,count(*) from employee group by dept;
+------+----------+
| sex  | count(*) |
+------+----------+
| 男    |        6 |
| 男    |        3 |
| 男    |        2 |
+------+----------+
3 rows in set (0.00 sec)

# 用as給查出的字段起别名
mysql> select salary as s,max(salary) as S from employee group by dept;
+-------+-------+
| s     | S     |
+-------+-------+
|  5500 |  8500 |
| 12000 | 12000 |
|  7500 |  8000 |
+-------+-------+
3 rows in set (0.00 sec)

3. having的使用

  • 在 SQL 中增加

    HAVING

     子句原因是，

    WHERE

     關鍵字無法與聚合函數一起使用。

    HAVING

     子句可以對分組後的各組資料進行篩選
    • where子句：對group by分組前的資料進行過濾
    • having子句：對group by分組後的資料進行過濾
  • 文法 
    #having文法
SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name
HAVING aggregate_function(column_name) operator value
  • 示例（用having在分組結果上進一步過濾） 
    mysql> select dept,count(*) from employee group by dept having count(*)<5;
+------+----------+
| dept | count(*) |
+------+----------+
| B    |        3 |
| C    |        2 |
+------+----------+
2 rows in set (0.00 sec)

mysql> select dept,max(salary) from employee group by dept having max(salary)>=10000;
+------+-------------+
| dept | max(salary) |
+------+-------------+
| B    |       12000 |
| C    |       10000 |
+------+-------------+
2 rows in set (0.00 sec)

五、GROUP_CONCAT函數的使用

1. 應用場景

  • 使用

    group by

     可以分組統計每個部門有多少員工。假如，除了統計每個部門的員工數量之外，還想知道具體是哪些員工（員工清單），又該如何實作呢
    MySQL學習筆記（4）—— 資料查詢語言DQL一、查詢二、MySQL内置函數三、查詢結果排序與分頁四、GROUP BY與HAVING的使用五、GROUP_CONCAT函數的使用六、使用DISTINCT去重七、表連接配接（内連接配接、外連接配接、自連接配接）八、子查詢EXISTS和IN

2. group_concat的使用

  • 配合

    group by

     一起使用，用于将某一列的值按指定的分隔符進行拼接，MySQL預設的分隔符為逗号
  • 文法 
    #group_concat文法
group_concat([distinct] column_name [order by column_name asc/desc ] [separator '分隔符']) 
from table_name
GROUP BY column_name

    • distinct

       ：選擇要不要去重
  • 示例 
    mysql> select dept,group_concat(name) from employee group by dept;
+------+-------------------------------------+
| dept | group_concat(name)                  |
+------+-------------------------------------+
| A    | 張三,馬小龍,馬小龍,張江,李芳,李小梅 	 |
| B    | 王五,李四                            |
| C    | 歐陽輝,龍五,馮小芳                     |
+------+-------------------------------------+
3 rows in set (0.34 sec)


mysql> select dept,group_concat(name separator '_') from employee group by dept;
+------+-------------------------------------+
| dept | group_concat(name separator '_')    |
+------+-------------------------------------+
| A    | 張三_馬小龍_馬小龍_張江_李芳_李小梅      |
| B    | 王五_李四                            |
| C    | 歐陽輝_龍五_馮小芳                     |
+------+-------------------------------------+
3 rows in set (0.00 sec)

六、使用DISTINCT去重

  • distinct

     用于在查詢中傳回列的唯一不同值（去重複），支援單列或多列。在實際的應用中，表中的某一列含有重複值是很常見的，如

    employee

     表的

    dept

     列。如果在查詢資料時，希望得到某列的所有不同值，可以使用

    distinct
  • 文法 
    #distinct文法
SELECT DISTINCT column_name, column_name
FROM table_name;
  • 示例 
    mysql> select distinct dept from employee;
+------+
| dept |
+------+
| A    |
| C    |
| B    |
+------+
3 rows in set (0.00 sec)

七、表連接配接（内連接配接、外連接配接、自連接配接）

1. 什麼是表連接配接

  • 表連接配接（JOIN）是在多個表之間通過一定的連接配接條件，使表之間發生關聯，進而能從多個表之間擷取資料
  • 文法 
    #表連接配接文法
SELECT table1.column, table2.column 
FROM table1, table2 
WHERE table1.column1 = table2.column2;	# 連接配接條件（不一定是等于）
MySQL學習筆記（4）—— 資料查詢語言DQL一、查詢二、MySQL内置函數三、查詢結果排序與分頁四、GROUP BY與HAVING的使用五、GROUP_CONCAT函數的使用六、使用DISTINCT去重七、表連接配接（内連接配接、外連接配接、自連接配接）八、子查詢EXISTS和IN

2. 表連接配接的幾種方式

MySQL學習筆記（4）—— 資料查詢語言DQL一、查詢二、MySQL内置函數三、查詢結果排序與分頁四、GROUP BY與HAVING的使用五、GROUP_CONCAT函數的使用六、使用DISTINCT去重七、表連接配接（内連接配接、外連接配接、自連接配接）八、子查詢EXISTS和IN

3. 各種表連接配接的差別

MySQL學習筆記（4）—— 資料查詢語言DQL一、查詢二、MySQL内置函數三、查詢結果排序與分頁四、GROUP BY與HAVING的使用五、GROUP_CONCAT函數的使用六、使用DISTINCT去重七、表連接配接（内連接配接、外連接配接、自連接配接）八、子查詢EXISTS和IN
  • 注：
    • mysql中不支援全連接配接
    • 這個表格中給出的示例用了

      join

       表示連接配接條件
      • 内連接配接也可以用

        where

         子句實作
      • 左連接配接/右連接配接不能用

        where

         實作
    • 不使指定連接配接條件，得到兩個表的笛卡爾積。就是用一個表的每一個元組連接配接第二個表的每一個元組。這個在實際使用中沒啥用

（1）内連接配接

  • 隻有比對的行被連接配接
    MySQL學習筆記（4）—— 資料查詢語言DQL一、查詢二、MySQL内置函數三、查詢結果排序與分頁四、GROUP BY與HAVING的使用五、GROUP_CONCAT函數的使用六、使用DISTINCT去重七、表連接配接（内連接配接、外連接配接、自連接配接）八、子查詢EXISTS和IN

（2）左連接配接

  • 左邊表的所有行（元組）都必須出現，每一行可能有不止一個連接配接
    MySQL學習筆記（4）—— 資料查詢語言DQL一、查詢二、MySQL内置函數三、查詢結果排序與分頁四、GROUP BY與HAVING的使用五、GROUP_CONCAT函數的使用六、使用DISTINCT去重七、表連接配接（内連接配接、外連接配接、自連接配接）八、子查詢EXISTS和IN

（3）自連接配接

  • 自連接配接是一種特殊的表連接配接，它是指互相連接配接的表在實體上同為一張表，但是邏輯上是多張表。自連接配接通常用于表中的資料有層次結構，如區域表、菜單表、商品分類表等。
  • 文法 
    #自連接配接文法
SELECT A.column1, B.column2 
FROM table A, table B
WHERE 連接配接條件;

4. 示例

  • 準備表 
    create table student(
	stu_no varchar(20) not null primary key comment '學号',
	name varchar(30) comment '姓名',
	address varchar(150) comment '位址'
);

insert into student(stu_no,name,address) values('2016001','張三','貴州');
insert into student(stu_no,name,address) values('2016002','李芳','陝西');
insert into student(stu_no,name,address) values('2016003','張曉燕','江西');

create table score(
	id int not null auto_increment primary key,
	course varchar(50) comment '科目',
	stu_no varchar(20) comment '學号',
	score int comment '分數',
	foreign key(stu_no) references student(stu_no)
);

insert into score(course,stu_no,score) values('計算機','2016001',99);
insert into score(course,stu_no,score) values('離散','2016001',85);
insert into score(course,stu_no,score) values('計算機','2016002',78);
  • 内連接配接 
    # 使用join訓示連接配接條件
mysql> select A.stu_no,A.name,B.course,B.score
    -> from student A
    -> join score B on(A.stu_no = B.stu_no);	# 這裡寫inner join效果一樣
+---------+------+--------+-------+
| stu_no  | name | course | score |
+---------+------+--------+-------+
| 2016001 | 張三 | 計算機   |    99 |
| 2016001 | 張三 | 離散     |    85 |
| 2016002 | 李芳 | 計算機   |    78 |
+---------+------+--------+-------+
3 rows in set (0.33 sec)

# 使用where子句訓示連接配接條件
mysql> select A.stu_no,A.name,B.course,B.score
    -> from student A,score B
    -> where A.stu_no = B.stu_no;
+---------+------+--------+-------+
| stu_no  | name | course | score |
+---------+------+--------+-------+
| 2016001 | 張三 | 計算機   |    99 |
| 2016001 | 張三 | 離散     |    85 |
| 2016002 | 李芳 | 計算機   |    78 |
+---------+------+--------+-------+
3 rows in set (0.00 sec)
  • 左連接配接 
    mysql> select A.stu_no,A.name,B.course,B.score
    -> from student A
    -> left join score B on(A.stu_no = B.stu_no);	# 注意這裡的left
+---------+--------+--------+-------+
| stu_no  | name   | course | score |
+---------+--------+--------+-------+
| 2016001 | 張三   | 計算機   |    99 |
| 2016001 | 張三   | 離散     |    85 |
| 2016002 | 李芳   | 計算機   |    78 |
| 2016003 | 張曉燕 | NULL    |  NULL |
+---------+--------+--------+-------+
4 rows in set (0.00 sec)
  • 自連接配接
    • 準備表 
      create table area(
	id int not null auto_increment primary key comment '區域id',
	pid int not null comment '父id(0-省份)',	# 如果pid為0則是省；否則pid為所屬省id
	name varchar(30) comment '名稱'
);

insert into area(id,pid,name) values(1,0,'貴陽');
insert into area(id,pid,name) values(2,1,'貴州');	# 屬于貴陽
insert into area(id,pid,name) values(3,1,'遵義');	# 屬于貴陽
insert into area(id,pid,name) values(4,0,'廣東');
insert into area(id,pid,name) values(5,4,'廣州');	# 屬于廣東
insert into area(id,pid,name) values(6,4,'深圳'); 	# 屬于廣東
    • 示例 
      mysql> select A.id,A.name,B.name as province
    -> from area A,area B
    -> where A.pid = B.id and A.pid != 0;
+----+------+----------+
| id | name | province |
+----+------+----------+
|  2 | 貴州  | 貴陽     |
|  3 | 遵義  | 貴陽     |
|  5 | 廣州  | 廣東     |
|  6 | 深圳  | 廣東     |
+----+------+----------+
4 rows in set (0.00 sec)

八、子查詢EXISTS和IN

1. 子查詢in

  • 之前的課程中，我們已經學習過運算符

    IN

     ，它允許我們在

    WHERE

     子句中過濾某個字段的多個值 
    #where子句使用in文法
SELECT column_name FROM table_name WHERE column_name IN(value1, value2, …)
  • 如果運算符

    in

     後面的值是來源于某個查詢結果，并非是指定的幾個值，這時就需要用到子查詢。子查詢又稱為内部查詢或嵌套查詢，即在 SQL 查詢的

    WHERE

     子句中嵌入查詢語句 
    #子查詢in文法
SELECT column_name 
FROM table_name 
WHERE column_name [not] IN(
 	SELECT column_name FROM table_name [WHERE]
);
  • 示例 
    # 查詢所有選課了的學生（先從score表B中找出所有學号，再從student中找出學号屬于那些的學生資訊）
mysql> select A.*
    -> from student A
    -> where A.stu_no in (select B.stu_no from score B);
+---------+------+---------+
| stu_no  | name | address |
+---------+------+---------+
| 2016001 | 張三 | 貴州    |
| 2016002 | 李芳 | 陝西    |
+---------+------+---------+
2 rows in set (0.00 sec)


# 查選了離散的學生
mysql> select A.*
    -> from student A
    -> where A.stu_no in (select B.stu_no from score B where B.course = '離散') ;
+---------+------+---------+
| stu_no  | name | address |
+---------+------+---------+
| 2016001  | 張三 | 貴州    |
+---------+------+---------+
1 row in set (0.00 sec)

2. 子查詢exists

  • EXISTS

     是子查詢中用于測試内部查詢是否傳回任何行的布爾運算符。将主查詢的資料放到子查詢中做條件驗證，根據驗證結果（

    TRUE

     或

    FALSE

     ）來決定主查詢的資料結果是否保留
  • 文法 
    #where子句使用exists文法
SELECT column_name1 
FROM table_name1 
WHERE [not] EXISTS (SELECT * FROM table_name2 WHERE condition);
  • 示例 
    # 這裡子查詢沒限制，從A中查出的每個元組送入子查詢比較，都不會傳回false
mysql> select A.*
    -> from student A
    -> where exists(select * from score B);
+---------+--------+---------+
| stu_no  | name   | address |
+---------+--------+---------+
| 2016001 | 張三   | 貴州    |
| 2016002 | 李芳   | 陝西    |
| 2016003 | 張曉燕 | 江西    |
+---------+--------+---------+
3 rows in set (0.00 sec)

# 查詢所有選課了的學生（注意這裡子查詢加了限制，可以把沒選課的張曉燕過濾出去）
mysql> select A.*
    -> from student A
    -> where exists(select * from score B where A.stu_no = B.stu_no);
+---------+------+---------+
| stu_no  | name | address |
+---------+------+---------+
| 2016001 | 張三 | 貴州    |
| 2016002 | 李芳 | 陝西    |
+---------+------+---------+
2 rows in set (0.00 sec)

# 查詢所有沒選課的學生（用not exists）
mysql> select A.*
    -> from student A
    -> where not exists(select * from score B where A.stu_no = B.stu_no);
+---------+--------+---------+
| stu_no  | name   | address |
+---------+--------+---------+
| 2016003 | 張曉燕  | 江西    |
+---------+--------+---------+
1 row in set (0.00 sec)
# MySQL mysql 資料查詢 select
上一篇: springboot中提升資料庫性能-redis緩存和資料監控1.最近在看springboot ，簡單做一個記錄
下一篇: 資料庫設計及規範 - MySQL

繼續閱讀