数据库基础知识和数据库优化

数据库基础知识和数据库优化

---

---

一、数据库基础知识

1.什么是数据库

数据库是按照数据结构来组织、存储和管理数据的仓库
1.数据库（Database，DB）：将大量数据保存起来，通过计算机加工而成的可以进行高效访问的数据集合。如：大型-银行存储的信息，小型-电话簿。

2.数据库管理系统（Batabase Management System，DBMS）：用来管理数据库的计算机系统。

3.关系型数据库（Relational Database，RDB）：目前应用最广泛的数据库。

4.结构化查询语言（Structured Query Language，SQL）：专门用于操作 RDB。

5.常见的 5 种关系型数据库管理系统（Relational Database Management System，RDBMS）：

①Oracle Database：甲骨文公司

②SQL Server：微软公司

③DB2：IBM 公司

④PostgreSQL：开源

⑤MySQL：开源

数据库是一个数据的集合。

其本质是一个文件系统，以文件的方式,将数据保存在电脑上

优点： 海量数据存储，提供不错的查询效率 ，数据可以永久保存 ，方便存储和管理数据 ，使用统一的方式操作数据库

缺点 ：占用资源(重型武器) ，有些数据库需要付费

表:

表是包含数据库中所有数据的数据库对象，由行和列组成（二维表格模型，就类似于Sheet），用于组织和存储数据。

字段:

表中每一列称为一个字段，字段有自己的属性，如字段类型、字段大小等。其中，字段类型是字段最重要的属性，它决定了字段能够存储哪种数据。

和Excel不同，这里针对列（字段要求比较严格），该列存储什么类型数据，长度多少都应该提前（建表的时候）进行定义

索引:

索引是一个单独的、物理的数据库结构。它是依赖于表建立，在数据库中使用索引，无须对整个表进行扫描，就可以找到需要的数据。

视图:

视图是从一张或多张表中导出的表（也称虚拟表），是用户查看数据表中数据的一种方式。

2. SQL分类

我们学的主要也就是这些

1） 数据查询语言（DQL:Data Query Language）重点！！！：

其语句，也称为“数据检索语句”，用以从表中获得数据，确定数据怎样在应用程序给出。保留字SELECT是DQL（也是所有SQL）用得最多的动词，其他DQL常用的保留字有WHERE，ORDER BY，GROUP BY和HAVING。这些DQL保留字常与其他类型的SQL语句一起使用。
where:使用是筛选条件
order by :排序 正排desc 倒排asc 默认正排
group by:进行单个字段分组或者多个字段分组
having: 作用类似于where，差别是where过滤行，having过滤组
使用顺序：where、having、group by、order by、limit

专门用于查询数据：代表指令为select

2） 数据操作语言：

其语句包括动词INSERT，UPDATE和DELETE。它们分别用于添加，修改和删除表中的行。也称为动作查询语言。

专门用于写数据：代表指令为insert，update和delete

3）事务处理语言（TPL）：

它的语句能确保被DML语句影响的表的所有行及时得以更新。TPL语句包括BEGIN TRANSACTION，COMMIT和ROLLBACK。（不是所有的关系型数据库都提供事务安全处理）
原子性（atomicity)

一个事务必须被视为一个不可分割的最小单元，整个事务中的所有操作要么 全部提交成功，要么全部失败，对于一个事务来说，不能只执行其中的一部分操作。

一致性（consistency)

一致性是指事务将数据库从一种一致性转换到另外一种一致性状态，在事务开始之前和事务结束之后数据库中数据的完整性没有被破坏。这意味着所有相关的数据规则都必须应用于事务的修改,以保持数据的完整性。

隔离性（Isolation）

一个事务的执行不能被其他事务干扰。即一个事务内部的操作及使用的数据 对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。数据库系统提供一定的隔离机制,保证事务在不受外部并发操作影响的“独立”环境执行。这意味着事务处理过程中的中间状态对外部是不可见的,反之亦然

持久性（Durability）

一旦事务提交，则其所做的修改就会永久保存到数据库中。此时即使系统崩溃，已经提交的修改数据也不会丢失。MySQL事务的持久性是通过redo log 来实现的。redo log也是InnoDB存储引擎特有的。具体实现方式是：当发生数据修改（增、删、改）的时候，InnoDB引擎会先将记录写到redo log中，并更新内存，此时更新就算完成了。同时InnoDB引擎会在合适的时机将记录刷到磁盘中。redo log是物理日志，记录的是在某个数据页做了什么修改，而不是SQL语句的形式。它有固定大小，是循环写的方式记录日志，空间用完后会覆盖之前的日志。undo log保证原子性，redo log保证持久性，设置隔离级别，保证并发事务进行的时候，保证数据一致性。

事务并发出现的问题:
脏读（Dirty Reads）
事务A读取到了事务B已经修改但尚未提交的数据，还在这个数据基础上做了操作。此时，如果B事务回滚，A读取的数据无效，不符合一致性要求

不可重复读 （Non-Repeatable Reads）
事务 A 多次读取同一数据，事务 B 在事务A多次读取的过程中，对数据作了更新并提交，导致事务A多次读取同一数据时，结果 不一致。事务A内部的相同查询语句在不同时刻读出的结果不一致，不符合隔离性

幻读（Phantom Reads）
一个事务按相同的查询条件重新读取以前检索过的数据，却发现其他事务插入了满足其查询条件的新数据，这种现象就称为“幻读”。 一句话：事务A读取到了事务B提交的新增数据，不符合隔离性

SQL标准中的四种隔离级别“脏读”、“不可重复读”和“幻读”,其实都是数据库读一致性问题,必须由数据库提供一定的事务隔离机制来解决。READ UNCOMMITTED：未提交读。
READ COMMITTED：已提交读。
REPEATABLE READ：可重复读。
SERIALIZABLE：可串行化

专门用于事务安全处理：transaction

4）数据控制语言（DCL）：

它的语句通过GRANT或REVOKE获得许可，确定单个用户和用户组对数据库对象的访问。某些RDBMS可用GRANT或REVOKE控制对表单个列的访问。

专门用于权限管理：代表指令为grant和revoke

5） 数据定义语言（DDL）：

其语句包括动词CREATE和DROP。在数据库中创建新表或删除表（CREAT TABLE 或 DROP TABLE）；为表加入索引等。DDL包括许多与人数据库目录中获得数据有关的保留字。它也是动作查询的一部分。

专门用于结构管理：代表指令create和drop（alter）

3.SQL 概要

1.SQL 语句：用关键字、表名和列名等组合而成的一条语句。

2. 3 种 SQL 语句种类：

（1）DDL（Data Definition Language，数据定义语言）：创建、删除或修改数据库以及数据库中的表等对象。

①CREATE：创建数据库和表等对象

②DROP：删除数据库和表等对象

③ALTER：修改数据库和表等对象

（2）DML（Data Manipulation Language，数据操作语言）：查询或修改表中的记录。

①SELECT：查询表中的数据

②INSERT：向表中插入数据

③UPDATE：修改表中的数据

④DELETE：删除表中的数据

（3）DCL（Data Control Language，数据控制语言）：确认或取消对数据库中的数据变更的执行操作，以及对用户的操作数据库中的对象权限进行设定。

①COMMIT：确认对数据库中的数据进行的变更

②ROLLBACK：取消对数据库中的数据进行的变更

③GRANT：赋予用户的操作权限

④REVOKE：取消用户的操作权限

【备注】DML 使用最频繁。

3.SQL 语句书写规范：

①以分号（；）结尾；

②大小写不敏感，不区分关键字的大小写；【注意】插入到表中的数据是区分大小写的，如“HI”、“Hi”和“hi”都不同。(怎么设置区分大小写)

③该系列随笔将采用“关键字大写，表名和列名的首字母大写”的格式。

④单词使用半角空格或换行符隔开

4.常数的书写方式：

字符串、日期：用单引号括起来（‘），如’Hello’，‘2016-09-24’。

数字：直接书写，不用加单引号，如：5。
创建数据库与表
　　1.数据库创建：

– 语法：CREATE DATABASE <数据库名称>
CREATE DATABASE Shop – 创建名为 Shop 的数据库

2.表的创建

– 语法：
– CREATE TABLE <表名>
– (
– <列名1> <数据类型> <约束>,
– <列名1> <数据类型> <约束>,
– …
– <表约束1>,
– <表约束2>,
– …
– )

– 创建名为 sales_return的表
create table sales_return
(
sr_id bigint(20) not null auto_increment comment ‘ID’,
order_id varchar(25) not null comment ‘订单号’,
order_parent_id varchar(25) not null comment ‘父订单号’,
order_status char(1) default ‘0’ comment ‘退货状态（0待确认 1已确认）’,
materials_code varchar(50) default null comment ‘物料编码’,
materials_name varchar(50) default null comment ‘物料名称’,
sr_num int default 0 comment ‘退货数量’,
sales_return_reason varchar(300) default null comment ‘退货原因’,
supplier_name varchar(50) default ‘’ comment ‘供应商名称’,-- 供应商username 直发送指定用户名,招标送空串(“”)
create_by varchar(64) default ‘’ comment ‘创建者’,
create_time datetime comment ‘创建时间’,
update_by varchar(64) default ‘’ comment ‘更新者’,
update_time datetime comment ‘更新时间’,
remark varchar(300) default ‘’ comment ‘备注’,
primary key (sr_id)
) engine = innodb
default charset = utf8
collate = utf8_bin comment = ‘退货表’;

3.命名规则：

①只能使用半角英文字母、数字、下划线（_）作为数据库、表和列的名称；

②规范要求命名以半角英文字符开头；

③名称不能重复。

4.数据类型的指定：声明该列的数据类型，是一种约束。

5.数据类型介绍：

①INTEGER：整型，意味不能存储小数；

②CHAR：字符串型，如 CHAR（10）和 CHAR（100）中的括号表名该列可以存储的字符串的最大长度。它是“定长字符串”，如 CHAR(8) 表示在列中插入 ‘abc’ 时会自动保存成 'abc '（后面5个半角空格）的形式；

③VARCHAR：字符串型，类似 CHAR，不同的是它是 “可变长字符串”，如 VARCHAR(8) 在插入 ‘abc’，保存的就是字符串 ‘abc’；

④DATE：日期类型；

… …

6.约束：

①非空约束：NULL 是空白（无记录）的意思的关键字，NOT NULL 表示必须输入的约束。

②主键约束：主键是可以确定一行数据的列，一般通过它取特定行的数据，它是唯一的，不允许重复。

… …

4.更新和删除表

1.删除表

– 语法：DROP TABLE <表名>
DROP TABLE Shohin; – 删除名为 Shohin 的表
　　【注意】被删的表，无法恢复。

2.更新表

（1）添加列

– 语法：ALTER TABLE <表名> ADD <列名> <类型>;
ALTER TABLE Shohin ADD Shohin_mei_Kana VARCHAR(100); – 在 Shohin 中添加名为 Shohin_mei_Kana 类型为 VARCHAR(100) 的列
　　　　（2）删除列

– 语法：ALTER TABLE <表名> DROP COLUMN <列名>;
ALTER TABLE Shohin DROP COLUMN Shohin_mei_Kana; – 删除 Shohin 表中名为 Shohin_mei_Kana 的列
　　【注意】表定义变更后无法恢复。

---

总结

提示：这里对文章进行总结：