一、备份与还原
1.创建库表并插入数据:
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);
2.数据备份与还原:
1、使用mysqldump命令备份数据库中的所有表
[root@localhost ~]# mysqldump -uroot -p123456 booksDB > /booksDB.sql
2、备份booksDB数据库中的books表
[root@localhost ~]# mysqldump -uroot -p123456 booksDB books >/booksDB_books.sql
3、使用mysqldump备份booksDB和test数据库
[root@localhost ~]# mysqldump -uroot -p123456 booksDB test > /booksD_test.sql
4、使用mysqldump备份服务器中的所有数据库
[root@localhost ~]# mysqldump -uroot -p123456 --all-databases > all_databases.sql
5、使用mysql命令还原第二题导出的book表
[root@localhost ~]# mysql -uroot -p123456 booksDB < /booksDB_books.sql
6、进入数据库使用source命令还原第二题导出的book表
mysql> source /booksDB_books.sql
二、索引
1.创建库表并插入数据:
1、建立一个utf8编码的数据库test1
2、建立商品表goods和栏目表category
按如下表结构创建表:存储引擎engine myisam 字符集charset utf8
+------------+-------------+------+-----+---------+----------------+
| 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 | |
| goods_desc | text | YES | | NULL | |
+------------+-------------+------+-----+---------+----------------+
+-----------+-------------+------+-----+---------+----------------+
| 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 | |
+-----------+-------------+------+-----+---------+----------------+
create table goods(good_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,
goods_desc text null)
engine=myisam,charset=utf8;
create table category(cat_id int(11) primary key auto_increment,
cate_name varchar(20) not null,
parent_id int(11) not null)
engine=myisqm,charset=utf8;
2.表操作以及索引创建:
3、删除 goods 表中的 goods_desc 字段及货号字段,并增加 click_count 字段
4、在 goods_name 列上加唯一性索引(用alter table方式)
5、在 shop_price 列上加普通索引(用create index方式)
6、在 click_count 上增加普通索引,然后再删除 (分别使用drop index和alter table删除)
3.
alter table goods drop goods_desc;
alter table goods add click_count;
4.
alter table goods add unique index p1 (goods_name);
5.
create index p2 on goods(shop_price);
6.
create index p2 on goods(shop_price);
drop index p3 on goods;
三、视图
1.创建表:
1.学生表:Student (Sno, Sname, Ssex , Sage, Sdept)
学号,姓名,性别,年龄,所在系 Sno为主键
2.课程表:Course (Cno, Cname,)
课程号,课程名 Cno为主键
3.学生选课表:SC (Sno, Cno, Score)
学号,课程号,成绩 Sno,Cno为主键
1.
CREATE TABLE `PR`.`Student` (
`Sno` int NOT NULL,
`Sname` varchar(20) NULL,
`Ssex` varchar(10) NULL,
`Sage` int NULL,
`Sdept` varchar(50) NULL,
PRIMARY KEY (`Sno`)
);
2.
CREATE TABLE `PR`.`Course` (
`Cno` int NOT NULL,
`Cname` varchar(50) NULL,
PRIMARY KEY (`Cno`)
);
3.
CREATE TABLE `PR`.`SC` (
`Sno` int NOT NULL,
`Cno` int NOT NULL,
`Score` int NULL,
PRIMARY KEY (`Sno`, `Cno`)
);
2.创建与删除视图:
1、创建一视图 stu_info,查询全体学生的姓名,性别,课程名,成绩。
2、删除视图 stu_info。
1.
create view stu_info(姓名,性别,课程,成绩) as select
studeent.Sname,student.Ssex,
Course.Cname,Sc.Score from Student,Coursee,SC
where Student.Sno=SC.Sno and Course.Cno=SC.Cno;
2.
drop view if exists stu_info;