oracle学习日志02

最新推荐文章于 2025-08-20 18:44:58 发布

原创最新推荐文章于 2025-08-20 18:44:58 发布 · 345 阅读

2 ·

CC 4.0 BY-SA版权

oracle 专栏收录该内容

1 篇文章

订阅专栏

本文介绍了 Oracle 数据库的基本操作，包括数据定义语言 (DDL)、数据操纵语言 (DML) 和数据控制语言 (DCL) 的使用方法。详细讲解了如何创建表、插入数据、更新数据、删除数据等常见任务。

oracle简单的操作

数据定义语言DDL (Data Definition Language)

(create,drop,alter,desc)

用于创建对象

如:create table ,altertable,droptable,createview;

数据操纵语言DML (Data Manipulation Language)

(insert,delete,update,select)

用于操作数据CRUD。

如：insert into,update,delete,select

数据控制语言DCL(Data Control Language)

(grant,revoke)

用于定义数据库用户的权限

如grant、Revoke

系统权限	允许用户的操作
CREATE SESSION	连接到数据库
CREATE SEQUENCE	创建序列，用来生成一系列的数值
CREATE SYNONYM	创建同义词
CREATE TABLE	在用户架构中创建表
CREATE ANY TABLE	在任意架构中创建表
DROP TABLE	从用户架构中删除表
DROP ANY TABLE	在任意构架中删除表
CREATE PROCEDURE	创建存储过程
EXECUTE ANY PROCEDURE	在任意架构中执行存储过程
CREATE USER	创建用户
DROP USER	删除用户
CREATE VIEW	创建视图

表的管理--创建表(基本语句)

CREATETABLE table_name

(

field1 datatype,

field2 datatype,

field3 datatype,

)

field：指定列名　datatype：指定列类型

分类	数据类型	说明
文本、二进制类型	CHAR(size) char(20) VARCHAR(size) varchar(20) nchar(n) nvarchar2(n) clob(character large object) blob(binary large object)	定长最大2000字符变长最大4000字符 Unicode数据类型 ,定长最大2000字符 Unicode数据类型 ,变长最大4000字符字符型大对象 ,最大8tb 二进制数据可以存放图片/声音8tb
数值类型	number(p,s)	p为整数位,s为小数位.范围：1 <= p <=38, -84 <= s <= 127 保存数据范围：-1.0e-130 <= number value < 1.0e+126 保存在机器内部的范围： 1 ~ 22 bytes
时间日期	date TIMESTAMP(n)	包含年月日,时分秒。默认格式：DD-MON-YYYY。从公元前4712年1月1日到公元4712年12月31日的所有合法日期 n的取值为0~9.表示指定TIMESTAMP中秒的小数位数。N为可选。如果n为0，timestamp与date等价[不推荐]

◇ number可以理解成是一个可变的数值类型,比如number(12),你放一个小整数，它占用的字节数就少,你放一个大整数，它占用的字节数就多，很好!

删除表

drop tablename；

使用ALTER TABLE 语句添加,修改,或删除列的语法.

ALTER TABLE tablename

ADD (columnname datatype);

ALTER TABLE table

MODIFY (columnname datatype);

ALTER TABLE table

DROP column(column);

修改表的名称：rename 表名to新表名

查表
desc tablename;

DML 对数据进行增删改查insert,delete,update,select)

使用 INSERT 语句向表中插入数据。

INSERT INTO table [(column [,column...])] VALUES (value [,value...]);

插入的数据应与字段的数据类型相同。
数据的大小应在列的规定范围内，例如：不能将一个长度为80的字符串加入到长度为40的列中。
在values中列出的数据位置必须与被加入的列的排列位置相对应。
字符和日期型数据应包含在单引号中。
插入空值，不指定或insert into table value(null)

使用 update语句修改表中数据。

UPDATE tbl_name

SETcol_name1=expr1 [,col_name2=expr2 ...]

[WHEREwhere_definition]

UPDATE语法可以用新值更新原有表行中的各列。
SET子句指示要修改哪些列和要给予哪些值。
WHERE子句指定应更新哪些行。如没有WHERE子句，则更新所有的行。

使用 delete语句删除表中数据。

delete from tbl_name       
	[WHERE where_definition]

如果不使用where子句，将删除表中所有数据。
Delete语句不能删除某一列的值（可使用update）
使用delete语句仅删除记录，不删除表本身。如要删除表，使用drop table语句。
同insert和update一样，从一个表中删除记录将引起其它表的参照完整性问题，在修改数据库数据时，头脑中应该始终不要忘记这个潜在的问题。
删除表中数据也可使用TRUNCATE TABLE 语句，它和delete有所不同，参看mysql文档。

■ 删除数据
delete from 表名;
删除所有记录,表结构还在,写日志,可以恢复的,速度慢
drop table 表名; 删除表的结构和数据
delete from student where xh='A001'; 删除一条记录
truncate table 表名;
删除表中的所有记录,表结构还在,不写日志,无法找回删除的记录,速度快

l基本select语句

SELECT [DISTINCT]*|{column1,column2.column3..}

FROM table;

简单的查询语句

lSelect 指定查询哪些列的数据。

lcolumn指定列名。

l*号代表查询所有列。

lFrom指定查询哪张表。

lDISTINCT可选，指显示结果时，是否剔除重复数据

■查看表结构

sql>desc表名;

■查询所有列

select * from 表名;

■查询指定列

select 列1，列2… from表名;

■如何取消重复行

select distinctdeptno ,job fromemp;

■使用列的别名

selectename"姓名",sal*12as "年收入"fromemp;

列后面接上as 再接上""(英文的点，内部放中文)，或直接连英文，只是改变

■连接字符串（重要）

concat(x,y) 将x和y拼接起来，并返回新字符串（仅限于两个字符串连接）

x || y 可连接多个，合并成一个。x || y || z || ‘中文’||......

例如，将EMP表的empno列和ename列进行拼接

SELECTCONCAT(empno,ename)FROM EMP;

■首字母大写，其他小写（不重要）

INITCAP(x)

将字母字符串转换为每个词首字母为大写，其他字母为小写

例如，将EMP表的ename列进行转换

SELECT empno,INITCAP(ename) FROM EMP;

■查找字符出现的位置（不重要）

INSTR(x, find_string [, start] [, occurrence])

返回指定字符串find_string在x中数字位置。可以指定开始搜索的位置start，并提供该字符串出现的次数occurrence。start和occurrence默认为1，表示从字符串开始的位置开始搜索，并返回第一次出现的位置

例如:在EMP表的ename列中查找’MITH’出现的位置

SELECTename,INSTR(ename,’MITH’) FROM EMP;

例如，在EMP表中的ename列，从1位置开始查找第2个’L’出现的位置

SELECTename,INSTR(ename,'L', 1, 2)

FROM EMP WHERE empno =7499

■计算数据长度（重要）

LENGTH(x)

返回表达式中的字符数

例如:计算emp表中雇员名的长度

SELECT ename,LENGTH(ename) FROM EMP;

同样可以计算数字、日期的长度

例如:计算工资长度（计算数字时，计算有效位）

SELECT ename,sal,LENGTH(sal)FROM EMP;

例如:计算日期长度

SELECT ename,hiredate,LENGTH(hiredate) FROM EMP;

■改变大小写（不重要）

LOWER(column|expression)

将字母字符值转换为小写

UPPER(column|expression)

将字母字符值转换为大写

例如:分别用大写和小写形式显示雇员姓名

SELECT UPPER(ename) as大写, LOWER(ename) as小写

FROMemp;

■填充数据两侧空白（不重要）

LPAD(x, width [, pad_string])

在字符串左侧填充pad_string字符，以使总字符宽度为width

RPAD(x, width [, pad_string])

在字符串右侧填充pad_string字符，以使总字符宽度为width

例如

SELECTRPAD(ename, 30,'.'), LPAD(job, 18, '*+‘) FROM emp;

■去除数据两侧空白或特定字符（重要）

LTRIM(x [, trim_string])

从x字符串左侧去除所有的trim_string字符串，如果没有指定trim_string字符串，则默认为去除左侧空白字符

RTRIM(x [, trim_string])

从x字符串右侧去除所有的trim_string字符串，如果没有指定trim_string字符串，则默认为去除右侧空白字符

TRIM(trim_string FROM x)

从x字符串两侧去除trim_string字符串

例如：SELECT

LTRIM(' Hello Gail Seymour!'),

RTRIM('Hi Doreen Oakley!abcabc', 'abc'),

TRIM('0' FROM '000Hey Steve Button!00000')

FROMdual;

■查null（重要）

NVL(x, value)

用于将一个NULL值转换为另外一个值。如果x是NULL值的话返回value值，否则返回x值本身

例如，查询emp表，如果comm列为NULL值，则显示0

SELECTempno,ename,NVL(comm,0) FROM EMP;

语法

NVL2(x, value1, value2)

如果x不为NULL值，返回value1，否则返回value2

例如，查看雇员表奖金情况

SELECTempno,ename,NVL2(comm, '有奖金', '无奖金')

FROM EMP;

■替换（不重要）

REPLACE(x, search_string, replace_string)

从字符串x中搜索search_string字符串，并使用replace_string字符串替换。并不会修改数据库中原始值。

例如，查找EMP表中ename列，将’SMITH’替换成‘史密斯'

SELECTename,REPLACE(ename,'SMITH', '史密斯') asnewname FROMEMP WHERE empno = 7369;

■不区分大小写查询（不重要）

SOUNDEX(x)

返回代表x字符串的语音的表示形式，可用于查找一些语音相同但是拼写不同的单词

例如

SELECTename FROMEMP

WHERESOUNDEX(ename) =SOUNDEX('smɪth');

■截取字符串（重要）

SUBSTR(x, start [, length])

返回字符串中的指定的字符，这些字符从字符串的第start个位置开始，长度为length个字符；如果start是负数，则从x字符串的末尾开始算起；如果length省略，则将返回一直到字符串末尾的所有字符

例如，从EMP表中提取ename列从第2个字符开始，长度为3的字符串

SELECT ename,SUBSTR(ename, 2,3) FROM EMP;

数字函数接受数字输入，并以数字形式返回处理

函数名	说明	举例
ABS(value)	返回value的绝对值	SELECT ABS(10),ABS(-10) FROM dual; 返回：10 和10
CEIL(value)	返回大于或等于value的最小整数	SELECT CEIL(5.8),CEIL(-5.2) FROM dual; 返回：6 和-5
FLOOR(value)	返回小于或等于value的最大整数	SELECT FLOOR(5.8),FLOOR(-5.2) FROM dual; 返回：5 和-6
POWER(value,n)	返回value的n次幂	SELECT POWER(2,1),POWER(2,3) FROM dual; 返回：2 和8
MOD(m,n)	返回m和n取余数的结果	SELECT MOD(8,3),MOD(8,4)FROM dual; 返回：2 和0
SQRT(value)	对value进行开方	SELECT SQRT(25),SQRT(5) FROM dual; 返回：5 和2.23606798
TRUNC(value,n)	对value进行截断。如果n>0，保留n位小数；n<0，则保留-n位整数位；n=0，则去掉小数部分	SELECT TRUNC(5.75),TRUNC(5.75,1), TRUNC(5.75,-1) FROM dual; 返回：5、5.7和 0
ROUND(value[,n])	对value进行四舍五入，保存小数点右侧的n位。如果n省略的话，相当于n=0的情况。	SELECT ROUND(5.75),ROUND(5.75,1),ROUND(5.75,-1) FROM dual; 返回：6、5.8和 10

装换函数

主要介绍以下几种：

TO_CHAR()

TO_NUMBER()

CAST()

■to_char()

TO_CHAR(x [, format])

将x转化为字符串。format为转换的格式，可以为数字格式或日期格式

例如，将数字转换为字符串

SELECTTO_CHAR(12345.67) FROM dual;

将数字以指定格式输出

例如，以指定99,999.99格式输出12345.67

SELECTTO_CHAR(12345.67, '99,999.99‘) FROM dual;

元素	说明	示例
9	数字位置(9的个数确定了显示的宽度)	SELECT TO_CHAR(1234,’999999’) FROM dual; 返回：__1234
0	显示前导0	SELECT TO_CHAR(1234,'099999') FROM dual; 返回：001234
$	浮动的美元符号	SELECT TO_CHAR(1234,'$999999') FROM dual; 返回：$1234
L	浮动的当地货币符号	SELECT TO_CHAR(1234,‘L999999') FROM dual; 返回：￥1234
.	指定位置的小数点	SELECT TO_CHAR(1234,'999999.99') FROM dual; 返回：1234.00
,	指定位置的逗号	SELECT TO_CHAR(1234,'999,999') FROM dual; 返回：1,234
EEEE	科学计数法(格式必须指定4个E)	SELECT TO_CHAR(1234,'9999.99EEEE') FROM dual; 返回：1.23E+03

■to_number()

TO_NUMBER(x [, format])

将x转换为数字。可以指定format格式

例如

SELECTTO_NUMBER('970.13') + 25.5 FROM dual;

SELECTTO_NUMBER('-$12,345.67', '$99,999.99‘) FROM dual;

■cast()

CAST(x AS type)

将x转换为指定的兼容的数据库类型。

例如

SELECT

CAST(12345.67 AS VARCHAR2(10)),

CAST('05-7月-07' AS DATE),

CAST(12345.678 AS NUMBER(10,2))

FROMdual;

■to_date()

TO_DATE(x [,format])

将x字符串转换为日期

例如

SELECTTO_DATE('2012-3-15','YYYY-MM-DD‘) FROM dual;

日期格式元素

年

YYYY：4位数字的年，如：2008

YY：2位数字的年，如：08

月

MM：两位数字的月份，如：09

MONTH：月份的全称的大写形式

MON：3位的月份

天

DD：月份中日的2位表示形式

DAY：大写的星期几；Day：表示小写的星期几

小时

HH24：24小时进制 HH：12小时进制

分钟

MI：2位的分钟数

秒

SS：2位的秒数

update person set email='chenjie@163.com' where pname='陈杰';
update person set pname = '陈杰1'，sex='女' where pname='陈杰';
update person (pname,sex,email) values ('李炜业','女','lwy@163.com') where pname='李炜业';

delete from person where pid=5;
select * from person;
/*根据已有表结构以及数据创建一张新表，新表的结果和数据与原来一致*/
/*create table person1 as select * from person;*/

select * from person1;

alter table person1 add (descr as(pname+sex+email));
alter table person1 drop column descr;

alter table person1 add(maxval number);
alter table person1 add(minval number);

select p.*,p.rowid from person1 p

alter table person1 add(avgval as ((maxval+minval)/2));

alter table person1 rename column pname to name;
/*将查询的结果插入的相应的表中，查询的字段和要插入的字段一定要一一对应*/
insert into person1 (pid,name,sex,birthday,email)
select pid,pname,sex,birthday,email from person where pid=6;