sqlplus:
1.命令行访问：sqlplus
2.sqlplus
3.isqlplus: http://127.0.0.1:5560/isqlplus
问题：
scott 账户锁定，以管理员登陆，sqlplus sys/password as sysdba
conn sys/passwords as sysdba
解除锁定：alter user scott account unlock，执行退出命令，
用scott登陆，提示密码过期，修改，仍用tigger
描述：
desc emp 员工表
desc dept 部门表
desc salgrade 薪水等级
desc dual 空表
查询表达式：select 2*3 from dual;
查询日期：select sysdate from dual;
起别名：
select ename,sal*12 annual_sal from emp; //字段名转为大写
select ename,sal*12 "annual sal" from emp; //双引号的字段名不变
空值：
任何含有空值的表达式的值都为空值
连接符：
select ename||sal from emp;
select ename||'abcdefg' from emp;
用两个单引号代替一个单引号：select ename||'abddd''adfa' from emp;
distinct：
select distinct deptno from emp;
select distinct deptno,job from emp;
select ename,sal,deptno from emp where sal>1500 and deptno<>10;
select ename,sal,deptno from emp where sal between 1000 and 1500 and deptno<>10;
空值处理：
select ename sal,comm from emp where comm is null;
select ename sal,comm from emp where comm is not null;
select ename ,sal from emp where sal in(800,1500,2000);
select ename ,sal from emp where ename in('King','abc');
select ename ,sal from emp where sal not in(800,1500,2000);
模糊查询：
select ename from emp where ename like '%ALL%';
转义字符/，含有%
select ename from emp where ename like '%/%%';
排序：
select ename,sal,deptno from emp order by deptno asc,ename desc;（先按deptno正序排，内部再按ename反序排）
小结： select ename,sal*12 annual_sal from emp where ename not like '_A%' and sal >800 order by sal desc;
常用的SQL函数：
lower() select lower(ename) from emp;
upper() select upper(ename) from emp;
substr() select substring(ename,1,3) from emp; 从第一个开始截3个字符
chr() select chr(65) from dual; 把ANSII转为字符
ascii() select ascii('A') from dual
round() select round(23.234,1) from dual; 四舍五入
to_char() select to_char(sal,'$999,999.9999') from emp; 转换数字
select to_char(hiredate,'yyyy-mm-dd hh:mi:ss') from emp; 转换日期
select to_char(hiredate,'yyyy-mm-dd hh24:mi:ss') from emp;
to_date() select ename,hiredate from emp where hiredate > to_date('1981-2-20 12:34:56
','yyyy-mm-dd hh24:mi:ss');
to_number() select sal from emp where sal > to_number('$1,250.00','$9,999.99');
NVL() select ename,sal*12+NVL(comm,0) from emp; 如果comm是空值则用0代替
分组：
group by语句
select deptno,avg(sal) from emp group by deptno;
select ename from emp where sal=(select max(sal) from emp);
出现在select列表中的字段没有出现在子函数中必须出现在group by中
having
select avg(sal),deptno from emp group by deptno having avg(sal) > 2000;
对分组进行限制
select - where- group by -having -order by
SQL> select avg(sal)
2 from emp
3 where sal>1200
4 group by deptno
5 having avg(sal) > 1500
6 order by avg(sal) desc;
SQL> select ename,sal from emp
2 join (select max(sal) max_sal,deptno from emp group by deptno) t
3 on (emp.sal=t.max_sal and emp.deptno=t.deptno);
子连接：SQL> select e1.ename,e2.ename from emp e1,emp e2 where e1.mgr=e2.empno;
SQL> select ename,dname from emp cross join dept; 产生一个笛卡尔成绩cross
SQL> select ename,dname from emp ,dept where emp.deptno=dept.deptno; 旧语法
SQL> select ename,dname from emp join dept on (emp.deptno=dept.deptno); 新语法
SQL> select ename ,dname from emp join dept using(deptno); 等值连接（不推荐）
SQL> select ename,grade from emp e join salgrade s on (e.sal between s.losal and
s.hisal);
SQL> select ename,dname grade from
2 emp e join dept d on (e.deptno=d.deptno)
3 join salgrade s on(e.sal between s.losal and s.hisal)
4 where ename not like '_A%';
SQL> select e1.ename,e2.ename from emp e1 join emp e2 on (e1.mgr=e2.empno);
SQL> select e1.ename,e2.ename from emp e1 left join emp e2 on (e1.mgr=e2.empno);
左外连接：把左边表e1的多余数据拿出来
SQL> select ename,dname from emp e right join dept d on (e.deptno=d.deptno);
右外连接
SQL> select ename,dname from emp e full join dept d on (e.deptno=d.deptno);
全外连接
求部门中哪些人的薪水最高：
SQL> select ename,sal from emp
2 join (select max(sal) max_sal,deptno from emp group by deptno) t
3 on (emp.sal=t.max_sal and emp.deptno=t.deptno);
求部门平均薪水的等级
SQL> select deptno,avg_sal,grade from
2 (select deptno,avg(sal) avg_sal from emp group by deptno) t
3 join salgrade s on (t.avg_sal between s.losal and s.hisal);
求部门平均的薪水等级
SQL> select deptno,avg(grade) from
2 (select deptno,ename ,grade from emp join salgrade s on (emp.sal between s.
losal and s.hisal)) t
3 group by deptno;
雇员中有哪些人是经理人
SQL> select ename from emp where empno in (select mgr from emp);
不准用组函数，求薪水的最高值
SQL> select distinct sal from emp where sal not in
2 (select distinct e1.sal from emp e1 join emp e2 on (e1.sal < e2.sal));
平均薪水最高的部门编号：
SQL> select deptno,avg_sal from
2 (select avg(sal) avg_sal,deptno from emp group by deptno)
3 where avg_sal=
4 (select max(avg_sal) from
5 (select avg(sal) avg_sal,deptno from emp group by deptno));
平均薪水最高的部门名称：
SQL> select dname from dept where deptno=
2 (select deptno from
3 (select avg(sal) avg_sal,deptno from emp group by deptno)
4 where avg_sal=
5 (select max(avg_sal) from
6 (select avg(sal) avg_sal,deptno from emp group by deptno)));
求平均薪水的等级最低的部门的部门名称
求比普通员工最高薪水还要高的经理人名称
SQL> select ename,sal from emp where empno in (select mgr from emp where mgr is
not null ) and sal > (select max(sal) from emp where empno not in (select distin
ct mgr from emp where mgr is not null));
求薪水最高的前五名职员：
SQL> select ename,sal from (
2 select ename ,sal from emp order by sal desc)
3 where rownum < 5;
求薪水最高的六到十名员工（重点掌握）<三层select嵌套>
SQL> select ename,sal from (
2 select ename,sal,rownum r from (
3 select ename,sal from emp order by sal desc)
4 ) where r >5 and r<11;
将部门10薪水提升1倍，并在名字后加-
SQL> update emp2 set sal=sal*2,ename=ename || '-' where deptno=10;
事务控制语句： transaction
一个transaction起始于第一条DML语句
结束语commit
rollback 回退
DDL（create table） DCL（grant ）自动提交transaction
用户正常断开连接，自动提交，非正常断开自动回滚。
约束：非空、唯一、主键、外键、Check
create table stu
(
id number(6) primary key,
name varchar(20) constraint stu_name_nn not null,
sex number(1),
age number(3),
sdate date,
grade number(2) default 1,
class number(4),
email varchar2(50) unique,
constraint stu_name_email_uni unique(email,name)
);
修改表结构：
alter table stu drop(addr)
alter table stu add(addr varchar(50))
alter table stu modify(addr varchar(150))
alter table stu drop constraint stu_class_fk 修改约束条件
序列：
create sequence seq;
select seq.nextval from dual;
Oracle Dictionary:
desc user_tables
select table_name from user_tables;
select view_name from user_views;
select constraint_name from user_constraints;
Index:不要轻易建立索引（读得效率高，插入效率低）
create index idx_stu_email on stu(email,class); 为两个字段的组合建立索引
select index_name from user_indexes;
view：
视图就是子查询，表结构改之后view也要改，建立表的一部分（保护私有数据），确定有用之后建立，视图可以更新数据，很少用。
create view V$ as select id,name from stu;