Mysql基础

原创

你的阿万 2020-04-21 11:20:08 ©著作权

文章标签 Mysql 文章分类 运维

©著作权归作者所有：来自51CTO博客作者你的阿万的原创作品，请联系作者获取转载授权，否则将追究法律责任

1. 关系型数据库介绍

1.1 数据结构模型 数据结构模型主要有：

层次模型
网状结构
关系模型

关系模型：二维关系：row，column

数据库管理系统：DBMS DBMS: DataBase Manager system 关系：Relational，RDBMS RDBMS: Relational DataBase Manager system

1.2 RDBMS专业名词 常见的关系型数据库管理系统：

MySQL：MySQL，MariaDB，Percona-Server
PostgreSQL：简称为pgsql
Oracle
MSSQL

记录:数据库中表的每行是一条记录事务：多个操作被当作一个整体对待就称为一个事务要看一个关系型数据库是否支持事务，需要看其是否支持并满足ACID测试 ACID：ACID是事务的一个基本标准

A：Automicity，原子性
C：Consistency，一致性
I：Isolation，隔离性
D：Durability，持久性

ACID:了解详细说明， acid（数据库事务正确执行的四个基本要素的缩写） ACID，指数据库事务正确执行的四个基本要素的缩写。包含：原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）、持久性（Durability）。一个支持事务（Transaction）的数据库，必须要具有这四种特性，否则在事务过程（Transaction processing）当中无法保证数据的正确性，交易过程极可能达不到交易方的要求。

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

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

主键约束：一个或多个字段的组合，填入的数据必须能在本表中唯一标识本行。且必须提供数据，不能为空（NOT NULL）。
一个表只能存在一个
惟一键约束：一个或多个字段的组合，填入的数据必须能在本表中唯一标识本行。允许为空（NULL）
一个表可以存在多个
外键约束：一个表中的某字段可填入数据取决于另一个表的主键已有的数据
检查性约束索引：将表中的一个或多个字段中的数据复制一份另存，并且这些数据需要按特定次序排序存储

关系运算：

选择：挑选出符合条件的行（部分行）
投影：挑选出需要的字段
连接

数据抽象方式：

物理层：决定数据的存储格式，即RDBMS在磁盘上如何组织文件
逻辑层：描述DB存储什么数据，以及数据间存在什么样的关系
视图层：描述DB中的部分数据

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

//配置MySQLyum源:
[root@wan ~]# wget http://dev.mysql.com/get/mysql57-community-release-el7-10.noarch.rpm

[root@wan ~]# ls
  mysql57-community-release-el7-10.noarch.rpm
  过程略
 [root@wan ~]# yum -y install mysql-community-server mysql-community-c
lient mysql-community-common mysql-community-devel
过程略

2.2mysql的配置

//启动mysql
[root@wan ~]# systemctl start mysqld
[root@wan ~]# systemctl status mysqld
● mysqld.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
   Active: active (running) since 五 2020-04-03 23:18:12 CST; 4s ago
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
  Process: 9027 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS (code=exited, status=0/SUCCESS)
  Process: 8978 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
 Main PID: 9031 (mysqld)
   CGroup: /system.slice/mysqld.service
           └─9031 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid

4月 03 23:18:08 wan systemd[1]: Starting MySQL Server...
4月 03 23:18:12 wan systemd[1]: Started MySQL Server.
//确保3306端口已经监听起来
[root@wan ~]# ss -antl
State      Recv-Q Send-Q           Local Address:Port                          Peer Address:Port              
LISTEN     0      128                          *:111                                      *:*                  
LISTEN     0      128                          *:22                                       *:*                  
LISTEN     0      100                  127.0.0.1:25                                       *:*                  
LISTEN     0      25                           *:514                                      *:*                  
LISTEN     0      128                         :::111                                     :::*                  
LISTEN     0      128                         :::80                                      :::*                  
LISTEN     0      128                         :::22                                      :::*                  
LISTEN     0      100                        ::1:25                                      :::*                  
LISTEN     0      128                         :::443                                     :::*                  
LISTEN     0      25                          :::514                                     :::*                  
LISTEN     0      80                          :::3306                                    :::*                  
在日志文件中找出临时密码
[root@wan ~]# grep "password" /var/log/mysqld.log 
2020-04-03T15:18:09.827705Z 1 [Note] A temporary password is generated for root@localhost: qWLqSDwr)3sV      这个：后面就是临时密码
使用获取到的临时密码登录MySQL
[root@wan ~]# 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.29

Copyright (c) 2000, 2020, 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.00 sec)

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'abcd123!';
Query OK, 0 rows affected (0.00 sec)

mysql> 


为避免mysql自动升级，这里需要卸载最开始安装的yum源
[root@qwan ~]# rpm -qa|grep mysql

[root@wan ~]# yum -y remove mysql57-community-release-el7-10.noarch.rpm

3. mysql的程序组成

客户端
mysql：CLI交互式客户端程序
mysql_secure_installation：安全初始化，强烈建议安装完以后执行此命令
mysqldump：mysql备份工具
mysqladmin
服务器端
mysqld

3.1 mysql工具使用

语法：mysql [OPTIONS] [database]
常用的OPTIONS：
    -uUSERNAME      指定用户名，默认为root
    -hHOST          指定服务器主机，默认为localhost，推荐使用ip地址
    -pPASSWORD      指定用户的密码
    -P#             指定数据库监听的端口，这里的#需用实际的端口号代替，如-P3307
    -V              查看当前使用的mysql版本
    -e          不登录mysql执行sql语句后退出，常用于脚本

[root@ip-10-0-100-141 ~]# mysql -V
mysql  Ver 14.14 Distrib 5.7.25, for Linux (x86_64) using  EditLine wrapper


[root@ip-10-0-100-141 ~]# mysql -uroot -pwenhs5479! -h127.0.0.1
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 5
Server version: 5.7.25 MySQL Community Server (GPL)

Copyright (c) 2000, 2019, 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>


注意，不推荐直接在命令行里直接用-pPASSWORD的方式登录，而是使用-p选项，然后交互式输入密码
[root@ip-10-0-100-141 ~]# mysql -uroot -p -h 127.0.0.1 -e 'SHOW DATABASES;'
Enter password: 	----->密码不会回显
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

4.mysql数据库操作

4.1 DDL操作 4.1.1 数据库操作*

创建数据库
语法：CREATE DATABASE [IF NOT EXISTS] 'DB_NAME';
创建数据库wenhs
mysql> CREATE DATABASE IF NOT EXISTS wenhs;
Query OK, 1 row affected (0.00 sec)

查看当前有哪些数据库
mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| wenhs              |
+--------------------+
5 rows in set (0.00 sec)

删除数据库
语法：DROP DATABASE [IF EXISTS] 'DB_NAME';
删除数据库wenhs
mysql> DROP DATABASE IF EXISTS wenhs;
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='存储引擎类型';
在数据库wenhs里创建表jbgsn
mysql> CREATE DATABASE wenhs;
Query OK, 1 row affected (0.00 sec)

mysql> USE wenhs;		进入wenhs数据库
Database changed

mysql> CREATE TABLE jbgsn (id int NOT NULL,name VARCHAR(100) NOT NULL,age tinyint);
Query OK, 0 rows affected (0.02 sec)

查看当前数据库有哪些表
mysql> SHOW TABLES;
+-----------------+
| Tables_in_wenhs |
+-----------------+
| jbgsn           |
+-----------------+
1 row in set (0.00 sec)

删除表
语法：DROP TABLE [ IF EXISTS ] 'table_name';
删除表jbgsn
mysql> DROP TABLE jbgsn;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW TABLES;
Empty set (0.00 sec)

4.1.3 用户操作

mysql用户帐号由两部分组成，如’USERNAME’@‘HOST’，表示此USERNAME只能从此HOST上远程登录

这里（‘USERNAME’@‘HOST’）的HOST用于限制此用户可通过哪些主机远程连接mysql程序，其值可为：

IP地址，如：10.0.100.141 通配符

%：匹配任意长度的任意字符，常用于设置允许从任何主机登录 _：匹配任意单个字符

数据库用户创建语法：CREATE USER 'username'@'host' [IDENTIFIED BY 'password']; 创建数据库用户wenhs mysql> CREATE USER 'wenhs'@'127.0.0.1' IDENTIFIED BY 'wenhs5479!'; Query OK, 0 rows affected (0.00 sec)

MYSQL安装

//配置MySQLyum源:
[root@wan ~]# wget http://dev.mysql.com/get/mysql57-community-release-el7-10.noarch.rpm

[root@wan ~]# ls
  mysql57-community-release-el7-10.noarch.rpm
  过程略
 [root@wan ~]# yum -y install mysql-community-server mysql-community-c
lient mysql-community-common mysql-community-devel
过程略

2.2mysql的配置
//启动mysql
[root@wan ~]# systemctl start mysqld
[root@wan ~]# systemctl status mysqld
● mysqld.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
   Active: active (running) since 五 2020-04-03 23:18:12 CST; 4s ago
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
  Process: 9027 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS (code=exited, status=0/SUCCESS)
  Process: 8978 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
 Main PID: 9031 (mysqld)
   CGroup: /system.slice/mysqld.service
           └─9031 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid

4月 03 23:18:08 wan systemd[1]: Starting MySQL Server...
4月 03 23:18:12 wan systemd[1]: Started MySQL Server.
//确保3306端口已经监听起来
[root@wan ~]# ss -antl
State      Recv-Q Send-Q           Local Address:Port                          Peer Address:Port              
LISTEN     0      128                          *:111                                      *:*                  
LISTEN     0      128                          *:22                                       *:*                  
LISTEN     0      100                  127.0.0.1:25                                       *:*                  
LISTEN     0      25                           *:514                                      *:*                  
LISTEN     0      128                         :::111                                     :::*                  
LISTEN     0      128                         :::80                                      :::*                  
LISTEN     0      128                         :::22                                      :::*                  
LISTEN     0      100                        ::1:25                                      :::*                  
LISTEN     0      128                         :::443                                     :::*                  
LISTEN     0      25                          :::514                                     :::*                  
LISTEN     0      80                          :::3306                                    :::*                  
在日志文件中找出临时密码
[root@wan ~]# grep "password" /var/log/mysqld.log 
2020-04-03T15:18:09.827705Z 1 [Note] A temporary password is generated for root@localhost: qWLqSDwr)3sV      这个：后面就是临时密码
使用获取到的临时密码登录MySQL
[root@wan ~]# 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.29

Copyright (c) 2000, 2020, 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.00 sec)

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'abcd123!';
Query OK, 0 rows affected (0.00 sec)

mysql> 


为避免mysql自动升级，这里需要卸载最开始安装的yum源
[root@ip-10-0-100-141 ~]# rpm -qa|grep mysql
mysql-community-libs-5.7.25-1.el7.x86_64
mysql-community-libs-compat-5.7.25-1.el7.x86_64
mysql57-community-release-el7-10.noarch
mysql-community-client-5.7.25-1.el7.x86_64
mysql-community-devel-5.7.25-1.el7.x86_64
mysql-community-common-5.7.25-1.el7.x86_64
mysql-community-server-5.7.25-1.el7.x86_64
[root@ip-10-0-100-141 ~]# yum -y remove mysql57-community-release-el7-10.noarch
Loaded plugins: amazon-id, rhui-lb, search-disabled-repos
Resolving Dependencies
--> Running transaction check
---> Package mysql57-community-release.noarch 0:el7-10 will be erased
--> Finished Dependency Resolution
......
  Erasing    : mysql57-community-release-el7-10.noarch                             1/1 
  Verifying  : mysql57-community-release-el7-10.noarch                             1/1 

Removed:
  mysql57-community-release.noarch 0:el7-10                                            

Complete!