MySQL数据库基础
一、概述
数据库(Database)
简称DB，按照一定格式存储数据的一些文件的组合，顾名思义就是存储数据的仓库，实际上就是一堆文件。
数据库管理系统（DateBase Management System）
简称DBMS，数据库管理系统是专门用来管理数据库中的数据的，可以对数据库当中的数据进行增、删、改、查 等操作，常见的数据库管理系统：MySql、Oracle、MS SQLServer、DB2、sysbase等…
SQL（Structured Query Language）
结构化查询语言，针对关系型数据库的一种语言；SQL 是一种操作数据库的语言，包括创建数据库、删除数据库、查询记录、修改记录、添加字段等。SQL在MySql中可以使用，同时在Oracle中也可以使用，在DB2中也可以使用 。
数据库的类型
- MariaDB/MySQL
- Oracle
- DB2
- MS SQLServer，只能用于windows系统
国产数据库 - 达梦
- 人大金仓
- TiDB，分布式关系型数据库
NOSQL数据库 - Redis，缓存数据库
- MongoDB，文档型数据库
在关系模型中，实体和联系通常通过以下三种类型来进一步分类：
一对一（1:1）；一对多（1:N）；多对多（M:N）；
关系数据库相关概念
- 库（Database）： 库是一个存储数据的容器，它可以包含多个数据库。在某些管理系统中，库等同于数据库。
- 表（Table）： 表是数据库中的一个表格，由行和列组成。表是存储数据的主要结构，每个表通常对应一个实体类型。表的列名称为属性，而表的行名称为记录。
- 行（Record）： 行也称为记录，它是表中的一个单元，代表表中的一个具体实例。每一行包含了一组属性值，这些值共同描述了一个实体的状态。
- 列（Column）： 列是表中的一列，它代表了表的一个属性。每列都有一个数据类型，用于定义存储在其中的数据的种类和格式。
- 字段（Field）： 字段通常指的是表中的行与列的交叉点，它存储了单个数据项。在数据库中，每个字段都有其特定的数据类型和用途。
- 数据（Data）： 数据是存储在数据库中的信息。它可以是文本、数字、日期、图像、声音等各种形式。数据是数据库管理和操作的核心。
SQL语句执行流程：
客户端 → 连接器 → [查询缓存] → 解析器 → 预处理器 → 优化器 → 执行引擎 → 存储引擎 → 返回结果
默认监听端口号：3306/tcp
二、安装
1、mariadb安装
服务端程序： mariadb-server；客户端程序：mariadb
[root@mysql ~]# yum install -y mariadb-server mariadb
2、mysql安装
服务端程序：mysql-server；客户端程序：mysql；
[root@mysql ~]# yum install -y mysqld-server mysql
##编译安装，需要cmake环境
3、启动并开机自启
[root@mysql ~]# systemctl enable --now mysqld.service
4、本地连接（本地登录）
[root@mysql ~]# mysql
Welcome to the mysqld monitor. Commands end with ; or \g.
Your mysqld connection id is 2
注意：mysqld默认的管理用户是“root”，与操作系统的“root”没有任何关系！！！root用户默认没有密码！！
三、mysqld数据库配置与命令
yum安装后生成的目录
[root@mysql mysql]# rpm -ql mysqld-server
/etc/logrotate.d/mysqld
/etc/my.cnf.d/server.cnf
/usr/bin/innochecksum
/usr/bin/myisam_ftdump
/usr/bin/myisamchk
/usr/bin/myisamlog
/usr/bin/myisampack
/usr/bin/mysql_convert_table_format
/usr/bin/mysql_fix_extensions
/usr/bin/mysql_install_db
/usr/bin/mysql_plugin
/usr/bin/mysql_secure_installation
/usr/bin/mysql_setpermission
/usr/bin/mysql_tzinfo_to_sql
/usr/bin/mysql_upgrade
/usr/bin/mysql_zap
/usr/bin/mysqlbug
/usr/bin/mysqld_multi
/usr/bin/mysqld_safe
/usr/bin/mysqld_safe_helper
/usr/bin/mysqldumpslow
/usr/bin/mysqlhotcopy
/usr/bin/mysqltest
/usr/bin/perror
/usr/bin/replace
/usr/bin/resolve_stack_dump
/usr/bin/resolveip
/usr/lib/systemd/system/mysqld.service
/usr/lib/tmpfiles.d/mysqld.conf
/usr/lib64/mysql/INFO_BIN
/usr/lib64/mysql/INFO_SRC
/usr/lib64/mysql/mysqlbug
/usr/lib64/mysql/plugin
/usr/lib64/mysql/plugin/adt_null.so
/usr/lib64/mysql/plugin/auth_0x0100.so
/usr/lib64/mysql/plugin/auth_pam.so
/usr/lib64/mysql/plugin/auth_socket.so
/usr/lib64/mysql/plugin/auth_test_plugin.so
/usr/lib64/mysql/plugin/daemon_example.ini
/usr/lib64/mysql/plugin/dialog_examples.so
/usr/lib64/mysql/plugin/ha_innodb.so
/usr/lib64/mysql/plugin/ha_sphinx.so
/usr/lib64/mysql/plugin/handlersocket.so
/usr/lib64/mysql/plugin/libdaemon_example.so
/usr/lib64/mysql/plugin/mypluglib.so
/usr/lib64/mysql/plugin/qa_auth_client.so
/usr/lib64/mysql/plugin/qa_auth_interface.so
/usr/lib64/mysql/plugin/qa_auth_server.so
/usr/lib64/mysql/plugin/query_cache_info.so
/usr/lib64/mysql/plugin/semisync_master.so
/usr/lib64/mysql/plugin/semisync_slave.so
/usr/lib64/mysql/plugin/server_audit.so
/usr/lib64/mysql/plugin/sphinx.so
/usr/lib64/mysql/plugin/sql_errlog.so
/usr/libexec/mysqld-prepare-db-dir
/usr/libexec/mysqld-wait-ready
/usr/libexec/mysqld
/usr/share/man/man1/innochecksum.1.gz
/usr/share/man/man1/msql2mysql.1.gz
/usr/share/man/man1/myisam_ftdump.1.gz
/usr/share/man/man1/myisamchk.1.gz
/usr/share/man/man1/myisamlog.1.gz
/usr/share/man/man1/myisampack.1.gz
/usr/share/man/man1/mysql.server.1.gz
/usr/share/man/man1/mysql_convert_table_format.1.gz
/usr/share/man/man1/mysql_fix_extensions.1.gz
/usr/share/man/man1/mysql_install_db.1.gz
/usr/share/man/man1/mysql_plugin.1.gz
/usr/share/man/man1/mysql_secure_installation.1.gz
/usr/share/man/man1/mysql_setpermission.1.gz
/usr/share/man/man1/mysql_tzinfo_to_sql.1.gz
/usr/share/man/man1/mysql_upgrade.1.gz
/usr/share/man/man1/mysql_zap.1.gz
/usr/share/man/man1/mysqlbinlog.1.gz
/usr/share/man/man1/mysqlbug.1.gz
/usr/share/man/man1/mysqlcheck.1.gz
/usr/share/man/man1/mysqld_multi.1.gz
/usr/share/man/man1/mysqld_safe.1.gz
/usr/share/man/man1/mysqldumpslow.1.gz
/usr/share/man/man1/mysqlhotcopy.1.gz
/usr/share/man/man1/mysqlimport.1.gz
/usr/share/man/man1/mysqltest.1.gz
/usr/share/man/man1/perror.1.gz
/usr/share/man/man1/replace.1.gz
/usr/share/man/man1/resolve_stack_dump.1.gz
/usr/share/man/man1/resolveip.1.gz
/usr/share/man/man8/mysqld.8.gz
/usr/share/mysql/README.mysql-cnf
/usr/share/mysql/errmsg-utf8.txt
/usr/share/mysql/fill_help_tables.sql
/usr/share/mysql/my-huge.cnf
/usr/share/mysql/my-innodb-heavy-4G.cnf
/usr/share/mysql/my-large.cnf
/usr/share/mysql/my-medium.cnf
/usr/share/mysql/my-small.cnf
/usr/share/mysql/mysql_performance_tables.sql
/usr/share/mysql/mysql_system_tables.sql
/usr/share/mysql/mysql_system_tables_data.sql
/usr/share/mysql/mysql_test_data_timezone.sql
/var/lib/mysql ##mysqld数据库的数据存储目录
/var/log/mysqld
/var/log/mysqld/mysqld.log
/var/run/mysqld
mysqld服务器的启动脚本
[root@mysql ~]# cat /usr/lib/systemd/system/mysqld.service
[Unit]
Description=mysqld database server
After=syslog.target
After=network.target
[Service]
Type=simple
User=mysql ##程序运行用户
Group=mysql ##指定运行服务的组
ExecStartPre=/usr/libexec/mysqld-prepare-db-dir %n
# Note: we set --basedir to prevent probes that might trigger SELinux alarms,
# per bug #547485
ExecStart=/usr/bin/mysqld_safe --basedir=/usr
ExecStartPost=/usr/libexec/mysqld-wait-ready $MAINPID
"/usr/lib/systemd/system/mysqld.service" 48L, 1697C 39,1 55%
# http://fedoraproject.org/wiki/Systemd#How_do_I_customize_a_unit_file.2F_add_a_custom_unit_file.3F
# For example, if you want to increase mysqld's open-files-limit to 10000,
# you need to increase systemd's LimitNOFILE setting, so create a file named
# "/etc/systemd/system/mysqld.service.d/limits.conf" containing:
# [Service]
# LimitNOFILE=10000
# Note: /usr/lib/... is recommended in the .include line though /lib/...
# still works.
# Don't forget to reload systemd daemon after you change unit configuration:
# root> systemctl --system daemon-reload
[Unit]
Description=mysqld database server
After=syslog.target
After=network.target
[Service]
Type=simple
User=mysql
Group=mysql
ExecStartPre=/usr/libexec/mysqld-prepare-db-dir %n
# Note: we set --basedir to prevent probes that might trigger SELinux alarms,
# per bug #547485
ExecStart=/usr/bin/mysqld_safe --basedir=/usr
ExecStartPost=/usr/libexec/mysqld-wait-ready $MAINPID
# Give a reasonable amount of time for the server to start up/shut down
TimeoutSec=300
# Place temp files in a secure directory, not /tmp
PrivateTmp=true
[Install]
WantedBy=multi-user.target
- [Unit]部分：Description是服务的描述，After指定服务在哪些系统组件之后启动（如网络就绪后）。
- [Service]部分：
Type：指定服务类型，常见的有simple（默认，启动主进程）、forking（服务后台运行）、idle（等系统空闲时启动）等。
ExecStart：启动服务时执行的命令或路径。
Restart：定义在什么情况下重启服务（如on-failure表示失败时重启）。
User和Group：指定运行服务的用户和组，根据需要设置。 - [Install]部分：WantedBy指定服务在哪个运行级别启动（multi-user.target是多用户命令行模式，graphical.target是图形模式）。
保存文件后，重新加载systemd配置
systemctl daemon-reload
服务启动时读取的默认配置文件
[root@mysql mysql]# cat /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql ##定义数据存储目录
socket=/var/lib/mysql/mysql.sock ##定义连接的网络接口文件
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mysqld according to the
# instructions in http://fedoraproject.org/wiki/Systemd
[mysqld_safe]
log-error=/var/log/mysqld/mysqld.log ##程序运行的错误日志
pid-file=/var/run/mysqld/mysqld.pid ##程序运行的PID文件
#
# include all files from the config directory
#
!includedir /etc/my.cnf.d
mysql命令
mysql数据库登录的命令行工具，本地登录时且root用户没有密码，输入mysql，等效于”mysql -uroot -hlocalhost -P3306“
mysql [options] db_name
常用选项
-u：指定登录用户
-p：指定用户密码
-h：指定登录数据库的IP或者域名
-P：指定登录数据库的端口号
-e：能够在终端执行数据库指令
四、默认数据库
- information_schema ：信息数据库，存储所有的库、表、列的名称，任意可登录数据库的用户都可读；sql注入
COLUMNS：数据表的列信息表。用于查看和管理MySQL数据库中数据表的列信息。
ENGINES：存储引擎信息表。用于查看和管理MySQL数据库中支持的存储引擎信息。
SCHEMA_PRIVILEGES：架构权限表。用于查看和管理架构下的权限信息，包括用户对架构下表的SELECT、INSERT、UPDATE等权限。
- mysql ：主数据库，mysqld运行的必须数据库，用户与配置信息
db：保存了每个数据库的权限信息，包括用户对每个数据库的CREATE、ALTER、DROP等权限。
host：保存了MySQL服务器上的主机信息，包括主机名、IP地址、连接权限等。
user：保存了MySQL服务器上的用户账号信息，包括用户名、密码、权限等。
- performance_schema：性能数据库，存储mysqld的资源使用、安全策略权限配置信息
cond_instances：条件实例表，该表用于存储各种条件或锁定的实例信息
rwlock_instances：读写锁实例表，记录了数据库中各个读写锁的信息
五、 SQL语言
- DDL：数据定义语言，对数据库结构操作
create：创建（用户，库，表）
alter：改变
drop：删除
- DML：数据操作语言，对数据表的操作
insert：插入
update：更新
delete：删除数据
- DCL：数据控制语言，针对用户权限设置
grant：用户赋权
revoke：移除用户权限
- DQL：数据查询语言，对数据表的操作
select：查询
六、mysqld数据类型
常用的数据类型有：
- 整型
- 浮点型
- BIT类型
- 定点数
- 日期时间类型
- 字符串
- NULL类型
七、数据库操作命令
库操作命令
- 查看数据库
show databases;
- 查看所在数据库
select database();
- 查看当前登录用户
select user();
- 查看当前数据库版本
select version();
- 查看用户权限列表
show privileges[\G];
- 查看指定用户的权限
show grants for root@localhost\G;
- 切换数据库
use databaseName[;]
- 创建数据库
create database databaseName [character set utf8];
- 删除数据库
drop database databaseName;
表结构操作命令
- 查看数据表
show tables;
- 查看表结构
desc[describe] tableName;
- 创建数据表
create table tableName(columnName（列名称） dataType（数据类型）, ............);
- 删除数据表
drop table tableName;
alter命令用法
ALTER TABLE <表名> [修改选项]
常见用法
| ADD COLUMN <列名> <类型>
| CHANGE COLUMN <旧列名> <新列名> <新列类型>
| ALTER COLUMN <列名> { SET DEFAULT <默认值> | DROP DEFAULT }
| MODIFY COLUMN <列名> <新类型>
| DROP COLUMN <列名>
| RENAME TO <新表名>
| CHARACTER SET <字符集名>
| COLLATE <校对规则名>
- 修改表名
alter table tab_name rename to tab_new;
- 修改字段类型(修改字段相对位置)
alter table stuS modify id int;
- 修改字段默认值/是否为空/自动增长
alter table stuS modify id int primary key;
- 修改字段名/字段类型
#alter table tab_name change field newfield newtype;
#修改字段类型
alter table stuS modify id int;
#修改字段名及类型
alter table stuS change column object subject varchar(50);
#通过alter modify 修改字段是否为null或者默认值!
alter table stuS modify column name varchar(50) not null default "未知";
- 添加字段
alter table stuS add column extra text;
#添加到第一列
alter table stuS add i int first;
#添加到某一字段后面!
alter table stuS add sex varchar(10) after name;
- 删除字段
alter table stuS drop id;
插入数据
- 单行插入
insert [into] tableName[(columnName,.........)] value('value1',value2,.......);
- 批量插入
insert into stu value('张三', 22, '11011011011', '男'),('王无',26 , '12011011011', '女'),('里斯',27 ,'12011011099', '男');
where字句运算符
比较运算符
| 运算符 | 说明 |
|---|---|
| >, >=, <, <= | 大于，大于等于，小于，小于等于 |
| = | 等于，NULL 不安全，例如 NULL = NULL 的结果是 NULL |
| <=> | 等于，NULL 安全，例如 NULL <=> NULL 的结果是 TRUE(1) |
| !=, <> | 不等于 |
| BETWEEN a0 AND a1 | 范围匹配，[a0, a1]，如果 a0 <= value <= a1，返回 TRUE(1) |
| IN (option, …) | 如果是 option 中的任意一个，返回 TRUE(1) |
| IS NULL | 是 NULL |
| IS NOT NULL | 不是 NULL |
| LIKE | 模糊匹配。% 表示任意多个（包括 0 个）任意字符；_ 表示任意一个字符 |
逻辑运算符
| 运算符 | 说明 |
|---|---|
| AND （&&） | 多个条件必须都为 TRUE(1)，结果才是 TRUE(1) |
| OR （||） | 任意一个条件为 TRUE(1), 结果为 TRUE(1) |
| NOT （！） | 条件为 TRUE(1)，结果为 FALSE(0) |
更改数据
update tableName set columnName=newValue;
删除数据
全表删除
delete from tableName;
查询
select * from tableName;
- 去重查询
select distinct class from stu;
- 统计查询，非空数据
count() 统计数量个数函数
select count(class) from stu;
- 聚合函数查询
sum()：计算和值
select sum(score) 总成绩 from sc;
avg()：计算平均值
select avg(score) 平均成绩 from sc;
max()：最大值
select max(score) 最高成绩 from sc;
min()：最小值
select min(score) 最低成绩 from sc;
- 分组查询（group by）
#每个班的总成绩
select class,sum(score) from sc group by class;
分组后的条件：having
##查询总成绩大于200分的班级
select class,sum(score) from sc where class!=2 group by class having sum(score) > 200.00;
- 排序查询（order by）
默认升序排序
select * from stu order by age [asc];
降序
select * from stu order by age desc;
- 分页查询（limit number）
# 显示stu数据库前两行
select * from stu limit 2;
#只查看总成绩的第二名
select * from stu limit 2,1;
- 子查询
select name,score from stu where name in (select name from info);
- union联合查询
select * from sc union select 1,2,3;
- 多表查询
内连接查询
select A.name as name, A.school, B.job from B,A where A.name=B.name;
外连接查询
左外连接
select A.name as nameA, A.school, B.name as nameB, B.job from B left join A on A.name=B.name;
右外连接
select A.name as nameA, A.school, B.name as nameB, B.job from B right join A on A.name=B.name;
在MySQL中，权限控制是通过Grant和Revoke命令来管理用户和角色的权限的。
- Alter：修改。允许用户修改数据库中的表结构。
- Alter routine：修改存储过程或函数。允许用户修改已创建的存储过程或函数。
- Create：创建。允许用户创建新的数据库对象，如表、视图、存储过程等。
- Create routine：创建存储过程或函数。允许用户创建新的存储过程或函数。
- Create temporary tables：创建临时表。允许用户创建临时表，这些表在会话结束时会自动删除。
- Create view：创建视图。允许用户创建新的视图。
- Create user：创建用户。允许用户创建新的数据库用户账户。
- Delete：删除。允许用户删除表中的数据。
- Drop：删除。允许用户删除数据库中的对象，如表、视图等。
- Event：事件。允许用户管理和修改事件调度器中的事件。
- Execute：执行。允许用户执行存储过程或函数。
- File：文件。允许用户访问数据库服务器的文件系统。
- Grant option：授予选项。允许用户将权限授予其他用户。
- Index：索引。允许用户在表上创建、修改或删除索引。
- Insert：插入。允许用户向表中插入数据。
- Lock tables：锁定表。允许用户锁定数据库中的表，以防止其他用户同时访问这些表。
- Process：进程。允许查看或杀死数据库服务器上的进程。
- Proxy：代理。允许用户作为代理登录，代表其他用户执行操作。
- References：引用。允许用户创建外键约束。
- Reload：重新加载。允许用户重新加载配置文件或刷新权限表。
- Replication client：复制客户端。允许用户从主服务器接收数据以进行复制。
- Replication slave：复制从机。允许用户作为数据复制的从服务器。
- Select：选择。允许用户查询表中的数据。
- Show databases：显示数据库。允许用户查看用的数据库列表。
- Show view：显示视图。允许用户查看数据库中的视图定义。
- Shutdown：关闭。允许用户关闭数据库服务器。
- Super：超级。允许用户所有的权限，并且可以不受限制地执行许多系统命令。
- Trigger：触发器。允许用户创建触发器，以在特定事件发生时自动执行代码。
- Create tablespace：创建表空间。允许用户创建新的表空间，用于存储数据库对象。
- Update：更新。允许用户更新表中的数据。
- Usage：使用。这个权限通常用于限制用户只能查看自己的权限，不能进行任何实际的数据库操作。
创建远程管理用户
创建用户
mysql> create user 'tian'@'192.168.49.147' identified by '';
查看用户权限
mysql> show grants for tian@'192.168.49.147';
MySQL远程连接
mysql> show grants for 用户名@远程主机ip
赋予权限
grant all on *.* to 'root'@'192.168.115.130';
删除用户权限
revoke all on 库.表 from 'username'@'ipaddress';
刷新权限
flush privileges;
-- 删除用户的连接权限
revoke usage on *.* from 'username'@'ipaddress';
--无法直接删除，只能删除用户
drop usage
九、mysqld数据库备份
9.1 数据备份的重要性
- 备份的主要目的是灾难恢复。
- 在生产环境中，数据的安全性至关重要。
- 任何数据的丢失都可能产生严重的后果。
9.2 造成数据丢失的原因
- 程序错误
- 人为操作错误
- 运算错误
- 磁盘故障
- 灾难（如火灾、地震）和盗窃
9.2 备份需要考虑的问题
- 可以容忍丢失多长时间的数据；
- 恢复数据要在多长时间内完成；
- 恢复的时候是否需要持续提供服务；
- 恢复的对象，是整个库，多个表，还是单个库，单个表。
9.4 备份类型
1、根据是否需要数据库离线
- 冷备（cold backup）：需要关mysql服务，读写请求均不允许状态下进行；
- 温备（warm backup）：服务在线，但仅支持读请求，不允许写请求；
- 热备（hot backup）：备份的同时，业务不受影响。
注意：
这种类型的备份，取决于业务的需求，而不是备份工具
MyISAM不支持热备，InnoDB支持热备，但是需要专门的工具
建议的恢复策略
- 完全+增量+二进制日志
- 完全+差异+二进制日志
9.5 常见的备份方法
1、物理冷备（完全备份）
备份时数据库处于关闭状态，直接打包数据库文件
备份速度快，恢复时也是最简单的
2、专用备份工具mysqldump或mysqlhotcopy （完全备份，逻辑备份）
mysqldump常用的逻辑备份工具 （导出为sql脚本）
mysqlhotcopy仅拥有备份MyISAM和ARCHIVE表
启用二进制日志进行增量备份 （增量备份）
进行增量备份，需要刷新二进制日志
3、第三方工具备份
免费的MySQL热备份软件Percona XtraBackup（阿里云的工具：dts，支持热迁移）
9.6 备份案例
mysqldump工具
1、完整备份
所有数据库的备份
备份
mysqldump --all-databases -uroot > all.sql
恢复
mysql -uroot < all.sql
指定数据库的备份
备份
mysqldump --databases jx -uroot > jx.sql
恢复
mysql -uroot < jx.sql
指定数据表的备份
备份
mysqldump jx stu > jx.stu.sql
恢复
mysql -uroot jx< jx.sql
2、增量备份
二进制备份
配置过程
vim /etc/my.cnf
[mysqld]
log-bin=mysql-bin
binlog_format="statement"
systemctl restart mysqld
查看日志文件内容
##在数据库中查看日志文件及书写位置
show master status;
######
mysqlbinlog mysql-bin.000001 |less
注意：在数据库中有很多函数，可以提升查询效率如：
– LENGTH(str):返回字符串str的字节长度；
– CONCAT(str1,str2,…)：返回来自于参数连结的字符串。如果任何参数是NULL，返回NULL。可以有超过2个的参数。一个数字参数被变换为等价的字符串形式；
– curdate()/current_date():获取当前日期；
– curtime()/current_time()：获取当前时间；
– now():获取当前日期时间；
– ABS(X):返回X的绝对值；
– rand():返回随机数；等等
扫一扫
2519
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
