MySQL支持的数据完整性有什么作用 mysql完全支持标准的sql语句

文章目录

• 引言
• 一、数据库函数

• 1、数学函数
• 2、聚合函数
• 3、字符串函数

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

• 4、日期时间函数

• 二、存储过程

• 1.概述
• 2.存储过程的优点：
• 修改存储过程
• 删除存储过程

引言

一、数据库函数

1、数学函数

• 数据库内存储的记录，经常要进行一系列的算术操作，所以 MySQL 支持很多数学函数。 常用的数学函数如表所示
• 把mysql 中的表导出为excel格式

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

示例:

#-6的绝对值 返回 x 的绝对值
mysql> mysql> select abs(-6);
+---------+
| abs(-6) |
+---------+
|       6 |
+---------+
1 row in set (0.00 sec)

#0-1的随机数（0<=x<1） 返回 0 到 1 的随机数
mysql> select rand();
+--------------------+
| rand()             |
+--------------------+
| 0.7320053854323973 |
+--------------------+
1 row in set (0.00 sec)

#也可以搭配运算符
mysql> select rand()*10;
+--------------------+
| rand()*10          |
+--------------------+
| 3.4525560061005467 |
+--------------------+
1 row in set (0.00 sec)

# 返回 x 除以 y 以后的余数 select mod(x,y);

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

#2的4次方 
mysql> select power(2,4);
+------------+
| power(2,4) |
+------------+
|         16 |
+------------+
1 row in set (0.00 sec)

#离3.79最近的整数
mysql> select round(3.79);
+-------------+
| round(3.79) |
+-------------+
|           4 |
+-------------+
1 row in set (0.00 sec)

#1.8666保留小数点后2位，这里会四舍五入
mysql> select round(1.8666,2);
+-----------------+
| round(1.8666,2) |
+-----------------+
|            1.87 |
+-----------------+
1 row in set (0.00 sec)



#返回平方根
mysql> select sqrt(4);
+---------+
| sqrt(4) |
+---------+
|       2 |
+---------+
1 row in set (0.00 sec)

mysql> select sqrt(5);
+------------------+
| sqrt(5)          |
+------------------+
| 2.23606797749979 |
+------------------+
1 row in set (0.00 sec)

#保留小数点后2位，但truncate函数不会四舍五入（截断）
mysql> select truncate(1.896,2);
+-------------------+
| truncate(1.896,2) |
+-------------------+
|              1.89 |
+-------------------+
1 row in set (0.00 sec)

#返回大于或等于5.2的最小整数
mysql> select ceil(5.2);
+-----------+
| ceil(5.2) |
+-----------+
|         6 |
+-----------+
1 row in set (0.01 sec)

#返回小于或等于5.2的最大整数
mysql> select floor(5.2);
+------------+
| floor(5.2) |
+------------+
|          5 |
+------------+
1 row in set (0.00 sec)

#返回最大值
mysql> select greatest(1,2,3);
+-----------------+
| greatest(1,2,3) |
+-----------------+
|               3 |
+-----------------+
1 row in set (0.00 sec)

#返回最小值
mysql> select least(1,2,3);
+--------------+
| least(1,2,3) |
+--------------+
|            1 |
+--------------+
1 row in set (0.00 sec)

2、聚合函数

MySQL 数据库函数中专门有一组函数是特意为库内记录求和或者对表中的数据进行集中概括而设计的，这些函数被称作聚合函数。

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

#返回分数的总和
mysql> select sum(score) from info;
+------------+
| sum(score) |
+------------+
|     662.00 |
+------------+
1 row in set (0.00 sec)

#返回分数字段的个数
mysql> select count(score) from info;
+--------------+
| count(score) |
+--------------+
|            8 |
+--------------+
1 row in set (0.00 sec)

#返回分数的最小值
mysql> select min(score) from info;
+------------+
| min(score) |
+------------+
|      70.00 |
+------------+
1 row in set (0.00 sec)

#返回分数的最大值
mysql> select max(score) from info;
+------------+
| max(score) |
+------------+
|      98.00 |
+------------+
1 row in set (0.00 sec)

#返回分数的平均值
mysql> select avg(score) from info;
+------------+
| avg(score) |
+------------+
|  82.750000 |
+------------+
1 row in set (0.00 sec)

3、字符串函数

字符串函数	描述
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 反转

length(x) 返回字符串 x 的长度

例子：
返回abcd的长度，空格也算一个字符

mysql> select length('abcd');
+----------------+
| length('abcd') |
+----------------+
|              4 |
+----------------+
1 row in set (0.00 sec)


mysql> select length('ab cd');
+-----------------+
| length('ab cd') |
+-----------------+
|               5 |
+-----------------+
1 row in set (0.01 sec)

trim() 返回去除格式的值

mysql> select trim('   sheng');
+------------------+
| trim('   sheng') |
+------------------+
| sheng            |
+------------------+
1 row in set (0.00 sec)

mysql> select '   sheng';
+----------+
| sheng    |
+----------+
|    sheng |
+----------+
1 row in set (0.00 sec)

concat(x,y) 将提供的参数 x 和 y 拼接成一个字符串

mysql> select concat('abc','def');
+---------------------+
| concat('abc','def') |
+---------------------+
| abcdef              |
+---------------------+
1 row in set (0.01 sec)


mysql> select concat('abc',' def');
+----------------------+
| concat('abc',' def') |
+----------------------+
| abc def              |
+----------------------+
1 row in set (0.00 sec)

还可以结合其他函数，如trim（将后面的函数删除格式）

mysql> select concat('abc',trim(' def'));
+----------------------------+
| concat('abc',trim(' def')) |
+----------------------------+
| abcdef                     |
+----------------------------+
1 row in set (0.00 sec)

upper(x) 将字符串 x 的所有字母变成大写字母

mysql> select upper('abc');
+--------------+
| upper('abc') |
+--------------+
| ABC          |
+--------------+
1 row in set (0.00 sec)

lower(x) 将字符串 x 的所有字母变成小写字母

mysql> select lower('ABC');
+--------------+
| lower('ABC') |
+--------------+
| abc          |
+--------------+
1 row in set (0.01 sec)

left(x,y) 返回字符串 x 的前 y 个字符

mysql> select left('abcdefg',3);
+-------------------+
| left('abcdefg',3) |
+-------------------+
| abc               |
+-------------------+
1 row in set (0.00 sec)

right(x,y) 返回字符串 x 的后 y 个字符

mysql> select right('abcdefg',3);
+--------------------+
| right('abcdefg',3) |
+--------------------+
| efg                |
+--------------------+
1 row in set (0.00 sec)

把字符串的前3个字母和后3个字母拼接起来

mysql> select concat(left('abcdefg',3),right('abcdefg',3));
+----------------------------------------------+
| concat(left('abcdefg',3),right('abcdefg',3)) |
+----------------------------------------------+
| abcefg                                       |
+----------------------------------------------+
1 row in set (0.00 sec)

repeat(x,y) 将字符串 x 重复 y 次

mysql> select repeat('abc',2);
+-----------------+
| repeat('abc',2) |
+-----------------+
| abcabc          |
+-----------------+
1 row in set (0.00 sec)

space(x) 返回 x 个空格

mysql> select length(space(3));
+------------------+
| length(space(3)) |
+------------------+
|                3 |
+------------------+
1 row in set (0.00 sec)

replace(x,y,z) 将字符串 z 替代字符串 x 中的字符串 y

mysql> select replace('hello','ll','aa');
+----------------------------+
| replace('hello','ll','aa') |
+----------------------------+
| heaao                      |
+----------------------------+
1 row in set (0.00 sec)

strcmp(x,y) 比较 x 和 y，返回的值可以为-1,0,1

比较17和18，小于返回-1，等于返回0，大于返回1，只会返回这3个值，它是比较第一位不同的数字

mysql> select strcmp(17,18);
+---------------+
| strcmp(17,18) |
+---------------+
|            -1 |
+---------------+
1 row in set (0.00 sec)

mysql> select strcmp(18,18);
+---------------+
| strcmp(18,18) |
+---------------+
|             0 |
+---------------+
1 row in set (0.00 sec)

mysql> select strcmp(19,18);
+---------------+
| strcmp(19,18) |
+---------------+
|             1 |
+---------------+
1 row in set (0.00 sec)

mysql> select strcmp(17,1);
+--------------+
| strcmp(17,1) |
+--------------+
|            1 |
+--------------+
1 row in set (0.00 sec)

substring(x,y,z) 获取从字符串 x 中的第 y 个位置开始长度为 z 的字符串

• 返回从字符串中第三个字符开始的4个字符

mysql> select substring('abcdefg',3,4);
+--------------------------+
| substring('abcdefg',3,4) |
+--------------------------+
| cdef                     |
+--------------------------+
1 row in set (0.00 sec)

reverse(x) 将字符串 x 反转

mysql> select reverse('gfedcba');
+--------------------+
| reverse('gfedcba') |
+--------------------+
| abcdefg            |
+--------------------+
1 row in set (0.00 sec)

• 返回字符串的前3个字符，然后反转输出

mysql> select reverse(left('gfedcba',3));
+----------------------------+
| reverse(left('gfedcba',3)) |
+----------------------------+
| efg                        |
+----------------------------+
1 row in set (0.00 sec)

• 先将字符串反转，再输出前3个字符

mysql> select left(reverse('gfedcba'),3);
+----------------------------+
| left(reverse('gfedcba'),3) |
+----------------------------+
| abc                        |
+----------------------------+
1 row in set (0.00 sec)

4、日期时间函数

字符串函数	描述
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()  |
+------------+
| 2021-10-29 |
+------------+
1 row in set (0.00 sec)

• 返回当前时间

mysql> select curtime();
+-----------+
| curtime() |
+-----------+
| 19:39:27  |
+-----------+
1 row in set (0.00 sec)

• 返回当前完整时间

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2021-10-29 19:39:34 |
+---------------------+
1 row in set (0.00 sec)

• 返回月份

mysql> select month(now());
+--------------+
| month(now()) |
+--------------+
|           10 |
+--------------+
1 row in set (0.00 sec)

• 返回当前日期是一年中的第几周

mysql> select week(now());
+-------------+
| week(now()) |
+-------------+
|          43 |
+-------------+
1 row in set (0.00 sec)

• 返回当前时间的小时

mysql> select hour(now());
+-------------+
| hour(now()) |
+-------------+
|          19 |
+-------------+
1 row in set (0.00 sec)

• 返回当前时间的分钟

mysql> select minute(now());
+---------------+
| minute(now()) |
+---------------+
|            41 |
+---------------+
1 row in set (0.00 sec)

• 返回当前时间的秒

mysql> select second(now());
+---------------+
| second(now()) |
+---------------+
|            17 |
+---------------+
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)

二、存储过程

1.概述

• 前面学习的 MySQL 相关知识都是针对一个表或几个表的单条 SQL 语句，使用这样的SQL 语句虽然可以完成用户的需求，
• 但在实际的数据库应用中，有些数据库操作可能会非常复杂，可能会需要多条 SQL 语句一起去处理才能够完成，
• 这时候就可以使用存储过程， 轻松而高效的去完成这个需求，有点类似shell脚本里的函数

2.存储过程的优点：

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

语法：
CREATE PROCEDURE <过程名> ( [过程参数[,…] ] ) <过程体>
[过程参数[,…] ] 格式
<过程名>：尽量避免与内置的函数或字段重名
<过程体>：语句
[ IN | OUT | INOUT ] <参数名><类型>

• 示例（不带参数的创建）

##创建存储过程 ##
DELIMITER $$				#将语句的结束符号从分号;临时改为两个$$(可以自定义)
CREATE PROCEDURE Proc()					#创建存储过程，过程名为Proc，不带参数
-> BEGIN								#过程体以关键字 BEGIN 开始
-> create table mk (id int (10), name char(10),score int (10));
-> insert into mk values (1, 'wang',13);
-> select * from mk;			        #过程体语句
-> END $$								#过程体以关键字 END 结束
DELIMITER ;								#将语句的结束符号恢复为分号

##调用存储过程 ##
CALL Proc();
I    存储过程的主体都分,被称为过程体
II   以BEGIN开始，以END结束，若只有一条sQL语句，则可以省略BEGIN-END
III  以DELIMITER开始和结束
mysgl>DEL工M工TER $$      $$是用户自定义的结束符 
省略存储过程其他步骤
mysql>DELIMITER ;  分号前有空格


##查看存储过程 ##
格式：
SHOW CREATE PROCEDURE [数据库.]存储过程名;		#查看某个存储过程的具体信息

mysql> show create procedure proc\G
*************************** 1. row ***************************
           Procedure: proc
            sql_mode: PIPES_AS_CONCAT,ANSI_QUOTES,NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_D_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
    Create Procedure: CREATE DEFINER="root"@"localhost" PROCEDURE "proc"()
begin
select id,name from info;
update info set score='10' where name='tiqnai';
end
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_general_ci
1 row in set (0.00 sec)

#查看存储过程
SHOW PROCEDURE STATUS 

#查看指定存储过程信息
mysql> SHOW PROCEDURE STATUS like '%proc%'\G
*************************** 1. row ***************************
                  Db: info
                Name: proc
                Type: PROCEDURE
             Definer: root@localhost
            Modified: 2021-07-15 05:45:21
             Created: 2021-07-15 05:45:21
       Security_type: DEFINER
             Comment: 
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_general_ci
1 row in set (0.01 sec)

• 存储过程的参数

参数	说明
IN 输入参数：	表示调用者向过程传入值（传入值可以是字面量或变量）
OUT输出参数：	表示过程向调用者传出值(可以返回多个值)（传出值只能是变量）
INOUT 输入输出参数：	既表示调用者向过程传入值，又表示过程向调用者传出值（值只能是变量）即表示调用者向过程传入值，又表示过程向调用者传出值（只能是变量)

mysql> delimiter $$
mysql> create procedure proc()				//行参
    -> begin
    -> create table mk (id int (10),name char(10),score int(10));
    -> insert into mk values(1,'wang',13);
    -> select * from mk;
    -> end $$
Query OK, 0 rows affected (0.04 sec)		
mysql> delimiter ;
mysql> call proc();			//实参
+------+------+-------+
| id   | name | score |
+------+------+-------+
|    1 | wang |    13 |
+------+------+-------+
1 row in set (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

修改存储过程

语法：

ALTER PROCEDURE <过程名>[<特征>... ]
例：
ALTER PROCEDURE GetRole MODIFIES SQL DATA SQL SECURITY INVOKER;

参数解释：
MODIFIES sQLDATA:表明子程序包含写数据的语句
SECURITY:安全等级
invoker:当定义为INVOKER时，只要执行者有执行权限，就可以成功执行。

删除存储过程

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

DROP PROCEDURE IF EXISTS Proc