MySQL 数据库的备份与还原案例分享 2023.07.12

/** 素材一 备份与还原 **/

1 创建数据库booksDB
mysql> create database booksDB;
Query OK, 1 row affected (0.00 sec)2.1 创建booksDB表
mysql> use booksDB
Database changed
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)2.2 为booksDB表插入数据
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.02 sec)2.1 创建authors表
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.00 sec)2.2 为authors表插入数据
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)3.1 创建authorbook表
mysql> CREATE TABLE authorbook-> (->   auth_id  INT NOT NULL,->   bk_id   INT NOT NULL,->   PRIMARY KEY (auth_id, bk_id),->   FOREIGN KEY (auth_id) REFERENCES authors (auth_id),->   FOREIGN KEY (bk_id) REFERENCES books (bk_id)-> );
Query OK, 0 rows affected (0.00 sec)3.2 为authorbook表插入数据
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)

/** 要求 **/

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

[root@node01 ~]# mysqldump -u root -p123456 booksDB > /root/booksDB.sql[root@node01 ~]# ll
total 33560
-rw-r--r--  1 root root      3909 Jul 12 16:31 booksDB.sql

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

[root@node01 ~]# mysqldump -u root -p123456 booksDB books > /root/books.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.[root@node01 ~]# ll
total 570976
-rw-r--r--  1 root root      3909 Jul 12 16:31 booksDB.sql
-rw-r--r--  1 root root      2121 Jul 12 16:44 books.sql

    3、使用mysqldump备份booksDB和test数据库

[root@node01 ~]# mysqldump -u root -p123456 --database booksDB test > /root/booksDB.sql test.sql

    4、使用mysqldump备份服务器中的所有数据库

mysqldump -uroot -p --all-databases > /root/book3.sql

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

---

---

 mysql -uroot -p booksDB < /root/book1.sql

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

mysql> source /root/book1.sql

/** 素材二 索引 **/

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',`goods_sn` char(12) NOT NULL,`shop_price` float(6,2) NOT NULL DEFAULT '0.00',`good_desc` text,PRIMARY KEY (`goods_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8CREATE TABLE `category` (`cat_id` int(11) NOT NULL AUTO_INCREMENT,`cate_name` varchar(20) NOT NULL,`parent_id` int(11) NOT NULL DEFAULT '0',PRIMARY KEY (`cat_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

/** 要求 **/ 

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

#删除字段alter table goods drop good_desc,drop goods_id;#添加字段
alter table goods add click_count int;

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

alter table goods add unique only_name(goods_name);

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

alter table goods add unique only_name(goods_name);

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

 

mysql> create index index_c on goods(click_count);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0mysql> drop index index_c on goods;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0mysql> create index index_c on goods(click_count);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0mysql> alter table goods drop index index_c;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

/** 素材三 视图**/

 CREATE TABLE `Student` (`Sno` varchar(10) NOT NULL,`Sname` varchar(50) DEFAULT NULL,`Ssex` varchar(10) DEFAULT NULL,`Sage` int(11) DEFAULT NULL,`Sdept` varchar(50) DEFAULT NULL,PRIMARY KEY (`Sno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 CREATE TABLE `Course` (`Cno` varchar(10) NOT NULL,`Cname` varchar(50) DEFAULT NULL,PRIMARY KEY (`Cno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 CREATE TABLE `SC` (`Sno` varchar(10) NOT NULL,`Cno` varchar(10) NOT NULL,`Score` int(11) DEFAULT NULL,PRIMARY KEY (`Sno`,`Cno`),KEY `Cno` (`Cno`),CONSTRAINT `SC_ibfk_1` FOREIGN KEY (`Sno`) REFERENCES `Student` (`Sno`),CONSTRAINT `SC_ibfk_2` FOREIGN KEY (`Cno`) REFERENCES `Course` (`Cno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

/** 要求 **/

1、创建一视图 stu_info,查询全体学生的姓名，性别，课程名，成绩

create view stu_info(姓名,性别,课程名,成绩)as select st.Sname, st.Ssex, c.Cname, SC.Scorefrom Student st, Course c, SCwhere  st.Sno=SC.Sno and SC.Cno=c.Cno ;

2、删除视图 stu_info

drop view stu_info;