MySQL从入门到高级 --- 5.DQL基本操作

文章目录

- 第五章：
- - 5.基本操作 - DQL
  - - 5.1 运算符
    - - 逻辑运算符
      - 位运算符
      - 算术运算符
    - 5.2 条件查询
    - 5.3 排序查询
    - 5.4聚合查询
    - - 5.4.1 聚合查询-NULL值处理
    - 5.5 分组查询 - group by
    - 5.5.1 条件筛选 - having
    - 5.6 分页查询 - limit
    - 5.7 INSERT INTO SELECT 语句
    - 5.8 练习
    - 5.9 正则表达式
    - 5.9 多表操作
    - - 5.9.1 外键约束
      - 方式1 - 在创建表时设置外键约束
        方式2 - 在创建表后设置外键约束
        数据插入
        删除数据
        删除外键约束
        多对多关系
      - 5.9.2 多表联合查询
      - 交叉连接查询
        内连接查询
        外连接查询
        子查询
        子查询关键字
        ALL
        ANY
        SOME
        IN
        EXISTS
      - 自关联查询
      - 5.9.3 练习

第五章：

5.基本操作 - DQL

数据查询是数据库管理系统中一个重要功能，数据查询不应是简单返回数据中存储的数据，而是根据需要对数据进行筛选以及确认数据的格式显示

语法：

select

[all|distinct]<目标列的表达式1> [别名],<目标列的表达式2> [别名]....

from <表名或视图名> [别名], <表名或视图名> [别名] …

[where <条件表达式>]

[group by <列名>]

[having <条件表达式>]

[order by <列名> [asc|desc]];

[limit <数字或列表>];

简化：select *| 列名 from 表名 where 条件

在这里插入图片描述

5.1 运算符

逻辑运算符

逻辑运算符	功能
NOT或！	逻辑非
AND或&&	逻辑与
OR 或\|
XOR	逻辑异或

位运算符

位运算符	功能

&	按位与
^	按位异或
<<	按位左移
>>	按位右移
~	按位取反

在这里插入图片描述

算术运算符

算术运算符	功能
+	加
-	减
*	乘
/	除
%	取模

在这里插入图片描述

5.2 条件查询

在这里插入图片描述

5.3 排序查询

语法：

select

字段名1，字段名2，....

from 表名

order by 字段名1 [asc|desc] , 字段名2 [asc|desc] …

asc升序,desc降序,默认升序
order by用于子句中可单个字段，多个字段，表达式，函数，别名
order by子句，放在查询语句末尾。除了LIMIT子句。

在这里插入图片描述

5.4聚合查询

纵向查询，对一列的值进行计算，返回一个单一的值，忽略空值

聚合函数	功能
count()	统计指定列中不为NULL的记录行数
sum()	计算指定列的数值和，若指定列类型不为数值类型，计算结果为0
max()	计算指定列的最大值，若指定列类型为字符串类型，使用字符串排序计算
min()	计算指定列的最小值，若指定列类型为字符串类型，使用字符串排序计算
avg()	计算指定列的平均值，若指定列类型不为数值类型，计算结果为0

在这里插入图片描述

5.4.1 聚合查询-NULL值处理

count：若参数为(*)，则统计所有记录个数。当参数为某字段时，不统计含null值的记录个数
sum与avg：忽略null值的存在
max与min：忽略null值的存在

5.5 分组查询 - group by

对查询信息进行分组

5.5.1 条件筛选 - having

分组后对统计结果进行筛选需使用having，不可使用where
where子句用于删筛选from子句中指定操作中所产生的行
group by子句用于分组where子句中的输出
having子句用于从分组的结果中筛选行

语法：select 字段1，字段2，… from 表名 group by 分组字段 having 分组条件；

在这里插入图片描述

注意：若要进行分组的话，select子句后只能出现分组字段和统计函数，其他字段不可出现

SQL执行顺序：from -> group by -> count() -> select -> having -> order by

5.6 分页查询 - limit

由于数据量大，显示屏长度有限，因此需对数据采取分页的显示方式

语法1：select 字段1，字段2，… from 表名 limit n;

语法2：select 字段1，字段2，… from 表名 limit m,n;

m：表示从第几条索引开始，计算方法：（当前页-1） * 每页显示条数
n：表示查询多少条数据

在这里插入图片描述

5.7 INSERT INTO SELECT 语句

语法：insert into table2(field1, field2,…) select value1, value2, … from table1 或 insert into table2 select * from table1

将一张表的数据导入到另一张表中，table2表必须存在

5.8 练习

在这里插入图片描述

5.9 正则表达式

本身就是一个字符串，使用该字符串用来描述、定义匹配规则，匹配一系列符合某个语句规则的字符串。通常被用来检索、替换符合某个规则的文本。

关键字：REGEXP

格式：

模式	功能
^	匹配输入字符串的开始位置
$	匹配输入字符串的结束位置
.	匹配除’\n‘之外的单个字符
[…]	字符集合，匹配所包含的任意一个字符
[^…]	负值字符集合，匹配未包含的任意字符
p1\|p2\|p3	匹配p1或p2或p3
*	匹配前面的子表达式零次或多次
+	匹配前面的子表达式一次或多次
{n}	匹配n次，n是一个非负整数
{n,m}	n<=m,最少匹配n次且最多匹配m次,m和n均为非负整数

在这里插入图片描述

5.9 多表操作

多表关系：

一对一：在任一表中添加唯一外键，指向另一方主键，确保一对一关系
一对多/多对一：在多的一方建立外键，指向一的一方的主键
多对多：实现需要借助第三张中间表。中间表至少包含两个字段，将多对多的关系，拆分成一对多的关系，中间表至少要有两个外键，这两个外键分别指向原来的两张表的主键

5.9.1 外键约束

表的一种特殊字段，用来建立主表与从表的关联关系，为两个表的数据建立连接，约束两个表中数据的一致性和完整性
对于两个具体关联关系的表而言，相关联字段中主键所在的表就是主表(父表)，外键所在的表就是从表(子表)

特点：

外键中列的数据数类型必须和主表主键中对应的数据类型相同
外键中列的数目必须和主表的主键中的数目相同
在主表的表明后面指定列名或列名的组合。该列或列的组合必须是主表的主键或候选键
主键不能包含空值，但允许在外键中出现空值
必须为主表定义主键
主表必须已存在于数据库中，或者是当前正在创建的表

关键字：foreign key

方式1 - 在创建表时设置外键约束

语法：[constraint <外键名>] foreign key 字段名 [, 字段名2, …] references <主表名> 主键列1 [，主键列2, …]

在这里插入图片描述

方式2 - 在创建表后设置外键约束

语法：alter table <数据表名> add constraint <外键名> foreign key (<列名>) references <主表名> (<列名>)

在这里插入图片描述

数据插入

在这里插入图片描述

删除数据

在这里插入图片描述

删除外键约束

语法：alter table <表名> drop foreign key <外键约束名>;

mysql">alter table emp2 drop foreign key dept_id_pk;

多对多关系

在这里插入图片描述

5.9.2 多表联合查询

多表查询是同时查询两个或两个以上的表。

分类：

交叉连接查询
内连接查询
外连接查询
子查询
表自关联

交叉连接查询

会返回被连接德两个表德所有数据行德笛卡尔积
笛卡尔积：一张表的每一行去和另一张表的任意一行进行匹配
笛卡尔积会产生很多冗余的数据
假设A表有M行，B表有N行，将会返回M*N行的数据

语法：select * from 表1, 表2, …;
在这里插入图片描述

在这里插入图片描述

内连接查询

查询多张表的交集。

语法：

隐式内连接(SQL92标准)：select * from A, B where 条件;

显示内连接(SQL99标准)：select * from A inner join B on 条件;
在这里插入图片描述

外连接查询

分类：

左外连接
右外连接
满外连接

左外连接语法：select * from A left outer join B 条件;

右外连接语法：select * from A right outer join B on 条件;

满外连接语法：select * from A full outer join B on 条件;

注意：mysql中对full join支持的不太好，可用union替代使用

在这里插入图片描述

子查询

包含select嵌套的查询

分类：

单行单列：返回一个具体列的内容
单行多列：返回一行数据中多个列的内容
多行单列：返回多行记录之间中同一列的内容，相当于给出了个操作范围
多行多列：返回结束为一个临时表

在这里插入图片描述

子查询关键字

ALL
ANY
SOME
IN
EXISTS

ALL

语法：select … from … where c > all(查询语句) --等价-- select … from … where c > result 1 and c > result2 and c > result3…

可于=、>、>=、< 、<=、 <>结合使用
指定列中的值必须大于子查询集中的每一个值，若是小于号，则小于查询表的最小值

ANY

语法：select … from … where c > all(查询语句) --等价-- select … from … where c > result 1 or c > result2 or c > result3…

与SOME作用相同

SOME

在这里插入图片描述

IN

语法：select …from … where c in(查询语句)

用于判断某个记录的值是否在指定的集合中

在这里插入图片描述

EXISTS

语法：select …from … where exists(查询语句)

EXISTS后的子查询不返回任何实际数据，只返回真或假，返回真时，where条件成立

在这里插入图片描述

自关联查询

一张表自己和自己关联，一张表当成多张表使用。自关联时必须给表起别名

语法：select 字段列表 from 表1 a, 表1 b where 条件；

        select 字段列表 from 表1 a [left] join 表1 b on 条件；

在这里插入图片描述

5.9.3 练习

在这里插入图片描述