MySQL高级SQL语句

最新推荐文章于 2022-06-27 15:36:02 发布

慧日破重昏，芳心困落日

最新推荐文章于 2022-06-27 15:36:02 发布

阅读量420

点赞数

分类专栏： MySQL 文章标签： mysql

本文链接：https://blog.csdn.net/weixin_46355881/article/details/108333818

版权

MySQL 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

MySQL高级SQL语句

文章目录

MySQL高级SQL语句
按关键字排序
对结果进行分组
限制结果条目
设置别名
通配符
子查询
运算符
连接查询
函数
存储过程

DDL DML DQL DCL
MySQL进阶查询
MySQL数据库函数
MySQL存储过程（一种安全控制）

按关键字排序

使用order by语句来实现排序
排序可针对一个或多个字段
ASC：升序，默认排序方式
DESC：降序
语法结构：select column1,column2,…from table_name order by column1,column2,…asc|desc;
按单字段排序
按多字段排序
ORDER BY 后面跟多个字段时，字段之间使用英文逗号隔开，优先级是按先后顺序而定。下面以 A 和 B 分别表示两个字段。ORDER BY A,B desc 指 A 用升序，B 用降序；’‘ORDER BY A asc,B desc 指 A 用升序，B 用降序；’'ORDER BY A desc,B desc 指 A 用降序，B 用降序；

按单字段排序
msyql -uroot -pabc123
creaate database school;
use school;
mysql> create table info(id int(4) not null primary key auto_increment,name char(10) not null,score decimal(5,2),address varchar(5) default ‘未知’)engine=innodb; nodb;
Query OK, 0 rows affected (0.00 sec)

mysql> show tables;
±-----------------+
| Tables_in_school |
±-----------------+
| info |
±-----------------+
1 row in set (0.00 sec)

主参考放前面，辅助参考放后面
mysql> insert info (name,score,address) values(‘tianqi’,80,‘bj’),(‘heiba’,70,‘nnj’);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
select id,name,socre from info where score>=70 order by score,id desc;

对结果进行分组

通过 SQL 查询出来的结果，还可以对其进行分组，使用 GROUP BY 语句来实现。
GROUP BY 从字面上看，是以 BY 后面的内容对查询出的数据进行分组，就是将一个“数据集”划分成若干个“小区域”，然后针对这些个“小区域”进行数据处理
GROUP BY 分组的时候可以按一个或多个字段对结果进行分组处理。
语法结构：SELECT column_name, aggregate_function(column_name)FROM table_name WHERE column_name operator value group by column_name;

使用group by
count () 计数
count (数组形式)
sum () 求和
avg () 平均值
max () 最大值
min (*) 最小值

group by分组
select count(name),score from info where score>=70 group by score;
group by结合order by
select count(name),score from info where score>=70 group by score order by score desc;
select avg(score) from info;

限制结果条目

在使用 MySQL SELECT 语句进行查询时，结果集返回的是所有匹配的记录。有时候仅需要返回第一行或者前几行，这时候就需要用到 LIMIT 子句。
使用limit语句限制条目
语法结构：SELECT column1, column2, … FROM table_name LIMIT [offset,] number’[offset]表示位置偏移量，从0开始，number表示返回记录行的最大数目
不从第一条开始取值
limit

select * from info;
select * from info limit 3;
select * from info limit 2,3; //查看3、4、5行

mysql> insert info (name,score,address) values(‘tom’,82,‘bj’),(‘jerry’,74,‘nj’));
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
select * from info order by score desc limit 3;

设置别名

使用AS语句设置别名，关键字AS可省略
设置别名时，保证不能与苦衷其他表或字段名称冲突
别名语法结构：SELECT column_name AS alias_nameFROM table_name; '列的别名’SELECT column_name(s) FROM table_name AS alias_name; ‘表的别名’‘原字段或者表名在库内不会被改变’

select count() from info;
select count() as 数量 from info;
mysql> create table Hob(id int(3) not null primary key auto_increment,hob_name varchar(10) not null);
Query OK, 0 rows affected (0.01 sec)
insert into Hob (hob_name) values (‘登山’),(‘跑步’),(‘游泳’);
mysql> alter table info add column hobby int(3) not null; //添加列
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0
NUll 空（什么值都没有） ’ '空字符串
mysql> update info set hobby=1 where score >=80;
Query OK, 4 rows affected (0.00 sec)
Rows matched: 4 Changed: 4 Warnings: 0
mysql> update info set hobby=2 where hobby=0;
Query OK, 4 rows affected (0.00 sec)
Rows matched: 4 Changed: 4 Warnings: 0
select * from info inner join Hob on info.hobby=Hob.id;
inner join 两表内联
select info.name,Hob.hob_name from info inner join Hob on info.hobby=Hob.id;

select i.name,h.hob_name from info as i inner join Hob as h on i.hobby=h.id;

select i.name,h.hob_name from info i inner join Hob h on i.hobby=h.id;
select i.name 姓名,h.hob_name兴趣 from info i inner join Hob h on i.hobby=h.id;

通配符

用于替换字符串中的部分字符
通常配合like一起使用，并协同where完成查询
常用通配符
%表示零个、一个或多个
_表示单个字符

select name,score from info where name like ‘z%’;
select name,score from info where name like ‘z______’;

子查询

也称作内查询或者嵌套查询
先于主查询被执行，其结果将作为外层主查询的条件
在增删改查中都可以使用子查询
支持多层嵌套
IN语句是用来判断某个值是否在给定的结果集中

select name,score from info where id=1;
select name,score from info where id=1;
select name,score from info where id=5;
select name,score from info where id=12;
select name,score from info where id=16;

select name,score from info where id in (1,5,12,16);
select id from tmp where level >=45;
select name,score from info where id in (select id from tmp where level >=45);
select name,hobby from info where hobby in (select id from Hob where hob_name=’’);

select name,hobby from info where hobby in (select id from Hob);
select name,hobby from info where hobby= (select id from Hob where hob_name=’’);

select name,hobby from info where hobby!=(select id from Hob where hob_name=’’);
select name,hobby from info where hobby<>(select id from Hob where hob_name=’’);
!= <> 不等于

create table tmp like info;
desc tmp;
select * from tmp;
insert into tmp select * from info where id in (select id from info where address=‘nj’);

select * from info;
update info set score=score+5 where score<80;
select * from info;

exists存在相当于if 如果后面的执行成立才会执行前面的后面只是true或false
select name,score from info where exists (select id from Hob where hob_name=’’);
exists 只能做select语言查询查询结果为空不执行前面的，查询结果不为空执行前面的

NULL 空
表示确缺失的值
与数字或者空白（spaces）是不同的
使用IS NULL 或IS NOT NULL进行判断
NULL值和空值的区别
空值长度为，不占空间；NULL值得长度为NULL，占用空间
IS NULL无法判断空值
空值使用“=”“<>”来处理
COUNT（）计算时，NULL会忽略，空值会加入计算

create table num (id int() not null primary key quto_increment,name char());
desc num;
select * from num;
insert into num (id ,name) values (1,‘tom’);
insert into num (id) values (2);
select count(id) from num;
select count(name) from num;
insert into num (id,name) values (3,’’);
select count(name) from num;

select * from num where name is not null;
过滤和删选null值

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


^	匹配开始字符	p1	p2
$	匹配结束字符	[…]	匹配字符集中的任意一个字符
.	匹配任意单个字符	[^…]	匹配不在中括号内的任何字符
*	匹配任意个前面的字符	{n}	匹配前面的字符串n次
+	匹配前面字符至少一次	{n,m}	匹配前面的字符串至少n次，至多m次

regexp

select * from info where name regexp '^wa';
select * from info where name regexp 'si$';
select * from info where name regexp 'wu';连续型包含
select * from info where name regexp 'tian..';
select * from info where name regexp 'zh|ti';
insert into info (name,score,address,hobby) values(ooo,85,'nj',3),(oooooooo,85,'nj',3);
select * from info where name regexp '.*';
select * from info where name regexp 'o*';
select * from info where name regexp '*';与Linux命令不同这个错误使用.*
select * from info where name regexp '^[a-h]';

用于对记录中的字段值进行运算
运算符分类
算术运算符
比较运算符
逻辑运算符
位运算符
优先级
算数运算符
比较运算符
逻辑运算符
位运算符对二进制

mysql支持的算术运算符
o+加法
o-减法
o*乘法
o/除法
o%取余数

select 1+2,23,5-4,7%2,7/2;
mysql> select 1+2,22,7%2,6/2;
±----±----±-----±-------+
| 1+2 | 22 | 7%2 | 6/2 |
±----±----±-----±-------+
| 3 | 4 | 1 | 3.0000 |
±----±----±-----±-------+
1 row in set (0.00 sec)
mysql> select 1+2,23,5-4,7%2,7/2;
±----±----±----±-----±-------+
| 1+2 | 2*3 | 5-4 | 7%2 | 7/2 |
±----±----±----±-----±-------+
| 3 | 6 | 1 | 1 | 3.5000 |
±----±----±----±-----±-------+
1 row in set (0.01 sec)

运算符

比较运算符

字符串的比较默认不区分大小写，可使用binary来区分
其中字符的比较是根据 ASCII 码来判断的，0为48，A是65，a为97
常用比较运算符

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

select 2=4,2=‘2’,‘e’=‘e’,(2+2)=(3+1),‘a’=NULL;
mysql> select 2=4,2=‘2’,‘e’=‘e’,(2+2)=(3+1),‘a’=NULL;
±----±------±--------±------------±---------+
| 2=4 | 2=‘2’ | ‘e’=‘e’ | (2+2)=(3+1) | ‘a’=NULL |
±----±------±--------±------------±---------+
| 0 | 1 | 1 | 1 | NULL |
±----±------±--------±------------±---------+
1 row in set (0.00 sec)

不等于运算符不能用于判断NULL
等于运算符
等号（=）是用来判断数字、字符串和表达式是否相等的，如果相等则返回 1，如果不相等则返回 0。
如果两者都是整数，则按照整数值进行比较。
如果一个整数一个字符串，则会自动将字符串转换为数字，再进行比较。
如果两者都是字符串，则按照字符串进行比较。
如果两者中至少有一个值是 NULL，则比较的结果是 NULL。
不等于运算符
不等于号有两种写法，分别是<>或者!=，用于针对数字、字符串和表达式不相等的比较。如果不相等则返回 1，如果相等则返回 0，这点正好跟等于的返回值相反。
不等于运算符不能用于判断null。
大于、大于等于、小于、小于等于运算符
大于（>）运算符用来判断左侧的操作数是否大于右侧的操作数，若大于返回 1，否则返回 0，同样不能用于判断 NULL。
小于（<）运算符用来判断左侧的操作数是否小于右侧的操作数，若小于返回 1，否则返回 0，同样不能用于判断 NULL。
大于等于（>=）判断左侧的操作数是否大于等于右侧的操作数，若大于等于返回 1，否则返回 0，不能用于判断 NULL。
小于等于（<=）判断左侧的操作数是否小于等于右侧的操作数，若小于等于返回 1，否则返回 0，不能用于判断 NULL。

mysql> select 4 between 2 and 6,5 between 5 and 10,‘f’ between ‘a’ and ‘z’;
±------------------±-------------------±------------------------+
| 4 between 2 and 6 | 5 between 5 and 10 | ‘f’ between ‘a’ and ‘z’ |
±------------------±-------------------±------------------------+
| 1 | 1 | 1 |
±------------------±-------------------±------------------------+
1 row in set (0.00 sec)

mysql> select least(10,20,30),least(‘a’,‘b’,‘c’),greatest(1,2,3),greatest(‘a’,‘b’,‘c’);
±----------------±-------------------±----------------±----------------------+
| least(10,20,30) | least(‘a’,‘b’,‘c’) | greatest(1,2,3) | greatest(‘a’,‘b’,‘c’) |
±----------------±-------------------±----------------±----------------------+
| 10 | a | 3 | c |
±----------------±-------------------±----------------±----------------------+
1 row in set (0.01 sec)

mysql> select ‘kfc’ like ‘kf_’,‘kfc’ like ‘%c’,‘tom’ not like ‘%j’;
±-----------------±----------------±--------------------+
| ‘kfc’ like ‘kf_’ | ‘kfc’ like ‘%c’ | ‘tom’ not like ‘%j’ |
±-----------------±----------------±--------------------+
| 1 | 1 | 1 |
±-----------------±----------------±--------------------+
1 row in set (0.00 sec)

逻辑运算符

又称为布尔运算符

用于判断表达式的真假
运算符描述
NOT 或！逻辑非
AND 或 && 逻辑与
OR 或 || 逻辑或
XOR 逻辑异或

且 0&&0=0 1&&0=0 0&&1=0 1&&1=1
或 0||0=0 1||0=1 0||1=1 1||1=1
异或 0^0=0 1^0=1 0^1=1 1^1=0

XOR异或NULL和0或1 结果都是NULL

位运算符

位运算符实际上是对二进制数进行计算的运算符，MySQL 内位运算会先将操作数变成二进制格式，然后进行位运算，最后在将计算结果从二进制变回到十进制格式

<<左移位运算
	1<<2
	0001   1   向左移动位数，空缺数补0
	0100   4

10<<3
1010  10
1010000 80

>>右移位运算 向右移动位数，多余的位数直接删除
15>>2
1111  15
0011|11 3

连接查询

inner join 取两张表的交集
外连接
左连接 left join left outjoin

右连接 right join right outjoin

函数

聚合函数

对表中数据记录进行集中概括而设计的都一类函数

常用的聚合函数

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

数学函数

常用的数学函数

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

select abs(-1),abs(-3.14);
mysql> select abs(-1),abs(-3.14);
±--------±-----------+
| abs(-1) | abs(-3.14) |
±--------±-----------+
| 1 | 3.14 |
±--------±-----------+
1 row in set (0.00 sec)

select rand();0到1的随机数
mysql> select rand();
±--------------------+
| rand() |
±--------------------+
| 0.32690428600358246 |
±--------------------+
1 row in set (0.01 sec)

select mod(10,3);
mysql> select mod(10,3);
±----------+
| mod(10,3) |
±----------+
| 1 |
±----------+
1 row in set (0.00 sec)

select power(3,2);
mysql> select power(3,2);
±-----------+
| power(3,2) |
±-----------+
| 9 |
±-----------+
1 row in set (0.01 sec)

select round(1.49);四舍五入
mysql> select round(1.49);
±------------+
| round(1.49) |
±------------+
| 1 |
±------------+
1 row in set (0.01 sec)

select round(1.49,1);小数点后面保留一位
mysql> select round(1.49,1);
±--------------+
| round(1.49,1) |
±--------------+
| 1.5 |
±--------------+
1 row in set (0.00 sec)

select round(3.147,2);小数点后面保留两位
mysql> select round(3.147,2);
±---------------+
| round(3.147,2) |
±---------------+
| 3.15 |
±---------------+
1 row in set (0.00 sec)

有效位数后面的一位做四舍五入
select sqrt(9);平方根
mysql> select sqrt(9);
±--------+
| sqrt(9) |
±--------+
| 3 |
±--------+
1 row in set (0.00 sec)

select truncate(3.1415926,3);截取小数点后面3位有效数字
mysql> select truncate(3.1415926,3);
±----------------------+
| truncate(3.1415926,3) |
±----------------------+
| 3.141 |
±----------------------+
1 row in set (0.00 sec)

select ceil(1.9);向上取整
mysql> select ceil(1.9);
±----------+
| ceil(1.9) |
±----------+
| 2 |
±----------+
1 row in set (0.00 sec)

select floor(1.9);向下取整
mysql> select floor(1.9);
±-----------+
| floor(1.9) |
±-----------+
| 1 |
±-----------+
1 row in set (0.00 sec)

字符串函数

常用的字符串函数

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 反转

mysql> select length(‘abcd’);
±---------------+
| length(‘abcd’) |
±---------------+
| 4 |
±---------------+
1 row in set (0.00 sec)
select length(‘ab cd’);空格算一个长度
mysql> select length(‘ab cd’);
±----------------+
| length(‘ab cd’) |
±----------------+
| 5 |
±----------------+
1 row in set (0.00 sec)

mysql> select trim(‘abcd’);
±-------------+
| trim(‘abcd’) |
±-------------+
| abcd |
±-------------+
1 row in set (0.01 sec)

日期时间函数

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 是本年的第几天
返回当前时间
mysql> select curdate();
±-----------+
| curdate() |
±-----------+
| 2020-08-25 |
±-----------+
1 row in set (0.00 sec)

mysql> select curtime();
±----------+
| curtime() |
±----------+
| 16:08:23 |
±----------+
1 row in set (0.00 sec)

mysql> select now();
±--------------------+
| now() |
±--------------------+
| 2020-08-25 16:08:54 |
±--------------------+
1 row in set (0.00 sec)

mysql> select month(curdate());
±-----------------+
| month(curdate()) |
±-----------------+
| 8 |
±-----------------+
1 row in set (0.01 sec)

mysql> select week(curdate());
±----------------+
| week(curdate()) |
±----------------+
| 34 |
±----------------+
1 row in set (0.01 sec)

mysql> select hour(curtime());
±----------------+
| hour(curtime()) |
±----------------+
| 16 |
±----------------+
1 row in set (0.00 sec)

mysql> select minute(curtime());
±------------------+
| minute(curtime()) |
±------------------+
| 10 |
±------------------+
1 row in set (0.00 sec)

mysql> select second(curtime());
±------------------+
| second(curtime()) |
±------------------+
| 35 |
±------------------+
1 row in set (0.00 sec)

mysql> select dayofweek(curdate());
±---------------------+
| dayofweek(curdate()) |
±---------------------+
| 3 |
±---------------------+
1 row in set (0.00 sec)

mysql> select dayofmonth(curdate());
±----------------------+
| dayofmonth(curdate()) |
±----------------------+
| 25 |
±----------------------+
1 row in set (0.00 sec)

mysql> select dayofyear(curdate());
±---------------------+
| dayofyear(curdate()) |
±---------------------+
| 238 |
±---------------------+
1 row in set (0.00 sec)

存储过程

是一组为了完成特定功能的SQL语句集合
比传统SQL速度更快、执行效率更高
存储过程的优点
    执行一次后，会将生成的二进制代码驻留缓冲区，提高执行效率
    SQL语句加上控制语句的集任，灵活性高
    在服务器端存储，客户端调用时，降低网络负载
    可多次重复被调用，可随时修改，不影响客户端调用
    可完成所有的数据库操作，也可控制数据库的信息访问权限

servlet是服务器容器，写了很多代码，sql语句嵌入在Java代码当中，你要去找远程的数据库服务器，而你在sql语句中写了很多select语句，然后发送给数据库，数据库执行sql语句再返回来。在这过程当中进行封装，通过网络，当SQL语句中子查询过多，使网络负载变大，高并发查询时阻塞严重。其次安全性，很多数据包通过封装传输，容易被截取。因此有了存储。

减轻网络负载
增加网络安全性
只要在JAVA代码中嵌入一个名字，将MyRole发送给对方就可以调用，执行SQL语句。从安全性上看，窃取者只能看到名字，而不会知道里面具体的操作。

create procedure <过程名> ([过程参数[,…]]) <过程体>

定义存储过程
delimiter $KaTeX parse error: Expected 'EOF', got '#' at position 44: \dots数名参数类型） begin #̲定义变量 declare 变量\dots$
delimiter ;

调用存储过程
call 存储过程名（实际参数）;
查询存储过程
show procedure status where db=‘数据库’

删除存储过程
drop procedure 存储过程名;