使用mysql
登录,可以用密码登录,也可以不用密码登录。命令格式“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 // 无密码登录
|
退出,命令“quit”
[root@localhost bin]# quit
|
创建数据库,命令“create database 数据库名称;”,注意这个命令后面有分号
mysql> create database test1;
Query OK, 1 row affected (0.00 sec)
|
查看数据库,命令“show databases;”
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| test |
| test1 |
+--------------------+
4 rows in set (0.00 sec)
|
删除数据库,命令“drop database 数据库名称;”
mysql> drop database test1;
Query OK, 0 rows affected (0.01 sec)
|
设置权限
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
免责声明:
① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的,并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据,供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。
② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341