MySql账号管理

mysql

MySQL是什么

MySQL 是最流行的数据库之一，是一个免费开源的关系型数据库管理系统，但也不意味着该数据库是完全免费的。MySQL 由瑞典 MySQL AB 公司开发，目前属于 Oracle 公司。MySQL 适合中小型软件，被个人用户以及中小企业青睐。

sql server oracle mysql 都是关系型数据管理系统

MySQL的优势

MySQL 数据库管理系统具有很多的优势，下面总结了其中几种。

1. MySQL 是开放源代码的数据库 MySQL 是开放源代码的数据库，任何人都可以获取该数据库的源代码。这就使得任何人都可以修正 MySQL 的缺陷，并且任何人都能以任何目的来使用该数据库。MySQL 是一款可以自由使用的数据库。

2. MySQL 的跨平台性 MySQL 不仅可以在 Windows 系列的操作系统上运行，还可以在 UNIX、Linux 和 Mac OS 等操作系统上运行。因为很多网站都选择 UNIX、Linux 作为网站的服务器，所以 MySQL 的跨平台性保证了其在 Web 应用方面的优势。虽然微软公司的 SQL Server 数据库是一款很优秀的商业数据库，但是其只能在 Windows 系列的操作系统上运行。因此，MySQL 数据库的跨平台性是一个很大的优势。

3. 价格优势 MySQL 数据库是一个自由软件，任何人都可以从 MySQL 的官方网站上下载该软件，这些社区版本的 MySQL 都是免费试用的，即使是需要付费的附加功能，其价格也是很便宜的。相对于 Oracle、DB2 和 SQL Server 这些价格昂贵的商业软件，MySQL 具有绝对的价格优势。

4. 功能强大且使用方便 MySQL 是一个真正的多用户、 多线程 SQL 数据库服务器。它能够快速、有效和安全的处理大量的数据。相对于 Oracle 等数据库来说，MySQL 的使用是非常简单的。MySQL 主要目标是快速、健壮和易用。

系统数据库

数据库可以看作是一个专门存储数据对象的容器，每一个数据库都有唯一的名称，并且数据库的名称都是有实际意义的，这样就可以清晰的看出每个数据库用来存放什么数据。在MySQL数据库中存在系统数据库和自定义数据库，系统数据库是在安装 MySQL 后系统自带的数据库，自定义数据库是由用户定义创建的数据库

1. information_schema

   information_schema提供了访问数据库元数据的方式。(元数据是关于数据的数据，如数据库名或表名，列的数据类型，或访问权限等。有时用于表述该信息的其他术语包括“数据词典”和“系统目录”。) 换句换说，information_schema是一个信息数据库，它保存着关于MySQL服务器所维护的所有其他数据库的信息。(如数据库名，数据库的表，表栏的数据类型与访问权 限等。) 在INFORMATION_SCHEMA中，有几张只读表。它们实际上是视图，而不是基本表。

2. mysql mysql的核心数据库，类似于sql server中的master表，主要负责存储数据库的用户、权限设置、关键字等mysql自己需要使用的控制和管理信息。(常用的，在mysql.user表中修改root用户的密码)。

3. performance_schema 主要用于收集数据库服务器性能参数。并且库里表的存储引擎均为PERFORMANCE_SCHEMA，而用户是不能创建存储引擎为PERFORMANCE_SCHEMA的表。MySQL5.7默认是开启的。

4. sys Sys库所有的数据源来自：performance_schema。目标是把performance_schema的把复杂度降低，让DBA能更好的阅读这个库里的内容。让DBA更快的了解DB的运行情况。

数据库操作

1. 查看数据库

在 MySQL 中，可使用 SHOW DATABASES 语句来查看或显示当前用户权限范围以内的数据库。查看数据库的语法格式为：

SHOW DATABASES;

1. 创建数据库

在 MySQL 中，可以使用 CREATE DATABASE 语句创建数据库，语法格式如下：

CREATE DATABASE [IF NOT EXISTS] <数据库名>
[[DEFAULT] CHARACTER SET <字符集名>] 
[[DEFAULT] COLLATE <校对规则名>];
[ENGINE=<存储引擎名称>]

• <数据库名>：创建数据库的名称。

• IF NOT EXISTS：在创建数据库之前进行判断，只有该数据库目前尚不存在时才能执行操作。此选项可以用来避免数据库已经存在而重复创建的错误。

• [DEFAULT] CHARACTER SET：指定数据库的字符集。指定字符集的目的是为了避免在数据库中存储的数据出现乱码的情况。如果在创建数据库时不指定字符集，那么就使用系统的默认字符集。查询当前服务器的字符集

SHOW VARIABLES LIKE 'character_set_server'

• [DEFAULT] COLLATE：指定字符集的默认校对规则。它们以字符集名开始,通常包括一个语言名,并且以ci(大小写不敏感)、cs(大小写敏感)或bin(二元,既比较是基于字符编码的值而与language无关)结束

#显示当前字符集的默认校对规则
SHOW VARIABLES LIKE 'collation_server'
#显示所有字符集的默认校对规则
show collation;

示例1:

CREATE DATABASE test_db_char;

示例2:

CREATE DATABASE IF NOT EXISTS test_db
 DEFAULT CHARACTER SET utf8
 DEFAULT COLLATE utf8_general_ci;

查询测试

SELECT * FROM t1 WHERE   NAME='a';
#BINARY强制它后面的字符串为一个二进制字符串
SELECT * FROM t1 WHERE  BINARY  NAME='a';

1. 删除数据库

在 MySQL 中，当需要删除已创建的数据库时，可以使用 DROP DATABASE 语句。其语法格式为：

DROP DATABASE [ IF EXISTS ] <数据库名>

用户管理

MySQL 是一个多用户数据库，具有功能强大的访问控制系统，可以为不同用户指定不同权限。在前面的 root 用户是超级管理员，拥有所有权限，包括创建用户、删除用户和修改用户密码等管理权限。

为了实际项目的需要，可以创建拥有不同权限的普通用户。

1. 创建用户

可以使用 CREATE USER 语句来创建 MySQL 用户，并设置相应的密码。其基本语法格式如下：

CREATE USER <用户>  IDENTIFIED BY 'password' 

1. 用户 指定创建用户账号，格式为 user_name'@'host_name。这里的user_name是用户名，host_name为主机名，即用户连接 MySQL 时所用主机的名字。如果在创建的过程中，只给出了用户名，而没指定主机名，那么主机名默认为“%”，表示一组主机，即对所有主机开放权限。

2. 使用 CREATE USER 语句时应注意以下几点： 使用 CREATE USER 语句必须拥有 mysql 数据库的 INSERT 权限或全局 CREATE USER 权限。 使用 CREATE USER 语句创建一个用户后，MySQL 会在 mysql 数据库的 user 表中添加一条新记录。

3. 新创建的用户拥有的权限很少，它们只能执行不需要权限的操作。如登录 MySQL、使用 SHOW 语句查询所有存储引擎和字符集的列表等。如果两个用户的用户名相同，但主机名不同，MySQL 会将它们视为两个用户，并允许为这两个用户分配不同的权限集合。

示例1：

使用 CREATE USER 创建一个用户，用户名是 test1，密码是 sasa，主机名是 localhost。SQL 语句和执行过程如下。

CREATE USER 'test1'@'localhost' IDENTIFIED BY 'sasa';

示例2:

CREATE USER 'test2' IDENTIFIED BY 'sasa';

查询mysql数据库中的user表

 use mysql;
 select * from user;

1. 查看用户信息

use mysql;
select host,user,authentication_string from user;

user表中host列的值的意义 % 匹配所有主机 localhost localhost不会被解析成IP地址，直接通过UNIXsocket连接 127.0.0.1 会通过TCP/IP协议连接，并且只能在本机访问； ::1 ::1就是兼容支持ipv6的，表示同ipv4的127.0.0.1

#修改host，充许远程登录
USE mysql;
UPDATE USER SET HOST='%' WHERE USER='test1';

1. 删除用户

drop user 用户名;

1. 修改用户密码

#修改密码格式
set password [for 用户名]=password('新密码');
#修改当前用户
set password=password('123123');
#修改指定用户
SET PASSWORD FOR test1@'%'=PASSWORD('123123');
刷新配置
flush privileges;

忘记密码

1. 停止服务

2. 打开命令窗口，停止权限检查

#mysql5.7
mysqld --skip-grant-tables
#mysql8.0
mysqld --console --skip-grant-tables --shared-memory

运行完之后，命令窗口等特状态。

1. 打开新的命令窗口,使用mysql免密连接

#默认端口
mysql
#如果不是默认端口，指定端口
mysql -P 3309

1. 修改mysql数据库中user表

use mysql;
update user set authentication_string=password('123') where user='root' and host='localhost';

权限管理

1. 授予权限(Grant)

GRANT priv_type [(column_list)] ON database.table TO user

• priv_type 参数表示权限类型；

• columns_list 参数表示权限作用于哪些列上，省略该参数时，表示作用于整个表；

• database.table 用于指定权限的级别；如果要授予用户对所有数据库和表的操作权限直接使用：*.*

• user 参数表示用户账户，由用户名和主机名构成，格式是“'username'@'hostname'”；

示例：

grant all on test_db.* to test1@'localhost';

授予表权限时，<权限类型>可以指定为以下值：

权限名称	对应user表中的字段	说明
SELECT	Select_priv	授予用户可以使用 SELECT 语句进行访问特定表的权限
INSERT	Insert_priv	授予用户可以使用 INSERT 语句向一个特定表中添加数据行的权限
DELETE	Delete_priv	授予用户可以使用 DELETE 语句从一个特定表中删除数据行的权限
DROP	Drop_priv	授予用户可以删除数据表的权限
UPDATE	Update_priv	授予用户可以使用 UPDATE 语句更新特定数据表的权限
ALTER	Alter_priv	授予用户可以使用 ALTER TABLE 语句修改数据表的权限
REFERENCES	References_priv	授予用户可以创建一个外键来参照特定数据表的权限
CREATE	Create_priv	授予用户可以使用特定的名字创建一个数据表的权限
INDEX	Index_priv	授予用户可以在表上定义索引的权限
ALL 或 ALL PRIVILEGES 或 SUPER	Super_priv	所有的权限名

1. 撤销权限(Revoke)

revoke priv_type [(column_list)] ON database.table from user

1. 查看用户权限

show grants for 用户名;

MySQL存储引擎

数据库存储引擎是数据库底层软件组件，数据库管理系统使用数据引擎进行创建、查询、更新和删除数据操作。简而言之，存储引擎就是指表的类型。数据库的存储引擎决定了表在计算机中的存储方式。不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能，使用不同的存储引擎还可以获得特定的功能。

现在许多数据库管理系统都支持多种不同的存储引擎。MySQL 的核心就是存储引擎。

MySQL 提供了多个不同的存储引擎，包括处理事务安全表的引擎和处理非事务安全表的引擎。在 MySQL 中，不需要在整个服务器中使用同一种存储引擎，针对具体的要求，可以对每一个表使用不同的存储引擎。

MySQL 5.7 支持的存储引擎有 InnoDB、MyISAM、Memory、Merge、Archive、CSV、BLACKHOLE 等。可以使用SHOW ENGINES;语句查看系统所支持的引擎类型

Support 列的值表示某种引擎是否能使用，YES表示可以使用，NO表示不能使用，DEFAULT表示该引擎为当前默认的存储引擎。

下面简要描写几种存储引擎，后面会对其中的几种（主要是 InnoDB 和 MyISAM ）进行详细讲解。

存储引擎	描述
ARCHIVE	用于数据存档的引擎，数据被插入后就不能在修改了，且不支持索引。
CSV	在存储数据时，会以逗号作为数据项之间的分隔符。
BLACKHOLE	会丢弃写操作，该操作会返回空内容。
FEDERATED	将数据存储在远程数据库中，用来访问远程表的存储引擎。
InnoDB	具备外键支持功能的事务处理引擎
MEMORY	置于内存的表
MERGE	用来管理由多个 MyISAM 表构成的表集合
MyISAM	主要的非事务处理存储引擎
NDB	MySQL 集群专用存储引擎

1. MyISAM 在 MySQL 5.1 版本及之前的版本，MyISAM 是默认的存储引擎。

MyISAM 存储引擎不支持事务和外键，所以访问速度比较快。如果应用主要以读取和写入为主，只有少量的更新和删除操作，并且对事务的完整性、并发性要求不是很高，那么选择 MyISAM 存储引擎是非常适合的。

1. InnoDB MySQL 5.5 版本之后默认的事务型引擎修改为 InnoDB。

InnoDB 存储引擎在事务上具有优势，即支持具有提交、回滚和崩溃恢复能力的事务安装，所以比 MyISAM 存储引擎占用更多的磁盘空间。

如果应用对事务的完整性有比较高的要求，在并发条件下要求数据的一致性，数据操作除了插入和查询以外，还包括很多的更新、删除操作，那么 InnoDB 存储引擎是比较合适的选择。