第5章--数据表基本操作.pptx

《第5章--数据表基本操作.pptx》由会员分享，可在线阅读，更多相关《第5章--数据表基本操作.pptx（46页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、5数据表基本操作第章5.1 创建数据表35.1 创建数据表创建数据表，实际上是规定列属性和实现数据完整性约束的过程，基本语法形式如下：数据表命名应遵循以下原则：u长度最好不超过30个字符；u多个单词之间使用下划线“_”分隔，不允许有空格；u不允许为MySQL关键字；u不允许与同一数据库中的其他数据表同名。CREATETABLEtable_name(col_name1data_typeConstraints,col_name2data_typeConstraints,col_namendata_typeConstraints);5.1.1 创建表的语法形式45.1 创建数据表【实例5-1】根据表

2、5-1的数据信息创建数据表goods。表5-1goods表结构字 段数据类型约 束注 释idINT(11)主键、自增商品编号typeVARCHAR(30)非空商品类别nameVARCHAR(30)唯一商品名称priceDECIMAL(7,2)无符号商品价格numINT(11)默认值为0商品库存add_timeDATETIME添加时间5.1.1 创建表的语法形式55.1 创建数据表5.1.2 使用SQL语句设置约束条件主键，也称主码，用于标识表中唯一的一条记录。一张表中只能有一个主键，并且主键值不能为空。col_namedata_typePRIMARYKEY设置主键约束1主键约束是最常用的一种约

3、束，设置主键约束的关键字为PRIMARYKEY，使用SQL语句可以在定义字段时设置主键约束，也可以在定义好表中所有字段后再设置主键约束。1）定义字段时设置主键约束语法形式为：实例5-1创建的数据表goods中的id字段便设置了主键约束，SQL语句如下：CREATETABLEgoods(idINT(11)PRIMARYKEY,);65.1 创建数据表5.1.2 使用SQL语句设置约束条件PRIMARYKEY(col_name)设置主键约束12）定义所有字段后设置主键约束语法形式为：为goods表id字段设置主键约束也可以使用这种方法，SQL语句如下：CREATETABLEgoods(idINT(

4、11),PRIMARYKEY(id);75.1 创建数据表5.1.2 使用SQL语句设置约束条件col_namedata_typeAUTO_INCREMENT设置自增约束2实例5-1创建的数据表goods中的id字段便设置了自增约束，SQL语句如下：CREATETABLEgoods(idINT(11)PRIMARYKEYAUTO_INCREMENT,);在向数据表中插入数据时，如果用户希望每条记录的“编号”自动生成，并且按顺序排列，可以为该字段设置自增约束。设置自增约束的关键字为AUTO_INCREMENT，语法形式如下：u一张表中只能设置一个字段为自增约束，并且该字段必须为主键。u默认的初始

5、值为1，每增加一条记录，字段值自动增加1。u字段类型必须为整数型。85.1 创建数据表5.1.2 使用SQL语句设置约束条件col_namedata_typeNOTNULL设置非空约束3实例5-1创建的数据表goods中的type字段便设置了非空约束，SQL语句如下：CREATETABLEgoods(typeVARCHAR(30)NOTNULL,);设置非空约束的关键字为NOTNULL，作用是规定字段的值不能为空，用户在向数据表中插入数据时，如果设置非空约束的字段没有指定值，系统就会报错。语法形式如下：95.1 创建数据表5.1.2 使用SQL语句设置约束条件col_namedata_type

6、UNIQUE设置唯一性约束4实例5-1创建的数据表goods中的name字段便设置了唯一性约束，SQL语句如下：CREATETABLEgoods(nameVARCHAR(30)UNIQUE,);设置唯一性约束的关键字为UNIQUE。语法形式为：1）定义字段时设置唯一性约束UNIQUEKEY(col_name)语法形式为：2）定义所有字段后设置唯一性约束105.1 创建数据表5.1.2 使用SQL语句设置约束条件col_namedata_typeUNSIGNED;设置无符号约束5实例5-1创建的数据表goods中的price字段便设置了无符号约束，SQL语句如下：CREATETABLEgoods

7、(priceDECIMAL(7,2)UNSIGNED,);为字段设置无符号约束的关键字为UNSIGNED，其作用是规定此列所存储的数据不为负数。语法形式如下：115.1 创建数据表5.1.2 使用SQL语句设置约束条件col_namedata_typeDEFAULTvalue设置默认约束6实例5-1创建的数据表goods中的num字段便设置了默认约束，SQL语句如下：CREATETABLEgoods(numINT(11)DEFAULT0,);设置默认约束的关键字为DEFAULT，语法形式如下：125.1 创建数据表5.1.2 使用SQL语句设置约束条件CONSTRAINTkey_nameFOR

8、EIGNKEY(child_col_name)REFERENCESparent_table_name(parent_col_name)设置外键约束7CONSTRAINT，FOREIGNKEY和REFERENCES为设置外键约束的关键字，key_name表示外键名，child_col_name表示从表中需要设置外键约束的字段名，parent_table_name表示主表名，parent_col_name表示主表中主键的字段名。设置外键约束的主要作用是保证数据的完整性。外键可以不是所属数据表的主键，但会对应着另外一张数据表的主键。例如，商品和订单之间具有一定关系，订单数据表会有一个字段存储商品的编

9、号，而这个字段的值对应着商品数据表中的商品编号，订单数据表可以称为从表，商品数据表可以称为主表，订单数据表中的商品编号字段就可以称为外键。设置外键约束的语法形式如下：135.1 创建数据表5.1.2 使用SQL语句设置约束条件CREATETABLEorders(o_idINT(11)PRIMARYKEY,add_timeDATETIME,goods_idINT(11),CONSTRAINTgoo_ordFOREIGNKEY(goods_id)REFERENCESgoods(id);设置外键约束7选择数据库db_shop后，执行以下SQL语句，创建数据表orders。【实例5-2】根据表5-2创

10、建从表orders，为goods_id字段设置外键约束。表5-2orders表结构字 段数据类型约 束注 释o_idINT(11)主 键订单编号add_timeDATETIME添加时间goods_idINT(11)外 键商品编号u主表和从表必须使用InnoDB存储引擎。u设置外键约束的字段和关联的主键必须具有相同的数据类型。设置外键约束时应注意以下几点：145.1 创建数据表5.1.2 使用SQL语句设置约束条件CREATETABLEcategory(idINT(11)PRIMARYKEY,nameVARCHAR(30),p_idINT(11)ENGINE=MyISAM;设置表的存储引擎8选择

11、数据库db_shop后，执行以下SQL语句，创建数据表category。【实例5-3】根据表5-3创建category表，并设置其存储引擎为MyISAM，用于存储商品类别。表5-3category表结构字 段数据类型约 束注 释idINT(11)主键类别编号nameVARCHAR(30)类别名称p_idINT(11)父类编号155.1 创建数据表5.1.3 使用图形化工具创建表并设置约束条件实际工作中，使用图形化工具可以更简单快捷地创建数据表。本节将通过创建商品评价表comment，并为其设置约束条件，介绍使用图形化工具创建表和设置约束条件的方法。comment表结构如表5-4所示。表5-4c

12、omment表结构字 段数据类型约 束注 释idINT(11)主键、自增评价编号goods_idINT(11)非空、无符号评价商品user_idINT(11)非空、无符号评价用户contentTEXT默认评价内容add_timeDATETIME添加时间表5-5reply表结构字 段数据类型约 束注 释idINT(11)主键、自增回复编号comment_idINT(11)非空、无符号评价编号user_idINT(11)非空、无符号评价用户r_contentTEXT回复内容add_timeDATETIME添加时间5.2 查看表结构175.2 查看表结构5.2.1 使用SQL语句查看表结构DESCR

13、IBEtable_name;查看表基本结构1查看表结构的关键字为DESCRIBE，语法形式如下：数据表创建完成后，可以通过查看表结构或者建表语句，来确认表的定义是否正确。mysqlDESCRIBEgoods;+-+-+-+-+-+-+|Field|Type|Null|Key|Default|Extra|+-+-+-+-+-+-+|id|int(11)|NO|PRI|NULL|auto_increment|type|varchar(30)|NO|NULL|name|varchar(30)|YES|UNI|NULL|price|decimal(7,2)unsigned|YES|NULL|num|i

14、nt(11)|YES|0|add_time|datetime|YES|NULL|+-+-+-+-+-+-+6rowsinset(0.09sec)【实例5-4】执行DESCRIBE语句，查看goods表结构，效果如下所示。185.2 查看表结构5.2.1 使用SQL语句查看表结构SHOWCREATETABLEtable_nameG查看建表语句2使用SHOWCREATETABLE语句可以查看表的建表语句，语法形式如下：mysqlSHOWCREATETABLEgoodsG*1.row*Table:goodsCreateTable:CREATETABLEgoods(idint(11)NOTNULLAU

15、TO_INCREMENT,typevarchar(30)NOTNULL,namevarchar(30)DEFAULTNULL,pricedecimal(7,2)unsignedDEFAULTNULL,numint(11)DEFAULT0,add_timedatetimeDEFAULTNULL,PRIMARYKEY(id),UNIQUEKEYname(name)ENGINE=InnoDBDEFAULTCHARSET=utf81rowinset(0.02sec)【实例5-5】执行SHOWCREATETABLE语句，查看goods表的建表语句，效果如下所示。195.2 查看表结构5.2.2 使用图形

16、化工具查看表结构查看表基本结构1启动NavicatforMySQL并连接MySQL后，双击打开localhost_3306连接，然后双击选择db_shop数据库，系统会在右侧“对象”选项卡中打开数据表列表，选中要查看的goods表，单击“设计表”按钮，即可查看数据表结构，如图5-9所示。205.2 查看表结构5.2.2 使用图形化工具查看表结构查看建表语句2进入数据表列表页面后，右击要查看的goods表，在弹出的快捷菜单中选择“对象信息”，表下方会出现“常规”和“DDL”选项卡，单击“DDL”切换到该选项卡，即可查看建表语句，如图5-10所示。图5-10查看表详细结构5.3 修改表225.3

17、修改表5.3.1 使用SQL语句修改数据表ALTERTABLEold_table_nameRENAMEnew_table_name;修改表名1修改数据表名称的关键字为RENAME，语法形式如下：MySQL提供了ALTER关键字来修改表结构。【实例5-6】执行SQL语句，将goods表的名称改为tb_goods。mysqlALTERTABLEgoodsRENAMEtb_goods;QueryOK,0rowsaffected(0.18sec)235.3 修改表5.3.1 使用SQL语句修改数据表ALTERTABLEtable_nameMODIFYcol_namenew_data_type;修改字段

18、数据类型2修改字段数据类型的关键字为MODIFY，语法形式如下：【实例5-7】执行SQL语句，将tb_goods表中type字段的数据类型修改为CHAR(30)。mysqlALTERTABLEtb_goodsMODIFYtypeCHAR(30);QueryOK,0rowsaffected(0.58sec)Records:0Duplicates:0Warnings:0mysqlDESCtb_goods;+-+-+-+-+-+-+|Field|Type|Null|Key|Default|Extra|+-+-+-+-+-+-+|id|int(11)|NO|PRI|NULL|auto_incremen

19、t|type|char(30)|YES|NULL|name|varchar(30)|YES|UNI|NULL|price|decimal(7,2)unsigned|YES|NULL|num|int(11)|YES|0|add_time|datetime|YES|NULL|+-+-+-+-+-+-+6rowsinset(0.00sec)245.3 修改表5.3.1 使用SQL语句修改数据表ALTERTABLEtable_nameCHANGEold_col_namenew_col_namedata_type;修改字段名3修改数据表字段名称的关键字为CHANGE，语法形式如下：【实例5-8】执行SQ

20、L语句，将tb_goods表中name字段的名称改为g_name。mysqlALTERTABLEtb_goodsCHANGEnameg_nameVARCHAR(30);QueryOK,0rowsaffected(0.09sec)Records:0Duplicates:0Warnings:0mysqlDESCtb_goods;+-+-+-+-+-+-+|Field|Type|Null|Key|Default|Extra|+-+-+-+-+-+-+|id|int(11)|NO|PRI|NULL|auto_increment|type|char(30)|YES|NULL|g_name|varchar

21、(30)|YES|UNI|NULL|price|decimal(7,2)unsigned|YES|NULL|num|int(11)|YES|0|add_time|datetime|YES|NULL|+-+-+-+-+-+-+6rowsinset(0.00sec)255.3 修改表5.3.1 使用SQL语句修改数据表mysqlALTERTABLEtb_goodsCHANGEg_namenameCHAR(30);QueryOK,0rowsaffected(0.49sec)Records:0Duplicates:0Warnings:0修改字段名3使用上述语句也可以同时修改数据类型。例如，将g_nam

22、e字段名称修改为name，数据类型修改为CHAR(30)，效果如下。265.3 修改表5.3.1 使用SQL语句修改数据表ALTERTABLEtable_nameADDcol_namedata_type;添加字段4常见添加字段的操作一般分为三种情况：在表的最后一列，在表的第一列或者在指定列之后。1）在表的最后一列添加字段添加字段的关键字为ADD，语法形式如下：【实例5-9】执行SQL语句，在tb_goods表中添加picture字段。ALTERTABLEtb_goodsADDpictureVARCHAR(255);275.3 修改表5.3.1 使用SQL语句修改数据表mysqlDESCtb_g

23、oods;+-+-+-+-+-+-+|Field|Type|Null|Key|Default|Extra|+-+-+-+-+-+-+|id|int(11)|NO|PRI|NULL|auto_increment|type|char(30)|YES|NULL|name|char(30)|YES|UNI|NULL|price|decimal(7,2)unsigned|YES|NULL|num|int(11)|YES|0|add_time|datetime|YES|NULL|picture|varchar(255)|YES|NULL|+-+-+-+-+-+-+7rowsinset(0.00sec)添加

24、字段4执行SQL语句查看表结构，可以发现在表的最后一列添加了一个名为picture的字段，结果如下：285.3 修改表5.3.1 使用SQL语句修改数据表ALTERTABLEtable_nameADDcol_namedata_typeFIRST;添加字段42）在表的第一列添加字段【实例5-10】执行以下SQL语句，在tb_goods表中添加state字段。ALTERTABLEtb_goodsADDstateTINYINT(4)FIRST;295.3 修改表5.3.1 使用SQL语句修改数据表添加字段4mysqlDESCtb_goods;+-+-+-+-+-+-+|Field|Type|Null

25、|Key|Default|Extra|+-+-+-+-+-+-+|state|tinyint(4)|YES|NULL|id|int(11)|NO|PRI|NULL|auto_increment|type|char(30)|YES|NULL|name|char(30)|YES|UNI|NULL|price|decimal(7,2)unsigned|YES|NULL|num|int(11)|YES|0|add_time|datetime|YES|NULL|picture|varchar(255)|YES|NULL|+-+-+-+-+-+-+8rowsinset(0.00sec)执行SQL语句查看表

26、结构，可以发现在表的第一列添加了一个名为state的字段，结果如下：305.3 修改表5.3.1 使用SQL语句修改数据表ALTERTABLEtable_nameADDcol_name1data_typeAFTERcol_name2;添加字段43）在表的指定列之后添加字段【实例5-11】执行SQL语句，在tb_goods表中num字段之后添加intro字段。ALTERTABLEtb_goodsADDintroTEXTAFTERnum;315.3 修改表5.3.1 使用SQL语句修改数据表添加字段4mysqlDESCtb_goods;+-+-+-+-+-+-+|Field|Type|Null|K

27、ey|Default|Extra|+-+-+-+-+-+-+|state|tinyint(4)|YES|NULL|id|int(11)|NO|PRI|NULL|auto_increment|type|char(30)|YES|NULL|name|char(30)|YES|UNI|NULL|price|decimal(7,2)unsigned|YES|NULL|num|int(11)|YES|0|intro|text|YES|NULL|add_time|datetime|YES|NULL|picture|varchar(255)|YES|NULL|+-+-+-+-+-+-+9rowsinset(

28、0.00sec)执行SQL语句查看表结构，可以发现在表中num字段之后添加了intro字段，结果如下：325.3 修改表5.3.1 使用SQL语句修改数据表ALTERTABLEtable_nameDROPcol_name;删除字段5删除数据表字段的关键字为DROP，语法形式如下：【实例5-12】执行以下SQL语句，将tb_goods表中的picture字段删除。ALTERTABLEtb_goodsDROPpicture;mysqlDESCtb_goods;+-+-+-+-+-+-+|Field|Type|Null|Key|Default|Extra|+-+-+-+-+-+-+|state|ti

29、nyint(4)|YES|NULL|id|int(11)|NO|PRI|NULL|auto_increment|type|char(30)|YES|NULL|name|char(30)|YES|UNI|NULL|price|decimal(7,2)unsigned|YES|NULL|num|int(11)|YES|0|intro|text|YES|NULL|add_time|datetime|YES|NULL|+-+-+-+-+-+-+8rowsinset(0.01sec)335.3 修改表5.3.1 使用SQL语句修改数据表ALTERTABLEtable_nameMODIFYcol_name

30、data_typeFIRST|AFTERcol_name2;修改字段顺序6修改字段顺序的关键字为MODIFY，语法形式如下：【实例5-13】执行以下SQL语句，将tb_goods表的state字段位置修改为id字段之后。ALTERTABLEtb_goodsMODIFYstateTINYINT(4)AFTERid;mysqlDESCtb_goods;+-+-+-+-+-+-+|Field|Type|Null|Key|Default|Extra|+-+-+-+-+-+-+|id|int(11)|NO|PRI|NULL|auto_increment|state|tinyint(4)|YES|NULL

31、|type|char(30)|YES|NULL|name|char(30)|YES|UNI|NULL|price|decimal(7,2)unsigned|YES|NULL|num|int(11)|YES|0|intro|text|YES|NULL|add_time|datetime|YES|NULL|+-+-+-+-+-+-+8rowsinset(0.00sec)345.3 修改表5.3.1 使用SQL语句修改数据表ALTERTABLEtable_nameENGINE=e_name;修改存储引擎7用户可以在创建表时设置存储引擎，也可以在表创建完成之后修改表的存储引擎，语法形式如下：SHOWC

32、REATETABLEtable_nameG在修改存储引擎之前，往往需要首先查看表当前的存储引擎，语法形式如下：【实例5-14】修改category表的存储引擎为InnoDB。355.3 修改表5.3.1 使用SQL语句修改数据表mysqlSHOWCREATETABLEcategoryG*1.row*Table:categoryCreateTable:CREATETABLEcategory(idint(11)NOTNULL,namevarchar(30)DEFAULTNULL,p_idint(11)DEFAULTNULL,PRIMARYKEY(id)ENGINE=MyISAMDEFAULTCHA

33、RSET=utf81rowinset(0.00sec)首先执行SQL语句，查看category表的存储引擎，结果如下：修改存储引擎7mysqlALTERTABLEcategoryENGINE=InnoDB;QueryOK,0rowsaffected(0.31sec)Records:0Duplicates:0Warnings:0365.3 修改表5.3.2 使用图形化工具修改数据表u修改表名：在数据表列表中右击需要修改名称的表，此处为tb_goods表，在弹出的快捷菜单中选择“重命名”，此时表名称将处于可编辑状态，删除原名称，重新输入新名称“goods”并按回车键即可，如图5-11所示。图5-1

34、1修改表名375.3 修改表5.3.2 使用图形化工具修改数据表u修改数据类型：首先选中需要修改结构的表，此处为goods表，单击“设计表”按钮，进入表结构设计界面。在下方显示的字段列表中，单击需要修改数据类型的字段“类型”列的下拉菜单按钮，在弹出的下拉菜单中选择合适的数据类型，如图5-12所示。图5-12修改字段的数据类型385.3 修改表5.3.2 使用图形化工具修改数据表u修改字段名：在需要修改字段名的字段“名”列中单击并重新输入名称即可。u在指定位置添加字段：选中指定字段，然后单击“插入栏位”按钮，可在指定字段之前创建一个空白字段，填写字段信息即可，如图5-13所示。图5-13修改字段

35、顺序u删除字段：选中需要删除的字段，单击“删除栏位”按钮，然后单击提示框中的“删除”按钮即可删除字段。此处将state和intro字段删除。u修改字段的排列位置：选中需要修改的字段，单击“上移”或“下移”按钮，即可修改字段顺序。5.4 删除表405.4 删除表删除数据表会将表的定义和表中的数据全部删除，因此，用户最好反复确认后再执行此操作。5.4.1 使用SQL语句删除表使用DROP关键字可以一次性删除一个或多个没有被其他表关联的表，语法形式如下：删除没有被关联的表1DROPTABLEIFEXISTStable_name1,table_name2,table_namen;提示如果删除的数据表不

36、存在，系统会提示错误信息并中断执行，加上“IFEXISTS”参数后，系统会在执行删除命令之前判断表是否存在，如果表不存在，命令仍可以顺利执行，但系统会提示警告。415.4 删除表5.4.1 使用SQL语句删除表mysqlDROPTABLEIFEXISTScategory,tb_goods;QueryOK,0rowsaffected,1warning(0.11sec)【实例5-15】执行SQL语句，删除category表和不存在的tb_goods表，并查看数据库中的所有表。执行删除表语句，删除category表和tb_goods表，结果如下：mysqlSHOWTABLES;+-+|Tables_

37、in_db_shop|+-+|goods|orders|+-+2rowsinset(0.00sec)执行SQL语句，查看数据库中的表，结果如下：425.4 删除表5.4.1 使用SQL语句删除表如果数据表之间存在外键关联，那么直接删除主表，系统会提示错误信息，这种情况下，可以先删除与它关联的从表，再删除主表。但有时需要保留从表中的数据，此时需解除主表和从表之间的关联，即删除从表中的外键约束。删除外键的语法形式如下：删除被其他表关联的主表2ALTERTABLEtable_nameDROPFOREIGNKEYkey_name;之前创建的comment表和reply表存在外键关联，如果直接删除com

38、ment表，系统会提示错误，如下所示。mysqlDROPTABLEcomment;ERROR1217(23000):Cannotdeleteorupdateaparentrow:aforeignkeyconstraintfails435.4 删除表5.4.1 使用SQL语句删除表可以执行SQL语句先删除reply表中的外键，然后删除comment表，如下所示。mysqlALTERTABLEreplyDROPFOREIGNKEYrep_com;QueryOK,0rowsaffected(0.07sec)Records:0Duplicates:0Warnings:0mysqlDROPTABLEco

39、mment;QueryOK,0rowsaffected(0.05sec)445.4 删除表5.4.2 使用图形化工具删除数据表在数据表列表中选中需要删除的表，此处为category表，单击“删除表”按钮，然后单击提示框中的“删除”按钮，即可删除数据表，如图5-14所示。删除没有被关联的表1图5-14删除数据表455.4 删除表5.4.2 使用图形化工具删除数据表选中从表orders，单击“设计表”按钮，单击“外键”选项卡，选中外键，然后单击“删除外键”按钮，之后单击“保存”按钮，如图5-15所示。最后选中主表goods，单击“删除表”按钮并确认，即可删除主表goods。删除被其他表关联的主表2图5-15删除外键感谢观看