【MySQL】 MySQL常用查询集锦

置顶浑浑噩噩的打工人

已于 2024-09-18 11:06:43 修改

阅读量496

点赞数 1

分类专栏： MySQL 文章标签： mysql

于 2019-04-24 17:35:40 首次发布

本文链接：https://blog.youkuaiyun.com/xiaobanv1/article/details/89499179

版权

MySQL 专栏收录该内容

6 篇文章

订阅专栏

本文汇总了MySQL数据库的常用查询操作，包括复杂条件筛选、联接查询、聚合函数应用以及优化技巧，帮助读者提升在实际工作中对MySQL查询的掌握和使用效率。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

-- 查询语句结果显示行号
select @r:=@r+1 as rowno , a.* from  my_tb a ,(select @r:=0) b

-- 遍历节点下的所有子节点

select dept_no,ischild from (
              select t1.dept_no,
              if(find_in_set(parent_dept_no, @pids) > 0, @pids := concat(@pids, ',', dept_no), 0) as ischild
              from (
                   select dept_no,parent_dept_no from t_dept t  order by parent_dept_no, dept_no
                  ) t1,
                  (select @pids := '00110000') t2
             ) t3 where ischild != 0;

-- SQL中加入条件查询的方式
SELECT  SUM(IFNULL(t1.data_value,0)) value,
    CASE t1.peak_valley_flag WHEN 1 THEN '峰' WHEN 2 THEN '平' WHEN 3 THEN '谷' WHEN 4 THEN '尖'END AS name
    FROM t_hourly_statistic t1 
    left join t_datapoint td on t1.point_id = td.point_id
    WHERE td.point_status != 3 and td.group_id ='1000011'
    and t1.data_time >= STR_TO_DATE(IF(LENGTH(:startTime)>0,:startTime,t1.data_time),'%Y-%m-%d %H:%i:%s')
    and t1.data_time <= STR_TO_DATE(IF(LENGTH(:endTime)>0,:endTime,t1.data_time),'%Y-%m-%d %H:%i:%s')
		group by t1.peak_valley_flag;


-- 查询当前月份之前的每个月份
-- gb_property_type表的数据要大于12条
SELECT  DATE_FORMAT(DATE(DATE_SUB(CURRENT_DATE, INTERVAL @s :=@s - 1 MONTH)),'%m') AS 
`dateTime`,0 sumGeneration,0 maxSpeed,0 avgSpeed FROM gb_property_type,(SELECT @s := 
DATE_FORMAT(NOW(),'%m')) temp WHERE @s > 0



select a.dateTime dateTime,SUM(a.sumGeneration) sumGeneration,SUM(a.maxSpeed) maxSpeed,SUM(a.avgSpeed) avgSpeed from 
((select DATE_FORMAT(d.date_time,'%m') dateTime, round(sum(d.power_generation),2) sumGeneration,
			 MAX(d.wind_speed) maxSpeed, Round(Avg(d.wind_speed),2) avgSpeed from gb_power_generation d
where DATE_FORMAT(d.date_time,'%Y') = DATE_FORMAT(NOW(),'%Y')
group by DATE_FORMAT(d.date_time,'%m'))
union (
 SELECT  DATE_FORMAT(DATE(DATE_SUB(CURRENT_DATE, INTERVAL @s :=@s - 1 MONTH)),'%m') AS `dateTime`,0 sumGeneration,0 maxSpeed,0 avgSpeed  
	FROM gb_lose_power,(SELECT @s := DATE_FORMAT(NOW(),'%m')) temp WHERE @s > 0
	)) a GROUP BY a.dateTime order by a.dateTime;

--  查询数据库表注释信息
SELECT DISTINCT COLUMN_NAME,COLUMN_COMMENT AS `备注`
FROM information_schema.COLUMNS
WHERE TABLE_NAME = '表名';

-- mybatis处理in的操作

select d.device_id,d.device_name,
    (select d.dict_item_name from t_dict d where d.dict_type='device_type' and d.dict_item_id= d.device_type) device_type,
    if(d.device_status='1','正常','故障') device_status,p.dept_name
    from t_device d,t_dept p
    <where>
      d.dept_id = p.dept_id
      <if test="deviceType!=null &amp;&amp; deviceType.length()&gt;0">
        and d.device_type = #{deviceType}
      </if>
      <if test="collectionType!=null &amp;&amp; collectionType.length()&gt;0">
        and d.collection_type = #{collectionType}
      </if>
      <if test="nodeList!=null &amp;&amp; nodeList.size()&gt;0">
        and p.dept_no in
        <foreach close=")" collection="nodeList" index="index" item="item" open="(" separator=",">
          #{item}
        </foreach>
      </if>
    </where>
    ORDER BY p.dept_no limit #{pageNo},#{pageSize}