个人工作SQL记录

工作中使用的SQL记录

-- 经常用到的
SELECT id FROM baselink_student s
LEFT JOIN baselink_class_manage c ON s.class_id = c.id
LEFT JOIN baselink_major_setup m ON c.major_id = m.id
LEFT JOIN baselink_depart d ON m.depart_id = d.id
LEFT JOIN baselink_college ON d.college_id = c.id
WHERE s.stu_id = 202001010119

-- 1. 左查询询嵌套子查询（用于去除左表中出现重复的数据）
SELECT g.`name`,g.`num`,g.`face`,i.`url`
FROM oa_im_group g
LEFT JOIN (select mid,url,type from oa_im_menu WHERE type =0 GROUP BY mid) i ON g.id = i.mid
WHERE g.`types`='事务大厅' AND g.`valid`=1 
ORDER BY g.sort
LIMIT 16

-- 2. 查找字段中包含某个元素的所有记录
select * from `oa_infor` where FIND_IN_SET('管理员',recename);


-- 3. 查询时在原来的记录上，增加固定的字段及值
SELECT 'official' AS num, o.id AS mid,o.title,o.optname,o.optdt  FROM oa_official o
LEFT JOIN oa_flow_bill  b ON (b.mid=o.id AND `table`='official')
WHERE FIND_IN_SET(1,b.nowcheckid)
AND b.`status`= 0

-- 4. 查询到当前用户大约6个月之内的记录(计算教师工作量)
SELECT EXTRACT(MONTH FROM `exam_start_time`) 	AS `month`
FROM baselink_exam_schedule 
WHERE FIND_IN_SET(948939891,`jk_teacher_id`)
AND `exam_start_time` > SUBDATE(34354545,183)
AND teacher_id = 

-- 5. 获取某个表中的所有字段
select COLUMN_NAME from information_schema.COLUMNS where table_name = 'baselink_admin' 
and table_schema = 'gxwlzy_ems' LIMIT 10;

-- 6. 使用主表的记录为条件做子查询(排课申请红蓝点显示)
SELECT *,(SELECT `class_week` FROM baselink_course_schedule WHERE id=baselink_class_schedule_apply.course_id) AS class_week 
FROM baselink_class_schedule_apply 
WHERE  enroll_year=2020

-- 7. 根据处理记录后的内容去重(教师按月考勤记录)
SELECT DISTINCT YEAR(class_date) AS `year` FROM baselink_tch_class_record where '1'

-- 8. 内连接
SELECT DISTINCT f.id 
FROM oa_flow_bill f 
INNER JOIN  oa_reads r on r.mid= f.mid AND r.`table`=f.`table` 
WHERE r.optid=1
ORDER BY f.id 

-- 9. IFNULL的使用(注意和isnull 的区别)
SELECT `id`,`num`,`name`,`table`,`type`,`isflow`,`isscl`, 
		IFNULL((SELECT mode_clicks FROM oa_mode_clicks WHERE `mode_id`=oa_flow_set.`id` AND `user_id`='1'),0) AS mode_clicks   
FROM oa_flow_set 
WHERE `status`=1 
AND `type`<>'系统'
AND islu=1 

-- 10. 5表联合查询显示所有字段 (物流ems 学籍异动)
SELECT CONCAT('c',`id`) AS id,`college_name` AS `name`,'0' AS pid FROM baselink_college
UNION
SELECT CONCAT('d',`id`) AS id,depart_name AS `name`,CONCAT('c',`college_id`) AS pid FROM baselink_depart
UNION
SELECT CONCAT('m',`id`) AS id,major_name AS `name`,CONCAT('d',`depart_id`) AS pid FROM baselink_major_setup
UNION
SELECT CONCAT('class',`id`) AS id,class_name AS `name`,CONCAT('m',`major_id`) AS pid FROM baselink_class_manage

-- 11. 查询结果自增
Set @i:=0;
Select  (@i:=@i+1) as `id`,enroll_year AS `name` from baselink_talent_train_prog GROUP BY enroll_year

-- truncate table 表名

-- 12. 批量入库和更新（mysql语法）
INSERT INTO baselink_kq_everyday_01 (`work_time`,`teacher_id`,`teacher_name`,`ztname`,`time`,`state`,`optdt`) VALUES (
                        '2020-12-30',       
                        '948939826',       
                        '梁杰',       
                        '第一节上班',       
                        '',       
                        '未打卡',  
                        '2020-12-31 14:46:04'    
                   ),(
                        '2020-12-30',       
                        '948939826',       
                        '梁杰',       
                        '第一节下班',       
                        '',       
                        '未打卡',  
                        '2020-12-31 14:46:04'    
                   )
                    ON DUPLICATE KEY UPDATE
										-- 可以更新的字段
                    `time` = VALUES(`time`),
                    `state`= VALUES(`state`),
                    `optdt`    = VALUES(`optdt`)
-- 13. 复制表结构										
-- CREATE TABLE baselink_tch_schedule_week LIKE baselink_tch_self_schedule

-- 14. 给排课表中的课表记录加上每节课的上课时间（物流ems）
UPDATE baselink_class_schedule_apply SET `class_end_time`= CASE 
	WHEN `course_session`= 1 THEN '08:40:00'
	WHEN `course_session`= 2 THEN '09:30:00'
	WHEN `course_session`= 3 THEN '10:30:00'
	WHEN `course_session`= 4 THEN '11:20:00'
	WHEN `course_session`= 5 THEN '12:10:00'
	
	WHEN `course_session`= 6 THEN '15:10:00'
	WHEN `course_session`= 7 THEN '16:00:00'
	WHEN `course_session`= 8 THEN '16:50:00'
	WHEN `course_session`= 9 THEN '17:40:00'
	
	WHEN `course_session`= 10 THEN '20:10:00'
	WHEN `course_session`= 11 THEN '21:00:00'
	WHEN `course_session`= 12 THEN '21:50:00'
	ELSE ''
END 

-- 15. 复制一个表结构和数据到另一个表
create table baselink_kqanay_day as select * from baselink_kqanay;

-- 16. 联合查询更新表
UPDATE -baselink_score_msg  score
LEFT JOIN -baselink_class_manage  class ON class.id = score.class_id
SET score.headmaster=class.headmaster

-- 17. mysql 从右截取字符串 right(string,lenth)
SELECT floor,RIGHT(dormitory_num,2) AS dorm_num,amount,gender,residence FROM sms_dormitory_manage ORDER BY dormitory_num

-- 18. 根据从其他表获取内容更新
UPDATE baselink_class_schedule_apply AS a SET class_week_text = (SELECT class_week_text FROM baselink_course_schedule WHERE id=a.course_id)

-- 19. 多条记录合并
SELECT GROUP_CONCAT(DISTINCT `week_num`) AS `week`,classroom_name,class_id,course_name,course_id,`week`,course_session,GROUP_CONCAT(DISTINCT `class_id`)  
FROM baselink_course_sch_apply 
WHERE  --------
GROUP BY `week`,`course_session`,`week`,`course_session`

-- 20. 根据一个主表的字段，多表联合更新(博白用户id变更，课表教师id不对应)
UPDATE baselink_admin AS table_1 
LEFT JOIN baselink_course_plan AS table_2 ON table_1.`name` = table_2. teacher_name 
LEFT JOIN baselink_course_schedule AS table_3 ON table_1.`name` = table_3. teacher 
LEFT JOIN baselink_course_sch_apply AS table_4 ON table_1.`name` = table_4. teacher_name 
SET table_2.teacher_id = table_1.id,table_3.teacher_id = table_1.id,table_4.teacher_id = table_1.id 
WHERE .....

-- 21. 创建联合索引
alter table demo_oa.oa_sjoin add unique index uni_typemid(`type`, `mid`, `sid`) COMMENT '类型，记录，用户id唯一';

-- 22. 使用正则匹配 判断a集合中的数据，b集合中是否存在
select concat('1,2', ',') regexp concat(replace('1,2,3,5',',',',|'),',')