Python数据库、MySQL存储引擎、使用分区表、更改表结构、获取数据库元数据

Python数据库随笔

:1. 数据库基本操作

1.1 创建数据库

- CREATE DATABASE test; #创建数据库

- GRANT ALL ON test.* to user(s); #为指定用户（或所有用户）提升权限 1.2 使用数据库

- USE test; 1.3 删除数据库

- DROP DATABASE test; 1.4 创建表

- CREATE TABLE users(login VARCHAR(8),userid INT , projid INT);

ＭｙＳＱＬ存储引擎

查看存储引擎：ＳＨＯＷ　ＥＮＧＩＮＥＳ；

ＩnnoDB:是ＭySQL的默认存储引擎，

其表在执行提交和回滚操作时是事务安全的，可以通过创建保存点（savepoint)来实现部分回滚． 在系统崩溃后可以直接恢复； 外键和引用完整性支持，包括级联删除和更新； 基于行级别的锁定和多版本化，使得在执行同时包含有检索和更新操作的组合条件查询时，可以表现出很好的并发性能； 从5.6开始支持全文搜索和ＦＵＬＬＴＥＸＴ索引；

MyISAM:
- 当保存连续相似的字符串索引值时，它会对键进行压缩，此外，ＭｙＩＳＡＭ还可以压缩相似的数字索引值，因为这些数值都是按高字节优先的方式来保存的，启动数字压缩功能将PACK_KEYS=1;
- 与其他存储引擎相比，它为AUTO_INCREMENT列提供了更多的功能；
- 每个ＭｙＩＳＡＭ表都有一个标志，它会在执行表检查操作时被设置，ＭｙＩＳＡＭ表还有一个标志，用于表明该表在上次使用后是否被正常关闭，如果服务器意外宕机或机器崩溃，那么可用这个标志来判断表是否需要检查和修复，如果象进行自动检查，则需要在启动服务器时，将mysiam_recover_options系统变量设置为一个包含有ＦＯＲＣＥ选项的值，
- 支持全文检索和ＦＵＬＬＴＥＸＴ索引；
- 支持空间数据类型和ＳＰＡＴＩＡＬ索引；

MEMORY存储引擎
　　ＭＥＭＯＲＹ存储引擎会把表存储在内存中，并且这些表的行长度固定不变；
在某种意义上来讲，ＭＥＭＯＲＹ表是临时性的，当服务器掉电时，其内容也会消失，也就是说，ＭＥＭＥＯＲＹ表在服务器重启之后依然存在，只是他们的内容为空，不过相对于临时表来说，ＭＥＭＯＲＹ表对其他客户端来说是可见的；
　　ＭＥＭＯＲＹ表的以下几个特点使他们比其他类型的表更易处理，因此速度也更快；
　
- 默认情况下，ＭＥＭＯＲＹ表使用的是散列索引，这种索引对＂相等比较＂非常快，对＂范围比较＂非常慢，因此，散列索引只适合于相等运算符＂＝＂和＂＜＞＂的操作；不适合于＜及＞操作及ＯＲＤＥＲ　ＢＹ子句里；
- 为更便于处理，存储在ＭＥＭＯＲＹ表里的行使用长度固定不变的格式．这意味着不能使用ＢＬＯＢ和ＴＥＸＴ，ＶＡＲＣＨＡＲ是一种长度可变的类型，但由于在ＭｙＳＱＬ内部，它被当作是一种长度固定不变的ＣＨＡＲ类型，所以可以在ＭＥＭＯＲＹ表里使用．

ＮＤＢ存储引擎
　　ＮＤＢ存储引擎是ＭｙＳＱＬ的集群存储引擎，对于这个存储引擎，ＭｙＳＱＬ服务器实际上变成了一个其他进程的集群客户端，集群节点会处理彼此间的通信，从而在内存中实现对表的管理，为了实现冗余，这些表会在集群进程之间被复制，内存存储提供了高性能，而集群机制则提供了高可用性，因为即使某个节点发生了故障，整个系统页不会崩溃．

ＡＲＣＨＩＶＥ存储引擎
　　该引擎提供了数据归档存储功能，主要适合于大批量存储那些＂写了就不会再更改＂的行，因此，它所支持的ＳＱＬ语句很有限，ＩＮＳＥＲＴ和ＳＥＬＥＣＴ可以而ＤＥＬＥＴＥ和ＵＰＤＡＴＥ不可以使用，为了节省空间，在存储时会对行进行压缩，而在检索时再对他们进行解压，ＡＵＣＨＩＶＥ表可以包含一个带索引的ＡＵＴＯ＿ＩＮＣＲＥＭＥＮＴ列，但其他列不能被索引；

ＢＬＡＣＫＨＯＬＥ存储引擎
　　该引擎所创建的表，其写操作会被忽略，读操作是返回空内容．

ＣＳＶ存储引擎
　　该引擎在存储数据时，会用逗号进行分割，对于每个表，它会在数据库目录里创建一个.CSV文件，这是一种普通文本文件，其中每个表行占用一个文本行，ＣＳＶ存储引擎不支持索引．
ＦＥＤＲＥＡＴＥＤ存储引擎
　　该引擎提供了访问由其他ＭｙＳＱＬ服务器进行管理的表的能力，也就是说，ＦＥＤＥＲＡＴＥＤ表的内容实际上位于远程，当创建ＦＥＤＲＥＡＴＥＤ表时，需要指定一台运行着其他服务器的主机，并提供该服务器上的某个账户的用户名和密码，需要访问ＦＥＤＲＥＡＴＥＤ表时，本地服务器将使用这个账户连接那台远程服务器．
ＭＥＲＧＥ存储引擎
　　该引擎提供了一种把多个ＭｙＩＳＡＭ表合并为一个逻辑单元的手段，查询一个ＭＥＲＧＥ表时，相当于查询其所有的成员表，这种做法的好处之一是，可以突破文件系统对单个ＭｙＩＳＡＭ表的最大尺寸所设定的限制，分区表可以替换ＭＥＲＧＥ表，并且不会受限于ＭｙＩＳＡＭ表．

暂时性表的创建
　　临时表会在客户端会话结束后自动删除，但最好还是在用完之后显性的删除以节省内存；
　　临时表只对创建该表的客户端可见；
　　临时表的名字可以与某个已有的永久表的名字相同，此时永久表会隐藏起来，如果删除的话也会先删除临时表，而永久表会显现出来；
　　使用临时表需注意：
　　 1. 如果客户端程序在与服务器的连接意外断开时自动连接，那么在重新连接之后，断开前的临时表将不复存在，如果之前用临时表来＂隐藏＂某个与之同名的永久表，那么这个永久表将成为现在使用的表；
　　 ２. 由于临时表只在创建它们的会话里是可见的，因此他们与连接池机制一起使用时没有什么用处；
　　 ３．如果使用连接池或永久性连接，那么当应用程序终止时，临时表与ＭｙＳＱＬ服务器之间的连接不一定被关闭，那些机制可能会保持处于连接状态，以供其他客户端使用．这表示当应用程序终止后不能假设临时表会自动消失．

根据其他表或查询结果来创建表
ＭｙＳＱＬ有两种方法来创建表副本

 - ＣＲＥＡＴＥ　ＴＡＢＬＥ　．．．　ＬＩＫＥ会根据原有表创建一个新表，该表是原有表的一个空副本，它会把原有表的结构丝毫不差的复制过来，而且会保留各列的所有属性，索引结构也照样会被复制，不过，由于这个表是空的，因此还需要一条数据填充语句（ＩＮＳＥＲＴ　ＩＮＴＯ　．．．　ＳＥＬＥＣＴ），请注意，该语句不能根据原有表的列子集创建出新表，也不能使用除原有表以外的任何其他表里的列．
 CREATE TABLE new_tbl_name LIKE tbl_name;
 INSERT INTO new_tbl_name SELECT * FROM tbl_name;
 - ＣＲＥＡＴＥ　ＴＡＢＬＥ　．．．　ＳＥＬＥＣＴ，默认情况下，这条语句不会复制所有的列属性，如ＡＵＴＯ＿ＩＮＣＲＥＭＥＮＴ，也不会把原有表里任何索引复制过去，因为结果集不会自己索引．不过该语句可完成创建表并填充数据这两个操作．
  CREATE TABLE new_tbl_name SELECT * FROM tbl_name;
  由于该条语句的不足在于不会把原有数据的所有特征全部复制到新表的结构中去，因此在某些场合可以采用在语句的ＳＥＬＥＣＴ部分使用ＣＡＳＴ（）函数的方式，在新表里强制使用某些特定的属性，如：
   CREATE  TABLE mytbl SELECT 
     CAST(1 AS UNSIGEND) AS i,
     CAST(CURTIME() AS TIME) AS t,
     CAST(PI() AS DECIMAL(10,5)) AS d;
     允许强制转换的类型包括：ＢＩＮＡＲＹ，ＣＨＡＲ，ＤＡＴＥ，ＤＡＴＥＴＩＭＥ，ＴＩＭＥ，ＳＩＧＮＥＤ，ＳＩＧＮＥＤ　ＩＮＴＥＧＥＲ，ＵＮＳＩＧＮＥＤ　ＩＮＴＥＧＥＲ　和　ＤＥＣＩＭＡＬ．

使用分区表

分区表的两个好处为：

表存储可以分布在多个设备上，可以通过Ｉ／Ｏ并行机制来缩短访问时间．

优化器可以把本地化搜索限定在某个特定的分区，或者并行搜索多个分区

分区函数的分区依据：

根据范围来分区，适用这种方式的情形是，行包含的是一些可划分为多个离散区间的值域，如日期，收入水平或重量．

根据列表来分区，适用这种方式的情形是，每个分区都分别对应于某些明确的列表值，如邮政编码，电话号码区号，按地理区域划分出的各实体的编号． 根据散列值来分区，适用这种方式的情形是，根据从行键计算出的散列值，把行分布到各个分区．
分区函数必须具有确定性，这样，在分配行时，才会把相同的输入值分配到同一个分区，有些函数不适合这个规则，如ＲＡＮＤ（）和ＮＯＷ（） 索引

ＭｙＳＱＬ提供了多种灵活的索引创建方法

可以对单个列或多个列建立索引，多列索引也被称作复合索引； 索引可以只包含唯一值，也可以包含重复值； 可以为同一个表创建多个索引，帮助优化对表的不同类型的查询．

对于除ＥＮＵＭ和ＳＥＴ以外的字符串类型，可以利用列的前缀创建索引，这种方法的优点在于对列的前缀而不是整个列进行索引，可以让索引变的更小，访问速度更快．
ＭｙＳＱＬ中支持索引的引擎包括InnoDB,MyISAM,MEMORY.
ＭｙＳＱＬ可以创建多种类型的索引：

唯一索引

常规索引（非唯一性） ＦＵＬＬＴＥＸＴ索引，这种索引适用于ＭyISAM和5.6.4版本以上的InnoDB SPATIAL索引，这种索引只适用于包含空间值的ＭｙＩＳＡＭ表． ＨＡＳＨ索引，这是ＭＥＭＯＲＹ表的默认索引类型，不过可以通过创建ＢＴＲＥＥ索引来改写它．
添加索引的方式：

ALTER TABLE tbl_name ADD INDEX index_name(index_columns);
CREATE INDEX index_name ON tbl_name(index_columns);

删除索引的方法

DROP INDEX index_name ON tbl_name;
DROP INDEX `PRIMARY` ON tbl_name;
ALTER TABLE tbl_name DROP INDEX index_name;

更改表结构 更改列的数据类型
如果在mytbl表里,列i的数据类型为SMALLINT UNSIGNED，如果要把它改为MEDIUMINT UNSIGNED ，则：

ALTER TABLE mytbl MODIFY i MEDIUMINT UNSIGNED;
ALTER TABLE mytbl CHANGE i i MEDIUMINT UNSIGNED;

为什么在使用CHANGE语句时要把i写两遍呢，因为ＣＨＡＮＧＥ语句在修改数据类型的同时可以将该列重命名．

如果要修改列的字符集：

ALTER TABLE t MODIFY c CHAR(20) CHARACTER SET ucs2;

修改存储引擎时应注意不同引擎之间功能是否兼容：

包含有ＢＬＯＢ列的表不能转换成MEMORY引擎； 包含有外键的InnoDB表不能转换为其他引擎；

如果希望某个表的内容在服务器重新启动后依然存在，那么不能使用ＭＥＭＯＲＹ引擎；

重新命名表

ALTER TABLE tbl_name RENAME TO new_tbl_name;
RENAME TABLE tbl1_name TO new_tbl1_name,tbl2_name TO new_tbl2_name,...;

获取数据库元数据 ＳＨＯＷ　命令

使用通配符：

SHOW COLUMNS FROM student LIKE  's%';
#表示在student表中打印s开头的所有列

使用标识符：

SHOW COLUMNS FROM student WHERE `Key` = `PRI`;
#表示在student表中打印主键列

列