mysql修改记录

时间:2023-03-08 20:31:57
mysql修改记录

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