数据基础及应用- 7

目录第2章MySQL数据库系统基础第1章数据库系统概述第3章MySQL数据库操作第5章使用SQL语句查询数据第4章MySQL数据表第6章视图和索引的使用第8章数据库的安全性管理第7章存储过程和触发器第9章数据库的备份与恢复目标导航重点和难点1．SQL语言概述；2．MySQL数据库简介；3．MySQL数据库的存储引擎；4．MySQL字符集；5．MySQL数据库的创建与维护。知识目标1.掌握SQL语言的特点和分类；2.了解MySQL数据库文件；3.了解MySQL数据库存储引擎及其优缺点；4.了解MySQL字符集；5．掌握数据库的创建、修改和删除的方法。技能目标1.具备使用NavicateforMySQL图形管理工具创建、修改、删除数据库的能力；2.具备使用SQL语句创建、修改和删除数据库的能力。1.SQL语言的特点3.1MySQL数据库概述（1）SQL语言是一体化语言（2）SQL语言是非过程化语言（3）SQL既是内含式语言，又是嵌入式语言（4）SQL简单易用3.1.1SQL概述2.SQL的功能分类（1）数据定义语言（DateDefinitionLanguage，DDL）（2）数据操纵语言（DataManipulationLanguage，DML）（3）数据控制语言（DataControlLanguage，DCL）3.1MySQL数据库概述3.1.1SQL概述1.MySQL数据库文件数据库管理的核心任务包括创建、操作和维护数据库。在MySQL中每个数据库都对应存放在一个与数据库同名的文件夹中。MySQL数据库文件包括MySQL所建数据库文件和MySQL所用存储引擎创建的数据库文件。MySQL数据库文件有“.frm”“.myd”“.myi”和“.log”4种。（1）MySQL服务创建并管理的数据库文件MySQL创建并管理的数据库文件为.frm文件，用于存储数据表的框架结构，文件名与表名相同，每个表对应一个同名frm文件，与操作系统和存储引擎无关，即不管MySQL运行在何种操作系统上，使用何种存储引擎，都有这个文件。3.1MySQL数据库概述3.1.2MySQL数据库文件和系统数据库（2）根据MySQL所使用的存储引擎的不同创建的数据库文件MySQL常用的两个存储引擎是MyISAM和InnoDB，MySQL根据使用存储引擎的不同会创建各自不同的数据库文件。其中MyISAM表文件包括3种：①.myd：即MYData，表数据文件。②.myi：即MYIndex，表数据文件中的索引文件。③.log：日志文件MySQL数据库文件默认存放的位置是C:\ProgramFiles\MySQL\MySQLServer5.7\data文件夹，用户也可以通过配置向导或手工配置my.ini参数文件更改数据库的默认存放位置。3.1MySQL数据库概述3.1.2MySQL数据库文件和系统数据库2.MySQL系统数据库MySQL安装完成后，系统将自动在其data目录下创建information_schema、mysql、performance_schema和sys四个系统数据库。（1）查看系统数据库在DOS命令提示符下登录到MySQL服务器，执行命令：SHOWDATABASE命令，即可查看当前所有存在的数据库，如图3-1所示。如果用NavicateforMySQL管理工具显示数据，只需双击窗口左侧的“MySQL57”服务器名称即可在其下方展开当前所有存在的数据库，如图3-2所示。3.1MySQL数据库概述3.1.2MySQL数据库文件和系统数据库知识点2MySQL数据库概述图3-1命令行方式显示数据库

图3-2在NavicateforMysql管理工具中显示数据库（2）系统数据库的作用从上述显示结果来看，MySQL安装后自动创建了4个数据库，数据库作用如表3-1所示。知识点2MySQL数据库概述数据库名称作用information_schema用于存储数据库元数据（关于数据的数据），例如数据库名、表名、列的数据类型与访问权限等。mysqlMySQL的核心数据库，类似于SQLServer中的Master表，主要负责存储数据库的用户、权限设置、关键字以及mysql数据库需要使用的控制和管理信息。mysql数据库不可以删除。performance_schemaMySQL5.5版本新增加的一个性能优化引擎，主要用环境收集数据库服务器性能参数。sysmysql5.7新增的sys系统数据库。通过视图的形式把information_schema和performance_schema结合起来，查询出更加令人容易理解的数据，存储过程可以执行一些性能方面的配置，也可以得到一些性能诊断报告内容。存储引擎是MySQL的一个特性，可简单理解为表类型。每一个表都有一个存储引擎，在使用CREATETABLE语句创建表时可以通过关键字ENGINE指定存储引擎。在介绍MySQL数据库存储引擎之前先了解一下存储引擎的概念。1．存储引擎概述存储引擎实际上就是如何存储数据、如何为存储的数据建立索引和如何更新、查询数据。因为在关系数据库数据是以表的形式存储的，所以存储引擎也可以理解为表类型。MySQL数据库提供了多种存储引擎，用户可以根据不同的需求为数据表选择不同的存储引擎，也可以根据自己的需要编写自己的存储引擎。数据库的存储引擎决定了表在计算机中的存储方式。与其他数据库管理系统不同,MySQL的核心是插件式的存储引擎,存储引擎是基于表的。同一个数据库、不同的表,存储引擎可以不同,也可以相同。在Oracle和SQLServer等数据库系统中只有一种存储引擎,所有数据存储管理机制都是一样的,但是MySQL数据库提供了多种存储引擎。MySQL中的每一种存储引擎都有各自的特点。为了提升性能,对不同业务类型的表,数据库开发人员应该选用适合不同表的存储引擎。MySQL常用的存储引擎有InnoDB和MyISAM。3.1MySQL数据库概述3.1.3MySQL数据库的存储引擎2.MySQL常用的存储引擎MySQL9.0支持的存储引擎主要有InnoDB、MyISAM、MEMORY、MRG-MYISAM、ARCHIVE、FEDERATED、CSV、PERFORMANCE-SCHEMA和BLACKHOLE等,可以使用SHOWENGINES语句查看系统的存储引擎。结果如图3-3所示。图3-3查看当前系统支持的存储引擎类型从输出的结果中可以看到当前系统支持多个存储引擎。其中Support列的值表示某种存储引擎能否使用,YES表示可用,NO表示不能使用,DEFAULT表示该引擎为当前默认的存储引擎。3.1MySQL数据库概述3.1.3MySQL数据库的存储引擎下面介绍MySQL最常用的InnoDB、MyISAM、Memory三种存储引擎。InnoDB存储引擎InnoDB是事务型数据库的首选引擎，支持事务安全表（ACID），其它存储引擎都是非事务安全表，支持行锁定和外键，MySQL5.5以后默认使用InnoDB存储引擎。MyISAM存储引擎MyISAM存储引擎是MySQL中常见的存储引擎，曾是MySQL的默认存储引擎，不支持事务、外键约束，但访问速度快，对事务完整性不要求，适合环境以SELECT/INSERT为主的表，如果执行大量的查询操作时，MyISAM是更好的选择。MEMORY存储引擎MEMORY存储引擎是MySQL中的一类特殊的存储引擎。Memory存储引擎使用存在于内存中的内容来创建表。每个表实际对应一个磁盘文件，格式是.frm。这种类型的表访问非常快，因为它的数据是放在内存中的，并且默认使用HASH索引，但是一旦服务关闭，表中的数据就会丢失掉。3.1MySQL数据库概述3.1.3MySQL数据库的存储引擎3.存储引擎的选择每种存储引擎都有自己的优缺点，所以选择存储引擎时首先需要考虑每一个存储引擎都提供了什么功能。表3-2对三种存储引擎功能进行了对比。功能InnoDBMyISAMMEMORY存储限制64TB256TBRAM支持事务支持无无空间使用高低低内存使用高低高支持数据缓存支持无无插入数据速度慢快快支持外键支持无无3.1MySQL数据库概述3.1.3MySQL数据库的存储引擎1.字符集概述字符集是一套文字符号及其编码、比较规则的集合。20世纪60年代初期，ANSI发布了第一个计算机字符集—ASCII，后来进一步变成了国际标准ISO-646。虽然这个美式的字符集很简单，包括的符号秀少，但直至今天依然是计算机世界里奠基性的标准。自从ASCII后，为了处理不同的语言和文字，很多组织和机构先后创建了几百种字符集，如ISO-8859系列、GBK等，这么多的字符集，收录的字符和字符的编码规则各不相同，全计算机软件开发和移植带来了很大困难，所以同一字符集编码成了20世纪80年代计算机行业的迫切需要和普遍共识。3.1MySQL数据库概述3.1.4MySQL的字符集2.MySQL支持的字符集MySQL服务器可以支持多种字符集，在同一台服务器、同一个数据库甚至同一个表的不同字段都可以使用相同的字符集。可以在NavicateforMySQL管理工具的命令列界面执行“showcharacterset;”命令查看所有可以使用的字符集，如图3-4所示。MySQL字符集包括字符集和校对规则两个概念。其中字符集用来定义MySQL存储字符串的方式，校对规则定义比较字符串的方式。字符集和校对规则是一对多的关系，两个不同的字符集不能有相同的校对规则，每个字符集有一个默认校对规则，例如gbk默认校对规则是gbk_chinese_ci。MySQL支持30多种字符集的70多种校对规则。每个字符集至少对应一个校对规则。可以在NavicateforMySQL管理工具的命令列界面执行“showcollationlike'gbk%'”命令查看gbk字符集的校对规则，如图3-5所示。3.1MySQL数据库概述3.1.4MySQL的字符集图3-4查看所有可以使用的字符集图3-5查看gbk字符集的校对规则3.1MySQL数据库概述3.1.4MySQL的字符集3.MySQL字符集的选择MySQL目前支持的字符集各类繁多，选择时应该从以下几点考虑：（1）满足应用支持语言的要求，如果应用要处理的语言种类多，要在不同语言的国家发布，就应该选择Unicode字符集，就目前对MySQL来说，选择UTF-8字符集。（2）如果应用中涉及已有数据的导入，就要充分考虑数据库字符集对已有数据的兼容性。（3）如果数据库只需要支持一般中文，数据量很大，性能要求很高，那就应该选择双字节GBK。如果主要处理的英文字符，只有少量汉字，那么选择UTF-8比较好。（4）如果数据库需要做大量的字符运算，如比较、排序等，那么选择定长字符集可能更好，因为定长字符集的处理速度要比变长字符集的处理速度快。（5）考虑客户端所使用的字符集编码格式，如果所有客户端都支持相同的字符集，则应该优先选择字符集作为数据库字符集。3.1MySQL数据库概述3.1.4MySQL的字符集3.2.1使用NavicatforMySQL图形化管理工具创建数据库（1）启动NavicateforMySQL软件，打开“NavicateforMySQL”窗口，并确保与MySQL57服务器建立连接，结果如图3-6所示。（2）在连接成功的NavicateforMySQL窗口，用鼠标右击左侧连接窗格中的MySQL90服务器名称或任何数据库，在弹出的快捷菜单中选择【新建数据库】命令，如图3-7所示。图3-6连接成功的NavicateforMySQL窗口

图3-7在快捷菜单中选择“新建数据库”3.2创建数据库（3）在弹出的“新建数据库”对话框中输入数据库名“gradem”,也可以设置字符集和排序规则(校验规则),如果省略则表示采用默认服务器的字符集和校验规则,如图3-8所示。最后单击“确定”按钮完成新建数据库。小提示：在“新建数据库”对话框也可以单击“SQL预览”选项卡查看创建数据库的SQL语句格式，如图3-9所示。图3-8“新建数据库”对话框图3-9“SQL预览对话框3.2.2使用SQL语句创建数据库在MySQL中,创建数据库的SQL语句可使用CREATEDATABASE或CREATESCHEMA命令,语法格式如下:CREATE{DATABASE|SCHEMA}{IFNOTEXISTS}db_name[[DEFAULT]CHARACTERSETcharset_name][[DEFAULT]COLLATEcollation_name]语法说明:(1)IFNOTEXISTS:判断db_name是否存在,如果不存在,则创建数据库。(2)db_name:数据库名称,数据库名称必须符合操作系统文件夹命名规则,在MySQL中命名不区分大小写。(3)[DEFAULT]CHARACTERSETcharset_name:其中DEFAULT表示设置默认字符集,可省略;CHARACTERSET表示设置指定字符集;charset_name表示字符集名称。若省略该句子,则采用服务器默认的字符集。(4)[DEFAULT]COLLATEcollation_name:其中DEFAULT表示设置默认校对规则;COLLATE表示设置校对规则;collation_name表示校对规则名称。若省略此句子,则采用服务器的默认校对规则。3.2.2使用SQL语句创建数据库1.在命令提示符下执行SQL语句创建数据库（1）在Windows桌面,选择“开始”→“运行”,在“运行”对话框输入“cmd”命令,单击“确定”按钮进入命令提示符。登录到MySQL服务器,如图3-10所示。（2）输入创建数据库example的SQL语句,按Enter键执行,结果如图3-11所示。图3-10登录成功界面图3-11创建数据库成功界面2.在NavicateforMySQL图形管理工具执行SQL语句创建数据库（1）启动NavicatforMySQL平台,打开“NavicatforMySQL”窗口,并确保与MySQL90服务器建立连接。（2）在连接成功的“NavicatforMySQL”窗口,用鼠标右键单击左侧连接窗格中的MySQL90服务器名称或任何数据库,在弹出的快捷菜单中选择“新建查询”命令,界面右侧会显示“无标题-查询”选项卡,如图3-12所示。（3）在新建查询界面输入创建数据库example的SQL语句:“CREATEDATABASEexampleDEFAULTCHARACTERSETutf8;”,单击“美化SQL”按钮系统自动将关键词大写并分行显示。如图3-13所示。图3-12新建查询界面图3-13输入创建数据库的SQL语句界面（4）输入SQL语句后,单击“运行”按钮,系统自动创建数据库,并显示语句执行结果,如图3-14所示。图3-14创建数据库的SQL语句执行结果界面例3使用NavicateforMySQL图形管理工具打开gradem数据库（1）启动NavicatforMySQL平台,打开“NavicatforMySQL”窗口,并确保与MySQL90服务器建立连接。（2）在连接成功的NavicateforMySQL窗口,用鼠标双击要打开的数据库gradem,此时数据库名称前的图标变为浅绿色,表明数据库已经打开,同时在右侧的窗格中显示gradem数据库包含的表。如图3-15所示。图3-15gradem数据库打开后的结果界面3.3打开和查看学生信息管理数据库例4使用SQL语句的USE命令打开example数据库，并显示数据库的详细信息（1）启动NavicatforMySQL平台,打开“NavicatforMySQL”窗口,并确保与MySQL90服务器建立连接。（2）在左侧服务器名MySQL90上单击鼠标右键,在弹出的快捷菜单中选择“命令列界面”,然后在右侧的命令列界面中的“mysql>”提示符后输入SQL语句“USEexample;”,按Enter键执行,结果显示Databasechanged,表示数据库已经打开,如图3-16所示。图3-16使用USE命令打开example数据库的结果界面（3）在命令列界面“mysql>”提示符后输入命令:“SHOWCREATEDATABASEexample;”,按Enter键执行,下方会显示数据库的详细信息,如图3-17所示。图3-17查看example数据库的详细信息随着数据库数据量的增加,系统消耗的资源会越来越多,运行速度也会减慢。此时,可将系统中不再需要的数据库删除,以释放其占用的磁盘空间和系统资源。删除数据库将一并删除数据库中的所有表和数据,因此操作前务必慎重,建议在删除数据库前先对数据库进行备份。删除数据库的SQL语句格式为:DROPDATABASEdb_name;其中,db_name为要删除的数据库名称。使用DROPDATABASE命令删除数据库时不会出现确认提示,所以使用该方法删除数据库时必须格外