Oracle数据库笔记

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的左边截去c2RTRIM(c1,c2) 从c1的右边截去c2LPAD（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指定日期语言。 日期语言： YY2为数字的年份YYYY4为数字的年份MON月份的简写MM2为数字的月份MONTH月份的全拼DD2位的天DAY周几的全拼DY周几的简写HH2424小时制的小时HH1212小时制的小时MI显示分SS显示秒

[tr]TO_CHAR(date[,fmt[,nlsparms]]) 将其它类型的转换位字符类型，将date类型的按照fmt格式输出字符串，nlsparms用于指定日期语言[/tr] [tr]LAST_DAY(date) 返回date所在月的最后一天[/tr] [tr]ADD_MONTHS(date，i) 返回date加上i个月后的日期值，i位整数，则加；i为负数，则减；i取小数，则截取小数后参与运算[/tr] [tr]MONTHS_BETWEEN(date1,date2) 计算date1和date2之间相隔了多少个月。[/tr] [tr]NEXT_DAY(date,char) 返回date日期的下一个周几，周几是由char来决定的，例如：WEDNESDAY[/tr] [tr]LEAST(exp1[,exp2[,exp3]])和GREATEST(exp1[,exp2[,exp3]]) 叫做比较函数，返回参数列表中最小和最大的值；注意：参数类型必须一致，后面的参数列表会隐含的转换和第一个参数一致，如果不能转换则会报错。[/tr] [tr]EXTRACT(date FROM datetime) 从date time中提取date指定的数据，比如：提取年，月，小时等。[/tr] [tr]NVL(exp1,exp2) 将NULL转换为非NULL,如果exp1为空，则取exp2的值，exp1和exp2可以是任何的数据类型，但是必须一致。[/tr] [tr]NVL2(exp1,exp2,exp3) 和NVL一致，如果exp1不为空，则取exp2，exp2为空，则取exp3；类别表达式语句。[/tr] [tr]MAX(column)和MIN(column) 用来统计列值的最大和最下值；可以用来统计任何数据类型，包括日期，数字，字符等[/tr] [tr]AVG(column)和SUM(column) 用来统计列或表达式的平均值和和值。必须是数字，忽略空值。[/tr] [tr]COUNT(column or *) 用来统计表中的记录数，忽略空值。聚合函数忽略空值。[/tr]

表的创建

CREATE TABLE table_name(
 
 column_name datetype[DEFAULT expr][,...]
 
 );

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语句

UPDATE table_name SET column=value,[,column=value]
 [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);

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

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

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

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

创建用户并授权

CREATE USER user_name IDENTIFIED BY password;
 GRANT CREATE TABLE TO user_name;//授予用户创建表
 GRANT CREATE SESSION TO user_name;//授予用户创建会话
 GRANT CREATE TABLESPACE TO user_name;//授予用户创建表空间
 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

用户权限

//授予用户使用表空间的权限：  
 alter user 用户名 quota unlimited on// 表空间;  
 或 alter user 用户名 quota *M on//表空间;