数据库MySQL理论study补充
Java养成计划----学习打卡第九十m天
内容导航
Java（打卡第八十m天)
分析书本：理论知识： ----- 数据库备份，触发器
虽然现在已经滚动到了javaWeb中了，但是mysql的理论知识还是不扎实，只是能够运用知识来解决问题，但是理论不是很熟悉，同时为了软考中级，所以还是要扎实一点
Mysql理论
数据库的认识
尽管可以看懂数据库的操作了，那对于数据库你真的了解吗？什么是Data DB DBMS DBS DBA？
-
数据data ： 这是计算机中最常见的理论了，数据就是描述客观事物的符号记录。 客观事物可以是可触及的对象比如树，零件 ；也可以是抽象的事件，比如球赛，演出；还可以是事务之间的联系，比如订单
-
数据库database : DB， 是存放数据的仓库。是长期存储在计算机内的、有组织的、可共享的数据集合。 在数据库中存放了有组织的有价值的数据资源。 数据库有最小冗余度，较高的数据独立性和易扩展性
-
数据库系统database system ： DBS 是指计算机系统中引入数据库后的系统构成，一般由数据、数据库管理系统、应用系统、数据库管理员和用户构成
-
数据库管理员database administer DBA : 是负责数据库的建立、使用、维护的专门人员
简单理解这几个概念就是数据是客观事物的记录，数据库是数据的仓库，DBA是维护仓库的专业人员，DBS是整个包含数据库的整体： 这里面由使用者客户，有维护者DBA，有操作的事务data，有data操作的系统应用系统和数据库管理系统
数据库的要求就是减少冗余，增加数据的独立性，同时要易扩展; 这也是数据库设计范式所提到的减少依赖，并且要避免间接依赖和部分依赖
数据库系统DBS
上面已经说过数据库系统包含数据库操作的所有部分 数据一般存放在数据库管理系统【数据库】中。 这里也可以架构一下
用户 活动在应用程序之上
应用程序的运行建立在 数据库管理系统之上
数据库管理系统基于 操作系统
操作系统基于硬件
- 数据是数据库管理系统的主体
- 硬件是整个体系的结构支撑，包括CPU，缓存等
- 软件 ：包括系统软件和应用软件；系统软件包括操作系统和数据库管理系统 ，而数据库系统就是数据库系统中最核心的软件 应用软件是在数据库系统的支持下基于用户需求设置的软件
- 用户包括专业用户，非专业用户和数据库管理员。 DBA是维护数据库系统，专业用户是Programmer设计程序，通过程序维护数据库，服务于最终的用户
数据库系统的特点
上面的介绍中已经提到了一些数据库系统的特点，比如冗余度小，易维护，数据独立性高。
- 数据结构化 ： 整个数据库按照一定的结构形式构成。描述数据不仅仅只是描述数据本身，还要描述数据之间的关系。用户可以通过不同的路径存取数据
- 数据的共享性高，冗余度低，易扩展 ： 数据库系统的数据面向整个系统，所以可以为多用户和多应用共享。每个用户只会与数据库中的一部分数据发生联系；用户数据可以重叠，多个用户可以同时操作数据而互相不影响。 可以减少数据的冗余，节约存储空间，避免数据的不一致性 之前的ACID 中的C 就是consistency 就是一致性。数据共享的，是一致的
- 数据的独立性高 ： 数据的独立性可以从物理独立性和逻辑独立性分析，物理独立性，用户的应用程序和存储在磁盘上的数据库是相互独立的， 数据的存储结构改变的时候，通过对映像的相应改变可以保持逻辑结构不变，从而应用程序也不用改变。 从逻辑上看，用户的应用程序和数据库的逻辑结构是相互独立的。及时数据的逻辑结构改变了，应用程序也不必修改
- 数据由数据库管理系统统一操作 ： 比如数据的安全性保护；数据的完整性检查；数据的并发控制 ；数据的备份和恢复
而Mysql数据库作为数据库的一种，其优势首先是不收费；其次就是体积小，速度快，相应的其存储的数据也就小了
Q : 为什么要使用DBS？
A ： 使用数据库的优点 ： 查询迅速、准确，可以减少纸质文件的使用； 数据结构化，由数据库管理系统统一挂你；数据冗余度小；数据的独立性和共享性好；数据库管理系统提供了数据控制功能
DBS 和 数据库管理系统是不一样的，数据库管理系统只是其中的一部分；DBS包括所有的部分包括使用者
系统数据库是MySQL自带数据库，是不能删除的
MySQL数据库概述
Mysql数据库是数据库的一种，也是存放数据的仓库，以系统文件的方式存储在磁盘上，由数据库系统进行管理和维护。
在Mysql中，数据库是按照数据结构进行组织，存储和管理的仓库。每个数据库中都由一个或者多个API，用来操作数据。数据也可以存储在文件中，但是文件读写数据的速度比较慢。所以现在使用关系型数据库Relationl database management system RDBMS 来存放和管理大数据量，MySQL就是一款关系型数据库。
- 关系型数据库，是建立在关系模型上的数据库，借助集合代数等方法来处理数据。关系型数据库的特点为：
- 数据以表格形式出现：数据库表最基本
- 每行为一个记录
- 每列为记录名称的作用域，字段
- 许多行和列组成一个表单
- 若干表单组成数据库
MySQL的数据库对象
MySQL数据库的数据在逻辑上被组织成一系列的对象，当数据连接到数据库后，所看到的是这些逻辑对象，不是物理的数据库文件，MySQL有很多数据库对象
- 数据表 ： 由行和列组成，一行数据称为一个记录，代表一个实体或者联系，每一列代表形同类型的数据，称为字段，一个数据库表由一行或者多行记录组成，没有记录的表称为空表
- 主键 ： 每个表通常有一个主关键字，用于唯一标识一条记录，主键是唯一的，用户可以通过主键来查询数据
- 外键 ： 用于关联两个表
- 复合键 ：复合键（组合键）将多个列作为一个索引键，一般用于复合索引
- 索引： 使用索引可以快速访问数据库表中的特定信息，索引是对数据库表中一列或者多列的值进行排序的一种结果，类似书籍的目录
- 视图： View，视图看上去和同表相似，具有一组命名的字段和数据项，但它就是一个虚拟的表，在数据库中并不存在，视图是由查询数据库表或其他视图产生的。它限制了用户能够看到和修改的数据。视图可以用来控制用户对数据的访问，可以简化数据的显示
- 默认值 ： 默认值是在当在表中创建列或者插入数据的时候，为没有指定具体值的列或者数据项赋予事先设定的好的值
- 约束 ： 是数据库实施数据一致性和完整性的方法，或者说是一套机制，包括主键约束，外键约束，唯一性约束，默认值约束和非空约束
- 规则 ： 用来限制数据库表中的字段的有限范围，以确保列中数据完整性的一种方式。
- 触发器 ： 一种特殊的存储过程，与表格或者某些操作相关联，当用户对数据进行插入、修改、删除或对数据库表进行建立、修改、删除时**，自动执行
- 存储过程： 一组经过编译的可以重复使用的T-SQL代码的组合，它是经过编译存储到数据库中，所以运行速度快于执行相同的SQL语句块
MySQL为关系型数据库，这种关系就像一种表格，一个关系数据库由一个或者数个表格组成。 表头header是每一列的名称，列col是具有相同数据类型的数据的集合；行row描述某一条记录的具体信息；值是行的具体信息，数据类型必须相同，键的值具有唯一性
系统数据库
上面说过系统的数据库是不能够删除的，系统数据库有以下几种
-
information_schema ：这个数据库保存了mysql服务器的所有数据库的信息，比如数据库的名、数据库表、访问权限、数据类型等，该数据库是一个虚拟的数据库，物理上不存在。
-
mysql： 这个数据库是MySQL的核心数据库，类似的有SQL server的master表，主要负责存储数据库表的用户、权限设置、关键字，还有mysql自己需要使用的控制和管理信息 ； 可以使用user表修改密码
-
performance_schema : 该数据库用来收集数据库服务器的性能参数，该数据库里表的存储引擎都是PERFOMANCE_SCHEMA ，但是用户是不能创建引擎为PERFORMANCE_SCHEMA的表的
-
sys ： 这个数据库的所有数据来自perfomace_schema数据库，使performace_schema数据库的复杂度降低，方便DBA阅读，以便让DBA了解数据库的运行
数据库的操作CREATE, DROP,USE就不再赘述
这里主要使介绍之前没提到的命令
查看数据库的charset等
查看数据库的详细信息使用命令
SHOW CREATE DATABASE basename;
mysql> SHOW CREATE DATABASE cfengtest;
+-----------+-------------------------------------------------------------------------------------------------------------------------------------+
| Database | Create Database |
+-----------+-------------------------------------------------------------------------------------------------------------------------------------+
| cfengtest | CREATE DATABASE `cfengtest` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */ |
+-----------+-------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
可以看到信息有character set，collate
- 数据库引擎 ：ENGINE
- SHOW ENGINES； 以表格的形式来查看引擎
- SHOW ENGINES \G; 以详细的信息来展示引擎
- SHOW VARIABLES LIKE “%have” Mysql支持的引擎
数据表的数据类型
合理选择数值类型，节约存储空间，提高查询效率
-
数值类型
- TINYINT 很小的整数 存储的所需字节1，4位就unsian 就是255
- SAMLLINT 小整数 2字节 8位
- MEDIUMINT 中等整数 3字节 12位
- INT 普通整数 4字节 16位
- BIGINT 大整数 8字节 32位
-
浮点数和定点数
- FLOAT 4字节
- DOUBLE 8字节
- DECIMAL(M,D) DEC 压缩严格
-
日期时间 【当值不合法的时候，会将0插入字段中】
- YEAR 日期格式为:YYYY 范围是：1901~2155 存储需要1字节
- TIME HH:MM:SS -838:59:59~838:59:59 3
- DATA YYYY-MM-SS 1000-01-01 ~ 9999-12-31 3
- DATETIME YYYY-MM-DD HH:MM:SS 1000-01-01 00:00:00 ~9999-12-31 23:59:59 8
- TIMESTAMP YYYY-MM-DD HH:MM:SS 1970-01-01 00:00:00 ~ 2038-01-19 03:14:07
-
字符串类型
- CHAR
- VARCHAR
…… 还有TEXT【非常小】 MEDUMTTEXT LONGTEXT ENUM SET
选择数据类型，应当选择占用存储空间最少的数据类型，这样不仅可以减少存储空间，还可以在数据计算的时候减轻CPU的负载
选择数据类型
MySQL提供了大量的数据类型，为了优化存储，提高数据库的性能，在任何情况下都要使用最精确的数据类型
整数和浮点数
如果不需要小数部分，就使用整数来保留； 如果需要，就使用浮点数； 如果列的值的范围是1-9999；则使用MEDIUMINT UNSIGNED 来存储；需要小数就是用FLOAT ;对于小数来说，如果精度较高，则应该使用DOUBLE
浮点数和定点数
浮点数与定点数相比的优势是，在长度一定的情况下，浮点数可以表示更大的数据。
但是浮点数容易产生误差，对于精确度要求很高的时候，建议使用定点数DECIMAL ； — 比如定义货币【账户】 如果是进行数值比较，最好使用DECIMAL类型
日期时间
日期时间如果只需要记录年份，那就是YEAR;只记录时间，就使用TIME ； 同时记录日期和时间 ，使用TIMESTAMP或者DATETIME；存储范围大的时候选择DATETIME
TIMESTAMP比DATETIME也有一个好处：当插入记录单没有但是没有TIMESTAMP时，这里就会插入当前的时间，并且这个在空间上更有效
字符串CHAR和VARCHAR
CHAR定长，效率高，但是分配不当空间浪费，兵器恩惠自动删除末尾的空格
VARCHAR 不定长，存储空间小，但是效率低，处理的速度慢 ；并且不会删除末尾的空格
所以对于速度又要求，并且长度相对固定的就是用CHAR
- 存储引擎对于选择的影响？
- 对于MyISAM引擎，特点是可以压缩，三个文件，可用的存储空间很大；所以最好使用固定长度的字符串让整个表静态化，用空间换时间
- 低于InnoDB引擎： 主要特点是安全，支持事务；空间不是很足，存储格式不分固定长和固定长，所以最好使用VARCHAR节省空间
CREATE 字段名 数据类型 约束类型 DEFAULT
DDL data definition language
之前提到过，真正的项目开发中，修改表的结构很少进行，因为修改会导致大量的数据出现问题；所以最好就要在一开始设计好数据库表
DDL包括对表的增，删，改【表结构】
修改数据库表的名称 RENAME
之前的查询操作中对于表的名称就是取别名，但是只是查询过程中，真正修改要使用ALTER语句
格式为
ALTER TABLE 旧表名 RENAME 新表名
这里可以演示一下
mysql> ALTER TABLE user_account RENAME use_account;
Query OK, 0 rows affected (0.03 sec)
mysql> SHOW TABLES;
+---------------------+
| Tables_in_cfengbase |
+---------------------+
| fruits |
| t_class |
| t_student |
| use_account |
+---------------------+
4 rows in set (0.00 sec)
修改成功，注意一定要注意是ALTER TABLE 之后加上关键字RENAME才可以
mysql> SHOW TABLES;
+---------------------+
| Tables_in_cfengbase |
+---------------------+
| fruits |
| t_class |
| t_student |
| use_account |
+---------------------+
4 rows in set (0.02 sec)
mysql> ALTER TABLE t_class RENAME class;
Query OK, 0 rows affected (0.06 sec)
修改字段的数据类型 MODIFY
modify 修改 modification 修订案
ALTER作为DDL语句，功能是很强大的，只要是我们开始创建的时候，比如创建一个表的时候，使用的是CREATE TABLE tb_name(col_name dataType Constraint Default value); 所以字段的数据类型也是可以修改的
ALTER TABLE table_name MODIFY col_name newType;
需要注意的是，这里的ALTER语句的格式就是ALTER + TABLE + 名称 +操作的关键字 + …… ；这里的修改的字段，不用加上原来的字段名或者原来的type，就当作是重新设置，不需要注意之前是什么类型
mysql> DESCRIBE class;
+-----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| classno | int | NO | PRI | NULL | |
| classname | varchar(25) | YES | | NULL | |
+-----------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)
mysql> ALTER TABLE class MODIFY classno INT(5);
Query OK, 0 rows affected, 1 warning (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 1
mysql> DESC class;
+-----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| classno | int | NO | PRI | NULL | |
| classname | varchar(25) | YES | | NULL | |
+-----------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
这里的修改是成功的，只是因为都是INT ，所以这里就没有展示出来区别
只用管新的数据类型
修改字段名 CHANGE
修改数据类型使用的关键字是MODIFY，修改表的名称是RENAME，修改表中字符按的名称使用的关键字是CHANGE
修改数据类型不需要表明原来的数据类型名，因为可以直接通过字段名定位到要修改的位置，但是这里不行，因为如果只给一个新的名称，会无法定位
ALTER TABLE table_name CHANGE pri_name new_name new_Type;
这里的数据类型必须写，如果不修改数据类型，可以写成原来的类型；因为这样子可以很方便，因为可能修改名称就代表数据类型可能发生变化
mysql> ALTER TABLE class CHANGE classno class_no INT;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
但是作为开发人员需要注意，不同哦数据类型在机器中的存储方式并不相同，修改数据类型可能影响表中已存在得记录，所以一般不要轻易修改数据类型
在数据表中添加字段 ADD
创建数据表后可能会出现字段信息不满足需要得情况，这个时候我们就要在数据表中添加新得记录，添加字段使用得关键字是ADD；DML中得添加时INSERT；DDL中得创建时CREATE；
CREATE TABLE table_name ADD col_name col_Type Contraint [FIRST| AFTER col_name];
前面就是设置字段得信息，包括创建表得时候得哪些，最后有一个特别的就是设置位置的，可以将其使用FIRST放在第一个字段，或者AFTER某个字段
mysql> ALTER TABLE class ADD class_location VARCHAR(25) NOT NULL DEFAULT 'Peking' AFTER class_no;
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> DESC class;
+----------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+-------------+------+-----+---------+-------+
| class_no | int | NO | PRI | NULL | |
| class_location | varchar(25) | NO | | Peking | |
| classname | varchar(25) | YES | | NULL | |
+----------------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)
这里的设置就包括了字段名，字段的数据类型，字段的默认值，字段的约束，字段的位置
修改字段的排序方式 MODIFY
这里修改字段的排序方式和修改字段的数据类型相同，都是对于字段的属性的修改，所以使用的关键字还是MODIFY；不是CHANGE ,CHANGE是修改的表中字段的名称；
ALTER TABLE table_name MODIFY col_name1 col_type1 FIRST| AFTER col_name2;
这里一次性只能修改一个字段的位置，可以让这个字段直接在第一个位置或者让这个字段放在某个字段的后面
mysql> ALTER TABLE class MODIFY classname VARCHAR(25) AFTER class_no;
Query OK, 0 rows affected (0.07 sec)
Records: 0 Duplicates: 0 Warnings: 0
这个样子就可以将classname的位置调整到class_no的后面 ;注意这里一点要加上字段数据类型
删除不需要的字段 DROP
这里的DROP和整体的DROP不一样，这里是对字段的操作，而那个是对于数据库或者数据表的操作
ALTER TABLE table_name DROP col_name;
这里的操作也是非常的easy
mysql> ALTER TABLE class DROP class_location;
Query OK, 0 rows affected (0.07 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> DESC class;
+-----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| class_no | int | NO | PRI | NULL | |
| classname | varchar(25) | YES | | NULL | |
+-----------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)
删除数据表或者删除数据库，DROP TABLE/DATABASE IF EXISTS table1,table2,……;
删除关联的表就是删除父表之后，删除子表中的外键字段就可
总的来说,ALTER中的操作一共
- 修改表的名称-- RENAME
- 修改表中字段的名称 ----- CREATE ，要带上字段的数据类型
- 修改字段的数据类型 ----- MODIFY
- 调整字段顺序 ---- MODIFY 加上数据类型
- 增加字段 ------ ADD 可以有位置
- 删除字段 ------- DROP
约束和数据完整性
数据库表中的数据必须是真实可信的，准确无误。对数据库表中的数据进行强制实施数据的完整性约束可保证合理性，约束之前分享过包括NOT NULL UNIQUE FK PK；还有默认
数据完整性
数据库不仅要能够存储数据，还要保证数据的正确性；SQL server对于数据完整性做出了要求
数据完整性可以分为实体完整性、域完整性和引用完整性
- 实体完整性： 同故宫表中的字段和字段的组合将表中的各个记录唯一性区分开来。实体完整性的实施方法是依靠PK 约束或者UNIQUE约束
- 域完整性： 表中的特定字段的值是有效取值，也就是虽然有数据类型，但不是说满足这个数据类型的数据都是有效的，比如出生日期不能晚于录入的时间。域完整性可以通过CHECK约束或者DEFAULT约束
- 引用完整性： 数据库表和表之间的字段值是由联系的，其中引用的字段的值应该不能超出被引用字段的值域。引用完整性的实施方法是添加FK
约束有很多类型
- 列级约束
- 表级约束
按照功能分为
- 对列的控制： PK UNIQUE
- 对列数据的控制 ： DEFAULT【这是一种约束，就是没有填入值得时候默认值】 CHECK NOT NULL
- 对表之间得关系进行控制 FK
emmm…… 又过去了????，剩下得没有弄完得接下来再弄
上一篇： 夏天凉水洗脚凉快? 夏季消暑六大误区