【檀越剑指大厂--mysql】mysql基础篇

女爷i 2024-04-08 08:18 67阅读 0赞

### 一.服务端设置 ###

#### 1.启动/关闭服务器 ####

net start mysql
    net stop mysql

#### 2.登录退出 ####

mysql -u root -p
    
    mysql -u root -p20191014   #进入mysql
    
    mysql -u root -p716288qwe
    
    exit; #退出mysql

#初始化
    ./mysqld --user=mysql --basedir=/usr/local/mysql8/  --datadir=/usr/local/mysql8/data/ --lower-case-table-names=1 --initialize

#刷新
    FLUSH PRIVILEGES;
    
    
    use  mysql;
    show global variables like 'port';

service mysql restart

#### 3.执行文件 ####

source /home/jumpserver_dev/datasense/sql/rv_0321.sql

#### 4.查看服务端编码 ####

show variables like 'character_set_server';

#### 5.修改密码 ####

mysqladmin -uroot password 'password';  #设置root用户的密码
    
    mysqladmin -uroot -p password 'password';   #修改root用户的密码

#### 6.查看缓冲池的信息 ####

SELECT * from information_schema.INNODB_BUFFER_POOL_STATS;

#### 7.获取连接 id ####

SELECT CONNECTION_ID()

#### 8.监控 ####

-- 1. 查询冗余索引
    select * from sys.schema_redundant_indexes;
    
    -- 2. 查询未使用过的索引
    select * from sys.schema_unused_indexes;
    
    -- 3. 查询索引的使用情况
    select index_name,rows_selected,rows_inserted,rows_updated,rows_deleted
    from sys.schema_index_statistics
    where table_schema='kwan' ;
    
    --  1. 查询表的访问量
    select table_schema,table_name,sum(io_read_requests+io_write_requests) as io from sys.schema_table_statistics group by table_schema,table_name order by io desc;
    
    --  2. 查询占用bufferpool较多的表
    select object_schema,object_name,allocated,data from sys.innodb_buffer_stats_by_table order by allocated limit 10;
    
    --  3. 查看表的全表扫描情况
    select * from sys.statements_with_full_table_scans where db='kwan';
    
    --  1. 监控SQL执行的频率
    select db,exec_count,query from sys.statement_analysis order by exec_count desc;
    
    --  2. 监控使用了排序的SQL
    select db,exec_count,first_seen,last_seen,query from sys.statements_with_sorting limit 1;
    
    --  3. 监控使用了临时表或者磁盘临时表的SQL
    select db,exec_count,tmp_tables,tmp_disk_tables,query
    from sys.statement_analysis where tmp_tables>0 or tmp_disk_tables >0
    order by (tmp_tables+tmp_disk_tables) desc;
    
    
    -- 1. 查看消耗磁盘IO的文件
    select file,avg_read,avg_write,avg_read+avg_write as avg_io
    from sys.io_global_by_file_by_bytes
    order by avg_read limit 10;

#### 9.查询死锁 ####

-- 行锁阻塞情况
    select * from sys.innodb_lock_waits;

#### 10.查询主从信息 ####

show master status\G;
    show slave status\G;

![image-20221216172959649][]

### 二.库级别 ###

#### 1.创建数据库 ####

create database name;  #创建数据库
    
    CREATE DATABASE IF NOT EXISTS `test`;

#### 2.显示所有数据库 ####

show  databases;

#### 3.显示数据库创建语句 ####

show create database kwan;

#### 7.选用数据库 ####

use databasenaem; #使用databasenaem数据库

#### 8.修改数据库 ####

alter database <数据库名字> character set <字符集> collate <校对规则名>

#### 9.删除数据库 ####

drop database dbname;

#### 10.注释 ####

# SELECT 2;
    
    -- SELECT 2;
    
    /**
     * SELECT 2;
     */

#### 11.查看数据库支持的引擎 ####

show engines;

![image-20220815145218233][]

#### 12.显示 ddl 频次 ####

SHOW GLOBAL STATUS LIKE 'Com_______';

\[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-dacR6psQ-1673068507543)(http://qinyingjie.top/blogImg/image-20220828110824471.png)\]

#### 13.查询库表列 ####

select * from information_schema.columns order by TABLE_SCHEMA
    
    select * from information_schema.tables order by TABLE_SCHEMA
    
    SELECT * FROM information_schema.schemata order by SCHEMA_NAME

#### 14.JDBC 的 URL 详解 ####

##### 14.1.jdbc 的格式 #####

jdbc:mysql://[host][,failoverhost...][:port]/[database] [?propertyName1][=propertyValue1][&propertyName2][=propertyValue2]...

##### 14.2.例如 #####

jdbc:mysql://10.171.55.193/uba_db?useUnicode=true&characterEncoding=UTF-8

##### 14.3.参数详解 #####

<table> 
 <thead> 
  <tr> 
   <th>参数</th> 
   <th>作用</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>user</td> 
   <td>数据库用户名（用于连接数据库）</td> 
  </tr> 
  <tr> 
   <td>password</td> 
   <td>用户密码（用于连接数据库）</td> 
  </tr> 
  <tr> 
   <td>useUnicode</td> 
   <td>是否使用 Unicode 字符集，如果参数 characterEncoding 设置为 gb2312 或 gbk，本参数值必须设置为 true）</td> 
  </tr> 
  <tr> 
   <td>characterEncoding</td> 
   <td>当 useUnicode 设置为 true 时，指定字符编码。比如可设置为 gb2312 或 gbk</td> 
  </tr> 
  <tr> 
   <td>autoReconnect</td> 
   <td>当数据库连接异常中断时，是否自动重新连接</td> 
  </tr> 
  <tr> 
   <td>autoReconnectForPools</td> 
   <td>是否使用针对数据库连接池的重连策略</td> 
  </tr> 
  <tr> 
   <td>failOverReadOnly</td> 
   <td>自动重连成功后，连接是否设置为只读</td> 
  </tr> 
  <tr> 
   <td>maxReconnects</td> 
   <td>autoReconnect 设置为 true 时，重试连接的次数</td> 
  </tr> 
  <tr> 
   <td>initialTimeout</td> 
   <td>autoReconnect 设置为 true 时，两次重连之间的时间间隔，单位：秒</td> 
  </tr> 
  <tr> 
   <td>connectTimeout</td> 
   <td>和数据库服务器建立 socket 连接时的超时，单位：毫秒。 0 表示永不超时，适用于 JDK 1.4 及更高版本</td> 
  </tr> 
  <tr> 
   <td>socketTimeout</td> 
   <td>socket 操作（读写）超时，单位：毫秒。 0 表示永不超时</td> 
  </tr> 
 </tbody> 
</table>

### 三.表级别 ###

#### 1.表信息 ####

##### 1.1.查看所有表 #####

show tables; #查看当前所有表

##### 1.2.建表语句 #####

show create table table_name; #查看当前数据库建表语句

CREATE TABLE `buy_log` (
      `userid` int unsigned NOT NULL,
      `buy_date` date DEFAULT NULL,
      KEY `userid` (`userid`),
      KEY `userid_2` (`userid`,`buy_date`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

##### 1.3.查看表字段 #####

desc table_name; #查看表字段信息
    describe tablename;

![image-20220813130013116][]

##### 1.4.新增表 #####

CREATE TABLE IF NOT EXISTS `biz_table_relationship` (
      `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
      `project_id` bigint(20) NOT NULL COMMENT '项目id',
      `source_id` bigint(20) NOT NULL COMMENT '数据源id',
      `database` varchar(64) NOT NULL COMMENT '库名',
      `relation_id` bigint(20) NOT NULL COMMENT '关系id',
      `base_table_name` varchar(255) NOT NULL COMMENT '主表名称',
      `relative_table_name` varchar(255) NOT NULL DEFAULT '' COMMENT '副表',
      `config` text COMMENT '关系详情',
      `sql` text COMMENT 'sql',
      `relationship_type` int(2) DEFAULT NULL COMMENT '关联类型,1:(1:1),2:(1:N),3:(N:1)',
      `creator` varchar(64) NOT NULL COMMENT '创建人名称',
      `memo` varchar(255) DEFAULT NULL COMMENT '描述信息',
      `tenant_id` varchar(64) NOT NULL DEFAULT '' COMMENT '租户id',
      `is_delete` tinyint(4) DEFAULT '0' COMMENT '是否删除',
      `create_by` bigint(20) NOT NULL DEFAULT '0' COMMENT '创建人id',
      `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
      `update_by` bigint(20) DEFAULT NULL COMMENT '更新人id',
      `update_time` datetime DEFAULT NULL COMMENT '更新时间',
      PRIMARY KEY (`id`) USING BTREE
    ) ENGINE=InnoDB AUTO_INCREMENT=41 DEFAULT CHARSET=utf8mb4 COMMENT='表之间关联信息表';

##### 1.5.删除表 #####

#删除表
    DROP TABLE if  EXISTS table_name;

##### 1.6.清空表 #####

#清空表
    TRUNCATE TABLE ddl_task_record;

##### 1.7.修改表的字符集 #####

alter table <表名> character set <字符集>; #修改表的字符集

##### 1.8.修改表名 #####

ALTER TABLE dim_bl_week_info_2 RENAME  ads_dim_financial_year_week_info;

#### 2.字段信息 ####

##### 2.1.新增列 #####

#新增列
    alter table rv.rv_schema_table add column `original_table_name` varchar(100) default null COMMENT '原始视图名' after `table_name`;

##### 2.2.更新列 #####

#修改注释
    ALTER TABLE `rv`.ddl_task MODIFY create_by VARCHAR (22) NOT NULL COMMENT '创建人';
    ALTER TABLE `rv`.ddl_task MODIFY update_by VARCHAR (22) NOT NULL COMMENT '更新人';
    
    ALTER TABLE `rv`.ddl_task_record MODIFY stdout_log LONGTEXT default NULL COMMENT '执行日志';
    ALTER TABLE `rv`.ddl_task_record MODIFY stderr_log LONGTEXT default NULL COMMENT '错误日志';
    ALTER TABLE `rv`.ddl_task_record MODIFY error LONGTEXT default NULL COMMENT '错误日志';

##### 2.3.删除列 #####

#删除字段
    ALTER TABLE `rv`.`ddl_task` DROP res_type;

##### 2.4.新增行 #####

#新增行
    INSERT INTO table_name ( column1 ,...) VALUES ( value1,...);

##### 2.5.更新行 #####

#更新数据
    UPDATE dct_logs SET operate_type = '更新' WHERE operate_type = 'UPDATE';
    
    UPDATE ddl_task_record SET stdout_log  = '' WHERE length(stdout_log)>100000;
    
    update rv_schema_table_column set column_type=0 where `column_type` ='null';

#更新多个字段
    UPDATE test_end_of_quarter_sell_out_rate_01
    SET product_year_name=2022
      , season_name      = '春'
    WHERE 1 = 1
    ;

##### 2.6.删除行 #####

#删除行数据
    DELETE FROM table_name WHERE some_column = some_value;

#### 3.索引信息 ####

##### 3.1.新增索引 #####

create index ddl_task_record_task_id_index
        on ddl_task_record (task_id);

CREATE INDEX ddl_task_record_start_run_time_IDX USING BTREE ON rv.ddl_task_record (start_run_time);

##### 3.2.删除索引 #####

#删除索引
    ALTER TABLE table_name DROP INDEX index_name;

##### 3.3.新建约束 #####

*  主键约束:primary key
 *  唯一约束:unique
 *  非空约束:not unll
 *  外键约束: foreign key 一张表中有一个非主键的字段指向了别一张表中的主键，就将该字段叫做外键

### 四.SQL 语句 ###

#### 1.普通查询 ####

SELECT * FROM `dct_logs`;

#### 2.模糊查询 ####

select *
    from rv.rv_schema_table
    where table_name like '%tm01%';

#### 3.查询今天 9 点 ####

SELECT curdate()                                                                   AS cur_date
         , date_add(curdate(), INTERVAL 9 HOUR)                                        AS cur_9
         , date_add(curdate(), INTERVAL 9.30 hour_minute)                              AS cur_9_30
         , date_add(date_add(curdate(), INTERVAL 9.30 HOUR_MINUTE), INTERVAL 9 SECOND) AS cur_9_30_9
         , DATE_ADD(DATE_SUB(CURDATE(), INTERVAL 1 DAY), INTERVAL 9 HOUR)              AS yes_9
    ;

#### 4.版本和当前日期 ####

select version(),current_date; //版本和当前日期

#### 5.查看等待锁的语句 ####

SELECT *
    FROM performance_schema.events_statements_history
    WHERE thread_id IN (
        SELECT b.`THREAD_ID`
        FROM sys.`innodb_lock_waits` AS a
           , performance_schema.threads AS b
        WHERE a.waiting_pid = b.`PROCESSLIST_ID`)
    ORDER BY timer_start ASC;

#### 6.持有锁的语句 ####

SELECT *
    FROM performance_schema.events_statements_history
    WHERE thread_id IN (
        SELECT b.`THREAD_ID`
        FROM sys.`innodb_lock_waits` AS a
           , performance_schema.threads AS b
        WHERE a.`blocking_pid` = b.`PROCESSLIST_ID`)
    ORDER BY timer_start ASC;

#### 7.查看锁信息 ####

# 查看锁信息
    SELECT *
    FROM information_schema.INNODB_TRX;
    SELECT *
    FROM `sys`.`innodb_lock_waits`;
    SELECT *
    FROM performance_schema.data_locks;
    SELECT *
    FROM performance_schema.data_lock_waits;

#### 8.CTE ####

公共表达式（CTE，Common Table Expression）

#### 9.group by ####

SELECT deptname, SUM(original_inv - id)
    FROM kwan.tb_dept
    GROUP BY deptname
    ;
    
    SELECT deptname, SUM(original_inv) - SUM(id)
    FROM kwan.tb_dept
    GROUP BY deptname
    ;

#### 10.常用时间函数 ####

-- 今天
    SELECT DATE_FORMAT(NOW(),'%Y-%m-%d 00:00:00') AS '今天开始';
    SELECT DATE_FORMAT(NOW(),'%Y-%m-%d 23:59:59') AS '今天结束';
    
    -- 昨天
    SELECT DATE_FORMAT( DATE_SUB(CURDATE(), INTERVAL 1 DAY), '%Y-%m-%d 00:00:00') AS '昨天开始';
    SELECT DATE_FORMAT( DATE_SUB(CURDATE(), INTERVAL 1 DAY), '%Y-%m-%d 23:59:59') AS '昨天结束';
    
    -- 上周
    SELECT DATE_FORMAT( DATE_SUB( DATE_SUB(CURDATE(), INTERVAL WEEKDAY(CURDATE()) DAY), INTERVAL 1 WEEK), '%Y-%m-%d 00:00:00') AS '上周一';
    SELECT DATE_FORMAT( SUBDATE(CURDATE(), WEEKDAY(CURDATE()) + 1), '%Y-%m-%d 23:59:59') AS '上周末';
    
    -- 本周
    SELECT DATE_FORMAT( SUBDATE(CURDATE(),DATE_FORMAT(CURDATE(),'%w')-1), '%Y-%m-%d 00:00:00') AS '本周一';
    SELECT DATE_FORMAT( SUBDATE(CURDATE(),DATE_FORMAT(CURDATE(),'%w')-7), '%Y-%m-%d 23:59:59') AS '本周末';
    -- 上面的本周算法会有问题,因为mysql是按照周日为一周第一天,如果当前是周日的话,会把时间定为到下一周.
    SELECT DATE_FORMAT( DATE_SUB(CURDATE(), INTERVAL WEEKDAY(CURDATE()) DAY), '%Y-%m-%d 00:00:00') AS '本周一';
    SELECT DATE_FORMAT( DATE_ADD(SUBDATE(CURDATE(), WEEKDAY(CURDATE())), INTERVAL 6 DAY), '%Y-%m-%d 23:59:59') AS '本周末';
    
    -- 上月
    SELECT DATE_FORMAT( DATE_SUB(CURDATE(), INTERVAL 1 MONTH), '%Y-%m-01 00:00:00') AS '上月初';
    SELECT DATE_FORMAT( LAST_DAY(DATE_SUB(CURDATE(), INTERVAL 1 MONTH)), '%Y-%m-%d 23:59:59') AS '上月末';
    
    -- 本月
    SELECT DATE_FORMAT( CURDATE(), '%Y-%m-01 00:00:00') AS '本月初';
    SELECT DATE_FORMAT( LAST_DAY(CURDATE()), '%Y-%m-%d 23:59:59') AS '本月末';

#### 11.赋值操作 ####

需求:

*  知道 27 周和 53 周的数据,填充其他周的数据.
 *  要求同财年的时候 27 和 53 周不变
 *  同财年的时候,大于 27,小于 53,取 27 周的数据
 *  不同财年,大于 53 周,小于 27 周,取上一次财年 53 周数据

ON (
      (t1.financial_year = t2.financial_year AND t1.financial_year_week >= t2.financial_year_week AND
       t1.financial_year_week >= 27 AND t1.financial_year_week < 53)
      OR (t1.financial_year = t2.financial_year AND t1.financial_year_week = 53 AND
          t1.financial_year_week = t2.financial_year_week)
      OR (t1.financial_year = t2.financial_year + 1 AND t1.financial_year_week < 27 AND
          t2.financial_year_week = 53)
    ) --27和27相同  t1: 28   t2:27     t1:53  t2
             AND t1.gender_name = t2.gender_name
             AND t1.category_name1 = t2.category_name1
             AND t1.brand_detail_no = t2.brand_detail_no

#### 12.重复数据 ####

SELECT period_sdate, product_key, size_code, managing_city_no
    FROM ads_sense_rep.ads_day_city_brand_sku_size_rep
    GROUP BY period_sdate, product_key, size_code, managing_city_no
    HAVING COUNT(*) > 1;

#### 13.distinct ####

select distinct name from A;
    
    select distinct name, id from A;
    
    --表中name去重后的数目
    select count(distinct name) from A;
    
    select count(distinct name, id) from A;

#### 14.统计数量 ####

SELECT financial_year, financial_week, COUNT(1)
    FROM `default`.xxxxx
    GROUP BY financial_year, financial_week
    ORDER BY financial_year, financial_week
    ;

#### 15.join ####

只写join,不会报错,会产生笛卡尔积

select * from a join b;

### 五.安装 mysql ###

#### 1.卸载步骤 ####

首先,查看一下是否装过 mysql,如果装过 mysql,需要卸载干净

rpm -qa | grep -i mysql

如果查询到,则删除

#删除
    rpm -e --nodeps mysql-errmsg-8.0.26-1.module_el8.4.0+915+de215114.x86_64

查找相关文件

#查找相关文件
    find / -name mysql

将上面查询到的文件删除

#删除文件
    rm -rf /etc/selinux/targeted/active/modules/100/mysql

再次验证是否装过 mysql

#验证
    rpm -qa | grep -i mysql

#### 2.安装步骤 ####

创建 mysql 目录,用于存储下载的 mysql 文件

cd /usr/local/

下载 mysql 文件

wget https://dev.mysql.com/get/mysql80-community-release-el8-4.noarch.rpm

安装 yum 储存库

rpm -Uvh mysql80-community-release-el8-4.noarch.rpm

禁用默认 MySQL 模块

yum -y module disable mysql

安装 mysql-community-server

yum -y install mysql-community-server

启动 mysql 服务器

systemctl start mysqld

检查 mysql 服务器状态

systemctl status mysqld

#### 3.修改密码 ####

#查看临时密码
    cat /var/log/mysqld.log | grep password
    
    #使用临时密码登录
    mysql -u root -p
    
    #修改密码，需要满足强度要求
    alter user 'root'@'localhost' identified by '你的密码';
    
    
    #新密码登录测试
    mysql -u root -p
    
    #密码策略设置为LOW，表示只验证密码长度，对密码的数字、字母都没要求。
    set global validate_password.policy=LOW;
    
    #密码长度设置为自己想要的长度，表示密码最少有几位。
    set global validate_password.length=7;
    
    #修改密码，只需要大于设置的长度就可以。
    alter user 'root'@'localhost' identified by '你的密码';

#### 4.远程连接 ####

#访问权限查询
    select user,host from mysql.user;
    
    #root用户的localhost表示只允许本机访问，要实现远程连接，可以将用户的host改为%，表示允许任意主机访问，如果需要设置只允许特定ip访问，则应改为对应的ip。
    
    
    #修改root用户的host字段为%
    update mysql.user set host="%" where user="root";
    
    #刷新配置
    flush privileges;
    
    
    #如果是物理服务器：
    #查询3306端口是否开放
    firewall-cmd --query-port=3306/tcp
    #开放
    firewall-cmd --zone=public --add-port=3306/tcp --permanent
    #重新加载防火墙设置
    firewall-cmd --reload
    #云服务器直接开放端口号3306
    
    #navicat/dbeaver测试能否连接

### 六.关键字 ###

#### 1.存储过程实现指定周区间 ####

-- 使用前先删除存储过程，因为创建存储过程后，存储过程会一直存在于mysql中
    DROP PROCEDURE IF EXISTS test_procedure_insert_week;
    -- 标记上面的命令执行结束  #声明;;为结束标志
    DELIMITER;;
    
    -- 创建存储过程
    CREATE PROCEDURE test_procedure_insert_week()
    BEGIN
        DECLARE
            i DATE;
        DECLARE
            next_year DATE;
        DECLARE
            week_count INT;
        DECLARE
            year INT;
        DECLARE
            last_day DATE;
        SET
            i = '2018-03-01';
        SET
            week_count = 1;
        SET
            year = YEAR(i);
        WHILE
            i < '2025-03-01'
            DO
                SET
                    next_year = STR_TO_DATE(CONCAT((year + 1), '-03-01'), '%Y-%m-%d');
                SET
                    i = IF(i >= next_year, next_year, i); #日期
                SET
                    year = IF(i >= next_year, year + 1, year); #财年
                SET
                    week_count = IF(i >= next_year, 1, week_count);
                SET
                    last_day = IF(DATE_ADD(i, INTERVAL 6 DAY) >= next_year, LAST_DAY(i),
                                  DATE_ADD(i, INTERVAL 6 DAY));
                INSERT INTO `kwan`.`dim_bl_week_info` (week_start_day, week_end_day, financial_year_week, financial_year)
                VALUES (i, last_day, week_count, year);
                SET
                    i = DATE_ADD(i, INTERVAL 7 DAY);
                SET
                    week_count = week_count + 1;
            END WHILE;
    END;;
    
    # 调用无参的存储过程，当然也可以设置参数，调用时也需要传递参数
    CALL test_procedure_insert_week();

#### 2.字符串拼接 ####

select concat("aaa","bbbb","ccccc")  as str

#### 3.字符串转日期 ####

select  STR_TO_DATE(CONCAT((year + 1), '-03-01'), '%Y-%m-%d');

**使用 str\_to\_date() 函数**

str\_to\_date() 是专门的字符串转日期函数，可以将字符串转换为日期时间值。

str_to_date(str, format)

*  str： 必须项。要格式化为日期的字符串
 *  format： 必须项。要使用的格式。可以是以下值中的一个或组合：

<table> 
 <thead> 
  <tr> 
   <th>format 格式</th> 
   <th>描述</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>％a</td> 
   <td>工作日缩写名称（周日至周六）</td> 
  </tr> 
  <tr> 
   <td>％b</td> 
   <td>缩写的月份名称（1 月至 12 月）</td> 
  </tr> 
  <tr> 
   <td>％C</td> 
   <td>数字月份名称（0 到 12）</td> 
  </tr> 
  <tr> 
   <td>％D</td> 
   <td>作为数值的月中的某一天，后跟后缀（第 1，第 2，第 3，…）</td> 
  </tr> 
  <tr> 
   <td>％d</td> 
   <td>作为数值的月份日期（01 到 31）</td> 
  </tr> 
  <tr> 
   <td>％e</td> 
   <td>每月的某一天作为数值（0 到 31）</td> 
  </tr> 
  <tr> 
   <td>％f</td> 
   <td>微秒（000000 至 999999）</td> 
  </tr> 
  <tr> 
   <td>％H</td> 
   <td>小时（00 到 23）</td> 
  </tr> 
  <tr> 
   <td>％h</td> 
   <td>小时（00 到 12）</td> 
  </tr> 
  <tr> 
   <td>%I</td> 
   <td>小时（00 到 12）</td> 
  </tr> 
  <tr> 
   <td>％i</td> 
   <td>分钟（00 至 59）</td> 
  </tr> 
  <tr> 
   <td>％j</td> 
   <td>一年中的某一天（001 至 366）</td> 
  </tr> 
  <tr> 
   <td>％k</td> 
   <td>小时（0 到 23）</td> 
  </tr> 
  <tr> 
   <td>％l</td> 
   <td>小时（1 到 12）</td> 
  </tr> 
  <tr> 
   <td>％M</td> 
   <td>月份名称（1 月至 12 月）</td> 
  </tr> 
  <tr> 
   <td>％m</td> 
   <td>月份名称作为数值（00 到 12）</td> 
  </tr> 
  <tr> 
   <td>％p</td> 
   <td>上午或下午</td> 
  </tr> 
  <tr> 
   <td>％r</td> 
   <td>时间为 12 小时 AM 或 PM 格式（hh：mm：ss AM / PM）</td> 
  </tr> 
  <tr> 
   <td>％S</td> 
   <td>秒（00 到 59）</td> 
  </tr> 
  <tr> 
   <td>％s</td> 
   <td>秒（00 到 59）</td> 
  </tr> 
  <tr> 
   <td>％T</td> 
   <td>24 小时格式的时间（hh：mm：ss）</td> 
  </tr> 
  <tr> 
   <td>％U</td> 
   <td>星期日是星期的第一天（00 到 53）的星期</td> 
  </tr> 
  <tr> 
   <td>％u</td> 
   <td>星期一是一周的第一天（00 到 53）的星期</td> 
  </tr> 
  <tr> 
   <td>％V</td> 
   <td>星期日是星期的第一天（01 到 53）的星期。与％X 一起使用</td> 
  </tr> 
  <tr> 
   <td>％v</td> 
   <td>星期一是星期的第一天（01 到 53）的星期。与％X 一起使用</td> 
  </tr> 
  <tr> 
   <td>％W</td> 
   <td>工作日名称全部（周日至周六）</td> 
  </tr> 
  <tr> 
   <td>％w</td> 
   <td>星期日 = 0 和星期六 = 6 的星期几</td> 
  </tr> 
  <tr> 
   <td>％X</td> 
   <td>星期日是一周的第一天的一周。与％V 一起使用</td> 
  </tr> 
  <tr> 
   <td>％x</td> 
   <td>星期一是一周的第一天的一周。与％V 一起使用</td> 
  </tr> 
  <tr> 
   <td>％Y</td> 
   <td>年份为数字，4 位数值</td> 
  </tr> 
  <tr> 
   <td>％y</td> 
   <td>年份为数字，2 位数值</td> 
  </tr> 
 </tbody> 
</table>

STR\_TO\_DATE()函数扫描输入字符串来匹配格式字符串。格式字符串可能包含以百分比 (%) 字符开头的文字字符和格式说明符。

#### 4.获取年 ####

select YEAR(i);

#### 5.加 7 天 ####

select DATE_ADD(i, INTERVAL 6 DAY);
    
    #前100天
    select DATE_ADD(NOW() , INTERVAL -100 DAY);

#### 6.这个月最后一天 ####

select  LAST_DAY(i);

#### 7.升降序 ####

asc表示升序
    desc表示降序
    多字段:order by(字段一) （升序/降序），（字段二）（升序/降序），（字段n）（升序/降序）

#### 8.多数据源 ####

import com.baomidou.dynamic.datasource.annotation.DS;
    @DS("data")
    @Service
    @Slf4j
    public class BrandCategoryServiceImpl implements BrandCategoryService {
        }

spring:
      datasource:
        dynamic:
          # 设置默认的数据源或数据源组，默认值即为master
          primary: biz
          # 严格匹配数据源，默认false，true未匹配到指定数据源时抛异常，false使用默认数据源
          strict: false
          datasource:
            biz:
              driver-class-name: com.mysql.cj.jdbc.Driver
              url: jdbc:mysql://xxxx:3306/insight_test?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=GMT%2B8&allowMultiQueries=true
              username: test
              password: test
              type: com.zaxxer.hikari.HikariDataSource
              hikari:
                minimum-idle: 1
                maximum-pool-size: 15
                idle-timeout: 3000
                max-lifetime: 1800000
                connection-test-query: SELECT 1
            data:
              driver-class-name: ru.yandex.clickhouse.ClickHouseDriver
              url: jdbc:clickhouse://xxxx:80/default?socket_timeout=3000000&max_memory_usage=100000000000
              username: default
              password: xxxx
              type: com.zaxxer.hikari.HikariDataSource
              hikari:
                minimum-idle: 1
                maximum-pool-size: 15
                idle-timeout: 3000
                max-lifetime: 1800000
                connection-test-query: SELECT 1

#### 9.Lag 函数和 lead 函数 ####

lag() over() 与 lead() over() 函数是跟偏移量相关的两个分析函数，通过这两个函数可以在一次查询中取出同一字段的前 N 行的数据(lag)和后 N 行的数据(lead)作为独立的列,从而更方便地进行进行数据过滤。这种操作可以代替表的自联接，并且 LAG 和 LEAD 有更高的效率。

Lag()就是取当前顺序的上一行记录。结合over就是分组统计数据的。

lead(t.create_date,1, null) over( partition by  cphm order by create_date asc ) t2

#### 10.前一天数据 ####

SELECT DATE_SUB(CURDATE(),INTERVAL 1 DAY);

#### 11.星期 ####

##### 1.DATE\_FORMAT #####

SELECT
    CASE
    		DATE_FORMAT(NOW(),'%w')
    		WHEN 1 THEN '星期一'
    		WHEN 2 THEN '星期二'
    		WHEN 3 THEN '星期三'
    		WHEN 4 THEN '星期四'
    		WHEN 5 THEN '星期五'
    		WHEN 6 THEN '星期六'
    		WHEN 0 THEN '星期日'
    	END
    as week
    ;

##### 2.DAYOFWEEK #####

select DAYOFWEEK('2020-07-02');

![日期	周一	周二	周三	周四	周五	周六	周日 返回值	2	3	4	5	6	7	1][_2_3_4_5_6_7_1]

##### 3.WEEKDAY #####

select WEEKDAY('2020-07-02');

![在这里插入图片描述][685c977f24b2164a8b84a9ff366eeffc.png]

### 七.特殊 SQL ###

#### 1.列转行 ####

列转行 sql

SELECT t4.`column_name`, t4.`column_value`, t4.`id`, t4.`name`, t4.`suject`, t4.`rv_sync_create_time`
    FROM ((SELECT 'class'    AS `column_name`,
                  t3.`class` AS `column_value`,
                  `column_name`,
                  `column_value`,
                  t3.`id`,
                  t3.`name`,
                  t3.`suject`,
                  t3.`class`,
                  t3.`rv_sync_create_time`
           FROM `default`.`aloong_class` AS t3)) AS t4

列转行结果

列转行结果会新增 2 列，一列是被转的列名，一列是被转的列值。上述事例中是将 class 这一列转为行，并且输出的结果中是没有 class 这一列的，它变成了行值，column\_name 这个新列。column\_value 是原来 class 列的值。

#### 2.行转列 ####

#建表  带注释+创建时间
    DROP TABLE IF EXISTS `Tmark`;
    
    # 新建表
    CREATE TABLE Tmark
    (
        Name   VARCHAR(10),
        Course VARCHAR(10),
        Score  INT
    );
    INSERT INTO Tmark
    VALUES ('张三', '语文', 74);
    INSERT INTO Tmark
    VALUES ('张三', '数学', 83);
    INSERT INTO Tmark
    VALUES ('张三', '物理', 93);
    INSERT INTO Tmark
    VALUES ('李四', '语文', 74);
    INSERT INTO Tmark
    VALUES ('李四', '数学', 84);
    INSERT INTO Tmark
    VALUES ('李四', '物理', 94);
    INSERT INTO Tmark
    VALUES ('王五', '语文', 86);
    INSERT INTO Tmark
    VALUES ('王五', '数学', NULL);
    INSERT INTO Tmark
    VALUES ('王五', '物理', NULL);
    
    SELECT *
    FROM Tmark;

**行转列 sql**

SELECT Name
         , SUM(CASE WHEN Course = '语文' THEN Score ELSE 0 END) AS 语文
         , SUM(CASE WHEN Course = '数学' THEN Score ELSE 0 END) AS 数学
         , SUM(CASE WHEN Course = '物理' THEN Score ELSE 0 END) AS 物理
    FROM Tmark
    GROUP BY Name;

![image-20221208135216030][]

#### 3.合并行 ####

合并行 sql

select t4.`name`,
           t4.`classes`,
           t4.`rv_sync_create_time`,
           t4.`subject`,
           t4.`fraction`,
           t4.`hobby`
    from ((
              SELECT t1.`name`,
                     t1.`classes`,
                     t1.`rv_sync_create_time`,
                     NULL as `subject`,
                     NULL as `fraction`,
                     NULL as `hobby`
              FROM `wy_class` as t1)
          union all
          (
              SELECT t3.`name`,
                     NULL as `classes`,
                     t3.`rv_sync_create_time`,
                     t3.`subject`,
                     t3.`fraction`,
                     t3.`hobby`
              FROM `wy_pivot_hobby` as t3))
             as t4

合并行结果

wy\_class 表有 3 个字段，wy\_pivot\_hobby 表有 5 个字段，2 个表有 2 个相同的字段 name 和 rv\_sync\_create\_time，

**输出结果是 6 个字段**name，classes，rv\_sync\_create\_time，subject，hobby，fraction。

使用 union 和 union all 必须保证各个 select 集合的结果有相同个数的列，并且每个列的类型是一样的。但列名则不一定需要相同，oracle 会将第一个结果的列名作为结果集的列名。上述输出 6 个不是必须的，也可以用 name 对应 classes，只需要输出的列个数一致即可，不够的(不存在的)字段需要用 null 替换。

**UNION 和 UNION ALL 对比**

1.对重复结果的处理：UNION 会去掉重复记录，UNION ALL 不会；

2.对排序的处理：UNION 会排序，UNION ALL 只是简单地将两个结果集合并；

3.效率方面的区别：因为 UNION 会做去重和排序处理，因此效率比 UNION ALL 慢很多；

#### 4.case when 加入 in ####

SELECT
    	  IFNULL( COUNT( CASE WHEN T.state IN ( 1, 2, 3, 4, 5, 6 ) THEN 1 END), 0 ) exception,
    		IFNULL( COUNT( CASE T.state WHEN 1 THEN 1 END ), 0 ) deal,
    		IFNULL( COUNT( CASE T.state WHEN 2 THEN 1 END ), 0 ) audit,
    		IFNULL( COUNT( CASE T.state WHEN 4 THEN 1 END ), 0 ) finish
    	FROM
    		o_alert T
    	WHERE
    		T.is_deleted = 0

#### 5.日期 ####

##### 1.date\_format 函数 #####

SELECT DATE_FORMAT(SYSDATE(),'%Y-%m-%d %H:%i:%s') from dual;

##### 2.date\_format 转换 #####

<table> 
 <thead> 
  <tr> 
   <th align="left">格式</th> 
   <th align="left">描述</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="left">%a</td> 
   <td align="left">缩写星期名</td> 
  </tr> 
  <tr> 
   <td align="left">%b</td> 
   <td align="left">缩写月名</td> 
  </tr> 
  <tr> 
   <td align="left">%c</td> 
   <td align="left">月，数值</td> 
  </tr> 
  <tr> 
   <td align="left">%D</td> 
   <td align="left">带有英文前缀的月中的天</td> 
  </tr> 
  <tr> 
   <td align="left">%d</td> 
   <td align="left">月的天，数值(00-31)</td> 
  </tr> 
  <tr> 
   <td align="left">%e</td> 
   <td align="left">月的天，数值(0-31)</td> 
  </tr> 
  <tr> 
   <td align="left">%f</td> 
   <td align="left">微秒</td> 
  </tr> 
  <tr> 
   <td align="left">%H</td> 
   <td align="left">小时 (00-23)</td> 
  </tr> 
  <tr> 
   <td align="left">%h</td> 
   <td align="left">小时 (01-12)</td> 
  </tr> 
  <tr> 
   <td align="left">%I</td> 
   <td align="left">小时 (01-12)</td> 
  </tr> 
  <tr> 
   <td align="left">%i</td> 
   <td align="left">分钟，数值(00-59)</td> 
  </tr> 
  <tr> 
   <td align="left">%j</td> 
   <td align="left">年的天 (001-366)</td> 
  </tr> 
  <tr> 
   <td align="left">%k</td> 
   <td align="left">小时 (0-23)</td> 
  </tr> 
  <tr> 
   <td align="left">%l</td> 
   <td align="left">小时 (1-12)</td> 
  </tr> 
  <tr> 
   <td align="left">%M</td> 
   <td align="left">月名</td> 
  </tr> 
  <tr> 
   <td align="left">%m</td> 
   <td align="left">月，数值(00-12)</td> 
  </tr> 
  <tr> 
   <td align="left">%p</td> 
   <td align="left">AM 或 PM</td> 
  </tr> 
  <tr> 
   <td align="left">%r</td> 
   <td align="left">时间，12-小时（hh:mm:ss AM 或 PM）</td> 
  </tr> 
  <tr> 
   <td align="left">%S</td> 
   <td align="left">秒(00-59)</td> 
  </tr> 
  <tr> 
   <td align="left">%s</td> 
   <td align="left">秒(00-59)</td> 
  </tr> 
  <tr> 
   <td align="left">%T</td> 
   <td align="left">时间, 24-小时 (hh:mm:ss)</td> 
  </tr> 
  <tr> 
   <td align="left">%U</td> 
   <td align="left">周 (00-53) 星期日是一周的第一天</td> 
  </tr> 
  <tr> 
   <td align="left">%u</td> 
   <td align="left">周 (00-53) 星期一是一周的第一天</td> 
  </tr> 
  <tr> 
   <td align="left">%V</td> 
   <td align="left">周 (01-53) 星期日是一周的第一天，与 %X 使用</td> 
  </tr> 
  <tr> 
   <td align="left">%v</td> 
   <td align="left">周 (01-53) 星期一是一周的第一天，与 %x 使用</td> 
  </tr> 
  <tr> 
   <td align="left">%W</td> 
   <td align="left">星期名</td> 
  </tr> 
  <tr> 
   <td align="left">%w</td> 
   <td align="left">周的天 （0=星期日, 6=星期六）</td> 
  </tr> 
  <tr> 
   <td align="left">%X</td> 
   <td align="left">年，其中的星期日是周的第一天，4 位，与 %V 使用</td> 
  </tr> 
  <tr> 
   <td align="left">%x</td> 
   <td align="left">年，其中的星期一是周的第一天，4 位，与 %v 使用</td> 
  </tr> 
  <tr> 
   <td align="left">%Y</td> 
   <td align="left">年，4 位</td> 
  </tr> 
  <tr> 
   <td align="left">%y</td> 
   <td align="left">年，2 位</td> 
  </tr> 
 </tbody> 
</table>

##### 3.str\_to\_date #####

-- str_to_date()函数字符串转换为date
    str_to_date('2016-12-15 16:48:40','%Y-%m-%d %H:%i:%S')

##### 4.日期加减 #####

select date_add(now(), interval 1 day); -- 加1天
    
    select date_add(now(), interval 1 hour); -- 加1小时
    
    select date_add(now(), interval 1 minute); -- 加1分钟
    
    select date_add(now(), interval 1 second); -- 加1秒
    
    select date_add(now(), interval 1 microsecond);-- 加1毫秒
    
    select date_add(now(), interval 1 week);-- 加1周
    
    select date_add(now(), interval 1 month);-- 加1月
    
    select date_add(now(), interval 1 quarter);-- 加1季
    
    select date_add(now(), interval 1 year);-- 加1年
    
    -- MySQL 为日期减去一个时间间隔：date_sub()
    select date_sub(now(), interval 1 year);-- 减去1年

##### 5.当前时间 #####

now()

#### 6.lag 和 lead ####

over()表示 lag()与 lead()操作的数据都在 over()的范围内，他里面可以使用 partition by 语句（用于分组） order by 语句（用于排序）。partition by a order by b 表示以 a 字段进行分组，再 以 b 字段进行排序，对数据进行查询。

例如：**lead(field, num, defaultvalue) field 需要查找的字段，num 往后查找的 num 行的数据，defaultvalue 没有符合条件的默认值**

##### 1.建表 #####

SHOW TABLES;
    #建表  带注释+创建时间
    DROP TABLE IF EXISTS `dim_bl_week_info_001`;
    
    # 新建表
    CREATE TABLE `dim_bl_week_info_001`
    (
        `id`                  int(11) NOT NULL AUTO_INCREMENT COMMENT '自增主键',
        `week_start_day`      date    DEFAULT NULL COMMENT '周开始',
        `week_end_day`        date    DEFAULT NULL COMMENT '周结束',
        `financial_year_week` int(32) DEFAULT NULL COMMENT '第n财年周',
        `financial_year`      int(32) DEFAULT NULL COMMENT '财年',
        PRIMARY KEY (`id`)
    ) ENGINE = InnoDB
      DEFAULT CHARSET = utf8;
    
    TRUNCATE TABLE dim_bl_week_info_001;
    INSERT INTO dim_bl_week_info_001 (week_start_day, week_end_day, financial_year_week, financial_year)
    VALUES ('2022-10-01', '2022-10-01', 1, 1)
         , ('2022-10-01', '2022-10-02', 2, 2)
         , ('2022-10-01', '2022-10-03', 3, 3);
    
    SELECT *
    FROM dim_bl_week_info_001;

##### 2.获取前一行的数据 #####

#前
    SELECT id, LAG(id, 1, NULL) OVER (PARTITION BY week_start_day ORDER BY financial_year_week ASC ) next_same_cphm_id
    FROM dim_bl_week_info_001
    ORDER BY id ASC
    ;

![image-20221207165743863][]

##### 3.获取后一行的数据 #####

#后
    SELECT id, LEAD(id, 1, NULL) OVER (PARTITION BY week_start_day ORDER BY financial_year_week ASC ) next_same_cphm_id
    FROM dim_bl_week_info_001
    ORDER BY id ASC
    ;

![image-20221207165732721][]

### 八.每日一题 ###

#### 1.请假天数 ####

SELECT t2.name
         , LEFT(t2.str, 7)
         , COUNT(1) AS r -- 通过姓名和 月份分组,统计天数
    FROM (
             SELECT t1.*, DATE_ADD(t1.start_date, INTERVAL cn DAY) AS str -- 从开始日期开始累加
             FROM (
                      SELECT a.*, ROW_NUMBER() OVER (PARTITION BY name,start_date) - 1 AS cn, t.help_topic_id -- 开窗函数排序
                      FROM (
                               SELECT *, DATEDIFF(end_date, start_date) + 1 AS days
                               FROM day_emp_leave -- 查询请假天数间隔
                           ) a
                               LEFT JOIN mysql.help_topic t -- 倒序
                                         ON t.help_topic_id > 0
                                             AND t.help_topic_id <= a.days
                  ) t1
         ) t2
    GROUP BY t2.name, LEFT(t2.str, 7);

![image-20230106214530018][]

#### 2.最多好友 ####

-- 新建表
    CREATE TABLE `day_most_friends`
    (
        `requester_id` INT(3) DEFAULT NULL COMMENT '请求人',
        `accepter_id`  INT(3) DEFAULT NULL COMMENT '接受人',
        `accept_date`  date   DEFAULT NULL COMMENT '接受日期'
    ) ENGINE = InnoDB
      DEFAULT CHARSET = utf8;
    -- 查询
    SELECT *
    FROM day_most_friends;
    -- 清空表
    TRUNCATE TABLE day_most_friends;
    -- 插入数据
    INSERT INTO day_most_friends (requester_id, accepter_id, accept_date)
    VALUES (1, 2, '2016-06-03')
         , (1, 3, '2016-06-08')
         , (2, 3, '2016-06-08')
         , (3, 4, '2016-06-09');

-- 查询所有数据
    SELECT a.IDS AS ID, COUNT(*) AS NUM
    FROM (
             SELECT requester_id AS IDS
             FROM day_most_friends
             UNION ALL
             SELECT accepter_id AS IDS
             FROM day_most_friends
         ) a
    GROUP BY a.IDS
    ORDER BY COUNT(*);
    
    -- 只查询一条数据
    SELECT a.IDS AS ID, COUNT(*) AS NUM
    FROM (
             SELECT requester_id AS IDS
             FROM day_most_friends
             UNION ALL
             SELECT accepter_id AS IDS
             FROM day_most_friends
         ) a
    GROUP BY a.IDS
    ORDER BY COUNT(*) DESC
    LIMIT 1;

[image-20221216172959649]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/08/60d1a07d50984505b453c3a75ab519bf.png
[image-20220815145218233]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/08/ac842ffd40c34404a56fcdd8d726f64b.png
[image-20220813130013116]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/08/523afaf1b9504124b467c0d4ee085c64.png
[_2_3_4_5_6_7_1]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/08/2e5832c90e164a3c84c1fcf5f671ea3c.png
[685c977f24b2164a8b84a9ff366eeffc.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/08/f8b011c6b3b14649920488c8d1f8ef13.png
[image-20221208135216030]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/08/5fd783a50cbb4be59b99637d6e76ab6e.png
[image-20221207165743863]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/08/b693b65623e54f70befc94b3071f595f.png
[image-20221207165732721]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/08/9015ea98c1ee4ba5b2897adc3c1b6b04.png
[image-20230106214530018]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/08/eb0f95e4d079421985366be46bad58ff.png