项目3---MySQL数据表的结构.pptx

单击此处编辑母版标题样式,编辑母版文本样式,第二级,第三级,第四级,第五级,2020/4/10,添加页脚,#,任务,1,认识,MySQL,数据类型,项目,3 MySQL,数据表的结构,任务,2,学会,创建数据表,任务,3,如何,修改表结构,任务,4,了解,和认识索引,数据库软件的主要作用是用来存储、管理数据，作为数据库最重要的组成部分，数据表的设计与实现将直接影响数据库的运行效率,。,认识,理解,MySQL,数据表的结构不仅是了解掌握数据库查询、更新操作的基础，更有助于建立合理规范的数据表，方便日后的修改和索引使用。,。,项目,3 MySQL,数据表的,结构,情境,描述,例,3-1,运行结果中的第二列是,Type,，也就是第一列属性名称的数据类型，它表明了第一列中的属性采取何种形式在数据库中存储。,在,MySQL,表的设计中，确定表中每列的数据类型是设计表的重要步骤。列的数据类型就是定义该列所能存放的数据的值的类型。在,books,表中为了存放书名定义,bookname,字段的数据类型为字符型,；存放,版本号的,version,字段，则定义其数据类型为日期时间型。,任务,1,认识,MySQL,数据类型,MySQL,数据库中的常量、变量和参数都有数据类型，它用来指定一定的存储格式、约束和有效范围,。,MySQL,数据库也提供了多种数据类型，主要包括数值型（涵盖整数型、小数型、位型）、字符串类型、日期和时间类型，不同的,MySQL,版本支持的数据类型可能会稍有,不同。,MySQL,支持的数据类型,子任务,1.1,MySQL,支持的整数类型,子任务,1.1,认识,MySQL,数据表的整数类型,对于整数类型，,MySQL,数据库支持类型名称后面的小括号内指定显示宽度，如例,3-2,中的,int(5),表示当数值宽度小于,5,位的时候在数字前面填满,宽度,如果,不显示指定宽度则默认为,int(11),，如例,3-2,中数据类型设置为,int,的,id1,，在数据表结构的,Type,栏中显示为,int(11),向数据表中添加新的内容,子任务,1.1,认识,MySQL,数据表的整数类型,从例,3-4,中我们可以看到，,2147483647,是有符号,int,类型的最大值。因此向,int,类型的,id1,和,int(5),类型的,id2,插入,2147483647,，都能够在数据表中成功,插入,但是，由于,MySQL,数据表的,int,整数类型默认为,signed,（有符号），如果插入数据大于,2147483647,，那么不管是,id1,还是,id2,都会出错。,子任务,1.1,认识,MySQL,数据表的整数类型,子任务,1.1,认识,MySQL,数据表的整数,类型,无符号整型,创建包含无符号整型字段的表,例,3-7,中显示，由于有符号,int,类型的最小值为,-2147483648,，所以插入成功，但是无符号,int,的最小值是,0,，所以无法向无符号,int,类型的,id2,中插入负数。,子任务,1.1,认识,MySQL,数据表的整数类型,无,符号整型,在例,3-2,至例,3-7,中，注意使用,desc,查看数据表结构时，会出现,int(m),的形式。在,MySQL,数据表的数据类型中，,int(m),用于表示数值的显示宽度,，,例如,对于,id2,的,int(5),整数类型，当数值宽度小于,5,位的时候在数字前面填满宽度，一般配合,zerofill,使用,。,zerofill,就是以,0,填充字段中数字位数的意思，在数字位数不够的空间用字符,0,填满,；超出,了数据宽度，在不超出数据范围的前提下，正常显示。,子任务,1.1,认识,MySQL,数据表的整数,类型,字符填充,从例,3-9,中可知，,zerofill,的填充在对齐上与普通显示有所区别；请注意虽然,t1,表中的有符号,int,是,int(11),，但其实在,MySQL-5.7,中，,int,的,zerofill,宽度实际还是为,10,。,另外，,id2,的类型是,int(5),，但在例,3-9,结果中,id2,字段的显示宽度与,id1,的,int,显示宽度相同，这是因为尽管,id2,的类型是,int(5),，但我们在例,3-9,中修改,zerofill,参数时，所用的代码,是,alter table t1 modify id2 int zerofill,;,正是这句代码中的,int zerofill,，使,id2,字段被修改为了带,zerofill,参数的有符号,int,子任务,1.1,认识,MySQL,数据表的整数类型,字符,填充,如果,想要,id2,字段数据,呈现,int(5),的,zerofill,效果，必须修改代码,alter table t1 modify id2 int(5)zerofill,。,子任务,1.1,认识,MySQL,数据表的整数类型,字符,填充,MySQL,支持的小数类型包括浮点数、定点数等类型，不同小数类型的名称和占用字节、值的范围具体如表,3-2,所示,子任务,1.2,认识,MySQL,数据表,的,浮点数类型,【例,3-11,】创建数据表,t2,，表中含,id1,、,id2,、,id3,共,3,个字段，字段类型分别为,float,，,double,和,decimal,，参数表示一共显示,5,位，小数部分占,2,位。,子任务,1.2,认识,MySQL,数据表,的,浮点,数,类型,子任务,1.2,认识,MySQL,数据表,的,浮点,数,类型,例,3-13,中，一条记录的,3,个字段数值均能成功插入数据库，没有,warning,提示，但是,id1,和,id2,由于标度的限制（创建表时设置数据类型的小数部分占,2,位），舍去了最后一位，都显示成了,1.23,。,子任务,1.2,认识,MySQL,数据表,的,浮点,数,类型,例,3-14,中，进行插入记录操作后会发现最后一个小数位,4,都被截断了，和之前只向,id1,和,id2,插入,1.234,相比，出现了一个,警告（,warning,）,。,子任务,1.2,认识,MySQL,数据表,的,浮点,数,类型,在例,3-15,中，向数据表,t2,添加的三个字段都为,1.235,的记录时，同样也出现了一个警告（,warning,）。虽然每个字段的值仍然被截断，但是由于四舍五入原则，表中显示的数据是,1.24,。,子任务,1.2,认识,MySQL,数据表,的,浮点,数,类型,【例,3-16,】创建数据表,t2_2,，三个字段,id1,、,id2,、,id3,小数类型和,t2,相同，但是,不设标度,。,此时,向,3,个字段均插入,1.234,。可以发现，此时数据表,t2_2,中,的,id1,和,id2,没有像表,t2,中被,截断,，但是，,id3,仅能显示整数部分。,子任务,1.2,认识,MySQL,数据表,的,浮点,数,类型,进一步理解同为小数类型的,float,和,double,的区别，我们向表,t2_2,中,插入超长小数，此时还是只有,id3,字段,的,decimal,类型的截断,warning,，但尽管,id1,字段,没有,截断,warning,，查询时却出现了四舍五入的截断,子任务,1.2,认识,MySQL,数据表的,浮点数类型,【例,3-18,】在例,3-17,的基础上，向,t2_2,表中插入超长小数，观察是否存在数据截断,。,注意警告信息,子任务,1.2,认识,MySQL,数据表的,浮点数类型,1.,在,decimal,类型的字段中，如果不指定,decimal,精度,时，默认的整数位为,10,，默认的小数位为,0,。,此时,在这个字段中插入任何小数，都将进行截断操作，只保留整数部分，而且对比例,3-17,、,3-18,插入的数据可知在截断时也是遵循四舍五入的规则进行,；,2.float,类型和,double,类型在不指定精度时，默认会按照实际的精度（由实际的硬件和操作系统决定）来显示。,子任务,1.2,认识,MySQL,数据表的,浮点数类型,MySQL,字符串类型的名称、大小和用途,如,下,表所,示,子任务,1.3,认识,MySQL,数据表,的,字符串,类型,二者的主要区别在于存储方式的不同,：,CHAR,类型,的,长度固定为创建表时声明的长度，长度可以为从,0,255,的任何值；而,VARCHAR,类型,中,的值为可变长字符串，长度可以指定为,0,65535,之间的任何值,。,在,检索的时候，,CHAR,类型,删除,了尾部的空格，而,VARCHAR,类型,保留,这些空格。在存储或检索过程中，,CHAR,和,VARCHAR,均不进行大小写转换。,子任务,1.3,认识,MySQL,数据表的字符串,类型,CHAR,和,VARCHAR,类型,【例,3-19,】创建数据表,t3,，数据表的两个字段,v,和,c,分别是,varchar,和,char,类型。像两个字段同时插入,ab (,注意,ab,后面有两个空格,),。,从根据对表,t3,的查询结果计算长度可知，,char,型数据的,c,字段数据会去掉数据后端的空格。,子任务,1.3,认识,MySQL,数据表的字符串,类型,例,3-20,中，,concat(),是一个合并字符串的函数，会分别将,v,字段、,c,字段中的值和字符,+,进行合并,，,通过,对比合并前后的同一记录的两个字段值的变化，可以发现,char,类型字段值后端的空格确实是去掉了（因为在对该字段值进行合并字符串操作时，新添加的字符串与原有值之间是不存在空格的）,子任务,1.3,认识,MySQL,数据表的字符串,类型,CHAR,类型的字段在进行数据存储时，会对字段内容的最后结尾部分进行判断，如果结尾部分全部为空格，则将这些空格全部截去，只保留非空格字符部分,；,如果,结尾部分包含其他非空格字符，则截去空格的时候，只截取最后一个非空格字符之后的空格，不截取其之前的空格。,子任务,1.3,认识,MySQL,数据表的字符串,类型,MySQL,数据库系统中有多种数据类型，可以用于日期和时间的,表示,MySQL,日期类型中使用最频繁的是,DATE,、,TIME,和,DATETIME,三种类型,子任务,1.4,认识,MySQL,数据库中的日期,类型,【例,3-22,】创建数据表,t4,，,3,个字段,d,、,t,、,dt,分别是,date,、,time,、,datetime,类型。并查看,t4,的数据表结构。,子任务,1.4,认识,MySQL,数据库中的日期类型,在,MySQL,数据库中有一个函数,now(),；该函数的功能就是获取当前系统的准确,时间,【例,3-23,】在例,3-22,的基础上，使用,now(),函数向,t4,表中,3,个不同类型的字段同时插入当前时间,子任务,1.4,认识,MySQL,数据库中的日期类型,MySQL,数据库中支持两种复合类型：,ENUM,枚举类型,和,SET,集合类型,。,ENUM,类型,中文名称叫,枚举类型,，它的值范围需要在创建表时通过枚举方式显式指定，对取值范围在,1,255,之间的需要,1,个字节存储；对于取值范围在,255,65535,之间的，需要,2,个字节存储。,ENUM,类型最多允许有,65535,个成员。,ENUM,类型只允许从已经给出的值集合中选取某单个值，而不能一次取多个,值,。,ENUM,类型因为只允许在集合中取得一个值,，类似于,单,选,题。,子任务,1.5,认识,MySQL,数据库中的复合类型,SET,类型中文名称叫集合类型，,SET,类型与,ENUM,类型既相似又有区别，相似之处是都有一个预先指定好取值的元素集合,，区别,之处在于,SET,类型一次可以选取多个成员，而,ENUM,则只能选一个,。,SET,类型可以从预定义的集合中取得任意数量的值。另外，,SET,类型与,ENUM,类型均不能在,SET,类型字段中插入预定义值之外的其他数据。,子任务,1.5,认识,MySQL,数据库中的复合类型,【例,3-24,】创建数据表,t5,，字段,gender,设置为,enum,类型，字段,interest,设置为,set,类型。,子任务,1.5,认识,MySQL,数据库中的复合类型,【例,3-25,】向表,t5,中插入具体数据,。可以,看出,enum,类型的,gender,只能在值集合中选择一个，而,set,类型的,interest,则可以在值集合中选择多个。,子任务,1.5,认识,MySQL,数据库中的复合类型,例,3-26,中，,interest,字段的值,体育,不在初始建表时给该字段预定义的集合中（预定义的集合中只有,阅读,、,游戏,两种值），因此在进行记录添加的操作时，出现了错误。,子任务,1.5,认识,MySQL,数据库中的复合类型,MySQL,中的二进制数据类型有,BIT,、,BINARY,、,VARBINARY,、,TINYBLOB,、,BLOB,、,MEDIUMBLOB,和,LONGBLOB,。这些数据类型的特点如表,3-5,所示,子任务,1.6,认识,MySQL,数据库中的二进制类型,BINARY,和,VARBINARY,类似于,CHAR,和,VARCHAR,，不同的是它们包含二进制字符串而不包含非二进制字符串。,BINARY,类型的长度是固定的，如果数据的长度不足最大长度，在后面用”,0,”,补齐,比如，数据类型为,BINARY(3),，当插入,a,时，实际存储的是”,a00,”,子任务,1.6,认识,MySQL,数据库中的二进制类型,在创建数据表时，数据类型的选择对表的设计非常重要,。根据,相应的特点来选择合适的数据类型,，在,满足应用的基础上，用较小的存储代价换来较高的数据库,性能,以,整数类型的具体选择为例,：,涉及,到学号、编号、课程号等信息时，如果不确定是哪一种整数类型，可根据插入数据的大小来选择整数类型,：,如果,数字为,0,255,，即可选择,tinyint,；,如果,数字为,0,4294967296,，则可选择,INT,。,子任务,1.7,数据类型的选择,CREATE TABLE,为创建表语句，它为表定义各列的名字、数据类型和完整性约束。其语法格式,如下,CREATE TEMPORARY TABLE IF NOT EXISTS,(,完整性约束,.),表的选项,；,说明：,在定义表结构的同时，还可以定义与该表相关的完整性约束条件（实体完整性、参照完整性和用户自定义完整性），这些完整性约束条件被存入系统的数据字典中，当用户操作表中的数据时，由,MySQL,数据库自动检查该操作是否违背这些完整性约束条件。,任务,2,学会创建数据表,数据库要求数据完整性。数据完整性是指数据的准确性和一致性，它是防止数据库中存在不符合语义规定的数据和防止因错误信息的输入输出造成无效操作而提出,的,数据库表中的约束包括主键约束、外键约束、唯一约束、检查约束、空值约束和默认值约束等。这里我们将重点通过实例介绍主键约束和外键约束。,子任务,2.1,设置数据库表中的约束,【例,3-30,】创建数据表,pk,，共包含,id,、,name,、,grade,三个字段；然后使用,desc,命令查看,pk,表结构,。,在例,3-30,的结果中，我们可以发现主键（,Key,）一列是空白，且允许空值（,Null,）这一列都是,YES,，这说明每个字段默认可以为空，而且,MySQL,并不会自动添加主键,子,任务,2.1,学会创建数据表,【例,3-31,】在例,3-30,基础上将数据表,pk,中的,id,字段设为主键，再查看表结构变化。,子,任务,2.1,学会创建数据表,例,3-31,的结果中，可以看到,id,已经被设置为非空主键，也就是说，对于,pk,表中所有的记录，这个字段都不允许为空值。,说明：,主键约束是指在表中定义一个主键来唯一确定表中每一行数据的,标识符,。,主键保证记录的唯一性，它唯一地标识了每一条数据。在将某个字段指定为主键后，该字段自动为非空即不允许存在空值；并且每张数据表只能存在一个主键，该字段不能为空值，还不允许表中任何两条记录在该字段上有相同的值。一个数据表最多可以创建一个主键。,子,任务,2.1,学会创建,数据表,主键约束,例,3-31,中创建的是单字段主键，在本例中，我们将删除,pk,数据表中原有的单字段主键。,子,任务,2.1,学会创建数据表,删除主,键约束,【例,3-33,】添加联合主键也就是多字段主键，此时数据表仍然只有一个主键，只是这个主键由两个字段组合而成,。,在例,3-33,中这种,组合形式生成的主键比仅有单一字段做主键更加灵活,。,以,id,、,name,两个字段组合形成复合主键，则,id,字段就允许出现两个或多个记录在该字段上的值重复（但不允许任何两条记录在,id,、,name,两个字段上都重复）,子,任务,2.1,学会创建数据表,创建多字段主,键约束,如果表,A,的主键是表,B,中的字段，则该字段称为表,B,的外键；另外表,A,称为主表，表,B,称为从表,。,外,键是用来实现参照完整性的，不同的外键约束方式将可以使两张表紧密的结合起来，特别是修改或删除的级联操作将使得日常维护更轻松,。,外,键主要用来保证数据的完整性和一致性。一个数据表中可以有多个外键,子,任务,2.1,学会创建,数据表,外,键及外键约束,在例,3-35,中,定义了表,Orders,的外键约束,P_id,外键约束在,Key,一列显示为,MUL,这,也是,Person,表中的主,键,如果向,Orders,表中插入某条记录，此记录的,P_id,字段值为,N,，而在,Person,表中所有记录的,P_id,字段都不为,N,，则说明此条记录的值为非法（因为不满足外键约束的条件）而无法插入该条记录,子,任务,2.1,学会创建,数据表,外,键及外键约束,在数据库库系统开发过程中，我们通常希望在每次插入新记录时，自动地创建主键字段的值。,MySQL,有一个定义列为自增的属性：,AUTO_INCREMENT,，也就是,MySQL,自增型字段。,AUTO_INCREMENT,会在新记录插入表中时生成一个唯一的数字默认从,1,开始自增，每次增加,1,，,MySQL,用户也可以修改自增起始段和每次自增的数量,子任务,2.2,设置自增型,字段,通过例,3-39,，可以看到设置为,auto_increment,的,ID,无需插入数据，而是随着插入,记录,的过程进行自增，默认从,1,开始。,子任务,2.2,设置自增型字段,如果希望,ID,字段的值从,100,开始自增，则代码如下,：,alter,table Person auto_increment=100,;,修改,自增起始段之后，对已有记录并没有影响。,子任务,2.2,设置自增型字段,当数据表中的表创建完成后，用户在使用过程中可能根据需要改变表中原先定义的许多选项，如对表的结构、约束或字段的属性进行修改,。,表,的修改与表的创建一样，可以通过,SQL,语句来实现，用户可进行的修改操作包括更改表名、增加字段、删除字段、修改已有字段的,属性,。,任务,3,如何修改,表结构,通过执行,alter table,语句，成功地向,Person,表中添加了新的字段,age,，并且将,age,字段设定为无符号的整型,数,添加,字段后，无论数据表,Person,中原先是否已有数据，新,增加,的列一律为空值，且新增加的字段位于表结构的末尾,子任务,3.1,修改字段相关,信息,添加字段,使用,alter,语句进行字段的删除,子任务,3.1,修改字段相关信息,删除字段,【例,3-43,】在例,3-42,的基础上，将数据表,Person,中原有的主键从自增型修改为普通型主键字段。,子任务,3.2,修改,约束条件,对,MySQL,数据表的表名进行修改，语法如下：,ALTER TABLE tablename RENAME as new_tablename,【例,3.22,】将,数据表,student2,更名,为,student,。,子任务,3.3,修改表名及其他选项,对数据库最频繁的操作是进行数据查询。一般情况下，数据库在进行查询操作时需要对整个表进行数据搜索。当表中的数据很多时，搜索数据就需要很长的时间，这就造成了服务器的资源浪费。为了提高检索数据的能力，数据库引入了索引机制,。,索引是一个列表，这个列表中包含了某个表中一列或若干列的集合以及这些值的记录在数据表中存储位置的物理地址。,表的存储由两部分组成，一部分是表的数据页面，另一部分是索引页面。索引就放在索引页面上。通常，索引页面相对于数据页面小得多。,任务,4,了解,和认识,索引,索引的定义,当进行数据检索时，系统先搜索索引页面，从中找到所需数据的指针，再直接通过指针从数据页面中读取数据,。,可以把数据库看作一本书，,把索引看作书的目录,，通过目录查找书中的信息，显然比查找没有目录的书要,方便、,快捷,不使用索引的情况下，,MySQL,检索记录时必须从第,1,条记录开始然后读完整个表直到找出相关的行。表越大，花费的时间越多。如果表中查询的字段有一个索引，,MySQL,能,快速到达一个位置去搜寻到,数据,，,没有必要看所有数据。,任务,4,了解,和认识索引,索引的作用,任务,4,索引,与主键、外键的对比,1,、对于查询频率高的字段创建索引,；,2,、对排序、分组、联合查询频率高的字段创建索引；,3,、索引的数目不宜太,多；,4,、若在实际工作中，需要将多个列设置索引时，可以采用多列索引,子任务,4.2,如何,选取索引,关键字,5,、选择唯一性,索引,；,6,、尽量使用数据量少的索引,；,7,、尽量使用前缀来,索引,；,8,、删除不再使用或者很少使用的,索引,。,子任务,4.2,如何选取索引关键字,索引在创建表的时候可以同时创建，也可以随时增加新的索引。创建新索引的语法为：,CREATE,UNIQUE|FULLTEXT|SPATIAL,INDEX index_name,USING index_type,ON,tbl_name(index_col_name,.),index_col_name,:,col_name,(length)ASC|DESC,子任务,4.3,如何为数据表创建,索引,1.,虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行,INSERT,、,UPDATE,和,DELETE,。因为更新表时，,MySQL,不仅要保存数据，还要保存一下索引文件,。,2.,建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重，但如果在一个,超大型,的数据表上创建了多种组合索引，索引文件会膨胀很快，占据大量磁盘空间。,DROP INDEX index_name ON tbl_name,其中,index_name,代表了索引名称，,tbl_name,代表了数据表名称。,子任务,4.4,如何删除,索引,学,完本章后，读者可以对每种数据类型的用途、物理存储、表示范围等有一个概要的了解。这样在面对具体应用时，就可以根据相应的特点来选择合适的数据类型，能够在满足应用需求的基础上，用较小的存储代价换来较高的数据库性能。,随后,介绍了数据表的创建，包括约束、自增型字段和其它选项的设置，并在数据表的修改上介绍了字段、约束条件和表名及其它选项的修改。最后介绍了数据表结构中索引的概念与使用。创建索引可以极大地提高系统的性能，典型的应用是加快查询的,速度,。,项目小结,