select * from emp
conn sys/change_on_install as sysdba--sys 必须加上 as sysdba
show user
conn system/manager
show user
set linesize 300
set pagesize 30
select * from scott.emp--sys用户下访问emp
select * from dept
select empno,ename,job from emp
select empno 编号,ename 姓名 ,job 工作 from emp
select job from emp
select distinct job from emp --distinct 消除重复值
select distinct empno,job from empselect * from emp
select '编号是：'||empno||'的员工姓名：'||ename||'工作是：'||job from emp--||是连接符
select ename, sal *12 from emp;
select ename name , sal *12 income from emp;--name ,income 为别名
select * from emp where sal>1500
select ename,comm from emp where comm is not null
select ename from emp where comm is null
select *from emp where sal>1500 and comm is not null
select * from emp where sal >1500 or comm is not null
select * from emp where not( sal>1500 and comm is not null)--not取反
select * from emp where sal>1500and sal<3000
select * from emp where sal between 1500 and 3000 -->=1500 <=3000
select * from emp where hiredate between '1-1月-81'and'31-12月-81'--日期写法 要加‘
select * from emp where ename='KING'
select * from emp where empno=7369 or empno =7499--or 后面写全
select * from emp where empno in (7369,7499,7521)
select * from emp where empno not in (7369,7499,7521)
select * from emp where ename in ('MARTIN','ALLEN','KING')
select * from emp where ename like '_M%'--like进行模糊查询 _可以匹配一个字符长度
select * from emp where ename like '%M%'--%可以匹配任意长度的内容
select * from emp where hiredate like '%81%'
select * from emp where ename like '%' --全部被查询
select * from emp where sal like '%5%'
select * from emp where empno !=7788
select * from emp where empno <>7788
select * from emp order by sal --默认升序
select * from emp order by sal asc
select * from emp order by sal desc
select * from emp where deptno=30 order by sal desc ,hiredate asc --如果sal相等再按hiredate排序
select upper('smith') from dual --dual 为临时表
select * from emp where ename=upper('smith')
select lower('hello world') from dual
select initcap(ename) from emp--第一个字母大写
select concat ('hello', 'world') from dual--concat 用法：连接和||作用相同
select substr('hello',1,3) 截取字符串,--substr截取点从0、1开始都可以
length('hello')字符串长度,
replace('hello','l','x')字符串替换
from dual
select ename,substr(ename,length(ename)-2) from emp
select ename,substr(ename,-3)from emp
select ename,substr(ename,-3,3)from emp --负数
select sysdate from dual --sysdate显示当前时间
select round(789.536) from dual --round 四舍五入
select round(789.536,2) from dual
select round(789.536,-2) from dual--对整数进行四舍五入
select trunc (789.536) from dual --不进行四舍五入
select trunc (789.536，2) from dual--两位小数，不四舍五入
select mod(10,3) from dual--取余
select sysdate from dual--系统时间
select empno,ename,round((sysdate-hiredate)/7)from emp--求出星期数
select empno,ename,round （months_between(sysdate,hiredate)，1）from emp--求出月数
select add_months(sysdate,4)from dual --输出相加之后的日期结果
select next_day(sysdate,'星期一') from dual --输出下一个指定日期
select last_day(sysdate) from dual --求出指定日期的最后一天
select empno ,ename,to_char(hiredate,'yyyy') year,
to_char(hiredate,'mm') month,
to_char(hiredate,'dd') from emp
select empno,ename,to_char（hiredate,'yyyy-mm-dd'）from emp
select empno,ename,to_char（hiredate,'fmyyyy-mm-dd'）from emp--fm的作用去掉前导0
select empno ,ename, sal from emp
select empno ,ename, to_char(sal,'$99,999')from emp
select to_number(123)+to_number(123) from dual --to_number将字符串转为数字
select to_date('2013-09-30','yyyy-mm-dd') from dual --to_date将字符串转为date格式
select empno,ename,(sal+comm)*12年薪 from emp
select empno,ename ,nvl(comm,0),(sal+nvl(comm,0)*12 income from emp--nvl很重要，可以将null转为0
select decode(1,1,'内容是1',2,'内容是2',3,'内容是3') from dual
select empno 雇员编号 ,ename 雇员姓名 ,hiredate 雇佣日期 ,
decode( job,'CLERK','业务员','SALESMAN','销售人员','MANAGER','经理','ANALYST','分析员','PRESIDENT','总裁') 职位
from emp
select * from emp where deptno=30
select ename, empno,job,deptno from emp where job= 'CLERK'
select * from emp where comm> sal
select * from emp where comm> sal*0.6
select * from emp
where (deptno=10 and job='MANAGER')
or(deptno=20 and job='CLERK')
or(not job ='CLERK' and not job ='MANAGE' and sal>2000)
select * from emp where comm is null or comm<100
select * from emp where last_day(hiredate)-2=hiredate
select * from emp where months_between(sysdate,hiredate)/12>12
select initcap(ename)from emp
select ename from emp where length(ename)>5
select * from emp where ename not like '%R%'
select ename,substr(ename,0,3)from emp
select * from emp order by hiredate
select ename ,job,sal from emp order by job desc,sal
select ename ,to_char(hiredate,'mm')mon,to_char(hiredate,'yyyy') year from emp order by mon,year
select ename, round(sal/30)日薪 from emp
select * from emp where to_char (hiredate,'mm')=2
select ename, round(sysdate-hiredate) from emp
select replace(ename,'A','a')from emp
select ename from emp WHERE ename like '%A%'
select ename, trunc(months_between(sysdate,hiredate)/12) year,
trunc(mod(months_between(sysdate,hiredate),12)) mon
from emp
select ename, trunc(months_between(sysdate,hiredate)/12) year,
trunc(mod(months_between(sysdate,hiredate),12)) mon,
trunc(mod(sysdate-hiredate,30))day
from emp
select * from emp,dept
select count(*) from emp `
select * from emp e, dept d WHERE e.deptno=d.deptno
select e.empno,e.ename,d.deptno,e.ename,d.loc
from emp e,dept d
where d.deptno=e.deptno
select e.empno,e.ename,e.job,m.ename 领导
from emp e,emp m
where e.empno=m.mgr--查找雇员的直接上级的姓名用emp和emp自己相关联
select e.ename,e.job,m.ename 领导,d.dname
from emp e,emp m,dept d
where e.mgr=m.empno
and e.deptno=d.deptno
select * from salgrade
select * from emp
select e.ename, e.sal, d.dname, s.grade
from emp e, dept d, salgrade s
where e.deptno=d.deptno
and e.sal between s.losal and s.hisal
select e.ename 姓名, e.sal 工资, d.dname 部门名称, s.grade 工资等级, m.ename 领导姓名, m.sal 领导工资, ms.grade 领导工资等级
from emp e, dept d, salgrade s, emp m, salgrade ms
where e.deptno=d.deptno
and e.sal BETWEEN s.losal and s.hisal
and e.mgr=m.empno
and m.sal BETWEEN ms.losal and ms.hisal
select e.ename, e.sal, d.dname,
decode(s.grade,1,'第五等级',2,'第四等级',3,'第三等级',4,'第二等级',5,'第一等级') 工资等级，m.ename, m.sal
from emp e, dept d, salgrade s, emp m, salgrade ms
where e.deptno=d.deptno
and e.sal between s.losal and s.hisal
and e.mgr =m.empno
and m.sal BETWEEN ms.losal and ms.hisal
select * from dept
select e.empno,e.ename,d.deptno,d.dname,d.loc
from emp e,dept d
where e.deptno=d.deptno
select e.empno,e.ename,m.empno,m.ename
from emp e,emp m
where e.mgr=m.empno
select * from emp cross join dept--sql1999语法，产生笛卡尔积
select * from emp NATURAL join dept--自动进行关联字段的匹配
select * FROM emp join dept using (deptno) where deptno=30
select * from emp join dept on(dept.deptno=emp.deptno)
--组函数 count max min avg sum
select count(*)from emp
select count(empno),sum(sal),avg(sal)from emp
select count(*) from emp;
select deptno,count (empno),round(avg (sal),2)
from emp
group by deptno
select comm , count(empno),avg(sal)
from emp
group by comm
--1.程序中存在group by 则程序可以使用分组函数
--2.如果不使用分组的话，则只能单独的使用分组函数
select deptno,count(empno) from emp--error 如果使用分组函数，不能出现分组函数和分组条件之外的字段
select count(empno) from emp group by empno
select deptno,empno,count(empno) from emp group by deptno--error
select deptno,count(empno) from emp group by deptno
select d.dname,count(e.empno)
from dept d, emp e
where d.deptno=e.deptno
group by d.dname
select deptno ,avg(sal) from emp where avg(sal)>2000 group by deptno--error 分组函数只能用在分组中，不允许在where 语句中出现
select deptno ,avg(sal) from emp group by deptno having avg(sal)>2000
select d.dname,count(e.empno) 公司人数 from dept d,emp e
where d.deptno=e.deptno
group by d.dname
select deptno,avg(sal) 平均工资 from emp having avg(sal)>2000 group by deptno--此处用having而不是where，where用于单行
select * from emp where job != 'SALSEMAN'
select job,sum(sal) sumsal from emp
where job !='SALSEMAN'
group by job having sum(sal) >4500
order by sumsal desc
select deptno,max(avg(sal))--error 分组函数可以嵌套，但是在组函数嵌套使用的时候不能再出现分组条件的查询语句
from emp
group by deptno
select max(avg(sal))
from emp
group by deptno
select * from emp where sal>(select sal from emp where empno=7654)--子查询必须在() 中编写
select * from emp where sal>(select sal from emp where empno=7654) and job=(select job from emp where empno=7788)
select min(sal) from emp
select ename,job,sal from emp where sal=(select min(sal) from emp)
select deptno,count(empno),avg(sal) from emp group by deptno
select d.dname,n.c,a,e.ename
from dept d,(select deptno,count(empno) c ,avg(sal) a ,min(sal) m from emp group by deptno) n,emp e
where n.deptno=d.deptno and e.sal=n.m
select * from emp where sal in(select min(sal) from emp group by deptno)
select * from emp where sal =any (select min(sal) from emp group by deptno)--=any 与in功能相同
select * from emp where sal >any (select min(sal) from emp group by deptno)-->any比最小值大
select * from emp where sal <any (select min(sal) from emp group by deptno)--<any 比最大值还小
select * from emp where sal <all (select min(sal) from emp group by deptno)--<all 比最小值小
select * from emp where sal >all (select min(sal) from emp group by deptno)-->all 比最大值大
select * from emp
where (sal,nvl(comm,-1) ) in (select sal,nvl(comm,-1) from emp where deptno=20)
create table myemp as select * from emp--复制
select * from myemp
drop table myemp
Insert into myemp(empno,ename,job,mgr,hiredate,sal ,comm,deptno)
Values (7899,'张三','清洁工',7369,'23-9月-2013',9000, 300,40)
insert into myemp values(8899,'李四'，'清洁工',7369,'23-9月-2013',9000,300,40)
insert into myemp (empno,ename,)
insert into myemp (empno,ename,job,hiredate,sal,deptno)
values(7889,'王五'，'清洁工'，'14-2月-1995',9000,40)
insert into myemp values (8889,'王五'，'清洁工',null,'14-2月-1995',9000,null,40)
Insert into myemp(empno,ename,job,hiredate,sal ,deptno)
Values (7898,'张三','清洁工',TO_DATE('2009-07-19','yyyy-mm-dd'),9000, 40);
update myemp set comm=1000
update myemp set sal=5000 where empno=7899
update myemp set mgr=null where empno=7899
update myemp set mgr=null,comm=null where empno in(7369,8899,7788)--在进行数据库操作时，一定要写上更新条件，拒绝批量更新
delete from myemp where empno=7899
delete from myemp where comm is not null
delete from myemp--不指定删除条件，表示删除表中所有记录
select * from myemp
drop table emp10
create table emp10 as select * from emp where deptno=10
delete from emp10 where empno=7782
select * from emp10
rollback--回滚
commit--提交
--查询练习
--至少有一个员工的部门
select deptno,count(empno) from emp group by deptno
select deptno,count(empno) from emp group by deptno having count(empno)>1
--列出薪水比SMIYH 高的所有员工
select * from emp where sal >(select sal from emp where ename='SMITH')
--列出所有员工的姓名的姓名和直接上级的姓名
select e.ename,m.ename from emp e,emp m where e.mgr=m.empno
--列出受雇日期早于直接上级的所有员工的编号，姓名，部门名称
select e.empno,e.ename ,d.dname from emp e,emp m, dept d where e.mgr=m.empno and e.hiredate<m.hiredate and d.deptno=e.deptno
--列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门 左右关联问题
select d.deptno,d.dname,e.empno,e.ename from dept d,emp e where d.deptno=e.deptno(+)
--列出所有CLERK的姓名及其部门名称，部门人数
select ename，deptno from emp where job='CLERK'
select e.ename,d.dname from emp e,dept d where e.deptno=d.deptno and job='CLERK'
select e.ename,d.dname,ed.cou
from emp e,dept d,(select deptno,count(empno) cou from emp group by deptno ) ed
where e.deptno=d.deptno and job='CLERK' and ed.deptno=e.deptno
--列出最低薪水大于1500的各种工作及从事该工作的全部雇员人数
select job ,min(sal) from emp group by job having min(sal)>1500
select e.job ,count(e.empno) from emp e
where e.job in (select job from emp group by job having min(sal)>1500)
group by e.job
--列出在部门SALES工作的员工姓名，假定不知道销售部门的编号
select deptno from dept where dname='SALES'
select ename from emp where deptno=(select deptno from dept where dname='SALES')
--列出薪水高于公司平均薪水的所有员工，所在部门，上级领导，公司的工资等级
select avg(sal) from emp
select e.ename,d.dname,m.ename,s.grade
from emp e,emp m,dept d,salgrade s
where e.sal>(select avg(sal) from emp) and e.deptno=d.deptno and e.mgr=m.empno(+) and e.sal between s.losal and s.hisal
--列出与scott从事相同工作的所有员工及部门名称
select job from emp where ename='SCOTT'
select ename,dname from emp e,dept d where job=(select job from emp where ename='SCOTT') and d.deptno=e.deptno
select ename,dname
from emp e,dept d
where job=(select job from emp where ename='SCOTT') and d.deptno=e.deptno and e.ename!='SCOTT'
--列出薪金等于部门30中员工的薪金的所有员工的姓名和薪金
select sal from emp where deptno=30
select ename ,sal from emp where sal in (select sal from emp where deptno=30) and deptno!=30
--列出薪金高于在部门30中员工的薪金的所有员工姓名和薪金、部门名称
select ename,sal,dname from emp e ,dept d where sal >all(select sal from emp where deptno=30)and e.deptno!=30 and e.deptno=d.deptno
--列出每个部门工作的员工数量、平均工资和平均服务年限
select d.dname,count(empno),avg(sal),avg(months_between(sysdate,hiredate)/12)
from emp e ,dept d where e.deptno=d.deptno group by d.dname
--列出所有员工的姓名、部门名称和工资
select ename,dname,sal from emp e ,dept d where d.deptno=e.deptno
--列出所有部门的详细信息和人数
select deptno ,count(empno) cou from emp group by deptno
select d.*,ed.cou from dept d ,(select deptno ,count(empno) cou from emp group by deptno) ed where d.deptno=ed.deptno(+)
--列出各种工作的最低工资及从事此工作的雇员姓名
select job,min(sal) from emp group by job
select * from emp e,(select job,min(sal) s from emp group by job) ed where sal=s
--列出各个部门的经理的最低薪金
select deptno,min(sal) from emp where job='MANAGER' group by deptno
--列出所有员工的年工资，按年薪从低到高的排序
select ename,(sal+nvl(comm,0))*12 income from emp order by income
--列出某个员工的上级主管，并要求这些主管中的薪水超过3000
select distinct m.* from emp e, emp m where e.mgr=m.empno and m.sal>3000
--列出部门名称中带S字符的部门的，工资合计，部门人数
select deptno,sum(sal),count(empno)
from emp
where deptno in (select deptno from dept where dname like '%S%' )
group by deptno
--给任职日期超过10年的人加薪10%
update emp set sal=sal*(1+0.1) where (months_between(sysdate,hiredate)/12)>10
select * from emp
insert into emp values(7839,'KING','PRESIDENT',NULL,to_date('1981-11-17','yyyy-mm-dd') ,5000, NULL, 10);
create table temp as (select * from emp )
select * from temp
drop table temp
create table person(
pid varchar2(18),
name varchar2(200),
age number(3),
birthday date,
sex varchar2(2) default'男'
);
insert into person(pid,name,age,birthday,sex)values('111111111111111111','张三',30，to_date('1983-09-29','yyyy-mm-dd'),'女')
insert into person(pid,name,age,birthday,sex)values('22222222222222222','李四',20，to_date('1993-09-29','yyyy-mm-dd'),'男')
update person set pid=222222222222222222 where pid=22222222222222222
select * from person
drop table person--删除表
create table person(
pid varchar2(18),
name varchar2(200),
age number(3),
birthday date,
sex varchar2(2) default'男',
address varchar2(200)
);
alter table person add(address varchar2(200) default'暂无地址' )--增加字段
alter table person modify (name varchar2(20) default '无名氏')--修改表结构 如果name里已经含有大于20的数据，则无法将长度修改为20
insert into person(pid,age,birthday)values('333333333333333333',32,to_date('1981-10-01','yyyy-mm-dd'))
rename person to tperson
truncate table tperson--截断表，需要清空一张表不需要回滚时
create table national (
name varchar2(30)
);
insert into national (name) values('中国');
insert into national (name) values('美国');
insert into national (name) values('巴西');
insert into national (name) values('荷兰');
select * from national
select t1.name,t2.name
from national t1,national t2
where t1.name<>t2.name
create table person(
pid varchar2(18)primary key,
name varchar2(200),
age number(3),
birthday date,
sex varchar2(2) default'男'
);
insert into person(pid,name,age,birthday,sex)values('111111111111111111','张三',30，to_date('1983-09-29','yyyy-mm-dd'),'女')
insert into person(pid,name,age,birthday,sex)values('222222222222222222','李四',20，to_date('1993-09-29','yyyy-mm-dd'),'男')
insert into person(pid,name,age,birthday,sex)values(null,'李四',20，to_date('1993-09-29','yyyy-mm-dd'),'男')
create table person(
pid varchar2(18),
name varchar2(200),
age number(3),
birthday date,
sex varchar2(2) default'男' ,
constraint person_pid_pk primary key(pid)--person_pid_pk即为约束条件的名字
);
drop table person
create table person(
pid varchar2(18),
name varchar2(200) not null,
age number(3) not null,
birthday date,
sex varchar2(2) default'男' ,
constraint person_pid_pk primary key(pid),
constraint person_name unique(name)--使用constraint 可以指定约束的名称，即可不适用自动的 约束名称编号
);
insert into person(pid,name,age,birthday,sex)values('111111111111111111','张三',30，to_date('1983-09-29','yyyy-mm-dd'),'女')
insert into person(pid,name,age,birthday,sex)values('222222222222222222','张三',30，to_date('1983-09-29','yyyy-mm-dd'),'女')
create table person(
pid varchar2(18),
name varchar2(200) not null,
age number(3) not null check(age between 0 and 100),
birthday date,
sex varchar2(2) default'男' check(sex in ('男'，'女')) ,
constraint person_pid_pk primary key(pid),
constraint person_name unique(name)
);
insert into person(pid,name,age,birthday,sex)values('111111111111111111','张三',300，to_date('1983-09-29','yyyy-mm-dd'),'女')
create table person(
pid varchar2(18),
name varchar2(200) not null,
age number(3) not null ,
birthday date,
sex varchar2(2) default'男' ,
constraint person_pid_pk primary key(pid),
constraint person_name unique(name),
constraint person_age_ck check(age between 0 and 100),
constraint person_sex_ck check(sex in ('男'，'女'))
);
insert into person(pid,name,age,birthday,sex)values('111111111111111111','张三',300，to_date('1983-09-29','yyyy-mm-dd'),'女')
insert into person(pid,name,age,birthday,sex)values('111111111111111111','张三',30，to_date('1983-09-29','yyyy-mm-dd'),'无')
create table person(
pid varchar2(18),
name varchar2(200) not null,
age number(3) not null ,
birthday date,
sex varchar2(2) default'男' ,
constraint person_pid_pk primary key(pid),
constraint person_name unique(name),
constraint person_age_ck check(age between 0 and 100),
constraint person_sex_ck check(sex in ('男'，'女'))
);
create table book(
bid number primary key not null,
bname varchar2(30),
bprice number(5,2),
pid varchar2(18)
);
insert into person(pid,name,age,birthday,sex)values('111111111111111111','张三',300，to_date('1983-09-29','yyyy-mm-dd'),'女')
insert into book(bid,bname,bprice,pid)values (1,'JAVA',89.9,111111111111111111)
insert into book(bid,bname,bprice,pid)values (2,'JAVA',89.9,000000000000000000)
drop table person
drop table book
create table person(
pid varchar2(18),
name varchar2(200) not null,
age number(3) not null ,
birthday date,
sex varchar2(2) default'男' ,
constraint person_pid_pk primary key(pid),
constraint person_name unique(name),
constraint person_age_ck check(age between 0 and 100),
constraint person_sex_ck check(sex in ('男'，'女'))
);
create table book(
bid number primary key not null,
bname varchar2(30),
bprice number(5,2),
pid varchar2(18),
constraint person_book_pid_fk foreign key(pid) references person(pid)
);
insert into book(bid,bname,bprice,pid)values (2,'JAVA',89.9,000000000000000000)
drop table book
drop table person
drop table book cascade constraint--不管约束直接删除，这种做法一般不用
--使用外键时应注意在子表中设置的外键在父表中必须是主键，删除子表再删除父表
insert into emp(empno,ename,job,sal,mgr,hiredate,comm,deptno)
values (8888,'张三','经理',9000,null,sysdate,null,50) --error 无法找到父项的关键字
insert into person(pid,name,age,birthday,sex)values('111111111111111111','张三',30，to_date('1983-09-29','yyyy-mm-dd'),'女')
insert into book(bid,bname,bprice,pid)values (1,'JAVA',89.9,111111111111111111)
delete from person where pid='111111111111111111'--error 因为在子表中存在此项的关联，所以无法删除
--如果希望一个表中的数据在删除时，可以自动删除掉其对应的子表记录，则可以使用联级删除的操作
create table person(
pid varchar2(18),
name varchar2(200) not null,
age number(3) not null ,
birthday date,
sex varchar2(2) default'男' ,
constraint person_pid_pk primary key(pid),
constraint person_name unique(name),
constraint person_age_ck check(age between 0 and 100),
constraint person_sex_ck check(sex in ('男'，'女'))
);
create table book(
bid number primary key not null,
bname varchar2(30),
bprice number(5,2),
pid varchar2(18),
constraint person_book_pid_fk foreign key(pid) references person(pid) on delete cascade
);
insert into person(pid,name,age,birthday,sex)values('111111111111111111','张三',30，to_date('1983-09-29','yyyy-mm-dd'),'女')
insert into book(bid,bname,bprice,pid)values (1,'JAVA',89.9,111111111111111111)
delete from person where pid='111111111111111111'--删除成功
drop table person cascade constraint
create table person(
pid varchar2(18),
name varchar2(200) not null,
age number(3) not null ,
birthday date,
sex varchar2(2) default'男'
);
alter table person add constraint person_pid_pk primary key(pid)
alter table person add constraint person_name unique(name)
alter table person add constraint person_age_ck check(age between 0 and 100)
alter table person add constraint person_sex_ck check(sex in ('男'，'女'))
drop table book
create table book(
bid number,
bname varchar2(30),
bprice number(5,2),
pid varchar2(18)
);
alter table book add constraint book_bid_PK primary key(bid)
alter table book add constraint person_book_pid_fk foreign key(pid) references person(pid) on delete cascade
alter table person drop constraint person_age_ck
alter table person drop constraint person_sex_ck
alter table book drop constraint person_book_pid_fk
select rownum,empno,ename,job,sal,hiredate from emp
select rownum,empno,ename,job,sal,hiredate from emp where rownum<=5
select rownum,empno,ename,job,sal,hiredate from emp where rownum between 5 and 10--未选定行
--如果想用中间截取，则只能用子查询
select * from (select rownum rm ,empno,ename,job,sal,hiredate from emp where rownum <=10) temp
where temp.rm>5
select * from (select rownum rm ,empno,ename,job,sal,hiredate from emp where rownum <=15) temp
where temp.rm>10
create table emp20 as select * from emp where deptno=20
--union 并：将多个查询结果组合到一个查询结果中，没有重复值
create table emp20 as select * from emp where deptno=20
select * from emp union select * from emp20
--union all 可以返回两个查询的重复内容
select * from emp union all select * from emp20
--intersect 交：返回多个查询结果中相同的部分
select * from emp intersect select * from emp20
--minus 差：返回两个查询结果的差集
select * from emp minus select * from emp20
drop table sporter cascade constraint
create table sporter(
sporterid number(4) primary key not null,
name varchar2(50) not null,
sex varchar2(2) not null,
department varchar2(30) not null,
constraint sporter_sex_ck check (sex in ('男','女'))
);
create table item (
itemid varchar2(4) primary key not null,
itemname varchar2(50) not null,
location varchar2(50) null
);
create table grade(
sporterid number(4),
itemid varchar2(4),
mark number(2),
constraint sporter_grade_sporter_fk foreign key(sporterid) references sporter(sporterid) on delete cascade,
constraint sporter_item_fk foreign key(itemid) references item(itemid) on delete cascade,
constraint grade_mark_ck check (mark in (6,4,2,0))
);
insert into sporter(sporterid,name,sex,department)values(1001,'李明','男','计算机系')
insert into sporter(sporterid,name,sex,department)values(1002,'张三','男','数学系')
insert into sporter(sporterid,name,sex,department)values(1003,'李四','男','计算机系')
insert into sporter(sporterid,name,sex,department)values(1004,'王二','男','物理系')
insert into sporter(sporterid,name,sex,department)values(1005,'李娜','女','心理系')
insert into sporter(sporterid,name,sex,department)values(1006,'孙俪','女','数学系')
insert into item (itemid,itemname,location)values('x001','男子五千米','一操场')
insert into item (itemid,itemname,location)values('x002','男子标枪','一操场')
insert into item (itemid,itemname,location)values('x003','男子跳高','二操场')
insert into item (itemid,itemname,location)values('x004','女子跳高','二操场')
insert into item (itemid,itemname,location)values('x005','女子三千米','三操场')
insert into grade (sporterid,itemid,mark)values(1001,'x001',6)
insert into grade (sporterid,itemid,mark)values(1002,'x001',4)
insert into grade (sporterid,itemid,mark)values(1003,'x001',2)
insert into grade (sporterid,itemid,mark)values(1004,'x001',0)
insert into grade (sporterid,itemid,mark)values(1001,'x003',4)
insert into grade (sporterid,itemid,mark)values(1002,'x003',6)
insert into grade (sporterid,itemid,mark)values(1004,'x003',2)
insert into grade (sporterid,itemid,mark)values(1005,'x004',6)
insert into grade (sporterid,itemid,mark)values(1006,'x004',4)
--求出目前总积分最高的系名，及其积分
select s.department,sum(g.mark) from sporter s,grade g where s.sporterid=g.sporterid group by s.department order by sum(g.mark) desc
select * from (select s.department,sum(g.mark)
from sporter s,grade g
where s.sporterid=g.sporterid group by s.department
order by sum(g.mark) desc)
where rownum=1
--求出在一操场进行比赛的个项目名称及其冠军的姓名
delete from grade where sporterid = 1004
drop table grade
select * from grade
show recyclebin--显示缓存
Create table myemp AS select * from emp;--复制emp
purge recyclebin--清空垃圾箱
select * from tperson
insert into myemp (empno,ename,job,mgr,hiredate,sal,comm,deptno)
values(8000,'张三','清洁工'，7839，to_date('1993-05-05','yyyy-mm-dd'),5000,1000,20)
--只有commit才能看见
select * from DEPT
rollback
select * from myemp
insert into myemp values(1111,'李四','清洁工',7369,sysdate,1234,4000,10)
select * from myemp
delete from myemp where empno=1111
commit
update myemp set job='MANAGER' where empno=7369
update myemp set mgr=null,comm=null where empno in (7369,8899,7788)
delete from emp where empno=7369
select * from myemp
rollback
select * from emp as of timestamp to_Date('2013-09-01 22:05:00', 'yyyy-mm-dd hh24:mi:ss')--恢复delete删除的数据
delete from myemp where empno in (7369,8000,8888,1111)
delete from myemp where comm is not null
create table emp10 as select * from emp where deptno=10
select * from emp10
delete from emp10 where empno=7782
扫一扫
04-10
5761
5761
11-29
1087
1087
02-09
1044
1044
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
