Linux下数据库学习过程之索引视图（持续更新中）

1视图

创建视图的目的：使得查询操作更加便捷

视图的语法

CREATE
    [OR REPLACE]
    [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
    [DEFINER = user]
    [SQL SECURITY { DEFINER | INVOKER }]
    VIEW view_name [(column_list)]
    AS select_statement
    [WITH [CASCADED | LOCAL] CHECK OPTION]

视图的使用案例

查询每一个员工的信息，并附带部门名称
这里是将emp和dept两张表进行内连接查询，数据比较多，我在这里选择了两条数据显示。这样的查询可能会非常多，一条条写select语句显得代码量比较大，因此将这样的一个结果图对应的语句用视图来代替，那么再查询的时候，代码量将会小很多。

mysql> select emp_no,emp_name,emp_job,hire_date,salary,bonus,dept_name from employee as emp inner join department as dept on emp.dept_id=dept.dept_id where emp_no=1 or emp_no=2;
+--------+----------+---------+---------------------+--------+-------+-----------+
| emp_no | emp_name | emp_job | hire_date           | salary | bonus | dept_name |
+--------+----------+---------+---------------------+--------+-------+-----------+
|      1 | 张三     | enginer | 2018-09-09 00:00:00 |  10000 |  2000 | 科技部    |
|      2 | 李四     | 保洁    | 2024-01-01 00:00:00 |   6000 |  1000 | 后勤部    |
+--------+----------+---------+---------------------+--------+-------+-----------+

创建视图
mysql> create view emp_info (no,name,job,date,salary,bonus,department) as (select emp_no,emp_name,emp_job,hire_date,salary,bonus,dept_name from employee as emp inner join department as dept on emp.dept_id=dept.dept_id);

测试添加数据是否会在视图查询下显示，结果：可以使用视图查询到新增数据
mysql> insert into employee values(null,'樟脑丸','艺人','2024-06-07',10000,3000,1005);
mysql> select *from emp_info;   视图查询

可以进行视图的视图，也就是说视图的查询也可以作为下一个视图的查询内容，比较灵活 
mysql>  create view emp_info_view as select no,name,job from emp_info;
mysql> select *from emp_info_view;

可以将查询的结果排序 order by，在这里强调，如果使用多级视图，那么order by，会被最外边的orderby覆盖
mysql> select *from emp_info_view order by job;

视图可以与表一起使用
mysql> select name,dept_id,dept_name from emp_info inner join department on emp_info.department=department.dept_name;

修改视图
往视图里面插入数据，也会在表里面留痕，也就是说，插入数据到视图也会同时插入到表中，但是视图的语句包括两张及其以上的表的话，在视图插入数据将会失败。

删除视图

查询都有哪些视图
mysql>  show table status\G;
mysql> select *from information_schema.views\G;

2.索引

1.索引分类

按数据类型分类：B+tree索引，Hash索引，Full-text索引
按物理存储分类：聚簇索引（主键索引），二级索引（辅助索引）
按字段特性分类：主键索引，唯一索引，普通索引，前缀索引
按字段个数分类：单列索引，联合索引

2.索引的练习

前期插入数据
建立存储过程，给一张表添加两百万条数据
（1）这里使用Navicat连接linux下的mysql，使用查询执行下面这段代码，连接方法在我另一篇博客：https://blog.csdn.net/L18291789297/article/details/140051699

create procedure if not exists insert_proc()
begin 
        declare i int default 1;
        declare num int default 2000000;
        while i<num
        do 
                insert into index_1 values(i,floor(i + rand()*i),md5(i));
                set i=i+1;
        end while;
        commit;
end;
执行

转到linux的mysql中的mydb2中（具体需要的数据库）

mysql> call insert_proc();

（2）在Linux下直接使用sql语句创建两百万条数据

mysql> delimiter $$   //定界符。保证里面的内容为一个整体，这里使用$$,其他的也可以
mysql> create procedure insert_proc2()
    -> begin 
    ->   declare i int default 1;
    ->   declare max int default 2000000;
    ->   while i <= max
    ->   do 
    ->     insert into index_1 values(i,floor(i + rand()*i),md5(i));
    ->     set i=i+1;
    ->   end while;
    ->   commit;
    -> end;
    -> $$
mysql> delimiter ;改回定界符为；

<1.建立索引的语法

CREATE TABLE 表名 ( 属性名 数据类型 [完整性约束条件],
属性名 数据类型 [完整性约束条件],
…
属性名 数据类型
[UNIQUE | FULLTEXT | SPATIAL] INDEX | KEY
[别名](属性名1 [(长度)] [ASC | DESC])
);

（1）在建表的时候建立索引

创建表

create table index_1(
id int,
num int,
content varchar(128)
);

创建索引，普通索引（主键索引）

mysql> create table new_index_tb(
    -> id int primary key ,
    -> name varchar(64) not null,
    -> index name_u (name(5) desc )
    -> );
    mysql> create table new_index_tb(
    -> id int primary key ,
    -> name varchar(64) not null,
    -> key name_u (name(5) desc )
    -> );

唯一索引

mysql> create table new_index_tb(
    -> id int primary key ,
    -> name varchar(64) not null,
    -> unique index name_u (name(5) desc )
    -> );
mysql> create table new_index_tb(
    -> id int primary key ,
    -> name varchar(64) not null,
    -> unique key name_u (name(5) desc )
    -> );

（2）直接创建索引

mysql> create index index_id on index_1(id);
在建立索引后发现，用mysql> select count(*) from index_1;查询效率在创建之前比创建之后更高，这是因为在插入数据后，在没有创建索引前，数据库会自动创建一个隐藏的索引，与表结构无关。

（3）用修改语句创建索引

（4）删除索引

注意：
1.当为一张表建立主键时，会自动建立索引
2.设置唯一键约束时也会自动建立索引

mysql使用索引场景

1、支撑where条件的查询
2、可以支撑多个where条件，并且会优先匹配规模最小的where
即假如where1匹配1000行，而where2匹配100行，那么优化器就会选择where2优先匹配，在匹配中的100行里在匹配符合where1条件的数据
3、如果是联合索引，那么必然遵守最左前缀匹配原则，即有索引1，索引2，索引3，在匹配时只给出索引2，索引3字段将会匹配失败，只有给出包含索引1字段的语句时才会成功。在匹配时给出索引1，索引3，则匹配成功。但只有索引1生效，即最左前缀匹配原则

3.聚簇索引和非聚簇索引

（1）聚簇索引，顺序IO，存储的物理地址连续
（2）非聚簇索引，随机IO，存储的物理地址不连续