增加一列：alter table bf_agt_dep_acct_sap_sub add column cust_age varchar(10) not null;
改变属性：alter table bf_agt_dep_acct_sap_sub modify cust_age int;
同时改变列名和属性：alter table student change column sname stuname varchar(20);
设置一列的值：update bf_agt_dep_acct_sap_sub set cust_age=19;
随机赋值：update bf_agt_dep_acct_sap_sub set cust_age = round(rand() * 50) + 1 where floor is null;
删除列：alter table bf_agt_dep_acct_sap_sub drop column cust_age;
改变列的大小写：update table set mediafile =lower(mediafile); //lower(),upper(),lcase(),ucase()
改变某一行的值：
注意：mysql字段名不区分大小写，表名区分
创建表：create table datausb(time varchar(20),temp int,humid int);
修改表名：alter table datausb rename to datausb0;
导入表格：load data local infile '/home/kang/Desktop/temp/datausb0.txt' into table datausb fields terminated by '\t';
load data local infile '/home/kang/Desktop/temp/data1.txt' into table BF_PR_IND_INFO_ECTIP fields terminated by '\t' lines terminated by '\r\n';
转化格式： iconv -f gb2312 -t utf8 ./data.txt >./data1.txt
导出表格：只导出数据，而不是一个insert的建表集（mysqldump）
方法一：select * from datausb0 into outfile '/home/kang/Desktop/temp/datausb0.txt' fields terminated by '\t' lines terminated by '\r\n';
出现错误：Can't create/write to file (Errcode: 13)
在确认不是目录权限的问题后，用下面方法解决
setsebool -P mysqld_disable_trans=1
方法二：mysql -uroot -p123456 -e "select * from datausb0" kang >/home/kang/Desktop/temp/datausb0.txt
由出生日期计算年龄：
year(birthday)-year(now()) 反了。。
select a.cust_name as cust_name,year(a.birday)-year(now()) as cust_age,b.original_dep_open_dt as original_dep_open_dt,b.update_dt as update_dt,a.month_incom as month_incom from bf_pr_ind_info_ectip a,b_s_cust_psummary b where a.dcc_custno = 'A4301111999102221290' and a.cert_no = b.cert_no0
查询各个字段
select COLUMN_NAME from information_schema.columns where table_schema= 'chen' and table_name= 'testpos';
查询字段长度
select count(*) from information_schema.columns where table_schema= 'chen' and table_name= 'testpos';