Oracle数据库经典教程复习

Oracle经典教程复习：

一、创建新用户名的步骤:

先普通用户登录scott/tiger,后conn sys/sys as sysdba;

create user 用户名 identified by 密码; ---创建用户名和密码 alter user 用户名 identified by 新密码;---修改用户的密码

alter user 用户名 account lock|unlock; ---修改用户处于锁定(非锁定)状态

grant resource,connect,create table to 用户名; ---授权资源、连接、创建表的权限给用户名 revoke resource,connect,create table from 用户名;--取消资源、连接、创建表权限 conn 用户名/密码; ---连接用户名 再开始创建表的一些信息 如:

create user student identified by student; grant resource,connect,create table to student; revoke resource,connect,create table from student; conn student/student; 二、创建表和约束

创建约束命令:alter table 表名 add constraint 约束名 约束内容 --创建一个学生信息表infos和约束 create table infos

(

stuid varchar2(7) not null, --学号 学号='s'+班号+2位序号 stuname varchar2(10) not null, --姓名 gender varchar2(2) not null, --性别 age number(2) not null, --年龄 seat number(2) not null, --座号

enrolldate Date, --入学时间

stuaddress varchar2(50) default '地址不详', --住址

classno varchar2(4) not null --班号 班号=学期序号+班级序号 )

/ --在oracle代码中，\执行缓存区中的语句，由于缓冲区中只存储一条刚刚保存过的语句，每条语句后没有分号结尾，只是保存在缓冲区，因此每条语句后面有\ alter table infos add constraint pk_infos primary key(stuid) --主键约束

/

alter table infos add constraint ck_infos_gender check(gender = '男' or gender = '女') --check约束 /

alter table infos add constraint ck_infos_seat check(seat >=0 and seat <=50) /

alter table infos add constraint ck_infos_age check(age >=0 and age<=100) /

alter table infos add constraint ck_infos_classno check((classno >='1001' and classno<='1999') or (classno >='2001' and classno<='2999'))

/

alter table infos add constraints un_stuname unique(stuname) --唯一约束，其列值是唯一的 /

--创建一个成绩表scores和约束 create table scores (

id number , --ID

term varchar2(2), --学期 S1或S2 stuid varchar2(7) not null, --学号

examno varchar2(7) not null, --考号 E+班号+序号 writtenscore number(4,1) not null, --笔试成绩 labscore number(4,1) not null --机试成绩 )

alter table scores add constraint ck_scores_term check(term = 's1' or term ='s2')

/

alter table scores add constraint fk_scores_infos_stuid foreign key(stuid) references infos(stuid) --外键约束，成绩的学号与学生学号一致 /

--数据插入

insert into 表名(列名1，列名2......) values(值1，值2......); --其中列名可省略，只是值要按照表中列的顺序来写，数据类型也要一致

insert into infos values ('s100102','林冲', '男', 22, 2, to_date('2009-8-9 06:30:10',' yyyy-mm-dd hh24:mi:ss '), '西安', '1001');

insert into infos values ('s100104','阮小二','男',26,3,sysdate,default,'1001'); --sysdate是指当前时间，default是指默认值

--其中日期的输入要使用to_date和to_char：

转换函数:将值从一种数据类型转换为另外一种数据类型，常用的转换函数有to_char(日期/数字,格式)和to_date(字符串，格式);

to_date对日期格式化后输入，采用格式化字符串对日期进行格式化时，格式化字符串中字符不区分大小写，yyyy表年份，mm表月份，dd表日期，hh24表小时0-23，mi表分钟，ss表秒 to_date 是把字符串转换为数据库中得日期类型,如:to_date('字符串','格式') to_date('2009-8-9 06:30:10','yyyy-mm-dd hh24:mi:ss')

select to_date('2012-02-05 23：11：59','yyyy-mm-dd hh24:mi:ss')from dual 结果:

TO_DATE('2012- -------------- 05-2月 -12

to_char是把日期或数字转换为字符串,如:to_char(日期/数字,'格式') to_char(日期,'yyyy-mm-dd hh24:mi:ss')

select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') as today from dual 结果: TODAY

-------------------

2012-04-16 19:47:21

--从一个表中向另一个表中insert插入一个结果集

insert into 表名2 select * from 表名1; ---表名2和表名1要表的结构一致才行 insert into infos2 select * from infos;

--向表中插入insert一个常量结果集

insert into 表名 select 表中列信息 from dual;

insert into infos select 's100106','华安','男',24,5,sysdate,'上海浦东','1002' from dual; --数据更新 语法结构:

update 表名 set 列名1=值，列名2=值.......where 条件;

update infos set classno='1002',stuaddress='江西南昌' where stuname='阮小二';

update infos set classno='1003',stuaddress='上海浦西',age=22where stuname='华安';

--数据删除

delete from 表名 where 条件;

delete from infos where stuid='s100103';

delete from infos where age=26; --删除了阮小二的信息

---truncate命令(ddl命令)可把表中所有数据一次性删除

语法结构:

truncate table 表名;

truncate与delete的区别:

truncate是DDL命令，删除的数据不能恢复;delete是DML命令，删除后的数据可通过rollback等恢复; 若一表中数据记录很多，truncate相对delete速度快;

--数据查询 语法结构:

select 列名|*|表达式 from 表名 where 条件 order by 列名; ---order by要求在查询的结果中排序，默认是升序

select stuname,gender,age,stuaddress from infos where gender='男' order by age;

--根据查询的结果根据结果集中的表结构和数据形成一张新表 语法结构：根据结果集创建表

create table 表名 as select 语句;---复制表结构和数据，就是全复制 create table infos1 as select * from infos;

---若想只是复制一个表结构，那只需使查询条件不成立，如where 1=2 语法结构:复制表结构

create table infos2 as select * from infos where 1=2;

三、一些练习题

1.每名员工年终奖是2000元，在emp表中，请显示基本工资在2000元以上的员工的月工资，年总工资。

select ename,sal,(sal*12+2000) from emp where sal>2000; 结果:

ename sal (sal*12+2000) ---------- ---------- -------------

JONES 2975 37700 BLAKE 2850 36200 CLARK 2450 31400 SCOTT 3000 38000 KING 5000 62000 FORD 3000 38000

2.在emp表中，查询工资在2000元以上的姓名以及工作。

select(ename || 'is a' || job) as \ 结果:

employee details ----------------------- JONESis aMANAGER BLAKEis aMANAGER CLARKis aMANAGER SCOTTis aANALYST KINGis aPRESIDENT

FORDis aANALYST

其中用于了||连接符，用于字符串的连接。

3.NULL空值操作:空值不等于0或空格，是指未赋值、未知或不可用的值。任何数据类型的列都可包括NULL值，除非该列被定义为非空或主键。

is null表空值，is not null表不为空值。

select ename,job,sal,comm from emp where sal<2000 and comm is null; 结果:

ENAME JOB SAL COMM ---------- --------- ---------- ----------

SMITH CLERK 800 ADAMS CLERK 1100 JAMES CLERK 950 MILLER CLERK 1300

select ename,job,sal,comm from emp where sal<2000 and comm is not null 结果:

ENAME JOB SAL COMM ---------- --------- ---------- ----------

ALLEN SALESMAN 1600 300 WARD SALESMAN 1250 500

MARTIN SALESMAN 1250 1400

TURNER SALESMAN 1500 0

4.in操作:在where子名中可以使用in操作符来查询其列值在指定的列表中的行。比如: 如:查询出工作职责是salesman,president或analyst的员工。条件有两种表示方法: where job='salesman' or job='president' or job='analyst' where job in ('salesman', 'president', 'analyst')

select ename,job,sal from emp where job in ('salesman', 'president', 'analyst'); 结果:

ENAME JOB SAL ---------- --------- ----------

ALLEN SALESMAN 1600 WARD SALESMAN 1250 MARTIN SALESMAN 1250 SCOTT ANALYST 3000 KING PRESIDENT 5000 TURNER SALESMAN 1500 FORD ANALYST 3000

注意:not in与in操作一样，结果相反。如:

select ename,job,sal from emp where job not in ('salesman', 'president', 'analyst'); 结果:

ENAME JOB SAL ---------- --------- ----------

SMITH CLERK 800 JONES MANAGER 2975 BLAKE MANAGER 2850 CLARK MANAGER 2450 ADAMS CLERK 1100 JAMES CLERK 950 MILLER CLERK 1300

5.between....and....操作

在where子句中，可以使用between操作符来查询列值包含在指定区间内的行。 如:查询工资从1000到2000之间的员工的姓名、工作和工资 select ename,job,sal from emp where sal between 1000 and 2000; 或select ename,job,sal from emp where sal>1000 and sal<2000; 结果:

ENAME JOB SAL ---------- --------- ----------

ALLEN SALESMAN 1600 WARD SALESMAN 1250 MARTIN SALESMAN 1250 TURNER SALESMAN 1500

ADAMS CLERK 1100 MILLER CLERK 1300

6.like模糊查询

字符匹配操作可使用两通配符\和\%:表0或多个字符 _:表任意一个字符

如:显示员工名称以J开头以S结尾的员工的姓名、工作和工资 select ename,job,sal from emp where ename like 'J%S'; 结果:

ENAME JOB SAL ---------- --------- ----------

JONES MANAGER 2975 JAMES CLERK 950

如:显示员工工资以第二个数为5，以为0结尾的员工的姓名、工作和工资 select ename,job,sal,comm from emp where sal like '_5%0'; 结果:

ENAME JOB SAL COMM ---------- --------- ---------- ----------

TURNER SALESMAN 1500 0 JAMES CLERK 950

7.集合运算:

就是将两个或者多个结果集组合成为一个结果集。其包括 intersect交集,返回两个查询共有的记录;

union all并集，返回各个查询的所有记录，包括重复记录; union并集，返回各个查询的所有记录，不包括重复记录;

minus补集，返回第一个查询检索出的记录减去第二个查询检索出的记录之后剩余的记录。

如:查询出dept表中哪个部门下设有员工。也就是只需求出dept表中的部门号和emp表中的部门号的补集 select deptno from dept minus select deptno from emp; 结果：

DEPTNO ---------- 40

如:用union把若干条记录一次性插入到一张表中

insert into dept select 50,'公安部','台湾' from dual union select 60,'研发部','西安' from dual union select 60,'研发部','西安' from dual 结果:

DEPTNO DNAME LOC ---------- -------------- -------------

10 ACCOUNTING NEW YORK

20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON 50 公安部 台湾 60 研发部 西安 70 技术部 上海 8.连接查询:

内连接inner join、外连接outer join(又分为左外连接left join和右外连接right join)。

对两个表或若干表之间的外连接用(+)表示。(+)出现在右边指左外连接;(+)出现在左边指右外连接; 1). ①内连接:

如:请查询出工资大于2000元的员工姓名、部门、工作、工资

解析:由于部门名称在dept表中，其它信息在emp表中，需要内连接才能完成。

select e.ename,e.job,e.sal,d.dname from emp e,dept d where e.deptno=d.deptno and e.sal>2000;---普通连接 或:

select e.ename,e.job,e.sal,d.dname from emp e inner join dept d on e.deptno=d.deptno where e.sal>2000;---内连接 结果:

ENAME JOB SAL DNAME ---------- --------- ---------- --------------

CLARK MANAGER 2450 ACCOUNTING KING PRESIDENT 5000 ACCOUNTING JONES MANAGER 2975 RESEARCH FORD ANALYST 3000 RESEARCH SCOTT ANALYST 3000 RESEARCH BLAKE MANAGER 2850 SALES

②.外连接:

如：请查询出每个部门下的员工姓名、工资

解析:emp表中外键deptno引用dept表中的deptno，在dept表中如果有某些部门没有员工，那么用内连接，没有员工的部门将无法显示，因此必须以dept表为基准的外连接

select e.ename,e.job,e.sal,d.dname from emp e,dept d where e.deptno(+)=d.deptno;----右外连接 或:

select e.ename,e.job,e.sal,d.dname from emp e right join dept d on e.deptno=d.deptno; 结果:

ENAME JOB SAL DEPTNO ---------- --------- ---------- ----------

CLARK MANAGER 2450 ACCOUNTING KING PRESIDENT 5000 ACCOUNTING MILLER CLERK 1300 ACCOUNTING SMITH CLERK 800 RESEARCH ADAMS CLERK 1100 RESEARCH FORD ANALYST 3000 RESEARCH

SCOTT ANALYST 3000 RESEARCH JONES MANAGER 2975 RESEARCH ALLEN SALESMAN 1600 SALES BLAKE MANAGER 2850 SALES MARTIN SALESMAN 1250 SALES JAMES CLERK 950 SALES TURNER SALESMAN 1500 SALES WARD SALESMAN 1250 SALES

OPERATIONS 公安部 研发部 技术部 2). 外连接的练习

表achart 表bchart ID NAME ID NAME --- ---- --- ---- 6 D 1 E 1 A 2 F 2 B 3 G

3 C 5 H 7 I

左外连接:左表做驱动表，左表全显示出来，右表若匹配则显示，若不匹配则补空 select * from achart a left join bchart b on a.id=b.id; 或:

select * from achart a,bchart b where a.id=b.id(+); 结果:

ID NAME ID NAME --- ------- ------- ---- 6 D

1 A 1 E 2 B 2 F

3 C 3 G

右外连接:右表做驱动表，右表全显示出来，左表若匹配则显示，若不匹配则补空。 select * from achart a right join bchart b on a.id=b.id; 或：

select * from achart a,bchart b where a.id(+)=b.id; 结果:

ID NAME ID NAME --- ------ -------- ---- 1 A 1 E 2 B 2 F 3 C 3 G

7 I

5 H 四.子查询

子查询在select,update,delete语句内部可以出现selecty语句。子查询的类型有: 单行子查询:不向外部返回结果，或者只返回一行结果。 多行子查询:向外部返回零行、一行或多行结果。

例如1:查询出销售部SALES下面的员工姓名、工作和工资。

解析:先从dept表中查询出销售部对应的部门号，然后根据当前部门号再到emp表中查询出符合该部门的员工记录就可。内部查询的结果作为外部查询的条件。

select ename,job,sal from emp where deptno=(select deptno from dept where dname='SALES'); 结果:

ENAME JOB SAL ---------- --------- ----------

ALLEN SALESMAN 1600 WARD SALESMAN 1250 MARTIN SALESMAN 1250 BLAKE MANAGER 2850 TURNER SALESMAN 1500 JAMES CLERK 950

例如2:查询出emp表中比任意一个销售员(SALESMAN)工资低的员工姓名、工作和工资。 解析:用到any关键字，表任意的意思。any子查询

select ename,job,sal from emp where sal

ENAME JOB SAL ---------- --------- ----------

SMITH CLERK 800 JAMES CLERK 950 ADAMS CLERK 1100 WARD SALESMAN 1250 MARTIN SALESMAN 1250 MILLER CLERK 1300 TURNER SALESMAN 1500

例如3:查询出比所有销售员的工资都高的员工姓名、工作和工资。

解析:要求比所有销售员工资都高，用all关键字，表示与子查询中所有元素比较。all子查询

select ename,job,sal from emp where sal>all(select sal from emp where job='SALESMAN'); 结果:

ENAME JOB SAL ---------- --------- ----------

JONES MANAGER 2975 BLAKE MANAGER 2850

CLARK MANAGER 2450 SCOTT ANALYST 3000 KING PRESIDENT 5000 FORD ANALYST 3000

五.伪列:伪列就像表中的列一样，但是在表中并不存储。伪列只能查询，不能进行增删改操作。 以下学习两伪列:rowid和rownum

rowid和rownum的不同:rowid是插入记录时生成，rownum是查询数据时生成。rowid标识的是行的物理地址，rownum标识的是查询结果中的行的次序。

①rowid伪列:返回的是某行的物理地址，用此伪列可快速的定位表中的某一行。表中每一行在数据文件中都有一个物理地址。

例如1:查询出工资大于2000的员工姓名的物理地址 select rowid,ename from emp where sal>2000; 结果:

ROWID ENAME ------------------ ----------

AAAL+ZAAEAAAAAdAAD JONES AAAL+ZAAEAAAAAdAAF BLAKE AAAL+ZAAEAAAAAdAAG CLARK AAAL+ZAAEAAAAAdAAH SCOTT AAAL+ZAAEAAAAAdAAI KING

AAAL+ZAAEAAAAAdAAM FORD

②rownum伪列:用于标识查询结果中的行的次序。为结果集中每一行标识一个行号，第一行返回1，第二行返回2，以此类推。

例如2:查询出员工表中前5名员工的姓名，工作和工资。 select rownum,ename,job,sal from emp where rownum<=5; 结果:

ROWNUM ENAME JOB SAL ------ ---------- --------- ----------

1 SMITH CLERK 800 2 ALLEN SALESMAN 1600 3 WARD SALESMAN 1250 4 JONES MANAGER 2975 5 MARTIN SALESMAN 1250

例如3:查询出工资最高的前5名员工的姓名，工作和工资。

解析:\工资前5名的\需要先降序，再取前5名。

select rownum,T.* from (select ename,job,sal from emp order by sal desc ) T where rownum <=5 结果:

ROWNUM ENAME JOB SAL ------- ---------- --------- ----------

1 KING PRESIDENT 5000 2 SCOTT ANALYST 3000

本文来源：https://www.bwwdw.com/article/5hcv.html