mysql 的基本操作

1、备份和还原

    创建表以及插入数据

 

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命令备份数据库中的所有表

mysqldump -uroot -p booksDB > /root/ssql/books.sql

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

mysqldump -uroot -p booksDB books > /root/ssql/book1.sql

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

 mysqldump -uroot -p --databases booksDB test > /root/ssql/book2.sql

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

mysqldump -uroot -p --all-databases > /root/ssql/book3.sql

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

 mysql -uroot -p booksDB < /root/ssql/book1.sql

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

mysql> source /root/ssql/book1.sql

2、索引

创建表

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删除）

#
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

 

3、视图

创建表

 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;