• 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);
    
    
    

    外键的创建

    查询结果

  • 相关阅读:
    常用正则表达式
    用Python开始机器学习(2:决策树分类算法)
    Query意图分析:记一次完整的机器学习过程(scikit learn library学习笔记)
    如何成为python高手(转)
    scikit-learn——快速入门
    程序员训练机器学习 SVM算法分享
    应用scikit-learn做文本分类
    sklearn文本特征提取
    中文分词入门之字标注法4
    poj 2965 The Pilots Brothers' refrigerator(dfs 枚举 +打印路径)
  • 原文地址:https://www.cnblogs.com/d534/p/15237141.html
Copyright © 2020-2023  润新知