ch01 oracle基础一

本章任务

1:网络配置
--	1、在服务端要启动两个服务
		OracleListener:监听器
		OracleServiceDB:数据库
	2、通过net Manager配置网络连接,一定要保存
		网络服务名
		用tcp/ip协议
		输入服务器主机fangyong(192.168.1.81)
		输入数据库名db(ORCL)
		测试
		保存
	3、进入oracle的企业管理控制器，企业管理器的用户名及密码分是system  system,
		加入用户，给两个角色Connect及Resource，给一个权限
		Unlimited tablespace
	4、cmd进入命令行，输入如下内容
		sqlplus 用户名/密码@网络服务名
		sqlplus 3t10001/123456@ORCL
	5、通过pl/sql developer，输入用户及密码进行操作。

2:oracle的基本sql操作
--sqlplus

desc emp//员工表

desc dept//部门

desc salgrade//工资等级

//年薪
select ename,sal*12 from emp;

//answer
select 2*3 fro emp;

//一张空表
desc dual
//answer
select 2*3 fro dual

//获取系统时间
select sysdate from dual;

//查看当前用户的津贴
select ename,comm from emp;

//查看雇员一年的工资sal+comm
select ename,sal*12+comm from emp;
//任何含有空值的数据表达式都是空值
select ename,sal,comm from emp where comm is null;
select ename,sal,comm from emp where comm is not null;
select ename,sal*12+nvl(comm,0) from emp

//字符串连接符
select ename||sal from emp;

//字符串的表示
select ename || 'accp' from emp;

//字符串中含'引号的处理,用两个单引号表示一个
select ename||'''accp''' from emp

select distinct deptno from emp;

//只是两个重复的组合被去掉了
select distinct deptno,job from emp;

//字符比较
select ename,sal from emp where ename>'CBA'

select ename,sal from emp where sal between 800 and 1500;
select ename,sal from emp where sal >=800 and sal<=1500;
select ename,sal from emp where sal in (800,1500,2000);
select ename,sal from emp where ename in ('sa','a','b');

//时间
select ename,sal,hiredate from emp where hiredate>'03-12月-81';

select to_char(hiredate,'YYYY-MM-DD HH:MM:SS') from emp
select to_char(sysdate,'YYYY-MM-DD HH:MM:SS') from dual
select to_char(sysdate,'YYYY-MM-DD HH24:MM:SS') from dual
select ename,hiredate from emp  where hiredate > to_date('1981-02-20 12:34:56','YYYY-MM-DD HH24:MI:SS')


//模糊查询
横线代表一个字母
select ename from emp where ename like '%ALL%';
select ename from emp where ename like '_A%';
//模糊查询时含有%的处理,采用转译字符
select ename from emp where ename like '%\%%';
//自定义转译字符
select ename from emp where ename like '%$%%' escape '$';

//排序
select ename,sal,deptno from emp order by deptno asc,ename desc;

//字符处理
select lower(ename) from emp;
//查看第二个字母是否是小写
select ename from emp where lower(ename) like '_a%'

select substr(ename,2,3) from emp;//重第二个字符开始截取，一共截取3个字符

//将数字转换为字母
select chr(65) from dual;

//得到某个字母的ASCII值
select ascii('A') from dual;

//四舍五入
select round(23.652) from dual;
->24
select round(23.652,2) from dual;
->23.65,后面小数点两位
select round(23.652,-1) from dual;
->20

//财务中的千位
select to_char(sal,'$99,999.9999') from emp;
select to_char(sal,'L99,999.9999') from emp;
L代表本地货币
select to_char(sal,'L00000,0000') from emp

to_number


select max(sal) from emp;
select min(sal) from emp;
select avg(sal) from emp;
select to_char(avg(sal),'99999999.99') from emp
select round(avg(sal),2) from emp
select sum(sal) from emp
select count(*) from emp;
select count(distinct deptno) from emp;

//部门的平均薪水
select deptno,avg(sal) from emp group by deptno;

//挣钱最多的那个人
select ename,sal from emp where sal=(select max(sal) from emp)

//平均薪水大于2000的 部门
select avg(sal),deptno from emp group by deptno having avg(sal)>2000

//大于平均薪水的人员
select ename,sal from emp where sal>(select avg(sal) from emp)

//各个部门谁挣的钱最多
select ename,sal from emp join (select max(sal) max_sal,deptno from emp group by deptno) t on (emp.sal=t.max_sal and emp.deptno = t.deptno)

//求员工对应的经理人的名字
自连接
select empno,ename,mgr from emp
select e1.ename ename1,e2.ename ename2 from emp e1,emp e2 where e1.mgr=e2.empno

//交叉连接
select ename,dname from emp cross join dept

//where里面只写数据过滤
//两张表的连接
select ename,dname from emp join dept on (emp.deptno=dept.deptno)
select dname,dname from emp join dept using(deptno)

//两张表的非等值连接
select ename,grade from emp e join salgrade s on (e.sal between s.losal and s.hisal)

//三个表的连接
select ename,dname,grade from emp e join dept d on(e.deptno=d.deptno) join salgrade s on (e.sal between s.losal and s.hisal) where ename not like '_A%';

左外连接
select e1.ename,e2.ename from emp e1 left join emp e2 on(e1.mgr=e2.empno)

右外连接
select e1.ename,e2.ename from emp e1 right join emp e2 on(e1.mgr=e2.empno)


部门平均工资信息
conn sys/a as sysdba;
grant create view to scott;

create view v$_dept_sal_info as
select deptno,grade,avg_sal from (select deptno,avg(sal) from emp group by deptno) t join salgrade s on (t.avg_sal between s.losal and s.hisal)
;

select * from v$_dept_avg_sal_info


1--backup scott
   exp

2--create user
   create user sa identified by sa default tablespace users quota 10M on users

   grant create session,create table,create view to sa;




3--import the date
   imp

create table emp3 as select * from emp;



求薪水最高的5名雇员

select empno,ename from emp where rownum <=5;

select ename from (select rownum r,ename from emp) where r>10

select ename,sal from emp where rownum <=5 order by sal desc

select ename,sal from (select ename,sal from emp order by sal desc) where rownum<=5

求薪水最高的第6-10名雇员
select ename,sal from (select ename,sal,rownum r from(select ename,sal from emp order by sal desc)) where r in


面试题目
select sname from s join sc on (s.sno=sc.sno) join c (c.cno = sc.cno) where c.cteacher <> 'liming';

select sname where sno in (select sno from sc where scgrade<60 group by sno having count(*)>=2)

select sno from sc where cno =1 ans sno in(select sno from sc where cno = 2)

----补充--
 	create table customer
	(
		customerName varchar2(20) primary key,
		age int,
		sex varchar2(20)
	);
	--基于一个表创建另一个表并加入数据
		create table customer1 as select * from customer;
	--只得到另一表的结构
		create table customer2 as select * from customer where 1=2;
		
	create  table buy
	(
		buyID integer primary key,
		buyName varchar2(20),
		amount number(10,2),
		price number(20,3),
		customerName varchar2(20),
		constraint customer_buy_fk  foreign key(customerName) references customer(customerName)
	);
  --增加'&'为参数--
  insert into buy values(&buyID,'&buyName',&amount,&price,'&customerName'); 
  --修改没有实现--
   update buy set buyName='c1' from buy,customer where buy.customerName=customer.customerName and buy.buyid=1;
  update buy set buyName='twoTable' from buy,customer where buy.customerName=customer.customerName and buy.buyID=3;
  --in--
  select * from buy where buyID in(1,2,3); 
  --like--
  select * from buy where buyName like 'a%';
  --between--
  select * from buy where between 1 and 3;
  --内连--
  select c.*,b.* from customer c,buy b;
  select * from customer,buy;
  select * from customer join buy on customer.customername=buy.customername;
  --左连--
  select c.*,b.* from customer c left join buy b on c.customerName=b.customerName;
  --右连--
   select c.*,b.* from customer c right join buy b on c.customerName=b.customerName;
   --联合--
   select * from customer union all select * from customer1;
   select * from customer union select * from customer1;
   --select 字段 from 表名 [where 条件 group by 字段  order by 字段 having 字段]--
   select customerName,sum(price) from buy where buyID<=10  group by customerName having sum(price)<=200 order by sum(price);

--判断--
	declare 
		    v_customer customer%rowtype;
		begin
		    select * into v_customer from customer where customerName='aa';
		    if(v_customer.sex='女') then
		               dbms_output.put_line('male');
		    elsif(v_customer.sex='男') then
		               dbms_output.put_line('fmale');
		    else
		               dbms_output.put_line('不清楚');
		    end if;
		end;
--case--
	declare
    		   score int;
    		begin
    		  score:=&a;
    		  case
    		      when score>=85 then dbms_output.put_line('优秀');
    		      when score<=84 and score>=75 then dbms_output.put_line('良好');
    		      when score<=74 and score>=60 then dbms_output.put_line('及格');
    		      else dbms_output.put_line('不及格');
 		  end case;
  		end;

  
本章目标
1:掌握oracle的网络配置

2:掌握oracle的基本sql操作