MySQL—课堂表的特殊操作.pptx

1、第七章 表,目 标,创建表 修改表 清空表 删除表 分配与使用外键,2,创建表,创建表的语法如下，其中包括各种列与表的选项： CREATE TABLE ( , , ) 例如： mysql CREATE TABLE CountryLanguage ( - CountryCode CHAR(3) NOT NULL, - Language CHAR(30) NOT NULL, - IsOfficial ENUM(True,False) NOT NULL DEFAULT False, - Percentage FLOAT(3,1) NOT NULL, - PRIMARY KEY (CountryCod

2、e, Language) - ) - ENGINE = MyISAM - COMMENT = Lists Language Spoken;,3,SHOW CREATE TABLE,SHOW CREATE TABLE语句可用于获取已有表的结构描述文本。 mysql SHOW CREATE TABLE CityG,4,基于已有的表创建新表,MySQL提供了两种基于一个表创建另一个表的方法： CREATE TABLEAS SELECT CREATE TABLELIKE,5,CREATE TABLEAS SELECT,根据SELECT语句的返回结果，创建一个空表或非空的表：例如： mysql CREA

3、TE TABLE CityCopy1 - AS - SELECT * FROM City; mysql CREATE TABLE CityCopy2 - AS - SELECT * FROM City - WHERE Population 2000000; mysql CREATE TABLE CityCopy3 - AS - SELECT * FROM City LIMIT 0; mysql CREATE TABLE CityCopy4 - AS - SELECT col1, col2 FROM City;,6,CREATE TABLELIKE,CREATE TABLELIKE与CREATE

4、 TABLESELECT结果的不同在于前者创建一个空表。 mysql CREATE TABLE t ( - i INT NOT NULL AUTO_INCREMENT, - PRIMARY KEY (i) - ) ENGINE = InnoDB; mysql CREATE TABLE copy1 SELECT * FROM t LIMIT 0; mysql CREATE TABLE copy2 LIKE t; mysql SHOW CREATE TABLE copy1G mysql SHOW CREATE TABLE copy2G,7,临时表,可以通过在CREATE TABLE语句中使用TEM

5、PORARY关键字来创建临时表。临时表与非临时表有以下不同： 它只在创建的会话里可见 临时表只存在于会话生命周期中 临时表可以与非临时表有相同的名称 临时表只可以使用ALTER TABLERENAME TO来改名 临时表不能使用SHOW TABLES显示，也不在information_schema.TABLES表里。 临时表示例 mysql CREATE TEMPORARY TABLE Texas - AS - SELECT Name - FROM City - WHERE District = Texas;,8,增加列,例如，要在City表里增加一个LocalName的列，可以使用语句： m

6、ysql ALTER TABLE City ADD COLUMN LocalName VARCHAR(35) CHARACTER SET utf8 - NOT NULL DEFAULT COMMENT The local name of this city; mysql DESC City; 为了在表的行里的特定位置增加列，可以使用FIRST或AFTER 。如果没有指定位置，新的列将被增加到最后。,9,删除列,可以使用DROP子句来删除一个列： mysql ALTER TABLE City DROP COLUMN LocalName;,10,修改列的定义,ALTER TABLE语句也能被用于修

7、改列的定义。它可以通过MODIFY或CHANGE子句来完成。 mysql ALTER TABLE City MODIFY ID BIGINT NOT NULL AUTO_INCREMENT;,11,改变列,修改列的定义的第二种方法是使用CHANGE子句。CHANGE子句可以修改列的定义与名称。 mysql ALTER TABLE HeadOfState CHANGE LastName LastName CHAR(40) NOT NULL; 为了改变列的名称（例如，改为Surname），使用以下语句： mysql ALTER TABLE HeadOfState CHANGE LastName S

8、urname CHAR(40) NOT NULL;,12,改变表名,改变表名不会改变表的结构，也不会改变表的内容。例如： mysql ALTER TABLE t1 RENAME TO t2 改变表名的另一种方法是使用RENAME TABLE语句： mysql RENAME TABLE t1 TO t2； RENAME TABLE语句能在一条操作语句中执行多个表的名称改变。该特征的用处之一是交换两个表的名称： mysql RENAME TABLE t1 TO tmp, t2 TO t1, tmp TO t2；,13,删除表,DROP TABLE语句可用于删除一个或多个表： DROP TEMPOR

9、ARY TABLE IF EXISTS table1； TEMPORARY关键字有以下作用： 语句只删除临时表 语句不会终止一个正在进行的事务 DROP TABLE实例： mysql DROP TABLE table1； mysql DROP TABLE IF EXISTS table1； mysql DROP TEMPORARY TABLE CityTmp；,14,外键约束,本节将讨论几个相关但不同的概念： 关系 外键 外键约束 引用完整性 外键是被用于数据库中行之间的引用。同样地，外键被用于实现数据行之间的关系。 外键约束被用于维持外键与确保引用的一致。同样外键约束也被用于强制引用完整性。

10、,15,创建外键约束,外键约束可以在CREATE TABLE语法中指定： mysql CREATE TABLE City ( - ID INT NOT NULL, - Name CHAR(35) NOT NULL, - CountryCode CHAR(3) NOT NULL, - District CHAR(20) NOT NULL, - Population INT NOT NULL, - PRIMARY KEY (ID), - FOREIGN KEY (CountryCode) REFERENCES Country (Code) - ) ENGINE = InnoDB; 也可以使用ALT

11、ER TABLE语句来增加外键约束到已有的表中： mysql ALTER TABLE City - ADD FOREIGN KEY (CountryCode) REFERENCES Country (Code);,16,创建外键约束,完整的外键语法显示如下： CONSTRAINT name FOREIGN KEY name (referencing_coll, referencing_colN) REFERENCES referenced_tab (referenced_coll, referenced_colN) ON DELETE CASCADE | NO ACTION | RESTRIC

12、T | SET NULL ON UPDATE CASCADE | NO ACTION | RESTRICT | SET NULL 因此，在外键约束中强制的成员是： 一个引用列的列表（在外键表中的列） 被引用表的名称 一个被引用列的列表 被引用列应该是组成PRIMARY KEY的约束,17,创建外键约束,可选的成员是： 约束名称 DELETE规则 CASCADE NO ACTION RESTRICT SET NULL UPDATE规则,18,目 标,向表中插入数据 从表中删除数据 更新表中的数据 替换表中的数据 清除表中的数据 在MySQL中使用预处理语句 预备、执行与释放预处理语句,19,IN

13、SERT语句,INSERT语句是很常见的用以增加新记录行到表中的方法。 INSERT有三种不同的语法： INSERTVALUES INSERTSET INSERTSELECT。,20,INSERTVALUES语法,该语句对于精确插入数据到表中非常有用，语法如下： INSERT INTO table_name (column_list) VALUES row_list table_name是将要插入新记录的表名 column_list是可选项。如果指定，列的顺序决定值的顺序，该顺序是它们出现在将要被加入到的表内行中的顺序。 (ID，Name，CountryCode) row_list是一个或多个

14、值的列表，称为行构造器。 mysql INSERT INTO City (ID, Name, CountryCode) VALUES - (NULL, Essaouira, Mar), (NULL, Sankt-Augustin, DEU);,21,INSERTSET语法,该语句也可以用于表示列与值的对应。前面的例子可以用以下的SET子句表示： mysql INSERT INTO City SET ID=NULL, Name=Essaouira, CountryCode= MAR; mysql INSERT INTO City SET ID=NULL, Name=Sankt-Augustion

15、, - CountryCode=DEU;,22,INSERTSELECT语法,INSERTSELECT语法对从一个已有的表中拷贝数据、或（临时）从一个查询中保存结果集将是非常有用的： INSERT INTO table_name (column_list) query_expression 例如： mysql INSERT INTO Top10_Cities(ID, Name, CountryCode) - SELECT ID, Name, CountryCode - FROM City - ORDER BY Population DESC - LIMIT 10;,23,INSERT的LAST

16、_INSERT_ID函数,可以使用MySQL特定的函数LAST_INSERT_ID来获取最后一次成功的INSERT语句中为AUTO_INCREMENT列产生的第一个值： mysql INSERT INTO City (Name, CountryCode) - VALUES(Sarah City, USA); mysql SELECT LAST_INSERT_ID();,24,DELETE语句,可以使用DELETE语句删除表中的记录。为了删除表中所有的记录，可使用以下语句： DELETE FROM table_name DELETE语句允许使用WHERE子句来指定删除某些记录： DELETE F

17、ROM table_name WHERE where_condition ORDER BY LIMIT row_count 例如： mysql DELETE FROM CountryLanguage WHERE IsOfficial = F;,25,使用带有ORDER BY与LIMIT的DELETE,DELETE支持ORDER BY与LIMIT子句，它们提供了对删除记录很好的控制。例如： mysql DELETE FROM people WHERE name=Emily LIMIT 4; mysql DELETE - FROM people - WHERE name=Emily - ORDER

18、 BY id DESC - LIMIT 4;,26,UPDATE语句,UPDATE语句用于修改现存记录值。下面是该语句的语法： UPDATE SET column=,.column=,column= WHERE where_condition ORDER BY LIMIT row_count 例如： mysql UPDATE Country SET Population = Population * 1.1;,27,UPDATE语句,UPDATE语句并不能确保更新记录行的顺序，而这有时会导致问题。 mysql SELECT * FROM people; 如果想要重新编号id值从1开始，可能执行

19、以下UPDATE语句： mysql UPDATE people SET id=id-1; 如果所更新记录未按顺序排列，其结果是不正确的。为了解决这个问题，可增加ORDER BY子句来确保更新按特定的顺序执行： mysql UPDATE people SET id=id-1 ORDER BY id；,28,UPDATE语句,UPDATE语句允许使用LIMIT子句，它指定更新的记录数量。 例如： mysql UPDATE people SET name=Vic WHERE name=Victor LIMIT 1;,29,REPLACE语句,MySQL特定的REPLACE语句可用于替换已有记录行或插

20、入新的记录行。 REPLACE使用以下语法： REPLACE INTO () VALUES() 例如： mysql REPLACE INTO people (id, name, age) VALUES(12, Bruce, 25);,30,REPLACE语句,MySQL为REPLACE语句使用以下算法： 1.插入新的记录到表中 2.当插入失败时（因为有与主键或UNIQUE索引重复的键而导致键错误）： 从表中删除冲突的行，因为该行有重复的键值 然后插入新的行到表里,31,TRUNCATE TABLE语句,除了标准的DELETE语句外，TRUNCATE语句提供了另一种非标准方法来删除表里的行： T

21、RUNCATE TABLE TRUNCATE TABLE语句总是删除整个表中的记录。,32,DELETE与TRUNCATE TABLE的区别,DELETE 如果包含WHERE子句，将删除特定范围的表中数据 执行效率较低 返回被删除的记录行数 面向事务（如果用于事务表中） TRUNCATE TABLE 不能删除部分记录行，而只能删除表中的全部数据 通常执行更快速 返回值为0，而不是实际被删除的行数 没有事务,33,使用预处理语句,MySQL服务器支持预处理语句。当想要执行多个查询，而每个查询之间只有很小的差别时，预处理语句将会非常有用。 例如，可以预备一条语句，然后多次执行它，而每次只是数据值不

22、同。 除了提供执行的方便外，预处理语句还能提高性能。 可以在mysql命令行客户端定义与使用预处理语句来测试与调试程序。,34,用户定义变量,用户可以保存一个值到用户定义的变量里（也称为用户变量），然后在以后执行不同的语句类型包括预处理语句时使用它。 用户变量用var_name表示。可使用SET语句来设置用户定义的变量： SET var_name = expr , var_name = expr ,35,预处理语句示例,下列示例预备了一条语句来确定一个给定国家有多少种语言被使用，然后使用用户定义的变量来执行它多次，并显示结果： mysql PREPARE my_stmt FROM - SELECT COUNT(*) FROM CountryLanguage WHERE CountryCode= ?; mysql SET code = ESP; mysql EXECUTE my_stmt USING code; mysql SET code = RUS; mysql EXECUTE my_stmt USING code; mysql DEALLOCATE PREPARE my_stmt;,36,预备一条语句,可使用PREPARE语句定义一条SQL语句，用以在以后执行