MySQL表结构+键值(普通索引、主键、外键)

一.MySQL表结构

1.约束条件

限制字段赋值

> desc 库名.表名;		#查看表结构、约束条件
Field: 字段名	Type: 类型	Null: (是否可以为)空值	Key: 键值	Default: 默认值	Extra: 额外设置

> create database db1 character set utf8mb4;
> use db1;						#创建表: 表字段是否可为空null,默认值default
> create table t7(name char(10) not null,age tinyint unsigned default 19,class char(7) not null default 'nsd1909',pay float(7,2) default 28000);
> desc t7;		#查看表结构、字段约束			
> insert into t7(name,age,class) values('tom',18,'nsd1907');

2.修改表结构

>alter table 库名.表名 执行动作;
动作:	add添加字段    modify修改字段类型    change修改字段名
		drop删除字段   rename修改表名

> alter table t7 rename student;	#rename修改表名
> alter table student add sex enum("boy","girl") not null default "boy";	#add添加字段(默认最后),可first/after xx 调整字段顺序
> alter table student add email char(50) after age(first);
> alter table student modify age int not null default 19;		#modify修改字段类型
> alter table student change sex gender enum('boy','girl') not null default 'boy';	#change修改字段名/字段类型
> alter table student drop id,drop email;	#drop删除字段

二.MySQL键值

1.键值类型

各自功能不同,约束字段赋值

• 普通索引: index
• 唯一索引: unique
• 全文索引: fulltext
• 主键: primary key
• 外键: foreign key

2.索引

对表中字段进行排序(书的目录),索引类型: Btree、B+tree、hash

• 优点:
  -可以加快数据的查询速度
  -通过创建唯一性索引,保证表中每一行数据的唯一性
• 缺点:
  -对数据增删改时,索引也要动态调整,降低了数据的维护速度;
  -索引需要占用物理空间

01.index普通索引

• 一个表中可以有多个index字段;
  字段值允许重复,且可以赋NULL值
• 作为查询条件的字段设置为index字段 —>标志: MUL

建表时创建索引:
> create table tea4(id int,name char(10),age int,index(id),index(name));
> desc tea4 	--->key(id、name):MUL
已有表里创建索引:
> create index 年龄 on tea4(age);
> drop index 年龄 on tea4;		#删除索引
查看表索引信息:
> show index from tea4\G;		#\G竖向显示

02.primary key主键

• 字段值不允许重复,且不允许赋NULL值;
  一个表中只能有一个primary key字段
• 多个字段都作为主键,称为复合主键,必须一起创建;
  主键通常与auto_increment连用(自增长 i++)
• 把表中唯一标识记录的字段设置为主键primary key —>标志: PRI

建表时创建主键:
> create table t8(name char(10) primary key,id int);
> desc t8; --->key(name):PRI
删除主键:
> alter table t8 drop primary key;
在已有表添加主键:
> alter table t8 add primary key(name);
创建复合主键:
> create table t5(name char(5),class char(7),status enum('yes','no'),primary key(name,class));
> alter table t5 drop primary key;				#删除复合主键
> delete from t5;
> alter table t5 add primary key(name,class);	#已有表添加复合主键
主键与auto_increment(额外设置)连用(自增长i++):
> create table t6(id int primary key auto_increment,name char(5),sex enum('man','woman'));
> insert into t6(name,sex) values('xixi','woman'); 	#id:1
> insert into t6(name,sex) values('tom','man'); 	#id:2
> insert into t6 values(5,'harry','woman');			#id:5
> insert into t6(name,sex) values('bob','man'); 	#id:6
> insert into t6 values(null,'tc','man');			#id:7
> delete from t6;		#删除数据,再增加数据查自增长id
> insert into t6(name,sex) values('dc','man');  	#id:8

03.foreign key外键

• 插入记录时,字段值在另一个表字段值范围内选择
• 表存储引擎必须是innodb;
  字段类型要一致;
  被参照字段必须是索引类型的一种(primary key …)

格式:
>create table 表2(字段1,字段2,...,
	foreign key(字段1) reference 表名1(字段1)	#reference指定外键
	on update cascade		#同步更新
	on delete cascade		#同步删除
	)engine=innodb;		#指定存储引擎innodb

例: 创建工资表gz,表设置外键,其id字段从员工表yg中id得到:

>create table yg(yg_id int primary key auto_increment,name char(6))engine=innodb;	#建员工表yg
>insert into yg(name) values('tom'),('harry');
>create table gz(gz_id int,gz float(7,2), 					#建工资表gz
	foreign key(gz_id) references yg(yg_id)  #指定外键
	on update cascade 
	on delete cascade)engine=innodb;
>show create table gz \G;	#查看外键
>alter table gz add primary key(gz_id); #以免重复发工资
>insert into gz values(1,10000),(2,2000);
同步更新、同步删除: 查gz表同步
>update yg set yg_id=5 where yg_id=2;
>delete from yg where name=’tom’;
>select * from gz;