查询表达式

当删除一条id=6的记录，最后的一条记录id=8，再插入一条记录id=9

DELIMITER //     修改结束符分号；为//

CREATE FUNCTION adduser(username VARCHAR(20)) 
->RETURNS INT UNSIGNED

->BEGINE

->INSERT test(username) VALUES(username);

->RETURN LAST_INSERT_ID();

->END

-> //

删除函数：DROP FUNCTION f1;

SET GLOBAL log_bin_trust_function_creators = 1;

CREATE FUNCTION f2(num1 SMALLINT UNSIGNED,num2 SMALLINT UNSIGNED)
    -> RETURNS FLOAT(10,2) UNSIGNED
    -> RETURN (num1+num2)/2;

SELECT f2(1,2);
+---------+
| f2(1,2) |
+---------+
|    1.50 |
+---------+

 CREATE FUNCTION f1() RETURNS VARCHAR(30)
 -> NO SQL
 -> RETURN DATE_FORMAT(NOW(),'%Y年%m月%d日 %H点:%i分:%s秒');

在 MySQL中，函数的类别有：
1.CONTAINS SQL 包含了SQL语句（函数默认为这种类型）
2.READS SQL DATA 只是读取数据，不修改数据
3.NO SQL 没有SQL语句（当然也不会修改数据）
4.DETERMINISTIC 确定的
5.MODIFIES SQL DATA 将修改数据

用户自定义函数UDF

函数一定有名字，后面跟小括号，括号内部是参数，执行函数后会得到返回值。

参数和返回值之间没有必然的内在的联系，所有函数都有返回值，但并不一定有参数，比如显示当前数据库版本的VERSION()函数。

参数的数量理论上不能超过1024个。

创建自定义函数：CREATE FUNCTION function_name RETURNS { STRING | INTEGER | REAL | DECIMAL } routine_body;

关于函数体

1. 函数体有合法的SQL语法构成

2. 函数体可以是简单的SELECT或INSERT语句

3. 函数体如果为复合结构则使用BEGIN...END语句

4. 复合结构可以包含声明，循环，控制结构

SELECT MD5('admin');返回32位的字符串

SELECT PASSWORD('admin');新版中，该条语句报错，已废弃password字段和password()函数，改为用SELECT SHA('admin');

 SELECT SHA('admin');
+------------------------------------------+
| SHA('admin')                             |
+------------------------------------------+
| d033e22ae348aeb5660fc2140aec35850c4da997 |
+------------------------------------------+

聚合函数典型特点：只有一个返回值。需要配合数据表使用

AVG()       平均值

 SELECT AVG(goods_price) AS avg_price FROM tdb_goods;返回所有价格的平均值

COUNT()   计数

SELECT COUNT(goods_id) AS counts FROM tdb_goods;返回所有记录的条数

MAX()       最大值

MIN()         最小值

SUM()         求和

SELECT CONNECTION_ID();返回当前连接的ID，也是线程的ID。 

SELECT DATABASE();返回当前打开的数据库的名称。

SELECT LAST_INSERT_ID();返回最后插入的记录的ID号。必须存在自动编号的id。同时写入多条记录时，只返回第一条写入记录的ID号，不能得到后续记录的ID。

SELECT USER();返回当前用户的名称。

SELECT VERSION();返回当前MySQL数据库的版本号。

SELECT NOW(); 返回当前日期和时间，年-月-日 时：分：秒

SELECT CURDATE(); 返回当前日期，年-月-日

SELECT CURTIME(); 返回当前时间，时：分：秒

SELECT DATE_ADD('2020-4-5', INTERVAL 365 DAY);返回2021-4-5，可以加-365 DAY，1 YEAR，3 WEEK，对日期进行变化

SELECT DATEDIFF('2020-4-5','2022-3-16');返回710，两个日期的差值

SELECT DATE_FORMAT('2020-4-5','%m/%d/%Y');返回04/05/2020，对日期进行格式化

[NOT] BETWEEN...AND...  [不]在范围之内

例如：SELECT 15 BETWEEN 1 AND 22; 返回1。

SELECT 35 BETWEEN 1 AND 22; 返回0。

表示的是一个闭合区间，[1,22]。

[NOT] IN()  [不]在取值范围内

例如：SELECT 10 IN(5,10,15,20);返回1

IS [NOT] NULL  [不]为空

NULL为空，空字符''不为空，0不为空

SELECT CEIL(3.01); 返回：4，进一取整，向上取整

SELECT FLOOR(3.97);返回：3，舍一取整，向下取整

SELECT 3/4;返回：0.75

SELECT 3 DIV 4;返回：0，整数除法

SELECT 5 MOD 3;返回：2，取余数（取模），MOD可以写成%，也表示取余数，被除数也可为小数

SELECT POWER(3,3);返回：27，3的3次幂运算

SELECT ROUND(3.652,2); 返回：3.66，四舍五入，保留小数点后留两位

SELECT TRUNCATE(125.89,1); 返回：125.8，直接截断小数位数，不进行四舍五入。

SELECT CONCAT('A','B'); 返回：AB，连接字符。

SELECT CONCAT(first_name,last_name) AS full_name FROM test;把表test中的first_name和last_name两列连接起来，显示为full_name一列。

CONCAT_WS('|','A','B','C');返回：A|B|C，使用指定分隔符连接字符串，至少需要三个参数。

CONCAT_WS('|',id,username) FROM test;把表test中的id和username两列用分隔符|连接起来，显示为一列。

SELECT FORMAT(12560.75,1);返回：12,560.8，数字格式化

SELECT LOWER('MySQL');返回：mysql，全部转换成小写字母

SELECT UPPER('MySQL');返回：MYSQL，全部转换成大写字母

SELECT LEFT('MySQL',2);返回：My，获取从左侧第一位为起始的，指定长度的字符

SELECT RIGHT('MySQL',2);返回：QL，获取从右侧第一位即最后一位为起始，往前数指定长度的字符

SELECT LENGTH('My SQL'); 返回：6， 获取字符长度，空格也算

LTRIM()  删除第一个字符之前的空格

RTRIM()  删除最后一个字符之后的空格

TRIM()  删除前导和后续空格，但是无法删除字符串中字符之间的空格

SELECT REPLACE('??My??SQL???','?','');返回：MySQL，将字符?替换成空

SELECT SUBSTRING('MySQL',1,2);返回：My，从第一位开始截取，取两位

SELECT SUBSTRING('MySQL',3); 返回：SQL，代表从第三位开始截取，取到结尾

SELECT SUBSTRING('MySQL',-3);返回：SQL，最后一位为-1，往前数到-3为起始位置，取到结尾

SELECT SUBSTRING('MySQL',-3,2); 返回：SQ，第-3位为起始，取两个字符长度

SELECT SUBSTRING('MySQL',-3,-1);  返回值为空，MySQL中不允许这种写法，只能起始位为负值，长度不能为负值

SELECT 'MySQL' LIKE 'MS%'; 返回值为1代表true，0代表false。

为了查找记录中first_name包含%的记录：

SELECT * FROM test WHERE username LIKE '%%%';MySQL中认为三个百分号都是通配符，并不是我们所认为的第二个为字符串，其他两个为通配符，所以不能找到正确的记录

SELECT * FROM test WHERE username LIKE '%1%%' ESCAPE '1'; 代表查找用户名中包含%字符的结果，1后边的%不需要再进行解析，直接认为是字符%，其中1可以为任意的一个字符。

通配符：百分号%代表任意一个或者多个字符，下划线_代表任意一个字符。

子查询是指出现在select语句中的，必须出现在小括号内。

子查询的外层可以是select语句，insert语句、update语句及delete语句。

子查询中可以包含多个关键字或条件，如group by、order by、limit以及相关函数等。

使用子查询的环境：

1. 比较运算符引发的子查询

2. in或not in引发的子查询

3. exist或not exist引发的子查询。
   

delete t1 from tdb_goods as t1  //从本表中删除,将tdb_goods看做t1
left join (select goods_id,goods_name from tdb_goods group by goods_name having count(goods_name)>=2) as t2  //子查询得到重复条目
on t1.goods_name=t2.goods_name  //t1和t2的连接条件
where t1.goods_id>t2.goods_id;  //删除id号较大的条目

删除表中名字相同的记录，删除id较大的记录。