Mysql数据库

关系型数据库——MySQL部分概念

存储过程

个人感觉很像是用SQL语句封装出的函数

一些预编译的SQL语句，执行效率较高
在一定程度上保障数据安全

视图

视图是一种虚拟的表，具有和物理表相同的功能。可以对视图进行增，改，查，操作，试图通常是有一个表或者多个表的行或列的子集。对视图的修改不影响基本表。它使得我们获取数据更容易，相比多表查询。

只暴露部分字段给访问者，所以就建一个虚表，就是视图。
查询的数据来源于不同的表，而查询者希望以统一的方式查询，这样也可以建立一个视图，把多个表查询结果联合起来，查询者只需要直接从视图中获取数据，不必考虑数据来源于不同表所带来的差异

索引

是对数据库表中一个或多个列的值进行排序的结构，帮助提高数据访问速度的数据库对象
索引加快了检索速度，但是降低了插入、删除、更新等维护任务的速度，大部分的更新操作同时需要更新索引
索引可以避免全表扫描，多数查询可仅扫描少量索引页及数据页
对于非聚集索引，有些查询甚至可以不访问数据页
聚集索引可以避免数据插入操作集中于表的最后一个数据页
某些情况下索引还可以避免排序操作

优点

大大加快检索速度;
创建唯一性索引，保证数据库表中每一行数据的唯一性;
加速表和表之间的连接;
在使用分组和排序子句进行数据检索时，可以显著减少查询中分组和排序的时间。
- 大大减少服务器需要扫描的数据量
- 帮助服务器避免排序和临时表
- 将所及I/O变为顺序I/O

缺点

索引需要占用数据表以外的物理存储空间
创建索引和维护索引要花费一定的时间
当对表进行更新操作时，索引需要被重建，这样降低了数据的维护速度。

存储

一条索引记录中包含的基本信息包括：键值（即你定义索引时指定的所有字段的值）+逻辑指针（指向数据页或者另一索引页）
当为空表创建索引时，数据库系统将分配一个索引页，在插入前保持空状态；当有数据插入时加入一行索引记录；当根结点满时，进行分裂：
- 创建两个儿子节点
- 将当前根结点近似分为两半，分别写入儿子节点
- 将根结点加上指针指向新的两个儿子节点
数据被删除导致索引中只包含一条索引记录时，该记录可能会被移至临近的索引页中，索引合并

注意

不要索引数据量不大的表，对于小表来讲，表扫描的成本并不高。
主键并非都是聚合索引，使用了聚合索引的主键查询速度更快
建立适当的聚合索引，才能更好的提高查询的效率
一个复合索引包含A,B,C,D四列，则A为“引导列”；复合索引的引导列应该是查询最频繁的列
索引有助于提高检索性能，但过多或不当的索引也会导致系统低效。因为用户在表中每加进一个索引，数据库就要做更多的工作。过多的索引甚至会导致索引碎片。所以说，我们要建立一个“适当”的索引体系，特别是对聚合索引的创建，更应精益求精，才能使数据库性能更好的发挥
不要设置过多的索引，在没有聚集索引的表中，最大可以设置249个非聚集索引，过多的索引首先会带来更大的磁盘空间，而且在数据发生修改时，对索引的维护是特别消耗性能的
索引通常查询数据比全表扫描要快，但需要空间来存储，也需要定期维护。不必要的索引反而会使查询反应时间变慢.使用索引查询不一定能提高查询性能

事务

并发控制的基本单位
一个操作序列，要么都执行，要么都不执行，结束时能保持数据一致性
定义事务的三条语句
- BEGIN TRANSACTION
- COMMIT
- ROLLBACK
用户在事务（transaction）内可以声明（declare）被称为保存点（savepoint）的标记。保存点将一个大事务划分为较小的片断。

触发器

触发器是一中特殊的存储过程，主要是通过事件来触发而被执行的。
它可以强化约束，来维护数据的完整性和一致性，可以跟踪数据库内的操作从而不允许未经许可的更新和变化。
可以联级运算。如，某表上的触发器上包含对另一个表的数据操作，而该操作又会导致该表触发器被触发。
一个数据表可以有多个触发器，但一个触发器只能对应一个表

drop、delete与truncate

不再需要一张表及其数据的时候，用drop
想删除部分数据行时候，用delete，并且带上where子句
保留表而删除所有数据的时候用truncate, 再插入时自增长id又从1开始
当表被TRUNCATE 后，这个表和索引所占用的空间会恢复到初始大小，而DELETE操作不会减少表或索引所占用的空间。DROP语句将表所占用的空间全释放掉。
速度上，drop > truncate > delete
delete语句为DML（data maintain Language),这个操作会被放到 rollback segment中,事务提交后才生效。如果有相应的 tigger,执行的时候将被触发。
truncate、drop是DLL（data define language),操作立即生效，原数据不放到 rollback segment中，不能回滚。(如果一不小心把一个表truncate掉，也是可以恢复的，只是不能通过rollback来恢复)
对于外键（foreignkey ）约束引用的表，不能使用 truncate table，而应使用不带 where 子句的 delete 语句。
truncatetable不能用于参与了索引视图的表。
drop语句将删除表的结构所依赖的约束，触发器，索引，依赖于该表的存储过程/函数将保留,但是变为invalid状态。

超键、候选键、主键、外键

超键：在关系中能唯一标识元组的属性集称为关系模式的超键。一个属性可以为作为一个超键，多个属性组合在一起也可以作为一个超键。超键包含候选键和主键。
候选键：是最小超键，即没有冗余元素（对数据唯一性不起作用的列元素）的超键。
主键：数据库表中对储存数据对象予以唯一和完整标识的数据列或属性的组合。一个数据列只能有一个主键，且主键的取值不能缺失，即不能为空值（Null）。
外键：在一个表中存在的另一个表的主键称此表的外键。

常用命令

CREATE TABLE Student( 
ID NUMBER PRIMARY KEY, 
NAME VARCHAR2(50) NOT NULL);//建表 

CREATE VIEW view_name AS 
Select * FROM Table_name;//建视图 

Create UNIQUE INDEX index_name ON TableName(col_name);//建索引 

INSERT INTO tablename {column1,column2,…} values(exp1,exp2,…);//插入 

INSERT INTO Viewname {column1,column2,…} values(exp1,exp2,…);//插入视图实际影响表 

UPDATE tablename SET name='zang 3' condition;//更新数据 

DELETE FROM Tablename WHERE condition;//删除 

GRANT (Select,delete,…) ON (对象) TO USER_NAME [WITH GRANT OPTION];//授权 

REVOKE (权限表) ON(对象) FROM USER_NAME [WITH REVOKE OPTION] //撤权

SQL vs NoSQL

Mysql数据库

存储过程

视图

索引

优点

缺点

存储

分类

注意

事务

触发器

drop、delete与truncate

超键、候选键、主键、外键

常用命令

相关文章