工作常用之Mysql命令

ChengZC2023-11-152024-06-19

Mysql命令

显示数据表字段

1	desc t_message

Field	Type	Null	Key	Default	Extra
id	bigint unsigned	NO	PRI	NULL
create_by	varchar(255)	YES		NULL
create_time	datetime	YES	MUL	NULL
del_flag	tinyint(1)	YES		NULL
update_by	varchar(255)	YES		NULL
update_time	datetime	YES		NULL
create_send	bit(1)	YES		NULL
title	varchar(255)	YES		NULL
type	varchar(255)	YES		NULL
content	longtext	YES		NULL
info_id	bigint	YES		NULL	~

显示数据表完整信息（常用语数据库字典编写，复制粘贴）

1	show full columns from t_message;

Field	Type	Collation	Null	Key	Default	Privileges	Comment
id	bigint unsigned	NULL	NO	PRI	NULL	select,insert,update,references	唯一标识
create_by	varchar(255)	utf8_general_ci	YES		NULL	select,insert,update,references	创建人
create_time	datetime	NULL	YES	MUL	NULL	select,insert,update,references	创建时间
del_flag	tinyint(1)	NULL	YES		NULL	select,insert,update,references	删除标识 0 未删除 1 删除
update_by	varchar(255)	utf8_general_ci	YES		NULL	select,insert,update,references	更新人
update_time	datetime	NULL	YES		NULL	select,insert,update,references	更新时间
create_send	bit(1)	NULL	YES		NULL	select,insert,update,references	发送人
title	varchar(255)	utf8_general_ci	YES		NULL	select,insert,update,references	标题
type	varchar(255)	utf8_general_ci	YES		NULL	select,insert,update,references	类型
content	longtext	utf8mb4_0900_ai_ci	YES		NULL	select,insert,update,references	内容
info_id	bigint	NULL	YES		NULL	select,insert,update,references	公告ID（新增字段）

修改表结构和增加索引

ALTER TABLE
  pipei_sms
ADD
  COLUMN phone varchar(50) DEFAULT NULL COMMENT '手机号',
ADD
  COLUMN channel_id int DEFAULT NULL COMMENT '通道id;0=阿里,1=百悟',
ADD
  COLUMN template_id varchar(100) DEFAULT NULL COMMENT '模板id;不同平台的id不一样',
ADD
  COLUMN template_name varchar(100) DEFAULT NULL COMMENT '模板名称;有的平台不能只传平台id',
ADD
  COLUMN sign_id varchar(100) DEFAULT NULL COMMENT '签名id',
ADD
  COLUMN sign_name varchar(100) DEFAULT NULL COMMENT '签名',
ADD
  COLUMN reback_status varchar(100) DEFAULT NULL COMMENT '返回状态',
ADD
  COLUMN check_code varchar(100) DEFAULT NULL COMMENT '被使用的验证码',
ADD
  COLUMN transaction_id varchar(100) DEFAULT NULL COMMENT '事务id;全局唯一值，与发送的事务id对应',
ADD
  COLUMN resend_status int DEFAULT NULL COMMENT '重发状态:0 自动重发 1 手动重发',
ADD
  index index_phone(`phone`),
ADD
  index index_channel_id(`channel_id`),
ADD
  index reback_status(`reback_status`);
  
-- 添加唯一约束
ALTER TABLE meminfo.yuan_user ADD unique(`wx_unionid`);
-- 添加索引
ALTER TABLE `gx_onlinechatrecord` ADD INDEX(`guestId`);
--显示指定表的索引
SHOW INDEX FROM meminfo.yuan_user;

堡垒机数据库连接

1	mysql -h host(指定数据库地址) -u账号 -p密码 -P3306 meminfo

堡垒机数据库连接，数据导出同步本地

导出数据库为dbname的表结构

1	mysqldump -h(指向的IP地址) -P(端口) -uroot -pdbpasswd -d dbname >db.sql;

导出数据库为dbname所有表结构及表数据（不加-d）

1	mysqldump -h(指向的IP地址) -P(端口) -uroot -pdbpasswd dbname >db.sql;

导出数据库为dbname某张表(test)结构及表数据（不加-d）

1	mysqldump -h(指向的IP地址) -P(端口) -uroot -pdbpasswd dbname test>db.sql;

指定条件导出，导出的是Insert语句

1	mysqldump -h host(指定数据库地址) -u账号 -p密码 --opt --where="1 order by id desc limit 100" xiaoke > xiaoke.sql

运行指定SQL方式导出到文件，可根据需求将文件导出到excel（为什么不直接导出为.csv和.xls格式？这样导出可能会变成科学计数或者精度丢失）

mysql -h host(指定数据库地址) -u账号 -p密码 -P3306 -e"SELECT CONCAT('\"', a.id, '\"') AS 主账号网站ID, a.cust_right_group AS 权限级别, a.pay_end_date AS 到期时间, c.sname AS 负责人, COUNT(d.id) AS 子账号数量, CONCAT('\"', GROUP_CONCAT(d.id ORDER BY d.id ASC SEPARATOR ', '), '\"') AS 对应子账号ID FROM meminfo.v_member_info a LEFT JOIN crm2.user_member_meta b ON a.id = b.memberId LEFT JOIN crm2.user_meta c ON b.staffId = c.sid LEFT JOIN meminfo.yuan_user d ON a.id = d.company_id WHERE a.cust_right_group > 0 AND a.pay_end_date >= '2023-11-09 00:00:00' GROUP BY a.id, a.cust_right_group, a.pay_end_date, c.sname ORDER BY a.id ASC"> /home/ops/sqlback/memberinfo_all.txt

导出数据中dbname多张表（test1,test2,test3）结构及表数据用用空格隔开

1	mysqldump -h(指向的IP地址) -P(端口) -uroot -pdbpasswd dbname test1 test2 test3>db.sql;

在服务器上执行SQL脚本

1	source /home/ops/sqlback/xxx.sql

8 sz和rz的使用

1 2	sz /home/ops/sqlback/memberinfo_all.txt #从服务器下载文件 rz 本地文件 #上传本地文件

注意，使用MobaXterm，执行命令后，使用右键选择Receive File或者Send File

统计子表数据，更新到主表

UPDATE tb_member AS m
JOIN (
  -- 子查询选择每个会员没到过期的充值记录的积分余额总和
  SELECT 
    member_id,
    COALESCE(SUM(CASE WHEN operation_type = 1 AND NOW() < end_time THEN budge_points ELSE 0 END), 0) 
      AS total_points
  FROM tb_member_points_manage
  WHERE 
    operation_type = 1 AND NOW() < end_time -- 没有过期的充值记录
  GROUP BY member_id
) AS points ON m.id = points.member_id
-- 更新会员表的积分列
SET m.points = points.total_points;

多表联合查询并显示指定字段，统计关联的子表，并显示对应子表的id

SELECT
	CAST( a.id AS CHAR ) AS 主账号网站 ID,
	a.cust_right_group AS 权限级别,
	a.pay_end_date AS 到期时间,
	c.sname AS 负责人,
	COUNT( d.id ) AS 子账号数量,
	GROUP_CONCAT( CAST( d.id AS CHAR ) ORDER BY d.id ASC ) AS 对应子账号 ID 
FROM
	meminfo.v_member_info a
	LEFT JOIN crm2.user_member_meta b ON a.id = b.memberId
	LEFT JOIN crm2.user_meta c ON b.staffId = c.sid
	LEFT JOIN meminfo.yuan_user d ON a.id = d.company_id 
WHERE
	a.cust_right_group > 0 
	AND a.pay_end_date >= '2023-11-09 00:00:00' 
GROUP BY
	a.id,
	a.cust_right_group,
	a.pay_end_date,
	c.sname 
ORDER BY
	a.id ASC 
	LIMIT 20,
	20;

主账号网站ID	权限级别	到期时间	负责人	子账号数量	对应子账号ID
12667365	1	2024-02-09 00:00:00	NULL	1	1318723690143744
12683641	2	2023-11-15 00:00:00	NULL	2	1318723710689280,1318723714297856
12687619	1	2023-11-15 00:00:00	NULL	1	1318727655432192
1525307622977536	1	2024-04-10 00:00:00	NULL	0	NULL
1619245216886784	1	2024-05-18 00:00:00	NULL	0	NULL

修改表字段类型

1
2
3

ALTER TABLE `gx_wechat_record` MODIFY COLUMN `contact_name` VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci; 
ALTER TABLE `gx_wechat_record` MODIFY COLUMN `remark` VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
ALTER TABLE `gx_wechat_record` MODIFY COLUMN `description` VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

查看建表语句

1	SHOW CREATE TABLE behavior_report

CREATE TABLE `behavior_report` (
  `id` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT 'id',
  `company_id` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '网站id',
  `user_id` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '子账号id',
  `adress` longtext CHARACTER SET utf8 COLLATE utf8_general_ci COMMENT '报告oss地址',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `create_by` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '创建人',
  `update_time` datetime DEFAULT NULL COMMENT '更新人',
  `update_by` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '更新人',
  `type` int DEFAULT NULL COMMENT '状态 0申请中 1待下载',
  `num` int DEFAULT NULL COMMENT '备用',
  `time` datetime DEFAULT NULL COMMENT '时间',
  `user_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '备用',
  `company` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `del_flag` int DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

查看数据库占用大小

库大小

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;

数据库	记录数	数据容量(MB)	索引容量(MB)
rsshub	287628	92.19	26.45
mysql	146414	8.79	0.27
lingmu	455	0.75	0.72
rsshub_blog	356	0.61	0.24
sys	6	0.01	0.00
information_schema	0	0.00	0.00
performance_schema	2819549	0.00	0.00

表大小

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
where
  table_schema = 'rsshub'
order by
  data_length desc,
  index_length desc;

数据库	表名	记录数	数据容量(MB)	索引容量(MB)
rsshub	comments_comment	12908	52.57	0.37
rsshub	components_vers_vers	58418	17.51	0.00
rsshub	posts	52192	12.51	3.03
rsshub	posts_components	53492	3.51	7.06
rsshub	posts_tags_links	53750	2.51	7.57
rsshub	posts_category_links	53828	2.51	6.06
rsshub	strapi_core_store_settings	37	0.17	0.00
rsshub	strapi_database_schema	1	0.12	0.00
rsshub	credits	550	0.07	0.03
rsshub	admin_permissions	189	0.06	0.03
rsshub	histories_posts_links	366	0.04	0.07
rsshub	credits_users_permissions_user_links	540	0.04	0.06
rsshub	histories	366	0.04	0.03
rsshub	files	5	0.01	0.12

查看数据库版本

1	SELECT VERSION();

查询指定表的information_schema

SELECT
  C.ORDINAL_POSITION AS 列 的 排 列 顺 序,
  T.TABLE_NAME AS 表 名,
  T.TABLE_COMMENT AS 表 注 释,
  C.COLUMN_NAME AS 列 名,
  C.COLUMN_COMMENT AS 列 注 释,
  C.COLUMN_TYPE AS 数 据 类 型,
  C.IS_NULLABLE AS 是 否 为 空,
  C.COLUMN_DEFAULT AS 默 认 值,
  C.EXTRA AS 代 码 表,
  C.COLUMN_KEY AS 键,
  C.COLLATION_NAME AS 约 束 条 件
FROM
  information_schema.TABLES T
  LEFT JOIN information_schema.COLUMNS C ON T.TABLE_NAME = C.TABLE_NAME
  AND T.table_schema = C.table_schema
WHERE
  T.TABLE_NAME = 'posts'
  and T.TABLE_SCHEMA = 'rsshub_blog'
ORDER BY
  C.TABLE_NAME,
  C.ORDINAL_POSITION;

列的排列顺序	表名	列名	数据类型	是否为空	默认值	代码表	键	约束条件
1	posts	id	int unsigned	NO	NULL	auto_increment	PRI	NULL
2	posts	title	varchar(255)	YES	NULL			utf8mb4_general_ci
3	posts	desrc	varchar(255)	YES	NULL			utf8mb4_general_ci
4	posts	ver	varchar(255)	YES	NULL			utf8mb4_general_ci
5	posts	cover	varchar(255)	YES	NULL			utf8mb4_general_ci
6	posts	status	tinyint(1)	YES	NULL			NULL
7	posts	images	longtext	YES	NULL			utf8mb4_general_ci
8	posts	ad_read	tinyint(1)	YES	NULL			NULL
9	posts	notice	varchar(255)	YES	NULL			utf8mb4_general_ci
10	posts	created_at	datetime(6)	YES	NULL			NULL
11	posts	updated_at	datetime(6)	YES	NULL			NULL
12	posts	published_at	datetime(6)	YES	NULL			NULL
13	posts	created_by_id	int unsigned	YES	NULL		MUL	NULL
14	posts	updated_by_id	int unsigned	YES	NULL		MUL	NULL

更新一列数据，在某一列前拼上指定字符串

1	UPDATE `rsshub_blog`.`posts` SET cover = CONCAT('https://chengzc.club/?url=', cover);

用户和权限

--显示当前用户信息和对应的权限
SHOW GRANTS;
--显示数据库所用用户和对应地址的权限
SHOW GRANTS;
SELECT DISTINCT
	CONCAT( 'User: ''', USER, '''@''', HOST, ''';' ) AS QUERY 
FROM
	mysql.USER;
--创建用户
CREATE USER '用户'@'指定IP' IDENTIFIED BY '密码';
--指定数据库权限
GRANT ALL PRIVILEGES ON `eoapi`.* TO '用户'@'指定IP';
--刷新缓存
FLUSH PRIVILEGES