1、MySQL复习资料“MYSQL”期末复习总结题型：填空题（30分）、简答题（20分）、综合应用题（表和数据库、查询、存储过程、用户创建、数据库备份 50分）第一章：MYSQL概述与系统安装（1）什么是LAMP？MySQL是开源的，其体积小、速度快、总体拥有成本低。使得很多中小型网站为了降低网站总体拥有成本而选择了MySQL作为网站数据库，即LAMP架构。LAMP=Linux+Apache+MySQL+PHP/Perl/Python（2）MYSQL的版本有哪些？v MYSQL数据库分类 社区版(Community Server)：完全免费，官方不提供任何支持。 企业版(Enterprise)：收
2、费，不能在线下载，提供更多功能和完备的技术支持。v 当前常用版本为5.2或5.5 Standard：标准版，大多数用户使用该版本 Max：包含standard以外的没正式发布的新特性，用于用户体验 Debug：和standard类似，包含了一些调试信息，会影响系统性能（3）“选择数据库”含义分别是什么？v 选择数据库用途 Multifunction Database(多功能数据库)：适用于事务性存储引擎(InnoDB)和非事务性存储引擎(MyISAM) Transactional Database only(事务性数据库)：优化了事务性存储引擎(InnoDB)，但非事务性存储引擎(MyISAM
3、)也能使用 Non-Transactional Database only(非事务性数据)：优化了非事务性存储引擎(MyISAM)，事务性存储引擎(InnoDB)不能使用（4）MYSQL的端口号是什么？默认情况下是什么模式？v 设置端口号 设置MYSQL服务器使用的端口号 3306 Enable Strict Mode：对字符集及SQL语法采用严格模式，便于数据迁移。v 设置默认字符集 Standard Character Set(标准字符集)：默认为Latin1 Best Support For Multilingualism(多语言支持字符集)：UTF8 Manual Selected D
4、efault Character Set(自选择字符集)（5）MYSQL的“启动”、“关闭”命令分别是什么？v 启动服务C:Program FilesMySQLMySQL Server 5.5binnet start mysqlv 关闭服务C:Program FilesMySQLMySQL Server 5.5binnet stop mysql第二章：MYSQL数据库与表的创建（1）什么是MYSQL的模式？与其他数据库不同，MYSQL可以运行在不同的SQL MODE模式下。SQL Mode定义了MYSQL应支持的SQL语法、数据校验等，便于在不同的环境中使用MYSQL。v SQL Mode解决
5、以下问题 完成不同严格程度的数据校验，有效保障数据准确性。 将SQL Mode设置为ANSI模式，保证大多数SQL符合标准的SQL语法，应用在不同的数据库之间迁移时，不需要对业务SQL进行较大的修改。 设置SQL Mode可以使MYSQL上的数据更方便的迁移到目标数据库中。（2）如何查看当前的模式？（模式查看、修改的MYSQL语法）查看模式：select sql_mode;修改模式：set session sql_mode=STRICT_TRANS_TABLES;（3）MYSQL的几种类型？作用？适用于、常用模式Sql_mode值描述ANSI这种模式使语法 和行为更符合标准的sql。STRIC
6、T_TRANS_TABLES适用于事务表和非事务表，属严格模式，不允许非法日期、或超过字段长度插入数据，插入不正确的值给出错误而不是警告。TRADITIONAL属于严格模式，适用于事务表和非事务表，在事务表时，只要出现错误就立即回滚（4）数值类型最常用的是第三个；v 整数类型支持INT(n)形式，用于指定整数值的显示宽度，如显示宽度小于n时从左侧填满宽度。显示宽度并不限制可以在列内保存的值的范围，也不限制超过列的指定宽度的值的显示。当结合可选扩展属性ZEROFILL使用时， 默认补充的空格用零代替。例如，对于声明为INT(5) ZEROFILL的列，值4检索为00004。v MYSQL小数有两
7、种方式：浮点数和定点数。浮点数包括float、double，为近似数据类型。定点数只有decimal，用于保存必须为确切精度的值，例如货币数据。浮点数和定点数都可以使用数据名后加“(M,D)”形式。例如：salary DECIMAL(5,2)在该例子中，5是精度，2是标度。精度表示保存值的主要位数，标度表示小数点后面可以保存的位数。整数类型有一个属性：AUTO_INCREMENT，用于定义自动增长字段。每增加一行增加1。一个表最多只能定义一个AUTO_INCREMENT列，应定义为NOT NULL，并定义为PRIMARY KEY或UNIQUE。v 每个时间类型有一个有效值范围和一个“零”值，当
8、指定不合法的MySQL不能表示的值时使用“零”值。v DATE用来表示“年月日”、DATETIME用来表示“年月日时分秒”、TIME用来表示“时分秒”。v TIMESTAMP值显示格式为“YYYY-MM-DD HH:MM:SS”，用于需要经常插入或更新日期为当前系统时间的列。比如注册时间。v YEAR只表示年份。（5）DATETIME与TIMESTAMP的区别：（第三条） TIMESTAMP插入和查询都受当地时区的影响；DATETIME只反映当地时区，其他时区人查询时数据会有误差。NOW()。 （6）字符串类型 BLOB 为二进制字符串(字节字符串),用来保存二进制数据，如图片。TEXT字符字
9、符串，保存较大文本，如文章。 BLOB列没有字符集，并且排序和比较基于列值字节的数值值。TEXT列有一个字符集，并且根据字符集的 校对规则对值进行排序和比较。 在TEXT或BLOB列的存储或检索过程中，不存在大小写转换。 当保存或检索BLOB和TEXT列的值时不删除尾部空格。 对于BLOB和TEXT列的索引，必须指定索引前缀的长度。对于CHAR和VARCHAR，前缀长度是可选的。 BLOB和TEXT列不能有默认值。 在执行大量的删除操作时，使用BLOB和TEXT会留下很大的“空洞”，从而影响插入数据的性能。可使用OPTIMIZE TABLE功能对表进行碎片整理。 ENUM是一个字符串对象，其值
10、来自表创建时在列规定中显式枚举的一列值。枚举最多可以有65,535个元素。 定义了一组ENUM枚举类型值后，在严格模式下，向该列中插入不在该枚举中的值时，系统禁止插入。在ANSI模式下，向该列插入空值。 SET是一个字符串对象，可以有零或多个值，其值来自表创建时规定的允许的一列值。v SET可以向列中插入多个定义的值，而ENUM只能插入一个定义的值。 指定包括多个SET成员的SET列值时各成员之间用逗号(,)间隔开。这样SET成员值本身不能包含逗号。最多可以有64个元素 在严格模式下，向该列中插入不在该集合中的值时，系统禁止插入。在ANSI模式下，向该列采用截断方式插入。 （7）什么是“数据库
11、引擎”？MySQL插件式存储引擎是MySQL数据库服务器中的组件，负责为数据库执行实际的数据I/O操作，并能允许和强制执行面向特殊应用需求的特定特性集合。（8）常用四种数据库引擎？特点？MyISAM、InnoDB、Memory、Merge。MyISAM：MySQL默认存储引擎。MyISAM不支持事务、不支持外键，特点是访问速度快。 MyISAM是在Web、数据仓储和其他应用环境下最常使用的存储引擎之一。v 每个MyISAM表在磁盘上存储为3个文件，文件名与表名相同，扩展名分别是： .frm（存储表定义） MYD（MYData,存储数据） MYI(MYIndex，存储索引)。InnoDB：用于事
12、务处理应用程序，具有众多特性，包括ACID(Atomicity原子性、Consistency一致性、Isolation隔离性、Durability持续性)事务支持。v InnoDB存储表和索引有两种方式： 共享表存储：表的结构存放在.frm文件中，数据和索引保存在innodb_data_home_dir和innodb_data_file_path定义的表空间中。 多表空间存储：表的结构存放在.frm文件中，数据和索引单独保存在.idb中。如果是分区表，则每个分区对应单独的.ibd，文件名是“表名+分区名”。v Memory：所有数据保存在RAM（内存）中，一旦服务器关闭，表中的数据就会丢失。在
13、需要快速查找引用和其他类似数据的环境下，可提供极快的访问。v Merge：允许MySQL DBA或开发人员将一系列等同的MyISAM表以逻辑方式组合在一起，并作为一个对象引用它们。优点是可以突破单个MyISAM表大小的限制，并将不同的表分布在多个磁盘上，从而有效改善访问效率。对于诸如数据仓储等VLDB环境十分适合。（9）查看存储引擎v 查看当前默认存储引擎 Show variables like table_type;v 查看当前数据库支持的存储引擎 Show engines; Show variables like have%; v 查看指定表的存储引擎 Show create table
14、表名;查看全部存储引擎 CREATE TABLE SC ( Sno CHAR(10)， Cno CHAR(4)， Grade SMALLINT， PRIMARY KEY (Sno，Cno)， FOREIGN KEY (Sno) REFERENCES Student(Sno) ON Delete Cascade On Update Cascade， FOREIGN KEY (Cno) REFERENCES Course(Cno) ON Delete Cascade On Update Cascade )ENGINE=InnoDB DEFAULT CHARSET=utf-8;1、创建参照完整性要求
15、主、外键的字段数据类型、字符集必须一致。(10)外键约束有四种形式(特点)：“简答题” RESTRICT:在子表有相关记录时，父表不允许更新或删除。 CASCADE:父表在更新或删除时，同时更新或删除子表中对应的记录 SET NULL:父表在更新或删除时，子表中对应记录的对应字段设置为NULL NOT ACTION:与RESTRICT相同（11）查看表的结构命令v 查看表的结构有三种MYSQL指令 Desc tablename; Describe tablename; Show create table tablename; （12）2.4 表结构的修改（小目录）v 2.4.1 修改表名、字段
16、名v 2.4.2 修改字段数据类型v 2.4.3 修改字段排列位置v 2.4.4 增加字段、删除字段v 2.4.5 更改表的存储引擎v 2.4.6 删除表的外键约束v 修改表名 ALTER TABLE qq RENAME qq_t;v 修改字段名 ALTER TABLE qq_t CHANGE qq qqno varchar(15);修改字段数据类型ALTER TABLE qq_t MODIFY qqno varchar(20);v 增加字段 ALTER TABLE qq_t ADD email varchar(20);v 删除字段 ALTER TABLE qq_t DROP email;语句
17、格式： INSERT INTO (，) VALUES ( ， )Insert插入记录学生表关系模式：Student(Sno,Sname,Ssex,Sage,Sdept) INSERT INTO Student (Sno，Sname，Ssex，Sdept，Sage) VALUES (200215128，陈冬，男，IS，18)如果插入值的顺序与关系模式中定义一致，可以不输入属性列表。 INSERT INTO Student VALUES (200215128，陈冬，男，18 ，IS)可仅填写部分字段 INSERT INTO Student (Sno，Sname，Ssex)VALUES (20021
18、5128，陈冬，男)MYSQL中，Insert语句一次可以插入多条记录 INSERT INTO qq_t VALUES (57879798,5), (56498779,6);（13）复制表中的记录：（三种） 创建新表 newtable,并复制mytbl的数据表结构： CREATE TABLE newtable LIKE mytbl; 将数据表mytbl中的数据复制到新表newtable： INSERT newtable SELECT * FROM mytbl; 一次复制整个表的结构和数据： CREATE TABLE newtable SELECT * FROM old_table; （14）索
19、引分类（6种）v 普通索引：可在任何数据类型的字段上创建。v 唯一索引：UNIQUE索引，该索引对应字段的值不能重复。v 全文索引：FULLTEXT索引，只能在CHAR、VARCHAR、TEXT类型字段上创建，仅MyISAM表支持全文索引。v 多列索引：索引建立在多个字段上。查询条件只有使用了该索引的第一个字段时，索引才会被使用。v 空间索引：SPATIAL索引。详情参看MYSQL的空间扩展。v 利用最左前缀。对应组合索引（多列索引），可利用最左边的列集进行匹配，这样的列集称为最左前缀。v 1、创建表时创建索引 CREATE TABLE 表名（属性名 数据类型 完整性约束， 属性名 数据类型
20、完整性约束， UNIQUE | FULLTEXT | SPATIAL INDEX | KEY 别名 (属性名 (长度) ASC | DESC, )v 2、在现有表上创建索引 CREATE UNIQUE | FULLTEXT | SPATIAL INDEX 索引名 ON 表名 (属性名 (长度) ASC | DESC, )v 3、利用ALTER TABLE语句创建索引 ALTER TABLE 表名 ADD UNIQUE | FULLTEXT | SPATIAL INDEX 索引名 (属性名 (长度) ASC | DESC, )CREATE UNIQUE INDEX qq_qqno_unique
21、ON qq (qqno(10) 前缀索引CREATE INDEX qq_qqname_email ON qq(qqname asc,email desc)第三章：MYSQL数据查询（15）Select 基本语法SELECT ALL /DISTINCT , FROM , WHERE GROUP BY HAING ORDER BY ASC/ DESC 其中表示在实际的语句中要采用所需内容进行代替 表示可以根据需要进行选择，也可以不选 表示必选项（16）LIMIT查询前N条记录v 基本语法 SELECT LIMIT offset_start,row_count ; Offset start：记录的起
22、始偏移量，默认值为0 Row_count：要显示的记录数利用RAND()提取随机行v 基本提取方法： 利用ORDER BY RAND() 对数据随机排序 然后从排序记录结果中提取指定的记录行数Select * from student order by rand() limit 3;利用GROUP BY的WITH ROLLUP子句做统计v WITH ROLLUP反映了OLAP思想，能在GROUP BY基础上，获得任何一个分组以及分组组合的聚合信息。 select year,country,product,sum(profit) from sales group by year,country,
23、product; select year,country,product,sum(profit) from sales group by year,country,product with rollup;（17）重要函数函数名作用DATABASE()返回当前数据库名VERSION()返回当前数据库的版本号USER()返回登录用户名CHARSET(str)返回字符集编码，一般返回系统默认的字符集PASSWORD(str)对str进行加密，不可逆MD5(str)对str进行MD5加密，不可逆第四章： MYSQL触发器与存储过程（18）什么是存储过程和函数？存储程序和函数是事先经过编译并存储在数据库
24、中的一套SQL语句。v 存储过程示例1返回单个数据（存储过程用CALL来调用）mysql delimiter $mysql CREATE PROCEDURE qq_count (OUT num INT) - BEGIN - SELECT COUNT(*) INTO num FROM qq; - END; - $mysql delimiter ;mysql CALL qq_count(a);mysql SELECT a;v 存储过程示例2返回数据集CREATE PROCEDURE getrecord()BEGIN SELECT * FROM qq;END;v 存储函数的创建示例mysql del
25、imiter $mysql CREATE FUNCTION id_email(id int) RETURNS varchar(20) Reads SQL data - BEGIN - declare email varchar(20); - SELECT email INTO email FROM qq where tid=id; - RETURN email; - END; - $mysql delimiter ;mysql CALL id_email(1);/ 调用存储过程mysql SELECT id_email(1);说明：函数的创建需要指定返回值类型；同时应当在定义体中指明返回的结果
26、。调用函数不能使用CALL语句，函数可直接使用。（19）触发器利用触发器实现学生的年龄只能是1640岁，性别只能是M或F。create trigger student_check before insert on student for each row begin DECLARE ssex CHAR(5); declare sage smallint; set sage=new.sage; set ssex=new.ssex; if (sage15 and sage41) and (ssex=m or ssex=f) then set choice=1; else set choice=0;
27、 insert into xxxx values (1); end if;end;第五章： MYSQL权限与安全（20）MySQL存取控制包含2个阶段：（简答题） 用户验证：服务器检查连接用户是否合法。不合法则拒绝连接。 权限验证：对通过认证的合法用户赋予相应的权限，用户在这些权限范围内对数据库做相应的操作。 （21）用户连接时，权限表的存取过程如下：（简答题）v 先通过user表的host、user、password字段判断连接的IP、用户名、密码是否正确。v 通过身份验证后，则按照userdbtables_privcolumns_priv顺序得到数据库的权限。权限的范围依次递减。 实际上u
28、serdbtables_privcolumns_priv 顺序对应的是： 服务器指定数据库指定表指定字段（22）创建账号创建账号CREATE USERv CREATE USER语法 CREATE USER user IDENTIFIED BY PASSWORD password , user IDENTIFIED BY PASSWORD password . 必须拥有mysql数据库的全局CREATE USER权限，或INSERT权限。 可同时创建多个账号 例如：CREATE USER zzh1localhost IDENTIFIED BY 123456, zzh2localhost IDEN
29、TIFIED BY 123456;创建账号添加user表记录v 语法 INSERT INTO user(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表中插入用户后，需要执行FLUSH PRIVIEGES;命令使MYSQL重新装载user表中权限，方能使添加的用户生效。当然也可以重新启动MYSQL服务器。 创建账号GRANT语句v 基本语法GRANT priv_type (column_list) , priv_type (column_list) . ON object_type tbl_n
