1.查询
select * from teacher(表名) t(别名) where tname like '陈%' or tname like '韩%';
2.更新
update teacher set deptno=40 where tno=1002
update teacher set sal=sal+2000 where tname like '陈%'
实例
--女性老师编号、姓名
--姓韩的女性老师编号、姓名、身份证号
--薪水在[10000,20000)之间老师列表
--岗位为讲师或者研发,按照薪水降序排序
--部门所有数据的INSERT语句
select tno,tname,gender from teacher where gender='女'
select tno,tname,gender from teacher where gender<>'男'
select tname,sal from teacher where sal>=10000 and sal<20000
select * from teacher where tno between 1006 and 1010 and tno<>1010
select * from teacher where job in ('讲师','研发') order by sal desc
(能用exist就不要用in)
3.增加数据
insert into dept(deptno,dname,loc) values(2,'aa','table')
(oracle中双引号引用的是关键字)
create table "table"(id number(4))
4.删除表空间,表名是关键字的表,只能用sql语句删除
drop table "abc"
select 'insert into dept values('||deptno||','''||dname||''''||','''||loc||''');' from dept;
--招生部门所有男老师姓名
--所有老师姓名、部门名称
--所有在招生部、人力部的老师列表
select * from teacher where deptno=
(select deptno from dept where dname='招生部') and gender='男'
select tname,(select dname from dept where deptno=teacher.deptno)
部门名称 from teacher
select * from teacher where deptno in
(select deptno from dept where dname in('招生部','人力部'))
子查询
--exists all any
select * from teacher where exists
(select deptno from dept where deptno=teacher.deptno and dname in('招生部','人力部'))
--查询比所有营销专员工资高的老师
--查询比任一个营销专员工资高的老师
select * from teacher where sal>(select max(sal) from teacher where job='营销专员' );
select * from teacher where sal>(select min(sal) from teacher where job='营销专员' )
select min(sal) from teacher where job='营销专员' ;
是不是所有的子查询都可以独立运行(不能,当有隐式连接,连接另一个表)
当一个查询的结果是另一个查询的条件时,称之为子查询。