2
starting the test for backup
4
DROP DATABASE IF EXISTS bup_db1;
5
DROP DATABASE IF EXISTS bup_db2;
6
CREATE DATABASE bup_db1;
9
CREATE TABLE t1(id int not null primary key, name char(10),city varchar(10));
12
(1,'aa1','RR1'),(2,'aa2','RR2'),(3,'aa3','RR3'),(4,'aa4','RR4'),(5,'aa5','RR5'),(6,'aa6','RR6'),(7,'aa7','RR7'),(8,'aa8','RR8');
24
CREATE TABLE t3(ccode int, District char(20) not null primary key, scode int, foreign key (scode) references t1(id));
27
(234, 'zuloa',1),(321,'yyy',2),(765,'iug',3),(124,'LKJ',4),(235,'uth',6);
36
CREATE TABLE t5(Gender char(5), cand_age int,foreign key(cand_age) references
38
Loading data into table t5
40
('F',23),('F',24),('M',19),('F',28),('M',43),('F',30),('M',31),('M',27);
51
*****Create views from the table t1 of bup_db1*******
52
CREATE VIEW v1 AS SELECT * FROM t1;
53
*****Creating views from 2 tables(t1 and t3) within same database******
54
CREATE VIEW vcomb AS SELECT name, city, ccode FROM t1, t3 WHERE id=scode;
55
CREATE DATABASE bup_db2;
57
CREATE TABLE t2(idno int, age int primary key, education char(20) ,foreign key (idno) references bup_db1.t1(id));
58
INSERT INTO t2 VALUES(1,23,'BS'),(2,24,'BE'),(3,19,'school'),(4,28,'MS'),(5,43,'PHD'),(6,30,'Doctor'),(7,31,'Lawyer'),(8,27,'undergrad');
70
CREATE VIEW v2 AS SELECT age, education FROM t2;
71
******Creating Views from combination of 2 databases*******
72
CREATE VIEW v3 AS SELECT name, age, education FROM bup_db1.t1 , bup_db2.t2 WHERE id=idno;
73
*********Creating View from another view ***********.
74
*****Creating View from other Database********
75
CREATE VIEW v4 AS SELECT * FROM bup_db1.t3;
77
RENAME TABLE v4 to student_details;
79
*******Creating View from database bup_db2**********
80
CREATE VIEW v5 AS SELECT * FROM bup_db2.t2;
81
******Creating View v6********
137
excercise objects of bup_db2
169
SELECT * FROM student_details;
176
showing objects and create statements.
177
SHOW FULL TABLES FROM bup_db1;;
179
Table_type BASE TABLE
181
Table_type BASE TABLE
183
Table_type BASE TABLE
188
Tables_in_bup_db1 vcomb
190
SHOW FULL TABLES FROM bup_db2;;
191
Tables_in_bup_db2 student_details
194
Table_type BASE TABLE
199
SHOW CREATE VIEW bup_db1.v1;;
201
Create View CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `bup_db1`.`v1` AS select `bup_db1`.`t1`.`id` AS `id`,`bup_db1`.`t1`.`name` AS `name`,`bup_db1`.`t1`.`city` AS `city` from `bup_db1`.`t1`
202
character_set_client latin1
203
collation_connection latin1_swedish_ci
204
SHOW CREATE VIEW bup_db1.vcomb;;
206
Create View CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `bup_db1`.`vcomb` AS select `bup_db1`.`t1`.`name` AS `name`,`bup_db1`.`t1`.`city` AS `city`,`bup_db1`.`t3`.`ccode` AS `ccode` from (`bup_db1`.`t1` join `bup_db1`.`t3`) where (`bup_db1`.`t1`.`id` = `bup_db1`.`t3`.`scode`)
207
character_set_client latin1
208
collation_connection latin1_swedish_ci
209
SHOW CREATE VIEW bup_db2.v3;;
211
Create View CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `bup_db2`.`v3` AS select `bup_db1`.`t1`.`name` AS `name`,`bup_db2`.`t2`.`age` AS `age`,`bup_db2`.`t2`.`education` AS `education` from (`bup_db1`.`t1` join `bup_db2`.`t2`) where (`bup_db1`.`t1`.`id` = `bup_db2`.`t2`.`idno`)
212
character_set_client latin1
213
collation_connection latin1_swedish_ci
215
BACKUP DATABASE bup_db1, bup_db2 TO 'bup_objectview.bak';
218
BACKUP DATABASE bup_db1 TO 'bup_objectview1.bak';
221
BACKUP DATABASE bup_db2 TO 'bup_objectview2.bak';
225
DROP DATABASE bup_db1;
226
DROP DATABASE bup_db2;
227
RESTORE FROM 'bup_objectview.bak';
230
showing objects and create statements
231
SHOW CREATE DATABASE bup_db1;;
233
Create Database CREATE DATABASE `bup_db1` /*!40100 DEFAULT CHARACTER SET latin1 */
234
SHOW FULL TABLES FROM bup_db1;;
236
Table_type BASE TABLE
238
Table_type BASE TABLE
240
Table_type BASE TABLE
245
Tables_in_bup_db1 vcomb
247
SHOW FULL TABLES FROM bup_db2;;
248
Tables_in_bup_db2 student_details
251
Table_type BASE TABLE
256
SHOW CREATE VIEW bup_db1.v1;;
258
Create View CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `bup_db1`.`v1` AS select `bup_db1`.`t1`.`id` AS `id`,`bup_db1`.`t1`.`name` AS `name`,`bup_db1`.`t1`.`city` AS `city` from `bup_db1`.`t1`
259
character_set_client latin1
260
collation_connection latin1_swedish_ci
261
SHOW CREATE VIEW bup_db1.vcomb;;
263
Create View CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `bup_db1`.`vcomb` AS select `bup_db1`.`t1`.`name` AS `name`,`bup_db1`.`t1`.`city` AS `city`,`bup_db1`.`t3`.`ccode` AS `ccode` from (`bup_db1`.`t1` join `bup_db1`.`t3`) where (`bup_db1`.`t1`.`id` = `bup_db1`.`t3`.`scode`)
264
character_set_client latin1
265
collation_connection latin1_swedish_ci
266
SHOW CREATE VIEW bup_db2.v3;;
268
Create View CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `bup_db2`.`v3` AS select `bup_db1`.`t1`.`name` AS `name`,`bup_db2`.`t2`.`age` AS `age`,`bup_db2`.`t2`.`education` AS `education` from (`bup_db1`.`t1` join `bup_db2`.`t2`) where (`bup_db1`.`t1`.`id` = `bup_db2`.`t2`.`idno`)
269
character_set_client latin1
270
collation_connection latin1_swedish_ci
271
****check for view contents after Restore*****
327
excercise objects of bup_db2
359
SELECT * FROM student_details;
366
DROP DATABASE bup_db1;
367
DROP DATABASE bup_db2;
369
RESTORE FROM 'bup_objectview.bak';
373
ALTER TABLE t1 CHANGE id id tinyint not null;
374
SHOW CREATE TABLE t1;;
376
Create Table CREATE TABLE `t1` (
377
`id` tinyint(4) NOT NULL,
378
`name` char(10) DEFAULT NULL,
379
`city` varchar(10) DEFAULT NULL,
381
) ENGINE=MyISAM DEFAULT CHARSET=latin1
392
DELETE FROM t1 WHERE id=7;
421
BACKUP DATABASE bup_db1, bup_db2 TO 'bup_objectview3.bak';
424
DROP DATABASE bup_db1;
425
DROP DATABASE bup_db2;
426
RESTORE FROM 'bup_objectview3.bak';
450
*** DROP bup_db1, bup_db2 DATABASE ****
452
DROP DATABASE bup_db1;
453
DROP DATABASE bup_db2;