devbox
酷酷是懒虫
这个屌丝很懒，什么也没留下！
关注作者
热门标签
热门文章
当前位置:   article > 正文

##1. 关系型数据库介绍 ##1.1 数据结构模型 数据库： 关系型数据库：存储在硬盘 mysql 开源软件 oracle 商业软件 mariadb 开源软件 10.0版本 sqlserver MS_关系型数据库数据存放的位置在

作者：酷酷是懒虫 | 2024-07-27 03:50:34

赞

踩

关系型数据库数据存放的位置在

#mysql基础

文章目录


##1. 关系型数据库介绍
##1.1 数据结构模型
数据库：
关系型数据库：存储在硬盘
mysql 开源软件
oracle 商业软件
mariadb 开源软件 10.0版本
sqlserver
MSSql MicroSoft sql
nosql(非关系型数据库): 数据是存放在内存上的 变量 key=value
redis 缓存数据库
memcache
mongodb
pgsql
sqlite 数据是存放在“文件”(数据文件)中 sql语句

数据结构模型主要有：
层次模型
网状结构
关系模型
字段：每一列就是一个字段
记录：每一行就是一个记录
DBMS ：数据库管理系统
关系模型：
二维关系：row，column
数据库管理系统：DBMS
关系：Relational，RDBMS
##1.2 RDBMS专业名词
###常见的关系型数据库管理系统：
MySQL：MySQL，MariaDB，Percona­Server
PostgreSQL：简称为pgsql
racle
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，数据控制语言
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-bsuq30q8-1658665908571)(./Snipaste_2022-07-24_19-09-10.png)]
##2. mysql安装与配置
###2.1 mysql安装
mysql安装方式有三种：
源代码：编译安装
二进制格式的程序包：展开至特定路径，并经过简单配置后即可使用
程序包管理器管理的程序包：
rpm：有两种
OS Vendor：操作系统发行商提供的
项目官方提供的
deb
配置mysql的yum源

[root@localhost ~]#  wget http://dev.mysql.com/get/mysql57-community-release-el7-11.noarch.rpm
URL transformed to HTTPS due to an HSTS policy
--2022-07-24 07:12:04--  https://dev.mysql.com/get/mysql57-community-release-el7-11.noarch.rpm
Resolving dev.mysql.com (dev.mysql.com)... 23.41.76.226, 2600:140e:6:7a7::2e31, 2600:140e:6:79d::2e31
Connecting to dev.mysql.com (dev.mysql.com)|23.41.76.226|:443... connected.
HTTP request sent, awaiting response... 302 Moved Temporarily
Location: https://repo.mysql.com//mysql57-community-release-el7-11.noarch.rpm [following]
--2022-07-24 07:12:08--  https://repo.mysql.com//mysql57-community-release-el7-11.noarch.rpm
Resolving repo.mysql.com (repo.mysql.com)... 96.17.188.242
Connecting to repo.mysql.com (repo.mysql.com)|96.17.188.242|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: 25680 (25K) [application/x-redhat-package-manager]
Saving to: ‘mysql57-community-release-el7-11.noarch.rpm’

mysql57-community-re 100%[=====================>]  25.08K  67.1KB/s    in 0.4s    

2022-07-24 07:12:09 (67.1 KB/s) - ‘mysql57-community-release-el7-11.noarch.rpm’ saved [25680/25680]

[root@localhost ~]# ls
anaconda-ks.cfg         httpd-2.4.54.tar.bz2
apr-1.6.5               initial-setup-ks.cfg
apr-1.6.5.tar.bz2       Music
apr-util-1.6.1          mysql57-community-release-el7-11.noarch.rpm
apr-util-1.6.1.tar.bz2  Pictures
Desktop                 Public
Documents               Templates
Downloads               Videos
httpd-2.4.54            youxi
[root@localhost ~]# rpm -Uvh mysql57-community-release-el7-11.noarch.rpm
warning: mysql57-community-release-el7-11.noarch.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Verifying...                          ################################# [100%]
Preparing...                          ################################# [100%]
Updating / installing...
   1:mysql57-community-release-el7-11 ################################# [100%]
[root@localhost ~]# 

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35

安装mysql5.7
[root@localhost ~]# yum ­y install mysql­community­server mysql­community­client
mysql­community­common mysql­community­devel –nogpgcheck

[root@localhost ~]# yum module disable mysql
Last metadata expiration check: 0:00:53 ago on Sun 24 Jul 2022 07:30:08 AM EDT.
Dependencies resolved.
===================================================================================
 Package            Architecture      Version             Repository          Size
===================================================================================
Disabling modules:
 mysql                                                                            

Transaction Summary
===================================================================================

Is this ok [y/N]: y
Complete!
[root@localhost ~]# yum -y install mysql-community-server mysql-community-client mysql-community-common mysql-community-devel  --nogpgcheck
Last metadata expiration check: 0:01:11 ago on Sun 24 Jul 2022 07:30:08 AM EDT.
Dependencies resolved.
===================================================================================
 Package                  Arch     Version               Repository           Size
===================================================================================
Installing:
 mysql-community-client   x86_64   5.7.38-1.el7          mysql57-community    28 M
 mysql-community-common   x86_64   5.7.38-1.el7          mysql57-community   311 k
 mysql-community-devel    x86_64   5.7.38-1.el7          mysql57-community   4.2 M
 mysql-community-server   x86_64   5.7.38-1.el7          mysql57-community   178 M
Installing dependencies:
 mysql-community-libs     x86_64   5.7.38-1.el7          mysql57-community   2.6 M
 ncurses-compat-libs      x86_64   6.1-7.20180224.el8    baseos              331 k

Transaction Summary
===================================================================================
Install  6 Packages

Total download size: 213 M
Installed size: 915 M
Downloading Packages:
(1/6): ncurses-compat-libs-6.1-7.20180224.el8.x86_ 1.3 MB/s | 331 kB     00:00    
(2/6): mysql-community-common-5.7.38-1.el7.x86_64. 140 kB/s | 311 kB     00:02    
(3/6): mysql-community-libs-5.7.38-1.el7.x86_64.rp 1.2 MB/s | 2.6 MB     00:02    
(4/6): mysql-community-devel-5.7.38-1.el7.x86_64.r 457 kB/s | 4.2 MB     00:09    
(5/6): mysql-community-client-5.7.38-1.el7.x86_64. 470 kB/s |  28 MB     01:00    
(6/6): mysql-community-server-5.7.38-1.el7.x86_64. 1.5 MB/s | 178 MB     01:59    
-----------------------------------------------------------------------------------
Total                                              1.7 MB/s | 213 MB     02:07     
Running transaction check
Transaction check succeeded.
Running transaction test
Transaction test succeeded.
Running transaction
  Preparing        :                                                           1/1 
  Installing       : mysql-community-common-5.7.38-1.el7.x86_64                1/6 
  Installing       : mysql-community-libs-5.7.38-1.el7.x86_64                  2/6 
  Running scriptlet: mysql-community-libs-5.7.38-1.el7.x86_64                  2/6 
  Installing       : ncurses-compat-libs-6.1-7.20180224.el8.x86_64             3/6 
  Installing       : mysql-community-client-5.7.38-1.el7.x86_64                4/6 
  Running scriptlet: mysql-community-server-5.7.38-1.el7.x86_64                5/6 
  Installing       : mysql-community-server-5.7.38-1.el7.x86_64                5/6 
  Running scriptlet: mysql-community-server-5.7.38-1.el7.x86_64                5/6 
  Installing       : mysql-community-devel-5.7.38-1.el7.x86_64                 6/6 
  Running scriptlet: mysql-community-devel-5.7.38-1.el7.x86_64                 6/6 
[/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.

  Verifying        : ncurses-compat-libs-6.1-7.20180224.el8.x86_64             1/6 
  Verifying        : mysql-community-client-5.7.38-1.el7.x86_64                2/6 
  Verifying        : mysql-community-common-5.7.38-1.el7.x86_64                3/6 
  Verifying        : mysql-community-devel-5.7.38-1.el7.x86_64                 4/6 
  Verifying        : mysql-community-libs-5.7.38-1.el7.x86_64                  5/6 
  Verifying        : mysql-community-server-5.7.38-1.el7.x86_64                6/6 
Installed products updated.

Installed:
  mysql-community-client-5.7.38-1.el7.x86_64                                       
  mysql-community-common-5.7.38-1.el7.x86_64                                       
  mysql-community-devel-5.7.38-1.el7.x86_64                                        
  mysql-community-libs-5.7.38-1.el7.x86_64                                         
  mysql-community-server-5.7.38-1.el7.x86_64                                       
  ncurses-compat-libs-6.1-7.20180224.el8.x86_64                                    

Complete!
[root@localhost ~]# 

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44
  • 45
  • 46
  • 47
  • 48
  • 49
  • 50
  • 51
  • 52
  • 53
  • 54
  • 55
  • 56
  • 57
  • 58
  • 59
  • 60
  • 61
  • 62
  • 63
  • 64
  • 65
  • 66
  • 67
  • 68
  • 69
  • 70
  • 71
  • 72
  • 73
  • 74
  • 75
  • 76
  • 77
  • 78
  • 79
  • 80

##2.2 mysql配置
启动mysql并设置开机自动启动
systemctl enable –now mysqld
systemctl status mysqld

[root@localhost ~]# systemctl enable --now mysqld
[root@localhost ~]#  systemctl status mysqld
● mysqld.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset:>
   Active: active (running) since Sun 2022-07-24 07:37:37 EDT; 8s ago
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
  Process: 9073 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/>
  Process: 9019 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SU>
 Main PID: 9075 (mysqld)
    Tasks: 27 (limit: 11070)
   Memory: 291.3M
   CGroup: /system.slice/mysqld.service
           └─9075 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.p>

Jul 24 07:37:32 localhost.localdomain systemd[1]: Starting MySQL Server...
Jul 24 07:37:37 localhost.localdomain systemd[1]: Started MySQL Server.

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17

确保3306端口已经监听起来
ss ­antl

[root@localhost ~]# ss -anlt
State    Recv-Q   Send-Q     Local Address:Port      Peer Address:Port   Process   
LISTEN   0        128              0.0.0.0:111            0.0.0.0:*                
LISTEN   0        128              0.0.0.0:22             0.0.0.0:*                
LISTEN   0        5              127.0.0.1:631            0.0.0.0:*                
LISTEN   0        128            127.0.0.1:6010           0.0.0.0:*                
LISTEN   0        80                     *:3306                 *:*                
LISTEN   0        128                 [::]:111               [::]:*                
LISTEN   0        128                 [::]:22                [::]:*                
LISTEN   0        5                  [::1]:631               [::]:*                
LISTEN   0        128                [::1]:6010              [::]:*              

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11

在日志文件中找出临时密码
grep “password” /var/log/mysqld.log

[root@localhost ~]# grep "password" /var/log/mysqld.log
2022-07-24T11:37:35.239636Z 1 [Note] A temporary password is generated for root@localhost: Y;Lrys(_l9P>

  • 1
  • 2

使用获取到的临时密码登录mysql
mysql ­uroot ­p

[root@localhost ~]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.38

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.


  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14

修改mysql登录密码

mysql> set global validate_password_policy=0;
Query OK, 0 rows affected (0.00 sec)

mysql> set global validate_password_length=1;
Query OK, 0 rows affected (0.00 sec)

mysql> alter user 'root'@'localhost' identified by 'leg9229';
Query OK, 0 rows affected (0.00 sec)

mysql> quit
Bye

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11

检验可以用新密码登录成功

[root@localhost ~]# mysql -uroot -pleg9229
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 4
Server version: 5.7.38 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> exit
Bye


  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16

为避免mysql自动升级，这里需要卸载最开始安装的yum源
rpm ­e mysql57­community­release

[root@localhost ~]# ls /etc/yum.repos.d/
CentOS-Stream-AppStream.repo         CentOS-Stream-Media.repo
CentOS-Stream-BaseOS.repo            CentOS-Stream-PowerTools.repo
CentOS-Stream-Debuginfo.repo         CentOS-Stream-RealTime.repo
CentOS-Stream-Extras.repo            mysql-community.repo
CentOS-Stream-HighAvailability.repo  mysql-community-source.repo
[root@localhost ~]# rpm -e mysql57-community-release
[root@localhost ~]# ls /etc/yum.repos.d/
CentOS-Stream-AppStream.repo  CentOS-Stream-HighAvailability.repo
CentOS-Stream-BaseOS.repo     CentOS-Stream-Media.repo
CentOS-Stream-Debuginfo.repo  CentOS-Stream-PowerTools.repo
CentOS-Stream-Extras.repo     CentOS-Stream-RealTime.repo

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12

yum 安装mairadb
yum安装mairadb软件包

[root@localhost ~]# yum -y install mariadb*
CentOS Stream 8 - AppStream                          4.8 kB/s | 4.4 kB     00:00    
CentOS Stream 8 - BaseOS                             5.0 kB/s | 3.9 kB     00:00    
CentOS Stream 8 - Extras                             3.3 kB/s | 2.9 kB     00:00    
Dependencies resolved.
=====================================================================================
 Package              Arch   Version                                 Repo       Size
=====================================================================================
Installing:
 mariadb              x86_64 3:10.3.28-1.module_el8.3.0+757+d382997d appstream 6.0 M
 mariadb-backup       x86_64 3:10.3.28-1.module_el8.3.0+757+d382997d appstream 6.1 M
 mariadb-common       x86_64 3:10.3.28-1.module_el8.3.0+757+d382997d appstream  64 k
 mariadb-connector-c  x86_64 3.1.11-2.el8_3                          appstream 200 k
 mariadb-connector-c-config
                      noarch 3.1.11-2.el8_3                          appstream  15 k
 mariadb-connector-c-devel
                      x86_64 3.1.11-2.el8_3                          appstream  68 k

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17

启动mariadb

[root@localhost ~]# systemctl start mariadb
[root@localhost ~]# systemctl enable mariadb
Created symlink /etc/systemd/system/mysql.service → /usr/lib/systemd/system/mariadb.service.
Created symlink /etc/systemd/system/mysqld.service → /usr/lib/systemd/system/mariadb.service.
Created symlink /etc/systemd/system/multi-user.target.wants/mariadb.service → /usr/lib/systemd/system/mariadb.service.

  • 1
  • 2
  • 3
  • 4
  • 5

确保3306端口已经监听起来

[root@localhost ~]# ss -anlt
State    Recv-Q   Send-Q      Local Address:Port       Peer Address:Port   Process   
LISTEN   0        80                0.0.0.0:3306            0.0.0.0:*                
LISTEN   0        128               0.0.0.0:111             0.0.0.0:*                
LISTEN   0        128               0.0.0.0:22              0.0.0.0:*                
LISTEN   0        128                  [::]:111                [::]:*                
LISTEN   0        128                  [::]:22                 [::]:*             

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7

修改mysql登录密码

[root@localhost ~]# mysql -uroot -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 10
Server version: 10.3.28-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> set password = password('leg9229');
Query OK, 0 rows affected (0.000 sec)

MariaDB [(none)]> exit
Bye

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15

检验可以用新密码登录成功

[root@localhost ~]# mysql -uroot -pleg9229;
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 11
Server version: 10.3.28-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> exit
Bye

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/酷酷是懒虫/article/detail/888507
推荐阅读
相关标签

Copyright © 2003-2013 www.wpsshop.cn 版权所有，并保留所有权利。

  

闽ICP备14008679号