oracle基础知识

SQL语言的分类

数据定义语言DDL(create alter drop)

数据操纵语言DML(insert delete update select)*

事务控制语言TCL(commit rollback)

数据控制语言DCL(grant revoke)

 

--ORACLE数据库中的伪列rowid,rownum

无须自己建表时添加，由数据库自动添加

rownum表中每一行的序号，可以用于获取前多少行的数据,只能小于，不能大于或者等于select * from emp where rownum<=5

 

rowid可以精确的区分所有字段内容都相同的数据

select rowid,tid,tname from temp where rowid='AAANENAAEAAAOJ2AAC'

 

--数据定义语言DDL

 

--create表的约束

create table temp(

empno number,

ename varchar2(200) default 'lifan',--表示ename默认为'lifan'

deptno number default 110,     --表示deptno默认为110

edate date default sysdate,     --默认值，表示为当前日期

address varchar2(200) not null,     --非空

sal number(8,2) constraint emp_salary_min check (sal>0),--检查约束,表示这个字段必须大于0

constraint fk_deptno foreign key(deptno) references dept(deptno),--外键

constraint pk_empno primary key(empno));--主键

 

 

--主键(非空，唯一)所有表必须要有主键

--方式一 在建表语句中添加

constraint pk_主键名称 primary key(主键字段)

 

--方式二 先建表，在修改字段为主键

alter table 表名 add constraint pk_主键名称 primary key(主键字段)

 

--方式三 直接在表字段后添加primary key

 

--外键(表中的外键字段，关联另外一个表的主键，确保数据的完整性)

--方式一 在建表语句中添加

constraint fk_外键名称 foreign key(外键字段) references 关联表(关联表主键)

 

--方式二 先建表，再修改字段为外键

alter table 表名 add constraint fk_外键名称 foreign key(外键字段) references 关联

--用户解锁

alter user scott account unlock;

 

--修改列,当表中有值的时候不允许

alter table 表名 modify (列名 数据类型);

alter table temp modify (tid number(5));

 

--添加列

alter table 表名 add(列名 数据类型);

alter table temp add(tadd varchar2(2000));

 

--删除列

alter table 表名 drop column 删除的列名;

alter table temp drop column tadd;

 

--rename表的重命名

方法一

alter table 表名 rename to 新表名;

alter table emp rename to newname;

 

方法二

rename 表名 to 新表名;

rename emp to newename;

 

--重命名列名

alter table 表名 rename column 列名 to 新列名;

alter table emp rename column ename to aaa;

 

--删除表(删除整个表结构,包括表中的数据)

drop table temp;

 

 

--数据操纵语言DML(insert delete update select)*

 

--利用现有表创建新表(表复制，包括表结构)

create table 新表名 as sql查询语句;

create table newemp as select * from emp;

 

--向表插入现有表的数据(数据复制)

insert into 表名 sql查询语句

insert into temp select * from emp;

 

--使用别名 三种方法，真接写，AS，加""号

select empno 编号,ename as 姓名,job "工作" from emp

select a.empno,a.ename from emp a

 

--事务控制语言TCL(commit,rollback)

--在新增修改删除时数据库会为这些操作自动加上一个事务管理,用于控制多条SQL语句执行确保数据的完整性

commit    提交

rollback  回滚

 

--truncate 截断表(和delete类似,删除表的数据)

truncate table temp;

 

--truncate 和 delete的区别

delete是可以回滚

truncate不能回滚

--数据控制语言 DCL

--创建用户

create user 用户名 identified by 密码;

--将对象的权限授予用户

grant 权限 on 表名 to 用户;

grant all on emp to username;

grant select,update,delete,insert on emp to username;

--删除用户权限

revoke 权限 on 表名 from 用户;

revoke select on emp from username;

--为用户授权(所有权限)

grant connect,resource,create session,

dba, create table, create view, create trigger,

select any table, create sequence, create procedure, create role,

grant any privilege, drop any role,

create public synonym, drop public synonym,SELECT ANY DICTIONARY to scott;

oracle中的数据类型:

 

oracle中函数整理：

--字符函数

CONCAT(字符串1,字符串2) 将字符串1和字符串2连接成一个新的字符串

示例: select CONCAT(job,ename) from emp  

LPAD(字段,总的大小,添充字符) 左填充即向右对齐

示例: select empno,lpad(sal,10,'*') from emp

RPAD(字段,总的大小,添充字符) 右填充即向左对齐

示例: select empno,rpad(sal,10) from emp  

LOWER(字符串) 将字符串全部变成小写;

UPPER(字符串) 将字符串全部变成大写;

INITCAP(字符串) 将字符串变成第一个字母大写,其余都变成小写;

LENGTH(字符串) 求出字符串的长度;

SUBSTR(字符串,开始位置,长度) 从字符串中取子串;

示例: select substr(ename,2,3) from emp;--从ename的第2位开始取3位

INSTR(字符串,字符) 查看字符是否在字符串中存在;不存在返回0;存在则返回字符所在的的位置；如果有两个以上的字符则返回第一个的位置.

示例:select instr(ename,'S') from emp;  

TRIM(字符 FROM 字符串) 去掉字符串首尾的字符;

示例: select trim('S' from ename) from emp;

TO_CHAR() 将不是其他类型转成字符类型；对于日期型可以控制其格式:TO_CHAR(日期,'格式');其中格式有: 'YYYY' --以4为显示年;'YEAR' --以标准格式显示年； 'MM' ; 'MON' ; 'DD' ; 'DAY'; 'HH' ; 'MI' ;'SS'

REPLACE(字符串,字符串1,字符串2) 将字符串中的字符1替换成字符2;

示例: select replace(ename,'SC','SS') from emp;  

TRANSLATE(字符串,字符串1,字符串2) 替换多的字符;

示例: select translate(ename,'SH','AB') from emp; --表示将ename中的'S'换成'A','H'换成'B';

ASCII(char) 求字符的ascii码

NLSSORT(字符串) 对字符串排序.

 

 

--数学函数

ABS(数字) 一个数的绝对值

CEIL(数字) 向上取整;不论小数后的书为多少都要向前进位;

CEIL(123.01)=124;

CEIL(-123.99)=-123;  

FLOOR(数字) 向下取整;不论小数后的书为多少都删除;|

floor(123.99)=123;

floor(-123.01)=-124;  

MOD(被除数,除数) 取余数;

MOD(20,3)=2

ROUND(数字,从第几为开始取) 四舍五入;

ROUND(123.5,0)=124;

ROUND(-123.5,0)=-124;

ROUND(123.5,-2)=100;

ROUND(-123.5,-2)=-100;  

SIGN(数字) 判断是正数还是负数;正数返回1，负数返回-1，0返回0;

SQRT(数字) 对数字开方；

POWER(m,n) 求m的n次方；

TRUNC(数字,从第几位开始) 切数字;

TRUNC(123.99,1)=123.9

TRUNC(-123.99,1)=-123.9

TRUNC(123.99,-1)=120

TRUNC(-123.99,-1)=-120

TRUNC(123.99)=123

GREATEST(数字列表) 找出数字列表中最大的数;

示例:select greatest(100,200,-100) from dual; --结果为200

LEAST(数字列表) 找出数字列表中最小的数;

SIN(n) 求n的正旋

COS(n) 求n的余旋

TAN(n) 求n的正切

ACos(n) 求n的反正切

ATAN(n) 求n的反正切

exp(n) 求n的指数

LN(n)  求n的自然对数,n必须大于0

LOG(m,n) 求n以m为底的对数,m和n为正数,且m不能为0

 

 

--日期函数

ADD_MONTHS(日期,数字) 在以有的日期上加一定的月份;

示例:select add_months(hiredate,20),hiredate from emp;  

LAST_DAY(日期) 求出该日期的最后一天.

MONTHS_BETWEEN(日期1，日期2) 求出两个月之间的天树（注意返回的天数为小数);

示例:select months_between(sysdate,hiredate) from emp;  

NEW_TIME(时间,时区,'gmt') 按照时区设定时间.

NEXT_DAY(d,char) 返回d指定的日期之后并满足char指定条件的第一个日期

 

 

其他函数

VSIZE(类型) 求出数据类型的大小;

NVL(字符串,替换字符) 如果字符串为空则替换，否则不替换

 

Oracle 中的异常类型：