1.用load data 命令将数据从文本录入数据库:
数据库中pet表字段:
name owner species sex birth death
文本 d:\pet.txt,字段之间用 tab 隔开
whistle gwen bird f 1998-10-23
whistle gwen1 bird m 1998-10-24 2100-19-25
mysql>
load data local infile 'd:\pet.txt' into table pet;
2.日期计算
mysql> SELECT name, birth, CURDATE(),
-> (YEAR(CURDATE())-YEAR(birth))
-> - (RIGHT(CURDATE(),5)<RIGHT(birth,5))
-> AS age
-> FROM pet;
YEAR( )、MONTH( ),DAYOFMONTH( )
unsigned zerofill: 0零填充,1表示为0001
CREATE TABLE shop (
-> article INT(4) UNSIGNED ZEROFILL DEFAULT '0000' NOT NULL,
-> dealer CHAR(20) DEFAULT '' NOT NULL,
-> price DOUBLE(16,2) DEFAULT '0.00' NOT NULL,
-> PRIMARY KEY(article, dealer));
3. show create table pet,查看pet表的建表sql.
数据库中pet表字段:
name owner species sex birth death
文本 d:\pet.txt,字段之间用 tab 隔开
whistle gwen bird f 1998-10-23
whistle gwen1 bird m 1998-10-24 2100-19-25
mysql>
load data local infile 'd:\pet.txt' into table pet;
2.日期计算
mysql> SELECT name, birth, CURDATE(),
-> (YEAR(CURDATE())-YEAR(birth))
-> - (RIGHT(CURDATE(),5)<RIGHT(birth,5))
-> AS age
-> FROM pet;
YEAR( )、MONTH( ),DAYOFMONTH( )
unsigned zerofill: 0零填充,1表示为0001
CREATE TABLE shop (
-> article INT(4) UNSIGNED ZEROFILL DEFAULT '0000' NOT NULL,
-> dealer CHAR(20) DEFAULT '' NOT NULL,
-> price DOUBLE(16,2) DEFAULT '0.00' NOT NULL,
-> PRIMARY KEY(article, dealer));
3. show create table pet,查看pet表的建表sql.