CSC6341 Database Systems

Database Backup

To back up your tables together with data into a script file, run the following command from Linux command line. For example:

mysqldump --no-tablespaces -u jdoe JDOE s p j spj -p > spj_backup.sql

This command will back up (save) four tables s, p, j and spj in the database named JDOE as a script file called spj_backup.sql. If you want to make changes to the tables, just modify the script and re-run it on MySQL database as below:

mysql JDOE -u jdoe -p < spj_backup.sql

If you want to add or delete a column for a table, use ALTER command. For example,

ALTER TABLE faculty DROP COLUMN dob, DROP COLUMN age;

The command above will add a column called "dob" ( meaning date of birth) and delete the column called "age" for table "faculty".

Views

How to create views, drop views, and check views


mysql> show full tables;
+-----------------+------------+
| Tables_in_JDOE | Table_type |
+-----------------+------------+
| cellar          | BASE TABLE |
| customer        | BASE TABLE |
| full_spj        | VIEW       |
| j               | BASE TABLE |
| movie           | BASE TABLE |
| p               | BASE TABLE |
| rents           | BASE TABLE |
| s               | BASE TABLE |
| spj             | BASE TABLE |
+-----------------+------------+
9 rows in set (0.00 sec)

mysql> show full tables where table_type = 'VIEW';
+-----------------+------------+
| Tables_in_JDOE | Table_type |
+-----------------+------------+
| full_spj        | VIEW       |
+-----------------+------------+
1 row in set (0.00 sec)

mysql> drop view full_spj;


mysql> create view full_spj as 
select s.sname as 'Supplier Name', p.pname as 'Part Name', j.jname 'Project Name', spj.qty as QTY  
from s, p, j, spj  
where s.s_num = spj.s_num and p.p_num = spj.p_num and j.j_num = spj.j_num;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from full_spj;
+---------------+-----------+--------------+------+
| Supplier Name | Part Name | Project Name | QTY  |
+---------------+-----------+--------------+------+
| Smith         | Nut       | Sorter       |  200 |
| Smith         | Nut       | Console      |  700 |
| Jones         | Screw     | Sorter       |  400 |
| Jones         | Screw     | Display      |  200 |
| Jones         | Cam       | Display      |  100 |
| Jones         | Screw     | OCR          |  200 |
| Jones         | Screw     | Console      |  500 |
| Jones         | Screw     | RAID         |  600 |
| Jones         | Screw     | EDS          |  400 |
| Jones         | Screw     | Tape         |  800 |
| Blake         | Screw     | Sorter       |  200 |
| Blake         | Screw     | Display      |  500 |
| Clark         | Cog       | OCR          |  300 |
| Clark         | Cog       | Tape         |  300 |
| Adams         | Bolt      | Display      |  200 |
| Adams         | Cog       | Display      |  200 |
| Adams         | Nut       | Console      |  100 |
| Adams         | Bolt      | Console      |  100 |
| Adams         | Screw     | Console      |  200 |
| Adams         | Screw     | Console      |  800 |
| Adams         | Cam       | Console      |  400 |
| Adams         | Cog       | Console      |  500 |
| Adams         | Cam       | RAID         |  500 |
| Adams         | Cam       | Tape         |  100 |
+---------------+-----------+--------------+------+
24 rows in set (0.00 sec)

CSC6341 Database Systems Tutorial

MySQL