《MySQL 8.0数据库管理与应用》第2章 数据库与表

第2章数据库与表2.1.1创建数据库使用CREATEDATABASE语句创建数据库：CREATE{DATABASE|SCHEMA}[IFNOTEXISTS]数据库名[创建选项]...创建选项：[DEFAULT]CHARACTERSET[=]字符集名|[DEFAULT]COLLATE[=]排序规则名|DEFAULTENCRYPTION[=]{'Y'|'N'}CREATESCHEMA是CREATEDATABASE的同义词。使用SHOWCREATEDATABASE语句可以显示创建命名数据库的CREATEDATABASE语句，可以查看该数据库的默认字符集和排序规则。例如：mysql>CREATEDATABASEdb1;mysql>SHOWCREATEDATABASEdb1\G2.1数据库操作2.1.2列举数据库使用SHOWDATABASES列举MySQL服务器主机上的所有数据库：SHOW{DATABASES|SCHEMAS}[LIKE'模式'|WHERE表达式]其中SHOWSCHEMAS是SHOWDATABASES的同义词。LIKE子句（如果存在）指示要匹配的数据库名称，'模式'是一个字符串，其中可以包含SQL通配符“%”和“_”，百分号“%”表示任意多个字符，下画线“_”表示单个任意字符。也可以使用WHERE子句来选择使用更一般条件的行。用户只能看到具有某种权限的数据库，除非拥有全局SHOWDATABASES权限。也可以使用mysqlshow命令获取此数据库清单。如果服务器是使用--skip-show-database选项启动的，则不能使用SHOWDATABASES语句，除非具有SHOWDATABASES权限。也可以使用mysqlshow命令列出数据库清单。2.1数据库操作2.1.3设置默认数据库使用USE语句设置默认的数据库：USE数据库名USE语句可以通知MySQL将具有指定名称的数据库作为当前的默认数据库来使用，在后续对数据库对象操作（例如创建表）的语句中，这个数据库将保持为默认数据库，直到语句段结束，或者直到发布另一个不同的USE语句。在MySQL中，可以使用DATABASE()函数来获取当前的默认数据库的名称。如果没有默认数据库，则DATABASE()函数将返回NULL。2.1数据库操作2.1.4修改数据库使用ALTERDATABASE语句修改数据库：ALTER{DATABASE|SCHEMA}[数据库名]修改选项...修改选项：[DEFAULT]CHARACTERSET[=]字符集名|[DEFAULT]COLLATE[=]排序规则名|DEFAULTENCRYPTION[=]{'Y'|'N'}ALTERDATABASE语句用于更改数据库的整体特征。这些特征存储在数据字典中。要使用ALTERDATABASE语句，需要拥有修改数据库的权限。ALTERSCHEMA是ALTERDATABASE的同义词。数据库名指定要修改的数据库。如果省略数据库名，则ALTERDATABASE语句用于当前的默认数据库。2.1数据库操作2.1.5删除数据库使用DROPDATABASE语句从MySQL服务器上删除数据库：DROP{DATABASE|SCHEMA}[IFEXISTS]数据库名数据库名指定要删除的数据库。IFEXISTS用于防止在数据库不存在时发生错误。DROPDATABASE必须拥有数据库的DROP权限。DROPSCHEMA是DROPDATABASE的同义词。由于执行DROPDATABASE语句时将从给定的数据库目录中删除由MySQL在正常操作期间创建的文件和目录，从而删除数据库中的所有表并删除整个数据库，因此使用这个语句时要非常小心，以防止发生误操作。也可以使用mysqladmin客户端工具来删除数据库。2.1数据库操作2.2.1数据类型1.数值类型BIT[(M)]INYINT[(M)][UNSIGNED][ZEROFILL]BOOL、BOOLEANSMALLINT[(M)][UNSIGNED][ZEROFILL]MEDIUMINT[(M)][UNSIGNED][ZEROFILL]INT[(M)][UNSIGNED][ZEROFILL]INTEGER[(M)][UNSIGNED][ZEROFILL]BIGINT[(M)][UNSIGNED][ZEROFILL]SERIALFLOAT[(M,D)][UNSIGNED][ZEROFILL]DOUBLE[(M,D)][UNSIGNED][ZEROFILL]DECIMAL[(M[,D])][UNSIGNED][ZEROFILL]DEC[(M[,D])][UNSIGNED][ZEROFILL]、NUMERIC[(M[,D])][UNSIGNED][ZEROFILL]、FIXED[(M[,D])][UNSIGNED][ZEROFILL]2.字符串类型

CHAR[(M)]VARCHAR(M)BINARY[(M)]VARBINARY(M)TINYBLOBTINYTEXT[(M)]BLOB[(M)]TEXT[(M)]MEDIUMBLOBMEDIUMTEXT[(M)]LONGBLOBLONGTEXT[(M)]ENUM('value1','value2',...)SET('value1','value2',...)3.日期时间类型

TIMEDATEDATETIMETIMESTAMPYEAR2.2表操作2.2.2存储引擎InnoDB：是MySQL8.0中的默认存储引擎。MyISAM：是在Web数据仓库和其他应用环境下最常使用的存储引擎之一。Memory：将所有数据存储在RAM中，可以在需要快速查找非关键数据的环境中提供快速访问。CSV：实际上是以逗号分隔数据的文本文件，它允许以CSV格式导入或转储数据。Archive：使用紧凑的无索引表，可用于存储和检索大量很少引用的历史、存档、或安全审计信息。Blackhole：接受但不存储数据，类似于Unix/dev/null设备。NDB：是一种群集数据库引擎，特别适用于具有高性能查找要求的应用程序。Merge：允许MySQLDBA或开发人员将一系列相同的MyISAM表以逻辑方式组合在一起，并将它们作为一个对象来用。Federated：将多个分离的MySQL服务器链接起来，从多个物理服务器创建一个逻辑数据库。Example：此引擎用作MySQL源代码中的示例，说明如何编写新的存储引擎。2.2表操作2.2.3创建表使用CREATETABLE语句在当前数据库中创建表：CREATE[TEMPORARY]TABLE[IFNOTEXISTS]表名(列定义,...)[CHARACTERSET字符集名][COLLATE排序规则名][COMMENT'表注释文字']ENGINE=存储引擎名列定义：列名数据类型[NOTNULL|NULL][DEFAULT默认值][AUTO_INCREMENT][UNIQUE[KEY]|PRIMARYKEY][COMMENT'列注释文字']2.2表操作2.2.4查看表信息1.使用SHOWTABLES语句列出数据库中的所有非临时表：SHOW[FULL]TABLES[FROM数据库名][LIKE'模式’]2.使用SHOWCREATETABLE语句可示用于创建给定表的CREATETABLE语句：SHOWCREATETABLE表名3.使用SHOWCOLUMNS语句查看一个给定表中各列的信息：SHOW[FULL]COLUMNSFROM表名[FROM数据库名][LIKE'模式’]4.使用DESCRIBE语句获取指定表中各列的信息：{DESCRIBE|DESC}表名[列名|通配符]2.2表操作2.2.5修改表使用ALTERTABLE语句修改表结构：ALTER[IGNORE]TABLE表名修改选项[,修改选项]...修改选项：ADD[COLUMN]列定义[FIRST|AFTER列名]|ADD[COLUMN](列定义,...)|ALTER[COLUMN]列名{SETDEFAULT常量|DROPDEFAULT}|CHANGE[COLUMN]old_列名列定义[FIRST|AFTER列名]|MODIFY[COLUMN]列定义[FIRST|AFTER列名]|DROP[COLUMN]列名|DROPPRIMARYKEY|RENAME[TO]新表名|ORDERBY列名|[DEFAULT]CHARACTERSET[=]字符集名|[DEFAULT]COLLATE[=]排序规则名]|COMMENT[=]'表注释文字'|ENGINE[=]存储引擎名2.2表操作2.2.6复制表通过复制现有表的结构和数据来创建表：CREATE[TEMPORARY]TABLE[IFNOTEXISTS]表名[(]LIKE源表名[)]|[AS]SELECT...其中表名指定要创建的新表，源表名表示现有的表。使用LIKE子句可以创建一个与现有表结构相同的新表，其中的列名、数据类型、是否允许为空等属性以及索引都被复制，但不复制表中的数据，因此所创建的新表是一个空表。使用AS子句可以复制由SELECT语句返回的数据，但索引和完整性约束不会被复制。2.2表操作2.2.7重命名表1.在ALTERTABLE语句中使用RENAME子句对指定表进行重命名：ALTERTABLE旧表名RENAMETO新表名例如，使用下面的语句可以将表test重命名为demo：ALTERTABLEtestRENAMETOdemo;2.使用RENAMETABLE语句对一个或多个表进行重命名：RENAMETABLE旧表名1TO新表名1[,旧表名2TO新表名2]...其中“旧表名1”和“旧表名2”表示表的原来名称，“新表名1”和“新表名2”指定表的新名称。与ALTERTABLE语句不同的是，使用RENAMETABLE语句可以同时对多个表进行重命名。例如：RENAMETABLEdemoTOtest,demo2TOtest2;2.2表操作2.2.8删除表使用DROPTABLE语句可以从数据库中删除一个或多个表，语法格式如下。DROP[TEMPORARY]TABLE[IFEXISTS]表名[,表名]...其中表名表示待删除的表的名称。当使用TEMPORARY关键词时，这个语句只删除临时表。对于不存在的表，使用IFEXISTS以防止错误发生。例如，下面的语句用于删除当前数据库中的test1和test2表：DROPTABLEIFEXISTStest1,test2;2.2表操作2.3.1主键约束主键（PrimaryKey）是表中的一列或多列的组合，由多列组成的主键也称为复合主键，主键的值称为键值，它能够唯一地标识表中的一行。在表中创建主键时，将会在主键列上创建唯一性索引，从而实现数据的唯一性，使用主键进行查询时还会加快数据访问速度。使用主键约束时必须遵循以下规则：每个表只能定义一个主键。主键的值必须能够唯一标识表中的每一行，表中两个不同行的主键值不能相同，并且不能为NULL。这条规则称为唯一性规则。复合主键不能包含不必要的多余列。如果从一个复合主键中删除一列后，剩下的列构成的主键仍能唯一标识表中的每一行，则这个复合主键就是不正确的。这条规则称为最小化规则。在复合主键中某一列的值可以重复，但复合主键中所有列的组合键必须是唯一的。一个列名在复合主键中只能出现一次。在MySQL中，可以在CREATETABLE或ALTERTABLE语句中使用PRIMARYKEY子句在表中定义主键。如果要使用主键作为列的完整性约束，则应当在相应列定义中添加关键字PRIMARYKEY。如果使用主键作为表的完整性约束，则应在定义的最后列后面添加PRIMARYKEY（列名,...）子句。2.3数据完整性约束2.3.2候选健约束候选键可以是表中的一列，也可以是表中多列的一个组合。任何时候，候选键的值必须是唯一的。候选键与主键之间的区别表现在以下几个方面。一个表中只能有一个主键，但一个表中可以有多个候选键，甚至不同候选键之间还可以重合。例如，在col1和col2列上定义了一个候选键，在col2和col3列上定义了另一个候选键，这两个候选键在col2列上重合了，这在MySQL中是允许的。主键的值不允许为NULL，候选键的值则可以取NULL，不过必须使用NULL或NOTNULL进行声明。一般情况下，创建主键时会自动生成PRIMARYKEY索引，创建候选键时会自动生成UNIQUE索引。候选键约束有两种实现方式：如果作为列完整性约束实现，则应在列定义中添加UNIQUE关键字；如果作为表完整性约束实现，则应在最后的列定义后面添加UNIQUE(列名,...)子句。2.3数据完整性约束2.3.3参照完整性约束参照完整性约束是指表（子表）中的某一列表现为一个外键，可以引用另一个表（父表）中的主键或候选键。例如，成绩表中的学号必须出现在学生表中，成绩表中的课程编号也必须出现在课程表中。在这个关系中，学生表和课程表为父表，成绩表为子表，成绩表中的学号列和课程编号列都表现为外键，。参照完整性约束可以在CREATETABLE或ALTERTABLE语句中使用FOREIGNKEY...REFERENCES子句来定义：... /*创建或修改子表*/[ADD]FOREIGNKEY[索引名](索引列,...) /*外键*/REFERENCES表名[(索引列,...)] /*父表和被引用列*/[ONDELETE{RESTRICT|CASCADE|SETNULL|NOACTION}][ONUPDATE{RESTRICT|CASCADE|SETNULL|NOACTION}]索引列：列名[(长度)][ASC|DESC]2.3数据完整性约束2.3.4检查完整性约束检查完整性约束是指在CREATETABLE语句中使用CHECK子句添加的约束条件，通过该约束条件可以限制在列中输入的值，语法格式如下。...CHECKL(条件)其中条件指定列值应满足的条件。检查完整性约束只能在创建表时定义，它可以在某个列上定义，也可以在表级别定义。注意：对于所有的存储引擎，CHECK子句都会被分析，但是会被忽略。接受该子句但又忽略子句的原因是为了提高兼容性，以便更容易地从其他SQL服务器中导入代码，并正常运行应用程序。2.3数据完整性约束2.3.5命名完整性约束为了修改和删除完整性约束，要求定义约束时对其进行命名。要对完整性约束进行命名，可以在约束定义之前使用CONSTRAINT子句，语法格式如下。CONSTRAINT[字符串]{PRIMARYKEY...|FOREIGNKEY...|CHECK...}其中字符串指定所定义约束的名称，该名称在数据库中必须是唯一的。如果省略字符串参数，则MySQL会自动创建这个名称。CONSTRAINT子句要放在在相应的约束前面，只能给表完整性约束指定名称，而不能给列完整性约束指定名称。当定义完整性约束时，应当尽量给约束指定一个名称，以便在修改或删除约束时快速找到它们。2.3数据完整性约束2.3.6删除完整性约束在MySQL中，可以在ALTERTABLE语句中使用DROP子句来删除完整性约束，语法格式如下。ALTERTABLE表名DROP{PRIMARYKEY|FOREIGNKEY外键名|INDEX索引名}其中，PRIMARYKEY指定删除主键约束，FOREIGNKEY指定删除外键约束，INDEX指定删除候选键约束。从图书表中删除候选键约束。mysql>USEtest;mysql>ALTERTABLE图书DROPINDEXisbn;2.3数据完整性约束2.4.1插入记录1.INSERT...VALUES语句通过INSERT...VALUES语句可以使用指定的值向表中插入一条或多条记录：INSERT[LOW_PRIORITY|HIGH_PRIORITY][IGNORE][INTO]表名[(列名,...)]VALUES({表达式|DEFAULT},...),(...),...[ONDUPLICATEKEYUPDATE列名=表达式,...]2.INSERT...SELECT语句通过执行INSERT...SELECT语句可以使用一个或多个现有表中的数据向另一个表中插入多条记录：INSERT[INTO]表名[(列名,...)]SELECT...[ONDUPLICATEKEYUPDATE列名=表达式,...]2.4表记录操作2.4.2修改记录在MySQL中，可以通过执行UPDATE语句使用新值来更新表中的各个列。单表语法UPDATE[LOW_PRIORITY][IG