外连接,exists存在,(DML):用SQL录入数据，用SQL删除数据，用SQL更新数据

1.外连接

• 左连接:left join on

select * from emp e left join dept d on e.deptId=d.deptId
1

• 右连接 right join on

select  * from emp e right join dept d on e.deptId=d.deptId
1

• 内连接:inner join on,和等值连接效果一样。

select * from dept d inner join emp e on d.deptId=e.deptId
1

• 全连接:union

select * from emp e left join dept d on e.deptId=d.deptId
union
select  * from emp e right join dept d on e.deptId=d.deptId
1
2
3

2.exists存在 not exists不存在

-- in
select * from card where cardNo in (select cardNo from transinfo)
1
2

-- exists
select * from card c where exists (select * from transinfo t where t.cardNo=c.cardNo )
1
2

-- not in
select * from card where cardNo not in (select cardNo from transinfo)
1
2

-- not exists
select * from card c where not exists (select * from transinfo t where t.cardNo=c.cardNo )
1
2

3.用SQL录入数据（DML）

• 单行录入数据

insert into user (userId,name)values('101','晓晓')
1

• 多行录入数据

insert into user (userId,name)values('101','晓晓'),('102','晓')
1

• 复制表

create table u1 as (select * from user where city='北京')
1

• 复制数据

insert into u1 (userId,name)(select userId,name from user where sex='男')
1

4.用SQL删除数据（DML）

• 删除记录

delete from user where city='北京'
1

• 整表数据删除

truncate table user
1

• 级联删除
  主要用于主外键
  cascade 级联删除，删除主键表中的数据，由数据库自动删除外键表中的相关数据

• 限制删除条数

delete from user where sex='男'limit 2
1

5.用SQL更新数据（DML）

• 更新一条数据

update user set name='晓晓',city='北京' where userId='101'
1

• 更新多条数据

update user set credit=credit+100 where credit<300
1

• 嵌套更新

update user set credit =(select credit from user where name='晓晓') where userId='101'
1