Sql使用记录几点记录

避免重复记录插入

insert  ignore into XXXXX;

账号授权

登录mysql执行下面的操作生成bash文件，source ~/lock.txt

SELECT concat('KILL ',id,';') FROM information_schema.processlist INTO OUTFILE '~/lock.txt';GRANT ALL PRIVILEGES ON tspark.* TO 'spark'@'%' IDENTIFIED BY '123456' WITH GRANT OPTION;FLUSH PRIVILEGES;ALTER TABLE XXX ADD COLUMN version_id varchar(40) DEFAULT "V1-beta";

展示权限

show grants for 'xxx';

修改列长度

alter table `xxxx` modify column ques_type varchar(40);

查询指定版本的query

select distinct query_content from XXX where version_id='V1-beta';

查询指定字段不为null

select query_content from XXX where domain is not null;

查询指定字段为null

select query_content from XXX where domain is null;

分组查询

select query_content from XXX group by query_content having count(query_content)>1;

全表复制

insert into XXX_new( query_content,feature_describe, assert_content, group_match_id ,build_id,topic_type) select query_content,feature_describe, assert_content, group_match_id ,build_id,topic_type from XXX_manual;

对于主键重复的记录：将更新小部分其他字段：

INSERT INTO table (a,b,c) VALUES (1,2,3) ON DUPLICATE KEY UPDATE c=c+1;

update操作 （注意不需要转义字符）

update XXX set ner_intents='["volume_adjust"]' where ner_intents like '%volume_control%';

查询domain字段为null的记录

select * from XXX where domain is null;

使用mysql表中的部分字段更新另一张表中的部分字段

update XXX a,XXXX2 b set a.ner_intents=b.intent where a.query_content=b.query_content and a.ques_type='statement_no';

更新字段A

update XXX a,XXXX b set a.ques_type=b.ques_type where a.query_content=b.query_content;update XXXs a,XXXX2 b set a.ques_type=b.ques_type where a.query_content=b.query_content and a.feature_sub_id like '%W%';

更新字段B

update XXX a,XXXX2 b set a.ner_intents=b.intent where a.query_content=b.query_content and a.ner_intents like '%None%';

更新ques_type字段

update XXX set ques_type='STATEMENT' where ques_type is null or ques_type='';

查询重复记录：

select query_content,group_match_id from XXXX group by query_content having count(query_content) > 1;

将没有重复记录的表复制到另一张表

insert into tab1 select * from tab2;select * from tab1 where query_content in (select query_content from tab1 group by query_content having count(query_content) > 1) and id not in (select id from tab1 group by query_content having count(query_content) > 1) and feature_sub_id not like '%M17%';

使用in语法进行where条件限制

select distinct name from projects where name in ('InternalChartFrame.java','NmonFileLoader.java');

复制表，插入相同数据（唯一值重复）

create table test1 like test2;insert into test1 select * from test2 on duplicate  key update test1.XX=test1.XX+N;

指定偏移数据指定行数写入

insert into test3 select * from test_result_data.tab_result_data limit 50 offset 100;