mysql笔记-mysql常用操作

一、中文汉字按照拼音首字母排序

       1、排序字段的字符编码是utf8_general_ci
           SELECT * FROM table t1 ORDER BY CONVERT(t1.`name` USING gbk) COLLATE gbk_chinese_ci ASC
       2、排序字段的字符编码是 latin1_swedish_ci
           ORDER BY birary(t1.`name`) ASC
       3、排序字段的字符编码是 GBK
           ORDER BY t1.`name` ASC

二、case when then else end语法的使用

        1：两种语法
             case sex when '1' then '男' when '2' then '女' else '其他' end
             case when sex='1' then '男' when sex='2' then '女' else '其他' end
        2：用法
            SELECT COUNT(*),sum(case when t1.state = '1' then 1 else 0 end),t1.sex
            FROM
            (SELECT 1 AS id , '张三' AS name, '1' AS sex, '1' AS state
            UNION ALL
            SELECT 2 AS id , '李四' AS name, '2' AS sex, '1' AS state
            UNION ALL
            SELECT 3 AS id , '王五' AS name, '1' AS sex, '2' AS state) AS t1
            GROUP BY t1.sex

三、取两表并集

        1：思路
              先两表左连接然后两表右连接，把这两部分的数据合起来去重
        2：用法
              SELECT  DISTINCT t.* FROM (
              SELECT * FROM
              (SELECT 1 AS id , 'b1' AS name
              UNION ALL
              SELECT 2 AS id , 'b2' AS name
              UNION ALL
              SELECT 3 AS id , 'b3' AS name) AS t1
              LEFT JOIN
              (SELECT 1 AS id1 , 'c1' AS con
              UNION ALL
              SELECT 4 AS id1 , 'c4' AS con) AS t2
              ON t1.id = t2.id1
              UNION ALL
              SELECT * FROM
              (SELECT 1 AS id , 'b1' AS name
              UNION ALL
              SELECT 2 AS id , 'b2' AS name
              UNION ALL
              SELECT 3 AS id , 'b3' AS name) AS t1
              RIGHT JOIN
              (SELECT 1 AS id1 , 'c1' AS con
              UNION ALL
              SELECT 4 AS id , 'c4' AS con) AS t2
              ON t1.id = t2.id1) as t

四、MySql8导入数据时insert插入数据慢

        1：同样的语句在MySql5中导入速度还可以，但在MySql8中变得非常慢。原因是MySql8默认开启了一些设置。
        2：修改mysql配置文件(ProgramData\MySQL\MySQL Server 8.0\my.ini)
            [mysqld]
            skip-log-bin
            # disable_log_bin Linux下使用这个

五、查看bin-log日志

        1：查看有哪些日志文件
            SHOW BINARY LOGS;
            SHOW MASTER LOGS;
        2：查看日志文件内容
            SHOW BINLOG EVENTS IN 'DESKTOP-FJ970FJ-bin.000043';(SHOW BINLOG显示第一个binlog的内容)
        3：查看当前正在写入的binlog日志文件
            SHOW MASTER STATUS;
        4：查看是否开启了binlog日志
            SHOW VARIABLES LIKE 'log_bin';（SELECT @@log_bin）
        5：查看binlog的日志和名称
            SHOW VARIABLES LIKE '%log_bin%';
        6：开始一个新的日志文件
            FLUSH LOGS;(重启mysql也可以达到相同的效果)

六、MySql常用运算符和函数

        1：等于(=不可以判断null)(<=>可以判断null)
        2：判断null(a is null,a is not null),(isnull(a),!isnull(a))
        3：区间(between min and max)(not between min and max)是一个闭区间
        4：运算符(<),(>),(<=),(>=)
        5：随机数 RAND();
        6：least(val1,val2,...)返回最小值
        7：greastest(val1,val2,...)返回最大值
        8：round(M,D);返回M的四舍五入的值, D表示要保留几们小数,默认值是0
        9：abs();绝对值
        10：avg();平均值
        11：count();数量 会忽略null值
        12：sum();求和
        13：min();最小
        14：max();最大
        15：日期时间now(6),sysdate(6),current_timestamp(6)
        16：curdate(),curtime()
        17：日期加 date_add(NOW(), interval '1 12:10' day_minute)，select date_add(NOW(), interval '1-1' YEAR_MONTH)，select date_add(NOW(), interval '1' SECOND)
        18：日期减 date_sub()  加1天-DATE_SUB(CURDATE(),INTERVAL -1 DAY);减1天-DATE_SUB(CURDATE(),INTERVAL 1 DAY);加1周-DATE_SUB(CURDATE(),INTERVAL -1 WEEK);
        19：日期差 datediff(NOW(),NOW());单位天
        20：计算日期中的星期 dayname(NOW());
        21：日期格式化 SELECT str_to_date('2017-02-01', '%Y-%m-%d %H:%i:%s');
        22：left(str, len) 返回字符串str的左端len个字符
        23：length() 返回长度
        24：lower(str) 返回小写的字符串str
        25：substring() 取子字符串, 第二个参数是截取的起始位置, 第三个参数是要截取的长度
        26：concat(val1,val2) 字符串拼接
        27：replace(name,'a','b')
        28：TIMESTAMPDIFF(类型FRAC_SECOND/SECOND/MINUTE/HOUR/DAY/WEEK/MONTH/QUARTER/YEAR，开始时间，结束时间)返回日期差

七、SQL语句执行分析

       检查功能是否开启：执行show profiles;如果没有记录表明没有开启通过 set profiling=1;开启
       执行“explain sql语句”可以得到sql语句执行的详细信息
       show WARNINGS;可以查看优化语句

八、定时任务

       开始前需要开启事件调度器
       SHOW VARIABLES LIKE '%event_sche%';
       SET global event_scheduler=on;//开启
       SET global event_scheduler=off;//关闭
       1：创建定时任务

-- 创建定时计划的例子,每天定时，自动将日期加1天
CREATE EVENT `test_update`
ON SCHEDULE EVERY 1 DAY STARTS '2020-01-01 01:00:00'
ON COMPLETION PRESERVE
ENABLE
DO
update test_update set start_date=CURRENT_DATE, end_date = date(CURRENT_DATE + 1);

          通过navicat创建：事件->新建事件->定义

         

          定义：可以调用存储过程，也可以直接执行语句-update test_update set start_date=CURRENT_DATE, end_date = date(CURRENT_DATE + 1)。
          状态：DISABLE不激活,ENABLE激活。
          ON COMPLETION：PRESERVE 当event到期了,event会被disable,但是该event还是会存在,NOY PRESERVE 当event到期的时候,该event会被自动删除掉。
          AT：时间戳，用来完成单次的计划任务。
          EVERY：在定期间隔重复执行。
          STARTS：开始生效时间 INTERVAL 可以设置间隔重复执行（+INTERVAL 与 EVERY 不能同时使用）。
          ENDS：结束时间可设置一个 INTERVAL 控制未来结束。
      2：定时任务调用存储过程
          创建存储过程

CREATE DEFINER=`root`@`localhost` PROCEDURE `erp_sure_jihua`()
BEGIN
  DECLARE now_date varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
  DECLARE now_day INT;
  DECLARE jihua_sure_day INT;
  DECLARE jihua_sure_yue INT;
  DECLARE sure_yue varchar(7) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

  SET now_date = DATE_FORMAT(NOW(), '%Y-%m-%d');
  SET now_day = DAY(now_date);

  SELECT value into jihua_sure_day from s_config WHERE only_tag ='home_erp_tibao_jihua_sure_day';
  SELECT value into jihua_sure_yue from s_config WHERE only_tag ='home_erp_tibao_jihua_sure_yue';

  IF  now_day = jihua_sure_day THEN
    IF jihua_sure_yue = '1' THEN
      SET sure_yue = DATE_FORMAT(DATE_SUB(now_date, interval  1 MONTH), '%Y-%m');
    ELSE
      SET sure_yue = DATE_FORMAT(now_date, '%Y-%m');
    END IF;
    UPDATE s_user_jh SET is_sure = 1, sure_time = unix_timestamp(now()) WHERE jh_date = sure_yue;
  END IF;
END

          任务中定义修改为 CALL erp_sure_jihua()

九、四舍五入

     FLOOR(X)：返回不大于X的最大整数值
  CEILING(X)：返回不小于X的最小整数值
  ROUND(X)：返回参数X的四舍五入的一个整数
  ROUND(X,D)：返回参数X的四舍五入的有D为小数的一个数字。如果D为0，结果将没有小数点或小数部分

十、CONVERT(字段名, 类型) 字符串转换用法

SELECT CONVERT("09", UNSIGNED);
DATE-日期、DATETIME-日期时间、TIME-时间、CHAR-字符串、SIGNED-有符号整型、UNSIGNED-正数、BINARY-二进制格式

十一、保存表情符号

      mysql8保存表情时报错，这时需要设置字段的字符集为utf8mb4排序为utf8mb4_general_ci，应为数据库保存时最终以字段字符为准，字段没设置时用表的字符设置，表没设置用数据库的字符设置，所以这里直接设置字段的字符集就可以。

十二、清除mysql命令行输入历史

   rm ~/.mysql_history

十三、mysql忘记root密码

      修改配置文件my.cnf->
      增加 skip-grant-tables 忽略权限->
      重启mysql服务(docker直接重启docker)->
      docker exec -it mysql8 /bin/bash(进入容器)->
      mysql -u root -p(此时免密)->
      flush privileges(防止出现The MySQL server is running with the --skip-grant-tables option so it cannot execute错误)->
      ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '12345';(也可以先创建新用户create user 'sroot'@'%' identified by 'password';->flush privileges->grant all privileges on *.* to  'sroot'@'%' with grant option;)->
      flush privileges;->
      ALTER USER 'sroot'@'%' IDENTIFIED WITH mysql_native_password BY '12345';->
      flush privileges;->
      exit;->
      修改my.cnf去掉开始增加的内容->
      重启mysql服务(docker直接重启容器)

十四、查看连接数进程数

    查询数据库当前进程的连接数：select count(*) from v$process;SHOW FULL PROCESSLIST;
    查看数据库当前会话的连接数：select count(*) from v$session;
    查看数据库的并发连接数：select count(*) from v$session where status='ACTIVE';
    查看当前数据库建立的会话情况：select sid,serial#,username,program,machine,status from v$session;
    查询数据库允许的最大连接数：select value from v$parameter where name = 'processes';或者：show parameter processes;SHOW GLOBAL STATUS LIKE 'Threads_connected';
    查询数据库最大使用的连接数：SHOW GLOBAL STATUS LIKE 'Max_used_connections';
    查询数据库设置的最大连接数：SHOW VARIABLES LIKE '%max_connections%';在配置文件通过设置[mysqld] max_connections = 200 修改设置mysql8默认151;

十五、计算地理位置距离ST_DISTANCE_SPHERE(5.7)和ST_DISTANCE(5.6)

    ST_DISTANCE_SPHERE(POINT(a.longitude, a.latitude),POINT(#{longitude}, #{latitude})) / 1000 单位km
    ST_DISTANCE(POINT(a.longitude, a.latitude),POINT(#{longitude}, #{latitude})) *111195/ 1000 单位km  ST_DISTANCE(单位度)*6371000*PI/180(地球半径)->米

十六、一次查询多个COUNT

    count(*)包含NULL count(字段)不包含NULL
    select
        count(case when sex ='男' then 1 end) as man,
        count(case when sex ='女' then 1 end) as woman,
        count(case when age>=18 then 1 end) as adult,
        count(case when age<18 then 1 end) as nonage
    from user
    count(case when age<18 then 1 end)还可以写成count(age<18 or NULL)或count(if(age<18, 1, NULL))

十七、查询数据字典

    SELECT
        C.COLUMN_NAME AS '字段名', C.COLUMN_TYPE AS '数据类型',C.IS_NULLABLE AS '允许为空', C.EXTRA AS 'PK', C.COLUMN_COMMENT AS '字段说明' 
    FROM COLUMNS C
    INNER JOIN TABLES T ON C.TABLE_SCHEMA = T.TABLE_SCHEMA 
    AND C.TABLE_NAME = T.TABLE_NAME 
WHERE
    T.TABLE_SCHEMA = 'dbname' AND T.TABLE_NAME = 'table'

十八、GROUP_CONCAT使用

    可以将分组中的数据拼接GROUP_CONCAT( DISTINCT s.driver_id ORDER BY s.driver_id DESC SEPARATOR ',' )，DISTINCT还可以去除重复数据 ORDER BY排序
    GROUP_CONCAT(CONCAT_WS(',',  s.a, s.b) SEPARATOR ',')
    默认情况下返回的最大长度1024，如果需要增大需修改SESSION或GLOBAL的group_concat_max_len

十九、Mysql8导入SQL文件时字符集问题

    mysql8.0导数时表字符集的的COLLATE总是utf8mb4_0900_ai_ci，但是通过语句创建是和默认数据库配置一致的
    mysql 8.0字符集uft8mb4默认的collate 是utf8mb4_0900_ai_ci ，这个值是参数default_collation_for_utf8mb4控制，如果创建表时只给了字符集utf8mb4,没指定collate话，就会是默认的utf8mb4_0900_ai_ci
    导入时指定CHARSET和COLLATE(DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci)就可以了
    utf8_general_ci是一个遗留的 校对规则，不支持扩展，它仅能够在字符之间进行逐个比较。utf8_general_ci校对规则进行的比较速度很快，但是与使用 utf8mb4_unicode_ci的校对规则相比，比较正确性较差，utf8mb4_unicode_ci是基于标准的Unicode来排序和比较，能够在各种语言之间精确排序，MySQL 8.0 默认的是 utf8mb4_0900_ai_ci，属于 utf8mb4_unicode_ci 中的一种，需要注意的是utf8mb4_unicode_ci不区分大小写而utf8_unicode_ci是区分的

二十、锁查看

SELECT * FROM information_schema.INNODB_TRX; -- 正在执行中的事务
SELECT * FROM performance_schema.data_locks; -- 正在锁的事务
SELECT * FROM performance_schema.data_lock_waits; -- 等待锁的事务
SHOW engine innodb status; -- 最近的死锁
SHOW OPEN TABLES WHERE In_use > 0; -- 查询是否锁表，In_use使用线程，Name_locked是否表锁