在服务器使用mysql_Linux服务器---使用mysql

使用mysql

1、登录，可以用密码登录，也可以不用密码登录。命令格式“mysql –u 用户名 –p 密码”

[root@localhost src]# mysql -u root–p//有密码登录

Enter password:

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

Your MySQL connection id is 3

[root@localhost src]#mysql -u root        //无密码登录

2、退出，命令“quit”

[root@localhost bin]# quit

3、创建数据库，命令“create database 数据库名称;”，注意这个命令后面有分号

mysql> create database test1;

Query OK, 1 row affected (0.00 sec)

4、查看数据库，命令“show databases;”

mysql> show databases;

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

| Database           |

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

| information_schema |

| mysql              |

| test               |

| test1              |

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

4 rows in set (0.00 sec)

5、删除数据库，命令“drop database 数据库名称;”

mysql> drop database test1;

Query OK, 0 rows affected (0.01 sec)

6、设置权限

mysql允许给某个特定的用户赋予指定的权利，而且可以指定在某台机器上使用。Mysql的权限如下

权限

数据库

Table

Column

说明

all privileges

√

所有权利

alter

√

√

增减、删除、修改列

create

√

√

创建数据库、表

delete

√

√

删除行

drop

√

√

删除表、数据库

file

√

操作文件

index

√

√

索引

insert

√

√

√

插入

process

√

查看线程、连接

reference

√

创建外键

reload

√

重新加载，拥有此权限可以刷新表

select

√

√

√

选择

shutdown

√

关闭

update

√

√

√

更新

usage

√

无权限，只能连接

1)授权用户权限，命令格式“grant 权限on 数据库文件to 用户名@ip identified by ‘密码’;”。在使用grant的时候，如果用户不存在，那么久创建用户。

//给david在本机授权插入功能，密码123456，只能对test01操作

mysql> grant insert on test01.* to david@localhost identified by '123456';

Query OK, 0 rows affected (0.00 sec)

mysql>

//给david所有权限，在所有的主机都可以操作，而且可以操作任意数据库

mysql> grant all privileges on *.* to david@'%' identified by '123456';

Query OK, 0 rows affected (0.00 sec)

mysql>

2)查看当前数据库所有授权情况，命令“select host，user from mysql.user”

mysql> select host,user from mysql.user;

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

| host                  | user  |

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

| %                     | david |

| 127.0.0.1             | root  |

| localhost             |       |

| localhost             | david |

| localhost             | root  |

| localhost.localdomain |       |

| localhost.localdomain | root  |

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

7 rows in set (0.00 sec)

mysql>

3)查看当前登录用户的权利，命令“show grants”

mysql> show grants;

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

| Grants for root@localhost                                                                                                             |

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

| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*1256939B1977AFF6C3D114C5594EE354EF363A8B' WITH GRANT OPTION |

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

1 row in set (0.00 sec)

mysql>

4)查看某个用户在某台机器的权限，命令“show grants for user@ip”

mysql> show grants for david@localhost;

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

| Grants for david@localhost                                                                                  |

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

| GRANT USAGE ON *.* TO 'david'@'localhost' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' |

| GRANT INSERT ON `test01`.* TO 'david'@'localhost'                                                            |

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

2 rows in set (0.00 sec)

mysql>

5)删除用户的权限，命令“revoke 权限on  数据库文件  from  user@ip”

mysql> revoke all privileges on *.* from david@'%';

Query OK, 0 rows affected (0.00 sec)

mysql>show grants for david@localhost;        //删除之后查看一下

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

| Grants for david@localhost                                                                                  |

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

| GRANT USAGE ON *.* TO 'david'@'localhost' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' |

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

2 rows in set (0.00 sec)

mysql>

6)删除用户，命令“delete from user where user=‘username’”

mysql> use mysql;       //首先要调用这个命令

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> delete from user where user='david';    //删除用户

Query OK, 2 rows affected (0.00 sec)

mysql> select host,user from mysql.user;     //查看用户

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

| host                  | user |

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

| 127.0.0.1             | root |

| localhost             |      |

| localhost             | root |

| localhost.localdomain |      |

| localhost.localdomain | root |

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

5 rows in set (0.00 sec)

mysql>

做了一个Linux学习的平台，目前出来一个雏形，各位可以参考使用

链接:https://pan.baidu.com/s/1GOLVU2CbpBNGtunztVpaCQ  密码:n7bk