《MYSQL数据库与表的创建》由会员分享，可在线阅读，更多相关《MYSQL数据库与表的创建（62页珍藏版）》请在金锄头文库上搜索。
1、数据库技术与应用,第二章 MYSQL数据库与表的创建,第2页,本章主要内容,2.1 MYSQL数据库 2.2 MYSQL数据类型 2.3 表的创建 2.4 表结构的修改 2.5 表的删除 2.6 向表中添加数据 2.7 表的索引 2.8 简单数据库备份与数据导入导出,第3页,2.1 MYSQL数据库,任何一个数据库，都是由各种数据库对象组织而成的，比如表、索引、视图等等。而操作的第一步，就是要创建数据库。本节将对下面两个问题进行讲解。 2.1.1 MYSQL模式简介 2.1.2 MYSQL数据库的创建与删除,第4页,2.1.1 MYSQL模式简介,与其他数据库不同，MYSQL可以运行在不同的S
2、QL MODE模式下。SQL Mode定义了MYSQL应支持的SQL语法、数据校验等，便于在不同的环境中使用MYSQL。 SQL Mode解决以下问题 完成不同严格程度的数据校验，有效保障数据准确性。 将SQL Mode设置为ANSI模式，保证大多数SQL符合标准的SQL语法，应用在不同的数据库之间迁移时，不需要对业务SQL进行较大的修改。 设置SQL Mode可以使MYSQL上的数据更方便的迁移到目标数据库中。,第5页,2.1.1 MYSQL模式简介,MYSQL中常用的SQL Mode,实际上，第一列模式都是一些原子模式的组合，类似于角色和权限的关系。,第6页,MYSQL模式操作实例,第7页
3、,MYSQL模式操作实例,第8页,2.1.2 MYSQL数据库的创建与删除,MYSQL系统数据库说明： Information_schema：存储系统的数据库对象信息，如用户表信息、列信息、权限信息、字符集信息、分区信息等。mysql：存储系统的用户权限信息 Test:系统自动创建的测试数据库，任何用户都可以使用 MYSQL数据库操作命令： 创建数据库：create database dbname 删除数据库：drop database dbname 显示系统中所有数据库：show databases 选择数据库：use dbname 查看数据库中所有数据表：show tables,第9页,2
4、.2 MYSQL数据类型,在开始创建表之前，必须了解表中字段可以选择的数据类型（即域定义），使得字段使用合理的数据类型，从而有效提高数据库的性能：存储空间小、查询速度快。 2.2.1 数值类型 2.2.2 日期时间类型 2.2.3 字符串类型,第10页,2.2.1 MYSQL中数值类型,第11页,2.2.1 MYSQL中数值类型-说明,整数类型支持INT(n)形式，用于指定整数值的显示宽度，如显示宽度小于n时从左侧填满宽度。显示宽度并不限制可以在列内保存的值的范围，也不限制超过列的指定宽度的值的显示。当结合可选扩展属性ZEROFILL使用时， 默认补充的空格用零代替。例如，对于声明为INT(5
5、) ZEROFILL的列，值4检索为00004。 所有整数类型可以有一个可选(非标准)属性UNSIGNED。在列内只允许非负数和该列需要较大的上限数值范围时可以使用无符号值。如果为一个数值列指定ZEROFILL，MySQL自动为该列添加UNSIGNED属性。 MYSQL小数有两种方式：浮点数和定点数。浮点数包括float、double，为近似数据类型。定点数只有decimal，用于保存必须为确切精度的值，例如货币数据。 浮点和定点类型也可以为UNSIGNED。同数类型，该属性防止负值保存到列中。然而，与整数类型不同的是，列值的上范围保持不变。 浮点数和定点数都可以使用数据名后加“(M,D)”形
6、式。例如：salary DECIMAL(5,2)在该例子中，5是精度，2是标度。精度表示保存值的主要位数，标度表示小数点后面可以保存的位数。 BIT数据类型可用来保存位(二进制数)字段值。如果为BIT(M)列分配的值的长度小于M位，在值的左边用0填充。例如，为BIT(6)列分配一个值b101，其效果与分配b000101相同。 整数类型有一个属性：AUTO_INCREMENT，用于定义自动增长字段。每增加一行增加1。一个表最多只能定义一个AUTO_INCREMENT列，应定义为NOT NULL，并定义为PRIMARY KEY或UNIQUE。,第12页,2.2.2 时间日期类型,每个时间类型有一个
7、有效值范围和一个“零”值，当指定不合法的MySQL不能表示的值时使用“零”值。 DATE用来表示“年月日”、DATETIME用来表示“年月日时分秒”、TIME用来表示“时分秒”。 TIMESTAMP值显示格式为“YYYY-MM-DD HH:MM:SS”，用于需要经常插入或更新日期为当前系统时间的列。比如注册时间。 YEAR只表示年份。 DATETIME与TIMESTAMP的区别 DATETIME范围大、TIMESTAMP范围小 表中第一个TIMESTAMP列自动设置为系统时间CURRENT_TIMESTAMP。其后则不管。 TIMESTAMP插入和查询都受当地时区的影响；DATETIME只反映
8、当地时区，其他时区人查询时数据会有误差。NOW()。,第13页,2.2.3 字符串类型,第14页,2.2.3 字符串类型,CHAR和VARCHAR比较 CHAR为定长、VARCHAR为变长。 保存CHAR值时，在右边填充空格以达到指定的长度。当检索到CHAR值时，尾部的空格被删除掉。在存储或检索过程中不进行大小写转换。 VARCHAR只保存需要的字符数，另加一个字节来记录长度。VARCHAR不进行填充。当值保存和检索时尾部的空格仍保留，符合标准SQL。,上表中最后一行的值只适用非严格模式时；如果MySQL运行在严格模式，超过列长度不的值不保存，并且会出现错误。,第15页,2.2.3 字符串类型
9、,BLOB和TEXT比较 BLOB 为二进制字符串(字节字符串),用来保存二进制数据，如图片。TEXT字符字符串，保存较大文本，如文章。 BLOB列没有字符集，并且排序和比较基于列值字节的数值值。TEXT列有一个字符集，并且根据字符集的 校对规则对值进行排序和比较。 在TEXT或BLOB列的存储或检索过程中，不存在大小写转换。 当保存或检索BLOB和TEXT列的值时不删除尾部空格。 对于BLOB和TEXT列的索引，必须指定索引前缀的长度。对于CHAR和VARCHAR，前缀长度是可选的。 BLOB和TEXT列不能有默认值。 在执行大量的删除操作时，使用BLOB和TEXT会留下很大的“空洞”，从而
10、影响插入数据的性能。可使用OPTIMIZE TABLE功能对表进行碎片整理。,第16页,2.2.3 字符串类型,ENUM枚举类型说明 ENUM是一个字符串对象，其值来自表创建时在列规定中显式枚举的一列值。枚举最多可以有65,535个元素。 创建表时，ENUM成员值的尾部空格将自动被删除。 检索时，保存在ENUM列的值使用列定义中所使用的大小写来显示。 确定一个ENUM列的所有可能的值：SHOW COLUMNS FROM tbl_name 定义了一组ENUM枚举类型值后，在严格模式下，向该列中插入不在该枚举中的值时，系统禁止插入。在ANSI模式下，向该列插入空值。,第17页,2.3 字符串类型,
11、ENUM枚举类型说明,第18页,2.2.3 字符串类型,SET类型说明 SET是一个字符串对象，可以有零或多个值，其值来自表创建时规定的允许的一列值。 SET可以向列中插入多个定义的值，而ENUM只能插入一个定义的值。 指定包括多个SET成员的SET列值时各成员之间用逗号(,)间隔开。这样SET成员值本身不能包含逗号。 创建表时，SET成员值的尾部空格将自动被删除。 对于包含多个SET元素的值，当插入值时元素所列的顺序并不重要。在值中一个给定的元素列了多少次也不重要。当以后检索该值时，值中的每个元素出现一次，根据表创建时指定的顺序列出元素。 在严格模式下，向该列中插入不在该集合中的值时，系统禁
12、止插入。在ANSI模式下，向该列采用截断方式插入。,第19页,2.2.3 字符串类型,SET类型说明,第20页,2.3 表的创建,2.3.1 MYSQL表的存储引擎 2.3.2 创建表 2.3.2.1 SQL基本语法 2.3.2.2 主键设置 2.3.2.3 空值约束设置 2.3.2.4 唯一性约束设置 2.3.2.5 自动增长字段设置 2.3.2.6 默认值设置 2.3.2.7 自定义约束设置 2.3.2.8 外键设置 2.3.3 查看表的结构,第21页,2.3.1 MYSQL表的存储引擎,MySQL插件式存储引擎是MySQL数据库服务器中的组件，负责为数据库执行实际的数据I/O操作，并能允
13、许和强制执行面向特殊应用需求的特定特性集合。使用特殊存储引擎的主要优点在于，仅需提供特殊应用所需的特性，数据库中的系统开销较小，最终结果具有更有效和更高的数据库性能。这也是MySQL被始终视为具有高性能的原因之一，在行业标准基准方面，它能匹敌或击败专有的整体式数据库。,第22页,2.3.1 MYSQL表的存储引擎,各种存储引擎的特性,下面重点介绍最常用的4种存储引擎：MyISAM、InnoDB、Memory、Merge。,第23页,2.3.1 MYSQL表的存储引擎,MyISAM：MySQL默认存储引擎。MyISAM不支持事务、不支持外键，特点是访问速度快。 MyISAM是在Web、数据仓储和
14、其他应用环境下最常使用的存储引擎之一。 每个MyISAM表在磁盘上存储为3个文件，文件名与表名相同，扩展名分别是： .frm（存储表定义） MYD（MYData,存储数据） MYI(MYIndex，存储索引)。 数据文件和索引文件可以房子不同路径，平衡IO，获得更快访问速度。 MyISAM类型表可能会损坏。应注意检查： CHECK TABLE语句检查健康 REPAIR TABLE语句修复损坏,第24页,2.3.1 MYSQL表的存储引擎,InnoDB：用于事务处理应用程序，具有众多特性，包括ACID(Atomicity原子性、Consistency一致性、Isolation隔离性、Durabi
15、lity持续性)事务支持。相对MyISAM引擎，写处理效率低一些，且占用较多磁盘空间用于存储数据和索引。 InnoDB是MYSQL中唯一支持外键的引擎。 InnoDB存储表和索引有两种方式： 共享表存储：表的结构存放在.frm文件中，数据和索引保存在innodb_data_home_dir和innodb_data_file_path定义的表空间中。 多表空间存储：表的结构存放在.frm文件中，数据和索引单独保存在.idb中。如果是分区表，则每个分区对应单独的.ibd，文件名是“表名+分区名”。,第25页,2.3.1 MYSQL表的存储引擎,Memory：所有数据保存在RAM（内存）中，一旦服务
16、器关闭，表中的数据就会丢失。在需要快速查找引用和其他类似数据的环境下，可提供极快的访问。 Merge：允许MySQL DBA或开发人员将一系列等同的MyISAM表以逻辑方式组合在一起，并作为一个对象引用它们。优点是可以突破单个MyISAM表大小的限制，并将不同的表分布在多个磁盘上，从而有效改善访问效率。对于诸如数据仓储等VLDB环境十分适合。,第26页,2.3.1 MYSQL表的存储引擎,查看当前默认存储引擎 Show variables like table_type; 查看当前数据库支持的存储引擎 Show engines; Show variables like have%; 查看指定表的存储引擎 Show create table 表名;,第27页,2.3 创建表,2.3.2.1 SQL基本语法 2.3.2.2 主键设置 2.3.2.3 空值约束设置 2.3.2.4 唯一性约束设置 2.3.2.5 自动增长字段设置 2.3.2.6 默认值设置 2.3.2.7 自定义约束设置 2.3.2.8 外键设置 2.3.2.9 查看表的结构,第28页,2.3.2.1 SQL基本语法,
