sql语句备忘
Mysql
左关联：
SELECT
a.after_sales_id AS afterSalesId,
a.after_sales_status AS afterSalesStatus,
c.children_id AS childrenId,
c.children_name AS childrenName,
c.avatar_url AS avatarUrl
FROM
t_bg_after_sales_service a
LEFT JOIN t_bg_children c ON c.children_id = a.children_id
WHERE
- user_id = 1
关键词：LEFT JOIN
指定字段值排序并且分组
SELECT
newTable.bindId,
newTable.childrenId,
newTable.childrenName,
newTable.sex,
newTable.avatarUrl
FROM
(
SELECT
a.device_contact_id AS bindId,
a.device_id AS deviceId,
c.children_id AS childrenId,
c.children_name AS childrenName,
c.sex,
c.avatar_url AS avatarUrl
FROM
`t_bg_children` c
LEFT JOIN `t_bg_device_contact` a ON a.children_id = c.children_id
WHERE
a.user_id = 1887403632150528
AND a.is_manage = 1
ORDER BY
FIELD(
a.device_id,
1788223859148818
) DESC
) AS newTable
GROUP BY
newTable.childrenId,
newTable.childrenName
ORDER BY FIELD() 关键字优先按指定字段值排序，值为0也可以查询
查询出来的值不加desc默认排在最后
查询后插入
INSERT INTO t_bg_device_relation (
`device_friends_relation_id`,
`device_id_from`,
`device_id_to`,
`is_pass`,
`source_type`,
`who_start`,
`status`,
`relation`
) SELECT
#{deviceFriendsRelationId},
#{deviceIdFrom},
#{deviceIdTo},
#{isPass},
#{sourceType},
#{whoStart},
#{status},
#{relation}
FROM
DUAL
WHERE
NOT EXISTS (
SELECT
<include refid="Base_Column_List"/>
FROM
t_bg_device_relation
WHERE
device_id_from = #{deviceIdFrom}
AND device_id_to = #{deviceIdTo}
)
在插入前insert时进行查询，首先建一张虚拟表关键字dual，这个虚拟表只存在于一条数据，当这条数据满足条件时，进行插入语句。接着，通过关键词NOT EXISTS相当于返回一个布尔判断，类似与not in查询出不存在子查询中的语句。
Mysql 避免重复插入记录方法
方案一：查询后插入
方案二：使用ignore关键字
如果是用主键primary或者唯一索引unique区分了记录的唯一性,避免重复插入记录可以使用：
| 代码如下 | 复制代码 |
| 1 INSERT IGNORE INTO `table_name` (`email`, `phone`, `user_id`) VALUES ('test9@163.com', '99999', '9999'); | |
这样当有重复记录就会忽略,执行后返回数字0
方案三：使用Replace
| 代码如下 | 复制代码 |
| REPLACE INTO `table_name`(`col_name`, ...) VALUES (...); REPLACE INTO `table_name` (`col_name`, ...) SELECT ...; REPLACE INTO `table_name` SET `col_name`='value', |
|
REPLACE的运行与INSERT很相像,但是如果旧记录与新记录有相同的值，则在新记录被插入之前，旧记录被删除，即：
尝试把新行插入到表中
当因为对于主键或唯一关键字出现重复关键字错误而造成插入失败时：
从表中删除含有重复关键字值的冲突行
再次尝试把新行插入到表中
旧记录与新记录有相同的值的判断标准就是：
表有一个PRIMARY KEY或UNIQUE索引，否则，使用一个REPLACE语句没有意义。该语句会与INSERT相同，因为没有索引被用于确定是否新行复制了其它的行。
返回值：
REPLACE语句会返回一个数，来指示受影响的行的数目。该数是被删除和被插入的行数的和
受影响的行数可以容易地确定是否REPLACE只添加了一行，或者是否REPLACE也替换了其它行：检查该数是否为1（添加）或更大（替换）。
方案四：ON DUPLICATE KEY UPDATE
你可以在INSERT INTO…..后面加上 ON DUPLICATE KEY UPDATE方法来实现。如果您指定了ON DUPLICATE KEY UPDATE，并且插入行后会导致在一个UNIQUE索引或PRIMARY KEY中出现重复值，则执行旧行UPDATE。
| 代码如下 | 复制代码 |
| INSERT INTO `table` (`a`, `b`, `c`) VALUES (1, 2, 3) ON DUPLICATE KEY UPDATE `c`=`c`+1; UPDATE `table` SET `c`=`c`+1 WHERE `a`=1; |
|
如果行作为新记录被插入，则受影响行的值为1；如果原有的记录被更新，则受影响行的值为2。
