全国计算机等级考试二级MySQL总结

第一章数据库技术的的基本概念与方法1.1基本概念数据：描述事物的符号记录数据库中存储的基本对象数据的描述形式还不能完全表达其内容，需要经过解释数据库：长期储存在计算机内的、有组织的、可共享的数据集合数据库中的数据按一定的数据模型组织、描述和存储，具有较小的冗余度、较高的数据独立性和易扩展性数据库管理系统：数据定义功能、数据操纵功能、数据库的运行管理、数据库的建立和维护功能、提供方便有效存储数据库信息的接口和工具数据库系统：在计算机系统中引入数据库后的系统一般由数据库、数据库管理系统、应用开发工具、应用系统、数据库管理员和用户构成1.2数据库系统的特点数据结构化数据冗余度小数据共享性好数据独立性高：物理独立性、逻辑独立性数据由DBMS统一管理和控制1.3数据库系统的结构从数据库用户试图的视角来看：三级模式结构从数据库管理系统的角度来看：集中式结构、分布式结构、客户/服务器结构、并行结构从数据库系统应用的角度来看：客户/服务器结构、浏览器/服务器结构1.3.1数据库系统的三级模式结构数据库系统由模式、外模式河内模式三级构成模式：逻辑模式或概念模式对数据库中全体数据的逻辑结构和特征的描述外模式：子模式或用户模式对数据库用户能够看见和使用的局部数据的逻辑结构和特征的描述内模式：存储模式对数据库中数据物理结构和存储方法的描述一个数据库只有一个内模式1.3.2三级模式结构的两级映像与两级数据独立性外模式/模式映像：同一个模式可以有任意多个外模式对每一个外模式，数据库系统都有一个外模式/模式映像逻辑独立性：模式发生改变，改变各个外模式/模式映像作相应改变，外模式保持不变，应用程序不必修改模式/内模式映像：唯一物理独立性：存储结构改变，改变模式/内模式映像，保持模式不变1.3.3数据库系统的运行与维护结构C/S结构：客户端/前台/表示层：完成与数据库使用者的交互任务服务器/后台/数据层：主要负责数据管理单机方式、网络方式B/S结构：基于Web应用的客户/服务器结构、三层客户/服务器结构表示层：浏览器处理层/中间层：处理数据库使用者的具体应用逻辑数据层1.4数据模型数据模型：对现实世界中数据特征的抽象，描述数据的共性数据模型满足三方面的要求：比较真实的模拟现实世界、容易为人们所理解、便于在计算机上实现分为三层：物理层：物理数据模型，数据物理存储结构和存储方法逻辑层：逻辑数据模型/数据模型，层次模型、网状模型、关系模型、面型对象模型概念层：概念数据模型/概念模型，独立于任何DBMS，但容易向DBMS所支持的逻辑数据模型转换1.4.1概念模型实体、属性、码、域、实体型、实体集、联系：一对一、一对多、多对多E-R图1.4.2关系模型用二维表结构来表示实体及实体间联系的模型关系、元组、属性、域、分量、关系模式：关系名（属性1，属性2，…，属性n）、表、行、列、主键、外键、数据类型关系规范化：1NF：表中每个列只包含具有原子性的值，关系的每个分量必须是不可分的数据项2NF：没有部分函数依赖，表中不存在非主键的列依赖于组合主键某个部分的现象3NF：没有传递函数依赖，表中不存在任何非主键列与其他非主键列相互关联的现象BCNF：表中不存在主键列对主键的部分函数依赖和传递函数依赖低一级的关系模式，通过模式分解消除数据依赖中不合适的部分，使模式中的各关系模式达到某种程度的分离，可以转换为若干个高一级的关系模式的集合1.5数据库设计需求分析、概念结构设计、逻辑结构设计、物理结构设计、数据库实施、数据库运行与维护1.5.1概念结构设计1.5.2逻辑结构设计把E-R图转换为关系模型一个实体型转换为一个关系模式一个一对一的联系可以转换为一个独立的关系模式，也可以与任意一端对应的关系模式合并一个一对多的联系可转换为一个独立的关系模式，也可以与N端对应的关系模式合并一个多对多的联系转换为一个关系模式三个或三个以上实体间的联系一个多元联系可以转换为一个关系模式具有相同码的关系模式可以合并1.5.3物理设计对物理结构进行评价的重点是时间和空间的效率第二章MySQL概述2.1MySQL系统特性2.2MySQL服务器的安装和配置MySQL安装包括典型安装、定制安装和完全安装三种安装类型在MySQL安装过程中，若选用“启用TCP/IP网络”，则MySQL会默认选用的端口号是3306MySQL安装成功后，在系统中会默认建立一个root用户2.3服务器的启动与关闭2.4MySQL客户端管理工具MySQL命令行客户端MySQL客户端程序与实用工具MySQL图形化管理工具2.5MySQL语言结构MySQL语言的组成：数据定义语言：create、alter、drop数据操纵语言：select、insert、update、delete数据控制语言：grant、revokeMySQL扩展增加的语言要素：常量、变量（用户变量前@，系统变量前@@）、运算符、表达式、内置函数第三章数据库和表3.1数据库的创建与使用3.1.1创建数据库Createdatabase/schema(ifnotexists)***((default)characterset=***(default)collate=***);3.1.2选择数据库Use***;再用createdatabase语句创建了数据库后，该数据库不会自动成为当前数据库3.1.3修改数据库Alterdatabase/schema(***)(default)characterset=***(default)collate=***;修改数据库默认字符集或校对规则，必须删除并重新创建使用数据库的默认参数的存储过程Updatedatadirectoryname3.1.4删除数据库Dropdatabase/schema(ifexists)***;3.1.5查看数据库Showdatabases/schemas(like‘***’/where***);3.2创建和操纵表3.2.1创建表创建定义、表选项、分区选项Create(temporary)table(ifnotexists)customers(Cust_idintnotnullauto_increment,Cust_namechar(50)notnull,Cust_sexchar(1)notnulldefault0,Cust_addresschar(50)null,Cust_contactchar(50)null,Primarykey(cust_id))engine=InnoDB;每个表只能有一个auto_increment列，并且它必须被索引，其值可以覆盖3.2.2更新表增加或删减列、创建或取消索引、更改原有列的数据类型、重新命名列或表、更改表的评注和表的引擎类型Alter(ignore)table***+…Addcolumn：altertablemysql_test.customersAddcolumncust_citychar(10)notnulldefault‘Wuhan’aftercust_sex;First/after类似：addprimarykey(首先删除表中已有的主键)、addforeignkey、addindexChangecolumn：altertablemysql_test.customersChangecust_sexsexchar(1)nulldefault‘M’;Altercolumn：修改或删除表中指定列的默认值Altertablemysql_test.customersAltercolumncust_citysetdefault‘Beijing’;Modifycolumn：只会修改制定列的数据类型，而不会干涉它的列名Altertablemysql_test.customersModifycolumncust_namechar(20)first;First/afterDropcolumn：类似：dropprimarykey、dropfoteignkey、dropindexRenameto：altertablemysql_test.customersRenametomysql_test.backup_customers;3.2.3重命名表Renametable***to***可以同时重命名多个表3.2.4复制表Create(temporary)table(isnotexists)***like***Asselect复制表的内容，但索引和完整性约束不会被复制3.2.5删除表Drop(temporary)table(ifexists)***(restrict/cascade);3.2.6查看表显示表的名称：show(full)tables(from/in***)(like‘***’/where…);显示表的结构：show(full)columnsfrom/in***(from/in***)(like‘***’/where…);Describe/desc***(***);第四章表数据的基本操作4.1插入表数据Insert语句：insert(low_priority/delayed/high_priority)(ignore)(into)***values/value***(onduplicatekeyupdate***=***,…);Insert(…)(…)(into)***set***=***/default(onduplicatekeyupdate…);Insert(…)(…)(into)***select…(onduplicatekeyupdate…);Replace语句：在插入数据前将表中与待插入的新纪录相冲突的旧记录删除4.2删除表数据Delete语句：从单个表中删除数据：delete(low_priority)(quick)(ignore)from***(where…)(orderby…)(limit…);从多个表中删除数据：Delete(…)***,***,…from***(where…);删除from子句之前表中对应的行Delete(…)from***,…using***(where…);删除from子句之中的表中对应的行Deletetb1,tb2fromtb1,tb2,tb3wheretb1.id=tb2.idandtb2.id=tb3.id;Deletefromtb1,tb2usingtb1,tb2,tb3wheretb1.id=tb2.idandtb2.id=tb3.id;Truncate语句：从表中删除所有的行Truncate(table)***;对于参与了索引和视图的表，不能用truncate语句删除数据4.3修改表数据修改单个表：update(low_priority)(ignore)***set…(where…)(order…)(limit…);修改多个表：updatetb1,=’李明’,=’张亮’wheretb1.id=tb2.id;第五章数据库的查询5.1select语句5.2列的选择与指定选择指定的列定义并使用列的别名：selectcust_name,cust_addressas‘地址’frommysql_test.customers;自定义的列别名中有空格，必须用单引号将列别名括起来列别名不允许出现在where子句中替换查询结果集中的数据：selectcust_nameCaseWhencust_sex=’M’then’男’Else’女’Endas性别Frommysql_test.customers;计算列值：selectcust_name,cust_id+100frommysql_test.customers;聚合函数5.3from子句与链接表交叉连接：select*fromtb1crossjointb2;Select*fromtb1,tb2;内连接：select…from***(inner)join***on…;Straight_join类似，其后不能使用using，只能用on设置连接条件相等连接不等连接自连接：需要在一个表中查找有相同列值的行要为表指定两个不同的别名，且对所有查询的引用均必须使用表别名限定自然连接：连接的列在两张表中名称相同Naturaljoin外连接：一定会提供数据行，无论该行能否在另外一个表中找出相匹配的行左外连接：left(outer)join，右外连接：right(outer)join5.4where子句比较运算字符串匹配：通配符：%、_可在搜索模式后用关键字escape和一个指定的转义字符，临时改变通配符的作用和意义Select*frommysql_test.customerswherecust_addresslike‘%#_%’escape‘#’;文本匹配：rlike/regexp(binary)基本字符匹配：select*frommysql_test.customerswherecust_addressregexp‘市’;选择匹配：分隔符‘|’范围匹配：[3-8],[a-p]特殊字符匹配：转义字符‘//’字符类匹配：将常用的数字、字符等定义成一个预定义的字符集，直接使用[:upper:]重复匹配使用定位符匹配：^文本的开始、$文本的结尾判定范围：between…and…In：最主要的作用是表达子查询Select*fromcustomerswherecust_idin(901,902,903);判定空值：isnull子查询：表子查询、行子查询、列子查询、标量子查询结合关键字in使用：子查询只返回一列数据结合比较运算符使用：all、some/any结合关键字exists使用：主要用于判定子查询的结果集是否为空子查询通常可以改为表的链接，只是两者的执行性能会有所差异5.5groupby子句与分组数据Groupby***(asc/desc)(withrollup);Withrollup包括分组汇总行，以及所有分组的整体汇总行除聚合函数外，select语句中的每个列都必须在groupby子句中给出Selectcust_address,cust_sex,count(*)as‘人数’Frommysql_test.customersGroupbycust_address,cust_sexWithrollup;5.6having子句Having子句用来过滤分组与where子句类似，Having子句中的条件可以包含聚合函数，where子句中不可以Selectcust_name,cust_addressFrommysql.customersGroupbycust_address,cust_nameHavingcount(*)<=3;5.7orderby子句Orderby***(asc/desc);5.8limit子句使用limit子句限制被select语句返回的行数第5位客户开始的3位客户Selectcust_id,cust_namefrommysql_test.customersOrderbycust_idLimit4,3;Selectcust_id,cust_nsmefrommysql_test.customersOrderbycust_idLimit3offset4;5.9union语句与联合查询Union语句中的每个select查询必须包含相同的列、表达式或聚合函数第一个select语句中被使用的列名会被作为union语句结果集的列名称只能使用一条orderby子句或limit子句，且必须置于最后一条select语句之后类有多个where子句的单条select语句，unionall可以完成where子句完成不了的工作第六章索引数据库性能调优技术的基础6.1索引概述6.2索引的存储与分类索引在存储引擎中实现，每种存储引擎所支持的索引类型不一定完全相同索引存储方式不同：B-树索引：索引的使用必须遵循“最左边前缀”原则哈希索引：memory存储引擎，heap存储引擎支持索引用途：普通索引：关键字index/key唯一性索引：unique主键：primary，主键是一种唯一性索引全文索引：fulltext空间索引单列索引/组合索引索引策略对应的索引类型：聚集索引：一个表只能有一个聚集索引覆盖索引：6.3索引的创建Createindex语句：create(online/offline)(unique/fulltext/spatial)index***(usingbtree/hash)on***(***(length)(asc/desc),….);不能创建主键Createtable语句：(constraint***)primarykey***(***,…)Index/key***(***,…)(constraint***)unique(index/key)***(***,…)Fulltext/spatial(idex/key)***(***,…)(constraint***)foreignkey***(***,…)…Altertable语句：add+上述语句6.4索引的查看Show(index/indexes/keys)from/in***(from/in***)(where…);6.5索引的删除Dropindex语句：drop(online/offline)index***on***;Alterindex语句：altertable***dropprimarykey;Dropindex***;Dropforeignkey***;6.6对索引的进一步说明第七章视图7.1视图概述7.2创建视图Create(orreplace)(algorithm=undefined/merge/temptable)(definer=***)(sqlsecurity/definer/invoker)view***(***,…)asselect…(with(cascaded/local)checkoption);7.3删除视图Dropview(ifexists)***,…(restrict/cascsde);7.4修改视图定义类createviewalter/先dropview，再createview/createorreplaceview7.5查看视图定义Showcreateview***;7.6更新视图数据可更新的视图要求该视图中的行和基础表中的行之间具有一对一的关系Insert语句通过视图向基础表插入数据Update语句通过视图修改基础表的数据Delete语句通过视图删除基础表的数据7.7查询视图数据7.8对视图的进一步说明视图不能索引，也不能有关联的触发器、默认值和规则视图可以和表一起使用第八章数据完整性约束与表维护语句8.1数据完整性约束8.1.1定义完整性约束各种完整性约束是数据库关系模式定义的一部分实体完整性：关系的主属性，即主码的组成不能为空主键约束：primarykey候选键约束：候选键的值必须是唯一的且不能为nullunique一个表只能有一个主键，可以有多个候选键参照完整性：foreignkey(***,…)references***(***(length)(asc/desc),…)(matchfull/matchpartial/matchsimple)(ondeleterestrict/cascade/setnull/noaction)(onupdate…);用户定义完整性：某一具体应用所涉及的数据应满足的要求非空约束：notnullCheck约束：check+SQL表达式基于列的check约束：cust_idintnotnullcheck(cust_idin(selectcust_idfromcustomers)),基于表的check约束：check(order>=2000andorder_amount<=5)8.1.2命名完整性约束在完整性约束的定义说明之前加上关键字constraint和该约束的名字只能给基于表的完整性约束指定名字，无法给基于列的完整性约束指定名字8.1.3更新完整性约束Altertable***dropconstraint…;Addconstraint…;完整性约束不能直接修改，先用altertable删除该约束，再增加一个新约束8.2表维护语句MySQL数据优化的基础Analyzetable：修复cardinality索引散列程度analyze(no_write_to_binlog/local)table***,…;Checktable：检查一个或多个表是否有错误checktable***,…(forupdate/quick/fast/medium/entended/changed);Checksumtable：对数据库中的每一个表计算校验和Checksumtable***,…(quick/extended);Optimizetable：重新利用未使用的空间Optimize(no_write_to_binlog/local)table***,…;Repairtable：尝试修复一个或多个可能被损坏的表Repair(no_write_to_binlog/local)table(quick)(extended)(use_frm)第九章触发器9.1触发器一个指定关联到一个表的数据库对象，当对某一个表的特定时间出现时，它会被激活响应insert、update和delete语句而自动执行的一条MySQL语句，其他语句不支持触发9.2创建触发器Create(definer=user/current_use)trigger***before/afterinsert…/update…/delete…on***foreachrow…;不能将触发器与临时表或视图联系起来同一个表不能勇于两个具有相同触发时刻和事件的触发期单一触发器不能与多个事件或多个表关联Showtriggers(from/in***)(like‘***’/where…);Createcustomers_insert_triggerafterinsertonmysl_test.customersforeachrowset@str=’onecustomeradded!’;9.3删除触发器Droptrigger(ifexists)***;触发器不能更新或覆盖，修改必须先删除再创建9.4使用触发器Insert触发器：在insert触发器代码内，可引用一个名为new的虚拟表，来访问被插入的行，new的值可以被更新Createtriggermysql_test.customers_insert_triggerafterinsertonmysql_test.customersforeachrowset@str=new.cust_id;Delete触发器：在delete触发器代码内，可以引用一个名为old的虚拟表，来访问被删除的行，old中的值是只读的，不能被更新Update触发器：在update触发器代码内，可引用一个名为old的虚拟表，来访问以前的值，也可以引用一个new的虚拟表访问新更新的值当触发器涉及对触发表自身的更新操作时，只能使用beforeupdate触发器，而afterupdate触发器将不被允许Createtriggermysql_test.customers_update_triggerbeforeupdateonmysql_test.customersforeachrowsetnew.cust_address=old.cust_contact;9.5对触发器的进一步说明第十章事件10.1事件时间调度器临时触发器，基于特定时间周期来执行某些任务，触发器基于某个表所产生的事件触发的10.2创建事件Create(definer=user/current_user)event(ifnotexists)***onscheduleat…+interval…/every…(starts…(+interval…))(ends…(+interval…))(oncompletion(not)preserve)(enable/disable/disableonslave)(comment‘…’)do(begin)…(end);10.3修改事件Alterevent类create可以使用一条alterevent语句让一个事件关闭或再次让其活动Altereventevent_insertdiable;Altereventevent_insertenable;Altereventevent_insertrenametoe_insert;10.4删除事件Dropevent(ifexists)***;第十一章存储过程与存储函数11.1存储过程一组为了完成某特定功能的SQL语句集，一段存放在数据库中的代码，由声明式的SQL语句和过程式的SQL语句组成11.1创建存储过程Create(definer=user/current_user)procedure***((in/out/inout)***type,…)(comment’…’/languagesql/(not)deterministic/(containssql/nosql/readssqldata/modifyiessqldata)/sqlsecurity(definer/invoker)begin…end;Delimiter$$Createproceduresp_update_sex(intcidint,intcsexchar(1))BeginUpdatecustomerssetcust_sex=csexwherecust_id=cid;End$$Showprocedurestatus;Showcreateprocedure***;11.1.2存储过程体局部变量：declare***type(default…);只能在存储过程体的begin…end语句块中声明，在存储过程体的开头处声明Set语句：为局部变量赋值Select…into语句：把选定的列的值直接存储到局部变量中Select语句返回的结果集只能有一行数据流程控制语句：条件判断语句：if…else(endif)case(endcase)循环控制语句：while…do…repeat…until…loop…leaveiterate游标：被select语句检索出来的结果集声明游标：declare***dursorforselect…;打开游标：open***;读取游标：fetch***into…;关闭游标：close***;11.1.3调用存储过程Call(***,…);11.1.4修改存储过程可以修改选项修改内容，先删除，再创建11.1.5删除存储过程Dropprocedurefunction(ifexists)***;11.2存储函数存储函数不能拥有输出参数，因为存储该函数本身就是输出参数可直接对存储函数进行调用，且不需要使用call语句11.2.1创建存储函数Create(definer=user/current_user)function***(***,…)returnstypecomment’…’/languagesql/(not)deterministic/(containssql/nosql/readssqldata/modifiessqldata)/sqlsecurity(definer/invoker);11.2.2调用存储函数Select***(***,…);11.2.3删除存储函数Dropfunction(ifexists)***;11.2.4修改存储函数同存储过程第十二章访问控制与安全管理12.1用户账号管理12.1.1创建用户账号Createuser***(indentifiedby(password)’…’/identifiedwith…);12.1.2删除用户Dropuser,…;12.1.3修改用户账号Renameuser***to***,…;12.1.4修改用户口令Setpassword(foruser)=password(‘…’)/’…’;12.2账户权限管理Showgrantsfor***;12.2.1权限的授予Grantselect/update/delete/insert/create/alter/…(***,…)ontable/function/procedure***to***,…(requirenone….And…)(with…);表权限、列权限、数据库权限、用户权限12.2.2权限的转移与限制转移：withgrantoption限制：withmax_queries_per_hour…/max_update_per_hour…/max_connections_per_hour…/max_user_connections…12.2.3权限的撤销Revokeselect/update/delete/insert/create/alter/…(***,…)ontable/function/procedure***from***,…;Revokeallprivileges,grantoptionfrom***,…;第十三章备份与恢复13.1数据库备份与恢复13.2数据库备份与恢复的方法13.2.1使用MySQL语句备份和恢复表数据只能导入导出数据的内容，而不包括表的结构Selectinto…outfile：select*intooutfile‘***’(characterset…)(fields(terminatedby‘…’)((optionally)enclosedby‘…’)(escapeby‘…’))(linesterminatedby‘…’);Loaddata…infile：loaddata(low_priority/concurrent)(local)infile‘***’(replace/ignore)intotable***(fields(terminatedby‘…’)((optionally)enclosedby‘…’)(escapeby‘…’))(lines(startingby‘…’)(terminatedby‘…’))(ignore…lines);Locktables***readlocktables***writeunlocktables13.2.2使用MySQL客户端实用程序备份和恢复数据可以备份数据库表的结构，数据库，甚至整个数据库系统Mysqldump：mysqldump(options)database(tables)>filename;mysqldump(options)–databases(options)db1,…>filename;mysqldump(options)–all-database(options)>filename;mysqldump–tab=filename;mysql：还原全部的SQL语句表结构损坏，用mysql单独做回复处理，表中原有的数据将会被全部清空Mysqlimport：只恢复表中的数据Mysqlimport(option)databasetextfile;13.2.3使用MySQL图形界面工具备份和恢复数据13.3二进制文件的使用第十四章PHP的MySQL数据库编程14.1PHP概述14.2PHP编程基础14.3使用潘合平进行MySQL数据库编程14.3.1编程步骤14.3.2建立与MySQL数据库服务器的连接Mysql_connect()：建立非持久的链接，数据库操作结束后会自动关闭<?Php$con=mysql_connect(“localhost:3306”,”root”,”123456”);If(!$con){ Echo“连接失败！<br>”;Echo“错误编号”.mysql_errno().”<br>”;Echo“错误信息”mysql_error().”<br>”;Die();//终止程序运行}Echo“连接成功！<br>”;?>Nysql_pconnect()：建立持久连接同上14.3.3选择数据库<?Php$con=mysql_connect(“localhost:3306”,”root”,”123456”);If(mysql_erron()){Echo“数据库服务器连接失败！<br>”;Die();//终止程序运行}Mysql_select_db(“mysql_test”,$con);If(mysql_errno()){Echo“数据库选择失败！<br>”;Die();//终止程序运行