数据基础及应用 6

第3章数据库和表3.4设计数据表3.3管理数据库数据库介绍3.2创建数据库3.5表的创建与维护CONTENTS目录

3.13.1数据库介绍3.1.1MySQL的数据库类型MySQL服务器安装成功后，就创建了一个实例一个实例包含1个系统数据库和若干个用户自定义数据库1.系统数据库：由MySQL系统创建和维护，名为mysql记录系统配置、任务情况和用户数据库等管理信息，控制MySQL和用户数据库的运行3.1.1MySQL的数据库类型（续）2.用户数据库：包括系统提供的示例数据库和用户创建的数据库（1）系统提供sakila和world数据库，供测试之用（2）用户根据实际需求自行创建的数据库MySQL实例的组成：MySQL实例=系统数据库(mysql)+用户数据库(sakila,world,自定义)3.1.2数据库文件MySQL中所有数据、对象和事务日志以文件形式保存在磁盘上文件分为两类：数据文件和事务日志文件1.数据文件（在data目录中，每张表创建三个文件）：.frm文件：存储表结构定义.MYD文件：存储表数据.MYI文件：存储表索引3.1.2数据库文件（续）2.事务日志文件（Transactionlogfile）：记录对数据库的INSERT、ALTER、DELETE、UPDATE等操作必要时可利用日志文件进行数据库恢复MySQL的日志文件类型：错误日志、查询日志、慢查询日志、二进制日志等3.2创建数据库3.2.1使用workbench创建数据库创建步骤：（1）单击Schemas选项卡，单击鼠标右键（2）选择CreateSchema菜单（3）输入数据库名称（如student），选择字符集utf8mb4（4）单击Apply按钮，显示创建数据库的SQL语句（5）再次单击Apply，创建数据库成功3.2.2使用SQL语句创建数据库语法格式：CREATEDATABASE数据库名;示例：CREATEDATABASEstudent;数据库名称命名规则：（1）不能与其他数据库重名（2）由字母、数字、下划线(_)和$组成，不能以单独数字开头（3）名称最长可为64个字符（4）不能使用MySQL关键字作为数据库名（5）建议使用小写定义数据库名（便于跨平台移植）3.3管理数据库3.3.1查看数据库方法一：在Schemas选项卡中查看当前数据库系统中的数据库方法二：在查询窗口中执行SHOWDATABASES命令SHOWDATABASES;可查看当前数据库系统中已创建的所有数据库3.3.2删除数据库方法一：使用workbench在要删除的数据库上单击右键→选择DropSchema→单击DropNow方法二：使用SQL语句语法：DROPDATABASE数据库名;示例：DROPDATABASEstudent;注意：当前正在使用的数据库不能删除，MySQL系统数据库无法删除3.4设计数据表3.4.1字符串类型MySQL字符串类型：CHAR、VARCHAR、BLOB、TEXT、ENUM、SET1.CHAR和VARCHAR类型：CHAR(n)：定长字符串，长度1~255，存储时右侧用空格填补VARCHAR(n)：变长字符串，长度1~255，只存储所需字符+1字节记录长度检索时CHAR尾部空格被删除，VARCHAR保留注意：同一表中不建议混用CHAR和VARCHAR3.4.1字符串类型（续）2.BLOB和TEXT类型：BLOB：二进制大对象，可保存图片等内容分为TINYBLOB、BLOB、MEDIUMBLOB、LONGBLOBTEXT：文本类型，分为TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXTBLOB和TEXT不能有DEFAULT值应定期运行OPTIMIZETABLE进行优化减少碎片3.4.1字符串类型——ENUM和SET3.ENUM类型（枚举）：语法：col_nameENUM('值1','值2','值3','值4')只能从枚举列表中取一个值，按索引排序允许NULL时NULL为默认值，否则第一个元素为默认值4.SET类型（集合）：语法：col_nameSET('值1','值2','值3','值4')最多64个成员，可从定义的列值中选择多个，成员间用逗号隔开3.4.2日期和时间类型MySQL日期时间类型：（1）DATE：日期，格式YYYY-MM-DD，范围1000-01-01~9999-12-31，3字节（2）DATETIME：日期+时间，格式YYYY-MM-DDHH:MM:SS，8字节（3）TIMESTAMP：时间戳，4字节，可自动记录INSERT/UPDATE操作时间（4）TIME：时间，格式HH:MM:SS，范围-838:59:59~838:59:59（5）YEAR：年份，1字节，范围1901~2155（4位格式）3.4.2TIMESTAMP类型示例【例】创建表时使用TIMESTAMP自动记录时间：CREATETABLEmy_test(idINTprimarykey,tsTIMESTAMPDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMP);插入数据时，ts自动取当前时间；更新记录时，ts自动更新3.4.3数字类型1.整数类型：TINYINT：1字节，范围-128~127（无符号0~255）SMALLINT：2字节INT(INTEGER)：4字节，最常用BIGINT：8字节，用于大数字3.4.3数字类型（续）2.浮点类型：FLOAT：4字节，单精度浮点数DOUBLE：8字节，双精度浮点数DECIMAL(M,D)：精确小数，M最大65，D为小数位数3.4.4JSON数据类型MySQL支持JSON数据类型，优势：（1）存储在JSON列中的文档会被自动验证，无效文档产生错误（2）文档被转换为允许快速读取的内部格式（3）MySQL8优化器可执行JSON列的局部就地更新JSON类型列的值被写为字符串如果字符串不符合JSON格式，则会产生错误3.5表的创建与维护3.5.1使用workbench工具创建表展开数据库→在Tables节点上单击右键→选择CreateTable列属性设置说明：PK：主键NN：非空UQ：唯一B：二进制UN：无符号ZF：零填充AI：自动增长G：计算列Default：默认值Comments：注释设置完成后单击Apply创建表3.5.2使用SQL语句创建表语法格式：CREATETABLE数据表名(列名数据类型[NOTNULL][DEFAULT默认值][AUTO_INCREMENT][PRIMARYKEY],...);注意：创建表前应使用USE数据库名;指定当前数据库否则会报Nodatabaseselected错误3.5.2创建表——例3-1创建major表【例3-1】创建major(专业)表：USEstudent;CREATETABLEmajor(namevarchar(40)NOTNULL,create_yearintNOTNULL,brieftext,PRIMARYKEY(name));说明：3列，varchar/date/text三种类型，专业名称和成立年份非空3.5.2创建表——例3-2创建student表【例3-2】创建student(学生信息)表：CREATETABLEstudent(idchar(10)NOTNULL,namevarchar(20)NOTNULL,gendervarchar(10),birthdaydate,markint,majorvarchar(20),

photolongblob,resumetext,is_bonusintDEFAULT'0',PRIMARYKEY(id));photo用longblob存照片，resume用text存简历3.5.2创建表——例3-3~3-5【例3-3】创建student_course(选课)表：CREATETABLEstudent_course(student_idchar(10)NOTNULL,course_idchar(10)NOTNULL,markfloat(5,2)DEFAULTNULL,PRIMARYKEY(student_id,course_id),KEYFK_course_id(course_id));复合主键(student_id,course_id)，mark为float(5,2)3.5.2创建表——例3-3~3-5【例3-4】创建course(课程)表：CREATETABLEcourse(idchar(10)NOTNULL,namevarchar(20)DEFAULTNULL,categoryvarchar(20)DEFAULTNULL,markintDEFAULTNULL,PRIMARYKEY(id));3.5.2创建表——例3-3~3-5（续）【例3-5】创建score(成绩)表：CREATETABLEscore(idintNOTNULLAUTO_INCREMENT,markjsonNOTNULL,createdtimestampNOTNULLDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMP,PRIMARYKEY(id))3.5.3使用SQL语句复制表1.创建和已知表结构一致的表：CREATETABLEnewTableLIKEoldTable;【例3-6】CREATETABLEcourse_bakLIKEcourse;2.创建和已知表结构相似的表：使用SHOWCREATETABLEcourse;显示建表语句在此基础上修改表名、添加或删除字段，快速创建新表3.5.4查看表结构1.DESCRIBE语句（查看字段信息）：DESCRIBE表名;【例3-7】DESCRIBEstudent;显示：Field(列名)、Type(类型)、Null(是否允许空值)、Key(是否索引)、Default(默认值)、Extra(附加信息)3.5.4查看表结构（续）2.SHOWCREATETABLE语句（显示建表SQL语句）：SHOWCREATETABLE表名;【例3-8】SHOWCREATETABLEstudent;还可查看存储引擎和字符编码3.5.5使用workbench维护表（1）在表名上单击右键→选择AlterTable（修改表）（2）在要删除的列上单击右键→选择Deleteselected删除列（3）单击列的名称，可对列进行修改（4）在空白行输入新的列名称，可添加列（5）操作完成后单击Apply保存（6）在表名上单击右键→选择DropTable→单击DropNow删除表3.5.6使用SQL语句维护表——修改表ALTERTABLE语句修改表结构：添加列：ALTERTABLE表名ADD列名类型[FIRST|AFTER列名];修改列：ALTERTABLE表名MODIFY列名新类型;删除列：ALTERTABLE表名DROP列名;改表名：ALTERTABLE表名RENAME新表名;3.5.6使用SQL语句维护表——修改表（续）【例3-9】添加email列，修改name列类型：ALTERTABLEstudentADDemailvarchar(30)NOTNULL,MODIFYnamevarchar(40);3.5.6使用SQL语句维护表——示例【例3-10】在第一列添加memo字段：ALTERTABLEstudentADDmemovarchar(10)NOTNULLFIRST;【例3-11】在gender列后添加age列：ALTERTABLEstudentADDageintNOTNULLAFTERgender;3.5.6使用SQL语句维护表——示例（续）【例3-12】删除memo列：ALTERTABLEstudentDROPmemo;【例3-13】修改表名：ALTERTABLEstudentRENAMEstudent_bak;3.5.6使用SQL语句维护表——删除表删除表语法：DROPTABLE数据表名;注意：删除表后表中数据全部清除，没有备份则无法恢复删除不存在的表会产生错误，可加入IFEXISTS避免：DROPTABLEIFEXISTSstudent;如果表student存在则删除，不存在也不会报错3.6使用workbench管理表中数据3.6使用workbench管理表中数据（1）查看数据：在表名上单击右键→选择selectrows（2）插入数据：在数据下方空行输入数据→单击Apply（3）修改数据：双击要修改的单元格→输入数据→单击Apply（4）删除数据：在数据行左边三角形右键单击→选择DeleteRow(s)→单击Apply3.7表的分区3.7表的分区——概述当表中数据量较大时，可采用分区存储提高性能分区优势：（1）数据分布在不同物理设备上，高效利用硬件（2）分区数据更容易维护（批量删除、优化、检查）（3）避免某些瓶颈（如InnoDB单个索引的互斥访问）3.7表的分区——概述（续）（4）可独立备份和恢复分区（5）查询优化：只搜索相关分区而非全表扫描MySQL分区规则：范围(RANGE)、列表(LIST)、哈希(HASH)、键(KEY)分区3.7表的分区——范围分区范围分区(RANGE)：按列值范围分区，使用VALUESLESSTHAN定义用于分区的列必须包含在主键中【例3-14】按入学成绩进行范围分区：PARTITIONBYRANGE(mark)(PARTITIONp0VALUESLESSTHAN(500),3.7表的分区——范围分区（续）PARTITIONp1VALUESLESSTHAN(600),PARTITIONp2VALUESLESSTHANmaxvalue);低于500存p0，500~600存p1，600以上存p23.7表的分区——列表分区列表分区(LIST)：按一组离散值分区，使用VALUESIN定义【例3-15】按是否有奖学金分区：PARTITIONBYLIST(is_bonus)(PARTITIONp0VALUESIN