MYSQL——DQL : 数据查询语句(select)

深藏阁楼爱情的钟 2022-07-14 03:21 254阅读 0赞

*  1SELECT 查询语句和条件语句
    
     *   *   *  select过滤去重
             *  select list其中可以包含一项或多项下列内容
             *  表名 数据库表名表名 AS 表别名
             *  常用条件
 *  2排序分组指针查询计算
    
     *  聚合合计函数计算
     *  控制流程函数
 *  联合查询Union将多条select语句的结果合并到一起称之为联合操作
    
     *  union联合
     *  Union all环境
     *  union排序
        
         *   *  子语句结果的排序
             *  对所以结果进行统一排序
 *  4子查询语句内部的查询语句就是子查询语句
    
     *   *  返回值分类单一值一列多列表多行多列
         *  出现位置
         *  如何使用获得一个值也就是说返回一个值使用关系运算符进行判断
     *  返回一行
        
         *  返回一个表
     *  Exists
        
         *   *  解决思路是不一样的
 *  5多表查询jion
    
     *   *   *  测试表
             *  内连接inner join
             *  外连接outer join
             *  自然连接
             *  交叉连接或笛卡尔积cross join

# 1、SELECT 查询语句和条件语句 #

SELECT [ALL | DISTINCT | DISTINCTROW ]
    select_list
    FROM tbl_name [AS] alias_name
    [WHERE where_definition]
    [GROUP BY {col_name | expr | position}
    [ASC | DESC], ... [WITH ROLLUP]]
    [HAVING where_definition]
    [ORDER BY {col_name | expr | position}
    [ASC | DESC] , ...]
    [LIMIT {[offset,] row_count | row_count OFFSET offset}]

#### select过滤、去重 ####

**DISTINCT、DISTINCTROW对查询返回的结果集提供了一个最基本但是很有用的过滤。那就是结果集中只含非重复行。重复的记录，指的是，字段、值，都相同的记录，而不是部分字段相同的记录。在这里要注意的是，对关键词DISTINCT、DISTINCTROW来说，空值都是相等的，无论有多少NULL值，只选择一个。默认是“all”，表示所有。**

#### select list：其中可以包含一项或多项下列内容： ####

1、“\*”，表示按照create table的顺序排列的所有列。  
2、按照用户所需顺序排列的列名的清单。  
3、可以使用别名取代列名，形式如下：column name as column\_heading。  
4、表达式（列名、常量、函数，或以算术或逐位运算符连接的列名、常量和函数的任何组合）。  
5、内部函数或集合函数。  
6、上述各项的任何一种组合。

#### 表名： 数据库.表名|表名 \[AS\] 表别名 ####

#### 常用条件： ####

1.  <、= 等于 、<>不等于、>、in (n,m) 包含 、 not in (n,m)不包含、like 匹配 、 between n and m (在n~m的范围) 、 not BETWEEN 不在范围 、is null：在搜索一个NULL值时使用。
2.  条件运算： and 、 or 、 ( )

# 2、排序,分组,指针查询,计算 #

分组语句：group by 字段 (语句最后 order之前)  
排序语句：order by 字段，字段 ASC / DESC (语句最后 limit之前，默认 升序，asc)  
指针查询：limit 初始值，结束值 (语句最后)

## 聚合（合计）函数(计算)： ##

`COUNT(*)` 统计函数

`MAX(*)`最大值函数

`MIN (*)`最小值函数

`AVG(*)` 平均值函数

`SUM(*)`累计值函数（∑）

## 控制流程函数 ##

select (case when 1<2 then 'yes' else 'no' end) as test;
    
    select if(1<2,'yes ','no') as test;

# 3 联合查询（Union）：将多条select语句的结果，合并到一起。称之为联合操作。 #

## union联合 ##

> **使用 union关键字 联合 两个select语句即可：**

mysql> select * from `users` where `age`=12
     union
     select * from `users` where `age`=13; +----------+-----+------+
    | userName | age | sex | +----------+-----+------+
    | 小敏        |  12 | 女    |
    | 小红        |  12 | 女    |
    | 小明        |  13 | 女    |
    | 小刚        |  13 | 男    |
    | 小默 | 13 | 女 | +----------+-----+------+
    5 rows in set (0.00 sec)

## Union all:环境： ##

1，获得0115班所有代课教师，结果按照代课天数升序排序，同时需要获得0228班，结果按照降序排序  
注意，如果union 的结果存在重复的记录，那么会消除重复.  
可以通过 union选项 all 达到目的。

mysql> select * from `users` where `age`=12
     union all
     select * from `users` where `age`=13; +----------+-----+------+
    | userName | age | sex | +----------+-----+------+
    | 小敏        |  12 | 女    |
    | 小敏        |  12 | 女    |
    | 小敏        |  12 | 女    |
    | 小红        |  12 | 女    |
    | 小明        |  13 | 女    |
    | 小明        |  13 | 女    |
    | 小刚        |  13 | 男    |
    | 小默 | 13 | 女 | +----------+-----+------+
    8 rows in set (0.00 sec)
    
    mysql>

## union排序 ##

#### 子语句结果的排序： ####

1.  将子语句包裹子括号内。
2.  子语句的order by，只要在配合limit时，才生效。union在做子语句时，会对没有limit的子语句进行优化

mysql> (select * from `users` where `userName`='小敏' ORDER BY `age`)
     union all
     (select * from `users` where `userName`='小明' ORDER BY `age` desc); +----------+-----+------+
    | userName | age | sex | +----------+-----+------+
    | 小敏        |  16 | 女    |
    | 小敏        |  10 | 女    |
    | 小敏        |  12 | 女    |
    | 小明        |   5 | 女    |
    | 小明 | 9 | 女 | +----------+-----+------+
    5 rows in set (0.00 sec)
    
    #子语句的order by，只要在配合limit时，才生效
    mysql> (select * from `users` where `userName`='小敏' ORDER BY `age` limit 3)
     union all
     (select * from `users` where `userName`='小明' ORDER BY `age` limit 2); +----------+-----+------+
    | userName | age | sex | +----------+-----+------+
    | 小敏        |  10 | 女    |
    | 小敏        |  12 | 女    |
    | 小敏        |  16 | 女    |
    | 小明        |   5 | 女    |
    | 小明 | 9 | 女 | +----------+-----+------+
    5 rows in set (0.00 sec)
    
    mysql>

#### 对所以结果进行统一排序： ####

只需要在最后一个select语句后，增加相应的排序规则即可

mysql> (select * from `users` where `userName`='小敏')
    union all
    (select * from `users` where `userName`='小明')
    ORDER BY `age` ;
    +----------+-----+------+
    | userName | age | sex  |
    +----------+-----+------+
    | 小明        |   5 | 女    |
    | 小明        |   9 | 女    |
    | 小敏        |  10 | 女    |
    | 小敏        |  12 | 女    |
    | 小敏        |  16 | 女    |
    +----------+-----+------+
    5 rows in set (0.00 sec)

> \*\*提示：  
> 子语句的括号不是必须的，是为了方便语句识别  
> 注意：  
> 规定，多个select语句的检索到的字段数，必须一致。  
> 更加严格的是，数据类型上，应该也有要求一致：  
> 但是，mysql内部会做类型转换处理，要求是能够转换成功；  
> 检索结果中的列名称问题：  
> 以第一条select语句的列名称而定\*\*

# 4、子查询：语句内部的查询语句，就是子查询语句。 #

### 返回值分类：单一值，一列，多列，表（多行，多列） ###

### 出现位置： ###

1.  **where后：where(subquery)**
2.  **exists后：exists(subquery)**
3.  **from后：from(subquery)**

### 如何使用：获得一个值（也就是说返回一个值），使用关系运算符，进行判断=、>、>=、<、<=、!= ###

mysql> SELECT * FROM `users` WHERE `age` = (SELECT max(age) FROM `users`); +----------+-----+------+
    | userName | age | sex | +----------+-----+------+
    | 小敏 | 16 | 女 | +----------+-----+------+
    1 row in set (0.00 sec)

集合运算符：当返回多列（多个值时），  
使用集合运算符，  
in、not in  
=any(集合) 等于集合中的任何一个即可。等同于in  
!=any(集合)不等于集合中的任意一个。  
!=all(集合) 不等于集合中的所有元素  
some(集合)集中的一些与any(集合)等同  
Not any(集合) 不是任何一个的,与!=all(集合)等同  
some==any some在语法上与any相同

## 返回一行： ##

在参与比较时，使用括号可以构建一行：  
(filed1, field2)

mysql> SELECT * FROM `users` WHERE (`userName`, `age`) = ( SELECT `userName`, `age` FROM `users` ORDER BY `age` DESC LIMIT 1 ); +----------+-----+------+
    | userName | age | sex | +----------+-----+------+
    | 小敏 | 16 | 女 | +----------+-----+------+
    1 row in set (0.00 sec)

### 返回一个表： ###

如果用from子语句内，from子语句内，要求使用一个表，而能是一个结果，一应该给这个查询结果起一个别名。

外部查询所使用的列名，是由子查询指定的。

mysql> SELECT * FROM ( SELECT * FROM `users` WHERE age > 12 ) AS temp WHERE userName LIKE '小%'; +----------+-----+------+
    | userName | age | sex | +----------+-----+------+
    | 小敏        |  16 | 女    |
    | 小刚 | 13 | 男 | +----------+-----+------+
    2 rows in set (0.00 sec)
    
    mysql>

## Exists ##

Exists(subquery) 判断依据：  
如果子查询的 可以返回数据，则认为 exists 表达式 返回真。否者，返回假。

下面的两个语句完成的是同样的事情：

select * from `teacher_class` where exists (select * from `teacher` where `teacher_class`.`id`=`t_id`);
    
    select * from `teacher_class` where `id` in (select `t_id` from `teacher`);

#### 解决思路是不一样的： ####

exists： 先获得每一条 teacher\_class的数据，然后获得id字段，去teacher表内查找对应值，找到，说明符合条件。

in：先获得所有的id的可能性。再在检索teacher\_class数据时，判断当前的id是否在id集合内。

# 5、多表查询（jion） #

每个实体，一个表  
一个业务逻辑，使用多个实体的数据，  
多张表应该在一起使用，将多个表的记录连接起来。

总体思路：

将所有的数据，按照某种条件，连接起来，再进行筛选处理。

连接的分类：  
根据连接的条件不同，分类入如下：

1、内连接（inner join）  
2、外连接 (outer join)  
3、自然连接

#### 测试表 ####

**class表**

<table> 
 <thead> 
  <tr> 
   <th align="center">class_id</th> 
   <th align="center">class_name</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="center">1</td> 
   <td align="center">a</td> 
  </tr> 
  <tr> 
   <td align="center">2</td> 
   <td align="center">b</td> 
  </tr> 
  <tr> 
   <td align="center">3</td> 
   <td align="center">c</td> 
  </tr> 
 </tbody> 
</table>

**student表**

<table> 
 <thead> 
  <tr> 
   <th align="center">student_id</th> 
   <th align="center">class_id</th> 
   <th align="center">student_name</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="center">1</td> 
   <td align="center">1</td> 
   <td align="center">李萌</td> 
  </tr> 
  <tr> 
   <td align="center">2</td> 
   <td align="center">2</td> 
   <td align="center">王武</td> 
  </tr> 
  <tr> 
   <td align="center">3</td> 
   <td align="center">1</td> 
   <td align="center">李浩</td> 
  </tr> 
 </tbody> 
</table>

#### 内连接(inner join)： ####

> 数据内部的连接，要求，连接的多个数据都必须存在才能进行连接

例：

select c.*,s.* from student as s [inner] join class as c on c.id=s.class_id

**inner join是Mysql默认的连接方案，可以省略 inner。**

--------------------

> 这个语法是内连接的另外一种写法，其执行结果与inner join 一样

select * from class,student where class.class_id=student.class_id

**查询结果如下**

<table> 
 <thead> 
  <tr> 
   <th align="center">class_id</th> 
   <th align="center">class_name</th> 
   <th align="center">student_id</th> 
   <th align="center">class_id1</th> 
   <th align="center">student_name</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="center">1</td> 
   <td align="center">a</td> 
   <td align="center">1</td> 
   <td align="center">1</td> 
   <td align="center">李萌</td> 
  </tr> 
  <tr> 
   <td align="center">2</td> 
   <td align="center">b</td> 
   <td align="center">2</td> 
   <td align="center">2</td> 
   <td align="center">王武</td> 
  </tr> 
  <tr> 
   <td align="center">1</td> 
   <td align="center">a</td> 
   <td align="center">3</td> 
   <td align="center">1</td> 
   <td align="center">李浩</td> 
  </tr> 
 </tbody> 
</table>

#### 外连接(outer join)： ####

> 数据内部的连接，要求，连接的多个数据都必须存在才能进行连接

A left  join   B   #的连接的记录数与A表的记录数同
    
    A right  join   B   #的连接的记录数与B表的记录数同
    
    A left join  B
    #等价
    B right join A

例：

select c.*,s.* from student as s left/right join class as c on c.id=s.class_id

（RIGHT JOIN的作用与LEFT JOIN的作用类似。要使代码可以在数据库内移植，`建议您使用LEFT JOIN代替RIGHT JOIN`。）

**左外连接（left join）**

select c.*,s.* from student as s left join class as c on c.class_id=s.class_id

> 如果左外连接（left join）的话，它将显示class表中所有记录

**查询结果如下**

**右外连接（rgint join）**

select c.*,s.* from student as s right join class as c on c.class_id=s.class_id

> 如果右外连接（rgint join）的话，它将显示student表中所有记录

**查询结果如下**

#### 自然连接： ####

> 通过 mysql 自己的判断完成连接过程！不需要指定连接条件。mysql会使用多表内的，相同字段，作为连接条件（注意，两个表同名的列不能超过1个。） 自然连接分成 内外之分：  
> 内:*\* natural join \**  
> 外：左外：Natrual left join  
> 右外：Natrual right join
> 
> **注意：**  
> *\* on student.class\_id = class.class\_id 等同于 using(c1) \**  
> *\* using:会去掉结果中的 重复字段，并放在列前。 \**

*\* natural join \**

select * from student natural join class;
    #等同于
    select * from student inner join class using(`class_id`);

**查询结果如下**

<table> 
 <thead> 
  <tr> 
   <th align="center">class_id</th> 
   <th align="center">student_id</th> 
   <th align="center">student_name</th> 
   <th align="center">class_name</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="center">1</td> 
   <td align="center">1</td> 
   <td align="center">李萌</td> 
   <td align="center">a</td> 
  </tr> 
  <tr> 
   <td align="center">1</td> 
   <td align="center">3</td> 
   <td align="center">李浩</td> 
   <td align="center">a</td> 
  </tr> 
  <tr> 
   <td align="center">2</td> 
   <td align="center">2</td> 
   <td align="center">王武</td> 
   <td align="center">b</td> 
  </tr> 
 </tbody> 
</table>

*\* Natrual left join \**

select * from student natural left join class;
    #等同于
    select * from student left join class using(`class_id`);

**查询结果如下**

<table> 
 <thead> 
  <tr> 
   <th align="center">class_id</th> 
   <th align="center">student_id</th> 
   <th align="center">student_name</th> 
   <th align="center">class_name</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="center">1</td> 
   <td align="center">1</td> 
   <td align="center">李萌</td> 
   <td align="center">a</td> 
  </tr> 
  <tr> 
   <td align="center">2</td> 
   <td align="center">2</td> 
   <td align="center">王武</td> 
   <td align="center">b</td> 
  </tr> 
  <tr> 
   <td align="center">1</td> 
   <td align="center">3</td> 
   <td align="center">李浩</td> 
   <td align="center">a</td> 
  </tr> 
 </tbody> 
</table>

*\* Natrual right join \**

select * from student natural right join class;
    #等同于
    select * from student right join class using(class_id);

**查询结果如下**

<table> 
 <thead> 
  <tr> 
   <th align="center">class_id</th> 
   <th align="center">class_name</th> 
   <th align="center">student_id</th> 
   <th align="center">student_name</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="center">1</td> 
   <td align="center">a</td> 
   <td align="center">1</td> 
   <td align="center">李萌</td> 
  </tr> 
  <tr> 
   <td align="center">2</td> 
   <td align="center">b</td> 
   <td align="center">2</td> 
   <td align="center">王武</td> 
  </tr> 
  <tr> 
   <td align="center">1</td> 
   <td align="center">a</td> 
   <td align="center">3</td> 
   <td align="center">李浩</td> 
  </tr> 
  <tr> 
   <td align="center">3</td> 
   <td align="center">c</td> 
   <td align="center">null</td> 
   <td align="center">null</td> 
  </tr> 
 </tbody> 
</table>

#### 交叉连接或笛卡尔积(cross join): ####

> 内连接，在连接时，是可以省略连接条件的。意味着，所有的左表的数据，都要与右表的记录做一个连接。共存在 `N*M`个连接，这种连接，就称之为，交叉连接，或者笛卡尔积。如本例会产生3\*2=16条记录，在开发过程中我们肯定是要过滤数据，所以这种很少用。

SELECT * FROM TableA CROSS JOIN TableB ; 
    #等同于
    SELECT * FROM TableA [INNER] JOIN TableB ;

**查询结果如下**

<table> 
 <thead> 
  <tr> 
   <th align="center">id</th> 
   <th align="center">class_name</th> 
   <th align="center">student_id</th> 
   <th align="center">class_id</th> 
   <th align="center">student_name</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="center">1</td> 
   <td align="center">1</td> 
   <td align="center">李萌</td> 
   <td align="center">1</td> 
   <td align="center">a</td> 
  </tr> 
  <tr> 
   <td align="center">2</td> 
   <td align="center">2</td> 
   <td align="center">王武</td> 
   <td align="center">1</td> 
   <td align="center">a</td> 
  </tr> 
  <tr> 
   <td align="center">3</td> 
   <td align="center">1</td> 
   <td align="center">李浩</td> 
   <td align="center">1</td> 
   <td align="center">a</td> 
  </tr> 
  <tr> 
   <td align="center">1</td> 
   <td align="center">1</td> 
   <td align="center">李萌</td> 
   <td align="center">2</td> 
   <td align="center">b</td> 
  </tr> 
  <tr> 
   <td align="center">2</td> 
   <td align="center">2</td> 
   <td align="center">王武</td> 
   <td align="center">2</td> 
   <td align="center">b</td> 
  </tr> 
  <tr> 
   <td align="center">3</td> 
   <td align="center">1</td> 
   <td align="center">李浩</td> 
   <td align="center">2</td> 
   <td align="center">b</td> 
  </tr> 
  <tr> 
   <td align="center">1</td> 
   <td align="center">1</td> 
   <td align="center">李萌</td> 
   <td align="center">3</td> 
   <td align="center">c</td> 
  </tr> 
  <tr> 
   <td align="center">2</td> 
   <td align="center">2</td> 
   <td align="center">王武</td> 
   <td align="center">3</td> 
   <td align="center">c</td> 
  </tr> 
  <tr> 
   <td align="center">3</td> 
   <td align="center">1</td> 
   <td align="center">李浩</td> 
   <td align="center">3</td> 
   <td align="center">c</td> 
  </tr> 
 </tbody> 
</table>

> **注意：**  
> mysql中 cross join与inner join相同，但在数据库的定义上，交叉连接就是笛卡尔积。是没有条件的inner join。