-mysql-1

最新推荐文章于 2023-03-17 13:24:04 发布

原创最新推荐文章于 2023-03-17 13:24:04 发布 · 157 阅读

0 ·

CC 4.0 BY-SA版权

SQL体系专栏收录该内容

8 篇文章

订阅专栏

本文详细介绍MySQL数据库的连接与管理方法，包括数据库创建、用户权限设置、远程访问配置及常见命令使用。通过实例演示如何修改用户主机地址、查看数据库信息、进程管理和表结构查询。

1mysql命令

mysql    直接回车  空账号空密码
mysql -uroot -p   回车 再回车    使用这种history中不显示密码
mysql -uroot -p -hlocalhost   ##机器名称登录

2三句话

create database ruozedata;   数据库

grant all privileges on ruozedata.* to ruoze@'localhost' identified by '123456';    用户

flush privileges;      刷新权限

3案例拓展

show grants for ruoze@'%';       ##发现缺少权限 ，再次执行权限

grant all privileges on ruozedata.* to ruoze@'%' identified by '123456';
flush privileges;      刷新权限

 mysql> use mysql;  ##命令
Database changed
mysql> select user,host,password from user;##命令
+-------+-----------+-------------------------------------------+
| user  | host      | password                                  |
+-------+-----------+-------------------------------------------+
| root  | localhost | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| root  | hadoop001 | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| root  | 127.0.0.1 | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| root  | ::1       | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| ruoze | localhost | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+-------+-----------+-------------------------------------------+
5 rows in set (0.00 sec)

mysql> update user set host='106.75.103.%' where user='ruoze';     ##命令修改
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> flush privileges;##命令生效
Query OK, 0 rows affected (0.00 sec)

mysql> select user,host,password from user;##命令
+-------+--------------+-------------------------------------------+
| user  | host         | password                                  |
+-------+--------------+-------------------------------------------+
| root  | localhost    | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| root  | hadoop001    | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| root  | 127.0.0.1    | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| root  | ::1          | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| ruoze | 106.75.103.% | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+-------+--------------+-------------------------------------------+
5 rows in set (0.00 sec)

mysql> show databases;  查看数据库
mysql> kill 193;     杀死进程
ERROR 1317 (70100): Query execution was interrupted
mysql> show processlist;   查看进程
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    203
Current database: *** NONE ***

+-----+-------+----------------------+-----------+---------+------+-------+------------------+
| Id  | User  | Host                 | db        | Command | Time | State | Info             |
+-----+-------+----------------------+-----------+---------+------+-------+------------------+
| 201 | ruoze | 117.136.24.171:50898 | ruozedata | Sleep   |   47 |       | NULL             |
| 202 | ruoze | 117.136.24.171:53383 | ruozedata | Sleep   |   46 |       | NULL             |
| 203 | ruoze | localhost            | NULL      | Query   |    0 | init  | show processlist |
+-----+-------+----------------------+-----------+---------+------+-------+------------------+
3 rows in set (0.00 sec)

mysql> show tables;   ##查看mysql数据库下有多少张表
mysql> desc user;    ##查看user底下有多少个字段
mysql> show create table user;  ##查看创建user表的创建语法