【MySQL】基本操作 —— DQL

DQL 基本介绍

DQL（Data Query Language）数据查询语言，用来查询数据库中表的记录

概念：

• 数据库管理系统的一个重要功能就是数据查询，数据查询不应只是简单返回数据库中存储的数据，还应该根据需要对数据进行筛选以及确定数据以什么样的格式来显示
• MySQL 提供了功能强大、灵活的语句来实现这些操作
• MySQL 数据库使用 select 语句来查询数据

常见操作：

• 基本查询
• 条件查询
• 聚合函数
• 分组查询
• 排序查询
• 分页查询

DQL 常见操作

以下的操作内容都是基于下表

代码示例：

create table product(
    pid int primary key auto_increment, -- 商品编号
    pname varchar(20) not null, -- 商品名字
    price double, -- 商品价格
    category_id varchar(20) -- 商品所属分类
);
insert into product values (null,'海尔洗衣机',5000,'c001'),
                           (null,'美的冰箱',3000,'c001'),
                           (null,'格力空调',5000,'c001'),
                           (null,'九阳榨汁机',1000,'c001');
insert into product values(null,'啄木鸟衬衣',300,'c002'),
                          (null,'恒源祥西裤',300,'c002'),
                          (null,'花公子夹克',440,'c002'),
                          (null,'劲霸休闲裤',266,'c002'),
                          (null,'海澜之家卫衣',180,'c002'),
                          (null,'杰克琼斯运动裤',430,'c002');
insert into product values(null,'兰蔻面霜',300,'c003'),
                          (null,'雅诗兰黛精华水',200,'c003'),
                          (null,'香奈儿香水',350,'c003'),
                          (null,'SK-II神仙水',350,'c003'),
                          (null,'资生堂粉底液',180,'c003');
insert into product values(null,'老北京方便面',56,'c004'),
                          (null,'良品铺子海带丝',17,'c004'),
                          (null,'三只松鼠坚果',88,'c004');

结果如下：

基本查询

查询字段

查询指定字段：select 字段名 from 表名;

代码如下：

select pname from product;

结果如下：

查询多个字段：select 字段名1,字段名2,... from 表名;

代码示例：

select pname,price from product;

结果如下：

查询所有字段：select * from 表名;

代码示例：

select * from product;

结果如下：

别名查询

表别名：select * from 表名 [as] 别名;

代码示例：

select * from product [as] p;

表别名会在多表查询中进行使用

字段别名：select 字段名 [as] 别名,字段名 [as] 别名,... from 表名;

代码示例：

select pname as '商品名称',price as '价格' from product;

结果如下：

去除重复记录

格式：select distinct 字段名 from 表名;

代码示例：

select distinct price from product;

结果如下：

查询结果是表达式（运算查询）

格式：select 字段名1,字段表达式 from 表名;

代码示例：

select pname,price-10 as new_price from product;

条件查询

条件运算符

算术运算符：

运算符	说明
+	加法运算，用于计算两个数值的和。
-	减法运算，用于计算两个数值的差。
*	乘法运算，用于计算两个数值的积。
/ 或 div	除法运算，用于计算两个数值的商。
% 或 mod	取模运算，用于计算两个数值相除的余数。

比较运算符：

运算符	说明
=	判断两个值是否相等，若相等则返回真，否则返回假
<> 或 !=	判断两个值是否不相等，若不相等则返回真，否则返回假
>	判断左边的值是否大于右边的值，若大于则返回真，否则返回假
<	判断左边的值是否小于右边的值，若小于则返回真，否则返回假
>=	判断左边的值是否大于或等于右边的值，若满足条件则返回真，否则返回假
<=	判断左边的值是否小于或等于右边的值，若满足条件则返回真，否则返回假
<=>	安全的等于，两个操作码均为 NULL 时，其所得值为 1；而当一个操作码为 NULL 时，其所得值为 0
BETWEEN ... AND ...	判断一个值是否在指定的两个值之间（包含边界值），若在则返回真，否则返回假
IN	判断一个值是否在给定的集合中，若在则返回真，否则返回假
LIKE	用于模糊匹配字符串，常与通配符 %（匹配任意数量的任意字符）和 _（匹配单个任意字符）一起使用
NOT LIKE	与 LIKE 相反，判断一个字符串是否不匹配指定的模式，若不匹配则返回真，否则返回假
IS NULL	判断一个值是否为 NULL，若是则返回真，否则返回假
IS NOT NULL	判断一个值是否不为 NULL，若不为 NULL 则返回真，否则返回假
LEAST	当有两个或多个参数时，返回最小值
GREATEST	当有两个或多个参数时，返回最大值
NOT IN	判断一个值是否在给定的集合中，若不在则返回真，否则返回假

逻辑运算符：

运算符	说明
NOT 或者 !	逻辑非
AND 或 &&	逻辑与
OR 或 `
XOR	逻辑异或

位移运算符：

运算符	说明
|	按位或
&	按位与
^	按位异或
<<	按位左移
>>	按位右移
~	按位取反，反转所有比特

位运算符是在二进制数上进行计算的运算符。位运算符会先将操作数变为二进制数然后进行位运算，然后再将计算结果从二进制数变回十进制数

条件查询

格式：select 字段名 from 表名 where 条件;

代码示例：

select pname,price from product where category_id='c002'
                                  and (price between 200 and 400)
                                  and (pname like '%裤');

结果如下：

排序查询

格式：select 字段名1,字段名2,... from 表名 order by 字段名1 [asc|desc],字段名2 [asc|desc] ...;

注意事项：

• asc 代表升序，desc 代表降序，如果不写默认升序
• order by 用于子句中可以支持单个字段，多个字段，表达式，函数，别名
• order by 子句，放在查询语句的最后面，limit 子句除外
• 如果是多字段排序，当第一个字段值相同时，才会根据第二个字段进行排序

代码示例：

select * from product order by pid desc ,price asc;

结果如下：

聚合查询

聚合查询是将一列数据作为一个整体，进行纵向计算

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

格式：select 聚合函数 from 表名 [where 条件];

代码示例：

select sum(price) from product where price >2000;

结果如下：

注意事项：null值不参与所有聚合函数运算

对 NULL 值的处理：

1. count 函数对 null 值的处理
   如果count函数的参数为星号（*），则统计所有记录的个数；而如果参数为某字段，不统计含 null 值的记录个数。
2. sum 和 avg 函数对 null 值的处理
   这两个函数忽略 null 值的存在，就好象该条记录不存在一样。
3. max 和 min 函数对 null 值的处理
   max和min两个函数同样忽略 null 值的存在。

分组查询

分组查询是指使用 group by 字句对查询信息进行分组

格式：select 字段名1,字段名2,... from 表名 [where 条件] group by 分组字段名 having 分组条件;

代码示例：

select 
    category_id,count(pid) 
from 
    product 
where 
    price>200 
group by 
    category_id 
having 
    count(pid) > 3;

结果如下：

注意事项：

• where 和 having 区别
  • 执行时机不同：where 是分组之前进行过滤，不满足 where 条件，不参与分组，而 having 是分组之后对结果进行过滤
  • 判断条件不同：where 不能对聚合函数进行判断，而 having 可以
• 执行顺序：where > 聚合函数 > having
• 分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段无任何意义

分页查询

分页查询在项目开发中常见，由于数据量很大，显示屏长度有限，因此对数据需要采取分页显示方式。例如数据共有 30 条，每页显示 5 条，第一页显示 1-5条，第二页显示 6-10 条…

格式：select 字段名 from 表名 limit 起始索引,查询记录数;

代码示例：

select * from product limit 5,5;

结果如下：

注意事项：

• 起始索引从 0 开始，起始索引=（查询页码 - 1）* 每页显示记录数
• 分页查询是数据库的方言，不同的数据库有不同的实现，MySQL 中是 LIMIT
• 如果查询的是第一页数据，起始索引可以省略，直接简写为 limit 10

INSERT INTO SELECT 语句

将一张表的数据导入到另一张表中，可以使用 INSERT INTO SELECT 语句

指定字段导入：

格式：

insert into table2(field1,field2,...) select value1,value2,... from table1;

代码示例：

create table product2(
    pname varchar(20),
    price double
);
insert into product2(pname, price) select pname,price from product;

结果如下：

所有字段导入：

格式：

insert into table2 select * from table1;

代码示例：

create table product2(
    pid int primary key auto_increment,
    pname varchar(20) not null,
    price double,
    category_id varchar(20)
);
insert into product2 select * from product;

结果如下：

正则表达式

概念：

正则表达式（regular expression）描述了一种字符串匹配的规则，其本身就是一个字符串，用于描述、定义匹配规则，以匹配一系列符合某个句法规则的字符串。在开发中，正则表达式通常被用来检索、替换那些符合某个规则的字符串。MySQL 通过 REGEXP 关键字支持正则表达式进行字符串匹配。

格式：

模式	描述
^	匹配输入字符串的开始位置。
$	匹配输入字符串的结束位置。
.	匹配除 \n 之外的任何单个字符。
[ ... ]	字符集合。匹配所包含的任意一个字符。例如，[abc] 可匹配 “plain” 中的 a。
[^ ... ]	负值字符集合。匹配未包含的任意字符。例如，[^abc] 可匹配 “plain” 中的 p。
`p1	p2
*	匹配前面的子表达式零次或多次。例如，zo* 能匹配 “z” 以及 “zoo”。* 等价于 {0,}。
+	匹配前面的子表达式一次或多次。例如，zo+ 能匹配 “zo” 以及 “zoo”，但不能匹配 “z”。+ 等价于 {1,}。
{n}	n 是一个非负整数。匹配确定的 n 次。例如，o{2} 不能匹配 “Bob” 中的 o，但是能匹配 “food” 中的两个 o。
{n,m}	m 和 n 均为非负整数，其中 n <= m。最少匹配 n 次且最多匹配 m 次。

^代码示例：

select * from product where pname regexp '^海';

结果如下：
$代码示例：

select * from product where pname regexp '水$';

结果如下：

.代码示例：

select * from product where price regexp '.00';

结果如下：

[ ... ]代码示例：

select * from product where pname regexp '[机水裤]';

结果如下：

[^...]代码示例：

select * from product where pname regexp '[^海尔洗衣机海澜之家卫衣劲霸休闲裤]';

结果如下：

p1|p2|p3代码示例：

select * from product where pname regexp '机|裤|水';

结果如下：