mysql基础 - #1

这是 DataWhale 新闻推荐系统实战的 Task2，主要任务有：

  • mysql 基础
  • MongoDB 基础
  • redis 基础

本篇博客主要是关于 mysql 基础，Fun-rec的文档在这里。

在开始之前，先了解一下mysql是个什么。

mysql 是一个开源的关系型数据库系统。拆解一下：mysql = my + sql。sql(Structured Query Language)是由ANSI定义的一个标准，ANSI 并没有规定 sql 的具体实现方式，只有sql能干啥事、用到的一些模块等。常听到的各种关系数据库，如MySQL、SQLServer、Oracle等，这些都是对sql标准的一个实现和扩展。那么mysql就是在sql标准上的一个实现和扩展，my指的是实现和扩展。

看一张mysql的架构图，参考博客[1]
mysql架构

可以很明显的发现，mysql整体分为：server、存储引擎、client，

  • server：大多数MySQL的核心服务功能都在这一层，包括查询解析、分析、优化、缓存以及所有的内置函数（例如，日期、时间、数学和加密函数），所有跨存储引擎的功能都在这一层实现：存储过程、触发器、视图等；
  • 存储引擎：存储引擎负责MySQL中数据的存储和提取。Server层通过API与存储引擎进行通信。这些接口屏蔽了不同存储引擎之间的差异，使得这些差异对上层的查询过程透明；
  • client：完成客户端的链接安全服务。负责一些连接处理、授权认证以及相关安全方案；
    以上介绍参考了[2][3]。其实，从mysql的github来看，还有一些其他的工具，如mysql-docker、mysql-shell等，但最常使用的就是上面这三种了。

1. 安装及初始设置

了解了mysql的整体架构，需要安装什么我们也就清楚了 --- server、存储引擎、client，安装时，存储引擎应该已经包含在server里了。

安装

  1. Ubuntu
    sudo apt install mysql-server mysql-client
  2. Windows
    参考MySQL Installer for Windows

接下来的介绍都已 Ubuntu 平台为基础。

配置MySQL的安全性

安装 mysql_secure_installation 插件：sudo mysql_secure_installation
过程参考：配置MySQL的安全性

登录 root

刚安装好 mysql 后，可能是无法直接使用密码登录 root 用户的。

  1. 通过 auth_socket 登录 root 用户
    在MySQL 8.0上，root 用户默认通过auth_socket插件授权。这种方式是不需要密码的，但是只能以 root 用户登录。登录方式：sudo mysql，这样就以 root 用户的身份登录了 mysql。为什么要这样呢？参考这里[4]。

  2. 通过密码登录

-- 语法中的'你的密码’指的是你自己设置的登录密码，可设置为字母数字组合。
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '你的密码';
FLUSH PRIVILEGES;

当然，这种方式的前提是要先登录mysql。

  1. 新建一个管理用户
CREATE USER '用户名'@'localhost' identified by '你的密码'

# 赋予admin用户全部的权限，你也可以只授予部分权限
GRANT ALL PRIVILEGES ON *.* TO '用户名'@'localhost';

这种方式也需要提前登录 root。

2. 用户管理

创建用户

  1. 使用CREATE USER语句创建用户
  • 语法：CREATE USER '用户名1'@'host_name1' [ IDENTIFIED BY [ PASSWORD ] '密码' ] [ ,'用户名2'@'host_name2' [ IDENTIFIED BY [ PASSWORD ] '密码' ]]
  • 注意其中 用户名、host_name、密码 由你自己指定
  • 可以一次性创建多个用户

  1. 使用 INSERT 语句新建用户
    在mysql中有一个预先创建好的数据库 myqsl 数据库，其中有一个表：user，其中就存储了已经有的用户名。因此，可以通过操作这张表来新建一个用户，操作和在一张表中插入数据是一样的。但是要注意哪些字段是没有默认值的，插入时要设置这些列的值。

  2. 使用GRANT语句新建用户

  • 在创建用户的时候顺便给用户授权
  • 语法：GRANT priv_type ON database.table TO '用户名'@'host_name' [IDENTIFIED BY [PASSWORD] 'password']
  • 其中 用户名、host_name 由你自己指定

删除用户

  1. Drop user
  • 语法：DROP USER <用户1> [ , <用户2> ]…
  • 用户的删除不会影响他们之前所创建的表、索引或其他数据库对象，因为 MySQL 并不会记录是谁创建了这些对象
  1. delete
  • 语法：DELETE FROM mysql.user WHERE Host='hostname' AND User='username'
  • Host 和 User 这两个字段都是 mysql.user 表的主键。因此，需要两个字段的值才能确定一条记录

权限管理

数据库的权限管理是十分复杂的，通常由专门的人员负责（DBA）。mysql的权限由四种级别[5]：

  • 列权限，和表中的一个具体列相关。例如，可以使用 UPDATE 语句更新表 students 中 student_name 列的值的权限
  • 表权限，和一个具体表中的所有数据相关。例如，可以使用 SELECT 语句查询表 students 的所有数据的权限
  • 数据库权限，和一个具体的数据库中的所有表相关。例如，可以在已有的数据库 mytest 中创建新表的权限
  • 用户权限，和 MySQL 中所有的数据库相关。例如，可以删除已有的数据库或者创建一个新的数据库的权限

这里就看看 grant 语句吧：

GRANT
<权限类型> [ ( <列名1>,<列名2>,... ) ] [ , <权限类型> [ ( <列名>,<列名2>,... ) ] ]
ON <对象> <权限级别> TO <用户>
其中<用户>的格式：
<用户名> [ IDENTIFIED ] BY [ PASSWORD ] <密码>
[ WITH GRANT OPTION]
| MAX_QUERIES_PER_HOUR <次数>
| MAX_UPDATES_PER_HOUR <次数>
| MAX_CONNECTIONS_PER_HOUR <次数>
| MAX_USER_CONNECTIONS <次数>

修改用户

  1. 重命名用户

  2. 修改用户密码

  • alter user
    • 语法：`alter user 'user_name'@'host_name' identified by '新密码'
  • mysqladmin
    • 语法(直接在shell执行)：mysqladmin -u<user_name> -p password 新密码
  • UPDATE
    • 语法：update user set authentication_string=password('新密码') where user='用户名' and host='主机名';
    • 注意，执行成功后还需要执行：flush privileges;，执行之后才会更新！

查看用户

  1. 有哪些用户
    直接查看 mysql 数据库下的 user 表即可。

  2. 查看用户权限

  • 语法：show grants for 'user_name'@'host_name';

3. mysql 中的数据类型

本节直接复制于Fun-rec中的对应内容。

数值类型

数值包含的类型如下：

  • 整型数据：TINYINT、INTEGER、SMALLINT、MEDIUMINT、DECIMAL 、NUMERIC 和BIGINT。

  • 浮点型数据：DECIMAL、FLOAT、REAL 和 DOUBLE PRECISION)。

其中，关键字INT是INTEGER的同义词，关键字DEC是的同义词。

不同关键字的主要区别就是表示的范围或精度不一样。具体如下表：

类型 大小 范围（有符号） 范围（无符号） 用途
TINYINT 1 Bytes (-128，127) (0，255) 小整数值
SMALLINT 2 Bytes (-32 768，32 767) (0，65 535) 大整数值
MEDIUMINT 3 Bytes (-8 388 608，8 388 607) (0，16 777 215) 大整数值
INT或INTEGER 4 Bytes (-2 147 483 648，2 147 483 647) (0，4 294 967 295) 大整数值
BIGINT 8 Bytes (-9,223,372,036,854,775,808，9 223 372 036 854 775 807) (0，18 446 744 073 709 551 615) 极大整数值
FLOAT 4 Bytes (-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38) 0，(1.175 494 351 E-38，3.402 823 466 E+38) 单精度 浮点数值
DOUBLE 8 Bytes (-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) 0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) 双精度 浮点数值
DECIMAL 对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2 依赖于M和D的值 依赖于M和D的值 小数值

日期和时间类型

表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。具体如下表：

类型 大小 ( bytes) 范围 格式 用途
DATE 3 1000-01-01/9999-12-31 YYYY-MM-DD 日期值
TIME 3 '-838:59:59'/'838:59:59' HH:MM:SS 时间值或持续时间
YEAR 1 1901/2155 YYYY 年份值
DATETIME 8 1000-01-01 00:00:00/9999-12-31 23:59:59 YYYY-MM-DD HH:MM:SS 混合日期和时间值
TIMESTAMP 4 1970-01-01 00:00:00/2038结束时间是第 2147483647 秒，北京时间 2038-1-19 11:14:07，格林尼治时间 2038年1月19日 凌晨 03:14:07 YYYYMMDD HHMMSS 混合日期和时间值，时间戳

字符串类型

字符串类型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET。具体如下表：

类型 大小 用途
CHAR 0-255 bytes 定长字符串
VARCHAR 0-65535 bytes 变长字符串
TINYBLOB 0-255 bytes 不超过 255 个字符的二进制字符串
TINYTEXT 0-255 bytes 短文本字符串
BLOB 0-65 535 bytes 二进制形式的长文本数据
TEXT 0-65 535 bytes 长文本数据
MEDIUMBLOB 0-16 777 215 bytes 二进制形式的中等长度文本数据
MEDIUMTEXT 0-16 777 215 bytes 中等长度文本数据
LONGBLOB 0-4 294 967 295 bytes 二进制形式的极大文本数据
LONGTEXT 0-4 294 967 295 bytes 极大文本数据

  • char声明的是定长字符串。若实际中字符串长度不足，则会在末尾使用空格进行填充至声明的长度。

  • varchar声明的是可变长字符串。存储过程中，只会按照字符串的实际长度来存储，但会多占用一位来存放实际字节的长度。

Reference

  1. MySQL 整体架构一览 ↩︎

  2. MYSQL架构理解 ↩︎

  3. https://www.zhihu.com/question/485523251 ↩︎

  4. 故障分析 | 正确使用 auth_socket 验证插件 ↩︎

  5. MySQL用户授权（GRANT） ↩︎

posted @ 2021-12-16 13:26  Milkha  阅读(23)  评论(0)  编辑  收藏  举报