数据库作业8

一、备份与恢复作业：
创库,建表：
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 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);

1、使用mysqldump命令备份数据库中的所有表
2、备份booksDB数据库中的books表
3、使用mysqldump备份booksDB和test数据库（test数据库自行准备）
4、使用mysql命令还原第二题导出的book表
5、进入数据库使用source命令还原第二题导出的book表

1、C:\Windows\System32>mysqldump -uroot -p20040830Nmx@ booksDB > book.sql
2、C:\Windows\System32>mysqldump -uroot -p20040830Nmx@ booksDB books> book1.sql
3、C:\Windows\System32>mysqldump -uroot -p20040830Nmx@ --databases booksDB test> bbt.sql
4、C:\Windows\System32>mysql -uroot -p20040830Nmx@ book<book.sql
5、mysql> source book1.sql
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 8 rows affected (0.00 sec)
Records: 8  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

二、索引作业：

创库,建表：
mysql> 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
);

mysql> create table category(
cat_id int primary key auto_increment,
cate_name varchar(20),
parent_id int default 0 );

1、删除 goods 表中的 goods_desc 字段及货号字段,并增加 click_count 字段
2、在 goods_name 列上加唯一性索引（用alter table方式）：
2、在 shop_price 列上加普通索引（用create index方式）
4、在 click_count 上增加普通索引，然后再删除 （分别使用drop index和alter table删除）

1、mysql>alter table goods drop goods_desc;
mysql> alter table goods drop goods_id;
mysql> alter table goods add click_count int;
2、mysql> alter table goods add unique index index_name(goods_name);
3、mysql> create index index_price on goods(shop_price);
4、mysql> create index index_count on goods( click_count);
mysql> drop index index_count on goods;
mysql> alter table goods drop index index_count;