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
注意:导入的数据不能和现有表格列设置的约束,发生冲突