数据库备份恢复和索引视图-CSDN博客

本文链接：https://blog.csdn.net/Liu_Cheng_H/article/details/131678918

样例表如下：


	/***************************样例表***************************/
	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
	);
	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);

	CREATE TABLE authors
	(
	  auth_id     INT NOT NULL PRIMARY KEY,
	  auth_name  VARCHAR(20),
	 auth_gender CHAR(1)
	);
	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');

	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)
	);

	INSERT INTO authorbook
	VALUES (1001, 11033), (1002, 11035), (1003, 11072), (1004, 11028),
	(1011, 11078), (1012, 11026), (1012, 11041), (1014, 11069);

	/***************************样例表***************************/

一、备份与还原

命令备份数据库中的所有表

[root@localhost ~]# mkdir /backup
[root@localhost ~]# mysqldump -u root -p booksDB > /backup/booksdb_20230712.sql
Enter password: 
[root@localhost ~]# 

[root@localhost ~]# cd /backup
[root@localhost backup]# vim booksdb_20230712.sql

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

[root@localhost ~]# mysqldump -u root -p booksDB books > /backup/books_20230712.sql
Enter password: 
[root@localhost ~]# 
[root@localhost ~]# cd /backup
[root@localhost backup]# ll
[root@localhost backup]# vim books_20230712.sql

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

[root@localhost ~]# mysqldump -u root -p --databases booksDB test > /backup/books_testDB_20230712
Enter password: 
[root@localhost ~]# 
[root@localhost ~]# cd /backup
[root@localhost backup]# ll
[root@localhost backup]# vim books_testDB_20230712

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

[root@localhost backup]# mysqldump -u root -p --all-databases > /backup/alldbinMySQL.sql
Enter password: 
[root@localhost backup]# 
[root@localhost ~]# cd /backup
[root@localhost backup]# ll

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

[root@localhost ~]# mysql -u root -p booksDB < /backup/books_20230712.sql 
Enter password: 
[root@localhost ~]#

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

mysql> source /backup/books_20230712.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, 1 warning (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.01 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected, 1 warning (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>

二、索引

-- 1、建立一个utf8编码的数据库test1
CREATE DATABASE test1 DEFAULT CHARACTER SET utf8;

-- 2、建立商品表goods和栏目表category
 CREATE TABLE goods(
goods_id INT(11) PRIMARY KEY 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,
goods_desc TEXT DEFAULT NULL
) ENGINE=MYISAM

DESC goods;

CREATE TABLE category(
cat_id INT(11) PRIMARY KEY AUTO_INCREMENT,
cate_name VARCHAR(20) NOT NULL,
parent_id INT(11) NOT NULL DEFAULT 0
) ENGINE=MYISAM

DESC category;

-- 3、删除 goods 表中的 goods_desc 字段及货号字段,并增加 click_count 字段
ALTER TABLE goods DROP goods_desc;
ALTER TABLE goods DROP goods_id;
ALTER TABLE goods ADD click_count INT;
DESC goods;

-- 4、在 goods_name 列上加唯一性索引（用alter table方式） 
ALTER TABLE goods ADD UNIQUE INDEX Uniqname (goods_name);

-- 5、在 shop_price 列上加普通索引（用create index方式）
CREATE INDEX SPriceIDX ON goods(shop_price); 

-- 6、在 click_count 上增加普通索引，然后再删除 （分别使用drop index和alter table删除）
CREATE INDEX CkcountIdx ON goods(click_count);
DROP INDEX CkcountIdx ON goods;
ALTER TABLE goods ADD INDEX CkcountIdx(click_count);
ALTER TABLE goods DROP INDEX CkcountIdx;

三、视图

创建学生表、课程表、学生选课表。

-- 学生表
CREATE TABLE Student(
Sno INT PRIMARY KEY,
Sname VARCHAR(20),
Ssex ENUM('M','F'),
Sage INT,
Sdept INT
);

-- 课程表
CREATE TABLE Course(
Cno INT PRIMARY KEY,
Cname VARCHAR(20)
);

-- 学生选课表
CREATE TABLE SC(
Sno INT,
Cno INT PRIMARY KEY,
Score INT
);

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

-- 1、创建一视图 stu_info,查询全体学生的姓名，性别，课程名，成绩。
CREATE VIEW stu_info(姓名,性别,课程名,成绩)
AS SELECT Sname,Ssex,Cname,Score 
FROM Student s,Course c,SC sc 
WHERE s.Sno=sc.`Sno` AND c.Cno=sc.`Cno`;

DESC stu_info;
SHOW TABLE STATUS LIKE 'stu_info';

2、删除视图 stu_info。

-- 2、删除视图 stu_info。
DROP VIEW IF EXISTS stu_info;