增加一列: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'
;