高阶SQL语句2（正则表达式，运算符，比较运算，逻辑运算符，位运算，连接查询）

正则表达式

MySQL 正则表达式通常是在检索数据库记录的时候，根据指定的匹配模式匹配记录中符合要求的特殊字符串。

MySQL 的正则表达式使用 regexp 这个关键字来指定正则表达式的匹配模式

regexp 操作符所支持的匹配模式如下：

匹配模式			描述									实例
^ 				匹配文本的开始字符 						‘^bd’ 匹配以 bd 开头的字符串
$ 				匹配文本的结束字符 						‘qn$’ 匹配以 qn 结尾的字符串
. 				匹配任何单个字符							‘s.t’ 匹配任何 s 和 t 之间有一个字符的字符串
* 				匹配零个或多个在它前面的字符 				‘fo*t’ 匹配 t 前面有任意个 o
+ 				匹配前面的字符 1 次或多次					‘hom+’ 匹配以 ho 开头，后面至少一个m 的字符串
字符串 			匹配包含指定的字符串 						‘clo’ 匹配含有 clo 的字符串
p1|p2 			匹配 p1 或 p2 							‘bg|fg’ 匹配 bg 或者 fg
[...] 			匹配字符集合中的任意一个字符 				‘[abc]’ 匹配 a 或者 b 或者 c
[^...] 			匹配不在括号中的任何字符 					‘[^ab]’ 匹配不包含 a 或者 b 的字符串
{n} 			匹配前面的字符串 n 次 					‘g{2}’ 匹配含有 2 个 g 的字符串
{n,m}			匹配前面的字符串至少 n 次，至多m 次		‘f{1,3}’ 匹配 f 最少 1 次，最多 3 次

PS：^表示匹配开始字符，但需要看 ' ^ ' 所处的位置，例如：
[ ^ ]表示不包含
^[]表示以……开头

1、 以" . "代替字符串中的任意一个字符的记录

select * from info where address regexp '.京';

2、 匹配前面字符的任意多次

select * from info where name regexp '赵*';

3、 匹配前面字符至少一次

select * from info where score regexp '9+';

4、 匹配字符串

select * from info where score regexp '80';

5、 匹配包含或者关系的记录

select * from info where name regexp '吴|钱';

6、 匹配指定字符集中的任意一个

select * from info where score regexp '[8,9]';

7、 匹配不在括号里的任意字符

select * from info where score regexp '[^9]'

8、 匹配前面的字符串 n 次

select * from info where score regexp '0{2}';

9、 匹配前面的字符串至少 n 次，至多 m 次

select * from info where score where score regexp '0{1,2}';

运算符

MySQL 的运算用于对记录中的字段值进行计算。
MySQL 的运算符共有四种，分别是：算术运算符、比较运算符、逻辑运算符和位运算符

算术运算

运算符	描述
+	加法
-	减法
*	乘法
/	除法
%	取余

在除法运算和求余数运算中，除数不能位0，若除数是 0 ，返回的结果则为 NULL。

PS：如果有多个运算符，按照先乘除后加减的优先级进行运算，相同优先级的运算符没有先后顺序

select 1+2,2-1,3*3,7/3,6%3,9/3;

create table zzz select 1+2,2-1,3*3,7/3,6%3,9/3;
desc zzz;

比较运算符

字符串的比较默认不区分大小写，可使用binary来区分

常用比较运算符（比较对象：数字，字符）

=	等于
!= 或<>	不等于
like	通配符匹配
>	大于
>=	大于等于
<	小于
<=	小于等于
is null	判断一个值是否为null
is not null	判断一个值是否不为null
between and	两者之间
greatest	两个或多个参数时返回最大值
least	两个或多个参数时返回最小值
in	在集合中

等于 （=）

用来判断数字、字符串和表达式是否相等的，如果相等则返回1，如果不相等则返回0。如果比较的两者有一个值是null，则比较的结果就是null

PS：其中字符的比较是根据ASCII码来判断的，如果ASCII码相等，则表示两个字符相同；如果ASCII不相等，则表示两个字符不相同。

例如字符串（字母）比较：其实比较的是底层ASCII码

需要关注的ascii码有a（97）、A（65）、0（48）

select 2=4,2=2,2='2','a'='a',(2+3)=(4+1),4=null;

结论：

如果两者都是整数，则按照整数值进行比较
如果一个整数一个字符串，则会自动将字符串转换为数字，再进行比较。（在程序中，开发人员是不会把这两者放在一起做对比）
如果两者都是字符串，则按照字符串进行比较
如果两者中至少有一个值是 null ，则比较的结果是 null 。

不等于（!=或<>）

用于针对数字、字符串和表达式不相等的比较，如果不相等则返回1，如果相等则返回0，与等于（=）的返回值相反，同时不等于无法用于判断null

select 'a'!='a','a'<>'b','a'!=null;

大于，小于

大于(>） 运算符用来判断左侧的操作数是否大于右侧的操作数，若大于返回1，否则返回 0，同样不能用于判断NULL。

小于(<） 运算符用来判断左侧的操作数是否小于右侧的操作数，若小于返回 1，否则返回0，同样不能用于判断NULL。

大于等于(>=) 判断左侧的操作数是否大于等于右侧的操作数，若大于等于返回1，否则返回o，不能用于判断NULLo

小于等于(<=） 判断左侧的操作数是否小于等于右侧的操作数，若小于等于返回1，否则返回o，不能用于判断 NULL。

select 5>4,'a'>'b',2>=3,(2+3)>=(1+4),4.4<3,1<3,'b'<='h',5<=5.5,'u'>=null;

两者之间（between……and……）

比较运算通常用于判断一个值是否落在某两个值之间。
例如，判断某数字是否在另外两个数字之间，也可以判断某英文字母是否在另外两个字母之间，具体操作，条件符合返回1，否则返回0

select 4 between 2 and 6;
select 2 between 2 and 6;

当有两个或多个参数时，返回其中最大/最小值，如果一个为null，则返回null（least greatest）

select least (1,2,3),least('a','b','c'),greatest(1,2,3),greatest('a','b','c');

在/不在集合中（in ，not in ）

in判断一个值是否在对应的列表中，如果是返回 1 ，否则返回0
not in 则相反

select 2 in (1,2,3,4,5),'b' not in ('a','b','c');

通配符匹配

like 用来匹配字符串，如果匹配成功则返回1，反之返回0.like 支持两种通配符：_ %

select 'qian' like 'qia_','yi'  not like '%i','ting' like '%g';

逻辑运算符

逻辑运算符又被称为布尔运算符，通常用来判断表达式的真假，如果为真返回 1 ，否则返回 0，真和假也可以用 true 和 false 表示。

MySQL中支持使用的逻辑运算符

not 或 ！	逻辑非
and 或 &&	逻辑与
or	逻辑或
xor	逻辑异或

逻辑非（not 或 ！）

逻辑非将跟在他后面的值取反，如果 not 后面的操作数为0时，所得值为1；如果操作数为非 0 时，所得值为0；如果操作数为null，所得值为null

优先级问题：
！1+1=1而not 1+1=0

逻辑与（and 或 &&）

当所有操作数都为非零值并且不为null时，返回值为1；
当一个或多个操作数为 0 时，返回值为0
操作数中有任何一个null时，返回值为null

select 2 and 3,4 && 0,0 && null, 1 and null;

总结：

逻辑或（or）

具体的语法规则为
1、当两个操作数都为非 null 值时，如果有任意一个操作数为非零值，则返回1，否则结果为0
2、当有一个操作数为 null 时，如果另一个操作数为非零值，则返回值为1，否则结果为 null
3、假如两个操作数均为 null 时，则返回值为null

select 2 or 3,0 or 1, 1 or null ,0 or null;

总结：

逻辑异或

具体语法规则为：
当任意一个操作数为 null 时，返回值为 null
对于非null 的操作数，如果两个操作数都是非 0 值或者都是0 值。则返回0
如果一个为0值，另一个为非0值，返回值为1

select 2 xor 3, 0 xor 1,0 xor 0,1 xor null,0 xor null;

总结：

位运算符

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

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

按位与运算（&）
是对应的二进制位都是 1 的，它们的运算结果为 1，否则为 0 ，所以 10 & 15 的结果为10

按位或运算（|）
是对应的二进制有一个或两个位1的，运算结果为1，否则为0，所以10 | 15 的结果就是 15

按位异或运算（^）
是对应的二进制位不同时，运算结果为 1 ，否则为 0 ，所以 10 ^ 15 的结果为 5 .

按位取反（~）
是对应的二进制数逐位反转，即 1 取反后变成 0 ， 0 取反后变成 1 .数字 1 的二进制是0001，取反后变为1110，5的二进制是0101，

连接查询

MySQL 的连接查询，通常都是将来自两个或多个表的行结合起来，基于这些表之间的共同字段，进行数据的拼接。首先，要确定一个主表作为结果集，然后将其他表的行有选择性的连接到选定的主表结果集上。使用较多的连接查询包括
内连接、左连接、右连接

准备表

CREATE TABLE test1 (
a_id int(11) DEFAULT NULL,
a_name varchar(32) DEFAULT NULL,
a_level int(11) DEFAULT NULL);
)

CREATE TABLE test2 (
b_id int(11) DEFAULT NULL,
b_name varchar(32) DEFAULT NULL,
b_level int(11) DEFAULT NULL);


insert into test1(a_id, a_name, a_level) values(1, 'aaaa', 10); 
insert into test1(a_id, a_name, a_level) values(2, 'bbbb', 20); 
insert into test1(a_id, a_name, a_level) values(3, 'cccc', 30); 
insert into test1(a_id, a_name, a_level) values(4, 'dddd', 40);

insert into test2(b_id, b_name, b_level) values(2, 'bbbb', 20); 
insert into test2(b_id, b_name, b_level) values(3, 'cccc', 30); 
insert into test2(b_id, b_name, b_level) values(5, 'eeee', 50); 
insert into test2(b_id, b_name, b_level) values(6, 'ffff', 60);

内连接

MySQL 中的内连接就是两张或多张表中同时符合某种条件的数据记录的组合。通常在 from 子句中使用关键字 inner join 来连接多张表，并使用 on 子句设置连接条件，内连接是系统默认的表连接，所以在 from 子句后可以省略 inner 关键字，只使用 关键字 join 。同时有多个表时，也可以联系使用 inner join 来实现多表的内连接，不过为了更好的性能，建议最好不要超过三个表

select a.a_id,a.a_name,a.a_level from test1 a inner join test2 b on a.a_id = b.b_id;

左连接

左连接也可以被称为左外连接，在 from 子句中使用 left join 或者 outer join 关键字来表示。左连接以左侧表为基础表，接收左表的所有行，并用这些行与右侧参考表中的记录进行匹配，也就是说匹配左表中的所有行以及右表中符合条件的行

select * from test1 a left join test2 b on a.a_name = b.b_name;

不匹配的行会以null来表示

右连接

select * from test1 a right join test2 b on a.a_name = b.b_name;