1.1 Oracle基本配置
1.1.1 Oracle安装与启动
Oracle安装:用户种类及初始密码
在oracle10g\11g中默认scott被锁定。
Oracle数据库的启动
a) 启动两个服务**Listener/**Service
b) “开始-》运行”-》sqlplus或 sqlplusw
1.2 Oracle用户管理
1.2.1 基本命令
用户连接、解锁、锁定
1 | c) conn/connect scott/tiger; system/orcl; sys/orcl as sysdba; |
用户的查找
1 | e) 显示当前的用户:show user; |
创建新用户
1 | g) 创建用户密码:SQL> create user xxx identified by 密码 default tablespace xx; |
i) 给用户授系统权限:
i. 授予连接数据库权限:
1 | a) SQL> grant create session to test; |
ii. 授予创建数据库表权限:
1 | a) SQL> grant create table to test; |
j) 给用户授对象权限:
i. 授予查询表修改等的权限
1 | a) SQL> grant select on dept to test; |
k) 给用户授角色:
1 | i. SQL>grant connect,resource to test; |
l) 收回权限
1 | i. SQL> revoke select on dept from test; |
删除用户
1 | m) SQL> drop user test; |
1.2.2 实例
- 创建用户:必需以dba的身份才能创建用户,否则会提示权限不足
a) 例:以scott的身份创建用户
b) 例:使用system来创建用户
- 一般以dba的身份去删除某个用户,如果用其他用户去删除用户,则需要有drop user 的权限。在删除用户时注意,如果要删除的用户已经创建了表,那么就需要在删除时带一个参数cascade。
a) 例1,使用scott删除本身
- 给用户创建权限,我们创建的用户刚开始是什么权限都没有,登录都不可以
a) 使用刚刚创建的chenzhou来登录
提示缺少权限,登录被取消
b) Oracle中权限的分类
i. 系统权限:用户对数据库的相关权限
ii. 对象权限:用户对数据库的数据对象操作的权限(select,insert,update,delete,all,create index……)
c) Oracle中角色的分类
i. 预定义角色
ii. 自定义角色
d) 给用户赋予connect角色
再登录:
e) 给用户赋予resource的角色,用户有了该角色就能够自己建表
f) 用户自己建表
g) 如何使用chenzhou用户来访问scott的emp表
i. 首先给用户赋权
ii. 赋权之后使用该用户来查询scott中的emp表,结果如下:
iii. 如果要赋予该用户对该表修改权限,则grant update……
iv. 如果想把对该表的所有权限够赋给该用户则 grant all on emp to chenzhou
- 给用户赋权使用grant,收回权限使用revoke
a) 例子:收回chenzhou用户对emp表的查询权限
再次查询scott中的emp表时:
- 对权限进行维护
a) 希望chenzhou用户可以去查询scott中的emp表,还希望他可以把这个权限交给别的对象
i. 如果是对象权限,就加入with grant option
例:grant select on emp to chenzhou with grant option
ii. 如果是系统权限,就加入with admin option
iii. 注:如果A对象把权限赋给B,然后B再赋给C,当A把B的权限revoke后,C拥有的权限也会被回收掉。
- 使用profile管理用户口令
a) 账户锁定
i. 概述:指定该账户登陆时最多可以输入密码的次数,也可以指定用户锁定的时间(天),一般用dba的身份去执行该命令
ii. 例子:指定scott这个用户最多只能尝试3次登陆,锁定时间为2天
1 | create profile lock_account limit failed_login_attempts 3 password_lock_time 2; |
b) 给账户解锁(dba操作)
1 | i. alter user scott account unlock; |
c) 终止口令
i. 概述:为了让用户定期修改密码可以使用终止口令的指令来完成(dba操作)
ii. 例子:给chenzhou用户创建一个profile文件,要求该用户每隔10天要修改自家的登录密码,宽限期为2天。
1 | iii. create profile profilename limit password_life_time 10 password_grace_time 2; |
d) 口令历史
i. 概述:如果希望用户在修改密码时,不能使用以前使用过的密码,可以使用口令历史,这样oracle就会将口令修改的信息存放到数据字典中,这样当用户修改密码时,oracle就会对新旧密码进行比较,当发现新旧密码一样时,就提示用户重新输入密码。
ii. 例子:
- 建立profile
1
2
3create profile password_history limit password_life_time 10 password_grace_time 3 password_reuse_time 10;
psssword_reuse_time //指定口令可以重用时间,即10天后就可以重用 - 分配给某个用户:
e) 删除profile
i. 概述:当不需要某个profile文件时,可以删除该文件。
1 | ii. drop profile password_history[cascade] |
1.3 基本数据类型及其sql语法
1.3.1 表管理
o) 查询数据库中有哪些表:select * from tab;
p) 查询某张表的结构:desc 表名;
1 | q) create table users( |
r) 添加字段
1 | alter table users add(pubdate date); |
s) 修改字段
1 | alter table users modify(userName varchar2(20)); |
t) 删除字段
1 | alter table users drop(password); |
u) 给表添加约束
1 | alter table users add constraint ck check(age>10 and age<60) |
v) 重新命名
1 | rename users to test;//将users表重新命名为test |
w) 注意:
mysql中一条INSERT语句插入批量数据的写法:INSERT INTO 表名 VALUES ([列值],[列值])), ([列值],[列值])), ([列值],[列值])); Oracle中不支持这种写法,不过可以采用下面 的方式:
INSERT ALL INTO a表 VALUES(各个值1) INTO a表 VALUES (其它值2) INTO a表 VALUES(其它值3) SELECT * FROM b表,如果SELECT * FROM b表会查询出n条数据的话,就会先往a表插入值1对应的各个字段插入n条记录,然后插入值2各个对应的字段n条记录,然后插入值3对应的各个字段n条记录。注意后边跟的SELECT语句可以随意,不过不是把它SELECT出来的内容插入前边的表里,而是起到前边的多个数据每次插入多少行的作用,这个多少行是和后边跟的SELECT语句查出来几条而定的
1.3.2 数据字典
维护系统对象的一套特殊表和视图
user_xxx 用户拥有的 all_xx 用户有权查看 dba_xxx(sys) 所有的信息
1、 查看所有数据字典对象的名称和用途
1 | select * from dictionary; |
2、 查看oracle数据库中所有用户
1 | select username from dba_users; |
3、 查看scott用户下所有的表
1 | select table_name from user_tables; |
4、 查看scott用户所有的视图
1 | select * from user_views |
5、查询scott用户有权查看的表
1 | select table_name,owner from all_tables; |
4、 创建表,讲解数据类型
1 | Number(m,n) char(n) varchar2(n) date |
a>创建新表
b>利用现有的表创建表
1 | create table test2 as select * from usersinfo; |
5、事务控制语言(TCL)可以放SQL后面
1 | Insert into dept values(11,null,null); |
1 | Select distinct * from emp where job=’CLERK’; |
1 | Select (empno||’,’||ename||’,’||job) as OUT_PUT from emp; |
1.4 基本SQL查询
1.4.1 简单查询
1 、select 简单查询
1 | select sid,sname from t_student |
–2班 女生
1 | select * from t_student where sclass = 2 and SSEX='f' |
–查询所有班级
1 | select sclass from t_student |
2、 select distinct 查询
1 | select distinct sclass from t_student |
3、 给列或表达式取别名
1 | select * from t_student; |
–从学生表中检索出2班的性别为女性的学生的信息
1 | select * from t_student where SCLASS=2 and SSEX='f'; |
–从学生表中检索出所有班级
1 | select SCLASS from t_student; |
–学号为‘10001’的学生参加了哪些课程的考试
1 | select CID from t_score where SID='10001' |
–给学员编号为10002的学生的各门课成绩进行升序和降序的排列
1 | select SCORE from t_score where SID='10002' order by SCORE asc |
–查询所有姓张的学生信息
1 | select * from t_student where SNAME like '张%'; |
–利用已有的t_student表生成新表
1 | create table t_student_bak1 as select * from t_student |
1.4.2 连接查询和子查询—重点
1、查询员工以及他所在的部门
1 | select a.*,b.* from emp a,dept b where a.deptno=b.deptno----(内连接) |
2、查询所有部门以及员工姓名
1 | select a.ename,b.deptno,b.dname from emp a,dept b where a.deptno(+)=b.deptno |
或者
1 | select a.ename,b.deptno,b.dname from dept b left outer join emp a on b.deptno=a.deptno---(左外连接) |
3、显示员工以及其直接上级
1 | select a.ename 员工,b.ename 经理 from emp a,emp b where a.mgr=b.empno(+) ----(自连接) |
4、查询工资高于平均工资的员工
1 | select * from emp where sal>(select avg(sal) from emp) ------(非关联子查询) |
先执行子查询,后执行主查询,叫做非关联子查询
5、查询每个部门最高工资的员工
1 | select * from emp a where (select count(*) from emp where deptno=a.deptno and sal>a.sal)=0 ---(关联子查询) |
先执行主查询,后执行子查询,将主查询的当作已经查询出来的结果
也可非关联实现
1 | select * from emp where (deptno,sal) in (select deptno,min(sal) from emp group by deptno) |
实例:
–内连接(join on/inner join on):查询学生总体学习情况:学生姓名,课程名,成绩
1 | select SNAME,CNAME,SCORE from t_student a |
–查询及格的学生的学习情况:学生姓名,课程名,成绩
1 | select SNAME,CNAME,SCORE from t_student a |
–查询有课程的教师的信息
1 | select * from t_teacher |
–查询所有教师的工作分配情况,有课程的和没课程的老师都要出现在查询结果中(左外连接)
1 | select * from t_teacher --主表 |
–全联接(full join 结果集中除了满足联接条件的记录外,还有左、右表中不满足条件的记录) 左连接和外连接的一个组合,先执行左连接,再执行右连接,删掉重复记录
1 | select * from t_teacher |
–学生和课程有多少可能的组合————
–交叉连接(cross join )————-
1 | select * from t_student |
———-子查询———-
–查询比张老师年龄大的教师信息
1 | select * from t_teacher where tage > (select tage from t_teacher where TNAME='张老师') |
–查询参加过课程编号为1的考试的学生信息
1 | select * from t_student where sid = (select sid from t_score where cid=1)---失败 |
–查询没有参加过课程编号为1的考试的学生信息
1 | select * from t_student where sid not in (select sid from t_score where cid=1) |
–查询所有已经安排教师上课的课程信息
1 | select * from t_course tc where cid in (select cid from t_teachercourse ) |
–查询所有没安排教师上课的课程信息
1 | select * from t_course tc where not exists (select * from t_teachercourse ttc where ttc.cid = tc.cid) |
–在成绩表中查询出所有学生的最高平均分(from后面的子查询)
1 | select max(avgscore) from (select avg(SCORE) as avgscore from t_score group by sid) |
–经典应用分页查询(rownum)
–查询成绩表的前5条记录
1 | select * from t_score where rownum>=1 and rownum<=5 |
–查询成绩表的6-10条记录
1 | select * from t_score where rownum>=6 and rownum<=10--错误,没有数据 |
1.4.3 层次查询
1、显示员工领导关系
1 | select lpad(ename,5*level,'+') from emp connect by prior empno=mgr start with ename='KING' |
或者start with mgr is null
connect by 用于执行记录之间的父子关系,start with 用于指定从哪个节点记录开始遍历访问 Level 在整个查询记录中的层次
2、查询BLAKE所领导团队工资总额
1 | select sum(sal) from emp connect by prior empno=mgr start with ename='BLAKE' |
1.5 操作符及其SQL函数
1.5.1 操作符
–算术操作符(加(+)、减(-)、乘(*)、除(/)):检索出课程号是’2’的成绩+10分后的结果
1 | select sid,cid,score+10 as "lastScore" from t_score where cid=2 |
———-逻辑操作符:and or not
———-检索班级是1班或2班的学生信息
1 | select * from t_student where SCLASS=1 or SCLASS=2 |
———-检索班级是1班的80后学生信息
1 | select * from t_student where SCLASS=1 and SBIRTHDAY between '1-1月-1980' and '31-12月-1989' |
比较操作符(包括 =、<>、<、>、<=、>=、BETWEEN…AND、IN、LIKE 和 IS NULL):
———-检索班级是1班或2班的学生信息
1 | select * from t_student where SCLASS in(1,2) |
–查询 1班的 80 后的学生信息
1 | select * from t_student where SCLASS=1 and SBIRTHDAY between '1-1月-1980' and '31-12月-1989' |
———-检索班级不是1班的80后学生信息
1 | select * from t_student where SCLASS<>1 and SBIRTHDAY between '1-1月-1980' and '31-12月-1989' |
– 检索1986 年出生的学生信息
1 | select * from t_student where SBIRTHDAY between '1-1月-1986' and '31-12月-1986' |
– 检索1980 年以前出生的学生信息
1 | select * from t_student where SBIRTHDAY < '1-1月-1980' |
–连接运算符:用于将多个字符串或数据值合并成一个字符串
–查询学生信息(把学号和姓名合并成一个列)
1 | select (sid || sname) as "学生信息" from t_student |
——集合操作符:将两个查询的结果组合成一个结果 ppt9
——-union、union all、intersect、minus
——统计学习操作系统(1)或数据结构(2)的同学学号(union:返回两个查询的不重复的所有行)
1 | select sid from t_score where CID='1' |
——-统计java web(5) 和java框架(6)都为及格的同学学号
——-intersect:相当于对2个查询结果集取交集,也就是只返回两个查询结果集的公共行
1 | select sid from t_score where cid='5' and score >= 60 |
–统计操作系统(1)70 分及以上但数据结构(2)未达到 65 的同学学号 –10004
1 | select sid from t_score where cid='1' and score > 70 |
1.5.2 集合运算
A集合 (1、2、3)
B集合 (2、3、4)
交集:(2、3)
并集:(1、2、3、2、3、4)或(1、2、3、4)
差集:A-B(1) B-A(4)
1 | create table a(id number);//插入1,2,3 |
1、 交集
1 | select * from a intersect select * from b |
2、并集
1 | select * from a union all select * from b(去掉重复的记录则去掉all) |
3、差集
1 | select * from a minus select * from b (A-B) |
1、查询部门10和部门20都有的工作类型
1 | select job from emp where deptno=10 intersect select job from emp where deptno=20; |
2、查询部门10的办事员和部门20的经理
1 | select * from emp where deptno=10 and job='CLERK' union all select * from emp where deptno=20 and job='MANAGER' |
3、查询部门30中有,而部门10中没有的工作类型
1 | select job from emp where deptno=30 minus select job from emp where deptno=10; |
1.5.3 常用函数
1 、聚合函数
count() sum() avg() max() min()
实例:
————————————–分组聚合查询——————————————–
–Min :最小值
–Max :最大值
–Sum :求和
–Avg :求平均值
–Count:计数
–查询课程表中最多和最少的课时数
1 | select MAX(CHOURS) as 最多的课时数,MIN(CHOURS)as 最少的课时数 from t_course |
–查询当前的学生数
1 | select count(*) from t_student |
–查询成绩表中每位同学的最高分,最低分,总分,平均分
1 | select sid,max(SCORE),min(SCORE),sum(SCORE),avg(SCORE) from t_score group by sid |
–统计平均成绩超过75分的学生的最高分,最低分,总分,平均分
1 | select sid,max(SCORE),min(SCORE),sum(SCORE),avg(SCORE) from t_score group by sid having avg(SCORE) >75 |
–group by :select后面的这些字段,要么是在聚合函数里面,要么是在group by字句中,否则回报错
1 | select sid,max(SCORE),min(SCORE),sum(SCORE),avg(SCORE) from t_score group by sid having avg(SCORE) >75 |
2 、字符函数 (参考PPT演示)
ltrim() 左侧截取
rtrim() 右侧截取
translate(‘jack’,’a’ ,’b’) 结果back
lpad(char1,n[,char2]) 使用char2字符补充在char1字符的左边,最终补足n个字符,如果没有char2字符,则用空格补足n个字符
1 | length () select length(‘abcdefg’) from dual |
实例:
———-字符函数
1 | select lower('FUN') from dual;--转换为小写 |
3 、数学函数
round[number,[decimal_places]] 四舍五入函数,将number按照指定小数位数进行四舍五入运算的结果
1 | select round(3.567) from dual;四舍五入取整 |
4 、转换函数
to_char(d[,fmt]) 也可number转换 将date数据类型的d转换成字符串类型数据
to_date(字符串,格式)
其他函数
nvl(a,b) 如果a不为null 则返回a,如果a为null则返回b,注意两者的类型要一致
nvl2(a,b,c) ,如果a不为null 则返回b,如果a为null则返回c; b和c类型不同的话,c会转换为b的类型
NULLIF (expr1, expr2) ->相等返回NULL,不等返回expr1
COALESCE (expr1, expr2, …, exprn) ->返回第一个不为NULL的表达式,各个表达式类型一致
1.5.4 日期函数
系统时间sysdate
add_months(d,no_of_month) 为日期d加上no_of_month月份
1 | select add_months(sysdate,2) from dual; |
months_between(d1,d2)返回日期d1和d2之间的月份数
例子:当前时间跟当前时间加上两个月的差额
last_day(d)d所在月份最后一天
例子:查询当前时间所在月份最后一天日期
to_date(‘字符串’,’日期格式化’)
1 | 例子:select to_date(‘20100302’,’yyyymmdd’) from dual; |
next_day(d,day) 返回d后工作日日期 day取值范围 1—7(周日、周一、周二……周六)
1 | select next_day(sysdate,3) from dual; 返回下周二 |
例子:
1、查询系统时间(sysdate)
1 | select sysdate from dual |
2、查询在12年前参加工作的员工(months_between(d1,d2)返回日期d1和d2之间的月份数)
1 | select empno,ename,hiredate from emp where months_between(sysdate,hiredate)>144 |
3、查询在当月倒数第三天参加工作的员工(last_day(d)d所在月份最后一天)
1 | select empno,ename,hiredate from emp where last_day(hiredate)-2=hiredate |
4、查询每个员工的工作天数(trunc(n[,m])返回截尾取整到小数点后m位的数)
1 | select empno,ename,hiredate,trunc(sysdate-hiredate) from emp |
5、显示系统时间为xxxx年xx月xx日,是一年中第几天(DDD),是星期几(DAY)(to_char(d,str)将d显示为str指定的格式)
1 | select to_char(sysdate,'yyyy"年"月"dd"日"DDD DAY') from dual |
6、计算每个员工已经工作了多少个月,忽略小数部分(months_between)
1 | select ename,trunc(months_between(sysdate,hiredate) from emp; |
7、查询在1987年2月到1987年5月参加工作的员工(to_date(str,formatstr)将str格式字符串日期转换成formatetr指定的格式日期)
1 | select ename,hiredate from emp where hiredate>=to_date('19870201','yyyymmdd') and |
实例:
———-日期函数
1 | select sysdate from dual |
–to_char
1.5.5 统计函数
1、统计部门最低工资大于900的部门和最低工资
1 | select deptno,min(sal) from emp group by deptno having min(sal)>900 |
2、统计每个部门工资在1400元以上的所有员工的工资总额
1 | select deptno,sum(sal) from emp where sal>1400 group by deptno |
3、统计不同工作的个数
1 | select count(distinct job) from emp |
1.6 数据库对象
1.6.1 序列
1、 创建
1 | create sequence seqa start with 1000 increment by 2; |
2、 得到序列自增的数字
1 | select seqa.CURRVAL from dual;(得到当前序列数值) |
3、 插入某表
1 | insert into test values(seqa.NEXTVAL,’aa’); |
要插入开始是字母的可以
1 | insert into test values(‘c’||trim(to_char(seqa.nextval,’0000’)),’aa’); |
4、 删除序列
1 | drop sequence seqa; |
1.6.2 伪列
rowId,rownum伪列
完成分页
1.7 PL/SQL
1 | declare |
1.8 游标的使用
1 、游标:
%notfound 没有找到数据为真,找到为假
%found 相反
%rowcount 统计影响的行数
2 、显示游标 sql;
更新指定员工工资加500
1 | declare |
3 、隐式游标
游标声明、打开、检索、关闭
1 | 声明 |
例如
每个部门平均工资
1 | declare |
显示指定工作的员工姓名和工资
1 | declare |
1.9 触发器
触发器
1 | tnew insert udate |
级联删除
1 | create or replace trigger tri_1 |
级联更新
1 | create or replace trigger tri_update |
函数
1 | create {or replace} function 函数名{(形参)} |
例子
1 | create or replace function fun |
求工资税函数
1 | create or replace function tax(money number) |
1.10 存储过程
存储过程
1 | create (or replace) procedure 存储过程名(形参) |
用存储过程实现某个工作地点的员工姓名及工资
1 | create or replace procedure p_emp_dept(loca varchar2) |