Linux常用命令（Mysql）

-- 删除表内数据(Mysql)
use dc; #切换到待删除表所在的数据库
truncate table [表名] #删除表

-- 数据库导入SQL文件数据（Mysql）
source a.sql;

-- SQL增删改查
insert into student (id,name,sex,birth) values ('01','赵雷','男','1990');
delete from student where id='01';
update student set name='张三' where id='01';
select * from student where id='01';

-- 建库建表加外键
create database mysql;
create table student(s_id varchar(20),s_name varchar(20) not null,s_birth varchar(20) not null,primary key(s_id));
alter table score add foreign key(s_id) references student(s_id);

-- SQL加减法
update ceshi.t_cate set cate_name=cate_name + 1 where ins_id = 12;
update ceshi.t_cate set cate_name=cate_name + 2 where ins_id = 12;
update ceshi.t_cate set cate_name=cate_name + 3 where ins_id = 12;

-- substr(string,int1,int2)，字符截取函数，string为列名，int1为从第几位开始截取，int2为截取几位数
select substr(app_name,1,1) as a,substr(app_name,2,1) as b,substr(app_name,3,1) as c from t_app; #用于脱敏字段

-- 子查询优化
select
cate.dev_id,
cate.sync_uuid,
cate.is_delete,
cate.gmt_create,
cate.gmt_modified,
cate.dgrp_name,
fjnx_db_connection.dev_name
from
(select dev_id,sync_uuid,is_delete,gmt_create,gmt_modified,dgrp_name from fjnx_db_connection where
gmt_create regexp '^2024-06-04') as cate
inner join (select dev_id,dev_name,gmt_create from fjnx_db_connection where gmt_create regexp '^2024-06-04')
fjnx_db_connection on cate.dev_id=fjnx_db_connection.dev_id
and cate.gmt_create=fjnx_db_connection.gmt_create; #as其实可以不写

-- Mysql工具（Navicat）查看会话
show processlist;

-- Mysql杀会话
kill [会话id] && kill connection [会话id]
kill -9 [会话id] #强制杀会话

-- 前端页面查看会话
select connection_id;

-- 表被锁住时，可以看到in_use状态为1
show open tables;

-- 查看锁住的表、锁住的数据
select * from information_schema.INNODB_LOCKS;
select * from information_schema.INNODB_LOCKS_WAITS;

-- 查看锁的类型，因为什么查询语句锁住了
select * from information_schema.INNODB_trX;
show status like '%lock%';

-- 查看Mysql默认互动会话超时时间
show variables like "%timeout%";