Java Mysql数据库创建视图、索引、备份和恢复

#使用视图获取多表中的数据
DROP VIEW IF EXISTS `view_result`;
CREATE VIEW `view_result`
AS
 SELECT s.`studentName` 学生姓名, su.`subjectName` 课程名,AVG(`studentResult`) 平均成绩 FROM `result` r
 INNER JOIN `student` s ON s.`studentNo`=r.`studentNo`
 INNER JOIN `subject` su ON su.`subjectNo`=r.`subjectNo`
 GROUP BY s.`studentName`,su.`subjectName`;
 
 SELECT * FROM view_result;
 SELECT * FROM result;

 

#使用mysqldump命令将myschool数据库中的学生表,成绩表备份到文件d:\ex\myschool_xxxxx.sql中

mysqldump -uroot -proot myschool student  result > d:\ex\myschool_20170926.sql

#使用mysql命令和source命令两种方式还原学生表,成绩表到school数据库 DROP DATABASE IF EXISTS `schoolDB`; CREATE DATABASE `schoolDB`; #使用mysql命令 mysql -u root -proot schoolDB < d:\ex\myschool_20170926.sql

#使用source命令 source d:\ex\myschool_20170926.sql;

 

 #使用SELECT....INTO outfile语句到处科目表subject中的记录

SELECT * FROM `subject` INTO OUTFILE 'd:/ex/subject_out.txt';

#使用load data infile语句导入subject_out.txt数据到schoolDB数据库

USE myschooldb;

#创建subject表结构

CREATE TABLE `subject` (  

`subjectNo` INT(4) NOT NULL AUTO_INCREMENT COMMENT '课程编号',  

`subjectName` VARCHAR(50) DEFAULT NULL COMMENT '课程名称',  

`classHour` INT(4) DEFAULT NULL COMMENT '学时',  

`gradeID` INT(4) DEFAULT NULL COMMENT '年级编号',  

PRIMARY KEY (`subjectNo`)

) ENGINE=INNODB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COMMENT='科目表';

#导入数据

LOAD DATA INFILE 'd:/ex/subject_out.txt' INTO TABLE `subject`;