oracle 自學總結2 ----很亂 但東西蠻多的
普通用户 scott 密码: tiger
普通管理员用户 system 密码：manager
超级管理员用户 sys 密码：xiachufeng
ed a；创建记事本用@a;来执行脚本
在d盘建一个demo.txt文件里面写sql语句 用@d:\demo.txt;或者@d：demo.txt
如果后缀名不是*.sql即(demo.sql)就可以用@d:demo;
切换连接 conn
管理员用户命令 conn system/manager(即用户名/密码)
超级管理员命令 conn sys/change_oninstall as sysdba;用超级管理员用户登录必须加上as sysdba；
在不同用户访问emp表 必须加上用户名加上完整的表面；即我在超级管理员的用用户下可以访问scott用户下的emp表 命令：select * from scott.emp;
忘记自己当前连接的用户是哪个 可以用 名 Show user； 即显示当前正在连接的用户
查看当前连接的用户下有多少个表可以用 select * from tab; 显示所有的表名
数据类型：
number(4) 表示数字 相当于SqlServer中的int类型；
varcahr2(10) 表示字符串 只能存放10个字符长度
data 时间类型
number(7,2) 表示数字 小数占2位 整数占5位熟 总共7位数
继续重复执行上一条命令 用/
输出一行数据 dbms_output.put_line('我是杜成刚') 这样就会输出'我是杜成刚'
DISTINCT 去除查询重复的列
oracle里面拼接字符串用 || 连接字符串 相当于c#里面的加号
清屏命名 clear scr;
锁定用户 alter user 用户名 account lock；
解除用户 alter user 用户名 account unlock；
创建用户 create user 管理员 identified by 密码;
修改用户密码 alter user scott identified by tiger(tiger是用户密码)
给用户创建权限 grant create table to 用户名；
给用户授予管理员权限 grant dba to 用户名；
给用户授予登陆权限 grant connect to 用户名；
给用户无限表空间权限 grant unlinmited tablespace to 用户名；
收回权限 rovoke dba from 用户名；
删除用户 drop user zl;
更改用户密码 alter user 管理员 identified by 密码;
查看表结构 desc 表名;
查看用户下所有的表 select * from user_tables; 或者 select * from tab;
查看所有存储过程 select * from user_source;
查看所有的视图 select view_name from user_views;
查看序列 select * from user_sequences;
查看索引个数和类别 select index_name,index_type,table_name from user_indexes order by table_name;
查看某表的创建时间 select object_name,created from user_objects where object_name=upper('&table_name');
管理员删除别的用户中的表用drop table 用户名.表名；
复制表 create table 表名称 as 子查询；
增加表和修改表 增加列 alter table 表名称 add(字段名 字段类型 default 默认值)
修改列alter table 表名称 modify(字段名 字段类型 default 默认值)
数据库的备份(exp)和恢复(imp)
备份：在d盘上建立一个data的文件夹，在此文件夹中保存所有的备份文件 ，如果要备份，则需要使用命令行的方式,进入到d:\data文件夹中输入exp
恢复: 将在命令行的d盘data文件夹下输入imp
--创建表
CREATE TABLE test (
id number(4) NOT NULL,
name varchar2(30));
--创建递增对象,且它的初始值为1,增量也为1 （创建序列）
create sequence squ_AutoId
increment by 1 --每次加几个
start with 1; --从1开始计数
create
--添加数据
insert into test(id, name)
values(squ_AutoId.nextval, 'name');
sql查询
查询出表前三行 select * from gang whererownum<=3;
查询出表的最后三行 select * from (select name n,age a,sex s,rownum r from gang) where r>(select count(*) from gang)-3 order by rownum desc;
disc用来退出数据库连接
oracle修改密码
输入passw 回车
旧口令:...
新口令:...
雇员表 emp表 雇员的基本信息
部门表 dept表
工资等级表 salgrade表
奖金表 bonus表
创建表
CREATE TABLE 表名字
（
字段名 数据类型 Default 默认值，
字段名 数据类型 Default 默认值，
.................,
..........
）
更改表
ALTER TABLE 表名字 ADD(字段 字段数据类型 Default 默认值);
修改表的结构
ALTER TABLE 表名字 MODIFY(列的名字 列的类型 Default 默认值);
修改表的名字
RENAME 旧的表名称 TO 新的表名称
截断表
TRUNCATE TABLE 表名称; 截断表中所有的数据了 r就算ollback也不行了
约束的分类
创建主键约束
CREATE TABLE person
(
pid NUMBER(4),
name VARCHAR2(30) PRIMARY KEY, //主键约束
age NUMBER(3),
birghday DATE,
sex VARCHAR2(2) DEFAULT ' 男'
);
创建主外键的约束 运动员 成绩
CONSTRAINT sports_grade_sporterid_fk FOREIGN KEY(sporterid) REFERENCES sporter(sporterid)
ON DELETE CASCADE
将person 中的pid指定名称
CREATE TABLE person
(
pid NUMBER(4),
name VARCHAR2(30) NOT NUL，
age NUMBER(3) NOT NUL,
birghday DATE,
sex VARCHAR2(2) DEFAULT ' 男',
CONSTRAINT person_pid_pk PRIMARY KEY(pid) //创建主键
);
唯一约束（UNIQUE 表示一个字段是唯一的其他了不允许重复）
CREATE TABLE person
(
pid NUMBER(4),
name VARCHAR2(30) UNIQUE NOT NUL，//姓名唯一约束
age NUMBER(3) NOT NUL,
birghday DATE,
sex VARCHAR2(2) DEFAULT ' 男',
CONSTRAINT person_pid_pk PRIMARY KEY(pid) //创建主键
);
建立主外键 （主表:person 字表：book）
CREATE TABLE person
(
pid VARCHAR2(18),
name VARCHAR2(30)
age NUMBER(3) NOT NUL,
birghday DATE,
sex VARCHAR2(2) DEFAULT ' 男',
CONSTRAINT person_pid_pk PRIMARY KEY(pid) //创建主键
CONSTRAINT person_name_uk(name) //唯一约束
CONSTRAINT person_age_ck CHECK(age BETWEEN 0 AND 150) //检查约束
CONSTRAINT person_sex_ck CHECK(sex IN('男','女','中'))
);
CREATE TABLE book
(
bid NUMBER PRIMARY KEY NOT NULL,
bname VARCHAR2(30),
bprice NUMBER(5,2),
pid VARCHAR2(18),
CONSTRAINT person_book_pid_fk FOREIGN KEY(pid) //建立外键
)
在system用户登录的情况下创建用户
CREATE USER xiaoming IDENTIFIED BY tiger
注意如果要删除的用户已经创建了表 那么必须加上参数CASCADE
删除用户 DELETE USER xiaoming [CASCADE]
希望xiaoming用户可以去查询emp表
GRANT SELECT ON emp To xiaoming;
希望xiaoming用户可以去修改/删除，查询，添加scott的emp表
GRANT ALL ON emp TO xiaoming
--如果是对象权限 ,就加入 WITH GTANT OPTION
GRANT SELECT ON emp TO xiaoming WITH GRANT OPTION;
--如果系统权限
System 给xiaoming 权限时候：
GRANT CONNECT TO xiaoming WITH admin OPTION;
scott希望收回 xiaoming对emp表的查询权限
REVOKE SELECT ON emp FROM xiaoming
Oracle 函數大全
SQL中的单记录函数
1.ASCII
返回与指定的字元对应的十进位数字;
SQL> select ascii('A') A,ascii('a') a,ascii('0') zero,ascii(' ') space from dual;
A A ZERO SPACE
--------- --------- --------- ---------
65 97 48 32
2.CHR
给出整数,返回对应的字元;
SQL> select chr(54740) zhao,chr(65) chr65 from dual;
ZH C
-- -
赵 A
3.CONCAT
连接两个字串;
SQL> select concat('010-','88888888')||'转23' 高干竞电话 from dual;
高干竞电话
----------------
010-88888888转23
4.INITCAP
返回字串并将字串的第一个字母变为大写;
SQL> select initcap('smith') upp from dual;
UPP
-----
Smith
5.INSTR(C1,C2,I,J)
在一个字串中搜索指定的字元,返回发现指定的字元的位置;
C1 被搜索的字串
C2 希望搜索的字串
I 搜索的开始位置,默认为1
J 出现的位置,默认为1
SQL> select instr('oracle traning','ra',1,2) instring from dual;
INSTRING
---------
9
6.LENGTH
返回字串的长度;
SQL> select name,length(name),addr,length(addr),sal,length(to_char(sal)) from gao.nchar_tst;
NAME LENGTH(NAME) ADDR LENGTH(ADDR) SAL LENGTH(TO_CHAR(SAL))
------ ------------ ---------------- ------------ --------- --------------------
高干竞 3 北京市海锭区 6 9999.99 7
7.LOWER
返回字串,并将所有的字元小写
SQL> select lower('AaBbCcDd')AaBbCcDd from dual;
AABBCCDD
--------
aabbccdd
8.UPPER
返回字串,并将所有的字元大写
SQL> select upper('AaBbCcDd') upper from dual;
UPPER
--------
AABBCCDD
9.RPAD和LPAD(粘贴字元)
RPAD 在列的右边粘贴字元
LPAD 在列的左边粘贴字元
SQL> select lpad(rpad('gao',10,'*'),17,'*')from dual;
LPAD(RPAD('GAO',1
-----------------
*******gao*******
不够字元则用*来填满
10.LTRIM和RTRIM
LTRIM 删除左边出现的字串
RTRIM 删除右边出现的字串
SQL> select ltrim(rtrim(' gao qian jing ',' '),' ') from dual;
LTRIM(RTRIM('
-------------
gao qian jing
11.SUBSTR(string,start,count)
取子字串,从start开始,取count个
SQL> select substr('13088888888',3,8) from dual;
SUBSTR('
--------
08888888
12.REPLACE('string','s1','s2')
string 希望被替换的字元或变数
s1 被替换的字串
s2 要替换的字串
SQL> select replace('he love you','he','i') from dual;
REPLACE('H
----------
i love you
13.SOUNDEX
返回一个与给定的字串读音相同的字串
SQL> create table table1(xm varchar(8));
SQL> insert into table1 values('weather');
SQL> insert into table1 values('wether');
SQL> insert into table1 values('gao');
SQL> select xm from table1 where soundex(xm)=soundex('weather');
XM
--------
weather
wether
14.TRIM('s' from 'string')
LEADING 剪掉前面的字元
TRAILING 剪掉后面的字元
如果不指定,默认为空白字元
15.ABS
返回指定值的绝对值
SQL> select abs(100),abs(-100) from dual;
ABS(100) ABS(-100)
--------- ---------
100 100
16.ACOS
给出反余弦的值
SQL> select acos(-1) from dual;
ACOS(-1)
---------
3.1415927
17.ASIN
给出反正弦的值
SQL> select asin(0.5) from dual;
ASIN(0.5)
---------
.52359878
18.ATAN
返回一个数字的反正切值
SQL> select atan(1) from dual;
ATAN(1)
---------
.78539816
19.CEIL
返回大于或等于给出数位的最小整数
SQL> select ceil(3.1415927) from dual;
CEIL(3.1415927)
---------------
4
20.COS
返回一个给定数字的余弦
SQL> select cos(-3.1415927) from dual;
COS(-3.1415927)
---------------
-1
21.COSH
返回一个数字反余弦值
SQL> select cosh(20) from dual;
COSH(20)
---------
242582598
22.EXP
返回一个数字e的n次方根
SQL> select exp(2),exp(1) from dual;
EXP(2) EXP(1)
--------- ---------
7.3890561 2.7182818
23.FLOOR
对给定的数位取整数
SQL> select floor(2345.67) from dual;
FLOOR(2345.67)
--------------
2345
24.LN
返回一个数字的对数值
SQL> select ln(1),ln(2),ln(2.7182818) from dual;
LN(1) LN(2) LN(2.7182818)
--------- --------- -------------
0 .69314718 .99999999
25.LOG(n1,n2)
返回一个以n1为底n2的对数
SQL> select log(2,1),log(2,4) from dual;
LOG(2,1) LOG(2,4)
--------- ---------
0 2
26.MOD(n1,n2)
返回一个n1除以n2的余数
SQL> select mod(10,3),mod(3,3),mod(2,3) from dual;
MOD(10,3) MOD(3,3) MOD(2,3)
--------- --------- ---------
1 0 2
27.POWER
返回n1的n2次方根
SQL> select power(2,10),power(3,3) from dual;
POWER(2,10) POWER(3,3)
----------- ----------
1024 27
28.ROUND和TRUNC
按照指定的精度进行舍入
SQL> select round(55.5),round(-55.4),trunc(55.5),trunc(-55.5) from dual;
ROUND(55.5) ROUND(-55.4) TRUNC(55.5) TRUNC(-55.5)
----------- ------------ ----------- ------------
56 -55 55 -55
29.SIGN
取数位n的符号,大于0返回1,小于0返回-1,等于0返回0
SQL> select sign(123),sign(-100),sign(0) from dual;
SIGN(123) SIGN(-100) SIGN(0)
--------- ---------- ---------
1 -1 0
30.SIN
返回一个数字的正弦值
SQL> select sin(1.57079) from dual;
SIN(1.57079)
------------
1
31.SIGH
返回双曲正弦的值
SQL> select sin(20),sinh(20) from dual;
SIN(20) SINH(20)
--------- ---------
.91294525 242582598
32.SQRT
返回数字n的根
SQL> select sqrt(64),sqrt(10) from dual;
SQRT(64) SQRT(10)
--------- ---------
8 3.1622777
33.TAN
返回数字的正切值
SQL> select tan(20),tan(10) from dual;
TAN(20) TAN(10)
--------- ---------
2.2371609 .64836083
34.TANH
返回数字n的双曲正切值
SQL> select tanh(20),tan(20) from dual;
TANH(20) TAN(20)
--------- ---------
1 2.2371609
35.TRUNC
按照指定的精度截取一个数
SQL> select trunc(124.1666,-2) trunc1,trunc(124.16666,2) from dual;
TRUNC1 TRUNC(124.16666,2)
--------- ------------------
100 124.16
36.ADD_MONTHS
增加或减去月份
SQL> select to_char(add_months(to_date('199912','yyyymm'),2),'yyyymm') from dual;
TO_CHA
------
200002
SQL> select to_char(add_months(to_date('199912','yyyymm'),-2),'yyyymm') from dual;
TO_CHA
------
199910
37.LAST_DAY
返回日期的最后一天
SQL> select to_char(sysdate,'yyyy.mm.dd'),to_char((sysdate)+1,'yyyy.mm.dd') from dual;
TO_CHAR(SY TO_CHAR((S
---------- ----------
2004.05.09 2004.05.10
SQL> select last_day(sysdate) from dual;
LAST_DAY(S
----------
31-5月 -04
38.MONTHS_BETWEEN(date2,date1)
给出date2-date1的月份
SQL> select months_between('19-12月-1999','19-3月-1999') mon_between from dual;
MON_BETWEEN
-----------
9
SQL>selectmonths_between(to_date('2000.05.20','yyyy.mm.dd'),to_date('2005.05.20','yyyy.mm.dd')) mon_betw from dual;
MON_BETW
---------
-60
39.NEW_TIME(date,'this','that')
给出在this时区=other时区的日期和时间
SQL> select to_char(sysdate,'yyyy.mm.dd hh24:mi:ss') bj_time,to_char(new_time
2 (sysdate,'PDT','GMT'),'yyyy.mm.dd hh24:mi:ss') los_angles from dual;
BJ_TIME LOS_ANGLES
------------------- -------------------
2004.05.09 11:05:32 2004.05.09 18:05:32
40.NEXT_DAY(date,'day')
给出日期date和星期x之后计算下一个星期的日期
SQL> select next_day('18-5月-2001','星期五') next_day from dual;
NEXT_DAY
----------
25-5月 -01
41.SYSDATE
用来得到系统的当前日期
SQL> select to_char(sysdate,'dd-mm-yyyy day') from dual;
TO_CHAR(SYSDATE,'
-----------------
trunc(date,fmt)按照给出的要求将日期截断,如果fmt='mi'表示保留分,截断秒
SQL> select to_char(trunc(sysdate,'hh'),'yyyy.mm.dd hh24:mi:ss') hh,
2 to_char(trunc(sysdate,'mi'),'yyyy.mm.dd hh24:mi:ss') hhmm from dual;
HH HHMM
------------------- -------------------
2004.05.09 11:00:00 2004.05.09 11:17:00
42.CHARTOROWID
将字元资料类型转换为ROWID类型
SQL> select rowid,rowidtochar(rowid),ename from scott.emp;
ROWID ROWIDTOCHAR(ROWID) ENAME
------------------ ------------------ ----------
AAAAfKAACAAAAEqAAA AAAAfKAACAAAAEqAAA SMITH
AAAAfKAACAAAAEqAAB AAAAfKAACAAAAEqAAB ALLEN
AAAAfKAACAAAAEqAAC AAAAfKAACAAAAEqAAC WARD
AAAAfKAACAAAAEqAAD AAAAfKAACAAAAEqAAD JONES
43.CONVERT(c,dset,sset)
将源字串 sset从一个语言字元集转换到另一个目的dset字元集
SQL> select convert('strutz','we8hp','f7dec') "conversion" from dual;
conver
------
strutz
44.HEXTORAW
将一个十六进位构成的字串转换为二进位
45.RAWTOHEXT
将一个二进位构成的字串转换为十六进位
46.ROWIDTOCHAR
将ROWID资料类型转换为字元类型
47.TO_CHAR(date,'format')
SQL> select to_char(sysdate,'yyyy/mm/dd hh24:mi:ss') from dual;
TO_CHAR(SYSDATE,'YY
-------------------
2004/05/09 21:14:41
48.TO_DATE(string,'format')
将字串转化为ORACLE中的一个日期
49.TO_MULTI_BYTE
将字串中的单字节字元转化为多位元组字元
SQL> select to_multi_byte('高') from dual;
TO
--
高
50.TO_NUMBER
将给出的字元转换为数位
SQL> select to_number('1999') year from dual;
YEAR
---------
1999
51.BFILENAME(dir,file)
指定一个外部二进位档
SQL>insert into file_tb1 values(bfilename('lob_dir1','image1.gif'));
52.CONVERT('x','desc','source')
将x栏位或变数的源source转换为desc
SQL> select sid,serial#,username,decode(command,
2 0,'none',
3 2,'insert',
4 3,
5 'select',
6 6,'update',
7 7,'delete',
8 8,'drop',
9 'other') cmd from v$session where type!='background';
SID SERIAL# USERNAME CMD
--------- --------- ------------------------------ ------
1 1 none
2 1 none
3 1 none
4 1 none
5 1 none
6 1 none
7 1275 none
8 1275 none
9 20 GAO select
10 40 GAO none
53.DUMP(s,fmt,start,length)
DUMP函数以fmt指定的内部数字格式返回一个VARCHAR2类型的值
SQL> col global_name for a30
SQL> col dump_string for a50
SQL> set lin 200
SQL> select global_name,dump(global_name,1017,8,5) dump_string from global_name;
GLOBAL_NAME DUMP_STRING
------------------------------ --------------------------------------------------
ORACLE.WORLD Typ=1 Len=12 CharacterSet=ZHS16GBK: W,O,R,L,D
54.EMPTY_BLOB()和EMPTY_CLOB()
这两个函数都是用来对大资料类型栏位进行初始化操作的函数
55.GREATEST
返回一组运算式中的最大值,即比较字元的编码大小.
SQL> select greatest('AA','AB','AC') from dual;
GR
--
AC
SQL> select greatest('啊','安','天') from dual;
GR
--
天
56.LEAST
返回一组运算式中的最小值
SQL> select least('啊','安','天') from dual;
LE
--
啊
57.UID
返回标识当前用户的唯一整数
SQL> show user
USER 为"GAO"
SQL> select username,user_id from dba_users where user_id=uid;
USERNAME USER_ID
------------------------------ ---------
GAO 25
58.USER
返回当前用户的名字
SQL> select user from dual;
USER
------------------------------
GAO
59.USEREVN
返回当前用户环境的资讯,opt可以是:
ENTRYID,SESSIONID,TERMINAL,ISDBA,LABLE,LANGUAGE,CLIENT_INFO,LANG,VSIZE
ISDBA 查看当前用户是否是DBA如果是则返回true
SQL> select userenv('isdba') from dual;
USEREN
------
FALSE
SQL> select userenv('isdba') from dual;
USEREN
------
TRUE
SESSION
返回会话标志
SQL> select userenv('sessionid') from dual;
USERENV('SESSIONID')
--------------------
152
ENTRYID
返回会话人口标志
SQL> select userenv('entryid') from dual;
USERENV('ENTRYID')
------------------
0
INSTANCE
返回当前INSTANCE的标志
SQL> select userenv('instance') from dual;
USERENV('INSTANCE')
-------------------
1
LANGUAGE
返回当前环境变数
SQL> select userenv('language') from dual;
USERENV('LANGUAGE')
----------------------------------------------------
SIMPLIFIED CHINESE_CHINA.ZHS16GBK
LANG
返回当前环境的语言的缩写
SQL> select userenv('lang') from dual;
USERENV('LANG')
----------------------------------------------------
ZHS
TERMINAL
返回用户的终端或机器的标志
SQL> select userenv('terminal') from dual;
USERENV('TERMINA
----------------
GAO
VSIZE(X)
返回X的大小(位元组)数
SQL> select vsize(user),user from dual;
VSIZE(USER) USER
----------- ------------------------------
6 SYSTEM
60.AVG(DISTINCT|ALL)
all表示对所有的值求平均值,distinct只对不同的值求平均值
SQLWKS> create table table3(xm varchar(8),sal number(7,2));
语句已处理。
SQLWKS> insert into table3 values('gao',1111.11);
SQLWKS> insert into table3 values('gao',1111.11);
SQLWKS> insert into table3 values('zhu',5555.55);
SQLWKS> commit;
SQL> select avg(distinct sal) from gao.table3;
AVG(DISTINCTSAL)
----------------
3333.33
SQL> select avg(all sal) from gao.table3;
AVG(ALLSAL)
-----------
2592.59
61.MAX(DISTINCT|ALL)
求最大值,ALL表示对所有的值求最大值,DISTINCT表示对不同的值求最大值,相同的只取一次
SQL> select max(distinct sal) from scott.emp;
MAX(DISTINCTSAL)
----------------
5000
62.MIN(DISTINCT|ALL)
求最小值,ALL表示对所有的值求最小值,DISTINCT表示对不同的值求最小值,相同的只取一次
SQL> select min(all sal) from gao.table3;
MIN(ALLSAL)
-----------
1111.11
63.STDDEV(distinct|all)
求标准差,ALL表示对所有的值求标准差,DISTINCT表示只对不同的值求标准差
SQL> select stddev(sal) from scott.emp;
STDDEV(SAL)
-----------
1182.5032
SQL> select stddev(distinct sal) from scott.emp;
STDDEV(DISTINCTSAL)
-------------------
1229.951
64.VARIANCE(DISTINCT|ALL)
求协方差
SQL> select variance(sal) from scott.emp;
VARIANCE(SAL)
-------------
1398313.9
65.GROUP BY
主要用来对一组数进行统计
SQL> select deptno,count(*),sum(sal) from scott.emp group by deptno;
DEPTNO COUNT(*) SUM(SAL)
--------- --------- ---------
10 3 8750
20 5 10875
30 6 9400
66.HAVING
对分组统计再加限制条件
SQL> select deptno,count(*),sum(sal) from scott.emp group by deptno having count(*)>=5;
DEPTNO COUNT(*) SUM(SAL)
--------- --------- ---------
20 5 10875
30 6 9400
SQL> select deptno,count(*),sum(sal) from scott.emp having count(*)>=5 group by deptno ;
DEPTNO COUNT(*) SUM(SAL)
--------- --------- ---------
20 5 10875
30 6 9400
67.ORDER BY
用于对查询到的结果进行排序输出
SQL> select deptno,ename,sal from scott.emp order by deptno,sal desc;
DEPTNO ENAME SAL
--------- ---------- ---------
10 KING 5000
10 CLARK 2450
10 MILLER 1300
20 SCOTT 3000
20 FORD 3000
20 JONES 2975
20
20 SMITH 800
30 BLAKE 2850
30 ALLEN 1600
30 TURNER 1500
30 WARD 1250
30 MARTIN 1250
30 JAMES 950
68, to_char(sal,’L
69，sys_context
1. terminal 当前会话客户所对应的终端的标识符
2. lanuage ：语言
3. db_name 当前数据库名称
4. nls_date_format 当前会话客户多对于的日期格式
5. session user :当前会话客户所对应的数据库用户名
6. current_schema当前会话客户所对于的默认方案名
7. host返回数据库所在的主机名称
通过该函数 可以查询一些重要信息，比如你怎么在使用那个数据库
Select sys_context(‘userenv,’db_name’) from dual;
70日期函数
to_char(sysdate,'q') 季
to_char(sysdate,'yyyy')年
to_char(sysdate,'mm')月
to_char(sysdate,'dd')日
to_char(sysdate,'d')星期中的第几天
to_char(sysdate,'DAY')星期几
to_char(sysdate,'ddd')一年中的第几天
extract(year from query_cxrq)年度
extract(month from query_cxrq)月份
extract(day from query_cxrq)日
日期间隔天数
两个日期间的天数(日期相减得天数)
select floor(sysdate - to_date('20020405','yyyymmdd')) from dual;
round()是四舍五入，不是取整。
ceil()是进一，floor()是去尾，都是返回整数。
trunc()也是去尾，但是可以指定小数位数，不指定即为取整，所以也是对的。
71，打印输出一句话 输出gangge
set serveroutput on –打开输出选项
begin
dbms_output.put_line('gangge');
end;
DECODE函数是ORACLE PL/SQL是功能强大的函数之一，目前还只有ORACLE公司的SQL提供了此函数，其他数据库厂商的SQL实现还没有此功能。DECODE有什么用途呢？ 先构造一个例子，假设我们想给智星职员加工资，其标准是：工资在8000元以下的将加20％；工资在8000元以上的加15％，通常的做法是，先选出记录中的工资字段值? select salary into var-salary from employee，然后对变量var-salary用if-then-else或choose case之类的流控制语句进行判断。 如果用DECODE函数，那么我们就可以把这些流控制语句省略，通过SQL语句就可以直接完成。如下：select decode(sign(salary - 8000),1,salary*1.15,-1,salary*1.2,salary from employee 是不是很简洁？ DECODE的语法：DECODE(value,if1,then1,if2,then2,if3,then3,...,else)，表示如果value等于if1时，DECODE函数的结果返回then1,...,如果不等于任何一个if值，则返回else。初看一下，DECODE 只能做等于测试，但刚才也看到了，我们通过一些函数或计算替代value，是可以使DECODE函数具备大于、小于或等于功能。
decode()函数使用技巧
·软件环境:
1、Windows NT4.0+ORACLE
2、ORACLE安装路径为:C:\ORANT
·含义解释:
decode(条件,值1,翻译值1,值2,翻译值2,...值n,翻译值n,缺省值)
该函数的含义如下:
IF 条件=值1 THEN
RETURN(翻译值1)
ELSIF 条件=值2 THEN
RETURN(翻译值2)
......
ELSIF 条件=值n THEN
RETURN(翻译值n)
ELSE
RETURN(缺省值)
END IF
· 使用方法:
1、比较大小
select decode(sign(变量1-变量2),-1,变量1,变量2) from dual; --取较小值
sign()函数根据某个值是0、正数还是负数，分别返回0、1、-1
例如:
变量1=10，变量2=20
则sign(变量1-变量2)返回-1，decode解码结果为“变量1”，达到了取较小值的目的。
2、表、视图结构转化
现有一个商品销售表sale，表结构为:
month char(6) --月份
sell number(10,2) --月销售金额
现有数据为:
200001 1000
200002 1100
200003 1200
200004 1300
200005 1400
200006 1500
200007 1600
200101 1100
200202 1200
200301 1300
想要转化为以下结构的数据:
year char(4) --年份
month1 number(10,2) --1月销售金额
month2 number(10,2) --2月销售金额
month3 number(10,2) --3月销售金额
month4 number(10,2) --4月销售金额
month5 number(10,2) --5月销售金额
month6 number(10,2) --6月销售金额
month7 number(10,2) --7月销售金额
month8 number(10,2) --8月销售金额
month9 number(10,2) --9月销售金额
month10 number(10,2) --10月销售金额
month11 number(10,2) --11月销售金额
month12 number(10,2) --12月销售金额
结构转化的SQL语句为:
create or replace view
v_sale(year,month1,month2,month3,month4,month5,month6,month7,month8,month9,month10,month11,month12)
as
select
substrb(month,1,4),
sum(decode(substrb(month,5,2),'01',sell,0)),
sum(decode(substrb(month,5,2),'02',sell,0)),
sum(decode(substrb(month,5,2),'03',sell,0)),
sum(decode(substrb(month,5,2),'04',sell,0)),
SELECT SYSDATE FROM dual 顯示系統當前日期
select name from dual where name like '%$%%' escape'$'// 自定義字符串，name里包含%的數據，并定義了$? ??字符。
函數
lower() select lower(name) from dual;-- name 都小?
substr() select substr(name,2,4) from dual --把name字段從2開始截取 截取4個字符
chr() select chr(68)from dual 把字段轉換成A
ascii() select ascii('A') from dual 把A轉換成ascii值
to_char() select to_char (12.356,'999,99') from dual;--12.35
round() select round(12.356,'999,99') from dual;--12.36 四折五入
select round(23,4565,2)from dual 四舍五入到第二位
函數：
sum() 計算和
avg() 平均數
count() 統計列的行數量
max() 最大值
min() 最小值
常用變量類型
binary_integer：證書 主要用來計數而不是用來表示字段類型
number ：數字類型
char：定義長字符串類型
varchar2： 變長字符串類型 最大4k
date：日期類型
long：長字符串，最長
boolean：布?類型 可以取值為false ，true，null 。null參與運算不確定，所以最好生命變量時就要賦值
變量的聲明(變量聲明最好用v開頭，：=用來給變量賦值 用||連接字符串)
declare
v_temp number(1);
v_count binary_integer:=0
v_sal number(7,2):=4000.00
v_date date:=sysdate
v_pi constant number(3,2):=3.14
v_valid boolean:=false
v_name varchar2(20) not null :='ynp'
begin
dbms_output.put_line('v_temp is'||v_temp)
end
變量的聲明使用%type屬性
declare
v_empno number(4)
v_empno2 emp.empno%type -- v_empno2的數據類型與emp表中empno的數據類型相同
v_empno3 v_empno2%type -- v_empno3的數據類型與v_empno2數據類型相同
begin
dbms_output.put_line('test')
end
函數練習
SELECT ASCII('A') FROM DUAL
SELECT CHR('65') FROM DUAL
select sysdate from dual
SELECT ADD_MONTHS(SYSDATE,12) S FROM DUAL --加月份
SELECT LAST_DAY(SYSDATE) D FROM DUAL --日期的最後一天
SELECT TRUNC(SYSDATE,'mm') FROM DUAL --本月的第一天 比如現在是7月份 那日期就是01-7月-10
SELECT TRUNC(SYSDATE,'yy') FROM DUAL --本年的第一天 比如現在是2010年 那日期就是01-1月-10
SELECT MONTHS_BETWEEN('19-3月-2000','19-12月-1999') FROM DUAL --計算相隔月份
SELECT TO_CHAR(SYSDATE,'DAY DDSPTH,MONTH,YYYY','NLS_date_LANGUAGE=ENGLISH') DAY FROM DUAL --把日期轉換成英文格式的
select to_char(sysdate,'ddd') from dual --今天本年中的第多少天
select floor(sysdate - to_date('20100701','yyyymmdd')) from dual --兩個日期中間的間隔天數
SELECT SUBSTR('duchenggang',3,5) from dual --截取字符串
select replace('duchenggang','cheng','') FROM DUAL --字符串替換
SELECT UPPER('duchenggang') from dual
SELECT lower('DUCHENGGANG') from dual
SELECT INITCAP('duchenggang') FROM DUAL --把字符串的第一個字母大寫
SELECT instr('duchenggang','en',1,1) FROM DUAL --搜索指定字符串的中字符的位置 返回數值
select TRIM('s' from 'string') from dual
SELECT TRIM(' duchenggang ') FROM DUAL --去掉左右兩邊的空格
select LTRIM(' gao qian jing') from dual
select RTRIM('gao qian jing ') from dual
SELECT CONCAT('021-','1141')||'FDFDFDF' FROM DUAL --拼接字符串
SELECT POWER(2,2) FROM DUAL --N的n次方
SELECT LENGTH('gang') FROM DUAL
select lpad('duchenggang',12,'?') from dual --用？填充左邊的值
select rpad('duchenggang',14,'?') from dual
SELECT lpad(rpad('gao',10,'*'),17,'*') from dual
SELECT ceil(3.5415927) from dual --進一
SELECT ROUND(3.5415926) from dual --去尾進一
select floor(3.5415926) from dual --去尾
select trunc(3.5415926) from dual --去尾，可以指定小數的位數，不指定即為整數
select NEXT_DAY('19-7月-2010','星期一 ') from dual --給出date和星期x算出下星期的時間
select to_char(trunc(sysdate,'hh'),'yyyy.mm.dd hh24:mi:ss') hh,to_char(trunc(sysdate,'mi'),'yyyy.mm.dd hh24:mi:ss') hhmm from dual --trunc(date,fmt)按照給定的要求給日期截斷 如果fmt='mi'表示保留分
select greatest('11','12','13') from dual --返回一組運算式中的最大值 即比較字源的編碼大小
select least('11','12','13') from dual; --返回一組運算式中的最中最小值
select soundex('DAWES') DAWES, SOUNDEX('DAWS') DAWS ,SOUNDEX('DAWSON') DAWSON FROM DUAL --返回一個與給定的字符串讀音相同的字符串
select min(all sal) from tableName --表示對所有值求最小值
select min(distinct sal) from tableName --表示對不同的值求最小值，即相同的只取一次
select user from dual --返回當前登錄用戶的名字
select userenv('isdba') from dual --查看當前用戶是否是dba 是就返回true
select userenv('language') from dual ---當前用環境變量數 select userenv('terminal') from dual ---返回終端或者機器的標誌
TRUNCATE table tableName --快速清除一個表中的數據
--IF..THEN..ELSE語法
IF condition THEN
STATEMENTS 1;
STATEMENTS 2;
............
ELSE
STATEMENTS 3;
STATEMENTS 4;
............
END IF;
--例子1.1
CREATE OR REPLACE FUNCTION test_1(A NUMBER,B NUMBER)
RETURN NUMBER IS G NUMBER;
BEGIN
IF A>B then
g:=A;
ELSE
G:=B;
END IF;
RETURN G;
END test_1;
--執行過程
SELECT test_1(2,4) FROM DUAL
--LOOP循環語法 即do while循環
--把1—50循環插入到數據庫
DECLARE
V_loop BINARY_INTEGER:=1;
BEGIN
INSERT INTO tb1(first_name) VALUES(V_loop);
V_loop:=V_loop+1;
EXIT WHEN V_loop>50; --當道v_loop插到50時退出循環
END
END;
--FOR循環語法例子
DECLARE
BEGIN
FOR bianliang in reverse tiaojian
END
END;
--1.2把全年的日期循環插入到數據庫中的某一列
CREATE TABLE inc_date(year DATE)
DECLARE
BEGIN
FOR i IN REVERSE 0..364
INSERT INTO inc_date SELECT TRUNC(SYSDATE,'YYYY')+i FROM dual;
END
END ;
SELECT * FROM inc_date
DELETE FROM inc_date
--1.3 把1-50插入到數據庫中的某一列
declare
begin
for i in reverse 1..50 loop
insert into tb1(first_name) values(i);
end loop;
end;
--複製表結構
create table y as select * from emp where 1=2
--複製表結構和表的數據
create table y as select * from emp where 1=1
--給表添加列
alter table tb1 add v_date date
--刪除列
alter table tb1 drop column vs_date
--修改列 把v_date 列的date型修改為varchar2()
alter table tb1 modify(v_date VARCHAR2(20))
select * from tb1
--創建過程
CREATE OR REPLACE PROCEDURE pro_p1( V_test VARCHAR2)
IS
BEGIN
EXCEPTION
END
--創建函數 --注意函數有返回值
CREATE OR REPLACE FUNCTION fun_f1( V_test VARCHAR2) RETURN VARCHAR2 IS V_test2 varchar2(20);
IS
BEGIN
EXCEPTION
END
--創建包
CREATE OR REPLACE PACKAGE sp_package1 is
PROCEDURE pro_update_sal(name varchar2,newSal number);
FUNCTION fun_annual_income(name varchar2) return number;
end;
--創建包體
create or replace PACKAGE body sp_package1 is
PROCEDURE pro_update_sal(name varchar2,newSal number)
is
begin
update emp set sal=newSal where ename=name;
end;
function fun_annual_income(name varchar2)
return number is
annual_salary number;
begin
select sal*12+nvl(comm,0) into annual_salary from emp where ename=name;
return annual_salary;
end;
end;
--調用包裏面的過程和函數
cell sp_package1.pro_update_sal('SCOTT',120);
cell sp_package1.fun_annual_income('SCOTT');
------------------------------------
--創建觸發器(觸發器有12種 )
--1.1
CREATE OR REPLACE TRIGGER tri_t1
BEFORE INSERT ON EMP
FOR EACH ROW
BEGIN
END;
--練習
--函數
CREATE OR REPLACE FUNCTION find_area(vlength in number,vwidth in number)
return number
as
declare
varea number;
BEGIN
varea:=vlength * vwidth;
return varea;
end;
drop function find_area
select find_area(10,30) from dual
DECLARE
v_in number:=1;
v_out number:=2;
v_inOut number:=3;
BEGIN
DBMS_OUTPUT.PUT_LINE('Before calling modeTest:');
dbms_output.put_line('v_in='||v_in||'v_out='||v_out||'v_inOut='||v_inOut);
end;
CREATE OR REPLACE PROCEDURE ParameterLength(
p_parameter
p_parameter
BEGIN
p_parameter1:='abcdefghijklm';
p_parameter2:=12.3;
END ParameterLength;
declare
v_Variable1 varchar2(40);
v_Variable2 number(7,3);
BEGIN
ParameterLength(v_Variable1,v_Variable2);
END;
truncate table tb1
create sequence sq_q1
start with 1
increment by 1
ALTER TABLE TB1 modify(first_name number)
SELECT * FROM TB1
declare
v_loop number:=1;
begin
loop
insert into tb1(first_NAME) values(v_loop);
v_loop:=v_loop+1;
exit when v_loop>20;
end loop;
end;
truncate table tb1
select * from tb1
begin
for i in REVERSE 21..50 loop
insert into tb1(first_name) values(i);
end loop;
end;
--分頁存儲過
--創建一個包
create or replace package tespackage as
type test_cursor is ref cursor;
end tespackage;
create OR REPLACE PROCEDURE sp_fengye
(
tableName in varchar2,--參數1表名
pagesize in number,
pagenow in number,
myrows out number,--總記錄數
mypagecount out number --總頁數
p_cursor out testpackage.test_cursor; --返回記錄集
)
is
--定義部份
--定義sql語句字符串
v_sql varchar(2000);
--定義兩個整數
v_begin number:=(pagenow-1)*pagesize+1;--從哪裡開始取值
v_end number:=pagenow*pagesize
BEGIN
v_sql:='select * from (select t1.ename,rownum rn from(select * from '||tableName||' ordey by sal) t1 where rownum<='||v_end||') where rn>='||v_begin;
--把遊標和sql語句關聯起來
open p_cursor for sql;
--計算myrows 和mypagecount的值
--組織一個sql
v_sql:='select count(*) from '||tableName
--執行sql 并把返回值付給myrows；
execute immediate v_sql into myrows;
--計算mypagecount
if mod(myrows,mypagesize)=0 then
mypagecount=myrows/pagesize;
else
mypagecount=myrows/pagesize+1;
end if;
--關閉遊標
close p_cursor;
end;
select to_char (12.356,'$99,999,9999') from dual
select * FROM EMP
select count( DISTINCT ename)from emp
select deportno, avg(salary)
from emp
where salary > 1200
group by deportno
having avg(salary) > 3000
order by avg(salary) asc
select * from dept
select * from emp
where salary in (select max(salary) from emp group by deportno)
select v.* from(select rownum r, ename,dept_name
from emp e
full join dept d
on e.deportno=d.dept_no) v where r in(1,2,3) and ename='Mark'
select salary
from emp
where salary not in
(select distinct (e1.salary) from emp e1, emp e2 where e1.salary < e2.salary)
select distinct (e1.salary) from emp e1, emp e2 where e1.salary < e2.salary
select distinct (e1.salary) from emp e1
select TABLESPACE_NAME from all_tables where table_name='EMP'
--遊標返回查詢的多條語句
TRUNCATE TABLE TB1
select * from tb1
create OR REPLACE PROCEDURE pro_test10 is
CURSOR c IS
SELECT * FROM EMP;
V_EMP c%rowtype;
begin
open c;
loop
fetch c into v_emp;
exit when(c%notfound);
INSERT INTO tb1(first_name,NAME) VALUES(1,v_emp.ename);
end loop;
close c;
end pro_test10;
--執行過程
begin
pro_test10;
end;
--通過遞規寫樹
--創建表并插入數據
create table article (
id number primary key,
cont varchar2(4000),
pid number,
isleaf number(1),
alevel number(2)
)
insert into article values(1,'螞蟻大象大戰',0,0,0);
insert into article values(2,'螞蟻被打趴下',1,0,1);
insert into article values(3,'螞蟻也不好過',2,1,2);
insert into article values(4,'瞎說',2,0,2);
insert into article values(5,'么有有瞎說',4,1,3);
insert into article values(6,'怎么可能',1,0,1);
insert into article values(7,'怎么沒有可能',6,1,2);
insert into article values(8,'可能行很大的',6,1,2);
insert into article values(9,'大象進醫院了',2,0,2);
insert into article values(10,'螞蟻是護士',9,1,3);
