Mysql常规使用，函数转换字符集,@的使用及存储过程等记录

NaselloSea

已于 2022-05-05 15:07:07 修改

阅读量911

点赞数

分类专栏：易忘记录文章标签： mysql 数据库 linux

于 2021-09-17 15:02:54 首次发布

本文链接：https://blog.csdn.net/weixin_44343658/article/details/120333570

版权

易忘记录专栏收录该内容

9 篇文章 1 订阅

订阅专栏

Linux中导出指定库表条件的数据：mysqldump -u root -p 库名 表名 --where=‘条件语句’ > 自定义指定路径及输出文件名 （执行后，需要输入mysql用户密码）比如：mysqldump -uroot -p platform statistic_app --where=’ CreateDate = “2022-01-15” ’ > /data/srv/ontest.sql

Linux中导入指定sql文件：登录mysql后可以直接使用 **source 需导入的文件全路径及文件名 **如果指定库导入则使用 **mysql -uroot -p 库名 < 需导入的文件全路径及文件名 **

授权远程访问执行：
grant all privileges on *.* to 'root用户名'@'%' identified by '用户密码';
之后可执行刷新权限：
flush privileges;

创建表结构示例：CREATE TABLE point_app_comm_user (
idx INT(32) NOT NULL AUTO_INCREMENT,
uid INT(32) NULL DEFAULT NULL COMMENT ‘用户ID’,
sourcetype VARCHAR(50) NULL DEFAULT NULL COMMENT ‘发布来源’ COLLATE ‘utf8_general_ci’,
sourcename VARCHAR(128) NULL DEFAULT NULL COMMENT ‘来源名称’ COLLATE ‘utf8_general_ci’,
createtime DATETIME NOT NULL COMMENT ‘创建时间’,
createdate DATE NOT NULL COMMENT ‘创建日期’,
inserttime DATETIME NULL DEFAULT CURRENT_TIMESTAMP COMMENT ‘插入时间’,
PRIMARY KEY (idx) USING BTREE,
INDEX createdate (createdate) USING BTREE
)
COMMENT=‘App NFT用户埋点表’
COLLATE=‘utf8_general_ci’
ENGINE=InnoDB
ROW_FORMAT=DYNAMIC
AUTO_INCREMENT=6;

删除库：DROP DATABASE 库名;

删除表：DROP TABLE IF EXISTS 表名;

修改表字段操作：

添加表字段：ALTER TABLE 表名 ADD COLUMN 字段名 VARCHAR(50) NULL DEFAULT NULL COMMENT ‘用户电话’ AFTER 指定已存在字段名 ;

删除表字段：ALTER TABLE 表名 ROP COLUMN 字段名;

修改表字段：ALTER TABLE 表名 CHANGE COLUMN 字段名 新起的字段名 VARCHAR(50) NULL DEFAULT NULL COMMENT ‘用户电话’ AFTER 指定已存在字段名 ;

基本表数据操作：

INSERT INTO 表名 (多参数字段名) VALUES (多字段插入值);
DELETE FROM 表名 WHERE 条件字段 IN (1,2,3);
UPDATE 表名 SET 字段名 = ‘更新值’ WHERE 条件字段 IN (1,2,3);
SELECT 多参数字段名 FROM 表名 WHERE 条件字段 IN (1,2,3);

查询时的常用运算符：

AND 和 OR : 逻辑与和逻辑或，不解释了；

NOT | ！：逻辑非，如字段名 IS NOT NULL；字段名 !=‘value’;

XOR ：逻辑异或，当任一个操作数为NULL时,返回为NULL，对于非NULL的操作数,两个的逻辑真假值相异，则返回结果为1，否则为0 ；

查询时的常用关键字与函数

（相关资源可以查看自有的各数据连接工具帮助文件或菜鸟教程）

COLLATE ：【可做字符集查询时转换】SELETE a.id, b.id FROM a, b WHERE a.xx = b.xx COLLATE utf8mb4_general_ci；当a表的xx的字符集与b表不一致时，出现–Illegal mix of collations错误；此时如果不想做数据表的字段修改，该方式解决查询字符集不匹配问题

CASE expr WHEN condition1 THEN result1 WHEN condition2 THEN result2 WHEN conditionN THEN resultN ELSE result END ：【同等与多条件分支，如switch语句】

IFNULL(r1,r2)：【如果r1为NULL，则返回r2】

COUNT()：【计数】 SELECT name,COUNT(*) FROM student;

DISTINCT()：【去重】 SELECT DISTINCT name, COUNT(DISTINCT age) FROM student GROUP BY name;

ASCII(s)：【s字符串的第一个字符的十进制ASCII码】 SELECT ASCII(‘dx’); -> 100

CONCAT(s1,s2…sn)：【拼接】 SELECT CONCAT(‘My’, ‘S’, ‘QL’); 如果拼接的有NULL ，结果将为NULL

CONCAT_WS(x, s1,s2…sn)：【后缀拼接】 SELECT CONCAT_WS(‘,’,‘First name’,NULL,‘Last Name’); -> ‘First name,Last Name’ 将忽略为NULL的字符串并继续拼接其后面的

CONVERT(expr,type)【转换格式】如：CONVERT(IFNULL(SUM(price),0),DECIMAL(10,2))) 或者：SELECT CONVERT(‘abc’ USING utf8);

FIELD(x,s1,s2…)：【S集合索引查找X(S索引从1开始，结果为0代表不存在)】SELECT FIELD(‘Gg’, ‘Aa’, ‘Bb’, ‘Cc’, ‘Dd’, ‘Ff’); -> 0

FIND_IN_SET(x,s)：【x在字符串s中匹配到的索引位置】SELECT FIND_IN_SET(‘b’,‘a,b,c,d’); -> 2

FORMAT(x,n)：【x进行格式化，留小数点后n位】SELECT FORMAT(12332.1,4); -> ‘12,332.1000’

INSERT（s1,x,e,s2）:【s2字符串替换s1字符串从x位置（索引从1开始）开始，长度为e的范围】SELECT INSERT(‘Quadratic’, 3, 100, ‘What’); -> ‘QuWhat’ 如果s1长度不够e的长度，相当于拿s2拼接到s1的x位置之后；

LCASE(s)与LOWER(s)：【s字符串小写与UCASE(s)和UPPER(s)大写字符串函数意义相反】SELECT LCASE(‘LCASE’) ; -> lcase

LENGTH()：【字符串计数】SELECT LENGTH(‘text’); -> 4

LPAD（s1,e,s2）：【s1起始位置追加s2，直到新字符串长度为e】 SELECT LPAD(‘hie’,2,‘io’); -> ‘hi’

REPEAT(s,n)：【s字符串重复n次】SELECT REPEAT(‘MySQL’, 3); -> ‘MySQLMySQLMySQL’

REPLACE(s,s1,s2)：【s2取代s字符串中的s1】SELECT REPEAT(‘MySQL’, ‘SQ’,‘.’); -> ‘My.L’

REVERSE(s)：【s字符串顺序颠倒一次】SELECT REVERSE(‘abc’); -> ‘cba’

LEFT(s,n)：【返回s字符串中的前n个字符与RIGHT(s,n)相反】

LTRIM(s)：【去掉s字符串左边（开始处）的所有空格，与RTRIM(s)结果相反，而TRIM(s)是去掉左右两边的所有空格】

SUBSTR(s, d, e)：【字符串 s 的 d位置截取长度为 e 的子字符串,d为负数则反向截取，e不指定,则截取到s结尾】SELECT SUBSTRING(‘Quadratically’,5,6) -> ‘ratica’

SUBSTRING_INDEX(s, lim, n)：【返回从字符串 s 的第 n个出现的分隔符 lim之后的子串，如果 n是正数，左计算，反正右计算】SELECT SUBSTRING_INDEX(‘www.mysql.com’, ‘.’, 2); -> ‘www.mysql’

ADDDATE(d,n)：【计算起始日期 d 加上 n 天的日期】

ADDTIME(t,n)：【时间 t 加上时间表达式 n，n 是一个mysql的时间表达式】SELECT ADDTIME(‘2011-11-11 11:11:11’, 5); ->2011-11-11 11:11:16 (秒)

CURDATE()：【返回当前日期】SELECT CURRENT_DATE(); -> 2008-09-19

CURTIME()：【返回当前时间】SELECT CURTIME(); -> 13:50:26

NOW()：【返回当前日期和时间 CURRENT_TIMESTAMP()与之相同】SELECT NOW(); -> ‘2007-12-15 13:50:26’

DATEDIFF(d1,d2)：【日期 d1->d2 之间相隔的天数】SELECT DATEDIFF(‘2010-11-30 23:59:59’,‘2010-12-31’); -> -31

TIMEDIFF(t1, t2)：【 t1->t2 之间时间差】SELECT TIMEDIFF(‘2008-12-31 23:59:59.000001’,‘2008-12-30 01:01:01.000002’); -> ‘46:58:57.999999’

DATE_FORMAT(d,f)：【按表达式 f 显示日期 d】SELECT DATE_FORMAT(‘1900-10-04 22:23:00’,‘%D %y %a %d %m %b %j’); -> ‘4th 00 Thu 04 10 Oct 277’

STR_TO_DATE(s, f)：【字符串按表达式 f 转变为日期】SELECT STR_TO_DATE(‘May 1, 2013’,‘%M %d,%Y’); -> ‘2013-05-01’

记录性备注：

进行多维度GROUP BY 且查询字段不包含全部的分组字段时因版本问题，可执行命令（该命令重启后需再次设置，如有需要可查询如果永久性配置）：

SET@@GLOBAL.sql_mode = ‘STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION’;

@符号的作用：

使用SET直接赋值变量，变量名以 @ 开头：如:SET @var=1;

@是用户变量可以在一个会话（一个连接）的任何地方声明，作用域是整个会话，随会话关闭而消失；

示例：设置–> SELECT @op = ‘2021-08-17’; 使用–>SELECT * FROM statistic_app_nft_sell WHERE createDay = @op; 查询–>SELECT @op FROM DUAL;

@@是系统变量可以在任何会话地方声明，作用域是当前运行mysql程序，随重启mysql而消失；

示例：如上多维度GROUP BY的时候使用的设置全局变量(也可以写： SELECT GLOBAL @@sql_mode = ‘xxx’😉，查询–>SELECT @@sql_mode FROM DUAL;

存储(函数)过程：

DECLARE: 关键字声明的变量，例如： DECLARE var1 INT DEFAULT 0;主要用在存储过程中，或者是给存储传参数中。在调用存储过程时，以DECLARE声明的变量都会被初始化为 NULL。

#创建存储过程语法：[]内是可选项
CREATE [DEFINER = { user | CURRENT_USER }] PROCEDURE sp_name 
(
    [proc_parameter[,...]]
)    
[characteristic ...] 
routine_body  

#释义：
#DEFINER 指定此对象的定义者是谁，若不显式指定，则创建此对象的用户就是定义者;
#proc_parameter 共有三种参数类型 IN,OUT,INOUT;
#characteristic 该存储过程特性，如注释， SQL SECURITY{DEFINER（按定义者拥有的权限来执行）|INVOKER（按调用的权限来执行）}
#routine_body 存储过程体，开始与结束使用BEGIN与END进行标识。

#变量定义：
DECLARE variable_name [,variable_name...] datatype [DEFAULT value];
#variable_name 变量名，可同时定义多个，及给定默认值value;
#datatype 为 MySQL 的数据类型，如: int, float, date,varchar(length)

#其他：
#声明语句结束符，可以自定义,如: DELIMITER $$
#@符号的用户变量及@@的系统变量均可入参调用存储过程
#存储过程内使用 --注释
#查询某存储过程： SELECT NAME FROM mysql.proc WHERE db='数据库名';
#查询某存储过程详情：SHOW CREATE PROCEDURE 数据库.存储过程名;
#删除某存储过程：DROPP ROCEDURE 数据库.存储过程名;
#if-then-else： if ... then ... else ... end;
#case语句: case ... when ... then ... else... end case;
#while语句：while ... do ... end while;

参数类型示例（以下结果，自行试验验证）

#IN:调用者向过程传入值
DELIMITER //
CREATE PROCEDURE in_param(IN p int)
    BEGIN
    SELECT p;
    SET p=2;
    SELECT p;
    END;
    //
DELIMITER ;

SET @p=5;
CALL in_param(@p);
# 结果：5，2，
SELECT @p;
# 结果：5，

#OUT 过程向调用者传出值
DELIMITER //
CREATE PROCEDURE out_param(OUT p int)
    BEGIN
    SELECT p;
    SET p=2;
    SELECT p;
    END;
    //
DELIMITER ;

SET @p=5;
CALL out_param(@p);
# 结果：NULL，2，
SELECT @p;
# 结果：2，

#INOUT 调用者向过程传入值，过程向调用者传出值
DELIMITER //
CREATE PROCEDURE inout_param(INOUT p int)
    BEGIN
    SELECT p;
    SET p=2;
    SELECT p;
    END;
    //
DELIMITER ;

SET @p=5;
CALL out_param(@p);
# 结果：5，2，
SELECT @p;
# 结果：2，

存储函数

#创建语法：[]内是可选项
CREATE [DEFINER = { user | CURRENT_USER }] FUNCTION func_name 
(
    [param_name type[,...]]
)
RETURNS type
[characteristic ...] 
BEGIN
	routine_body
END;

#释义：
#DEFINER 指定此对象的定义者是谁，若不显式指定，则创建此对象的用户就是定义者;
#func_name， param_name 函数名和函数参数；
#characteristic 该特性，如注释， SQL SECURITY{DEFINER（按定义者拥有的权限来执行）|INVOKER（按调用的权限来执行）}
#routine_body 指定体，开始与结束使用BEGIN与END进行标识。

#使用方式：SELECT func_name([parameter[,…]]);
#查询存储函数： SHOW FUNCTION STATUS WHERE db='数据库名';
#删除存储函数：DROP FUNCTION 数据库.存储函数名;

函数使用示例：

DELIMITER //
-- 创建函数，入参为起始时间，结束时间，订单产生时间
CREATE FUNCTION `calOnlineDays`(
	`startDay` varchar(20),
	`endDay` varchar(20),
	`orderCreateTime` datetime
)
-- 返回值为int类型（订单在范围时间内的存在天数）
RETURNS int(11)
-- 部分定义特性
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
-- 备注释义
COMMENT '该方法为测试方法' 
SQL SECURITY DEFINER
--方法起始
BEGIN	
	-- 设置无默认值的字符串型变量
	DECLARE queryStartDay VARCHAR(20) ;
	-- 设置默认值为1的数字变量
	DECLARE onlineDays INT(11) DEFAULT 1 ;
	-- 如果传入的起始时间大于等于传入的订单产生时间， 将传入的起始时间赋值给字符串变量
	IF startDay>=DATE_FORMAT(orderCreateTime,'%Y-%m-%d') THEN
		SET queryStartDay = startDay;
	ELSE
	-- 反之将传入的订单产生时间赋值给字符串变量
		SET queryStartDay = DATE_FORMAT(orderCreateTime,'%Y-%m-%d');
	END IF;
	
	-- 从日历表base_calendar中查询天数值赋值给返回值数字变量
	SELECT COUNT(1) INTO onlineDays
	FROM base_calendar bc
	WHERE bc.CalDate<=endDay AND bc.CalDate>=queryStartDay;
	-- 返回数字变量
	RETURN onlineDays;
END;
    //
DELIMITER ;



#使用：
SELECT calOnlineDays('2021-09-10','2021-09-12','2021-09-01 00:00:01')  -> 3

视图：

视图可以理解为单个或多个表按需要的结果展示出来的一个临时数据表，主要用来做查询使用，该表可按普通表使用，但是其本身并不包含数据。

#创建视图：[]内是可选项
CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
VIEW view_name [(column_list)]
AS select_statement
[WITH [CASCADED | LOCAL] CHECK OPTION]

#释义：
#OR REPLACE	表示替换已有视图；
#ALGORITHM	表示视图选择算法，默认算法是UNDEFINED(未定义的)：MySQL自动选择要使用的算法 ；merge合并；temptable临时表；
#view_name	视图名称；
#select_statement	select语句；
#WITH CASCADED|| LOCAL CHECK OPTION	视图在更新时保证在视图的权限范围之内,默认值为CASCADED，即更新时满足视图和表的相关条件
而LOCAL表示满足视图定义的一个条件即可

#查看视图 SHOW CREATE VIEW 视图名;
#查看视图字段信息 DESC 视图名;
#删除视图 DROP VIEW 视图名;
#修改视图 ALTER [characteristic ...] VIEW view_name AS select_statement

使用示例：

#单表视图
CREATE VIEW base_view_calen(id,onday,yearm)
AS SELECT id,CalDate,YearMonth FROM base_calendar WHERE id<100 
WITH CHECK OPTION;

DESC base_view_calen;
#对视图进行更新操作会影响原表数据
UPDATE base_view_calen SET onday = '2017-01-02' WHERE id = 1

#多表视图(有些时候多表视图会因WITH CHECK OPTION 范围而报错，比如下面这个)
CREATE VIEW base_view_calentest
AS SELECT bc.id,bc.CalDate,bc.YearMonth, fo.ip FROM base_calendar bc 
LEFT JOIN db_info fo  ON bc.id = fo.id
WHERE bc.id<100 


#注意，在视图创建时，如果SELECT查询语句中有，distinct,group by, order by, union等集合运算，WHERE后有子查询，以及FROM 多个表的时候，该视图中不能使用DML(增删改)语句的视图

NaselloSea

关注

0
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
Mysql常规使用，函数转换字符集,@的使用及存储过程等记录

日常查询，修改，函数等记录库、表的创建（导入\导出）操作:修改表字段操作：基本表数据操作：查询时的常用运算符：查询时的常用关键字与函数（相关资源可以查看自有的各数据连接工具帮助文件或[菜鸟教程](https://www.runoob.com/mysql)）记录性备注：库、表的创建（导入\导出）操作:查看当前连接所有库：SHOW DATABASES;使用指定库：USE 库名;查看使用库所有表：SHOW TABLES;查看使用库的指定表结构：SHOW CREATE TABLE 表名;查看非使用库的指
复制链接

扫一扫