MySQL实战开发技巧

╰+攻爆jí腚メ 2022-05-29 06:54 210阅读 0赞

29、设置自增起始值

alter table xxx AUTO_INCREMENT=1;

28、查询表字段，并转换，注意我的例子中，最后都以逗号结尾

SELECT
    concat ( COLUMN_NAME, ' as ', "'", column_comment, "'", ',' ) columnName 
    FROM
    information_schema.COLUMNS 
    WHERE
    TABLE_SCHEMA = 'dbName' 
    AND TABLE_NAME = 'tbName';

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMwMDM4MTEx_size_16_color_FFFFFF_t_70]

27、查询表字段

select TABLE_NAME,GROUP_CONCAT(COLUMN_NAME) from information_schema.columns where TABLE_SCHEMA = '数据库名' group by TABLE_NAME
    select TABLE_NAME,GROUP_CONCAT(COLUMN_NAME) from information_schema.columns where TABLE_SCHEMA = '数据库名' and table_name = '表名' group by TABLE_NAME;

26、分区需要注意的地方

1.  如果数据是属于某个分区，但实际上并没有创建和这个分区，就会报错，例如`2019-09-11`属于第`36`周，`ERROR：1526 - Table has no partition for value 201936`
2.  增加分区`alter TABLE table_name add PARTITION (PARTITION p201953 VALUES LESS THAN MAXVALUE );`
3.  删除分区的同时也会将分区的数据删除`alter TABLE table_name drop PARTITION p201929 ;`
4.  按照范围分区时，在最后加上超过定义的最大范围的数据放在`maxvalue`分区内
5.  案例：按周进行分区
    
        CREATE TABLE `tb_push_record` (
          `msg_id` char(36) NOT NULL,
          `data_time` date NOT NULL COMMENT '推送时间',
          PRIMARY KEY (`msg_id`,`data_time`) USING BTREE
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT
        PARTITION BY RANGE (YEARWEEK(data_time)) (
        PARTITION p202001 VALUES LESS THAN (202001),
        PARTITION p202002 VALUES LESS THAN (202002),
        PARTITION p202003 VALUES LESS THAN (202003),
        ...
        PARTITION p202049 VALUES LESS THAN (202049),
        PARTITION p202050 VALUES LESS THAN (202050),
        PARTITION p202051 VALUES LESS THAN (202051),
        PARTITION p202052 VALUES LESS THAN (202052),
        PARTITION p202053 VALUES LESS THAN MAXVALUE );

25、`mysql`查看`innodb`最近一次的执行记录

show engine innodb status\G;

24、`mysql`中`datetime、date、timestamp`类型的列查询时是按照字符串查询的。`mysql`支持`java`使用`String`或`Date`传递参数，并查询数据

select * from tb where data_date between 2019-03-14 and 2019-03-14
    select * from tb where data_date >= '2019-03-14'

23、`count`计数时，如果与`group by`连用，那么`count`可能为`null`

22、`join`连接使用索引，避免全表扫描  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMwMDM4MTEx_size_16_color_FFFFFF_t_70 1]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMwMDM4MTEx_size_16_color_FFFFFF_t_70 2]

21、查看`mysql`日志路径

show variables like 'general_log_file';
    show variables like 'log_error';
    show variables like 'slow_query_log_file';

20、一次执行多个`sql`时，可能会出现重复的主键、表已经存在、不存在的列等等的错误，如何让`mysql`遇到这些问题时跳过并继续执行下面的命令？  
我们可以创建`.sql`文件，把所有的`sql`写入文件，使用`cmd`执行如下命令，其中`--force`是确保继续执行的关键，`keycloak`是数据库名。

mysql -uroot -proot --force keycloak < C:\Users\yangzhen\Desktop\keycloak-database-update.sql

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMwMDM4MTEx_size_16_color_FFFFFF_t_70 3]

19、数据库连接的`url`参数

jdbc:mysql://10.8.12.113:3306/keycloak?autoReconnect=true&useUnicode=true&createDatabaseIfNotExist=true&characterEncoding=UTF-8&useSSL=true

**在`xml`配置文件中，`&`符号需要转义：`&`**

jdbc:mysql://10.8.12.113:3306/keycloak?autoReconnect=true&useUnicode=true&createDatabaseIfNotExist=true&characterEncoding=UTF-8&useSSL=true

18、数据库授权

-- GRANT 权限 ON 数据库.表 TO '用户' @ 'ip' IDENTIFIED BY '密码' WITH GRANT OPTION;
    GRANT ALL PRIVILEGES ON *.* TO 'root' @'%' IDENTIFIED BY 'root' WITH GRANT OPTION;
    FLUSH PRIVILEGES;

17、`mysqldump`

mysqldump -h1.1.1.1 -uroot -p database_name table_name --where="condition1='1321312312981' and condition2 > '2018-10-23 07:32:00'"  > f:/data.sql
    注意：不能以分号结尾，否则报错：`mysqldump: Couldn't find table: ";"`

16、`insert into select`

语法：insert into table2(field1,field2,...) select value1,value2,... from table1
    案例：
    INSERT INTO db_test.tb_user ( user_id, user_name, app_id, app_code, create_time ) SELECT
    account_id,
    account_name,
    biz_code,
    biz_code,
    now() 
    FROM
    	db_test_old.tb_user_old;

15、查看表结构信息

-- 查询表结构信息
    SELECT * FROM information_schema.tables WHERE table_schema='database_name' AND table_name='table_name'
    
    -- 查询表中所有的列，按照表分组
    SELECT TABLE_SCHEMA,GROUP_CONCAT(COLUMN_NAME) FROM information_schema.`COLUMNS` WHERE table_schema='database_name' AND table_name='table_name' group by TABLE_SCHEMA

14、允许远程连接`mysql`

use mysql;
    select user,host from user;
    grant all privileges on *.* to 'root'@'%' identified by 'root';
    flush privileges;

13、`ON DUPLICATE KEY UPDATE`  
[详细使用方法，请点这里][Link 1]

1、命令行登录客户端

mysql -h localhost -u root -p database_name

2、命令行客户端修改密码

# 其中PASSWORD是一个加密函数
    update mysql.user set password=PASSWORD('123') where user='root';
    flush privileges;

3、`delete` 删除数据需要注意别名问题

# from 后指定了表的别名时，delete 后面必须要加上别名，否则有语法错误
    DELETE ui FROM user_info ui WHERE ui.name = '德莱文';

4、`update` 更新数据时，需要注意 `where` 后面的 `from` 子句不能包含要更新的表

# 错误演示
    UPDATE user_info a SET a.name = '文森特' WHERE a.id IN ( SELECT b.id FROM user_info b WHERE b.name = '德莱文' );
    # 正确演示
    UPDATE user_info a JOIN ( SELECT b.id FROM user_info b WHERE b.name = '德莱文' ) c ON a.id = c.id SET a.name = '文森特';

5、`count(*)`、`count(1)`、`count(column)` 区别  
参见：[count（\*）、count（1）、count（column）区别和执行效率][count_count_1_count_column]

6、`union` 和 `union all` 的联合查询  
    `union` 的查询结果不会有重复项，因为它的内部使用了一次 `distinct`，而 `union all` 的查询结果包含重复项。  
**注意：参与联合查询的各查询结果的列数必须相同，对应项的数据类型也必须相同**

7、`if` 相关的函数

# IF(expr,t f) 如果 expr 是真，返回 t；否则返回 f
    SELECT name, IF ( name = '文森特', '斧头', '无' ) '装备' FROM user_info
    
    # IFNULL(value1,value2) 如果 value1 为 null，返回 value2
    SELECT IFNULL(name, '无名') name FROM user_info

8、`distinct` 多列时只要有一列不同，则不去重

`distinct` 关键字应用于所有列而不是它后面的第一个列。一般情况下去重时，`distinct` 后面的列要包含 `unique` 的列，否则查询可能会出错。例如：查询入住信息，并标记这段时间内入住的新用户。新用户定义：在查询时间段开始的时间前没有入住过的用户，是新用户。例如下面的`SQL`，查询结果如下，`distinct`多列时只要有一列不同，则不去重

select distinct hch.user_id userId,a.newCustomer from tb_hotel_customer_history hch left join 
    (select user_id,name, case when check_in_time < '2019-11-18 00:00:00' then 2 else 1 end newCustomer from tb_hotel_customer_history  where group_id = 154898966604025856 and sub_group_id = 154959708522942464) a on hch.user_id = a.user_id
    where group_id = 154898966604025856 and sub_group_id = 154959708522942464 and hch.check_in_time >='2019-11-18 00:00:00' and hch.check_in_time <='2019-11-30 23:59:59'

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

SELECT
    	a.user_id,
    CASE
    	WHEN ISNULL( b.user_id ) THEN
    	1 ELSE 2 
    	END newUser 
    FROM
    	( SELECT DISTINCT user_id FROM tb_hotel_user WHERE check_in_time >= '2019-10-03 00:00:00' AND check_in_time <= '2019-12-02 23:59:59' ) a
    LEFT JOIN ( SELECT user_id FROM tb_hotel_user WHERE check_in_time <= '2019-12-02 23:59:59' GROUP BY user_id HAVING count( * ) > 1 ) b ON a.user_id = b.user_id

-- 创建表  学生表
    CREATE TABLE `edu_student` (
        `stu_id` VARCHAR (16) NOT NULL COMMENT '学号',
        `stu_name` VARCHAR (20) NOT NULL COMMENT '学生姓名',
        PRIMARY KEY (`stu_id`)
    ) COMMENT = '学生表' ENGINE = INNODB;
    
    -- 课程表 
    CREATE TABLE `edu_courses` (
        `course_no` VARCHAR (20) NOT NULL COMMENT '课程编号',
        `course_name` VARCHAR (100) NOT NULL COMMENT '课程名称',
        PRIMARY KEY (`course_no`)
    ) COMMENT = '课程表' ENGINE = INNODB;
    
    -- 成绩表
    CREATE TABLE `edu_score` (
        `stu_id` VARCHAR (16) NOT NULL COMMENT '学号',
        `course_no` VARCHAR (20) NOT NULL COMMENT '课程编号',
        `scores` FLOAT NULL DEFAULT NULL COMMENT '得分',
        PRIMARY KEY (`stu_id`, `course_no`)
    ) COMMENT = '成绩表' ENGINE = INNODB;
    
    INSERT INTO edu_student (stu_id, stu_name)
    VALUES
        ('1001', '盲僧'),
        ('1002', '盲僧');
    -- 课程表数据 
    INSERT INTO edu_courses (course_no, course_name)
    VALUES
        ('C001', '大学语文'),('C002', '新视野英语'),('C003', '离散数学'),
        ('C004','概率论与数理统计'),('C005', '线性代数'),('C006', '高等数学');
    -- 成绩表数据
    INSERT INTO edu_score (stu_id, course_no, scores)
    VALUES
        ('1001', 'C001', 67),   ('1002', 'C001', 67),   ('1001', 'C002', 87),   ('1002', 'C002', 87), 
        ('1001', 'C003', 83),   ('1002', 'C003', 83),   ('1001', 'C004', 88),   ('1002', 'C004', 88),
        ('1001', 'C005', 77),   ('1002', 'C005', 78);
    
    SELECT
    	distinct
    	est.stu_name,
    	sum(ese.scores)
    FROM
    	edu_student est
    JOIN edu_score ese ON est.stu_id = ese.stu_id
    GROUP BY
    	est.stu_id

9、`case when` 和行转列  
参见：[case when 和行转列][case when]

10、`group by` 分组统计

**注意：1）ROLLUP和 ORDER BY 是互相排斥的 2）LIMIT 用在 ROLLUP 后面**

# 语法：FROM tablename [WHERE where_contition][GROUP BY field1,field2,……fieldn [WITH ROLLUP]] [HAVING where_contition]
    
    SELECT
    	ifnull(a.year_, '合计') '年',
    	a.total,
    	a.order_total
    FROM
    	(
    		SELECT
    			DATE_FORMAT(create_date, '%Y') year_,
    			sum(payable_amount) AS total,
    			count(*) AS order_total
    		FROM
    			shop_order
    		GROUP BY
    			date_format(create_date, '%Y') WITH ROLLUP
    	) a;

![这里写图片描述][aHR0cDovL2ltZy5ibG9nLmNzZG4ubmV0LzIwMTgwMzE5MjAzOTIxNjE3]

**数据：**

CREATE TABLE `shop_order` (
      `id` char(36) NOT NULL COMMENT 'id',
      `create_date` datetime NOT NULL COMMENT '创建日期',
      `modify_date` datetime NOT NULL COMMENT '修改日期',
      `order_number` varchar(50) NOT NULL COMMENT '订单编号',
      `payable_amount` decimal(10,2) NOT NULL COMMENT '应付金额',
      PRIMARY KEY (`id`),
      UNIQUE KEY `shop_order_number` (`order_number`),
      KEY `create_date` (`create_date`) USING BTREE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='商城订单表';
    
    INSERT INTO `shop_order` 
    VALUES 
    ( 'fe4918e8-2cfd-4077-949b-79db2452d8da', '2017-10-25 17:21:39', '2017-07-25 17:21:39', 'NT20170725000043', '66.00' ), 
    ( 'fe565435-52dd-410e-b43d-a1529530fec8', '2016-11-15 17:06:59', '2017-03-15 17:06:59', 'NT20170315000003', '223.10' ), 
    ( 'fe77e32f-890d-460b-8957-96a4a378ddd3', '2018-03-17 09:42:54', '2017-06-17 09:42:54', 'NT20170617000008', '55.00' ), 
    ( 'fe839068-0f1d-45dd-9290-830fab3b638e', '2017-08-29 15:34:24', '2017-08-29 15:34:24', 'NT20170829000024', '114.00' ), 
    ( 'feaaa6e3-1520-417e-afc4-dee37df736a2', '2018-03-29 11:16:06', '2017-08-29 11:16:06', 'NT20170829000018', '114.00' ), 
    ( 'feb2147d-5f5c-4a80-9035-da8be2ae8f98', '2017-03-27 13:01:59', '2017-06-27 13:01:59', 'NT20170627000015', '162.00' ), 
    ( 'fec7a900-7644-452f-bfc1-bffb46d24481', '2017-07-22 11:08:06', '2017-06-22 11:08:06', 'NT20170622000025', '48.00' ), 
    ( 'fecce78d-14f7-48c3-93f8-de470030a55f', '2017-06-10 10:13:34', '2017-07-10 10:13:34', 'NT20170710000001', '180.00' ), 
    ( 'feeb950a-a624-4b40-8abe-cde13bab5dd9', '2018-02-28 14:22:36', '2017-08-28 14:22:36', 'NT20170828000009', '288.00' ), 
    ( 'fef7e6ef-d361-4d79-a06a-ca6106e2c6a9', '2018-01-14 16:33:02', '2017-08-14 16:33:02', 'NT20170814000014', '132.00' ), 
    ( 'fefcd408-ef18-4533-a13a-4bf6116709d8', '2017-11-30 17:30:20', '2017-07-31 17:30:20', 'NT20170731000028', '114.00' ), 
    ( 'feffded3-f5f6-480e-9e6a-1501c86c13b1', '2017-09-24 14:27:19', '2017-07-24 14:27:19', 'NT20170724000009', '660.00' ), 
    ( 'ff02a9e7-43e7-42b9-9068-d1d8c29f9d7d', '2017-12-03 11:13:14', '2017-08-03 11:13:14', 'NT20170803000016', '144.00' ), 
    ( 'ff06abf4-a56a-4756-87bb-b1d054acc4fb', '2017-02-26 09:51:58', '2017-06-26 09:51:58', 'NT20170626000009', '48.00' ), 
    ( 'ff16e6f7-bf7b-47a4-b55c-3f7e397e60b1', '2017-04-03 15:16:43', '2017-07-03 15:16:43', 'NT20170703000011', '31.00' ), 
    ( 'ff227299-f4b9-41d1-a53b-10b8804608a1', '2017-05-07 16:06:33', '2017-07-07 16:06:33', 'NT20170707000033', '48.00' ), 
    ( 'ff26cbc4-25d0-4252-ba5b-5669bad78f3d', '2016-12-08 00:03:35', '2017-04-08 00:03:35', 'NT20170408000001', '206.60' ), 
    ( 'ff6e5032-23aa-44dc-87fb-94c479d02bf6', '2017-08-22 11:03:31', '2017-07-22 11:03:31', 'NT20170722000008', '48.00' ), 
    ( 'ff9fdb57-2343-4c25-8711-9d66b7a3adad', '2016-10-17 14:10:42', '2017-05-17 14:10:42', 'NT20170517000009', '208.00' ), 
    ( 'ffb59cf1-ca94-47d7-91fd-2dd03c097a3c', '2017-05-12 11:38:18', '2017-06-12 11:38:18', 'NT20170612000010', '96.00' ), 
    ( 'ffccfdfe-0fcc-4e53-b3e0-5020845f85af', '2017-04-07 17:13:18', '2017-06-07 17:13:18', 'NT20170607000023', '797.70' ), 
    ( 'ffcda1bd-23b6-47d4-a8e6-3b2934780a22', '2017-01-24 09:00:27', '2017-06-24 09:00:27', 'NT20170624000003', '66.00' ), 
    ( 'ffe2a267-2edc-43c9-a975-dd7579530f37', '2017-07-13 17:25:21', '2017-07-13 17:25:21', 'NT20170713000033', '66.00' );

11、`ignore` 的使用

INSERT INTO shop_order select * from shop_order
    [SQL] INSERT INTO shop_order  select * from shop_order
    [Err] 1062 - Duplicate entry 'fe4918e8-2cfd-4077-949b-79db2452d8da' for key 'PRIMARY'
    
    # 使用 ignore 忽略错误（不忽略语法错误），返回 0，可以通过这个判断是否执行成功
    # 例如：定义联合主键，通过联合主键判断是否有重复的插入，就可以用 ignore
    INSERT IGNORE INTO shop_order select * from shop_order
    [SQL] INSERT IGNORE INTO shop_order  select * from shop_order
    受影响的行: 0

12、临时变量的使用，当且仅当在当前登录的`mysql`客户端下可以使用，重新打开一个客户端无法使用，或者使用 navicat 新建两个查询窗口，那么另一个窗口无法使用其他窗口中定义的临时变量

# 设置变量：set @变量名=值 
    # 获取变量：select @变量名
    # 结合 sql 语句使用临时变量
    select @max_sort:=a.max_sort from ( select max(sort) max_sort from sys_resource where parent_id='0') a ;
    update sys_resource set sort = @max_sort + 1 where name='商城设置' and parent_id='0' ;

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMwMDM4MTEx_size_16_color_FFFFFF_t_70]: /images/20220529/07249e46d65b42b0be3103853cb89af6.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMwMDM4MTEx_size_16_color_FFFFFF_t_70 1]: /images/20220529/41bed89f57e945a587b9e4002c313a63.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMwMDM4MTEx_size_16_color_FFFFFF_t_70 2]: /images/20220529/5815ef58fcce4ff8a17c68bb1757a3a8.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMwMDM4MTEx_size_16_color_FFFFFF_t_70 3]: /images/20220529/4c28a202854b4f24bc6b3781adef8aa3.png
[Link 1]: https://blog.csdn.net/qq_30038111/article/details/85053114
[count_count_1_count_column]: https://blog.csdn.net/qq_30038111/article/details/82386512
[20191202140155973.png]: /images/20220529/8b56c630149a4f4c857f4b879083335a.png
[case when]: http://blog.csdn.net/qq_30038111/article/details/79611167
[aHR0cDovL2ltZy5ibG9nLmNzZG4ubmV0LzIwMTgwMzE5MjAzOTIxNjE3]: /images/20220529/c21b8b51775742139116c310a90a4f82.png