MySQL-5

淡淡的烟草味﹌ 2021-09-29 14:32 193阅读 0赞

# 主键 #

约束类型：Primary Key 主键约束。

表示id不可以重复，通过id来确定唯一的一条记录  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2gzNTYzNjM_size_16_color_FFFFFF_t_70]

# 非空， 自增 #

非空： 表示这个列的值不能空

自增，表示插入记录的时候，不需要手动插入，会自动增。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2gzNTYzNjM_size_16_color_FFFFFF_t_70 1]

# 连表查询 #

use java10;
    
    insert teacher (name,subject) values ('张老师','语文');
    
    select * from teacher;
    
    
    -- 501班的老师名字和教的科目名字
    
    select id from team where name='501';
    
    select teacher_id from team_teacher where team_id='1';
    
    select name,subject from teacher where id=1 or id=2;
    -- in 表示id 只要是等于小括号里面的值都可以
    select name,subject from teacher where id in (1,2);
    
    select name,subject from teacher where id in (1,2,3);
    -- 子查询，把一个sql 查询的结果，作为另外一个查询条件使用
    select t.name,t.subject from teacher t where
    t.id in 
    (select teacher_id from team_teacher where team_id='1');
    
    select t.name,t.subject from teacher t where
    t.id in 
    (select teacher_id from team_teacher where team_id in 
    (select id from team where name='501'));
    
    -- 左连接
    
    select t.name,t.subject from teacher t
    left join team_teacher tt on (t.id=tt.teacher_id)
    left join team te on (te.id=tt.team_id)
    where te.name='501';
    
    -- 对于左连接， 在查询结果中，左边的记录会全部包含
    --  on 后面的匹配条件，如果匹配到0或者1 对结果的总数没影响
    -- 如果匹配，大于1条，那么对结果，结果就就增加 匹配数量-1
    select * from team t 
    left   join team_teacher tt on (t.id=tt.team_id);
    
    
    -- 右连接
    
    select * from team t 
    right   join team_teacher tt on (t.id=tt.team_id);

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2gzNTYzNjM_size_16_color_FFFFFF_t_70]: /images/20210726/fc403cd10fca41e2a81be4f700d3d97f.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2gzNTYzNjM_size_16_color_FFFFFF_t_70 1]: /images/20210726/b098539703ed42cfa450b91116cafff1.png