5



视图
USE myschool;
DROP VIEW IF EXISTS `view_student_result`;
 删除视图`
CREATE VIEW view_student_result`
AS
 创建视图
  ……
SELECT * FROM `view_student_result`;
查看视图

创建视图
CREATE VIEW vw_python5 AS
SELECT studentname,sex,phone,studentresult,examdate FROM student2
INNER JOIN results ON student2.`studentNo`=results.`studentno`;
查看视图
 SELECT * FROM vw_python5;

视图可以使用多个表
视图也可以嵌套
CREATE VIEW vw_neinei AS

select studentname,studentresult from vw_python5;

 USE information_schema;use 调用数据库
 SELECT * FROM views;查看所有的视图
SELECT * FROM views\G;\G全局

#查看学生各科目考试成绩平均分 
CREATE VIEW vw_result AS
SELECT studentname,subjectname,studentresult FROM student2
INNER JOIN results ON student2.`studentNo`=results.`studentno`
INNER JOIN subjects ON results.`subjectno`=subjects.`subjectno`
GROUP BY studentname,subjectname;

索引
相当于目录的概念
提高检索速度
改善数据库性能
B—树索引
哈希索引
索引类型
普通索引；唯一索引unique（允许有一个空值）；主键索引（非空唯一）；复合索引；全文索引fulltext（用于大的文字类型）；空间索引spatial（例如地图搜附近，空间数据类型的列建立的索引）
查询时减少使用*
show index from 查看索引 

在cmd
数据备份（数据库故障；突然断电；病毒入侵；误操作导致数据丢失）
mysqldump命令
mysqldump [options（选项参数）] –u username() –h host –ppassword 
dbname[tbname1[,tbname2……]]>filename.sql
mysqldump -uroot -hlocalhost -p123456 school student2 > G:\MYSQL\schools.sql;备份命令
mysql(DOS命令)
恢复数据库mysql -uroot -p123456 school student2 > G:\MYSQL\schools.sql;
source filename;在mysql中恢复
导出
SELECT * FROM `result`
WHERE `subjectNo` = 
(SELECT `subjectNo` FROM `subject` 
WHERE `subjectName` = 'Logic Java')
INTO OUTFILE 'd:/backup/result_Java.txt';
导入
LOAD DATA INFILE 'd:/back/result_Java.txt' INTO TABLE result;