MySQL_06索引、视图、外键

一：索引

show tables;
desc aa;
desc cc;
desc duoceshi;
select * from duoceshi;

1.普通索引

create index bb on aa(name);   #在aa表的name字段上创建一个索引叫bb名字
show index from aa;            #查看索引
create index yy on cc(score);  #在cc表的score字段上创建一个索引叫yy名字
show index from cc;
alter table aa drop index bb;  #删除aa表中的bb索引

2.唯一索引   ===对应字段的值必须是唯一的，允许有空值(如果有多个字段同时创建唯一索引字段值可以不唯一)

 

create unique index r on duoceshi(class);    #创建唯一索引
show index from duoceshi;    
alter table duoceshi drop index r;    #删除唯一索引

 

3.主键索引跟主键约束相辅相成的

注意：添加一个主键约束就是添加一个主键索引，添加主键索引也是加主键约束(一个表里面只能有一个主键约束或者主键索引)

 

alter table duoceshi add PRIMARY key (id);   #添加主键约束也叫主键索引
alter table duoceshi change sid id int(20);  #去掉自增长
alter table duoceshi drop PRIMARY key;   #删除主键索引
show index from duoceshi;     
desc duoceshi;

 

 

二：视图

select * from duoceshi;
create view dcs as(select name from aa);  #创建一个dcs视图参照duoceshi表的name，age字段

show create view dcs;   #查看视图
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`%` SQL SECURITY DEFINER VIEW `dcs` AS (select `duoceshi`.`name` AS `name`,`duoceshi`.`age` AS `age` from `duoceshi`)

select * from dcs;
修改视图或者修改原表数据，同时都会影响
update duoceshi set age=60 where id=3;    #修改原表数据

update dcs set name='wangwu' where age=60;

删除视图和原表：删除原表会影响视图，删除视图不会影响原表
drop view dcs;
drop table aa;

三：外键约束====》foreign key  表对表之间的约束

 

show create table duoceshi;

CREATE TABLE `duoceshi` (
  `id` int(20) NOT NULL DEFAULT '0',
  `class` int(20) DEFAULT NULL,
  `english` int(20) DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL,
  `age` int(20) NOT NULL DEFAULT '0',
  `chinese` int(20) DEFAULT NULL,
  `math` int(20) DEFAULT NULL,
  `time` date DEFAULT NULL,
  PRIMARY KEY (`age`)
) ENGINE=MyISAM AUTO_INCREMENT=14 DEFAULT CHARSET=latin1

ENGINE=MyISAM    ===>数据库的默认引擎
create table dcs1(id int(20) PRIMARY key, name varchar(20))engine=INNODB;    #创建一个dcs1表数据库引擎为engine=INNODB


show create table dcs1;
CREATE TABLE `dcs1` (
  `id` int(20) NOT NULL,
  `name` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8


create table dcs2(sid int(20) PRIMARY key,sname varchar(20),constraint aa foreign key (sid) REFERENCES dcs1(id))engine=innodb;
#dcs2表中的sid参照dcs1表的id创建一个名叫aa的外键约束
show create table dcs2;

CREATE TABLE `dcs3` (
  `sid` int(20) NOT NULL,
  `sname` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`sid`),
  CONSTRAINT `aa` FOREIGN KEY (`sid`) REFERENCES `dcs1` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

如果主表不存在的数据任何一张子表是无法插入跟该数据相关的任何数据
insert into dcs1 values(1,'xiaocheng'),(2,'xiaoli');
select * from dcs1;

insert into dcs2 values(3,'lisi');

select * from dcs2;

如果要删除主表数据需要先删除与主表相关的子表数据，否则不能删除
delete from dcs1 where id=1;
delete from dcs2 where sid=1;


删除外键
alter table dcs2 drop foreign key aa;  #删除外键

show create table dcs2;
CREATE TABLE `dcs3` (
  `sid` int(20) NOT NULL,
  `sname` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`sid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8