MYSQL基础

koreyoshiiiii

已于 2022-02-17 15:31:21 修改

阅读量173

点赞数

文章标签： mysql 数据库

于 2021-08-24 02:16:44 首次发布

本文链接：https://blog.csdn.net/a512153770/article/details/119881381

版权

MYSQL基础

MySQL概述
1、什么是数据库？
　数据的仓库，如：在ATM的示例中我们创建了一个 db 目录，称其为数据库

2、什么是 MySQL、Oracle、SQLite、Access、MS SQL Server等？
　他们均是一个软件，都有两个主要的功能：

a. 将数据保存到文件或内存
b. 接收特定的命令，然后对文件进行相应的操作

3、什么是SQL ？
　MySQL等软件可以接受命令，并做出相应的操作，由于命令中可以包含删除文件、获取文件内容等众多操作，对于编写的命令就是是SQL语句。

RDBMS专业名词

常见的关系型数据库管理系统：
mysql 5.7 免费/ mariadb 10.0
mysql 8.0
MySQL：MySQL，MariaDB，Percona-Server
PostgreSQL：简称pgsql
Oracle 收费
mongodb
sqlite
MSSQL MicroSoft 图形化数据库

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

索引：将表中的一个或多个字段中的数据复制一份另存，并且这些数据需要按特定次序排序存储

关系型数据库的常见组件

关系型数据库的常见组件有：

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

mysql安装

mysql安装方式有三种：

源代码：编译安装
二进制格式的程序包：展开至特定路径，并经过简单配置后即可使用
程序包管理器管理的程序包：
源代码安装

yum -y install mariadb mariadb-server

mysql备份工具mysqldump

//语法：
    mysqldump [OPTIONS] database [tables ...]
    mysqldump [OPTIONS] --all-databases [OPTIONS]
    mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
    
//常用的OPTIONS：
    -uUSERNAME      //指定数据库用户名
    -hHOST          //指定服务器主机，请使用ip地址
    -pPASSWORD      //指定数据库用户的密码
    -P#             //指定数据库监听的端口，这里的#需用实际的端口号代替，如-P3307

//备份整个数据库(全备)
MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| mkf                |
+--------------------+
4 rows in set (0.001 sec)
MariaDB [(none)]> use mkf;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
MariaDB [mkf]> show tables;
+---------------+
| Tables_in_mkf |
+---------------+
| student       |
| mk            |
+---------------+
2 rows in set (0.000 sec)
[root@localhost ~]# ls
公共  视频  文档  音乐  anaconda-ks.cfg
模板  图片  下载  桌面  initial-setup-ks.cfg
[root@localhost ~]# mysqldump -uroot -p1 --all-databases > all-$(date '+%Y%m%d').sql
[root@localhost ~]# ls
公共  视频  文档  音乐  all-20210826.sql  initial-setup-ks.cfg
模板  图片  下载  桌面  anaconda-ks.cfg

//备份mkf库的student表和mk表
[root@localhost ~]# mysqldump -uroot -p1 mkf student mk > tables-$(date '+%Y%m%d').sql
[root@localhost ~]# ls
公共  文档  all-20210826.sql      tables-20210826.sql
模板  下载  all-.sql
视频  音乐  anaconda-ks.cfg
图片  桌面  initial-setup-ks.cfg

//备份mkf库
[root@localhost ~]# mysqldump -uroot -p1 --databases mkf > mkf-$(date '+%Y%m%d').sql

//模拟误删wangqingge数据库
MariaDB [(none)]> drop database mkf;
Query OK, 2 rows affected (0.005 sec)

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
3 rows in set (0.000 sec)

差异备份与恢复

mysql差异备份
开启MySQL服务器的二进制日志功能

root@localhost ~]# vim /etc/my.cnf 
[mysqld]
basedir = /usr/local/mysql
datadir = /opt/data
socket = /tmp/mysql.sock
port = 3306
user = mysql
pid-file = /tmp/mysql.pid
skip-name-resolve

server-id = 10         //设置服务器标识符
log-bin = mysql_bin    //开启二进制日志功能

[root@localhost ~]# systemctl restart mysqld.service

对数据库进行完全备份

//完全备份
[root@localhost ~]# mysqldump -uroot -proot --single-transaction --flush-logs --master-data=2 --all-databases --delete-master-logs > all-20210826.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# ls
公共  视频  文档  音乐  all-20210826.sql  initial-setup-ks.cfg
模板  图片  下载  桌面  anaconda-ks.cfg   pass

//增加新内容
mysql> select * from student;
+----+-------------+------+
| id | name        | age  |
+----+-------------+------+
|  1 | tom         |   20 |
|  2 | jerry       |   23 |
|  3 | wangqing    |   25 |
|  4 | sean        |   28 |
|  5 | zhangshan   |   26 |
|  6 | zhangshan   |   20 |
|  7 | lisi        |   50 |
|  8 | chenshuo    |   10 |
|  9 | wangwu      |    3 |
| 10 | qiuyi       |   15 |
| 11 | qiuxiaotian |   20 |
+----+-------------+------+
11 rows in set (0.00 sec)

mysql> insert student(name,age) values('xiaozhou',18);
Query OK, 1 row affected (0.00 sec)

mysql> select * from student;
+----+-------------+------+
| id | name        | age  |
+----+-------------+------+
|  1 | tom         |   20 |
|  2 | jerry       |   23 |
|  3 | wangqing    |   25 |
|  4 | sean        |   28 |
|  5 | zhangshan   |   26 |
|  6 | zhangshan   |   20 |
|  7 | lisi        |   50 |
|  8 | chenshuo    |   10 |
|  9 | wangwu      |    3 |
| 10 | qiuyi       |   15 |
| 11 | qiuxiaotian |   20 |
| 12 | xiaozhou    |   18 |
+----+-------------+------+
12 rows in set (0.00 sec)

//修改内容
mysql> select * from student;
+----+-------------+------+
| id | name        | age  |
+----+-------------+------+
|  1 | tom         |   20 |
|  2 | jerry       |   23 |
|  3 | wangqing    |   25 |
|  4 | sean        |   28 |
|  5 | zhangshan   |   26 |
|  6 | zhangshan   |   20 |
|  7 | lisi        |   50 |
|  8 | chenshuo    |   10 |
|  9 | wangwu      |    3 |
| 10 | qiuyi       |   15 |
| 11 | qiuxiaotian |   20 |
| 12 | xiaozhou    |   18 |
+----+-------------+------+
12 rows in set (0.00 sec)

mysql> update student set age = 17 where id = 9;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from student;
+----+-------------+------+
| id | name        | age  |
+----+-------------+------+
|  1 | tom         |   20 |
|  2 | jerry       |   23 |
|  3 | wangqing    |   25 |
|  4 | sean        |   28 |
|  5 | zhangshan   |   26 |
|  6 | zhangshan   |   20 |
|  7 | lisi        |   50 |
|  8 | chenshuo    |   10 |
|  9 | wangwu      |   17 |
| 10 | qiuyi       |   15 |
| 11 | qiuxiaotian |   20 |
| 12 | xiaozhou    |   18 |
+----+-------------+------+
12 rows in set (0.00 sec)

mysql差异备份恢复

模拟误删数据

mysql> drop database mkf;
Query OK, 2 rows affected (0.01 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

刷新创建新的二进制日志

[root@localhost ~]# ll /opt/data/
总用量 122980
-rw-r-----. 1 mysql mysql       56 8月  26 14:44 auto.cnf
-rw-------. 1 mysql mysql     1680 8月  26 14:44 ca-key.pem
-rw-r--r--. 1 mysql mysql     1112 8月  26 14:44 ca.pem
-rw-r--r--. 1 mysql mysql     1112 8月  26 14:44 client-cert.pem
-rw-------. 1 mysql mysql     1676 8月  26 14:44 client-key.pem
-rw-r-----. 1 mysql mysql      301 8月  26 14:57 ib_buffer_pool
-rw-r-----. 1 mysql mysql 12582912 8月  26 15:36 ibdata1
-rw-r-----. 1 mysql mysql 50331648 8月  26 15:36 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 8月  26 14:44 ib_logfile1
-rw-r-----. 1 mysql mysql 12582912 8月  26 15:26 ibtmp1
-rw-r-----. 1 mysql mysql    19289 8月  26 15:26 localhost.localdomain.err
drwxr-x---. 2 mysql mysql     4096 8月  26 14:44 mysql
-rw-r-----. 1 mysql mysql      860 8月  26 15:35 mysql_bin.000002
-rw-r-----. 1 mysql mysql       19 8月  26 15:26 mysql_bin.index
-rw-r-----. 1 mysql mysql        6 8月  26 14:57 mysql.pid
drwxr-x---. 2 mysql mysql     8192 8月  26 14:44 performance_schema
-rw-------. 1 mysql mysql     1680 8月  26 14:44 private_key.pem
-rw-r--r--. 1 mysql mysql      452 8月  26 14:44 public_key.pem
-rw-r--r--. 1 mysql mysql     1112 8月  26 14:44 server-cert.pem
-rw-------. 1 mysql mysql     1680 8月  26 14:44 server-key.pem
drwxr-x---. 2 mysql mysql     8192 8月  26 14:44 sys

//刷新创建新的二进制日志
[root@localhost ~]# mysqladmin -uroot -pmkf flush-logs
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# ll /opt/data/
总用量 122984
-rw-r-----. 1 mysql mysql       56 8月  26 14:44 auto.cnf
-rw-------. 1 mysql mysql     1680 8月  26 14:44 ca-key.pem
-rw-r--r--. 1 mysql mysql     1112 8月  26 14:44 ca.pem
-rw-r--r--. 1 mysql mysql     1112 8月  26 14:44 client-cert.pem
-rw-------. 1 mysql mysql     1676 8月  26 14:44 client-key.pem
-rw-r-----. 1 mysql mysql      301 8月  26 14:57 ib_buffer_pool
-rw-r-----. 1 mysql mysql 12582912 8月  26 15:36 ibdata1
-rw-r-----. 1 mysql mysql 50331648 8月  26 15:36 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 8月  26 14:44 ib_logfile1
-rw-r-----. 1 mysql mysql 12582912 8月  26 15:26 ibtmp1
-rw-r-----. 1 mysql mysql    19289 8月  26 15:26 localhost.localdomain.err
drwxr-x---. 2 mysql mysql     4096 8月  26 14:44 mysql
-rw-r-----. 1 mysql mysql      907 8月  26 15:38 mysql_bin.000002
-rw-r-----. 1 mysql mysql      154 8月  26 15:38 mysql_bin.000003
-rw-r-----. 1 mysql mysql       38 8月  26 15:38 mysql_bin.index
-rw-r-----. 1 mysql mysql        6 8月  26 14:57 mysql.pid
drwxr-x---. 2 mysql mysql     8192 8月  26 14:44 performance_schema
-rw-------. 1 mysql mysql     1680 8月  26 14:44 private_key.pem
-rw-r--r--. 1 mysql mysql      452 8月  26 14:44 public_key.pem
-rw-r--r--. 1 mysql mysql     1112 8月  26 14:44 server-cert.pem
-rw-------. 1 mysql mysql     1680 8月  26 14:44 server-key.pem
drwxr-x---. 2 mysql mysql     8192 8月  26 14:44 sys

恢复完全备份

[root@localhost ~]# mysql -uroot -proot < all-20210826.sql 
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# mysql -uroot -proot -e "show databases;" 
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| mkf                |
+--------------------+
[root@localhost ~]# mysql -uroot -proot -e "show tables from mkf;" 
mysql: [Warning] Using a password on the command line interface can be insecure.
+---------------+
| Tables_in_mkf |
+---------------+
| student       |
| teacher       |
+---------------+
[root@localhost ~]# mysql -uroot -proot -e "select * from mkf.student;" 
mysql: [Warning] Using a password on the command line interface can be insecure.
+----+-------------+------+
| id | name        | age  |
+----+-------------+------+
|  1 | tom         |   20 |
|  2 | jerry       |   23 |
|  3 | wangqing    |   25 |
|  4 | sean        |   28 |
|  5 | zhangshan   |   26 |
|  6 | zhangshan   |   20 |
|  7 | lisi        |   50 |
|  8 | chenshuo    |   10 |
|  9 | wangwu      |    3 |
| 10 | qiuyi       |   15 |
| 11 | qiuxiaotian |   20 |
+----+-------------+------+

恢复差异备份

//检查误删数据库的位置在什么地方
[root@localhost ~]# mysql -uroot -proot
mysql> show binlog events in 'mysql_bin.000002';
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| Log_name         | Pos | Event_type     | Server_id | End_log_pos | Info                                  |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| mysql_bin.000002 |   4 | Format_desc    |        10 |         123 | Server ver: 5.7.34-log, Binlog ver: 4 |
| mysql_bin.000002 | 123 | Previous_gtids |        10 |         154 |                                       |
| mysql_bin.000002 | 154 | Anonymous_Gtid |        10 |         219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql_bin.000002 | 219 | Query          |        10 |         290 | BEGIN                                 |
| mysql_bin.000002 | 290 | Table_map      |        10 |         343 | table_id: 244 (mkf.student)           |
| mysql_bin.000002 | 343 | Write_rows     |        10 |         393 | table_id: 244 flags: STMT_END_F       |
| mysql_bin.000002 | 393 | Xid            |        10 |         424 | COMMIT /* xid=642 */                  |
| mysql_bin.000002 | 424 | Anonymous_Gtid |        10 |         489 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql_bin.000002 | 489 | Query          |        10 |         560 | BEGIN                                 |
| mysql_bin.000002 | 560 | Table_map      |        10 |         613 | table_id: 244 (mkf.student)           |
| mysql_bin.000002 | 613 | Update_rows    |        10 |         675 | table_id: 244 flags: STMT_END_F       |
| mysql_bin.000002 | 675 | Xid            |        10 |         706 | COMMIT /* xid=646 */                  |
| mysql_bin.000002 | 706 | Anonymous_Gtid |        10 |         771 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql_bin.000002 | 771 | Query          |        10 |         860 | drop database mkf                     |
| mysql_bin.000002 | 860 | Rotate         |        10 |         907 | mysql_bin.000003;pos=4                |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
15 rows in set (0.00 sec)

//使用mysqlbinlog恢复差异备份
[root@localhost ~]# mysqlbinlog --stop-position=794 /opt/data/mysql_bin.000002 | mysql -uroot -proot
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql> select * from student;
+----+-------------+------+
| id | name        | age  |
+----+-------------+------+
|  1 | tom         |   20 |
|  2 | jerry       |   23 |
|  3 | wangqing    |   25 |
|  4 | sean        |   28 |
|  5 | zhangshan   |   26 |
|  6 | zhangshan   |   20 |
|  7 | lisi        |   50 |
|  8 | chenshuo    |   10 |
|  9 | wangwu      |   17 |
| 10 | qiuyi       |   15 |
| 11 | qiuxiaotian |   20 |
| 12 | xiaozhou    |   18 |
+----+-------------+------+
12 rows in set (0.00 sec)

密码破解

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
mysql> select * from user\G

破解密码步骤：
1、绕过密码验证
跳过skip-grant-tables加入/etc/my.cnf

[root@localhost ~]# vim /etc/my.cnf 
[mysqld]
basedir = /usr/local/mysql
datadir = /opt/data
socket = /tmp/mysql.sock
port = 3306
pid-file = /opt/data/mysql.pid
user = mysql
skip-name-resolve

server-id = 10
log-bin = mysql_bin

skip_grant-tables

重启服务

[root@localhost ~]# systemctl restart mysqld.service

修改密码

use mysql;
update mysql.user set authentication_string = ‘123456’ where User = ‘root’ and Host = ‘localhost’;

[root@localhost ~]# mysql
mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
mysql> update user set authentication_string = password('123456') where User = 'root' and Host = 'localhost';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

恢复密码验证
从/etc/my.cnf文件中删除skip-grant-tables

[root@localhost ~]# vim /etc/my.cnf 
[mysqld]
basedir = /usr/local/mysql
datadir = /opt/data
socket = /tmp/mysql.sock
port = 3306
pid-file = /opt/data/mysql.pid
user = mysql
skip-name-resolve

server-id = 10
log-bin = mysql_bin

重启服务

[root@localhost ~]# systemctl restart mysqld.service

SQL多表查询（group by、order by、inner by、left by、right by）

order by：排序
group by：计数、算平均值、求和等等
inner join：查询多张表中都存在的数据
left join：显示以左边表为主的内容，右边表有匹配左边表内容时完成显示，无匹配时NULL
right by：显示以右边表为主的内容，左边表有匹配右边表内容时完成显示，无匹配时NULL

group by（聚合函数或分组查询）
group by 一般和聚合函数一起使用才有意义，比如count sum avg等，使用group by的两个要素：

出现在select后面的字段，要么是聚合函数中的，要么就是group by中的。
要筛选结果，可以先使用where再用group by或者先用group by再用having

group by 多个条件分析

mysql> create database mkf;
Query OK, 1 row affected (0.00 sec)

mysql> use mkf;
Database changed
mysql> create table test(a varchar(20),b varchar(20),c varchar(20));
Query OK, 0 rows affected (0.00 sec)

mysql> insert test value(1,'a','jia');
Query OK, 1 row affected (0.00 sec)

mysql> insert test value(1,'a','jia');
Query OK, 1 row affected (0.00 sec)

mysql> insert test value(1,'a','jia');
Query OK, 1 row affected (0.00 sec)

mysql> insert test value(1,'a','jia');
Query OK, 1 row affected (0.00 sec)

mysql> insert test value(1,'a','jia');
Query OK, 1 row affected (0.00 sec)

mysql> insert test value(1,'a','yi');
Query OK, 1 row affected (0.00 sec)

mysql> insert test value(1,'b','yi');
Query OK, 1 row affected (0.00 sec)

mysql> insert test value(1,'b','yi');
Query OK, 1 row affected (0.00 sec)

mysql> insert test value(1,'b','yi');
Query OK, 1 row affected (0.00 sec)

mysql> insert test value(1,'b','yi');
Query OK, 1 row affected (0.00 sec)
//第一次查询
mysql> select * from test;
+------+------+------+
| a    | b    | c    |
+------+------+------+
| 1    | a    | jia  |
| 1    | a    | jia  |
| 1    | a    | jia  |
| 1    | a    | jia  |
| 1    | a    | jia  |
| 1    | a    | yi   |
| 1    | b    | yi   |
| 1    | b    | yi   |
| 1    | b    | yi   |
| 1    | b    | yi   |
+------+------+------+
10 rows in set (0.00 sec)
结果中，按照b列来分：则是6个a，4个b；按照c列来分：则是5个jia5个yi

//第二次查询
mysql> select count(a),b from test group by b;   //count后面括号里写什么多可以，可以是数字，*号，但是1的效率比*号要高，所以通常用1
+----------+------+
| count(a) | b    |
+----------+------+
|        6 | a    |
|        4 | b    |
+----------+------+
2 rows in set (0.00 sec)

mysql> select count(1),c from test group by c;   //group by c：就是以c为组去计算
+----------+------+
| count(a) | c    |
+----------+------+
|        5 | jia  |
|        5 | yi   |
+----------+------+
2 rows in set (0.00 sec)

//求平均值
mysql> select avg(age) from student;
+----------+
| avg(age) |
+----------+
|  19.0000 |
+----------+
1 row in set (0.00 sec)

//求和
mysql> select sum(age) from student;
+----------+
| sum(age) |
+----------+
|      190 |
+----------+
1 row in set (0.00 sec)

inner by（内连接）、left join（左连接）、right by（右连接）

//先创建两个表
mysql> create table tablea(id int,name varchar(30));
Query OK, 0 rows affected (0.00 sec)

mysql> create table tableb(id int,job int,parent_id int);
Query OK, 0 rows affected (0.00 sec)

mysql> insert tablea values(1,'zhangshan'),(2,'lisi'),(3,'wangwu'); 
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0
mysql> insert tableb values(1,23,1),(2,34,2),(3,34,4);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from tablea;
+------+-----------+
| id   | name      |
+------+-----------+
|    1 | zhangshan |
|    2 | lisi      |
|    3 | wangwu    |
+------+-----------+
3 rows in set (0.00 sec)

mysql> select * from tableb;
+------+------+-----------+
| id   | job  | parent_id |
+------+------+-----------+
|    1 |   23 |         1 |
|    2 |   34 |         2 |
|    3 |   34 |         4 |
+------+------+-----------+
3 rows in set (0.00 sec)

1）内连接，查询两张表相同的数据
mysql> select tablea.*,tableb.* from tablea inner join tableb on tablea.id = tableb.parent_id;
+------+-----------+------+------+-----------+
| id   | name      | id   | job  | parent_id |
+------+-----------+------+------+-----------+
|    1 | zhangshan |    1 |   23 |         1 |
|    2 | lisi      |    2 |   34 |         2 |
+------+-----------+------+------+-----------+
2 rows in set (0.01 sec)

2）左连接，以左边为标准：在left join左边的就是左，在left join右边的就是右如果右边跟左边不匹配的值则显示空null
mysql> select tablea.*,tableb.* from tablea left join tableb on tablea.id = tableb.parent_id;
+------+-----------+------+------+-----------+
| id   | name      | id   | job  | parent_id |
+------+-----------+------+------+-----------+
|    1 | zhangshan |    1 |   23 |         1 |
|    2 | lisi      |    2 |   34 |         2 |
|    3 | wangwu    | NULL | NULL |      NULL |
+------+-----------+------+------+-----------+
3 rows in set (0.00 sec)

3）右连接
mysql> select tablea.*,tableb.* from tablea right join tableb on tablea.id = tableb.parent_id;
+------+-----------+------+------+-----------+
| id   | name      | id   | job  | parent_id |
+------+-----------+------+------+-----------+
|    1 | zhangshan |    1 |   23 |         1 |
|    2 | lisi      |    2 |   34 |         2 |
| NULL | NULL      |    3 |   34 |         4 |
+------+-----------+------+------+-----------+
3 rows in set (0.00 sec)