程序员经验分享-hwhale

mysql笔记-mysql常用操作

2023-11-15

目录

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

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

三、取两表并集

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

五、查看bin-log日志

六、MySql常用运算符和函数

七、SQL语句执行分析

八、定时任务

九、四舍五入

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

十一、保存表情符号

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

十三、mysql忘记root密码

十四、查看连接数进程数

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

十六、一次查询多个COUNT

十七、查询数据字典

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

       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;
    查看数据库当前会话的连接数：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;

十五、计算地理位置距离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

        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

十七、查询数据字典

    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'

本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系:hwhale#tublm.com(使用前将#替换为@)

mysql

mysql笔记-mysql常用操作 的相关文章

随机推荐

  • 用于回归问题的异常鲁棒极限学习机（ORELM）（Matlab代码实现）

    欢迎来到本博客 博主优势 博客内容尽量做到思维缜密 逻辑清晰 为了方便读者 座右铭 行百里者 半于九十 目录 1 概述 2 运行结果 3 参考文献 4 Matlab代码实现 1 概述 文献来源 极限学习机 ELM 作为机器学习中最有用的技术

  • 加密算法中的加盐值

    在应用中 出于到安全的考虑和数据的保密 需要使用到加密算法 有时候为了让加密的的结果更加扑朔迷离神鬼莫测一些 常常会给被加密的数据加点 盐 说白了 盐就是一串数字 完全是自己定义的 不多说 上实例 获取当前用户盐 param string

  • STM32外设系列—sg90（舵机）

    文章目录 一 sg90简介 二 引脚连接 三 控制方法 四 程序设计 4 1 配置定时器 4 2 编写控制程序 五 360 舵机 一 sg90简介 首先介绍说一下什么是舵机 舵机是一种位置 角度 伺服的驱动器 适用于一些需要角度不断变化的

  • listview动态标识被点击项并且不出现背景重绘紊乱的解决方法

    private int publicIndex 1 记录被点击item的id viewHolder layout setId position if position 1 0 publicIndex 1 第一次加载的时候默认选中第一项 如果

  • fpga卷积神经网络加速器,FPGA卷积神经网络综述

    如何使用FPGA加速机器学习算法 如何使用FPGA加速机器学习算法 当前 AI因为其CNN 卷积神经网络 算法出色的表现在图像识别领域占有举足轻重的地位 基本的CNN算法需要大量的计算和数据重用 非常适合使用FPGA来实现 上个月 Ralp

  • sql盲注

    提示 文章写完后 目录可以自动生成 如何生成可参考右边的帮助文档 sql注入 盲注 前言 一 什么是盲注 二 步骤示例 1 测试注入点 2 测所在数据库长度 3 利用ASCII码猜当前数据库名 4 利用ASCII码查询表名 5 利用ASCI

  • Nginx的TCP运行时健康检查

    Nginx的TCP运行时健康检查 本章介绍如何配置TCP的运行状况检查 介绍 先决条件 被动TCP运行状况检查 服务器缓慢启动 活动TCP运行状况检查 微调TCP运行状况检查 匹配 配置块 介绍 NGINX和NGINX Plus可以持续测试

  • ORACLE如何停止一个JOB

    ORACLE如何停止一个JOB 1 相关表 视图 dba jobs all jobs user jobs 包含登录用户所有的JOB信息 dba jobs running 包含正在运行job相关信息 注意 须使用oracle的sys用户登录到

  • Matplotlib中plot和scatter以及subplot和add_axes区别

    之前在做练习时一直有两种方法 plot和scatter一直傻傻分不清 加上这次对matplotlib深入了解 可以清楚的去做判断 plot用法 from matplotlib import pyplot as plt plt plot 5

  • redis安装+主从+哨兵模式和坑。

    一 安装 版本 3 2 11 1 解压 2 进入解压后的目录 执行 make 3 执行 make install 这一步需要root权限的用户执行 注 不使用root用户安装时 在make install 后添加PREFIX usr loc

  • 跨境电商如何更好地备战销售旺季？

    跨境电商秋促来临 不知道各位卖家是否做好准备了呢 据外媒报道 TikTok Shop于近日开启了年度最大规模的黑五大促 而为了抢占旺季流量 继周二亚马逊宣布将于10月10日至11日举办第二届 Prime Big Deal Days 大促后

  • Unity—事件

    每日一句 我见青山多妩媚 料青山见我应如是 目录 事件 委托和事件的关系 为什么使用委托类型来声明事件 为什么事件是基于委托的 事件的定义 事件的核心功能 事件和事件模型 如何自定义事件 事件 委托和事件的关系 事件是委托类型字段的包装器

  • 【AI 绘画】 MidJourney 入门、参数解析、进阶玩法、变现指南、资料包

    AI 绘画 MidJourney 入门 参数解析 进阶玩法 变现指南 资料包 ai绘画 视频文件 网易订阅

  • windows子系统启动ubuntu“另一个程序正在使用此文件，进程无法访问”

    背景 问题 磁盘整理后重启 WSL无法启动 提示 另一个程序正在使用此文件 进程无法访问 解决 重启和资源管理清理 一天都无法解决 碰巧用命令行重启 还是true false的两行提示 不确定成功没 重启居然就能打开ubuntu了 管理员身

  • c++生成二维码

    vs2010编译好的qrencode库 http files cnblogs com files verstin qrencode rar 版本是3 4 4 编译方法参考 http blog csdn net liyuanbhu artic

  • c语言实现两个值互换

    include

  • C语言-运算符优先级

    规律 1 C语言中只有一个三目运算符 2 所有双目运算符中只有赋值运算符的结合方向是从右往左 3 对于优先级 算术运算符 gt 关系运算符 gt 逻辑运算符 gt 赋值运算符 逻辑运算符中 逻辑非 除外 4 同一优先级看结合方向 优先级 运

  • npm install报错ERR code ETIMEDOUT的解决办法

    将 npm 改为 淘宝镜像即可 1 查看npm镜像设置 npm config get registry 2 将npm设置为淘宝镜像 npm config set registry https registry npm taobao org

  • git切换到tag中

    1 首先查看tag列表 tag以BJ 20230904为例 git tag 2 切换到想查看的tag分支 新创建一个本地分支 git checkout b tag BJ 20230904 BJ 20230904 注 tag BJ 20230

  • mysql笔记-mysql常用操作

    目录 一 中文汉字按照拼音首字母排序 二 case when then else end语法的使用 三 取两表并集 四 MySql8导入数据时insert插入数据慢 五 查看bin log日志 六 MySql常用运算符和函数 七 SQL语句

热门标签