MySQL的管理，包括新建账户、基本信息查看



    上篇博文介绍了如何安装MySQL，本文会对如何操作MySQL数据库进行介绍。首先登陆进入mysql>状态。

1  在mysql>状态下，输入命令：

   help 或 help contents

   可以得到很多帮助信息：

   Help

   Note that all text commands must be firston line and end with ';'

   ?        (\?) Synonym for `help'.

   clear     (\c) Clear the current input statement.

   connect   (\r) Reconnect to the server. Optional arguments are db and host.

   Delimiter  (\d) Set statement delimiter.

   ego      (\G) Send command to mysql server, display result vertically.

   exit      (\q) Exit mysql. Same as quit.

   go       (\g) Send command to mysql server.

   help     (\h) Display this help.

   notee    (\t) Don't write into outfile.

   print     (\p) Print current command.

   prompt   (\R) Change your mysql prompt.

   quit      (\q) Quit mysql.

   rehash   (\#) Rebuild completion hash.

   source    (\.) Execute an SQL script file. Takes a file name as an argument.

   status   (\s) Get status information from the server.

   tee      (\T) Set outfile [to_outfile]. Append everything into given outfile.

   use      (\u) Use another database. Takesdatabase name as argument.

   charset  (\C) Switch to another charset. Might be needed for processing binlogwith multi-byte charsets.

   warnings (\W) Show warnings after every statement.

   Nowarning (\w) Don't show warnings after every statement.

   Resetconnection (\x) Clean session context.

   【注意，每行命令必须以分号结束。】

   Help contents

   For more information, type 'help<item>', where <item> is one of the following

   categories:

      Account Management

      Administration

     Compound Statements

      Data Definition

      Data Manipulation

      Data Types

     Functions

      Functions and Modifiers for Use with GROUP BY

      Geographic Features

      Help Metadata

      Language Structure

     Plugins

      Procedures

     Storage Engines

      Table Maintenance

      Transactions

     User-Defined Functions

      Utility

 

   mysql> help Account Management

   You asked for help about help category:"Account Management"

   For more information, type 'help<item>', where <item> is one of the following

   topics:

      ALTER USER

     CREATE USER

      DROP USER

     GRANT

      RENAME USER

     REVOKE

     SET PASSWORD

 

2  建立mysql账户

   Mysql 有一个默认数据库：

   输入命令：

   use mysql;

   select * from user ;

可以看到里面存储的用户信息，包括很多字段。

 

示例输出：

命令：select host,user,select_priv,insert_priv from user ;

3 创建用户：

mysql> insert into user(host,user,authentication_string,select_priv,insert_priv,update_priv,ssl_cipher,x509_issuer,x509_subject)values('localhost','luise2','luise222','y','y','y','y','y','y');

Query OK, 1 row affected (0.05 sec)

查询如下：

使用PASSWORD()函数对密码加密

insert into user(host,user,authentication_string,select_priv,insert_priv,update_priv,ssl_cipher,x509_issuer,x509_subject)values('localhost','luise3',PASSWORD('luise333'),'y','y','y','y','y','y');

【注意，我们使用FLUSH PRIVILEGES语句，让服务器重新加载授权表。就可以立马使用新账户连接账户】

下面列出了一些重要且经常会用到的MySQL命令：

USE Databasename 用于在MySQL工作区内选择具体某个数据库。

SHOW DATABASES 列出 MySQL DBMS所能访问的数据库。

SHOW TABLES 一旦数据库被 use命令选中，显示数据库中的表。

SHOW COLUMNS FROM tablename 显示表的属性、属性类型、键信息、是否允许 NULL值，默认值，以及其他一些信息。

SHOW INDEX FROM tablename 显示表中所有索引的细节信息，包括PRIMARY KEY。

SHOW TABLE STATUS LIKE tablename\G 报告MySQL DBMS的性能及统计的细节信息。【注意表名必须加单引号，不然会出错】