Mysql入门教程--基础命令


#mysql

-----------------------------------------------------------------------------------------

Welcome to the MySQL monitor.  Commands end with ; or \g.

mysql>                                                                          

// 进入mysql

-----------------------------------------------------------------------------------------

mysql> mysql -u root

    -> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('newpassword');                                                                               

//修改root密码

mysql>show processlist;                                                         

//显示了有哪些线程在运行

mysql> show engines;                                                            

//看你的mysql现在已提供什么存储引擎  

mysql> show variables like '%storage_engine%';                                  

//看你的mysql当前默认的存储引擎

mysql> show charset;                                                            

//查看mysql所支持的字符集

mysql> show variables like '%char%';                                            

//查看MySQL数据库服务器和数据库字符集 

mysql> show table status from mysql_db like '%tb_name%'                         

//查看表的字符集  

mysql> show full columns from tb_name;                                          

//查看表中所有列的字符集

set character_set_connection=gb2312;       

//修改连接层字符集

set character_set_database=gb2312;         

//修改当前选中数据库的默认字符集

set character_set_results=gb2312;          

//修改查询结果字符集

set character_set_server=gb2312;           

//修改默认的内部操作字符集

set character_set_client=gb2312;           

//修改客户端来源数据使用的字符集

set character_set_system=gb2312;           

//修改系统元数据(字段名等)字符集

set collation_connection=gb2312;

set collation_database=gb2312;

set collation_server=gb2312;    

//还有以collation_开头的同上面对应的变量，用来描述字符序           

ALTER TABLE tb_name CONVERT TO CHARACTER SET gb2312;                                        

//修改表的字符集 

alter table tb_name modify column tb_column varchar(30) character set gb2312 not null;      

//修改字段字符集 

-----------------------------------------------------------------------------------------

mysql> SELECT DISTINCT CONCAT('User: ''',user,'''@''',host,''';') AS query FROM mysql.user;             

//查看数据库登陆所有用户

+----------------------------+

| query                      |

+----------------------------+

| User: 'root'@'127.0.0.1';  |

| User: ''@'localhost';      | 

| User: 'root'@'localhost';  |

| User: ''@'webq';           |

| User: 'root'@'webq';       |

+----------------------------+

5 rows in set (0.00 sec)

mysql> select * from mysql.user where user='root' \G                                         

//查看数据库中具体某个用户的权限 

mysql> desc mysql.user;                                                               

//查看mysql.user表结构　

mysql> show databases;

//显示所有数据库

+--------------------+

| Database           |

+--------------------+

| information_schema |

| mysql              |

| test               |

+--------------------+

3 rows in set (0.00 sec)

mysql> use mysql;

//打开数据库mysql;切换数据库

mysql> show tables;

//显示数据库mysql的所有表

-----------------------------------------------------------------------------------------

mysql> insert into mysql.user(Host,User,Password) values("localhost","TesT",password("p@ssw0rd"));             

//增加数据库用户

mysql> flush privileges;     

//刷新系统权限表；

+----------------------------+

| query                      |

+----------------------------+

| User: 'root'@'127.0.0.1';  |

| User: ''@'localhost';      |

| User: 'TesT'@'localhost'; |

| User: 'root'@'localhost';  |

| User: ''@'webq';           |

| User: 'root'@'webq';       |

+----------------------------+

6 rows in set (0.00 sec)

mysql> delete from mysql.user where user='filon' and host='localhost';                                  

//删除数据库用户命令：

+---------------------------+

| query                     |

+---------------------------+

| User: 'root'@'127.0.0.1'; |

| User: ''@'localhost';     |

| User: 'root'@'localhost'; |

| User: ''@'webq';          |

| User: 'root'@'webq';      |

+---------------------------+

5 rows in set (0.00 sec)

mysql> CREATE USER 'TesT'@'localhost' IDENTIFIED BY 'p@ssw0rd'; 

//创建数据库用户

+---------------------------+

| query                     |

+---------------------------+

| User: 'root'@'127.0.0.1'; |

| User: ''@'localhost';     |

| User: 'TesT'@'localhost'; |

| User: 'root'@'localhost'; |

| User: ''@'webq';          |

| User: 'root'@'webq';      |

+---------------------------+

6 rows in set (0.00 sec)

mysql> create database TesT;

mysql> show databases;

+--------------------+

| Database           |

+--------------------+

| information_schema |

| TesT               |

| mysql              |

| test               |

+--------------------+

4 rows in set (0.00 sec)

mysql> grant all privileges on TesT.* to TesT@localhost identified by 'p@ssw0rd';

数据库授权:

mysql> grant select,insert,update,delete on    *.* to username@"%" Identified by "p@ssw0rd";

mysql> grant select,insert,update,delete on test.* to username@localhost identified by "p@ssw0rd";

mysql> grant all privileges on test.* to username@localhost identified by 'p@ssw0rd';

//数据库授权格式：grant select on 数据库.* to 用户名@登录主机 identified by "密码"

mysql>flush privileges; 

//刷新系统权限表

mysql> drop user TesT@'localhost';

//删除账户及权限

mysql> create database <数据库名>;

//创建数据库

mysql> CREATE TABLE IF NOT EXISTS <数据库表名> (

column1 INT(11),

column2 VARCHAR(255),

column3 DATE,

.

.

.

//A table must have at least 1 column

);

//创建数据库表

mysql> drop database 库名;                                                                         

//删除数据库

mysql> drop table 表名;                                                                           

//删除表

-----------------------------------------------------------------------------------------

mysql> mysqlcheck --all-databases -o                       

//优化数据库 #optimize（一周使用一次就可以，酌情适当增加使用次数）

mysql> mysqlcheck –all-databases -r                       

//修复数据库  #repair

mysql> mysqlcheck –all-databases -a                       

//分析数据库  #analyze

mysql> mysqlcheck -r steelssc_ccc                       

//修复单个数据库 #repair

mysql> mysqlcheck -r database1 database2 ···                  

///修复多个数据库 #repair

-----------------------------------------------------------------------------------------

  

                                  

备份数据库 (命令其实非常简单，可以不需要密码：)

方法一：

mysql> mysqldump TesT_bbs > TesT_bbs.sql

//进入到库目录    备份数据库   

格式:mysql> mysqldump -u root -p --opt 数据库名>备份名;  

方法二：mysql

>use TesT_bbs;

>source /home/TesT/public_html/TesT_bbs.sql;

恢复数据库

mysql> mysql -u root -p 数据库名<备份名;                                               

//恢复时数据库必须存在，可以为空数据库

导入数据库

#mysql TesT_bbs < /home/TesT/public_html/TesT_bbs.sql

//导入数据库：mysql 数据库名称 < 绝对路径+要导入数据库名称.sql