MySQL数据库视图、索引和备份数据库恢复数据库的关键指令

1 视图

1 创建视图：数据来源于原始表
CREATE VIEW student_result
AS
SELECT s.studentName AS 姓名,r.studentResult AS 成绩,r.examDate AS 考试时间
FROM student AS s,result AS r
WHERE s.studentNo = r.studentNo
– 2 查看视图
SELECT * FROM student_result

– 查看所有视图
USE information_schema;
SELECT * FROM views;

2 索引

创建索引
CREATE INDEX index_student_studentName ON student(studentName);
– 删除索引
DROP INDEX index_student_studentName ON student
– 查看索引
SHOW INDEX FROM student

SHOW INDEX FROM student\G; 加上\G表示格式化 在DOS命令窗口中

3 备份数据库

mysqldump –u root –p myschool student > d:\backup\student0306.sql

mysqldump –help

4 恢复数据库（表）

首先在mysql中创建新的数据库school0306
mysql -uroot -p school0306 < E:\2020\backup\student0306.sql

表数据导出到外部文本文件中
SELECT * FROM student
WHERE studentName = ‘张三’
INTO OUTFILE ‘E:/2020/backup/studentOut.txt’
注意：文件studentOut.txt不能事先存在.

文本文件导入到数据表
LOAD DATA INFILE ‘E:/2020/backup/studentOut.txt’ INTO TABLE student
注意：导入的数据不能和现有表格列设置的约束，发生冲突