常用mysql脚本_Mysql中常用脚本收藏夹

最新推荐文章于 2022-10-17 11:15:12 发布

weixin_39807541

最新推荐文章于 2022-10-17 11:15:12 发布

阅读量83

点赞数

文章标签：常用mysql脚本

本文链接：https://blog.csdn.net/weixin_39807541/article/details/113269599

版权

1.mysql查询表大小行数：

①.查询表rows

useinformation_schema;select table_name,table_rows from tables where TABLE_SCHEMA = 'koaladb' order bytable_rowsdesc;

View Code

②.查询表所站空间

selecttable_schemaas '数据库',

table_nameas '表名',

table_rowsas '记录数',truncate(data_length/1024/1024, 2) as '数据容量(MB)',truncate(index_length/1024/1024, 2) as '索引容量(MB)'

frominformation_schema.tableswhere table_schema='mysql' --数据库

order by data_length desc, index_length desc;

View Code

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

--my_colo.new_t_patient 当前数据库需要新创建的表--koaladb.t_patient 被复制的数据库的表(结构+数据)--夸库操作：

create table my_colo.new_t_patient likekoaladb.t_patient;--insert intomy_colo.new_t_patientselect *

fromkoaladb.t_patient;--单库操作：

create table koaladb.new_t_patient likekoaladb.t_patient;--insert intokoaladb.new_t_patientselect *

from koaladb.t_patient;

View Code

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

View Code

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

# 【Mysql的bin目录】\mysql –u用户名 –p密码 –D数据库

C:\MySQL\bin\mysql –uroot –p123456-Dtest

View Code

4.CONCAT()函数

①.拼接删除数据库脚本

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

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

useinformation_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.病人基本信息 ASswhere dbo.病人基本信息 is not null

group bys.建档门诊

whith rollup--生成一个空的统计分组

View Code

6.统计平时产品上线量

SELECT s.ServiceRepresentativeName as 实施, s.ServiceStatusName as 状态,count(*) as总量fromv_receipts SWHERE s.ServiceStatusName='已上线'

GROUP BYs.ServiceRepresentativeNamewith rollup

View Code

7.Mysql中临时表的使用

--创建临时表Tmp_table并将t_patient表的查询结果存入临时表

CREATE TEMPORARY TABLE Tmp_table SELECT * FROM t_patient WHERE IsDelete=0 LIMIT 100;--查询临时表

SELECT * FROMTmp_table;--清空t_patient表

TRUNCATE TABLEt_patient;--将临时表Tmp_table的数据从新插入到t_patient

INSERT INTO t_patient SELECT * FROMTmp_table;--删除临时表

DROP TABLE Tmp_table;

View Code

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 joinbu_mobile bon a.PrivateId =b.PrivateCodeset a.Mobile = b.Mobile,a.PhoneNumber =b.PhoneNumberwhere 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)

View Code

9. CEILING()：向上取整函数

--四舍五入：

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`ASsWHEREs.`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();

(15)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 位

View Code

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 BYa.ChargerOrderId;--2.以ChargerOrderId分组，把name字段的值打印在一行，分号分隔

SELECT a.ChargerOrderId,GROUP_CONCAT(a.`Name` SEPARATOR ';') FROM product AS a GROUP BYa.ChargerOrderId;--3.去重

SELECT a.ChargerOrderId,GROUP_CONCAT(DISTINCT a.`Name`) FROM product AS a GROUP BYa.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;

View Code

12.SUBSTR() 与SUBSTRING

-- substring 函数用来截取按指定字元输出

-- MySQL: SUBSTR( ), SUBSTRING( )

-- Oracle: SUBSTR( )

-- SQL Server: SUBSTRING( )

--样例数据：

--原数据

SELECT goods_Name FROM Goods WHERE goods_Id=1;