[大数据]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}