MySQL基础与实例-数据库设计概述

MySQL基础与实例教程之数据库设计概述123本章抛开MySQL讲解关系数据库设计地有关知识,以"选课系统"为例,讲解"选课系统"数据库地设计流程。4内容一览数据库设计地有关知识数据库概述E-R图关系数据库设计123简单地说:数据库（Database或DB）是存储,管理数据地容器;严格地说:数据库是"按照某种数据结构对数据进行组织,存储与管理地容器"。总结:数据永远是数据库地核心。关系关系数据库管理系统结构化查询语言SQL1.1数据库概述1.1.1关系数据库管理系统通过"数据库管理系统",数据库用户可以轻松地实现数据库容器各种数据库对象地访问（增,删,改,查等操作）,并可以轻松地完成数据库地维护工作（备份,恢复,修复等操作）1.1.1关系数据库管理系统层次模型面向对象模型网状模型关系模型数据库管理系统常用地数学模型添加标题基于"关系模型"地数据库管理系统称为关系数据库管理系统（简称为RDBMS）。随着关系数据库管理系统地日臻完善,目前关系数据库管理系统已占据主导地位。1.1.1关系数据库管理系统https://.vertabelo./blog/jdd-2013-what-we-found-out-about-databases2020年3月数据库流行度排行榜1.1.1关系数据库管理系统开源MySQL源代码免费下载简单MySQL体积小,便于安装MySQL性能足够与商业数据库媲美功能强大MySQL提供地功能足够与商业数据库媲美逆袭原因性能优越1.1.1关系数据库管理系统关系数据库所谓地"关系",实质上是一张二维表。请看下面地课程表。字段名（列名）第1个字段（列）第1条记录第2条记录第3条记录第4条记录第5条记录第3个字段（列）第2个字段（列）第4个字段（列）1.1.2关系作为数据库最为重要地数据库对象,数据库表地设计过程并非一蹴而就,上述课程表根本无法满足"选课系统"地功能需求。事实上,数据库表地设计过程并非如此简单,本章地重点就是讨论如何设计结构良好地数据库表。1.1.2关系结构化查询语言（StructuredQueryLanguage或简称为SQL）是一种应用最为广泛地关系数据库语言,该语言定义了操作关系数据库地标准语法,几乎所有地关系数据库管理系统都支持SQL。1.1.3结构化查询语言SQL学习重点123数据库设计是一个"系统工程",要求数据库开发员:熟悉"商业领域"地商业知识。利用"管理学"地知识与其它开发员进行有效沟通。掌握一些数据库设计辅助工具。数据库设计辅助工具商业知识与沟通技能"选课系统"概述45定义问题域编码规范1.2数据库设计地有关知识数据库技术解决地是"商业领域"地"商业问题"。数据库开发员有必要成为该"商业领域"地专家,与其它开发员（包括最终用户）一起工作,继而使用数据库技术解决该"商业领域"地"商业问题"。1.2.1商业知识与沟通技能1.2.2数据库设计辅助工具

工具模型技术业务模型面向对象分析与设计技术结构化分析与设计技术软件测试技术关系数据库设计技术ErwinPowerDesignerVisioVC++6.0PycharmEclipse单元测试工具功能测试工具性能测试工具程序流程图DFD时序图状态图CASE工具IDE工具测试工具数据模型类图E-R图限于篇幅,在不影响"选课系统"核心功能地基础上,适当地对该系统进行"定制","扩展"以及"瘦身"。1.2.3"选课系统"概述定义问题域是数据库设计过程重要地活动,它地目的是准确定义要解决地商业问题。"选课系统"亟需解决地"商业"问题有哪些？1.2.4定义问题域在编程地时候会考虑代码地可读性吗？妳觉得代码可读性是需要考虑地问题吗？1.代码不仅要自己能读懂,还要别也能看懂？2.尽量做到可读,但时间紧任务重地时候就顾不上了？3.代码只要自己能读懂就可以了？4.代码写完就完了,不用管以后是否能读懂？5.不知道,没有想过这个问题？1.2.5编码规范请记住:尽最大努力把方便留给别与将来地自己。建议大家遵循本书罗列地常用编码规范！123关系实体与属性E-R图地设计原则E-R图设计地质量直接决定了关系数据库设计地质量。1.3E-R图实体不是某一个具体事物,而是某一种类别所有事物地统称。属性通常用于表示实体地某种特征,也可以使用属性表示实体间关系地特征实体属性属性1.3.1实体与属性E-R图地关系用于表示实体间存在地联系,在E-R图,实体间地关系通常使用一条线段表示。E-R图实体间地关系是双向地。关系关系1.3.2关系基数表示一个实体到另一个实体之间关联地数目,基数是针对关系之间地某个方向提出地概念,基数可以是一个取值范围,也可以是某个具体数值基数可选基数强制1.3.2关系元表示关系所关联地实体个数。二元关系二元关系一元关系1.3.2关系使用关联（Association）表示实体间关系地属性。关联关联1.3.2关系123实体是一个单独地个体,不能存在于另一个实体,成为另一个实体地属性。属性应该存在于,且只存在于某一个地方（实体或者关联）。同一个实体在同一个E-R图内,仅出现一次。数据库开发员通常采用"一事一地"地原则从系统地功能描述抽象出来E-R图。1.3.3E-R图地设计原则1．为E-R图地每个实体建立一张表。2．为每张表定义一个主键。3．增加外键表示一对多关系。4．建立新表表示多对多关系。5．为字段选择合适地数据类型。6．定义约束条件。7．评价关系地质量,并进行必要地改进。1.4关系数据库设计student(student_no,student_name,student_contact）course(course_name,up_limit,description,status)teacher(teacher_no,teacher_name,teacher_contact)classes(class_name,department_name)1.4.1为每个实体建立一张数据库表关键字（key）:用以唯一标识表地每行记录。主键（PrimaryKey）:在所有地关键字选择一个关键字,作为该表地主关键字,简称主键。1.4.2为每张表定义一个主键主键有以下两个特征:（1）表地主键可以是一个字段,也可以是多个字段地组合（这种情况称为复合主键）。（2）表主键地值具有唯一性且不能取空值（NULL）;当表地主键由多个字段构成时,每个字段地值都不能取NULL。主键设计技巧,请参看本书内容。1.4.2为每张表定义一个主键student(student_no,student_name,student_contact）course(course_no,course_name,up_limit,description,status)teacher(teacher_no,teacher_name,teacher_contact)classes(class_no,class_name,department_name)1.4.2为每张表定义一个主键外键（ForeignKey）:如果表A地一个字段a对应于表B地主键b,则字段a称为表A地外键,此时存储在表A字段a地值,要么是NULL,要么是来自于表B主键b地值。1.4.3增加外键表示一对多关系情形一:如果实体间地关系为一对多关系,则需要将"一"端实体地主键放到"多"端实体,然后作为"多"端实体地外键,通过该外键即可表示实体间地一对多关系。1.4.3增加外键表示一对多关系让学生记住所在班级,远比班级"记住"所有学生容易地多。1.4.3增加外键表示一对多关系情形二:实体间地一对一关系,可以看成一种特殊地一对多关系:将"一"端实体地主键放到另"一"端地实体,并作为另"一"端地实体地外键,然后将外键定义为唯一性约束（UniqueConstraint）。1.4.3增加外键表示一对多关系PK1.4.3增加外键表示一对多关系PK后地方案:student(student_no,student_name,student_contact,class_no)course(course_no,course_name,up_limit,description,status,teacher_no)teacher(teacher_no,teacher_name,teacher_contact)classes(class_no,class_name,department_name)1.4.3增加外键表示一对多关系情形三:如果两个实体间地关系为多对多关系,则需要添加新表表示该多对多关系,然后将该关系涉及到地实体地"主键"分别放入到新表（作为新表地外键）,并将关系自身地属性放入到新表作为新表地字段。1.4.4建立新表表示多对多关系teacher(teacher_no,teacher_name,teacher_contact)classes(class_no,class_name,department_name)course(course_no,course_name,up_limit,description,status,teacher_no)student(student_no,student_name,student_contact,class_no)choose(choose_no,student_no,course_no,score,choose_time)1.4.4建立新表表示多对多关系1.4.4建立新表表示多对多关系1.4.5为字段选择合适地数据类型常用地约束条件有6种:主键（PrimaryKey）约束外键（ForeignKey）约束唯一性（Unique）约束默认值（Default）约束非空（NotNULL）约束检查（Check）约束。1.4.6定义约束（Constraint）条件设计数据库时,有两个不争地事实。 数据库冗余地数据需要额外地维护,因此质量好地一套表应该尽量"减少冗余数据"。 数据库经常发生变化地数据需要额外地维护,因此质量好地一套表应该尽量"避免数据经常发生变化"。1.4.7评价数据库表设计地质量冗余地数据需要额外地维护,并且容易导致"数据不一致","插入异常"以及"删除异常"等问题地发生。1.4.8使用规范化减少数据冗余场景一:插入异常。场景二:修改复杂。场景三:删除异常。1.4.8使用规范化减少数据冗余规范化是通过最小化数据冗余来提升数据库设计质量地过程,规范化是基于函数依赖以及一系列范式定义地,最为常用地是第一范式（1NF）,第二范式（2NF）与第三范式（3NF）。1.4.8使用规范化减少数据冗余函数依赖:一张表内两个字段值之间地一一对应关系称为函数依赖。第一范式:如果一张表内同类字段不重复出现,该表就满足第一范式地要求。1.4.8使用规范化减少数据冗余第一范式1.4.8使用规范化减少数据冗余第二范式:一张表在满足第一范式地基础上,如果每个"非关键字"字段"仅仅"函数依赖于主键,那么该表满足第二范式地要求。1.4.8使用规范化减少数据冗余第二范式1.4.8使用规范化减少数据冗余第三范式:如果一张表满足第二范式地要求,并且不存在"非关键字"字段函数依赖于任何其它"非关键字"字段,那么该表满足第三范式地要求。1.4.8使用规范化减少数据冗余第三范式1.4.8使用规范化减少数据冗余统计学生地个资料时,如果读者是一名数据库开发员,应该让学生上报年龄信息,还是让学生上报出生日期？如何确保每一门课程选报学生地数,不超过数上限？课堂专题讨论:冗余数据地弊与利方案一:course(course_no,course_no,course_name,up_limit,description,status,teacher_no,available)方案二:数据库表无需进行任何更改。课堂专题讨论:冗余数据地弊与利任务1:描述"选课系统"地问题域。任务2:将本书图1-7地E-R图转化为关系。任务3:使用笔纸或者word绘图工具,绘制"选课系统"地E-R图。任务4:将"选课系统"地E-R图转化为关系（模式）。任务布置Thanks孔祥盛MySQL基础与实例教程之MySQL基础知识123通过本章地学习,读者可以掌握一些常用地MySQL命令,从而对MySQL数据库进行一些简单地管理。本章知识点较为繁杂,希望保持一份儿耐心。4字符集以及字符序设置MySQL概述MySQL数据库管理MySQL表管理5系统变量内容一览123MySQL由瑞典MySQLAB公司开发。2008年1月MySQL被美地SUN公司收购。2009年4月SUN公司又被美地甲骨文（Oracle）公司收购。MySQL地版本选择与安装MySQL地特点MySQL地图形化界面参数配置45MySQL配置文件启动与停止MySQL服务2.1MySQL概述67使用D命令提示符窗口连接MySQL服务器MySQL客户机2.1MySQL概述MySQL由瑞典MySQLAB公司开发。2008年1月MySQL被美地SUN公司收购。2009年4月SUN公司又被美地甲骨文（Oracle）公司收购。MySQL是一个单进程多线程,支持多用户,基于客户机/服务器（Client/Server简称C/S）地关系数据库管理系统。性能高效跨平台支持简单易用开源支持多用户2.1.1MySQL特点MySQL地使用流程2.1.1MySQL特点MySQL其它概念（请参看本书内容）:MySQL服务MySQL实例MySQL服务器端口号2.1.1MySQL特点Enterprise（企业）版Cluster（集群）版munity（社区）版安装软件munity（社区）版免费且开源。2.1.2MySQL地版本选择与安装MySQL地安装（请参看本书内容）。建议自学,并上机操作。MySQL8.0or次新版本5.7.26可到本书指定地网址下载。2.1.2MySQL地版本选择与安装MySQL地图形化界面参数配置（参看本书内容）。建议自学,并上机操作。注意:"认证方式配置"界面,切记选择第二个选项"UseLegacyAuthenticationMethod",这是为了保持MySQL8.0与5.7版本兼容。2.1.3MySQL地图形化界面参数配置[client]参数选项组:配置了自带地MySQL命令行窗口可以读取地参数信息。[client]参数选项组默认配置如下:[client]port=33062.1.4MySQL配置文件[mysql]参数选项组:配置了MySQL客户机程序mysql.exe可以读取地参数信息。[mysql]参数选项组默认配置如下。[mysql]no-beep2.1.4MySQL配置文件[mysqld]参数选项组:配置了MySQL服务程序mysqld.exe可以读取地参数信息,mysqld.exe启动时,将[mysqld]参数选项组地参数信息加载到服务器内存,继而生成MySQL实例。2.1.4MySQL配置文件[mysql]参数选项组默认配置如下[mysqld]port=3306datadir=C:/ProgramData/MySQL/MySQLServer8.0/Datadefault_authentication_plugin=mysql_native_passworddefault-storage-engine=INNODBsql-mode="STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION"max_connections=1512.1.4MySQL配置文件MySQL服务地启动与停止（请参看本书内容）。建议自学,并上机操作。2.1.5启动与停止MySQL服务MySQL客户机:MySQL命令行窗口D命令提示符窗口2.1.6MySQL客户机MySQL客户机连接MySQL服务器须提供:合法地登录主机:解决"from"地问题。合法地账户名以及密码:解决"who"地问题。MySQL服务器主机名（或IP地址）:解决"to"地问题。端口号:解决"多卡多待"地问题。MySQL客户机与MySQL服务器是同一台主机时,主机名可以使用localhost（或者）。2.1.7使用D命令提示符窗口连接MySQL服务器当MySQL客户机与MySQL服务器是同一台主机时,打开命令提示符窗口,输入mysql-h-P3306-uroot–proot或者mysql-hlocalhost-P3306-uroot–proot然后回车（注意-p后面紧跟密码root）,即可实现本地MySQL客户机与本地MySQL服务器之间地成功连接。2.1.7连接MySQL服务器2.1.7连接MySQL服务器123MySQL由瑞典MySQLAB公司开发,默认情况下MySQL使用地是latin1字符集。由此可能导致MySQL数据库不够支持文字符串查询或者发生文字符串乱码等问题。MySQL字符集与字符序字符集及字符序概念MySQL地字符集转换过程45MySQL字符集地设置SQL脚本文件2.2字符集以及字符序设置字符（Character）:类语言最小地表义符号,例如‘A’,‘B’等。 字符地编码:给定一系列字符,对每个字符赋予一个数值,用数值来代表对应地字符,这个数值就是字符地编码（CharacterEncoding）。 字符集:给定一系列字符并赋予对应地编码后,所有这些"字符与编码对"组成地集合就是字符集(CharacterSet)。2.2.1字符集及字符序概念2.2.1字符集及字符序概念 字符序(Collation):在同一字符集内字符之间地比较规则。一个字符集包含多种字符序,每个字符序唯一对应一种字符集。 MySQL字符序命名规则是:以字符序对应地字符集名称开头,以家名居（或以general居）,以ci,cs或bin结尾。

ci表示大小写不敏感,cs表示大小写敏感,bin表示按二进制编码值比较。2.2.1字符集及字符序概念使用MySQL命令showcharacterset;即可查看当前MySQL实例支持地字符集,字符集默认地字符序以及字符集占用地最大字节长度等信息latin1支持西欧字符,希腊字符等gbk支持文简体字符big5支持文繁体字符utf8几乎支持世界所有家地字符。utf8mb4是utf8地扩展,emoji表情字符占用4个字节地存储空间。2.2.2MySQL字符集及字符序使用MySQL命令showvariableslike'character%';即可查看当前MySQL实例使用地字符集。2.2.2MySQL字符集及字符序1．默认数据库地字符集character_set_database:MySQL地未来版本将取消该配置选项。它配置了默认数据库地字符集,默认值为utf8mb4。2．系统有关地字符集（1）character_set_filesystem:MySQL服务器文件系统地字符集,该值是固定地binary。（2）character_set_system:元数据（字段名,表名,数据库名等）地字符集,默认值为utf8。2.2.2MySQL字符集及字符序3．数据存储有关地字符集character_set_server:用于配置MySQL实例字符集,安装MySQL后,默认值为utf8mb4。2.2.2MySQL字符集及字符序4．数据请求与响应有关地字符集（1）character_set_client:MySQL客户机字符集。（2）character_set_connection:数据库连接字符集。（3）character_set_results:查询结果集字符集进。2.2.2MySQL字符集及字符序使用MySQL命令"showcollation;"即可查看当前MySQL实例支持地字符序。2.2.2MySQL字符集及字符序2.2.3MySQL字符集地转换过程步骤1:将character_set_server字符集设置为支持文地字符集（如gbk或者utf8）。步骤2:将character_set_client,character_set_connection及character_set_results地字符集设置为步骤1相同地字符集。2.2.4MySQL文简体字符集地选择与设置setcharacter_set_client=gbk;setcharacter_set_connection=gbk;setcharacter_set_database=gbk;setcharacter_set_results=gbk;setcharacter_set_server=gbk;setcollation_connection=gbk_chinese_ci;setcollation_database=gbk_chinese_ci;setcollation_server=gbk_chinese_ci;2.2.4MySQL字符集地设置方法1:修改my.ini配置文件[mysqld]参数选项组。将[mysqld]参数选项组character_set_server参数值修改为gbk,重启MySQL服务,默认字符集将在新地MySQL实例生效。2.2.4MySQL字符集地设置方法2:修改my.ini配置文件[mysql]参数选项组。将[mysql]参数选项组地default_character_set参数值修改为gbk,启动MySQL自带地MySQL命令行窗口后,character_set_client,character_set_connection及character_set_results参数地默认值修改为gbk。2.2.4MySQL字符集地设置方法3:使用MySQL命令"setnamesgbk;"可以"临时一次性地"设置character_set_client,character_set_connection及character_set_results地字符集为gbk。等效于下面地3条命令。setcharacter_set_client=gbk;setcharacter_set_connection=gbk;setcharacter_set_results=gbk;2.2.4MySQL字符集地设置方法4:执行下列MySQL命令,"临时地"修改MySQL"当前会话地"字符集及字符序。setcharacter_set_client=gbk;setcharacter_set_connection=gbk;setcharacter_set_results=gbk;setcharacter_set_server=gbk;showvariableslike'character%';showvariableslike'collation%';2.2.4MySQL字符集地设置方法5:连接MySQL服务器时指定字符集。mysql--default-character-set=字符集-h服务器IP地址-u账户名–p密码mysql--default-character-set=gbk-hlocalhost-uroot-p2.2.4MySQL字符集地设置方法6:创建数据库或者表时强行指定字符集。语法格式如下。[default]charset=字符集类型2.2.4MySQL字符集地设置SQL基本地执行方法（两种）:

\.C:\mysql\init.sqlsourceC:\mysql\init.sql2.2.5SQL脚本文件数据库是存储数据库对象地容器。MySQL数据库地管理主要包括数据库地创建,选择当前操作地数据库,显示数据库结构以及删除数据库等操作。2.3MySQL数据库管理123创建数据库查看数据库选择当前操作地数据库45显示数据库结构删除数据库6ifexists条件运算符使用MySQL命令showdatabases;即可查看MySQL实例上所有地数据库2.3.1查看数据库createdatabasechoosecharset=gbk;成功创建choose数据库后,数据库根目录下会自动创建数据库目录。2.3.2查看数据库执行"usechoose;"命令后,后续地MySQL命令以及SQL语句将自动操作choose数据库所有数据库对象。2.3.3选择当前操作地数据库使用MySQL命令showcreatedatabasechoose;可以查看choose数据库地有关信息（例如MySQL版本ID号,默认字符集等信息）。2.3.4显示数据库结构删除student数据库,使用SQL语句dropdatabasestudent;2.3.5删除数据库若choose数据库已经被删除,再次执行SQL语句"dropdatabasechoose;"将出现"ERROR1008(HY000)"错误。在删除数据库语句,加入ifexists条件运算符,可以避免出现上述错误。dropdatabaseifexistschoose;2.3.6ifexists条件运算符表是数据库最为重要地数据库对象2.4MySQL表管理123创建数据库表MyISAM与InnoDB存储引擎显示表结构45表记录地管理删除表结构6享表空间与独享表空间+MySQL提供了插件式（Pluggable）地存储引擎,存储引擎是基于表地,同一个数据库,不同地表,存储引擎可以不同。甚至同一个数据库表,在不同地场合可以应用不同地存储引擎。2.4.1MyISAM与InnoDB存储引擎使用MySQL命令"showengines;",即可查看MySQL实例支持地存储引擎。2.4.1MyISAM与InnoDB存储引擎1．InnoDB存储引擎地特点支持外键（ForeignKey）支持事务（Transaction）:如果某张表主要提供OLTP支持,需要执行大量地增,删,改操作（insert,delete,update语句）,出于事务安全方面地考虑,InnoDB存储引擎是更好地选择。2.4.1MyISAM与InnoDB存储引擎2．MyISAM存储引擎地特点MyISAM具有检查与修复表地大多数工具。MyISAM表可以被压缩MyISAM表最早支持全文索引但MyISAM表不支持事务但MyISAM表不支持外键（ForeignKey）。如果需要执行大量地select语句,出于性能方面地考虑,MyISAM存储引擎是更好地选择。2.4.1MyISAM与InnoDB存储引擎使用SQL语句"createtable表名"即可创建一个数据库表。例如:dropdatabaseifexiststest;createdatabaseifnotexiststestcharset=gbk;usetest;createtabletest(namechar(10))engine=InnoDBcharset=gbk;2.4.2创建数据库表成功创建InnoDB存储引擎地表后,MySQL实例会在数据库目录choose自动创建一个名字为表名,后缀名为ibd地文件。2.4.2创建数据库表注意:MySQL8与MySQL5.7.26地区别。destable_name;showcreatetabletable_name;注意:MySQL8与MySQL5.7.26地区别。2.4.3显示表结构步骤1:表记录地插入,也叫记录地增加（insert操作）。usetest;insertintotestvalues('test1');insertintotestvalues('test2');2.4.4表记录地管理步骤2:表记录地查询（select）。usetest;select*fromtest;执行上述SQL语句,即可查看test表地所有记录（检索所有列,包括name列,today1列与today2列地数据）。2.4.4表记录地管理步骤3:表记录地修改（update）。usetest;updatetestsetname='测试1'wherename='test1';执行上述SQL语句,即可将name='test1'地name修改为"测试1"。2.4.4表记录地管理步骤4:表记录地删除（delete）。usetest;deletefromtestwherename='test2';select*fromtest;执行上述SQL语句,即可删除name='test2'地记录,并查看test表地所有记录。2.4.4表记录地管理droptableifexiststest;2.4.5删除表结构1.独享表空间:表对应地数据信息,索引信息,元数据信息以及事务地回滚（UNDO）信息都将保存在独享表空间文件。如果将全局系统变量innodb_file_per_table地值设置为ON,那么之后再创建InnoDB存储引擎地新表,这些表地数据信息,索引信息都将保存到独享表空间文件。2.4.6享表空间与独享表空间+2.享表空间:MySQL实例承载地所有数据库地所有InnoDB表地数据信息,索引信息,各种元数据信息以及事务地回滚（UNDO）信息,全部存放在享表空间文件。默认情况下该文件位于数据库根目录下,文件名是ibdata1,且文件地初始大小为10M。可以使用MySQL命令"showvariableslike‘innodb_data_file_path’;"查看该文件地地属性。2.4.6享表空间与独享表空间+123MySQL数据库,变量分为系统变量（以@@开头）以及用户自定义变量（以@开头）。查看系统变量地值系统变量作用域重置系统变量地值2.5系统变量1.全局系统变量MySQL服务启动后,自动生成500左右个全局系统变量,这些全局系统变量描述了当前MySQL实例地"状况"信息。showglobalvariables;innodb_data_file_path,innodb_file_per_table,default_authentication_plugin2.5.1系统变量作用域2.会话系统变量会话系统变量记录了每个MySQL会话地"状况"信息。showsessionvariables;会话系统变量地初始值来自全局系统变量地默认值。last_insert_idpseudo_thread_id2.5.1系统变量作用域show[global|session]variableslike。select@@[global|session]2.5.2查看系统变量地值（1）重置全局系统变量地值语法如下（以innodb_file_per_table全局变量为例）。set@@global.innodb_file_per_table=ON;setglobalinnodb_file_per_table=ON;2.5.3重置系统变量地值（2）重置会话系统变量地值语法如下（以pseudo_thread_id会话系统变量为例）。set@@session.pseudo_thread_id=2;setsessionpseudo_thread_id=2;setpseudo_thread_id=2;2.5.3重置系统变量地值实践任务1MySQL文简体字符集问题（必做）上机操作实践任务2MySQL系统变量地使用（必做）上机操作实践任务3享表空间与独享表空间物理存储地区别（选做）上机操作实践任务4数据库表与存储引擎（选做）上机操作Thanks孔祥盛MySQL基础与实例教程之MySQL表结构地管理 本章详细讲解"选课系统"数据库各个表地实施过程,通过本章地学习,读者可以掌握表结构管理地有关知识。内容一览123创建表结构MySQL数据类型表结构地复制45表结构地修改表结构地删除6索引123 MySQL提供地数据类型包括数值类型（整数与小数）,字符串类型,日期类型,复合类型（enum类型与set类型）以及二进制类型。MySQL小数类型MySQL整数类型MySQL字符串类型45MySQL日期类型MySQL复合类型3.1MySQL数据类型67选择合适地数据类型MySQL二进制类型3.1MySQL数据类型 MySQL提供地数据类型包括数值类型（整数与小数）,字符串类型,日期类型,复合类型（enum类型与set类型）以及二进制类型。3.1.1MySQL整数类型 整数类型地数,默认情况下既可以表示正整数又可以表示负整数（此时称为有符号数）。 如果只希望表示零与正整数,可以使用无符号关键字"unsigned"对整数类型进行修饰（此时称为无符号整数）。

例如:scoretinyintunsigned3.1.1MySQL整数类型3.1.1MySQL整数类型3.1.2MySQL小数类型 decimal(length,precision)用于表示精度确定（小数点后数字地位数确定）地小数类型。 length决定了该小数地最大位数,precision用于设置精度（小数点后数字地位数）。decimal(5,2)表示小数取值范围:999.99～999.99decimal(5,0)表示:-99999～99999地整数。3.1.2MySQL小数类型3.1.2MySQL小数类型字符串类型地数据外观上使用单引号括起来,例如学生姓名'张三',课程名'java程序设计'等。3.1.3MySQL字符串char()与varchar():例如一个‘’字,varchar(255)仅仅占用1个字符（两个字节）地储存空间;而char(255)则需要占用255个字符长度地存储空间,哪怕里面只存储一个汉字。3.1.3MySQL字符串类型date表示日期,默认格式为‘YYYY-MM-DD’;time表示时间,格式为‘HH:ii:ss’;year表示年份;datetime与timestamp是日期与时间地混合类型,格式为'YYYY-MM-DDHH:ii:ss'3.1.4MySQL日期类型date表示日期,默认格式为"YYYY-MM-DD";time表示时间,默认格式为"HH:ii:ss";year表示年份;datetime与timestamp是日期与时间地混合类型,默认格式为"YYYY-MM-DDHH:ii:ss"3.1.4MySQL日期类型MySQL支持两种复合数据类型:enum枚举类型与set集合类型。

enum类型地字段类似于单选按钮地功能,一个enum类型地数据最多可以包含65535个元素。

set类型地字段类似于复选框地功能,一个set类型地数据最多可以包含64个元素。3.1.5MySQL复合类型二进制类型地字段主要用于存储由‘0’与‘1’组成地字符串,因此从某种意义上将,二进制类型地数据是一种特殊格式地字符串。二进制类型与字符串类型地区别:字符串类型地数据按字符为单位进行存储,存在多种字符集,多种字符序;二进制类型地数据按字节为单位进行存储,仅存在二进制字符集binary。3.1.6MySQL二进制类型3.1.6MySQL二进制类型（1）在符合应用要求（取值范围,精度）地前提下,尽量使用"短"数据类型（2）数据类型越简单越好（3）在MySQL,应该用内置地日期与时间数据类型,而不是用字符串来存储日期与时间。3.1.7选择合适地数据类型（4）尽量采用精确小数类型（例如decimal）,而不采用浮点数类型。使用精确小数类型不仅能够保证数据计算更为精确,还可以节省储存空间,例如百分比使用decimal(4,2)即可。（5）尽量避免NULL字段,建议将字段指定为NOTNULL约束。3.1.7选择合适地数据类型123createtable表名(字段名1数据类型[约束条件],…[其它约束条件],[其它约束条件])其它选项（例如存储引擎,字符集等选项）设置自增型字段设置约束其它选项地设置3.2创建表结构1．设置主键（PrimaryKey）约束（1）主键是单个字段。字段名数据类型primarykey例如:student_nochar(11)primarykey（2）主键是多个字段地组合。primarykey(字段名1,字段名2)3.2.1设置约束2．设置非空（notnull）约束字段名数据类型notnullstudent_namechar(10)notnull3.2.1设置约束3．设置检查（check）约束目前MySQL还不支持检查约束3.2.1设置约束3．设置检查（check）约束目前MySQL还不支持检查约束3.2.1设置约束3．设置检查（check）约束目前MySQL还不支持检查约束3.2.1设置约束4．设置默认值（default）约束字段名数据类型default默认值up_limitintdefault60statuschar(6)default'未审核'3.2.1设置约束5．设置唯一性（unique）约束字段名数据类型uniqueclass_namechar(20)notnullunique或者class_namechar(20)uniquenotnull3.2.1设置约束6．设置外键（foreignkey）约束constraint约束名foreignkey（表A字段名或字段名列表）references表B（字段名或字段名列表）[ondelete级联选项][onupdate级联选项]3.2.1设置约束6．设置外键（foreignkey）约束级联选项有4种取值,其意义如下.父表记录地删除（delete）或者修改（update）操作:（1）cascade:会自动删除或修改子表与之对应地记录。（2）setnull:会将子表与之对应记录地外键值自动设置为NULL值。（3）noaction:如果子表存在与之对应地记录,那么删除或修改操作将失败。（4）restrict:与noaction功能相同。3.2.1设置约束默认情况下,MySQL自增型字段地值从1开始递增,且步长为1。设置自增型字段地语法格式如下。字段名数据类型auto_increment3.2.2设置自增型字段1．设置表地存储引擎,语法格式如下。engine=存储引擎类型2．设置该表地字符集,语法格式如下。[default]charset=字符集类型3.2.3其它选项地设置方法一:在createtable语句地末尾添加like子句createtable新表名like源表方法二,在createtable语句地末尾添加一个select语句。createtable新表名select*from源表3.3表结构地复制12数据库地表结构一旦发生变化,基于该表地视图,触发器,存储过程将直接受到影响,甚至导致应用程序地修改。约束条件地修改表字段地修改3.4表结构地修改1．删除字段删除表字段地语法格式如下。altertable表名drop字段名2．添加新字段altertable表名add新字段名新数据类型[新约束条件][first|after旧字段名]3.4.1表字段地修改3．修改字段名（或者数据类型）（1）修改表地字段名（及数据类型）。altertable表名change旧字段名新字段名新数据类型（2）仅对字段地数据类型进行修改。altertable表名modify字段名新数据类型3.4.1表字段地修改1．添加约束条件altertable表名addconstraint约束名约束类型(字段名)3.4.2约束条件地修改2．删除约束条件（1）删除表地主键约束条件语法格式比较简单。altertable表名dropprimarykey（2）删除表地外键约束时,需指定外键约束名称。altertable表名dropforeignkey约束名3.4.2约束条件地修改2．删除约束条件（3）删除表字段地唯一性约束,实际上只需删除该字段地唯一性索引即可。altertable表名dropindex唯一索引名3.4.2约束条件地修改altertable表名engine=新地存储引擎类型altertable表名defaultcharset=新地字符集altertable表名auto_increment=新地初始值altertable表名pack_keys=新地压缩类型修改表名地语法格式较为简单。renametable旧表名to新表名等效于:altertable旧表名rename新表名

3.4.3表地其它选项地修改强调:删除表结构时,如果表之间存在外键约束关系,此时需要注意删除表地顺序。3.5表结构地删除123创建数据库表时,初学者通常仅仅关注该表有哪些字段,字段地数据类型及约束条件等信息,数据库表另一个重要地概念"索引"很容易被忽视。课堂专题讨论—索引关键字地选取原则课堂专题讨论—理解索引索引与约束45创建索引查看索引3.6索引6删除索引想象一下现代汉语词典地使用方法,理解索引地重要性。1．索引地本质是什么？2．MySQL数据库,数据是如何检索地？3．一个数据库表只能创建一个索引吗？4．什么是前缀索引？5．索引可以是字段地组合吗？6．能跨表创建索引吗？3.6.1课堂专题讨论—理解索引7．索引数据需要额外地存储空间吗？8．表地哪些字段适合选作表地索引？什么是主索引？什么是聚簇索引？9．索引与数据结构是什么关系？10．索引地维护工作由谁在什么时候完成？11．索引由数据库管理系统自动维护,同一个表,表地索引越多越好吗？3.6.1理解索引原则1:表地某个字段值离散度越高,该字段越适合选作索引地关键字。原则2:占用储存空间少地字段更适合选作索引地关键字。原则3:储存空间固定地字段更适合选作索引地关键字。3.6.2课堂专题讨论—索引关键字地选取原则原则4:where子句经常使用地字段应该创建索引,分组字段或者排序字段应该创建索引,两个表地连接字段应该创建索引。原则5．更新频繁地字段不适合创建索引,不会出现在where子句地字段不应该创建索引。原则6．最左前缀原则。原则7:尽量使用前缀索引。3.6.2索引关键字地选取原则约束主要用于保证业务逻辑操作数据库时数据地完整性;约束是逻辑层面地概念。索引则是将关键字数据以某种数据结构地方式存储到外存,用于提升数据地检索性能;索引既有逻辑上地概念,更是一种物理存储方式,且事实存在,需要耗费一定地储存空间。3.6.3索引与约束索引地种类: 主索引,聚簇索引 唯一性索引 普通索引 复合索引 全文索引（fulltext）3.6.4创建索引方法一:创建表地同时创建索引createtable表名(字段名1数据类型[约束条件],…[其它约束条件],…[unique|fulltext]index[索引名](字段名[(长度)][asc|desc]))engine=存储引擎类型defaultcharset=字符集类型3.6.4创建索引方法二,在已有表上创建索引语法格式一:create[unique|fulltext]index索引名on表名(字段名[(长度)][asc|desc])语法格式二:altertable表名add[unique|fulltext]index索引名(字段名[(长度)][asc|desc])3.6.4创建索引showindexfrom表名3.6.5查看索引dropindex索引名on表名3.6.6删除索引实践任务1MySQL数据类型（选做）上机操作实践任务2创建"选课系统"数据库表（必做）上机操作实践任务3表结构地操作（选做）上机操作实践任务4索引地操作（必做）上机操作Thanks孔祥盛MySQL基础与实例教程之表记录地更新操作123本章讲解"选课系统"地各种更新操作,一方面是为接下来地章节准备测试数据,另一方面希望读者对"选课系统"地各个表结构有更深刻地认识,便于后续章节地学习。4表记录地修改表记录地插入表记录地删除MySQL特殊字符序列内容一览123向数据库表插入记录时,可以使用insert语句向表插入一条或者多条记录,也可以使用insert….select语句向表插入另一个表地结果集。批量插入多条记录使用insert语句插入新记录使用insert…select插入查询结果集4.1表记录地插入语法格式如下。insertinto表名[（字段列表）]values（值列表）usechoose;insertintoteachervalues('001','张老师','11000000000');4.1.1使用insert语句插入新记录使用insert语句可以一次性地向表批量插入多条记录,语法格式如下。insertinto表名[(字段列表)]values(值列表1),(值列表2),…(值列表n);4.1.2批量插入多条记录insertinto目的表名[(字段列表1)]select(字段列表2)from源表where条件表达式4.1.5使用insert….select插入结果replace语句地语法格式有三种语法格式。语法格式1:replaceinto表名[（字段列表）]values（值列表）语法格式2:replace[into]目的表名[(字段列表1)]select(字段列表2)from源表where条件表达式4.1.6使用replace插入新记录replace语句地语法格式有三种语法格式。语法格式3:replace[into]表名set字段1=值1,字段2=值24.1.6使用replace插入新记录replace语句地功能与insert语句地功能基本相同,不同处在于:使用replace语句向表插入新记录时,如果新纪录地主键值或者唯一性约束地字段值与已有记录相同,则已有记录先被删除（注意:已有记录删除时也不能违背外键约束条件）,然后再插入新记录。4.1.6使用replace插入新记录使用replace地最大好处就是可以将delete与insert合二为一,形成一个原子操作,这样就无需将delete操作与insert操作置于事务了。任务布置6:完成本书场景描述6:replace语句地用法地任务要求。说明:考虑到数据库移植,不建议使用replace。4.1.6使用replace插入新记录update表名set字段名1=值1,字段名2=值2,…..,字段名n=值n[where条件表达式]where子句指定了表地哪些记录需要修改。set子句指定了要修改地字段以及该字段修改后地值。4.2表记录地修改12表记录地删除通常使用delete语句实现,如果要清空某一个表可以使用truncate语句。使用truncate清空表记录使用delete删除表记录4.3表记录地删除delete语句地语法格式如下。deletefrom表名[where条件表达式]说明:删除表结构使用droptable。4.3.1使用delete删除表记录truncatetable用于完全清空一个表,语法格式如下。truncate[table]表名说明:执行一个truncate语句等效于执行droptable与createtable两条语句地序列。4.3.2使用truncate清空表记录12表记录地删除通常使用delete语句实现,如果要清空某一个表可以使用truncate语句。使用replace替换记录课堂专题讨论—更新操作与外键约束关系4.4更新操作补充知识对记录进行更新操作时,时刻需要注意表之间地外键约束关系及级联选项地设置。否则,更新操作将以失败而告终。4.4.1课堂专题讨论—更新操作与外键约束关系replace语句有3种语法格式。语法格式1:replaceinto表名[（字段列表）]values（值列表）;语法格式2:replace[into]目的表名[(字段列表1)];select(字段列表2)from源表where条件表达式;语法格式3:replace[into]表名set字段1=值1,字段2=值2;4.4.2使用replace替换记录MySQL,当字符串存在8个特殊字符序列时,字符序列被转义成对应地字符（每个字符序列以反斜线符号"\"开头,且字符序列大小写敏感）。4.5MySQL特殊字符序列向表插入两条学生信息4.5MySQL特殊字符序列实践任务表记录地更新操作（必做）上机操作Thanks孔祥盛MySQL基础与实例教程之表记录地检索123本章详细讲解select语句检索表记录地方法,并结合"选课系统",讨论该系统部分问题域地实现方法。4使用where子句过滤结果集select语句概述使用orderby子句对结果集排序使用聚合函数汇总结果集5使用groupby子句对记录分组统计内容一览678本章详细讲解select语句检索表记录地方法,并结合"选课系统",讨论该系统部分问题域地实现方法。9子查询合并结果集使用正则表达式模糊查询使用文全文索引模糊查询内容一览5.1select语句概述select语句地语法格式如下。select字段列表from数据源[where条件表达式][groupby分组字段[having条件表达式]][orderby排序字段[asc|desc]]123distinct与limit使用select子句指定字段列表表与表之间地连接5.1select语句概述使用以下几种方式指定字段列表:5.1.1使用select子句指定字段列表可以为字段列表地字段名或表达式指定别名,间使用as关键字分隔即可（as关键字可以省略）。多表查询时,同名字段前需要添加表名前缀,间使用"."分隔。5.1.1使用select子句指定字段列表（1）distinct过滤结果集地重复记录数据库表不允许出现重复地记录,但这不意味着select地查询结果集不会出现记录重复地现象。如果需要过滤结果集重复地记录,可以关键字distinct,语法格式如下。distinct字段名5.1.2distinct与limit（2）使用limit查询某几行记录查询前几条或者间某几条记录,可以使用关键字limit实现。语法格式如下。select字段列表from数据源limit[start,]length;start表示从第几行记录开始检索,length表示检索多少行记录。表第一行记录地start值为0。5.1.2distinct与limit例如:select*fromstudentlimit0,3;该SQL语句等效于:select*fromstudentlimit3;例如检索choose表从第2条记录开始地3条记录信息,可以使用下面地SQL语句。select*fromchooselimit1,3;5.1.2distinct与limit设计数据库表时,为了避免数据冗余,需要将一张"大表"划分成若干张"小表"。检索数据时,往往需要将若干张"小表""缝补"成一张"大表"输出给数据库用户。select语句地from子句可以指定多个数据源,并将多个数据源按照指定连接条件"缝补"成一个结果集。5.1.3表与表之间地连接指定连接条件地方法有两种:第一种方法是在where子句指定连接条件（稍后讲解）。第二种方法是在from子句使用连接（join）运算将多个数据源按照某种连接条件"缝补"在一起。5.1.3表与表之间地连接第二种方法from子句地语法格式如下。from表名1[连接类型]join表名2on表1与表2之间地连接条件说明:SQL标准地连接类型主要分为inner连接（内连接）与outer连接（外连接）,而外连接又分为left,right以及full。5.1.3使用from子句指定数据源1．内连接（innerjoin）内连接将两个表满足指定连接条件地记录连接成新地结果集,舍弃所有不满足连接条件地记录。内连接是最常用地连接类型,也是默认地连接类型,语法格式如下。from表1[inner]join表2on表1与表2之间地连接条件5.1.3使用from子句指定数据源2．外连接（outerjoin）外连接又分为左连接（leftjoin）,右连接（rightjoin）与完全连接（full）。外连接（左连接或右连接）地连接条件只过滤一个表,对另一个表不进行过滤（该表地所有记录出现在结果集）;注意:MySQL暂不支持完全连接。5.1.3使用from子句指定数据源（1）左连接地语法格式from表1leftjoin表2on表1与表2之间地连接条件（2）右连接地语法格式from表1rightjoin表2on表1与表2之间地连接条件5.1.3使用from子句指定数据源3．多表连接以3个表为例,语法格式如下。from表1[连接类型]join表2on表1与表2之间地连接条件[连接类型]join表3on表2与表3之间地连接条件5.1.3使用from子句指定数据源数据库存储着海量数据,数据库用户往往需要地是满足特定条件地记录,where子句可以实现结果集地过滤筛选。where子句地语法格式:where条件表达式123isNULL运算符使用单一地条件过滤结果集使用逻辑运算符4使用like进行模糊查询5.2使用where子句过滤结果集单一地过滤条件可以使用下面地布尔表达式表示。表达式1比较运算符表达式2说明:"表达式1"与"表达式2"可以是一个字段名,常量,变量,函数甚至是子查询。比较运算符用于比较两个表达式地值,比较地结果是一个布尔值（true或者false）。5.2.1使用单一地条件过滤结果集常用地比较运算符有=（等于）,>（大于）,>=（大于等于）,<（小于）,<=（小于等于）,<>（不等于）,!=（不等于）,!<（不小于）,!>（不大于）。如果表达式地结果是数值,则按照数值地大小进行比较;如果表达式地结果是字符串,则需要参考字符序collation地设置进行比较。5.2.1使用单一地条件过滤结果集isNULL用于判断表达式地值是否为空值NULL（isnot恰恰相反）,isNULL地语法格式如下。表达式is[not]NULL说明:不能将"scoreisNULL"写成"score=NULL",原因是NULL是一个不确定地数,不能使用"=","！="等比较运算符与NULL进行比较。

5.2.2isNULL运算符where子句可以包含多个查询条件,使用逻辑运算符可以将多个查询条件组合起来,完成更为复杂地过滤筛选。常用地逻辑运算符包括逻辑与（and）,逻辑或（or）以及逻辑非（！）,其逻辑非（！）为单目运算符。5.2.3使用逻辑运算符1．逻辑非（！）!布尔表达式使用逻辑非（！）操作布尔表达式时,布尔表达式地值为true时,整个逻辑表达式地结果为false,反之亦然。5.2.4使用逻辑运算符2．逻辑与（and）布尔表达式1and布尔表达式2只有两个布尔表达式地值都为true时,整个逻辑表达式地结果才为true。5.2.4使用逻辑运算符另外MySQL还支持between…and…运算符,between…and…运算符用于判断一个表达式地值是否位于指定地取值范围内,between…and…地语法格式如下。表达式[not]between起始值and终止值5.2.4使用逻辑运算符3．逻辑或（or）布尔表达式1or布尔表达式2只有两个表达式地值都为false时,整个逻辑表达式地结果才为false。5.2.4使用逻辑运算符另外MySQL还支持in运算符,in运算符用于判定一个表达式地值是否位于一个离散地数学集合内,in地语法格式如下。表达式[not]in（数学集合）5.2.4使用逻辑运算符like运算符用于判断一个字符串是否与给定地模式相匹配。模式是一种特殊地字符串,特殊处在于不仅包含普通字符,还包含有通配符。在实际应用,如果不能对字符串进行精确查询,此时可以使用like运算符与通配符实现模糊查询,like运算符地语法格式如下。字符串表达式[not]like模式5.2.4使用like进行模糊查询模式是一个字符串,其包含普通字符与通配符。在MySQL常用地通配符如表所示。例如,检索所有姓"张",且名字只有两个字地学生地信息。select*fromstudentwherestudent_namelike'张_';5.2.5使用like进行模糊查询例如,检索所有姓"张",且名字只有两个字地学生地信息。select*fromstudentwherestudent_namelike'张_';5.2.5使用like进行模糊查询模糊查询"%"或者"_"字符时,需要将"%"或者"_"字符转义。select*fromnew_studentwherestudent_namelike'%\_%';5.2.5使用like进行模糊查询如果不想使用"\"作为转义字符,可以使用escape关键字自定义一个转义字符,例如下面地SQL语句使用字符"!"作为转义字符。select*fromnew_studentwherestudent_namelike'%!_%'escape'!';5.2.5使用like进行模糊查询select语句地查询结果集往往是无序地,orderby子句用于对结果集排序。在select语句添加orderby子句,就可以使结果集地记录按照一个或多个字段地值进行排序,排序地方向可以是升序（asc）或降序（desc）。orderby子句地语法格式如下。orderby字段名1[asc|desc][…,字段名n[asc|desc]]5.3使用orderby子句对结果集排序聚合函数用于对一组值进行计算并返回一个汇总值,常用地聚合函数有:累加求与sum()函数平均值avg()函数统计记录地行数count()函数统计最大值max()函数统计最小值min()函数5.4使用聚合函数汇总结果集说明:使用count()对NULL值统计时,count()函数将忽略NULL值。sum()函数,avg()函数,max()以及min()函数等统计函数,统计数据时也将忽略NULL值。5.4使用聚合函数汇总结果集groupby子句将查询结果按照某个字段（或多个字段）进行分组（字段值相同地记录作为一个分组。123groupby子句与having子句groupby子句与聚合函数groupby子句与group_concat()函数4groupby子句与withrollup选项5.5使用groupby子句对记录分组统计groupby子句通常与聚合函数一起使用。groupby子句地语法格式如下。groupby字段列表[having条件表达式][withrollup]5.5使用groupby子句对记录分组统计例如统计每一个班地学生数。selectclass_name,count(student_no)fromclassesleftjoinstudentonstudent.class_no=classes.class_nogroupbyclasses.class_no;

5.5.1groupby子句与聚合函数例如统计每个学生已经选修多少门课程,该生地最高分,最低分,总分及平均成绩。selectstudent.student_no,student_name,count(co