06_MySQL基本操作-DQL-基本查询

06_MySQL基本操作-DQL-基本查询

• 数据库管理系统的一个重要功能就是数据查询，数据查询不应只是简单返回数据库中存储的数据，还应该根据需要对数据进行筛选以及确定数据以什么样的格式显示。

• MySQL提供了功能强大，灵活的语句来实现这些操作。

• MySQL数据库使用select语句来查询数据。

一、Select语法格式

二、简单查询

1.添加数据

-- 创建数据库
create database if not existS mydb2;
use mydb2;
-- 创建商品表
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,'九阳电饭煲',5000,'c001');
														
insert into product values(null,'啄木鸟衬衣',300,'c002'),
						(null,'恒源祥西裤',800,'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,'资生堂粉底液',430,'c003');
													
insert into product values(null,'老北京方便面',56,'c004'),
						  (null,'良品铺子海带丝',17,'c004'),
						  (null,'三只松鼠坚果',88,null);

2.简单查询

-- 1.查询所有的商品
select * from product;
-- 2.查询商品名和商品价格。
select pname,price from product;
-- 3.别名查询，使用的关键字是as（可以省略的）
-- 3.1表别名
select * from product as p;
-- 3.2 列别名
select pname as pn from product;
-- 4.去掉重复值
select distinct price from product;
-- 5.查询结果是表达式（运算查询）：将所有商品的价格+10元进行显示。
select pname ,price+10 from product;

三、MySQL中的运算符

数据库中的表结构确立后，表中的数据代表的意义就已经确定。通过MySQL运算符进行运算，就可以获取到表结构以外的另一种数据结构。

例如，学生表中存在一个birth字段，这个字段表示学生的出生年份。而运用MySQL的算术运算符用当前的年份减学生出生的年份，那么得到的就是这个学生的实际年龄数据。

MySQL支持4种运算符

• 算术运算符
• 比较运算符
• 逻辑运算符
• 位运算符

1.算术运算符

算术运算符	说明
+	加法运算
-	减法运算
*	乘法运算
/或DIV	除法运算，返回商
%或MOD	求余运算，返回余数

2.比较运算符

比较运算符	说明
=	等于
< 和 <=	小于和小于等于
> 和 >=	大于和大于等于
<=>	安全的等于，两个操作码均为null时，其所得值为1；而当一个操作码为null时。其所得之为0
<>或！=	不等于
IS NULL 或ISNULL	判断一个值是否为NULL
IS NOT NULL	判断一个值是否不为NULL
LEAST	当有两个或多个参数（多个列名）时，返回最小值
GREATEST	当有两个或多个参数（多个列名）时，返回最大值
BETWEEN AND	判断一个值是否落在两个值之间
IN	判断一个值是IN列表中的任意一个值
NOT IN	判断一个值不是IN列表中的任意一个值
LIKE	通配符匹配（下划线匹配单个字符，%匹配多个字符）
REGEXP	正则表达式匹配

3.逻辑运算符

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

4.位运算符（了解）

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

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

四、排序查询

1.介绍

如果我们需要对读取的数据进行排序，我们就可以使用MySQL的order by字句来设定你想按哪个字段哪种方式来进行排序，再返回结果。

select 
	字段名1，字段名2....
from 表名
order by 字段名1 [asc|desc],字段名2[asc|desc].....

2.特点

1.asc代表升序，desc代表降序，如果不写默认升序

2.order by用于字句中可以支持单个字段，多个字段，表达式，函数，别名

3.order by字句，放在查询语句的最后面。LIMIT字句除外。

3.操作

-- 1.使用价格排序（降序）
select * from product order by price desc;
-- 2.在价格排序（降序）的基础上，以分类排序（降序）
select * from product order by price desc,category_id asc;
-- 3.显示商品的价格（去重复），并排序（降序）
select distinct price from product order by price desc;

五、聚合查询

1.简介

​ 之间我们做的查询都是横向查询，它们都是根据条件一行一行的进行判断，而使用聚合函数查询时纵向查询，它是对一列的值进行计算，然后返回一个单一的值；另外聚合函数会忽略空值。

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

2.聚合查询对null值的处理

介绍

1.count函数对null值的处理

​ 如果count函数的参数为星号（*）,则统计所有记录的个数。而如果参数为某字段，不统计含null值的记录个数。

2.sum和avg函数对null值的处理

​ 这两个函数忽略null值的存在，就好像该条记录不存在一样。

3.max和min函数对null值的处理

​ max和min两个函数同样忽略null值的存在

六、分组查询

1.简介

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

​ 格式：

select 字段1，字段2... from 表名 group by 分组字段 having 分组条件;

​ 操作

-- 1.统计各个分类商品的个数
select category_id,count(*) from product group by category_id;

-如果要进行分组的话，则select子句之后，只能出现分组的字段和统计函数，其他的字段不能出现；

2.分组之后的条件筛选-having

• 分组之后对统计结果进行筛选的话必须使用having，不能使用where
• where子句用来筛选from子句中指定的操作所产生的行
• group by 子句用来分组where子句的输出
• having子句用来从分组的结果中筛选行
• having可以使用聚合函数，where不行

格式：

select 字段1，字段2... from 表名 group by 分组字段 having 分组条件;

操作：

-- 2.统计各个分类商品的个数，且只显示个数大于4的信息
select category_id , count(*) from product group by category having count(*) > 4;

七、分页查询

1.简介

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

2.格式

-- 方式1-显示前n条
select 字段1，字段2... from 表名 limit n;
-- 方式2-分页显示
select 字段1，字段2... from 表名 limit m,n;
m: 整数，表示从第几条记录开始，计算方式（当前页-1）*每页显示条数
n: 整数，表示查询多少条数据

3.操作

-- 查询product表的前5条记录
select * from product limit 5;
-- 从第4条开始显示，显示5条
select * from product limit 3,5;

八、INSERT INTO SELECT语句

1.简介

​ 将一张表的数据导入到另一张表中，可以使用insert into select语句。

2.格式

insert into Table2(field1,field2,...) select value1,value2,... from Table1;
或者
insert into Table2 select * from Table1;

要求目标表Table2必须存在

九、正则表达式匹配查询

1.介绍

​ 正则表达式（regular expression）描述了一种字符串匹配的规则，正则表达式本身就是一个字符串，使用这个字符串来描述，用来定义匹配规则，匹配一系列符合某个句法规则的字符串。在开发中，正则表达式通常被用来检索，替换那些符合某个规则的文本。

​ MySQL通过REGEXP关键字支持正则表达式进行字符串匹配。

2.格式

模式	描述
^	匹配输入字符串的开始位置
$	匹配输入字符串的结束位置
.	匹配除“\n”之外的任何单个字符
[…]	字符集合，匹配所包含的任意一个字符。例如’[abc]'可以匹配“plain”中的a.
[^…]	负值字符集合。匹配未包含的任意字符。例如，"[^abc]"可以匹配“plain”中的p
p1|p2|p3	匹配p1或p2或p3，例如，’z|food‘能匹配“z”或“food”。’（z|f）food‘则匹配“zood”或“food”。
*	匹配前面的子表达式零次或多次。例如，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次。

3.实例

-- 正则表达式
-- ^在字符串开始处进行匹配
select 'abc' REGEXP '^b';
select * from product where pname regexp '^海澜';
-- $ 在字符串末尾开始匹配
select 'abc' REGEXP 'a$';
select * from product where pname REGEXP '电饭煲$';
select 'abc' REGEXP 'C$';
-- . 匹配任意字符
select 'abc' REGEXP '.b';
select * from product where pname REGEXP '.电';
-- [...] 匹配括号内的任意单个字符
select 'abc' REGEXP '[xyz]';-- 0
select 'abc' REGEXP '[xaz]';-- 1

-- [^...] 注意^符合只有在[]内才是取反的意思，在别的地方都是表示开始处匹配
select 'a' REGEXP '[^abc]';-- 0
select 'x' REGEXP '[^abc]';-- 1
select 'abc' REGEXP '[^a]';-- 1

-- a* 匹配0个或多个a，包括字符串。可以作为占位符使用。有没有指定字符串都可以匹配到数据。
select 'stab' REGEXP '.ta*b';-- 1
select 'stb' REGEXP '.ta*b';-- 1
select '' REGEXP 'a*';-- 1

-- a+ 匹配1个或者多个a,但是不包括空字符
select 'stab' REGEXP '.ta+b';-- 1
select 'stb' REGEXP '.ta+b';-- 0

-- a? 匹配0个或者1个a
select 'stb' regexp '.ta?b';-- 1
select 'stab' regexp '.ta?b';-- 1
select 'staab' REGEXP '.ta?b';-- 0

-- a1 | a2 匹配a1或者a2
select 'a' REGEXP 'a|b';-- 1
select 'b' regexp 'a|b';-- 1
select 'b' regexp '^(a|b)';-- 1
select 'a' regexp '^(a|b)';-- 1
select 'c' regexp '^(a|b)';-- 0

-- a{m} 匹配m个a
select 'auuuuc' regexp 'au{4}c';-- 1
select 'auuuuc' regexp 'au{3}c';-- 0

-- a{m,} 匹配m个或者更多个a
select 'auuuuc' REGEXP 'au{3,}c';-- 1
select 'auuuuc' regexp 'au{4,}c';-- 1
select 'auuuuc' REGEXP 'au{5,}c';-- 0

-- a{m,n} 匹配m到n个a,包含m和n
select 'auuuuc' regexp 'au{3,5}c';-- 1
select 'auuuuc' regexp 'au{4,5}c';-- 1
select 'auuuuc' REGEXP 'au{5,10}c';-- 0

-- (abc)
-- abc作为一个序列匹配，不用括号括起来都是用单个字符去匹配，如果要把多个字符作为一个整体去匹配就需要用到括号,所以括号适合上面的所有情况
select 'xababy' regexp 'x(abab)y';-- 1
select 'xababy' regexp 'x(ab)*y';-- 1
select 'xababy' regexp 'x(ab){1,2}y';-- 1
select 'xababy' regexp 'x(ab){3}y';-- 0

‘auuuuc’ regexp ‘au{3,5}c’;-- 1
select ‘auuuuc’ regexp ‘au{4,5}c’;-- 1
select ‘auuuuc’ REGEXP ‘au{5,10}c’;-- 0

– (abc)
– abc作为一个序列匹配，不用括号括起来都是用单个字符去匹配，如果要把多个字符作为一个整体去匹配就需要用到括号,所以括号适合上面的所有情况
select ‘xababy’ regexp ‘x(abab)y’;-- 1
select ‘xababy’ regexp ‘x(ab)*y’;-- 1
select ‘xababy’ regexp ‘x(ab){1,2}y’;-- 1
select ‘xababy’ regexp ‘x(ab){3}y’;-- 0