索引和视图

索引

索引是数据库中用来提高数据查询效率最常用的数据库对象，好比新华字典的音序表，通过音序表可以快速的查找内容。索引对数据表中一列或多列的值与记录行之间按照一定的顺序建立关系，以提高对数据表中数据的查询速度。根据索引实现语法不同，MySQL 中常见的索引大致分为 5 种，具体描述如下。

创建数据库时创建索引

上述语法格式中各选项的含义如下所示。（1）{INDEX | KEY}：INDEX 和 KEY 为同义词，表示索引，二者选一即可。（2）索引名：可选项，表示为创建的索引定义的名称，不使用该选项时，默认使用建立索引的字段表示，复合索引则使用第一个字段的名称作为索引名称。（3）索引类型：可选项，某些存储引擎允许在创建索引时指定索引类型，使用语法是 USING {BTREE | HASH}，不同的存储引擎支持的索引类型也不同。
（4）UNIQUE：可选项，表示唯一性索引。（5）FULLTEXT：表示全文索引。（6）SPATIAL：表示空间索引。

为了方便读者更好的理解索引的创建，下面通过案例演示如何在创建数据表 dept_index 时，创建单列的普通索引、唯一性索引、主键索引、全文索引和空间索引

CREATE TABLE dept_index(
	id INT,
	deptno  INT ,
	dname   VARCHAR(20),
	introduction VARCHAR(200),
	address GEOMETRY NOT NULL SRID 4326,
	PRIMARY KEY(id),        -- 创建主键索引
	UNIQUE INDEX (deptno),  -- 创建唯一性索引
	INDEX (dname),          -- 创建普通索引
	FULLTEXT (introduction),-- 创建全文索引
	SPATIAL INDEX (address) -- 创建空间索引
) ;

#若数据库emp_mgr存在则删除
DROP DATABASE IF EXISTS emp_mgr;

#创建数据库emp_mgr的数据
CREATE DATABASE emp_mgr CHARACTER SET utf8;

SHOW DATABASES;

#选择当前数据库为emp_mgr
USE emp_mgr;

/*6.1.2索引的创建  */
# 创建数据表dept_index时，创建单列的普通索引、唯一性索引、主键索引、全文索引
#-------------------------------------------
#字段    		类型    	索引
#-------------------------------------------
# id		 	int		primary key
# deptno		int		unique key
# dname 		varchar(20)	index
# introduction		varchar(200)	fulltext index
# ------------------------------------------

CREATE TABLE dept_index(
id INT,
deptno INT,
dname VARCHAR(20),
introduction VARCHAR(200),
PRIMARY KEY(id), #主键索引不允许起名字
UNIQUE KEY(deptno),
INDEX (dname),
FULLTEXT INDEX(introduction)
);

# 查看创建数据表dept_index的语句
SHOW CREATE TABLE dept_index;

# 创建数据表index_multi，在数据表中的id和name字段上建立索引名为multi的普通索引
#---------------------------------------
#字段    	类型    	属性
#---------------------------------------
# id		int		不为空
# name	 	varchar(20)	不为空
# score 	float
# --------------------------------------
CREATE TABLE index_multi(
id INT NOT NULL,
NAME VARCHAR(20) NOT NULL,
score FLOAT
);


ALTER TABLE index_multi ADD INDEX multi(id, NAME);

# 查看数据表index_multi的创建信息
SHOW CREATE TABLE index_multi;

# 创建新数据表dept_index02
#-----------------------------
#字段    	类型
#-----------------------------
# id		int
# deptno	int
# dname		varchar(20)
# introduction  varchar(200)
#-----------------------------
CREATE TABLE dept_index02(
id INT,
deptno INT,
dname VARCHAR(20),
introduction VARCHAR(200)
);



# 在数据表dept_index02中的id字段上，建立一个名称为unique_id的唯一性索引
ALTER TABLE dept_index02 ADD UNIQUE KEY unique_id(id);

# 查看数据表dept_index02的创建信息
SHOW CREATE TABLE dept_index02;

# 在dept_index02表中的deptno字段和dname字段上，创建一个名称为multi_index的复合索引

ALTER TABLE dept_index02 ADD UNIQUE KEY multi_index(deptno, dname);

# 查看数据表dept_index02的创建信息
SHOW CREATE TABLE dept_index02;

# 创建数据表dept_index03
#----------------------------
#字段    	类型
#----------------------------
# id		int
# deptno	int
# dname		varchar(20)
#----------------------------
CREATE TABLE dept_index03(
id INT,
deptno INT,
dname VARCHAR(20)
);

# 在数据表dept_index03中的id字段上，创建名称为index_id的唯一性索引
ALTER TABLE dept_index03 ADD UNIQUE KEY index_id(id);

# 查看数据表dept_index03的创建信息
SHOW CREATE TABLE dept_index03;

# 在dept_index03表中的deptno字段和dname字段上，创建一个名称为multi_index的复合唯一性索引
ALTER TABLE dept_index03 ADD UNIQUE KEY multi_index(deptno, dname);

# 查看数据表dept_index03的创建信息
SHOW CREATE TABLE dept_index03;

/*6.1.3索引的查看  */
# 查看数据表dept_index 中的索引
SHOW INDEX FROM dept_index;

# 往数据表dept_index中插入数据
DESC dept_index;

INSERT INTO dept_index VALUES(1, 10, '组织部', '这是一个政府部门');

SELECT * FROM dept_index;

/*6.1.4索引的删除  */
# 查看数据表dept_index的建表语句
SHOW CREATE TABLE dept_index;

# 删除索引introduction
ALTER TABLE dept_index DROP INDEX introduction;

# 删除数据表dept_index中名称为dname的索引
ALTER TABLE dept_index DROP INDEX dname;

# 删除数据表dept_index中名称为deptno的索引
ALTER TABLE dept_index DROP INDEX deptno;

# 删除数据表dept_index中的主键索引
ALTER TABLE dept_index DROP PRIMARY KEY;

# 查看数据表dept_index的建表语句
SHOW CREATE TABLE dept_index;

视图

准备工作：
1.导入atguigudb数据库
2.建立dbtest06数据库

CREATE DATABASE dbtest06 CHARACTER SET utf8;

3.切换当前数据库为dbtest06
USE dbtest06;

4.建立表
CREATE TABLE emps
AS
SELECT * FROM atguigudb.employees;

CREATE TABLE depts
AS
SELECT * FROM atguigudb.departments;

DESC emps;

---------------------------------------------------进行以下操作----------------------------------------------------

#1. 使用表emps创建视图employee_vu，其中包括姓名（LAST_NAME），员工号（EMPLOYEE_ID），部门
号(DEPARTMENT_ID)
CREATE VIEW employee_vu
AS
SELECT last_name, employee_id, department_id
FROM emps;

SHOW TABLES;

#2. 显示视图的结构
DESC employee_vu;

#3. 查询视图中的全部内容
SELECT *
FROM employee_vu;

#4. 将视图中的数据限定在部门号是80的范围内
ALTER VIEW employee_vu
AS
SELECT last_name, employee_id, department_id
FROM emps
WHERE department_id = 80;

SELECT *
FROM employee_vu;

#5.在表emps的employee_id 字段上添加主键
ALTER TABLE emps ADD PRIMARY KEY(employee_id);

#6.在表emps的email字段上添加唯一性约束
ALTER TABLE emps ADD UNIQUE KEY(email);

#7.在表emps的phone_number字段添加唯一性约束
ALTER TABLE emps ADD UNIQUE KEY(phone_number);

#8.在表depts中department_id字段上添加主键约束
ALTER TABLE depts ADD PRIMARY KEY(department_id);

#9.在表emps的department_id字段上添加外键，引用depts表中的department_id字段
ALTER TABLE emps ADD FOREIGN KEY(department_id) REFERENCES depts(department_id);

#10.在表emps的manager_id字段上添加外键，引用表emps的employee_id字段
ALTER TABLE emps ADD FOREIGN KEY(manager_id) REFERENCES emps(employee_id);



#11. 创建视图emp_v1,要求查询电话号码以‘011’开头的员工姓名和工资、邮箱
CREATE VIEW emp_v1
AS
SELECT last_name, salary, email
FROM emps
WHERE phone_number LIKE '011%';

SELECT *
FROM emp_v1;

#12. 要求将视图 emp_v1 修改为查询电话号码以‘011’开头的并且邮箱中包含 e 字符的员工姓名和邮箱、电话号码
ALTER VIEW emp_v1
AS
SELECT last_name, salary, email, phone_number
FROM emps
WHERE phone_number LIKE '011%' AND email LIKE '%e%';

SELECT *
FROM emp_v1;

#13. 向 emp_v1 插入一条记录，是否可以？
INSERT INTO emp_v1
VALUES('tom', 5000, 'tom@qq.com', '123456789');

DESC emps;

不能插入

#14. 修改emp_v1中员工的工资，每人涨薪1000
UPDATE emp_v1
SET salary = salary + 1000;

#15. 删除emp_v1中姓名为Olsen的员工
DELETE FROM emp_v1
WHERE last_name = 'Olsen';

#16. 创建视图emp_v2，要求查询部门的最高工资高于 12000 的部门id和其最高工资
CREATE VIEW emp_v2
AS
SELECT department_id, MAX(salary) AS max_sal
FROM emps
GROUP BY department_id
HAVING max_sal > 12000;

SELECT *
FROM emp_v2;

#17. 向 emp_v2 中插入一条记录，是否可以？
INSERT INTO emp_v2 VALUES(1000, 15000);

#18. 删除刚才的emp_v2 和 emp_v1
DROP VIEW emp_v2;

DROP VIEW emp_v1;

SHOW TABLES;

#19.删除表emps
DROP TABLE emps;


#20.删除表depts
DROP TABLE depts;

SHOW TABLES;

#21.删除数据库dbtest06;
DROP DATABASE dbtest06;

SHOW DATABASES;

索引和视图 ​

索引 ​

创建数据库时创建索引 ​

视图 ​

索引和视图

索引

创建数据库时创建索引

视图