MySQL教程（第4版） 课件 第3章 数据库和表

第3章

数据库和表——MySQL数据库MySQL数据库安装

MySQL

系统时，就生成了系统使用的数据库，包括information_schema、mysql和performance_schema等，MySQL把有关DBMS自身的管理信息都保存在这几个数据库中，如果删除了它们，MySQL将无法正常工作，故请读者操作时千万留神！如果安装时选择安装实例数据库，则系统还有另外两个实例数据库sakila和world。通过以下命令可以查看MySQL已有的数据库：SHOWDATABASES;命令执行结果如图。01创建数据库创建数据库创建数据库语句如下：CREATE{DATABASE|SCHEMA}[IFNOTEXISTS]数据库名[创建选项,...]其中，创建选项：[DEFAULT]CHARACTERSET字符集名|[DEFAULT]COLLATE校对规则名IFNOTEXISTS：在创建数据库前须进行判断，只有该数据库目前尚不存在时才可执行CREATEDATABASE操作。用此选项可以避免出现数据库已经存在却再新建的错误。DEFAULT：指定默认值。CHARACTERSET：指定数据库字符集。COLLATE：指定字符集的校对规则。说明：如果在MySQL环境下采用下列命令设置了字符集，每个数据库创建时不需要单独重新设置：SETCHARACTER_SET_DATABASE='gbk';SETCHARACTER_SET_SERVER='gbk';创建数据库【例】创建test数据库。CREATEDATABASEtest;说明：如果创建数据库不指定选项就使用默认选项参数。如果已经创建了名为test的数据库，重复创建时系统将会提示数据库已经存在的错误信息，不能再创建。使用IFNOTEXISTS子句可不显示错误信息：CREATEDATABASEIFNOTEXISTStest;SHOWDATABASES; #显示的数据库中多了test02修改数据库修改数据库修改数据库语句如下：ALTER{DATABASE|SCHEMA}[数据库名]修改选项[,修改选项]...其中，修改选项：[DEFAULT]CHARACTERSET字符集名|[DEFAULT]COLLATE校对规则名说明：ALTERDATABASE用于更改数据库的全局特性，这些特性储存在数据库目录中的db.opt文件中。用户必须有对数据库进行修改的权限，才可使用ALTERDATABASE。修改数据库的选项与创建数据库的相同，功能不再重复说明。如果语句中将数据库名称忽略，则修改当前（默认）数据库。【例】修改数据库test的默认字符集和校对规则。ALTERDATABASEtestDEFAULTCHARACTERSETgb2312DEFAULTCOLLATEgb2312_chinese_ci;03删除数据库删除数据库删除数据库语句如下：DROPDATABASE[IFEXISTS]数据库名使用IFEXISTS子句，可避免删除不存在的数据库时出现MySQL错误信息。例如，删除test数据库：DROPDATABASEtest;SHOWDATABASES; #显示数据库中没有test04打开和关闭数据库打开和关闭数据库数据库创建后，在同一个会话中就自动打开。下列语句打开指定数据库，使其成为当前数据库：USE数据库名关闭数据库，其后会话就没有当前数据库了。第3章

数据库和表——MySQL表01创

建

表列定义及基本属性列键属性列其他属性列数据类型虚拟列（生成列）由原有的表创建新表创

建

表创建表语句如下：CREATE[TEMPORARY]TABLE[IFNOTEXISTS]表名(列定义,...[表索引][完整性约束])[表选项]说明：TEMPORARY：包含此关键字表示新建的表为临时表，否则创建的表通常称为持久表。IFNOTEXISTS：在创建表前加上一个判断，只有该表目前尚不存在时才创建。列定义：列又称字段。列定义包括列名、数据类型和宽度等，还可包含是否允许空值和完整性约束。表索引：UNIQUEKEY(...)|PRIMARYKEY(...)|INDEX(...)：第1项指定部分列（或单列）值的唯一性，第2项列作为主键，第3项列创建索引。完整性约束：CHECK、FOREIGNKEY(…)：前者定义部分列（或单列）值数据完整性，后者定义本表与参考表的记录完整性。表选项：指定表的属性。创

建

表1．列定义及基本属性列按照下列形式定义：列名数据类型[(长度和小数)][空值][键][字符集][列其他属性][注释]其中：列名：必须符合标识符规则，长度不能超过64个字符，而且在表中要唯一。如果有MySQL保留字则必须用单引号括起来。数据类型[(长度和小数)]：列保存数据类型。整数型、实数型和字符串型需要指定长度，实数型还需要指定小数位。空值：NOTNULL|NULL：指定该列是否允许为空，前者为不允许为空，后者为可以为空。如果不指定，则默认为NULL。字符集：如果列数据类型为字符串型，可以指定存储字符的字符集和校对规则：CHARACTERSET字符集名COLLATE校对规则名注释：COMMENT'注释内容'，列的描述内容，说明列的作用。创

建

表2．列键属性列键属性如下：PRIMARYKEY：列设置为主键。一个表只能定义一个主键，主键一定要为NOTNULL。UNIQUE：列设置为唯一键。将确保所有值都有不同的值，只有NULL值可以重复。一个表可以设置多个列为唯一键。创

建

表3．列其他属性列还可以指定下列属性：AUTO_INCREMENT：设置自增属性，只有数据类型为整型的列才能设置此属性。当插入NULL值或0，将列原来值增1，顺序从1开始。每个表只能有一个AUTO_INCREMENT列，并且必须能被索引。DEFAULT：指定列默认值，默认值必须为一个常数。其中，BLOB和TEXT类型列不能被赋予默认值。UNSIGNED：对于整数类型，指定为无符号整数。ZEROFILL：可用于任何数值类型，用0填充所有剩余列空间。例如，无符号INT的默认宽度是10，因此，当值为4时，将它表示为0000000004。IDENTITY：包含系统所生成序号值的一个标识列，该序号值唯一标识表中的一列，可以作为键值。每个表只能有一个列被设置为标识属性，该列数据类型只能是整型。定义标识属性时，可指定其种子（起始）值、增量值，二者的默认值均为1。系统自动更新标识列值。例如：idintNOTNULLIDENTITYidintNOTNULLIDENTITY(1,1)创

建

表4．列数据类型列数据类型按照下列形式描述：整数类型名[(长度)][UNSIGNED][ZEROFILL]实数类型名[(长度.小数位)][UNSIGNED][ZEROFILL]大数据类型名字符串类型名(长度)[BINARY|ASCII|UNICODE]文本类型名[BINARY]日期时间类型名空间类型名位类型：bit[n]枚举类型：enum(值,...)集合类型：set(值,...)键值类型：json其中，具体类型名如下：整数类型名：tinyint|smallint|mediumint|int|integer|bigint实数类型名：real|double|decimal|numeric大数据类型名：tinyblob|blob|mediumblob|longblob字符串类型名：char|varchar|tinytext|text|mediumtext|longtext日期时间类型名：date|time|datetime|timestamp|year创

建

表5．虚拟列（生成列）虚拟列又称生成列，按照下列形式描述：列名数据类型GENERATEDALWAYSAS(列生成表达式)照表达式计算的值同步变化。【例】在xscj数据库中创建一个学生表，表名为xs。输入以下命令：CREATEDATABASExscj;USExscj;CREATETABLExs(学号 char(6) NOTNULLPRIMARYKEY,姓名 char(4) NOTNULL,专业 char(10) NULL,性别 tinyint(1) NOTNULLDEFAULT1,出生日期 date NOTNULL,总学分 tinyint(1) NULL,备注 text NULL,照片 blob NULL);创

建

表说明：（1）PRIMARYKEY：表示将“学号”列定义为主键。（2）DEFAULT1：表示“性别”的默认值为1。实际上，性别如果仅保存2种状态，可以定义为bit(1)。已经创建的表可以使用以下命令显示表结构：DESCRIBE表名;例如：USExscj; #打开xscj数据库SHOWTABLES; #显示xscj数据库中包含的表DESCRIBExs; #显示xs表结构创

建

表6．由原有的表创建新表除了全新创建，用户也可以直接复制数据库中原有表的结构和数据，用这种方式十分方便、快捷。由原有的表创建新表语句如下：CREATE[TEMPORARY]TABLE[IFNOTEXISTS]表名[LIKE源表名]|[AS(SELECT语句)];说明：（1）使用LIKE关键字创建一个与“源表”相同结构的新表，源表的列名、数据类型、是否空值、主键、默认值、索引、约束、分区等都将被复制，但是源表的记录不会复制，因此创建的新表是一个空表。（2）使用AS关键字可以复制SELECT语句查询的结果表，但源表的一些属性（如主键、生成列等）却不会被复制。创

建

表【例】在xscj数据库中，复制xs表创建表名为xs1的表；再创建一个名为xs2的表，包含xs表的部分指定列。打开xscj数据库：USExscj;CREATETABLExs1LIKExs; #复制xs表创建xs1表结构CREATETABLExs2AS(SELECT学号,姓名,专业,总学分FROMxs); #复制xs表部分列创建xs2表

SHOWTABLES; #（a）DESCRIBExs2; #（b）显示结果如图。

02修

改

表增加修改删除列增加删除列、索引和完整性约束修改表选项修

改

表ALTERTABLE用于修改原有表的结构。例如，可以增加（删减）列、创建（取消）索引、更改原有列的类型、重命名列或表，还可以更改表的注释和表的类型。修改表语句如下：ALTER[IGNORE]TABLE表名[ADD列定义] /*增加列*/[DROP列名] /*删除列*/[MODIFY列名列属性] /*修改列属性*/[ALTER列名SETDEFAULT值|DROPDEFAULT] /*设置默认值和删除默认值*/[RENAME表名 /*表更名*/[CHANGE原列名新列定义修改...] /*修改列名同时修改列属性*/[ADD主键|索引|完整性约束] /*增加表索引和完整性约束*/[DROP列名|索引名|主键|完整性约束名] /*删除表列、索引、主键和完整性约束*/[ORDERBY列名,...] /*列排序*/[表选项] /*增加修改表属性*/修

改

表1．增加修改删除列下面介绍增加修改删除列描述形式。（1）增加列ADD[COLUMN]列定义[FIRST|AFTER列名]列定义参考CREATETABLE语句。FIRST：指定增加列为第1列。AFTER列名：增加在指定列后面。（2）修改和删除指定列的默认值ALTER[COLUMN]SETDEFAULT值|DROPDEFAULT（3）修改列的名称和定义CHANGE旧列名列定义[FIRST|AFTER列名]（4）修改列属性MODIFY[COLUMN]列名列属性（5）删除列DROP列名修

改

表【例】修改xs2表结构。USExscj;ALTERTABLExs2 ADDCOLUMN考评tinyintNULL;ALTERTABLExs2 CHANGE考评考评分tinyint;ALTERTABLExs2 DROP考评分;ALTERTABLExs2 MODIFY专业char(12)NOTNULL;DESCRIBExs2;说明：第1句：在xs2表中增加新的一列“考评”。第2句：把xs2表“考评”列名变更为“考评分”。第3句：把xs2表的“考评分”列删除。第4名：把xs2表“专业”列数据类型改为char(12)。（6）指定记录排序列ORDERBY列名,...用于在创建新表时，让各行（记录）按一定的顺序排列。修

改

表2．增加删除列、索引和完整性约束下面介绍增加删除列、列索引和完整性约束的描述形式。（1）增加列、列索引和完整性约束ADD{INDEX|KEY}索引名索引定义|ADDPRIMARYKEY主键定义|ADDUNIQUE唯一性键名唯一性定义|ADDFOREIGNKEY外键名外键定义|ADDCHECK(完整性约束条件)【例】在xscj数据库的xs2表中，增加主键和生成列（专业编号）。USExscj;ALTERTABLExs2 ADD专业编号char(2)GENERATEDALWAYSAS(SUBSTRING(学号,3,2)), ADDPRIMARYKEY(学号);DESCRIBExs2;修

改

表说明：①xs2表采用“CREATE…ASSELECT…”方式创建，虽然原xs表包含“学号”列主键，但xs2表没有主键。这里给xs2表增加“学号”列主键。②“专业编号”列为char(2)数据类型，它由学号列的第3、4位生成，是虚拟的列。执行后，xs2表的结构如图。修

改

表（2）删除列、列索引、主键和完整性约束DROP[COLUMN]列名|DROP{INDEX|KEY}索引名|DROPPRIMARYKEY|DROPFOREIGNKEY外键约束名|CHECK完整性约束名（3）修改表索引名和表名RENAME{INDEX|KEY}原索引名TO新索引名3．修改表选项具体定义与CREATETABLE语句一样。03表删除和更名更改表名表删除表删除和更名1．更改表名除了上面的ALTERTABLE命令用“RENAME新表名”修改表名，还可以直接用下列语句来更改表的名字。RENAMETABLE

原表名TO新表名,...2．表删除当需要删除一个表时可以使用下列语句。DROP[TEMPORARY]TABLE[IFEXISTS]表名,...说明：这个命令将表的描述、完整性约束、索引及与表相关的权限等一并删除。第3章

数据库和表——表记录的操作01插

入

记

录插入新记录插入图片用已有表记录插入当前表记录替换旧记录系统模式插入记录1．插入新记录向表中插入全新的记录用下列语句。INSERT[选项][INTO]表名[(列名,...)]VALUES({表达式|DEFAULT},...),...或者INSERT[选项][INTO]表名[(列名,...)]|SET列名={表达式|DEFAULT},...[ONDUPLICATEKEYUPDATE列名=表达式,...]插入记录说明：（1）INTO子句：如果只给表的部分列插入数据，需要指定这些列。若没有指定列，表示对所有列插入数据，值的顺序与表结构定义的顺序相同。（2）

VALUES子句：包含各列需要插入的数据清单，数据的顺序要与列的顺序相对应。若表名后不给出列名，则要在VALUES子句中给出每一列（除IDENTITY和timestamp类型的列）的值，如果列值为空，则值必须为NULL，否则会出错。（3）

选项：LOW_PRIORITY：可以使用在INSERT、DELETE和UPDATE等操作中，当原有客户端正在读取数据时，延迟操作的执行，直到没有其他客户端从表中读取数据为止。DELAYED：若使用此关键字，则服务器会把待插入的行放到一个缓冲器中，而发送INSERTDELAYED语句的客户端会继续运行。HIGH_PRIORITY：可以使用在SELECT和INSERT操作中，使操作优先执行。IGNORE：使用此关键字，在执行语句时出现的错误就会被当作警告处理。ONDUPLICATEKEYUPDATE…：使用此选项插入行后，若导致UNIQUEKEY或PRIMARYKEY出现重复值，则根据UPDATE后的语句修改旧行（使用此选项时DELAYED被忽略）。（4）

SET子句：SET子句用于给列指定值，使用SET子句时表名的后面省略列名。要插入数据的列名在SET子句中指定，列名等号后面为指定数据，未指定的列，其值为默认值。插入记录【例】向xscj数据库的xs表（表中列包括学号、姓名、专业、性别、出生日期、总学分、照片、备注）中插入如下一行记录：221101，王林，计算机，1，2004-02-10，15使用下列语句插入记录：USExscj;INSERTINTOxsVALUES('221101','王林','计算机',1,'2004-02-10',15,NULL,NULL);若xs表中性别采用默认值，照片和备注为NULL，插入记录：INSERTINTOxs(学号,姓名,专业,出生日期,总学分)VALUES('221104','韦严平','计算机','2004-08-26',12);使用SET子句插入记录：INSERTINTOxsSET学号='221201',姓名='刘华',专业='通信工程',性别=DEFAULT,出生日期='2004-06-10',总学分=13;插入记录2．插入图片MySQL还支持图片的插入，图片一般可以以路径的形式来存储，即插入图片时可以采用插入图片的存储路径的方式。【例】向xs表中插入一行记录：221102，程明，计算机，1，2005-02-01，15，照片E:\mysql5\data\chenmin.jpg（1）照片列保存文件名INSERTINTOxsVALUES('221102','程明','计算机',1,'2005-02-01',15,NULL,'E:\mysql5\data\chenmin.jpg');SELECT*FROMxs;（2）照片列直接存储图片本身INSERTINTOxsVALUES('221102','程明','计算机',1,'2005-02-01',15,NULL,LOAD_FILE('E:\mysql5\data\chenmin.jpg'));执行结果如图。插入记录3．用已有表记录插入当前表记录下列语句可以从已有表中查询记录插入指定表：INSERT[选项][INTO]表名[(列名,...)]SELECT语句|LIKE[ONDUPLICATEKEYUPDATE列名=表达式,...]说明：（1）SELECT语句中返回的是一个查询到的结果集，INSERT语句将这个结果集插入指定表中，但结果集中每行数据的列数、列的数据类型要与被操作的表完全一致。（2）若当前表结构中存在主键或唯一性列，而插入的数据行中含有与原有行中相同的列值，则INSERT语句无法插入此行。如果希望替换原来记录，需要使用REPLACE语句。【例】向xs1表中插入xs表中的所有记录。USExscj;DROPTABLEIFEXISTSxs1; #删除xs1表CREATETABLExs1LIKExs; #创建xs1表结构INSERTINTOxs1 SELECT*FROMxs; #插入xs表所有记录到xs1表INSERTINTOxs2(学号,姓名,专业,总学分) SELECT学号,姓名,专业,总学分FROMxs; #插入xs表所有记录部分列到xs2表插入记录4．替换旧记录REPLACE语句与INSERT语句基本相同。如果存在相同的记录，则REPLACE语句可以先删除旧记录，再插入新记录。相当于替换旧记录。【例】在xs1表替换下列记录：081211，刘华，通信工程，1，1995-03-08，48，辅修计算机专业，空因为若直接使用INSERT语句，则会产生如下错误。使用REPLACE语句，则可以成功替换原来记录：USExscj;REPLACEINTOxs1VALUES('221201','刘华','通信工程',1,'2004-06-10',13,'辅修计算机',NULL);SELECT*FROMxs1;说明：因为xs1表包含（学号列）主键，由于学号为“221201”记录已经存在，上述语句替换原来记录。插入记录5．系统模式在系统宽松模式（set@@sql_mode=''）下，数据库表数据输入不正确也不会报告错误，而且还会保存到表中，例如：向char(10)类型列输入超过10个字符、向日期类型列输入'2000-00-09'，插入和修改表内容的值为被0除的表达式。如果修改系统为严格模式（set

@@sql_mode=TRADITIONAL|…组合使用各种设置项），出现上述问题，系统就会显示错误信息，而且不会将数据加入数据库表中。02修

改

记

录修改单个表修改多个表修改记录修改表（单表或者多表）中的记录时可使用下列语句。UPDATE[选项]表名,... SET列名=表达式,...] [WHERE条件] [ORDERBY...] [LIMIT行数]SET

子句：用表达式修改列名对应的列（数据类型需要相同）。可包含多个项，中间用逗号隔开，同时修改所在数据行的多个列值。WHERE子句：指定对符合条件的数据行进行修改，否则更新所有行。ORDERBY子句：指定修改记录行的顺序，但与LIMIT子句联用时才起作用。LIMIT子句：指定被修改行的最大值。修改记录1．修改单个表【例】将xs1表中的所有学生的总学分都增加1。将姓名为“刘华”的学生的学号修改为“221200”备注改为“辅修计算机专业”。USExscj;UPDATExs1SET总学分=总学分+1;UPDATExs1SET学号='221200',备注='辅修计算机专业'WHERE姓名='刘华';xs1表中所有学生的总学分都增加了1；姓名为“刘华”的学生学号修改为“221200”，备注改为“辅修计算机专业”。修改记录2．修改多个表【例】将xs1表和xs2表中所有学生的总学分都加4。USExscj;UPDATExs1,xs2SETxs2.总学分=xs2.总学分+4,xs1.总学分=xs1.总学分+4WHERExs2.学号=xs1.学号;SELECT学号,姓名,总学分FROMxs1; #（a）SELECT学号,姓名,总学分FROMxs2; #（b）其中，WHERE包含xs1和xs2两个表的连接条件，命令执行后xs1表和xs2表记录如图。

03删

除

记

录删除表符合条件的记录快速清除表所有记录删除记录1．删除表符合条件的记录DELETE[选项]FROM表名 [WHERE条件] [ORDERBY...] [LIMIT行数]FROM子句：要删除数据的表名。WHERE子句：指定的删除条件。如果省略WHERE子句则删除该表的所有行。ORDERBY子句：指定删除的顺序，此子句只在与LIMIT子句联用时才起作用。LIMIT子句：指定被删除行的最大值。选项：指定删除记录参数，可参考有关文档。删除记录【例】删除xs2表中姓名为刘华的记录。可使用如下语句：USExscj;DELETEFROMxs2WHERE姓名='刘华';SELECT*FROMxs2;查询结果如图。也可以一次删除多个表记录：DELETE[选项]表名[.*],...FROM表名1,...][WHERE条件]删除记录2．快速清除表所有记录使用TRUNCATETABLE语句：TRUNCATETABLE表名说明：（1）该语句将删除指定表中的所有数据，也称其为清除表数据语句。使用时必须十分小心！（2）虽然不带WHERE子句的DELETE语句也能删除表中的全部行，但TRUNCATETABLE比DELETE速度快，且使用的系统和事务日志资源少。（3）对于参与了索引和视图的表，不能使用TRUNCATETABLE删除数据，而应使用DELETE语句。【例3.14】删除xs2表中所有记录。USExscj;TRUNCATETABLExs2;SELECT*FROMxs2;DROPTABLExs2;SELECT*FROMxs2;第3章

数据库和表——表操作综合01准备系统查询需要表完善学生（xs）表样本记录创建课程表（kc）结构和加入样本记录创建成绩表（cj）结构和加入样本记录准备系统查询需要表【例】创建学生成绩数据库（xscj）表结构和表记录。1．完善学生（xs）表样本记录学生（xs）表结构已经创建，同时已经加入了部分样本记录。这里参考附录A，加入其他记录。2．创建课程表（kc）结构和加入样本记录创建课程表（kc）结构：USExscj;CREATETABLEkc(

课程号 char(3) NOTNULLPRIMARYKEY,

课程名 varchar(8) NOTNULL,

开课学期 tinyint NOTNULL,

学时 tinyint NOTNULL,

学分 tinyint NOTNULL);准备系统查询需要表3．创建成绩表（cj）结构和加入样本记录创建成绩表（cj）结构：CREATETABLEcj(

学号 char(6) NOTNULL,

课程号 char(3) NOTNULL,

成绩 tinyint NULL, PRIMARYKEY(学号,课程号));02非基本数据类型表操作创建学生扩展表（xsk）结构插入学生扩展表（xsk）记录修改学生（xsk）扩展表记录非基本数据类型表操作【例】在xscj数据库加入学生扩展表（xsk）。1．创建学生扩展表（xsk）结构参考附录A，在xscj数据库中创建一个学生扩展表结构，表名为xsk。USExscj;CREATETABLExsk(

学号 `char(6)NOTNULLPRIMARYKEY,

爱好 set('书法','绘画','音乐','运动')NULL,

毕业去向 enum('直接就业','考研','考公务员','出国留学','创业'),

家庭地址 json,

地理位置 geometry);说明：（1）“爱好”列，set（集合）数据类型，可实现多选。（2）“毕业去向”列，enum（枚举）数据类型，可实现单选。（3）“家庭地址”列，json数据类型，可实现用简化XML格式描述省、市、区、街道等规范信息。（4）“地理位置”列，geometry数据类型，可描述家庭位置。非基本数据类型表操作2．插入学生扩展表（xsk）记录参考附录A，往学生扩展表xsk中插入记录。USExscj;INSERTINTOxsk (学号,爱好,毕业去向,家庭地址,地理位置) VALUES ( '201101','书法,绘画','考研', '{"省":"江苏","市":"南京","区县":"栖霞","街道":"仙林智谷","电话":}', ST_GeomFromText('POINT(118.91200032.096790)') );INSERTINTOxsk (学号,爱好,毕业去向,家庭地址,地理位置) VALUES ( '201103','书法','直接就业', '{"省":"山东","市":"威海","区县":"龙城","街道":"成山大道102号","电话":}', ST_GeomFromText('POINT(122.25362100137.103460)') );非基本数据类型表操作INSERTINTOxsk (学号,爱好,毕业去向,家庭地址,地理位置) VALUES ( '201203','音乐,运动','直接就业',NULL,NULL);INSERTINTOxsk (学号,爱好,毕业去向,家庭地址,地理位置) VALUES ( '201205','绘画','创业', '{"省":"江苏","市":"南京","区县":"浦口","电话":,"街道":"沿江镇学府路8号"}',NULL );SELECT*FROMxsk;查询结果如图。非基本数据类型表操作3．修改学生（xsk）扩展表记录（1）修改表家庭地址（json类型）列数据USExscj;SELECT*FROMxskWHERE家庭地址ISNULL; #显示家庭地址为NULL的记录UPDATExsk SET家庭地址=JSON_OBJECT("省","黑龙江","市","大庆","区县","高新","街道","学府街99号") WHERE学号='201203';UPDATExsk SET家庭地址=JSON_INSERT(家庭地址,'$."收件人"',"欧阳红",'$."电话"',"1538099366X") WHERE学号='201203';UPDATExsk SET家庭地址=JSON_REMOVE(家庭地址,'$."电话"') WHERE学号='201203'; 非基本数据类型表操作（2）修改表地理位置（geometry类型）列数据UPDATExsk SET地理位置=ST_GeomFromText('POINT(125.14140346.588425)') WHERE学号='201203';SELECT*FROMxsk;查询结果如图。第3章

数据库和表——表

选

项01存

储

引

擎MyISAM存储引擎InnoDB存储引擎CSV存储引擎Memory存储引擎Merge存储引擎Cluster/NDB存储引擎存储引擎MySQL支持很多存储引擎，包括MyISAM、InnoDB、BDB、MEMORY、MERGE、EXAMPLE、ARCHIVE、NDBCluster等，其中InnoDB和BDB支持事务安全。下列语句査看系统所支持的存储引擎：SHOWENGINES;或者SELECT*FROMINFORMATION_SCHEMA.ENGINES;1．MyISAM存储引擎每个MyISAM在磁盘上存储为3个文件，文件名和表名相同，扩展名frm存储表定义，myd存储数据，myi存储索引。在创建表的时候通过DATADIRECTORY和INDEXDIRECTORY属性来指定数据文件和索引文件的存储路径，这样可平均分布IO，加快访问速度。支持3种不同的存储格式：（1）静态表（fixed）：默认的存储格式。静态表中的字段都是非变长字段，每个记录都是固定的长度，当表不包含变长列（例如varchar、text、blob）时，使用这个格式。（2）动态表（dynamic）：包含变长列或者该表创建时用ROW_FORMAT=dynamic指定，则该表使用动态格式存储。它占用空间小，但频繁的更新和删除操作会产生碎片，需要定期用OPTIMIZETABLE（优化）语句或myisamchk-r命令来改善性能，并且在出现故障后较难恢复。（3）压缩表：由myisampack工具创建，占据磁盘空间较小，因为每个记录都是被单独压缩的。存储引擎2．InnoDB存储引擎MySQL5.5之后的默认存储引擎，支持事务和外键。如果应用对事务的完整性有较高的要求，在并发条件下要求数据的一致性，数据操作中包含读、插入、删除和更新，InnoDB是最好的选择。但相比较于MyISAM，写的处理效率差一点，并且会占用更多的磁盘空间来存储数据和索引。特点：（1）自动增长列必须是索引，如果是组合索引，也必须是其第一列。而MyISAM表的自动增长列可以是组合索引的其他列。（2）支持外键约束。这样当某个表被其它表创建了外键参照，那么该表对应的索引和主键禁止被删除。（3）存储数据和索引有共享表空间和独立表空间两种存储方式，通过参数innodb_file_per_table控制，0（或OFF）表示共享表空间（也是默认的），1（或ON）表示独立表空间。表结构保存在.frm文件中，数据和索引保存在idb文件中。存储引擎3．CSV存储引擎该存储引擎表在MySQL安装目录“Data\数据库名”子目录中生成一个.CSV文件。它是一种普通文本文件，每个记录占用一个文本行，各列数据由逗号分隔。但不支持索引，即表没有主键列，不允许表中的字段为空。【例】CSV存储引擎表测试。（1）创建CSV存储引擎表，插入记录：CREATEDATABASEtest;USEtest;CREATETABLEexcelb( id intNOTNULL, name varchar(11)NOTNULL, salary decimal(8,2)NOTNULL)ENGINE=CSV;INSERTINTOexcelbVALUES (1,'A',1.45), (2,'A',0.99);SELECT*FROMexcelb;显示查询结果如图。存储引擎（2）用Excel打开MySQL安装目录下Data子目录test数据库子目录中（C:\ProgramData\MySQL\MySQLServer5.7\Data\test）的excelb.csv文件，可看到上面INSERT语句插入的两条记录。如图。存储引擎4．Memory存储引擎该存储引擎通过在内存中创建临时表来存储数据。每个表对应一个只存储表结构的磁盘文件，该文件的文件名和表名是相同的，类型为.frm。由于它的数据是存放在内存中的，并且默认使用HASH索引，所以访问速度特别快，但同时也造成了缺点，就是数据库服务一旦关闭，数据就会丢失，另外对表的大小有限制。每个表中可存储数据量的大小受到max_heap_table_size变量的约束，初始值是16MB，可以在定义表的时候通过max_rows指定表的最大行数。MEMORY的主要特性如下：（1）每个表可以有多达32个索引、每个索引16列以及最大键长度500字节。在表中可以有非唯一键；对可包含NULL值的列索引；可执行HASH和BTREE索引。（2）表使用一个固定的记录长度格式。（3）支持AUTOINCREMENT列，不支持BLOB或TEXT列。（4）表在所有客户端之间共享。存储引擎【例】Memory存储引擎测试。（1）创建memory存储引擎表，插入记录：USEtest;CREATETABLEmemoryb( id intNOTNULL, name varchar(11)NOTNULL, salary decimal(8,2)NOTNULL)ENGINE=memory;INSERTINTOmemorybVALUES (1,'A',1.45), (2,'A',0.99);SELECT*FROMmemoryb;显示查询结果如图。存储引擎（2）停止MySQL服务，然后重新启动，查询memoryb记录。USEtest;SELECT*FROMmemoryb;显示查询结果如图。存储引擎Merge表在磁盘上保留两个文件，.frm文件存储表的定义，.mrg文件存储组合表的信息。【例】Merge存储引擎测试。（1）创建表指定存储引擎。USEtest;DROPTABLEIFEXISTSmerge1,merge2,mergeg; CREATETABLEmerge1( id int, name varchar(11), salary decimal(8,2))ENGINE=MYISAM;CREATETABLEmerge2LIKEmerge1;CREATETABLEmergeg( id int, name varchar(11), salary decimal(8,2))ENGINE=MERGEUNION=(merge1,merge2)INSERT_METHOD=LAST;存储引擎（2）向表中插入记录。USEtest;INSERTINTOmerge1VALUES (1,'A',1.45), (2,'A',0.99);INSERTINTOmerge2VALUES (3,'B',2.10), (4,'B',4.29);INSERTINTOmergegVALUES (5,'B',3.10), (6,'B',4.36);SELECT*FROMmerge1; #（a）SELECT*FROMmerge2; #（b）SELECT*FROMmergeg; #（c）显示merge1和merge2表中所有记录存储引擎运行结果如图。

（3）MERGE表删除记录。USEtest;DELETEFROMmergeg;SELECT*FROMmerge1;SELECT*FROMmerge2;存储引擎6．Cluster/NDB存储引擎所谓“集群”是一种被广泛使用的分布式数据库系统，由众多网络数据库NDB节点计算机组成一个群体，每个NDB上都存有完整的数据库副本；集群中有一台管理它的主机，可为整个集群配置NDB节点和监控各节点的状态；外部用户或应用程序则通过SQL节点来访问集群数据，一个典型的集群系统的架构原理如图。02表

空

间表空间类型表空间创建和使用表空间中表的移动删除表空间表

空

间1．表空间类型（1）系统表空间系统表空间是由InnoDB引擎管理的一个特殊的共享表空间。默认情况下，用户创建的表存放在系统表空间中，文件存放在MySQL默认的目录，采用默认的文件名。但用户可以在配置文件中通过下列参数进行配置。innodb_data_file_path：设定表空间大小及文件。例如：innodb_data_file_path=ibdata1:50M; ... ibdata2:50M:autoextend[:max:空间大小]其中，autoextend表示自动扩展（默认每次扩展64M），max为最大文件大小，只能在最后一个文件上指定。默认值为ibdata1:12M:autoextend。innodb_data_home_dir：设定表空间的存放位置格式为：innodb_data_home_dir=/文件路径表

空

间（2）通用表空间通用表空间是用来存放用户创建的表数据及索引的一个共享表空间，可指定多个表存放在同一通用表空间内，表空间文件的存放路径是用户创建时指定的绝对路径，否则将存放在数据库默认路径下。通用表空间是用户创建和命名的，用名称引用。（3）临时表空间临时表空间用于暂存MySQL中的临时表，通过innodb_temp_data_file_path参数配置表空间临时数据文件的相对路径、名称、大小和属性。临时表空间在每次启动MySQL服务器时创建，在正常关闭时将被删除，但服务器意外停止时则不会删除，这种情况下需要数据库管理员手动删除临时表空间或重新启动MySQL服务器。表

空

间（4）日志表空间MySQL日志表空间包括重做日志表空间（REDO表空间）和撤销日志表空间（UNDO表空间）。REDO表空间用于在数据库崩溃后进行数据恢复，保证数据完整性，表空间位于数据库默认路径下的ib_logfile0、ib_logfile1等文件中。UNDO表空间用于事务回滚和多版本控制（MVCC），位于数据库默认路径下的ibdata1文件中。（5）独立表空间独立表空间每一个表对应一个.ibd文件存储表的数据内容以及索引。该文件可以在不同的数据库中移动。“DROPTABLE表名”操作自动回收表空间，删除大量数据后通过“ALTERTABLE表名ENGINE=INNODB”和“TURNCATETABLE表名”回缩不用的空间。表

空

间2．表空间创建和使用通过下列语句创建表空间：CREATE[UNDO]TABLESPACE表空间名 ADDDATAFILE文件名;其中，带UNDO指明创建UNDO日志表空间，否则创建的是通用表空间；ADDDATAFILE后的“文件名”是对应表空间文件的名称。【例】通用表空间的创建和使用。（1）创建通用表空间。CREATETABLESPACEmyGSpace ADDDATAFILE'myGSpace.ibd’ Engine=InnoDB;此时，在MySQL的数据目录（…\Data）下可找到该表空间对应的数据文件myGSpace.ibd，如图。表

空

间（2）在创建表结构时指定表空间。USExscj;DROPTABLEIFEXISTSxsb;CREATETABLExsb(学号 char(6) NOTNULLPRIMARYKEY,姓名 char(4) NOTNULL,专业 char(10) NULL,性别 tinyint(1) NOTNULLDEFAULT1,出生日期 date NOTNULL,总学分 tinyint(1) NULL,备注 text NULL,照片 blob NULL)TABLESPACEmyGSpace;INSERTINTOxsbSELECT*FROMxs;说明：虽然在xscj数据库上创建xsb表，但由于指定了表空间，实际创建的表存储在（…\Data）下的myGSpace.ibd文件中，而xscj数据库目录中只有xsb.frm文件而没有xsb.ibd文件。表

空

间（3）在修改表结构时指定表空间。USExscj;DROPTABLEIFEXISTScjb;CREATETABLEcjbASSELECT*FROMcj;ALTERTABLEcjbTABLESPACEmyGSpace;（4）查看通用表空间信息。SELECTNAME,FLAG FROMinformation_schema.INNODB_SYS_TABLESWHERESPACE_TYPE='General';查询结果如图。说明：“General”表示表空间类型是通用表空间，表采用的表空间的类型信息只能从information_schema系统库的INNODB_TABLES表中查到。表

空

间【例】独立表空间的创建和使用。SETGLOBALinnodb_file_per_table=ON;DROPTABLEIFEXISTSkcb;CREATETABLEkcbASSELECT*FROMkc;SELECTNAME,SPACE_TYPEFROMinformation_schema.INNODB_SYS_TABLES WHERENAME='xscj/kcb';查询结果如图。说明：因为当前会话前设置了innodb_file_per_table=ON，而CREATETABLEkcb...创建表又没有指定表空间，该表默认就为独立表空间。“Single”表示表空间类型是独立表空间。表

空

间3．表空间中表的移动ALTERTABLE语句通过指定表空间项，可使表在系统表空间、独立表空间、通用表空间等不同类型的表空间之间自由移动，语句格式为：ALTERTABLE表名TABLESPACE=表空间名/类型;其中，“表空间名”是要移动到的表空间的名称；“类型”用来标识系统表空间或独立表空间，“innodb_system”表示系统表空间，“innodb_file_per_table”是独立表空间。表

空

间【例】表空间移动。（1）将kcb表由独立表空间移入系统表空间。ALTERTABLExscj.kcbTABLESPACE=innodb_system;此时，在…\Data\xscj目录下只有kcb.frm文件，没有该表独立表空间的kcb.ibd文件。（2）将kcb表由系统表空间移入独立表空间。ALTERTABLExscj.kcbTABLESPACE=

innodb_file_per_table;此时，在…\Data\xscj目录下又产生了独立表空间的kcb.ibd文件。（3）将kcb表由独立表空间移至通用表空间myGSpace。ALTERTABLExscj.kcbTABLESPACE=

myGSpace;SELECTNAME,FLAGFROMinformation_schema.INNODB_SYS_TABLES WHERESPACE_TYPE='General';此时在…\Data\xscj目录下独立表空间的kcb.ibd文件又不见了，而通用表空间myGSpace中则包含了3个表xsb、cjb和kcb。表

空

间4．删除表空间删除表空间使用下列语句：DROP[UNDO]TABLESPACE表空间名;对于不同类型的表空间，删除时需要满足不同的要求。共享表空间必须先删除表后才能删除表空间。03表记录分区范围分区列表分区散列分区键分区子分区分区管理表记录分区1．范围分区每个分区包含分区表达式值位于给定范围内的行，范围应该是连续的而不是重叠的。范围分区定义如下：PARTITIONBYRANGE(表达式|列名) #（a）|PARTITIONBYRANGECOLUMNS(列名表) #（b）PARTITIONS数量[( PARTITION分区名VALUESLESSTHAN(值表), ...)];说明：（a）范围分区（BYRANGE）含列表达式或者列只能为整数类型。分区的列值为NULL，将其视为小于任何其他值，表达式可以包含部分系统函数，例如YEAR(出生日期)。（b）范围列（BYRANGECOLUMNS）接受一个或多个列的列名表，列的数据类型可以整数、字符串（text和blob除外）、日期（日期时间）。表记录分区1）创建分区表xsb【例】创建xscj数据库一个分区表xsb，按照学生入学年份划分为3个分区。USExscj;DROPTABLEIFEXISTSxsb;#SETGLOBALinnodb_file_per_table=ON; #（e）CREATETABLExsb(

学号 char(6) NOTNULLPRIMARYKEY,

姓名 char(4) NOTNULL,

专业 char(10) NULL,

性别 tinyint(1) NOTNULLDEFAULT1,

出生日期 date NOTNULL,

总学分 tinyint(1) NULL,

备注 text NULL,

照片 blob NULL) ENGINE=INNODB PARTITIONBYRANGECOLUMNS(学号) #（a） PARTITIONS3 ( PARTITIONp0VALUESLESSTHAN('21'), #（b） PARTITIONp1VALUESLESSTHAN('22'), PARTITIONp2VALUESLESSTHANMAXVALUE #（c） );INSERTINTOxsbSELECT*FROMxs; #（d）表记录分区说明：（a）因为学号列为字符型，所以不能采用RANGE(学号)分区。（b）定义每一个分区：PARTITION后面跟的是分区的名称（p0，p1，p2），名称遵循标识符的规则，不区分大小写。如果没有指定分区的名称，自动为分区命名p0、p1、p2、…、pn-1（n是分区数量）。（c）因为按照范围（BYRANGE）小于（LESSTHAN）分区，最后一个采用MAXVALUE表示最大值。（d）插入记录到分区表xsb中。（e）如果创建独立表空间表xsb，那么，一个分区就会对应一个数据文件，如图为xsb表p0、p1、p2分区数据文件和表结构等信息文件。表记录分区2）查询分区信息MySQL的表分区信息统一存储在系统数据库information_schema的PARTITIONS表中，用户可根据需要查询指定表分区的情况。【例】查询xsb表分区信息。SELECT PARTITION_NAME分区名称, PARTITION_ORDINAL_POSITION排序, PARTITION_METHOD分区类型, PARTITION_EXPRESSION表达式, PARTITION_DESCRIPTION描述, CREATE_TIME创建时间, TABLE_ROWSAS记录数 FROMinformation_schema.PARTITIONS WHERETABLE_SCHEMA='xscj'ANDTABLE_NAME='xsb';分区信息如图。表记录分区3）查询分区数据记录在对表分区后，就可以使用包含PARTITION(分区名,...)子句的SELECT语句分别单独查询存储在不同分区中的数据记录。【例】查询xsb表分区记录。USExscj;SELECT学号,姓名,性别FROMxsbPARTITION(p1); #（a）SELECT学号,姓名,性别 FROMxsbPARTITION(p1,p2) WHERE性别=0; #（b）SELECT学号,姓名FROMxsbWHERE学号>'2212'; #（c）SELECT学号,姓名,出生日期FROMxsbWHEREYEAR(出生日期)>2003; #（d）显示结果如图。

表记录分区4）修改分区表修改分区表就是在ALTERTABLE语句修改表结构的同时使用PARTITIONBY子句描述修改的分区信息，包括对未分区的表进行分区和对已分区的表重新规划分区，语句格式如下：ALTERTABLE表名 PARTITIONBY分区类型(分区表达式) (

分区定义,... );这实际上就是将新的分区类型及定义信息完整写出来，用以覆盖已有的分区。表记录分区【例】修改cjb表结构，加入表分区信息。按照课程号分为3个分区。（1）创建cjb表：USExscj;DROPTABLEIFEXISTScjb;CREATETABLEcjbSELECT*FROMcj;（2）修改cjb表结构，加入分区信息：ALTERTABLEcjb ADDPRIMARYKEY(学号,课程号) #（a） PARTITIONBYRANGECOLUMNS(课程号) #（b） PARTITIONS3 ( PARTITIONCj100VALUESLESSTHAN('200'), PARTITIONcj200VALUESLESSTHAN('300'), PARTITIONcj300VALUESLESSTHANMAXVALUE );SELECTcount(*)FROMcjbPARTITION(cj100); 查询显示结果如图。表记录分区2．列表分区列表分区中，每个分区都是根据一组值表中的一个列值的成员关系来定义和选择的，而不是根据一个连续的值范围来选择。它也有两种形式如下。PARTITIONBYLIST(表达式|列名) #（a）|PARTITIONBYLISTCOLUMNS(列名表) #（b）PARTITIONS数量[( PARTITION分区名VALUESIN(值表), #（a） ...)];列表分区与范围分区相比有下列不同。（1）范围分区是小于指定值的范围内的记录均进入分区，而列表分区只有列的值或者表达式的值在值表中才能加入分区。也就是说，范围分区的条件是一条线，而列表分区条件是多个点。（2）由于列表分区的记录值只能在分区定义的IN子句后的值表中选择，向这种分区表中不能插入任意值的记录。（3）列表分区将空值NULL也看作是一个值，像对待任何其他的值一样。但当且仅当分区定义中的某一个分区使用包含NULL的值表定义时，列表分区表才允许分区列上的NULL值插入。表记录分区【例】对kcb表按“开课学期”年度分区。USExscj;DROPTABLEIFEXISTSkcb;CREATETABLEkcbSELECT*FROMkc;ALTERTABLEkcb PARTITIONBYLIST(开课学期) ( PARTITION一学年课VALUESIN(1,2), PARTITION二学年课VALUESIN(3,4), PARTITION三学年课VALUESIN(5,6), PARTITION四学年课VALUESIN(7) );SELECT*FROMkcbPARTITION(一学年课);显示结果如图。表记录分区