mysql常用命令

1. 通过if、case语句判断值：

select if(m.status = 1, "在库" , "借出") as status from machin_tool_ledger m
select CASE m.status WHEN 1 THEN '在库' ELSE '借出' END as status from machin_tool_ledger m
select CASE m.status WHEN 1 THEN '在库' when 0 then '借出' ELSE 'more' END as status from machin_tool_ledger m

注意：

判断字段是否为空用 is Null

 

判断字段是否超过24小时：

SELECT * FROM guide_screen_sync_monitoring gsm WHERE TIMESTAMPDIFF(HOUR, create_time, NOW()) < 24;

 

2. 根据查询的结果，创建一个新表：

create table aa select * from broad_cast_info b where b.create_time between "2023-12-06 16:45:34" and "2023-12-06 16:45:34"

 

3.建表时写注释；创建一个关联表示例：

CREATE TABLE `brand_cars_ref` (
  `cars_id` varchar(32) NOT NULL comment '汽车id',
  `brand_id` varchar(32) NOT NULL comment '品牌id',
  PRIMARY KEY (`cars_id`,`brand_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='汽车与品牌关联表';

 

4. mysql 统计某一字段的数量：

　　转自：http://t.csdnimg.cn/Fo1BW

　　1）最常规的 就是 if判断：

　　　　SELECT
    　　　　sum(if(car.STATUS = '启用',1,0)) AS '启用的车数'
　　　　FROM
　　　　　‘car’

　　备注 if（表达式，true对应的值，false对应的值）
　　2) 比较通用的就是case when:
　　　　SELECT
    　　　　sum( case when car.STATUS = '启用' then 1 else 0 end ) AS '启用的车数'
　　　　FROM
　　　　　‘car’

　　备注：case when 表达式 then true对应的值 else 其他情况对应的值 end

　　3）or关键字：

　　　　SELECT
   　　　　 count( car.STATUS = '启用' OR NULL ) AS '启用的车数'
　　　　　FROM
    　　　　‘car’
　　　备注：count( car.STATUS = ‘启用’ OR NULL ) 此处这个 or null 有妙用。

　　　因为count 只会把非null的记录进行计数为1，所以要把所有 status不等于’启用’的记录的值都设置为null，这样count就会过滤掉那些不满足条件的记录（1 or null 是1 , 0 or null 是 null）　　

　　eg:

    　　SELECT
  　　  count(*) AS accessNum,
 　　   sum(
　　    IF
   　　 ( h.netStatus = '0', 1, 0 )) AS onlineNum,
  　　  sum(
   　　  IF
  　　  ( h.netStatus = '-1', 1, 0 )) AS offlineNum
　　FROM
    　　abc_table h

　　注意：有时候可能sum后的结果为null，可以使用ifnull判断：

　　eg:

　　SELECT
    　　IFNULL( sum( IF ( b.type = '1', 1, 0 )), 0 ) AS warningNum,
    　　IFNULL( sum( IF ( b.type = '2', 1, 0 )), 0 ) AS alarmNum,
    　　IFNULL( sum( IF ( b.type = '3', 1, 0 )), 0 ) AS cardsNum,
  　　  IFNULL( sum( IF ( b.type = '4', 1, 0 )), 0 ) AS NoCardsNum
　　FROM
    　　kyz_zdbj_bjxx b
　　WHERE
    　　b.stop_time BETWEEN "2024-07-18 15:39:26"
    　　AND "2024-07-19 15:39:26"

 

5. count(1) 和 count(*) 和 count(列名) 的区别：
　　count(1)：统计所有的记录（包括null）。
　　count(*)：统计所有的记录（包括null）。
　　count(字段)：统计该"字段"不为null的记录。
　　count(distinct 字段)：统计该"字段"去重且不为null的记录。

　　count(1)中的1并不是表示第一个字段，而是表示一个固定值。其实就可以想成表中有这么一个字段，这个字段就是固定值1，count(1)，就是计算一共有多少个1。count(*)，执行时会把星号翻译成字段的具体名字，效果也是一样的，不过多了一个翻译的动作，比固定值的方式效率稍微低一些。
　执行效率:

　　　他们之间根据不同情况会有些许区别，MySQL 会对count()做优化。(1)如果表中只有一列，则count( )效率最优。(2)如果表有多列，且存在主键，count (主键列名)效率最优，其次是：count (1) >count( *)。(3)如果表有多列，且不存在主键，则count(1 )效率优于count( *)
　执行过程:

　　　count(*)包括了所有的列，相当于行数，在统计结果的时候， 包括列值为NULL的行。
　　　count(1)包括了忽略所有列，用1代表代码行，在统计结果的时候， 包括列值为NULL的行。
　　　count(列名)只包括列名那一列，在统计结果的时候，会忽略列值为空（这里的空不是只空字符串或者0，而是表示null）的计数， 即某个字段值为NULL时，不统计。
　按照效率排序的话:

　　　count(字段)<count(主键id)<count(1)≈count(*)，建议尽量使用count(*)。

 

 6. mysql将查询到的结果插入到 另一个表中，

　　eg：INSERT INTO 目标表 ( NAME, age ) SELECT NAME, age FROM 来源表 LIMIT 0,2;

　　eg: 将某表的数据保存到临时表然后修改临时表中的数据，再作为新的数据新增到原来的表中，用到了UUID和时间加减：

　　-- 1.截断临时表
　　TRUNCATE TABLE energy_hour_statistics_temp;
　　-- 2.将最近的各个电表保存到临时表中
　　insert into energy_hour_statistics_temp
　　select * from energy_hour_statistics e order by e.meterdata_time desc limit 93;
　　-- 3.更新临时表中的电表数据，将能耗值加20，将时间加30分钟
　　update energy_hour_statistics_temp s set s.earliest_EC_Value = s.earliest_EC_Value + 20, s.latest_EC_Value = s.latest_EC_Value + 20, s.meterdata_time = DATE_ADD(s.meterdata_time, INTERVAL 30 MINUTE);
　　-- 4.将临时表中的数据新增到小时能耗表中（1.用到了UUID、2.用到了时间加减）
　　INSERT INTO energy_hour_statistics ( id, s_foreign_code, device_type, s_region, earliest_EC_Value, latest_EC_Value, difference_Value, savepower_Value, meterdata_time, create_time, s_station_code )
 　　SELECT
    　　UUID(),
    　　s_foreign_code,
    　　device_type,
    　　s_region,
    　　earliest_EC_Value,
    　　latest_EC_Value,
    　　difference_Value,
    　　savepower_Value,
   　　 meterdata_time,
    　　create_time,
    　　s_station_code
　　FROM
    　　energy_hour_statistics_temp

 　　eg: 对第二个例子优化，不使用临时表：

　　--     写法二：
　　-- 1. 新增数据93条
 　　insert into energy_hour_statistics
    　　SELECT
        　　UUID(),
        　　s_foreign_code,
        　　device_type,
       　　 s_region,
        　　( earliest_EC_Value + 20 ) AS earliest_EC_Value,
        　　( latest_EC_Value + 20 ) AS latest_EC_Value,
        　　difference_Value,
        　　savepower_Value,
        　　DATE_ADD(meterdata_time, INTERVAL 30 MINUTE) AS meterdata_time,
        　　create_time,
        　　s_station_code
    　　FROM
        　　energy_hour_statistics
    　　order by meterdata_time desc limit 93;
　　--  2.   删除两个月前的数据
    　　DELETE FROM energy_hour_statistics  WHERE DATE(meterdata_time) < DATE(DATE_ADD(NOW(), INTERVAL -60 DAY));

7. mysql事件例子：

　　-- MySql事件：定时新增假数据：每天20:30执行一次，8天数据
　　BEGIN
 　　DECLARE fs int;
 　　set fs =(select count(*) from kyz_train_timetable where DATE(start_date)=DATE(DATE_ADD(NOW(),INTERVAL 2 DAY)));
 　　if fs =0 THEN
 　　insert into kyz_train_timetable
　　(
　　select
　　SF_Train_Code,
　　Train_Code,
　　DATE_FORMAT(DATE(DATE_ADD(DATE(Start_Date),INTERVAL 2 DAY)), '%Y%m%d') as Start_Date,
　　Station_Order,
　　Station_Name,
　　Arrive_Time,
　　Start_Time,
　　DATE_ADD(`Depart_Time`, INTERVAL 2 DAY)  AS Depart_Time,
　　Distance,
　　Train_State,
　　Arrive_New,
　　Start_New,
　　NOW() as Create_Time from kyz_train_timetable
　　where DATE(Start_Date)=DATE(DATE_ADD(NOW(),INTERVAL 0 DAY))
　　)
　　;
　　DELETE  FROM kyz_train_timetable  WHERE DATE(Start_Date) = DATE(DATE_ADD(NOW(),INTERVAL -2 DAY));
　　update dayplanstate set State=1 where name='trs';
　　end if;
　　END

 8. 获取当前月上个月的最后一天的时间：

　　select CONCAT(LAST_DAY(CURDATE() - interval 1 MONTH), ' 23:59:59') AS meterdata_time,

 

9. 问题：

　　在使根据同一张表的子查询结果去删除或修改该表时报错：you can't specify target table  for update in from clause

　分析：

　　mysql 不能在同一语句中，先 select出同一表中的某些值，再 修改这个表，即不能依据某字段值做判断再来更新某字段的值，错误的原因就是因为MySQL不支持在子查询中引用更新目标表。

　解决：使用查询的数据作为中间表再查询。

　　eg：根据子查询的id删除某张表的数据如下（使用中间表时要记得加别名）：

　　delete from b_station_dict where s_id not in (
    　　select s_id from ( select s_id from b_station_dict where s_id in ('1','2') ) tempTab
　　)