MySQL复习资料.docx
- 文档编号:29804503
- 上传时间:2023-07-27
- 格式:DOCX
- 页数:30
- 大小:134.52KB
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
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- MySQL 复习资料