oracle基础入门教程

----第一章----

表空间->用户->表
– 创建表空间
create tablespace J5
datafile ‘c:\J5.dbf’
size 10M
autoextend on

– 创建用户
create user hong
identified by dream1234
default tablespace J5
temporary tablespace temp;
–赋权
grant connect to hong;

grant dba to hong;-- 为了授课方便，统一给dba角色

grant select ,update on 表名 to 用户名;

–撤消权限
revoke select ,update on 表名 from 用户名;
revoke select ,update on 用户名.表名 from 用户名; – 回收操作权限

revoke dba from 用户名;–回收角色

–修改密码
alter user 用户名 IDENTIFIED by 密码;

– 删除用户
drop user 用户名;
drop user hong cascade;–连带模式一起删除用户

–登录命令行
–sqlplus 用户名/密码@主机标识符

-------第二章-----------

–mysql的数据类型：tinyint、int、mediumint、longint、char、varchar
–oracle数据基本类型：
char(5)：数据类型的列长度可以是 1 到 2000 个字节
varchar：数据类型的大小在1至4000个字节范围内
1、oracle里面varchar类型是为了跟其它数据库兼容，当设置为varchar类型的
后会自动转换为varchar2类型
2、创建的类型为int型也会自动转换为number(38)
varchar2：
nvarchar2:后面带的字符
UTF8：一个汉字占三个字节
GBK：一个汉字占两个字节
– 与mysql不一样的是，char与varchar后面带的是字节个数

create table test(c1 char(10),
c2 varchar(14));
insert into test1 values(‘一二三’,‘一二三四’);
insert into test1 values(‘一二三’,‘一二三四’);

select table_name from tabs; --查看当前用户存在哪些表
desc test1;

– 数值类型
– number(p,s):数字型p是位数总长度, s是小数的长度, 可存负数 ,最长38位. 不够位时会四舍五入.
number(5,2) 145,678 – 145.68
number(5,2) 12345,678 --报错
number(5,-2) 145,678 --100
number(5,-2) 1567,678 --1600
number(5,-2) 156734.6789 --156700

create table test2(id1 number(5,2),
id2 number(5,2),
id3 number(5,-2),
id4 number(5,-2));
insert into test2(id3) values(156734.6789);
select * from test2

– 日期类型date DD-MM-YYYY

select sysdate from dual;-- dual是一张万能表
create table test3(d date);
insert into test3 values(‘24-May-2020’);–通过连接工具对于date的格式
insert into test3 values(‘24-9月-2020’);–通过命令行对于date的格式
select * from test3;

–创建学生表
create table student(
studentid int primary key,
name char(20) not null,
address varchar(50),
grade float,
email varchar(50),
sex int
);

–插入学生信息数据 --oracle不支持批量插入,并且values中的s不能少
insert into student values (1,‘张三’,‘深圳’,1,‘zhangsan@163.com’,1);
insert into student values (2,‘李四’,‘广州’,1,‘ls@163.com’,0);
insert into student values (3,‘王宝强’,‘陕西’,1,‘werbq@163.com’,1);
insert into student values (4,‘麻子’,‘上海’,1,‘mz@163.com’,1);
insert into student values (5,‘田七’,‘长沙’,1,‘tqi@163.com’,0);

select * from student;

– 复制表
– create table 新表 as select * from 旧表; --新表不能是已存在的表
create table stu1 AS
select * from student;–与mysql不一样的是，这里的as不能少

– insert into 已存在的表 select * form 旧表;–已存在的表必须是已创建好的
insert into stu2
select * from student;

– 复制表结构
create table stu2 as
select * from student where 1=2;
select * from stu2;

–修改表
– 修改表结构alter table
alter table stu add sage int;–增加表字段
alter table stu modify sage char(10);–修改字段类型
alter table stu rename column sage to age; --修改字段名
alter table stu drop column age ;
alter table stu drop(sage); --删除字段名
rename stu to student_copy;-- 修改表名
alter table student_copy rename to stu;–修改表名

–修改表数据
update stu set name=‘张四’ where name=‘张三’;
select * from student where studentid=10;

update student set studentid=1 where studentid=10;

– 删除表
–删除表数据delete truncate
delete与truncate的区别：
1、delete带条件
2、delete删除不会重置auto_increment，而truncate会重置
3、delete不会立即从磁盘删除，而truncate是立即释放
4、delete删除后可以回滚
delete from stu2;
truncate table stu;–table不能掉

– 删除表数据+结构drop
drop table stu;–不支持同时删除多个表

–约束
–主键 、外键、唯一、非空、默认、自增
–主键
– 创建表时创建
–创建课程表
create table course(
courseid int primary key,
coursename varchar(50)
);

create table course(
courseid int ,
coursename varchar(50),
primary key(courseid)
);

–修改表时创建
alter table course add constraint c_pk primary key(courseid);
–删除主键
alter table course drop primary key;

select * from user_constraints where table_name=‘COURSE’;–查询当前用户下的约束信息
select * from user_cons_columns where table_name=‘COURSE’;–查询字段与约束的关系

–查看表下面的约束关系：
select user_constraints.OWNER,user_constraints.table_name,user_constraints.CONSTRAINT_NAME,user_constraints.CONSTRAINT_TYPE,
user_cons_columns.COLUMN_NAME from user_constraints,user_cons_columns
where user_constraints.CONSTRAINT_NAME=user_cons_columns.CONSTRAINT_NAME
and user_constraints.OWNER=‘HONG’
and user_cons_columns.table_name=‘STUDENT’;

–外键
–创建表时添加外键
–创建分数表
create table score(
scoreid int primary key,
studentid number not null,
courseid number,
score number ,
constraint s_pk foreign key(studentid) references student(studentid)
);
–删除外键约束
alter table score drop constraint s_pk;

–check检查
–创建表时添加
drop table student;
create table student(
studentid int primary key,
name char(20) not null,
address varchar(50),
grade float,
email varchar(50),
sex char(3) check(sex in (‘男’,‘女’))
);
insert into student values (1,‘张三’,‘深圳’,1,‘zhangsan@163.com’,‘不’);

–删除约束
alter table student drop constraint SYS_C005154;
–在已存在的表上添加check约束
alter table student modify sex char(3) check(sex in (‘男’,‘女’));

–运算符
–比较运算符=、!=、<=、<等等，oracle中不支持rlike
–连接运算符
select ‘中国’,address from stu;
select concat(‘中国’,address) “地址” from stu;–别名要么为双引号，要么不要引号
select ‘中国’||address “地址” from stu;–跟concat的效果一样，Msyql不支持

–数据的提交与回退
– CREATE、DROP、TRUNCATE TABLE不经过回退段
–INSERT、UPDATE、DELETE操作对数据产生的影响
select * from student;

savepoint p1;
update student set name=‘张三’ where studentid=1;
savepoint p2;
delete from student where studentid=1;
savepoint p3;
update student set name=‘张四’ where studentid=1;
rollback to p2;

---------第三章--------

–伪列
– rowid、rownum
– rowid:地址编号
– rownum:伪列
– 查询班上学号小于5的学生信息
select * from student where studentid < 5;
select * from student order by studentid asc limit 5;–oracle中不存在limit

select * from student where rownum < 3;–在oracle中通过伪列替代limit

– 查询班上rownum大于3的学生信息
select * from student where rownum >3;–为空，rownum始终都是从1开始取

select * from (select studentid,name,rownum r from student)t
where t.r > 3;

– 查询score表分数最高的学生姓名及最高分数
select t.studentid,name,t.max(score) from student,
(select max(score),studentid from score)t
where t.studentid = student.studentid;–注意给max(score)加别名

–日期函数
select next_day(sysdate,‘星期一’) from dual;

–sign(数值表达式)
– 返回表达式的结果是正、负、零、空值
–1 正, -1 负, 0 零或相等, ‘’ NULL

select sign(5-1),sign(1-5),sign(5-5),sign(’’) from dual;
select * from student;

–在select中实现if else
decode(表达式,值1,结果1,[值2,结果2…][,否则]
– 查询学生信息表中的信息，并且显示年级大于等于2的学生信息

select * from student where grade >=2;
select name,decode(sex,1,‘女’,0,‘男’,‘不合格的性别’) 性别
from student ;

–查询分数表，并按等级来显示
select score,
decode(sign(score-60),0,‘刚好及格’,
1,‘优秀’,
-1,‘未及格’)
from score;

–转换函数
–to_date(date,format)：将字符串转为日期类型
select to_date(‘2020-09-12 15:23:34’,‘YYYY/MM/DD HH24:mi:ss’)
from dual;
–to_char(date,format)：将日期类型转为字符型
select to_char(date’2020-09-12’,‘YYYY-DD-MM’)
from dual;
–to_number：将字符型转为数值型
select to_number(‘a’)from dual;–只能将字符型数字转为数值型

—转换空值的函数
–NVL(A,B) 假如A为null的话那么返回B，A不为NULL直接返回A
select nvl(1,2)from dual;
–NVL2(表达式,A,B),表达式不为null的话返回A，为null的话返回B
select nul2(4>5,‘不为空’,‘为空’)from dual;
–NULLIF(A,B),判断A=B，假如相等那么返回空值，否则返回A值
select nullif(4,5)from dual;

–分组
–group by
select sex,name from student group by sex;–oracle报错
–在分组中，select后面的值必须 是分组字段，要么就是结合 聚合
–函数一起使用的字段

–where跟having的区别
– 统计每个地区20岁以上的学生人数,不包含长沙地区的

select address，count(*) from student
where age > 20
group by address
having address !=‘长沙’;

SELECT stu.sno, stu.sname FROM student stu WHERE stu.sno IN
#选出所有李诚老师所授课程的成绩单，根据学号分类，
#数目等于李诚老师的全部课程数的为全选
(SELECT sc.sno
FROM score sc
WHERE sc.cno IN
(SELECT co.cno FROM teacher tea,course co
WHERE tea.tno=co.tno AND tname=‘t1’)
GROUP BY sc.sno HAVING COUNT(*)>=
(SELECT COUNT(co.cno) FROM teacher tea,course co
WHERE tea.tno=co.tno AND tname=‘t1’)
)

– 集合运算符
–MINUS:表1 miunus 表2 ：在表1中减掉表2的结果
select * from t1
MINUS
select * from t2;–t1-t2的结果

select * from t2
MINUS
select * from t1;-- t2-t1的结果

–UNION：对表的个数与类型必须要一样
select sno,sname from student
union
select tno,tname from teacher;

–INTERSECT：交集
select * from t1
intersect
select * from t2;

create table t1(id number);–1,3,5,7,9
insert into t2 values(9);
create table t2(id number);–1,3,2,4,9

–多表之间的关联
–内连接
–查询及格了的学生信息
1、通过=号进行连接
2、通过inner join on
3、通过join on
select * from student ,score
where student.studentid=score.studentid
and score > 60;

– 外连接（left/right/full [outer]join on)
– 左连接：left join on
显示所有左表内的数据及满足连接条件的右表数据
–右连接：right join on
显示所有右表内的所有数据及满足连接条件的左表数据
– 全连接：full join on
select * from student
full join score
on student.studentid = score.studentid;

– case语句
CASE用法1
CASE 表达式/字段/变量…
WHEN 值1 THEN 结果1
WHEN 值2 THEN 结果2　
…
WHEN 值N THEN 结果N
ELSE 默认结果
END
–以具体年级名称来查询student表中的信息（grade为1,显示一年级,为2,二年……）
select * from student;
select studentid,name , case grade
when 1 then ‘一年级’
when 2 then ‘二年级’
when 3 then ‘三年级’
else ‘其它年级’
END 年级
from student;

CASE用法2
CASE
WHEN 条件表达式1 THEN 结果1
WHEN 条件表达式2 THEN 结果2　
…
WHEN 条件表达式N THEN 结果N
ELSE 默认结果
END
–按照分数显示优、良、及格、差等级来查询学生分数

select studentid,score, case
when score >= 80 then ‘优’
when score >= 70 then ‘良’
when score >= 60 then ‘及格’
else ‘不及格’
END 等级
from score;

–序列
–oracle中不支持auto_increment，一般通过序列来产生自增
create sequence J7_seq --序列名
start with 7 --开始值
increment by 1 —步长
maxvalue 10 --最大值
minvalue 1 —最小值，必须小于开始值
cycle —如果设置cycle，则必须设置maxvalue
cache 10 --默认为20，且设置的值必须小于等于产生的序列数

create table t3(id number primary key );
insert into t3 values(J5_seq.nextval);
select * from t3;

–currval:取当前的序列值
–nextval：序列的下一个值

select J5_seq.currval from dual;
select J5_seq.nextval from dual;

– 树形结构查询
–构造数据
create table tb_menu(
id number(10) not null,–主键ID
pid number(10) not null,–父菜单ID
title varchar2(50)–菜单名称
);

insert into tb_menu(id, pid,title ) values(1,0,‘父菜单1’);
insert into tb_menu(id, pid,title ) values(2,0,‘父菜单2’);
insert into tb_menu(id, pid,title ) values(3,0,‘父菜单3’);
insert into tb_menu(id, pid,title ) values(4,0,‘父菜单4’);
insert into tb_menu(id, pid,title ) values(5,0,‘父菜单5’);

insert into tb_menu(id, pid,title) values(6,1,‘一级菜单6’);
insert into tb_menu(id, pid,title) values(7,1,‘一级菜单7’);
insert into tb_menu(id, pid,title) values(8,1,‘一级菜单8’);
insert into tb_menu(id, pid,title) values(9,2,‘一级菜单9’);
insert into tb_menu(id, pid,title) values(10, 2, ‘一级菜单10’);
insert into tb_menu(id, pid,title) values(11, 2, ‘一级菜单11’);
insert into tb_menu(id, pid,title) values(12, 3,‘一级菜单12’);
insert into tb_menu(id, pid,title) values(13, 3,‘一级菜单13’);
insert into tb_menu(id, pid,title) values(14, 3,‘一级菜单14’);
insert into tb_menu(id, pid,title) values(15, 4,‘一级菜单15’);
insert into tb_menu(id, pid,title) values(16, 4,‘一级菜单16’);
insert into tb_menu(id, pid,title) values(17, 4,‘一级菜单17’);
insert into tb_menu(id, pid,title) values(18, 5,‘一级菜单18’);
insert into tb_menu(id, pid,title) values(19, 5,‘一级菜单19’);
insert into tb_menu(id, pid,title) values(20, 5,‘一级菜单20’);
commit;

insert into tb_menu(id, title, pid) values(21, ‘二级菜单21’,6);
insert into tb_menu(id, title, pid) values(22, ‘二级菜单22’,6);
insert into tb_menu(id, title, pid) values(23, ‘二级菜单23’,7);
insert into tb_menu(id, title, pid) values(24, ‘二级菜单24’,7);
insert into tb_menu(id, title, pid) values(25, ‘二级菜单25’,8);
insert into tb_menu(id, title, pid) values(26, ‘二级菜单26’,9);
insert into tb_menu(id, title, pid) values(27, ‘二级菜单27’,10);
insert into tb_menu(id, title, pid) values(28, ‘二级菜单28’,11);
insert into tb_menu(id, title, pid) values(29, ‘二级菜单29’,12);
insert into tb_menu(id, title, pid) values(30, ‘二级菜单30’,13);
insert into tb_menu(id, title, pid) values(31, ‘二级菜单31’,14);
insert into tb_menu(id, title, pid) values(32, ‘二级菜单32’,15);
insert into tb_menu(id, title, pid) values(33, ‘二级菜单33’,16);
insert into tb_menu(id, title, pid) values(34, ‘二级菜单34’,17);
insert into tb_menu(id, title, pid) values(35, ‘二级菜单35’,18);
insert into tb_menu(id, title, pid) values(36, ‘二级菜单36’,19);
insert into tb_menu(id, title, pid) values(37, ‘二级菜单37’,20);

insert into tb_menu(id, title, pid) values(38, ‘三级菜单38’,21);
insert into tb_menu(id, title, pid) values(39, ‘三级菜单39’,22);
insert into tb_menu(id, title, pid) values(40, ‘三级菜单40’,23);
insert into tb_menu(id, title, pid) values(41, ‘三级菜单41’,24);
insert into tb_menu(id, title, pid) values(42, ‘三级菜单42’,25);
insert into tb_menu(id, title, pid) values(43, ‘三级菜单43’,26);
insert into tb_menu(id, title, pid) values(44, ‘三级菜单44’,27);
insert into tb_menu(id, title, pid) values(45, ‘三级菜单45’,28);
insert into tb_menu(id, title, pid) values(46, ‘三级菜单46’,28);
insert into tb_menu(id, title, pid) values(47, ‘三级菜单47’,29);
insert into tb_menu(id, title, pid) values(48, ‘三级菜单48’,30);
insert into tb_menu(id, title, pid) values(49, ‘三级菜单49’,31);
insert into tb_menu(id, title, pid) values(50, ‘三级菜单50’,31);
commit;

select * from tb_menu;
– select 字段 from 表 start with 上级节点ID=节点值
– connect by prior 节点ID=上级节点ID

select * from tb_menu start with id=2
connect by prior pid = id;

欢迎各位朋友一起交流学习！~