常用sql语句

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';