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

第六章 表 1 课程目标 创建表修改表重命名表删除表 2 1 CREATETABLE创建新表 使用CREATETABLE可以创建一个新表 这一语句可以分为两种写法 一种用于直接创建新表 另一种用于参照其它表的定义创建一个新表 3 1 CREATETABLE创建新表 以下写法用于直接创建新表 CREATE TEMPORARY TABLE IFNOTEXISTS 表名 列定义1 列定义2 表级约束 表选项 select表达式 其中列定义的基本写法为 列名列的类型 4 1 CREATETABLE创建新表 以下代码用于创建一个名为 student 的表 该表有两列 ID INT类型 用于存储学生的编号 NAME CHAR 8 类型 用于存储学生的姓名 表的创建代码如下 mysql createtablestudent IDint NAMEchar 8 QueryOK 0rowsaffected 0 09sec 5 1 CREATETABLE创建新表 MySQL将每一个表都单独保存在一个 frm 格式的文件中 该文件存储在相应的数据库目录中 表的存储引擎也可能会创建其它文件 如对于MyISAM类型的表 存储引擎会自动创建数据和索引文件 6 1 CREATETABLE创建新表 创建临时表在创建表格时 可以使用TEMPORARY关键词使当前建立的表为临时表 临时表只能在当前连接中使用 当连接关闭时 临时表也被自动删除 这意味着两个不同的连接可以创建两个名称相同的临时表 这两个临时表不会互相冲突 也不与原有的同名的非临时表冲突 7 1 CREATETABLE创建新表 创建临时表如果临时表的名称与数据库中现有的非临时表名称相同 则非临时表会被隐藏 直到该临时表被删除为止 创建临时表要求当前帐户拥有CREATETEMPORARYTABLES权限 8 1 CREATETABLE创建新表 避免创建已经存在的表如果表已存在 则使用关键词IFNOTEXISTS可以防止发生错误 注意 原有表的结构与CREATETABLE语句中表示的表的结构是否相同 这一点没有验证 注释 如果在CREATETABLE SELECT语句中使用IFNOTEXISTS 则不论表是否已存在 由SELECT部分选择的记录都会被插入 9 1 CREATETABLE创建新表 是否允许列为空值在列定义中 可以加入以下选项以指明某一列是否允许出现空值 NULL 允许出现空值 NOTNULL 不允许出现空值 10 1 CREATETABLE创建新表 以下代码在创建 student 表时 不允许学生的编号 ID 和姓名 NAME 为空 但允许住址 ADDR 为空 mysql createtablestudent IDintNOTNULL NAMEchar 8 NOTNULL ADDRchar 60 NULL QueryOK 0rowsaffected 0 09sec 11 1 CREATETABLE创建新表 指定列的默认值在列定义中 可以加入以下选项以指明某一列的默认值 DEFAULT默认值 12 1 CREATETABLE创建新表 默认值必须为常数 不能是函数或表达式 例如 一个日期列的默认值不能被设置为一个函数 如NOW 或CURRENT DATE 这里有一个特例 可以对TIMESTAMP列指定CURRENT TIMESTAMP为默认值 BLOB和TEXT类型的列不能指定默认值 13 1 CREATETABLE创建新表 使列值自动增加如果一个列是整数类型 则可以使用 AUTO INCREMENT 关键字指定该列的值自动增加 使用自动增加可以很方便的在表中的某一列上 为每一条记录建立不同的列值 从而根据该列的值可以唯一的确定表中的一条记录 14 1 CREATETABLE创建新表 当指定某一列为自动增加时 向该列插入一个NULL值 建议 或0时 该列会被自动设置为比上一次插入时更大的值 通常情况下依次增加1 也就是说 新增加的列值总是当前表中该列的最大值 如果新增加的记录是表中的第一条记录 则该值为1 15 1 CREATETABLE创建新表 指定列的字符集如果某一列是字符列 则其定义可以包括一个CHARACTERSET属性 用来指定字符集和校对规则 可以将CHARACTERSET缩写为CHARSET 16 1 CREATETABLE创建新表 为列添加注释为了记录表中某些列的作用 可以在建表时将这些列的用法添加列的注释中 添加注释使用COMMENT关键字 可以通过SHOWCREATETABLE或SHOWFULLCOLUMNS语句来查看列的注释 17 1 CREATETABLE创建新表 设置主键主键就好比是表中每一条记录的身份 一张表中不能出现两条主键相同的记录 换言之 只要主键的值确定了 就唯一的确定了表中的一条记录 被设置为主键的列同时会被强制设置为NOTNULL 一张表只能设置一个主键 主键可以是一个列 也可以是多个列的组合 18 1 CREATETABLE创建新表 设置主键将一个列设置为主键的方法是 在列的定义中使用 PRIMARY KEY 子句 以下代码将 student 表的ID列设置为主键 Createtablestudent IDintKEY NAMEchar 8 19 1 CREATETABLE创建新表 要将主键设置为多个列的组合 必须使用列级约束PRIMARYKEY子句 以下代码演示了将表grade 成绩 的列SID 学号 CID 课程号 设置为主键 createtablegrade sidint cidint gradeint primarykey sid cid 20 1 CREATETABLE创建新表 使列值不重复在表中输入数据时 有时需要某一列的内容不含重复内容 如书藉的出版号 此时可以使用UNIQUE KEY 关键字来防止列值重复 如果在添加新行时插入的列值在表中已存在 则会出现错误 例外情况是 如果索引中的一个列允许包含NULL值 NULL值可以重复出现 21 1 CREATETABLE创建新表 使用索引可以使用如下方法在表中指定索引 INDEX索引名索引类型 索引列1 索引列2 索引用于加速表的查询速度 有关索引的详细信息可参考索引的管理 22 1 CREATETABLE创建新表 SERIAL实际使用中 经常需要将一个列设置为 BIGINTUNSIGNEDNOTNULLAUTO INCREMENTUNIQUE 属性 为便于进行这一操作 可以使用关键字 SERIAL 来替代这段很长的代码 23 1 CREATETABLE创建新表 外键约束外键约束是指当前表中的某些列与其它参考表中的某些列存在着参照关系 在当前表中的列的值必须已经出现在参考表的对应列中 在列定义和表级约束中都可以指定表的外键约束 24 1 CREATETABLE创建新表 外键定义服从下列情况 所有tables必须是InnoDB型 它们不能是临时表 在引用表中 必须有一个索引 外键列以同样的顺序被列在其中作为第一列 这样一个索引如果不存在 它必须在引用表里被自动创建 在引用表中 必须有一个索引 被引用的列以同样的顺序被列在其中作为第一列 不支持对外键列的索引前缀 这样的后果之一是BLOB和TEXT列不被包括在一个外键中 这是因为对这些列的索引必须总是包含一个前缀长度 如果CONSTRAINTsymbol被给出 它在数据库里必须是唯一的 如果它没有被给出 InnoDB自动创建这个名字 25 1 CREATETABLE创建新表 表选项表选项用于对表进行优化 以下介绍表选项的含义 ENGINE和TYPEENGINE和TYPE选项用于为表指定存储引擎 ENGINE是首选的选项名称 ENGINE和TYPE选项可参考MySQL备份与恢复中相关内容 26 1 CREATETABLE创建新表 表选项如果被指定的存储引擎无法利用 则MySQL使用MyISAM代替 例如 一个表定义包括ENGINE BDB选项 但是MySQL服务器不支持BDB表 则表被创建为MyISAM表 这样 如果在主机上有事务表 但在从属机上创建的是非交互式表 以加快速度 时 可以进行复制设置 在MySQL5 1中 如果没有遵守存储引擎规约 则会出现警告 27 1 CREATETABLE创建新表 AUTO INCREMENT表的初始AUTO INCREMENT值 在MySQL5 1中 本选项只适用于MyISAM和MEMORY表 InnoDB也支持本选项 如果引擎不支持AUTO INCREMENT表选项 则要设置引擎的第一个auto increment值 需插入一个 假 行 该行的值比创建表后的值小一 然后删除该假行 28 1 CREATETABLE创建新表 AVG ROW LENGTH表中平均行长度的近似值 只需要对含尺寸可变的记录的大型表进行此项设置 DEFAULT CHARACTERSET用于为表指定一个默认字符集 CHARSET是CHARACTERSET的同义词 COLLATE用于为表指定一个默认整序 29 1 CREATETABLE创建新表 CHECKSUM如果希望MySQL随时对所有行进行实时检验求和 也就是 表变更后 MySQL自动更新检验求和 则应把此项设置为1 这样做 表的更新速度会略微慢些 但是更容易寻找到受损的表 CHECKSUMTABLE语句用于报告检验求和 仅限于MyISAM COMMENT表的注释 最长60个字符 30 1 CREATETABLE创建新表 CONNECTIONFEDERATED表的连接字符串 注释 较早版本的MySQL使用COMMENT选项用于连接字符串 MAX ROWS打算储存在表中的行数目的最大值 这不是一个硬性限值 而更像一个指示语句 指示出表必须能存储至少这么多行 MIN ROWS打算存储在表中的行数目的最小值 31 1 CREATETABLE创建新表 PACK KEYS如果希望索引更小 则把此选项设置为1 这样做通常使更新速度变慢 同时阅读速度加快 把选项设置为0可以取消所有的关键字压缩 把此选项设置为DEFAULT时 存储引擎只压缩长的CHAR或VARCHAR列 仅限于MyISAM 如果不使用PACK KEYS 则默认操作是只压缩字符串 但不压缩数字 如果使用PACK KEYS 1 则对数字也进行压缩 32 1 CREATETABLE创建新表 PASSWORD使用密码对 frm文件加密 在标准MySQL版本中 本选项不起任何作用 DELAY KEY WRITE如果想要延迟对关键字的更新 等到表关闭后再更新 则把此项设置为1 仅限于MyISAM 33 1 CREATETABLE创建新表 ROW FORMAT定义各行应如何储存 当前 此选项只适用于MyISAM表 对于静态行或长度可变行 此选项值可以为FIXED或DYNAMIC myisampack用于把类型设置为COMPRESSED 在默认情况下 InnoDB记录以压缩格式存储 ROW FORMAT COMPACT 通过指定ROW FORMAT REDUNDANT 仍然可以申请用于较早版本的MySQL中的非压缩格式 34 1 CREATETABLE创建新表 RAID TYPE在MySQL5 0中 RAID支持被删除了 UNION当想要把一组相同的表当作一个表使用时 采用UNION UNION仅适用于MERGE表 对于映射到一个MERGE表上的表 必须拥有SELECT UPDATE和DELETE权限 35 1 CREATETABLE创建新表 INSERT METHOD如果希望在MERGE表中插入数据 必须用INSERT METHOD指定应插入行的表 INSERT METHOD选项仅用于MERGE表 使用FIRST或LAST把行插入到第一个或最后一个表中 或者使用NO 阻止插入行 DATADIRECTORY INDEXDIRECTORY通过使用DATADIRECTORY directory 或INDEXDIRECTORY directory 可以指定MyISAM存储引擎放置表格数据文件和索引文件的位置 注意 目录应是通向目录的完整路径 不是相对路径 36 1 CREATETABLE创建新表 在一个表的基础上创建表可以在CREATETABLE语句的末尾添加一个SELECT语句 在一个表的基础上创建表 CREATETABLE新表名SELECT FROM老表名 MySQL会对SELECT中的所有项创建新列 37 1 CREATETABLE创建新表 参照其它表的定义创建一个新表以下写法用于参照其它表的定义创建一个新表 CREATE TEMPORARY TABLE IFNOTEXISTS 表名LIKE参照表的表名 38 2 ALTERTABLE修改表 ALTER IGNORE TABLE表名修改项1 修改项2 其中 修改项写法如下 ADD COLUMN 列定义 FIRST AFTER列名 ADD COLUMN 列定义1 列定义2 ADDINDEX 索引名 索引类型 索引列名 ADD CONSTRAINT 约束名 PRIMARYKEY 索引类型 索引列名 ADD CONSTRAINT 约束名 UNIQUE 索引名 索引类型 索引列名 39 2 ALTERTABLE修改表 ADD FULLTEXT SPATIAL 索引名 索引列名 ADD CONSTRAINT 约束名 FOREIGNKEY 索引名 索引列名 外键约束定义 ALTER COLUMN 列名 SETDEFAULTliteral DROPDEFAULT CHANGE COLUMN 旧列名列定义 FIRST AFTER列名 MODIFY COLUMN 列定义 FIRST AFTER列名 DROP COLUMN 列名 DROPPRIMARYKEY DROPINDEX索引名 40 2 ALTERTABLE修改表 DROPFOREIGNKEY外键约束名 DISABLEKEYS ENABLEKEYS RENAME TO 新表名 ORDERBY列名 CONVERTTOCHARACTERSET字符集 COLLATE校对规则 DEFAULT CHARACTERSET字符集 COLLATE校对规则 DISCARDTABLESPACE IMPORTTABLESPACE 表选项 41 2 ALTERTABLE修改表 ALTERTABLE用于更改原有表的结构 例如 可以增加或删减列 创建或取消索引 更改原有列的类型 或重新命名列或表 还可以更改表的评注和表的类型 允许进行的修改中 许多子句的语法与CREATETABLE中的子句的语法相近 其中包括表选项修改 选项有ENGINE AUTO INCREMENT和AVG ROW LENGTH等 可以参考 CREATETABLE语法 42 2 ALTERTABLE修改表 IGNOREIGNORE是MySQL相对于标准SQL的扩展 如果在新表中有重复关键字 或者当STRICT模式启动后出现警告 则使用IGNORE控制ALTERTABLE的运行 如果没有指定IGNORE 当重复关键字错误发生时 复制操作被放弃 返回前一步骤 如果指定了IGNORE 则对于有重复关键字的行 只使用第一行 其它有冲突的行被删除 并且 对错误值进行修正 使之尽量接近正确值 43 2 ALTERTABLE修改表 使用多个子句可以在一个ALTERTABLE语句里写入多个ADD ALTER DROP和CHANGE子句 中间用逗号分开 这是MySQL相对于标准SQL的扩展 在标准SQL中 每个ALTERTABLE语句中每个子句只允许使用一次 例如 在一个语句中取消多个列 mysql ALTERTABLEt2DROPCOLUMNc DROPCOLUMNd 44 2 ALTERTABLE修改表 CHANGE和MODIFY可以使用 CHANGE旧列名新列定义 子句对列进行重命名 重命名时 需给定旧的和新的列名称和列当前的类型 例如 要把一个INTEGER列的名称从a变更到b 需要如下操作 mysql ALTERTABLEt1CHANGEabINTEGER 45 2 ALTERTABLE修改表 FIRST和AFTER可以使用 FIRST列名 或 AFTER列名 在一个表行中的某个特定位置添加列 默认把列添加到最后 也可以在CHANGE或MODIFY语句中使用FIRST和AFTER 修改缺省值 ALTER SETDEFAULT 或 ALTER DROPDEFAULT 用于指定列的新默认值 或删除旧的默认值 如果旧的默认值被删除同时列值为NULL 则新的默认值为NULL 如果列值不能为NULL MySQL会指定一个默认值 46 2 ALTERTABLE修改表 修改索引DROPINDEX用于取消索引 这是MySQL相对于标准SQL的扩展 如果列从表中被取消了 则这些列也从相应的索引中被取消 如果组成一个索引的所有列均被取消 则该索引也被取消 删除列如果一个表只包含一列 则此列不能被取消 如果想要取消表 应使用DROPTABLE 47 2 ALTERTABLE修改表 DROPPRIMAYDEY删除主键DROPPRIMAYDEY用于取消主索引 注释 在MySQL较早的版本中 如果没有主索引 则DROPPRIMARYKEY会取消表中的第一个UNIQUE索引 在MySQL5 1中不会出现这种情况 如果在MySQL5 1中对没有主键的表使用DROPPRIMARYKEY 则会出现错误信息 48 2 ALTERTABLE修改表 添加唯一约束和主键约束如果向表中添加UNIQUEKEY或PRIMARYKEY 则UNIQUEKEY或PRIMARYKEY会被储存在非唯一索引之前 这样MySQL就可以尽早地检查出重复关键字 ORDERBYORDERBY用于在创建新表时 让各行按一定的顺序排列 注意 在插入和删除后 表不会仍保持此顺序 当知道多数情况下会按照特定的顺序查询各行时 可以使用这个选项 在对表进行了大的改动后 通过使用此选项 可以提高查询效率 在有些情况下 如果表按列排