MySQL 运维 - 高阶SQL语句 一、排序① 按单字段排序② 多字段排序 二、条件判断① or/and② 嵌套/多条件 三、查询① 查询不重复记录② 指定多字段去重③ 对结果进行分组④ 结合排序语法⑤ 限制条目结果⑥ 子查询⑦ 子查询-exists⑧ AS作为连接语句⑨ 子查询-别名⑩ IN 五、通配符六、视图[类似与镜像]七、NULL值八、正则表达式运算符九、操作符① 较运算符等于不等于判断一个值为/不为NULL(是否为空或非空)两者之间(between...and...)在/不在集合中(in,not in) ② 逻辑运算符逻辑非(not 或 ！)逻辑与逻辑或逻辑异或 ③ 位运算符位与运算符 &位或运算符 |位异或运算 ^位取反运算&~ 十、内连接查询十一、左连接查询十二、右连接查询十三、数据库函数十四、聚合函数十五、字符串函数十六、日期时间函数十七、存储过程

一、排序 ① 按单字段排序

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判断一个值是否为NULLIS NOT NULL判断一个值是否不为NULLBETWEEN 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中的字符串ystrcmp(x,y)比较x和y，返回的值可以为-1，0，1substring(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速度更快，执行效率更高。

存储过程的优点

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

语法：

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 函数名

---

1.本站遵循行业规范，任何转载的稿件都会明确标注作者和来源；2.本站的原创文章，会注明原创字样,如未注明都非原创，如有侵权请联系删除！；3.作者投稿可能会经我们编辑修改或补充；4.本站不提供任何储存功能只提供收集或者投稿人的网盘链接。