Mysql高阶语句

一，正则表达式

• Mysql正则表达式通常是在检索数据库记录的时候，根据指定的匹配模式匹配记录中符合要求的特殊字符串
• Mysql的正则表达式使用的PEGEXP这个关键字来指定正则表达式的匹配模式
• REGEXP操作符所支持的匹配模式如下
  匹配模式描述^匹配文本的开始字符$匹配文本的结束字符.匹配如何单个字符*匹配零个或多个在他前面的字符+匹配前面的字符一次或多次字符串匹配包含指定的字符串p1|p2匹配p1或p2[...]匹配字符集合中的任意一个字符[^...]匹配不在括号中的如何字符{n}匹配前的字符串n次{n,m}匹配前面的字符串至少n次，至多m次

  1，以“.”代替任意一个字符

2，匹配前面字符多次

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

4,匹配字符串

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

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

二，运算符

• Mysql的运算符用于对记录中的字段进行运算
• MysQL的运算符共有四种，分别是：算术运算符、比较运算符、逻辑运算符

1、算数运算

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

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

2、比较运算符

字符串的比较默认不区分大小写，可使用 binary 来区分
常用比较运算符说明=等于!=或<>不等于>大于>=大于等于<小于<=小于等于is null判断是否为nullis not null判断是否不为nullbetween and两者之间greatest两个或多个参数时返回最大值least两个或多个参数时返回最小值in在集合中

1，等于

• 等号(=)用来判断数字、字符串和表达式是否相等的，如果相等则返回1，如果不相等则返回0。
• ** 如果比较的两者有一个值是NULL，则比较的结果就是NULL。**
• ** 其中字符的比较是根据ASCII码来判断的，如果ASCIl码相等，则表示两个字符相同;如果ASCII码不相等，则表示两个字符不相同。例如字符串(字母)比较： (‘a’ > ‘b’)其实比较的是底层ASCll码需要关注的ASCII码有：a、A、0**

• ** 如果两者都是整数，则按照整数值进行比较。**
• ** 如果一个整数一个字符串，则会自动将字符串转换为数字，再进行比较。**
• ** 如果两者都是字符串，则按照字符串进行比较。**
• ** 如果两者中至少有一个值是NULL，则比较的结果是NULL**

2，不等于

• 用于针对数字、字符串和表达式不相等的比较，如果不相等则返回1，如果相等则返回0
• 不等于(!=,<>)无法用于判断是否为null

3，大于，大于等于，小于，小于等于

• 大于(>)运算符用来判断左侧的操作数是否大于右侧的操作数，若大于返回1，否则返回0，同样不能用于判断NULL
• ** 小于(<)运算符用来判断左侧的操作数是否小于右侧的操作数，若小于返回1，否则返回0，同样不能用于判断NULL**
• ** 大于等于(>=)判断左侧的操作数是否大于等于右侧的操作数，若大于等于返回1，否则返回0，不能用于判断NULL**
• ** 小于等于(<=)判断左侧的操作数是否小于等于右侧的操作数，若小于等于返回1，否则返回0，不能用于判断NULL**

3，逻辑运算符

• 逻辑运算符又被称为布尔运算符，通常用来判断表达式的真假，如果为真返回1，否则返回0，真和假也可以用TRUE和FALSE表示。
• Mysql中支持使用的逻辑运算符有四种
• • not 或 ! ：逻辑非
• • and 或 && ：逻辑与
• • or ：逻辑或
• • xor ：逻辑异或

1，逻辑非（not或！）

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

2，逻辑与（and 或 &&）

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

3，逻辑或（or）

• 当两个操作数都为非NULL值时，如果有任意一个操作数为非零值，则返回值为1，否则结果为0
• 当有一个操作数为NULL时，如果另一个操作数为非零值，则返回值为1，否则结果为NULL
• 假如两个操作数均为NULL时,则返回值为NULL

三，连接查询

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

1，内连接

Mysql中的内连接就是两种或多张表中同时符合某种条件的数据记录的组合。

通常在from子句中使用关键字inner join 来连接多张表，并使用on子句设置连接条件

内连接是系统默认的表连接，所以在from子句后可以省略inner关键字，只使用关键字join，同时有多个表时，也可以连续使用inner join 来实现多表的内连接，不过为了更好的性能，建议最好不要超过三个表

select 字段 from  表1 inner join 表2 on 表1.字段=表2.字段

2，左连接

左连接也可以称为左外连接在from子句中使用left join 或者 left outer join 关键字来表示。

左连接以左侧表为基础表，接收左表的所以表，并用这些行与右侧参考中的记录进行匹配，也就是说匹配左表的所有行以及右表中符合条件的行。

3，右连接

右连接也称为右外连接，在from子句中使用right join 或者 right outer join 关键字来表示。右连接跟左连接正好相反，他是以右连接为基础表，用于接收右表所有行，并用这些记录与左表中的进行匹配

mysql> select * from nba05 right join ky21 on nba05.nba=ky21.nba;

右表全部的内容显示，左表只显示相同字段的记录，左表不足的地方以null显示

四，数据库函数

1.1数字函数

数据库内存存储的记录，经常要进行一系列的算数操作，所以mysql支持很多数学函数。

常用的数学函数：
数学函数描述ads(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....)least(x1,x2....)
示例

-2的绝对值
select abs(-2);
 
0-1的随机数(0<-x<1) 
select rand();
 
可以搭配运算符
select rand() *100;
 
除以2的余数
select mod(5,2);
 
2的3次方
select power(2,3);
 
离1.89最近的整数
select round(1.49);
select round(1.5);
 
1.893保留小数点后2位，1.896保留小数点后2位，这里会四舍五入
select round(1.893,2);
select round(1.896,2);
 
返回平方根
select sqrt(4);
select sqrt (5);
 
保留小数点后2位，但truncate函数不会四舍五入(截断)
select truncate(1.896,2);
 
返回大于或等于5.2的最小整数
select ceil(5.2);
 
返回小于或等于5.2的最大整数
select floor(5.2);
 
返回最大值
select greatest(1,2,3);
 
返回最小值.
select least (1,2,3)

1.2 聚合函数

Mysql数据库函数中专门有一组函数是特意位库内记录求和或者对表中的数据进行几种概况而设计的，这些函数被称为聚合函数
聚合函数描述avg ()返回指定列的平均值count()返回指定列中非NULL值的个数min()返回指定列的最小值max ()返回指定列的最大值sum(x)返回指定列的所有值之和

 select sum(age) from ky21;

select count(age) from ky21;
返回ky21表中age的个数

 select min(age) from ky21;
返回ky21表中，age的最小值

1.3 字符串函数

字符串函数描述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重复γ次space (x)返回x个空格replace(x,y,z)将字符串z替代字符串x中的字符串ystrcmp(x, y)比较x和y，返回的值可以为 -1,0,1substring(x,y,z)获取从字符串x中的第y个位置开始长度为z的字符串reverse (x)将字符串x反转
示例：

length(x) 返回字符串x的长度
返回abcd的长度，空格也算一个字符
select length('abcd');
select length('abc  d');
 
trim()  返回去除格式的值
select trim('     sheng');
select '       sheng';
 
concat(x,y) 将提供的参数x和y拼接成一个字符串
select concat('abc', 'def');
select concat('abc','   def');
 
还可以结合其他函数，如trim(将后面的函数删除格式)
select concat('abc',trim('def'));
 
upper(x) 将字符串x的所有字母变成大写字母
select upper('abc');
 
lower(x) 将字符串x的所有字母变成小写字母
select lower('ABC');
 
left (x,y)   返回字符串x的前y个字符
select left('abcdefg',3);
 
right (x,y)  返回字符串x的后y个字符
select right ('abcdefg',3);
 
把字符串的前3个字母和后3个字母拼接起来
select concat (left('abcdefg' ,3),right ('abcdefg',3));
 
repeat(x,y) 将字符串x重复y 次
select repeat ('abc',2);
 
space(x) 返回x个空格
select length (space(3));
 
replace(x,y,z)  将字符串z替代字符串x中的字符串y
select replace('hello','ll','aa');
 
strcmp(x,y) 比较x和y，返回的值可以为-1,0,1
 
比较17和18，小于返回-1，等于返回0，大于返回1，只会返回这3个值，它是比较第一位不同的数字
select strcmp(17,18);
select strcmp(18,18);
select strcmp(19,18);
select strcmp(17,1);
 
substring(x,y,z) 获取从字符串x中的第y个位置开始长度为z的字符串，返回从字符串中第三个字符开始的4个字符。
select substring('abcdefg',3,4);
 
reverse (x)  将字符串x反转
select reverse('gfedcba');
 
返回字符串的前3个字符，然后反转输出
select reverse (left ('gfedcba',3));
 
先将字符串反转，再输出前3个字符
select left (reverse ('gfedcba'),3);

1.4 日期时间函数

字符串函数描述curdate()返回当前时间的年月日curt ime{)返回当前时间的时分秒now()返回当前时间的日期和时间month (x)返回日期x中的月份值week (x)返回日期x是年度第几个星期hour (x)返回x中的小时值minute (x)返回x中的分钟值second (x)返回x中的秒钟值dayofweek(x)返回x是星期几，1星期日，2星期一dayo fmonth (x)计算日期x是本月的第几天dayofyear (x)计算日期x是本年的第几天

五，存储过程

1，存储过程简介

存储过程也shell脚本里的函数类似，只不过存储过程，封装的是一组SQL语句

• Mysql数据库存储过程是一组为了完成特定功能的sql语句集合
• 存储过程在使用过程中是将常用或者复杂的工作预先使用的sql语句写好并用一个指定的名称存储起来，这个过程编译的优化后存储在数据库服务器中，当需要使用该存储过程时，只需要调用它即可
• 存储过程在执行上比传统sql速度更快，执行效率更高

2，存储过程的优点

• 执行一次后，会生成的二进制代码驻留缓冲区，提高执行效率
• sql语句加上控制语句的集合，灵活性高
• 在服务器存储，客户调用时，降低网络负载
• 可多次重复调用，可随时修改，不影响客户端调用
• 可完成所以数据库操作，也可控制数据库的信息访问权限

create procedure <过程名> ([过程参数……]) <过程体>
[过程参数……] 格式
<过程名>：尽量避免与内置的函数或字段重名
<过程体>：语句
[in|out|inout] <参数名><类型>

例：
delimiter $$                                    #将语句的结束符号从分号；临时修改，以防出问题，可以自定义
create procedure ee(in inname varchar(16))      #创建存储过程，过程名自定义,（）可带参数
     begin                                       #过程体以关键字BEGIN开始
     select * from test1 where a_name='aaaa';      #过程体语句
     end$$                                        #过程体以关键字END结尾
delimiter ;                                     #将语句的结束符号恢复为分号
call ee('aaaa');                                  #调用存储过程

--------------查看存储过程--------------
show create procedure XXX\G                        #查看某个储存过程的具体信息