<Oracle 培训视频>~

sqlplus

sqlplusw

http://192.168.1.88:5560/isqlplus/

 

 

1. unlock user：
      alter user scott account unlock;

 

2. table_structures

 

sql> desc tablename;   查看表结构

varchar2  支持国际化

 

别名中有空格加双引号

select 2*3 "aaa bbb" from dual;

 

 两个单引号代表一个单引号

select 'aaa''bbb' from dual;

 

转义字符 "\"， escape "$"

 

3. function

(1) lower, upper

(2) substr

(3) chr(int)

(4) ascii('A')

(5) round(d, n)

(6) to_char()

     to_char(d, '$99,999.9999')    0: 没有数字，强制加0

     to_char(sysdate, 'YYYY-MM-DD HH:MI:SS')    

     to_char(sysdate, 'YYYY-MM-DD HH24:MI:SS')    

(7) to_number()

     to_number('$1,250','$999,999')

(8) nvl (comm,0) 

(9) count

     null不算， count(distinct empno)

 

4. group by

having

 

5. 子查询

 

6. SQL 1999

笛卡尔乘积

select ename,dname from emp
cross join dept

 

select ename,dname from emp
join dept on (emp.deptno = dept.deptno)

 

select ename,dname from emp
(inner) join dept using (deptno)

 

 

 select ename,dname from emp
left join dept on (emp.deptno = dept.deptno)

 

 

  select ename,dname from emp
right (outer) join dept on (emp.deptno = dept.deptno)

 

    select ename,dname from emp
full join dept on (emp.deptno = dept.deptno)

 

(1)求部门中哪些人的薪水最高

select ename,sal from emp
inner join (select deptno,max(sal) max_sal from emp group by deptno) t
on emp.deptno = t.deptno and emp.sal = t.max_sal

 

(2)求部门平均薪水的等级

select deptno,salgrade.grade from (select deptno,avg(sal) avg_sal from emp group by deptno) t
inner join salgrade on avg_sal between salgrade.losal and salgrade.hisal

 

(3)求部门平均的薪水等级

select deptno, avg(salgrade) from
(select deptno,salgrade.grade salgrade from emp
inner join salgrade on sal between salgrade.losal and salgrade.hisal) t
group by deptno

 

(4)雇员中有哪些是经理人

select * from emp
where emp.empno in (select distinct mgr from emp)

 

(5) 不用组函数求最高薪水

自连接比较，最大的not in

select * from emp where emp.sal not in
(select e1.sal from emp e1
inner join emp e2 on e1.sal < e2.sal)

 

(6) 求平均薪水最高的部门的部门编号

select t1.deptno, t1.avg_sal
  from (select deptno, avg(sal) avg_sal from emp group by deptno) t1
 where t1.avg_sal =
       (select max(t.avg_sal) max_avg_sal
          from (select deptno, avg(sal) avg_sal from emp group by deptno) t)

 

最多用主函数两次
select max(avg(sal)) from emp group by deptno

 

(7) 求平均薪水的等级最低的部门编号

 (select deptno, sg.grade from
(select deptno, avg(sal) avg_sal from emp group by deptno) t
inner join salgrade sg on t.avg_sal between sg.losal and sg.hisal) p

select * from p where p.grade = (select min(grade) from p)

 

(8) 求部门经理人中平均薪水最低的部门编号

 select ename from
(select ename,sal from emp
where emp.empno in (select distinct mgr from emp)) t
where t.sal = (select min(sal) from emp
where emp.empno in (select distinct mgr from emp))

 

(9)求比普通员工的最高薪水还要高的经理人名称

 

(10) 求薪水最高的前5名雇员

 select * from
(  select rownum r,t.* from
  (select empno,ename,sal from emp order by sal desc) t )
  where r <=5

 

(11) 求薪水最高的第6-10雇员

 select * from
(  select rownum r,t.* from
  (select empno,ename,sal from emp order by sal desc) t )
  where r >=6  and r <=10

 

 

 效率比较：

 select * from emp where deptno=10 and ename like '%A%'
select * from emp where ename like '%A%' and deptno=10

 

 

 1_create_tablespaces

CREATE TABLESPACE "DATA1" LOGGING DATAFILE 'c:\oracle\oradata\data1_1.ora' SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

 CREATE TEMPORARY
    TABLESPACE "ALS_TEMP" TEMPFILE 'c:\oracle\oradata\temp_1.ora'
    SIZE 500M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL UNIFORM SIZE 5M;

 

 2_create_users

 CREATE USER user1 IDENTIFIED BY user1
DEFAULT TABLESPACE "DATA1"
TEMPORARY TABLESPACE "TEMP_1";
GRANT CONNECT,RESOURCE,EXP_FULL_DATABASE,IMP_FULL_DATABASE TO user1;

 

import

imp system/als@orcl10g file=kewillfwd_meta_v30.dmp fromuser=kewillfwd_meta_v30 touser=kewillfwd

 

export

 exp userid=user1/user1@orcl10g owner=user1 rows=y indexes=y buffer=65536 feedback=1000 file=user1.dmp grants=y;

 

 exp  user1/user1@orcl11g owner=user1 file=t1.dmp,t2.dmp,t3.dmp filesize=4g buffer=209715200

 

 

 drop user xxx cascade;

 

 create table xxx as select * from tb1;

 

 insert into tb2 select * from tb1;

 

 7. Create table

 create table EMP
(
  EMPNO    NUMBER(4) not null,
  ENAME    VARCHAR2(10),
  JOB      VARCHAR2(9),
  MGR      NUMBER(4),
  HIREDATE DATE,
  SAL      NUMBER(7,2),
  COMM     NUMBER(7,2),
  DEPTNO   NUMBER(2)
)
tablespace USERS

 

8. Constraint

非空:  EMPNO    NUMBER(4) not null,

         ENAME    VARCHAR2(10) constraint stu_name not null,

 

alter table TB1
  add constraint XXX
  check ("F1" IS NOT NULL);

唯一:  EMPNO    NUMBER(4) unique,        空值不check

 

表级约束：

       constraint stu_name_email_unique unique (empno,deptno)

 

alter table TB1
  add unique (F2)
  using index
  tablespace USERS

 

主键:

    EMPNO    NUMBER(4)   primary key,

 

alter table TB1
  add primary key (F1)
  using index
  tablespace USERS

外键:

    constraint xxx foreign key (class) reference class(id)

 
check:

  constraint XXX check ("F1" IS NOT NULL);

 

 9. alter,drop table

 alter table stu add(addr varchar2(100))

 alter table stu drop (addr)

 alter talbe stu modify (addr varchar2(50))

 

 alter table stu drop constraint xxx;

 

10. Oracle Dictionaries

 

    user_tables, user_views, user_contraints, user_indexes

   

 select * from dictionary

 

 

 11. Index and View

 

create index xxx on tb(f1,f2)

drop index xxx

 

12. Sequence

 

 create sequence xxx;

select xxx.nextval from dual;

 

 数据库设计三范式

(1) 有主键

(2) 不存在部分依赖 （复合主键）

(3) 不存在层次依赖