Mysql index and view_mysql

Mysql index and view bitsCN.com

Original table student field:

mysql> select column_name,data_type    -> from information_schema.columns    -> where table_name = 'student';+-------------+-----------+| column_name | data_type |+-------------+-----------+| stu_id      | int       || stu_name    | varchar   || stu_tel     | int       || stu_score   | int       |+-------------+-----------+4 rows in set (0.01 sec)

Raw data in the table:

mysql> select * from student;+--------+----------+---------+-----------+| stu_id | stu_name | stu_tel | stu_score |+--------+----------+---------+-----------+|      1 | a        |     151 |        60 ||      2 | b        |     152 |        61 ||      3 | c        |     153 |        62 ||      4 | d        |     154 |        63 |+--------+----------+---------+-----------+4 rows in set (0.00 sec)

IndexCreation format:

create [ 
 
   ] index 
  
    [ using {btree | hash} ] on table specification ( 
   
     [,
    
      ] )
     
       := unique | fulltext | spatial
      
       :=
       
         [asc | desc]
       
      
     
    
   
  
 

Create a simple index:

mysql> create index stu_index     -> on student(stu_id);Query OK, 0 rows affected (0.36 sec)Records: 0  Duplicates: 0  Warnings: 0

A non-unique index is created here. by default, asc is used for ascending order.

If the using declaration is not specified, mysql automatically creates a B-tree. So the above index is actually like this:

mysql> create index stu_index using btree     -> on student(stu_id asc);Query OK, 0 rows affected (0.19 sec)Records: 0  Duplicates: 0  Warnings: 0

Of course, btree indexes can be replaced with hash indexes.

You can also create a unique index for multiple columns:

mysql> create unique index stu_index using hash     -> on student(stu_id,stu_name);Query OK, 0 rows affected (0.19 sec)Records: 0  Duplicates: 0  Warnings: 0

Add an index:

mysql> alter table student    -> add unique index stu_index2    -> using hash (stu_tel);Query OK, 0 rows affected (0.36 sec)Records: 0  Duplicates: 0  Warnings: 0

Delete an index:

mysql> drop index stu_index on student;Query OK, 0 rows affected (0.22 sec)Records: 0  Duplicates: 0  Warnings: 0

Define indexes when creating a table:

mysql> create table student(    -> stu_id          int primary key,    -> stu_name        varchar(5) not null,    -> stu_tel         int(5) unique,    -> stu_score       int(2),    -> index stu_index(stu_id)    -> );

You only need to add the index creation statement at the end of the table.

ViewIt is a virtual table in the database. it stores not its own content, but is integrated from other tables through select. When the content of other tables changes, the content in the view changes accordingly. Under certain conditions, updating the view also changes the source table.

Create view:

Create [or replace] view
 
  
[
  
   
]
   
mysql> select * from view1;+--------+----------+---------+-----------+| stu_id | stu_name | stu_tel | stu_score |+--------+----------+---------+-----------+|      1 | a        |     151 |        60 ||      2 | b        |     152 |        61 ||      3 | c        |     153 |        62 ||      4 | d        |     154 |        63 |+--------+----------+---------+-----------+4 rows in set (0.00 sec)
   
You can also change the original column name when creating a view.
   
mysql> create or replace view view1(id,name,tel,score) as    -> (select * from student);Query OK, 0 rows affected (0.03 sec)
   
mysql> select * from view1;+----+------+------+-------+| id | name | tel  | score |+----+------+------+-------+|  1 | a    |  151 |    60 ||  2 | b    |  152 |    61 ||  3 | c    |  153 |    62 ||  4 | d    |  154 |    63 |+----+------+------+-------+4 rows in set (0.00 sec)
   
Delete View:
   
drop view view1;
   
 
  
    
[With [cascaded | local] check option] mysql> create view view1 as-> (select * from student); Query OK, 0 rows affected (0.16 sec)If the view already exists when you create a view, replace can be used to recreate the view.When a view can be updated, you can use the [with [cascaded | local] check option] option to check the update pair effectively.BitsCN.com