MySQL数据库详解

最新推荐文章于 2024-08-27 16:57:03 发布

Aheroliesinyou

最新推荐文章于 2024-08-27 16:57:03 发布

阅读量488

点赞数

本文链接：https://blog.csdn.net/Aheroliesinyou/article/details/83934327

版权

数据库简介：
数据库是一个比较模糊的概念，简单的一个数据表格、一份歌曲列表都可以称为数据库。如果仅仅是一两个类似的数据表，我们完全可以手动管理这些数据，但在如今这个大数据的年代，数据量都是以TB为单位，数据库一般都是多个数据表的集合，具体的数据被存放在数据表中，而且大多数情况下，表与表之间都有内在联系。例如，员工信息表与公子表之间就有内在联系，一般都有对应的员工姓名以及员工编号，存在这种表与表之间相互引用的数据库被称为关系型数据库。而MySQL是一个专门的关系型数据库管理系统，它由瑞典MySQL AB 公司开发，该公司现被Oracle公司收购。利用MySQL可以创建数据库和数据表、添加数据表、修改、查询数据等，MySQL数据库系统的特色是功能强大、速度快、性能优越、稳定性强、使用简单、管理方便。

一、安装软件

本次数据库实验安装的是MariaDB,它是MySQL的另外一个衍生版本，基本上原有的mysql写法和特性都支持。（如果想了解更多两者联系，请看文末链接）
在这里插入图片描述

安装完毕
systemctl start mariadb ##开启mariadn服务
在mysql数据库初始化时，默认会创建一个调试用的空白数据库test，同时初始化程序还会在mysql数据库中创建user数据表，该数据表为MySQL数据库系统的账户及权限表，初始情况下，该表有如下一些问题：
1、mysql.user表中的root为超级管理员账户，然而该帐号默认没有密码，因此可能导致非法用户在没有密码的情况下直接访问数据库。
2、mysql.user中默认创建了匿名帐号，这些帐号同样没有密码。
通过如下命令可查看MySQL默认创建的user帐号及密码数据
在这里插入图片描述
为此，MySQL特别提供了一个Perl脚本程序专门解决这些问题。运行脚本后会提示输入root密码，首次运行直接回车即可，之后会询问是否为root设置密码，是否移除匿名账户，是否禁止root用户从远程访问、是否删除test数据库、是否立刻重新加载新的数据。建议所有的提问都选择Y，特别是在企业生产环境中这一步是必需的
mysql_secure_installation
在这里插入图片描述
步骤太长，截取一部分，后面步骤相同，都选Y
至此，数据库安装完毕

二、MySQL管理工具

MySQL是基于客户端/服务器体系架构的数据库系统，MySQL服务器以守护进程的方式运行，mysqld为服务器主进程。当我们需要对数据库进行任何操作时，都需要使用客户端软件来连接服务器进行操作。MySQL客户端程序有很多，可以使用自带软件mysql (这里指的是数据库客户端软件，而不是MySQL数据库系统)、mysqladmin、mysqldump等命令对数据库进行数据操作，也可以自己设计动态网站通过API连接MySQL数据库进行相同的数据操作。另外，在MySQL官方网站上可以下载到MySQL WorkBench工具，该工具是一个图形化MySQL数据库客户端管理程序。本此重点介绍MySQL软件包集成的客户端软件。

1、mysql工具

mysql工具是MySQL官方提供的连接工具，用户可以通过mysql工具连接到mysqld上进行一系列的SQL操作。mysql工具有两种模式：交互模式和命令行模式。交互模式指令需要连接到mysql服务器下达（如果没有设置过账户密码，直接在命令行输入 “mysql“即可连接到数据库，如果设置过，就需要指定账户名称和密码）；命令行模式通过特定（-e）参数读取shell命令行传递的指令到服务器。
连接指令用法如下：
mysql [OPTIONS] [database]
-h, --help 显示帮助信息
-e, --execute=name 执行指定命令，命令行模式
-u, --user=name 指定连接的用户
-h, --host=name 指定主机名
-p, --password 指定密码
-P, --port 指定端口
交互模式连接数据库演示如下：
mysql -u root -p #使用root帐号连接服务器，回车后提示输入密码
mysql -u root -p '123 #使用root帐号连接服务器，密码为123，不提倡是使用
mysql -u root -p -h 172.25.254.235 #使用root帐号连接172.25.254.235服务器,回车后提示输入密码
交互模式连接到数据库后常用指令：
help (\h) 获取帮助
connect (\r) 重新连接
delimiter (\d) 设置服务端语句的结束符，默认是分号
ego (\G) 发送指令到服务器，等于语句结束符，但是以列的方式显示
exit (\q) 退出mysql客户端
go (\g) 发送指令到服务器，等于语句结束符
quit (\q) 退出Mysql
source (.) 执行系统上的sql脚本
status (\s) 获取MySQL服务的基本信息
system (!) 执行shell命令
tee (\T) 分流输出
use (\u) 选定使用的数据库
charset (\C) 切换字符集
warnings (\W) 语句的警告信息显示出来
nowarning (\w) 不显示语句的警告信息
在这里插入图片描述
非交互模式命令很简单，通过特定（-e）参数读取shell命令行传递的指令到服务器。如下命令为连接到数据库后执行命令查看所有的数据库。

2、mysqladmin工具）

mysqladmin是一个执行管理操作的工具，使用它可以检查服务器配置、当前运行状态，以及创建、删除数据库等。与mysql不同的是，mysqladmin是MySQL官方提供的shell命令行工具(mysql是连接工具)，其参数都需要在shell命令行上执行。

mysqladmin [OPTIONS] COMMAND COMMAND…
COMMAND：
create db_name 创建名为db_name的数据库
drop db_name 删除名为db_name的数据库及数据库中的所有数据表
debug 打开调试日志并记录于error log中
status 输出服务器的基本状态信息
--sleep：status的子参数，多久刷新一次
--count：status的子参数，显示的批次
extended-status 显示扩展的状态信息，等于：SHOW GLOBAL STATUS;
flush-hosts 清空主机相关的缓存，包括：DNS解析缓存、连接错误次数过多而被拒绝访问mysqld的主机等
flush-logs 关闭日志，打开新日志对二进制、中继日志进行滚动
flush-privileges 刷新配置
flush-status 重置状态变量
flush-tables 关闭当前打开的表文件句柄
flush-threads 清空线程缓存池
kill 杀死指定的线程
password 修改指定用户的密码
ping 探测服务器是否在线
processlist 显示mysql线程列表
reload 相当于flush-privileges
refresh 相当于同时使用flush-logs和flush-hosts
shutdown 关闭mysql服务
start-slave 启动从服务器线程
stop-slave 关闭从服务器线程
variables 输出mysqld的服务器变量
version 显示mysql服务器版本
在这里插入图片描述

3、mysqldump工具

mysqldump是一个数据库逻辑备份程序，我们可以使用它对一个或多个MySQL数据库进行备份或将数据库传输至其他MySQL服务器。（执行mysqldump时需要账户拥有SELECT权限才可以备份数据表，SHOW VIEW权限用于备份视图，REIGGER权限用于备份触发器。某些命令选项可能还需要更多的权限才可以完成操作)。
mysqldump不是大数据备份的解决方案，因为mysqldump需要通过重建SQL语句来实现备份功能，对于数据量比较大的数据库备份与还原操作，速度都比较慢。打开mysqldump备份文件会发现，其实里面就是数据库的SQL语言重现。关于数据库的备份后面会有更多的介绍。

三、结构化查询语言SQL

MySQL使用SQL（结构化查询语言）作为自己的数据库操作语言。该语言是对数据库的定义与操作的语法结构，目前SQL是大多数关系型数据库系统的工业标准。也就是说不管我们使用那种关系型数据库系统，他们大多数都支持SQL语言，这是一个通用的关系型数据库语言。
SQL主要分为六部分：数据查询语言、数据操作语言、数据定义语言、数据控制语言、事务处理语言、指针控制语言。SQL语句不区分大小写，但大写更为规范。

1、数据库定义语言

1.1 CREATE DATABASES

描述：该语句用来创建数据库（需要使用者有CREATE权限）
在这里插入图片描述

1.2 CREATE TABLE

描述：该语句用来在数据库中创建数据表，需要先使用USE db_name进入数据库
常用数据类型如下：
TINYINT(n) 8位整数类型（一个字节）
SMALLINT(n) 16位整数类型（2个字节）
MEDIUMINT(n) 24位整数类型（3个字节）
INT(n) 32位整数类型（4个字节）
BIGINT(n) 64位整数类型（8个字节）
FLOAT(n,d) 单精度浮点数
DOUBLE(n,d) 双精度浮点数
DATE 日期格式
TIME 时间格式
char(n) 固定长度字串
varchar(n) 非定长字串
BIT 二进制数据
BLOB 非定长二进制数据
关于数据类型的参考博客：
https://blog.csdn.net/belen_xue/article/details/54357018
http://www.cnblogs.com/carekee/articles/2094676.html
常用属性如下：
NOT NULL 要求数据位非空值
AUTO_INCREMENT 用户插入新的数据后对应整数数据列自动加1
PRIMARY KEY 创建主索引列
KEY 普通索引列
DEFAULT CARSET 设置默认字符集
ENGINE 设置默认数据库存储引擎
关于索引的参考：
https://zhidao.baidu.com/question/92889097.html

在这里插入图片描述

DESCRIBE 语句用来查看数据表的数据结构

1.3 ALTER DATABASW

描述：用来修改数据库的属性，属性被保存在数据库目录的db.opt文件中。
演示：用alter语句修改数据库默认的字符集及校对规则
在这里插入图片描述
附上关于COLLATE的讲解博客
https://blog.csdn.net/flqljh/article/details/49834843
https://blog.csdn.net/xiaoliuliu2050/article/details/52875680

1.4 ALTER TABLE

描述：用来修改数据表结构，如添加删除列、创佳删除索引、修改数据类型等
演示：
数据表重命名：
在这里插入图片描述
添加删除字段：

datatime和timestamp的区别：https://blog.csdn.net/u014696474/article/details/70568733
查看结果：

在name字段后面添加字段：

ALTER TABLE test2 DROP note ##删除note字段

1.5 DROP TABLE

描述：用来删除一个或多个数据表
在这里插入图片描述

1.6 DROP DATABASE

描述：用来删除数据库以及数据库中所有的数据表，慎用
在这里插入图片描述

1.7 DROP INDEX

描述：删除特定数据表中的索引
DROP INDEX ‘PRIMARY‘ ON user； ##从user表中删除索引
DROP INDEX ‘index‘ ON tbl_name; ##从tbl_name表中删除索引

1.8 RENAME TABLE

描述：用来对一个或多个数据表进行重命名
演示：将数据表test1和数据表test3名称对换
在这里插入图片描述
发现原本是test1的内容现在变为test2了。

2、数据库操作语言

2.1 INSERT

描述：在数据表中插入一行新的数据（插入数据时，数据的具体值跟在VALUES后面的括号内，可以使用INSERT一次插入一条数据，也可以同时插入多条数据）在这里插入图片描述

SELECT * FROM employees ###查询数据表employee的所有数据记录，在数据库查询语言会写

2.2 UPDATE

描述：用来更新数据表中现有的数据值（仅修改满足where条件的数据记录）
演示：将westos1的部门改为“hq“
在这里插入图片描述

2.3 LOAD DATA INFILE

描述：用来快速从文本文件中读取数据到数据表中
文本文件中的分隔符：默认使用Tab键为列分隔符，换行符为行分隔符。（可以使用 FIELDS TERMINATED BY设置列分隔符，LINES STARTING BY设置行分隔符）
在这里插入图片描述

本次实验中两个telephone值相同是因为11位电话号码已经超过了int的范围。int：存储所占四个字节，也就是2的32次方，可以存储4294967296种可能性，无符号可以存储0-4294967295，有符号则是-2147483648-2147483647。

2.4 DELETE

描述：用来把满足条件的数据记录删除并返回删除的记录数量
在这里插入图片描述

3、数据库查询语言

SELECT
描述：用来查询数据表中的数据记录。
SELECT name,employee_id,department FROM employees ##查询employees数据表中的name、employee_id、department记录
SELECT name FROM employees WHERE employee_id=‘1’ ##查询employee_id为1的所有记录，并显示出相应的name记录
SELECT * FROM employees ORDER BY employee_id DESC ##查询数据表中的所有记录，并按照employee_id 排列，DESC位降序，AES为升序
SELECT * FROM memployee LIMIT 2 ## 限制仅显示数据记录中的前两行记录
在这里插入图片描述

四、数据库备份与还原

备份对于数据库而言是至关重要的。当数据文件发生损坏、MySQL服务出现错误、系统内核崩溃、计算机硬件损坏或者数据被误删等事件时，使用一种有效的数据备份方案，就可以快速解决以上问题。MySQL提供多种备份方案，包括：逻辑备份、物理备份、全备份以及增量备份，你可以选择最适合自己使用的方式备份数据。
物理备份通过直接复制包含有数据库内容的目录与文件实现，这种备份方式适用于对重要的大规模数据备份，并且要求实现快速还原的生产环境。典型的物理备份就是复制MySQL数据库的部分或全部目录，物理备份还可以备份相关的配置文件。但采用物理备份需要MySQL处于关闭状态或者对数据库进行锁操作，防止备份的过程中改变发送数据。物理备份可以使用mysqlbackup对InnoDB数据进行备份，使用mysqlhotcopy对MyISAM数据进行备份。另外也可以使用系统级别的cp、scp、tar、rsync等命令。
逻辑备份通过保存代表数据库结构及数据内容的描述信息实现。如保存创建数据结构以及添加数据内容的SQL语句，这种备份方式设用于少量数据的备份与还原。逻辑备份需要查询MySQL服务器获得数据结构及内容信息，因为需要查询数据库信息并将这些信息转换为逻辑格式，所以相对于物理备份而言比较慢。逻辑备份不会备份日至、配置文件等不属于数据库内容的资料。逻辑备份的优势在于不管是服务层面数据库层面还是数据表层面的备份都可以实现，由于是以逻辑格式存储的，所以这种备份与系统、硬件无关。
全备份将备份某一时刻所有的数据，增量备份仅备份某一段时间内发生过改变的数据。通过物理或逻辑备份工具就可以完成完全备份，而增量备份需要开启MySQL二进制日至，通过日志记录数据的改变，从而实现增量备份差异。
下面介绍如何使用MySQL提供的工具命令进行逻辑备份。
使用mysqldump 备份所有的数据库，默认该工具会将SQL语句信息导出至标准输出，可以通过重定向将输出保存至文件。
mysqldump -u root -p -all-databases > /mnt/alldata.sql ###备份所有数据库
mysqldump -u root -p -all-databases --no-data > /mnt/nodata.sql ####不备份数据，只备份数据表结构
mysqldump -u root -p westos > /mnt/westos.sql ###备份westos数据表
mysqldump -u root -p --databases hr > /mnt/hr.sql 与 mysqldump -u root -p hr > /mnt/hr.sql 都是备份hr数据库，差别在于不使用–databases 选项，则备份输出信息中不会包含CREATE DATABASES 或 USE 语句。不使用–databases选项备份的数据文件，在后期进行数据还原操作时，如果数据库不存在，必须先创建该数据库再还原。
在这里插入图片描述

可以看到备份时加上–databases后hr.sql备份文件中有创建数据库CREATE DATABASE和使用数据库USE的操作
先删除后还原:

五：忘记密码后如何更改密码

[root@localhost Desktop]# systemctl stop mariadb
[root@localhost Desktop]# mysqld_safe --skip-grant-tables &
[1] 6538
[root@localhost Desktop]# 181106 03:30:23 mysqld_safe Logging to ‘/var/log/mariadb/mariadb.log’.
181106 03:30:23 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql

[root@localhost Desktop]# cd
[root@localhost ~]# mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 1
Server version: 5.5.35-MariaDB MariaDB Server

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

MariaDB [(none)]> 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

MariaDB [mysql]> UPDATE user SET Password=‘redhat’ WHERE User=‘root’;
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3 Changed: 3 Warnings: 0

MariaDB [mysql]> UPDATE user SET Password=password(‘redhat’) WHERE User=‘root’;
Query OK, 3 rows affected (0.01 sec)
Rows matched: 3 Changed: 3 Warnings: 0

MariaDB [mysql]> quit
Bye
更改完毕
然后先结束进程，在重启
[root@localhost ~]# ps aux | grep mysql
root 6538 0.0 0.1 113252 1608 pts/0 S 03:30 0:00 /bin/sh /usr/bin/mysqld_safe --skip-grant-tables
mysql 8191 2.1 9.1 794944 88340 pts/0 Sl 03:37 0:00 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --skip-grant-tables --log-error=/var/log/mariadb/mariadb.log --pid-file=/var/run/mariadb/mariadb.pid --socket=/var/lib/mysql/mysql.sock
root 8218 0.0 0.0 112640 936 pts/0 S+ 03:37 0:00 grep --color=auto mysql
[root@localhost ~]# kill -9 6538
[1]+ Killed mysqld_safe --skip-grant-tables (wd: ~/Desktop)
(wd now: ~)
[root@localhost ~]# ps aux | grep mysql
mysql 8191 0.5 9.1 794944 88340 pts/0 Sl 03:37 0:00 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --skip-grant-tables --log-error=/var/log/mariadb/mariadb.log --pid-file=/var/run/mariadb/mariadb.pid --socket=/var/lib/mysql/mysql.sock
root 8239 0.0 0.0 112640 936 pts/0 R+ 03:38 0:00 grep --color=auto mysql
[root@localhost ~]# kill -9 8191
[root@localhost ~]# ps aux | grep mysql
root 8251 0.0 0.0 112640 932 pts/0 R+ 03:38 0:00 grep --color=auto mysql
[root@localhost ~]# kill -9 8251
bash: kill: (8251) - No such process
[root@localhost ~]# ps aux | grep mysql
root 8273 0.0 0.0 112640 936 pts/0 R+ 03:39 0:00 grep --color=auto mysql
[root@localhost ~]# systemctl start mariadb
[root@localhost ~]# mysql -uroot -predhat
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 5.5.35-MariaDB MariaDB Server

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

MariaDB [(none)]> quit
Bye

六用户授权

[root@localhost ~]# mysql -uroot -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 3
Server version: 5.5.35-MariaDB MariaDB Server

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

建立用户
MariaDB [(none)]> CREATE USER szm@localhost identified by ‘szm’;
Query OK, 0 rows affected (0.01 sec)

查看权限
MariaDB [(none)]> SHOW GRANTS FOR szm@localhost;
±-----------------------------------------------------------------------------------------------------------+
| Grants for szm@localhost |
±-----------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON . TO ‘szm’@‘localhost’ IDENTIFIED BY PASSWORD ‘*10282F3441F41C60798AF66CC03BE3615CF0646A’ |
±-----------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

MariaDB [(none)]> quit
Bye

实验
[root@localhost ~]# mysql -u szm -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 4
Server version: 5.5.35-MariaDB MariaDB Server

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

MariaDB [(none)]> SHOW DATABASES;
±-------------------+
| Database |
±-------------------+
| information_schema |
±-------------------+
1 row in set (0.00 sec)

MariaDB [(none)]> quit
Bye
什么都看不到

返回root用户登陆数据库
MariaDB [(none)]> GRANT SELECT ON westos.* TO szm@localhost;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> SHOW GRANTS FOR szm@localhost;
±-----------------------------------------------------------------------------------------------------------+
| Grants for szm@localhost |
±-----------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON . TO ‘szm’@‘localhost’ IDENTIFIED BY PASSWORD '10282F3441F41C60798AF66CC03BE3615CF0646A’ |
| GRANT SELECT ON westos. TO ‘szm’@‘localhost’ |
±-----------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

回到root继续添加权限
MariaDB [(none)]> GRANT INSERT ON westos.* TO szm@localhost;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> SHOW GRANTS FOR szm@localhost;
±-----------------------------------------------------------------------------------------------------------+
| Grants for szm@localhost |
±-----------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON . TO ‘szm’@‘localhost’ IDENTIFIED BY PASSWORD '10282F3441F41C60798AF66CC03BE3615CF0646A’ |
| GRANT SELECT, INSERT ON westos. TO ‘szm’@‘localhost’ |
±-----------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

接触权限
MariaDB [(none)]> REVOKE SELECT ON westos.* FROM szm@localhost;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> SHOW GRANTS FOR szm@localhost;
±-----------------------------------------------------------------------------------------------------------+
| Grants for szm@localhost |
±-----------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON . TO ‘szm’@‘localhost’ IDENTIFIED BY PASSWORD ‘*10282F3441F41C60798AF66CC03BE3615CF0646A’ |
±-----------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

删除用户
MariaDB [(none)]> DROP USER szm@localhost;
Query OK, 0 rows affected (0.01 sec)

参考博客：
https://blog.csdn.net/liumiaocn/article/details/56665800
https://blog.csdn.net/u010419967/article/details/25193507
https://baike.baidu.com/item/结构化查询语言/10450182?fr=aladdin
2：MySQL工具相关的博客
https://www.cnblogs.com/hanxiaomeng/p/5422047.html
http://blog.51cto.com/64314491/1674115
https://blog.csdn.net/qingsong3333/article/details/77074529