MySQL数据库的备份与还原、视图基础操作

一、备份与还原

 

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

mysqldump -uroot -p'#$%#*@#^*' booksDB authorbook authors books > /backup/db/booksDB.spl

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

mysqldump -uroot -p*&*&……%￥#￥% booksDB books > /backup/db/booksDB_books.spl

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

mysqldump -uroot -p*0-#&**&%!@# --databases booksDB test > /backup/db/booksANDtest.spl

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

mysqldump -uroot -p'*&$!#*##' -A > /backup/db/All_db.spl

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

mysql -u root -p#%……&&—% booksDB < /backup/db/booksDB_books.spl

 

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

mysql> drop table books;
Query OK, 0 rows affected (0.01 sec)
 
mysql> source /backup/db/booksDB_books.spl

…………

Query OK, 8 rows affected (0.00 sec)

二、索引

 1、建立一个utf8编码的数据库test1

mysql> CREATE DATABASE test1 CHARACTER SET utf8 COLLATE utf8_general_ci;
Query OK, 1 row affected (0.00 sec)

2、建立商品表goods和栏目表category
    按如下表结构创建表：存储引擎engine myisam 字符集charset utf8

mysql> CREATE TABLE goods (
    ->     goods_id INT(11) 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,
    ->     good_desc TEXT
    -> ) ENGINE=MyISAM CHARSET=UTF8;
Query OK, 0 rows affected (0.00 sec)

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

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

mysql> alter table goods
    -> drop good_desc,
    -> drop goods_id
    -> ;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0
--
mysql> alter table goods
    -> ADD click_count INT;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

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

mysql> Alter Table goods
    -> Add UniQue inx_g_name(goods_name)
    -> ;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

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

mysql> Create INDEX index_shop_price ON goods(shop_price);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

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

mysql> Create INDEX inx_cli_count ON goods(click_count);
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> Drop INDEX inx_cli_count ON goods;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

--

mysql> Create INDEX inx_cli_count ON goods(click_count);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table goods
    -> Drop INDEX inx_cli_count;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

三、视图   

    学生表：Student (Sno, Sname, Ssex , Sage, Sdept)
    学号，姓名，性别，年龄，所在系 Sno为主键
    课程表：Course (Cno, Cname,)
    课程号，课程名 Cno为主键
    学生选课表：SC (Sno, Cno, Score)
    学号，课程号，成绩 Sno,Cno为主键    

mysql> CREATE TABLE Student (
    ->   Sno VARCHAR(10) PRIMARY KEY,
    ->   Sname VARCHAR(50),
    ->   Ssex VARCHAR(10),
    ->   Sage INT,
    ->   Sdept VARCHAR(50)
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE Course (
    ->   Cno VARCHAR(10) PRIMARY KEY,
    ->   Cname VARCHAR(50)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE SC (
    -> Sno VARCHAR(10),
    -> Cno VARCHAR(10),
    -> Score INT,
    -> PRIMARY KEY (Sno, Cno),
    -> FOREIGN KEY (Sno) REFERENCES Student(Sno),
    -> FOREIGN KEY (Cno) REFERENCES Course(Cno)
    -> );
Query OK, 0 rows affected (0.01 sec)

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

mysql> Create VIEW stu_info(姓名,性别,课程名,成绩)
    -> AS SELECT s.Sname, s.Ssex, c.Cname, SC.Score
    -> FROM Student s, Course c, SC
    -> WHERE s.Sno=SC.Sno 
    -> AND SC.Cno=c.Cno ;
Query OK, 0 rows affected (0.00 sec)

 2、删除视图 stu_info。 

mysql> Drop VIEW stu_info;
Query OK, 0 rows affected (0.00 sec)