MySQL 06
视图
什么是视图？
----简单来说就是一条select语句执行后返回的结果集
视图的特性：
视图是对若干张基本表的引用，一张虚表，查询语句执行的结果，不存储具体的数据（基本表数据发生了改变，视图也会跟着改变）；
可以跟基本表一样，进行增删改查操作(使用视图修改数据会有许多限制，一般在实际开发中视图仅用作查询)
视图的作用：
方便操作，特别是查询操作，减少复杂的SQL语句，增强可读性；
更加安全，数据库授权命令不能限定到特定行和特定列，但是通过合理创建视图，可以把权限限定到行列级别；
使用场合：
权限控制的时候，不希望用户访问表中某些含敏感信息的列，比如salary...
关键信息来源于多个复杂关联表，可以创建视图提取我们需要的信息，简化操作；
如何创建视图：
select view 视图名
as
<select语句>;
删除视图：drop view [if exisit] 视图名;
举个列子：使用之前我们创建的myschool库中的几个表，创建一个学生姓名，所学课程，平均成绩的视图。
CREATE VIEW re_ave
AS
SELECT studentName,sub.`subjectName`,AVG(r.`studentResult` )
FROM student AS s
INNER JOIN result AS r ON s.`studentNo`=r.`studentNo`
INNER JOIN subject_1 AS sub ON r.`subjectNo`=sub.`subjectNo`
GROUP BY studentName,sub.`subjectName`;
查询结果如下：
索引
什么是索引？
----索引（Index）是帮助MySQL高效获取数据的数据结构。可以得到索引的本质：索引是数据结构。
可以理解为“排好序的快速查找数据结构”
在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据,
这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。
索引的优势：
类似大学图书馆建书目索引，提高数据检索效率，降低数据库的IO成本。
通过索引对数据进行排序，降低数据排序的成本，降低了CPU的消耗。
劣势：
索引列需占空间，虽然索引大大提高了查询速度，但是降低了表的更新速度。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段。
常用索引类型
| 普通索引 | 基本索引类型 允许在定义索引的列中插入重复值和空值 |
| 唯一索引 | 索引列数据不重复 允许有空值 |
| 主键索引 | 主键列中的每个值是非空、唯一的 一个主键将自动创建主键索引 |
| 复合索引 | 将多个列组合作为索引 |
| 全文索引 | 支持值的全文查找 允许重复值和空值 |
| 空间索引 | 对空间数据类型的列建立的索引 |
MySQL索引按存储类型分类
B-树索引：InnoDB、mylSAM均支持
哈希索引
创建索引
create [unique|fulltext|spatial] index 索引名 (唯一索引、全文索引或空间索引、可选)
on table_name(column_name[length]....);
删除索引
drop index index_name on table_name; 注：删除表时，该表的所有索引同时会被删除。
哪些情况需要创建索引
①主键自动建立唯一索引
②频繁作为查询条件的字段应该创建索引
③查询中与其他表关联的字段，外键关系建立索引
④频繁更新的字段不适合建立索引，因为每次更新不单单是更新了记录还会更新索引
⑤WHERE条件里用不到的字段不创建索引
⑥单键/组合索引的选择问题，who?(在高并发下倾向创建组合索引)
⑦查询中排序的字段，排序的字段若通过索引去访问将大大提高排序速度
⑧查询中统计或者分组字段
哪些情况不要创建索引
①表记录太少
②经常增删改的表
提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE、和DELETE。
因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。
数据重复且分布平均的表字段，因此应该只为最经常查询和最经常排序的数据建立索引。
③注意，如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果。
按学生名和年级编号组合查询
学生身份证号是唯一的
按成绩区间范围查找学生考试信息
CREATE INDEX sn_gid
ON student(`studentName`,`gradeID`)
CREATE UNIQUE INDEX s_id
ON student(`identityCard`)
CREATE INDEX s_result
ON `result`(`studentResult`)数据库备份
为了避免各种意外情况导致数据库数据丢失我们需要对数据进行备份。
mysqldump命令——MySQL一个常用的备份工具
将CREATE和INSERT INTO语句保存到文本文件
属于DOS命令！！！！！使用时不能进入数据库
举个列子，我们备份myschool中的student表
这样我们就备份了student表到D盘aa文件夹中的student_1.sql文件中了。
常用的选项参数有：
| 参数 | 描述 |
| -add-drop-table | 在每个CREATE TABLE语句前添加DROP TABLE语句，默认是打开的，可以用-skip-add-drop-table来取消 |
| --add-locks | 该选项会在INSERT 语句中捆绑一个LOCK TABLE 和UNLOCK TABLE 语句 好处：防止记录被再次导入时，其他用户对表进行的操作 |
| -t或-no-create-info | 只导出数据，而不添加CREATE TABLE语句 |
| -c或--complete-insert | 在每个INSERT语句的列上加上列名，在数据导入另一个数据库时有用 |
| -d或--no-data | 不写表的任何行信息，只转储表的结构 |
备份文件包含的主要信息：
备份后文件包含信息MySQL及mysqldump工具版本号
备份账户的名称
主机信息
备份的数据库名称
SQL语句注释和服务器相关注释
CREATE和INSERT语句
恢复数据库
方法1 mysql命令
mysql -u用户名 -p 数据库名 < 备份文件地址filename.sql
需注意：mysql为dos命令
在执行该语句之前，必须在MySQL服务器中创建新数据库，如果不存在恢复数据库过程将会出错。
方法2 source命令
source filename;
需注意：登录MySQL服务后使用
执行该命令之前，先创建并选择恢复后的目标数据库
表数据导出到文本文件
语法： select * from tablename
[where contion]
into outfile 'filename' [option]
例子： 将result表中的‘Logic Java’课程的成绩信息导出到文件中。
SELECT * FROM `result`
WHERE `subjectNo` =
(SELECT `subjectNo` FROM `subject`
WHERE `subjectName` = 'Logic Java')
INTO OUTFILE 'd:/backup/result_Java.txt';
文本文件导入到数据库
语法： load data infile filename into table
tablename [option]
需注意的是：导入数据前应确保目标表已存在！
例子：将刚刚导出到文本文件中的数据导入到result表中。
LOAD DATA INFILE 'd:/back/result_Java.txt' INTO TABLE result;
要求：
在每个INSERT中语句加上字段名
恢复时使用mysql命令和source命令两种方式
1).备份数据库文件
2).恢复
使用mysql命令：
使用source命令：
扫一扫
184
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
