MySQL基础
- 关系型数据库介绍
1.1 数据结构模型
1.2 RDBMS专业名词
1.3 关系型数据库的常见组件
1.4 SQL语句 - mysql安装与配置
2.1 mysql安装
2.2 mysql配置
2.3 yum 安装mairadb
1.关系型数据库介绍
1.1 数据结构模型
数据结构模型主要有:
- 层次模型
- 网状结构
- 关系模型
关系模型:
二维关系:row,column
数据库管理系统:DBMS(DataBase Management Server )
关系:Relational,RDBMS (Relationship DBMS 关系型数据库管理系统)
关系型数据库:硬盘
mysql 开源软件 5.7 (5.7及5.7之前一直是开源的)商业软件 8.0
Oracle 商业软件
mariadb 开源软件 (从10.0版本开始)
sqlserver
MSSql MicroSoft sql
nosql(非关系型数据库): 数据是存放在内存上的 变量 key=value
redis 缓存数据库
memcache
mongodb
pgsql
sqlite 数据是存放在”文件”(数据文件)中 sql语句 cat vim
字段:每一列就是一个字段
记录:每一行就是一个记录
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源
[root@localhost ~]# wget http://dev.mysql.com/get/mysql57-community-release-el7-11.noarch.rpm
--2022-07-24 07:47:03-- http://dev.mysql.com/get/mysql57-community-release-el7-11.noarch.rpm
Resolving dev.mysql.com (dev.mysql.com)... 23.2.135.207, 2600:140b:2:8b6::2e31, 2600:140b:2:8ac::2e31
Connecting to dev.mysql.com (dev.mysql.com)|23.2.135.207|:80... connected.
HTTP request sent, awaiting response... 301 Moved Permanently
Location: https://dev.mysql.com/get/mysql57-community-release-el7-11.noarch.rpm [following]
--2022-07-24 07:47:03-- https://dev.mysql.com/get/mysql57-community-release-el7-11.noarch.rpm
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-11.noarch.rpm [following]
--2022-07-24 07:47:04-- https://repo.mysql.com//mysql57-community-release-el7-11.noarch.rpm
Resolving repo.mysql.com (repo.mysql.com)... 23.1.245.43
Connecting to repo.mysql.com (repo.mysql.com)|23.1.245.43|: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-release-el7-11 100%[=======================================================>] 25.08K 128KB/s in 0.2s
2022-07-24 07:47:10 (128 KB/s) - ‘mysql57-community-release-el7-11.noarch.rpm’ saved [25680/25680]
[root@localhost ~]# ls
anaconda-ks.cfg mysql57-community-release-el7-11.noarch.rpm
[root@localhost ~]# ls /etc/yum.repos.d/
abc.repo
[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 ~]# ls /etc/yum.repos.d/
abc.repo mysql-community-source.repo mysql-community.repo
//安装mysql5.7
[root@localhost ~]yum module disable mysql //禁用mysql模版
yum module disable mysql
CentOS Stream 8 - AppStream 5.2 kB/s | 4.4 kB 00:00
CentOS Stream 8 - AppStream 75 kB/s | 24 MB 05:24
CentOS Stream 8 - BaseOS 7.1 kB/s | 3.9 kB 00:00
CentOS Stream 8 - BaseOS 5.9 MB/s | 25 MB 00:04
CentOS Stream 8 - Extras 5.2 kB/s | 2.9 kB 00:00
MySQL Connectors Community 43 kB/s | 49 kB 00:01
MySQL Tools Community 252 kB/s | 651 kB 00:02
MySQL 5.7 Community Server 811 kB/s | 2.6 MB 00:03
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-clientmysql-community-common mysql-community-devel --nogpgcheck
Last metadata expiration check: 0:06:15 ago on Sun 24 Jul 2022 09:33:51 AM CST.
Package mysql-community-server-5.7.38-1.el7.x86_64 is already installed.
Package mysql-community-client-5.7.38-1.el7.x86_64 is already installed.
Package mysql-community-common-5.7.38-1.el7.x86_64 is already installed.
Dependencies resolved.
......
Complete!
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 preset: disabled)
Active: active (running) since Sun 2022-07-24 09:43:32 CST; 16s ago
Docs: man:mysqld(8)
http://dev.mysql.com/doc/refman/en/using-systemd.html
Process: 13382 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS (code=exited, status=>
Process: 13333 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
Main PID: 13385 (mysqld)
Tasks: 27 (limit: 11201)
Memory: 309.9M
CGroup: /system.slice/mysqld.service
└─13385 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid
Jul 24 09:43:28 localhost.localdomain systemd[1]: Starting MySQL Server...
Jul 24 09:43:32 localhost.localdomain systemd[1]: Started MySQL Server.
//确保3306端口已经监听起来
ss -antl
[root@localhost ~]# ss -antlp
State Recv-Q Send-Q Local Address:Port Peer Address:Port Process
LISTEN 0 128 0.0.0.0:22 0.0.0.0:* users:(("sshd",pid=978,fd=5))
LISTEN 0 80 *:3306 *:* users:(("mysqld",pid=13385,fd=17))
LISTEN 0 128 [::]:22 [::]:* users:(("sshd",pid=978,fd
// 在日志文件中找出临时密码
grep “password” /var/log/mysqld.log
[root@localhost ~]# grep "password" /var/log/mysqld.log
2022-07-23T01:43:29.594394Z 1 [Note] A temporary password is generated for root@localhost: kpCewyTyo8/o
//使用获取到的临时密码登录mysql
[root@localhost ~]# mysql -uroot -p
Enter password: //此处输入密码,可以直接复制你的密码粘贴至此处,也可手动输入
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.38
Copyright (c) 2000, 2022, Oracle and/or its affiliates. All rights reserved.
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 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.01 sec)
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'wyn123!';
Query OK, 0 rows affected (0.00 sec)
mysql> quit
Bye
//检验新密码能否成功登入
[root@localhost ~]# mysql -uroot -pwyn123
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.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
//为避免mysql自动升级,这里需要卸载最开始安装的yum源
rpm -e mysql57-community-release
[root@localhost ~]# ls /etc/yum.repos.d/
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/
abc.repo
2.3 yum 安装mairadb
//yum安装mairadb软件包
root@localhost ~]# yum -y install mariadb*
Repository AppStream is listed more than once in the configuration
Last metadata expiration check: 2:37:32 ago on Sun 24 Jul 2022 07:49:53 AM CST.
Dependencies resolved.
====================================================================================================================
Package Arch Version Repository Size
=======================================================================================================
......
Complete!
//启动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.
//确保3306端口已经开启
[root@localhost ~]# ss -antl
State Recv-Q Send-Q Local Address:Port Peer Address:Port Process LISTEN 0 80 0.0.0.0:3306 0.0.0.0:*
//修改mariadb登录密码
[root@localhost ~]# mysql -uroot -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 8 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('wyn123');
Query OK, 0 rows affected (0.001 sec)
MariaDB [(none)]> exit
Bye
//检验可以用新密码登录成功
[root@localhost ~]# mysql -uroot -pwyn123
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 9
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