第八次作业

一、备份与恢复作业

1使用mysqldump命令备份数据库中的所有表

C:\Users\Administrator>mysqldump -uroot -p123456 -B booksdb > E:\sqll\booksdb.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.

2、备份booksDB数据库中的books表

C:\Users\Administrator>mysqldump -uroot -p123456 booksdb books > E:\sqll\booksdb_1.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.

3、使用mysqldump备份booksDB和test数据库（test数据库自行准备）

C:\Users\Administrator>mysqldump -uroot -p123456 -B booksdb test > E:\sqll\booksdb_2.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.

4、使用mysql命令还原第二题导出的book表

C:\Users\Administrator>mysql -uroot -p123456 -hlocalhost -e "select * from booksdb.books"
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------+--------------------------+-----------+
| bk_id | bk_title                 | copyright |
+-------+--------------------------+-----------+
| 11026 | Guide to MySQL 5.5       |      2008 |
| 11028 | Learing C++              |      2005 |
| 11033 | Study Html               |      2011 |
| 11035 | How to use php           |      2003 |
| 11041 | Inside VC++              |      2011 |
| 11069 | MySQL professional       |      2009 |
| 11072 | Teach youself javascript |      2005 |
| 11078 | Learning MySQL           |      2010 |
+-------+--------------------------+-----------+

C:\Users\Administrator>mysql -uroot -p123456 -hlocalhost -e "drop table booksdb.books"
mysql: [Warning] Using a password on the command line interface can be insecure.

C:\Users\Administrator>mysql -uroot -p123456 booksdb < E:\sqll\booksdb_2.sql
mysql: [Warning] Using a password on the command line interface can be insecure.

C:\Users\Administrator>mysql -uroot -p123456 -hlocalhost -e "select * from booksdb.books"
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------+--------------------------+-----------+
| bk_id | bk_title                 | copyright |
+-------+--------------------------+-----------+
| 11026 | Guide to MySQL 5.5       |      2008 |
| 11028 | Learing C++              |      2005 |
| 11033 | Study Html               |      2011 |
| 11035 | How to use php           |      2003 |
| 11041 | Inside VC++              |      2011 |
| 11069 | MySQL professional       |      2009 |
| 11072 | Teach youself javascript |      2005 |
| 11078 | Learning MySQL           |      2010 |
+-------+--------------------------+-----------+

5、进入数据库使用source命令还原第二题导出的book表
 

C:\Users\Administrator>mysql -uroot -p123456 -hlocalhost -e "drop table booksdb.books"
mysql: [Warning] Using a password on the command line interface can be insecure.

C:\Users\Administrator>mysql -uroot -p123456 -hlocalhost -e "select * from booksdb.books"
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1146 (42S02) at line 1: Table 'booksdb.books' doesn't exist

C:\Users\Administrator>mysql -uroot -p123456 -hlocalhost booksdb -e "source E:\sqll\booksdb.sql"
mysql: [Warning] Using a password on the command line interface can be insecure.

C:\Users\Administrator>mysql -uroot -p123456 -hlocalhost -e "select * from booksdb.books"
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------+--------------------------+-----------+

| bk_id | bk_title | copyright |
| ----- | -------- | --------- |
|       |          |           |

+-------+--------------------------+-----------+

| 11026 | Guide to MySQL 5.5 | 2008 |
| ----- | ------------------ | ---- |
|       |                    |      |

| 11028 | Learing C++ | 2005 |
| ----- | ----------- | ---- |
|       |             |      |

| 11033 | Study Html | 2011 |
| ----- | ---------- | ---- |
|       |            |      |

| 11035 | How to use php | 2003 |
| ----- | -------------- | ---- |
|       |                |      |

| 11041 | Inside VC++ | 2011 |
| ----- | ----------- | ---- |
|       |             |      |

| 11069 | MySQL professional | 2009 |
| ----- | ------------------ | ---- |
|       |                    |      |

| 11072 | Teach youself javascript | 2005 |
| ----- | ------------------------ | ---- |
|       |                          |      |

| 11078 | Learning MySQL | 2010 |
| ----- | -------------- | ---- |
|       |                |      |

+-------+--------------------------+-----------+

二、索引作业

1、删除 goods 表中的 goods_desc 字段及货号字段,并增加 click_count 字段

mysql> alter table goods drop column goods_desc,drop column cat_id;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table goods add column click_count int ;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

2、在 goods_name 列上加唯一性索引（用alter table方式）：

mysql> alter table goods add unique index_goods_name(goods_name);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show index from goods;
+-------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name         | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| goods |          0 | PRIMARY          |            1 | goods_id    | A         |           0 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| goods |          0 | index_goods_name |            1 | goods_name  | A         |           0 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+-------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
2 rows in set (0.01 sec)

3、在 shop_price 列上加普通索引（用create index方式）

mysql> create index index_shop_price on goods(shop_price);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show index from goods;
+-------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+

| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
| ----- | ---------- | -------- | ------------ | ----------- | --------- | ----------- | -------- | ------ | ---- | ---------- | ------- | ------------- | ------- | ---------- |
|       |            |          |              |             |           |             |          |        |      |            |         |               |         |            |

+-------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+

| goods | 0    | PRIMARY | 1    | goods_id | A    | 0    | NULL | NULL |      | BTREE |      |      | YES  | NULL |
| ----- | ---- | ------- | ---- | -------- | ---- | ---- | ---- | ---- | ---- | ----- | ---- | ---- | ---- | ---- |
|       |      |         |      |          |      |      |      |      |      |       |      |      |      |      |

| goods | 0    | index_goods_name | 1    | goods_name | A    | 0    | NULL | NULL |      | BTREE |      |      | YES  | NULL |
| ----- | ---- | ---------------- | ---- | ---------- | ---- | ---- | ---- | ---- | ---- | ----- | ---- | ---- | ---- | ---- |
|       |      |                  |      |            |      |      |      |      |      |       |      |      |      |      |

| goods | 1    | index_shop_price | 1    | shop_price | A    | 0    | NULL | NULL |      | BTREE |      |      | YES  | NULL |
| ----- | ---- | ---------------- | ---- | ---------- | ---- | ---- | ---- | ---- | ---- | ----- | ---- | ---- | ---- | ---- |
|       |      |                  |      |            |      |      |      |      |      |       |      |      |      |      |

+-------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
3 rows in set (0.01 sec)

4、在 click_count 上增加普通索引，然后再删除 （分别使用drop index和alter table删除）

mysql> alter table goods add index index_click_count(click_count);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show index from goods;
+-------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name          | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| goods |          0 | PRIMARY           |            1 | goods_id    | A         |           0 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| goods |          0 | index_goods_name  |            1 | goods_name  | A         |           0 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| goods |          1 | index_shop_price  |            1 | shop_price  | A         |           0 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| goods |          1 | index_click_count |            1 | click_count | A         |           0 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
+-------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
4 rows in set (0.01 sec)

mysql> drop index index_click_count on goods;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> mysql> show index from goods;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'mysql> show index from goods' at line 1
mysql> show index from goods;
+-------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name         | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| goods |          0 | PRIMARY          |            1 | goods_id    | A         |           0 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| goods |          0 | index_goods_name |            1 | goods_name  | A         |           0 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| goods |          1 | index_shop_price |            1 | shop_price  | A         |           0 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+-------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
3 rows in set (0.00 sec)

mysql>
mysql> alter table goods add index index_click_count(click_count);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table goods drop index index_click_count;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0