下文給大家?guī)黻P(guān)于MySQL數(shù)據(jù)庫的索引和視圖具體是怎么樣的，感興趣的話就一起來看看這篇文章吧，相信看完Mysql數(shù)據(jù)庫的索引和視圖具體是怎么樣的對大家多少有點(diǎn)幫助吧。
創(chuàng)新互聯(lián)堅(jiān)持“要么做到，要么別承諾”的工作理念，服務(wù)領(lǐng)域包括：網(wǎng)站設(shè)計(jì)、成都網(wǎng)站建設(shè)、企業(yè)官網(wǎng)、英文網(wǎng)站、手機(jī)端網(wǎng)站、網(wǎng)站推廣等服務(wù)，滿足客戶于互聯(lián)網(wǎng)時(shí)代的墨脫網(wǎng)站設(shè)計(jì)、移動媒體設(shè)計(jì)的需求，幫助企業(yè)找到有效的互聯(lián)網(wǎng)解決方案。努力成為您成熟可靠的網(wǎng)絡(luò)建設(shè)合作伙伴！
數(shù)據(jù)庫的索引與書籍中的目錄類似
在一本書中，無需閱讀整本書，利用目錄就可以快速查找所需信息
書中的目錄是一個(gè)詞語列表，其中注明了包含各個(gè)詞的頁碼
數(shù)據(jù)庫索引
在數(shù)據(jù)庫中，索引數(shù)據(jù)庫程序無需對整個(gè)表進(jìn)行掃描，就可以在其中找到所需數(shù)據(jù)
數(shù)據(jù)庫中的索引是某個(gè)表中一列或若干列的集合，以及物理標(biāo)識這些值的數(shù)據(jù)頁的邏輯指針清單
設(shè)置了合適的索引之后，數(shù)據(jù)庫利用葛總快速的定位技術(shù)，能夠大大加快查詢速率
特別是當(dāng)表很大時(shí)，或者查詢涉及到多個(gè)表時(shí)，使用索引可使查詢加快成千倍
可以降低數(shù)據(jù)庫的IO成本，并且索引還可以降低數(shù)據(jù)庫的排序成本
通過創(chuàng)建唯一索引保證數(shù)據(jù)表數(shù)據(jù)的唯一性
可以加快表與表之間的連接
在使用分組和排序時(shí)，可大大減少分組和排序時(shí)間
普通索引
這是最基本的索引類型，而且它沒有唯一性的限制
唯一性索引
索引的列的所有值都只能出現(xiàn)一次，即必須唯一
主鍵
主鍵是一種唯一性索引，但它必須指定為“PRIMARY KEY”
全文索引
全文索引可以在VARCHAR或者TEXT類型的列上創(chuàng)建
表的主鍵，外鍵必須有索引
數(shù)據(jù)量超過300行的表應(yīng)該有索引
經(jīng)常與其他表進(jìn)行連接的表，在連接字段上應(yīng)該建立索引
唯一性太差的字段不適合建立索引
更新太頻繁的字段不適合創(chuàng)建索引
經(jīng)常出現(xiàn)在Where字句中的字段，特別是大表的字段，應(yīng)該建立索引
索引應(yīng)該建在選擇性高的字段上
索引應(yīng)該建在小字段上，對于大的文本字段甚至超長字段，不要建索引
create index 索引名字 on tablename(列的列表
重慶分公司
重慶分公司
create index 索引名字 on tablename(列的列表）
[root@localhost ~]# mysql -u root -p #進(jìn)入mysql數(shù)據(jù)庫
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.20 Source distribution
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases; #查看所有數(shù)據(jù)庫
mysql> use school; #創(chuàng)建school數(shù)據(jù)庫
Database changed
mysql> create table info ( #創(chuàng)建數(shù)據(jù)表
id int(4) not null primary key auto_increment, #int類型整型為4，不能為空，主鍵索引，數(shù)值自然增長
name varchar(10) not null, #varchar字符串不能為空
address varchar(50) default 'nanjing', #字符串默認(rèn)是nanjing
age int(3) not null); #int類型
Query OK, 0 rows affected (0.05 sec)
mysql> insert into info (name,address,age) values ('zhangsan','beijing',20),('lisi','shanghai',22);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * form info; #查看數(shù)據(jù)表
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'form info' at line 1
mysql> select * from info;
+----+----------+----------+-----+
| id | name | address | age |
+----+----------+----------+-----+
| 1 | zhangsan | beijing | 20 |
| 2 | lisi | shanghai | 22 |
+----+----------+----------+-----+
2 rows in set (0.00 sec)
mysql> desc info; #查看表結(jié)構(gòu)
+---------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+----------------+
| id | int(4) | NO | PRI | NULL | auto_increment |
| name | varchar(10) | NO | | NULL | |
| address | varchar(50) | YES | | nanjing | |
| age | int(3) | NO | | NULL | |
+---------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
mysql> create index index_age on info (age); #創(chuàng)建索引固定搭配，index_age索引作用在info表中的age列
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show index from info; #查看數(shù)據(jù)表中的索引
+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| info | 0 | PRIMARY | 1 | id | A | 2 | NULL | NULL | | BTREE | | |
| info | 1 | index_age | 1 | age | A | 2 | NULL | NULL | | BTREE | | |
+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)
mysql> drop index index_age on info; #刪除數(shù)據(jù)表中的index_age的索引
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show index from info;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| info | 0 | PRIMARY | 1 | id | A | 2 | NULL | NULL | | BTREE | | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)create unique index 索引的名字 on tablename (列的列表)
mysql> create unique index unique_name on info (name); #創(chuàng)建唯一索引，create unique index固定搭配起個(gè)名字，作用在info的name列中 Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show index from info; +-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | info | 0 | PRIMARY | 1 | id | A | 2 | NULL | NULL | | BTREE | | | | info | 0 | unique_name | 1 | name | A | 2 | NULL | NULL | | BTREE | | | +-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 2 rows in set (0.00 sec) mysql> alter table info add unique index index_name (name); #另一種方法alter table info add 唯一索引 索引名字，作用在name列名中 Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show index from info; +-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | info | 0 | PRIMARY | 1 | id | A | 2 | NULL | NULL | | BTREE | | | | info | 0 | index_name | 1 | name | A | 2 | NULL | NULL | | BTREE | | | +-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 2 rows in set (0.00 sec)
1.創(chuàng)建表的時(shí)候 直接定義
2.create index 索引名稱 on 表名 （列名1，列名2）；列名可以是多個(gè)
3.alter table 表名 add index 索引名稱 （列名）；
mysql> alter table info add unique index index_name (name); #另一種方法alter table info add 唯一索引 索引名字，作用在name列名中 Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show index from info; +-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | info | 0 | PRIMARY | 1 | id | A | 2 | NULL | NULL | | BTREE | | | | info | 0 | index_name | 1 | name | A | 2 | NULL | NULL | | BTREE | | | +-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 2 rows in set (0.00 sec) mysql> create table user ( -> id int(4) not null primary key auto_increment, -> name varchar(10) not null, -> score decimal not null, -> hobby int(2) not null default '1', -> index index_score (score)); #在創(chuàng)建表的時(shí)候可以直接定義索引 Query OK, 0 rows affected (0.05 sec) mysql> desc user; +-------+---------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+---------------+------+-----+---------+----------------+ | id | int(4) | NO | PRI | NULL | auto_increment | | name | varchar(10) | NO | | NULL | | | score | decimal(10,0) | NO | MUL | NULL | | | hobby | int(2) | NO | | 1 | | +-------+---------------+------+-----+---------+----------------+ 4 rows in set (0.00 sec)
#要對應(yīng)著列名填寫數(shù)據(jù)
mysql> insert into user (name,score,hobby) values ('test01',88,1),('stu01',99,2),('wangwu',77,3);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from user;
+----+--------+-------+-------+
| id | name | score | hobby |
+----+--------+-------+-------+
| 1 | test01 | 88 | 1 |
| 2 | stu01 | 99 | 2 |
| 3 | wangwu | 77 | 3 |
+----+--------+-------+-------+
3 rows in set (0.00 sec)mysql> create table hob (
-> id int (2) not null primary key,
-> hob_name varchar(10) not null);
Query OK, 0 rows affected (0.04 sec)
mysql> desc hob;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id | int(2) | NO | PRI | NULL | |
| hob_name | varchar(10) | NO | | NULL | |
+----------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> insert into hob (id,hob_name) values (1,'看書'),(2,'運(yùn)動'),(3,'跑步');
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from hob;
+----+----------+
| id | hob_name |
+----+----------+
| 1 | 看書 |
| 2 | 運(yùn)動 |
| 3 | 跑步 |
+----+----------+
3 rows in set (0.00 sec)
mysql> insert into user (name,score,hobby) values ('zhaoliu',66,2); #在user表中再插一行數(shù)據(jù)
Query OK, 1 row affected (0.00 sec)
mysql> select * from user inner join hob on user.hobby=hob.id; #把user這種表加入到hob表中，user的hobby對應(yīng)hob里面的id
+----+---------+-------+-------+----+----------+
| id | name | score | hobby | id | hob_name |
+----+---------+-------+-------+----+----------+
| 1 | test01 | 88 | 1 | 1 | 看書 |
| 2 | stu01 | 99 | 2 | 2 | 運(yùn)動 |
| 3 | wangwu | 77 | 3 | 3 | 游 |
| 4 | zhaoliu | 66 | 2 | 2 | 運(yùn)動 |
+----+---------+-------+-------+----+----------+
4 rows in set (0.00 sec)mysql> select user.name,hob.hob_name from user inner join hob on user.hobby=hob.id; +---------+----------+ | name | hob_name | +---------+----------+ | test01 | 看書 | | stu01 | 運(yùn)動 | | wangwu | 跑步 | | zhaoliu | 運(yùn)動 | +---------+----------+ 4 rows in set (0.00 sec)
mysql> select u.name,h.hob_name from user u inner join hob h on u.hobby=h.id; +---------+----------+ | name | hob_name | +---------+----------+ | test01 | 看書 | | stu01 | 運(yùn)動 | | wangwu | 跑步 | | zhaoliu | 運(yùn)動 | +---------+----------+ 4 rows in set (0.00 sec)
create view 視圖名 as
視圖建立一個(gè)映射，把結(jié)果呈現(xiàn)出來，真實(shí)的數(shù)據(jù)還在原有表中
mysql> create view view_user as select u.name,h.hob_name from user u inner join hob h on u.hobby=h.id; Query OK, 0 rows affected (0.00 sec) mysql> select * from view_user; +---------+----------+ | name | hob_name | +---------+----------+ | test01 | 看書 | | stu01 | 運(yùn)動 | | wangwu | 跑步 | | zhaoliu | 運(yùn)動 | +---------+----------+ 4 rows in set (0.00 sec)
mysql> create fulltext index full_addr on info (address); Query OK, 0 rows affected, 1 warning (0.21 sec) Records: 0 Duplicates: 0 Warnings: 1 mysql> show index from info; +-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | info | 0 | PRIMARY | 1 | id | A | 2 | NULL | NULL | | BTREE | | | | info | 0 | index_name | 1 | name | A | 2 | NULL | NULL | | BTREE | | | | info | 1 | full_addr | 1 | address | NULL | 2 | NULL | NULL | YES | FULLTEXT | | | +-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 3 rows in set (0.00 sec)
mysql> create index index_name_score on user (name,score); Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show index from user; +-------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | user | 0 | PRIMARY | 1 | id | A | 3 | NULL | NULL | | BTREE | | | | user | 1 | index_score | 1 | score | A | 3 | NULL | NULL | | BTREE | | | | user | 1 | index_name_score | 1 | name | A | 4 | NULL | NULL | | BTREE | | | | user | 1 | index_name_score | 2 | score | A | 4 | NULL | NULL | | BTREE | | | +-------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+4 rows in set (0.00 sec)
看了以上關(guān)于Mysql數(shù)據(jù)庫的索引和視圖具體是怎么樣的詳細(xì)內(nèi)容，是否有所收獲。如果想要了解更多相關(guān)，可以繼續(xù)關(guān)注我們的行業(yè)資訊板塊。
