• mysql数据库 学习(三)主外键创建


    主外键关系

    -- 副表
    create table stu
    (
        s_no int(8) not null primary key auto_increment,
        s_name varchar(12),
        s_sex varchar(4),
        s_score double(6,1)
    );
    
    -- 主表
    create table tea
    (
        t_no int(8) not null primary key auto_increment,
        t_name varchar(12),
        t_sex varchar(4),
        t_age varchar(4),
        t_tel varchar(11)
    );
    
    -- 添加数据
    insert into tea
    (`t_name`,`t_sex`,`t_age`,`t_tel`)
    values
    ("张老师","男","39","132***"),
    ("吴老师","男","39","132***"),
    ("周老师","男","39","132***"),
    ("王老师","男","39","132***"),
    ("向老师","男","39","132***");
    
    insert into stu
    (`s_name`,`s_sex`,`s_score`)
    values
    ("小一","男","89"),
    ("小二","男","89"),
    ("小三","男","89"),
    ("小四","男","89"),
    ("小五","男","89");
    
    
    -- 查询数据
    select * from st stu,tea t where s.t_id = t.t_no;
    -- 返回两张表的(数据相乘) 笛卡儿积 
    select * from stu s,tea t;
    
    -- 左连接
    select * from stu s left join tea t on s.t_id = t.t_no;
    -- 右连接
    select * from stu s right join tea t on s.t_id = t.t_no;
    -- 内连接
    select * from stu s inner join tea t on s.t_id = t.t_no;
    -- 全连接 
    select * from stu s left join tea t on s.t_id = t.t_no
    union all
    select * from stu s right join tea t on s.t_id = t.t_no;
    -- 去重复:union 
    select * from stu s left join tea t on s.t_id = t.t_no
    -- 去重复
    union 
    select * from stu s right join tea t on s.t_id = t.t_no;
    
    -- 添加 unioue  该字段不能重复
    alter table stu add unique(s_no);
    alter table stu add unique(s_no,s_name);
    
    -- 添加外键
    alter table stu add foreign key (t_id) references tea(t_no);
    -- 查询 表结构
    desc stu;
    -- 创建索引 
    create index score_index on stu(s_score);
    -- 查询 
    select * from stu where s_score >80;
    -- 动态添加字段
    alter table tea add column t_address varchar(20);
    
    
    

    外键的创建

    查询结果

  • 相关阅读:
    字符串转换成整数
    回文字符串判断
    字符串包含
    翻转单词顺序VS左旋转字符串
    穷举子集合
    求S=a+aa+aaa+aaaa+aa...a的值
    数组元素去重
    找最长等差数列的长度
    Git pull and push
    Google 开发console查找元素或方法
  • 原文地址:https://www.cnblogs.com/d534/p/15237141.html
Copyright © 2020-2023  润新知