数据库总结

     由于最近要参加面试，不得不把雪藏很久的数据库知识拿出来翻一翻，我想吧，数据库作为一个基础也是核心的技能是每一个程序员都要会使用的，因为我是一个刚入坑的小程序猿一枚，所分享的东西只是数据库知识中的毛皮，望大牛勿喷，

---

1) 数据库的显示、创建、删除

显示数据库：show database;

创建数据库：CREATE DATABASE DBNAME ;

删除数据库：DROP DATABASE DBNAME ;

选中数据库：USE DBNAME ;

DBNAME:数据库名

TABLENAME:数据库表名

---

2) 数据表的显示、创建、删除

显示数据表：SHOW TABLE TABLENAME    （注明：操作此语句前首先必须得选择数据库）

显示表结构：DESC TABLENAME 或者  DESCRIBE TABLENAME ;

创建数据表：CREATE TABLE TABLENAME(字段设定表的列)

删除数据表：DELETE FROM TABLENAME ;

清除数据表中的数据：

DELETE FROM TABLENAME  或者    TRUNCATE TABLE TABLENAME

注明：不带WHERE参数的DELETE语句可以删除表中所有内容，使用TRUNCATA TABLE 也可以清除表中所有数据

从效率上来讲 TRUNCATE 要比DELETE 快，但 TRUNCATE 删除后不记录日志并不可恢复数据。

添加数据表中的数据：

INSERT INTO TABLENAME (列1,列2,列3) VALUES(值1,值2,值3);

添加数据表中的主键：

ALTER TABLE TABLENAME() ADD PRIMARY KEY (主键列名);

删除数据表中的主键：

ALTER TABLE TABLENAME DROP PRIMARY KEY (主键列名);

---

创建索引：

CREATE [UNIQUE] INDEX IDXNAME ON TABLENAME(列名);

删除索引：

DROP INDEX IDXNAME;

注明：索引是不可更改的，想更改必须删除索引重新建。

---

索引：

索引是以表列为基础的数据库对象，保存着表中排序的索引列，并记录了索引列在数据表中的物理存储位置实现了表中数据的逻辑排序。

索引的作用：

1. 通过创建唯一性索引可以保证数据库表中每一行数据的唯一性。

2. 创建索引可以大大加快数据的索引速度，这是创建索引的最重要的原因。

3. 可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。

4. 在使用分组和排序子句进行数据检索时，同样可以减少查询中分组和排序的时间。

5. 在查询过程中使用优化隐藏器，提高系统的性能。

索引的缺点：

1. 创建索引和维护索引需要耗费时间，并随着数据量的增加而增加。

2. 索引需要占用物理空间，每一个索引需要占用一定的物理空间，如果建立聚簇索引那么需要的空间更大。

3. 当对表中的数据进行增删改查时，索引也需要动态维护，那么就降低了数据的维护速度。

---

创建视图：

CREATE VIEW VIEWNAME AS SELECT (SQL语句);

删除视图：

DROP VIEW VIEWNAME;

视图：

视图是一种数据库对象，它是一个虚拟表，并不代表任何我物理数据，只是用来查看数据的窗口。

视图的优点：

1. 数据的安全性，通过定义不同的视图使用户只能看到与自己有关的数据。

2. 查询简单化，将复杂的查询语句建立成一个视图，可以使用户只针对视图进行查询。

3. 逻辑数据独立性，当构成视图的基本表需要修改时，只需要修改视图中的子查询部分，而基于视图的部分不需要改变。

视图的缺点：

1. 性能，需要耗费一定时间。

2. 修改限制，有 UNIQUE 等集合操作符的视图、有 GROUP BY 子句的视图、有 AVG/SUM/MAX等聚合函数时、使用 DISTINCT 关键字的视图、连接表的视图都是不可修改的。

3. 创建限制， 视图查询语句不能含有 ORDER BY 、 COMPURER 、COMPURER BY 、INFO 关键字。

基本查询语句：

选择查询：SELECT * FROM TABLE WHERE 值;

更新操作：UPDATA TABLENAME SET 原值  = 现值

排序操作：SELECT * FROM TABLENAME ORDER BY 列名 DESC;

分页查询操作：

oracle： SELECT * FROM(SELECT A.*, ROWNUM RN FROM(SELECT * FROM TABLENAME) A) WHERE RN BETWEEN 范围1 AND 范围2

MySQL ： SELECT * FROM TABLE (查询条件) ORDER BY ID LIMIT ?,?;

---

存储过程：

存储过程是一组为完整特定功能的SQL语句集，经编译后存储在数据库中，用户通过指定存储过程的名字给出参数来执行它。

存储过程优点：

1. 存储过程只在创造时进行编译，并通过了语法检查和性能优化，一次编译多次执行，使用存储过程可以提高数据库的执行速度。

2. 允许组件式编程，存储过程可以设置参数，可以根据传入参数的不同重复使用同一个存储过程，从而提高代码的优化率和可读性

3. 减少网络流量

4. 提高系统安全性，建立特定的存储过程避免非授权用户对数据的访问，包中数据的安全。

存储过程的缺点：

1. 移植性差，依赖数据库厂商，难以移植。

2. 难以试调、维护，当业务逻辑大的时候封装性不够，难以试调维护。

3. 服务器不能负载均衡，复杂的应用程序在存储过程中实现业务逻辑的负担在数据库服务器上，没有增加负载、

存储过程分类：

1. 系统存储过程

2. 本地存储过程

3. 临时存储过程

4. 远程存储过程

5. 扩展存储过程

---

事物：

事物是单个的工作单元，

事物的属性：

1. 原子性，事物必须是原子工作单元，对其数据修改，要么全都执行，要么全部不执行。

2. 一致性，事物在完成时必须使所有的数据都保持一致，在相关的数据库中所有额规则必须应用于事物的修改，确保数据 完整性，事物结束时数据必须是争取的。

3. 隔离性，由并发是事物所做的修改必须与任何其他并发事物所做的修改隔离。

4. 持久性，事物完成之后对系统的影响是永久性的。

事物的分类：

1. 显示事物，

2. 隐性事物，

3. 自动提交事物

触发器：触发器是特定时间出现时自动执行的存储过程

---

触发器的功能

1.自动生成数据

2.自定义复杂的安全权限

3.提供审计和日志记录

4.启用复杂的业务逻辑

---

SQL语句执行的过程

1.客户端把语句发送给服务器端执行

2.服务器端对语句进行语句解析，

查询高速缓存，服务器端先查找高速缓存区，如果高速缓存区存在这个SQL语句则直接执行这个SQL语句。

语句合法性检查，高速缓存区未能查到这个SQL语句则服务器进程就会对这个SQL语句进行合法性检查，如果不符合规则，则把错误信息反馈给客户端，在合法性检查中，不会对SQL语句中包含的表名、列名进行查询。

语义检查，若SQL语句符合合法性，则服务器端进程会对语句中的字段、表内容进行检查，

获取对象解析锁，当语法语义都通过检查后，系统会对查询的对象加锁，保证数据的一致性，防止在查询过程中其他用户对这个对象结构发生改变

数据访问权的核对，当语法、语义都通过后服务器端进程检查所连接的用户是否有访问权。

确定最佳执行计划，当签名条件都符合之后，服务器进程会根据一定的规则对SQL语句进行优化，确定最佳执行计划之后将这条SQL语句存储到数据高速缓存区，如果下次遇到这个语句时就会忽略语法语义权限检查的步骤，而直接执行SQL语句，提高SQL语句的处理效率

3 .语句的执行

若选择的数据块已经被读取到数据缓存区中，服务器进程直接会把这个数据传递到客户端，不需要再去数据库文件中查询数据。

若数据不再缓冲区，服务器进程将从数据库文件中查询相关数据，并吧这些数据放入数据缓存区中。

4.提取数据

---

SQL语句中函数 、 关键字 、 排序等执行顺序：

from子句返回处理结果集

where子句排除不满足搜索条件的行

group by 子句将选定的行到group by 子句中各个唯一值的组中

选择列表中指定的聚合函数可以计算各组的汇总值

having子句排除不满足搜索条件的行

计算所有的表达式

使用order by 对结果集进行排序

查找你搜索的字段

SQL优化：

select中避免 *

减少访问数据库的次数

用where子句替换having子句

通过内部函数提高SQL效率

使用表的别名

用exists 代替in 、 用not exists代替 not in

尽量多使用commit

删除重复记录

整合简单无关联的数据库

用truncate 替代delete

---