第5章-MySQL索引与完整性约束课件.ppt
《第5章-MySQL索引与完整性约束课件.ppt》由会员分享,可在线阅读,更多相关《第5章-MySQL索引与完整性约束课件.ppt(36页珍藏版)》请在淘文阁 - 分享文档赚钱的网站上搜索。
1、第第5章章 MySQL索引与完整性约束索引与完整性约束MySQL索引索引5.15.2MySQL索引索引创建创建MySQL数据完整性约束数据完整性约束5.35.1 MySQL索引索引5.1.1 索引及作用索引及作用1.索引索引索引是根据表中一列或若干列按照一定顺序建立的列值与记录行之间的对应关系表。在列上创建了索引之后,查找数据时可以直接根据该列上的索引找到对应行的位置,从而快速地找到数据。例如,如果用户创建了xs表中学号列的索引,MySQL将在索引中排序学号列,对于索引中的每一项,MySQL在内部为它保存一个数据文件中实际记录所在位置的“指针”。因此,如果要查找学号为“081241”的学生信息
2、,MySQL能在学号列的索引中找到“081241”的值,然后直接转到数据文件中相应的行,准确地返回该行的数据。5.1.1 索引及作用索引及作用2索引作用索引作用在数据库系统中建立索引主要有以下作用:l快速读取数据;l保证数据记录的唯一性;l实现表与表之间的参照完整性;l在使用GROUP BY、ORDER BY子句进行数据检索时,利用索引可减少排序和分组的时间。5.1.1 索引及作用索引及作用3.MySQL索引索引在MySQL 5.6中,所有的MySQL列类型都能被索引,但要注意以下几点:l只有当表类型为MyISAM、InnoDB或BDB时,才可以向有NULL、BLOB或TEXT列中添加索引。l
3、一个表最多可有16个索引。最大索引长度是256个字节。l对于CHAR和VARCHAR列,可以索引列的前缀。这样索引的速度更快并且比索引整个列需要较少的磁盘空间。lMySQL能在多个列上创建索引。索引可以由最多15个列组成(在CHAR和VARCHAR列上,也可以使用列的前缀作为索引的部分)。5.1.2 索引的分类索引的分类1.BTREE索引索引目前大部分MySQL索引都是以B树(BTREE)方式存储的,索引类型分成下列几个。(1)普通索引()普通索引(INDEX)这是最基本的索引类型,它没有唯一性之类的限制。创建普通索引的关键字是INDEX。(2)唯一性索引()唯一性索引(UNIQUE)这种索引
4、和前面的普通索引基本相同,但有一个区别:索引列的所有值都只能出现一次,即必须是唯一的。创建唯一性索引的关键字是UNIQUE。(3)主键()主键(PRIMARY KEY)主键是一种唯一性索引,它必须指定为“PRIMARY KEY”。主键一般在创建表的时候指定,也可以通过修改表的方式加入主键。但是每个表只能有一个主键。(4)全文索引()全文索引(FULLTEXT)MySQL支持全文检索和全文索引。在MySQL中,全文索引的索引类型为FULLTEXT。5.1.2 索引的分类索引的分类2哈希索引(哈希索引(HASH)当表类型为MEMORY或HEAP时,除了BTREE索引,MySQL还支持哈希索引(HA
5、SH)。使用哈希索引,不需要建立树结构,但是所有的值都保存在一个列表中,这个列表指向相关页和行。当根据一个值获取一个特定的行时,哈希索引非常快。5.2 MySQL索引索引创建创建1CREATE INDEX语句创建语句创建使用CREATE INDEX语句可以在一个已有表上创建索引,一个表可以创建多个索引。语法格式:语法格式:CREATE UNIQUE|FULLTEXT|SPATIAL INDEX 索引名 索引类型 ON 表名(索引列名.)索引选项.索引列名=:列名(长度)ASC|DESC说明:说明:lUNIQUE|FULLTEXT|SPATIAL:UNIQUE表示创建的是唯一性索引;FULLTE
6、XT表示创建全文索引;SPATIAL表示为空间索引,可以用来索引几何数据类型的列(本书不讨论这种索引)。l索引名:索引在一个表中名称必须是唯一的。l索引类型:MySQL支持的索引类型有BTREE和HASH。l索引列名:创建索引的列名后的长度表示该列前面创建索引字符个数。这可使索引文件大大减小,从而节省磁盘空间。5.2 MySQL索引索引创建创建【例5.1】根据xs表的学号列上的前5个字符建立一个升序索引xh_xs。use xscjcreate index xh_xs on xs(学号(5)asc);也可以在一个索引的定义中包含多个列,中间用逗号隔开,但它们属于同一个表,这样的索引叫作复合索引。
7、【例5.2】在xs_kc表的学号列和课程号列上建立一个复合索引xskc_in。create index xskc_in on xs_kc(学号,课程号);5.2 MySQL索引索引创建创建2ALTER TABLE语句创建语句创建前面章节介绍了如何使用ALTER TABLE语句修改表,其中也包括向表中添加索引。语法格式如下:语法格式如下:ALTER IGNORE TABLE 表名.|ADD INDEX|KEY 索引名 /*添加索引*/索引类型(索引列名.)索引选项.|ADD CONSTRAINT symbol PRIMARY KEY/*添加主键*/索引类型(索引列名.)索引选项.|ADD CON
8、STRAINT symbolUNIQUE INDEX|KEY 索引名索引类型(索引列名.)索引选项./*添加唯一性索引*/|ADD FULLTEXT INDEX|KEY 索引名(索引列名.)索引选项./*添加全文索引*/|ADD SPATIAL INDEX|KEY 索引名(索引列名.)索引选项./*添加空间索引*/|ADD CONSTRAINT symbol FOREIGN KEY 索引名(索引列名.)参照性定义 /*添加外键*/|DISABLE KEYS|ENABLE KEYS5.2 MySQL索引索引创建创建【例5.3】在xs表的姓名列上创建一个非唯一的索引。alter table xsa
9、dd index xs_xm using btree(姓名);【例5.4】以xs表为例(假设表中主键未定),创建这样的索引,以加速表的检索速度:alter table xsadd index mark(出生日期,性别);这个例子创建了一个复合索引。5.2 MySQL索引索引创建创建如果想要查看表中创建的索引的情况,可以使用SHOW INDEX FROM 表名语句,例如:show index from xs;系统显示已创建的索引信息如下:5.2 MySQL索引索引创建创建3在建立表时创建索引在建立表时创建索引在前两种情况下,索引都是在表建立之后创建的。索引也可以在创建表时一起创建。在创建表的CR
10、EATE TABLE语句中可以包含索引的定义。语法格式:语法格式:CREATE TEMPORARY TABLE IF NOT EXISTS 表名 (列定义 ,.|索引定义)表选项 select语句;索引定义索引定义=:CONSTRAINT symbolPRIMARY KEY 索引类型(索引列名.)/*主键*/|INDEX|KEY 索引名 索引类型(索引列名.)/*索引*/|CONSTRAINT symbol UNIQUE INDEX|KEY 索引名 索引类型(索引列名.)/*唯一性索引*/|FULLTEXT|SPATIAL INDEX|KEY 索引名(索引列名.)/*全文索引*/|CONSTR
11、AINT symbol FOREIGN KEY 索引名(索引列名.)参照性定义 /*外键*/5.2 MySQL索引索引创建创建【例5.5】在mytest数据库中创建成绩(cj)表,学号和课程号的联合主键,并在成绩列上创建索引。use mytestcreate table xs_kc(学号 char(6)not null,课程号 char(3)not null,成绩 tinyint(1),学分 tinyint(1),primary key(学号,课程号),index cj(成绩);5.2 MySQL索引索引创建创建4删除索引删除索引当一个索引不再需要的时候,可以用DROP INDEX语句或ALT
12、ER TABLE语句删除它。(1)使用)使用DROP INDEX删除删除语法格式:语法格式:DROP INDEX 索引名 ON 表名(2)使用)使用ALTER TABLE删除删除语法格式:语法格式:ALTER IGNORE TABLE 表名.|DROP PRIMARY KEY/*删除主键*/|DROP INDEX|KEY 索引名/*删除索引*/|DROP FOREIGN KEY fk_symbol/*删除外键*/5.2 MySQL索引索引创建创建【例5.6】删除xs表上的mark索引。alter table xsdrop index mark;读者可使用SHOW INDEX FROM 表名语句
13、查看执行结果。5.3 MySQL数据完整性约束数据完整性约束5.3.1 主键约束主键约束可以用两种方式定义主键,作为列或表的完整性约束。作为列的完整性约束时,只需在列定义的时候加上关键字PRIMARY KEY,这个在3.2.1节中已作过介绍。作为表的完整性约束时,需要在语句最后加上一条PRIMARY KEY(col_name,)语句。【例5.7】创建表xs1,将姓名定义为主键。create table xs1(学号 varchar(6)null,姓名 varchar(8)not null primary key,出生日期 datetime);5.3.1 主键约束主键约束当表中的主键为复合主键时
14、,只能定义为表的完整性约束。【例5.8】创建course表来记录每门课程的学生学号、姓名、课程号、学分和毕业日期。其中学号、课程号和毕业日期构成复合主键。create table course(学号 varchar(6)not null,姓名 varchar(8)not null,毕业日期date not null,课程号varchar(3),学分tinyint,primary key(学号,课程号,毕业日期);5.3.1 主键约束主键约束原则上,任何列或者列的组合都可以充当一个主键。但是主键列必须遵守一些规则。这些规则源自于关系模型理论和MySQL所制定的以下规则:(1)每个表只能定义一个主
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- MySQL 索引 完整性 约束 课件
限制150内