一、登录 / 退出
# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor.
...
mysql> exit;
二、使用 / 操作
1、用户 创建/删除/授权/撤权/修改
举例:增加一个用户 user1,密码为 password1,让其可以在本机上登录:
mysql> insert into mysql.user(Host,User,Password) values("localhost","user1",password("password1"));
mysql> flush privileges;
##或者##
mysql> create user 'user1'@'localhost' IDENTIFIED BY 'password1';
授予数据库 testDB 的查询、插入、修改权限:
mysql> grant select,insert,update on testDB.* to user1@localhost identified by "password1";
mysql> flush privileges;
修改权限为所有数据库所有权限:
mysql> grant all privileges on *.* to
user1@"%" identified by "password1";
mysql> flush privileges;
撤销用户的修改权限:
mysql> revoke delete on testDB.* from user@localhost;
修改用户密码为 password2:
mysql> update mysql.user set password=password('password2') where User="user1" and Host="localhost";
mysql> flush privileges;
##或者##
mysql> set password for 'user1'@'localhost' = password("password2");
然后删除用户:
mysql> delete from user where user='user1' and host='localhost';
mysql> flush privileges;
##或者##
mysql> drop user 'user1'@'localhost';
2、数据库 创建/删除/列出/切换
mysql> create database [库名];
mysql> drop database [库名];
mysql> show databases;
mysql> use [库名];
3、数据表 创建/删除/列出/显示/修改/复制
mysql> create table [表名]([字段 类型]);
mysql> drop table [表名];
mysql> show tables;
mysql> desc [表名];
mysql> alter table [表名] [操作] column [列名] [类型];
mysql> create table [新表名] select * from [表名];
举例:创建一张 tbl 表,包含自增长的 id、不为空的 title 以及日期 date、默认为 2000.00 的工资,其中编码设为 utf8,主键为 id 和 date,:
mysql> CREATE TABLE IF NOT EXISTS tbl (
-> id INT UNSIGNED AUTO_INCREMENT COMMENT '编号',
-> title VARCHAR(100) NOT NULL COMMENT '标题',
-> date DATE COMMENT '日期',
-> salary FLOAT(10,2) NOT NULL DEFAULT 2000.00 COMMENT '工资',
-> PRIMARY KEY ( runoob_id,submission_date )
-> )ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.16 sec)
4、数据表数据 插入/索引/删除/修改/查询
mysql> insert into [表] values([值]);
mysql> create index [索引名] on [表]([字段]);
mysql> delete from [表] where [条件];
mysql> update [表] set [列]=[修改值] where [条件];
mysql> select [查询字段] from [表] where [条件];
三、备份 / 还原
导出或备份 数据库 db1,db2 到文本 /root/import.sql:
# mysqldump -u root -p123456 --databases db1 db2 > /root/import.sql
导入或还原 sql 备份 /root/import.sql 到 db1:
mysql> use db1;
mysql> source /root/import.sql;
##或者##
# mysqlimport -u root -p123456 < /root/import.txt
导入或还原 文本 备份 /root/import.txt 到 db1:
mysql> load data local infile "/root/import.txt" into table db1;