一、备份与还原
/***************************样例表***************************/
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);
/***************************样例表***************************/
1、使用mysqldump命令备份数据库中的所有表
[root@localhost ~]# mysqldump -uroot -p密码 booksDB > db/booksDB
2、备份booksDB数据库中的books表
[root@localhost ~]# mysqldump -u root -p密码 booksDB books > db/books
3、使用mysqldump备份booksDB和test数据库
[root@localhost ~]# mysqldump -u root -p密码 --databases booksDB test > db/two
4、使用mysqldump备份服务器中的所有数据库
[root@localhost ~]# mysqldump -uroot -p密码 -A > db/all
5、使用mysql命令还原第二题导出的books表
先删除table
[root@localhost ~]# mysql -uroot -p密码 booksDB < db/books
查看
6、进入数据库使用source命令还原第二题导出的books表
#其它步骤一样
mysql> source db/books;
二、索引
1、建立数据库test1
2、建立商品表goods和栏目表category
按如下表结构创建表:存储引擎engine myisam 字符集charset utf8
mysql> desc goods;
+------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+----------------+
| goods_id | int(11) | NO | PRI | NULL | auto_increment |
| goods_name | varchar(20) | NO | | | |
| cat_id | int(11) | NO | | 0 | |
| brand_id | int(11) | NO | | 0 | |
| goods_sn | char(12) | NO | | | |
| shop_price | float(6,2) | NO | | 0.00 | |
| good_desc | text | YES | | NULL | |
+------------+-------------+------+-----+---------+----------------+
7 rows in set (0.00 sec)
mysql> 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.00,
-> good_desc text default null)engine=myisam;
mysql> desc category;
+-----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+----------------+
| cat_id | int(11) | NO | PRI | NULL | auto_increment |
| cate_name | varchar(20) | NO | | | |
| parent_id | int(11) | NO | | 0 | |
+-----------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
mysql> 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 );
3、删除 goods 表中的 goods_desc 字段及货号字段,并增加 click_count 字段
mysql> alter table goods drop column goods_sn,drop column good_desc;
mysql> alter table goods add click_count int;
4、在 goods_name 列上加唯一性索引(用alter table方式)
mysql> alter table goods add unique index_name(goods_name);
5、在 shop_price 列上加普通索引(用create index方式)
mysql> create index index_price on goods(shop_price);
6、在 click_count 上增加普通索引,然后再删除 (分别使用drop index和alter table删除)
mysql> create index index_click on goods(click_count);
#drop index删除
mysql> drop index index_click on goods;
#alter table删除
mysql> alter table goods drop index index_click;
三、视图
学生表:Student (Sno, Sname, Ssex , Sage, Sdept)
依次为学号,姓名,性别,年龄,所在系, Sno为主键
mysql> CREATE TABLE student (
-> sno INT PRIMARY KEY,
-> sname VARCHAR(255),
-> ssex VARCHAR(10),
-> sage INT,
-> sdept VARCHAR(255)
-> );
insert into student values(1,'bob','m',20,'人工智能');
insert into student values( 2,'harry','m',22,'电气信息');
insert into student values( 3,'natasha','f',23,'电气信息');
insert into student values( 4,'sarah','m',21,'人文');
insert into student values( 5,'manalo','f',21,'艺术');
课程表:Course (Cno, Cname,)
课程号,课程名, Cno为主键
mysql> create table cource(
-> cno int primary key,
-> cname varchar(255));
insert into cource values( 10,'JAVA');
insert into cource values( 11,'英语');
insert into cource values( 12,'近代史');
insert into cource values( 13,'C++');
insert into cource values( 14,'毛概');
学生选课表:SC (Sno, Cno, Score)
学号,课程号,成绩 Sno,Cno为主键
mysql> create table sc( sno int , cno int primary key, score int);
insert into sc values(1,10,78.5);
insert into sc values(2,11,82.5);
insert into sc values(3,14,90.0);
insert into sc values(4,13,98.5);
insert into sc values(5,12,75.5);
1、创建一视图 stu_info,查询全体学生的姓名,性别,课程名,成绩。文章来源:https://www.toymoban.com/news/detail-590560.html
mysql> CREATE VIEW `stu_info` (`姓名`, `性别`, `课程名`, `成绩`) AS SELECT stu.sname, stu.ssex, cou.cname, sc.score FROM student stu, cource cou, sc WHERE stu.sno = sc.sno and cou.cno = sc.cno;
2、删除视图 stu_info。文章来源地址https://www.toymoban.com/news/detail-590560.html
mysql> drop view stu_info;
到了这里,关于【MySQL备份与还原、索引、视图】练习的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!