mysql笔记_mysql strtodate(concat)

目录

目录

查看版本

创建用户

删除用户

用户授权

删除用户权限

终端执行sql 文件

查询表属于哪个库实例，从 information_schema 中查询

常用函数

列转行

命令行创建存储过程

终端存储过程查看

备份表

update报错

批量update

时间差函数（TIMESTAMPDIFF、DATEDIFF）

 日期转换计算函数（date_add、day、date_format、str_to_date）

---

---

查看版本

不登录： mysql -V  mysql --version
登录后： select version();        status;

 

删除数据库

DROP DATABASE  `user-center`;

创建用户

参考：https://www.cnblogs.com/zhongyehai/p/10695659.html

例子：CREATE USER 'wechat'@'%' IDENTIFIED BY 'wechat';

说明：命令:CREATE USER 'username'@'host' IDENTIFIED BY 'password';
username：你将创建的用户名
host：指定该用户在哪个主机上可以登陆，如果是本地用户可用localhost，如果想让该用户可以从任意远程主机登陆，可以使用通配符%
password：该用户的登陆密码，密码可以为空，如果为空则该用户可以不需要密码登陆服务器
例子：
CREATE USER 'dog'@'localhost' IDENTIFIED BY '123456';
CREATE USER 'pig'@'192.168.1.101_' IDENDIFIED BY '123456';
CREATE USER 'pig'@'%' IDENTIFIED BY '123456';
CREATE USER 'pig'@'%' IDENTIFIED BY '';
CREATE USER 'pig'@'%';

     ERROR

     创建用户时报错：报错：[Err] 1396 - Operation CREATE USER failed for 'wechat'@'%'

     原因删除用户后未刷新权限，就是没有执行：flush PRIVILEGES;

删除用户

1、delete from user where user in ('wechat');

      flush PRIVILEGES;

2、DROP USER 'username'@'host';

用户授权

单个schema授权 ：

grant all privileges on user_center.* to wechat@'%' identified by 'wechat';

 全部授权（跟root一样），一般不用
grant all privileges on *.* to wechat@'%' identified by 'wechat';

授权报错：

[Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-upms.* to wechat@'%' identified by 'wechat'' at line 1

 语句：grant all privileges on  wechat-upms.* to wechat@'%' identified by 'wechat';

 原因：库名有中划线

处理：反引号"`" ， grant all privileges on ` wechat-upms`.* to wechat@'%' identified by 'wechat';

删除用户权限

revoke all on *.* from wechat@'%';

终端执行sql 文件

1、上传sql文件到指定路径
2、登录mysql :   mysql> source /home/cjf/sql-20200409.sql

查询表属于哪个库实例，从 information_schema 中查询

SELECT table_schema FROM   `TABLES` WHERE table_name = 'user';

常用函数

替换：REPLACE(str,from_str,to_str)

说明：在字符串 str 中所有出现的字符串 from_str 均被 to_str替换，然后返回这个字符串：

例子：UPDATE tb1 SET f1=REPLACE(f1, 'abc', 'def');

列转行

SELECT GROUP_CONCAT('''',e.em_id,'''') em_ids from db_call_admin.tb_call_employe e

结果：'134','135','129','139','130','128','131','133','137','132','136','138'

SELECT GROUP_CONCAT(e.em_id) em_ids from db_call_admin.tb_call_employe e

结果：134,135,129,139,130,128,131,133,137,132,136,138

命令行创建存储过程

delimiter //

delimiter ;

终端存储过程查看

SHOW PROCEDURE STATUS WHERE name LIKE '%add_column%'

SHOW PROCEDURE STATUS WHERE db = 'yiibaidb';

显示源码： SHOW CREATE PROCEDURE stored_procedure_name

备份表

create table xxx as select * from a;  会报错

问题：Error Code: 1786 Statement violates GTID consistency: CREATE TABLE ... SELECT.
处理：

create table xxx as select 的方式会拆分成两部分。

create table xxxx like data_mgr;
insert into xxxx select *from data_mgr;

update报错

问题：1093 - You can't specify target table 'e' for update in FROM clause

原因：mysql不允许update目标表和子查询里面的表为同一张表

解决办法: 利用子查询sql可以改变双层的子查询，即可执行成功  (但性能较差，仅仅适合较小的数据量的)

eg:
UPDATE zs_work_approval_node
SET uid = 4963,
 NAME = '***'
WHERE
    id IN (
        SELECT
            *
        FROM
            (
                SELECT
                    m.id
                FROM
                    zs_work_approval_node m
                WHERE
                    uid = 4967
                AND STATUS IN (0, 1, 4)
            ) AS temp
    );

批量update

# 不使用select情况

UPDATE OldData o, NewData n
SET o.name = n.name, o.address = n.address
where n.nid=234 and o.id=123;

# 使用select情况

UPDATE OldData o, (select name, address from NewData where id = 123) n
SET o.name = n.name, o.address = n.address
where n.nid=234;

 

时间差函数（TIMESTAMPDIFF、DATEDIFF）

--0
select datediff(now(), now());
 
--2
select datediff('2015-04-22 23:59:00', '2015-04-20 00:00:00');
 
--2
select datediff('2015-04-22 00:00:00', '2015-04-20 23:59:00');
 
--1
select TIMESTAMPDIFF(DAY, '2015-04-20 23:59:00', '2015-04-22 00:00:00');
 
--2
select TIMESTAMPDIFF(DAY, '2015-04-20 00:00:00', '2015-04-22 00:00:00');
 
--2
select TIMESTAMPDIFF(DAY, '2015-04-20 00:00:00', '2015-04-22 12:00:00');
 
--2
select TIMESTAMPDIFF(DAY, '2015-04-20 00:00:00', '2015-04-22 23:59:00');
 
--71
select TIMESTAMPDIFF(HOUR, '2015-04-20 00:00:00', '2015-04-22 23:00:00');
 
--4260
select TIMESTAMPDIFF(MINUTE, '2015-04-20 00:00:00', '2015-04-22 23:00:00');
-- 秒
select TIMESTAMPDIFF(SECOND, '2015-04-20 00:00:00', '2015-04-22 23:00:00');

 SELECT  executor_handler, trigger_time,handle_time,  TIMESTAMPDIFF(SECOND, trigger_time, handle_time) AS utime  FROM  xxl_job_log   WHERE  trigger_time >  STR_TO_DATE('2020-07-17', '%Y-%m-%d %H')  
     AND    executor_handler = 'yeepayReportStatusQueryTask2Handler'  HAVING utime >0 ORDER BY utime DESC  LIMIT 10;

 日期转换计算函数（date_add、day、date_format、str_to_date）

-- 用日期与字符串转换，计算当月第一天、下月第一天
select curdate() as '当前日期', 
DATE_FORMAT(curdate(), '%Y-%m') as '当前月份', 
str_to_date(concat(DATE_FORMAT(curdate(), '%Y-%m'), '-01'), '%Y-%m-%d') as '当前月的第一天', 
date_add(str_to_date(concat(DATE_FORMAT(curdate(), '%Y-%m'), '-01'), '%Y-%m-%d'), interval 1 month) as '下月的第一天';
 
-- 当前月的最后一天
select last_day(curdate());
 
-- 下月第一天
select date_add(last_day(curdate()), interval 1 day);
 
-- 当天为当月的第几天
select day(curdate());
 
-- 当月第一天
select date_add(curdate(), interval 1-(day(curdate())) day);