MySql增加用户、授权、修改密码等语句

数据库top_develop

登录数据库

1：新增用户

[mysql@lcamdb ~]$ mysql -u root -p

Enter password: *****

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>use mysql

mysql> insert into mysql.user(Host,User,Password,ssl_cipher,x509_issuer,x509_subject) values("localhost","1111",password("111"),'','','');

Query OK, 1 row affected (0.00 sec)

mysql> FLUSH PRIVILEGES;

Query OK, 0 rows affected (0.00 sec)

mysql> SELECT host, user, password FROM user WHERE user = "111";

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

| host      | user    | password                                  |

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

| localhost | 1111 | *9E2182EFC0FDC01E6EDC9FE113C4158587B31889 |

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

1 row in set (0.00 sec)

进行登录测试

[mysql@lcamdb mysql]$ mysql -u 1111 -p

Enter password: 

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

Your MySQL connection id is 35

Server version: 5.6.17 Source distribution

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use top_develop

ERROR 1044 (42000): Access denied for user '1111'@'localhost' to database '111'

2：进行授权，访问top_develop 数据库所有对象的权限

grant all privileges on top_develop.* to 1111@localhost identified by '1111';

Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges; 

Query OK, 0 rows affected (0.00 sec)

权限添加之后：

mysql> use top_develop

ERROR 1044 (42000): Access denied for user '1111'@'localhost' to database '2222'

mysql> use 2222

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

mysql> exit

Bye

如果想指定部分权限给一用户，可以这样来写: 

mysql>grant select,update on 2222.* 

3.删除用户。 

@>mysql -u root -p 

@>密码 

mysql>Delete FROM user Where User="1111" and Host="localhost"; 

mysql>flush privileges; 

//删除用户的数据库 

mysql>drop database 2222; 

4.修改指定用户密码。 

@>mysql -u root -p 

@>密码 

mysql>update mysql.user set password=password('新密码') where User="1111" and Host="localhost"; 

mysql>flush privileges; 

5.列出所有数据库 

mysql>show database; 

6.切换数据库 

mysql>use '数据库名'; 

7.列出所有表 

mysql>show tables; 

8.显示数据表结构 

mysql>describe 表名; 

9.删除数据库和数据表 

mysql>drop database 数据库名; 

mysql>drop table 数据表名;