Database Backup and Restoration

Create Database and Tables

CREATE DATABASE booksDB;
USE booksDB;

CREATE TABLE books (
  bk_id INT NOT NULL PRIMARY KEY,
  bk_title VARCHAR(50) NOT NULL,
  copyright YEAR NOT NULL
);

CREATE TABLE authors (
  auth_id INT NOT NULL PRIMARY KEY,
  auth_name VARCHAR(20),
  auth_gender CHAR(1)
);

CREATE TABLE authorbook (
  auth_id INT NOT NULL,
  bk_id INT NOT NULL
);

Insert Data

INSERT INTO books VALUES 
  (11078, 'Learning MySQL', 2010),
  (11033, 'Study Html', 2011),
  (11035, 'How to use php', 2003),
  (11072, 'Teach youself javascript', 2005),
  (11028, 'Learing C++', 2005),
  (11069, 'MySQL professional', 2009),
  (11026, 'Guide to MySQL 5.5', 2008),
  (11041, 'Inside VC++', 2011);

INSERT INTO authors VALUES 
  (1001, 'WriterX', 'f'),
  (1002, 'WriterA', 'f'),
  (1003, 'WriterB', 'm'),
  (1004, 'WriterC', 'f'),
  (1011, 'WriterD', 'f'),
  (1012, 'WriterE', 'm'),
  (1013, 'WriterF', 'm'),
  (1014, 'WriterG', 'f'),
  (1015, 'WriterH', 'f');

INSERT INTO authorbook VALUES 
  (1001, 11033), (1002, 11035), (1003, 11072), (1004, 11028),
  (1011, 11078), (1012, 11026), (1012, 11041), (1014, 11069);

Backup Operations

1. Back up all tables in the booksDB database using mysqldump.

2. Back up only the books table from the booksDB daatbase.

3. Back up both booksDB and test databases.

4. Restore the books table using the mysql command.

5. Restore the books table using the source commend.

mysqldump -u root -p booksdb > D:\booksdb.sql
mysqldump -u root -p --databases booksDB test > D:\booksdb_test.sql
mysqldump -u root -p booksDB books > \booksdb-books.sql
mysql -u root -p booksdb < \booksdb-books.sql
source \booksdb-books.sql

Index Management

Create Tables

CREATE TABLE goods (
  goods_id INT PRIMARY KEY AUTO_INCREMENT,
  goods_name VARCHAR(20) NOT NULL,
  cat_id INT NOT NULL DEFAULT 0,
  brand_id INT NOT NULL DEFAULT 0,
  goods_sn CHAR(12) NOT NULL,
  shop_price FLOAT(6,2) NOT NULL DEFAULT 0.00,
  goods_desc TEXT
);

CREATE TABLE category (
  cat_id INT PRIMARY KEY AUTO_INCREMENT,
  cate_name VARCHAR(20),
  parent_id INT DEFAULT 0
);

Indexing Requirements

1. Remove the goods_desc and goods_sn columns from the goods table, and add a new column click_count.

2. Add a unique index on the goods_name column using ALTER TABLE.

3. Add a regular index on the shop_price column using CREATE INDEX.

4. Add a regular index on click_count, then remove it using both DROP INDEX and ALTER TABLE.

ALTER TABLE goods DROP COLUMN goods_desc;
ALTER TABLE goods ADD COLUMN click_count INT;
ALTER TABLE goods ADD UNIQUE INDEX UniqIdx(goods_name);
CREATE INDEX NormalIdx ON goods(shop_price);
CREATE INDEX NormalIdx2 ON goods(click_count);
DROP INDEX NormalIdx2 ON goods;
ALTER TABLE goods DROP INDEX NormalIdx2;