/** 素材一 备份与还原 **/
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=utf8
CREATE 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删除）
文章来源地址https://www.toymoban.com/news/detail-573142.html
mysql> create index index_c on goods(click_count);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> drop index index_c on goods;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> create index index_c on goods(click_count);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> 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.Score
from Student st, Course c, SC
where st.Sno=SC.Sno
and SC.Cno=c.Cno ;2、删除视图 stu_info
drop view stu_info;文章来源：https://www.toymoban.com/news/detail-573142.html
到了这里，关于MySQL 数据库的备份与还原案例分享 2023.07.12的文章就介绍完了。如果您还想了解更多内容，请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章，希望大家以后多多支持TOY模板网！
