在linux下使用mysql

在linux下使用mysql

mysqladmin -u root password YOUR_PASSWORD

设置 root用户密码

mysqladmin -u root -p create DB_NAME 

创建数据库DB_NAME

mysql -u root -p albums

登入mysql客户端

CREATE TABLE collection (title VARCHAR(100), artist VARCHAR(100), genre VARCHAR(30), condition VARCHAR(10), worth DOUBLE(2,2), notes TEXT, released DATE, added DATE, opened ENUM('yes','no'), updated TIMESTAMP(14), ID int(11) NOT NULL auto_increment, PRIMARY KEY (ID))

sql 建表

table albums.collection

CREATE TABLE collection (
title VARCHAR(100),
artist VARCHAR(100),
genre VARCHAR(30),
condition VARCHAR(10),
worth DOUBLE(4,2),
notes TEXT,
released DATE,
added DATE,
opened ENUM('yes','no'),
ID int(11) NOT NULL auto_increment,
PRIMARY KEY (ID));

mysql -u root -p albums < collection.sql

sql 文件建表

'Armed Forces', 'Elvis Costello', 'Pop/Rock', 'exlnt',
'20.00', 'Probably his best', '1979-01-05', '1982-03-14', 'yes', '20030925201500'
'Zenyatta Mondatta', 'Police', 'Pop/Rock', 'fair',
'5.00', 'interesting tracks', '1980-10-03', '1981-12-25', 'yes', '20030925201500'

LOAD DATA INFILE '/home/bob/record_collection' INTO TABLE collecton FIELDS TERMINATED BY ',' ENCLOSED BY '\'' LINES TERMINATED BY '\n'

从文件导入数据

mysqldump --add-lock -u root -p --databases albums > albums_all.sql

备份数据库

mysql -u root -p < albums_all.sql

恢复数据库

mysqldump --add-lock albums -u root -p collection > albums_collection.sql  

备份具体的表

mysqldump --add-lock albums -u root -p collection -n -t "--where=updated like '200310%'" > oct_collection.sql

备份更新部分的数据 -n指不包含建库语句，-t 不包含建表语句

DROP table lexington; 

删除表

ALTER TABLE collection ADD COLUMN label VARCHAR(30); 

加字段

ALTER TABLE collection MODIFY condition VARCHAR(20);

改字段

INSERT INTO user VALUES('localhost','stan', PASSWORD('77JyC8'),'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y'); 

新建用户

FLUSH PRIVILEGES; 

更新权限

GRANT ALL ON album.* TO stan@localhost IDENTIFIED BY '77JyC8';

为用户对数据库授权

to do : insert select update delete DDL 语句及索引增加删除语句等常用语句就不一一列举。