db2不能直接修改字段名，要先drop删除字段名再add新的，如下：
alter table TM_APP_FINAL_AUDIT_QUOTA drop column graduallyApprovalLevel alter table TM_APP_FINAL_AUDIT_QUOTA add column gradually_Approval_Level varchar(12);
db2 增加多个字段
alter table TM_APP_CREDIT_INFO add column IS_FIT_PBOC_MARRIAGE varchar(1) add column IS_FIT_COM_MOBILE varchar(1) add column IS_FIT_HOME_ADDRESS_MOBILE varchar(1) add column IS_FIT_EDUCATIONAL varchar(1)
感觉麻烦就没有将表建进来,需要有基础的朋友替换相应的表和字段,谢谢
–mysql分页查询 5是查询在数据存储第5条数据20是从第五条开始的20条数据,因此显示的是6至26之间的数据
select * from tm_acl_dict where 1=1 limit 5,20
–修改字段长度
alter table tm_zm_score modify column CREDIT_UP_DATE varchar(32);
–移动表数据,从s2移动到tm_zm_watch_list_detail表,前提是两表的字段一致
insert into tm_zm_watch_list_detail select * from s2 ;
–统计两张表的数据个数
select * from (select count(*) as c from tm_zm_watch_list_detail) as t0, (select count(*) from s2) as s0;
–删除表
drop table s2;
–先去重一张表再右连接一张表,最后排序输出
select * from ( select distinct id_no from tm_zm_watch_list_detail) t0 right join cust on t0.id_no = cust.id_no order by t0.id_no;
–先去重再计数
select count(distinct id_no) from cust;
//查询日期等于某天的
select * from tm_cis_req_prd_his t where t.req_date = ‘20170522’ select * from tm_zm_score t where t.zm_score=’666′ //大于芝麻分666的 select zm_score as score from tm_zm_score where zm_score>666 //求和 select sum(zm_score) as score from tm_zm_score //求平均值 select avg(zm_score) as score from tm_zm_score //求最大值 select max(zm_score 0) as score from tm_zm_score //求最小值 select min(zm_score 0)as score from tm_zm_score //求数据条数 select count(zm_score) from tm_zm_score //数字字符串排序 select * from tm_zm_score order by zm_score 0 desc //between and用法 select * from tm_zm_score where zm_score between 6 and 66 order by zm_score 0 //in 用法 select * from tm_zm_score where zm_score in (66) //查询某个时间段所有大于0的数据并按芝麻分降序排列 select * from tm_zm_score where zm_score>0 and create_date between ‘2017-04-30 00:00:00’ and ‘2017-05-24 00:00:00’ order by zm_score desc //增加一列 alter table tm_zm_score add bb varchar(11) //删除一列 alter table tm_zm_score drop aa //模糊查询 select * from tm_zm_score where zm_score like ‘%6%’ //插入数据 insert into tm_zm_score(cust_id,org) value(’01’,’01’); //删除数据 delete from tm_zm_score where cust_id=’02’ //更新数据 update tm_zm_score set cust_id=’02’ where cust_id=’01’ //左连接表查询 select * from tm_zm_watch_list left join tm_zm_watch_list_detail on tm_zm_watch_list.cust_id=tm_zm_watch_list_detail.cust_id //右连接查询 select * from tm_zm_watch_list right join tm_zm_watch_list_detail on tm_zm_watch_list.cust_id=tm_zm_watch_list_detail.cust_id //内连接 select * from tm_zm_watch_list inner join tm_zm_watch_list_detail on tm_zm_watch_list.cust_id=tm_zm_watch_list_detail.cust_id //索引创建 create index q on tm_acl_dict(id) //删除索引 drop index q on tm_acl_dict select * from tm_acl_dict where id=1000
select zm_score as score from tm_zm_score where zm_score=66 //group by 分组查询 select type_name,count(type_name) from tm_acl_dict group by type_name select count(type_name) from tm_acl_dict t where t.type_name=’省’ select count(type_name) from tm_acl_dict where type_name=’省’ //case 用法 select type_name as ‘地区’, case type when ‘state’ then code else 0 end as’code’
from tm_acl_dict
//改变主键前要先删掉原先的主键
alter table tm_td_post_loan_monitoring_data_person_info drop PRIMARY KEY
alter table tm_td_post_loan_monitoring_data_person_info add primary key(id_number)
发布者：全栈程序员栈长，转载请注明出处：https://javaforall.cn/106366.html原文链接：https://javaforall.cn
