日常开发中的特殊常用sql，直接复制使用，方便极了！

前言日常开发中总会用到一些使用率较低但是非常有特点的sql都记录在这了后续有用到也会继续补充到这篇文章下1、数据库某表在某个字段后增加一个新字段记得修改类型与说明alter table 表名 add 字段名 tinyint(长度) NOT NULL DEFAULT 默认值 after 字段名2、查询当前节点与所有子节点SELECT * FROM YW_XYZB CONNECT BY PRIOR ID PARENT_ID START WITH ID ***3、查询当前节点与所有父节点SELECT * FROM YW_XYZB CONNECT BY PRIOR PARENT_ID ID START WITH ID ***4、统一替换某一个字段值update t_news set author REPLACE(author,张三,张力)4.1、根据子查询的id统一更改数据UPDATE cvec_ss_task SET task_level 2 WHERE school_id xxxxxx AND parent_id IN ( SELECT id FROM ( SELECT id FROM cvec_ss_task WHERE school_id xxxxxxx AND parent_id 0 ) AS tmp )5、查询某个人有哪些角色SELECT r.* from cvec_user_role ur left join cvec_role r on r.id ur.role_id where ur.user_id0a96f4bb1f0d42a386813d94e29bc0c36、查询一个数据库实例里各个数据库的总条数select TABLE_SCHEMA,sum(table_rows) from information_schema.tables group by TABLE_SCHEMA7、统一修改某一个字段内容的sql语句将该表中该字段所有等于45的字段改为45分钟update cvec_org_report set task_suggest_timereplace(task_suggest_time,45,45分钟) where task_suggest_time45;8、查询某一个字段不同长度分组查询数量的sql其中out_id为查询的字段select length (out_id) AA , count(*) 数量 FROM cvec_school_syn_user group by AA order by AA9、查询某个机构在2024年不同身份每日登录人数统计sqlselect cau.identity_name, SUBSTR(l.create_time, 1,10) AS useTime, COUNT(*) AS useNum from cvec_login_log l left join cvec_admin_user cau on cau.id l.user_id where l.create_time like 2024% group by useTime,cau.identity_name ORDER BY useTime desc10、sql中的大于小于等于方式一 小于符号表示为 lt; 小于等于符号表示为 lt; 大于符号表示为 gt; 大于等于符号表示为 gt; 方式二 大于等于表示为 ![CDATA[ ]] 小于等于表示为 ![CDATA[ ]]11、查询数据库数据量SELECT table_schema AS Database Name, SUM(data_length index_length) / 1024 / 1024 AS Database Size (MB) FROM information_schema.tables GROUP BY table_schema;12、查询课程课程名称、教师名称、是否有班级有学生的、是否有章节、章节下是否有资源课程创建时间SELECT ztc.course_name, ztc.create_user_name as createUserName, ( SELECT COUNT(*) FROM cvec_teach_class ctc WHERE ctc.course_id ztc.id AND EXISTS ( SELECT 1 FROM cvec_class_student_catalog ccsc WHERE ccsc.course_id ctc.course_id AND ccsc.class_id ctc.id ) ) as classNum, ( SELECT COUNT(*) FROM cvec_chapter ccr WHERE ccr.course_id ztc.id ) as chapterNum, ( SELECT COUNT(*) FROM cvec_chapter_resource ccr WHERE ccr.course_id ztc.id ) as chapterResourceNum, ztc.create_time FROM cvec_teach_course ztc LEFT JOIN cvec_teach_user ztu ON ztc.create_user_id ztu.user_id WHERE ztc.is_delete 0 AND ztc.create_enterprise_id 686e20c7e4b0a1413969a087 ORDER BY ztc.create_time DESC13、同步人员数据################################ ################ ################ ################ ################ ################ # 行政班同步sql,谨慎执行执行前必需备份cvec_teach_user表数据 #1、查询原班级为空实际班级不为空的 的学生保存结果以备修改后核查 SELECT t.job_num, t.full_name , t.executive_class_id AS 原班级ID, t.executive_class_name AS 原班级名, u.class_id AS 新班级ID, c.class_name AS 新班级名 FROM cvec-ai-teaching.cvec_teach_user t JOIN cvec-ucenter.cvec_user_organization u ON t.job_num u.job_num JOIN cvec-ucenter.cvec_class c ON u.class_id c.id WHERE t.executive_class_id is null and t.teach_identity_id ! 72 LIMIT 10; #2、查询cvec_teach_user班级为空的学生和1结果对比看条数是否一致。不一致需要查看原因 select * FROM cvec-ai-teaching.cvec_teach_user t where t.executive_class_id is null and t.teach_identity_id ! 72 # 批量修改cvec_teach_user班级为空的学生改为cvec_user_organization取到的班级看修改结果条数是否与12保持一致。 UPDATE cvec-ai-teaching.cvec_teach_user t JOIN cvec-ucenter.cvec_user_organization u ON t.job_num u.job_num JOIN cvec-ucenter.cvec_class c ON u.class_id c.id SET t.executive_class_id u.class_id, t.executive_class_name c.class_name WHERE t.executive_class_id is null and t.teach_identity_id ! 72 # 最后和1、保留的结果数据对比抽查修改数据是否正确 必做 ################################ ################ ################ ################ ################ ################ ################14、查看研创中心排除平台3个老师测试创建课程列表团队成员# 查看研创中心真实排除平台3个老师测试创建创建课程列表团队成员 SELECT ROW_NUMBER() OVER () AS 序号, c.id, c.course_name AS 研创课程名称, c.course_principal AS 负责人, IFNULL(fu.faculty_name, ) AS 所属院系, IFNULL(GROUP_CONCAT(DISTINCT cm.full_name SEPARATOR ,), ) AS 虚拟教研室成员, c.create_time AS 创建时间, c.update_time AS 更新时间 FROM cvec-research-creation.course c LEFT JOIN cvec-research-creation.course_member cm ON c.id cm.course_id AND cm.is_delete 0 LEFT JOIN cvec-ucenter.cvec_user_organization uo ON c.create_user_id uo.user_id COLLATE utf8mb4_general_ci LEFT JOIN cvec-ucenter.cvec_faculty fu ON uo.faculty_id fu.id COLLATE utf8mb4_general_ci WHERE c.is_delete 0 AND c.create_user_id NOT IN (9bf146b863bc477dbdbe7938a5c56251,6b74d2708a964c32be743cf0c586213f,c7054024070e49fe9f398d11efa0286b) AND c.course_version IS NULL GROUP BY c.id ORDER BY c.create_time DESC;其他随笔记录1、查看linux中某个文件不展示注释的内容grep -Ev ^$|^# /etc/ntp.conf2、list去除某一个特定元素ListString collect list.stream().filter(f -!f.equals(1)).collect(Collectors.toList());未完待续后续会继续补充...