一些实用的MySql方法（持续更新）

最新推荐文章于 2024-11-01 10:12:28 发布

原创最新推荐文章于 2024-11-01 10:12:28 发布 · 264 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#mysql

sql 专栏收录该内容

8 篇文章

订阅专栏

本文汇总了MySQL数据库中常见的操作技巧，包括数据修改、字段调整、索引添加及性能查看等，同时提供了SQL语句优化及事务隔离级别的深入解析。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

在mysql中我们会经常遇到一些常用但是猛一想，想不到的sql。下面我们总结一些：

实际数据操作。

建表以及一些数据

-- ----------------------------
-- Table structure for `product`
-- ----------------------------
DROP TABLE IF EXISTS `product`;
CREATE TABLE `product` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'id',
`brand_info` varchar(255) DEFAULT '' COMMENT '品牌信息',
`customer_code` varchar(40) DEFAULT '' COMMENT '客户编码',
`final_code` varchar(200) DEFAULT '' COMMENT '入库码',
`height` decimal(10,2) DEFAULT '0.00' COMMENT '高',
`length` decimal(10,2) DEFAULT '0.00' COMMENT '长',
`min_qty` int(11) DEFAULT NULL COMMENT '安全库存',
`pm_id` int(11) DEFAULT NULL COMMENT '包材ID',
`product_code` varchar(100) DEFAULT '' COMMENT '天珅库内编码',
`product_name` varchar(255) DEFAULT '' COMMENT '商品中文名',
`product_name_en` varchar(255) DEFAULT '' COMMENT '商品英文名',
`product_package_type` varchar(10) DEFAULT '' COMMENT '货物类型，信封，包裹',
`product_receive_status` varchar(2) DEFAULT '' COMMENT '0:新产品;1:已收过货产品',
`product_sku` varchar(200) DEFAULT '' COMMENT '客户编码(SKU)',
`pv_id` int(11) DEFAULT NULL COMMENT '材积ID',
`volume` decimal(10,6) DEFAULT '0.000000' COMMENT '体积（立方米）',
`weight` decimal(10,3) DEFAULT '0.000' COMMENT '重',
`width` decimal(10,2) DEFAULT '0.00' COMMENT '宽',
`warehouse` varchar(20) DEFAULT NULL COMMENT '仓库code',
`delete_flag` tinyint(1) NOT NULL DEFAULT '0' COMMENT '逻辑删除标识',
`org_code` varchar(30) DEFAULT NULL COMMENT '组织代码',
`org_type` tinyint(1) DEFAULT NULL COMMENT '组织类型 1-行政组织 2-财务组织 3-仓库组织 4-销售组织 5-物流组织',
`env` tinyint(1) NOT NULL DEFAULT '1' COMMENT '环境标识符：0测试环境 1正式环境',
`creator_id` varchar(40) NOT NULL COMMENT '创建人ID',
`create_time` datetime NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '创建时间',
`updater_id` varchar(40) DEFAULT NULL COMMENT '修改人ID',
`update_time` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='仓库货品属性表';

-- ----------------------------
-- Records of product
-- ----------------------------
INSERT INTO `product` VALUES ('1', '无', '10021', 'SD-M-10021-000047AA', '1.00', '1.00', '0', null, 'SD-M-10021-000047', 'mc01', 'yw01', 'package', '1', 'TEST-2018121903', '0', '0.000001', '1.000', '1.00', 'PVG1', '0', '00002', '3', '1', 'sdpmb', '2018-12-27 15:40:10', 'sdpmb', '2019-03-06 17:41:59');
INSERT INTO `product` VALUES ('2', '无品牌', '10021', 'SD-M54UGAJM', '1.00', '1.00', '0', null, 'SD-M54UGAJM', '名-称', '“”ms', 'package', '1', 'MEI_20181101183220_415754', '0', '0.000001', '1.000', '1.00', 'PVG1', '0', '00002', '3', '1', 'sdpmb', '2018-12-27 15:40:10', 'sdpmb', '2018-12-27 15:57:53');
INSERT INTO `product` VALUES ('3', 'wongsbedding', '200880', 'SD-M35D37SE', '1.00', '1.00', '0', null, 'SD-M35D37SE', '床单套件', 'duvet cover set', 'package', '1', 'AQ08068Q3', '0', '0.000001', '1.000', '1.00', 'PVG1', '0', '00002', '3', '1', 'sdpmb', '2018-12-27 15:40:10', 'sdpmb', '2018-12-27 15:57:53');

一些基本操作：

1：删除表里的数据（记住delete后面要加from）

delete from product where product_id=1;

2:修改字段的长度

alter table product MODIFY final_code VARCHAR(128) DEFAULT '' COMMENT '入库码';

3:添加字段

alter table product add product_pic_url varchar(255) DEFAULT '' comment '产品图片地址'

4:修改一些数据：

update product set delete_flag=0 where delete_flag=1;

5：添加索引

ALTER TABLE product ADD INDEX index_final_code(final_code);

6：批量修改一张表里的两个字段的内容替换

update product as a, product as b set a.product_code=b.final_code, a.final_code=b.product_code where a.id=b.id

比较常用的查询方法

1:查询重复的数据的条数（需要使用Having这个方法）

SELECT final_code,COUNT(*) FROM product GROUP BY final_codeHAVING COUNT(*) > 1//查看重复数据的条数

2:IF语句的判断

select if(delete_flag=1,'是','否') as '是否删除' from product
3：case when的用法

select NOW() as createTime,NULL AS min_qty,CASE warehouse
WHEN 'PVG1' THEN
'123'
ELSE
''
END AS org_code from product

4：按照月份，年份查询

按照月份查询

select DATE_FORMAT(create_time,'%Y-%m') months,count(product_id) count from product group by months;
按照年份查询

select DATE_FORMAT(create_time,'%Y') months,count(product_id) count from product group by months;

5:语法：ROW_NUMBER() OVER(PARTITION BY COLUMN ORDER BY COLUMN)

（1）、ROW_NUMBER() OVER (ORDER BY Column1 DESC)

　　根据Column1 降序排列，再为降序之后的每条数据加上序号。

（2）、ROW_NUMBER() OVER(PARTITION BY Column1 ORDER BY Column2)

　　根据column1分组，再在组内按照column2升序排列，加上序号。

查看数据库的性能

查看最大连接数

SHOW VARIABLES LIKE '%max_connections%';

修改最大连接数

SET GLOBAL max_connections = 2000;

查看wait_tiem

SHOW GLOBAL VARIABLES LIKE 'wait_timeout';

修改wait_time

SET GLOBAL wait_timeout = 604800;

设置数据库的事务级别

1.查看当前会话隔离级别

select @@tx_isolation;

2.查看系统当前隔离级别

select @@global.tx_isolation;

3.设置当前会话隔离级别

set session transaction isolatin level repeatable read;

4.设置系统当前隔离级别

set global transaction isolation level repeatable read;

5.命令行，开始事务时

set autocommit=off 或者 start transaction

关于隔离级别的理解

1.read uncommitted

可以看到未提交的数据（脏读），举个例子：别人说的话你都相信了，但是可能他只是说说，并不实际做。

2.read committed

读取提交的数据。但是，可能多次读取的数据结果不一致（不可重复读，幻读）。用读写的观点就是：读取的行数据，可以写。

3.repeatable read(MySQL默认隔离级别)

可以重复读取，但有幻读。读写观点：读取的数据行不可写，但是可以往表中新增数据。在MySQL中，其他事务新增的数据，看不到，不会产生幻读。采用多版本并发控制（MVCC）机制解决幻读问题。

4.serializable

可读，不可写。像java中的锁，写数据必须等待另一个事务结束。

在nvicat查询mysql的性能

1. 查看所有数据库容量大小

select
table_schema as '数据库',
sum(table_rows) as '记录数',
sum(truncate(data_length/1024/1024, 2)) as '数据容量(MB)',
sum(truncate(index_length/1024/1024, 2)) as '索引容量(MB)'
from information_schema.tables
group by table_schema
order by sum(data_length) desc, sum(index_length) desc;

2. 查看所有数据库各表容量大小

select
table_schema as '数据库',
table_name as '表名',
table_rows as '记录数',
truncate(data_length/1024/1024, 2) as '数据容量(MB)',
truncate(index_length/1024/1024, 2) as '索引容量(MB)'
from information_schema.tables
order by data_length desc, index_length desc;

具体可参考：

https://blog.youkuaiyun.com/qq_40238006/article/details/89486735

特别推荐：

一条SQL语句执行得很慢的原因有哪些

ySQL高性能优化规范建议