当前位置：Gxlcms > 数据库问题 > MySQL用户管理及SQL语句详解

MySQL用户管理及SQL语句详解

时间：2021-07-01 10:21:17 帮助过：12人阅读

主机域

mysql> select user,host,password from mysql.user;
+--------+------------+-------------------------------------------+
| user   | host       | password                                  |
+--------+------------+-------------------------------------------+
| root   | localhost  | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
| root   | 127.0.0.1  |                                           |
| znix   | 172.16.1.% | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
| clsn   | 172.16.1.% | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
| root   | 10.0.0.1   | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
+--------+------------+-------------------------------------------+
6 rows in set

1.1.2 用户的作用

　　　　1、用户登录

　　　　2、用于管理数据库及数据

1.1.3 连接数据库

 　　  定义用户：用户名+主机域，密码

   　　定义权限：对不同的对象进行权限(角色)定义

命令:

  grant 权限 on 权限范围  to 用户  identified  by ‘密码‘

权限

        对数据库的读、写等操作
    （insert update、select、delete、drop、create等）

角色

    数据库定义好的一组权限的定义
    （all privileges、replication slave等）

权限范围

    全库级别： *.*
    单库级别：clsn.*
    单表级别：clsn.t1

用户

    ‘clsn‘@‘localhost‘  本地
    ‘clsn‘@‘192.168.66.149‘
    ‘clsn‘@‘192.168.66.%‘
    ‘clsn‘@‘192.168.66.14%‘

1.1.4 【练习题】按照要求创建用户

用户只能通过10.0.0.0/24网段访问，用户名为clsn 密码为123

这个用户只能对clsn数据库下的对象进行增insert create、改update 、查select；

  创建命令：

    grant select,create,insert,update on clsn.* to ‘clsn‘@‘10.0.0.%‘ identified by ‘123‘;

查看用户权限

mysql>  show grants for clsn@‘172.16.1.%‘\G
*************************** 1. row ***************************
Grants for clsn@172.16.1.%: GRANT USAGE ON *.* TO ‘clsn‘@‘172.16.1.%‘ IDENTIFIED BY PASSWORD ‘*23AE809DDACAF96AF0FD78ED04B6A265E05AA257‘
1 row in set (0.00 sec)

查看当前存在的用户:

select user,host from mysql.user;

创建用户语法

CREATE USER ‘用户‘@‘主机‘ IDENTIFIED BY ‘密码‘;

示例： 

create user ‘clsn‘@‘localhost‘ identified by ‘clsn123‘;

注意这个样创建的用户只有连接权限

企业里创建用户一般是授权一个内网网段登录，最常见的网段写法有两种。

方法1：172.16.1.%（%为通配符，匹配所有内容）。

方法2：172.16.1.0/255.255.255.0，但是不能使用172.16.1.0/24，是个小遗憾。

标准的建用户方法：

create user ‘web‘@‘172.16.1.%‘ identified by ‘web123‘;

查看用户对应的权限

show grants for oldboy@localhost\G

1.1.5 用户删除

删除用户语法：

drop user ‘user‘@‘主机域‘

【练习】用户优化：只保留

| root | 127.0.0.1 |
| root | localhost |

 

特殊的删除方法:(慎用，尽量不要直接去修改表)

mysql> delete from mysql.user where  user=‘clsn‘ and host=‘localhost‘; 
Query OK, 1 row affected (0.00 sec)
mysql> flush privileges;

1.1.6 用户授权

给用户授权

# 创建用户
create user ‘clsn‘@‘localhost‘ identified by ‘clsn123‘;
# 查看用户
select user,host from mysql.user;
# 授权所有权限给clsn用户
GRANT ALL ON *.* TO ‘clsn‘@‘localhost‘;
# 查看clsn用户的权限
SHOW GRANTS FOR ‘clsn‘@‘localhost‘\G

创建用户的同时授权

grant all on *.* to clsn@‘172.16.1.%‘ identified by ‘clsn123‘;
# 刷新权限
flush privileges; #<==可以不用。

创建用户然后授权

create user ‘clsn‘@‘localhost‘ identified by ‘clsn123‘;
GRANT ALL ON *.* TO ‘clsn‘@‘localhost‘;

授权和root一样的权限

grant all on *.* to system@‘localhost‘ identified by ‘clsn123‘ with grant option;

授权给用户select,create,insert,update 权限

grant select,create,insert,update on clsn.* to ‘clsn‘@‘10.0.0.%‘ identified by ‘123‘;

回收权限

REVOKE INSERT ON *.* FROM clsn@localhost;

可以授权的用户权限

INSERT,SELECT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, 
PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, 
CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, 
REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER 
ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE

【示例】博客授权收回示例

grant select,insert,update,delete,create,drop on blog.* to ‘blog‘@‘172.16.1.%‘ identified by ‘blog123‘;
revoke create,drop on blog.* from ‘blog‘@‘172.16.1.%‘;

   授权博客类的最多权限：select,insert,update,delete

1.2 MySQL 客户端工具

1.2.1 MysQL客户端命令介绍

　　mysql命令客户端

　　　　用于数据库连接管理

　　　　将 用户SQL 语句发送到服务器

　　mysqladmin命令 ：命令行管理工具

　　mysqldump命令 ：备份数据库和表的内容

1.2.2 mysql命令说明

　　用于连接数据库

　　用于管理数据库通过下列方式进行管理

命令接口自带命令

　　DDL：数据定义语言

　　DCL：数据控制语言

　　DML：数据操作语言

　　mysql命令接口自带命令说明

命令

命令说明

\h 或 help 或 ? 

获取帮助

\G

格式化输出（行转列）

\T 或 tee

记录操作日志  tee /tmp/mysql.log

\c 或 CTRL+c

退出mysql

\s 或 status

查看数据库状态信息

\. 或 source

mysql> source /tmp/world.sql

\!

使用shell中的命令

 mysql> \! cat /etc/redhat-release

CentOS release 6.9 (Final) 

\u 或use   

use  world

show databases  看当前所有数据库的名字

show tables   查看当前use到的数据库所有的表

show  tables from world   查看目标数据库下的表

快捷键

上下翻页、TAB键、ctrl +C 、ctrl +L

1.2.2.1  mysql中help命令的使用

　　在mysql命令行中输入mysql或 ?都可以查看帮助

mysql> help

　　使用 help contents查看完整的sql类别列表帮助

mysql> help contents

　　有关特定 SQL 类别或语句的帮助

mysql> help Account Management

　　查看 grant 的帮助

mysql> help GRANT

　　有关与状态相关的 SQL 语句的帮助

mysql> help status

1.2.2.2  source命令的使用

在 mysql 中处理输入文件：

如果这些文件包含 SQL 语句，则称为“脚本文件”或“批处理文件”。

使用 SOURCE 命令：

mysql> SOURCE /data/mysql/world.sql

　　或者使用非交互式：(尽量避免使用mysql 导入数据，会产生大量的无用日志)

mysql</data/mysql/world.sql

1.2.3 mysqladmin命令说明

基本语法

mysqladmin -u<name> -p<password> commands

　　命令说明图表

功能选项

说明

mysqladmin -u用户 -p密码 ping

“强制回应 (Ping)”服务器。

mysqladmin -u用户 -p密码 shutdown

关闭服务器。

mysqladmin -u用户 -p密码 create databasename

创建数据库。

mysqladmin -u用户 -p密码drop databasename

删除数据库

mysqladmin -u用户 -p密码 version

显示服务器和版本信息

mysqladmin -u用户 -p密码 status

显示或重置服务器状态变量

mysqladmin -u用户 -p密码 password

设置口令

mysqladmin -u用户 -p密码 flush-privileges

重新刷新授权表。

mysqladmin -u用户 -p密码 flush-logs

刷新日志文件和高速缓存。

以上信息通过mysqladmin  --help 获得

1.2.4 mysqldump简单说明

　　mysqldump是一款数据库备份工具。

命令帮助及基本语法：

[root@db02 ~]# mysqldump --help
Dumping structure and contents of MySQL databases and tables.
Usage: mysqldump [OPTIONS] database [tables]
OR     mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
OR     mysqldump [OPTIONS] --all-databases [OPTIONS]

　　情参照mysqldump --help

1.3 SQL语句入门

1.3.1 DDL语句(数据定义语言)

定义范围：

        库 ：名字、特性

        表：表名字、列

1.3.1.1  数据库查看

查看数据库--查看全部

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| clsn               |
+--------------------+

   查看数据库--模糊匹配

mysql> show databases like "%s%";
+--------------------+
| Database (%s%)     |
+--------------------+
| information_schema |
| clsn               |
| mysql              |
| performance_schema |
+--------------------+
4 rows in set (0.00 sec)

查看你相关的帮助

mysql> ? show databases;
Name: ‘SHOW DATABASES‘
Description:
Syntax:
SHOW {DATABASES | SCHEMAS}
    [LIKE ‘pattern‘ | WHERE expr]

1.3.1.2  数据库操作

　　创建一个数据库

mysql> create database haha;

   通过show 命令能够查看创建的数据库的格式

mysql> show create database haha;
+----------+---------------------------------------------------------------+
| Database | Create Database                                               |
+----------+---------------------------------------------------------------+
| haha     | CREATE DATABASE `haha` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+---------------------------------------------------------------+
1 row in set (0.00 sec)

创建数据库时定义字符编码

mysql> create database clsn charset utf8 ;
mysql> show create database clsn;     查询数据库定义信息。

存在的数据库修改字符编码：

mysql> alter database clsn charset gbk;

修改数据库编码格式示例

mysql> alter database haha charset gbk
    -> ;
Query OK, 1 row affected (0.00 sec)

mysql> show create database haha;
+----------+--------------------------------------------------------------+
| Database | Create Database                                              |
+----------+--------------------------------------------------------------+
| haha     | CREATE DATABASE `haha` /*!40100 DEFAULT CHARACTER SET gbk */ |
+----------+--------------------------------------------------------------+
1 row in set (0.00 sec)

　　标准修改系统

ALTER DATABASE [db_name] CHARACTER SET  charset_name COLLATE collation_name;
ALTER DATABASE clsn CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

查看支持的字符集和校对规则.

mysql> show character set;

人气教程排行

本站所有资源全部来源于网络，若本站发布的内容侵害到您的隐私或者利益，请联系我们删除！