mysql基础

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

#配置mysql的yum源
wget http://dev.mysql.com/get/mysql57-community-release-el7-10.noarch.rpm
URL transformed to HTTPS due to an HSTS policy
--2022-07-25 13:26:26--  https://dev.mysql.com/get/mysql57-community-release-el7-10.noarch.rpm
Resolving dev.mysql.com (dev.mysql.com)... 23.2.135.207, 2600:140b:2:18e::2e31, 2600:140b:2:1a7::2e31
Connecting to dev.mysql.com (dev.mysql.com)|23.2.135.207|:443... connected.
HTTP request sent, awaiting response... 302 Moved Temporarily
Location: https://repo.mysql.com//mysql57-community-release-el7-10.noarch.rpm [following]
--2022-07-25 13:26:27--  https://repo.mysql.com//mysql57-community-release-el7-10.noarch.rpm
Resolving repo.mysql.com (repo.mysql.com)... 184.30.152.230
Connecting to repo.mysql.com (repo.mysql.com)|184.30.152.230|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: 25548 (25K) [application/x-redhat-package-manager]
Saving to: ‘mysql57-community-release-el7-10.noarch.rpm’

mysql57-community-r 100%[===================>]  24.95K  --.-KB/s    in 0s

2022-07-25 13:26:28 (101 MB/s) - ‘mysql57-community-release-el7-10.noarch.rpm’ saved [25548/25548]

[root@localhost ~]# ls
anaconda-ks.cfg   apr-util-1.6.1.tar.gz
apr-1.7.0         httpd-2.4.54
apr-1.7.0.tar.gz  httpd-2.4.54.tar.gz
apr-util-1.6.1    mysql57-community-release-el7-10.noarch.rpm


#安装mysql5.7
[root@localhost ~]# rpm -Uvh mysql57-community-release-el7-10.noarch.rpm
warning: mysql57-community-release-el7-10.noarch.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Verifying...                          ################################# [100%]
Preparing...                          ################################# [100%]
Updating / installing...
   1:mysql57-community-release-el7-10 ################################# [10

//禁用mysql

[root@localhost ~]# yum module disable mysql
MySQL Connectors Community                       26 kB/s |  49 kB     00:01
MySQL Tools Community                           183 kB/s | 651 kB     00:03
MySQL 5.7 Community Server                      172 kB/s | 2.6 MB     00:15
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:00:38 ago on Mon 25 Jul 2022 01:28:07 PM CST.
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-9.20180224.el8       baseos            328 k
 net-tools              x86_64 2.0-0.52.20160912git.el8 baseos            322 k

略......

2.2 mysql配置

#启动mysql并设置开机自动启动
[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 pres>
   Active: active (running) since Mon 2022-07-25 13:31:13 CST; 6s ago
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
  Process: 54353 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mys>
  Process: 54304 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=>
 Main PID: 54356 (mysqld)
    Tasks: 27 (limit: 49262)
   Memory: 301.7M
   CGroup: /system.slice/mysqld.service
           └─54356 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysq>

Jul 25 13:31:11 localhost.localdomain systemd[1]: Starting MySQL Server...
Jul 25 13:31:13 localhost.localdomain systemd[1]: Started MySQL Server.

#确保3306端口已经监听起来
[root@localhost ~]# ss -antl
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                *:*

#在日志文件中找出临时密码
[root@localhost ~]# grep "password" /var/log/mysqld.log
2022-07-25T05:31:11.960829Z 1 [Note] A temporary password is generated for root@localhost: l0edhV9ow5&?

#使用获取到的临时密码登录mysql
[root@localhost ~]