# 语法

# 增删改索引

create index my_index on my_table(column1, column2);
alter table my_table drop index my_index;

1
2

# 增删改表增删改字段

# 在name字段后新增一个字段
alter table my_table add column age int not null comment '年龄' after name;
# 在表最后后新增一个字段
alter table my_table add column age int not null comment '年龄' after name;

# 修改字段名
alter table my_table change old_name new_name int not null comment '新年龄';
# 修改字段类型
alter table my_table modify name varchar(128) comment '名字';

# 删除字段
alter table my_table drop name;

1
2
3
4
5
6
7
8
9
10
11
12

# 修改表名

rename table my_table to new_table;

alter table my_table rename as new_table;
alter table my_table rename new_table;

1
2
3
4

# `<choose></choose>`

使用 if 标签时，只要 test 中的表达式为 true，就会执行 if 标签中的条件。MyBatis 提供了 choose 元素。if 标签是与(and)的关系，而 choose 是或(or)的关系。
类似于 Java 的 switch 语句，choose 为 switch，when 为 case，otherwise 则为 default。

SELECT DATE_FORMAT(t.GATHER_TIME, '%Y-%m-%d %H:%i:%s') as GATHER_TIME,
<choose>
    <when test='@com.fourfaith.base.mybatis.Ognl@strEquals(params.operation,"instantaneousFlow")'>
        JSON_EXTRACT(data, '$."instantaneousFlow"') as BAT_MOUNT
    </when>
    <when test='@com.fourfaith.base.mybatis.Ognl@strEquals(params.operation,"cumulativeFlow")'>
        JSON_EXTRACT(data, '$."cumulativeFlow"') as BAT_MOUNT
    </when>
    <otherwise>
        JSON_EXTRACT(data, '$."todayInstantaneousFlow"') as BAT_MOUNT
    </otherwise>
</choose>
FROM DEV_DATA t

1
2
3
4
5
6
7
8
9
10
11
12
13

# MySQL 中":="和"="的区别

=
- 只有在set和update时才是和:=一样，赋值的作用，其它都是等于的作用。鉴于此，用变量实现行号时，必须用:=。
:=
- 不只在 set 和 update 时时赋值的作用，在 select 也是赋值的作用。

set @num = 0;
SELECT @num := @num+1 AS rowno, NAME as nickname from dev_device;

1
2

# CASE WHEN

语法一

CASE WHEN condition THEN result
 
[WHEN...THEN...]
 
ELSE result
 
END

1
2
3
4
5
6
7

语法二

CASE expression
WHEN expression1 THEN result1
[[WHEN expression2 THEN result2] [...]]
[ELSE resultN]
END

1
2
3
4
5

WARNING

THEN 后边的值与 ELSE 后边的值类型应一致，否则会报错。如下：

CASE SCORE WHEN 'A' THEN '优' ELSE 0 END

'优'和 0 数据类型不一致则报错：

[Err] ORA-00932: 数据类型不一致: 应为 CHAR, 但却获得 NUMBER

# case when 场景

有分数score，score<60返回不及格，score>=60返回及格，score>=80返回优秀

SELECT
    STUDENT_NAME,
    (CASE WHEN score < 60 THEN '不及格'
        WHEN score >= 60 AND score < 80 THEN '及格'
        WHEN score >= 80 THEN '优秀'
        ELSE '异常' END) AS REMARK
FROM
    TABLE

1
2
3
4
5
6
7
8

现老师要统计班中，有多少男同学，多少女同学，并统计男同学中有几人及格，女同学中有几人及格，要求用一个 SQL 输出结果。

SELECT
  SUM (CASE WHEN STU_SEX = 0 THEN 1 ELSE 0 END) AS MALE_COUNT,
  SUM (CASE WHEN STU_SEX = 1 THEN 1 ELSE 0 END) AS FEMALE_COUNT,
  SUM (CASE WHEN STU_SCORE >= 60 AND STU_SEX = 0 THEN 1 ELSE 0 END) AS MALE_PASS,
  SUM (CASE WHEN STU_SCORE >= 60 AND STU_SEX = 1 THEN 1 ELSE 0 END) AS FEMALE_PASS
FROM
  THTF_STUDENTS

1
2
3
4
5
6
7

经典行转列，并配合聚合函数做统计：现要求统计各个城市，总共使用了多少水耗、电耗、热耗，使用一条SQL语句输出结果

SELECT
  E_CODE,
  SUM(CASE WHEN E_TYPE = 0 THEN E_VALUE ELSE 0 END) AS WATER_ENERGY,--水耗
  SUM(CASE WHEN E_TYPE = 1 THEN E_VALUE ELSE 0 END) AS ELE_ENERGY,--电耗
  SUM(CASE WHEN E_TYPE = 2 THEN E_VALUE ELSE 0 END) AS HEAT_ENERGY--热耗
FROM
  THTF_ENERGY_TEST
GROUP BY
  E_CODE

1
2
3
4
5
6
7
8
9

CASE WHEN中使用子查询：根据城市用电量多少，计算用电成本。假设电能耗单价分为三档，根据不同的能耗值，使用相应价格计算成本


CASE WHEN energy <= (SELECT P_LIMIT FROM TABLE_PRICE WHERE P_LEVEL = 0) THEN (SELECT P_PRICE FROM TABLE_PRICE WHERE P_LEVEL = 0)
    WHEN energy > (SELECT P_LIMIT FROM TABLE_PRICE WHERE P_LEVEL = 0) AND energy <= (SELECT P_LIMIT FROM TABLE_PRICE WHERE P_LEVEL = 1) THEN (SELECT P_PRICE FROM TABLE_PRICE WHERE P_LEVEL = 1)
    WHEN energy > (SELECT P_LIMIT FROM TABLE_PRICE WHERE P_LEVEL = 1) AND energy <= (SELECT P_LIMIT FROM TABLE_PRICE WHERE P_LEVEL = 2) THEN (SELECT P_PRICE FROM TABLE_PRICE WHERE P_LEVEL = 2)

1
2
3
4

结合max聚合函数

# group by 与 order by

单独写不存在问题，但是如果同时存在，就需要注意

select oad.ALARM_ID, count(oad.ID) as alarmNum
from odt_alarm_delay oad
group by oad.ALARM_ID order by oad.CREATE_TIME

1
2
3

以上会出错

select oad.ALARM_ID, count(oad.ID) as alarmNum
from odt_alarm_delay oad
group by oad.ALARM_ID order by oad.ALARM_ID