MySQL高级SQL语句

一、概览

1、NULL值

1、表示缺失的值
2、与数字0或者空白(spaces) 是不同的
3、使用IS NULL或IS NOT NULL进行判断
4、NULL值和空值的区别

• 空值长度为0，不占空间; NULL值的长度为NULL,占用空间
• IS NULL无法判断空值
• 空值使用“=”或者“<> “来处理
• COUNT()计算时，NULL会忽略,空值会加入计算

2、正则表达式

• 根据指定的匹配模式匹配记录中符合要求的特殊字符

• 使用REGEXP关键字指定匹配模式

• 常用匹配模式

  1、^匹配开始字符

  2、p1|p2匹配p1或p2

  3、$匹配结束字符

  4、[…] 匹配字符集中的任意一个字符

  5、.匹配任意单个字符

  6、[^…] 匹配不在中括号内的任何字符

  7、*匹配任意个前面的字符

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

  9、+匹配前面字符至少1次

  10、{n,m}匹配前面的字符串至少n次,至多m次

3、运算符

用于对记录中的字段值进行运算
运算符分类
●算术运算符
●比较运算符
●逻辑运算符
●位运算符

4、算术运算符

MySQL支持的算术运算符

• +加法
• -减法
• *乘法
• /除法
• %取余数

5、比较运算符

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

• = 等于
• >大于
• <小于
• >=大于等于
• <=小于等于
• !=或<>不等于
• IN 在集合中
• LIKE通配符匹配
• IS NULL判断一一个值是否为NULL
• IS NOT NULL判断一个值是否不为NULL
• BETWEEN AND两者之间
• GREATEST两个或多个参数时返回最大值
• LEAST两个或多个参数时返回最小值

6、逻辑运算符

又被称为布尔运算符

用来判断表达式的真假

常用的逻辑运算符

• NOT或! ：逻辑非
• AND或&&：逻辑与
• OR或||：逻辑或
• XOR：逻辑异或

7、位运算

<< 左移位运算，空缺处补0

>> 右移位运算，多余的位数直接删除

8、运算符的优先级

表 5-6 运算符的优先级

9、内连接

inner join——-内连接

select a.id,b.name from info a inner join abc b on a.id=b.nid;

10、左连接

left join或left outer——左连接

select a.id,b.name from info a left join abc b on a.id=b.nid;

11、右连接

right join或right outer——右连接

select a.id,b.name from info a right join abc b on a.id=b.nid;

12、数据库函数

■MySQL提供了实现各种功能的函数
■常用的函数分类
1、数学函数
2、聚合函数
3、字符串函数
4、日期时间函数

13、常用的数学函数

●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…) 返回集合中最小的值

14、聚合函数

对表中数据记录进行集概括而设计的一类函数
常用的聚合函数
●avg()返回指定列的平均值
●count()返回指定列中非NULL值的个数
●min()返回指定列的最小值
●max()返回指定列的最大值
●sum()返回指定列的所有值之和

15、字符串函数

常用的字符串函数
●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个空格
●replace(x,y,z) 将字符串z替代字符串x中的字符串y
●strcmp(x,y) 比较x和y,返回的值可以为-1,0,1
●substring(x,y,z) 获取从字符串x中的第y个位置开始长度为z的字符串
●reverse(x) 将字符串x反转

16、日期时间函数

常用的日期时间函数
●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是本年的第几天

17、存储过程简介

■是一组为了完成特定功能的SQL语句集合

■比传统SQL速度更快、执行效率更高

■存储过程的优点
1、执行一-次后，会将生成的二进制代码驻留缓冲区，提高执行效率
2、SQL语句加上控制语句的集合，灵活性高
3、在服务器端存储，客户端调用时，降低网络负载
4、可多次重复被调用，可随时修改,不影响客户端调用
5、可完成所有的数据库操作,也可控制数据库的信息访问权限

18、存储过程参数类型

●输入参数: IN
●输出参数: OUT
●输入输出参数: INOUT

19、创建存储过程

■存储过程的主体部分，被称为过程体
■以BEGIN开始，以END结束，若只有一条SQL语句，则可以省略BEGIN-END
■以DELIMITER开始和结束

20、定义存储过程

delimiter $$

create procedure 存储过程名（in 参数名 参数类型）

begin

#定义变量

declare 变量名 变量类型

#变量赋值

set 变量名 = 值

​ sql语句1;

​ sql语句2;

​ …

​ sql语句n;

end$$

delimiter ;

21、调用存储过程

call 存储过程名（实际参数）

22、查询存储过程

show procedure status where db=‘数据库’；

23、删除存储过程

drop procedure 存储过程名；

24、修改存储过程

ALTER PROCEDURE proc_countByName

MODIFIES SQL DATA

SQL SECURITY INVOKER;