1.查询表字段备注comment?sql+mapper
#sql:
SELECT COLUM_COMMENT FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '表名';
//mapper:
//为了保证返回顺序,选择用linkedHashMap类型出参
<select id = "selectComment" resultType = "java.util.LinkedHashMap">
SELECT COLUM_COMMENT FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '表名';
</select>
2.子查询sql
select * from user u where exists
(select user_id from studet where user_id = u.user_id);
3.联合查询
select a.user_id form user a join studet b on a.user_id =b.user_id;
4.删除语句
#删除数据
delete from user where user_id ='43132';
#删除表结构
drop table user;
5.复杂sql,将一个表的查询结果插入到另一张表中(多用于数据量比较大的修数操作,大多数情况都有数据量限制,所以limit分批执行)
insert into tmp c (`id`,`no`)
(select b.`id`,a`no`
from aa a join bb b on a.name =b.name
where not exists(
select * from tmp c where c.id = b.id)
limit 10000)
6.复杂sql,根据查询结果更新表子段值
#1.子查询效率多数情况是相对偏低的
update user a set a.no = (
select b.no
from tmp b
where a.id = b.id )
where a.no is null
limit 10000;
#2.联合查询
update user u ,tmp t set u.no = t.no
where u.id=t.id and u.no is null;
7.复杂sql,根据查询结果插入数据
insert into user b(`id`,`no`,`u_status`,`u_common`)
(select `id`,`no`,'1','无'
from tmp a
where not exist(
select * from user b where b.id = a.id)
limit 10000);
8.查询表结构
SELECT column_name as 字段名 , is_nullable as 允许为空,
column_default as 默认值
from INFORMATION_SCHEMA.COLUMS
where TABLE_NAME='user';