mysql 数据库巡检脚本

1.mysql查询表大小行数：

 ①.查询表rows

use information_schema;
select table_name,table_rows from tables where TABLE_SCHEMA = 'koaladb' order by 
table_rows desc;

 ②.查询表所站空间

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='mysql' --数据库
order by data_length desc, index_length desc;

2.MySQL中数据库复制另外一个数据库表（结构+数据）操作

--my_colo.new_t_patient 当前数据库需要新创建的表
--koaladb.t_patient 被复制的数据库的表(结构+数据)
--夸库操作：
create table my_colo.new_t_patient like koaladb.t_patient;
--
insert into my_colo.new_t_patient
select * 
from koaladb.t_patient;

--单库操作：
create table koaladb.new_t_patient like koaladb.t_patient;
--
insert into koaladb.new_t_patient
select * 
from koaladb.t_patient;

3.mysql中dump数据库：结构+数据（只包含一条insert语句）

mysqldump --no-create-db --single-transaction -R --quick --default-character-set=utf8 --hex-blob -uroot -p123456 -hlocalhost --set-gtid-purged=OFF koaladb > d:\tt.sql

--mysql执行sql脚本还原数据库：先创建要还原的数据库

# 【Mysql的bin目录】\mysql –u用户名 –p密码 –D数据库<【sql脚本文件路径全名】
C:\MySQL\bin\mysql –uroot –p123456 -Dtest<C:\test.sql

4.CONCAT()函数

①.拼接删除数据库脚本

use information_schema;
select concat('drop database ', SCHEMA_NAME, ';') 
from information_schema.schemata where SCHEMA_NAME regexp '.*_201812.*' limit 1000;

②拼接数据库下所有表的select语句

use information_schema;
SELECT (concat('select * from  ',table_name,';'))table_name,table_rows from tables where TABLE_SCHEMA = 'MyDatabase' ORDER BY table_rows desc;

5.根据所有分院查询各院的患者数量：

select distinct s.建档门诊,COUNT(s.病人编号) as 病人总数量
from dbo.病人基本信息 AS s
where dbo.病人基本信息 is not null
group by s.建档门诊
whith rollup   --生成一个空的统计分组

6.统计平时产品上线量

SELECT s.ServiceRepresentativeName as 实施, s.ServiceStatusName as 状态,count(*) as 总量
from v_receipts S
WHERE s.ServiceStatusName='已上线'  
GROUP BY s.ServiceRepresentativeName 
with rollup

7.Mysql中临时表的使用

-- 创建临时表Tmp_table并将t_patient表的查询结果存入临时表
CREATE TEMPORARY TABLE Tmp_table SELECT * FROM t_patient WHERE IsDelete=0 LIMIT 100;
-- 查询临时表
SELECT * FROM Tmp_table;
-- 清空t_patient表
TRUNCATE TABLE t_patient;
-- 将临时表Tmp_table的数据从新插入到t_patient
INSERT INTO t_patient SELECT * FROM Tmp_table;
-- 删除临时表
DROP TABLE Tmp_table;

8.MySql中UPDATE与DELETE中使用子查询

--1.更新Followuple表下满足子查询的条件所有数据
UPDATE Followuple a,(SELECT Id FROM Followuple WHERE OfficeId=20 AND CreatorName='系统' AND Content IS NULL AND IsInactive=0) b set IsInactive =1 WHERE a.Id=b.Id

--2.b.Price*b.Number结果赋值给a.Amount, a.Id=c.Id  
UPDATE Order a,Order b,(SELECT Id FROM Order WHERE OrderId IN (SELECT Id FROM ProcurementOrder where OrderNo IN('621C66446555638411'))) c SET a.Amount=b.Price*b.Number WHERE b.Id=a.Id AND a.Id=c.Id;  

update patient a inner join bu_mobile b
on a.PrivateId = b.PrivateCode
set a.Mobile = b.Mobile,a.PhoneNumber =b.PhoneNumber
where a.isinactive=0 and a.Mobile="";

--3.删除Id>5的数据
DELETE FROM patinet WHERE Id IN(SELECT n.Id FROM (SELECT Id FROM patinet WHERE Id>5) AS n)

9.

-- 四舍五入：
SELECT ROUND()

-- 向上取整：
SELECT CEILING()

-- 向下取整：
SELECT FLOOR()

①.需求：将Point字段的值除以100并向上取整（即：原积分为101-199，变动后的积分为：2）

②.处理语句：

UPDATE patinet a,(select Id,(CEILING((patinet.Point)/100.0))Point FROM patinet) b SET a.Point=b.Point WHERE a.Id=b.Id

10.MySQL中以时间为单位取数据相关

①查询当天的所有数据

-- 获取当天时间段的数据
SELECT * FROM `aresglobal`.`backgroundtask` AS s WHERE to_days(s.`SubmitTime`) =to_days(now());

②查询某天每个小时的统计数据

-- 获取某天每个小时的统计数据
SELECT HOUR(s.`SubmitTime`) as Hour,count(*) as Count FROM  `aresglobal`.`backgroundtask`AS s
WHERE
    s.`SubmitTime` BETWEEN '2019-12-29 00:00:00'
AND '2019-12-29 23:00:00'
GROUP BY HOUR(s.`SubmitTime`) ORDER BY Hour(s.`SubmitTime`);

③查询昨天的数据

-- 获取昨天的数据
SELECT * FROM `aresglobal`.`backgroundtask` AS S WHERE TO_DAYS(NOW()) - TO_DAYS( S.`SubmitTime`)=1;

④查询最近2天的数据

-- 获取最近2天的数据
SELECT * FROM `aresglobal`.`backgroundtask` AS S WHERE TO_DAYS(NOW()) - TO_DAYS( S.`SubmitTime`)<=1;

⑤查询最近7天的数据

-- 获取最近7天的数据
SELECT * FROM `aresglobal`.`backgroundtask` AS S where DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(S.`SubmitTime`)

⑥查询本月的数据

-- 获取最近一个月的数据
SELECT * FROM `aresglobal`.`backgroundtask` AS S WHERE DATE_FORMAT( S.`SubmitTime`,  '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' );

⑦查询上个月数据

-- 1.获取上月数据
SELECT COUNT(*) FROM `aresglobal`.`backgroundtask` AS S WHERE PERIOD_DIFF( date_format( now( ) , '%Y%m' ) , date_format( S.`SubmitTime`,  '%Y%m' ) ) =1 ORDER BY s.`SubmitTime` ASC;

-- 2.获取上月数据
select s.`SubmitTime`  from `aresglobal`.`backgroundtask` AS s where date_format(s.`SubmitTime`, '%Y-%m')=date_format(DATE_SUB(curdate(), INTERVAL 1 MONTH),'%Y-%m');

⑧查询本季度数据

-- 获取本季度数据
select S.`SubmitTime` from `aresglobal`.`backgroundtask`AS S where QUARTER(S.`SubmitTime`)=QUARTER(now())  ORDER BY S.`SubmitTime` ASC;

⑨查询上季度数据

-- 获取上季度数据
select S.`SubmitTime`,COUNT(*) from `aresglobal`.`backgroundtask`AS S where QUARTER(S.`SubmitTime`)=QUARTER(DATE_SUB(now(), INTERVAL 1 QUARTER))  ORDER BY S.`SubmitTime` ASC;

⑩查询本年的数据

-- 查询本年所以数据
SELECT s.`SubmitTime` , COUNT(*) FROM `aresglobal`.`backgroundtask` AS s WHERE YEAR(s.`SubmitTime`)  = YEAR (NOW()) ;

(11)查询上年的数据

-- 查询上年的数据
SELECT s.`SubmitTime` , COUNT(*) FROM `aresglobal`.`backgroundtask` AS s WHERE YEAR(s.`SubmitTime`)  = YEAR (DATE_SUB( NOW(),INTERVAL 1 YEAR )) ;

(12)查询本周数据

-- 查询本周的数据(周日-周六)
SELECT S.`SubmitTime` FROM `aresglobal`.`backgroundtask` AS s WHERE YEARWEEK(DATE_FORMAT(S.`SubmitTime`,'%Y-%m-%d')) = YEARWEEK(NOW());

(13)查询上周数据

-- 查询上周数据
SELECT S.`SubmitTime` FROM `aresglobal`.`backgroundtask` AS s WHERE YEARWEEK(date_format(S.`SubmitTime`, '%Y-%m-%d')) = YEARWEEK(now())-1;

(14)查询距当前6个月的数据

-- 查询距离当前现在6个月的数据
select s.`SubmitTime` ,COUNT(*) from `aresglobal`.`backgroundtask` AS s  where s.`SubmitTime` between date_sub(now(),interval 6 month) and now();

mysql的日期输出格式

%a     缩写星期名
        %b     缩写月名
        %c     月，数值
        %D     带有英文前缀的月中的天
        %d     月的天，数值(00-31)
        %e     月的天，数值(0-31)
        %f     微秒
        %H     小时 (00-23)
        %h     小时 (01-12)
        %I     小时 (01-12)
        %i     分钟，数值(00-59)
        %j     年的天 (001-366)
        %k     小时 (0-23)
        %l     小时 (1-12)
        %M     月名
        %m     月，数值(00-12)
        %p     AM 或 PM
        %r     时间，12-小时（hh:mm:ss AM 或 PM）
        %S     秒(00-59)
        %s     秒(00-59)
        %T     时间, 24-小时 (hh:mm:ss)
        %U     周 (00-53) 星期日是一周的第一天
        %u     周 (00-53) 星期一是一周的第一天
        %V     周 (01-53) 星期日是一周的第一天，与 %X 使用
        %v     周 (01-53) 星期一是一周的第一天，与 %x 使用
        %W     星期名
        %w     周的天 （0=星期日, 6=星期六）
        %X     年，其中的星期日是周的第一天，4 位，与 %V 使用
        %x     年，其中的星期一是周的第一天，4 位，与 %v 使用
        %Y     年，4 位
        %y     年，2 位

11.GROUP_CONCAT()函数

CREATE TABLE IF NOT EXISTS `product` (
  `Id` int(20) NOT NULL,
  `ChargerOrderId` bigint(20) DEFAULT NULL,
  `CreationTime` datetime(6) DEFAULT NULL,
  `Name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

BEGIN;
INSERT INTO product(`Id`,`ChargerOrderId`,`CreationTime`,`Name`) VALUES(1,5,NOW(),'hhuia'),(2,5,NOW(),'hhuias'),(3,1,NOW(),'hhuiaa'),(4,1,NOW(),'hhuiab'),(5,2,NOW(),'hhuiac'),(6,3,NOW(),'hhuiad'),(7,4,NOW(),'hhuiae'),(8,4,NOW(),'hhuiaf'),(9,5,NOW(),'hhuiag');
COMMIT;

-- 1.以ChargerOrderId分组，把name字段的值打印在一行，逗号分隔(默认) 
SELECT a.ChargerOrderId,GROUP_CONCAT(a.`Name`) FROM product AS a GROUP BY a.ChargerOrderId;

-- 2.以ChargerOrderId分组，把name字段的值打印在一行，分号分隔 
SELECT a.ChargerOrderId,GROUP_CONCAT(a.`Name` SEPARATOR ';') FROM product AS a GROUP BY a.ChargerOrderId;

-- 3.去重
SELECT a.ChargerOrderId,GROUP_CONCAT(DISTINCT a.`Name`) FROM product AS a GROUP BY a.ChargerOrderId;

-- 4.以ChargerOrderId分组，把name字段的值打印在一行，默认逗号分割，以name排倒序
SELECT a.ChargerOrderId,GROUP_CONCAT(a.`Name` ORDER BY a.`Name` DESC) FROM product AS a GROUP BY a.ChargerOrderId;

12.SUBSTR() 与SUBSTRING

-- substring 函数用来截取按指定字元输出
-- MySQL: SUBSTR( ), SUBSTRING( )
-- Oracle: SUBSTR( )
-- SQL Server: SUBSTRING( )

--样例数据：

-- 原数据
SELECT goods_Name FROM Goods WHERE goods_Id=1;

-- SUBSTR() AND SUBSTRING()

SELECT SUBSTR(goods_Name,3) FROM Goods WHERE goods_Id=1;

SELECT SUBSTRING(goods_Name,3) FROM Goods WHERE goods_Id=1;

 

13.REVERSE()函数

-- reverse()函数：倒序输出对应str数据

SELECT REVERSE('ABCD');

--配合SUBSTRING()函数使用

SELECT REVERSE(SUBSTRING(goods_Name,3)) AS REVERSE_LOG FROM Goods WHERE goods_Id=1;

14.查看或修改MySql事务隔离级别

/* mysql事务隔离级别
1.读未提交（read-uncommitted）
2.不可重复读（read-committed）
3.可重复读（repeatable-read）
4.串行化（serializable）*/
-- 查看当前数据库的支持的事务隔离形式，默认隔离级别为REPEATABLE-READ
SELECT @@tx_isolation;
-- 修改隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- 参考文献：