sql总结

---

sql总结

一、sql基本关键字

ASC:升序排列
DESC:降序排列
UNION:
	操作符用于合并两个或多个 SELECT 语句的结果集。
	请注意，UNION 内部的每个 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每个 SELECT 语句中的列的顺序必须相同。
IFNULL(expr1,expr2):
	如果 expr1 不是 NULL，IFNULL() 返回 expr1，否则它返回 expr2。

ifnull(use_level,0):如果为空返回后面的内容

if  
	在mysql中if()函数的用法类似于java中的三目表达式，其用处也比较多，具体语法如下：
	IF(expr1,expr2,expr3)，如果expr1的值为true，则返回expr2的值，如果expr1的值为false，
	则返回expr3的值。

is null  :筛选某列的值为null
is not null :筛选某列的值不为为null

concat(str1,str2,…) : 返回结果为连接参数产生的字符串。

group_concat()函数
1、功能：将group by产生的同一个分组中的值连接起来，返回一个字符串结果。
2、语法：group_concat( [distinct] 要连接的字段 [order by 排序字段 asc/desc  ] [separator '分隔符'] )
说明：通过使用distinct可以排除重复值；如果希望对结果中的值进行排序，可以使用order by子句；separator是一个字符串值，缺省为一个逗号。

separator 将多行查询结果用特定字符串连接起来,适用于一对多
 trun
 cate table 多表; -->清空表 直接初始化

二、线上修改库时新增列

alter table t_reagent_area  ADD `xxx` varchar(32) DEFAULT NULL COMMENT 'xxx';

提示：ADD 后面可以直接从Navicat ddl中粘贴出需要添加的一行

三、Mybatis转义字符

&lt;	<	小于
&gt;	>	大于
&amp;	&	与
&apos;	'	单引号 '
&quot;	"	双引号 "

提示：

在xml文件中因为标签的问题 大于小于等字符推荐转义

三、数据库时间相关

select unix_timestamp(now());		 当前时间秒值
select unix_timestamp(now())*1000;   当前时间毫秒值
select current_timestamp();  		 当前日期到秒
select date_format(now(), '%Y-%m-%d') 当前年月日 格式 yyyy-MM-dd
select now()                         当前年月日时分秒 格式 yyyy-MM-dd HH:mm:ss
datediff(rs.expiration_time_end,now())  两时间差多少天()

四、新增数据获取主键

提示：使用mybatis plus crud接口 添加数据时 对象.getId()方法可获取主键

    <insert id="insertAndgetkey" parameterType="com.soft.mybatis.model.User">
        <!-- 获取主键-->
        <selectKey keyProperty="id" order="AFTER" resultType="java.lang.Integer">
            SELECT LAST_INSERT_ID()
        </selectKey>
        insert into t_user (username,password,create_date) values(#{username},#{password},#{createDate})
    </insert>

五、简单Case函数和Case搜索函数

case函数和代码里面 if…else…很是相似，个人感觉这就是一个判断选择函数
Case函数语法：case…when…then…else…end

--简单Case函数
CASE sex
         WHEN '1' THEN '男'
         WHEN '2' THEN '女'
ELSE '其他' END

--Case搜索函数
CASE WHEN sex = '1' THEN '男'
         WHEN sex = '2' THEN '女'
ELSE '其他' END

六、数据库字典导出模板

USE information_schema;

SELECT
    T.TABLE_SCHEMA AS '数据库名称',
    T.TABLE_NAME AS '表名',
    T.TABLE_TYPE AS '表类型',
    T. ENGINE AS '数据库引擎',
    C.ORDINAL_POSITION AS '字段编号',
    C.COLUMN_NAME AS '字段名',
    C.COLUMN_TYPE AS '数据类型',
    C.IS_NULLABLE AS '允许为空',
    C.COLUMN_KEY AS '键类型',
    C.EXTRA AS '自增属性',
    C.CHARACTER_SET_NAME AS '编码名称',
    C.COLUMN_COMMENT AS '字段说明'
FROM
    COLUMNS C
INNER JOIN TABLES T ON C.TABLE_SCHEMA = T.TABLE_SCHEMA
AND C.TABLE_NAME = T.TABLE_NAME
WHERE
    T.TABLE_SCHEMA = '数据库名'  -- 指定要生成数据字典的数据库

六、批量更新插入语句

INSERT INTO 和replace into 是有区别的

INSERT INTO是对当前数据库记录修改
replace into 如果查询索引有相同的会删除当前记录创建一条新的记录
使用INSERT INTO时主键连续
使用Ireplace into 时主键会存在跳过问题

INSERT INTO

INSERT INTO 
      tablename(field1,field2, field3, ...) 
    VALUES
      (value1, value2, value3, ...) 
    ON DUPLICATE KEY UPDATE 
      field1=value1,field2=value2, field3=value3, ...;

replace into

void updateBatchById(@Param("list") List<CpRechargeBagDto> list);

<insert id="updateBatchById">
        replace into cp_recharge_bag (id,top_ups_amount,gift_amount,actual_amount,suggest)
        <foreach item="CpRechargeBagDto" index="index" collection="list" open="(" separator="),(" close=")">
            #{CpRechargeBagDto.id},
            #{CpRechargeBagDto.topUpsAmount},
            #{CpRechargeBagDto.actualAmount},
            #{CpRechargeBagDto.suggest}
        </foreach>
    </insert>

七、foreach的使用

说明

• foreach的主要用在构建in条件中，它可以在SQL语句中进行迭代一个集合。

• foreach元素的属性主要有item，index，collection，open，separator，close。
  • 1.item表示集合中每一个元素进行迭代时的别名
  • 2.index指定一个名字，用于表示在迭代过程中，每次迭代到的位置，
  • 3.open表示该语句以什么开始，
  • 4.separator表示在每次进行迭代之间以什么符号作为分隔符，
  • 5.close表示以什么结束

三种情况

• 在使用foreach的时候最关键的也是最容易出错的就是collection属性，该属性是必须指定的，但是在不同情况下，该属性的值是不一样的

• 主要有一下3种情况
  • 1.如果传入的是单参数且参数类型是一个List的时候，collection属性值为list
  • 2.如果传入的是单参数且参数类型是一个array数组的时候，collection的属性值为array
  • 3.如果传入的参数是多个的时候，我们就需要把它们封装成一个Map了，当然单参数也可以封装成map，实际上如果你在传入参数的时候， 在MyBatis里面也是会把它封装成一个Map的，map的key就是参数名，所以这个时候collection属性值就是传入的List或array对象在自己封装的map里面的key

单参数List的类型

public List<Blog> dynamicForeachTest(List<Integer> ids);  

<select id="dynamicForeachTest" resultType="Blog">  
    select * from t_blog where id in  
    <foreach collection="list" index="index" item="item" open="(" separator="," close=")">  
        #{item}  
    </foreach>  
</select>  

List<对象>的类型

void updateBatchById(@Param("list") List<CpRechargeBagDto> list);

<insert id="updateBatchById">
        replace into cp_recharge_bag (id,top_ups_amount,gift_amount,actual_amount,suggest) VALUES
        <foreach item="CpRechargeBagDto" index="index" collection="list" open="(" separator="),(" close=")">
            #{CpRechargeBagDto.id},
            #{CpRechargeBagDto.topUpsAmount},
            #{CpRechargeBagDto.giftAmount},
            #{CpRechargeBagDto.actualAmount},
            #{CpRechargeBagDto.suggest}
        </foreach>
    </insert>

单参数array数组的类型

public List<Blog> dynamicForeach2Test(int[] ids); 

<select id="dynamicForeach2Test" resultType="Blog">  
    select * from t_blog where id in  
    <foreach collection="array" index="index" item="item" open="(" separator="," close=")">  
        #{item}  
    </foreach>  
</select>  

参数封装成Map的类型

public List<Blog> dynamicForeach3Test(Map<String, Object> params);  

<select id="dynamicForeach3Test" resultType="Blog">  
    select * from t_blog where title like "%"#{title}"%" and id in  
    <foreach collection="ids" index="index" item="item" open="(" separator="," close=")">  
        #{item}  
    </foreach>  
</select>  

八、UNION

UNION 操作符用于合并两个或多个 SELECT 语句的结果集。
请注意，UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。
同时，每条 SELECT 语句中的列的顺序必须相同。
默认地，UNION 操作符选取不同的值。如果允许重复的值，请使用 UNION ALL。

    SELECT column_name(s) FROM table_name1
    UNION
    SELECT column_name(s) FROM table_name2

九、CONCAT(str1,str2,…)

返回结果为连接参数产生的字符串。如有任何一个参数为NULL ，则返回值为 NULL。
请注意，
(1)如果所有参数均为非二进制字符串，则结果为非二进制字符串。
(2)如果自变量中含有任一二进制字符串，则结果为一个二进制字符串。

    mysql> select concat('10');

  +--------------+
   | concat('10') |
  +--------------+
  | 10 |
  +--------------+
 1 row in set (0.00 sec)

  mysql> select concat('11','22','33');
  +------------------------+
  | concat('11','22','33') |
  +------------------------+
  | 112233 |
 +------------------------+
 1 row in set (0.00 sec)

 MySQL的concat函数在连接字符串的时候，只要其中一个是NULL,那么将返回NULL

 mysql> select concat('11','22',null);
 +------------------------+
 | concat('11','22',null) |
 +------------------------+
 | NULL |
 +------------------------+
1 row in set (0.00 sec)

---