MySQL项目实战的命令整合

MySQL项目实战的命令整合

1、MySQL删除数据库所有表

​
SELECT concat('DROP TABLE IF EXISTS ', table_name, ‘;’)
FROM information_schema.tables
WHERE table_schema = ‘sss’;
-- 此处sss调整为数据库名称，根据运行结果全部复制执行即可。

2、MySQL取一个表中的一个随机数

​
select id from table_name order by rand() limit 1;

3、MySQL查看死锁并解锁

​-- 1、查看当前被锁的表，确认是否有锁表
show OPEN TABLES where In_use > 0;  

-- 2、查看数据库当前进程，确认有无正在执行的慢SQL记录线程。不知道这个有啥用？？
show processlist;

-- 3、查看当前运行的事务         
select
  trx_state 			-- 事务状态,
  ,trx_started 			-- 事务开始时间,
  ,trx_mysql_thread_id 	-- 事务线程ID,
  ,trx_query 			-- 具体SQL语句
from
  information_schema.innodb_trx;

-- 4、杀死死锁线程ID
kill 24759

4、MySQL为已存在的表增加一个自增字段，并自动批量自增赋值

/*分两步操作*/
/*首先增加自增字段，但此时值为空*/
alter table t_abc add column id int auto_increment primary key;

/*然后自动为已存在的数据的自增字段赋初值，从1开始*/
alter table t_abc auto_increment=1;

5.1、SQL server通过SQL语句获取表列名等信息

SELECT a.name        -- 列名,
       ,b.name        -- 类型,
       ,a.max_length  -- 长度,
       ,a.precision   -- 数值精度,
       ,a.scale       -- 数值小数,
       ,a.is_nullable -- 是否为空
FROM   sys.columns a
       LEFT JOIN sys.types b
              ON a.user_type_id = b.user_type_id
WHERE  a.object_id = Object_id('table_name');

-- 行转列获取所有列
select STUFF((select ',' + '"'+ name + '"' 
from syscolumns 
where id = object_id('table_name') 
order by colid FOR XML PATH('') ),1,1,'');

5.2、MySQL通过SQL语句获取表列名等信息

select table_name,column_comment,COLUMN_NAME,COLUMN_type,is_nullable 
from information_schema.columns 
where TABLE_NAME = 'table_name'
order by ordinal_position;

-- 行转列获取所有列
select group_concat(concat('"',COLUMN_NAME,'"'),',') 
from information_schema.columns 
where TABLE_NAME = 'table_name';

6.1、SQL server通过SQL语句过滤中文（或英文）数据

SELECT COLUMN_NAME
FROM table_name
where PATINDEX(N'%[吖-咗]%',COLUMN_NAME) = 0 
	and PATINDEX(N'%[A-Za-z]%',COLUMN_NAME) = 0
/*	
其中，函数：PATINDEX ('%pattern%', expression)
参数：
 pattern一个文字字符串。可以使用通配符，但 pattern 之前和之后必须有 % 字符（搜索第一个或最后一个字符时除外）。 pattern 是字符串数据类型类别的表达式。
 expression一个 表达式 ，通常为要在其中搜索指定模式的列， expression 为字符串数据类型类别。
返回类型：  如果 expression 的数据类型为 varchar(max) 或 nvarchar(max)，则为 bigint，否则为 int。
*/

6.2、MySQL通过SQL语句过滤中文（或英文）数据

SELECT COLUMN_NAME
FROM table_name
where  COLUMN_NAME not  REGEXP '[a-z]+'
-- https://blog.youkuaiyun.com/wzy0623/article/details/130791381
-- 正则表达式语法可参考链接

7、MySQL对数据进行脱敏（适用于手机号、银行卡、家庭住址等）

SELECT bankaccount
-- 固定长度。只保留前三+后四
,CONCAT(LEFT(bankaccount,3), '******' ,RIGHT(bankaccount,4)) bankaccount_new 
-- 非固定长度。按照原字段替换******
,INSERT ( bankaccount, 4, 6, '******' )  bankaccount_neww
FROM  t_bank_account