一、 Oracle 基础
1. Oracle 用户管理
--Oracle 用户管理
--1.创建账户
--创建用户
create user scott identified by hello;
--修改密码
alter user scott identified by root;
--忘记密码
--cmd 输入命令：sqlplus /nolog
--输入命令：conn /as sysdba
--输入命令：alter user 要修改的用户名 identified by 新的密码;
--2.维护账户
--赋予权限语法： grant 权限或角色名 to 用户名;
grant connect to scott; --赋予数据库登陆连接权限
grant resource to scott; --赋予资源操纵权限
grant dba to scott; --赋予 dba 权限
--删除权限语法： revoke 权限或角色名 from 用户名;
revoke dba from scott;
--3.删除账户
drop user scott;
2. Oracle 二维表管理
--Oracle 二维表管理
--1.Oracle 二维表的创建
--创建表的同时添加约束
--主键约束
--非空约束
--检查约束
--唯一约束
--外键约束
--创建表
varchar2(len) --len 表示字符的最大长度，实际内存根据字符长度大小分配
--特点：动态分布存储空间，节省空间
varchar(len) --直接分配最大长度的储存空间
--特点：存储效率高于 varchar2
create table student(
sno number(10), --primary key,
sname varchar2(100) not null,
sage number(3), --check(sage<150 and sage>0)
ssex char(4),
sfav varchar2(500),
sbirth date,
sqq varchar2(100), --unique
constraints pk_student_sno primary key(sno),
constraints ck_student_sage check(sage<150 and sage>0),
constraints un_student_sqq unique(sqq)
);
--删除表
drop table student;
...................................................................................
--2.添加约束
--1.添加主键，主键特点：非空唯一
--添加方法
直接在创建表字段后使用 primary key
或者 constraints pk_表名_字段名 primary key(字段名)
或者 alter table 表名 add constraints pk_表名_字段名 primary key(字段名);
--删除方法
alter table 表名 drop constraints 主键约束名;
--2.添加非空约束
--添加方法
直接在创建表字段后使用 not null
或者 constraints ck_表名_字段名 check(字段名 is not null)
或者 alter table 表名 add constraints ck_表名_字段名 check(字段名 is not null);
--删除方法
alter table 表名 drop constraints 非空约束名;
--3.添加检查约束
--添加方法
直接在创建表字段后使用 check(sage<150 and sage>0)
或者 constraints ck_student_sage check(sage<150 and sage>0)
或者 alter table student add constraints ck_student_sage check(sage<150 and sage>0);
--删除方法
alter table 表名 drop constraints 检查约束名;
--4.添加唯一约束
--添加方法
直接在创建表字段后使用 unique
或者 constraints un_student_sqq unique(sqq)
或者 alter table student add constraints un_student_sqq unique(sqq);
--删除方法
alter table 表名 drop constraints 唯一约束名;
--5.添加外键
--关键字：references
--概念：当一张表(子表)的某个字段的值需要依赖另外一张表(父表)的某个字段的值，则使用外键约束
--作用：当在子表中插入的数据在父表中不存在，则会自动报错
--外键选取：一般选取父表的主键作为子表的外键
--添加方法
直接在创建表字段后使用 references 父表名(字段)
或者 constraints fk_子表名_字段名 foreign key(字段名) references 父表名(字段名)
或者 alter table 子表名 add constraints fk_子表名_字段名 foreign key(字段名) references 父表名(字段名);
--删除方法
alter table 子表名 drop constraints 外键约束名;
--外键的缺点
--无法直接删除父表数据，除非级联删除
--级联删除：在添加外键约束时，使用关键字 on delete cascade
--使用：当删除父类数据时，自动删除子表相关所有数据
--缺点：无法保存子表历史数据
--使用关键字 on delete set null
--删除父表数据时，将子表的依赖字段的值设为 null
--注意：子表依赖字段不能添加非空约束，不然矛盾
...................................................................................
--3.Oracle 二维表的维护
--1.添加新的字段
--语法：alter table 表名 add 字段名 类型;
alter table student add sphone number(11);
--2.修改原有字段
--修改字段类型：modify
--语法：alter table 表名 modify 字段名 新类型;
alter table student modify sphone varchar2(100);
--修改字段名，基本不用
--语法：alter table 表名 rename column 旧字段名 to 新字段名;
alter table student rename column sphone to phone;
--删除字段：drop column
--语法：alter table 表名 drop column 字段名;
alter table student drop column phone;
--3.修改表名
rename student to student2;
--4.查看表结构
打开命令窗口，输入命令：desc student;
3. Oracle 二维表的增删改 & 备份
--Oracle 二维表的增删改 & 备份
--注意：增删改的 SQL 执行完毕后，不会立马进行数据的写入，还需手动对数据进行提交(Commit)，如果数据有问题可以回滚(Rollback)
--1.增加数据
--语法：insert into 表名(字段名, 字段名, 字段名, ...) values(值1, 值2, 值3, ...);
--主键必须给值，允许为空的字段可以不给值
--值和字段名必须一一对应
--如果是全字段插入，可以省略字段名部分，即 insert into 表名 values(值1, 值2, 值3, ...);
insert into dept(deptno, dname, loc) values(1, '计算机学院', '北京');
--2.删除数据
--删除一行记录
--语法：delete from 表名 where 条件;
--删除表中所有记录
--语法：delete from 表名;
--truncate table 表名; 删除表中的所有记录，效率比 delete 高
--3.更新数据
--语法：update 表名 set 字段名 = 新的值, 字段名 = 新的值, ... where 条件;
--4.数据的备份
--注意：只会备份表结构和表的数据，约束不会备份，比如主键没有设置
--表级别备份
--全部备份：create table 新的表名 as select * from 表名;
create table deptBak as select * from dept;
--部分备份
create table deptBak as select deptno, dname from dept;
--数据整体插入
--insert into 表名2 select * from 表名1;
--注意：表1和表2的表结构要一致，即字段数量一致，类型一致
4. 单表查询
--单表查询学习
--1.查询表的所有数据：select * from 表名;
select * from emp;
--2.查询表中的指定字段的值：select 字段名1,字段名2, ... from 表名;
select empno1,empno2 from emp;
--3.给查询结果中的字段使用别名
--在字段名后使用：字段名 as "别名";
--注意：as 关键字可以省略不写，别名中没有特殊字符双引号也可以省略不写
select empno 员工编号,job as 工作 from emp;
--4.连接符：字段名 || '字符' || 字段名 ||,... from 表名;
--注意：拼接好的字段名在结果集中作为一个新的字段显示，可以使用别名优化显示
--5.去除重复，使用 distinct 去除行重复，即两行数据完全相同取其一
select distinct job,mgr from emp;
--6.排序：order by 字段名 desc降序 / asc升序(默认)
--多字段排序(先按字段1排序,如果相同则按照字段2排序)：order by 字段名1,字段名2,... desc降序 / asc升序(默认)
select * from emp order by empno desc;
select * from emp order by empno1,empno2;
--7.使用 where 条件查询
--注意：在 where 中不能出现多行函数
--语法：select 字段名,.. from 表名 where 筛选条件;
--单筛选条件
--使用运算符进行筛选 =, >, <, >=, <=, <>
--多筛选条件
--在 where 子句中使用关键字
--and "与"，and 的优先级比 or 更高
--or "或"
--模糊查询 like, not like
-- % 表示任意多个的任意字符
select * from student where sname like '%s%'; --'%s%'包含s, '%s'以s结尾, 's%'以s开头, '_s%'第二个字符是s
select * from student where sname like '%/_%' escape '/'; --escape 把普通字符转为转译字符，转译字符可以使得特殊字符转换为普通字符
--is null
--is not null
--in
--between 100 and 200
5. Oracle 函数
--Oracle 函数学习(单行函数，多行函数，转换函数，其它函数)
--1.单行函数：不改变真实数据，只是修饰显示
--语法：select 字段名, 函数名(字段名),... from 表名;
--1.字符函数
length(字段名)
initcap(字段名) 字段名内容首个字母大写
lower(字段名)
upper(字段名)
ltrim('hello', 'he') -->llo
rtrim('hello', 'llo') -->he
concat('hello', 'World') -->helloWorld 连接字符串
instr('hello', 'e') -->2 所在下标
substr('hello', 2, 4) -->ello 取子串
replace('hello', 'l', 're') -->Herereo
--2.数值函数：对数值类型的数据进行运算
abs()
ceil()
floor()
round()
mod()
--伪表：关键字 dual ，不是真实存在的表，是为了方便进行数据的验证而临时存在的表
select 1+2 from dual;
--3.日期函数:
--sysdate 现在的时间
add_months('09-9月-2018', -3); --> 09-6月-2018
next_day('09-9月-2018', '星期日');
last_day('09-9月-2018'); --> 30-9月-2018
--2.多行函数 (max, min, avg, sum, count)
--作用：对查询的数据进行统计
--注意：多行函数不能和普通字段以及单行函数混用
--语法：select 字段名, count(字段名),... from 表名;
count(*) 返回表的记录数
count(字段名) 返回非空值的数量
count(distinct 字段名) 去除重复后的字段值的数量
--3.转换函数
--to_number()
--to_char()
--9 表示占位；0 可以进行占位；L 表示人民币符号；$ 表美元符号
select to_char(123456789, 'L000,000,000,000') from dual;
select to_char(123456789, '$999,999,999') from dual;
--to_date()
select to_date('2018-09-13','yyyy-mm-dd') from dual;
select to_date('2018/09/13','yyyy/mm/dd') from dual;
select to_char(to_date('2018/09/13','yyyy/mm/dd')) from dual;
--4.其它函数
nvl(字段名, 值m)：如果字段值不为 null，返回该字段值；若为 null，则返回值 m
nvl2(字段名, 处理1, 处理2)：如果字段值不为 null，执行处理1；若为 null，则执行处理2
decode(字段名, 值1, 处理1, 值2, 处理2, 值3, 处理3, ..., 公共处理)：如果字段值等于某个值，则执行对应的处理；如果都没对应的值，则执行公共处理
6. 分组查询
--分组查询
--关键字：group by 分组字段名,分组字段名...
--注意1：使用了分组后，在 select 语句中只允许出现分组字段和多行函数
--注意2：如果是多字段分组，则先按照第一字段分组，然后每个组继续按照第二个字段继续分组，以此类推
--注意3：在 where 中不能出现多行函数
--查询各工作岗位的人数
select job,count(*) from emp group by job;
--查询不同部门的不同工作岗位的人数
select deptno,job,count(*) from emp group by deptno,job order by deptno;
--分组筛选
--关键字：having
--作用：对分组后的数据筛选，允许使用多行函数
--注意：having 关键词必须和分组结合使用，不允许单独使用
--查询不同部门的不同工作岗位的并且人数大于1的信息
select deptno,job,count(*) from emp group by deptno,job having count(*)>1 order by deptno;
错误写法，在 where 中不能出现多行函数：
select deptno,job,count(*) from emp where count(*)>1 group by deptno,job order by deptno;
--where 和 having 的比较
--在 where 中不能出现多行函数，但 having 可以
--都可以使用普通字段直接进行筛选，但是 where 的执行效率比 having 的高
--where 执行顺序：from--> where--> group by--> select--> order by
--having 执行顺序：from--> group by--> select--> having--> order by
--结论：在分组语句中，使用 where 进行字段级别的筛选，使用 having 进行多行函数的筛选
select deptno,job,count(*) from emp group by deptno,job having deptno>1 order by deptno;
select deptno,job,count(*) from emp where deptno>1 group by deptno,job order by deptno;
7. 多表联合查询
--多表联合查询
--当需要获取的数据分布在多张表中的情况使用多表联合查询
--SQL92 方式
--笛卡尔积：多表的笛卡尔积,结果的数量为所有表的数量的乘积
select * from emp,dept;
--等值连接筛选
--概念：先做表的笛卡尔积，然后筛选，筛选条件为等值筛选
--注意：条件为字段的值相同来进行筛选，字段名可以不同
select * from emp e,dept d where e.deptno=d.deptno;
--不等值连接
select * from emp e,salary s where e.sal>=s.lowsal and e.sal<=s.hightsal;
...................................................................................
--SQL99 方式
--笛卡尔积：使用 cross join 关键字
--语法：select 内容 from 表名 cross join 表名;
select * from emp cross join dept;
--筛选
--自然链接：关键字 natural join
--语法：select 内容 from 表名 natural join 表名;
--特点：底层先笛卡尔积，然后对所有同名同值字段自动进行筛选，无须添加连接条件，并且在结果中消除重复的属性列
select * from emp natural join dept;
--内链接：关键字 inner join，可省略 inner，
--问题1：如果只想对部分同名同值字段进行筛选怎么办？
--解决1：使用 using 关键字
--作用：使用指定的字段对联合查询的结果进行等值筛选
--注意：指定的字段必须是两表的同名同值字段
--语法：select 内容 from 表名 inner join 表名 using(字段名,字段名,...);
select * from emp inner join dept using(deptno);
--问题2：如果想对不同字段名但同值的字段进行筛选怎么办？
--解决2：使用 on 关键字进行自定义连接条件筛选(等值筛选，不等值筛选)
--注意：普通筛选条件使用 where 进行筛选，不要使用 on 。好处：SQL 的可读性变强
select * from emp inner join dept on emp.deptno=dept.no where sal>2000;
--外连接
--左外连接：left outer join
--特点：左边表保留未符合条件的记录，右边表对应属性列的值设为 null
--查询员工姓名，工作，薪资，部门名称及没有部门的员工信息
select * from emp e left outer join dept d on e.deptno=d.deptno;
--右外连接：right outer join
--特点：右边表保留未符合条件的记录，左边表对应属性列的值设为 null
--查询员工姓名，工作，薪资，部门名称及没有员工的部门信息
select * from emp e right outer join dept d on e.deptno=d.deptno;
--全外连接：full outer join
--特点：结果集 = 左外连接和右外连接的并集
select * from emp e full outer join dept d on e.deptno=d.deptno;
...................................................................................
--三表联合查询
--SQL92 实现
--查询员工信息及部门名称及所在城市名称并且员工的工资大于2000或者有奖金
select * from emp e,dept d,city c
where (e.deptno=d.deptno and d.loc=c.cid and e.sal>2000) or (e.deptno=d.deptno and d.loc=c.cid and e.comm is not null);
order by e.sal;
--SQL99 实现
select * from emp e
inner join dept d
on e.deptno=d.deptno
inner join city c
on d.loc=c.cid
where e.sal>2000 or e.comm is not null
order by e.sal;
8. 子查询
--子查询学习
--使用时机：当查询的筛选条件依赖于另一条查询结果时，考虑使用子查询
--单行子查询
--查询所有比雇员"CLARK"工资高的员工信息
select * from emp where sal>(select sal from emp where ename='CLARK');
--多行子查询
--关键字：any
--查询工资高于任意一个"攻城狮"岗位薪资的员工信息
select * from emp where sal> any (select sal from emp where job='攻城狮');
--关键字：all
--查询工资高于所有SUN的员工信息
select * from emp where sal> all (select sal from emp where ename='SUN');
--关键字：in 表任意存在；not in 表不存在
--查询部门 20 中和部门 10 中的岗位一样的雇员信息
select * from emp where job in (select job from emp where deptno=10) and deptno=20;
9. Oracle 序列 & 索引 & 视图
--Oracle 的序列学习
--创建序列
--语法：create sequence 序列名;
--特点1：默认开始是没有值的，也就是指针指在了没有值的位置
--特点2：序列名.nextval 每次执行都会自增一次，默认步长为1
--特点3：序列名.currval 查看当前序列的值，开始是没有的
--作用：作为主键使用，动态的获取主键值，极大的避免了主键冲突
--创建默认序列
create sequence cc;
select cc.currval from dual;
select cc.nextval from dual;
--创建自定义序列
create sequence aa;
start with 5; --设置开始位置
increment by 2; --设置步长
--删除序列
--语法：drop sequence 序列名;
drop sequence cc;
...................................................................................
--Oracle 的索引学习
--作用：提高查询效率
--创建索引
--语法：create index 索引名 on 表名(字段名);
--特点：显示的创建，隐式执行
--注意：Oracle 会自动给表的主键创建索引
create index index_student_sno on student(sno);
--删除索引
--语法：drop index 索引名;
drop index index_student_sno;
...................................................................................
--Oracle 的视图学习
--创建视图
--语法：create view 视图名 as select 对外提供的字段名,字段名,... from 真实表名;
--特点1：保护真实表，隐藏重要字段的数据，保护数据
--特点2：在视图中的操作会映射执行到真实表中
--特点3：可手动开启只读模式:with read only
create view stu as select sno,sname,sphone from student with read only;
--注意：视图的创建必须拥有 dba 权限
--删除视图
--语法：drop view 视图名;
drop view stu;
10. Oracle 的分页查询
--Oracle 的分页查询
--当表中的数量特别大的时候，如果一次性全部显示给用户，体验极差，需要用分页查询
--使用
--关键字：rownum，Oracle 对外提供自动给查询结果编号的关键字，与每行的数据无关
--注意：rownum 只能使用小于号
--查询学生信息的第一页数据，每页最多5行数据
select rownum, s.* from student s where rownum<=5;
--查询学生信息的第二页数据
select * from (select rownum r, s.* from student s where rownum<=10) where r>5;
--查询学生信息的第三页数据
select * from (select rownum r, s.* from student s where rownum<=15) where r>10;
--总结: 查询学生信息的第 n 页数据,每页有 m 行数据
select * from (select rownum r, s.* from student s where rownum<=m*n) where r>m*(n-1);
11. 其它
--创建用户表
create table users(
usid NUMBER(10) primary key,
uname VARCHAR2(20) not null,
upassword VARCHAR2(20) not null,
registerdate Date not null,
ubirth DATE,
usex VARCHAR2(4),
uage NUMBER(3),
constraints ck_users_usex check(usex='男' or usex='女')
);
--创建微博表
create table blogs(
bid NUMBER(10) primary key,
usid NUMBER(10) not null,
publisheddate DATE not null,
btitle VARCHAR2(20) not null,
bcontent CLOB not null,
constraints fk_blog_uid foreign key(usid) references users(usid)
);
--创建好友表
create table friends(
fid NUMBER(10) not null,
usid NUMBER(10) not null,
adddate DATE not null,
remark VARCHAR2(20),
constraints fk_friends_usid foreign key(usid) references users(usid),
constraints fk_friends_fid foreign key(fid) references users(usid)
);
--创建评论表
create table comments(
cid NUMBER(10) primary key,
usid NUMBER(10) not null,
bid NUMBER(10) not null,
comdate DATE not null,
comcontent CLOB not null,
constraints fk_comments_usid foreign key(usid) references users(usid),
constraints fk_comments_bid foreign key(bid) references blogs(bid)
);
--创建留言表
create table messages(
mid NUMBER(10) primary key,
usid NUMBER(10) not null,
fid NUMBER(10) not null,
messdate DATE not null,
messcontent CLOB not null,
constraints fk_messages_usid foreign key(usid) references users(usid),
constraints fk_messages_fid foreign key(fid) references users(usid)
);
--创建照片表
create table photos(
pid NUMBER(10) primary key,
usid NUMBER(10) not null,
upldate DATE not null,
pname VARCHAR2(20) not null,
address VARCHAR2(30) not null,
describ VARCHAR2(60),
constraints fk_photos_usid foreign key(usid) references users(usid)
);
-------------------------------------------------------------------------------------------------------------------------
--创建用户序列
create sequence users_id start with 1 increment by 1;
--新增用户存储过程
create or replace procedure addUsers
(
v_usname VARCHAR2,
v_upassword VARCHAR2,
v_ubirth DATE,
v_usex VARCHAR2
)
is
v_exister users%rowtype;
v_uage users.uage%type := to_char(sysdate,'yyyy') - to_char(v_ubirth,'yyyy');
begin
select * into v_exister from users where uname=v_usname;
if(v_exister.usid>0) then
dbms_output.put_line('该用户名已存在,请重新输入!');
end if;
exception
when no_data_found then
insert into users values(users_id.nextval, v_usname, v_upassword, sysdate, v_ubirth, v_usex, v_uage);
commit;
dbms_output.put_line('注册成功!');
when others then
dbms_output.put_line('error');
end;
--执行新增用户存储过程
declare
v_usname users.uname%type := '张三';
v_upassword users.upassword%type := 'a123456';
v_ubirth users.ubirth%type := to_date('1995-09-01','YYYY-MM-DD');
v_usex users.usex%type := '男';
begin
addUsers(v_usname, v_upassword, v_ubirth, v_usex);
end;
select * from users;
drop table users;
drop sequence users_id;
---------------------------------------------------------------------------------------------------------------------------
--添加好友存储过程
create or replace procedure addfriend
(
v_ffid NUMBER,
v_userid NUMBER,
v_remark in out VARCHAR2
)
is
v_exister friends%rowtype;
v_fd friends%rowtype;
v_usname users.uname%type;
begin
select * into v_exister from friends where fid=v_ffid and usid=v_userid;
if(v_exister.fid>0) then
dbms_output.put_line('已是好友,不能重复添加!');
end if;
exception
when no_data_found then
if(length(v_remark)=0) then
select uname into v_remark from users where usid=v_ffid;
end if;
insert into friends values(v_ffid, v_userid, sysdate, v_remark);
select uname into v_usname from users where usid=v_userid;
insert into friends values(v_userid, v_ffid, sysdate, v_usname);
commit;
dbms_output.put_line('添加好友成功!');
when others then
dbms_output.put_line('添加好友失败!');
end;
--执行添加好友存储过程
declare
v_fid NUMBER := 1;
v_userid NUMBER := 2;
v_remark friends.remark%type :='好友';
begin
addfriend(v_fid, v_userid, v_remark);
end;
select * from users;
select * from friends;
--------------------------------------------------------------------------------------------------------------------
--创建留言序列
create sequence messages_id start with 1 increment by 1;
--好友留言存储过程
create or replace procedure leaveMessage
(
v_friendid users.usid%type,
v_userid users.usid%type,
v_messcontent messages.messcontent%type
)
is
v_exister friends%rowtype;
v_clength number := length(v_messcontent);
begin
select * into v_exister from friends where usid=v_userid and fid=v_friendid;
if(v_exister.fid>0 and v_clength>0) then
insert into messages values(messages_id.nextval, v_friendid, v_userid, sysdate, v_messcontent);
commit;
dbms_output.put_line('留言成功!');
else
dbms_output.put_line('留言内容不能为空!');
end if;
exception
when no_data_found then
dbms_output.put_line('还不是好友,请添加后进行留言!');
when others then
dbms_output.put_line('error');
end;
--执行好友留言存储过程
declare
v_friendid users.usid%type := 1;
v_userid users.usid%type := 2;
v_messcontent messages.messcontent%type := '今天天气真好,很适合学习!';
begin
leaveMessage(v_friendid, v_userid, v_messcontent);
end;
select * from users;
select * from friends;
select * from messages;
drop table friends;
View Code