数据库管理与应用第章表

第六章表1课程目标创建表修改表重命名表删除表21.CREATETABLE创建新表使用CREATETABLE可以创建一个新表，这一语句可以分为两种写法:一种用于直接创建新表;另一种用于参照其它表的定义创建一个新表。31.CREATETABLE创建新表以下写法用于直接创建新表：CREATE[TEMPORARY]TABLE[IFNOTEXISTS]表名[(列定义1，列定义2，...，[表级约束])][表选项][select表达式]其中列定义的基本写法为：列名列的类型41.CREATETABLE创建新表以下代码用于创建一个名为“student”的表，该表有两列：ID：INT类型，用于存储学生的编号；NAME：CHAR（8）类型，用于存储学生的姓名。表的创建代码如下：mysql>createtablestudent->(->IDint,->NAMEchar(8)->);QueryOK,0rowsaffected(0.09sec)51.CREATETABLE创建新表MySQL将每一个表都单独保存在一个“.frm”格式的文件中，该文件存储在相应的数据库目录中。表的存储引擎也可能会创建其它文件，如对于MyISAM类型的表，存储引擎会自动创建数据和索引文件。61.CREATETABLE创建新表创建临时表在创建表格时，可以使用TEMPORARY关键词使当前建立的表为临时表。临时表只能在当前连接中使用，当连接关闭时，临时表也被自动删除。这意味着两个不同的连接可以创建两个名称相同的临时表，这两个临时表不会互相冲突，也不与原有的同名的非临时表冲突。71.CREATETABLE创建新表创建临时表如果临时表的名称与数据库中现有的非临时表名称相同，则非临时表会被隐藏，直到该临时表被删除为止。创建临时表要求当前帐户拥有CREATETEMPORARYTABLES权限。81.CREATETABLE创建新表避免创建已经存在的表如果表已存在，则使用关键词IFNOTEXISTS可以防止发生错误。注意，原有表的结构与CREATETABLE语句中表示的表的结构是否相同，这一点没有验证。注释：如果在CREATETABLE...SELECT语句中使用IFNOTEXISTS，则不论表是否已存在，由SELECT部分选择的记录都会被插入。91.CREATETABLE创建新表是否允许列为空值在列定义中，可以加入以下选项以指明某一列是否允许出现空值：NULL：允许出现空值；NOTNULL：不允许出现空值。101.CREATETABLE创建新表以下代码在创建“student”表时，不允许学生的编号（ID）和姓名（NAME）为空，但允许住址（ADDR）为空。mysql>createtablestudent->(->IDintNOTNULL,->NAMEchar(8)NOTNULL,->ADDRchar(60)NULL->);QueryOK,0rowsaffected(0.09sec)111.CREATETABLE创建新表指定列的默认值在列定义中，可以加入以下选项以指明某一列的默认值：DEFAULT默认值121.CREATETABLE创建新表默认值必须为常数，不能是函数或表达式，例如，一个日期列的默认值不能被设置为一个函数，如NOW()或CURRENT_DATE。这里有一个特例，可以对TIMESTAMP列指定CURRENT_TIMESTAMP为默认值。BLOB和TEXT类型的列不能指定默认值。131.CREATETABLE创建新表使列值自动增加如果一个列是整数类型，则可以使用“AUTO_INCREMENT”关键字指定该列的值自动增加。使用自动增加可以很方便的在表中的某一列上，为每一条记录建立不同的列值，从而根据该列的值可以唯一的确定表中的一条记录。141.CREATETABLE创建新表当指定某一列为自动增加时，向该列插入一个NULL值（建议）或0时，该列会被自动设置为比上一次插入时更大的值，通常情况下依次增加1。也就是说，新增加的列值总是当前表中该列的最大值。如果新增加的记录是表中的第一条记录，则该值为1。151.CREATETABLE创建新表指定列的字符集如果某一列是字符列，则其定义可以包括一个CHARACTERSET属性，用来指定字符集和校对规则。可以将CHARACTERSET缩写为CHARSET。161.CREATETABLE创建新表为列添加注释为了记录表中某些列的作用，可以在建表时将这些列的用法添加列的注释中，添加注释使用COMMENT关键字。可以通过SHOWCREATETABLE或SHOWFULLCOLUMNS语句来查看列的注释。171.CREATETABLE创建新表设置主键主键就好比是表中每一条记录的身份，一张表中不能出现两条主键相同的记录，换言之，只要主键的值确定了，就唯一的确定了表中的一条记录。被设置为主键的列同时会被强制设置为NOTNULL。一张表只能设置一个主键，主键可以是一个列，也可以是多个列的组合。181.CREATETABLE创建新表设置主键将一个列设置为主键的方法是，在列的定义中使用“[PRIMARY]KEY”子句。以下代码将“student”表的ID列设置为主键。Createtablestudent(IDintKEY,NAMEchar(8))191.CREATETABLE创建新表要将主键设置为多个列的组合，必须使用列级约束PRIMARYKEY子句。以下代码演示了将表grade（成绩）的列SID（学号），CID（课程号）设置为主键。createtablegrade(sidint,cidint,gradeint,primarykey(sid,cid))201.CREATETABLE创建新表使列值不重复在表中输入数据时，有时需要某一列的内容不含重复内容，如书藉的出版号，此时可以使用UNIQUE[KEY]关键字来防止列值重复。如果在添加新行时插入的列值在表中已存在，则会出现错误。例外情况是，如果索引中的一个列允许包含NULL值，NULL值可以重复出现。211.CREATETABLE创建新表使用索引可以使用如下方法在表中指定索引：INDEX索引名索引类型（索引列1，索引列2，…）索引用于加速表的查询速度，有关索引的详细信息可参考索引的管理。221.CREATETABLE创建新表SERIAL实际使用中，经常需要将一个列设置为“BIGINTUNSIGNEDNOTNULLAUTO_INCREMENTUNIQUE”属性，为便于进行这一操作，可以使用关键字“SERIAL”来替代这段很长的代码。231.CREATETABLE创建新表外键约束外键约束是指当前表中的某些列与其它参考表中的某些列存在着参照关系，在当前表中的列的值必须已经出现在参考表的对应列中。在列定义和表级约束中都可以指定表的外键约束。241.CREATETABLE创建新表外键定义服从下列情况：所有tables必须是InnoDB型，它们不能是临时表。在引用表中，必须有一个索引，外键列以同样的顺序被列在其中作为第一列。这样一个索引如果不存在，它必须在引用表里被自动创建。在引用表中，必须有一个索引，被引用的列以同样的顺序被列在其中作为第一列。不支持对外键列的索引前缀。这样的后果之一是BLOB和TEXT列不被包括在一个外键中，这是因为对这些列的索引必须总是包含一个前缀长度。如果CONSTRAINTsymbol被给出，它在数据库里必须是唯一的。如果它没有被给出，InnoDB自动创建这个名字。251.CREATETABLE创建新表表选项表选项用于对表进行优化，以下介绍表选项的含义。ENGINE和TYPEENGINE和TYPE选项用于为表指定存储引擎，ENGINE是首选的选项名称。ENGINE和TYPE选项可参考MySQL备份与恢复中相关内容。261.CREATETABLE创建新表表选项如果被指定的存储引擎无法利用，则MySQL使用MyISAM代替。例如，一个表定义包括ENGINE=BDB选项，但是MySQL服务器不支持BDB表，则表被创建为MyISAM表。这样，如果在主机上有事务表，但在从属机上创建的是非交互式表（以加快速度）时，可以进行复制设置。在MySQL5.1中，如果没有遵守存储引擎规约，则会出现警告。271.CREATETABLE创建新表AUTO_INCREMENT表的初始AUTO_INCREMENT值。在MySQL5.1中，本选项只适用于MyISAM和MEMORY表。InnoDB也支持本选项。如果引擎不支持AUTO_INCREMENT表选项，则要设置引擎的第一个auto-increment值，需插入一个“假”行。该行的值比创建表后的值小一，然后删除该假行。281.CREATETABLE创建新表AVG_ROW_LENGTH表中平均行长度的近似值。只需要对含尺寸可变的记录的大型表进行此项设置。[DEFAULT]CHARACTERSET用于为表指定一个默认字符集。CHARSET是CHARACTERSET的同义词。COLLATE

用于为表指定一个默认整序。291.CREATETABLE创建新表CHECKSUM如果希望MySQL随时对所有行进行实时检验求和（也就是，表变更后，MySQL自动更新检验求和），则应把此项设置为1。这样做，表的更新速度会略微慢些，但是更容易寻找到受损的表。CHECKSUMTABLE语句用于报告检验求和（仅限于MyISAM）。COMMENT表的注释，最长60个字符。301.CREATETABLE创建新表CONNECTIONFEDERATED表的连接字符串。（注释：较早版本的MySQL使用COMMENT选项用于连接字符串。MAX_ROWS打算储存在表中的行数目的最大值。这不是一个硬性限值，而更像一个指示语句，指示出表必须能存储至少这么多行。MIN_ROWS打算存储在表中的行数目的最小值。311.CREATETABLE创建新表PACK_KEYS

如果希望索引更小，则把此选项设置为1。这样做通常使更新速度变慢，同时阅读速度加快。把选项设置为0可以取消所有的关键字压缩。把此选项设置为DEFAULT时，存储引擎只压缩长的CHAR或VARCHAR列（仅限于MyISAM）。如果不使用PACK_KEYS，则默认操作是只压缩字符串，但不压缩数字。如果使用PACK_KEYS=1，则对数字也进行压缩。321.CREATETABLE创建新表PASSWORD使用密码对.frm文件加密。在标准MySQL版本中，本选项不起任何作用。DELAY_KEY_WRITE如果想要延迟对关键字的更新，等到表关闭后再更新，则把此项设置为1（仅限于MyISAM）。331.CREATETABLE创建新表ROW_FORMAT

定义各行应如何储存。当前，此选项只适用于MyISAM表。对于静态行或长度可变行，此选项值可以为FIXED或DYNAMIC。myisampack用于把类型设置为COMPRESSED。在默认情况下，InnoDB记录以压缩格式存储（ROW_FORMAT=COMPACT）。通过指定ROW_FORMAT=REDUNDANT，仍然可以申请用于较早版本的MySQL中的非压缩格式。341.CREATETABLE创建新表RAID_TYPE在MySQL5.0中,RAID支持被删除了。UNION当想要把一组相同的表当作一个表使用时，采用UNION。UNION仅适用于MERGE表。对于映射到一个MERGE表上的表，必须拥有SELECT,UPDATE和DELETE权限。351.CREATETABLE创建新表INSERT_METHOD如果希望在MERGE表中插入数据，必须用INSERT_METHOD指定应插入行的表。INSERT_METHOD选项仅用于MERGE表。使用FIRST或LAST把行插入到第一个或最后一个表中；或者使用NO，阻止插入行。DATADIRECTORY,INDEXDIRECTORY

通过使用DATADIRECTORY='directory'或INDEXDIRECTORY='directory'，可以指定MyISAM存储引擎放置表格数据文件和索引文件的位置。注意，目录应是通向目录的完整路径（不是相对路径）。361.CREATETABLE创建新表在一个表的基础上创建表可以在CREATETABLE语句的末尾添加一个SELECT语句，在一个表的基础上创建表。CREATETABLE新表名SELECT*FROM老表名;MySQL会对SELECT中的所有项创建新列。371.CREATETABLE创建新表参照其它表的定义创建一个新表以下写法用于参照其它表的定义创建一个新表：CREATE[TEMPORARY]TABLE[IFNOTEXISTS]表名LIKE参照表的表名382.ALTERTABLE修改表ALTER[IGNORE]TABLE表名修改项1[,修改项2]...其中，修改项写法如下：ADD[COLUMN]列定义[FIRST|AFTER列名]|ADD[COLUMN](列定义1,列定义2)|ADDINDEX[索引名][索引类型](索引列名,...)|ADD[CONSTRAINT[约束名]]PRIMARYKEY[索引类型](索引列名,...)|ADD[CONSTRAINT[约束名]]UNIQUE[索引名][索引类型](索引列名,...)392.ALTERTABLE修改表|ADD[FULLTEXT|SPATIAL][索引名](索引列名,...)|ADD[CONSTRAINT[约束名]]FOREIGNKEY[索引名](索引列名,...)[外键约束定义]|ALTER[COLUMN]列名{SETDEFAULTliteral|DROPDEFAULT}|CHANGE[COLUMN]旧列名列定义

[FIRST|AFTER列名]|MODIFY[COLUMN]列定义[FIRST|AFTER列名]|DROP[COLUMN]列名

|DROPPRIMARYKEY|DROPINDEX索引名402.ALTERTABLE修改表|DROPFOREIGNKEY外键约束名

|DISABLEKEYS|ENABLEKEYS|RENAME[TO]新表名

|ORDERBY列名

|CONVERTTOCHARACTERSET字符集[COLLATE校对规则]|[DEFAULT]CHARACTERSET字符集[COLLATE校对规则]|DISCARDTABLESPACE|IMPORTTABLESPACE|表选项412.ALTERTABLE修改表ALTERTABLE用于更改原有表的结构。例如，可以增加或删减列，创建或取消索引，更改原有列的类型，或重新命名列或表。还可以更改表的评注和表的类型。允许进行的修改中，许多子句的语法与CREATETABLE中的子句的语法相近。其中包括表选项修改，选项有ENGINE,AUTO_INCREMENT和AVG_ROW_LENGTH等。可以参考“CREATETABLE语法”。422.ALTERTABLE修改表IGNOREIGNORE是MySQL相对于标准SQL的扩展。如果在新表中有重复关键字，或者当STRICT模式启动后出现警告，则使用IGNORE控制ALTERTABLE的运行。如果没有指定IGNORE，当重复关键字错误发生时，复制操作被放弃，返回前一步骤。如果指定了IGNORE，则对于有重复关键字的行，只使用第一行，其它有冲突的行被删除。并且，对错误值进行修正，使之尽量接近正确值。432.ALTERTABLE修改表使用多个子句可以在一个ALTERTABLE语句里写入多个ADD,ALTER,DROP和CHANGE子句，中间用逗号分开。这是MySQL相对于标准SQL的扩展。在标准SQL中，每个ALTERTABLE语句中每个子句只允许使用一次。例如，在一个语句中取消多个列：mysql>ALTERTABLEt2DROPCOLUMNc,DROPCOLUMNd;442.ALTERTABLE修改表CHANGE和MODIFY可以使用“CHANGE旧列名新列定义”子句对列进行重命名。重命名时，需给定旧的和新的列名称和列当前的类型。例如：要把一个INTEGER列的名称从a变更到b，需要如下操作：mysql>ALTERTABLEt1CHANGEabINTEGER;452.ALTERTABLE修改表FIRST和AFTER可以使用“FIRST列名”或“AFTER列名”在一个表行中的某个特定位置添加列。默认把列添加到最后。也可以在CHANGE或MODIFY语句中使用FIRST和AFTER。修改缺省值“ALTER...SETDEFAULT”或“ALTER...DROPDEFAULT”用于指定列的新默认值，或删除旧的默认值。如果旧的默认值被删除同时列值为NULL，则新的默认值为NULL。如果列值不能为NULL，MySQL会指定一个默认值。462.ALTERTABLE修改表修改索引DROPINDEX用于取消索引。这是MySQL相对于标准SQL的扩展。如果列从表中被取消了，则这些列也从相应的索引中被取消。如果组成一个索引的所有列均被取消，则该索引也被取消。删除列如果一个表只包含一列，则此列不能被取消。如果想要取消表，应使用DROPTABLE。472.ALTERTABLE修改表DROPPRIMAYDEY删除主键DROPPRIMAYDEY用于取消主索引。注释：在MySQL较早的版本中，如果没有主索引，则DROPPRIMARYKEY会取消表中的第一个UNIQUE索引。在MySQL5.1中不会出现这种情况。如果在MySQL5.1中对没有主键的表使用DROPPRIMARYKEY，则会出现错误信息。482.ALTERTABLE修改表添加唯一约束和主键约束如果向表中添加UNIQUEKEY或PRIMARYKEY，则UNIQUEKEY或PRIMARYKEY会被储存在非唯一索引之前，这样MySQL就可以尽早地检查出重复关键字。ORDERBYORDERBY用于在创建新表时，让各行按一定的顺序排列。注意，在插入和删除后，表不会仍保持此顺序。当知道多数情况下会按照特定的顺序查询各行时，可以使用这个选项；在对表进行了大的改动后，通过使用此选项，可