MySQL数据库管理常用命令
参考: http://blog.linuxeye.com/419.html
|
安装 |
利用RPM包安装MySQL |
|
|
设置TCP 3306端口的iptables |
||
|
root密码管理 |
设置root用户的密码 |
mysqladmin -u root password ‘password’ |
|
修改root用户的密码 |
mysqladmin -u root -p password ‘password’ |
|
|
数据库，表管理 |
进入mysql |
mysql -h hostname -u root -p |
|
创建数据库 |
mysql> create database location |
|
|
导入数据结构 |
mysql -u root -p location <./location.sql |
|
|
查看数据库 |
mysql> show databases; |
|
|
进入某个库 |
mysql> use location; |
|
|
查看表信息 |
mysql> show tables; |
|
|
查看表结构 |
mysql> desc Contact; |
|
|
改表名 |
mysql> rename table Contact to Contact_new |
|
|
删除库 |
mysql> drop database location |
|
|
删除表 |
mysql> drop table Contact |
|
|
授权部分 |
建立用户并授权 |
mysql> grant all on location.* to gk1020@’10.1.11.71′ identified by ‘gk1020′ |
|
取消授权 |
mysql> revoke all on location.* from gk1020@’10.1.11.71′ |
|
|
刷新权限 |
mysql> flush privileges |
|
|
操作语句 |
查询 |
mysql> select * from Contact |
|
mysql> select count(*) from Contact |
||
|
修改 |
mysql> update Contact set RegTime=‘2008-01-01 00:00:00’ where id=1 |
|
|
mysql> update Contact set RegTime=‘2008-01-01 00:00:00’,CID=1 where id=1 |
||
|
插入 |
mysql> insert into Contact values(”,”…) |
|
|
mysql> insert into Contact(ID,CID,Contact) values(”,”…) |
||
|
删除 |
mysql> delete from Contact where id=1 |
|
|
导出 |
导出数据库location |
mysqldump -u root -p location >./location.sql |
|
导出某个表 |
mysqldump -uroot -p –database location –table Contact >./contact.sql |
|
|
导出数据库location的数据结构 |
mysqldump -d -uroot -p location >./location.sql |
|
|
复制表 |
复制表Contact为Contact_bak |
mysql> create table Contact_bak as select * from Contact |
|
复制表Contact的结构 |
mysql> create table Contact_bak as select * from Contact where 1=2 |
|
|
查看正在执行的任务 |
mysql> show processlist |
|
|
说明: |
||
|
location为库名，Contact为表名 |
||
