sql大数据 基础（DML数据操纵语言）

DML 数据操纵语言
select 增删改

1. 增

insert into +表名(列1，列2…) values (值1，值2…);
如果没有赋值的列则为空
insert into +表名 values (值1，值2…);
默认对表内所有字段新增数据
select * from sc;
insert into sc values (‘s099’,‘c099’,60);
select * from student;
insert into student(sno,sname) values (‘s099’,‘小明’);
练习： 将小张老师加入教师表
insert into teacher values (‘t099’,‘小张’);
select * from teacher;
快捷插入
insert into student (sno,sname) select empno,ename from scott.emp ;

2. 改

update 表名
set 列名 = 新值
[where 条件];
注：如果没有where条件则默认全表数据修改
update student set sage = 25 where sno = ‘7499’;
update student set sage = 25,ssex = ‘男’ where sno = ‘7499’;
select * from student where sno = ‘7499’;
练习：将员工表SMITH的工资涨2000
update scott.emp set sal = sal+2000 where ename = ‘SMITH’ ;
将员工表ALLEN,SMITH的工资涨2000
update scott.emp set sal = sal+2000 where ename in ( ‘SMITH’ ,‘ALLEN’);
空值
update student set ssex = null where sno = ‘7499’;

3. 删

delete from 表名 [where 条件]
注：如果不加where条件则默认删除全表数据
delete from student where sno = ‘s099’;
select * from student;
delete from student where sno in (select to_char(empno) from scott.emp);