1. 关系型数据库介绍
1.1 数据结构模型
数据结构模型主要有：
- 层次模型
- 网状结构
- 关系模型
关系模型：
二维关系：row，column
数据库管理系统：DBMS
关系：Relational，RDBMS
1.2 RDBMS专业名词
常见的关系型数据库管理系统：
- MySQL：MySQL，MariaDB，Percona-Server
- PostgreSQL：简称为pgsql
- Oracle
- MSSQL
SQL：Structure Query Language，结构化查询语言
约束：constraint，向数据表提供的数据要遵守的限制
- 主键约束：一个或多个字段的组合，填入的数据必须能在本表中唯一标识本行。且必须提供数据，不能为空（NOT NULL）。
- 一个表只能存在一个
- 惟一键约束：一个或多个字段的组合，填入的数据必须能在本表中唯一标识本行。允许为空（NULL）
- 一个表可以存在多个
- 外键约束：一个表中的某字段可填入数据取决于另一个表的主键已有的数据
- 检查性约束
索引：将表中的一个或多个字段中的数据复制一份另存，并且这些数据需要按特定次序排序存储
1.3 关系型数据库的常见组件
关系型数据库的常见组件有：
- 数据库：database
- 表：table，由行（row）和列（column）组成
- 索引：index
- 视图：view
- 用户：user
- 权限：privilege
- 存储过程：procedure
- 存储函数：function
- 触发器：trigger
- 事件调度器：event scheduler
1.4 SQL语句
SQL语句有三种类型：
- DDL：Data Defination Language，数据定义语言
- DML：Data Manipulation Language，数据操纵语言
- DCL：Data Control Language，数据控制语言
| SQL语句类型 | 对应操作 |
|---|---|
| DDL | CREATE：创建 DROP：删除 ALTER：修改 |
| DML | INSERT：向表中插入数据 DELETE：删除表中数据 UPDATE：更新表中数据 SELECT：查询表中数据 |
| DCL | GRANT：授权 REVOKE：移除授权 |
2. mysql安装与配置
2.1 mysql安装
mysql安装方式有三种：
- 源代码：编译安装
- 二进制格式的程序包：展开至特定路径，并经过简单配置后即可使用
- 程序包管理器管理的程序包：
- rpm：有两种
- OS Vendor：操作系统发行商提供的
- 项目官方提供的
- deb
- rpm：有两种
[root@rhel1 ~]# rm -rf *
[root@rhel1 ~]# ls
[root@rhel1 ~]#
//下载一个文件（Index of /232905 (mysql.com)）
[root@rhel1 ~]# wget https://repo.mysql.com/mysql57-community-release-el7-10.noarch.rpm
--2022-04-18 06:36:19-- https://repo.mysql.com/mysql57-community-release-el7-10.noarch.rpm
正在解析主机 repo.mysql.com (repo.mysql.com)... 23.76.232.185
正在连接 repo.mysql.com (repo.mysql.com)|23.76.232.185|:443... 已连接。
已发出 HTTP 请求，正在等待回应... 200 OK
长度：25548 (25K) [application/x-redhat-package-manager]
正在保存至: “mysql57-community-release-el7-10.noarch.rpm”
mysql57-community-rel 100%[=======================>] 24.95K --.-KB/s 用时 0.05s
2022-04-18 06:36:19 (531 KB/s) - 已保存 “mysql57-community-release-el7-10.noarch.rpm” [25548/25548])
[root@rhel1 ~]# ls
mysql57-community-release-el7-10.noarch.rpm
//安装文件
[root@rhel1 ~]# rpm -ivh mysql57-community-release-el7-10.noarch.rpm
警告：mysql57-community-release-el7-10.noarch.rpm: 头V3 DSA/SHA1 Signature, 密钥 ID 5072e1f5: NOKEY
Verifying... ################################# [100%]
准备中... ################################# [100%]
正在升级/安装...
1:mysql57-community-release-el7-10 ################################# [100%]
//依次下载5个文件
[root@rhel1 ~]# rpm -ivh mysql57-community-release-el7-10.noarch.rpm
警告：mysql57-community-release-el7-10.noarch.rpm: 头V3 DSA/SHA1 Signature, 密钥 ID 5072e1f5: NOKEY
Verifying... ################################# [100%]
准备中... ################################# [100%]
正在升级/安装...
1:mysql57-community-release-el7-10 ################################# [100%]
[root@rhel1 ~]# wget http://repo.mysql.com/yum/mysql-5.7-community/el/7/x86_64/mysql-community-client-5.7.37-1.el7.x86_64.rpm
--2022-04-18 06:39:55-- http://repo.mysql.com/yum/mysql-5.7-community/el/7/x86_64/mysql-community-client-5.7.37-1.el7.x86_64.rpm
正在解析主机 repo.mysql.com (repo.mysql.com)... 23.194.211.12
正在连接 repo.mysql.com (repo.mysql.com)|23.194.211.12|:80... 已连接。
已发出 HTTP 请求，正在等待回应... 200 OK
长度：26670472 (25M) [application/x-redhat-package-manager]
正在保存至: “mysql-community-client-5.7.37-1.el7.x86_64.rpm”
mysql-community-clien 100%[=======================>] 25.43M 6.57MB/s 用时 4.9s
2022-04-18 06:40:00 (5.15 MB/s) - 已保存 “mysql-community-client-5.7.37-1.el7.x86_64.rpm” [26670472/26670472])
[root@rhel1 ~]# wget http://repo.mysql.com/yum/mysql-5.7-community/el/7/x86_64/mysql-community-common-5.7.37-1.el7.x86_64.rpm
--2022-04-18 06:40:19-- http://repo.mysql.com/yum/mysql-5.7-community/el/7/x86_64/mysql-community-common-5.7.37-1.el7.x86_64.rpm
正在解析主机 repo.mysql.com (repo.mysql.com)... 23.194.211.12
正在连接 repo.mysql.com (repo.mysql.com)|23.194.211.12|:80... 已连接。
已发出 HTTP 请求，正在等待回应... 200 OK
长度：318884 (311K) [application/x-redhat-package-manager]
正在保存至: “mysql-community-common-5.7.37-1.el7.x86_64.rpm”
mysql-community-commo 100%[=======================>] 311.41K 853KB/s 用时 0.4s
2022-04-18 06:40:19 (853 KB/s) - 已保存 “mysql-community-common-5.7.37-1.el7.x86_64.rpm” [318884/318884])
[root@rhel1 ~]# wget http://repo.mysql.com/yum/mysql-5.7-community/el/7/x86_64/mysql-community-devel-5.7.37-1.el7.x86_64.rpm
--2022-04-18 06:40:26-- http://repo.mysql.com/yum/mysql-5.7-community/el/7/x86_64/mysql-community-devel-5.7.37-1.el7.x86_64.rpm
正在解析主机 repo.mysql.com (repo.mysql.com)... 23.76.232.185
正在连接 repo.mysql.com (repo.mysql.com)|23.76.232.185|:80... 已连接。
已发出 HTTP 请求，正在等待回应... 200 OK
长度：4122424 (3.9M) [application/x-redhat-package-manager]
正在保存至: “mysql-community-devel-5.7.37-1.el7.x86_64.rpm”
mysql-community-devel 100%[=======================>] 3.93M 4.87MB/s 用时 0.8s
2022-04-18 06:40:27 (4.87 MB/s) - 已保存 “mysql-community-devel-5.7.37-1.el7.x86_64.rpm” [4122424/4122424])
[root@rhel1 ~]# wget http://repo.mysql.com/yum/mysql-5.7-community/el/7/x86_64/mysql-community-libs-5.7.37-1.el7.x86_64.rpm
--2022-04-18 06:40:34-- http://repo.mysql.com/yum/mysql-5.7-community/el/7/x86_64/mysql-community-libs-5.7.37-1.el7.x86_64.rpm
正在解析主机 repo.mysql.com (repo.mysql.com)... 104.83.196.238
正在连接 repo.mysql.com (repo.mysql.com)|104.83.196.238|:80... 已连接。
已发出 HTTP 请求，正在等待回应... 200 OK
长度：2474780 (2.4M) [application/x-redhat-package-manager]
正在保存至: “mysql-community-libs-5.7.37-1.el7.x86_64.rpm”
mysql-community-libs- 100%[=======================>] 2.36M 2.28MB/s 用时 1.0s
2022-04-18 06:40:36 (2.28 MB/s) - 已保存 “mysql-community-libs-5.7.37-1.el7.x86_64.rpm” [2474780/2474780])
[root@rhel1 ~]# wget http://repo.mysql.com/yum/mysql-5.7-community/el/7/x86_64/mysql-community-server-5.7.37-1.el7.x86_64.rpm
--2022-04-18 06:40:45-- http://repo.mysql.com/yum/mysql-5.7-community/el/7/x86_64/mysql-community-server-5.7.37-1.el7.x86_64.rpm
正在解析主机 repo.mysql.com (repo.mysql.com)... 23.194.211.12
正在连接 repo.mysql.com (repo.mysql.com)|23.194.211.12|:80... 已连接。
已发出 HTTP 请求，正在等待回应... 200 OK
长度：182274360 (174M) [application/x-redhat-package-manager]
正在保存至: “mysql-community-server-5.7.37-1.el7.x86_64.rpm”
mysql-community-serve 100%[=======================>] 173.83M 6.55MB/s 用时 26s
2022-04-18 06:41:12 (6.59 MB/s) - 已保存 “mysql-community-server-5.7.37-1.el7.x86_64.rpm” [182274360/182274360])
[root@rhel1 ~]# ls
mysql57-community-release-el7-10.noarch.rpm
mysql-community-client-5.7.37-1.el7.x86_64.rpm
mysql-community-common-5.7.37-1.el7.x86_64.rpm
mysql-community-devel-5.7.37-1.el7.x86_64.rpm
mysql-community-libs-5.7.37-1.el7.x86_64.rpm
mysql-community-server-5.7.37-1.el7.x86_64.rpm
[root@rhel1 ~]#
//安装上面我们下载的rpm包
[root@rhel1 ~]# rm -f mysql57-community-release-el7-10.noarch.rpm
[root@rhel1 ~]# yum -y install *.rpm
MySQL Connectors Community 256 kB/s | 133 kB 00:00
MySQL Tools Community 944 kB/s | 832 kB 00:00
MySQL 5.7 Community Server 1.7 MB/s | 2.5 MB 00:01
上次元数据过期检查：0:00:01 前，执行于 2022年04月18日 星期一 06时45分21秒。
依赖关系解决。
======================================================================================
软件包 架构 版本 仓库 大小
======================================================================================
安装:
mysql-community-client x86_64 5.7.37-1.el7 @commandline 25 M
mysql-community-common x86_64 5.7.37-1.el7 @commandline 311 k
mysql-community-devel x86_64 5.7.37-1.el7 @commandline 3.9 M
mysql-community-libs x86_64 5.7.37-1.el7 @commandline 2.4 M
mysql-community-server x86_64 5.7.37-1.el7 @commandline 174 M
安装依赖关系:
ncurses-compat-libs x86_64 6.1-9.20180224.el8 baseos 328 k
net-tools x86_64 2.0-0.52.20160912git.el8 baseos 322 k
事务概要
======================================================================================
安装 7 软件包
总计：206 M
总下载：650 k
安装大小：904 M
下载软件包：
(1/2): ncurses-compat-libs-6.1-9.20180224.el8.x86_64. 849 kB/s | 328 kB 00:00
(2/2): net-tools-2.0-0.52.20160912git.el8.x86_64.rpm 827 kB/s | 322 kB 00:00
--------------------------------------------------------------------------------------
总计 679 kB/s | 650 kB 00:00
运行事务检查
事务检查成功。
运行事务测试
事务测试成功。
运行事务
准备中 : 1/1
安装 : mysql-community-common-5.7.37-1.el7.x86_64 1/7
安装 : mysql-community-libs-5.7.37-1.el7.x86_64 2/7
运行脚本: mysql-community-libs-5.7.37-1.el7.x86_64 2/7
安装 : net-tools-2.0-0.52.20160912git.el8.x86_64 3/7
运行脚本: net-tools-2.0-0.52.20160912git.el8.x86_64 3/7
安装 : ncurses-compat-libs-6.1-9.20180224.el8.x86_64 4/7
安装 : mysql-community-client-5.7.37-1.el7.x86_64 5/7
运行脚本: mysql-community-server-5.7.37-1.el7.x86_64 6/7
安装 : mysql-community-server-5.7.37-1.el7.x86_64 6/7
运行脚本: mysql-community-server-5.7.37-1.el7.x86_64 6/7
安装 : mysql-community-devel-5.7.37-1.el7.x86_64 7/7
运行脚本: mysql-community-devel-5.7.37-1.el7.x86_64 7/7
[/usr/lib/tmpfiles.d/mysql.conf:23] Line references path below legacy directory /var/run/, updating /var/run/mysqld → /run/mysqld; please update the tmpfiles.d/ drop-in file accordingly.
验证 : ncurses-compat-libs-6.1-9.20180224.el8.x86_64 1/7
验证 : net-tools-2.0-0.52.20160912git.el8.x86_64 2/7
验证 : mysql-community-client-5.7.37-1.el7.x86_64 3/7
验证 : mysql-community-common-5.7.37-1.el7.x86_64 4/7
验证 : mysql-community-devel-5.7.37-1.el7.x86_64 5/7
验证 : mysql-community-libs-5.7.37-1.el7.x86_64 6/7
验证 : mysql-community-server-5.7.37-1.el7.x86_64 7/7
已安装:
mysql-community-client-5.7.37-1.el7.x86_64
mysql-community-common-5.7.37-1.el7.x86_64
mysql-community-devel-5.7.37-1.el7.x86_64
mysql-community-libs-5.7.37-1.el7.x86_64
mysql-community-server-5.7.37-1.el7.x86_64
ncurses-compat-libs-6.1-9.20180224.el8.x86_64
net-tools-2.0-0.52.20160912git.el8.x86_64
完毕！
关闭防火墙以后面连接
[root@rhel1 ~]# systemctl disable --now firewalld.service
[root@rhel1 ~]# setenforce 0
[root@rhel1 ~]# vim /etc/selinux/config
[root@rhel1 ~]# cat /etc/selinux/config
# This file controls the state of SELinux on the system.
# SELINUX= can take one of these three values:
# enforcing - SELinux security policy is enforced.
# permissive - SELinux prints warnings instead of enforcing.
# disabled - No SELinux policy is loaded.
SELINUX=disabled
# SELINUXTYPE= can take one of these three values:
# targeted - Targeted processes are protected,
# minimum - Modification of targeted policy. Only selected processes are protected.
# mls - Multi Level Security protection.
SELINUXTYPE=targeted
[root@rhel1 ~]# systemctl enable --now mysqld //设置开机自启
[root@rhel1 ~]# systemctl status mysqld.service
● mysqld.service - MySQL Server
Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: di>
Active: active (running) since Mon 2022-04-18 06:56:23 EDT; 2min 7s ago
Docs: man:mysqld(8)
http://dev.mysql.com/doc/refman/en/using-systemd.html
Process: 2656 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mys>
Process: 2607 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCE>
Main PID: 2659 (mysqld)
Tasks: 27 (limit: 11174)
Memory: 290.3M
CGroup: /system.slice/mysqld.service
└─2659 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid
4月 18 06:56:20 rhel1 systemd[1]: Starting MySQL Server...
4月 18 06:56:23 rhel1 systemd[1]: Started MySQL Server.
[root@rhel1 ~]# ss -antl //查看3306端口已经开启（3306端口是mysql的默认端口）
State Recv-Q Send-Q Local Address:Port Peer Address:Port Process
LISTEN 0 128 0.0.0.0:22 0.0.0.0:*
LISTEN 0 128 [::]:22 [::]:*
LISTEN 0 80 *:3306 *:*
LISTEN 0 128 *:80 *:*
//查看临时密码
[root@rhel1 ~]# grep "password" /var/log/mysqld.log
2022-04-18T10:56:21.437788Z 1 [Note] A temporary password is generated for root@localhost: ybOyOAl6Q0!%
[root@rhel1 ~]# mysql -uroot -p'ybOyOAl6Q0!%'
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.37
Copyright (c) 2000, 2022, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> //成功进入mysql
mysql> set password = password('Wg1234!'); //修改密码
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> set global validate_password_policy=0; //设置密码的复杂性设为最低
Query OK, 0 rows affected (0.00 sec)
mysql> set global validate_password_length=1; //设置密码的长度最小为1
Query OK, 0 rows affected (0.01 sec)
mysql> set password = password('Wg1234!');
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> quit //退出验证密码是否修改成功
Bye
[root@rhel1 ~]#
[root@rhel1 ~]# mysql -uroot -pWangGang01!
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.37 MySQL Community Server (GPL)
Copyright (c) 2000, 2022, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> //成功进入mysql
//为避免mysql自动升级，这里需要卸载最开始安装的yum源
rpm -e mysql57-community-release
3. mysql的程序组成
- 客户端
- mysql：CLI交互式客户端程序
- mysql_secure_installation：安全初始化，强烈建议安装完以后执行此命令
- mysqldump：mysql备份工具
- mysqladmin
- 服务器端
- mysqld
[root@rhel1 ~]# mysql -uroot -pWangGang01!
mysql: [Warning] Using a password on the command line interface can be insecure.Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4Server version: 5.7.37 MySQL Community Server (GPL)
Copyright (c) 2000, 2022, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> GRANT ALL ON *.* TO 'root'@'192.168.78.136' IDENTIFIED BY 'Wg1234！';Query OK, 0 rows affected, 1 warning (0.01 sec)
//授权root用户已192.168.78.136的连接
mysql> create database game; //创建一个game的数据
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| game |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.01 sec)
mysql> quitBye
连接测试
连接成功
浙公网安备 33010602011771号