mysql 字段中不含有某个字符 mysql不包含函数

自定义函数

• 函数可以无参数，但必须有返回值

函数创建\删除\调用：

-- 创建函数
CREATE [AGGREGATE] FUNCTION function_name(parameter_name type,[parameter_name type,...])
RETURNS {STRING|INTEGER|REAL}
runtime_body
-- 翻译
CREATE FUNCTION 函数名称(参数列表)
RETURNS 返回值类型
函数体
-- 删除函数
DROP FUNCTION function_name;
-- 调用函数
SELECT function_name(parameter_value,...);

函数体：

在函数体中,如果包含多条语句,我们需要把多条语句放到BEGIN...END语句块中

CREATE FUNCTION deleteById(targetId SMALLINT UNSIGNED) 
RETURNS VARCHAR(20) 
BEGIN
DELETE FROM table_name WHERE id = targetId;
RETURN (SELECT COUNT(*) FROM table_name);
END

修改默认的结束符语法：

DELIMITER //
CREATE FUNCTION deleteById(uid SMALLINT UNSIGNED) 
RETURNS VARCHAR(20) 
BEGIN
DELETE FROM son WHERE id = uid;
RETURN (SELECT COUNT(id) FROM son);
END//

函数sql语法

• 声明函数变量

• 作用域为 BEGIN...END 代码块中

DECLARE var_name[,varname]...date_type [DEFAULT VALUE];
DECLARE 变量1[,变量2,... ]变量类型 [DEFAULT 默认值];
-- example
DECLARE a, b SMALLINT UNSIGNED DEFAULT 10;

• 变量赋值

-- 赋已知的值
SET parameter_name = value[,parameter_name = value...]
-- example
SET  a = x, b = y;
-- 赋未知值
SELECT INTO parameter_name
-- example
SELECT COUNT(id) FROM tdb_name INTO x;
SELECT group_concat(pid) INTO sTempPar FROM treenodes where pid<>id

• 声明用户变量(可以理解成全局变量)

• 作用域：作用域只为当前用户的客户端有效

SET @allParam = 100;
SELECT @allParam;

• 流程控制

MySQL中可以使用IF语句、CASE语句、LOOP语句、LEAVE语句、ITERATE语句、REPEAT语句和WHILE语句来进行流程控制。

IF语句：

• search_condition参数表示条件判断语句；
• statement_list参数表示不同条件的执行语句

IF search_condition
THEN 
	statement_list 
[ELSEIF search_condition THEN statement_list] ... 
[ELSE statement_list] 
END IF

Example：

IF age > 20 
THEN 
	SET @count1=@count1+1;  
ELSEIF age = 20 
THEN 
	SET @count2=@count2+1;  
ELSE 
	SET @count3=@count3+1;  
END IF;

CASE语句：

CASE语句也用来进行条件判断，其可以实现比IF语句更复杂的条件判断

• case_value参数表示条件判断的变量；
• when_value参数表示变量的取值；
• statement_list参数表示不同when_value值的执行语句

CASE case_value 
WHEN when_value 
THEN 
	statement_list 
[WHEN when_value THEN statement_list] ... 
[ELSE statement_list] 
END CASE

example

CASE age 
WHEN 20 
THEN 
	SET @count1=@count1+1; 
ELSE 
	SET @count2=@count2+1; 
END CASE;

CASE语句2：

• search_condition参数表示条件判断语句；
• statement_list参数表示不同条件的执行语句；

CASE 
WHEN search_condition THEN statement_list 
[WHEN search_condition THEN statement_list] ... 
[ELSE statement_list] 
END CASE

example

CASE 
WHEN age=20 
THEN 
	SET @count1=@count1+1; 
ELSE 
	SET @count2=@count2+1; 
END CASE;

LOOP语句 LEAVE语句：

LOOP语句可以使某些特定的语句重复执行，实现一个简单的循环。

但是LOOP语句本身没有停止循环的语句，必须是遇到LEAVE语句等才能停止循环。

add_num: LOOP 
SET @count=@count+1; 
IF @count=100 THEN 
	LEAVE add_num;
END LOOP add_num;

ITERATE语句：

ITERATE语句也是用来跳出循环的语句。但是，ITERATE语句是跳出本次循环，然后直接进入下一次循环。

ITERATE语句只可以出现在LOOP、REPEAT、WHILE语句内。

add_num: LOOP 
SET @count=@count+1;
IF @count=100 THEN 
	LEAVE add_num;
ELSE IF MOD(@count,3)=0 
THEN 
	ITERATE add_num; 
SELECT * FROM employee;
END LOOP add_num;

REPEAT语句：

REPEAT语句是有条件控制的循环语句。当满足特定条件时，就会跳出循环语句

[begin_label:] REPEAT 
statement_list 
UNTIL search_condition 
END REPEAT [end_label]

example

REPEAT 
SET 
	@count=@count+1; 
UNTIL @count=100 
END REPEAT ;

WHILE语句：

WHILE语句也是有条件控制的循环语句。但WHILE语句和REPEAT语句是不一样的。

WHILE语句是当满足条件时，执行循环内的语句。

[begin_label:] WHILE search_condition DO 
statement_list 
END WHILE [end_label]

example

WHILE @count<100 DO 
SET @count=@count+1; 
END WHILE ;

MySQL常用内置函数

字符串

1、字符串函数

-- ASCII('a')：返回ASCII码值，空串返回0
mysql> SELECT ASCII('a');
+------------+
| ASCII('a') |
+------------+
|         97 |
+------------+
1 row in set (0.01 sec)

-- CONV(n, from_base, to_base):对数字n进制转换,并转换为字串返回(任何参数为null时返回null,进制范围为2-36进制,当to_base是负数时n作为有符号数否则作无符号数,conv以64位点精度工作)
mysql> SELECT CONV(6, 10, 2);
+----------------+
| CONV(6, 10, 2) |
+----------------+
| 110            |
+----------------+
1 row in set (0.00 sec)

-- CONCAT(str1,str2,...):把参数连成一个长字符串并返回(任何参数是null时返回null)  
mysql> SELECT CONCAT('aa', 'bb', 'cc');
+--------------------------+
| CONCAT('aa', 'bb', 'cc') |
+--------------------------+
| aabbcc                   |
+--------------------------+
1 row in set (0.00 sec)

-- length(str):字符串长度
mysql> SELECT LENGTH('MySQL');
+-----------------+
| LENGTH('MySQL') |
+-----------------+
|               5 |
+-----------------+
1 row in set (0.00 sec)

-- locate(substr,str):返回字符串substr在字符串str第一次出现的位置(str不包含substr时返回0)
-- locate(substr,str,pos):返回substr在字符串str的第pos个位置起第一次出现的位置(str不包含substr时返回0)
mysql> SELECT LOCATE('y', 'MySQL');
+----------------------+
| LOCATE('y', 'MySQL') |
+----------------------+
|                    2 |
+----------------------+
mysql> SELECT LOCATE('y', 'MySQL', 3);
+-------------------------+
| LOCATE('y', 'MySQL', 3) |
+-------------------------+
|                       0 |
+-------------------------+

-- lpad(str,len,padstr):用字符串padStr填充str左侧,直到长度达到len
-- rpad(str,len,padstr):用字符串padStr填充str右侧,直到长度达到len
mysql> SELECT RPAD('MySQL', 10, 'x');
+------------------------+
| RPAD('MySQL', 10, 'x') |
+------------------------+
| MySQLxxxxx             |
+------------------------+
1 row in set (0.00 sec)

mysql> SELECT LPAD('MySQL', 10, 'x');
+------------------------+
| LPAD('MySQL', 10, 'x') |
+------------------------+
| xxxxxMySQL             |
+------------------------+
1 row in set (0.00 sec)

-- left(str,len):返回字符串str的左端len个字符
-- right(str,len):返回字符串str的右端len个字符
-- substring(str,pos,len):返回字符串str的位置pos起len个字符mysql
-- substring(str,pos):返回字符串str的位置pos起的一个子串
mysql> SELECT LEFT('MySQL', 3);
+------------------+
| LEFT('MySQL', 3) |
+------------------+
| MyS              |
+------------------+
1 row in set (0.00 sec)

mysql> SELECT RIGHT('MySQL', 3);
+-------------------+
| RIGHT('MySQL', 3) |
+-------------------+
| SQL               |
+-------------------+
1 row in set (0.00 sec)

mysql> SELECT SUBSTRING('MySQL', 2, 2);
+--------------------------+
| SUBSTRING('MySQL', 2, 2) |
+--------------------------+
| yS                       |
+--------------------------+
1 row in set (0.00 sec)

mysql> SELECT SUBSTRING('MySQL', 2);
+-----------------------+
| SUBSTRING('MySQL', 2) |
+-----------------------+
| ySQL                  |
+-----------------------+
1 row in set (0.00 sec)

-- trim([[both | leading | trailing] [remstr] from] str):返回前缀或后缀remstr被删除了的字符串str(位置参数默认both,remstr默认值为空格)  
mysql> SELECT TRIM('               bar        ');
+------------------------------------+
| TRIM('               bar        ') |
+------------------------------------+
| bar                                |
+------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT TRIM(LEADING ' ' FROM '               bar        ');
+-----------------------------------------------------+
| TRIM(LEADING ' ' FROM '               bar        ') |
+-----------------------------------------------------+
| bar                                                 |
+-----------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT TRIM(LEADING 'x' FROM 'xxxxxxxxxxfooxxxx');
+--------------------------------------------+
| TRIM(LEADING 'x' FROM 'xxxxxxxxxxfooxxxx') |
+--------------------------------------------+
| fooxxxx                                    |
+--------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT TRIM(TRAILING 'x' FROM 'xxxxxxxxxxfooxxxx');
+---------------------------------------------+
| TRIM(TRAILING 'x' FROM 'xxxxxxxxxxfooxxxx') |
+---------------------------------------------+
| xxxxxxxxxxfoo                               |
+---------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT TRIM(BOTH 'x' FROM 'xxxxxxxxxxfooxxxx');
+-----------------------------------------+
| TRIM(BOTH 'x' FROM 'xxxxxxxxxxfooxxxx') |
+-----------------------------------------+
| foo                                     |
+-----------------------------------------+
1 row in set (0.00 sec)

-- replace(str,source_str,target_str):用字符串to_str替换字符串str中的子串from_str并返回
mysql> SELECT REPLACE('MySQL', 'S', 'A');
+----------------------------+
| REPLACE('MySQL', 'S', 'A') |
+----------------------------+
| MyAQL                      |
+----------------------------+
1 row in set (0.00 sec)

-- reverse(str):颠倒字符串顺序并返回
mysql> SELECT REVERSE('MySQL');
+------------------+
| REVERSE('MySQL') |
+------------------+
| LQSyM            |
+------------------+
1 row in set (0.00 sec)

-- find_in_set(str,strlist):返回str在字符串集strlist中的序号(任何参数是null则返回
-- null,如果str没找到返回0,参数1包含","时工作异常)  
mysql> SELECT FIND_IN_SET('3', '1,2,3,4,5,6');
+---------------------------------+
| FIND_IN_SET('3', '1,2,3,4,5,6') |
+---------------------------------+
|                               3 |
+---------------------------------+
1 row in set (0.00 sec)

2、数学函数

-- abs(n):绝对值
mysql> SELECT ABS(-12);
+----------+
| ABS(-12) |
+----------+
|       12 |
+----------+
1 row in set (0.00 sec)

-- FLOOR(1.23): 1
-- CEILING(1.23): 2
-- ROUND(n,d):返回n的四舍五入值,保留d位小数(d的默认值为0)
mysql> SELECT FLOOR(1.23);
+-------------+
| FLOOR(1.23) |
+-------------+
|           1 |
+-------------+
1 row in set (0.00 sec)

mysql> SELECT CEILING(1.23);
+---------------+
| CEILING(1.23) |
+---------------+
|             2 |
+---------------+
1 row in set (0.00 sec)

mysql> SELECT ROUND(1.23,1);
+---------------+
| ROUND(1.23,1) |
+---------------+
|           1.2 |
+---------------+
1 row in set (0.00 sec)

-- EXP(n):返回e的n次方
-- LOG(n):返回n的自然对数 
-- LOG10(n):10为底的对数
-- pow(x,y):返回x的y次幂
-- sqrt(n):返回非负数n的平方根  
-- pi():返回圆周率   
-- rand(n):返回0-1之间随机浮点值
mysql> SELECT ROUND(RAND() * 100, 0);
+------------------------+
| ROUND(RAND() * 100, 0) |
+------------------------+
|                     33 |
+------------------------+
1 row in set (0.01 sec)

mysql> SELECT ROUND(RAND() * 100, 0);
+------------------------+
| ROUND(RAND() * 100, 0) |
+------------------------+
|                     64 |
+------------------------+
1 row in set (0.00 sec)

3、时间函数

-- dayofweek(date):返回日期date是星期几(1=星期天,2=星期一,……7=星期六,odbc标准)
-- dayofmonth(date):返回date是一月中的第几日(在1到31范围内)
-- dayofyear(date):返回date是一年中的第几日(在1到366范围内) 
-- month(date):返回date中的月份数值
-- dayname(date):返回date是星期几(按英文名返回) 
-- monthname(date):返回date是几月(按英文名返回)
-- week(date,first):返回date是一年的第几周(first默认值0,first取值1表示周一是周的开始,0从周日开始)
-- quarter(date):返回date是一年的第几个季度
-- year(date):返回date的年份(范围在1000到9999)  
-- hour(time):返回time的小时数(范围是0到23) 
-- minute(time):返回time的分钟数(范围是0到59) 
-- second(time):返回time的秒数(范围是0到59) 
-- now():sysdate():current_timestamp():当前时间
mysql> SELECT NOW();
+---------------------+
| NOW()               |
+---------------------+
| 2019-03-18 16:48:23 |
+---------------------+
1 row in set (0.00 sec)

mysql> SELECT SYSDATE();
+---------------------+
| SYSDATE()           |
+---------------------+
| 2019-03-18 16:48:38 |
+---------------------+
1 row in set (0.00 sec)

mysql> SELECT CURRENT_TIMESTAMP();
+---------------------+
| CURRENT_TIMESTAMP() |
+---------------------+
| 2019-03-18 16:48:59 |
+---------------------+
1 row in set (0.01 sec)

mysql> SELECT CURRENT_TIMESTAMP() + 0;
+-------------------------+
| CURRENT_TIMESTAMP() + 0 |
+-------------------------+
|          20190318164904 |
+-------------------------+
1 row in set (0.00 sec)