常用Mysql语句

最新推荐文章于 2025-12-04 15:25:15 发布

转载最新推荐文章于 2025-12-04 15:25:15 发布 · 59 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/aeolian/p/9877793.html

文章标签：

#数据库 #网络

数据库相关SQL

显示哪些线程正在运行

show full PROCESSLIST;

报告TCP/IP连接的主机名称（采用host_name:client_port格式），以方便地判定哪个客户端正在做什么。

如果得到“too many connections”错误信息，并且想要了解正在发生的情况，本语句是非常有用的。

如果是root帐号，你能看到所有用户的当前连接。如果是其它普通帐号，只能看到自己占用的连接。

查看Mysql数据库最大连接数

show variales like '%max%';

这里指的是整个数据库

查看整个数据库的表个数、视图个数、触发器个数

-- 从上到下分别为 表个数,视图个数,触发器个数
set @db_name = 'database_Name';
select * from 
(SELECT count(TABLE_NAME) as tableNum FROM information_schema.TABLES WHERE TABLE_SCHEMA=@db_name
union 
SELECT count(TABLE_NAME) as viewNum FROM information_schema.VIEWS WHERE TABLE_SCHEMA=@db_name
union 
SELECT count(TRIGGER_NAME) as triggerNum FROM information_schema.TRIGGERS WHERE TRIGGER_SCHEMA=@db_name) as temp

show语句

查看当前连接的所有数据库

SHOW DATABASES;   -- 查看连接的所有数据库

查看当前数据库的所有表

SHOW TABLES;   -- 查看当前数据库所有表

查看表的创建语句

show create table table_name;

查看表的字段

show full columns from table_name;    -- 含注释
{describe|desc} table_name [col_name|wild]  -- 不含注释

定位SQL

根据字段名称查找表位置

SELECT
    COLUMN_NAME,
    table_name,
    DATA_TYPE,
    COLUMN_COMMENT 
FROM
    information_schema.COLUMNS 
WHERE
    table_schema = '数据库名称' 
    AND COLUMN_NAME LIKE '%字段名称%';

根据表注释查找表位置

select * from information_schema.`TABLES` where TABLE_COMMENT like '%表注释%'

时间SQL

`UNIX_TIMESTAMP和FROM_UNIXTIME`

UNIX_TIMESTAMP为把时间(年月日或者年月日时分秒)转为时间戳,FROM_UNIXTIME相反

SELECT UNIX_TIMESTAMP("2016-07-17 23:59:59"),FROM_UNIXTIME(1468771199)

本月数据

SELECT * FROM 表名 WHERE DATE_FORMAT( 时间字段名, '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' )

上一月数据

SELECT * FROM 表名 WHERE PERIOD_DIFF( date_format( now( ) , '%Y%m' ) , date_format( 时间字段名, '%Y%m' ) ) =1

昨天数据

-- DATEDIFF函数忽略time部分，只做日期是天数差 =1 为昨天数据，>=1为昨天到以前的数据, <=为昨天到今天的数据
SELECT * FROM 表名 WHERE DATEDIFF(now(),日期时间字段名) = 1

DATEDIFF函数只做日期差，执行

SELECT DATEDIFF(now(),'2019-01-01 01:01:01');

结果如下图

24小时内数据

-- TIMEDIFF函数，计算两个datetime的hh:mm:ss差，<'24:00:00'为往前24小时内的数据
SELECT * FROM 表名 WHERE TIMEDIFF(now(),日期时间字段名) < '24:00:00';

TIMEDIFF做时间差，执行

SELECT TIMEDIFF(now(),'2019-01-01 01:01:01')

结果如下，当前时间距离2019-01-01 01:01:01已经704小时34分钟26秒

根据yyyy-mm-dd获取某天最晚最晚时间

select DATE_SUB( DATE_ADD( '2019-04-03', INTERVAL 1 DAY ), INTERVAL 1 SECOND )   -- 先加一天,在减一秒

根据日期分组

根据day分组

GROUP BY date_format( createTime, '%Y%m%d' )    --其中createTime为timestamp(格式2019-03-21 18:39:36)

sql打印指定时间段内所有日期

set @i = -1;
set @sql = repeat(" select 1 union all",-datediff('2019-04-01','2019-04-30')+1);   -- 建立30条数据
set @sql = left(@sql,length(@sql)-length(" union all"));  -- 去掉最后一个多余的union all
set @sql = concat("select date_add('2019-04-01',interval @i:=@i+1 day) as date from (",@sql,") as tmp");
prepare stmt from @sql;   -- 将@sql变量变为prepare语句
execute  stmt  -- 执行查询语句

函数

函数有字符串函数、数字函数、日期函数、高级函数等。

通常利用case、sum等函数配合group by写报表

报表中的计数

SELECT
    code,
    name,
    sum( IF ( table2.chargeType = 2, 1, 0 ) ) AS icCount,  --计数
    FORMAT( sum( IF ( table2.chargeType = 2, table2.stopTime, 0 ) ) / 3600, 2 ) AS icStopTime,  --计算总时长
    sum( IF ( table2.chargeType = 2, table2.receiveAmount, 0 ) ) / 100 AS icAmount   --计算总金额
FROM
    org_chargepost  --分组的表
    LEFT JOIN ( --一个组对多条记录的表
   select table2 ...
        ) ON code= code
GROUP BY
    code
ORDER BY code

cast转换类型

将123456789转换为decimal，12代表小数点左侧数字加右侧数字。2为小数点个数。

select cast(sum(123456789)/100 as decimal(12,2))

sum与case结合 - 实现分段统计统计一个字段某个类型值的记录条数

sum( CASE WHEN type = 1 THEN 1 [WHEN type = 2 THEN 1] ELSE 0 END )

计算当type值为1[和2]的记录的总数

sum与case结合 - 统计不同类型的总金额

sum( CASE WHEN type = 1 THEN money [WHEN type = 2 THEN money] ELSE 0 END )

计算当type值为1[和2]的总金额

统计一个字段各个类型的百分率

CONCAT(    format(
    sum( CASE WHEN type = 2 THEN 1 ELSE 0 END ) / count( id ) * 100,2),'%' ) AS Type2percent,

注意统计类型字段用的是sum()，统计总共的记录用的是count() 。

用case找出符合对应类型的记录，sum(符合case条件)/count(所有)；

format(数值，小数位)返回指定位数的小数值；

concat(‘’，‘’)连接字符。

group by后根据一个字段类型把另一字段分成多列

用到group by统计后，统计的数字金额一般要用sum、cout之类的，不然只是取group组中的第一条。

SELECT
    id,
    name,
    -- sum(if(TRIM(type='现金'),money,0)) as '现金',
    -- sum(if(TRIM(type='支付宝'),money,0)) as '支付宝',
    -- sum(if(TRIM(type='微信'),money,0)) as '微信',
  sum(case TRIM(type) when '现金' then money else 0 end) as '现金',    -- 一定要用sum,不然查出来的金额是这一天中三种金额的第一条
  sum(case TRIM(type) when '支付宝' then money else 0 end) as '支付宝',
  sum(case TRIM(type) when '微信' then money else 0 end) as '微信',
    date
FROM
    table(每一天都有支付宝微信现金三条记录)
group by date  -- 根据日期group by

truncate 清表数据

truncate table tbl_name;

将某一字段设为指定范围的随机值

update tbl_name set col = floor(1 + rand()*100)   --rand()代表0-1，*100代表0-100，floor代表小于参数的最大整数(为了取整)

根据某一个表（类别表）显示各种统计数据的报表

写法一（推荐）

这种先用类别表(a)关联已分组且过滤的记录表（b），然后在where，group by。这样虽然a的某一类别对应的b的数据没有一条，但是a的类别没有被过滤掉，即使没有数据（对应b的数据）的类别a依然可以显示。

SELECT
    a.CODE,
    b.* 
FROM
    a  -- 类别表a
    LEFT JOIN (
    SELECT  -- 先把b的数据分组好
    count( b.id ) AS count,
    sum( b.money ) AS money 
FROM
    b 
    WHERE  -- 先过滤条件，过滤掉不符合条件的数据
    b.time >= UNIX_TIMESTAMP( '2018-11-13 00:00:00' ) 
    AND b.time <= UNIX_TIMESTAMP( '2018-12-03 23:59:59' ) 
    GROUP BY  -- 然后分组（即使没有对应一些a的也没关系）
    b.cat_code 
    ) b ON a.CODE = b.cat_code -- 用符合条件的数据（b）关联a
    WHERE  -- 过滤，单纯的过滤需要显示的类别（a），即使b没有数据依然可以显示
    a.type = 0 
GROUP BY
    a.CODE

写法二（不推荐）

这种先用类别表(a)关联记录表（b），然后在where，group by。会导致只显示有数据（b）的类别（a），没有数据的类别不显示。

原因：

先关联a和b，然后在where过滤掉了所有不符合条件的b，但是同时也把a的数据一起过滤掉。

SELECT
    a.CODE,
    count( b.id ) AS count,
    sum( b.money ) AS money 
FROM
    a
    LEFT JOIN ( SELECT * FROM b ) b ON a.CODE = b.cat_code -- 先关联b的数据
WHERE
    a.type = 0 
    AND b.time >= UNIX_TIMESTAMP( '2018-11-13 00:00:00' ) -- 再过滤不符合条件的b，这里同时也会把a的数据一起过滤掉，因为已经关联成一个表了
    AND b.time <= UNIX_TIMESTAMP( '2018-12-03 23:59:59' ) 
GROUP BY
    a.CODE -- 然后再分组，最后只会显示一部分的a的类别，另一部分因为b数据不符合不显示

转载于:https://www.cnblogs.com/aeolian/p/9877793.html