MySQL复习资料.docx

MySQL复习资料.docx

《MySQL复习资料.docx》由会员分享，可在线阅读，更多相关《MySQL复习资料.docx（30页珍藏版）》请在冰点文库上搜索。

MySQL复习资料

“MYSQL”期末复习总结

题型：

填空题（30分）、简答题（20分）、综合应用题（表和数据库、查询、存储过程、用户创建、数据库备份50分）

第一章：

MYSQL概述与系统安装

（1）什么是LAMP？

？

？

MySQL是开源的，其体积小、速度快、总体拥有成本低。

使得很多中小型网站为了降低网站总体拥有成本而选择了MySQL作为网站数据库，即LAMP架构。

LAMP=Linux+Apache+MySQL+PHP/Perl/Python

（2）MYSQL的版本有哪些？

vMYSQL数据库分类

٭社区版（CommunityServer）：

完全免费，官方不提供任何支持。

٭企业版（Enterprise）：

收费，不能在线下载，提供更多功能和完备的技术支持。

v当前常用版本为5.2或5.5

٭Standard：

标准版，大多数用户使用该版本

٭Max：

包含standard以外的没正式发布的新特性，用于用户体验

٭Debug：

和standard类似，包含了一些调试信息，会影响系统性能

（3）“选择数据库”含义分别是什么？

v选择数据库用途

٭MultifunctionDatabase（多功能数据库）：

适用于事务性存储引擎（InnoDB）和非事务性存储引擎（MyISAM）

٭TransactionalDatabaseonly（事务性数据库）：

优化了事务性存储引擎（InnoDB），但非事务性存储引擎（MyISAM）也能使用

٭Non-TransactionalDatabaseonly（非事务性数据）：

优化了非事务性存储引擎（MyISAM），事务性存储引擎（InnoDB）不能使用

（4）MYSQL的端口号是什么？

默认情况下是什么模式？

v设置端口号

٭设置MYSQL服务器使用的端口号3306

٭EnableStrictMode：

对字符集及SQL语法采用严格模式，便于数据迁移。

v设置默认字符集

٭StandardCharacterSet（标准字符集）：

默认为Latin1

٭BestSupportForMultilingualism（多语言支持字符集）：

UTF8

٭ManualSelectedDefaultCharacterSet（自选择字符集）

（5）MYSQL的“启动”、“关闭”命令分别是什么？

？

v启动服务

C:

\ProgramFiles\MySQL\MySQLServer5.5\bin>netstartmysql

v关闭服务

C:

\ProgramFiles\MySQL\MySQLServer5.5\bin>netstopmysql

第二章：

MYSQL数据库与表的创建

（1）什么是MYSQL的模式？

？

与其他数据库不同，MYSQL可以运行在不同的SQLMODE模式下。

SQLMode定义了MYSQL应支持的SQL语法、数据校验等，便于在不同的环境中使用MYSQL。

vSQLMode解决以下问题

٭完成不同严格程度的数据校验，有效保障数据准确性。

٭将SQLMode设置为ANSI模式，保证大多数SQL符合标准的SQL语法，应用在不同的数据库之间迁移时，不需要对业务SQL进行较大的修改。

٭设置SQLMode可以使MYSQL上的数据更方便的迁移到目标数据库中。

（2）如何查看当前的模式？

？

（模式查看、修改的MYSQL语法）

查看模式：

select@@sql_mode;

修改模式：

setsessionsql_mode=’STRICT_TRANS_TABLES’;

（3）MYSQL的几种类型？

？

—>作用？

？

—>适用于、、、、、、

常用模式

Sql_mode值

描述

ANSI

这种模式使语法和行为更符合标准的sql。

STRICT_TRANS_TABLES

适用于事务表和非事务表，属严格模式，不允许非法日期、或超过字段长度插入数据，插入不正确的值给出错误而不是警告。

TRADITIONAL

属于严格模式，适用于事务表和非事务表，在事务表时，只要出现错误就立即回滚

（4）数值类型

最常用的是第三个；

v整数类型支持INT（n）形式，用于指定整数值的显示宽度，如显示宽度小于n时从左侧填满宽度。

显示宽度并不限制可以在列内保存的值的范围，也不限制超过列的指定宽度的值的显示。

当结合可选扩展属性ZEROFILL使用时，默认补充的空格用零代替。

例如，对于声明为INT（5）ZEROFILL的列，值4检索为00004。

vMYSQL小数有两种方式：

浮点数和定点数。

浮点数包括float、double，为近似数据类型。

定点数只有decimal，用于保存必须为确切精度的值，例如货币数据。

浮点数和定点数都可以使用数据名后加“（M,D）”形式。

例如：

salaryDECIMAL（5,2）在该例子中，5是精度，2是标度。

精度表示保存值的主要位数，标度表示小数点后面可以保存的位数。

整数类型有一个属性：

AUTO_INCREMENT，用于定义自动增长字段。

每增加一行增加1。

一个表最多只能定义一个AUTO_INCREMENT列，应定义为NOTNULL，并定义为PRIMARYKEY或UNIQUE。

v每个时间类型有一个有效值范围和一个“零”值，当指定不合法的MySQL不能表示的值时使用“零”值。

vDATE用来表示“年月日”、DATETIME用来表示“年月日时分秒”、TIME用来表示“时分秒”。

vTIMESTAMP值显示格式为“YYYY-MM-DDHH:

MM:

SS”，用于需要经常插入或更新日期为当前系统时间的列。

比如注册时间。

vYEAR只表示年份。

（5）DATETIME与TIMESTAMP的区别：

（第三条）

٭TIMESTAMP插入和查询都受当地时区的影响；DATETIME只反映当地时区，其他时区人查询时数据会有误差。

NOW（）。

（6）字符串类型

٭BLOB为二进制字符串（字节字符串）,用来保存二进制数据，如图片。

TEXT字符字符串，保存较大文本，如文章。

٭BLOB列没有字符集，并且排序和比较基于列值字节的数值值。

TEXT列有一个字符集，并且根据字符集的校对规则对值进行排序和比较。

٭在TEXT或BLOB列的存储或检索过程中，不存在大小写转换。

٭当保存或检索BLOB和TEXT列的值时不删除尾部空格。

٭对于BLOB和TEXT列的索引，必须指定索引前缀的长度。

对于CHAR和VARCHAR，前缀长度是可选的。

٭BLOB和TEXT列不能有默认值。

٭在执行大量的删除操作时，使用BLOB和TEXT会留下很大的“空洞”，从而影响插入数据的性能。

可使用OPTIMIZETABLE功能对表进行碎片整理。

٭ENUM是一个字符串对象，其值来自表创建时在列规定中显式枚举的一列值。

枚举最多可以有65,535个元素。

٭定义了一组ENUM枚举类型值后，在严格模式下，向该列中插入不在该枚举中的值时，系统禁止插入。

在ANSI模式下，向该列插入空值。

٭SET是一个字符串对象，可以有零或多个值，其值来自表创建时规定的允许的一列值。

vSET可以向列中插入多个定义的值，而ENUM只能插入一个定义的值。

٭指定包括多个SET成员的SET列值时各成员之间用逗号（‘,’）间隔开。

这样SET成员值本身不能包含逗号。

最多可以有64个元素

٭在严格模式下，向该列中插入不在该集合中的值时，系统禁止插入。

在ANSI模式下，向该列采用截断方式插入。

（7）什么是“数据库引擎”？

？

MySQL插件式存储引擎是MySQL数据库服务器中的组件，负责为数据库执行实际的数据I/O操作，并能允许和强制执行面向特殊应用需求的特定特性集合。

（8）常用四种数据库引擎？

？

特点？

？

MyISAM、InnoDB、Memory、Merge。

MyISAM：

MySQL默认存储引擎。

MyISAM不支持事务、不支持外键，特点是访问速度快。

MyISAM是在Web、数据仓储和其他应用环境下最常使用的存储引擎之一。

v每个MyISAM表在磁盘上存储为3个文件，文件名与表名相同，扩展名分别是：

٭.frm（存储表定义）

٭MYD（MYData,存储数据）

٭MYI（MYIndex，存储索引）。

InnoDB：

用于事务处理应用程序，具有众多特性，包括ACID（Atomicity原子性、Consistency一致性、Isolation隔离性、Durability持续性）事务支持。

vInnoDB存储表和索引有两种方式：

٭共享表存储：

表的结构存放在.frm文件中，数据和索引保存在innodb_data_home_dir和innodb_data_file_path定义的表空间中。

٭多表空间存储：

表的结构存放在.frm文件中，数据和索引单独保存在.idb中。

如果是分区表，则每个分区对应单独的.ibd，文件名是“表名+分区名”。

vMemory：

所有数据保存在RAM（内存）中，一旦服务器关闭，表中的数据就会丢失。

在需要快速查找引用和其他类似数据的环境下，可提供极快的访问。

vMerge：

允许MySQLDBA或开发人员将一系列等同的MyISAM表以逻辑方式组合在一起，并作为一个对象引用它们。

优点是可以突破单个MyISAM表大小的限制，并将不同的表分布在多个磁盘上，从而有效改善访问效率。

对于诸如数据仓储等VLDB环境十分适合。

（9）查看存储引擎

v查看当前默认存储引擎

٭Showvariableslike‘table_type’;

v查看当前数据库支持的存储引擎

٭Showengines;

٭Showvariableslike‘have%’;

v查看指定表的存储引擎

٭Showcreatetable表名;

查看全部存储引擎

CREATETABLESC

（SnoCHAR（10），

CnoCHAR（4），

GradeSMALLINT，

PRIMARYKEY（Sno，Cno），

FOREIGNKEY（Sno）REFERENCESStudent（Sno）

ONDeleteCascadeOnUpdateCascade，

FOREIGNKEY（Cno）REFERENCESCourse（Cno）

ONDeleteCascadeOnUpdateCascade

）ENGINE=InnoDBDEFAULTCHARSET=utf-8;

1、创建参照完整性要求主、外键的字段数据类型、字符集必须一致。

（10）外键约束有四种形式（特点）：

“简答题”

٭RESTRICT:

在子表有相关记录时，父表不允许更新或删除。

٭CASCADE:

父表在更新或删除时，同时更新或删除子表中对应的记录

٭SETNULL:

父表在更新或删除时，子表中对应记录的对应字段设置为NULL

٭NOTACTION:

与RESTRICT相同

（11）查看表的结构命令

v查看表的结构有三种MYSQL指令

٭Desctablename;

٭Describetablename;

٭Showcreatetabletablename;

（12）2.4表结构的修改（小目录）

v2.4.1修改表名、字段名

v2.4.2修改字段数据类型

v2.4.3修改字段排列位置

v2.4.4增加字段、删除字段

v2.4.5更改表的存储引擎

v2.4.6删除表的外键约束

v修改表名

ALTERTABLEqqRENAMEqq_t;

v修改字段名

ALTERTABLEqq_tCHANGEqqqqnovarchar（15）;

修改字段数据类型

ALTERTABLEqq_tMODIFYqqnovarchar（20）;

v增加字段

ALTERTABLEqq_tADDemailvarchar（20）;

v删除字段

ALTERTABLEqq_tDROPemail;

语句格式：

INSERT

INTO<表名>[（<属性列1>[，<属性列2>…）]

VALUES（<常量1>[，<常量2>]…）

Insert插入记录

学生表关系模式：

Student（Sno,Sname,Ssex,Sage,Sdept）

INSERT

INTOStudent（Sno，Sname，Ssex，Sdept，Sage）

VALUES（'200215128'，'陈冬'，'男'，'IS'，18）

如果插入值的顺序与关系模式中定义一致，可以不输入属性列表。

INSERT

INTOStudent

VALUES（'200215128'，'陈冬'，'男'，18，'IS'）

可仅填写部分字段

INSERT

INTOStudent（Sno，Sname，Ssex）

VALUES（'200215128'，'陈冬'，'男'）

MYSQL中，Insert语句一次可以插入多条记录

INSERTINTOqq_t

VALUES

（‘57879798‘,5）,

（‘56498779‘,6）;

（13）复制表中的记录：

（三种）

٭创建新表newtable,并复制mytbl的数据表结构：

CREATETABLEnewtableLIKEmytbl;

٭将数据表mytbl中的数据复制到新表newtable：

INSERTnewtableSELECT*FROMmytbl;

٭一次复制整个表的结构和数据：

CREATETABLEnewtableSELECT*FROMold_table;

（14）索引分类（6种）

v普通索引：

可在任何数据类型的字段上创建。

v唯一索引：

UNIQUE索引，该索引对应字段的值不能重复。

v全文索引：

FULLTEXT索引，只能在CHAR、VARCHAR、TEXT类型字段上创建，仅MyISAM表支持全文索引。

v多列索引：

索引建立在多个字段上。

查询条件只有使用了该索引的第一个字段时，索引才会被使用。

v空间索引：

SPATIAL索引。

详情参看MYSQL的空间扩展。

v利用最左前缀。

对应组合索引（多列索引），可利用最左边的列集进行匹配，这样的列集称为最左前缀。

v1、创建表时创建索引

CREATETABLE表名（属性名数据类型[完整性约束]，

属性名数据类型[完整性约束]，…

[UNIQUE|FULLTEXT|SPATIAL]INDEX|KEY

[别名]（属性名[（长度）][ASC|DESC],…）

v2、在现有表上创建索引

CREATE[UNIQUE|FULLTEXT|SPATIAL]INDEX索引名

ON表名（属性名[（长度）][ASC|DESC],…）

v3、利用ALTERTABLE语句创建索引

ALTERTABLE表名ADD[UNIQUE|FULLTEXT|SPATIAL]

INDEX索引名（属性名[（长度）][ASC|DESC],…）

CREATEUNIQUEINDEXqq_qqno_uniqueONqq（qqno（10））前缀索引

CREATEINDEXqq_qqname_emailONqq（qqnameasc,emaildesc）

第三章：

MYSQL数据查询

（15）Select基本语法

SELECT[ALL/DISTINCT]

<目标列表表达式>[,<目标列表达式>]…

FROM<表名或视图名>[,<表名或视图名>]…

[WHERE<条件表达式>]

[GROUPBY<列名1>[HAING<条件表达式>]]

[ORDERBY<列名2>{ASC/DESC}]

其中<>表示在实际的语句中要采用所需内容进行代替

[]表示可以根据需要进行选择，也可以不选

{}表示必选项

（16）LIMIT查询前N条记录

v基本语法

SELECT……[LIMIToffset_start,row_count];

٭Offsetstart：

记录的起始偏移量，默认值为0

٭Row_count：

要显示的记录数

利用RAND（）提取随机行

v基本提取方法：

٭利用ORDERBYRAND（）对数据随机排序

٭然后从排序记录结果中提取指定的记录行数

Select*fromstudentorderbyrand（）limit3;

利用GROUPBY的WITHROLLUP子句做统计

vWITHROLLUP反映了OLAP思想，能在GROUPBY基础上，获得任何一个分组以及分组组合的聚合信息。

٭selectyear,country,product,sum（profit）fromsalesgroupbyyear,country,product;

٭selectyear,country,product,sum（profit）fromsalesgroupbyyear,country,productwithrollup;

（17）重要函数

函数名

作用

DATABASE（）

返回当前数据库名

VERSION（）

返回当前数据库的版本号

USER（）

返回登录用户名

CHARSET（str）

返回字符集编码，一般返回系统默认的字符集

PASSWORD（str）

对str进行加密，不可逆

MD5（str）

对str进行MD5加密，不可逆

第四章：

MYSQL触发器与存储过程

（18）什么是存储过程和函数？

？

？

存储程序和函数是事先经过编译并存储在数据库中的一套SQL语句。

v存储过程示例1—返回单个数据（存储过程用CALL来调用）

mysql>delimiter$$

mysql>CREATEPROCEDUREqq_count（OUTnumINT）

->BEGIN

->SELECTCOUNT（*）INTOnumFROMqq;

->END;

->$$

mysql>delimiter;

mysql>CALLqq_count（@a）;

mysql>SELECT@a;

v存储过程示例2—返回数据集

CREATEPROCEDUREgetrecord（）

BEGIN

SELECT*FROMqq;

END;

v存储函数的创建示例

mysql>delimiter$$

mysql>CREATEFUNCTIONid_email（idint）RETURNSvarchar（20）

ReadsSQLdata

->BEGIN

->declareemailvarchar（20）;

->SELECTemailINTOemailFROMqqwheretid=id;

->RETURNemail;

->END;

->$$

mysql>delimiter;

mysql>CALLid_email

（1）;//调用存储过程

mysql>SELECTid_email

（1）;

说明：

函数的创建需要指定返回值类型；同时应当在定义体中指明返回的结果。

调用函数不能使用CALL语句，函数可直接使用。

（19）触发器

利用触发器实现学生的年龄只能是16~40岁，性别只能是M或F。

createtriggerstudent_checkbeforeinsertonstudentforeachrow

begin

DECLAREssexCHAR（5）;

declaresagesmallint;

setsage=new.sage;

setssex=new.ssex;

if（sage>15andsage<41）and（ssex='m'orssex='f'）then

set@choice=1;

else

set@choice=0;

insertintoxxxxvalues

（1）;

endif;

end;

第五章：

MYSQL权限与安全

（20）MySQL存取控制包含2个阶段：

（简答题）

٭用户验证：

服务器检查连接用户是否合法。

不合法则拒绝连接。

٭权限验证：

对通过认证的合法用户赋予相应的权限，用户在这些权限范围内对数据库做相应的操作。

（21）用户连接时，权限表的存取过程如下：

（简答题）

v先通过user表的host、user、password字段判断连接的IP、用户名、密码是否正确。

v通过身份验证后，则按照user→db→tables_priv→columns_priv顺序得到数据库的权限。

权限的范围依次递减。

实际上user→db→tables_priv→columns_priv顺序对应的是：

服务器→指定数据库→指定表→指定字段

（22）创建账号

创建账号—CREATEUSER

vCREATEUSER语法

CREATEUSERuser[IDENTIFIEDBY[PASSWORD]'password']   

[,user[IDENTIFIEDBY[PASSWORD]'password']]...

٭必须拥有mysql数据库的全局CREATEUSER权限，或INSERT权限。

٭可同时创建多个账号

٭例如：

CREATEUSER‘zzh1’@’localhost’IDENTIFIEDBY‘123456’,‘zzh2’@’localhost’IDENTIFIEDBY‘123456’;

创建账号—添加user表记录

v语法

INSERTINTOuser（host,user,password,ssl_cipher,x509_issuer,x509_subject）VALUES（‘localhost',‘zzh1',PASSWORD（‘123456'）,’’,’’,’’）

٭在直接向user表插入用户时，密码必须要用password（）函数加密，MYSQL默认添加用户时密码用passwrod（）函数处理。

否则，即便创建了用户，该用户也不能登录。

٭User表中，ssl_cipher,x509_issuer,x509_subject三个字段没有默认值，所以必须填充初始值才能插入用户。

٭直接向user表中插入用户后，需要执行FLUSHPRIVIEGES;命令使MYSQL重新装载user表中权限，方能使添加的用户生效。

当然也可以重新启动MYSQL服务器。

创建账号—GRANT语句

v基本语法

GRANTpriv_type[（column_list）][,priv_type[（column_list）]]...

ON[object_type]{tbl_n