Mysql数据库基础

最新推荐文章于 2024-06-13 17:33:35 发布

时羽天

最新推荐文章于 2024-06-13 17:33:35 发布

阅读量398

点赞数

文章标签：数据库 mysql sql mariadb

本文链接：https://blog.csdn.net/mw5258/article/details/125938193

版权

文章目录

关系型数据库

数据结构

数据结构模型主要有：

层次模型
网状结构
关系模型
面向对象模型

关系模型
二维关系：row(行)，column(列)

数据库管理系统：DBMS
关系：Relational，RDBMS

RDBMS专业名词

常见的关系型数据库管理系统：

MySQL
Oracle
Mariadb
SQL Server
MS SQL
PostgreSQL

SQL：Structure Query Language，结构化查询语言

约束：constraint，向数据表提供的数据要遵守的限制

主键约束：一个或多个字段的组合，填入的数据必须能在本表中唯一标识本行。且必须提供数据，不能为空（NOT NULL）。
一个表只能存在一个
唯一约束：唯一约束用于限制不受主键约束的列上数据的唯一性，与主键约束不同的是，唯一约束可以为空值（NULL）。
- 一个表可以存在多个唯一约束
外键约束：一个表中的某字段可填入数据取决于另一个表的主键已有的数据
检查性约束

索引：将表中的一个或多个字段中的数据复制一份另存，并且这些数据需要按特定次序排序存储。它可以帮助用户快速找到对应的内容。

关系型数据库的常见组件

数据库：database
表：table，由行（row）和列（column）组成
索引：index
视图：view
用户：user
权限：privilege
存储过程：procedure
存储函数：function
触发器：trigger
事件调度器：event scheduler

SQL语句

DDL：Data Defination Language，数据定义语言
DML：Data Manipulation Language，数据操纵语言
DCL：Data Control Language，数据控制语言
DQL：Data Query Language，数据查询语言

SQL语句类型	对应操作
DDL	CREATE：创建 DROP：删除 ALTER：修改
DML	INSERT：向表中插入数据 DELETE：删除表中数据 UPDATE：更新表中数据 SELECT：查询表中数据
DCL	GRANT：授权 REVOKE：移除授权
DQL	SELECT：查询 FROM：什么表 WHERE：查找限制条件

Mysql安装和配置

YUM源安装Mysql57

下载mysql57的yum仓库包进行安装

[root@139 ~]# wget http://dev.mysql.com/get/mysql57-community-release-el7-11.noarch.rpm
--2022-07-22 18:04:36--  http://dev.mysql.com/get/mysql57-community-release-el7-11.noarch.rpm
Resolving dev.mysql.com (dev.mysql.com)... 223.119.203.77, 2600:140e:6:a94::2e31, 2600:140e:6:aaf::2e31
Connecting to dev.mysql.com (dev.mysql.com)|223.119.203.77|: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-22 18:04:36--  https://dev.mysql.com/get/mysql57-community-release-el7-11.noarch.rpm
Connecting to dev.mysql.com (dev.mysql.com)|223.119.203.77|: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-22 18:04:37--  https://repo.mysql.com//mysql57-community-release-el7-11.noarch.rpm
Resolving repo.mysql.com (repo.mysql.com)... 104.91.72.230
Connecting to repo.mysql.com (repo.mysql.com)|104.91.72.230|: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.noarch.rpm          100%[===================================================================================================================>]  25.08K   157KB/s    in 0.2s

2022-07-22 18:04:40 (157 KB/s) - ‘mysql57-community-release-el7-11.noarch.rpm’ saved [25680/25680]

[root@139 ~]# ls
anaconda-ks.cfg  mysql57-community-release-el7-11.noarch.rpm
[root@139 ~]# 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@139 ~]# ls /etc/yum.repos.d/
111.repo  mysql-community.repo  mysql-community-source.repo

[root@139 ~]# yum -y install mysql-community-server mysql-community-client mysql-community-common mysql-community-devel --nogpgchek

mysql配置

启动mysql并设置开机自启
[root@139 ~]# systemctl enable --now mysqld
[root@139 ~]# systemctl status mysqld
Active: active (running) 
查看端口3306是否监听
[root@139 ~]# ss -antl
LISTEN                   0                        80                                                     *:3306                                                *:*
查看日志文件里的密码，登录mysql
[root@139 ~]# grep password /var/log/mysqld.log
2022-07-22T10:23:19.123433Z 1 [Note] A temporary password is generated for root@localhost: Oo<2WjDTsIZ:

[root@139 ~]# 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 '123456';
Query OK, 0 rows affected (0.00 sec)

mysql> quit
Bye

[root@139 ~]# mysql -uroot -p123456
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>
修改成功

mariadb安装和配置

yum安装mariadb数据库

[root@100 ~]# yum -y install mariadb*
[root@100 ~]# systemctl enable --now 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.
[root@100 ~]# systemctl status mariadb
 Active: active (running)
 
 设置密码
[root@100 ~]# mysql
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('123456');
Query OK, 0 rows affected (0.000 sec)

MariaDB [(none)]> quit
Bye

测试登录
[root@100 ~]# mysql -uroot -p123456
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)]>