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
- rpm：有两种
开干开干：
[root@localhost ~]# ls
公共 模板 视频 图片 文档 下载 音乐 桌面 anaconda-ks.cfg initial-setup-ks.cfg
[root@localhost ~]# rm -f *.cfg
[root@localhost ~]# ls
公共 模板 视频 图片 文档 下载 音乐 桌面
//基操
下载一个文件（Index of /232905 (mysql.com)）
[root@localhost ~]# wget https://repo.mysql.com/mysql57-community-release-el7-10.noarch.rpm
//下载一个mysql57-community-release-el7-10.noarch.rpm文件
[root@localhost ~]# ls
公共 模板 视频 图片 文档 下载 音乐 桌面 mysql57-community-release-el7-10.noarch.rpm
安装这个文件
[root@localhost ~]# rpm -ivh mysql57-community-release-el7-10.noarch.rpm
//安装mysql57-community-release-el7-10.noarch.rpm
下载5个小文件（Index of /232905/yum/mysql-5.7-community/el/7）
[root@localhost ~]# 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
[root@localhost ~]# 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
[root@localhost ~]# 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
[root@localhost ~]# 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
[root@localhost ~]# 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
[root@localhost ~]# ls
公共 文档 mysql57-community-release-el7-10.noarch.rpm mysql-community-libs-5.7.37-1.el7.x86_64.rpm
模板 下载 mysql-community-client-5.7.37-1.el7.x86_64.rpm mysql-community-server-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
安装
[root@localhost ~]# rm -f mysql57-community-release-el7-10.noarch.rpm
//因为有一个没用的rpm包，已经在第一步安装完成所以这里需要删掉
[root@localhost ~]# yum -y install *.rpm //安装/root目录下的所有.rpm结尾的包
关掉防火墙等一系列骚操作
[root@localhost ~]# systemctl disable --now firewalld.service
//关墙
Removed /etc/systemd/system/multi-user.target.wants/firewalld.service.
Removed /etc/systemd/system/dbus-org.fedoraproject.FirewallD1.service.
[root@localhost ~]# setenforce 0 //关setenforce
[root@localhost ~]# vim /etc/selinux//config //永久关
[root@localhost ~]# 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 //这一行改成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@localhost ~]# systemctl status mysqld
● mysqld.service - MySQL Server
Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
Active: inactive (dead)
Docs: man:mysqld(8)
http://dev.mysql.com/doc/refman/en/using-systemd.html
[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 Tue 2022-04-19 13:22:57 CST; 4s ago
Docs: man:mysqld(8)
http://dev.mysql.com/doc/refman/en/using-systemd.html
Process: 82741 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS (code=exited, status>
Process: 82485 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
Main PID: 82743 (mysqld)
Tasks: 27 (limit: 23329)
Memory: 335.9M
CGroup: /system.slice/mysqld.service
└─82743 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid
4月 19 13:22:52 localhost.localdomain systemd[1]: Starting MySQL Server...
4月 19 13:22:57 localhost.localdomain systemd[1]: Started MySQL Server.
看端口
[root@localhost ~]# ss -antl
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 32 192.168.122.1:53 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 80 *:3306 *:*
LISTEN 0 128 [::]:111 [::]:*
LISTEN 0 128 [::]:22 [::]:*
LISTEN 0 5 [::1]:631 [::]:*
//出现3306
进去搞临时密码改成永久的密码：
[root@localhost ~]# grep "password" /var/log/mysqld.log
2022-04-18T09:09:26.148970Z 1 [Note] A temporary password is generated for root@localhost: sehOgNLwq3.G
//看临时密码是多少（一般在结尾sehOgNLwq3.G）
[root@localhost ~]# mysql -uroot -p'sehOgNLwq3.G' //注意-p和密码之间不能有空格
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> set password = password('Qwer1234!'); //密码需要数字 大写字母 小写字母和符号（注意：密码带‘’是为了区分特殊字符，带（）是为了加密传输防止网络嗅探到密码）
Query OK, 0 rows affected, 1 warning (0.00 sec)
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> set password = password('Qwer1234!'); //密码更改
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> quit //退出去验证密码是否更改成功
Bye
[root@localhost ~]# mysql -uroot -pQwer1234!
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> quit //退出
删除安装好的mysql57-community-release，否则会升级很麻烦
[root@localhost ~]# rpm -e mysql57-community-release
进去创建个表格看看，用于测试
[root@localhost ~]# mysql -uroot -pQwer1234!
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.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> GRANT ALL ON *.* TO 'root'@'192.168.142.1' IDENTIFIED BY 'Qwer1234!';
//这句话的意思是放行'root'@'192.168.142.1'的连接操作
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> create database test; //创建一个test的数据库
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test |
+--------------------+
5 rows in set (0.00 sec)
mysql> quit
Bye
试试远程连接（Navicat）
[root@localhost ~]# ip a
1: lo: mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000
link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
inet 127.0.0.1/8 scope host lo
valid_lft forever preferred_lft forever
inet6 ::1/128 scope host
valid_lft forever preferred_lft forever
2: ens160: mtu 1500 qdisc mq state UP group default qlen 1000
link/ether 00:0c:29:31:ff:61 brd ff:ff:ff:ff:ff:ff
inet 192.168.142.132/24 brd 192.168.142.255 scope global dynamic noprefixroute ens160
valid_lft 1765sec preferred_lft 1765sec
inet6 fe80::20c:29ff:fe31:ff61/64 scope link noprefixroute
valid_lft forever preferred_lft forever
3: virbr0: mtu 1500 qdisc noqueue state DOWN group default qlen 1000
link/ether 52:54:00:94:ba:1c brd ff:ff:ff:ff:ff:ff
inet 192.168.122.1/24 brd 192.168.122.255 scope global virbr0
valid_lft forever preferred_lft forever
//看看本机IP是192.168.142.132