sql语句笔记

1. mybatis中< 小于号，> 大于号
2. 删除两个表中的数据

delete employee, employeeSkills
from employee, employeeSkills, department
where employee.employeeID = employeeSkills.employeeID
and employee.departmentID = department.departmentID
and department.name='Finance';

3. 删除两个表中的数据,用using语法

delete from employee, employeeSkills
using employee, employeeSkills, department
where employee.employeeID = employeeSkills.employeeID
and employee.departmentID = department.departmentID
and department.name='Finance';

4. 年份用year()函数，月份用month()函数，日期是星期几用DAYOFWEEK()，格式化为年月日DATE_FORMAT(now(), '%Y-%m-%d')，时分DATE_FORMAT(now(), '%T:%f')，date_sub(ds.constant_date,interval 1 day)取前一天

5. 时间戳比较

<select id="queryEndTime" parameterType="map" resultType="int">
        select count(*) from innovate_declaration_process_setting
        where declare_process_name = #{declareProcessName}
        and UNIX_TIMESTAMP(NOW()) &gt; UNIX_TIMESTAMP(end_time)
    </select>

6. 先查询再保存，批量保存多条

insert into attendance(student_no,is_attend,record_time,user_id,record_id)
          SELECT student_no,0,NOW(),record.user_id,record_id from record
               LEFT JOIN attendance_statistics on record.class_id = attendance_statistics.class_id WHERE record_id = ?

7. 当记录不存在时保存

insert if not exists
INSERT INTO clients
(client_id, client_name, client_type)
SELECT 10345, 'IBM', 'advertising'
FROM dual
WHERE not exists (select * from clients
where clients.client_id = 10345);
//使用 dual 做表名可以让你在 select 语句后面直接跟上要插入字段的值，即使这些值还不存在当前表中。

8.回滚操作

SET XACT_ABORT ON;
GO
BEGIN TRANSACTION;
INSERT INTO t2 VALUES (4);
INSERT INTO t2 VALUES (5); -- Foreign key error.
INSERT INTO t2 VALUES (6);
COMMIT TRANSACTION;
GO

9. 删除同一张表先查后删

delete from attendance_statistics WHERE class_id in (SELECT s.id FROM (SELECT attendance_statistics.class_id as id from attendance_statistics where id = ?) as s)

10. mysql可通过begin;和commit实现事务处理，中间可执行多个sql语句，其中每句sql都要有分号分割

11. max()配合group by查询每个人的最新时间的记录会出现数据不匹配的问题,所以改用子查询与原表关联

SELECT
	ds.stu_name,
	fir.return_id,
	b.*,
	fir.identify_time 
FROM
	face_bed b
	LEFT JOIN face_student ds ON ds.stu_no = b.stu_no
	LEFT JOIN (
	SELECT
		* 
	FROM
		face_identify_record y 
	WHERE
		y.identify_time = ( SELECT max( x.identify_time ) FROM face_identify_record x WHERE x.stu_no = y.stu_no ) 
	ORDER BY
	y.identify_time
	) fir ON ds.stu_no = fir.stu_no

12. mysql> set global max_allowed_packet=30*1024*1024;设置一次最多更新内容大小，默认的话是最多4m

13. 另一个表记录为空时查询该记录，3种方法，not exist这种方法可以参考not in理解，首先选取主表中的一个元组，然后一次判断子表中的每个元组是否可以输出，只要有一个元组可以输出，则最外层查询的where子句返回为false，即输出主表的该元组记录，接着选取主表中的下一个元组

	# not exist
	SELECT
	    s.stu_id,
        s.stu_name,
        s.username
        from
        train_student s
        <where>
            not exists (select ts.stu_task_id from train_submit ts where ts.stu_id = s.stu_id and ts.task_arrange_id = #{taskArrangeId})
            and s.classes_id = #{classesId}
        </where>
===================================================================
	# not in
	SELECT
		* 
	FROM
		train_student s 
	WHERE
		s.stu_id NOT IN ( SELECT ts.stu_id FROM train_submit ts ) 
		AND s.classes_id = 37
===================================================================
	# where条件实现
	SELECT
		s.stu_id,
		s.stu_name,
		s.username 
	FROM
		train_student s
		LEFT JOIN train_submit ts ON ts.stu_id = s.stu_id 
		AND ts.task_arrange_id = #{taskArrangeId}	
	WHERE
		ts.task_id IS NULL 
		AND s.classes_id = #{classesId}

14. union all只是合并查询结果，并不会进行去重和排序操作，在没有去重的前提下，使用union all的执行效率要比union高

15. explain语法解析，具体可参考：https://zhuanlan.zhihu.com/p/623125075

• 执行计划的一条记录就代表着MySQL对某个表的执行查询时的访问方法，又称访问类型，即这里的type。比如，type是ref，表名mysql将使用ref方法对改行记录的表进行查询。
• 完整的访问方法如下：system > const > eq_ref > ref > fulltext > ref_or_null >
  index_merge > unique_subquery > index_subquery > range > index >
  all，越靠前代表效率越高 ​
• SQL性能优化的目标：至少要达到range级别，要求是ref级别，最好是const级别。

16. 在某个字段不同的条件下查询另一字段是否相同
其中COUNT(DISTINCT DD_DatabaseName): 这个表达式用于计算指定列中不重复值的数量。它只会计算DD_DatabaseName列中的不同值的数量，并排除重复的值。例如，如果DD_DatabaseName列包含’A’、‘B’、‘A’、‘C’、‘B’这五个值，那么COUNT(DISTINCT DD_DatabaseName)的结果将是3，因为只有’A’、'B’和’C’是不同的值。

// 自连接的方法
SELECT dd1.*
FROM dd AS dd1
INNER JOIN dd AS dd2 ON dd1.DD_TableName = dd2.DD_TableName
WHERE dd1.DD_DatabaseName <> dd2.DD_DatabaseName
AND dd1.DD_TableName IN ('xxx','xxx');

// 子查询的方法
SELECT DD_TableName
  FROM dd
  WHERE DD_DatabaseName IN ('xxx','xxx')
  GROUP BY DD_TableName
  HAVING COUNT(DISTINCT DD_DatabaseName) > 1