索引

查看索引","marks":[{"type":"fontSize","value":18},{"type":"fontFamily","value":"Arial Black"}]}]}],"state":{"index":1}},{"type":"block","id":"BdHv-1660094919893","name":"list-item","data":{"listId":"aYg9-1660094729774","listType":"ordered","listLevel":1},"nodes":[{"type":"text","id":"eu5E-1660094919891","leaves":[{"text":"creata  index tt on dcs(sid);   ==>给dcs表中的sid创建索引（tt）","marks":[{"type":"fontSize","value":18},{"type":"fontFamily","value":"Arial Black"}]}]}],"state":{"index":2}},{"type":"block","id":"cACM-1660095200505","name":"list-item","data":{"listId":"aYg9-1660094729774","listType":"ordered","listLevel":1},"nodes":[{"type":"text","id":"YSnn-1660095200503","leaves":[{"text":"creata  index  tt1  on  dcs（sid，name）； 给dcs表中的sid，name创建索引（tt1）","marks":[{"type":"fontSize","value":18},{"type":"fontFamily","value":"Arial Black"}]}]}],"state":{"index":3}},{"type":"block","id":"ThOR-1660096438033","name":"list-item","data":{"listId":"aYg9-1660094729774","listType":"ordered","listLevel":1},"nodes":[{"type":"text","id":"Kj4K-1660096438030","leaves":[{"text":"alter  table dcs drop index tt1；删除dcs表","marks":[{"type":"fontSize","value":18},{"type":"fontFamily","value":"Arial Black"}]}]}],"state":{"index":4}},{"type":"block","id":"bDcK-1660098378889","name":"paragraph","data":{},"nodes":[{"type":"text","id":"snPo-1660098378887","leaves":[{"text":"注：添加一个主键约束就是添加主键索引，主键索引就是主键约束","marks":[{"type":"fontSize","value":18},{"type":"fontFamily","value":"Arial Black"}]}]}],"state":{}},{"type":"block","id":"gF73-1660098466883","name":"paragraph","data":{},"nodes":[{"type":"text","id":"pFyn-1660098466881","leaves":[{"text":"alter  table emp drop PRIMARY key；","marks":[{"type":"fontSize","value":18},{"type":"fontFamily","value":"Arial Black"}]}]}],"state":{}},{"type":"block","id":"AcRt-1660098546446","name":"paragraph","data":{},"nodes":[{"type":"text","id":"m4Lh-1660098546443","leaves":[{"text":"show  index from emp；","marks":[{"type":"fontSize","value":18},{"type":"fontFamily","value":"Arial Black"}]}]}],"state":{}},{"type":"block","id":"LBOh-1660098588318","name":"paragraph","data":{},"nodes":[{"type":"text","id":"JZAl-1660098588315","leaves":[{"text":"desc emp；","marks":[{"type":"fontSize","value":18},{"type":"fontFamily","value":"Arial Black"}]}]}],"state":{}},{"type":"block","id":"T5EC-1660098604324","name":"paragraph","data":{},"nodes":[{"type":"text","id":"64YV-1660098604322","leaves":[{"text":"视图","marks":[{"type":"fontSize","value":18},{"type":"fontFamily","value":"Arial Black"}]}]}],"state":{}},{"type":"block","id":"9d77-1660098610278","name":"list-item","data":{"listId":"4SB7-1660098619024","listType":"ordered","listLevel":1},"nodes":[{"type":"text","id":&quotWdk-1660098610276","leaves":[{"text":"视图是由基本表产生的虚表","marks":[{"type":"fontSize","value":18},{"type":"fontFamily","value":"Arial Black"}]}]}],"state":{"index":1}},{"type":"block","id":"d0rN-1660098683416","name":"list-item","data":{"listId":"4SB7-1660098619024","listType":"ordered","listLevel":1},"nodes":[{"type":"text","id":"hvfk-1660098683408","leaves":[{"text":"视图的更新和删除会影响基础表","marks":[{"type":"fontSize","value":18},{"type":"fontFamily","value":"Arial Black"}]}]}],"state":{"index":2}},{"type":"block","id":"SGRX-1660098722532","name":"list-item","data":{"listId":"4SB7-1660098619024","listType":"ordered","listLevel":1},"nodes":[{"type":"text","id":"gHQ9-1660098722529","leaves":[{"text":"基础表的更新和删除也会影响到视图","marks":[{"type":"fontSize","value":18},{"type":"fontFamily","value":"Arial Black"}]}]}],"state":{"index":3}},{"type":"block","id":"Jmmg-1660098776465","name":"paragraph","data":{},"nodes":[{"type":"text","id":"pSW1-1660098776463","leaves":[{"text":"create view dcs as(select sid,name,age from emp); ==》创建视图\r","marks":[{"type":"fontSize","value":18},{"type":"fontFamily","value":"Arial Black"}]}]}],"state":{}},{"type":"block","id":"k1Wo-1660121818281","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"XffU-1660121818282","leaves":[{"text":"select * from dcs;\r","marks":[{"type":"fontSize","value":18},{"type":"fontFamily","value":"Arial Black"}]}]}],"state":{}},{"type":"block","id":"vFNV-1660121818283","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"9cu5-1660121818284","leaves":[{"text":"update dcs set name=" zhangsan'="" where="" sid="1789;" =="》修改视图数据\r","marks":[{"type":"fontSize","value":18},{"type":"fontFamily","value":"Arial" black"}]}]}],"state":{}},{"type":"block","id":"qfat-1660121818285","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"kj5t-1660121818286","leaves":[{"text":"update="" emp="" set="" name="张三" black"}]}]}],"state":{}},{"type":"block","id":"puvn-1660121818287","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"jojb-1660121818288","leaves":[{"text":"drop="" view="" dcs;="=》删除视图\r","marks":[{"type":"fontSize","value":18},{"type":"fontFamily","value":"Arial" black"}]}]}],"state":{}},{"type":"block","id":"gwax-1660121818289","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"kmrq-1660121818290","leaves":[{"text":"\r","marks":[{"type":"fontsize","value":18},{"type":"fontfamily","value":"arial="" black"}]}]}],"state":{}},{"type":"block","id":"sfaw-1660121818291","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"wzgb-1660121818292","leaves":[{"text":"ddl:数据库定义语言(对表和表结构操作)\r","marks":[{"type":"fontsize","value":18},{"type":"fontfamily","value":"arial="" black"}]}]}],"state":{}},{"type":"block","id":"2p9z-1660121818293","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"rii4-1660121818294","leaves":[{"text":"create\r","marks":[{"type":"fontsize","value":18},{"type":"fontfamily","value":"arial="" black"}]}]}],"state":{}},{"type":"block","id":"djrs-1660121818295","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"zc6a-1660121818296","leaves":[{"text":"alter="" table\r","marks":[{"type":"fontsize","value":18},{"type":"fontfamily","value":"arial="" black"}]}]}],"state":{}},{"type":"block","id":"higb-1660121818298","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"hlhf-1660121818299","leaves":[{"text":"drop\r","marks":[{"type":"fontsize","value":18},{"type":"fontfamily","value":"arial="" black"}]}]}],"state":{}},{"type":"block","id":"03fj-1660121818300","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"bgal-1660121818301","leaves":[{"text":"\r","marks":[{"type":"fontsize","value":18},{"type":"fontfamily","value":"arial="" black"}]}]}],"state":{}},{"type":"block","id":"adxa-1660121818302","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"jdwy-1660121818303","leaves":[{"text":"dml:数据库操作语言(表数据操作)\r","marks":[{"type":"fontsize","value":18},{"type":"fontfamily","value":"arial="" black"}]}]}],"state":{}},{"type":"block","id":"nqnd-1660121818304","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"mcra-1660121818305","leaves":[{"text":"insert="" into\r","marks":[{"type":"fontsize","value":18},{"type":"fontfamily","value":"arial="" black"}]}]}],"state":{}},{"type":"block","id":"sbl0-1660121818306","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"iijx-1660121818307","leaves":[{"text":"delete","marks":[{"type":"fontsize","value":18},{"type":"fontfamily","value":"arial="" black"}]},{"text":"\r","marks":[{"type":"fontsize","value":18}]}]}],"state":{}},{"type":"block","id":"2l2p-1660121818308","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"p4re-1660121818309","leaves":[{"text":"update\r","marks":[{"type":"fontsize","value":18},{"type":"fontfamily","value":"arial="" black"}]}]}],"state":{}},{"type":"block","id":"bebz-1660121818310","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"9v45-1660121818311","leaves":[{"text":"select\r","marks":[{"type":"fontsize","value":18},{"type":"fontfamily","value":"arial="" black"}]}]}],"state":{}},{"type":"block","id":"4jgl-1660121818312","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"0nej-1660121818313","leaves":[{"text":"\r","marks":[{"type":"fontsize","value":18},{"type":"fontfamily","value":"arial="" black"}]}]}],"state":{}},{"type":"block","id":"a9kp-1660121818314","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"nyfg-1660121818315","leaves":[{"text":"\r","marks":[{"type":"fontsize","value":18},{"type":"fontfamily","value":"arial="" black"}]}]}],"state":{}},{"type":"block","id":"djdr-1660121818316","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"4yne-1660121818317","leaves":[{"text":"数据库约束\r","marks":[{"type":"fontsize","value":18},{"type":"fontfamily","value":"arial="" black"}]}]}],"state":{}},{"type":"block","id":"exee-1660121818318","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"uktv-1660121818319","leaves":[{"text":"not="" null="=">非空约束\r","marks":[{"type":"fontSize","value":18},{"type":"fontFamily","value":"Arial Black"}]}]}],"state":{}},{"type":"block","id":"HS8p-1660121818320","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"pIcu-1660121818321","leaves":[{"text":"primary key ==》主键约束\r","marks":[{"type":"fontSize","value":18},{"type":"fontFamily","value":"Arial Black"}]}]}],"state":{}},{"type":"block","id":"GBGw-1660121818322","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"qfe-1660121818323","leaves":[{"text":"auto_increment ==>自增长约束\r","marks":[{"type":"fontSize","value":18},{"type":"fontFamily","value":"Arial Black"}]}]}],"state":{}},{"type":"block","id":"GEx6-1660121818324","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"0Wg0-1660121818325","leaves":[{"text":"default ==》默认值约束  前四种都是对表结构的约束\r","marks":[{"type":"fontSize","value":18},{"type":"fontFamily","value":"Arial Black"}]}]}],"state":{}},{"type":"block","id":"Et3b-1660121818326","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"MzRr-1660121818327","leaves":[{"text":"foreigin key ==》外键约束 对表与表之间的约束\r","marks":[{"type":"fontSize","value":18},{"type":"fontFamily","value":"Arial Black"}]}]}],"state":{}},{"type":"block","id":"4PH9-1660121818328","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"a4QZ-1660121818329","leaves":[{"text":"\r","marks":[{"type":"fontSize","value":18},{"type":"fontFamily","value":"Arial Black"}]}]}],"state":{}},{"type":"block","id":"tyCW-1660121818330","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"f4qb-1660121818331","leaves":[{"text":"表1 姓名 年龄 身份证号  语文 数学 英语\r","marks":[{"type":"fontSize","value":18},{"type":"fontFamily","value":"Arial Black"}]}]}],"state":{}},{"type":"block","id":"fGqA-1660121818332","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"V07a-1660121818333","leaves":[{"text":"表2 姓名 年龄 身份证号  语文 篮球\r","marks":[{"type":"fontSize","value":18},{"type":"fontFamily","value":"Arial Black"}]}]}],"state":{}},{"type":"block","id":"zCso-1660121818334","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"jWdc-1660121818335","leaves":[{"text":"\r","marks":[{"type":"fontSize","value":18},{"type":"fontFamily","value":"Arial Black"}]}]}],"state":{}},{"type":"block","id":"KxcX-1660121818336","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"Q4Qw-1660121818337","leaves":[{"text":"show create table dcs1; ==》查看建表语句\r","marks":[{"type":"fontSize","value":18},{"type":"fontFamily","value":"Arial Black"}]}]}],"state":{}},{"type":"block","id":"g0sD-1660121818338","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"LbRK-1660121818339","leaves":[{"text":"create table dcs1(id int(5)PRIMARY key,name varchar(10))ENGINE=innodb;\r","marks":[{"type":"fontSize","value":18},{"type":"fontFamily","value":"Arial Black"},{"type":"color","value":"#CC0000"}]}]}],"state":{}},{"type":"block","id":"47WZ-1660121818340","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"3OwR-1660121818341","leaves":[{"text":"==》创建dcs1表，修改类型为innodb","marks":[{"type":"fontSize","value":18},{"type":"fontFamily","value":"Arial Black"},{"type":"color","value":"#CC0000"}]},{"text":"\r","marks":[{"type":"fontSize","value":18},{"type":"fontFamily","value":"Arial Black"}]}]}],"state":{}},{"type":"block","id":"8VXh-1660121818342","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"5M0z-1660121818343","leaves":[{"text":"\r","marks":[{"type":"fontSize","value":18},{"type":"fontFamily","value":"Arial Black"}]}]}],"state":{}},{"type":"block","id":"eKd6-1660121818344","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"79t7-1660121818345","leaves":[{"text":"CREATE table dcs2(sid int(5)PRIMARY key,sname varchar(10),\r","marks":[{"type":"fontSize","value":18},{"type":"fontFamily","value":"Arial Black"},{"type":"color","value":"#CC0000"}]}]}],"state":{}},{"type":"block","id":"nvRs-1660121818346","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"J5RL-1660121818347","leaves":[{"text":"CONSTRAINT xiaoliu foreign key dcs2(sid) REFERENCES dcs1(id))ENGINE=INNODB;\r","marks":[{"type":"fontSize","value":18},{"type":"fontFamily","value":"Arial Black"},{"type":"color","value":"#CC0000"}]}]}],"state":{}},{"type":"block","id":"XbRy-1660121818348","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"vmTM-1660121818349","leaves":[{"text":"==>创建dcs2表同时创建外键约束","marks":[{"type":"fontSize","value":18},{"type":"fontFamily","value":"Arial Black"},{"type":"color","value":"#CC0000"}]},{"text":"\r","marks":[{"type":"fontSize","value":18},{"type":"fontFamily","value":"Arial Black"}]}]}],"state":{}},{"type":"block","id":"AfKs-1660121818350","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"fsXT-1660121818351","leaves":[{"text":"\r","marks":[{"type":"fontSize","value":18},{"type":"fontFamily","value":"Arial Black"}]}]}],"state":{}},{"type":"block","id":"BLtf-1660121818352","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"wuGL-1660121818353","leaves":[{"text":"如果dcs1和dcs2存在外键约束,dcs1(","marks":[{"type":"fontSize","value":18},{"type":"fontFamily","value":"Arial Black"}]}]}],"state":{}}]'>索引
是一种数据结构
可以利用索引快速访问数据库中的特定信息
普通索引

• show index from emp; ==>查看索引
• creata  index tt on dcs(sid);   ==>给dcs表中的sid创建索引（tt）
• creata  index  tt1  on  dcs（sid，name）； 给dcs表中的sid，name创建索引（tt1）
• alter  table dcs drop index tt1；删除dcs表
  

注：添加一个主键约束就是添加主键索引，主键索引就是主键约束
alter  table emp drop PRIMARY key；
show  index from emp；
desc emp；
视图

• 视图是由基本表产生的虚表
• 视图的更新和删除会影响基础表
• 基础表的更新和删除也会影响到视图
  

create view dcs as(select sid,name,age from emp); ==》创建视图
select * from dcs;
update dcs set name='zhangsan' where sid=1789; ==》修改视图数据
update emp set name='张三' where sid=1789; ==》修改基本表数据
drop view dcs;==》删除视图

DDL:数据库定义语言(对表和表结构操作)
create
alter table
drop

DML:数据库操作语言(表数据操作)
insert into
delete
update
select


数据库约束
not null ==>非空约束
primary key ==》主键约束
auto_increment ==>自增长约束
default ==》默认值约束  前四种都是对表结构的约束
foreigin key ==》外键约束 对表与表之间的约束

表1 姓名 年龄 身份证号  语文 数学 英语
表2 姓名 年龄 身份证号  语文 篮球

show create table dcs1; ==》查看建表语句
create table dcs1(id int(5)PRIMARY key,name varchar(10))ENGINE=innodb;
==》创建dcs1表，修改类型为innodb

CREATE table dcs2(sid int(5)PRIMARY key,sname varchar(10),
CONSTRAINT xiaoliu foreign key dcs2(sid) REFERENCES dcs1(id))ENGINE=INNODB;
==>创建dcs2表同时创建外键约束

如果dcs1和dcs2存在外键约束,dcs1((主表) dcs2(子表)
主表中步存在的数据,不能在子表中进行插入