--修改表结构--添加一列altertable person add gender number(1);--修改列类型altertable person modify gender char(1);--修改列名称altertable person renamecolumn gender to sex;--删除一列altertable person dropcolumn sex;
数据的增删改
--查询表中记录select*from person;--添加一条记录insertinto person (pid , pname )values(1,'小明');commit;--修改一条记录update person set pname ='小马'where pid =1;commit;--三个删除--删除表中全部记录deletefrom person;--删除表结构droptable person;--先删除表,再次创建表。效果等同于删除表中全部记录。--在数据量大的情况下,尤其在表中带有索引的情况下,该操作效率高。--索引可以提高查询效率,但是会影响增删改效率。truncatetable person;
序列的使用
CREATE SEQUENCE 序列名
[INCREMENT BY n][STARTWITH n][{MAXVALUE/MINVALUE n|NOMAXVALUE}][{CYCLE|NOCYCLE}][{CACHE n|NOCACHE}]
scott用户
--scott用户,密码tiger。--解锁scott用户alteruser scott account unlock;--解锁scott用户的密码【此句也可以用来重置密码】alteruser scott identified by tiger;--切换scott用户
--分组查询----查询出每个部门的平均工资----分组查询中,出现在group by 后面的原始列,才能出现在select后面----没有出现在group by 后面的列,现在select后面出现,必须加上聚合函数----聚合函数有一个特性,可以把多行记录变成一个值。select e.deptno ,avg(e.sal)--, e.enamefrom emp e
groupby e.deptno;----查询出平均工资高于2000的部门信息select e.deptno ,avg(e.sal) asal
from emp e
groupby e.deptno
havingavg(e.sal)>2000;--having asal>2000;这样是错误的。----所有条件都不能使用别名来判断----比如下面的条件语句也不能使用别名当条件,因为where语句是优先于select 语句的。--select * from emp where sal>1500; √--select ename , sal s from emp where sal>1500;√--select ename , sal s from emp where s>1500; ×--查询出每个部门工资高于800的员工的平均工资 select e.deptno ,avg(e.sal) asal
from emp e
where e.sal>800groupby e.deptno;----where是过滤分组前的数据,having是过滤分组后的数据。----表现形式,where必须在group by 之前,having实在group by之后。----查询出每个部门工资高于800的员工的平均工资然后再查询出平均工资高于2000的部门select e.deptno ,avg(e.sal) asal
from emp e
where sal>800groupby e.deptno
havingavg(e.sal)>2000;
多表查询中的一些概念
----多表查询的一些概念----笛卡尔积select*from emp e,dept d;----等值连接select*from emp e,dept d
where e.deptno = d.deptno;----内连接select*from emp e innerjoin dept d
on e.deptno = d.deptno;---查询出所有部门,以及部门下的员工信息。【外连接】select*from emp e rightjoin dept d
on e.deptno = d.deptno;----查询所有员工信息,以及员工所属部门select*from emp e leftjoin dept d
on e.deptno=d.deptno;----oracle中专用外连接-- 上段 right join 外连接select*from emp e,dept d
where e.deptno(+)= d.deptno;----上段 left join 外连接select*from emp e,dept d
where e.deptno = d.deptno(+);
自连接概念和练习
select*from emp;--查询出员工姓名,员工领导姓名--自连接:自连接其实就是站在不同的角度把一张表看成多场表select e1.ename ,e2.ename
from emp e1 , emp e2
where e1.mgr = e2.empno;----查询员工姓名,员工部门名称,员工领导姓名,员工领导部门名称select e1.ename , d1.dname , e2.ename , d2.dname
from emp e1, emp e2, dept d1, dept d2
where e1.mgr = e2.empno
and e1.deptno = d1.deptno
and e2.deptno = d2.deptno;
子查询
--子查询--子查询返回一个值----查询出工资和scott一样的员工信息select*from emp where sal in(select sal from emp where ename ='SCOTT');--子查询返回一个集合--查询出工资和10号部门任意员工一样的员工信息select*from emp where sal in(select sal from emp where deptno =10);--子查询返回一张表--查询出每个部门最低工资,和最低工资员工姓名,和该员工所在部门名称--1.先查询出每个部门最低工资select deptno ,min(sal) msal
from emp
groupby deptno;--2.三表联查,得到最终结果select t.deptno , t.msal , e.ename , d.dname
from(select deptno ,min(sal) msal
from emp
groupby deptno) t , emp e , dept d
where t.deptno = e.deptno
and t.msal = e.sal
and e.deptno = d.deptno;
分页查询
--oracle中的分页----rownum行号:当我们做select操作的时候,----每查询出一行记录,就会在该行上加上一个行号,----行号从1开始,依次递增,不能跳着走。--排序操作会影响rownum的顺序select rownum ,e.*from emp e orderby e.sal desc;----如果涉及到排序,但是还要使用rownum的话,我们可以再次嵌套查询select*from(select rownum ,e.*from emp e orderby e.sal desc) t;--emp 表工资倒叙排列后,每页5条记录,查询第二页--rownum行号不能写上大于一个整数select*from(select rownum rn, e.*from(select*from emp e orderby e.sal desc) e where rownum <11)where rn >5;
视图
--视图----视图的概念:视图就是提供一个查询的窗口,所有的数据来自于原表。---查询语句创建表createtable emp asselect*from scott.emp;select*from emp;---创建一个视图【必须有dba权限】createview v_emp asselect ename , job from emp;----查询视图select*from v_emp;----修改视图update v_emp set job ='CLERK'where ename ='ALLEN';commit;----创建只读视图createview v_emp1 asselect ename , job from emp withread only;----视图的作用?----第一:视图可以屏蔽掉一些敏感字段。----第二:保证总部和分部数据及时统一。
索引
--索引----索引的概念:索引就是在表的列上,创建一个二叉树----达到大幅度提高查询效率的目的,但是索引会影响增删改的效率。--单列索引----创建单列索引createindex idx_enamen on emp(ename);----单列索引触发规则,条件必须是索引列中的原始值。----单行函数,模糊查询,都会影响索引的触发。select*from emp where ename ='SCOTT';--复合索引----创建复合索引create inde idx_enamejob on emp(ename,job);----符复合索引中第一列为优先检索列----如果要触发复合索引,必须包含有优先检索列中的原始值。select*from emp where ename ='SCOTT'and job ='xx';--触发复合索引select*from emp where ename ='SCOTT'or job ='xx';--不触发索引select*from emp where ename ='SCOTT'--触发单列索引
pl/sql编程语言
pl/sql怎么定义变量
--pl/sql编程语言----pl/sql编程语言是对sql语言的扩展,使得sql语言具有过程化编程的特性。----pl/sql编程语言比一般的过程化语言,更加灵活高效。----pl/sql编程语言主要用来编写存储过程和存储函数等。--声明方法,类似main方法----赋值操作可以使用:=也可以使用into查询语句赋值declare
i number(2) :=10;
s varchar2(10) :='小明';
ena emp.ename%type;----引用型变量
emprow emp%rowtype;----记录型变量begin
dbms_output.put_line(i);
dbms_output.put_line(s);select ename into ena from emp where empno =7788;
dbms_output.put_line(ena);select*into emprow from emp where empno =7788;
dbms_output.put_line(emprow.ename ||'的工作为:'||emprow.job);end;
pl/sql中的if判断
--pl/sql中的if判断----输入小于18的数字,输出未成年----输入大于18小于40的数字,输出成年人----输入大于40的数字,输出老年人declare
i number(3) :=ⅈbeginif i<18then
dbms_output.put_line('未成年');
elsif i<40then
dbms_output.put_line('中年人');else
dbms_output.put_line('老年人');endif;end;
pl/sql中的循环
--pl/sql中的loop循环---用三种方式输出1到10十个数字---while循环declare
i number(2) :=1;beginwhile i<11loop
dbms_output.put_line(i);
i := i +1;endloop;end;---exit循环declare
i number(2) :=1;beginloopexitwhen i>10;
dbms_output.put_line(i);
i := i +1;endloop;end;---for循环declarebeginfor i in1..10loop
dbms_output.put_line(i);endloop;end;
pl/sql中的游标
--游标:可以存放多个对象,多行记录。----输出emp表中所有员工的姓名declarecursor c1 isselect*from emp;
emprow emp%rowtype;beginopen c1;loopfetch c1 into emprow;exitwhen c1%notfound;
dbms_output.put_line(emprow.ename);endloop;close c1;end;----给指定部门员工涨工资declarecursor c2(eno emp.deptno%type)isselect empno from emp where deptno = eno;
en emp.empno%type;beginopen c2(10);loopfetch c2 into en;exitwhen c2%notfound;update emp set sal = sal +100where empno = en;commit;endloop;close c2;end;----查询10号部门员工信息select*from emp where deptno =10;
存储过程使用
--存储过程 ----存储过程:存储过程就是提前编译好的一段pl/sql语言,放置在数据库端----可以直接被调用。这一段pl/sql一般都是固定步骤的业务。--创建存储过程语法:----create [or replace] PROCEDURE 过程名[(参数名 in/out 数据类型)]----AS/is----begin--------pl/sql 子程序体;----end;--给指定员工涨100块工资createorreplaceprocedure p1(eno emp.empno%type)isbeginupdate emp set sal = sal +100where empno = eno;commit;end;select*from emp where empno =7788;----测试p1declarebegin
p1(7788);end;
存储函数
--存储函数----create or replace function 函数名(Name in type , Name in type ,····) return 数据类型----is 结果变量 数据类型;----begin--------return(结果变量);----end 函数名;--通过存储函数实现计算指定员工的年薪----创建存储过程和存储函数的参数都不能带长度----存储函数的返回值类型不能带长度createorreplacefunction f_yearsal(eno emp.empno%type)return number
is
s number(10);beginselect sal *12+nvl(comm,0)into s from emp where empno = eno;return s;end;----测试f_yearsal----存储函数在调用的时候,返回值需要接收。declare
s number(10);begin
s := f_yearsal(7788);
dbms_output.put_line(s);end;
out类型参数如何使用
--out类型参数如何使用----使用存储过程来算年薪createorreplaceprocedure p_yearsal(eno emp.empno%type,yearsal out number)is
s number(10);
c emp.comm%type;beginselect sal*12,nvl(comm,0)into s , c from emp where empno = eno;
yearsal := s+c;end;--测试p_yearsaldeclare
yearsal number(10);begin
p_yearsal(7788,yearsal);
dbms_output.put_line(yearsal);end;----in 和 out 类型参数的区别是什么?----凡是涉及到into查询语句赋值或者:= 赋值操作的参数,都必须使用out来修饰。
触发器的概念和分类
--触发器,就是指定一个规则,在我们做增删改操作时,--只要满足规则,自动触发,无需调用。----语句级触发器:不包含for each row 的就是行级触发器----行级触发器:包含有 for each row 的就是行级触发器--加for each row 是为了使用::old 或者 :new对象。--语句级触发器:---插入一条记录,输出一个新员工入职createorreplacetrigger t1
afterinserton person
declarebegin
dbms_output.put_line('一个新员工入职');end;----触发t1insertinto person values(1,'小红');commit;--查看表select*from person;--行级触发器:----不能给员工降薪createorreplacetrigger t2
before
updateon emp
for each rowdeclarebeginif :old.sal > :new.sal then
raise_application_error(-20001,'不能给员工降薪');endif;end;----触发t2select*from emp where empno =7788;--查看是否修改了数据。update emp set sal = sal -1where empno =7788;commit;
触发器实现主键自增
---触发器实现主键自增。【行级触发器】----分析:在用户做插入操作之前,拿到即将插入的数据,----给该数据中的主键列赋值。createorreplacetrigger auid
before
inserton person
for each rowdeclarebeginselect s_person.nextval into :new.pid from dual;end;--使用auid实现主键自增insertinto person (pname)values('a');commit;