第四次作业

一、将mydb9_stusys数据库下的student、sc 和course表，备份到本地主机保存为st_msg_bak.sql文件，然后将数据表恢复到自建的db_test数据库中；

1、备份文件

C:\Users\28406>mysqldump -uroot -p mydb9_stusys student sc course > st_msg_bak.sql

Enter password: *****

2、恢复

C:\Users\28406>mysql -u root -p db_test < st_msg_bak.sql

Enter password: *****

3、查看恢复情况

二、在db_test数据库创建一视图 stu_info,查询全体学生的姓名，性别，课程名，成绩。

1、创建视图stu_info

mysql> use db_test;

Database changed

mysql> create view stu_info as

-> select s.sname as 姓名,s.ssex as 性别,c.cname as 课程名,sc.score as 成绩

-> from student s

-> join sc on s.sno = sc.sno

-> join course c on sc.cno = c.cno;

Query OK, 0 rows affected (0.02 sec)

2、以视图查询学生信息

mysql> select * from stu_info;

三、查看mydb9_stusys库下哪些是视图表；

mysql> use mydb9_stusys;

Database changed

mysql> select table_name

-> from information_schema.views

-> where table_schema = 'mydb9_stusys';

四、删除视图表

mysql> use db_test;

Database changed

mysql> drop view stu_info;