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：有两种
由于sysql8.0以上的版本是要收费的所以我们要去官网安装我们需要的软件包
网站：Index of /232905/yum/mysql-5.7-community/el/7/x86_64
需要下载的五个软件包：
http://repo.mysql.com/yum/mysql-5.7-community/el/7/x86_64/mysql-community-server-5.7.43-1.el7.x86_64.rpm
http://repo.mysql.com/yum/mysql-5.7-community/el/7/x86_64/mysql-community-common-5.7.43-1.el7.x86_64.rpm
http://repo.mysql.com/yum/mysql-5.7-community/el/7/x86_64/mysql-community-client-5.7.43-1.el7.x86_64.rpm
http://repo.mysql.com/yum/mysql-5.7-community/el/7/x86_64/mysql-community-libs-5.7.43-1.el7.x86_64.rpm
http://repo.mysql.com/yum/mysql-5.7-community/el/7/x86_64/mysql-community-devel-5.7.43-1.el7.x86_64.rpm
用yum安装mysql：
#下载mysql5.7的软件包
[root@mysql ~]# wget http://repo.mysql.com/yum/mysql-5.7-community/el/7/x86_64/mysql-community-server-5.7.43-1.el7.x86_64.rpm http://repo.mysql.com/yum/mysql-5.7-community/el/7/x86_64/mysql-community-common-5.7.43-1.el7.x86_64.rpm http://repo.mysql.com/yum/mysql-5.7-community/el/7/x86_64/mysql-community-client-5.7.43-1.el7.x86_64.rpm http://repo.mysql.com/yum/mysql-5.7-community/el/7/x86_64/mysql-community-libs-5.7.43-1.el7.x86_64.rpm http://repo.mysql.com/yum/mysql-5.7-community/el/7/x86_64/mysql-community-devel-5.7.43-1.el7.x86_64.rpm
#下载成功
[root@mysql ~]# ls
anaconda-ks.cfg
mysql-community-client-5.7.43-1.el7.x86_64.rpm
mysql-community-common-5.7.43-1.el7.x86_64.rpm
mysql-community-devel-5.7.43-1.el7.x86_64.rpm
mysql-community-libs-5.7.43-1.el7.x86_64.rpm
mysql-community-server-5.7.43-1.el7.x86_64.rpm
#下载好了安装
[root@mysql ~]# yum -y localinstall *.rpm
2.2mysql配置
#安装好的mysql是没有设置开机自启的
[root@mysql ~]# systemctl enable --now mysqld #开启mysql服务并开机自启
[root@mysql ~]# systemctl status mysqld
● mysqld.service - MySQL Server
Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled>
Active: active (running) since Tue 2023-08-29 15:03:48 CST; 10m>
Docs: man:mysqld(8)
http://dev.mysql.com/doc/refman/en/using-systemd.html
Main PID: 13199 (mysqld)
Tasks: 27 (limit: 11301)
Memory: 284.3M
CGroup: /system.slice/mysqld.service
└─13199 /usr/sbin/mysqld --daemonize --pid-file=/var/ru>
Aug 29 15:03:45 mysql systemd[1]: Starting MySQL Server...
Aug 29 15:03:48 mysql systemd[1]: Started MySQL Server.
查看3306端口是否已经存在
[root@mysql ~]# ss -antl
State Recv-Q Send-Q Local Address:Port Peer Address:Port
LISTEN 0 128 0.0.0.0:22 0.0.0.0:*
LISTEN 0 80 *:3306 *:*
LISTEN 0 128 [::]:22 [::]:*
[root@mysql ~]#
查看mqspl的临时密码，并重新设置密码：
[root@mysql ~]# grep password /var/log/mysqld.log
2023-08-29T07:03:46.563007Z 1 [Note] A temporary password is generated for root@localhost: (8C<-ols+F.f
进入mysql里面设置密码：
[root@mysql ~]# mysql -uroot -p'(8C<-ols+F.f' #进入mqsql设置密码
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.43
Copyright (c) 2000, 2023, 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> #登录成功
#取消密码复杂的的设置并设置密码为123456
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('123456');
Query OK, 0 rows affected, 1 warning (0.00 sec)
#删除mysql57-community-release这个软件包避免他自动升级mysql的包
[root@mysql ~]# rpm -qa | grep mysql
mysql-community-libs-5.7.43-1.el7.x86_64
mysql-community-devel-5.7.43-1.el7.x86_64
mysql57-community-release-el7-10.noarch
mysql-community-common-5.7.43-1.el7.x86_64
mysql-community-server-5.7.43-1.el7.x86_64
mysql-community-client-5.7.43-1.el7.x86_64
[root@mysql ~]# rpm -e mysql57-community-release
[root@mysql ~]# rpm -qa | grep mysql
mysql-community-libs-5.7.43-1.el7.x86_64
mysql-community-devel-5.7.43-1.el7.x86_64
mysql-community-common-5.7.43-1.el7.x86_64
mysql-community-server-5.7.43-1.el7.x86_64
mysql-community-client-5.7.43-1.el7.x86_64
3. mysql的程序组成
- 客户端
- mysql：CLI交互式客户端程序
- mysql_secure_installation：安全初始化，强烈建议安装完以后执行此命令
- mysqldump：mysql备份工具
- mysqladmin
- 服务器端
- mysqld
#mysql初始化
[root@mysql ~]# mysql_secure_installation
Securing the MySQL server deployment.
Enter password for user root: #在这里虽然自己设置的密码或者临时密码
The 'validate_password' plugin is installed on the server.
The subsequent steps will run with the existing configuration
of the plugin.
Using existing password for root.
Estimated strength of the password: 50
Change the password for root ? ((Press y|Y for Yes, any other key for No) : n #这里是否修改root的密码我选择no
... skipping.
By default, a MySQL installation has an anonymous user,
allowing anyone to log into MySQL without having to have
a user account created for them. This is intended only for
testing, and to make the installation go a bit smoother.
You should remove them before moving into a production
environment.
Remove anonymous users? (Press y|Y for Yes, any other key for No) : y
Success. 是否删除匿名用户
Normally, root should only be allowed to connect from
'localhost'. This ensures that someone cannot guess at
the root password from the network.
Disallow root login remotely? (Press y|Y for Yes, any other key for No) : n
#是否禁止root用户远程登录
... skipping.
By default, MySQL comes with a database named 'test' that
anyone can access. This is also intended only for testing,
and should be removed before moving into a production
environment.
Remove test database and access to it? (Press y|Y for Yes, any other key for No) : y #是否删除测试数据库访问它？
- Dropping test database...
Success.
- Removing privileges on test database...
Success.
Reloading the privilege tables will ensure that all changes
made so far will take effect immediately.
Reload privilege tables now? (Press y|Y for Yes, any other key for No) : y
Success. #是否选择重新加载权限表吗
All done!
[root@mysql ~]#
3.1 mysql工具使用
//语法：mysql [OPTIONS] [database]
//常用的OPTIONS：
-uUSERNAME //指定用户名，默认为root
-hHOST //指定服务器主机，默认为localhost，推荐使用ip地址
-pPASSWORD //指定用户的密码
-P# //指定数据库监听的端口，这里的#需用实际的端口号代替，如-P3307
-V //查看当前使用的mysql版本
-e //不登录mysql执行sql语句后退出，常用于脚本
#演示不登录查看数据库
[root@mysql ~]# mysql -uroot -p123456 -e'show databases'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
#演示查看版本
[root@mysql ~]# mysql -V
mysql Ver 14.14 Distrib 5.7.43, for Linux (x86_64) using EditLine wrapper
查看mysql的本版
#-h演示：登录到mysql的服务端，登录前必须在客户端下载好mariadb和关闭防火墙，且在服务端的mysql里给需要登录的客户端进行复权，才可以登录：
#服务端：
[root@mysql ~]# ss -antl #查看端口
State Recv-Q Send-Q Local Address:Port Peer Address:Port
LISTEN 0 128 0.0.0.0:22 0.0.0.0:*
LISTEN 0 80 *:3306 *:*
LISTEN 0 128 [::]:22 [::]:*
[root@mysql ~]# mysql -uroot -p123456 #登录到MySQL
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 8
Server version: 5.7.43 MySQL Community Server (GPL)
Copyright (c) 2000, 2023, 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 'dxw'@'192.168.227.133' identified by '123456';
#对客户机进行授权允许dxw这个用户用123456的密码登录
Query OK, 0 rows affected, 1 warning (0.00 sec)
#授权后我们要在服务端在刷星权限
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
#客户端：
[root@kh2 ~]# yum -y install mariadb #安装mariadb
[root@kh2 ~]# systemctl stop firewalld #关闭防火墙
[root@kh2 ~]# mysql -udxw -p123456 -h192.168.227.134
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 5.7.43 MySQL Community Server (GPL)
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MySQL [(none)]> #登录成功
4. mysql数据库操作
4.1 DDL操作
4.1.1 数据库操作
#数据库的创建和删除和查看
1.创建数据库
//语法：CREATE DATABASE [IF NOT EXISTS] 'DB_NAME';
//创建数据库sjk
mysql> create database if not exists sjk; #创建一个名为sjk的数据库
Query OK, 1 row affected (0.04 sec) #if not exists 如果不存在就创建可以不加
mysql> show databases; #查看sjk数据库是否创建成功
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sjk |
| sys |
+--------------------+
5 rows in set (0.00 sec)
//删除数据库
//语法：DROP DATABASE [IF EXISTS] 'DB_NAME';
//删除数据库sjk
mysql> DROP DATABASE IF EXISTS sjk;
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
#删除成功！！！
4.1.2 表操作
//创建表
//语法：CREATE TABLE table_name (col1 datatype 修饰符,col2 datatype 修饰符) ENGINE='存储引擎类型';
//在数据库sjk里创建表yuangong
mysql> create database sjk; #创建sjk的数据库
Query OK, 1 row affected (0.00 sec)
mysql> show databases; #查看是否创建成功
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sjk |
| sys |
+--------------------+
5 rows in set (0.01 sec)
mysql> use sjk; #进入到sjk的数据库里面
Database changed
mysql> create table yuangong(id int not null,name varchar(20),age tinyint);
#创建一个名为yuangong的表
int #写入的数据必须是整数
not null #这个这段不能为空
varchar(20) #字母最大20个
tinyint #无符号值最大255
//查看当前数据库有哪些表
mysql> show tables;
+---------------+
| Tables_in_sjk |
+---------------+
| yuangong |
+---------------+
1 row in set (0.00 sec)
#查看表的结构
mysql> desc sjk.yuangong;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | NO | | NULL | |
| name | varchar(20) | YES | | NULL | |
| age | tinyint(4) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.04 sec)
//删除表
//语法：DROP TABLE [ IF EXISTS ] 'table_name';
//删除表yuangong
mysql> drop table yuangong;
Query OK, 0 rows affected (0.02 sec)
#查看表是否删除
mysql> SHOW TABLES;
Empty set (0.00 sec)
4.1.3 用户操作
mysql用户帐号由两部分组成，如’USERNAME’@‘HOST’，表示此USERNAME只能从此HOST上远程登录
这里（‘USERNAME’@‘HOST’）的HOST用于限制此用户可通过哪些主机远程连接mysql程序，其值可为：
- IP地址，如：172.16.12.129
- 通配符
- %：匹配任意长度的任意字符，常用于设置允许从任何主机登录
- _：匹配任意单个字符
#对所以227网段的所以主机都进行授权，并创建一个dxw的用户并设置密码为123456
mysql> grant all on *.* to 'dxw'@'192.168.227.%' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
#查看是否对277的所有网段都授权成功
MySQL [(none)]> show grants for dxw@'192.168.227.%';
+------------------------------------------------------+
| Grants for dxw@192.168.227.% |
+------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'dxw'@'192.168.227.%' |
+------------------------------------------------------+
1 row in set (0.000 sec)
#[root@kh2 ~]# mysql -udxw -p123456 -h192.168.227.134
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 15
Server version: 5.7.43 MySQL Community Server (GPL)
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MySQL [(none)]> #还是可以登录成功，但是一定要把之前的授权取消掉
4.3.3 取消授权REVOKE
//语法：REVOKE priv_type,... ON db_name.table_name FROM 'username'@'host';
mysql> revoke all on *.* from dxw@192.168.227.133; #取消dxw用户的远程登录
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges; #重新更新授权
Query OK, 0 rows affected (0.00 sec)
#查看是否删除
mysql> show grants for dxw@192.168.227.133;
+-----------------------------------------------+
| Grants for dxw@192.168.227.133 |
+-----------------------------------------------+
| GRANT USAGE ON *.* TO 'dxw'@'192.168.227.133' |
+-----------------------------------------------+
1 row in set (0.05 sec)
USAGE就是没有权限
注意：mysql服务进程启动时会读取mysql库中的所有授权表至内存中：
- GRANT或REVOKE等执行权限操作会保存于表中，mysql的服务进程会自动重读授权表，并更新至内存中
- 对于不能够或不能及时重读授权表的命令，可手动让mysql的服务进程重读授权表
mysql> FLUSH PRIVILEGES;
扫一扫
4万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
