MySQL 运维 - 高阶SQL语句

一、排序

① 按单字段排序

asc 升序[默认] desc 降序
语法

select 显示用字段 from 表名 where 判断用字段>=Number order by 以哪个字段做排序 desc;

举例：

select id from user where point>=200 order by point desc;

② 多字段排序

order by 以后的参数 使用","分割优先是按先后循序而定
语法

select 字段1 from 表名 order by 字段2 desc,字段3 desc;

举例：

select id from user order by point desc,id desc;

现根据字段1进行降序排序，在进行字段2的降序排序
第一个字段只有在出现相同数值的时候第二字段才有意义

二、条件判断

① or/and

语法

select * from 表名 xx >20 and xx<=10;

and表示需要同时满足
or表示只需要满足其中之一
举例：

select id from user where point > 100 and point <= 500;

② 嵌套/多条件

语法

select * from 表名 where xx > 10 or (xx > 15 and xx < 50);

举例：

select id from user where point > 100 or (point > 150 and point < 500);

三、查询

① 查询不重复记录

语法

select distinct 字段 from 表名;

举例：

select distinct id from user;

② 指定多字段去重

distinct必须放在最开头
distinct只能使用需要去重的字段进行操作
指定多个字段去重的话，必须要满足这几个字段同时重复时才会被过滤
语法

select distinct 字段 from 表名 where 字段 in (select distinct 字段 from 表名);

举例：

select id,name from user where point in (select distinct point from user);

③ 对结果进行分组

通过 SQL 查询出来的结果，还可以对其进行分组，使用group by语句来实现，group by 通常都是结合聚合函数一起使用的，常用的聚合函数包括：计数(count) 求和(sum) 求平均数(avg) 最大值(max) 最小值(min)，group by 分组的时候可以按一个或多个字段对结果进行分组处理

语法

select count(字段)  from * where 表名 group by 字段;  

举例：

select count(name),point from user where point>=300 group by point;

对user表进行分组
筛选范围/条件是point大于等于300的name，point相同的会默认分在一组

④ 结合排序语法

语法

select count(字段)  from * where 表名 group by 字段 order by count(字段) desc;

举例：

select count(id),name from user group by point;

select count(id),name from user group by point order by count(id) asc;

⑤ 限制条目结果

结果集返回的是所有匹配的记录
使用limit语句限制条目
位置偏移量第一条是0第二条是1以此类推
语法

select * from 表名 limit number;

举例：

select * from user limit 2,3;

结合order by排序

select * from 表名 order by 字段 desc limit number;

select * from user order by id desc limit 2,3;

⑥ 子查询

子查询也被称作内查询或者嵌套查询，是指在一个查询语句里面还嵌套着另一个查询语句
子查询语句是先于著查询语句被执行，其结果作为外层的条件返回给主查询进行下一步的查询过滤

子语句可以与主语句所查询的表相同，也可以是不同的表

不同表举例：

select name,score from info where id in (select id from info where score>80);

主语句举例：

select name,score from info where id;

子语句举例：

select id from info where score > 80;

子查询不仅可以在select语句中使用，在insert、update、delete中也同样使用
支持多次嵌套
可以使用not in表示对结果集取反

⑦ 子查询-exists

select count(*) as number from 表名 where exists(
select id from 表名 where 字段='值'
)

exists 布尔值判断，后面的内查询语句，是否成立where 之后跟条件判断加exists之后的条件是否成立，如果成立，则正常执行count为计数 sum为求和 使用sum求和结合exists，如子查询结果集不成立输出null

设定模式(指定字段) 选择表 判断 (选中查询的字段);

⑧ AS作为连接语句

语法

create table 表名 as select * from 表名

举例：

create table user_new as select * from user;

1.创建一个新的表 定义了表结构 插入表数据
2.但是约束没有复制过来，但是如果原表设置了主键那么Defaut默认为0

⑨ 子查询-别名

先查询表字段[看作一个结果集]
将结果集作为一张表将那些查询的时候
语法

select 别名.字段 from (select 字段 from 表) 别名;

举例：

select a.id from (select id,name from info)a;

select * from 表名，此为标准格式，而以上的查询语句，"表名"的位置其实是一个结果集，mysql并不能识别
此时给与结果集设置一个别名，并且以select a.id,name from a;的方式查询，将此结果集视为一张表就可以正常查询出数据了

相当于select info.id,name from info;
     select 表.字段,字段 from 表;

⑩ IN

显示已知的资料
语法

select 字段 from 表名 where 字段 in ('值1','值2'...);

举例：

select name from user where money in (100,200);
# 四、设置别名
当表中有命名过长的字段时，可以通过
设置别名来缩短操作

**语法**

```sql
select 字段 AS 别名 from 表名;

举例：

select name AS '名字' from user;

AS可以省略

使用AS后可以用别名代替字段名，其中AS语句时可选的
之后的别名。主要为表内列或者表提供临时的名称
在查询过程中使用，库内实际的表名或字段不会改变的

表数据别名设置示例

select a.name as 姓名,a.point as 点数 from user as a;

使用场景
1.对复杂的表进行查询的时候，别名可以缩短查询语句书写
2.多表相连查询的时候

五、通配符

通配符主要用于替换字符串的部分字符，通过部分字符的匹配将相关结果查询出来
%百分号表示零个或以后多个字符
_下划线表示单个字符

查询名字是a开头的记录
模糊查询举例：
搜索任意包含M字符的行

select * from info where name like '%M%';

搜索F后任意单个字符i后零个或多个任意字符k

select * from info where name like 'F_i%k';

六、视图[类似与镜像]

用于动态保存结果集 as 可以作为把上一个表的内容复制过来
语法

create view v_xxx as select * from 表名 where 字段>=Number;

举例：

create view v_user as select * from info where score>60;

查看表的状态

show table status\G

Comment:VIEW

查看视图

select * from v_xxx;

修改原表数据[该原数据后就自动映射到视图中]

update 表名 set 字段='xx' where 字段='xx';

update info set score=1 where name='Sans';

七、NULL值

NULL通过用来表示缺失的值，也就是在表中该字段没有值
如果在创建表时，限制某些字段不为空，则可以使用NOT NULL关键字不适用则默认可以为空
在向表内插入记录或者更新记录时，如果该字段没有NOT NULL并且没有值这时候新纪录的该字段将被保存为NULL，需要注意的是，NULL值与数字0或者空白的字段是不同的，值为NULL的字段没有值，在SQL语句中，使用IS NULL可以判断表内的某个字段是不是NULL值，相反用IS NOT NULL可以判断不是NULL值

NULL值和空值的区别

NULL值	空值
空值长度为0	不占用空间
NULL值的长度为null	占用空间

IS NULL无法判断空值
空值使用=或者<>来处理(!=)
count()计算时，NULL会忽略，空值会加入计算

举例：

检测NULL值是否会计入统计

select count(id) from 表名;

将其中一条值改为空值

update 表名 set 字段='' from 字段=值;

检测空值是否会被统计

select count(字段) from 表名;

查询为null的值

select * from 表名 where 字段 is null;

查看不能null的值

select * from 表名 where 字段 is not null;

八、正则表达式

MySQL正则表示通常是在检索数据库记录的时候，根据指定的匹配模式匹配记录中符合要求的特殊字符串
MySQL的正则表达式使用REGEXP这个关键字来指定正则表达式的匹配模式

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

操作符	含义
^	匹配文本的开始字符
$	匹配文本的结束字符
.	匹配任何单个字符
*	匹配零个或多个在它前面的字符
+	匹配前面的字符一次或多次
字符串	匹配包含指定的字符串
^a	匹配所有以a开头的字符
a1 | a2	匹配a1或a2
[a-z]	匹配集合中任意一个字符
[^a-z]	匹配除集合中任意一个字符
{n}	匹配前面的字符串n次
{n,m}	匹配前面的字符串至少n次，至多m次

举例：
匹配C开头的内容

select id,name from info where name regexp '^C';

匹配所有n结尾的内容

select id,name,address from info where address regexp 'n$';

匹配任何单个或多个字符

select id,name from info where name regexp 'S..s';

匹配零个或多个在它前面的字符

select address from info where address regexp 'p*';

匹配town和Home

select address from info where address regexp 'town|Home';

匹配集合中的任意一个字符

select address from info where address regexp '[a,b]';

匹配集合以外的字符

select address from info where address regexp '[^lazy]';

匹配前面的字符串1次

select name from info where name regexp 's{1}';

匹配前面的字符1次最多1次

select name from info where name regexp 's{1,1}';

运算符

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

算法运算

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

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

举例：

select 4+5,3*3,12/2,20-20,9%9,1+8-2;

create table awsl (select 4+5,3*3,12/2,20-20,9%9,1+8-2);

九、操作符

① 较运算符

字符串的比较默认不区分大小写，可以用binary来区分
常用比较运算符(比较对象：数字、字符)

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

等于

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

其中字符的比较时根据ASCII码来判断的，如果ASCII码相等，则表示两个字符相同

如果ASCII码不相等，则表示两个字符不相同，例如字符串比较
起始比较的时ASCII码

举例：

select 'A'+'SOH'='B';

为什么A+SOH会等于B？？
那是因为他们比较之前会先转为ASCII码中的十进制
转换后：65(A)+1(SOH)=66(B)

不等于

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

select 'Cat'<>'Dog',1<>2,3!=3,6.9!=6, NULL<>NULL;

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

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

大于等于判断左侧的操作数是否大于等于右侧的操作数，若大于等于返回1，否则返回0，同样他也不能用于判断NULL

小于等于判断左侧的操作数是否小于等于右侧的操作数，若小于等于返回1，否则返回0，同样他也不能用于判断NULL

举例：

select 10>9,'a'>'b',2>=3,(2+3)>=(9+6),7.74<7,'x'<='y',5<=5.5,'u'>=NULL;

判断一个值为/不为NULL(是否为空或非空)

IS NULL 判断一个值是否为NULL，如果为NULL返回1，否则返回0
IS NOT NULL 判断一个值是否不为NULL，如果不为NULL返回1，否则返回0

select '404 ERROR Page' is null,'=)' is not null,null is null;

两者之间(between…and…)

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

select 4 between 2 and 6,5 between 6 and 8,'c' between 'a' and 'f'

数字比较，按大小排列
字母比较，从a到b排序，字母越前越小

在/不在集合中(in,not in)

IN 判断一个值是否在对应的列表中，如果是返回1，否则返回0
NOT IN 判断一个值是否不在对应的列表中，如果是返回1，否则返回0

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

② 逻辑运算符

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

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

逻辑非(not 或 ！)

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

举例：

select not 2,13,not 0,!(4-4);

逻辑与

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

举例：

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

逻辑或

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

举例：

select 2 OR 3,0 OR null;

逻辑异或

当任意一个操作数为NULL时，返回值为NULL
当于非NULL的操作数，如果两个操作数都是非0值或者都是0值，则返回值为0
如果一个为0值，另一个为非0值，返回值为1

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

③ 位运算符

用于对二进制字节中的位进行测试或者位于等测试处理

运算符	作用
&	位与
^	位异或
<<	位左移
>>	位右移
~	位取反

位与运算符 &

两个数进行二进制逐位运算，如都为1则为1，如其中一个为0则为0

select 6 & 2;

6的二进制0110
2的二进制0010
与运算后的结果为0010=2

位或运算符 |

两个数对应的二进制有一个或两个为1则为1，否则为0

select 5 | 1;

5的二进制0101
1的二进制0001
或运算后的结果为0101=5

位异或运算 ^

应的二进制数不同时，对应的结果才为1；如果两个对应位数都为0或者都为1，则为0

select 5 ^ 1;

5的二进制0101
1的二进制0001
或运算后的结果为0100=4

位取反运算&~

根据优先级先执行~在执行&

select 6 &~ 1;

6的二进制0110
1的二进制0001
因先执行~，所以1全部取反=1110
最后执行&与运算
得到结果：0110=6

十、内连接查询

MySQL中的内连接就是两张或多张表中同时相同的部分

select xx.字段 from 表名 xx  inner join 表名2 b on xx.字段=xx.字段;

十一、左连接查询

以左侧表为基础表，接收左表的所有行，并用这些行与右侧关键字来表示

select * from 表名 left join 表名2 on 表名.字段= 表名2.字段;

十二、右连接查询

以右侧表为基础表，接收右表的所有行，并用这些行与左侧关键字来表示

select * from 表名 right join 表名2 on 表名.字段=表名2.字段;

十三、数据库函数

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

数学函数	描述
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…)	返回集合中最小的值

举例：
-2的绝对值

select abs(-2);

获取随机数[范围0-1]

select rand();

求9除2的余数

select mod(9,2);

求2的10次方

select power(2,10);

从超多小数中拯救整数

select round(3.14159265358979323846);

保留指定数量的小数并四舍五入

select round(3.14159265358979323846,2);

求6的平方根

select sqrt(6);

指定保留几位小数

select truncate(1.1111,2);

返回大于等于3.14的最小整数

select ceil(3.14);

返回小于等于3.14的最大整数

select floor(3.14);

返回集合中最大的值

select greatest(1,2,3,4,5);

返回集合中最小值

select least(1,2,3,4,5);

十四、聚合函数

聚合函数	描述
avg()	平均值
count()	计数
min()	最小值
max()	最大值
sum()	所有值之和

十五、字符串函数

字符串函数	描述
length()	返回字符串长度
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反转

测试中发现一些有趣的bug
select upper(‘a’)=‘a’;会成立

它会优先去找可以转化成数字的字符，如果单引号里是1abc就不会成立，它会把字母抛弃变为0，所以结果会成立

十六、日期时间函数

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

十七、存储过程

存储过程是一组为了完成特定功能的SQL语句集合

存储过程在使用过程中是将常用或者复杂的工作预先使用SQL语句写好并用一个指定的名称来进行储存，这个过程经编译和优化后存储在数据库服务器中，当需要使用该存储过程时，只需要调用它即可，存储过程在执行上比传统SQL速度更快，执行效率更高。

存储过程的优点

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

语法：

DELIMITER !!    				#将语句的结束符号从分号；临时修改，以防出问题，可以自定义
CREATE PROCEDURE XXX()  	#创建存储过程，过程名自定义,（）可带参数
 	BEGIN   		    	#过程体以关键字BEGIN开始
 	select * from xxx;  	#过程体语句
 	END!!    				#过程体以关键字END结尾
DELIMITER ; 			    	#将语句的结束符号恢复为分号

call XXX;	  				#调用存储过程


show create procedure [数据库.]储存过程名; 	  #查看某个储存过程的具体信息
show create procedure XXX;
show procedure status [like '%XXX%'] \G

例：

delimiter $$
create procedure test()
begin
create table user(id int(10));
insert into user values (1);
end $$

delimiter ;
call test;

查看存储过程

show procedure status;

修改存储功能

alter procedure 函数名 modifies sql data sql security invoker;

删除存储过程

drop procedure if exists 函数名