MySQL约束条件的内容与SQL修改

MySQL的约束条件和SQL修改

• 约束条件主键
• 约束条件自增
• 约束条件外键
• 表关系的种类
• 修改表相关SQL语句

约束条件主键

1.单从约束层面上来讲 相当于not null + unique = 非空且唯一

primary key # 主键
语法：
    create table 名 (名 类型 primary key);
eg：
　　create table ta1(id int primary key);

验证：
    insert into ta1 values(null);
    insert into ta1 values(2);

2.但是主键还是innoDB存储引擎组织数据表的依据

'''
1.innoDB中表必须由且只有一个主键
2.如果不指定主键，会采用采用隐藏字段作主键
3.当表中没有主题但是有非空且唯一的字段会自动成为主键
4.主键可以加快查询速度
'''
验证3：
eg：
create table ta2(
    id int,
    name char(16),
    age int not null unique,
    addr char(16) not null unique);

# 结论：创建表都需要一个id字段,并且该字段应设置为表的主键字段

语法：
    create table 表名(
        id int primary key,
        name char(16) 

 

约束条件自增

当有大量数据要写入数据库时，id量会非常巨大难以手写输入，自增就可以解决这问题

'''
功能：
    数据的值以自然数的自动增加
'''
语法：
create table 名(
    id int pimary key auto_increment,
    name char(16));

eg:
create table ta3(
　　id int primary key auto_increment,
　　user varchar(16)
);

insert into ta3 (user) values('gg'),('kk');

'''
创建表时 主键字段的默认写法
id int primary key auto_increment
'''

自增补充

1.自增不会因为delete删除操作而重置

eg：
    delete from ta3 where id =2;
    insert into ta3(user) values('kk');

2.重置自增数据
    truncate  # 删除数据和重置主键

eg：
    truncate table ta3;
    insert into ta3(user) values('kk'),('jk');

 

 约束条件外键

为什么要有外键

该表问题：
1.表结构不清晰     2.表数据重复    3.数据扩展性极差

'''
为解决上诉问题，进行了拆表
但是员工与部门之间缺少了关系
'''

在员工表里面添加一个部门id（即外键），以此来确定员工与部门的关系
'''
外键：记录表与表之间数据关系的字段
'''
语法：
　　# 用于员工表内
　　foreign key(员工表的部门id) renferences 部门表(id)

 

表关系的种类

• 一对多关系
• 多对多关系
• 一对一关系

一对多关系

"""判断表关系遵循 换位思考 的原则"""

员工表：
    一个员工不可以对应多个部门
部门表：
    一个部门可以对应多个员工
'''
结论：员工表与部门表的关系为”一对多“
'''

一对多关系的使用规范：

1. "一对多"表关系外键字段建在多的一方
2. 先写基本字段类型与约束条件，后写外键

eg：
create table dep(
    id int primary key auto_increment,
    name varchar(16),
    fc varchar(16));

create table emp(
    id int primary key auto_increment,
    name varchar(16),
    age int,
    job_id int,
    foreign key(job_id) references dep(id));

insert into dep(name,fc) values('财务','存钱'),('保安','守钱');
insert into emp(name,age,job_id) values('小王',23,1),('小李',43,1),('小明',12,2),('小张',32,2);

 

外键字段的特性

1.再创建表的时候一定要先创建被关联表(自身没有外键字段的表)
2.在插入数据的时候也是先插入被关联表再插入关联表
3.关联表中只能输入被关联字段中出现的值

问题：
    被关联表的数据无法自由删改
解决：
    on update cascade  # 级联更新，可以自由更新数据
    on delete cascade  # 级联删除，可以删除更新数据

eg:
create table dep(
    id int primary key auto_increment,
    name varchar(16),
    fc varchar(16));

create table emp(
    id int primary key auto_increment,
    name varchar(16),
    age int,
    job_id int,
    foreign key(job_id) references dep(id)
    on update cascade  # 级联更新
    on delete cascade  # 级联删除
);

insert into dep(name,fc) values('财务','存钱'),('保安','守钱');
insert into emp(name,age,job_id) values('小王',23,1),('小李',43,1),('小明',12,2),('小张',32,2);

# 修改数据，把dep中id=2修改为id=4
update dep set id=4 where id=1；

# 删除dep中id=2的数据
delete from dep where id=2;

 

 多对多关系

书名表：
    一本书可以对应多个作者
作者名：
    一个作者可以有多本书
'''
结论：书名表与作者名的'多对多'
'''

# 针对多对多的表关系 外键字段需要建在第三张关系表中
eg:
create table bk(
    id int primary key auto_increment,
    name varchar(16));

create table author(
id int primary key auto_increment,
name varchar(16)
);

create table rel(
    id int primary key auto_increment,
    authorid int,
    bookid int,
    foreign key(authorid) references author(id)
    on update cascade
    on delete cascade,
    foreign key(bookid) references bk(id)
    on update cascade
    on delete cascade
);

# 插入书名数据
insert into bk(name) values('a书'),('b书'),('c书');

# 出入作者名
insert into author(name) values('小明'),('小王');

# 插入关系表

insert into rel(authorid,bookid) values(1,1),(1,2),(2,3),(2,1);

 

 

 一对一

# 以夫妻关系为例
丈夫：
    只有一个妻子
妻子：
    只有一个丈夫
'''
结论：丈夫和妻子是“一对一”关系
'''
'''
在一对一关系中，外键建在任意一方都可以 但是推荐建在查询频率较高的表中
'''
eg:

create table hus(
    id int primary key auto_increment,
    name varchar(16),
    wife_id int unique,
    foreign key(wife_id) references wife(id)
    on update cascade
    on delete cascade
);

create table wife(
    id int primary key auto_increment,
    name varchar(16)
);

# 插入妻子信息

insert into wife(name) values('丽丽'),('小倩');

insert into hus(name,wife_id) values('老王',2),('老丁',1);

 

修改表相关SQL语句

1.修改表名

alter table 名 rename 新名；
eg：
　　alter table wife rename ex_wife;  # 把表名wife改成ex_wife 

2.增加字段

语法：
alter table 表名
                          add 字段名  数据类型 [完整性约束条件…],
                          add 字段名  数据类型 [完整性约束条件…];
eg：
alter table hus add bobby char(16); 

语法：
# 将数据放在第一位
alter table 表名 add 字段名  数据类型 [完整性约束条件…]  first;

eg：
alter table hus add age int first; 

语法：
# 把字段加在某数据之后
alter table 表名 add 字段名  数据类型 [完整性约束条件…]  after 字段名;

eg：
alter table hus add car varchar(16) after name; 

 

 3.删除字段

语法：
alter table 表名 drop 字段名;

eg：
alter table hus drop car；

4.修改字段

# modify只改字段数据类型完整约束，不改字段名，但change可以
语法：
 alter table 表名 modify  字段名 数据类型 [完整性约束条件];   

eg：
alter table hus modify wife_id int(8) unique;

语法：
alter table 表名 change 旧字段名 新字段名 旧/新数据类型 [完整性约束条件…];

eg：
alter table hus change bobby hobby int(11) unique;