开发篇
CHP_01 表类型(存储引擎)的选择
插件式存储引擎是MySQL数据库最重要的特性之一.
MySQL 5.0 支持的存储引擎包括: MyISAM , InnoDB , BDB ,
MEMORY , MERGE , EXAMPLE ,
NDB Cluster , ARCHIVE , CSV , BLACKHOLE , FEDERATED
注: InnoDB 和 BDB 提供事务安全表,其他存储引擎均为非事务安全表
下面重点介绍常用的四种存储引擎
MyISAM (MySQL默认的存储引擎):
1.不支持事务,也不支持外键;
2.访问速度快;
3.对事务完整性没有要求或者以SELECT , INSERT为主的应用基本可以使用这个引擎来创建表
MyISAM在磁盘上存储成三个文件: .frm(存储表定义) ; MYD(MYData,存数数据) ; MYI(MYIndex,存储索引)
MyISAM类型的表提供修复的工具,可以用CHECK TABLE 语句来检查MyISAM表的健康,并用REPAIR TABLE语句修复一个损坏的MyISAM表
支持三种不同的存储格式: 静态(固定长度)表 ; 动态表 ; 压缩表
其中静态表是默认的存储格式 ; 优点是存储非常迅速,容易缓存,出现故障容易恢复 ; 缺点是占用的空间通常比动态表多
InnoDB(MySQL唯一支持外键的存储引擎):
提供具有提交 , 回滚 和奔溃恢复能力的事务安全 ; 对比MyISAM的存储引擎 , InnoDB写的处理效率差一些,并且会占用更多的磁盘空间以保留数据和索引.
1.自动增长列-----------auto_increment
2.外键约束-------------在创建外键约束的时候,要求父表必须有对应的索引,子表在创建外键的时候也会自动创建对应的索引
3.存储方式(InnoDB存储表和索引有两种方式):
使用共享表空间存储 , 这种存储方式创建的表的结构保存在.frm文件中
使用多表空间存储 , 这种方式创建的表的结构仍在.frm文件中,但是每个表的数据和索引单独保存在.ibd文件中
MEMORY:
使用存在内存中的内容来创建表 ; MEMORY类型的表访问速度非常快 , 默认使用HASH索引 ,但是一旦服务关闭 , 表中的数据就会丢失;
MEMORY类型的存储引擎主要用于那些内容变化不频繁的代码表,或者作为统计操作的中间结果表,便于高效的最中间结果进行分析并得到最终的统计结果
MERGE:
MERGE存储引擎是一组MyISAM表的组合.这些MyISAM表必须结构完全相同
如何选择合适的存储引擎(下面是常用的存储引擎适用环境):
MyISAM:默认的MySQL插件式存储引擎-----如果应用是以读操作和插入操作为主,只有很少的更新和删除操作,并且对事务的完整性,并发性
要求不是很高,那么选择这个存储引擎是非常合适的.MyISAM是在Web,数据仓储和其他应用环境下最常使用的存储引擎之一.
InnoDB :用于事务处理应用程序,支持外键.如果应用对事务的完整性有比较高的要求,在并发条件下,要求数据的一致性,数据操作除了插入和
查询以外,还包括很多的更新和删除操作,那么首选InnoDB,InnoDB存储引擎除了有效的降低由于删除和更新导致的锁定,还可以
确保事务的完整提交(commit)和回滚(rollback),对于类似计费系统或者财务系统等对数据准确性要求比较高德系统,InnoDB首选.
2.TEXT 和 BLOG-----------------------用来保存较大文本
MEMORY:将所有的数据存储在RAM中,在需要快速定位记录和其他类似数据的环境下,可提供极快的访问.
MEMORY的缺陷是:对表的大小有限制,,太大的表无法CACHE在内存中,,其次是要确保表的数据可以恢复,数据库异常终止后,表中
的数据是可以恢复的.MEMORY通常用于更新不太频繁的小表,用以快速得到访问结果.
MERGE :用于将一系列等同的MyISAM表以逻辑方式组合在一起,并作为一个对象引用他们;
MERGE表的优点在于可以突破对单个MyISAM表大小的限制,并且通过将不同的表分布在多个磁盘上,可以有效的改善MERGE表
的访问效率,这对于诸如数据仓储等VLDB环境十分适合.
CHP_02
选择合适的数据类型
1.CHAR
和 VARCHAR----------------用来存储字符串
CHAR属于固定长度的字符类型 ; 优点是处理速度快 , 缺点是浪费存储空间
VARCHAR属于可变长度的字符类型
BLOG用来保存二进制数据,比如照片
TEXT只能保存字符数据;比如一篇文章或者日记
注:在对表进行删除,尤其是执行大量的删除操作时,建议定期使用OPTIMIZE TABLE功能对表进行碎片整理,避免因为"空洞",导致性能问题
3.浮点数和定点数
可以使用合成的(Synthetic)索引来提高大文本字段的查询功能(这种技术只能用于精确匹配的查询)---可以使用MD5()函数生成散列值
如果需要模糊查询,MySQL提供了前缀索引
在不必要的时候,尽量避免检索大型的BLOG或TEXT值
把BLOG或TEXT列分离到单独的表中
浮点数------------含有小数部分的数值
定点数------------实际上是以字符串形式存放的
对货币等精度敏感的数据上,应该使用定点数表示或存储;
在编程中,如果用到浮点数,要特别注意误差问题,并尽量避免做浮点数比较
要注意浮点数中一些特殊值的处理
选择日期类型的原则:
(1)根据实际需要选择能够满足应用的最小存储的日期类型.如果应用只需要记录"年份",那么用一个字节来存储的YEAR类型完全可以满足,而不
需要用四个字节来存储的DATE类型,这样不仅仅能节约存储,更能够提高表的存操作效率;
(2)如果要记录年月日时分秒,并且记录的年份比较久远,那么最好使用DATETIME,而不要使用TIMESTAMP,因为TIMESTAMP表示的日期范围
比DATETIME短得多;
(3)如果记录的日期需要让不同时区的用户使用,那么最好使用TIMESTAMP,因为日期类型中只有它能够和实际时区相对应
CHP_03
字符集
1.Unicode
选择合适的字符集主要考虑因素:
而对于BTREEE索引
(1)满足应用支持语言的需求,对MySQL而言,目前就是UTF-8;
(2)如果应用中涉及已有数据的导入,就要充分考虑数据库字符集对已有数据的兼容性;
(3)如果数据库只需要支持一般中文,数据量很大,性能要求也很高,
那就应该选择双字节编码的中文字符集,比如:GBK;
(4)如果数据库需要做大量的字符运算,如比较,排序等,选择定长字符集可能更好,,引文定长字符集的处理速度比变长字符集的处理速度要快;
(5)如果所有客户端程序都支持相同的字符集,应该优先选择该字符集作为数据库字符集
查看所有可用的字符集的命令是show
character set ; 或 desc information_schema.character_sets ;
MySQL的字符集包括: 字符集(CHARACTER) 和 校对规则(COLLATION)------------两者是一对多的关系
字符集是用来定义MySQL存储字符串方式 校对规则则是定义了比较字符串的方式
MySQL的字符集和校对规则有4个级别的默认设置:服务器级 ; 数据库级 ; 表级 ; 字段级
CHP_04
索引的设计和引用
每种存储引擎对每个表至少支持16个索引,总索引长度至少为256字节;
MyISAM和InnoDB存储引擎的表默认创建的都是BTREE索引,
MyAQL目前还不支持函数索引,但是支持前缀索引,MySQL还支持全文本(FULLTEXT)索引(注:只有MyISAM存储引擎支持FULLTEXT全文索引 ),
索引总是对整个列进行的,不支持局部(前缀)索引;
默认情况下,MEMORY存储引擎使用HASH索引,但也支持BTREE索引
创建新索引的语法:
CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name
[USING index_type]
ON tb1_name (index_col_name,...)
index_col_name:
col_name [(length)] [ASC|DESC]
BTREE
和 HASH索引
HASH索引的一些特征:
(1)只用于使用
= 或 <=>操作符的等式比较;
(2)优化器不能使用HASH索引来加速ORDER
BY操作;
(3)MySQL不能确定在两个值之间大约有多少行.如果将一个MyISAM表改为HASH索引的MEMORY表,会影响一些查询的执行效率;
(4)只能使用整个关键字来搜索一行
当对索引字段进行范围查询的时候,只有BETREE索引可以通过索引访问
(3)SELECT中包含子查询;
CHP_05
视图
视图:一种虚拟存在的表;
视图相对于普通的表的优势如下:
(1)简单;
(2)安全;
(3)数据独立
视图操作:
(1)创建或者修改视图
---创建语法
CREATE [OR REPLACE] [ALGORITHM={ UNDEFINED | MERGE | TEMPTABLE }]
VIEW view_name [ (column_list) ]
AS select_statement
[WITH [CASCADED | LOCAL] CHECK OPTION]
---修改语法
ALTER [ALGORITHM={
UNDEFINED | MERGE | TEMPTABLE }]
VIEW view_name [ (column_list) ]
AS select_statement
[WITH [CASCADED | LOCAL] CHECK OPTION]
注:WITH [CASCADED
| LOCAL] CHECK OPTION]决定了是否允许更新数据使记录不再满足视图的条件
LOCAL是只要满足本视图的条件就可以更新
CASCADED(默认) 则是必须满足所有针对该视图的所有视图的条件才可以更新
以下类型的视图是不可更新的
(1)包含一下关键字的SQL语句:聚合函数(SUM
, MIN , MAX , COUNT) , DISTINCT , GROUP BY , HAVING , UNION或者UNION ALL;
(2)常量视图;
(5)FROM 一个不能更新的视图;
(6)WHERE字句的子查询引用了FROM 字句中的表
(2)删除视图
---删除语法:
DROP VIEW [IF EXISTS] view_name [ , view_name] ...... [RESTRICT | CASCADE]
CHP_06
存储过程和函数
存储过程和函数是事先经过编译并存储在数据库中的一段SQL语句的集合
存储过程和函数的区别: 函数必须有返回值,而存储过程没有
CREATE PROCEDURE sp_name ( [proc_parameter [,...]] )
删除存储过程或者函数(一次只能删除一个存储过程或者函数):
[characteristic ...] routine_body
CREATE FUNCTION sp_name ( [func_parameter [,...]] )
RETURNS type
[characteristic ...] routine_body
proc_parameter :
[IN | OUT | INOUT] param_name type
func_parameter :
param_name type
type
:
Any valid MySQL data type
characteristic
:
LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA}
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT 'string'
routine_body
:
Valid SQL procedure statement or statements
ALTER
{ PROCEDURE | FUNCTION } sp_name [characteristic ...]
characteristc
:
{ CONTAINS
SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT 'string'
调用过程的语法:
CALL sp_name ( [parameter [,...]] )
具体语法:
DROP { PROCEDURE | FUNCTION } [IF EXISTS] sp_name
光标的使用:
包括光标的声明
; OPEN ; FETCH 和 CLOSE ,语法如下:
声明光标:
DECLARE cursor_name CURSOR FOR select_statement
OPEN
光标:
OPEN cursor_name
FETCH光标:
FETCH cursor_name
INTO var_name [ , var_name ] ...
CLOSE光标:
CLOSE cursor_name
注意:变量,条件,处理程序,光标都是通过DECLARE定义的,他们之间是有先后顺序要求的.变量和条件必须在最前面声明,然后才能是光标的声
明,最后才可以是处理程序的声明.
流程控制:
1.IF语句
2.CASE语句
3.LOOP语句:实现简单的循环,具体语法如下:
[begin_label : ] LOOP
statement_list
END LOOP [end_label]
4.LEAVE语句
CHP_07
触发器
创建触发器
语法:
CREATE TRIGGER trigger_name trigger_time trigger_event
ON tbl_name FOR EACH ROW trigger_stmt
注:触发器只能创建在永久表上,不能对临时表创建
对同一个表相同触发时间的触发事件,只能定义一个触发器
其中trigger_time是触发器的触发时间,可以是BEFORE或者是AFTER,BEFORE的含义指在检查约束前触发,而AFTER指在检查约束后触发
trigger_event是触发器的触发事件,可以是INSERT,UPDATE,DELETE
对于有重复记录,需要进行UPDATE操作的INSERT,触发器触发的顺序是:
BEFORE INSERT--->BEFORE UPDATE--->AFTER UPDATE
对于没有重复记录的INSERT,就是简单的执行INSERT,触发器触发的顺序是:
BEFORE INSERT--->AFTER INSERT
删除触发器
DROP TRIGGER [Sschema_name.] trigger_name
触发器的使用:
触发器执行的语句有两点限制
(1)触发程式不能调用将数据返回客户端的存储程序,也不能使用采用CALL语句的动态SQL语句,但是允许存储程序通过参数将数据返回触发程
序.也就是存储过程或者函数通过OUT或者INOUT类型的参数将数据返回触发器是可以的,但是不能调用直接返回数据的过程.
(2)不能在触发器中使用以显示或隐式方式开始或结束事务的语句,如START
TRANSACTION , COMMIT 或 ROLLBACK.
CHP_08
事务控制和锁定语句
1.LOCK
TABLE 和 UNLOCK TABLE
LOCK TABLES : 可用于锁定当前线程的表,如果表被其他线程锁定,则当前线程会等待,直到可以获取所有锁定为止.
UNLOCK
TABLES :可以释放当前线程获得的任何锁定,
语法如下:
LOCK TABLES
tbl_name [AS Alias] { READ [LOCAL] | [LOW PRIORITY] WRITE }
[ , tbl_name
[AS Alias] { READ [LOCAL] | [LOW PRIORITY] WRITE }]
...
UNLOCK TABLES
事务控制:
通过SET
AUTOCOMMIT , START TRANSACTION , COMMIT 和 ROLLBACK等语句支持本地事务
具体语法:
START TRANSACTION | BEGIN [WORK]
COMMIT [WORK] [AND [NO] CHAIN] [ [NO] RELEASE]
ROLLBACK [WORK] [WORK]
[AND [NO] CHAIN] [ [NO] RELEASE]
SET AUTOCOMMIT = { 0 | 1 }
默认MySQL是自动提交(AUTOCOMMIT)的,如果需要明确的Commit和RollBack来提交和回滚事务,需要明确的事务控制命令来开始事务.
START
TRANSACTION 或 BEGIN语句可以开始一项新的事务;
COMMIT和ROLLBACK用来提交和回滚事务;
CHAIN和RELEASE子句分别用来定义在事务提交或者回滚之后的操作,CHAIN会立即启动一个新事务,并且和刚才的事务具有相同的隔离级别,RELEASE则会断开和客户端的连接
SET
AUTOCOMMIT可以修改当前连接的提交方式,如果设置0,则设置之后的所有事务都需要通过明确的命令进行提交或者回滚.
CHP_09
SQL中的安全问题更新
CHP_10
SQL Mode 及相关问题
扫一扫
1759
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
