mysql基础

Aurora yhm

于 2022-07-22 19:02:04 发布

阅读量442

点赞数 1

文章标签：数据库 mysql

本文链接：https://blog.csdn.net/weixin_72898433/article/details/125938429

版权

mysql基础

文章目录

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，数据控制语言

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
[root@localhost ~]# ls
[root@localhost ~]# rpm -Uvh mysql57-community-release-el7-11.noarch.rpm

安装mysql5.7

[root@localhost ~]# yum -y install mysql-community-server mysql-community-client mysql-community-common mysql-community-devel --nogpgcheck
Last metadata expiration check: 0:33:04 ago on Fri 22 Jul 2022 04:46:49 PM CST.
All matches were filtered out by modular filtering for argument: mysql-community-server
All matches were filtered out by modular filtering for argument: mysql-community-client
All matches were filtered out by modular filtering for argument: mysql-community-common
All matches were filtered out by modular filtering for argument: mysql-community-devel
Error: Unable to find a match: mysql-community-server mysql-community

如果像上面一样报错，就先执行yum module disable mysql ,再执行相同的命令

[root@localhost ~]yum module disable mysql //禁用mysql模版 
[root@localhost ~]# yum -y install mysql-community-server mysql-community-clientmysql-community-common mysql-community-devel --nogpgcheck

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 pres>
   Active: active (running) since Fri 2022-07-22 17:36:37 CST; 15s ago
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
  Process: 34197 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mys>
  Process: 34143 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=>
 Main PID: 34199 (mysqld)
    Tasks: 27 (limit: 11070)
   Memory: 294.2M
   CGroup: /system.slice/mysqld.service
           └─34199 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysq>

Jul 22 17:36:33 localhost.localdomain systemd[1]: Starting MySQL Server...
Jul 22 17:36:37 localhost.localdomain systemd[1]: Started MySQL Server.

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

[root@localhost ~]# ss -anlt
LISTEN   0        80                     *:3306                *:*

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

[root@localhost ~]# grep "password" /var/log/mysqld.log 
2022-07-22T09:36:35.697673Z 1 [Note] A temporary password is generated for root@localhost: m_cj?0d:kH;f

使用获取到的临时密码登录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 2
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.

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.00 sec)

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

mysql> quit
Bye

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

[root@localhost ~]# mysql -uroot -pyhm123
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/
CentOS-Stream-HighAvailability.repo

yum 安装mairadb
yum安装mairadb软件包

[root@localhost ~]# yum -y install mariadb*

启动mariadb

[root@localhost ~]# systemctl start mariadb
[root@localhost ~]# systemctl enable mariadb

确保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:*

修改mysql登录密码

 [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('yhm123');
Query OK, 0 rows affected (0.001 sec)

MariaDB [(none)]> exit
Bye

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

[root@localhost ~]# mysql -uroot -pyhm123;
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
 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