鸿 网 互 联 www.68idc.cn

当前位置 : 服务器租用 > 数据库 > oracle > >

Oracle数据库笔记

来源:互联网 作者:佚名 时间:2018-02-03 10:52
SQL分为四大类别 1、 DDL:Date Definition Language 数据定义语言 用于建立、修改、删除数据库对象(create创建表和其它对象结构;alter修改表或其它结构;drop删除表或其它结构;truncate删除表数据,保留表结构) 2、 DML:Data Manipulation Language 数据

SQL分为四大类别

1、DDL:Date Definition Language 数据定义语言 

用于建立、修改、删除数据库对象(create创建表和其它对象结构;alter修改表或其它结构;drop删除表或其它结构;truncate删除表数据,保留表结构)

2、DML:Data Manipulation Language 数据操作语言

用于改变数据表中的数据(和事务相关,执行完以后要进行提交,才能真正的把改变保存到数据库中)

INSERT:向数据表中插入一条数据

UPDATE:更新数据表中已经存在的数据

DELETE:删除数据表中的一条数据

3、TCL:Transaction Controller Language 事务控制语句

用来维护数据一致性的。

COMMIT:提交,确认已经进行的数据改变;

ROLLBACK:回滚,取消已经进行的数据改变;

SAVEPOINT:保存点,使得事务可以回滚到指定的保存点,用于取消改变;

4、DQL:Data Query Language 数据查询语言

用来查询所需要的数据 (SELECT语句)

5、DCL:Data Controller Language 数据控制语言

用于执行权限的授予和收回

GRANT:用于给用户或角色授予权限

REVOKE:用于收回用户或角色的权限

CREATE USER:创建用户

ORACLE数据类型

数据类型 说明
CHAR

表示固定长度的字符类型,CHAR(N) N表示占用的字节数,最大长度2000字节

VARCHAR2

表示可变长度的字符类型,VARCHAR2(N) N表示最大占用的字节数,最大长度4000字节,

NUMBER(N,M)

表示数字类型,N表示数字总的位数,M表示小数的位数,N的取值为1-38.INT,INTERGER,SMALLINT,DUOBLE,REAL,FLOAT,DECIMAL(P,S),NUMERIC(P,S)完全映射到NUMBER,目的是多种数据库及编程语言的兼容。

DATE

用于定义时间类型的数据,长度是7个字节,默认的格式是DD-MON-RR,例如10-APR-99,第一个字节:世纪+100;第二字节:年;第三字节:月;第四字节:天;第五字节:小时+1;第六字节:分+1;第七字节:秒+1;

LONG

是VARCHAR2的加长版,有诸多限制,不介意使用,一个表中只能有一个列是LONG类型的,不能作为主键,不能出现在查询条件中,最多可以存2GB的数据。

CLOB

存储定长和可变长的字符,最大容量4GB,开发中经常使用CLOB代替LONG数据类型。

TIMESTAMP 与DATE不同的是,可以精确到ns(纳秒)
SYSDATE 返回系统的时间,默认的格式是:DD-MON-RR;
SYSTIMESTAMP 返回系统的日期和时间,精确到毫秒。

  常用函数

CONCAT(char1,char2)和||使用相同 连接两个字符串,
LENGTH(char1)

返回字符串的长度(如何是CHAR,这返回补空格后的长度,

如果是VARCHAR2,则返回字符的实际长度。)

UPPER(char1) 用于将字符串转换成大写
LOWER(char1) 用于讲字符串转换成小写字符
INITCAP(char1) 用于将字符串中的首个单词转换成大写
TRIM(c2,c1) 用于截去c1前后的c2,如果没有c2就是去掉前后空格
LTRIM(c1,c2)

从c1的左边截去c2

RTRIM(c1,c2)

从c1的右边截去c2

LPAD(char1,n,char2) 左补位函数,用于在char1的左边用char2补足n位
RPAD(char1,n,char2)

右补位函数,用于在char1的右边用char2补足n位

SUBSTR(char,[n,m])

用于获取字符串的子串,返回char中n,m的子串。n=0,则从开始位置,n等于负数,则从末尾开始,m没有,则取到末尾,m过大则取到末尾。注意,字符串的首位从1开始。

INSTR(char1,char2,[n,m])

返回char2在char1中的位置,n没有指定则从1开始,m用于指定出现的次数,如果不指定,则取1,如果在char1中没有找到char2,则返回0.

ROUND(N,[,M])

参数n可以是任何数字,m必须为整数。m默认是0;m为正数,则四舍五入到小数点后m为;m为负数,则四舍五入到小数点前m位,m取0,则四舍五入到整数位。

TRUNC(N,[,M])

用于截取,和ROUND相同,不同的是,TRUNC是用于截取。

MOD(N,[,M]) N除于M的余数,M没有,则直接返回N。
CEIL(N)和FLOOR(N) 取N的最大值和N的最小值。
TO_DATE(char1[,fmt[,nlsparms])

char为要转换的字符串,fmt为格式,nlsparms指定日期语言。

日期语言:

YY 2为数字的年份
YYYY 4为数字的年份
MON 月份的简写
MM 2为数字的月份
MONTH 月份的全拼
DD 2位的天
DAY 周几的全拼
DY 周几的简写
HH24 24小时制的小时
HH12 12小时制的小时
MI 显示分
SS 显示秒
TO_CHAR(date[,fmt[,nlsparms]])

将其它类型的转换位字符类型,将date类型的按照fmt格式输出字符串,nlsparms用于指定日期语言

LAST_DAY(date) 返回date所在月的最后一天
ADD_MONTHS(date,i)

返回date加上i个月后的日期值,i位整数,则加;i为负数,则减;i取小数,则截取小数后参与运算

MONTHS_BETWEEN(date1,date2) 计算date1和date2之间相隔了多少个月。
NEXT_DAY(date,char) 返回date日期的下一个周几,周几是由char来决定的,例如WEDNESDAY
LEAST(exp1[,exp2[,exp3]])和GREATEST(exp1[,exp2[,exp3]])

叫做比较函数,返回参数列表中最小和最大的值;注意:参数类型必须一致,后面的参数列表会隐含的转换和第一个参数一致,如果不能转换则会报错。

EXTRACT(date FROM datetime) 从date time中提取date指定的数据,比如:提取年,月,小时等。
NVL(exp1,exp2)

将NULL转换为非NULL,如果exp1为空,则取exp2的值,exp1和exp2可以是任何的数据类型,但是必须一致。

NVL2(exp1,exp2,exp3)

和NVL一致,如果exp1不为空,则取exp2,exp2为空,则取exp3;类别表达式语句。

MAX(column)和MIN(column)

用来统计列值的最大和最下值;可以用来统计任何数据类型,包括日期,数字,字符等

AVG(column)和SUM(column) 用来统计列或表达式的平均值和和值。必须是数字,忽略空值。
COUNT(column or *) 用来统计表中的记录数,忽略空值。聚合函数忽略空值。
DECODE(expr,search1,result[,searche2,result...][,default])

decode用expr比较参数的值,匹配到哪一个searche,就返回对应的result,如果都没有匹配到,就返回default的值,如果没有指定default的值,则返回null。

ROWNUM 分页查询ROWNUM:被称作伪列,用于返回标识行数据顺序的数字。
ROW_NUMBER() OVER(PARTITION BY col1 ORDER BY col2)

根据col1分组,col2排序。比ROWNUM功能更强大,可以之间从结果集中取子集。

PANK() OVER(PARTITION BY col1 ORDER BY col2)

根据col1分组,col2排序,跳跃排序,如果数据相同,则排名相同。

DENSE_RANK()OVER(PARTITION BY col1 ORDER BY col2 ) 根据col1分组,组内根据col2排序。连续排序。
INTERSECT

获得两个结果集的交接,只有在两个结果集都存在的数据才会显示。结果集会用第一列做升序排序。

MINUS

获得连个结果集的差集,只有在第一个结果中由的数据才会显示出来。

ROLLUP

高级分组函数,例如:GROUP BY ROLLUP(a,b,c)对应ROLLUP从右往左,一次少一列的方式进行分组。直到所有的列都去掉后的分组。

CUBE 和ROLLUP相似
GROUPING SETS 和CUBE或ROLLUP函数对比。
   
   

表的创建

1 CREATE TABLE table_name(
2 
3 column_name datetype[DEFAULT expr][,...]
4 
5 );
DESC:DESC table_name //用于显示表的结构

DEFAULT:用于给列指定默认值

NOT NULL:非空,指定列值不能为空

RENAME oldtablename ON newtablename//给表从新命名
ALTER TABLE table_name ADD(columnname typedate[DEFAULT expr][,...])//注意,列只能追加到最后
ALTER TABLE table_name DROP(column)

 

ALTER TABLE table_name MODIFIED(column typedata[DEFAULT expr][,...])

注意:修改列以后只能对后面插入的数据起作用,把数据由大改小,可能不成功!

INSERT语句

INSERT INTO table_name(column[,column]) VALUES(values[,values])

注意:执行DML,需要commit后,才能把此条记录提交到数据库中。

UPDATE语句

1 UPDATE table_name SET column=value,[,column=value]
2 [WHERE=condition];

DELETE语句

 

DELETE FROM table_name [WHERE=condition]

 注意:如果没有where语句,则全表的数据都会被删除。

DELETE与TRUNCATE语句的区别:

DELETE语句可以有选择的删除,是DML语句,可以回退。当表的记录很多的时候,执行效率没有TRUNCATE语句高

TRUNCATE语句则删除全表的数据,是DDL语句,立即生效,不可回退。执行效率比DELETE语句高。

关联查询

基础:笛卡儿积

等值连接:使用=;连接两张关联的表;

内连接:内连接返回所以满足条件的记录;例如:

SELECT * FROME DEPT D JION EMP E ON(D.NO=E.NO);

 

外连接:外连接不仅返回满足条件的记录,还返回不满足条件的记录。

1 SELECT table1.column table2.column FROM table1 t1 [FULL][LEFT][RIGHT][OUTER] JION table2 t2 ON(t1.no=t2.no);

 

全外连接:返回满足条件的记录,还返回不满足条件的记录,是左右连接的查询结果的综合。FULL

自连接:数据来源一张表,可以自己参考自己的列,把表虚拟化成两张表。

创建用户并授权

 

1 CREATE USER user_name IDENTIFIED BY password;
2 GRANT CREATE TABLE TO user_name;//授予用户创建表
3 GRANT CREATE SESSION TO user_name;//授予用户创建会话
4 GRANT CREATE TABLESPACE TO user_name;//授予用户创建表空间
5 GRANT CREATE VIEW TO user_name;//授予用户创建视图

 

创建表空间

CREATE TABLESPACE tablespace_name
datafile '\路径(要先建好)\**.dbf'
size 80M
autoExtend on next 5m maxsize 10m;
//删除表空间
drop tablespace tablespace_name including contents and datafiles

用户权限

1 //授予用户使用表空间的权限:  
2 alter user 用户名 quota unlimited on// 表空间;  
3alter user 用户名 quota *M on//表空间;  

 

网友评论
<