Day9
今天讲了数据库相关的知识
主键
约束类型:Primary Key 主键约束。
表示id不可以重复,通过id来确定唯一的一条记录
非空, 自增
非空: 表示这个列的值不能空
自增,表示插入记录的时候,不需要手动插入,会自动增。
连表查询
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);