数据库-理论基础

什么是数据库

数据：描述事物的符号记录，可以是数字、文字、图形、图像、声音、语言等，数据有多种形式，它们都可以经过数字化后存入计算机。

数据库：存储数据的仓库，是长期存放在计算机内、有组织、可共享的大量数据的集合。数据库中的数据按照一定数据模型组织、描述和存储，具有较小的冗余度，较高的独立性和易扩展性，并为各种用户共享，总结为以下几点：
数据结构化
数据的共享性高，冗余度低，易扩充
数据独立性高
数据由DBMS统一管理和控制（安全性、完整性、并发控制、故障恢复）

数据库与文件系统的区别

管理对象不同：文件系统的管理对象是文件，数据库直接对数据进行存储和管理
存储方式不同：文件系统使用不同的文件将数据分类（.doc/.mp4/.jpg）保存在外部存储上；数据库系统使用标准统一的数据类型进行数据保存（字母、数字、符号、时间）
调用数据的方式不同：文件系统使用不同的软件打开不同类型的文件；数据库系统由DBMS统一调用和管理。如下图：

优点总结

不再需要了解数据存储和其他实现的细节，直接通过DBMS就能获取数据
因为调用数据的一致性，并发访问能力和共享性提高
低延时访问，搜索速度快
能够较为频繁的对数据进行修改，对性能的消耗相比文件系统比较小。
对事务的支持

结构图

区别

联系

常见数据库

关系型数据库(RDBMS)
概念
关系型数据库是建立在关系模型（二维表格模型）基础上的数据库，由多张能互相联接的二维行列表格组成的数据库。
当前主流的关系型数据库有Oracle、Microsoft SQL Server、MySQL、DB2、Microsoft Access
实体关系模型简称E-R模型，利用图形的方式（实体-关系图）来表示数据库的概念设计

非关系型数据库
又被称为NoSQL（Not Only SQL )，非关联型的，是一种轻量、开源、不兼容SQL 功能的数据库，强调Key-Value（键值对） 存储和文档数据库的优点
常见的非关系型数据库：redis(文档型) MongoDB（键值对型）

关系式数据库的组成结构和名词解释

数据以表格的形式出现，每行为单独的一条记录，每列为一个单独的字段，许多的记录和字段组成一张表单（table），若干的表单组成库（database）

SQL语句
DDL：数据定义语言
create drop alter（增删库表字段）
DML：数据操纵语言
update delete insert（增删改记录）
DQL：数据查询语言
select（查数据）
DCL：数据控制语言
grant revoke commit rollback（授权、事务、回滚）

MySQL常见基础数据类型

MySQL数据类型
数据类型用于指定特定字段所包含数据的规则，它决定了数据保存在字段里的方式
包括分配字段的宽度是否可以是字母、数字、日期和时间

类型：
*字符串类型：（CHAR（0-255固定长度，默认11）,VARCHAR（0-255可变长度，节省空间）TEXT（存放最大长度为65,535 个字符的字符串））
*数值类型：
（INT（整数型）、FLOAT(size,d)（浮点型））
小数：decimal，如decimal(5,2)表示共存5位数，小数占2位，整占三位
*日期和时间类型：（DATE（年月日）、DATETIME（年月日时分秒）TIME（时分秒）YEAR(年)）

演示：

数值类型默认正负各占一半数值，一般设置unsigned会全都设置成正值

char和varchar的区别
CHAR的长度是固定的，而VARCHAR2的长度是可以变化的，当varchar存储的字符小于设定值时，按实际长度存储
char的存取数度比varchar快，因为其长度固定，方便程序的存储与查找；但是，因为其长度固定，会有多余的空格占位符占据空间，以空间换取时间效率
char插入数据不计算空格，varchar插入数据计算空格

MySQL约束类型
唯一约束unique：
指定table的列或列组合不能重复，保证数据的唯一性
非空约束not null
非空约束用于确保当前列的值不为空值
主键约束primary key：
相当于唯一约束+非空约束的组合
当创建主键的约束时，系统默认会在所在的列和列组合上建立对应的唯一索引（目录）
外键约束foreign key：
保证一个或两个表之间的参照完整性
默认值default：
default设定默认值

其他知识点
MySQL索引
是一个单独的、物理的数据库结构，它是某个表中一字段或若干字段值的集合，查找时索引类似于一个目录，建立合理的索引，就能加速数据的检索过程。

MySQL锁
当多个用户并发地存取数据时，在数据库中就会产生多个事务同时存取同一数据的情况。加锁后事务释放锁之前，其他的事务不能对此数据对象进行更新操作
锁库、表

MySQL的存储引擎
存储引擎就是存储数据，建立索引，更新查询数据等等技术的实现方式。
MYISAM
默认引擎、插入和查询速度块（但是不支持事务，不支持行级锁，只能锁表，不支持外键约束）
INNODB
支持事务、行级锁和外键约束
MEMORY
工作在内存中，速度快、不能永久保存

事务
并发控制的基本单位
可以把一系列要执行的操作称为事务，而事务管理就是管理这些操作要么完全执行，要么完全不执行

MySQL事务具有以下四个属性（通常称为ACID属性）：

1、原子性（Atomicity）：事务是一个原子操作，它要么全部成功，要么全部失败回滚。如果事务中的任何操作失败，则所有操作都将回滚到之前的状态，以确保数据库中的数据不会被部分更改。一个 SQL 语句失败了，那么该事务的所有SQL 语句必须都进行回滚，退回到事务前的状态。（COMMIT、ROLLBACK）

2、一致性（Consistency）：事务的执行必须使数据库从一个一致状态转换到另一个一致状态。这意味着事务必须满足所有约束条件，以保持数据的完整性和一致性。比如说你转钱给别人，你的银行卡-100，别人的必须+100

3、隔离性（Isolation）：并发事务的执行不能相互干扰。事务必须在独立的空间内执行，这意味着它们看起来像是在独占访问数据库。对于事务的操作，主要分成两种：读操作与写操作之间的影响、写操作与写操作之间的影响。写操作与写操作：通过锁来解决 写操作与读操作：主要是通过 MVCC 机制

4、持久性（Durability）：一旦事务完成提交，其结果就是永久性的，并且即使在系统故障的情况下，也必须能够恢复这些结果。持久性是通过 Redo log 来实现的。

MySQL安装部署

安装包
MySQL：MySQL客户端程序
MySQL-Server：MySQL服务器端程序

源代编译安装：
编译工具：configure、cmake、make
数据库常用的配置选项

-DCMAKE_INSTALL_PREFIX=/PREFIX ----指定安装路径（默认的就是/usr/local/mysql）
-DMYSQL_DATADIR=/data/mysql ----mysql的数据文件路径
-DSYSCONFDIR=/etc ----配置文件路径
-DWITH_INNOBASE_STORAGE_ENGINE=1 ----使用INNOBASE存储引擎
-DWITH_READLINE=1 ----支持批量导入mysql数据
-DWITH_SSL=system ----mysql支持ssl
-DWITH_ZLIB=system ----支持压缩存储
-DMYSQL_TCP_PORT=3306 ----默认端口3306
-DENABLED_LOCAL_INFILE=1 ----启用加载本地数据
-DMYSQL_USER=mysql ----指定mysql运行用户
-DMYSQL_UNIX_ADDR=/tmp/mysql.sock ----默认套接字文件路径
-DEXTRA_CHARSETS=all ----是否支持额外的字符集
-DDEFAULT_CHARSET=utf8 ----默认编码机制
-DWITH_DEBUG=0 ----DEBUG功能设置

需要了解的知识
服务：mysqld
端口：3306
主配置文件：/etc/my.cnf

数据目录：/var/lib/mysql

日志文件：/var/log/mariadb/mariadb.log

进程文件：/var/run/mariadb/mariadb.pid

套接字文件：/var/lib/mysql/mysql.sock

mysql命令

server设置mysql用户及密码

设置密码：mysqladmin -uroot password '123'
登录：mysql -u 用户名 -p密码 -P 端口 -S 套接字文件 -e SQL语句
-h 登陆位置（主机名或ip地址，要登陆哪个服务端主机IP的数据库）
退出命令：exit或ctrl+d

MySQL管理命令

用户，密码设定

创建登录用户
create user zhangsan@'192.168.1.%' identified by '123'
%:指任意的远程终端
'192.168.1.%'指所有1网段IP的主机都可以登录，也可直接指定某一个IP

用户登录
客户端下载mariadb（mysql）
-h 在哪个IP上登录
mysql -uzhangsan -p123 -h 192.168.1.10

用户为自己更改密码
set password=password('123');

root用户为其他用户找回密码
set password for zhangsan@'%'=password('123456');

root找回自己的密码并修改

方法一
关闭数据库，修改主配置文件（/etc/my.cnf）添加：skip-grant-tables
启动数据库，空密码登录并修改密码
update mysql.user set password=password('新密码') where user='root';
然后将配置文件中的skip-grant-tables删除

方法二
systemctl stop mariadb
mysqld_safe --skip-grant-table &
update mysql.user set password=password('123') where user='root' and host='localhost';
可以不进入库直接查询表

杀死进程
ps aux | grep "skip-grant-table"

kill -9 9062;kill -9 9306

授权

对数据库下的所有表授权
grant all on discuzdb.* to zhangsan@'%';
grant all on discuzdb.* to zhangsan@'localhost';

单独对某个表进行授权
grant all on yun211.student to lisi@'%';

创建用户并授权

grant all on yun211.student to wangwu@'%' identified by '123';

取消王五的删除库、表、字段和记录权限

revoke drop,delete on yun211.student from wangwu@'%';

查看指定用户的权限

show grants for wangwu@'%';

刷新权限
flush privileges;

备份还原

mysqldump备份：
若是全库备份则无需在新数据库上创建库，单库备份，需要在新的数据库上创建数据库

外部备份
选项
-u 指定用户
-p 指定密码
-P 指定端口
-h 指定主机
-t 导出数据
-d 导出结构
-B 备份多个库

备份所有库（不需要指定库）：mysqldump -u用户名 -p密码 -A > /备份路径/备份文件名

只备份数据，不备份结构
mysqldump -u用户名 -p密码 -t 数据库名> data.sql

只备份结构，不备份数据
mysqldump -u用户名 -p密码 -d 库名 > database.sql
mysqldump -u用户名 -p密码 -d 库名 表名> database.sql

备份单个数据库（数据+结构）
mysqldump -u用户名 -p密码 数据库名 > /备份路径/备份文件名

备份多个库：
mysqldump -u用户名 -p密码 -B 数据库1 数据库2 > database.sql

备份表
mysqldump -u用户名 -p密码 数据库名 表名> /备份路径/备份文件名

还原
还原库或者表（先建库建表）：mysql -u用户名 -p密码 数据库名 < 备份文件（xxx.sql）
或者是：cat 备份文件.sql | mysql -uroot -p123 库名
导入所有数据库
mysql -u用户名 -p密码 < all.sql

内置备份
先进入mysql，选择一个库，导入
source school.sql

通过脚本实现备份和导入

#!/bin/bash
read -p "输入用户名:" name
read -s -p "输入密码:" pass
echo
read -p "导出(1) 导入(2):" num
if [ $num -eq 1 ];then
	read -p "请选择备份方式(1:库 2:表):" choice1
	if [ $choice1 -eq 1 ];then
		read -p "选择库备份方式(1：全部备份 2：选择备份):" choice2
		if [ $choice2 -eq 1 ];then
			mysqldump -u$name -p$pass -A >all.sql-$(date +"%Y-%m-%d")
		elif [ $choice2 -eq 2 ];then
			mysql -u$name -p$pass -e "show databases;"
			read -p '选择需要备份的库' database_name
				mysqldump -u$name -p$pass $database_name > $database_name.sql-$(date +"%Y-%m-%d")
		else
			echo "输入正确格式！"
		fi
	elif [ $choice1 -eq 2 ];then
		mysql -u$name -p$pass -e "show databases;"
		read -p '选择库:' database_name2
		mysql -u$name -p$pass -e "use $database_name2;show tables;"
		read -p '选择表:' table_name
			mysqldump -u$name -p$pass $database_name2  $table_name >$table_name-table.sql-$(date +"%Y-%m-%d")
	else
		echo "请输入备份方式的正确格式(1:库 2:表)"
	fi
elif [ $num -eq 2 ];then
	read -p "选择导入方式(1:所有库 2:选择库文件):" type
	if [ $type -eq 2 ];then
		read -p "输入导入的sql文件" sql_file
		read -p "输入创建的库名" base_name
		mysql -u$name -p$pass -e "drop database if exists $base_name;create database $base_name;"
		mysql -u$name -p$pass $base_name < $sql_file
	elif [ $type -eq 1 ];then
		read -p "选择库文件" sql_file2
		mysql -u$name -p$pass  < $sql_file2
	fi
else
	echo  "请选择(1：备份 2：导入)"
fi

mysqlhotcopy 备份：（略）
mysql-binlog日志备份（增量备份）：（略）

锁定和解锁

读锁：又称共享锁（S锁）。若事务T对数据对象A(某一资源)加上S锁，则事务T可以读A但不能修改A。其它事务也只能读A但不能修改A。并且，其他事务只能再对A加S锁，不能加X锁，除非T释放A上的S 锁。简单的说，自己只能读，别人也只能读。

写锁：又称排他锁(X锁)、独占锁。若事务T对数据对象A(某一资源)加上X锁，事务T可以读A也可以修改A。其它事务不能读A也不能修改A。并且，其他事务不能再对A加任何锁（共享锁或排他锁），直到T释放A上的锁。简单的说，自己可读可写，别人不可读不可写。

解锁所有表
unlock tables;
加锁所有表，全局锁
flush tables with read lock; 
单独对某一个表上锁：
lock table mysql.user read/write;

数据库操作

列出库
show databases;
建库
create database yun211;
删库
drop database yun211;
进库
use yun211;
查看当前使用的数据库
select database();

数据表操作

建表
格式

简单版
create table student (code int ,name char(20));
创建student表，并添加code和name字段以及类型

复杂版

create table test_table (
id int unsigned not null primary key auto_increment, #字段要求为正数、非空、自增长
name char(30) not null default 0, #字符型长度30字节，默认值为空
age int not null default 0, #字段非空，默认值为0
primary key (id)); #设置id为主键

修改表名
alter table student rename students;

查表
列出所有表名
show tables;
查看单个表结构
desc student;
查看表的创建过程
show create table 表名;

删表
drop table test_table;

字段操作（需要加table）

添加字段
默认末尾追加
alter table student add time datetime;
第一列增加
alter table student add birthday year first;
添加到指定字段后
alter table student add sex char(1) after age;

修改字段
modify（不修改字段名）
修改字段属性
alter table student modify name char(20);
调整字段顺序
alter table student modify age int(3) [first] [after] 字段名;
change（修改字段名）
格式：alter table 表名 change 原字段 新字段 字段类型 约束 [first][after ][字段];
alter table student change name studentname char(30) not null default '' first;

删除字段
alter table student drop birthday;

内容数据操作

插入数据
简单插入
指明插入字段和数据
insert into student (id,name,age) values (1,'zhangsan',21);
按顺序插入指定字段
insert into student values (2,'lisi',20);
插入多条数据
insert into student values (3,'wangwu',18),(4,'zhaoliu',19),(5,'sunqi',25);
将表2查询到的数据插入到表1
insert into table1(id,name) select id,name from table2;
删除记录
删除表中所有数据
delete from student;
删除id=2的记录
delete from student where id=2;
删除年龄在20-30之间的
delete from student where age between 20 and 30;
注：库、表和字段的删除用drop,记录删除用delete
修改记录
update student set age=21 where id=3;
update 表名 set 字段=新值 where 条件;

运维知识额外补充

在数据库中使用\c换行

不进入数据库输入命令（用于写脚本）
mysql -uroot -p123 -e "use mysql;show tables;" | wc -l | grep -v "+"

在数据库中执行系统命令
system指令

用户和密码都存在mysql库中

如果只定义了字段类型，其他的值：

由上图可知：
数据类型可以为空
主键外键默认不设定
默认值为空NULL
其他条件（正整数，自增长等等未设定，否则会出现在Extra中）

查看警告
show warnings;

综合小练习：

实验前准备：
mariadb5.5设置密码方式：
mysqladmin -uroot password 123
mysql -uroot -p123

建库
show databases;

删除原来的库
drop database abc;

再建一个库，设定中文字符集
create database abc charset=utf8;

入库
use abc;

建表

student表（名字，编码）
create table student(name varchar(10),code int(3) zerofill);

info表（编码，科目，成绩）
create table info(code int(3) zerofill, subject varchar(10) ,score int(3));

插入数据
student表：
insert into student values('张三',1),('李四',2),('王五',3),('甲六',4);

info表：insert into info values(1,"数学",80),(2,"数学",75),(1,"语文",90),(2,"语文",80),(1,"英语",90),(2,"英语",85),(3,"英语",80),(4,"英语",70);