• 博客园logo
  • 会员
  • 众包
  • 新闻
  • 博问
  • 闪存
  • 赞助商
  • HarmonyOS
  • Chat2DB
    • 搜索
      所有博客
    • 搜索
      当前博客
  • 写随笔 我的博客 短消息 简洁模式
    用户头像
    我的博客 我的园子 账号设置 会员中心 简洁模式 ... 退出登录
    注册 登录
悬溺
博客园    首页    新随笔    联系   管理    订阅  订阅
mysql基础

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
[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

连接测试

连接成功

posted on 2022-04-18 18:08  悬溺·  阅读(179)  评论(0)    收藏  举报
刷新页面返回顶部
博客园  ©  2004-2025
浙公网安备 33010602011771号 浙ICP备2021040463号-3