MySQL的高级语句应用（2）

一、正则表达式

1.作用

在查询数据库记录时，根据指定的匹配条件匹配记录中符合要求的特殊字符串

2.类别

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

3.使用语句

使用正则表达式时格式

select column1,column2,... from table_namae where column1,... REGEXP '正则表达式';

列句

select id,name,from info where name REGEXP 'z';  //表中查询包含k字符串的name字段并打印对应的 id、name 

select id,name, from info where name REGEXP 'oooo+';//表中查询包含四个或者更多个o的 name 字段并打印对应的 id、name

注意：“^”不仅可以放到左侧，也可以放到方括号内，
放到左侧表示以这些字符集内的字符开头，而放到方括号内则表示不在指定的字符集合内的字符。

二、运算符

MySQL 的运算符共有四种，分别是：算术运算符、比较运算符、逻辑运算符和位运算符

1、算术运算符

+（加法）、-（减法）、*（乘法）、/（除法）、%（取余）

例如：select 1+2 as a, 2-1 as b, 2*3 as c, 4/2 as d, 7%2 as e;

在除法运算和求余数运算中，除数不能为 0，若除数是 0，返回的结果则为 NULL。需要注意的是，
如果有多个运算符，按照先乘除后加减的优先级进行运算，相同优先级的运算 符没有先后顺序

2、比较运算符

比较运算符是查询数据记录时经常使用的一类运算符。通过使用比较运算符可以判断出
表中有哪些记录是符合条件的，如果比较的结果为真则返回 1，如果为假则返回 0，
比较的结果如果不确定则返回 NULL。其中字符串在进行比较的时候默认是不区分大小写的

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

注意：如果两者都是整数，则按照整数值进行比较。
如果一个整数一个字符串，则会自动将字符串转换为数字（其中字符的比较是根据 ASCII 码来转换），再进行比较。
如果两者都是字符串，则按照字符串进行比较。
如果两者中至少有一个值是 NULL，则比较的结果是 NULL。

‘字符串’与’字符串’比较是或的关系，第一个字符相比 结果出来了后面字符就不进行了
between包含头和尾部

least 最小值 放的中有字符，不能执行，如果其中一个值为 NULL，则返回结果就为 NULL
greatest 最大值 其中值中有字符，会屏蔽其中的字符，如果其中一个值为 NULL，则返回结果就为 NULL

3.逻辑运算符
逻辑运算符又被称为布尔运算符，通常用来判断表达式的真假，如果为真返回 1，否则返回 0，真和假也可以用 TRUE 和 FALSE 表示。MySQL 中支持使用的逻辑运算符有四种

3.1 类别

运算符	含义
NOT或！	逻辑非
AND或&&	逻辑与
OR或||	逻辑或
XOR	逻辑异或

3.2运算规则

且 0&&0=0 1&&0=0 0&&1=0 1&&1=1 0&&null=0 1&&null=null
或 0||0=0 1||0=1 0||1=1 1||1=1 0 or null=null 1or null=1
异或 0^0=0 1^0=1 0^1=1 1^1=0 0 xor null=null , 1 xor null=null

注意：或运算时不用 || ，用or

4.位运算符
位运算符实际上是对二进制数进行计算的运算符。MySQL 内位运算会先将操作数变成二进制格式，然后进行位运算，最后在将计算结果从二进制变回到十进制格式，方便用户查看。MySQL 支持 6 种位运算符

4.1 类别

运算符	含义
&	按位与
|	按位或
~	按位取反
^	按位异或
<<	按位左移
>>	按位右移

4.2例句

select 5 &~1; //按位取反（~），是对应的二进制数逐位反转，即 1 取反后变为 0, 0 取反后变为 1。数字 1 的二进制是 0001，取反后变为 1110, 数字 5 的二进制是 0101，将 1110 和 0101进行求与操作，其结果是二进制的 0100，转换为十进制就是 4

“15>>2”将数字 15 转换为二进制是 1111，向右移动两位，右侧的两位 11 被丢弃，变为 11， 左侧用 00 补齐，最终变为二进制的 0011，转换为十进制就是 3。

注意：不管哪种运算符，在使用过程中都有优先级问题。运算符的优先级决定了不同的运 算符在计算过程中的先后顺序。级别高的运算符会先进行计算，如果运算符的级别相同， MySQL 会按照顺序从左到右依次进行计算。
“!”的优先级最高，而“:=”的优先级最低。

运算符优先级

优先级	运算符	优先级	运算符
1	！	8	|
2	~	9	=,<=>,>=,<=，<,<>,!=，IS,LIKE,REGEXP,IN
3	^	10	BETWEEN,CASE,WHEN,THEN,ELSE
4	*,/(DIV),%(MOD)	11	NOT
5	+,-	12	&&,AND
6	>>,<<	13	||,OR,XOR
7	&	14	：=

三、连接查询

1，概念

MySQL 的连接查询，通常都是将来自两个或多个表的行结合起来，基于这些表之间的共同字段，进行数据的拼接。

2.分类

内连接、左连接、右连接

3.语法

3.1.内连接

通常在FROM 子句中使用关键字 INNER JOIN 来连接多张表，
并使用 ON 子句设置连接条件。

SELECT column_name(s)FROM table1 INNER JOIN table2 ON table1.column_name = table2.column_name;

注意：建议最好不要超过三个表

3.2 、左连接

左连接也可以被称为左外连接，在 FROM 子句中使用 LEFT JOIN，左连接以左侧表为基础表，接收左表的所有行，并用这些行与右侧参考表中的记录进行匹配，也就是说匹配左表中的所有行以及右表中符合条件的行

SELECT column_name(s)FROM table1 LEFT JOIN table2 ON table1.column_name = table2.column_name;

3.3右连接

右连接也被称为右外连接，在 FROM 子句中使用 RIGHT JOIN 。右连接跟左连接正好相反，它是以右表为基础表，用于接收右表中的所有行，并用这些记录与左表中的行进行匹配。也就是说匹配右表中的每一行及左表中符合条件的记录

SELECT column_name(s)FROM table1 RIGHT JOIN table2 ON table1.column_name = table2.column_name;

四 数据库函数

1.分类

数学函数、聚合函数、字符串函数和日期时间函数。

2.数学函数

数学函数	含义
abs(x)	返回 x 的绝对值
rand()	返回 0 到 1 的随机数
mod(x,y)	返回 x 除以 y 以后的余数
power(x,y)	返回 x 的 y 次方
round(x)	返回离 x 最近的整数
round(x,y)	保留x 的y 位小数四舍五入后的值
sqrt(x)	返回 x 的平方根
truncate(x,y)	返回数字 x 截断为 y 位小数的值
ceil(x)	返回大于或等于 x 的最小整数
floor(x)	返回小于或等于 x 的最大整数
greatest(x1,x2…)	返回集合中最大的值
least(x1,x2…)	返回集合中最小的值

注意;rand()取值能取到0，取不到1，

3.聚合函数

聚合函数	含义
avg()	返回指定列的平均值
count()	返回指定列中非 NULL 值的个数
min()	返回指定列的最小值
max()	返回指定列的最大值
sum()	返回指定列的所有值之和

注意：count（）可以放数值，但只能一个数

4.字符串函数

字符串函数	含义
length(x)	返回字符串 x 的长度
trim()	返回去除指定格式的值
concat(x,y)	将提供的参数 x 和 y 拼接成一个字符串
upper(x)	将字符串 x 的所有字母变成大写字母
lower(x)	将字符串 x 的所有字母变成小写字母
left(x,y)	返回字符串 x 的前 y 个字符
right(x,y)	返回字符串 x 的后 y 个字符
repeat(x,y)	将字符串 x 重复 y 次
space(x)	返回 x 个空格
repiace(x,y,z)	将字符串 z 替代字符串 x 中的字符串 y
strcmp(x,y)	比较 x 和 y，返回的值可以为-1,0,1
substring(x,y,z)	获取从字符串 x 中的第 y 个位置开始长度为 z 的字符串
reverse(x)	将字符串 x 反转

注意;length（‘ bcd’）中空格算一个长度
length（null）长度是null
trim（）去头尾的空格，空格在中间的不去 ，
left（x,y）,right(x,y)中x是字符串，y是个数
replace（x,y,z）=(‘abcefg’,‘bc’,‘23’) 结果是a23efg
substring(‘abcdefg’,3,3)结果是cde

5.日期时间函数

时间日期函数	含义
curdate()	返回当前时间的年月日
curtime()	返回当前时间的时分秒
now()	返回当前时间的日期和时间
month(x)	返回日期 x 中的月份值
week(x)	返回日期 x 是年度第几个星期
hour(x)	返回 x 中的小时值
minute(x)	返回 x 中的分钟值
second(x)	返回 x 中的秒钟值
dayofweek(x)	返回 x 是星期几，1 星期日，2 星期一
dayofmonth(x)	计算日期 x 是本月的第几天
dayofyear(x)	计算日期 x 是本年的第几天

五、存储过程

1.简介

MySQL 数据库存储过程是一组为了完成特定功能的 SQL 语句的集合。存储过程这个功能是从 5.0 版本才开始支持的，它可以加快数据库的处理速度，
增强数据库在实际应用中的灵活性。存储过程在使用过程中是将常用或者复杂的工作预先使用 SQL 语句写好并用一个指定的名称存储起来，
这个过程经编译和优化后存储在数据库服务器中。当需要使用该存 储过程时，只需要调用它即可。操作数据库的传统 SQL 语句在执行时需要先编译，
然后再去执行，跟存储过程一对比，明显存储过程在执行上速度更快，效率更高。

2.优点

存储过程执行一次后，生成的二进制代码就驻留在缓冲区，之后如果再次调用的话，将 直接调用二进制代码，使得存储过程的执行效率和性能得到大幅提升。
存储过程是 SQL 语句加上控制语句的集合，有很强的灵活性，可以完成复杂的运算。
存储过程存储在服务器端，客户端调用时，直接在服务器端执行，客户端只是传输的调 用语句，从而可以降低网络负载。
存储过程被创建后，可以多次重复调用，它将多条 SQL 封装到了一起，可随时针对 SQL
语句进行修改，不影响调用它的客户端。
存储过程可以完成所有的数据库操作，也可以通过编程的方式控制数据库的信息访问权 限。

3.创建存储过程

CREATE PROCEDURE <过程名> ( [过程参数[,…] ] ) <过程体>[过程参数[,…] ] 格式
[ IN | OUT | INOUT ] <参数名><类型>

注意：存储过程的名称应该尽量避免选取与 MySQL 内置的函数或者字段相同的名称，否则会发生错误

4.修改存储过程

ALTER PROCEDURE <过程名> [ <特征> … ]

5.删除存储过程

DROP { PROCEDURE | FUNCTION } [ IF EXISTS ] <过程名>

注意：存储过程名称后面没有参数列表，也没有括号。在删除之前，必须确认 该存储过程没有任何依赖关系，否则会导致与之关联的存储过程无法运行。