Oracle基本操作&&查询总结(其一)

Oracle简单语句总结

一、Oracle表空间、用户操作

表空间操作
–1. 创建表空间
create tablespace studentspace
datafile ‘D:\Oracle\fanzhe\oradata\MYOWNER\studentdata01’ size 200m
autoextend on maxsize 500m;
–2 修改表空间
alter tablespace studentspace
add datafile ‘D:\Oracle\fanzhe\oradata\MYOWNER\studentdata02’ size 200m
autoextend on maxsize 500m;
–关闭表空间
alter tablespace studentspace offline;
–开启表空间
alter tablespace studentspace online;
–3 删除表空间
drop tablespace studentspace including contents and datafiles;
用户操作
–1创建用户
Creat user 用户名
Identified by 密码
Default tablespace 指定表空间
–2为用户授权
Grant dba（权限：connect（连接角色，基本角色） resource：开发者角色 dba：超级管理员角色） to 用户名

二、Oracle基本操作(注：字符串类型是单引号，而不能用双引号)

以下表在scott表下操作，其中四张是我自己建立的表（course、score、sdept、student），其余表全是scott用户下自带表。

--创建数据库
--1创建sdept表
create table scott.sdept(
 deptno char(2) primary key,
 dname char(30) not null unique,
 address character(30),
 telphone char(15) unique
)tablespace "STUDENTSPACE";
--2.创建学生表
create table scott.student(
 sno char(15) ,
 sname char(8) not null,
 ssex char(2),
 sage int default 18,
 deptno char(2),
 --主键约束
 constraint xpk_stu primary key(sno) ,
 --外键约束
 constraint fk_stu_sdept foreign key(deptno) references sdept(deptno),
 --检查约束
 constraint ck_stusex check(ssex='男' or ssex='女'),
 constraint ck_stuage check(sage between 15 and 35)
)tablespace "STUDENTSPACE";

--3.创建课程表
create table scott.course(
con char(8) primary key,
cname char(30) not null unique,
ccredit number(3,1),
institue char(2),
constraint fk_cour_sdept foreign key(institue) references sdept(deptno)
)tablespace "STUDENTSPACE";
--4.创建scroe
create table scott.score(
sno char(15),
cno char(8) ,
grade number(6,2),
constraint pk_score primary key(sno,cno),
constraint fk_sc_cour foreign key(cno) references course(cno),
constraint fk_sc_stu foreign key(sno) references student(sno)
)tablespace "STUDENTSPACE";

1）、–Insert遇到问题：
主键问题：唯一、非空
检查约束：不能违反
数据类型不匹配问题
缺失、过剩字段值。 例四个字段输入三个或者字段过多
2）、Update:
修改人文学院的王艳年龄改为17

update student set sage=17 where deptno=(select  deptno from sdept where dname like'%人文学院%'); 

将c语言这门课课程学分修改为4

update course set ccredit=4 where cname='c语言';

修改计算机的电话为88885555

update sdept set telphone=88885555 where dname='计算机学院';

修改学号为06010120212学生所在的院系为计算机学院

update student set deptno=(select deptno from sdept where dname='计算机学院') where sno='06010120212';

3）、Select:
–基本查询
1、查询与高天在同一院系的所有学生姓名
方一：

select sname 
from student
where deptno in(select deptno from student where 
sname='高天');

方二：自身查询

select s2.sname
from student s1,student s2
where s1.sname=s2.sname
and s1.sname='高天' and s2.sname!='高天';

2、查询选修了c语言的所有学生的姓名、学号、院系名称和成绩

select s1.sname,s1.sno,sdept.dname,s2.grade
from student s1,score s2,course c1,sdept
where s2.cno=(select cno from course where cname='C语言') and s1.sno=s2.sno and s2.cno=c1.cno and s1.deptno=sdept.deptno;

3、查询了选修了C语言的所有学生的学号、姓名

select student.sno,student.sname
from student,course,score
where score.cno =(select cno from course where cname='C语言')  and student.sno = score.sno and score.cno = course.cno;

4、查询选修了课程号为B002且成绩低于安然的所有学生的成绩

select sno,grade 
from score 
where cno='B002' and grade<
(select grade 
from score 
where sno in（select sno from student where sname='安然' and cno='B002' ))

5、使用内连接查询每个人的选修课成绩情况，要求显示学生姓名、课程号、成绩

select sname,cno,grade from student inner join score on student.sno=score.sno;

6、使用union查询自动化和人文学院的所有学生

select * from student 
where deptno=(select deptno from sdept where dname like'%人文学院%')

函数

• 聚合函数
  –count（*）和count（1）推荐使用 底层一样，都是查询第一列数量
  select count(1) from emp;–查询总数量
  select sum(sal) from emp;–工资总和
  select max(sal) from emp;–最大工资
  select min(sal) from emp;–最低工资
  select avg(sal) from emp;–平均工资

• 单行函数
  作用于一行，返回一个值

• 多行函数
  作用于多行，返回一个值

• 字符函数

select upper('yes') from dual;--YES  小写变大写
select lower('YES') from dual;--yes 大写变小写

• 数值函数

select round(26.16,1) from dual;
--四舍五入（26.2），后面表示保留的位数
select trunc(26.16,1) from dual;
--直接截取（26.1），后面表示保留的位数
select mod(10,3) from dual;
--求余数（1）

• 日期函数
  –查询出emp表中所有员工入职距离现在几天

select sysdate-e.hiredate from emp e;

--算出明天此刻
select sysdate+1 from dual;
--查询出emp表中所有员工入职距离现在几月
select months_between(sysdate,e.hiredate) from emp e;
--查询出emp表中所有员工入职距离现在几年
select months_between(sysdate,e.hiredate)/12 from emp e;
--查询出emp表中所有员工入职距离现在几周(四舍五入去掉小数)
select round((sysdate-e.hiredate)/7) from emp e;

• 转换函数

–日期转字符串

select to_char(sysdate,'fm yyyy-mm-dd hh24:mi:ss') from dual;

–字符串转日期

select to_date( '2020-9-15 17:55:54','fm yyyy-mm-dd hh24:mi:ss') from dual;

• 通用函数
  –算出emp表中所有员工的年薪
  –奖金里面有null值，null和任意数字进行算数运算，结果都是null

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

条件表达式

通用写法（Oracle和MySQL通用）
–给emp表中员工起中文名

select e.ename ,
      case e.ename
        when 'SMITH' then '老年人'
          when 'WARD' then '松松皮'
            when 'BLAKE' then '曹贼'
              when 'FORD' then '弯弯龙'
                else '无名'
                end
from emp e;

–判断emp表中员工工资，>3000高收入，300-1500中等收入，其余低收入

select e.sal ,
      case 
        when e.sal>3000 then '高收入'
          when e.sal>1500 then '中等收入'
                else '低收入'
                end
from emp e;

注：–Oracle中除了起别名，都用单引号
–Oracle专用写法

select e.ename ,
      decode(e.ename,
        'SMITH' , '老年人',
           'WARD' , '松松皮',
             'BLAKE' , '曹贼',
               'FORD' , '弯弯龙',
                 '无名')中文名
from emp e;

分组查询

查询出每个部门的平均工资

--分组查询中，出现在group by后面的原始列，才可以出现在select后面
--没有出现在group by后面的列，想在select后面，必须加上聚合函数

–聚合函数有一个条件，可以把多行记录变成一个值

select e.deptno,avg(e.sal)--,e.ename
from emp e
group by e.deptno;

–查询平均工资高于2000的工资
–所有条件都不能使用别名

select e.deptno,avg(e.sal)--,e.ename
from emp e
group by e.deptno
having avg(e.sal)>2000;

（有的条件优先于servlet，条件不满足不会servlet）
比如下面
–查询出每个部门工资高于800的员工的平均工资

select e.deptno,avg(e.sal)
from emp e
where avg(e.sal)>800
group by e.deptno;

--where和having区别：
–where是过滤分组前的数据，having是过滤分组后的数据
–表现形式：where必须在group by之前，having在group by之后
–例：查询每个部门工资高于800的员工的平均工资
–然后再查询出平均工资高于2000的部门
select e.deptno,avg(e.sal)
from emp e
where e.sal>800
group by e.deptno
having avg(e.sal)>2000;

子查询

查询出每个部门最低工资和最低工资员工姓名和该员工所在部门名称
–1先查询出每个部门最低工资

select deptno,min(sal) msal
from emp
group by deptno;

–2三表联查，得到最终结果

select t.deptno ,t.msal ,e.ename ,d.dname 
from (select deptno,min(sal) msal
      from emp
      group by 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开始，依次递增，不能跳着走
–例：emp表工资倒序排列后，每页五条记录，查询第二页
–排序操作会影响rownum的顺序

select * 
from(
    select rownum r2,e.* from(
          select * from emp order by sal desc
          ) e where rownum<11
    ) 
where r2>5;

注：以后在Oracle中遇到分页查询，不会灵活运用的就可以嵌套模板

例：查询5-11页
select * 
from(
    select rownum r2,e.* from(
          select * from emp order by sal desc
          ) e where rownum<11
    ) 
where r2>5;