MySQL常用脚本

常用脚本

登录 : mysql -u root -p

退出： exit

添加用户：

use mysql;

INSERT INTO user (host, user, password, select_priv, insert_priv, update_priv) VALUES ('localhost', 'guest', PASSWORD('guest123'), 'Y', 'Y', 'Y');

FLUSH PRIVILEGES;

数据库操作：

use 数据库名；

show databases;

show tables;

show columns from 数据库；

创建数据库：

 mysqladmin -u root -p create 数据库

删除数据库：

 mysqladmin -u root -p drop 数据库；

数据类型：

数:

TINYINT,SMALLINT,MEDIUMINT,INT,BIGINT,FLOAT,DOUBLE,DECIMAL

日期时间：

DATE(yyyy-mm-dd),TIME(hh-mm-ss),YEAR,DATETIME,TIMESTAMP

字符：

CHAR,VARCHAR,TINYBLOB,TINYTEXT,BLOB,TEXT.MEDIUMBLOB,MEDIUMTEXT,LONGBLOB,LONGTEXT

操作：

CREATE TABLE xxx (col type, col type);

CREATE TABLE insect 

( 

id INT UNSIGNED NOT NULL AUTO_INCREMENT, 

PRIMARY KEY (id),

name VARCHAR(30) NOT NULL, # type of insect 

date DATE NOT NULL, # date collected 

origin VARCHAR(30) NOT NULL # where collected

);

DROP/DELETE TABLE xxx;

INSERT INTO xxx (xx,xx) VALUES (xx,xx);

SELECT xx FROM xx;

UPDATE xxx SET xx=xx;

导入导出：

SELECT * FROM xx INTO OUTFILE 'data.txt' FIELD TERMINATED BY ','  OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n'

LOAD DATA LOCAL INFILE 'dump.txt' INTO TABLE mytbl FIELDS TERMINATED BY ':' LINES TERMINATED BY '\r\n';