数据库管理与应用第章表.ppt

单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,*,第六章,表,1,课程目标,创建表,修改表,重命名表,删除表,2,1.CREATE TABLE,创建新表,使用,CREATE TABLE,可以创建一个新表，这一语句可以分为两种写法,:,一种用于直接创建新表,;,另一种用于参照其它表的定义创建一个新表。,3,1.CREATE TABLE,创建新表,以下写法用于直接创建新表：,CREATE TEMPORARY TABLE,IF NOT EXISTS,表名,(,列定义,1,，列定义,2,，,.,，,表级约束,),表选项,select,表达式,其中列定义的基本写法为：,列名 列的类型,4,1.CREATE TABLE,创建新表,以下代码用于创建一个名为,“,student,”,的表，该表有两列：,ID,：,INT,类型，用于存储学生的编号；,NAME,：,CHAR,（,8,）类型，用于存储学生的姓名。,表的创建代码如下：,mysql create table student,-(-ID int,-NAME char(8),-);,Query OK,0 rows affected(0.09 sec),5,1.CREATE TABLE,创建新表,MySQL,将每一个表都单独保存在一个,“,.frm,”,格式的文件中，该文件存储在相应的数据库目录中。表的存储引擎也可能会创建其它文件，如对于,MyISAM,类型的表，存储引擎会自动创建数据和索引文件。,6,1.CREATE TABLE,创建新表,创建临时表,在创建表格时，可以使用,TEMPORARY,关键词使当前建立的表为临时表。临时表只能在当前连接中使用，当连接关闭时，临时表也被自动删除。这意味着两个不同的连接可以创建两个名称相同的临时表，这两个临时表不会互相冲突，也不与原有的同名的非临时表冲突。,7,1.CREATE TABLE,创建新表,创建临时表,如果临时表的名称与数据库中现有的非临时表名称相同，则非临时表会被隐藏，直到该临时表被删除为止。,创建临时表要求当前帐户拥有,CREATE TEMPORARY TABLES,权限。,8,1.CREATE TABLE,创建新表,避免创建已经存在的表,如果表已存在，则使用关键词,IF NOT EXISTS,可以防止发生错误。注意，原有表的结构与,CREATE TABLE,语句中表示的表的结构是否相同，这一点没有验证。注释：如果在,CREATE TABLE.SELECT,语句中使用,IF NOT EXISTS,，则不论表是否已存在，由,SELECT,部分选择的记录都会被插入。,9,1.CREATE TABLE,创建新表,是否允许列为空值,在列定义中，可以加入以下选项以指明某一列是否允许出现空值：,NULL,：允许出现空值；,NOT NULL,：不允许出现空值。,10,1.CREATE TABLE,创建新表,以下代码在创建,“,student,”,表时，不允许学生的编号（,ID,）和姓名（,NAME,）为空，但允许住址（,ADDR,）为空。,mysql create table student,-(-ID int NOT NULL,-NAME char(8)NOT NULL,-ADDR char(60)NULL,-);,Query OK,0 rows affected(0.09 sec),11,1.CREATE TABLE,创建新表,指定列的默认值,在列定义中，可以加入以下选项以指明某一列的默认值：,DEFAULT,默认值,12,1.CREATE TABLE,创建新表,默认值必须为常数，不能是函数或表达式，例如，一个日期列的默认值不能被设置为一个函数，如,NOW(),或,CURRENT_DATE,。这里有一个特例，可以对,TIMESTAMP,列指定,CURRENT_TIMESTAMP,为默认值。,BLOB,和,TEXT,类型的列不能指定默认值。,13,1.CREATE TABLE,创建新表,使列值自动增加,如果一个列是整数类型，则可以使用,“,AUTO_INCREMENT,”,关键字指定该列的值自动增加。使用自动增加可以很方便的在表中的某一列上，为每一条记录建立不同的列值，从而根据该列的值可以唯一的确定表中的一条记录。,14,1.CREATE TABLE,创建新表,当指定某一列为自动增加时，向该列插入一个,NULL,值（建议）或,0,时，该列会被自动设置为比上一次插入时更大的值，通常情况下依次增加,1,。也就是说，新增加的列值总是当前表中该列的最大值。如果新增加的记录是表中的第一条记录，则该值为,1,。,15,1.CREATE TABLE,创建新表,指定列的字符集,如果某一列是字符列，则其定义可以包括一个,CHARACTER SET,属性，用来指定字符集和校对规则。可以将,CHARACTER SET,缩写为,CHARSET,。,16,1.CREATE TABLE,创建新表,为列添加注释,为了记录表中某些列的作用，可以在建表时将这些列的用法添加列的注释中，添加注释使用,COMMENT,关键字。可以通过,SHOW CREATE TABLE,或,SHOW FULL COLUMNS,语句来查看列的注释。,17,1.CREATE TABLE,创建新表,设置主键,主键就好比是表中每一条记录的身份，一张表中不能出现两条主键相同的记录，换言之，只要主键的值确定了，就唯一的确定了表中的一条记录。被设置为主键的列同时会被强制设置为,NOT NULL,。一张表只能设置一个主键，主键可以是一个列，也可以是多个列的组合。,18,1.CREATE TABLE,创建新表,设置主键,将一个列设置为主键的方法是，在列的定义中使用,“,PRIMARY KEY,”,子句。以下代码将,“,student,”,表的,ID,列设置为主键。,Create table student,(,ID int KEY,NAME char(8),),19,1.CREATE TABLE,创建新表,要将主键设置为多个列的组合，必须使用列级约束,PRIMARY KEY,子句。以下代码演示了将表,grade,（成绩）的列,SID,（学号），,CID,（课程号）设置为主键。,create table grade,(,sid int,cid int,grade int,primary key(sid,cid),),20,1.CREATE TABLE,创建新表,使列值不重复,在表中输入数据时，有时需要某一列的内容不含重复内容，如书藉的出版号，此时可以使用,UNIQUE KEY,关键字来防止列值重复。,如果在添加新行时插入的列值在表中已存在，则会出现错误。例外情况是，如果索引中的一个列允许包含,NULL,值，,NULL,值可以重复出现。,21,1.CREATE TABLE,创建新表,使用索引,可以使用如下方法在表中指定索引：,INDEX,索引名 索引类型,（索引列,1,，索引列,2,，,）,索引用于加速表的查询速度，有关索引的详细信息可参考索引的管理。,22,1.CREATE TABLE,创建新表,SERIAL,实际使用中，经常需要将一个列设置为,“,BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE,”,属性，为便于进行这一操作，可以使用关键字,“,SERIAL,”,来替代这段很长的代码。,23,1.CREATE TABLE,创建新表,外键约束,外键约束是指当前表中的某些列与其它参考表中的某些列存在着参照关系，在当前表中的列的值必须已经出现在参考表的对应列中。在列定义和表级约束中都可以指定表的外键约束。,24,1.CREATE TABLE,创建新表,外键定义服从下列情况：,所有,tables,必须是,InnoDB,型，它们不能是临时表。,在引用表中，必须有一个索引，外键列以同样的顺序被列在其中作为第一列。这样一个索引如果不存在，它必须在引用表里被自动创建。,在引用表中，必须有一个索引，被引用的列以同样的顺序被列在其中作为第一列。,不支持对外键列的索引前缀。这样的后果之一是,BLOB,和,TEXT,列不被包括在一个外键中，这是因为对这些列的索引必须总是包含一个前缀长度。,如果,CONSTRAINT symbol,被给出，它在数据库里必须是唯一的。如果它没有被给出，,InnoDB,自动创建这个名字。,25,1.CREATE TABLE,创建新表,表选项,表选项用于对表进行优化，以下介绍表选项的含义。,ENGINE,和,TYPE,ENGINE,和,TYPE,选项用于为表指定存储引擎，,ENGINE,是首选的选项名称。,ENGINE,和,TYPE,选项可参考,MySQL,备份与恢复中相关内容。,26,1.CREATE TABLE,创建新表,表选项,如果被指定的存储引擎无法利用，则,MySQL,使用,MyISAM,代替。例如，一个表定义包括,ENGINE=BDB,选项，但是,MySQL,服务器不支持,BDB,表，则表被创建为,MyISAM,表。这样，如果在主机上有事务表，但在从属机上创建的是非交互式表（以加快速度）时，可以进行复制设置。在,MySQL 5.1,中，如果没有遵守存储引擎规约，则会出现警告。,27,1.CREATE TABLE,创建新表,AUTO_INCREMENT,表的初始,AUTO_INCREMENT,值。在,MySQL 5.1,中，本选项只适用于,MyISAM,和,MEMORY,表。,InnoDB,也支持本选项。如果引擎不支持,AUTO_INCREMENT,表选项，则要设置引擎的第一个,auto-increment,值，需插入一个,“,假,”,行。该行的值比创建表后的值小一，然后删除该假行。,28,1.CREATE TABLE,创建新表,AVG_ROW_LENGTH,表中平均行长度的近似值。只需要对含尺寸可变的记录的大型表进行此项设置。,DEFAULT CHARACTER SET,用于为表指定一个默认字符集。,CHARSET,是,CHARACTER SET,的同义词。,COLLATE,用于为表指定一个默认整序。,29,1.CREATE TABLE,创建新表,CHECKSUM,如果希望,MySQL,随时对所有行进行实时检验求和（也就是，表变更后，,MySQL,自动更新检验求和），则应把此项设置为,1,。这样做，表的更新速度会略微慢些，但是更容易寻找到受损的表。,CHECKSUM TABLE,语句用于报告检验求和（仅限于,MyISAM,）。,COMMENT,表的注释，最长,60,个字符。,30,1.CREATE TABLE,创建新表,CONNECTION,FEDERATED,表的连接字符串。（注释：较早版本的,MySQL,使用,COMMENT,选项用于连接字符串。,MAX_ROWS,打算储存在表中的行数目的最大值。这不是一个硬性限值，而更像一个指示语句，指示出表必须能存储至少这么多行。,MIN_ROWS,打算存储在表中的行数目的最小值。,31,1.CREATE TABLE,创建新表,PACK_KEYS,如果希望索引更小，则把此选项设置为,1,。这样做通常使更新速度变慢，同时阅读速度加快。把选项设置为,0,可以取消所有的关键字压缩。把此选项设置为,DEFAULT,时，存储引擎只压缩长的,CHAR,或,VARCHAR,列（仅限于,MyISAM,）。,如果不使用,PACK_KEYS,，则默认操作是只压缩字符串，但不压缩数字。如果使用,PACK_KEYS=1,，则对数字也进行压缩。,32,1.CREATE TABLE,创建新表,PASSWORD,使用密码对,.frm,文件加密。在标准,MySQL,版本中，本选项不起任何作用。,DELAY_KEY_WRITE,如果想要延迟对关键字的更新，等到表关闭后再更新，则把此项设置为,1,（仅限于,MyISAM,）。,33,1.CREATE TABLE,创建新表,ROW_FORMAT,定义各行应如何储存。当前，此选项只适用于,MyISAM,表。对于静态行或长度可变行，此选项值可以为,FIXED,或,DYNAMIC,。,myisampack,用于把类型设置为,COMPRESSED,。,在默认情况下，,InnoDB,记录以压缩格式存储（,ROW_FORMAT=COMPACT,）。通过指定,ROW_FORMAT=REDUNDANT,，仍然可以申请用于较早版本的,MySQL,中的非压缩格式。,34,1.CREATE TABLE,创建新表,RAID_TYPE,在,MySQL 5.0,中,RAID,支持被删除了。,UNION,当想要把一组相同的表当作一个表使用时，采用,UNION,。,UNION,仅适用于,MERGE,表。,对于映射到一个,MERGE,表上的表，必须拥有,SELECT,UPDATE,和,DELETE,权限。,35,1.CREATE TABLE,创建新表,INSERT_METHOD,如果希望在,MERGE,表中插入数据，必须用,INSERT_METHOD,指定应插入行的表。,INSERT_METHOD,选项仅用于,MERGE,表。使用,FIRST,或,LAST,把行插入到第一个或最后一个表中；或者使用,NO,，阻止插入行。,DATA DIRECTORY,INDEX DIRECTORY,通过使用,DATA DIRECTORY=directory,或,INDEX DIRECTORY=directory,，可以指定,MyISAM,存储引擎放置表格数据文件和索引文件的位置。注意，目录应是通向目录的完整路径（不是相对路径）。,36,1.CREATE TABLE,创建新表,在一个表的基础上创建表,可以在,CREATE TABLE,语句的末尾添加一个,SELECT,语句，在一个表的基础上创建表。,CREATE TABLE,新表名,SELECT*FROM,老表名,;,MySQL,会对,SELECT,中的所有项创建新列。,37,1.CREATE TABLE,创建新表,参照其它表的定义创建一个新表,以下写法用于参照其它表的定义创建一个新表：,CREATE TEMPORARY TABLE,IF NOT EXISTS,表名,LIKE,参照表的表名,38,2.ALTER TABLE,修改表,ALTER IGNORE TABLE,表名,修改项,1,修改项,2.,其中，修改项写法如下：,ADD COLUMN,列定义,FIRST|AFTER,列名,|ADD COLUMN(,列定义,1,列定义,2),|ADD INDEX,索引名,索引类型,(,索引列名,.),|ADD CONSTRAINT,约束名,PRIMARY KEY,索引类型,(,索引列名,.),|ADD CONSTRAINT,约束名,UNIQUE ,索引名,索引类型,(,索引列名,.),39,2.ALTER TABLE,修改表,|ADD FULLTEXT|SPATIAL,索引名,(,索引列名,.),|ADD CONSTRAINT,约束名,FOREIGN KEY,索引名,(,索引列名,.),外键约束定义,|ALTER COLUMN,列名,SET DEFAULT literal|DROP DEFAULT,|CHANGE COLUMN,旧列名 列定义,FIRST|AFTER,列名,|MODIFY COLUMN,列定义,FIRST|AFTER,列名,|DROP COLUMN,列名,|DROP PRIMARY KEY,|DROP INDEX,索引名,40,2.ALTER TABLE,修改表,|DROP FOREIGN KEY,外键约束名,|DISABLE KEYS,|ENABLE KEYS,|RENAME TO,新表名,|ORDER BY,列名,|CONVERT TO CHARACTER SET,字符集,COLLATE,校对规则,|DEFAULT CHARACTER SET,字符集,COLLATE,校对规则,|DISCARD TABLESPACE,|IMPORT TABLESPACE,|,表选项,41,2.ALTER TABLE,修改表,ALTER TABLE,用于更改原有表的结构。例如，可以增加或删减列，创建或取消索引，更改原有列的类型，或重新命名列或表。还可以更改表的评注和表的类型。,允许进行的修改中，许多子句的语法与,CREATE TABLE,中的子句的语法相近。其中包括表选项修改，选项有,ENGINE,AUTO_INCREMENT,和,AVG_ROW_LENGTH,等。可以参考,“,CREATE TABLE,语法,”,。,42,2.ALTER TABLE,修改表,IGNORE,IGNORE,是,MySQL,相对于标准,SQL,的扩展。如果在新表中有重复关键字，或者当,STRICT,模式启动后出现警告，则使用,IGNORE,控制,ALTER TABLE,的运行。如果没有指定,IGNORE,，当重复关键字错误发生时，复制操作被放弃，返回前一步骤。如果指定了,IGNORE,，则对于有重复关键字的行，只使用第一行，其它有冲突的行被删除。并且，对错误值进行修正，使之尽量接近正确值。,43,2.ALTER TABLE,修改表,使用多个子句,可以在一个,ALTER TABLE,语句里写入多个,ADD,ALTER,DROP,和,CHANGE,子句，中间用逗号分开。这是,MySQL,相对于标准,SQL,的扩展。在标准,SQL,中，每个,ALTER TABLE,语句中每个子句只允许使用一次。例如，在一个语句中取消多个列：,mysql ALTER TABLE t2 DROP COLUMN c,DROP COLUMN d;,44,2.ALTER TABLE,修改表,CHANGE,和,MODIFY,可以使用,“,CHANGE,旧列名 新列定义,”,子句对列进行重命名。重命名时，需给定旧的和新的列名称和列当前的类型。例如：要把一个,INTEGER,列的名称从,a,变更到,b,，需要如下操作：,mysql ALTER TABLE t1 CHANGE a b INTEGER;,45,2.ALTER TABLE,修改表,FIRST,和,AFTER,可以使用,“,FIRST,列名,”,或,“,AFTER,列名,”,在一个表行中的某个特定位置添加列。默认把列添加到最后。也可以在,CHANGE,或,MODIFY,语句中使用,FIRST,和,AFTER,。,修改缺省值,“,ALTER.SET DEFAULT,”,或,“,ALTER.DROP DEFAULT,”,用于指定列的新默认值，或删除旧的默认值。如果旧的默认值被删除同时列值为,NULL,，则新的默认值为,NULL,。如果列值不能为,NULL,，,MySQL,会指定一个默认值。,46,2.ALTER TABLE,修改表,修改索引,DROP INDEX,用于取消索引。这是,MySQL,相对于标准,SQL,的扩展。,如果列从表中被取消了，则这些列也从相应的索引中被取消。如果组成一个索引的所有列均被取消，则该索引也被取消。,删除列,如果一个表只包含一列，则此列不能被取消。如果想要取消表，应使用,DROP TABLE,。,47,2.ALTER TABLE,修改表,DROP PRIMAY DEY,删除主键,DROP PRIMAY DEY,用于取消主索引。注释：在,MySQL,较早的版本中，如果没有主索引，则,DROP PRIMARY KEY,会取消表中的第一个,UNIQUE,索引。在,MySQL 5.1,中不会出现这种情况。如果在,MySQL 5.1,中对没有主键的表使用,DROP PRIMARY KEY,，则会出现错误信息。,48,2.ALTER TABLE,修改表,添加唯一约束和主键约束,如果向表中添加,UNIQUE KEY,或,PRIMARY KEY,，则,UNIQUE KEY,或,PRIMARY KEY,会被储存在非唯一索引之前，这样,MySQL,就可以尽早地检查出重复关键字。,ORDER BY,ORDER BY,用于在创建新表时，让各行按一定的顺序排列。注意，在插入和删除后，表不会仍保持此顺序。当知道多数情况下会按照特定的顺序查询各行时，可以使用这个选项；在对表进行了大的改动后，通过使用此选项，可以提高查询效率。在有些情况下，如果表按列排序，对于,MySQL,来说，排序可能会更简单。,49,2.ALTER TABLE,修改表,更改字符集,如果想要把表默认的字符集和所有字符列（,CHAR,VARCHAR,TEXT,）改为新的字符集，应使用如下语句：,ALTER TABLE,表名,CONVERT TO CHARACTER SET,字符集,;,50,3.RENAME TABLE,重命名表,RENAME TABLE,表名,TO,新表名,表名,2 TO,新表名,2.,本语句用于对一个或多个表进行重命名，当对表进行重命名时，其它线程不能读取任何表。,51,3.RENAME TABLE,重命名表,如果此语句用于对多个表进行重命名，则重命名操作从左至右进行。如果想要交换两个表的名称，可以这样做（假设不存在名称为,tmp_table,的表）：,RENAME TABLE old_table TO tmp_table,new_table TO old_table,tmp_table TO new_table;,52,3.RENAME TABLE,重命名表,只要两个数据库位于同一文件系统中，还可以对表进行重命名，把表从一个数据库中移动到另一个数据库中：,RENAME TABLE current_db.tbl_name TO other_db.tbl_name;,53,4.DROP TABLE,删除表,DROP TEMPORARY TABLE,IF EXISTS,表名,1,表名,2.,RESTRICT|CASCADE,DROP TABLE,用于删除一个或多个表。必须有每个表的,DROP,权限。所有的表数据和表定义会被取消，所以使用本语句要小心！,54,4.DROP TABLE,删除表,TEMPORARY,关键词具有以下作用：,语句只取消,TEMPORARY,表。,语句不会终止正在进行中的事务。,不会查验存取权。（,TEMPORARY,表仅对于创建该表的客户端是可见的，所以查验是不必要的。）,使用,TEMPORARY,是确保不会意外取消一个非,TEMPORARY,表的良好方法。,55,本章小结,本章介绍了,MySQL,表的管理，包括表的创建、修改、重命名和删除。,56,