注:面试过程中整理的学习资料,如有侵权联系我即刻删除。
第二部分:索引的操作
什么是主键?
:就是表中唯一标识每一行的一列或者多列字段。
什么是索引?
:就是表中按照一定顺序排列的一列或者多列字段。
索引相当于是一本书的目录,而主键相当于是一本书的页码。主键也是索引的一种。
索引的优缺点?
:优点:提高数据检索的性能。
缺点:索引会占据物理存储空间;当向表中添加/删除数据时,索引也需动态更新,降低了插入/删除的速度。
一个索引会包含表中按照一定顺序排列的一列或多列字段。数据库对象索引可以类比书的目录,主要为了提高从表中检索数据的速度。索引是由表中的一个字段或者多个字段生成的键组成的,这些键存储在数据结构中(b树或者是哈希表),根据索引的存储类型,可以将索引分为b型树索引和哈希索引。
InnoDB和MyISAM存储引擎支持b树类型索引,MEMORY支持哈希类型索引。
适合创建索引:经常被查询的字段、分组的字段、主键或外键字段、设置唯一完整性约束的字段。
不适合创建索引:查询中很少被使用的字段、拥有许多重复值的字段;
频繁更新的字段;字段不在where语句中出现时不创建,只有where语句出现,mysql才会去使用索引。(有where时索引才有用)
MYSQL对每个表支持至少16个索引,总索引长度至少为256个字节。
普通索引可以创建在任何数据类型的字段上。
- 创建表时创建普通索引:index key_num(字段名);设置了字段名的索引对象key_num。这是在表中的语句。
查看索引是否创建成功:show create table 表名 \G;
查看索引是否启用:explain select * from 表名where 字段名=1\G
- 在已经创建好的表上创建普通索引:create index key_num on 表名 (字段名);
create index key_num on product(id);
c.用alter来添加普通索引,alter table 表名 add index key_num(字段名);
alter table product add index key_num(id);
d.删除索引:
drop index key_num on product;
唯一索引:索引的值是唯一的,当表中的某个字段设置主键或者唯一完整性约束(unique)时,系统就会自动创建关联该字段的唯一索引。
跟普通索引相比也就是在index前面多了一个unique。
全文索引:主要关联在数据类型为char、varchar、text的字段上。能够快速查询数据量较大的字符串类型的字段。只能在MyISAM的数据库表上创建全文索引。
与普通索引相比也就是在index前面多了一个fulltext。如果是创建表的时候创建全文索引,还要加上engine=MyISAM;
多列索引:索引关联的不是一个字段,而是多个字段。查询条件中必须使用所关联字段中的第一个字段,多列索引才会被使用。语句就是普通索引的语句中添加多个字段名,中间以逗号’,‘隔开。
删除索引:drop index索引对象 on 表名;
再通过show create table test5 \G查看是否还存在索引对象。
第三部分:视图VIEW的操作
什么是视图view?
:
视图是一个虚表,它的行列数据是从别的基础表中获得的,某几个字段可能来自于基础表A,某几个字段可能来自于基础表B。操作视图的时候,实际上操作的就是基础表。视图整个的建立和删除不影响基础表,而对视图内容的删除、增加、修改直接影响基本表。当视图来自于多个基本表时,不允许删除和增加数据。视图在物理上是不存在的,数据库系统没有专门的位置为视图存储数据,视图的数据来源于查询语句。
视图的作用:
提高了sql语句的复用性,就像一个函数;对数据库进行了重构,却不影响程序的运行;针对不同用户来说,提高了安全性能;
(1)创建视图:首先我们有一个product表,表中有如下信息,
| id | name | price |
+------+--------+-------+
| 1 | apple | 6.5 |
| 2 | banana | 4.5 |
| 3 | orange | 1.5 |
| 4 | pear | 2.5 |
+------+--------+-------+
create view view_product as select id,name from product;
相当于我们创建了一个视图,将表中的id,name字段的查询语句封装起来起了一个别名。(本质也就是封装查询语句)
然后我们再查询视图:select * from view_product;结果如下,
+------+--------+
| id | name |
+------+--------+
| 1 | apple |
| 2 | banana |
| 3 | orange |
| 4 | pear |
+------+--------+
(2)可以创建使用聚合函数(sum、min、max、count等)查询语句的视图:
create view view_product as select count(name) from product;
- 可以创建实现排序功能查询语句的视图。
create view view_p2 as select name from product order by id desc;
- 可以创建实现表内连接查询语句的视图。
create view view_p2
as
select s.name from
t_student as s,t_group as g where s.group_id=g.id and g.id=2;
//输出表group中id为2且id和表student中group_id相等时的表student中的name
得到的view_p2就是:
- 创建实现表外连接查询语句的视图,也实现以上效果,
左外连接:全部输出left join左边的那个表,输出另一个表中满足条件的数据。
右外连接:全部输出right join右边的那个表,输出另一个表中满足条件的数据。
外连接:full join,两个表完全输出。
内连接:inner join,两个表都需要满足条件输出
create view view_p2 as select s.name,g.name from t_student as s left join t_group as g on s.group_id=g.id where g.id=2;
<左连接中,当有where时,on后面的条件是两个表都得满足关联才行,不会出现join左边的表全输出的情况。>
- 创建实现子查询相关查询语句的视图,
create view view_p3 as select s.name from t_student as s where s.group_id in (select id from t_group);
- 创建实现联合查询语句的视图,
create view view_p4 as select id,name from t_student union all select id,name from t_group;
查看视图:
show tables; //不仅显示表的名字,也显示视图的名字
查看数据库里视图和表的详细信息:
show table status from 数据库 \G
查看视图的设计信息:desc 视图名;
information_schema是自动创建的系统数据库,这个数据库中存在一个包含视图信息的表views,通过查看views可以查看所有视图的相关信息。
进到系统库中:use information_schema;
删除视图:drop view 视图名1,视图名2,视图名3,......;
修改视图:
create or replace view view_product as select name from product;
然后再select * from view_product;查看
用alter语句也可以修改视图:
alter view view_product as select id from product;
通过视图添加一行新数据:
insert into view_product (id,name,price) values(11,'peach',10);
通过视图删除一行数据:
比如在视图中删除名为apple的数据,
delete from view_product where name='apple';
通过视图更新数据:
比如将peach的价格改为4.2,
update view_product set price=4.2 where name='peach';