2、修改数据update语句
3、删除 语句delete
SELECT * FROM emp
--插入数据,insert语句
--写出要给值的列名,
INSERT INTO dept(deptno,dname,loc) VALUES(50,'HHHD','GUANGZHOU');
--省略列名,默认所有列都要有值
INSERT INTO dept VALUES(60,'EEEEE','HUNAN');
--某列可以接受null,这样的可以省略,默认 null
INSERT INTO dept(deptno,dname) VALUES(70,'WWWW');
INSERT INTO dept(deptno,dname) VALUES(11,'&WWW&');
--插入日期型数据
INSERT INTO emp VALUES(7777,'JERRY','CLERK',7499,SYSDATE,5500,100,20);
INSERT INTO emp VALUES(7778,'TOM','CLERK',7499,'01-1月-2017',5800,100,30);
INSERT INTO emp VALUES(7779,'TOM','CLERK',7499,to_date('2018-1-21','YYYY-MM-DD'),5800,100,30);
--创建数据表,根据已经某张表创建,结构和某表一样
CREATE TABLE manager AS
SELECT * FROM emp WHERE deptno=10;
--一次插入多条记录,用子查询
INSERT INTO manager
SELECT *
FROM emp
WHERE deptno=20
--修改指定条件的记录,修改一行
UPDATE emp
SET ename='TONNY'
WHERE empno=7779
--不加条件,修改所有行
UPDATE emp
SET deptno=10
--修改多行
UPDATE emp
SET sal=sal+1000
WHERE sal=1250
--修改多列
UPDATE emp
SET sal=sal+1000,comm=comm+100
WHERE empno IN(7777,7778,7779)
--利用自查询修改列的值
UPDATE emp
SET sal=sal+(SELECT AVG(sal) FROM emp)
WHERE mgr=7499
--利用相关子查询来修改数据
ALTER TABLE emp
ADD (dname VARCHAR(14));
UPDATE emp e
SET dname=(SELECT dname
FROM dept d
WHERE e.deptno=d.deptno)
-- 删除选中记录
DELETE FROM emp
WHERE job='CLERK'
--删除所有记录
DELETE FROM emp
--基于另一张表删除记录
DELETE FROM emp
WHERE deptno=(SELECT deptno
FROM dept
WHERE dname='SALES')
--删除时完整性错误
DELETE FROM dept
WHERE deptno=10
--相关删除:删除没有员工的部门
DELETE FROM dept d
WHERE NOT EXISTS(
SELECT ename
FROM emp
WHERE deptno=d.deptno
)
--产生一个报告,显示BLAKE的所有下级(包括直接下级和间接下级)雇员的名字,薪水和部门号
-- 查询比所在职位平均工资高的员工姓名,职位
--用集合运算查询出职位为SALESMAN和部门编号为10的人员编号.姓名.职位,不排除重复结果.
select ename,deptno,sal
from emp
start with ename='BLAKE'
connect by prior empno=mgr
select e1.empno,e1.ename,e1.job
from emp e1
where sal>(select avg(sal) from emp e2 where e1.deptno=e2.deptno group by deptno)
select ename,job,deptno
from emp
where job='SALESMAN'
union all
select ename,job,deptno
from emp
where deptno='10'
---新增部门
insert into dept_bak (deptno,dname,loc) values (60,'developer','guangzhou');
---null值被插入
----字段的数量与值的数量要匹配
insert into dept_bak (deptno,dname,loc) values (70);----wrong
insert into dept_bak (deptno,dname,loc) values (70,null,null);--ok,显示赋值null
insert into dept_bak (deptno) values (70);----right,隐式给dname,loc赋值null
---新增日期
---解决方案一
insert into emp (empno,hiredate) values(9999,sysdate);
---解决方案二
insert into emp (empno,hiredate) values(7777,to_date('30-03-2018 09:28','DD-MM-YYYY hh24:mi'));
---插入特殊字符& %
insert into emp(empno,ename) values(7676,'&'||'huairen'||'&');----拼接字符串
insert into emp(empno,ename) values(7676,chr(ascii('&'))||'huairen');----使用转换函数
---插入多行数据,
---注意:目标表的列数和对应类型必须和子查询中列数及数据类型匹配
create table emp_bak as select * from emp where 1=0;----拷贝表的定义,不拷贝数据,因为1=0 是假
insert into emp_bak select * from emp;---拷贝emp表的数据给emp_bak,不需要使用values关键字
---创建表并插入多行数据
create table emp_back as select * from emp where 1=0;
insert into emp_back
select * from emp where hiredate>to_date('01-01-1982','DD-MM-YYYY');
---------
--------------------------------------修改,编辑,狭义的更新 update------------------------------------
---修改编号是7777的员工信息
update emp
set ename='zhoujielun',sal=2100,comm=200,job='CLERK'
where empno=7777;
update emp
set deptno=80 ----not ok,因为部门表中不存在这个部门编号,会抛出未找到父项关键字错误
where empno=7777;
update emp_back
set deptno=10;----慎用,因为没有指定限制条件,会修改表中所有的记录,部门编号都改为10,太恐怖了!!!!!
---------------------一次修改多列--------------------------------------
----部门编号为10的员工,编号改为20,工资增加100
update emp_back
set deptno=20,sal=sal+100
where deptno=10;
----部门编号为20的员工,编号改为10,工资增加所有人的平均工资
update emp_back
set deptno=20,sal=nvl(sal,0)+(select avg(nvl(sal,0)) from emp_back)
where deptno=10;
----给emp_back表添加一个列
alter table emp_back add(dname varchar2(40));
---使用相关子查询更新dname列为正确的名称
update emp_back e
set dname=(select dname from dept d where d.deptno=e.deptno)
-------------------------delete 删除------------------------
删除50号部门
delete dept----可以不要from
where deptno=50;
delete from dept
where deptno=50;
delete emp_back
where job='CLERK';---- 删除多行数据
delete emp_back;
---删除'tongji'部门的员工信息,使用嵌套子查询,
insert into emp_bak select * from emp;
delete emp_bak
where deptno=(select deptno from dept where dname='tongji')
---删除部门编号为10的部门
delete dept where deptno=10;----not ok,已找到子记录,就是说有一堆孩子,所以该父项不能被删除
---如果真的想删除该部门,怎么办?
1.让约束失效,不启用外键约束
2.让那一堆孩子的部门编号置空
------------------------------------使用相关子查询进行删除操作------------------------------
---删除曾经变动过岗位的员工记录
delete emp_bak e1
where (select count(1) from emp_jobhistory e2 where e2.empno=e1.empno)>0
---删除没有员工的部门记录
create table dept_bak as select * from dept;
delete dept_bak d
where not exists(select (1) from emp_bak e2 where e2.deptno=d.deptno)
---删除没有部门的员工记录
delete emp_bak e1
where deptno is null
--1.向部门表新增一个部门,部门编号为50,部门名称为HR,工作地点为SY。
insert into dept (deptno,dname,loc) values(50,'HR','SY');
--2.向部门表新增一个部门,部门编号为60,部门名称为MARKET。
insert into dept (deptno,dname) values(60,'MARKET')
--练习2 2
--1.向员工表中新增一个员工,员工编号为8888,姓
--名为BOB,岗位为CLERK,经理为号7788,入职日期
--为1985-03-03,薪资3000,奖金和部门为空。
select * from dept
insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno)values
(8888,'BOB','CLERK',7788,'03-3月-1985',3000,null,null);
--练习3
--1.使用CREATE TABLE emp_back as
--SELECT * FROM EMP WHERE 1=0,创建
--emp_back表,拷贝下来即可。
create table emp_back
as select * from emp where 1=0
--2.把emp表中入职日期大于1982年1月1日之前的员
--工信息复制到emp_back表中。
insert into emp_back
(select * from emp where hiredate>'1-1月-1982');
--练习4 4
--1.修改部门20的员工信息,把82年之后入职的员工入职日期向后调整10天
update emp
set hiredate=hiredate-10
where hiredate>to_date('1982-1-1','yyyy-MM-dd') and deptno=20
--2.修改奖金为null的员工,奖金设置为0
update emp
set comm=0
where comm=null
--3.修改工作地点在NEW YORK或CHICAGO的员工工资,工资增加500
update emp
set sal=sal+500
where deptno=
(select deptno from dept d where loc ='NEW TORK' and loc='CHICAGO')
--练习5 1.重复做一下刚才的案例。
alter table emp_back
add(dname varchar2(14));
update emp_back e
set dname =
(select dname from dept d where deptno=e.deptno)
--练习6
--1.删除经理编号为7566的员工记录
delete emp_back
where emp_back.mgr=(select deptno from emp where deptno=7566)
--2.删除工作在NEW YORK的员工记录
delete emp_back e
where deptno=(select deptno from dept where loc='NEW YORK')
--3.删除工资大于所在部门平均工资的员工记录
delete emp_back e
where sal>(select avg(e.sal) from dept d where d.deptno=e.deptno group by d.deptno)
--练习8
--1.test表为空表,分析如下语句操作后,最后test表的状态。
insert into test(id,name) values(1,'a');
insert into test(id,name) values(2,'b');
savepoint s1;
insert into test(id,name) values(3,'c');
insert into test(id,name) values(4,'d');
delete from test where id in(1,3);
rollback to s1;
delete from test where id in(2,4);
commit;--一提交就不能回滚了
rollback;
select * from class
--课后作业
--1.使用如下语句,创建学生表student和班级表class
create table student(
xh char(4),--学号
xm varchar2(10),--姓名
sex char(2),--性别
birthday date,--出生日期
sal number(7,2),--奖学金
studentcid number(2)--学生班级号
)
create table class(
classid number(2),--班级编号
cname varchar2(20),--班级名称
ccount number(3)--班级人数
)
commit;
--2.基于上述学生表和班级表,完成如下问题
--(1)添加三个班级信息为:1,JAVA1班,null
--2,JAVA2班,null
--3,JAVA3班,null
insert into class values(1,'JAVA1班',null);
insert into class values(2,'JAVA2班',null);
insert into class values(3,'JAVA3班',null);
--(2)添加学生信息如下:‘A001’,‘张三’,‘男’,‘01-5月-05’,100,1
insert into student(xh,xm,sex,birthday,sal,studentcid) values
('A001','张三','男','01-5月-05',100,1)
select * from student
drop table student
--(3)添加学生信息如下:'A002','MIKE','男','1905-05-06',10
insert into student(xh,xm,sex,birthday,sal) values
('A002','MIKE','男','06-5月-1905',10)
--(4)插入部分学生信息: 'A003','JOHN','女’
insert into student(xh,xm,sex)values ('A003','JOHN','女')
--(5)将A001学生性别修改为'女‘
update student set sex='女' where xm='A001'
--(6)将A001学生信息修改如下:性别为男,生日设置为1980-04-01
update student set sex='男',birthday=('01-4月-1980') where xh='A001'
--(7)将生日为空的学生班级修改为Java3班
update student set studentcid=3 where birthday=null
--(8)请使用一条SQL语句,使用子查询,更新班级表中每个班级的人数字段
select ccount 班级人数,classid 班级
from class
where classid in
(select studentcid from student where class.classid=student.studentcid )
group by classid,ccount
--做不出来
--用分组函数做的
select count(ccount) 班级人数,c.classid 班级
from class c,student s
where c.classid=s.studentcid
group by c.classid
insert into class values (4,'jj班',0)
UPDATE CLASS c
SET ccount=(SELECT COUNT(xh)FROM student WHERE studentcid=c.classid)
select * from student
select * from student
--3.使用如下语句,建立以下表
CREATE TABLE copy_emp (
empno number(4),
ename varchar2(20),
hiredate date default sysdate ,
deptno number(2),
sal number(8,2))
select * from copy_emp
--课后作业
--4.在第三题表的基础上,完成下列问题
--(1)在表copy_emp中插入数据,要求sal字段插入空值,部门号50
--,参加工作时间为2000年1月1日,其他字段随意
insert into copy_emp (empno,ename,hiredate,deptno,sal)
values(123,'dd','1-1月-2000',50,null)
--(2)在表copy_emp中插入数据,要求把emp表中部门号为10号部门的员工信息插入
insert into copy_emp
(select empno,ename,hiredate,deptno,sal from emp where deptno=10)
--(3)修改copy_emp表中数据,要求10号部门所有员工涨20%的工资
update copy_emp set sal=sal*1.2
where deptno=10
--(4)修改copy_emp表中sal为空的记录,工资修改为平均工资
update copy_emp set sal=(select avg(sal) from copy_emp)
where sal in null
--(5)把工资为平均工资的员工,工资修改为空
update copy_emp set sal=null
where sal=(select avg(sal) from copy_emp)
--(6)另外打开窗口2查看以上修改
??
--(7)执行commit,窗口2中再次查看以上信息
commit;
--(8)删除工资为空的员工信息
delete from copy_emp
where sal=null
--(9)执行rollback
rollback;