oracle学习日志02

oracle简单的操作

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

(create,drop,alter,desc)

用于创建对象

如 :create table ,alter table,drop table,create view;

数据操纵语言 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;

简单的查询语句

l Select 指定查询哪些列的数据。

l column 指定列名。

l *号代表查询所有列。

l From 指定查询哪张表。

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

■查看表结构

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;