数据库的索引、视图、数据库备份
一、索引
1.什么是索引
索引是由数据库表中一列或多列组合而成，其作用是提高对表中数据的查询速度。
类似于图书的目录，方便快速定位，寻找指定的内容。
2.索引的优缺点
优点：提高查询数据的速度。
缺点：创建和维护索引的时间增加了，同时占用硬盘空间。
3.索引分类
1) 普通索引：是最基本的索引，它没有任何限制；
2) 唯一索引：与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一；
3) 主键索引：是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值；
4) 组合索引：指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用组合索引时遵循最左前缀集合；
5) 全文索引：使用FULLTEXT参数可以设置，全文索引只能创建在CHAR,VARCHAR,TEXT类型的字段上。主要作用是提高查询较大字符串类型的速度；只有MyISAM引擎支持该索引，MySQL默认引擎不支持；mysql5.7+
4.如何创建索引
CREATE [UNIQUE|FULLTEXT] INDEX 索引名 ON 表名(字段名[(长度)][ASC|DESC])
5.如何修改索引
ALTER TABLE 表名 ADD [UNIQUE|FULLTEXT] INDEX 索引名(字段名[(长度)][ASC|DESC])
6.如何删除索引
DROP INDEX 索引名 ON 表名
练习：
#全文索引
通过使用索引,查询速度显著提升
ALTER TABLE t_p1_blog ADD FULLTEXT ft_blog_summary_content (title,summary,content)
select * from t_p1_blog where match (title,summary,content) against (‘ssm Linux’);
#删除索引 DROP INDEX 索引名 ON 表名
DROP INDEX ft_blog_summary_content ON db_xiaoli.t_p1_blog ;
#普通索引 通过使用索引,查询速度显著提升
create index idx_moduleid on t_log(moduleid);
select * from t_log where moduleid=‘100310’;
#组合索引
create index idx_group_1 on t_log(id,moduleid,url);
DROP index idx_group_1 on t_log;
select * from t_log where
id=‘d7a46a9d60bf11e99fe528d24413c293’
and url=’/log/queryLogPager’
and moduleid=‘110401’;
二、数据库备份
导出整个数据库结构和数据
mysqldump -h localhost -uroot -p123456 database > dump.
mysqldump -h localhost -uroot -p123456 db_xiaoli > d:/t_log_001.sql
导出单个数据表结构和数据
mysqldump -h localhost -uroot -p123456 database table > dump.sql
mysqldump -h localhost -uroot -p123456 db_xiaoli t_log > d:/t_log_002.sql
mysqldump -h localhost -uroot -p123456 db_xiaoli t_log > t_log_003.sql
导出整个数据库结构（不包含数据）
mysqldump -h localhost -uroot -p123456 -d database > dump.sql
导出单个数据表结构（不包含数据）
mysqldump -h localhost -uroot -p123456 -d database table > dump.sql
三、视图
含义：虚拟表，和普通表一样使用
mysql5.1版本出现的新特性，是通过表动态生成的数据
比如：舞蹈班和普通班级的对比
创建语法的关键字 是否实际占用物理空间 使用
视图 create view 只是保存了sql逻辑 增删改查，只是一般不能增删改
表 create table 保存了数据 增删改查
语法：create view 视图名
as
查询语句;
创建视图
#1.查询姓名中包含a字符的员工名、部门名和工种信息
#①创建
CREATE VIEW myv1
AS
SELECT last_name,department_name,job_title
FROM t_mysql_employees e
JOIN t_mysql_departments d ON e.department_id = d.department_id
JOIN t_mysql_jobs j ON j.job_id = e.job_id;
#②使用
SELECT * FROM myv1 WHERE last_name LIKE ‘%a%’;
视图的修改
方式一：
create or replace view 视图名
as
查询语句;
CREATE OR REPLACE VIEW emp_v2
AS
SELECT AVG(salary),job_id
FROM t_mysql_employees
GROUP BY job_id;
SELECT * FROM emp_v2
方式二：
语法：
alter view 视图名
as
查询语句;
ALTER VIEW emp_v2
AS
SELECT * FROM t_mysql_employees;
三、删除视图
语法：drop view 视图名,视图名,…;
DROP VIEW emp_v1,emp_v2,myv3;
四、查看视图
DESC myv3;
SHOW CREATE VIEW emp_v2;
五、视图的更新
创建
CREATE OR REPLACE VIEW myv1
AS
SELECT last_name,email,salary12(1+IFNULL(commission_pct,0)) “annual salary”
FROM t_mysql_employees;
修改
CREATE OR REPLACE VIEW myv1
AS
SELECT last_name,email
FROM employees;
SELECT * FROM myv1;
SELECT * FROM t_mysql_employees;
#1.插入
INSERT INTO myv1 VALUES(‘张飞’,‘[email protected]’);
#2.修改
UPDATE myv1 SET last_name = ‘张无忌’ WHERE last_name=‘张飞’;
#3.删除
DELETE FROM myv1 WHERE last_name = ‘张无忌’;
#一、创建视图emp_v1,要求查询电话号码以‘011’开头的员工姓名和工资、邮箱
CREATE OR REPLACE VIEW emp_v1
AS
SELECT last_name,salary,email
FROM t_mysql_employees
WHERE phone_number LIKE ‘011%’;
#二、创建视图emp_v2，要求查询部门的最高工资高于12000的部门信息
CREATE OR REPLACE VIEW emp_v2
AS
SELECT MAX(salary) mx_dep,department_id
FROM t_mysql_employees
GROUP BY department_id
HAVING MAX(salary)>12000;
SELECT d.*,m.mx_dep
FROM t_mysql_departments d
JOIN emp_v2 m
ON m.department_id = d.department_id;