2.1 数据库管理
1 [root@Mysql_server tools]# mysql -uroot -p 2 Enter password: 3 Welcome to the MySQL monitor. Commands end with ; or \g. 4 Your MySQL connection id is 8 5 Server version: 5.5.32 MySQL Community Server (GPL) 6 7 Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved. 8 9 Oracle is a registered trademark of Oracle Corporation and/or its 10 affiliates. Other names may be trademarks of their respective 11 owners. 12 13 Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. 14 15 mysql> show databases; ##查看数据库; 16 +--------------------+ 17 | Database | 18 +--------------------+ 19 | information_schema | 20 | mysql | 21 | performance_schema | 22 | test | 23 +--------------------+ 24 4 rows in set (0.00 sec) 25 26 mysql> drop database test; ##删除test库; 27 Query OK, 0 rows affected (0.00 sec) 28 29 mysql> show databases; 30 +--------------------+ 31 | Database | 32 +--------------------+ 33 | information_schema | 34 | mysql | 35 | performance_schema | 36 +--------------------+ 37 3 rows in set (0.00 sec) 38 39 mysql> select user,host from mysql.user; ##查看用户列表; 40 +------+---------------+ 41 | user | host | 42 +------+---------------+ 43 | root | 127.0.0.1 | 44 | root | ::1 | 45 | | Mysql\_server | 46 | root | Mysql\_server | 47 | | localhost | 48 | root | localhost | 49 +------+---------------+ 50 6 rows in set (0.00 sec) 51 52 mysql> drop user root@'::1'; ##删除无用用户 53 Query OK, 0 rows affected (0.00 sec) 54 55 mysql> drop user ''@'Mysql\_server'; ##对于大写、特殊字符删除不了,下面有介绍 56 Query OK, 0 rows affected (0.00 sec) 57 58 mysql> drop user ''@'localhost'; 59 Query OK, 0 rows affected (0.01 sec) 60 61 mysql> drop user 'root'@'Mysql\_server'; 对于大写、特殊字符删除不了,下面有介绍 62 Query OK, 0 rows affected (0.00 sec) 63 64 mysql> select user,host from mysql.user; 65 +------+---------------+ 66 | user | host | 67 +------+---------------+ 68 | root | 127.0.0.1 | 69 | | Mysql\_server | 70 | root | Mysql\_server | 71 | root | localhost | 72 +------+---------------+ 73 4 rows in set (0.00 sec) 74 对于大写、特殊字符删除不了解决办法: 75 mysql> delete from mysql.user where user="" and host="Mysql\_server"; 76 Query OK, 1 row affected (0.02 sec) 77 78 mysql> delete from mysql.user where user="root" and host="Mysql\_server"; 79 Query OK, 1 row affected (0.00 sec) 80 81 mysql> select user,host from mysql.user; ##将无用用户删除以后,查看用户列表 82 +------+-----------+ 83 | user | host | 84 +------+-----------+ 85 | root | 127.0.0.1 | 86 | root | localhost | 87 +------+-----------+ 88 2 rows in set (0.00 sec) 89 mysql> flush privileges; ##最后使得权限生效 90 Query OK, 0 rows affected (0.00 sec) |
创建用户sql语句:
1 mysql> grant all on *.* to 'root'@'localhost' identified by '123456' with grant option;flush privileges;