Oracle查询语句及实例

来源：互联网发布：数据库系统概论pdf下载编辑：程序博客网时间：2024/05/17 19:14

一．数据库网络编程

CS (Client/Server) BS (Browser/Server)

oracle 连接命令 (socket编程)

服务器 (ServerSocket[ip+端口])

关于网络编程主目录（C:\app\Administrator\product\11.2.0\dbhome_1\NETWORK\ADMIN）

listener.ora（ip+端口）

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.14.101)(PORT = 1521))

ServerScoket ss=new ServerSocket(1521)

测试 telnet ip端口

tnsnames.ora (连接数据库)

ORCL36 =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.14.36)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = orcl)

)

//show user 查看当前用户

1. 客户端sqlplus命令

as sysdba 如果某个用户是超级管理员（dba）普通用户不需要添加as sysdba

(

create user 用户名 identified by 密码//创建用户

grant connect to 用户名

)

sqlplus / as sysdba

sqlplus 用户名/密码@链接描述符 as sysdba

oracle默认的用户名是sys密码123456

开发客户端 plsqldevleoper

2. oracle管理工具

dbca 用于管理数据库的维护每一个数据库都存在名称默认orcl

netca 用于管理网络连接 listener.ora和tnsnames.ora建议手工编辑

3. oracle常用的服务(services.msc)

OracleServiceORCL （主服务）必须开启的服务

net start|stop 服务名

OracleOraDb11g_home1TNSListener （监听服务用于远程连接）

lsnrctl stop|start|reload

4. 注册表（regedit）

备份注册表 HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE

备份 %ORACLE_HOME%/bin

5. oracle卸载和重装

OUI(卸载工具)

一关闭服务

net stop oracleserviceorcl

lsnrctl stop

二删除开始菜单

三删除注册表

四删除服务

sc delete 服务名

五删除目录

C:\Program Files\Oracle

二 . 查询语句

select * from emp where sal=any(3000,5000);

select * from emp where sal in(3000,5000);

--7.查询出员工表中月薪不是3000和5000的员工的信息。

select * from emp where sal not in(3000,5000);

--8.查询出员工表中名字以'A'开头的员工的信息。

select * from emp where ename like 'A%';

--9.查询出员工表中名字第三个字母为'O'的员工的信息。

select * from emp where ename like '__O%';

--10.查询出员工表中名字中含有'J'的员工的信息。

select * from emp where ename like '%J%';

--11查询员工的姓名、年薪，按年薪升序排列。

select ename,sal*12 from emp ORDER BY sal ASC;

--12查询出部门编号为30的员工信息，按编号降序排列。

select * from emp where deptno=30 ORDER BY EMPNO DESC;

All, any, in区别

select * from emp;

SELECT * FROM emp WHERE sal > 2000;

--all 大于取最大小于取最小

SELECT * FROM emp WHERE sal <ALL(1000, 2000, 3000);

SELECT * FROM emp WHERE sal >ALL(1000, 2000, 3000);

SELECT * FROM emp WHERE sal =ALL(1000, 2000, 3000);

--any 小于取大大于取小

SELECT * FROM emp WHERE sal <ANY(1000, 2000, 3000);--小于大的

SELECT * FROM emp WHERE sal >ANY(1000, 2000, 3000);--大于小的

SELECT * FROM emp WHERE sal =ANY(1000, 2000, 3000);

SQL函数带有一个或多个参数并返回一个值。

Oracle提供一系列用于执行特定操作的函数

三 . 以下是SQL函数的分类：

五种方法

count(1), count(*) count(ename) count(rowid)效率高;

一：单行函数：同时只能对一行进行操作，且对输入的每一行返回一行输出结果。

#---rownum 结果查询后才出值，大于1以上的值，无法获取数据，小于任何值都行

如：select t.*,rownum from emp t where rownum<20;

#--分页

select t.* from (select q.*,rownum rn from emp q)t where t.rn>=11 and t.rn<=20;

#--子查询

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

【1】单行函数可以大致划分为：

【如： select * from emp;

select ename,lower(ename),initcap(lower(ename)),upper('aaa') from emp;

select replace(ename,'LL','MM') from emp

select ename,instr (ename,'M',2) from emp;

select ename,instr (ename,'M',-2) from emp;

select substr(ename,1,2) from emp;

select trim(' a b c '),ltrim(' a b c '),rtrim(' a b c ') from dual;

select concat('a',concat('v','c')) from dual;--连接字符串

select length('啊'),length('a') from dual;

select nvl(10,9) from emp;

select nvl2(null,9,14) from emp;

select chr(65) from dual;--数字转化为字符

select ascii('A') from dual;--字符转化为数字

select decode(8,9,10,111,12,8,10)--特有函数返回10

select decode(8,9,10,111,12,8,10,22)--单个直接

select ename,decode(job,'SALESMAN','销售员','MANAGER','经理','PRESIDENT','总裁',ename) from emp;

select ename,case

when job='SALESMAN' then '销售员'

when job='MANAGER' then '经理'

when job='PRESIDENT' then '总裁'

else job end as job

from emp;

】

字符函数：接收字符串输入并返回字符串或数值

asci(x); 返回单个字码

chr(x); 返回字码为X字符

initcap(x); 首字母大写

lower(x) 转换成小写

upper(x); 转换成大写

replace(x,tname,hname); 替换

instr(x,start,count) 查找字符

substr(x,start,length); 截取字符串

concat(x,y); 和 || 连接字符串

length(x);

trim( x ,v ); 默认截取空格

nvl(x,value); 如果x为null,返回value;

nvl2(x,value,value2); 如果X不为null，返回value1否则返回value2;

【2】数字函数：接收数值输入并返回数值

【如：

四舍五入

select round(23.654) from dual;

select round(23.652,1) from dual;

select round(23.652,-1) from dual;

select to_char(sal,'$99,999.9999') from emp;

select to_char(sal,'L99,999.9999') from emp;

select to_char(sal,'L00000,0000') from emp;

select to_char(sysdate,'YYYY')

】

ABS(n); 绝对值

CEIL(n) 大于或等于N的最小整数

FLOOR(n); 小于或等于N的最大整数

POWER(m,n); m的n次冥

SQRT(n); n的平方根

mod(m,n); m除以n的余数

round(m,[n]) 四舍五入

trunc(m,[n]); 截断整数

【3】日期函数：对日期进行操作

【如：

日期

select hiredate,hiredate，ADD_MONTHS（hiredate,+1）from dual;

select hiredate,hiredate+1 from emp; --日期加一天

select hiredate,hiredate+30,ADD_MONTHS(hiredate,-1) from emp;

select sysdate-hiredate from emp;-- 直接天数

select months_between(sysdate,hiredate) from emp;-- 月数

】

select sysdate-1 from dual;

select ename,(sysdate-hiredate) from emp;

add_months(d,n); d日期加n表示n个月

months_between(d1,d2); 两个日期相差月数

last_day(d); 制定日期当月最后一天

round(d,[a]); 取整

next_day(d,day); 接下来的一天

trunc(d,[a]) 截断当月的第一天

时区函数

current_date

dbtimezone 获取数据库的时区

sessiontimezone 获取与数据库的时区

【4】转换函数：从一种数据类型转换成另一种数据类型

【如：

select to_char(hiredate,'YYYY-MM-DD hh:mm:ss') from emp;

select to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') from dual;

select ename,hiredate from emp where hiredate > to_date('1981-7-1 16:27:34' ,'YYYY-MM-DD HH24:MI:SS'); --字符串转化为日期

select sal from emp where sal>'$1,250.00';

select sal from emp where sal> to_number('$1,250.00','$9,999.99');

select ename,sal*12+nvl(comm,0) from emp;

】

to_char(a,b);

to_number(a,[b])

to_char(datetime,a);

to_date(char,[a]);

DECODE(x,a,result) ;

日期时间和字符串转换

YYYY 年份 YY年份后两位

MM 月份 month

WW 本年的第几周 W本月第几周

DDD 本年的第几天 DD本月第几天D本周的第几天

HH24 小时 MI分钟数

【5】正则表达式函数

regexp_like(a,b);

regexp_instr(a,b)

regexp_replace(a,b)

regexp_substr(a,b);

regexp_count(a,b);

【6】

【如：

最高值最低值平均值

select max(sal) from emp;

select min(sal) from emp;

select avg(sal) from emp;

select to_char(avg(sal),'99999.99') from emp;

select round(avg(sal),2) from emp;

】

三:聚合函数：同时可以对多行进行操作，且只返回一行输出结果。

--having 分组限制

如：select avg(sal),deptno from emp group by deptno;

select avg(sal),deptno from emp group by deptno having avg(sal) >2000;

员工表：

查询出不是总裁（PRESIDENT）的职位名以及该职位的员工月薪总和，

还要满足同职位的员工的月薪总和大于4500。

输出结果按月薪的总和降序排列。

求出部门平均月薪最高的部门编号和平均月薪

select * from emp where job<>'PRESIDENT';

select job,sum(job) from emp where job<>'PRESIDENT' group by job;

select job,sum(sal) from emp where job<>'PRESIDENT' group by job having sum(sal)>4500 order by sum(sal) desc;

【如：多表连接

select * from dept;

select * from emp;

select emp.ename,dept.dname from dept,emp where dept.deptno=emp.deptno;

内连接 通过条件关联后无法关联数据丢掉

select * from dept d inner join emp e on d.deptno=e.deptno;

外连接(左右) 以指定表为主表，能关联的显示，不能关联的保留

select * from dept d right join emp e on d.deptno=e.deptno;

全连接 将左右连接合并去重

select * from dept d full join emp e on d.deptno=e.deptno;

】

例子查询语句

select * from emp;

--1.选择部门30的所有员工

select ename,deptno from emp where deptno =30;

--2、列出所有办事员（CLERK）的姓名，员工编号和部门编号

select ename 员工姓名,empno员工编号,deptno部门编号from emp where job='CLERK';

--3、找出佣金高于薪金的员工

select * from emp where nvl(comm,0)> sal;

--4、找出佣金高于薪金60%的员工

select * from emp where nvl(comm,0)> sal*0.6;

--5、找出部门10中的所有经理（MANAGER）和部门20的所有办事员（CLERK）的详细资料

select * from emp where (deptno =10 and mgr='MANNGER') or (deptno=20 and job='CLERK');

--6、找出部门10的所有经理（MANAGER），部门20中所有办事员（CLERK），既不是办事员也不是经理但是薪金大于2000的所有员工的详细资料

select * from emp where (deptno =10 and mgr='MANNGER') or (deptno=20 and job='CLERK') or (job<>'MANAGER' or job<>'CLERK');

--7、找出收取佣金的员工的员工的不同工作

select distinct job,comm from emp where nvl(comm,0)<>0 ;

--8、找出不取佣金或者是收取佣金小于100的员工详细信息

select ename,comm from emp where nvl(comm,0)<100;

--9、找出各月的倒数第三天受雇佣的员工

select ename,hiredate from emp where hiredate=LAST_DAY(hiredate)-2 ;

--每个员工的雇佣时间是不一样的，所有需要找出每个员工雇佣的时间所在月份的最后一天，

select ename,hiredate from emp where hiredate=LAST_DAY(hiredate);

--之后按照‘日期-数字’的方式求出前三天的日期，这个日期必须和雇佣日期相符才能满足条件。

--10、找出年前受雇的员工

select ename, hiredate from emp where hiredate< ADD_MONTHS(sysdate,-12);

--11、以首字母大写的方式显示员工姓名

select ename,initcap(lower(ename)) from emp;

--12、显示姓名正好为5个长度的所有员工

select * from emp where length(ename)=5;

--13、显示带有'R'的员工的详细

select * from emp where ename like 'R';

--14、显示员工姓名的前三个字符

select ename,substr(ename,1,3) from emp;

--15、显示所有员工的姓名，用‘a’替换所有的'A'

select ename,replace(ename,'a','A') from emp;

16、列出满10年雇佣期限的员工的详细信息

select * from emp where sysdate >= ADD_MONTHS(hiredate,12*10);

--17、显示员工的详细资料，并按姓名排序

select * from emp order by ename asc;

--18、显示员工的姓名和受雇日期，并按照老的员工排在前面的方式显示出来

select ename,hiredate from emp order by hiredate asc;

--19、显示所有员工的姓名、工作和薪金，按工作的降序排列，工作相同则按照薪金的升序排列

select ename,job,sal from emp order by job desc,sal asc;

--20、显示所有员工的姓名、加入公司的年份和月份、按接受所在雇佣月排序，若月的相同则按最早年份的员工排在最前面

--本程序需要求出所雇的日期的年份和月份，然后再来显示

select t.* from emp t order by sal desc

select ename,hiredate,sal from ( select t.* from emp t order by sal desc) order by hiredate asc;

--21、显示一个月为30天的情况下，所有员工的日薪，忽略余数

select round(sal/30) from emp

--22、找出在（任何年份的）2月受雇的员工

select * from emp where to_char(hiredate,'mm')=2;

--23、对于每个员工显示其来到公司的天数

select ename,ceil(sysdate-hiredate) from emp;

--24、显示姓名字段的任何位置包含有’A‘的员工

select * from emp where ename like '%A%'

--25、以月份的方式显示所有员工的服务年限

select ename, MONTHS_BETWEEN(sysdate,hiredate) from emp;

例子2

all_tables查询

select * from all_tables ;

--1 查询scott用户下的所有表

select * from all_tables where owner='SCOTT';

--2 查询所有闰年创建的表

select * from all_tables where mod(to_char(last_analyzed,'YYYY'),4)=0 and mod(to_char(last_analyzed,'YYYY'),400)=0 and mod(to_char(last_analyzed,'YYYY'),100)!=0;

--3 查询cluster_name列上有两个#同事有一个#结尾的数据

select cluster_name from all_tables where regexp_like(cluster_name, '.*#.*#$');

二 v$sql查询

select * from v$sql

--4 查询sql_Text中有注释的sql语句

select sql_text from v$sql where regexp_like(sql_text,'.*--.*') or regexp_like(sql_text,'/\*.*\*/');

--5 查询所有的查询语句（有select或者from的语句注意大小写问题）

select sql_text from v$sql where regexp_like(sql_text,'.*select.*from.*');

--6 查询SQL_ID中存在数字的数据行

select SQL_ID from v$sql where regexp_like(SQL_ID,'.*[0-9]+');

--7 查询所有上班时段执行的数据（上午9-12下午1-5点）

select last_active_time from v$sql where to_char(last_active_time,'hh24') between 9 and 12 or to_char(last_active_time,'hh') between 1 and 5;

--8 查询所有Service列上是小写字母的数据行

select service from v$sql where regexp_like(service,lower('.*[a-z]+'));

--9 查询module_hash列上所有10位的负数（使用正则表达式）

select module_hash from v$sql where regexp_like(module_hash,'^-\d{10}');

--10 查询runtime_mem（占用内存）列中占用内存最小和最大的数据行

select min(runtime_mem),max(runtime_mem) from v$sql ;

三 all_tab_columns

select * from all_tab_columns;

--11 查询所有Scott账户下数据类型为Number类型的表和列要求和编辑表看到的列的顺序一致

select * from all_tab_columns where owner='scott' or data_type='NUMBER';

--12 查询scott账户下最后时间创建的表

select max(last_analyzed) from all_tab_columns where owner='scott';

--13 查询TYPE$表中toid列以字母结尾的行

select * from TYPE$ where regexp_like(toid,'.*[a-zA-Z]$');

--14 查询Types$表中version列$开头并且是其他都是数字的数据行

select * from TYPE$ where regexp_like(version,'^\$.*[0-9]+.*')

例子3 多表查询，子查询

select * from sc ;

select * from course;

select * from student;

select * from teacher;

--1.查询“语文”课程比“数学”课程成绩高的所有学生的学号；

方法一 select * from sc where c#=(select c# from course where cname='语文');

select * from sc where c#=(select c# from course where cname='数学');

select * from sc s,sc s1 where s.s#=s1.s# and s.c#=(select c# from course where cname='语文') and s1.c#=(select c# from course where cname='数学') and s.score>s1.score;

方法二

select * from sc s inner join sc s1 on s.s#=s1.s# and s.c#=(select c# from course where cname='语文') and s1.c#=(select c# from course where cname='数学') and s.score>s1.score;

--2.查询平均分大于60的学生的学号和平均分

select s#,avg(score) from sc group by s# having avg(score) >60;

--3.查询所有学生的学号，姓名，选课数，总分

select s.s#,s.sname,count(*),sum(score) from sc sc inner join student s on sc.s#=s.s# group by s.s# ,s.sname;

select * from emp e group by e.e

--4.查询姓叶的老师的个数

select count(rowid) from teacher where tname like '%叶%' ;

--5.查询学过“语文”并且也学过“数学”课程的同学的学号、姓名；

方法一 select * from sc where c#=(select c# from course where cname='语文');

select * from sc where c#=(select c# from course where cname='数学');

select s.s#,(select sname from student where s.s#= s#) from sc s,sc s1 where s.s#=s1.s# and s.c#=(select c# from course where cname='语文') and s1.c#=(select c# from course where cname='数学');

方法二

select s1.s#,st.sname from sc s1 inner

join sc s2 on s1.s#=s2.s#

join student st on st.s#=s1.s# and st.s#=s2.s#

where s1.s#=s2.s# and s1.c#=(select c# from course where cname='语文') and s2.c#=(select c# from course where cname='数学');

--6.查询课程编号“2”的成绩比课程编号“1”课程低的所有同学的学号、姓名；

方法一 select * from sc where c#=2);

select * from sc where c#=1);

select s.s#,(select sname from student where s.s#= s#) from sc s,sc s1 where s.s#=s1.s# and s.c#=1 and s1.c#=2 and s.score>s1.score;

方法二

select * from sc s1 inner

JOIN sc s2 ON s1.s#=s2.s#

join student st on st.s#=s1.s# and st.s#=s2.s#

where s1.s#=s2.s# and s1.c#=1 and s2.c#=2 and s1.score>s2.score;

--7.查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分

select c#,(select max(score) from sc where c#=c.c#),(select min(score) from sc where c#=c.c#)from course c;

--8.查询不同老师所教不同课程平均分从高到低显示

select c# from sc group by c#;

select c.cname,c.t#,avg(score) from sc sc inner join course c on sc.c#=c.c# group by c.cname,c.c#,c.t# order by avg(score) desc;

--9.统计列印各科成绩,各分数段人数:

--课程ID,课程名称,[100-85],[85-70],[70-60],[ <60]

select c#,(

select cname from course where c#=s1.c#

(

select count(score) from sc where c#=s1.c# and score between 85 and 100

) as "[100-85]"

(

select count(score) from sc where c#=s1.c# and score between 70 and 84

)

as "[85-70]"

(

select count(score) from sc where c#=s1.c# and score between 60 and 69

)

as "[70-60]"

(

select count(score) from sc where c#=s1.c# and score<60

)

as "[ <60]" from sc s1 group by c#;

--10.查询出只选修了一门课程的全部学生的学号和姓名

select s# from sc group by s# having count(rowid)=1;

--11.查询没学过“叶平”老师课的同学的学号、姓名；

select t# from teacher where tname='叶平';

select c# from course where t#=(select t# from teacher where tname='叶平');

select s# from sc where c#=any(select c# from course where t#=(select t# from teacher where tname='叶平'));

select s#,sname from student where s# not in(select s# from sc where c#=any(select c# from course where t#=(select t# from teacher where tname='叶平')));

--方法二

select * from student where s# not in(select s.s# from sc s where c#=(select c.c# from teacher t inner join course c on t.t#=c.t# where t.tname='叶平'));

--12.查询学过“叶平”老师所教的所有课的同学的学号、姓名；c

select t# from teacher where tname='叶平';

select c# from course where t#=(select t# from teacher where tname='叶平');

--三种方法

select s#, (select sname from student where s#=s.s#) sname from sc s where c#=any(select c# from course where t#=(select t# from teacher where tname='叶平'));

select s#,(select sname from student where s#=s.s#) sname from sc s where c#=(select c.c# from teacher t inner join course c on t.t#=c.t# where t.tname='叶平')

select sc.s#,s.sname from sc sc inner join student s on sc.s#=s.s# where c#=(select c.c# from teacher t inner join course c on t.t#=c.t# where t.tname='叶平');

--13.查询所有课程成绩小于60分的同学的学号、姓名；

select count(rowid),s# from sc group by s# having count(rowid)=(select count(rowid) from sc where score<60)

select s#,max(score),(select sname from student where s#=sc.s#) from sc sc group by s# having max(score)<60

--14.查询没有学全所有课的同学的学号、姓名；

select count(rowid) from course ;

select s#,count(rowid)from sc group by s# having count(rowid)<>(select count(rowid) from course);

--方法二

select * from student where s# not in(select s# from student s where

(select count(rowid) from course)=(select count(rowid) from sc where s#=s.s# and c# in(select c# from course))

)

--15.查询至少有一门课与学号为“1”的同学所学相同的同学的学号和姓名；

select c# from sc where s#=1;

select sc.s# from sc group by sc.s#;

select s.s#,s.sname from sc sc inner join student s on sc.s#=s.s# where c# in(select c# from sc where s#=1);

--方法二

select distinct sc.s#,s.sname from sc sc inner join student s on sc.s#=s.s# where c# in(select c# from sc where s#=1) and sc.s#<>1

--16.查询与学号为“6”同学所学课门数相同的其他同学学号和姓名；

select count(rowid) from sc where s#=6;

select sc.s# from sc group by sc.s#;

select sc.s#,s.sname from sc sc inner join student s on sc.s#=s.s# group by sc.s#,s.sname having count(*)=(select count(rowid) from sc where s#=6);

--方法二

select s# from sc where s#!=6 group by s# having count(rowid)=(select count(rowid) from sc where s#=6)

--17.查询和“1”号的同学学习的课程完全相同的其他同学学号和姓名；

方法一select count(rowid) from sc where c#=1 ;

select s# from sc where c#=1 group by s# having count(rowid)=(select count(rowid) from sc where c#=1);

select s# from sc where c# in( select s# from sc where c#=1 group by s# having count(rowid)=(select count(rowid) from sc where c#=1));

方法二

select count(rowid) from sc where c#=1 ;

select s# from sc group by s#;

select s.s#,st.sname from sc s inner join student st on s.s#=st.s# group by s.s#,st.sname having count(*)=(select count(rowid) from sc where c#=1);

方法三

select s# from student s where s#<>1 and

(select count(rowid) from sc where s#=1)=(select count(rowid) from sc where s#=s.s# and c# in(select c# from sc where s#=1))

and (select count(rowid) from sc where s#=s.s#)=(select count(rowid) from sc where s#=1)

--18.按各科平均成绩从低到高和及格率的百分数从高到低顺序

select c#,avg(score),round((select count(rowid) from sc where c#=s.c# and score>=60)/(select count(rowid) from sc where c#=s.c#)*100,2)||'%' from sc s group by c#

order by avg(score) asc,round((select count(rowid) from sc where c#=s.c# and score>=60)/(select count(rowid) from sc where c#=s.c#)*100,2)||'%' desc;

--19.查询每门功成绩最好的前两名

select c#,(select max(score) from sc where c#=c.c#),(select max(score) from sc where c#=c.c# and score not in(select max(score) from sc where c#=c.c#)) from course c;

四 . 循环语句

egin

dbms_output.put_line('dfdf');

end;

--constant 常量

declare i constant binary_integer:=10;

begin

i:=5;

end;

数组健是在规则的int类型累加的数字

a[0]='test'

a[1]='test1'

map

map.put('sex','男')

map.put('name','sa')

class c{

String uid=1;

String uname='aa';

}

--tables类型数组类型

declare

type myTable is TABLE OF varchar2(20) INDEX BY BINARY_INTEGER;--定义类型varchar2

myarr myTable ;

begin

myarr(0):='a';

myarr(1):='b';

DBMS_OUTPUT.PUT_LINE(myarr(0)||myarr(1));

myarr(2):='d';

myarr(3):='e';

DBMS_OUTPUT.PUT_LINE(myarr(2)||myarr(3));

end;

--record 类型类和对象

declare

type myclass is record(

myid number,

myname varchar2(20),

mysex varchar2(3)

);

myinstance myclass;

myinstance1 myclass;

begin

myinstance.myid:=1;

myinstance.myname:='aa';

myinstance.mysex:='男';

dbms_output.put_line(myinstance.myname||' '|| myinstance.mysex);

end;

--变量特殊声明

行类型

declare i emp.sal%type;

myrow emp%rowtype;

begin

i:=52.125;

dbms_output.put_line(i);

myrow.empno:=5566;

myrow.ename:='aa';

myrow.sal:=1000;

dbms_output.put_line(myrow.empno||myrow.ename||myrow.sal);

end;

--if循环

declare

sex varchar2(20):='&请输入性别';

begin

if(sex='男')then

dbms_output.put_line('你的性别为:男');

elsif(sex='女')then

dbms_output.put_line('你的性别为:女');

else

dbms_output.put_line('你的性别异常');

end if;

end;

declare

sex varchar(20):='&请输入你的性别';

begin

case

when sex='男' then

dbms_output.put_line('你的性别为:男');

when sex='女' then

dbms_output.put_line('你的性别为:女');

else

dbms_output.put_line('你的性别异常');

end case;

end;

--1-10奇数

declare mx binary_integer:=10;

i binary_integer:=1;

begin

loop

if(mod(i,2)<>0) then

dbms_output.put_line(i);

end if;

i:=i+1;

exit when i=10;

end loop;

end;

--while循环

declare mx binary_integer:=10;

i binary_integer:=1;

begin

while(i<=10) loop

if(mod(i,2)<>0) then

dbms_output.put_line(i);

end if;

i:=i+1;

end loop;

end;

--for循环

declare mx binary_integer:=10;

i binary_integer:=1;

begin

for i in 1..mx loop

if(mod(i,2)<>0) then

dbms_output.put_line(i);

end if;

end loop;

end;

--倒序

declare mx binary_integer:=10;

i binary_integer:=1;

begin

for i in reverse 1..mx loop

if(mod(i,2)<>0) then

dbms_output.put_line(i);

end if;

end loop;

end;

--select into 语句只能抓取单行记录

declare mysal emp.sal%type;

begin

select sal into mysal from emp where ename='SMITH';

dbms_output.put_line(mysal);

end;

2.定义变量

declare mysal emp.sal%type;

myjob emp.job%type;

begin

select sal,job into mysal,myjob from emp where ename='SMITH';

dbms_output.put_line(mysal||myjob);

end;

declare myemp emp%rowtype;

begin

select * into myemp from emp where ename='SMITH';

dbms_output.put_line(myemp.ename||myemp.sal);

end;

--删除

begin

delete from emp where ename='SMITH';

rollback;

end;

--如果表存在删除,不存在不执行

declare totalCount number;

begin

select count(rowid) into totalCount from tab where ename='SMITH';

if(totalCount>=1) then

execute immediate 'drop table emp';

end if;

end;

dml中cud都可以直接出现plsql中，select 必须搭配into

ddl语句不允许出现plsql语句, execute immediate 'ddl'

例子如下:

1.使用plsql打印9*9乘法表

declare

x number:=1;

y number:=1;

begin

for x in 1..9 loop

for y in 1..9 loop

if y<x+1 then

dbms_output.put(x||'*'||y||'='||x*y||' ');

end if;

end loop;

dbms_output.put_line(' ');

end loop;

end;

2.使用plsq输出1-1000中所有的质数（只能被1和自己整除的数）

declare

flag boolean;

begin

for i in 2..1000 loop

flag:=true;

for j in 2..trunc(i/2)loop

if(mod(i,j)=0)then

flag:=false;

exit;

end if;

end loop;

if (flag)then

dbms_output.put_line(i);

end if;

end loop;

end;

3.使用数组添加元素后排序输出

declare type myTable is TABLE OF number INDEX BY BINARY_INTEGER;

arr myTable;

x number;

begin

arr(1):=5;

arr(2):=3;

arr(3):=7;

arr(4):=4;

for i in 1..4 loop

for j in i+1..4 loop

if(arr(i)>arr(j))then

x:=arr(i);

arr(i):=arr(j);

arr(j):=x;

end if;

end loop;

for i in 1..4 loop

dbms_output.put_line(arr(i));

end loop;

end;

五.表约束

create table USERINFO
(
userid NUMBER,
uname VARCHAR2(20),
createtime DATE

)
--添加列
alter table USERINFO add telephone number(7);
--删除列
alter table USERINFO drop column telephone

--修改列类型
alter table USERINFO modify telephone number(8)
--修改列名
alter table USERINFO rename column telephone to phone
--列添加注释
comment on column USERINFO.Userid
is '用户id'
comment on column USERINFO.Uname
is '用户姓名'

--修改表本身
--重新命名表
rename USERINFO to myuser
comment on table myuser
is '用户表'
select * from userinfo;

给students表添加两个字段：age和gender，性别的默认值为’男’。
更新age列，将它默认值设置为18。
将age列删除。
把表名更改为stu
create table students
(
stuid number,
stuname varchar2(20)
)
alter table students add age number(3);
alter table students add gender varchar2(3) default '男';
alter table students modify age number(3) default 18;
alter table students drop column age;

rename students to stu;
select * from students for update;
insert into students(stuid,stuname) values(2,'张三');
commit;

--将部门下的所有的员工信息，列表显示, 研发部张三/小米财务部王晓
select d.deptno,d.dname,wm_concat(e.ename) from emp e inner join dept d on e.deptno=d.deptno group by d.deptno,d.dname

一 not null 不能为空约束
约束的定义无语法顺序
create table USERINFO
(
userif number,
uname varchar2(20) constraints MM_USERINFO_UNAME not null,
createtime date
)
alter table userinfo modify uname varchar2(20) default 'zs' not null;
alter table userinfo drop constraints MM_USERINFO_UNAME --根据别名删除约束

二.unique(该列的值,不允许重复)
create table userinfo
(
userid number constraints UQ_USERINFO_USERID unique,
uname varchar2(20) not null,
createtime date
)
alter table userinfo modify userid number unique

三.check(最灵活, 可以使用where中的条件控制)
drop table userinfo;
create table userinfo
(
userid number,
uname varchar(20) not null constraints CHK_USERINFO_UNAME check(length(uname)<3),
sex varchar2(3) check(sex in('男','女')),
createtime date
)

select * from userinfo;

四.primary key(每一张表都有一个唯一标识符主键)
primary key 就将相当于添加 unique+not null

create table userinfo(
userid number primary key,
uname varchar(20) not null,
createtime date
)
alter table userinfo modify userid number constraints PK_USERINFO_USERID primary key,

五.foreign key (外键) 用于关联到其他表的列
外键要求值必须在引用的主表中存在的主键
create table grade(
gradeid number primary key,
gradename varchar2(20)
);
create table student(
stuid number primary key,
stuname varchar2(20),
gradeid number references grade(gradeid)
);

删除表：把表中所有的行和表结构都删除。
DROP TABLE 表名;
Oracle中删除表时并没有直接删除，只是放置到“回收站”
显示回收站中的对象：SHOW RECYCLEBIN;
恢复回收站中的表：FLASHBACK TABLE 表名 TO BEFORE DROP;
删除回收站中的表：PURGE TABLE 表名;
彻底删除：DROP TABLE 表名 PURGE;
截断表：删除表中所有的数据行，重置表的存储空间。
TRUNCATE TABLE 表名;

--列题如下:
现有一分组对抗赛表，表中有队名和组编号：
中国 A
韩国 A
日本 A
美国 B
巴西 B
荷兰 B
现要通过查询实现A组和B组进行循环对抗，写出完整建库表脚本和SELECT语句
create table gpgame(
gameid number,
country varchar2(20),
grname varchar(1)
)
select * from gpgame;

insert into gpgame values((select nvl(max(gameid),0)+1 from gpgame),'中国','A');
insert into gpgame values((select nvl(max(gameid),0)+1 from gpgame),'韩国','A');
insert into gpgame values((select nvl(max(gameid),0)+1 from gpgame),'日本','A');
insert into gpgame values((select nvl(max(gameid),0)+1 from gpgame),'美国','B');
insert into gpgame values((select nvl(max(gameid),0)+1 from gpgame),'巴西','B');
insert into gpgame values((select nvl(max(gameid),0)+1 from gpgame),'荷兰','B');

select a.country,b.country from (select country from gpgame where grname='A')a,(select country from gpgame where grname='B') b;
delete from gpgame where gameid=1

六.视图 添加如下表
学生信息表（学生编号，学生姓名，学生年龄日期，学生出生日期，学生入学时间，学生性别，所在班级编号）
班级表（班级编号，班级名称）
以下题目请使用sql来完成：

--班级表
create table classes
(
clsid number,--班级编号
clsname varchar2(20) --班级名称

)

select * from classes for update;
drop table classes;
--学生表
create table student
(
stuid number, --学生编号
stuname varchar2(20), --学生姓名
age number(3), --学生年龄
birthdate date, -- 学生出生日期
begindate date, --学生入学时间
sex varchar2(3), --学生性别
clsid number -- 所在班级编号
)
select * from student for update;
insert into student(stuid,stuname) values('1','小明')
drop table student

1.给学生编号添加主键约束
alter table student modify stuid number primary key;
2.给所在班级编号添加主键约束
alter table classes modify clsid number primary key;
3.给学生性别添加约束只能是男和女
alter table student modify sex varchar2(3) constraints CHK_STUDENT_SEX check(sex in('男','女'));
4.给学生年龄添加约束只能1-100
alter table student modify age number(3) constraints CHK_STUDENT_AGE check(age between 1 and 100);
5.给学生出生日期添加约束月份只能是偶数
alter table student modify birthdate date constraints CHK_STUDENT_BIRTHDATE check(mod(to_char(birthdate,'mm'),2)=0);
6.给学生姓名添加唯一约束和 not null约束
alter table student modify stuname varchar(20) constraints PK_STUDENT_STUNAME primary key not null;
7.给学生姓名添加约束只能输入中文
alter table student modify stuname varchar(20) constraints CHK_STUDENT_STUNAME check()
8.试着给所在班级添加约束要求该列的值只能在班级表存在的才能插入
alter table student modify clsid number references classes(clsid);

add constraints FK_STUDENT_CLSID foreign key (clsid)
insert into student values(STU_SC.nextval,'王华','15',to_date('2002-5-4','YYYY-MM-DD'),to_date('2017-8-24','YYYY-MM-DD'),'男','3');
insert into student values(STU_SC.nextval,'小红','18',to_date('1998-4-21','YYYY-MM-DD'),to_date('2016-8-24','YYYY-MM-DD'),'女','5');
insert into student values(STU_SC.nextval,'小明','17',to_date('1997-11-4','YYYY-MM-DD'),to_date('2017-8-24','YYYY-MM-DD'),'女','3');
insert into student values(STU_SC.nextval,'曹操','21',to_date('1996-6-12','YYYY-MM-DD'),to_date('2016-8-24','YYYY-MM-DD'),'女','3');
insert into student values(STU_SC.nextval,'刘备','20',to_date('1996-4-13','YYYY-MM-DD'),to_date('2016-8-24','YYYY-MM-DD'),'男','5');

insert into classes values(STU_SC.nextval,'1501');
insert into classes values(STU_SC.nextval,'1502');

9.添加一个序列从1开始增长步长为2 最大为10000
给学生信息表和班级表插入一些测试数据每个表的主键使用序列插入

create sequence STU_SC
minvalue 1
maxvalue 10000
start with 1
increment by 2
cache 20;

select STU_SC.nextval from dual;
insert into student(stuid,stuname) values(STU_SC.nextval,'张三');
select * from student;

select STU_SC.currval from dual;
insert into classes(clsid,clsname) values(STU_SC.currval, '1607')
select * from classes;

drop sequence STUDENT_SC
10.给学生性别添加一个位图索引
select * from student where sex=12
create BITMAP index bm_student_sex on student(sex);
11.创建以下视图
查询今年入学的所有学生
create view stuview as select * from student where to_char(begindate,'YYYY')=to_char(sysdate,'YYYY');
select * from stuview
统计1501班所有的女性同学
create view stu_se as select c.clsname,s.sex from student s inner join classes c on s.clsid=c.clsid where c.clsname='1501' and s.sex='女';
select * from stu_se
统计所有年龄大于18岁的所有学生
create view stu_ag as select * from student where age>18;
select * from stu_ag;

12.创建scott关于表 emp和dept的同义词
查询部门20的所有雇员
查询部门20中年薪最高的雇员
select e.* from emp e where deptno=20;
create synonym e for scott.emp;
select max(sal*12) from e;

阅读全文

0 0