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;