数据库第八次作业

无妄之灾|

于 2024-07-14 20:49:18 发布

阅读量163

点赞数 1

文章标签：数据库

本文链接：https://blog.csdn.net/2201_75408313/article/details/140422118

版权

一、备份与恢复作业：
创库,建表：

mysql> create database booksDB;
mysql> use booksDB;

mysql> create table books(
    -> bk_id INT NOT NULL PRIMARY KEY,
    -> bk_title VARCHAR(50) NOT NULL,
    -> copyright YEAR NOT NULL);
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE authors(
    -> auth_id INT NOT NULL PRIMARY KEY,
    -> auth_name VARCHAR(20),
    -> auth_gender CHAR(1));
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE authorbook(
    -> auth_id INT NOT NULL,
    -> bk_id INT NOT NULL);
Query OK, 0 rows affected (0.01 sec)

插入数据：

mysql> 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);
Query OK, 8 rows affected (0.00 sec)
Records: 8  Duplicates: 0  Warnings: 0

mysql> 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');
Query OK, 9 rows affected (0.00 sec)
Records: 9  Duplicates: 0  Warnings: 0

mysql>  INSERT INTO authorbook VALUES
    -> (1001, 11033), (1002, 11035), (1003, 11072), (1004, 11028),(1011, 11078), (1012, 11026), (1012, 11041), (1014, 11069);
Query OK, 8 rows affected (0.00 sec)
Records: 8  Duplicates: 0  Warnings: 0

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

mysqldump  -B -uroot -proot booksDB > /root/mysql/all_tables.sql

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

mysqldump  -uroot -proot booksDB books > /root/mysql/table_books.sql

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

mysqldump -uroot -p --databases booksDB test >/root/mysql/books_test_DB.sql
Enter password:******

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

[root@node1 ~]# mysql -u root -p booksDB</root/mysql/table_books.sql
Enter password: 
mysql> select * from books;
+-------+--------------------------+-----------+
| 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表

mysql> drop table books;
mysql> source /root/mysql/table_books.sql
mysql> select * from books;
+-------+--------------------------+-----------+
| 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 |
+-------+--------------------------+-----------+

二、索引作业：
创库,建表：

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);
Query OK, 0 rows affected (0.01 sec)

 mysql> create table category( 
     -> cat_id int primary key auto_increment, 
     -> cate_name varchar(20), 
     -> parent_id int default 0 );
Query OK, 0 rows affected (0.01 sec)

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

mysql> alter table goods drop column goods_desc;
mysql> alter table goods drop column goods_sn;
+-------------+-------------+------+-----+---------+----------------+
| Field       | Type        | Null | Key | Default | Extra          |
+-------------+-------------+------+-----+---------+----------------+
| goods_id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| goods_name  | varchar(20) | NO   | UNI | NULL    |                |
| cat_id      | int(11)     | NO   |     | 0       |                |
| brand_id    | int(11)     | NO   |     | 0       |                |
| shop_price  | float(6,2)  | NO   |     | 0.00    |                |
+-------------+-------------+------+-----+---------+----------------+


mysql> alter table goods add click_count int;
+-------------+-------------+------+-----+---------+----------------+
| Field       | Type        | Null | Key | Default | Extra          |
+-------------+-------------+------+-----+---------+----------------+
| goods_id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| goods_name  | varchar(20) | NO   |     | NULL    |                |
| cat_id      | int(11)     | NO   |     | 0       |                |
| brand_id    | int(11)     | NO   |     | 0       |                |
| goods_sn    | char(12)    | NO   |     | NULL    |                |
| shop_price  | float(6,2)  | NO   |     | 0.00    |                |
| click_count | int(11)     | YES  |     | NULL    |                |
+-------------+-------------+------+-----+---------+----------------+

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

mysql> alter table goods add unique index goods_name_index (goods_name(20));
mysql> show create table goods\G;
*************************** 1. row ***************************
       Table: goods
Create Table: CREATE TABLE `goods` (
  `goods_id` int(11) NOT NULL AUTO_INCREMENT,
  `goods_name` varchar(20) NOT NULL,
  `cat_id` int(11) NOT NULL DEFAULT '0',
  `brand_id` int(11) NOT NULL DEFAULT '0',
  `shop_price` float(6,2) NOT NULL DEFAULT '0.00',
  `click_count` int(11) DEFAULT NULL,
  PRIMARY KEY (`goods_id`),
  UNIQUE KEY `goods_name_index` (`goods_name`),
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4

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

mysql> create index g_price_index on goods(shop_price);
mysql> show create table goods\G;
*************************** 1. row ***************************
       Table: goods
Create Table: CREATE TABLE `goods` (
  `goods_id` int(11) NOT NULL AUTO_INCREMENT,
  `goods_name` varchar(20) NOT NULL,
  `cat_id` int(11) NOT NULL DEFAULT '0',
  `brand_id` int(11) NOT NULL DEFAULT '0',
  `shop_price` float(6,2) NOT NULL DEFAULT '0.00',
  `click_count` int(11) DEFAULT NULL,
  PRIMARY KEY (`goods_id`),
  UNIQUE KEY `goods_name_index` (`goods_name`),
  KEY `g_price_index` (`shop_price`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4

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

mysql> alter table goods add index c_l_index (click_count);
mysql> show create table goods\G;
*************************** 1. row ***************************
       Table: goods
Create Table: CREATE TABLE `goods` (
  `goods_id` int(11) NOT NULL AUTO_INCREMENT,
  `goods_name` varchar(20) NOT NULL,
  `cat_id` int(11) NOT NULL DEFAULT '0',
  `brand_id` int(11) NOT NULL DEFAULT '0',
  `shop_price` float(6,2) NOT NULL DEFAULT '0.00',
  `click_count` int(11) DEFAULT NULL,
  PRIMARY KEY (`goods_id`),
  UNIQUE KEY `goods_name_index` (`goods_name`),
  KEY `g_price_index` (`shop_price`),
  KEY `c_l_index` (`click_count`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4

mysql> alter table goods drop index c_l_index;
mysql> drop index c_l_index on goods;

mysql> show create table goods\G;
*************************** 1. row ***************************
       Table: goods
Create Table: CREATE TABLE `goods` (
  `goods_id` int(11) NOT NULL AUTO_INCREMENT,
  `goods_name` varchar(20) NOT NULL,
  `cat_id` int(11) NOT NULL DEFAULT '0',
  `brand_id` int(11) NOT NULL DEFAULT '0',
  `shop_price` float(6,2) NOT NULL DEFAULT '0.00',
  `click_count` int(11) DEFAULT NULL,
  PRIMARY KEY (`goods_id`),
  UNIQUE KEY `goods_name_index` (`goods_name`),
  KEY `g_price_index` (`shop_price`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4

无妄之灾|

关注

1
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
数据库第八次作业

4、在 click_count 上增加普通索引，然后再删除（分别使用drop index和alter table删除）1、删除 goods 表中的 goods_desc 字段及货号字段,并增加 click_count 字段。3、使用mysqldump备份booksDB和test数据库（test数据库自行准备）3、在 shop_price 列上加普通索引（用create index方式）5、进入数据库使用source命令还原第二题导出的book表。1、使用mysqldump命令备份数据库中的所有表。
复制链接

扫一扫