注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

风雨启示录

欢迎光临我的网店优优我心:http://uumyheart.taobao.com

 
 
 

日志

 
 

MySQL:MySQL学习笔记  

2010-08-20 19:32:10|  分类: 数据库学习笔记 |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |
 

2010-8-6

参考数据《深入浅出MySQL数据库开发、优化与管理维护》唐汉明·人民邮电出版社

我的进数据库的密码:webdev

事务性数据库:transactional database

InnoDB 事务性存储引擎

MyISAM非事务性存储引擎

-------------------------------------------------------------------------------------------------------------------------------

第一部分: 基础篇

1、打开或关闭mysql服务的比较简洁的方式:

    在命令提示符里输入:net stop mysql/net start mysql

2、SQL: structure query language(结构化查询语言)

    它是使用关系模型的数据库应用语言

    SQL分类:

(1)DDL(data definition language)语言:数据定义语言,这些语句定义了不同的数据段、数据库、表、列、索引等数据库对象的定义。常用的关键字主要包括:create、drop、alter等。

(2)DML(data manipulation language)语言:数据操纵语演,用于添加、删除、跟新和查询数据库记录,并检查数据完整性。常用的关键字主要包括insert、delete、update和select。

(3)DCL(data control language)语句:数据控制语句,用于控制不同数据段直接的许可和访问级别的语句,这些语句定义了数据库、表、字段、用户的访问权限和安全级别。主要的语句关键字包括grant、revoke等。

3、DDL语句

(1)创建数据库

Create database (dbname);

(2)选择数据库

Use database (dbname);

(3)删除数据库

Drop database (dbname);

-------------------------------------------------------------------------------

2010-8-7

(4)创建表

Create table tablename(column_1 column_type constraints, column_2 column_type constraints,…, column_n column_type constraints);

(5)查看表的信息

Desc tablename;

Desc输出的信息不够全面,为了查看更全面的表定义信息,有时需要通过查看创建表的SQL语句来得到,可以用如下的命令来实现:

Show create talbe tablename \G;

(6)删除表

Drop table tablename;

(7)查询表行数

Select count(1) from tablename;

(8)指定某一行开始读记录

Select * from tablename order by column_name limit offset_start(起始行数), rowcount(读几行记录);

 

(9)子查询

子查询主要用到的关键字包括in , not in ,= , != ,exists, not exists等

 

第三章 MySQL支持的数据类型

 

第二部分 开发篇

------------------------------------------------------------------------------

第七章 表类型(存储引擎)的选择

插件式存储引擎室MySQL数据库最重要的特性之一。

MySQL 5.0支持的存储引擎包括:MyISAM, InnoDB, BDB, MEMORY, MERGE, EXAMPLE, NDB Cluster, ARCHIVE, CSV, BLACKHOLE, FEDERATED等,其中InnoDB和BDB提事务安全表,其他的存储引擎都是非事务安全表。

1、MyISAM

它是MySQL的默认存储引擎,不支持事务,也不支持外键,其优势是访问速度快,对事务完

整性没有要求或者以select insert为主的应用基本上都可以使用这个引擎来创建表。

--------------------------------------------------------------------------------------------------------------------------------

2010-8-8

每个MyISAM 在磁盘上存储为3个文件,其文件名都很表名相同,但扩展名分别是:

.frm —— 存储表定义;

.MYD —— MYData 存储数据;

.MYI —— MYIndex 存储索引。

数据文件和索引文件可以放置在不用的目录,平均分配IO,获得更快的速度。

MyISAM还支持3中不用的存储格式:

静态表(固定长度);

动态表;

压缩表;

其中,静态表是默认的存储格式。每个记录都是固定长度的,这种存储方式的优点是存储非

常迅速,容易缓存,出现故障容易恢复,缺点是占用的空间通常比动态表多。

静态表的数据在存储的时候会根据长度来补充空格,但是在应用返回的时候并不会得到这些

空格,这些空格在返回给应用前就已经去掉。

但是需要特别注意的是:如果需要保存的内容后面本来就带有空格,那么在返回结果的时候

也被去掉了,开发人员在编写程序的时候要特别注意,因为静态表式默认的存储格式,如果

意识到这一点,就会丢失掉尾部的空格。(插入记录后面的空格都会去掉,前面的保留)

 

动态表中包含变长字段,记录不是固定长度的,这样存储的优点是占用的空间相对较少,但是频繁的更新删除记录会产生碎片,需要定期执行OPTIMIZE TABLE语句或myisamchk –r命令来改善性能。

 

压缩表由myisampack工具创建,占据非常小的磁盘空间。

 

 

2、InnoDB

InnoDB存储引擎提供了具有提交、回滚和崩溃恢复能力的事物安全,但是比MyISAM的存储引擎,InnoDB写的处理效率要差一些,且会占用更多的磁盘空间以保留数据和索引。

(1)自动增长列

可以通过 ”alter table *** auto_increment = n” 语句强制设置自动增长列的初始值,默认从1开始。

可以使用LAST_INSERT_ID()当前线程最后插入记录使用的值:

SELECT LASE_INSERT_ID();

对于InnoDB 表,自动增长列必须是索引,也必须是组合索引的第一列,但是对于MyISAM表,自动增长列是按照前面几列进行排序后递增的。

 

(2)外键约束

MySQL支持外键约束的只有InnoDB,在创建外键的时候,要求父表必须有对应的索引,字表在创建外键的时候也会自动创建对应的索引。

在创建索引时,可以指定在删除,更新父表时,对字表进行的相应操作,包括RESTRICT、CASCADE、SET NULL和NO ACTION。其中:

RESTRICT和NO ACTION作用相同,是指限制在字表有关联记录的情况下,父表不能更新;

CASCADE表示父表在更新或删除时,更新或删除字表对应的记录;

SET NULL则表示父表在更新或删除时,字表对应的字段被SET NULL;

选择后两种方式要谨慎,可能会因为错误的操作 ,导致数据丢失。

当某个表被其他表创建了外键参照,那么该表的对应索引或者主键禁止被删除。

 

3、MEMORY

MEMORY存储引擎使用存在内存中的内容来创建表,每个MEMORY表只实际对应一个磁盘文件,格式为.frm。MEMORY类型的表访问速度非常快,因为他的数据时放在内存当中的,并且默认使用HASH索引,但是一旦服务关闭,表中的数据就会丢失。

MEMORY存储引擎主要用于那些内容变化不频繁的代码表,或者作为统计操作的中间结果表,便于高效的对中间结果进行分析并得到最终的统计结果。

对MEMORY表进行更新操作要谨慎,因为数据并没有实际写入磁盘中,所以一定要对下次重新启动服务后如何获得这些修改后的数据有多考虑。

 

4、MERGE

MERGE存储引擎是一组MyISAM表的组合,这些 MyISAM 表必须结构完全相同,MERGE表本身没有数据,对MERGE类型表可以进行查询、删除、更新等操作,实际上是对内部实际的MyISAM表进行的。

对于MERGE表的插入操作,是通过INSERT_METHOD子句定义插入的表,可以有3个不同的值,使用FIRST或LAST是作用在第一或最后一个表上,不定义或者定义为NO表示不能对这个MERGE表执行插入操作。

可以对MERGE进行DROP操作,这只是删除MERGE表的定义,对内部的表没有任何的影响。

MERGE并不能智能的将记录写到对应的表中,而分区表是可以的(分区功能将在5.1版中推出),通常我们使用MERGE表来透明的对多个表进行查询和更新操作。

 

第八章 选择合适的数据类型

1、CHAR 和VARCHAR

CHAR和VARCHAR类型相似,都用来存储字符串,但它们保存和检索的方式不同,CHAR属于固定长度的字符类型,而VARCHAR属于可变长度的字符类型。

由于CHAR是固定长度的,所以它的处理速度比VARCHAR要快的多,但是其缺点是浪费空间。

在MySQL中,不同的存储引擎对CHAR和VARCHAR的使用原则也不同,简单的概括如下:

(1)MyISAM:建议使用固定长度的数据代替可变长度的数据列;

(2)MEMORY:目前都使用固定长度的数据行存储,因此无论使用什么,都是作为CHAR类型处理的;

(3)InnoDB:建议使用VARCHAR类型,

 

2、TEXT和BLOB

当要保存交大文本时,通常会选择使用TEXT或BLOB,二者之间的主要差别是BLOB能用来保存二进制数据,比如照片;而TEXT只能保存字符数据,

(1)TEXT和BLOB值会引起一些性能问题,特别在执行了大量的删除操作时。

删除操作会在数据表中留下很大的“空洞”,以后填入这些“空洞”的记录在插入的性能上会有影响。为了提高性能,建议定期使用OPTIMIZE TABLE 功能对这类表进行碎片整理,避免因为“空洞”导致性能问题。

(2)可以使用合成的索引来提高大文本字段的查询性能。

简单的说,合成索引就是大文本字段的内容简历一个散列值,并把这个值存储在单独列中,接下来就可以通过检索散列值找到数据行了。但是,要注意这种技术只能用于精确匹配的查询(散列值对于类似<或>=等范围搜索符是没有用处的)可以使用MD5(), SHA1()或CRC32()函数来生成散列值。

(3)在不必要的时候,避免检索大型的BLOB或TEXT值。

(4)把BLOB货TEXT列分离到单独的表中。

 

3、浮点数和定点数

浮点数一般用于表示含有小数部分的数值,当一个字段被定为浮点类型后,如果插入数据的精度超过该列定义的实际精度,则插入值会被四舍五入到实际定义的精度值,然后插入,四舍五入的过程不会报错,float double(或real)用来表示浮点数。

定点数不同于浮点数 ,定点数实际上是以字符串形势存放的,所以定点数可以更精确地保存数据。Decimal(numberic)用来定义定点数。

Decimal(M,D)的含义:

M:该值一共显示M位数字(整数+小数);

D:小数多少位;

浮点型存储时会产生一定的误差,这是浮点型特有的问题,因此在精度要求比较高的应用中,要使用定点数而不是浮点数来保存数据。

另外浮点数比较也存在着一定的问题。

总之,关于浮点数和定点数的应用,考虑到以下原则即可:

(1)浮点数存在误差问题

(2)对货币等精度比较高的数据,应该用定点数

(3)尽量避免做浮点数比较

(4)要注意浮点数中一些特殊值的处理。

 

 

第九章 字符集

-----------------------------------------------------------------------------------------------------------------------------

2010-8-9

第十章 索引的设计和使用

1、设计索引的原则

(1)索引列不一定是所要选择的列。换句话说,最适合做索引的列式出现在WHERE子句中的列,或链接子句中的列,而不是出现在SELECT关键字后面的选择列表中的列。

(2)使用唯一索引。索引的列的基数越大,索引的效果就越好。

(3)使用短索引。如果对字符串列索引,应该指定前缀长度,只要有可能就应该这样做。

(4)利用最左前缀;

(5)不要过度索引。

 

第十一章 视图

视图是一种虚拟存在的表,视图并不在数据库中实际存在,行和列数据来自定义视图的查询中使用的表,并且在使用视图时动态的生成。

视图对于普通的表的优势主要是:

(1)简单:完全不需要关心后面表的结构、关联条件和筛选条件,对于用户来说已经是过滤好的复合条件的结果集。

(2)安全:使用视图的用户只能访问它们被允许查询的表,对表的权限管理并不能限制到某个行某个列,但是通过视图可以简单的实现;

(3)数据独立:一旦视图的结构确定了,可以屏蔽表结构变化对用户的影响,源表增加列对视图没有影响;源表修改列名,则可以通过修改视图来解决,不会造成对访问者的影响;

视图的操作包括:创建、修改、删除和查看视图定义

MySQL视图的定义有一些限制,例如,在FROM后面不能包含子查询。如果视图是从其他

数据库迁移过来的,那么可能需要一些改动,可以将子查询的内容先定义成一个个视图,然

后对该视图再创建一个视图就可以实现类似的功能。

 

第十二章 存储过程和函数

Delimiter //  /*自定义结束符

过程的参数类型默认为IN

会话变量:@x  相当于临时变量

游标的特性:

         只读性:可以取值,不能对其进行更新

         不可以滚动

         不允许在已打开的游标的表执行update事务。

 

2010-8-20

1、如何处理MyISAM存储引擎的表损坏

一张表损坏的症状通常是查询意外中断并且能看到下述错误:

(1)“tb_name.frm”被锁定不能更改

(2)不能找到文件“tb_name.MYI”(R=Errcode: nnn)

(3)文件意外结束

(4)记录文件被损坏

(5)从表处理器得到错误nnn。

解决方法:

方法一、

(使用MySQL自带的myisamchk工具进行修复:

命令格式:myisamchk –r tablename

如果不行,则使用:myisamchk –o tablename可以进行更安全的修复;

【注】此方法不适合在WINDOWS下执行

方法二、

(1)先检查表是否有损坏

Check table table_name1, table_name2 ….[option]…

Option: {quick| fast| medium| extended |changed}

(2)再修复

Repair [ LOCAL | NO_WRITE_TO_BINLOG ] /

table table_name1, table_name2… [qucik]  [extended]  [use_frm]

 

2、MyISAM 表超过4GB无法访问的问题

可以用下面的命令对数据文件的最大size进行扩充

Alter table table_name MAX_ROWS = 1000000000 AVG_ROW_LENGTH = 15000;

 

3、数据目录磁盘空间不足的问题

(1)对于MyISAM表

建表时可分别指定数据目录和索引目录存储到不同的磁盘空间:

Create table t1( id int )

Data directory = ‘/var/lib/mysql/data’

Index directoty = ‘/var/lib/mysql/index’

使用过程中不可以随时对这些路径进行变更

(2)对InnoDB表

由于数据文件和索引文件时存放在一起的,所以无法将它们分离,当磁盘空间不足时,可以增加一个新的数据文件,具体实现方式是在参数:innodb_data_file_path中增加此文件,路径写为新磁盘的绝对路径。

 

  评论这张
 
阅读(1075)| 评论(0)
推荐 转载

历史上的今天

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2017