【数据库】数据库实践 - hippowc/hippowc.github.io GitHub Wiki

数据库常识

索引

普通索引(非唯一索引)

普通索引（由关键字KEY或INDEX定义的索引）的唯一任务是加快对数据的访问速度。因此，应该只为那些最经常出现在查询条件（WHEREcolumn=）或排序条件（ORDERBYcolumn）中的数据列创建索引。只要有可能，就应该选择一个数据最整齐、最紧凑的数据列（如一个整数类型的数据列）来创建索引。

唯一索引

唯一索引是这样一种索引，它通过确保表中没有两个数据行具有完全相同的键值来帮助维护数据完整性。为表定义了唯一索引之后，每当在该索引内添加或更改键时就会强制执行唯一性。此强制执行包括插入、更新、装入、导入和设置完整性以命名一些键。除了强制数据值的唯一性以外，唯一索引还可用来提高查询处理期间检索数据的性能。

主键索引

主键索引是唯一索引的特殊类型。数据库表通常有一列或列组合，其值用来唯一标识表中的每一行。该列称为表的主键。主键索引不能为空。

约束

  • 默认值约束(default)
  • 非空约束(not null)
  • 唯一值约束(unique)
  • 主键约束(primary key)
  • 自增长约束(auto_increment)
  • 外键约束(foreign key)

联合约束／索引

像主键和唯一性索引（约束），可以声明多个列作为联合索引或者约束

另外仅仅针对查询来讲，联合索引，Mysql会从左到右的使用索引中的字段，一个查询可以只使用索引中的一部份，但只能是最左侧部分。例如索引是key index （a,b,c）。 可以支持a | a,b| a,b,c 3种组合进行查找，但不支持 b,c进行查找 .当最左侧字段是常量引用时，索引就十分有效。

数据库隔离级别

  1. read uncommitted。一个事务中可以读取到另一个事务未提交的数据。问题：当另一个事务回滚的时候可能会脏读。
  2. read committed。一个事务只能读取另一个事务提交之后的数据。同一个事务中修改的数据是可以立即读取到的。问题：不可重复读；在读取数据时如果另外一个事务修改单条数据，可能会多次读取的数据不一致。但是这个并不一定是一个问题。
  3. repeatable read。保证一个事务多次读取的数据是一致的。针对单条数据，对于整个数据库的操作还是无法保证，譬如：增加删除数据。问题：幻读，读取数据条数不相同。但是在mysql的测试中，读取事务并没有锁住当前数据，该数据仍然是可以修改的；从这个角度来看，不确定这个隔离级别是否优于第二个级别。
  4. Serializable。整个数据库的事务操作都是串行的，效率低下。

一些mysql的例子：

select @@global.tx_isolation; 查看全局隔离级别

select @@session.tx_isolation; 查看会话隔离级别

set session transaction isolation level read uncommitted; 设置会话隔离级别

start transaction; 开启事务

commit; 提交事务

详细了解：http://www.cnblogs.com/zhoujinyi/p/3437475.html

常见数据库

SQLite

SQLite是开源嵌入式数据库，SQLite可以很好的支持关系型数据库所具备的一些基本特征，如标准SQL语法、事务、数据表和索引等。SQLite的主要优势在于灵巧、快速和可靠性高。SQLite的设计者们为了达到这一目标，在功能上作出了很多关键性的取舍，与此同时，也失去了一些对RDBMS关键性功能的支持，如高并发、细粒度访问控制(如行级锁)、丰富的内置函数、存储过程和复杂的SQL语句等。

缺点：

  • 如果你有多个客户端需要同时访问数据库中的数据，特别是他们之间的数据操作是需要通过网络传输来完成的。在这种情况下，不应该选择SQLite。由于SQLite的数据管理机制更多的依赖于OS的文件系统，因此在这种操作下其效率较低。也就是说最好是单机本机操作。

  • 受限于操作系统的文件系统，在处理大数据量时，其效率较低。

  • SQLite只是提供了表级锁，没有提供行级锁。在这种同步机制下，并发性能很难高效。

优点：

  • SQLite本身并不需要任何初始化配置文件，也没有安装和卸载的过程。当然也不存在服务器实例的启动和停止。在使用的过程中，也无需创建用户和划分权限。在系统出现灾难时，如电源问题、主机问题等，对于SQLite而言，不需要做任何操作。
  • SQLite没有单独的服务器进程，以供客户端程序访问并提供相关的服务。SQLite作为一种嵌入式数据库，其运行环境与主程序位于同一进程空间，因此它们之间的通信完全是进程内通信，而相比于进程间通信，其效率更高。
  • SQLite的数据库被存放在文件系统的单一磁盘文件内，只要有权限便可随意访问和拷贝，这样带来的主要好处是便于携带和共享。
  • 平台无关性
  • 弱类型。大多数支持静态类型的数据库不同的是，SQLite中的数据类型被视为数值的一个属性。因此对于一个数据表列而言，即便在声明该表时给出了该列的类型，我们在插入数据时仍然可以插入任意类型

基本操作

在命令行输入 sqlite3 即可进入sqlite命令界面，也可以使用sqlite3 xxx.db直接创建数据库，后面的文件名就是数据库名称（一个普通文件）

sqlite命令以 分号 结尾，sqlite有很多 .命令，一些基本的如下：

.show，查看sqlite的配置

.header on, .mode column：格式化输出，表格样式

.tables 查看所有表

.schema 表名称，可以查看这个表的基本信息

创建表

create table xxx (
  column1 datatype PRIMARY KEY AUTOINCREMENT,
  column2 text not null,
  ...
);

这时id信息是自动增长的，在插入的时候id表示为null即可。

常见约束

约束的格式是放在字段类型的后面如：column datatype not null

NOT NULL 约束：确保某列不能有 NULL 值。

DEFAULT 约束：当某列没有指定值时，为该列提供默认值。

UNIQUE 约束：确保某列中的所有值是不同的。

PRIMARY Key 约束：唯一标识数据库表中的各行/记录。

CHECK 约束：CHECK 约束确保某列中的所有值满足一定条件。

常见索引：

索引有助于加快 SELECT 查询和 WHERE 子句，但它会减慢使用 UPDATE 和 INSERT 语句时的数据输入。索引可以创建或删除，但不会影响数据。

创建索引： CREATE INDEX index_name ON table_name (column);

还可以创建唯一索引：CREATE UNIQUE INDEX index_name on table_name (column_name);

删除索引： DROP INDEX index_name;