Mysql 数据库 -------- SQL语句进阶查询 ------- 后部分_语句select length(trim('abc')的结果-优快云博客

本文链接：https://blog.youkuaiyun.com/XCsuperman/article/details/109152529

Mysql ---- SQL高级查询

连接查询
- 内连接
- 外连接
数据库函数
存储过程

连接查询

内连接

内连接：两张或多张表中同时符合某种条件的数据记录组合
from 子句中使用 inner join 关键字连接多张表，并使用 on 设置连接条件
是系统默认的表连接方式，可以省略 inner 关键字
多表支持连续使用 inner join，建议不超过三个表

语法结构：
select 字段名1，字段名2 from 表名1 inner join 表名2 on 表名1.字段名 = 表名2.字段名;

示例：
select info.id,info.name from info inner join num on info.id=num.id;

外连接

也称为左外连接、右外连接
在 from 子句中使用 left join 关键字来表示左连接；使用 right join 关键字来表示右连接
匹配左表中所有行及右表中符合条件的行
以左边为主表，左表会完全展现出来，右表只有符合条件的行才会被展现

示例：左外连接
select info.id,info.name,hob.hob_name from info left join hob on info.hobby=hob.id;

示例：右外连接
select info.id,info.name,hob.hob_name from info right join hob on info.hobby=hob.id;

数据库函数

常用的函数分类：数学函数、聚合函数、字符串函数、日期时间函数

数学函数

abs(x)   ：返回x的绝对值                   例：select abs(-10);       返回值为10
rand()   ：返回0到1的随机数                  例： select rand();         返回值为任意不为1的数，只能无限接近1，但永远不为1
mod(x,y)   ：返回x除以y以后的余数               例：select mod(7,2);       返回值为 余数1
power(x,y)   ：返回x的y次方               例：select power(2,3);          返回值为 8
round(x)   ：返回离x最近的整数              例：select round(1.4);   返回值为1 ；    select round(1.5);   返回值为2     ;        select round(1.48);    返回值为1
round(x,y)   ：保留x的y位小数四舍五入后的值          例：select round(1.49，2)；   返回值位1.49 ；       select round(1.4449,3);    返回值为1.445
sqrt(x)   ：返回x的平方根           例：select sqrt(9);    返回值为3   ；      select sqrt(8);     开平方不满足整数，返回值为 小数
truncate(x,y)   ：返回数字x截断为y位小数的值           例：select truncate(1.4449,3);       只保留小数点后3位，多余的丢弃，返回值为1.444
ceil(x)   ：返回大于或等于x的最小整数              例：select ceil(1.1);       向上取整最小整数，要比1大，返回值为2
floor(x)   ：返回小于或等于x的最大整数              例：select floor(x);           向下取最大整数，要比1小，返回值为1
greatest(x1,x2...)   ：返回集合中最大的值
least(x1,x2)   ：返回集合中最小的值

函数嵌套：
随机输出1-100 ：select ceil(rand()*100);
取余0、1、2     ：select mod(ceil(rand()*100),3);

聚合函数（给数据表用的）

avg()   ：返回指定列的平均值              例：select avg(score) from info;      统计info表中socre字段中数据的平均值
count()   ：返回指定列中非null值的个数            例：select count(2);        返回值为 统计2 只有1个，不能为多个数字；         select count(name) from info;    统计info表中name列的个数； 
min()   ：返回指定列的最小值               例：select min(score) from info;           统计info表中socre字段中数据最低的那个人
max()   ：返回指定列的最大值               例：select max(score) from info;        统计info表中socre字段中数据最高的那个人
sum()   ：返回指定列的所有值值和           例： select sum(score) from info;      统计info表中socre字段中数据的总和

函数嵌套：
查看表中成绩最高的三个人  ：select score from where 1=1 order by score desc；       ###其中 where 也可以不用写

字符串函数（字符串用’'格式）

length(x)   ：返回字符串x的长度           例：select length('abc');   返回值为3       ;       select length(' abc');     返回值为4   ； 
trim()   ：返回去除指定格式的值           例：select trim(' abc');        返回值为 abc   ；去除空格
concat(x,y)   ：将提供的参数x和y拼接成一个字符串          例：select length(concat('abc','def'));       返回值为abcdef
upper(x)   ：将字符串x的所有字母变成大写字母           例：select upper('acd');     返回值为 ABC   
lower(x)   ：将字符串x的所有字母变成小写字母           例：select lower('YdB');      返回值为 ydb
left(x,y)   ：返回字符串x的前y个字符            例：select left('abc',3);     返回值为 abc   ；从左往右数3个
right(x,y)   ：返回字符串x的后y个字符         例：mysql> select right('abcdef',3);       返回值为 def   ；从右往左数3个
repeat(x,y)   ：将字符串x重复y次            例：select repeat('abc',3);    返回值为 abc abc abc
space(x)   ：返回x个空格          例：select length(space(3));       返回值 为3，因为返回的空格不可见，所以增添长度函数配合使用
replace(x,y,z)   ：将字符串z替代字符串x中的字符串y         例：select replace('abcdf','ab','cd');       返回值为cdcdf  ；将cd 替换字符串abcdf 中的 ab
strcmp(x,y)   ：比较x和y，返回的值可以为-1，0,1           例：select strcmp(4,5);     返回值为-1 ；(5,5)  返回值为0  ； (6,5)  返回值为1
substring(x,y,z)   ：获取从字符串x中的第y个位置开始长度为z的字符串           例：select substring('abcdefg',5,2);      返回值为ef   ；从字符串abcdefg的第5个位置开始取2个长度的字符
reverse(x)   ：将字符串x反转            例：select reverse('mnbv');       返回值为 vbnm


示例：
##length(x) ：
select length('');        返回值为0
select length(null);        返回值为null
select length('null');          返回值为4
select length(' abc ');         返回值为5

##trim()：
select length(trim(' abc '))         返回值为3             ####去头尾空格                  
select length(trim(' a bc '))         返回值为4            ####不去中间空格

##concat(x,y)：
select length(concat('abc','def'));         返回值为6

##space(x)：
select concat('a',space(3),'b');        返回值为 | a   b                    |          ####并不是很方便

日期时间函数

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(),curtime(),now();
+------------+-----------+---------------------+
| curdate()  | curtime() | now()               |
+------------+-----------+---------------------+
| 2020-10-18 | 15:34:31  | 2020-10-18 15:34:31 |
+------------+-----------+---------------------+

mysql> select month('2020-10-18'),week('2020-10-18'),hour('15:46');
+---------------------+--------------------+---------------+
| month('2020-10-18') | week('2020-10-18') | hour('15:46') |
+---------------------+--------------------+---------------+
|                  10 |                 42 |            15 |
+---------------------+--------------------+---------------+
 
mysql> select hour(now());                ####可以做函数嵌套
+-------------+
| hour(now()) |
+-------------+
|          15 |
+-------------+

mysql> select minute(now()),second(now());
+---------------+---------------+
| minute(now()) | second(now()) |
+---------------+---------------+
|            55 |            22 |
+---------------+---------------+

mysql> select dayofweek(now());              ####1表示星期日
+------------------+
| dayofweek(now()) |
+------------------+
|                1 |
+------------------+

mysql> select dayofweek(now()),dayofmonth(now()),dayofyear(now());            ####星期日，今月的第几天，今年的第几天
+------------------+-------------------+------------------+
| dayofweek(now()) | dayofmonth(now()) | dayofyear(now()) |
+------------------+-------------------+------------------+
|                1 |                18 |              292 |
+------------------+-------------------+------------------+

存储过程

存储过程的优点：
1、存储过程执行一次后，生成的二进制代码就驻留在缓冲区，之后如果再次调用的话，将直接掉用二进制代码
使得存储过程的执行效率和性能得到大幅提升。

2、存储过程是SQL语句加上控制语句的集合，有很强的灵活性，可以完成复杂的运算。

3、存储过程在服务器端，客户端调用时，直接在服务器端执行，客户端只是传输的调用语句，从而可以降低网络负载。

4、存储过程被创建后，可以多次重复调用，它将多条SQL封装到了一起，可随时针对SQL语句进行修改，不影响调用它的客户端。

5、存储过程可以完成所有的数据库操作，也可以通过编程的方式控制数据库的信息访问权限。

6、增强数据库的安全性

创建存储过程

存储过程语法格式：
create procedure 过程名([过程参数[....]]) 过程体                 ####过程体就是 begin到 end $$ 之间的内容

过程参数 ：in   、out  、 inout
格式：
(过程参数 参数名 类型)

要创建存储过程，必须要具有 create routine 权限

show procedure status;             ####查看数据库中有哪些存储过程
show create procedure 存储过程名         ####查看某个存储过程的具体信息


一、示例：（不带过程参数的）查看player表中三条数据

use 数据库名；

delimiter $$
create procedure playerRole()
begin
select id,name,level from player limit 3;
end $$
delimiter ;             ####delimiter后面加空格;
call playerRole();


二、示例：（带过程参数的）查看info表中的zhangsan这条纪录
use 数据库名；

mysql> desc info;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(5)      | NO   | PRI | NULL    |       |
| name  | varchar(50) | YES  |     |         |       |
| addr  | varchar(50) | NO   |     | NULL    |       |
| socre | varchar(48) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> delimiter $$
mysql> create procedure cok(in a varchar(50))
         -> begin
         -> select id,name,socre from info where name=a;
         -> end $$
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> call cok('zhangsan');            #####call cok('name字段下的某个名字');
+----+----------+-------+
| id | name     | socre |
+----+----------+-------+
| 12 | zhangsan | 100   |
+----+----------+-------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

创建存储过程中的传递参数的介绍

in 输入参数：表示调用者向过程传入值（传入值可以时字面量或变量）
out 输出参数：表示过程向调用者传出值（可以返回多个值）（传出值只能是变量）
inout 输入输出参数：既表示调用者向过程传入值，又表示过程向调用者传出值（值只能是变量）

##in 输入参数
示例：
mysql> delimiter $$
mysql> create procedure lll(in n int)
         -> begin
         -> select a;
         -> set a=5;
         -> select a;
         -> end $$
mysql> delimiter ;
mysql> set @a=10;
mysql> call lll(5);
+------+
| n    |
+------+
|    5 |
+------+
1 row in set (0.00 sec)

+------+
| n    |
+------+
|    5 |
+------+
1 row in set (0.00 sec)

以上可以看出，a 在存储过程中被修改，但并不影响 a 的值，因为前者为局部变量，后者为全局变量




##out 输出参数
示例：
mysql> delimiter $$
mysql> create procedure lt(out f int)
         -> begin
         -> select f;
         -> set a=5;
         -> select f;
         -> end $$
mysql> delimiter ;
mysql> call lt(@f);           ####用 out 过程参数 call的时候必须要带变量符号@
+------+
| f    |
+------+
| NULL |                                ####因为out是向调用者输出参数，不接收输入的参数，所以存储过程 f 为 NULL
+------+
1 row in set (0.00 sec)

+------+
| f    |
+------+
|    5 |
+------+
1 row in set (0.00 sec)




##inout 输入输出参数
示例：
mysql> delimiter $$
mysql> create procedure utf(inout ppd int)
         -> begin
         -> select ppd;
         -> set ppd=5;
         -> select ppd;
         -> end $$
mysql> delimiter ;
mysql> set @ppd=1;            ####用 inout 过程参数时，它可以接收输入的参数，不过需要给它定义一个全局变量
mysql> call utf(@ppd);
 +------+
|   ppd  |
+------+
|      1    |
+------+
1 row in set (0.00 sec)

+------+
|  ppd  |
+------+
|     5    |
+------+
1 row in set (0.00 sec)

存储过程中的过程体的判断语句

一、条件语句

1）if-then；else 语句
示例：
mysql> delimiter $$
mysql> create procedure btup(in a int)
         -> begin
         -> declare var int;            ####在存储过程内申明变量，类型（局部变量，只作用在内部）
         -> set var=a+1；           ###给 var 赋值
         -> if var=0 then             ####如果 var=0，则插入17
         -> insert into t values(17);
         -> end  if;
         -> if a=0 then              ####如果 a=0，则修改 t 表中 s1+1
         -> update t set s1=s1+1;
         -> else                    ####否则修改 t 表中 s1+2
         -> update t set s1=s1+2;
         -> end if;
         -> end $$
mysql> delimiter ;



2）case 语句
示例：
mysql> delimiter $$
mysql> create procedure potp(in a int)
         -> begin
         -> declare var int;
         -> set var=a+1;
         -> case var                   ####case var这个变量
         -> when 0 then             ####当0时，插入17；当1时插入18；否则插入19
         -> insert into t values(17)
         -> when 1 then
         -> insert into t values(18)
         -> else
         -> insert into t values(19)
         -> end case;
         -> end $$
mysql> delimiter ;

二、循环语句

1）while ... end while
示例：
mysql> delimiter $$
mysql> create procedure dftp(in a int)
         -> begin
         -> declare b int;
         -> set b=0;
         -> while a<10 do
         -> insert into t values(a,b)
         -> set a=a+1                 ####不设置这个变量就死循环
         -> set b=b+2
         -> end while;
         -> end $$
mysql> delimiter ;
mysql> call dftp(1);

修改存储过程

存储过程的修改分为特征的修改和业务内容的修改

语法结构：
alter procedure 过程名(特征 ...)

存储过程内容的修改方法是通过删除原有存储过程，之后再以相同的名称创建新的存储过程

删除存储过程

语法结构：
drop procedure 过程名；
1、需要注意的是：存储过程名称后面没有参数列表，也没有括号。
2、在删除之前，必须确认该存储过程没有任何依赖关系，否则会导致与之关联的存储过程无法运行。

示例：
mysql> drop procedure cok;
Query OK, 0 rows affected (0.00 sec)

mysql> call cok('zhangsan');
ERROR 1305 (42000): PROCEDURE bbs.cok does not exist