《数据库原理及应用》第2版 课件 第4章 数据库及表基本操作

《数据库原理及应用》数据库虚拟教研室制作数据库目录01

SQL概述02存储引擎03字符集与字符编码04MySQL数据库管理05MySQL数据类型06MySQL数据表管理07数据表的数据操作第4章数据库及表基本操作SQL概述SQL：StructuredQueryLanguage，结构化查询语言。其功能包括数据查询、定义、操纵和控制等方面。SQL有SQL89、SQL92和SQL99多个版本，并向下兼容。注意：SQL是关系数据库的标准语言，在语法上，不同的数据库产品略有不同，MySQL的语法与其他工具差别较大。SQL组成数据库存储引擎：是数据库底层软件组织，是对数据库文件的一种存取机制，为存储的数据建立索引以及如何实现更新、查询数据等技术的方法。存储引擎为什么要使用不同的存储引擎：现实生活中，由于不同业务产生了不同的数据，这些数据有的可能偏重于被频繁查询，有的可能偏重于修改速度，有的则对事务、索引、外键有特殊规定。这样一来，就对存储数据要求使用不同的数据组织结构，使用不同的存储引擎可以将数据以不同的结构进行存储。查看MySQL支持的存储引擎：

showengines存储引擎存储引擎的类型服务器是否支持该类型引擎注释事务分布式交易处理的XA规范事务保存点通过系统变量default-storage-engine来查看存储引擎，查看方式：showvariableslike‘default-storage-engine’；存储引擎MySQL存储引擎可在定义表的时候设置，也可以通过修改表的方式来设置。服务器在运行期间可修改数据库服务器默认存储引擎方式：setdefault-storage-engine=存储引擎名；数据库服务在启动时会读取配置文件my.ini，配置文件中有默认存储引擎的设置。(C:\ProgramData\MySQL\MySQL

Server

8.0\my.ini)[mysqld]default-storage-engine=innodb字符集（Characterset）：是字符的集合，是一套符号以及编码规则，以二进制形式存储。常用的字符集如下表：字符集与字符编码字符编码：将字符变成字节的过程。在符号集合与数字系统之间建立对应关系。与字符集相对应，不同的字符集有不同的字符编码。解码：将二进制字节转换成对应字符的过程。字符集与字符编码转码utf8gbk查看当前MSQL支持的字符集及其默认的排序方式：showcharset;/showcharacterset;使用系统变量查看当前MySQL使用的字符集:showvariableslike'character%';字符集与字符编码数据库服务在启动时会读取配置文件my.ini，配置文件中有默认字符集的设置。[client]default-character-set=utf8[mysql]default-character-set=utf8[mysqld]character-set-server=utf8字符序：是指在同一字符集内字符之间的比较规则。字符集与字符编码如何比较两个英文字符的大小？最简单的方式是比较两个字符二进制编码的大小，如ASCII码字符集中的’A’的编号为65，’a’的编码为97。如何比较两个汉字字符的大小？UTF8中文采用康熙字典中定义的偏旁索引顺序排序,GBK中文采用新华字典中定义的字母顺序排序。二都可以转码。例：将student表的sname字段由utf8字符集转为gbk排序。SELECT*FROMstudentORDERBYCONVERT(snameUSINGgbk);创建数据库创建数据库的语法格式CREATEDATABASE|SCHEMA[IFNOTEXISTS]db_name[[DEFAULT]CHARACTERSETcharset_name][[DEFAULT]COLLATEcollation_name];MySQL数据库管理创建数据库命令早期数据库称谓待创建数据库名已经存在时，给出错误信息待创建数据库名设置默认字符集设置字符集默认校对规则在创建数据库时省略了字符集和校对规则的设置，MySQL将采用默认字符集和默认校对规则，默认字符集是在哪里设置的？【例4-1】创建名称为teaching_manage的数据库，字符集和排序规则为采用系统默认。CREATEDATABASEteaching_manage;MySQL数据库管理说明：创建数据库后，会在存储数据文件夹data下生成一个与数据库同名的目录，将来创建的表数据保存在此文件夹中。【例4-2】创建名称为mytest的数据库，字符集为utf8mb4和排序规则为采用系统默认。CREATEDATABASEmytestcharsetutf8mb4;查看数据库查看所有的数据库SHOWDATABASES;MySQL数据库管理查看数据库查看某个数据库的创建语句SHOWCREATEDATABASEteaching_manage;使用数据库MySQL服务器中可以同时存在多个数据库，在做数据处理前，需要切换到指定数据库。语法如下：USE数据库名;MySQL数据库管理【例】将teaching_manage切换为当前数据库useteaching_manage;修改数据库更改数据库的字符集和排序规则ALTERDATABASEdb_name[[DEFAULT]CHARACTERSETcharset_name][[DEFAULT]COLLATEcollation_name];MySQL数据库管理【例4-3】修改已建数据库mytest，将其字符集改为utf8。ALTERDATABASEmytestCHARACTERSETutf8;删除数据库系统收回已创建数据库所占用的存储空间，清除其在数据字典中的定义，数据库中所包含数据也将被清除。DROPDATABASE[IFEXISTS]数据库名;MySQL数据库管理【例4-4】删除mytest数据库，删除前判断数据库是否存在。DROPDATABASEIFEXISTSmytest;数据类型:是指数据所代表信息的类型,不同的数据类型在使用范围、存储方式都不一样。MySQL数据类型数字类型日期和时间类型字符型数据库通用SET类型ENUM类型JSON数据类型MySQL使用(自学)数字类型MySQL数据类型类型字节取值范围（有符号）取值范围（无符号）TINYINT1（-128,127）（0,255）SAMLLINT2（-32768,32767）（0,65535）MEDIUMINT3（-8388608,8388607）（0，16777215）INT4（-2147483648,2147483647）（0,4294967295）BIGINT8（-263,263-1）（0,264-1）DECIMAL[(P[，S])]如果P>S,为P+2字节，否则为S+2字节和P、S的值有关和P、S的值有关关于数字类型的相关说明如下：MySQL数据类型（1）在选择数据类型时，若一个数据将来可能参与数学计算，推荐使用整数或定点数类型：若只用来显示，则推荐使用字符串类型。例如，商品库存保存为整数类型；用户的身份证、电话号码可以保存为字符串型。（2）表的主键推荐使用整数类型，与字符串相比，整数类型的处理效率更高，查询速度更快。（3）定义无符号数据类型，需要在数据类型右边加UNSIGNED关键字，例如INTUNSIGNED；字符型MySQL数据类型数据类型说明限制CHAR(n)固定长度字符串0~255字符VARCHAR(n)可变长度字符串与字符集相关TEXT长文本数据0~28-1字符BLOB二进制长文本数据0~216-1字符关于字符类型的相关说明如下：MySQL数据类型（1）CHAR(n)存储存储大小为n个字符。如果实际输入不足n个字符，系统会自动在后面添加空格来填满。（2）VARCHAR(n)存储大小为输入数据的字符的实际长度，其存储的最大长度取决于字符集，gbk和utf8对应的最大值分别为32766和21488。（3）BLOB类型用于保存数据量较大的二进制数据，如图片、PDF文档等，BLOB与TEXT类型的主要区别为：BLOB类型根据二进制数据进行比较与排序，区分字符大小写；TEXT类型根据字符进行比较与排序，不区分字符大小写。日期和时间类型MySQL数据类型类型字节取值范围格式YEAR11901到2155YYYYTIME3-838:59到838:59:59HH:MM:SSDATE31000-01-01到9999-12-31YYYY-MM-DDDATETIME81000-01-0100:00:00到9999-12-3123:59:59YYYY-MM-DDHH:MM:SSTIMESTAMP41970-01-0100:00:00到2038-01-1903:14:07YYYY-MM-DDHH:MM:SS关于日期时间类型的相关说明如下：MySQL数据类型（1）在输入YEAR类型的值时，既可以使用四位字符和数字，也可以使用2位字符和数字。使用两位字符表示为'00'~'99'，其中，'00'~'69'会被转化为2000~2069，'70'~'99'的值会被转换为1970~1999。（2）日期型DATE的常量有多种表示方式：'20221025'、'2022-10-25'、'22-10-25'、'221025'都表示2022年10月25日。日期型的变量CURRENT_DATE返回系统当前日期，日期函数NOW()返回系统当前日期和日间。（3）时间型的常量以'HHMMSS'或HHMMSS格式表示，如输入'121314'或121314都表示12:13:14（12小时13分14秒）。时间型的变量CURRENT_TIME返回系统当前时间。创建数据表在已存在的数据库中建立新表语法格式：CREATETABLE[IFNOTEXISTS]<表名>(<字段名>

<字段类型>[字段属性]…[索引定义])[表选项];MySQL数据表管理【例4-5】在teaching_manage数据库中创建一个名称为student的数据表，保存学生信息。CREATETABLEIFNOTEXISTSstudent( snoCHAR(6)PRIMARYKEYCOMMENT'学生编号’, snameVARCHAR(20)NOTNULL, deptVARCHAR(20), sexCHAR(1), birthdayDATE, totalcreditDECIMAL(4,1)DEFAULT0, remarksVARCHAR(100) );MySQL数据表管理【例4-6】在teaching_manage数据库中创建一个名称为course的数据表，保存课程信息。

CREATETABLEcourse( cnoCHAR(3)PRIMARYKEY cnameVARCHAR(30)NOTNULLUNIQUE termTINYINT, ctimeTINYINTUNSIGNED, creditDECIMAL(3,1));MySQL数据表管理【例4-7】在teaching_manage数据库中创建一个名称为score的数据表，保存成绩信息。CREATETABLEscore( score_idINTUNSIGNEDAUTO_INCREMENTsnoCHAR(6), cnoCHAR(3), gradeDECIMAL(4,1)CHECK(grade>=0) PRIMARYKEY(score_id),CONSTRAINTfk_score_snoFOREIGNKEY(sno)REFERENCESstudent(sno),CONSTRAINTfk_score_cnoFOREIGNKEY(cno)REFERENCEScourse(cno));MySQL数据表管理【例4-8】在teaching_manage数据库中利用score创建一个名称为sc的数据表，保存成绩信息。CREATETABLEscASSELECT*FROMscore;MySQL数据表管理注意：使用CREATETABLE+子查询方式创建表，只复制表结构和内容，原表中的约束和索引通常不会被复制。查看数据表1.查看库中数据表SHOWTABLES[LIKE匹配模式];MySQL数据表管理【例4-9】查看当前数据库中所有数据表。SHOWTABLES;2.查看数据表的详细信息SHOWTABLESTATUS[FROM数据库名][LIKE匹配模式];MySQL数据表管理【例4-10】查看当前数据库中所有数据表的详细信息。SHOWTABLESTATUS;3.查看表结构DESC<数据表名>;MySQL数据表管理【例4-11】查看当前数据库中student表的结构。DESCstudent;4.查看表的创建语句SHOWCREATETABLE<数据表名>;MySQL数据表管理【例4-12】查看当前数据库中student表的创建语句。SHOWCREATETABLEstudent;修改数据表1.修改数据表名称ALTERTABLE<原数据表名>RENAME[TO|AS]<新数据表名>;MySQL数据表管理【例4-13】修改数据表student为student1。ALTERTABLEstudentRENAMETOstudent1;2.修改数据表选项ALTERTABLE<数据表名><表选项>[=]<表选项>;MySQL数据表管理【例4-14】修改数据表student的字符集为gbk。ALTERTABLEstudentCHARSET=gbk;修改表结构1.新增字段ALTERTABLE<数据表名>ADD[COLUMN]<字段名类型>[FIRST|AFTER字段名];MySQL数据表管理【例4-15】修改数据表student增加1个新列nativeplace

。ALTERTABLEstudentADDnativeplaceVARCHAR(50);2.修改字段（1）修改字段名ALTERTABLE<数据表名>CHANGE<旧字段名><新字段名类型>MySQL数据表管理【例4-16】修改数据表student将nativeplace改名为native。ALTERTABLEstudentCHANGEnativeplacenativeVARCHAR(50);（2）修改字段类型和位置ALTERTABLE<数据表名>MODIFY<字段名类型>[FIRST|AFTER字段名2]MySQL数据表管理【例4-17】修改数据表student将nativeplace类型改为VARCHAR(30)。ALTERTABLEstudentMODIFYnativeplaceVARCHAR(30);3.删除字段ALTERTABLE<数据表名>DROP[COLUMN]<字段名>;MySQL数据表管理【例4-18】修改数据表student删除列nativeplaceALTERTABLEstudentDROPnativeplace;4.添加和删除约束ALTERTABLE<数据表名>[ADDCONSTRAINT<完整性约束名><完整性约束>][DROPCONSTRAINT<完整性约束名>]MySQL数据表管理【例4-19】修改数据表score，在sno、cno两字段上添加唯一约束uk_sno_cno

ALTERTABLEscoreADDCONSTRAINTuk_sno_cnoUNIQUE(sno,cno);删除数据表DROPTABLE<数据表名>;表被删除后，表在数据字典中的定义也被删除，此表上建立的索引和视图也被自动删除。MySQL数据表管理【例4-20】删除学生表DROPTABLEstudent;SQL提供了数据更新功能INSERT插入DELETE删除UPDATE更新数据表的数据操作插入数据1.插入单个记录语句格式

INSERT INTO<表名>[(<属性列1>[，<属性列2>…)] VALUES(<常量1>[，<常量2>]…)数据表的数据操作注意：

（1）INTO子句属性列的顺序可与表定义中的顺序不一致

（2）VALUES子句提供的值必须与INTO子句匹配1.插入单个记录数据表的数据操作【例4-21】向student表中插入一条记录，所有字段都有值INSERTINTOstudentVALUES('001242','张青','计算机','女','04-01-22',50,'三好生');【例4-22】

向student表中插入一条记录，部分字段有值INSERTINTOstudent(sno,sname,dept)VALUES('001243','李四','计算机');2.插入多个记录INSERT[INTO]<表名>[(字段名,…)][AS]子查询数据表的数据操作【例4-23】新建一个stu表，并向stu表中插入查询结果CREATETABLEstu(snoCHAR(6),snameVARCHAR(10),deptVARCHAR(20),sexVARCHAR(1));INSERTINTOstuSELECTsno,sname,dept,sexFROMstudentWHEREdept='计算机';注意：stu表定义的字段的类型与长度最好与SELECT后面字段的类型和长度相同，否则拒绝插入。修改数据UPDATE<表名>SET列名={表达式|(子查询)}[,…][WHERE<条件表达式>];注意：

（1）如果不选WHERE子句，则表中所有的行全被更新；

（2）如果选择了WHERE子句，则使WHERE中条件表达式为真的行被更新。数据表的数据操作【例4-24】把student表中的总学分加10。UPDATEstudentSETtotalcredit=totalcredit+10;数据表的数据操作【例4-25】把student表中姓名为“罗林琳”的学生的专业改为“计算机”，备注改为“三好学生”。UPDATEstudentSETdept='计算机',remarks='三好学生'WHEREsname='罗林琳';删除数据DELETEFROM<表名>[WHERE<条件表达式>];WHERE子句指定要删除的元组缺省表示要删除表中的全部元组，表的定义仍在字典中数据表的数据操作数据表的数据操作【例4-26】删除student表中计算机系全体学生的记录。DELETEFROMstudentWHEREdept='计算机';【例4-27】删除student表中的所有记录。。DELETEFROMstudent;TRUNCATETABLE语句截断基本表，从表中删除所有的行，并且释放该表所使用的存储空间。在使用TRUNCATETABLE语句时，不能回退已删除的行。数据表的数据操作【例4-28】将表STUDENT删除，并释放其存储空间。TRUNCATETABLESTUDENT;为什么要定义自增型的字段？自增型字段（1）业务需求（2）空间效率：相同长度的字符串和数值所能表示的范围有较大差别。

如4个字节的数值型字符所表示的范围为：‘0000’—‘9999’共10000种不同的值，而4个字节的int类型所能表示的范围为：0——232-1共42亿种不同的值。（3）时间效率：相同长度的字符串和数值进行比较时，数值的比较速度要高于字符的比较速度。AUTO_INCREMENT类型AUTO_INCREMENT定义自增长字段，查看参数下：SHOWVARIABLESLIKE'auto_increment%';自增型字段参数名称默认值取值范围作用auto_increment_increment11~65535控制增量的幅度。auto_increment_offset11~65535增量开始的位置（开始的偏移量）。--设置AUTO_INCREMENT类型的初始值为2SET@@auto_increment_offset=2;--设置AUTO_INCREMENT类型的增量为2SET@@auto_increment_increment=2;自增型字段的数据插入AUTO_INCREMENT类型定义的字段默认值和步长均为1，每插入一条记录，该字段的值自动加1。插入操作中，被AUTO_INCREMENT修饰的字段可以给空值或具体值。自增型字段【例4-28】创建stu表，设置自增型字段，插入记录。CREATETABLEstu(stu_idINTUNSIGNEDAUTO_INCREMENTPRIMARYKEY,snameVARCHAR(10));INSERTINTOstuVALUES(NULL,'张三'),(NULL,'李四'),(10,'王五');2.自增型字段的数据修改在定义表结构时，可以设置自增型字段的初始值，也可以通过修改表结构来修改初始值。当修改表结构，重新定义初始值时，原表中的数据不会发生变化，只对新插入的数据起作用。自增型字段【例4.30】创建数据表stu，定义stu_id为自增型字段，设置初始值为200001，并插入两条记录。CREATETABLEstu(stu_idINTUNSIGNEDAUTO_INCREMENTPRIMARYKEY,snameVARCHAR(10))AUTO_INCREMENT=200001;INSERTINTOstu2VALUES(NULL,'张三'),(NULL,'李四');自增型字段【例4.31】修改stu表的stu_id字段的初始值为202201，并插入两条记录。

ALTERTABLEstuAUTO_INCREMENT=202201;INSERTINTOstu2VALUES(NULL,'张三'),(NULL,'李四');3.自增型字段的数据删除执行DELETE命令删除表记录后，再执行插入操作时，自增型字段的值在原来序列最大值的基础上自动增加。此时如想从初始值开始自增，必须使用TRUNCATE命令删除全表的记录。自增型字段自增型字段【例4.32】用DELETE命令删除stu中所有记录，重新插入新记录DELETEFROMstu；INSERTINTOstuVALUES(NULL,'张三'),(NULL,'李四');【例4.33】用TRUNCATE命令删除stu中所有记录，插入新记录TRUNCATETABLEstu;INSERTINTOstuVALUES(NULL,'张三'),(NULL,'李四');数据库的完整性约束数据库的完整性是指保证数据库中数据的正确性和相容性。数据的正确性是指数据库数据符合现实世界语义且反映当前的实际状况。数据的相容性是指数据库同一对象在不同关系表中的数据是一致的。在定义数据表时可以进一步定义与此表有关的完整性约束条件，如主码、外码、空值、唯一值和默认值等约束。当数据库用户对数据库进行更新操作时，数据库管理系统会自动检测操作数据是否符合相关完整性约束，并做出相应的处理。实体完整性约束保证一个表的每行取值是唯一的。要实现实体完整性，必须指定一个表中的一列或多列作为表的主键（

PRIMARYKEY）。定义方式有：列级和表级。（1）实体完整性定义【例】：列级定义主码

CREATETABLEXS

(学号CHAR(9)PRIMARYKEY，

姓名CHAR(8)NOTNULL);【例】：表级定义主码

CREATETABLEscore(snoCHAR(6)，

cnoCHAR(3)，

gradeDECIMAL(5,1)

PRIMARYKEY（sno,cno）

//表级定义

);实体完整性约束对主码列进行插入或更新操作时，DBMS按照实体完整性规则自动进行检查。包括：检查主码值是否唯一，如果不唯一则拒绝执行；检查主码的各个属性是否为空，如果为空就拒绝执行。（2）实体完整性检查实体完整性违约处理：拒绝(RESTRICT)执行。（3）实体完整性违约处理参照完整性约束通过声明一个FOREIGNKEY在表中定义一个或多个外键并实现参照完整性，用REFERENCES定义外键来自的表名，即主表或父表名。（1）参照完整性定义通过在定义外键时加参照完整性选择项ONUPDATE/ONDELETE来实现当主表被引用的主键被修改/删除时，为了不破坏参照完整性约束所有可能的处理办法：

RESTRICT：缺省选择项，称为限制引用操作。凡是被子表所引用的主键，不得进行任何操作；

CASCADE：称为级联引用操作。凡是对主表的主键进行的操作，子表中引用此主键的行也随之自动被修改/删除SETNULL:置空操作。凡是对主表的主键进行的操作，造成外键值与主键不一致，子表中引用此主键的值被设置为NULL。参照完整性约束（2）参照完整性检查和违约处理被参照表（父表）参照表（子表）违约处理可能破坏参照完整性插入记录拒绝可能破坏参照完整性修改外码值拒绝删除记录可能破坏参照完整性拒绝/级联删除/设置为空值修改主码值可能破坏参照完整性拒绝/级联删除/设置为空值参照完整性约束【例4-34】：创建dept和emp表，设置dno字段的参照完整性关系，并设置更新和删除违约处理方式CREATETABLEdept(dnoCHAR(3)PRIMARYKEY,dnameVARCHAR(50));CREATETABLEemp(enoCHAR(3)PRIMARYKEY,enameVARCHAR(50),dnoCHAR(2),CONSTRAINTfk_dnoFOREIGNKEY(dno)REFERENCESdept(dno)

ONDELETESETNULLONUPDATECASCADE);用户自定义完整性约束就某一个体应用涉及的数据必须满足的语义要求。（1）用户自定义完整性定义定义类型：列值非空（NOTNULL）列值唯一（UNIQUE）默认值约束（DEFAULT）检查列值是否满足一个布尔表达式（CHECK）对插入或更新操作时，