美文网首页
SQL数据库的、外键和查询

SQL数据库的、外键和查询

作者: BJ000 | 来源:发表于2019-11-26 15:24 被阅读0次

    外键


    增加外键

    创建表的时候增加外键:在所有的表字段之后,使用foreign key(外键字段) references 外部表(主键字段)

    在新增表之后增加外键:修改表结构,使用alter table 表名 add [constraint 外键名字] foreign key(外键字段) references 父表(主键字段);

    修改外键&删除外键

    alter table 表名 drop foreign key 外键名;

    外键条件

    外键要存在,首先必须保证表的存储引擎是innodb

    列类型必须与父表的主键类型一致

    一张表中的外键名字不能重复

    增加外键的字段数据已经存在,必须保证数据与父表主键要求对应

    外键约束

    有三种约束模式

    district:严格模式(默认的)

    cascade:级联模式

    set null:置空模式

    语法:foreign key(外键字段) references 父表(主键字段) on delete 模式 on update 模式;

    查询

    联合查询

    基本语法:

    select 语句1

    union [union 选项]

    select 语句2……

    union 选项

    all:保留所有,不管重复

    distinct:去重,默认的

    子查询(sub query)

    按位置分类

    from子查询

    where子查询

    exists子查询

    按结果分类

    标量子查询

    列子查询

    行子查询

    表子查询

    子查询

    列子查询

    =any等价于in; -- 其中一个即可

    any等价于some; -- 二者是一样的

    =all为全部


    以下代码:

    -- 创建外键

    create table my_foreign1(

    idint primary key auto_increment,

    name varchar (20)not null comment

    '学生姓名',

    c_idint comment'班级id',

    -- 增加外键

    foreign key(c_id)references

    my_class(id)

    )charset utf8;

    -- 创建表

    create table my_foreign2(

    idint primary key auto_increment,

    name varchar (20)not null comment

    '学生姓名',

    c_idint comment'班级id'  -- 普通字段

    )charset utf8;

    -- 增加外键

    alter table my_foreign2add

    -- 指定外键的名字

    constraint student_class_1  -- 可以指定多个外键 但是名字不能相同

    -- 指定外键的字段

    foreign key(c_id)

    -- 引用父表主键

    references my_class(id);

    -- 删除外键

    alter table my_foreign1drop

    foreign key my_foreign1_ibfk_1;  -- my_foreign1_ibfk_1 通过外键的名字来删

    -- 插入数据;外键字段在父表不存在

    insert into my_foreign2values (

    null,'郭富城',4);  -- 没有4号班级

    insert  into my_foreign2values (

    null,'项羽',1);

    insert  into my_foreign2values (

    null,'刘邦',2);

    insert  into my_foreign2values (

    null,'韩信',3);

    -- 更新父表的记录

    update my_classset id=4 where id=1;  -- 失败;id=1记录已经被学生引用

    update my_foreign2set c_id=2 where id=4;  -- 更新

    update my_classset id=4 where id=3;  -- 可以;没有学生引用此班级

    -- mysql中添加外键约束遇到一下情况:

    -- cannot add foreign key constraint

    -- 出现这个问题的原因是,外键的使用:

    -- 1. 外键字段不能为该表的主键;

    -- 2. 外键字段参考字段必须为参考表的主键

    -- 插入数据

    insert into my_foreign1values (

    null,'马超','3'

    );

    -- 增加外键

    alter table my_foreign1add

    foreign key(c_id)references

    my_class(id);  -- 失败;因为没有3号班了

    -- 创建外键,指定模式;删除置空;更新级联

    create table my_foreign3(

    idint primary key auto_increment,

    name varchar (20)not null,

    c_idint,

    -- 增加外键

    foreign key (c_id)

    -- 引用表

    references my_class(id)

    -- 指定删除模式

    on delete set null

    -- 指定更新模式

    on update cascade

    )charset utf8;

    -- 插入数据

    insert into my_foreign3values (

    null,'刘备',1),

    (null,'曹操',1),

    (null,'孙权',1),

    (null,'祝贺量',2),

    (null,'周瑜',2);

    -- 解除My_foreign2表的外键

    alter table my_foreign2drop

    foreign key student_class_1;

    -- 更新父表主键

    update my_classset id=3 where id=1;

    -- 删除父表主键

    delete from  my_classwhere id=2;

    -- 联合查询

    select * from my_class

    union  -- 默认去重

    select * from my_class;

    select * from my_class

    union all  -- 不去重

    select * from my_class;

    select id,c_name,roomfrom my_class

    union all  -- 不去重

    select name,number,idfrom my_student;

    -- 需求;男生升序;女生降序(年龄)

    (select * from my_student

    where sex='男'

    order by ageasc limit9999999)

    union

    (select * from my_student

    where sex='女'

    order by agedesc limit9999999);

    select * from my_studentwhere

    c_id=(

    -- 标量子查询

    select idfrom my_classwhere

    c_name='python1903');-- id一定只有一个值(一行一列)

    insert into my_classvalues (1,

    'python1907','B407');

    -- 列子查询

    select * from my_studentwhere

    c_idin(select idfrom my_class);

    -- any,some,all

    select * from my_studentwhere

    c_id=any(select idfrom my_class);

    select * from my_studentwhere

    c_id=some(select idfrom my_class);

    select * from my_studentwhere

    c_id=all(select idfrom my_class);

    select * from my_studentwhere

    c_id!=any(select idfrom my_class);  -- 所有结果(null除外)

    select * from my_studentwhere

    c_id!=some(select idfrom my_class);  -- 所有结果(null除外)

    select * from my_studentwhere

    c_id!=all(select idfrom my_class);  -- 所有2号班级(null除外)

    select * from my_studentwhere

    age=(select max(age)from

    my_student)

    and

    height=(select max(height))from

    my_student);

    -- 行子查询

    select * from my_student

    -- (age,height)称之内为行元素

    where (age,height)=(select max(

    age),max(height)from my_student);

    update my_studentset height=188

    where name='王五';

    select * from my_studentorder by

    agedesc,heightdesc limit1;

    select * from my_studentorder by

    heightdesc;

    -- 表子查询

    select * from my_studentgroup by

    c_idorder by heightdesc;  -- 每个班选出第一个学生再按身高排序

    select * from (select * from

    my_studentorder by heightdesc)

    as studentgroup by student.c_id;

    相关文章

      网友评论

          本文标题:SQL数据库的、外键和查询

          本文链接:https://www.haomeiwen.com/subject/pooewctx.html