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

1、全国计算机等级考试二级 MySQL 总结第一章数据库技术的的基本概念与方法1.1 基本概念数据：描述事物的符号记录数据库中存储的基本对象数据的描述形式还不能完全表达其内容， 需要经过解释数据库：长期储存在计算机内的、有组织的、可 共享的数据集合数据库中的数据按一定的数据模型组 织、描述和存储，具有较小的冗余度、 较高的数据独立性和易扩展性数据库管理系统:数据定义功能、数据操纵功能、 数据库的运行管理、数据库的 建立和维护功能、提供方便有 效存储数据库信息的接口和 工具数据库系统：在计算机系统中引入数据库后的系 统一般由数据库、数据库管理系统、 应用开发工具、应用系统、数 据库管理员和用户构成1

2、.2 数据库系统的特点数据结构化数据冗余度小数据共享性好数据独立性高：物理独立性、逻辑独立性 数据由DBMS统一管理和控制1.3 数据库系统的结构从数据库用户试图的视角来看：三级模式结构 从数据库管理系统的角度来看：集中式结构、分 布式结构、客户/服务器结构、并行结构从数据库系统应用的角度来看：客户/服务器结 构、浏览器/服务器结构1.3.1 数据库系统的三级模式结构数据库系统由模式、外模式河内模式三级构成 模式：逻辑模式或概念模式对数据库中全体数据的逻辑结构和特征 的描述外模式：子模式或用户模式对数据库用户能够看见和使用的局部 数据的逻辑结构和特征的描述内模式：存储模式对数据库中数据物理结构

3、和存储方法的描述一个数据库只有一个内模式1.3.2 三级模式结构的两级映像与两级数据独立性外模式/模式映像：同一个模式可以有任意多个 外模式对每一个外模式，数据库系统都有一个外模式/模式映像逻辑独立性：模式发生改变, 改变各个外模式/模式映像 作相应改变，外模式保持不 变，应用程序不必修改模式/内模式映像：唯一物理独立性：存储结构改变, 改变模式/内模式映像，保持模式不变1.3.3 数据库系统的运行与维护结构C/S结构：客户端/前台/表示层：完成与数据库 使用者的交互任务服务器/后台/数据层：主要负责数据管单机方式、网络方式B/S结构：基于 Web应用的客户/服务器结构、 三层客户/服务器结构

4、表示层：浏览器处理层/中间层：处理数据库使用者的具体应用逻辑数据层1.4 数据模型数据模型：对现实世界中数据特征的抽象， 描述 数据的共性数据模型满足三方面的要求：比较真实的模拟现 实世界、容易为人们所理解、便于在计算机上实 现分为三层：物理层：物理数据模型，数据物理存 储结构和存储方法逻辑层：逻辑数据模型/数据模型， 层次模型、网状模型、关系 模型、面型对象模型概念层：概念数据模型/概念模型， 独立于任何DBMS,但容易 向DBMS所支持的逻辑数据模型转换1.4.1 概念模型实体、属性、码、域、实体型、实体集、联系： 一对一、一对多、多对多E-R图1.4.2 关系模型用二维表结构来表示实体及

5、实体间联系的模型关系、元组、属性、域、分量、关系模式：关系 名（属性1,属性2, 属性n）、表、行、列、主键、外键、数据类型关系规范化：1NF:表中每个列只包含具有原子 性的值，关系的每个分量必 须是不可分的数据项2NF:没有部分函数依赖，表中不 存在非主键的列依赖于组合 主键某个部分的现象3NF:没有传递函数依赖，表中不 存在任何非主键列与其他非 主键列相互关联的现象BCNF:表中不存在主键列对主键的部分函数依赖和传递函数 依赖低一级的关系模式，通过模式分解 消除数据依赖中不合适的部 分，使模式中的各关系模式达到某种程度的分离， 可以转换为若干个高一级的 关系模式的集合1.5 数据库设计需求

6、分析、概念结构设计、逻辑结构设计、物理 结构设计、数据库实施、数据库运行与维护1.5.1 概念结构设计1.5.2 逻辑结构设计把E-R图转换为关系模型一个实体型转换为一个关系模式一个一对一的联系可以转换为一个独立的关系 模式，也可以与任意一端对应的关系模式合并 一个一对多的联系可转换为一个独立的关系模 式，也可以与N端对应的关系模式合并 一个多对多的联系转换为一个关系模式三个或三个以上实体间的联系一个多元联系可以转换为一个关系模式具有相同码的关系模式可以合并1.5.3 物理设计对物理结构进行评价的重点是时间和空间的效率第二章MySQL概述2.1 MySQL系统特性2.2 MySQL服务器的安装

7、和配置MySQL安装包括典型安装、定制安装和完全安装三种安装类型在MySQL安装过程中，若选用“启用 TCP/IP 网络”，则MySQL会默认选用的端口号是3306 MySQL安装成功后，在系统中会默认建立一个 root用户2.3 服务器的启动与关闭2.4 MySQL客户端管理工具MySQL命令行客户端MySQL客户端程序与实用工具MySQL图形化管理工具2.5 MySQL语言结构MySQL语言的组成：数据定义语言：createalter、drop数据操纵语言：selectinsert、update delete数据控制语言：grant、revokeMySQL扩展增加的语言 要素:常 量、变

8、量( 用 户 变量 、心 刖系 统 变 量、心 刖 )、运 算 符、表 达 式函数第三章数据库和表3.1 数据库的创建与使用3.1.1 创建数据库Create database/schema (if not exists) *(default)chaa c t e rs e t*( de f al t ) c ol l a t e*);3.1.2 选择数据库Use *；再用create database句创建了数据库后)该数库 不 会 自 动 成 为 当、心 刖数 据 库3.1.3 修改数据库Alter database/schema (*) (default) characterset*(

9、d e f a u l t ) c o l l a t e*; ;修改数据库默认字符集或校对规则，必须删除并重 新 创 建 使 用 数 据 库 的 默 认 参 数 的 存 储 过Update data directory name3.1.4 删除数据库Drop database/schema (if exists) *;3.1.5 查看数据库Show databases/schemas (like* /where *);3.2 创建和操纵表3.2.1 创建表创建定义、表选项、分区选项Create (temporary) table (if not exists)c us t o m e rs(

10、Cust_id int not null auto_increment,Cust_name char(50) not null,Cust_sex char(1) not null default 0,Cust_address char(50) null,Cust_contact char(50) null,Primary key(cust_id)engine = InnoDB;每个表只能有一个 auto_increment歹U)并且它必须 被 索 引其 值 可 以 覆 盖3.2.2 更新表增加或删减列、创建或取消索引、更改原有列的数 据重 新 命 名 列 或 表、更 改 表 的 评 注和表的引

11、擎类型Alter (ignore) table * +Add column: alter table mysql_test.customersAdd column cust_city char(10)no tn u l ld e f a u l tW u h aa f tercu s tse x;；First/after类似：add primary key（首先删除表 中键)、a d dforeg nkea d dn dexChange columnalter tablem y s q ite s tu s t o m e r sChange cust_sex sex charn ul ld

12、e f a uAlter column:修改或删除表中指定列的默认值Alter table mysql_test.customersAlter column cust_city set defaultModify column:只会修改制定列的数据类型,而 不 会 干涉 它 的 列 名Altertablemy sq ites t.cus tm ersModify column cust_namec ha r( 20) fr s t;；First/afterDrop column:类彳以： drop primary key、dropo t eg nk e y、 d r o Pn d eRenam

13、e to: alter table mysql_test.customersRenametoy s q it e s t.b a c k u Pc utomers; ；3.2.3 重命名表Rename table * to *可以同时重命名多个表3.2.4 复制表Create (temporary) table (is not exists) * like*As selec反制表的内容，但索引和完整性约束不会 被 复 制3.2.5 删除表Drop (temporary) table (if exists) *( r e s t rc t/ c a s c a d e ); ;3.2.6 查看表

14、显示表的名称：show (full) tables (from/in *)lk e*/ w h e r e) ; ;显示表的结构：show (full) columns from/in *r o m/n*) (lk e*/ wh e r e) ; ;Describe/desc *(*)；第四章 表数据的基本操作4.1插入表数据Insert 语 句 ： insert (low_priority/delayed/high_priority) (ignore) (into) * values/value * (on duplicatekey update* 一* . - =,);Insert ()(

15、)(into) * set *=*/default (on duplicate key update );Insert ( ) ( ) (into) * select(on duplicate key update );Replace语句：在插入数据前将表中与待插入的新纪录相冲突的旧记录删除4.2 删除表数据Delete语句：从单个表中删除数据： delete (low_priority) (quick) (ignore) from * (where ) (order by ) (limit ); 从多个表中删除数据： Delete ( ) *,*, from * (where);删除from

16、子句之前表中 对应的行 Delete ( ) from *, using * (where);删除from子句之中的表 中对应的行 Delete tb1,tb2 from tb1,tb2,tb3 where tb1.id=tb2.id and tb2.id=tb3.id;Delete from tb1,tb2 using tb1,tb2,tb3 where tb1.id=tb2.id and tb2.id=tb3.id;Truncate语句：从表中删除所有的行Truncate (table) *;对于参与了索引和视图的表，不 能用truncate语句删除数据4.3 修改表数据修改单个表：upd

17、ate (low_priority) (ignore) * set(where ) (order )(limit )；修改多个表：update tb1,tb2 set ='李 明,='张亮'where tb1.id=tb2.id;第五章数据库的查询5.1 select 句5.2 列的选择与指定选择指定的列定义并使用列 的别名：select cust name, cust_address as '地 址'from mysql_test.customers;自定义的列别名中有空格，必须用单引号将列别名括起来 列别名不允许出现在

18、where子句中替换查询结果集中的数据：select cust_nameCaseWhencust_sex=M ' then'男'Else'女' End as性别Frommysql_test.customers;计算歹 U 值:select cust_name, cust_id+100 frommysql_test.customers；聚合函数5.3 from子句与链接表交叉连接：select * from tb1 cross join tb2;Select * from tb1,tb2;内连接：select - from * (inner) join *

19、 on ;Straight_join类似，其后不能使用using,只能用on设置连接条件相等连接 不等连接自连接：需要在一个表中查找有相同列值的行 要为表指定两个不同的别名，且对所有 查询的引用均必须使用表别名限定 自然连接：连接的列在两张表中名称相同Natural join 外连接：一定会提供数据行，无论该行能否在另 外一个表中找出相匹配的行 左外连接：left (outer) join,右外连接： right (outer) join5.4 where 子句比较运算 字符串匹配：通配符：%、可在搜索模式后用关键字 escape 和一个指定的转义字符，临时改变 通配符的作用和意义Select

20、*frommysql_test.customers where cust_addresslike %#_% ' escape #' 文本匹配：rlike/regexp (binary)基本字符匹配：select * from mysql_test.customers where cust_address regexp市'；选择匹配：分隔符'|'范围匹配：3-8,a-p特殊字符匹配：转义字符字符类匹配：将常用的数字、字符等 定义成一个预定义的字符集，直接 使用:upper:重复匹配使用定位符匹配：人文本的开始、$ 文本的结尾判定范围： between-an

21、d In:最主要的作用是表达子查询Select * from customerswhere cust_id in (901,902,903);判定空值：is null子查询：表子查询、行子查询、列子查询、标量 子查询结合关键字in使用：子查询只返回一 列数据结合比较运算符使用：all、some/any结合关键字exists使用：主要用于判定 子查询的结果集是否为空子查询通常可以改为表的链接，只是两 者的执行性能会有所差异5.5 group by子句与分组数据Group by * (asc/desc) (with rollup);With rollup包括分组汇总行，以及所有分组的 整体汇总行除

22、聚合函数外，select语句中的每个列都必须在 group by子句中给出Select cust_address,cust_sex,count(*) as 数'From mysql_test.customersGroup by cust_address,cust_sexWith rollup;5.6 having 子句Having子句用来过滤分组与 where子句类似)Having子句中的条件可以 包含聚合函数)where子句中不可 以Select cust_name,cust_addressFrom mysql.customers Group by cust_address,cust

23、_nameHaving count(*)<=3;5.7 order by 子句Order by * (asc/desc);5.8 limit 子句 使用limit子句限制被selec畸句返回的行数第5位客户开始的3位客户Select cust_id,cust_name from mysql_test.customersOrder by cust_idLimit 4,3;Select cust_id,cust_nsme from mysql_test.customersOrder by cust_id Limit 3 offset 4;5.9 union语句与联合查询Union语句中的每个

24、select查询必须包含相同的歹I、表达式或聚合函数第一个select语句中被使用的列名会被作为 union语句结果集的列名称只能使用一条 order by子句或limit子句，且必 须置于最后一条selec储句之后类有多个 where子句的单条select句，union all 可以完成where子句完成不了的 工作第六章索引数据库性能调优技术的基础6.1 索引概述6.2 索引的存储与分类索引在存储引擎中实现，每种存储引擎所支持的 索引类型不一定完全相同索引存储方式不同：B-树索引：索引的使用必须 遵循“最左边前缀”原则哈希索引：memory存储引 擎，heap存储引擎支持索引用途：普通索引

25、：关键字index/key唯一性索引：unique主键：primary,主键是一种唯一性索引全文索引：fulltext空间索引单列索引/组合索引索引策略对应的索引类型：聚集索引：一个表只 能有一个聚集索引覆盖索引：6.3 索引的创建Create index 语 句 ： create (online/offline) (unique/fulltext/spatial) index * (using btree/hash) on *(*(length) (asc/desc),.);不能创建主键Create table语句:(constraint *) primary key*(*,)Index/k

26、ey *(*,)(constraint *) unique (index/key) *(*,)Fulltext/spatial (idex/key)*(* .)(constraint *) foreign key*(*, )Alter table语句：add+上述语句6.4 索引的查看Show (index/indexes/keys)from/in * (from/in*) (where );6.5 索引的删除Drop index语句：drop (online/offline) index * on *;Alter index 语句：alter table * drop primarykey;Drop index *;Drop foreignkey *;6.6 对索引的进一步说明 第七章视图7.1 视图概述7.2 创建视图Create(orreplace)(algorithm=undefi