oracle练习笔记

发布 2021-05-11 22:51:28 阅读 7080

1、 pl/sql是oracle系统的核心语言,现在oracle的许多部件都是由pl/sql写成。在pl/sql中可以使用的sql语句有:

select,update,delete,insert into,commit,rollback,s**epoint

提示:在pl/sql中只能用sql语句中的dml部分,不能用ddl部分,如果要在pl/sql中使用ddl(如create,table等)的话,只能以动态的方式来使用。

2、 pl/sql程序由三块组成:即声明部分,执行部分,异常处理部分。

pl/sql块的结构如下:

declare

*声明部分:在此声明pl/sql用到的变量,类型及游标,以及局部的存储过程和函数*/

beging

*执行部分:过程及sql语句,即程序的主要部分*/

exception

*执行异常部分:错误处理*/

end其中,执行部分是必须的。

oracle用户的管理。

1、 使用profile管理用户口令。

概述:profile是口令限制,资源限制的命令集合,当建立数据库时,oracle会自动建立名称为default的profile,当建立用户没有指定profile选项,那oracle就会将default分配给用户。

1) 账户锁定。

概述:指定该账户(用户)登陆时最多可以输入密码的次数,也可以指定用户锁定的时间(天),一般用dba的身份去执行该命令。

创建profile文件:

create profile lock_account limit failed_login_attempts 3 password_lock_time 2;

alter user tea profile lock_account;

oracle 表的管理。

1、 添加一个字段。

alter table student add(classid number(2));

2、 修改字段的长度。

alter table student modify(xm varchar2(30));

3、 修改字段的类型/或是名字(不能有数据),空表才能操作。

alter table student modify(xm char(30));

4、 删除一个字段。

alter table student drop column sal;

5、 修改表的名字。

rename student to stu;

6、 删除表。

drop table student;

7、 查看表的结构。

desc student;

1、添加数据。

insert into student values(1,’小明’,’男’,’11-12月-1997’,2345.6,12);

oracle 中默认的日期格式:’dd-mon-yy’

2024年6月9日改日期的默认格式:

alter session set nls_date_format=’yyyy-mm-dd’;

修改后,可以用我们熟悉的格式添加日期类型。

如:insert into student values(1,’小明’,’男’,’1997-09-11’,2345.6,12);

查看值为空是的select语句:

select * from student where birthday is null;

2、 删除数据。

在删除数据前,如果设置有保存点,则可以回滚。

s**epoint a;

delete from student;

rollback to a;

不做处理的话,保存点只有一个,可以设置多个保存点。

1) delete from student;

删除所有的记录,表的结构还在,写日志,可以恢复的速度慢。

2) drop table student;

删除表的结构和数据。

3) delete from student where xh=’a001’;

删除一条记录。

4) truncate table student;

删除表中的所有记录,表的结构还在,不写日志,无法找回删除的记录,速度快。

清屏命令:clear

1、 set timing on;

打开显示操作时间的开关。

2、 insert into users(userid,username,userpaw) select * from users;

可以疯狂地复制。

3、 select count(*)from users;

4、 select distinct userid from users;

第8讲:1、 select sal*13 [as] “年工资”,ename from emp;

其中,年工资是sal*13的别名。

2、 当有奖金时算年工资可用简单的加法。

select sal*13+comm*13 [as] “年工资”,ename from emp;

但是此时没有奖金的员工的年工资被清零。

只要运算符里有一个null值,整个算术结果也会变成null值。故使用:

select sal*13+nvl(comm,0)*13 [as] “年工资”,ename from emp;

意思是:如果comm的值为空值,则用0来代替。

如果comm的值不为空,则用本身的值。

3、 使用where子句。

1)显示工资高于3000的员工。

select * from emp where sal>3000;

2)显示1982.1.1**职的员工。

select * from emp where hiredate>’1-1月-1982’;

3)显示工资在2000到2500之间的员工的情况。

select * from emp where sal between 2000 and 2500;

select * from emp where sal >=2000 and sal<=2500;

4、 如何使用like操作:

:表示任意0到多个字符。

:表示任意单个字符。

1) 显示首字符为 s的员工姓名和工资。

select ename,sal from emp where ename like ‘s%’;

2) 显示第三个字符为大写o的所有员工的姓名和工资。

select ename,sal from emp where ename like ‘_o%’;

5、 在where条件中使用in

1) 显示empno为123,345,800…的雇员的情况。

select * from emp where empno in(123,345,800);

6、 使用is null的操作符。

1) 显示没有上级的雇员的情况。

select * from emp where mgr is null;

7、 使用逻辑操作符号。

1)查询工资高于500或是岗位为manager的雇员,同时还要满足他们的姓名的首字母为大写的j

select * from emp where sal>500 and ename like ‘j%’ or job=’manager’ and ename like ‘j%’;

select * from emp where (sal>500 or job='manager') and

ename like 'j%';

第9讲。8、 使用order by子句。

1) 按照工资的从低到高的顺序显示雇员的信息。

select ename,sal from emp order by sal;

默认的是从低到高排序。(asc)

select ename,sal from emp order by sal desc;(从高到低)

2)按照部门号升序而雇员的工资降序排列。

select * from emp order by deptno,sal desc;

3)按照部门号升序而入职时间降序排列。

select * from emp order by deptno,hiredate desc;

9、 使用列的别名排序。

select ename ,sal*13 [as] "年工资" from emp order by "年工资" asc;

select ename ,(sal+nvl(comm,0))*12 as "年工资" from emp order by "年工资" asc;

oracle表的复杂查询:

1、 数据分组:max,min,**g,sum,count

1) 显示所有员工中最高工资和最低工资。

select max(sal) ,min(sal) from emp;

把最高工资的员工姓名打印出来:(用到子句)

select ename,sal from emp where sal=(select max(sal) from emp);

2) 显示所有员工的平均工资和工资总和。

select **g(sal),sum(sal) from emp;

3) 计算共有多少员工。

select count(*)from emp;

select语句中如果有一个是分组函数,则其他的必须是分组函数。

如:select ename,max(sal) from emp;/*不允许出现*/

select min(sal),max(sal) from emp;/*允许出现*/

4) 显示工资高于平均工资的员工信息。

select * from emp where sal>(select **g(sal) from emp);

2、 group by和h**ing子句。

group by 用于对查询的结果分组统计;

h**ing子句用于限制分组显示结果。

1) 显示每个部门的平均工资和最高工资。

select **g(sal),max(sal),deptno from emp group by deptno;

分组的字段一定要出现在查询的列当中。

select **g(sal),max(sal),deptno from emp group by deptno order by deptno;

2) 显示每个部门的每种岗位的平均工资和最低工资。

select **g(sal),min(sal),deptno,job from emp group by deptno , job;

3) 显示平均工资低于2000的部门号和它的平均工资。

错误:select deptno,**g(sal) from emp where (select **g(sal) from emp)<2000 group by deptno;

正确:select **g(sal),max(sal) ,deptno from emp group by deptno h**ing **g(sal)>2000;

Oracle练习笔记

设某商业集团数据库中有三个实体集。一是 仓库 实体集,属性有仓库号 仓库名和地址等。二是 商店 实体集,属性有商店号,商店名,地址等,三是 商品 实体集,属性有商品号 商品名,单价 int数据类型 设仓库与商品之间存在 库存 联系,每个仓库可存储若干种商品,每种商品存储在若干仓库中,每个仓库每存储一...

oracle笔记

1 查看表的结构 desc tabledescription 2 set timing on 打开操作表的时间记录。3 消除重复行 distinct 4 大小写不区分的是列名,而不是里面的数据。1 可以对某一列直接进行加减乘除。两列相加。2 如果有一列为null,所得结果也为空。使用nvl函数处理n...

Oracle笔记

第1页1.oracle 的使用 1.1.sqlplus 的命令 初始化表的位置 set nls lang american 设置编码才可以使用下面脚本 cd oracle home rdbms cd demo 我们目前使用的是oralce 9i 9201 版本 select from v versi...