mysql索引与视图 原始表student字段: mysql select column_name,data_type - from information_schema.columns - where table_name = 'student';+-------------+-----------+| column_name | data_type |+-------------+-----------+| stu_id | int || stu_n
mysql索引与视图
原始表student字段:
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)表中原始数据:
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)
索引创建格式:
create [ ] index [ using {btree | hash} ] on table specification ( [,] )
:= unique | fulltext | spatial
:=[asc | desc]
创建一个最简单的索引:
mysql> create index stu_index
-> on student(stu_id);
Query OK, 0 rows affected (0.36 sec)
Records: 0 Duplicates: 0 Warnings: 0这里创建立一个非唯一性的索引,其中默认使用asc升序排列。
如果没有指定using声明的话,mysql自动创建一个B树。所以上面的索引其实是这样子的:
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当然,btree索引可以换成哈希索引。
也可以为多个列创建唯一的索引:
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添加索引:
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删除索引:
mysql> drop index stu_index on student;
Query OK, 0 rows affected (0.22 sec)
Records: 0 Duplicates: 0 Warnings: 0创建表时定义索引:
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)
-> );
只需在表的最后添加创建索引的语句即可。
视图是数据库中的虚拟表,它存储的不是自己的内容,而是经过select从其他表整合而来的。当其他表的内容改变是,视图内的内容跟着改变。在一定条件下,对视图的更新也将改变源表。
创建视图:
create [ or replace ] view [] as [with [ cascaded |local ] check option ]
mysql> create view view1 as
-> (select * from student);
Query OK, 0 rows affected (0.16 sec)
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)创建视图时,如果视图已存在,可用replace重新覆盖创建。
创建视图时还可以更改原始列名。
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)当一个视图可以更新时,就能够使用[with [ cascaded |local ] check option ]选项对更新对有效检查。
删除视图:
drop view view1;
网友评论
文明上网理性发言,请遵守 新闻评论服务协议我要评论
立即提交
专题推荐独孤九贱-php全栈开发教程
全栈 100W+
主讲:Peter-Zhu 轻松幽默、简短易学,非常适合PHP学习入门
玉女心经-web前端开发教程
入门 50W+
主讲:灭绝师太 由浅入深、明快简洁,非常适合前端学习入门
天龙八部-实战开发教程
实战 80W+
主讲:西门大官人 思路清晰、严谨规范,适合有一定web编程基础学习
php中文网:公益在线php培训,帮助PHP学习者快速成长!
Copyright 2014-2020 https://www.php.cn/ All Rights Reserved | 苏ICP备2020058653号-1