1.修改数据表名 
ALTER TABLE OLD_TABLE_NAME RENAME TO NEW_TABLE_NAME; 
2.修改列名 
ALTER TABLE TABLE_NAME RENAME COLUMN OLD_COLUMN_NAME TO NEW_COLUMN_NAME; 
3.修改列的数据类型 
ALTER TABLE TABLE_NAME MODIFY COLUMN_NAME NEW_DATATYPE; 
4.插入列 
ALTER TABLE TABLE_NAME ADD COLUMN_NAME DATATYPE; 
5.删除列 
ALTER TABLE TABLE_NAME DROP COLUMN COLUMN_NAME;

登录数据库
mysql -u root -p 数据库名称查询所有数据表
show tables;截断表
truncate table 表名;
or 
truncate 表名;删除表
drop table 表名;查询表的字段信息
desc 表名称;查看emp表格中的相关信息:
show full columns from emp;查看锁表情况
show OPEN TABLES where In_use > 0;
show processlist
show full processlist
kill 224649查看id自动递增情况
SHOW VARIABLES LIKE 'auto_inc%';
设置id自动递增情 1=自动递增，2=奇数递增
set @@auto_increment_increment=1;查看超时时间和设置超时时间
show global variables like '%timeout';
SET GLOBAL connect_timeout = 3600*3查看mysql最大连接数
show variables like "max_connections";
设置mysql最大连接数
set GLOBAL max_connections=1000;---------------------------bin_log-------------------------------
show variables like '%log_bin%';查看自己的mysql是否打开了binlog选项
show variables like 'log_bin';查看binlog的格式
show variables like 'binlog_format';查看mysql binlog文件
show master status;
SHOW MASTER LOGS;查看mysql 版本
select version() from dual;
SELECT @@server_id;
show variables like 'server_id';
show variables like 'max_binlog_size';
show variables like 'binlog_format';
show variables like 'binlog_row_image';

alter table 表名称 add 字段名称 字段类型 [是否允许非空];alter table 表名 add column 字段名 double DEFAULT NULL COMMENT "备注信息";
alter table 表名 add column 字段名 varchar(50) DEFAULT NULL COMMENT '备注信息';alter table 表名 add transactor varchar(10) not Null;
alter table 表名 add id int unsigned not Null auto_increment primary key
alter table 表名 add id bigint(20) not Null auto_increment primary key COMMENT 'id';在某个字段后面添加一个字段
ALTER TABLE crawl_data.t_sentiment_news   ADD COLUMN update_tag INT(4) DEFAULT 0  NULL COMMENT '是否被修改过; 0 未被修改,1 已被修改' AFTER `tag_id`,ADD COLUMN newstag_type VARCHAR(100) NULL COMMENT '新闻标签分类(打标签时使用),用逗号分隔' AFTER `sentiment`;

alter table 表名称 change 字段名称 字段名称 字段类型 [是否允许非空];
alter table 表名称 modify 字段名称 字段类型 [是否允许非空];

alter table 表名称 change 字段原名称 字段新名称 字段类型 [是否允许非空]alter table T_USER change USER_ID user_id INT(11) not null;

ALTER TABLE 表名 DROP 字段名;
ALTER TABLE 表名 DROP COLUMN 字段名;

alter table 表名 convert to character set utf8 collate utf8_general_ci;
alter table 表名 convert to character set utf8mb4 collate utf8mb4_general_ci;

select COLUMN_NAME from information_schema.COLUMNS
where table_name = 'your_table_name';

select COLUMN_NAME from information_schema.COLUMNS
where table_name = 'your_table_name'
and table_schema = 'your_db_name' ORDER BY COLUMN_NAME DESC;

select COLUMN_NAME,DATA_TYPE,COLUMN_COMMENT from information_schema.COLUMNS 
where table_name = '表名' and table_schema = '数据库名称';

#table_schema为数据库名,table_name是表名，
SELECT table_name FROM information_schema.tables 
WHERE table_schema = "test" ORDER BY table_name DESC

# -*- coding；utf-8 -*-
import MySQLdbdmdb_dbconn = MySQLdb.connect(host='192.168.8.30', user='root', passwd='', db='test', charset='utf8')
dmdb_cursor = dmdb_dbconn.cursor()#table_schema为数据库名,table_name是表名，
#获取数据库里所有表名
sql_table='SELECT table_name FROM information_schema.tables WHERE table_schema = "test" ORDER BY table_name DESC'
#获取表里所有字段名和字段类型
sql_fields='select COLUMN_NAME,DATA_TYPE from information_schema.COLUMNS where table_name = %s%s%s and table_schema = "test"'
#更改字段大小写语句，因为类型包含关键字，取出来的类型是字符串，通过占位符匹配进去，运行会报错
sql_change='alter table %s change %s %s %s;'#(表名，原始字段名，新字段名，类型)#修改字符集编码
# 'alter table 表名 convert to character set 字符集 collate 排序规则'
# 'alter table 表名 convert to character set utf8 collate utf8_general_ci'
dmdb_cursor.execute(sql_table)
tables = dmdb_cursor.fetchall()
# print(tables)
for table in tables:sql_field=sql_fields%('"',table[0],'"')dmdb_cursor.execute(sql_field)fileds = dmdb_cursor.fetchall()print(fileds)for filed in fileds:#将字段名转换为小写--lower(),大写---upper()#方法一：把所有执行语句写到一个文件中，在工具中执行所有sql语句sql_change_full=sql_change%(table[0],filed[0],str(filed[0]).lower(),filed[1])with open('sql_change.txt','a+') as f:f.write(sql_change_full+'\n')#方法二upper_lower_change=str(filed[0]).lower()#转换为小写# upper_lower_change=str(filed[0]).upper()#转换为大写if filed[1]=='int':sql_change_type = 'alter table %s change %s %s int'dmdb_cursor.execute(sql_change_type%(table[0],filed[0],upper_lower_change))dmdb_dbconn.commit()elif filed[1]=='varchar':sql_change_type = 'alter table %s change %s %s varchar'dmdb_cursor.execute(sql_change_type%(table[0],filed[0],upper_lower_change))dmdb_dbconn.commit()elif filed[1]=='char':sql_change_type = 'alter table %s change %s %s char'dmdb_cursor.execute(sql_change_type%(table[0],filed[0],upper_lower_change))dmdb_dbconn.commit()elif filed[1]=='datetime':sql_change_type = 'alter table %s change %s %s datetime'dmdb_cursor.execute(sql_change_type%(table[0],filed[0],upper_lower_change))dmdb_dbconn.commit()elif filed[1]=='timestamp':sql_change_type = 'alter table %s change %s %s timestamp'dmdb_cursor.execute(sql_change_type%(table[0],filed[0],upper_lower_change))dmdb_dbconn.commit()elif filed[1]=='bigint':sql_change_type = 'alter table %s change %s %s bigint'dmdb_cursor.execute(sql_change_type%(table[0],filed[0],upper_lower_change))dmdb_dbconn.commit()elif filed[1]=='decimal':sql_change_type = 'alter table %s change %s %s decimal'dmdb_cursor.execute(sql_change_type%(table[0],filed[0],upper_lower_change))dmdb_dbconn.commit()elif filed[1]=='double':sql_change_type = 'alter table %s change %s %s double'dmdb_cursor.execute(sql_change_type%(table[0],filed[0],upper_lower_change))dmdb_dbconn.commit()elif filed[1]=='text':sql_change_type = 'alter table %s change %s %s text'dmdb_cursor.execute(sql_change_type%(table[0],filed[0],upper_lower_change))dmdb_dbconn.commit()elif filed[1]=='tinyint':sql_change_type = 'alter table %s change %s %s tinyint'dmdb_cursor.execute(sql_change_type%(table[0],filed[0],upper_lower_change))dmdb_dbconn.commit()else:print(filed[1],'不在所给的条件中，请添加条件')

---------------------------------start---------------------------------
sql将一个表中的数据插入到另一个表中声名：a,b ,都是表 
--b表存在（两表结构一样） 
insert into b select * from a 若两表只是有部分（字段）相同，则 
insert into b(col1,col2,col3,col4,...) select col1,col2,col3,col4,... from a where... 把表a插入到表b中去--b表不存在 
select * into b from a 
or
select (字段1，字段2，...) into b from a 例子：
INSERT INTO bond_implied_ratings_hist(bond_uni_code,`date`,bond_code,implied_ratings,implied_rate,create_time) 
SELECT bond_uni_code,`date`,bond_code,implied_ratings,implied_rate,create_time from bond_valuation 
where date<='2018-07-10';
---------------------------------end---------------------------------将两段sql语句连接起来用union all 
select * from table1
union all
select * from table2将大写字母改为小写字母
update 表名 set 字段名a= Lower(字段a)  将小写字母转化成大写字母
update 表名 set 字段名a= upper(字段名a) 统计某一字段相同的情况下另一字段的加和
select 字段A，sum（字段B） from table group by 字段A;多表连接
SELECT * FROM t_zid  a INNER JOIN t_amac_manager_detail b ON a.zerone_id = b.fund_manager_zid WHERE TYPE = 2id自增相关
REPLACE INTO test.t_generate_comp_id (stub) VALUES ('comp_id');
insert INTO test.t_generate_comp_id2 (stub) VALUES ('comp_id4');
SELECT LAST_INSERT_ID() as id;
select id from test.t_generate_comp_id
SELECT MAX(zerone_id) from test.t_zid WHERE type=2 and zerone_id like 'Comp%';替换某个字段的特殊字符
select * from t_sentiment where company_name.REPLACE(company_name,'(','（').REPLACE(company_name,')','）') in (select entity_name from t_zid where type=1)获取当天时间
select date_format(now(),'%Y-%m-%d')mysql查询某个日期最接近的数据
SELECT *, abs(UNIX_TIMESTAMP(r_date)-UNIX_TIMESTAMP(date_format(now(),'%Y-%m-%d'))) as min from base_wind.t_eastmoney_gdfx  GROUP BY min asc

模糊查询
select * from t_business_basic_info where company_name like'%斗鱼%'
select * from t_business_basic_info where company_name not like'%斗鱼%'查找以4开头的
select * from t_com_info where substr(com_uni_code,1,1)='4'查找某个字段长度小于10的
SELECT * from companynews_add WHERE LENGTH(content)<10 AND id>22209;查找某个字段在某个表中，某个字段长度等于8
select * from crawl_data.amac_person_detail where manager_id in (select amac_id from t_zid where type=2 and LENGTH(amac_id)=8)length 和char_length
SELECT * FROM xinhua_data.sm_org_basic WHERE length(website)!=char_length(website)查找某个字段不同的值
select DISTINCT invest_area from xinhua_data.sm_org_vcpe以某个字段降序 limit 800条
Select * from dmdb.t_bond_ann_info order by  create_time DESC limit 800以某个字段升序 limit 800条
Select * from dmdb.t_bond_ann_info order by  create_time ASC limit 800统计某个字段非重复值数量
Select COUNT(distinct ann_id) from dmdb.t_bond_ann_att_info where last_update_time >= '2018-03-09 14:00:00';根据条件查询出ann_id字段不同的数量
Select COUNT(distinct ann_id) from dmdb.t_bond_ann_att_info where last_update_time >= '2018-03-09 14:00:00';根据条件查询id字段的数量
Select COUNT(id) from dmdb.t_bond_ann_info where create_time >= '2018-03-09 14:00:00';查询a表中的id不在t_bond_ann_att_info这个表中的ann_id字段中
Select a.* from dmdb.t_bond_ann_info a
where a.id not in (select ann_id from  t_bond_ann_att_infowhere last_update_time >= '2018-03-09 14:00:00'   
);查询ann_id字段中，在所给的条件中
select  * from t_bond_ann_att_info where ann_id in ('13441','13442')-----------------------------------start-------------------------------------
其它
select manag_company_zid,count(fund_zid) as cnt_fund,group_concat(fund_kind) as fund_kind,group_concat(form_kind) as form_kind,sum(fund_scale) as total_scalefrom zerone_data.t_sm_fund
where  manag_company_zid is not nulland fund_zid is not null
group by manag_company_zidselect count(1) from bond_valuation where date = ( select date from t_is_holiday where is_holiday = 0 and date < CURDATE() order by date desc limit 1 )
-----------------------------------end-------------------------------------

------------------------------------start---------------------------------
以某个字段为组，数量大于1
select * from amac_person_detail WHERE manager_name in ( select manager_name from amac_person_detail group by manager_name having count(manager_name)>1)select entity_name,count(*) from (select zerone_id,entity_name,type from t_zid where type=1) b GROUP BY entity_name HAVING count(entity_name)>1select * from (
select entity_name,max(qk_id) as ma,min(qk_id) as mi from zerone_data.t_zid 
where type=3
group by entity_name
having count(*)>1
) bb
where bb.ma<>bb.mi
------------------------------end----------------------------------------查找表中多余的重复记录，重复记录是根据单个字段（peopleId）来判断
select * from people where peopleId in 
(select peopleId from people group by peopleId having count(peopleId) > 1)查找表中多余的重复记录（多个字段） 
select * from vitae a
where (a.peopleId,a.seq) in  (select peopleId,seq from vitae group by peopleId,seq  having count(*) > 1)查找表中多余的重复记录（多个字段），不包含rowid最小的记录
select * fromvitae a
where (a.peopleId,a.seq) in  (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)
and rowid not in (select min(rowid) from vitae group by peopleId,seq having count(*)>1)比方说在A表中存在一个字段“name”，而且不同记录之间的“name”值有可能会相同，现在就是需要查询出在该表中的各记录之间，“name”值存在重复的项；
Select Name,Count(*) From A Group By Name Having Count(*) > 1如果还查性别也相同大则如下:
Select Name,sex,Count(*) From A Group By Name,sex Having Count(*) > 1去重
select distinct * from tableName;select identity(int,1,1) as autoID, * into Tmp from tableName;
select min(autoID) as autoID into Tmp2 from Tmp group byName,autoID;

# LENGTH(0+nickname) = LENGTH(nickname) 就是纯数字
SELECT * FROM m_customer WHERE LENGTH(0+nickname) = LENGTH(nickname);
SELECT * FROM m_customer WHERE LENGTH(0+nickname) != LENGTH(nickname);

update t_sm_fund set form_kind='99' where manag_company_zid="Comp100000231692";
update t_sm_org_basic t,t_zid z set t.company_zid=z.zerone_id where z.type=2 and t.company_zid=z.qk_id;UPDATE test_zhou.t_zid set amac_id=Null where amac_id in (select amac_id from test_zhou.t_zid where type=2 and LENGTH(amac_id)=8);update dmdc.bond_issuser_rela_detail c inner join (SELECT DISTINCT a.com_uni_code,a.com_chi_name,b.com_uni_code as code2,b.com_chi_name as name2FROM dmdc.bond_issuser_rela_detail aLEFT JOIN dmdc.t_com_info b ON a.com_chi_name=b.com_chi_nameWHERE a.com_uni_code is NULL and b.com_uni_code is not NULL ORDER BY b.com_uni_code) d on d.com_chi_name=c.com_chi_name
set c.com_uni_code = d.code2;update test_zhou.t_zid t set t.qk_id = (
select s.org_id from xinhua_data.sm_org_basic s where t.entity_name=s.org_name and s.data_status=1 limit 1
)where t.type=2;

删除ann_id字段中，在所给的条件中
delete from t_bond_ann_att_info where ann_id in ('13441','13442')删除表中多余的重复记录，重复记录是根据单个字段（peopleId）来判断，只留有rowid最小的记录
delete from people 
where peopleId  in (select  peopleId  from people  group  by  peopleId   having  count(peopleId) > 1)
and rowid not in (select min(rowid) from  people  group by peopleId  having count(peopleId )>1)删除表中多余的重复记录（多个字段），只留有rowid最小的记录
delete from vitae a
where (a.peopleId,a.seq) in  (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)
and rowid not in (select min(rowid) from vitae group by peopleId,seq having count(*)>1)DELETE from t_sm_org_basic WHERE company_zid not like 'Comp%';
DELETE from companynews_two WHERE content='' and id>='3855111';多表多字段关联删除
delete a from history_rongzi_investor a left join history_rongzi_investor_new b 
on a.invest_id=b.invest_id and a.investor_id=b.investor_id where b.investor_id is null;

//普通索引
alter table table_name add index index_name (column_list) USING BTREE COMMENT '普通索引';
//唯一索引
alter table table_name add unique (column_list) USING BTREE COMMENT '唯一索引';
//主键索引
alter table table_name add primary key (column_list) USING BTREE COMMENT '主键索引';

drop index index_name on table_name ;alter table table_name drop index index_name ;alter table table_name drop primary key ;