文章目录
一、MySQL进阶查询
在 MySQL 中,可以使用 select 语句来查询数据。查询数据是指从数据库中根据需求,使用不同的查询方式来获取不同的数据,是使用频率最高、最重要的操作。
select * from 表名;
select < 列名 > from < 表名 >;
是查询数据的一般用法,比较简单,今天给大家带来查询语句的高级语法。
1.1、按关键字排序(降序升序)
- 使用order by语句来实现排序
- 排序可针对一个或多个字段
- ASC:升序,默认排序方式
- DESC:降序
- order by的语法结构
select 字段1,字段2 from 表名 order by 字段1 desc|asc,字段2 desc|asc;
1.1.1 按单字段排序
排序前
降序
升序
1.1.2 按多字段排序
主要参考字段写在前面,辅助参考字段写在后面。先比较主要参考字段,如果相同,再比较辅助参考字段。
1.2、对结果进行分组
1、使用group by 语句来实现分组
2、通常结合聚合函数一起使用
3、可以按一个或多个字段对结果进行分组
4、group by的语法结构
增加按分数降序
1.3、限制结果条目
1、只返回select查询结果的第一行或前几行
2、使用limit语句限制条目
3、limit语法结构
select 字段1,字段2 from 表名 limit [offset,] number;
# offset:位置偏移量,从0开始
# number:返回记录行的最大数目
1.4、设置别名
1、使用as语句设置别名,关键字as可省略
2、设置别名时,保证不能与库中其他表或字段名称冲突
3、别名的语法结构
字段别名:
select 字段 as 别名 from 表名;
表的别名:
select 字段 from 表名 as 别名;
两张表相连查询
1.5、通配符
1、用于替换字符串中的部分字符
2、通常配合like一起使用,并协同where完成查询
3、常用通配符
%表示零个、一个或多个即任意字符
_表示单个字符
1.6、子查询
1、也称作内查询或者嵌套查询
2、先于主查询被执行,其结果将作为外层主查询的条件
3、在增删改查中都可以使用子查询
4、支持多层嵌套
5、in语句用来判断某个值是否在给定的结果集中
多层嵌套
select、update、delete都支持多层嵌套
exists的用法
1.7、NULL值
null:真空(什么都没有)
‘’:空气(还有空气)
1、表示缺失的值
2、与数字0或者空白(spaces)是不同的
3、使用is null或is not null进行判断
4、null值和空值(’’)的区别
空值长度为0,不占空间;null值的长度为null,占用空间
is null无法判断空值
空值使用“=”或者“<>”来处理
count()计算时,null会忽略,空值会加入计算
1.8、正则表达式
1、根据指定的匹配模式匹配记录中符合要求的特殊字符
2、使用regexp关键字指定匹配模式
3、常用匹配模式
^ 匹配文本的开始字符
$ 匹配文本的结束字符
. 匹配任何单个字符
1. 匹配前面的字符零次或多次
2. 匹配前面的字符一次或多次
字符串 匹配包含指定的字符串
p1lp2 匹配p1或p2
[…] 匹配字符集合中任一字符
[^…] 匹配不在括号中的任一字符
{n} 匹配前面的字符串n次
{n,m} 匹配前面的字符串至少n次,之多m次
- 以特定字符串开头的记录
mysql> select * from aa where name regexp ‘^z’;
- 以特定字符串结尾的记录
mysql> select * from aa where name regexp ‘i$’;
- 包含指定字符串的记录
- 以“.”代替字符串中的任意一个字符的记录
- 匹配包含或者关系的记录
- “*”匹配前面字符的任意多次
- “+”匹配前面字符至少一次
mysql> select * from aa where name regexp ‘z+’;
- 匹配指定字符集中的任意一个
1.9、运算符
用于对记录中的字段值进行运算
MySQL 的运算符共有四种,分别是:算术运算符、比较运算符、逻辑运算符和位运算符
1.9.1 算数运算符
运算符 | 描述 |
---|---|
+ | 加法 |
- | 减法 |
* | 乘法 |
/ | 除法 |
% | 取余数 |
以select命令来实现最基础的加减乘除运算
整数相除,出来的结果是浮点型的。
在除法运算和求余数运算中,除数不能为 0,若除数是 0,返回的结果则为 NULL。
需要注意的是,如果有多个运算符,按照先乘除后加减的优先级进行运算。
注意:某些字符串类型的字段存储的数字型字符串,这些字段在进行算术运算时将会被自动转换为数字的值。如果字符串的开始部分是数字,在转换时将被转换为这个数字。如果是既包含字符又包含数字得的混合字符串,无法转换为数字时,将被转换为 0。
1.9.2 比较运算符
运算符 | 描述 |
---|---|
= | 等于 |
> | 大于 |
< | 小于 |
>= | 大于等于 |
<= | 小于等于 |
!=或<> | 不等于 |
is null | 判断一个值是否为NULL |
is not null | 判断一个值是否不为NULL |
between and | 两者之间 |
in | 在集合中 |
like | 通配符匹配 |
greatest | 两个或多个参数时返回最大值 |
least | 两个或多个参数时返回最小值 |
regext | 正则表达式 |
1.9.3 等于运算符
用来判断数字、字符串和表达式是否相等的,如果相等则返回 1,如果不相等则返回 0。其中字符的比较是根据 ASCII 码来判断的。
ASCII码表转换:
0-48,1-49,…9-57
A-65,B-66
a-97,b-98
比较规则:
如果两者都是整数,则按照整数值进行比较。
如果一个整数一个字符串,则会自动将字符串转换为数字,再进行比较。
如果两者都是字符串,则按照字符串进行比较。
如果两者中至少有一个值是 NULL,则比较的结果是 NULL。
1.9.4 不等于运算符
不等于号有两种写法,分别是<>或者!=,用于针对数字、字符串和表达式不相等的比较。如果不相等则返回 1,如果相等则返回 0。
需要注意的是不等于运算符不能用于判断 NULL。
1.9.5 大于、大于等于、小于、小于等于运算符
注意:都不能用于判断NULL。
between and
用于判断一个值是否落在某两个值之间
例如,判断某数字是否在另外两个数字之间,也可以判断某英文字母是否在另外两个字母之间
least、greatest
LEAST:当有两个或者多个参数时,返回其中的最小值。如果其中一个值为 NULL,则返回结果就为 NULL。
GREATEST:当有两个或者多个参数时,返回其中的最大值。如果其中一个值为 NULL, 则返回结果就为 NULL。
in、not in
IN 判断一个值是否在对应的列表中,如果是返回 1,否则返回 0。
NOT IN 判断一个值是否不在对应的列表中,如果不是返回 1,否则返回 0。
like、not like
LIKE 用来匹配字符串,如果匹配成功则返回 1,反之返回 0;NOT LIKE 正好跟 LIKE 相反。
LIKE 支持两种通配符:’%’ 用于匹配任意数目的字符,而’_’只能匹配一个字符。
1.9.6 逻辑运算符
又被称为布尔运算符
用来判断表达式的真假
运算符 | 描述 |
---|---|
NOT或! | 逻辑非 |
AND或&& | 逻辑与 |
OR或 | |
XOR | 逻辑异或 |
逻辑非
逻辑非将跟在它后面的逻辑测试取反,把真变为假,把假变为真。
如果 NOT 后面的操作数为 0 时,所得值为 1;如果操作数为非 0 时,所得值为 0;如果操作数为 NULL 时,所得值为 NULL。
注意:非0值都是1
逻辑与
如果所有值都是真返回 1,否则返回 0。
逻辑或(最好用or)
逻辑或表示包含的操作数,任意一个为非零值并且不是 NULL 值时,返回 1,否则返回0。
逻辑异或
两个非 NULL 值的操作数,如果两者都是 0 或者都是非 0,则返回 0;如果一个为 0, 另一个为非 0,则返回结果为 1;
当任意一个值为 NULL 时,返回值为 NULL。
运算总结:
and运算,只要碰到0就是0,(非0和null是null)
or运算,只要碰到非0值就是1,(0和null是null)
异或运算,只要碰到null都是null
1.9.7 位运算符
位运算符实际上是对二进制数进行计算的运算符。
位运算方法:
按位与运算
10 | 1010 | / |
---|---|---|
15 | 1111 | |
& | 1010 | 10 |
按位与运算(&),是对应的二进制位都是 1 的,它们的运算结果为 1,否则为 0。
按位或运算
10 | 1010 | / |
---|---|---|
15 | 1111 | |
l | 1111 | 15 |
按位或运算(|),是对应的二进制位只要是 1 的,它们的运算结果就为 1,否则为 0。
按位取反运算
15 | 1111 | / |
---|---|---|
10 | 1010 | |
& | 1010 | 10 |
~ | 0101 | 5 |
按位取反(~),是对应的二进制数逐位反转,即 1 取反后变为 0, 0 取反后变为 1。
按位左移运算
1 | 0001/ |
---|---|
1<<2 | 0100 |
按位右移运算
10 | 0001/ | / |
---|---|---|
10>>2 | 0010 | 2 |
按位右移2位,多余的位数直接删除
| 10 |0001/ | / |
按位左移3位,空缺处补0
常用的运算符优先级
优先级 | 运算符 |
---|---|
1 | ! |
2 | ~ |
3 | ^ |
4 | *,/(DIV),%(MOD) |
5 | +,- |
6 | >>,<< |
7 | & |
8 | l |
9 | =,<=>,>=,>,<,<>,!=,IS,LIKE,REGEXP,IN |
10 | BETWEEN,CASE,WHEN,THEN,ELSE |
11 | NOT |
12 | &&,AND |
13 | II,OR,XOR |
14 | := |
1.10、连接查询
通常都是将来自两个或多个表的行结合起来,基于这些表之间的共同字段,进行数据的拼接。
要先确定一个主表作为结果集,然后将其他表的行有选择性的连接到选定的主表结果集上。
使用较多的连接查询包括:内连接、左连接和右连接
内连接
在from子句中使用关键字 inner join 来连接多张表,并使用 on子句设置连接条件。
内连接是系统默认的表连接,所以在 FROM 子句后可以省略 INNER 关键字,只使用关键字 JOIN。同时有多个表时,也可以连续使用 INNER JOIN 来实现多表的内连接,不过为了更好的性能,建议最好不要超过三个表。
外连接
左连接,主表在左边,主表内容会全部显示出来,在从表中没匹配到的以NULL显示出来
右连接,主表在右边,主表内容会全部显示出来,在从表中没匹配到的以NULL显示出来