MySQL一周目四日

约束条件补充、表关系、修改表相关sql语句

1　　约束条件补充

1.1　主键　　primary key

主键的要点:

　　1　　是innoDB存储引擎组织数据表的依据，类似于目录的形式，用于加快查找的速度

　　2　　innoDB中每一个表都必须有主键，并且只能有一个主键

　　3　　在创建表的格式中没有设定主键，会采用隐藏字段作为主键，隐藏意味着无法使用主键，即无法加快查询速度

　　4　　主键在约束条件上来说等于not null（非空）和unique（唯一）的集合体

　　5　　表中没有主键时，由于4的效果，会自动将第一个约束条件为not null和unique的字段升级为主键

 

　　　 因此在创建表的时候一般需要一个id字段，并将其设置为表的主键字段

补充:　　主键可以是单单一个字段名的主键，也可以是多字段名的联合主键。但是联合主键的使用频率非常低

create table t1(id int,name char(16),primary key(id,name));
#    如此表示id和name的联合为主键

1.2　　自增　　auto_increment

自增配合主键一起使用，作为额外限制条件，可以减轻填写主键的负担

如　未使用自增，每次插入数据时都要填写主键，那么当主键的id序号忘记时，需要重新去表格中查找序号数字，方可填写

　　 采用自增，每次插入时不需要填写作为主键的id，可以减少录入的数量，还能保持数据的查找速度

 补充：　使用delete from删除数据　由于自增的特性，主键序号不会因为删除数据而回退或是重置

　　　　 只能使用truncate 删除数据并重置主键，此时所有内容要重新输入

1.3　　外键

外键：记录表与表之间数据关系的字段

2　　表关系

有关系的表可以分为三种，分别是：一对多关系、多对多关系、一对一关系。　　一对多关系中包含一对多和多对一

定于外键的限制条件可以使用　　foreign key(外键字段名) references 链接表名(表id字段)　　来表示

　　外键的特性

　　1　　创建表的时候，一定要先创建被关联表，即自身没有外键字段的表

　　2　　插入数据的时候，一定要先插入被关联的表，在插入关联表。关联表中的字段只能是被关联表表的id的值，因此必须先有id，类似于枚举的概念。

　　3　　正常情况下，被关联表中的数据无法只有删除和修改，必须取消关联后才能修改

　　4　　创建表时使用在外键之后增加约束条件，级联更新和级联删除可以修改表内容　　级联更新　　on update cascade　　级联删除　　on delete cascade

　　　　　　级联更新与级联删除的运用

　　　　　　　　级联更新可以修改被关联表中数据的同时，修改关联表中的数据

　　　　　　　　级联删除可以删除被关联表中数据的同时，删除关联表中的数据

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

2.1　　一对多关系

　　1　判断表1中的一个数据能否对应表2中的多个数据

　　2　判断表2中的一个数据能否对用表1中的多个数据

　　3　判断结果只有一项成立，即为一对多关系

以员工表与部门表为例，一个员工只能对应一个部门，结果1不成立；一个部门有多个员工，结果2成立，那两张表为一对多关系

　　建立表的时候要把表放在对应关系多的一方，即在员工表处建立外键

create table emp(
    id int primary key auto_increment,
    name char(16),
    age int,
    dep_id int,
    foreign key(dep_id) references dep(id)
);
create table dep(
    id int primary key auto_increment,
    dep_name char(16),
    dep_desc char(32)
);

2.2　　多对多关系

　　1　判断表1中的一个数据能否对应表2中的多个数据

　　2　判断表2中的一个数据能否对用表1中的多个数据

　　3　判断结果两项都成立，即为多对多关系

以书籍表与作者表为例，一本书允许有多个作者，关系1成立；一个作者可以写多本书，关系2成立，两项都成立，即为多对多关系

　　建立表时，不能再表1与表2中都建立外键；两张表格都由于另一张表未建立而无法建立，

　　采用的方法使是，正常创建表1与表2，并输入数据，随后建立第三张表格，表3中记录表1与表2的对应关系

create table book(
    id int primary key auto_increment,
    name char(16),
    price int
);
create table author(
    id int primary key auto_increment,
    name char(16),
    age int
);
create table book2author(
    id int primary key auto_increment,
    author_id int,
    book_id int,
    foregin key(author_id) references author(id)
    on update cascade
    on delete cascade,
    foreign key(book_id) references book(id)
    on update cascade
    on delete cascade
)；

2.3　　一对一关系

　　1　判断表1中的一个数据能否对应表2中的多个数据

　　2　判断表2中的一个数据能否对用表1中的多个数据

　　3　判断结果两项都不成立，但是表1与表2又具有关系，则属于一一对应关系

以简介表与详情表为例，一份简介不对应多份详情，一份详情也不对应多份简写，然而两张表中的数据组合后都为一个用户的信息，即一对一关系

　　以一对多关系为建表的基础，保证一对于关系，即在外键字段处添加限制 只能唯一 　　建立一对一关系时，任意一方的表处都可以建立，但是建议建在使用频率高的一方

create table user(
    id int primary key auto_increment,
    name varchar(16),
    detail_id int unique,
    foreign key(detail_id) references user_detail(id)
    on update cascade
    on delete cascade
);
create table user_detail(
    id int primary key auto_increment,
    phone bigint,
    addr varchar(16)
);

3　　修改表相关语句　　修改关键词　　alter

3.1　　修改表名　　

关键词 　rename　　方法　　alter table 表名 rename 新表名；

3.2　　增加字段

关键词　　add　　   方法　　alter table 表名 add 字段名 字段类型 约束条件 位置；位置处填写：插入为第一个用　　first 　　插入在指定字段名之后　　after 字段名 

3.3　　修改字段

关键词　　modify　　方法　　alter table 表名 modify 字段名 字段类型 完整的约束条件；　　modify只能修改字段的约束条件

关键词　　change　　方法　　alter table 表名 change 旧字段名 新字段名 旧字段类型 完整约束条件；　　此处改字段名

关键词　　change　　方法　　alter table 表名 change 旧字段名 新字段名 新数据类型 完整约束条件；　　此处改字段名和字段类型