Oracle基础

一、基本数据库表的操作：

1、创建表：创建表的同时添加约束（包含三种添加约束的方式--要仔细区分哦）

create table student(
  sno number(3) primary key,--主键约束
  sname varchar(23) not null,--非空约束
  sex varchar(3) default '男' not null,--设置默认值+非空
  tel varchar(12) not null,
  constraint uq_tl unique(tel)--constraint：限制、约束  添加唯一约束到tel列
  --uq_tl为定义的约束名
);

 注意：主键非空且唯一

create table student(
  sno number(3) unique,
  sname varchar(23) not null,
  sex varchar(3) default '男' not null,
  tel varchar(12) not null constraint uq_tel unique//非空约束再添加添加唯一约束
);

 检查约束：

create table student(
  sno number(3),
  sname varchar(21),
  sage number(2) check(sage between 16 and 60),--检查约束，年龄在16-60之间则符合约束
  phone varchar(11) constraint ch_phone check(phone like '1%'),--利用模糊查询，手机号以1开头的则符合检查约束
  grade number(3),
  constraint c_grade check(grade between 0 and 100)  --班级在0-100之间的检查约束
);

 外键约束：

/*第一种方式*/
create table employee(
  eno number(3),
  age number(3),
  phone varchar(11),
  deptno number(10) constraint fk_deptno1 REFERENCES dept(deptno)--references 指向dept表的deptno列（即为外键），使从表dept与主表employee的deptno相关联
);

/*添加外键的第二种方式*/
create table employee(
  eno number(3),
  age number(3),
  phone varchar(11),
  deptno number(10),
  constraint fk_deptno1 foreign key(deptno) REFERENCES dept(deptno)
);

2、删除表

drop table student;

3、修改表

 改表名称：

rename employee to employe;--rename 旧名称 to 新名称

改表结构，增加新列：

alter table employee add(sex varchar(3) default '女');

表创建完成后添加约束：

alter table employee add(address varchar(50) not null);--为employee 表的address 添加非空约束

alter table employee modify(eno primary key);--修改约束

修改列和删除列：

alter table employee modify(age number(2));--修改列长

alter table employee drop(sex);--修改表结构，删除列

二、表中数据的操作
1、约束管理，禁止，激活，删除，重命名

--添加表注释，添加字段注释
--注释的作用：字段注释-说明字段含义，表注释-说明表含义
comment on table student is '学生表';--表的注释 comment on关键字
comment on column student.sno is '学号';--列加注释 comment on column关键字

--重命名约束
--1创建表
create table student(
  sno varchar2(6) primary key,
  sname varchar2(20) not null,
  stell number(11) constraint uq_tel unique,
  age number(2) constraint ch_age check(age between 19 and 60)
);
--2插入语句
insert into student values(12,'乾进',17806270773,25);
insert into student values(11,'lisi',17806270774,25);
--3修改约束名称
alter table student rename constraint SYS_C0011500 to pk_sno;--oldname to new name

--禁止约束
alter table student disable constraint ch_age;
--激活约束
alter table student enable constraint ch_age;
--删除约束
alter table student drop constraint ch_age;

2、索引

作用：加快查询效率 ，索引是加载字段上的
数据量大，需要经常查询数据、修改删除插入等用的较少的数据表可加入索引，加入索引后查询数据变快，但是删除、修改数据速度变慢。原因是什么呢？因为添加索引后，对于数据量大的数据表，查询的时候只需要根据几个字段进行查询，而不再需要将左右的数据加载进行查询；而删除和修改数据表是因为要同时删除和修改索引表的数据，因此数据的处理速度变慢。

--创建单一索引
create index idx_sname on student(sname);
--创建复合索引  尽量把查询用的最多的列放到前面
create index idx_sno_sname on student(sno,sname);

--利用索引进行查询
select sname from student;--启用单一索引
select sno from student;--启用复合索引
select sno,sname from student;--启用复合索引 
select sno,sname,sex from student;--启用复合索引 三个打乱顺序也能启用
select sname,sex，sno from student;--启用复合索引

3、插入、修改、删除、查询数据语句

insert into student values(10,'赵六',15205368463,43);
insert into student(sno,sname,stell) values(9,'赵六',15205368467);

update student set stell=12546789,age=34 where sno=9;

delete from student where sno='9';
--回滚
rollback;

--简单数据查询
select * from emp;
select empno,ename from emp;
select empno,ename from emp where sal>3000;
select empno,ename from emp where sal>2000;
--添加别名，省略了as
select empno,ename,sal*12+1000 年薪 from emp where sal>3000;
select empno,ename,sal*12+1000 as 年薪 from emp where sal>3000;
--先按照身高排序，身高相同的再按照年龄排序 desc将序排列
select * from student order by height desc,age desc;
--不声明的话，默认升序asc
select empno,ename from emp where sal>3000;
select * from emp order by sal desc;

--联合查询,两张表查询后结果合在一张表里
select sname from student union select ename from emp;

--模糊查询
select * from emp where ename like '%A%';
select * from emp where ename like 'A____';
select * from emp where ename like '__A__';
--null值查询
select * from emp where comm is null;
select * from emp where JOB is not null;

4、分组语句，分组函数

分组：用groupby关键字 

--几个关键字  最小值 最大值 平均值 求和 数量
min max avg sum count
select count(*) from emp;--emp表中有几条数据

--分组函数
select deptno,job,min(sal) from emp group by deptno,job;
select deptno,avg(sal) from emp group by deptno;
--退一法（保留两位小数，直接舍去）
select deptno,trunc(avg(sal),2) from emp group by deptno;
--进一法（保留两位小数，四舍五入）
select deptno,round(avg(sal),2) from emp group by deptno;

select deptno,round(avg(sal),2) from emp where job is not null group by deptno having avg(sal)>1000;
--分组函数不能体现在where语句中  要用having子句加在group by之后
--有having语句一定有group by语句，反之不一定
select deptno,sum(sal) from emp where deptno!=30 group by deptno having sum(sal)>2000;

5、多表查询连接查询

连接查询：
内连接： 显式内连接-inner join on  && 隐式内连接
外连接：左外连接 && 右外连接 && 完整外连接

--显式内连接  
select e.ename,e.sal,e.deptno,d.dname,d.loc from emp e 
inner join dept d 
on e.deptno=d.deptno
where d.deptno<>30;--!=相当于<>
--隐式内连接
select e.*,d.dname,d.loc
from emp e,dept d
where d.deptno=e.deptno and d.deptno!=30;

--左外连接
select e.ename,e.sal,e.deptno,d.dname,d.loc from emp e 
left join dept d 
on e.deptno=d.deptno
where d.deptno<>30;
--右外连接
select e.ename,e.sal,e.deptno,d.dname,d.loc from emp e 
right join dept d 
on e.deptno=d.deptno
where d.deptno<>30;
--完整外连接
select e.ename,e.sal,e.deptno,d.dname,d.loc from emp e 
full join dept d 
on e.deptno=d.deptno
where d.deptno<>30;

关于左外连接右外连接以及完整外连接的区别：看懂这张图就足够了

6、子查询

分为单行子查询、多行子查询、多列子查询、 相关子查询

--单行子查询  指的是子查询的结果只有一行 子查询执行一遍
select sal from emp where ename='ALLEN';
select * from emp where sal> 1600;
select * from emp where sal>(select sal from emp where ename='ALLEN');
--多行子查询  any,all,in  子查询执行一遍
select * from emp where sal>any(select sal from emp where deptno=20);
select * from emp where sal in (select sal from emp where deptno=20);
--相关子查询  主查询执行多少次，子查询就执行多少次
select * from dept d where 2<(select count(*) from emp where d.deptno=deptno);

三、练习题

在不看答案的情况下，完成以下题目，那么Oracle的基础知识你已经掌握了。

练习题中涉及三张表：student表、course表、sc表

表结构如下：

表中的数据如下：

1、student表

2、course表

3、sc表

--1求学生编号为’0001’的学生的学号、姓名、性别
select sno,sname,sex from student where sno='0001';
--2求学院编号(DNO)为’10’ 的男生的学号、姓名、性别
select sno,sname,sex from student where dno='10';
--3求选修授课号为‘327401’且成绩在80～90之间的学生学号和成绩，并将成绩乘以系数0.8输出，且将SNO列更名为学号，成绩列更名为处理成绩 。
select sno as "学号",grade*0.8 as "处理成绩" from sc where cno='327401' and grade between 80 and 90;
--4求选修了课程的学生的学号
select DISTINCT(sno) from sc where cno is not null;
--5求姓名中包含’丽’的学生信息
select * from student where sname like '%丽%';
--6姓名是二个字的张姓同学
select * from student where sname like '张_';
--7所有姓张得同学
select * from student where sname like '张%';
--8统计选课学生人数及最高分成绩和最低分成绩
select count(distinct(sno)),max(grade),min(grade) from sc;
--9查询授课编号为‘153701’的课程的平均分
select avg(grade) from sc where cno='153701';
--10统计各个学院的人数  
select dno,count(sno) from student group by dno;
--11按课程号统计选修该课程的人数，并按照人数升序排列。
select cno,count(sno) from sc group by cno ORDER BY count(sno);
--12统计成绩超过80分的学生人数及平均成绩
select cno,count(sno),avg(grade) from sc where grade>80 group by cno; 
--13求学号为'0001'的同学的每门课的成绩，输出格式为：学号，课程名，课程成绩
select s.sno,c.cname,s.grade from sc s 
inner join course c
on s.cno = c.cno 
where s.sno='0001';

--14查询每个学生的每门课程的成绩，要求输出学号，课程名，成绩
select s.sno,c.cname,s.grade from sc s,course c
where s.CNO = c.CNO order by s.sno;

--15查询每个学生的每门课程的成绩，要求输出学号，姓名，课程名，成绩
select s.sno,st.sname,c.cname,grade from sc s
inner join student st on s.sno=st.sno
inner join course c on s.cno=c.cno
order by s.sno;

--16使用嵌套查询方法，查询“赵蓉”教师任课的学生成绩，并按成绩递增排列
select grade from sc where cno 
in (select cno from course where tname='赵蓉') 
order by grade asc;

--17查询成绩比该课程平均成绩低的学生成绩表
select round(avg(grade),2) from sc group by cno;
select c.cname,st.sname,s.grade from course c,student st,sc s
where  s.sno=st.sno and s.cno=c.cno and 
s.grade < any(select round(avg(grade),2) from sc);

--18用子查询语句查询选修了“线性代数“的学生学号和姓名
select sno,sname from student where sno 
in (select sno from sc where 
cno=(select cno from course where cname='线性代数'));

文中如有不正确的地方还请大家指正