SQL 基础语句

根据排序顺序给sort字段赋值

SET @sort:=0;
UPDATE test, 
(
	SELECT id, (@sort:=@sort + 1) sort, score FROM test
	ORDER BY score DESC
) tmp
SET test.sort_id=tmp.sort
WHERE test.id=tmp.id

创建与已有表结构相同的新表

CREATE TABLE disability_check_1 LIKE disability_check

根据id指定顺序排序

使用FIELD关键字，根据id根据指定顺序排序

SELECT * FROM user 
WHERE FIND_IN_SET(id, 3, 10, 6)
ORDER BY FIELD(id, 3, 10, 6)

在使用MyBatis时，FIELD需使用foreach

<select id="selectIds1" resultMap="KtResultMap">
	SELECT *
	FROM user
	WHERE find_in_set(id, #{ids})
	ORDER BY
	<foreach collection="idsIntList" item="id" open=" FIELD(a.id," close=")" separator=",">
		#{id}
	</foreach>
</select>

插入查询出来的数据

INSERT INTO disability_check_1 SELECT * FROM disability_check

连表删除

删除条件是关联表的

DELETE table_a 
FROM table_a , table_b 
WHERE table_a .bid=table_b.id AND table_b.name='name'

删除重复数据

-- SELECT *
DELETE
FROM
    oa_usersum
WHERE
    id IN (
       SELECT * from (
	       SELECT
	           id
	       FROM
	           oa_usersum a
	       GROUP BY
			sumdt, type, appid, sumvalue
	       HAVING
	           count(id) > 1
        ) usersum
	)

模糊搜索-根据匹配度排序

SELECT DISTINCT a.id, a.create_time
FROM view_kt a
WHERE
a.keyword_all like CONCAT('%', 'keyword', '%') 
ORDER BY
LENGTH( keyword_all ) - LENGTH( REPLACE ( keyword_all, 'keyword', '' ) ) DESC

创建视图

拼接不同表的数据

CREATE VIEW search_all AS
	SELECT a.title, a.id, a.create_time, '抗体' type 
		FROM kt a WHERE a.delete_status=0 
UNION ALL
	SELECT b.title, b.id, b.create_time, '科技服务' type 
		FROM technology b WHERE b.delete_status=0 

IN、EXISTS、FIND_IN_SET

user表：

id	username	role_id	role
1	myName	1,2,3	superAdmin

role表：

id	name
1	superAdmin
2	admin
3	test

EXISTS关键字，后面的数据必须是子表数据
后面的查询语句，可以返回多个字段，也可返回任意值

SELECT * FROM user a WHERE EXISTS (
	SELECT * FROM role b WHERE b.name=a.role
);
-- 测试可以返回任意值，返回结果与上面SQL相同
SELECT * FROM user a WHERE EXISTS (
		select '测试' from role b where b.name=a.role
);

IN关键词，后面的查询语句，只能返回单个字段

SELECT * FROM user a WHERE a.role IN (SELECT name FROM role)

FIND_IN_SET可以查询以逗号分隔的字符串

SELECT * FROM user a WHERE FIND_IN_SET(2, role_id);

建议优先使用EXISTS和FIND_IN_SET

删除表数据

清空表，并且将自增的id恢复从零开始

TRUNCATE TABLE tableName

计算时间差

计算相差的天数：

SELECT TIMESTAMPDIFF(DAY, '2021-03-17',DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%S'))

如果想要查询其他，可参考下表：

参数	注释
MICROSECOND	微秒
SECOND	秒
MINUTE	分钟
HOUR	小时
DAY	天
WEEK	周
MONTH	月份
QUARTER	季度
YEAR	年份