Oracle总结+备忘录ITeye - 亚美娱乐

Oracle总结+备忘录ITeye

2019年02月23日14时36分42秒 | 作者: 傲柔 | 标签: 用户,字段名,束缚 | 浏览: 1946

5、conn 用户名/暗码  依据用户名和暗码衔接数据库 假如衔接超级办理员(sys) 则应加上as sysdba;     6、show user; 显现当时衔接的用户     7、select * from tab;  得到当时用户下的一切表     8、desc temp; 检查表结构     9、/  持续履行上一个查询句子        clear scr;  清屏    字符函数    10、select upper(coolszy) from dual; 将小写字母转化成大写,dual 为一虚表    11、select lower(KUKA) from dual; 将大写字母转化成小写    12、select initcap(kuka) from dual; 将首字母大写     13、select concat(Hello, world) from dual; 衔接字符串,但没有||好用select concat(Hello,world) from dual;    14、select substr(hello,1,3) from dual; 截取字符串    15、select length(hello) from dual; 求字符串长度    16、select replace(hello,l,x) from dual; 替换字符串     17、select substr(hello,-3,3) from dual; 截取后三位    数值函数    18、select round(789.536) from dual; 四舍五入,舍去小数    19、select round(789.536,2) from dual; 保存两位小数    20、select round(789.536,-1) from dual; 对整数进行四舍五入    21、select trunc(789.536) from dual; 舍去小数,但不进位    22、select trunc(789.536,2) from dual;    23、select trunc(789.536,-2) from dual;    24、select mod(10,3) from dual; 回来10%3的成果    日期函数    25、select sysdate from dual; 回来当时日期    26、select months_between(sysdate,16-6月 -08) from dual; 回来之间的月数    27、select add_months(sysdate,4) from dual; 在日期上加上月数    28、select next_day(sysdate,星期一) from dual; 求下一个星期一    29、select last_day(sysdate) from dual; 求本月的最终一天    转化函数    30、select to_char(sysdate,yyyy) year,to_char(sysdate,mm),to_char(sysdate,dd) from dual;    31、select to_char(sysdate,yyyy-mm-dd) from dual;    32、select to_char(sysdate,fmyyyy-mm-dd) from dual; 吊销月 日 前面的0    33、select to_char(20394,99,999) from dual; 切割钱 9表明格局    34、select to_char(2034,L99,999) from dual; 加上钱币符号    35、select to_number(123)*to_number(2) from dual;    36、select to_date(1988-07-04,yyyy-mm-dd) from dual;     通用函数    37、select nvl(null,0) from dual; 假如为null,则用0替代    38、select decode(1,1,内容是1,2,内容是2,3,内容是3) from dual; 类似于 switch...case...    业务处理    39、commit;  提交业务    40、rollback; 回滚业务    41、select rownum from table; 在没一列前面显现行号    42、drop table 表名 cascade constraint         on delete casecade 当父表中的内容被删去后,子表中的内容也被删去    43、desc表名 显现表的结构    44、create user [username] identified by [password] 创立新的用户    45、grant 权限1、权限2...to 用户  给创立用户权限     ex:grant create session to [username] 此刻只能衔接到数据库        grant connect,resource to [username] 此刻权限能满意要求    46、alter user [username] identified by [password] 修正用户暗码    47、alter user [username] password expired 下次登录时提示修正暗码    48、alter user [username] account lock  锁住用户    49、alter user [username] account unlock  解锁锁用户    50、grant select,delete on scott.emp to [username] 把scott下emp表的两个权限给用户    51、revoke select ,delete on scott.emo from [username] 收回权限 

 

二、Oracle基础知识

 

一、挑选行
1. 简略的SELECT 句子
SELECT 字段名1 [AS] 字段名1 解说 FROM table;
2. 处理NULL
NVL函数可把NULL转化成其它类型的符号
编程技巧: NVL函数在多条件含糊查询的时分比较有用
NVL函数可回来多种数据类型:
回来日期 NVL(start_date,2002-02-01)
回来字符串 NVL(title,no title)
回来数字 NVL(salary,1000)
3. 运用SQL*PLUS(1)
SQL desc table; 显现表结构
SQL select * from tab; 检查用户下一切的表
SQL set pause on; 能够使许多成果集在用户按“Enter”(回车)后翻页
SQL set pagesize 100; 设定SQL句子回来成果集一页的行数100, 默许值是14
SQL set linesize 100; 设定SQL句子回来成果集一行的宽度100, 默许值是80
4. SQL*PLUS里规矩字段的显现格局
规矩数字的显现格局
SQL column 字段名 format 99999999999;
SQL column 字段名 format 999,999,999,999;
SQL column 字段名 format a数字 [word_wrapped];
规矩long字符的显现宽度
SQL set long 200;
规矩字段名的显现内容
SQL column 字段名 heading 字段名显现内容;
SQL set heading off; 查询时不显现字段称号
规矩字段的对齐方向
SQL column 字段名 justify [left | right | center];
铲除字段的格局
SQL column 字段名 clear;
5. SQL*PLUS里规矩字段的显现格局比方
SQL column last_name heading Employee|Name format a15;
SQL column salary justify right format $99,999.99;
SQL column start_date format a10 null Not Hired;
阐明:假如start_date为null, 显现字符串Not Hired
6. 判断题(T/F)
(1). SQL command are always held in sql buffer. [T]
(2). SQL*PLUS command assit with query data. [T]
5

J2EE @ zxw
SQL*PLUS指令只操控SELECT成果集的显现格局及操控文件.只需SQL指令能拜访数据库.
二、束缚挑选行
1. 按指定的规矩排序
SELECT expr FROM table [ORDER BY {column, expr} [ASC | DESC] ];
默许的排序是ASC升序(由小到大)
还能够ORDER BY 字段名的方位[1]| [2] ASC| DESC;
2. 用WHERE束缚挑选行(1)
比较操作符 = = = != ^= 与NULL比较不能用上面的比较操作符 ANY SOME
ALL
SQL操作符 BETWEEN … AND… IN LIKE IS NULL
NOT BETWEEN … AND… NOT IN NOT LIKE IS NOT NULL
逻辑操作符 AND OR NOT
3. 用WHERE束缚挑选行(2)
比较次序(能够用括号改动它们的次序)
(1). = = = in like is null between
(2). and
(3). Or
4. LIKE操作
% 零到恣意多个字符 _ 一个字符
例如: 字段名 like M% 字段名 like %m% 字段名 like job_
假如要找含下划线的字符, 要加反斜线 例如:字段名 like %X/_Y% escape /
5. 日期字段的比较
举例:
日期字段 between to_date(2001-12-12,YYYY-MM-DD) and to_date(2002-02-01,YYYY-MM-DD)
日期字段 to_date(2001-12-12,YYYY-MM-DD) and日期字段 =
to_date(2002-02-01,YYYY-MM-DD);
6. 不能用到索引的比较操作符
     IS NULL
IS NOT NULL
LIKE %m%
三、单行函数
1. 数字函数
ABS 取绝对值 POWER 乘方 LN 10为底数取0
SQRT 平方根 EXP e的n次乘方 LOG(m,n) m为底数n取0
数学运算函数:ACOS ATAN ATAN2 COS COSH SIGN SIN SINH TAN TANH
CEIL 大于或等于取整数
FLOOR 小于或等于取整数
MOD 取余数
ROUND(n,m) 按m的位数取四舍五入值假如round(日期): 正午12今后将是明日的日期.    
round(sysdate,Y)是年的第一天
TRUNC(n,m) 按m的位数取小数点后的数值假如trunc(日期), 确省的是去掉时刻
6

J2EE @ zxw
2. 字符函数
CHR 按数据库的字符集由数字回来字符
CONCAT(c1,c2) 把两个字符c1,c2组合成一个字符, 和 || 相同
REPLACE(c,s,r) 把字符c里出现s的字符替换成r, 回来新字符
SUBSTR(c,m,n) m大于0,字符c从前面m处开端取n位字符,m等于0和1相同,
m小与0,字符c从后边m处开端取n位字符
TRANSLATE(c,f1,t1) 字符c按f1到t1的规矩转化成新的字符串
INITCAP 字符首字母大写,其它字符小写
LOWER 字符悉数小写
UPPER 字符悉数大写
LTRIM(c1,c2) 去掉字符c1左面出现的字符c2
RTRIM(c1,c2)
TRIM(c1,c2) 去掉字符c1左右两头的字符c2
LPAD(c1,n,c2) 字符c1按拟定的位数n显现缺乏的位数用c2字符串替换左面的空位
RPAD(c1,n,c2)
3. 日期函数
ADD_MONTHS(d,n) 日期值加n月
LAST_DAY  回来当月的最终一天的日期
MONTHS_BETWEEN(d1,d2) 两个日期值间的月份,d1 d2 回来负数
NEXT_DAY  回来日期值下一天的日期
SYSDATE 当时的体系时刻
DUAL是SYS用户下一个空表,它只需一个字段dummy
4. 转化函数(1)
TO_CHAR(date,日期显现格局)
TO_CHAR(number) 用于显现或报表的格局对齐
TO_DATE(char,日期显现格局)
TO_LOB 把long字段转化成lob字段
TO_NUMBER(char) 用于核算或许比较巨细
4. 转化函数(2)
to_date里日期显现格局
YYYY 年 YEAR YYY YY Y
Q 季度
MM 月 MONTH MON
W 星期 (week of month) WW, IW (week of year)
(阐明:周计是按ISO规范,从的星期数到后边七天为一周,纷歧定是从周一到周日)
DD 日 DAY DY
HH24 小时 HH12 HH
MI 分钟
SS 秒
假如想固定日期的显现格局能够在数据库的参数文件initorasid.ora里新写一行参数     
NLS_DATE_FORMAT=yyyy-mm-dd
hh24:mi:ss能够在UNIX环境变量或许NT的注册表里的设置 NLS_DATE_FORMAT=yyyy-mm-dd     
7

J2EE @ zxw
hh24:mi:ss
4. 转化函数(3)
假如想固定日期的显现格局能够用alter session指令改动
SQL alter session set nls_date_format=yyyy-mm-dd hh24:mi:ss;
它的效果次序如下:
initialization parameter
Environment variable
ALTER SESSION command
4. 转化函数 (4)
to_char(number)里数字显现格局
9 数字位
0 数字前面补0 to_char(-1200,00000.00)
. 小数点的方位
, 符号方位的逗号 用在数字显现格局的左面
L 依据数据库字符集加钱银符号 to_char(-1200,L9999.99)
B 把数字0显现为空格,用在数字显现格局的右边
MI 右边的负数符号 to_char(-1200,9999.99MI)
PR 括起来的负数 to_char(-1200,9999.99PR)
EEEE 用指数办法表明 to_char(-1200,9999.99EEEE)
5. 输入字符,回来数字的函数
instr(c1,c2) 字符c2出现在c1的方位, 不出现, 回来0, 常用于含糊查询
length  按数据库的字符集,算出字符c的长度,跟数据库的字符集有关, 一个汉字长度为1
6. 有逻辑比较的函数NVL(EXPR1, EXPR2)函数
解说: IF EXPR1=NULL RETURN EXPR2
ELSE RETURN EXPR1
DECODE(AA0V10R10V20R2....)函数
解说: IF AA=V1 THEN RETURN R1
IF AA=V2 THEN RETURN R2
..…
ELSE
RETURN NULL
举例: decode(id,1,dept sale,2,dept tech)
四、从多个表里选取数据记载
1. 数据表间的衔接
简略的衔接语法:
SELECT 字段名1, 字段名2, …… FROM 表名1, [表名2, ……]
WHERE 表名1.字段名 = 表名2. 字段名 [ AND ……] ;
SELECT 字段名1, 字段名2, …… FROM 表名1, [表名2, ……]
WHERE 表名1.字段名 = 表名2. 字段名(+) [ AND ……] ;
有(+)号的字段方位主动补空值
衔接的分类:
等于的衔接 =
不等于的衔接 != BETWEEN … AND … IN 留意IN和OR不能一重用
8

J2EE @ zxw
外衔接 有一个字段名(+) , 没有满意的条件补空值
自衔接 同一个表自己跟自己衔接 例如找重复记载
2. 数据表间的衔接比方
删去table_name表里字段名email重复的记载:
SQL delete from table_name t1
where t1.rowid
(select min(rowid) from table_name t2
where t1.email = t2.email
group by email
having count(email)
找到手机用户的效劳区域:
SQL select a.handphoneno,nvl(c.name,null),a.totalscore
from topscore a,chargeoperator cc,chargeoperatorinfo c
where substr(a.handphoneno,1,7)=cc.hpnohead(+)
and cc.chargetype=c.chargetype(+)
order by a.totalscore desc;
3. 数据表间的衔接技巧
衔接N个表, 需求N-1个衔接操作
被衔接的表最好建一个单字符的别号, 字段名前加上这个单字符的别号
BETWEEN .. AND.. 比用 = AND = 要好
衔接操作的字段名上最好要有索引
衔接操作的字段最好用整数数字类型
有外衔接时, 不能用OR或IN的比较操作
4. 怎么剖析和履行SQL句子
写多表衔接SQL句子时要知道它的剖析履行方案的状况.
Sys用户下运转@/ORACLE_HOME/sqlplus/admin/plustrce.sql 发生plustrace人物
Sys用户下把此人物赋予一般用户 SQL grant plustrace to username;
一般用户下运转@/ORACLE_HOME/rdbms/admin/utlxplan.sql
发生plan_table
SQL set time on; 阐明:翻开时刻显现
SQL set autotrace on; 阐明:翻开主动剖析核算,并显现SQL句子的运转成果
SQL set autotrace traceonly; 阐明:翻开主动剖析核算,不显现SQL句子的运转成果
接下来你就运转测验SQL句子,看到其剖析核算成果了。
一般来讲,咱们的SQL句子应该防止大表的全表扫描。
SQL set autotrace off; 阐明:封闭主动剖析核算
五、调集函数
常常和group by一同运用
1. 调集函数列表
AVG (DISTINCT | ALL | N) 取平均值
COUNT (DISTINCT | ALL | N | expr | * ) 核算数量
MAX (DISTINCT | ALL | N) 取最大值
MIN (DISTINCT | ALL | N) 取最小值
SUM (DISTINCT | ALL | N) 取算计值
9

J2EE @ zxw
STDDEV (DISTINCT | ALL | N) 取偏差值,假如组里挑选的内容都相同,成果为0
VARIANCE (DISTINCT | ALL | N) 取平方偏差值
2. 运用调集函数的语法
SELECT column, group_function FROM table
WHERE condition GROUP BY group_by_expression
HAVING group_condition ORDER BY column;
3. 运用count时的留意事项
SELECT COUNT(*)  FROM table;
SELECT COUNT(常量) FROM table;
都是核算表中记载数量,假如没有PK后者要好一些
SELECT COUNT(all 字段名) FROM table;
SELECT COUNT(字段名) FROM table;
不会核算为NULL的字段的数量
SUM,AVG时都会疏忽为NULL的字段
4. 用group by时的束缚条件
SELECT字段名不能随意, 要包括在GROUP BY的字段里
GROUP BY后ORDER BY时不能用方位符号和别号
束缚GROUP BY的显现成果, 用HAVING条件
5. 比方
SQL select title,sum(salary) payroll from s_emp
where title like VP% group by title
having sum(salary) 5000 order by sum(salary) desc;
找出某表里字段重复的记载数, 并显现
SQL select (duplicate field names) from table_name
group by (list out fields) having count(*)
6. 判断题(T/F)
(1) Group functions include nulls in calculations [F]
(2) Using the having clause to exclude rows from a group calculation [F]
解说:
Group function 都是疏忽NULL值的 假如您要核算NULL值, 用NVL函数
Where句子在Group By前把成果集扫除在外Having句子在Group By后把成果集扫除在外
六、子查询
1. 查询句子能够嵌套
例如: SELECT …… FROM (SELECT …… FROM表名1, [表名2, ……] WHERE 条件) WHERE 条件2;
2. 何处可用子查询?
当查询条件是不确定的条件时
DML(insert, update,delete)句子里也可用子查询
HAVING里也可用子查询
3. 两个查询句子的成果能够做调集操作
例如:
并集UNION(去掉重复记载)
并集UNION ALL(不去掉重复记载)
10

J2EE @ zxw
差集MINUS,
交集INTERSECT
4. 子查询的留意事项
先履行括号里边的SQL句子,一层层到外面
内部查询只履行一次
假如里层的成果集回来多个,不能用= = =等比较符要用IN.
5. 子查询的比方(1)
SQL select title,avg(salary) from s_emp
group by title Having avg(salary) =
(select min(avg(salary)) from s_emp
group by title);
找到最低平均薪酬的职位称号和薪酬
5. 子查询的比方(2)
子查询能够用父查询里的表名
这条SQL句子是对的:
SQL select cty_name from city where st_code in
(select st_code from state where st_name=TENNESSEE and
city.cnt_code=state.cnt_code);
阐明:父查询调用子查询只履行一次.
6.取出成果集的80 到100的SQL句子
ORACLE处理每个成果集只需一个ROWNUM字段标明它的逻辑方位,
而且只能 用ROWNUM 100, 不能用ROWNUM 80。
以下是经过剖析后较好的两种ORACLE获得成果集80到100间的SQL句子
( ID是仅有关键字的字段名 )
句子写法:
SQL select * from (
( select rownum as numrow, c.* from (
select [field_name,...] from table_name where 条件1 order by 条件2) c)
where numrow 80 and numrow = 100 )
order by 条件3;
七、在履行SQL句子时绑定变量
1. 接纳和界说变量的SQL*PLUS指令
ACCEPT
DEFINE UNDEFINE

2. 绑定变量SQL句子的比方(1)
SQL select id, last_name, salary from s_emp where dept_id = department_number;
Enter value for department_number: 10
old 1: select id, last_name, salary from s_emp where dept_id= department_number;
new 1: select id, last_name, salary from s_emp where dept_id= 10
SQL SET VERIFY OFF | ON;能够封闭和翻开提示承认信息old 1和new 1的显现.
3. 绑定变量SQL句子的比方(2)
SQL select id, last_name, salary
from s_emp
where title = job_title;
Enter value for job_title: Stock Clerk
11

J2EE @ zxw
SQL select id, last_name, salary
from s_emp
where hiredate to_date( start_hire_date,YYYY-MM-DD);
Enter value for start_hire_date : 2001-01-01
把绑定字符串和日期类型变量时,变量外面要加单引号
也可绑定变量来查询不同的字段名
输入变量值的时分不要加;等其它符号
4. ACCEPT的语法和比方
SQL ACCEPT variable [datatype] [FORMAT] [PROMPT text] [HIDE]
阐明: variable 指变量名 datatype 指变量类型,如number,char等 format 指变量显现格
式 prompt text 可自界说弹出提示符的内容text hide 躲藏用户的输入符号
运用ACCEPT的比方:
ACCEPT p_dname PROMPT Provide the department name:
ACCEPT p_salary NUMBER PROMPT Salary amount:
ACCEPT pswd CHAR PROMPT Password: HIDE
ACCEPT low_date date format YYYY-MM-DD PROMPT“Enter the low date range(YYYY-MM-DD):”
4. DEFINE的语法和比方
SQL DEFINE variable = value
阐明: variable 指变量名 value 指变量值
界说好了变良值后, 履行绑定变量的SQL句子时不再提示输入变量
运用DEFINE的比方:
SQL DEFINE dname = sales
SQL DEFINE dname
DEFINE dname = “sales” (CHAR)
SQL select name from dept where lower(name)= dname;
NAME
-
sales
sales
SQL UNDEFINE dname
SQL DEFINE dname
Symbol dname is UNDEFINED
5. SQL*PLUS里传递参数到保存好的*.sql文件里
SQL @ /路径名/文件名 参数名1[,参数名2, ….]
SQL start /路径名/文件名 参数名1[,参数名2, ….]
留意事项:
一次最多只能获取9个 变量, 变量称号只能是从 1, 2到 9
变量名后不要加特其他完毕符号
假如在SQL*PLUS里要把 符号保存在ORACLE数据库里,要修正sql*plus环境变量define
SQL set define off;
八、概述数据模型和数据库规划
1. 体系开发的阶段:
Strategy and Analysis
Design
Build and Document
Transition
Production
12

J2EE @ zxw
2. 数据模型
Model of system in clients mind
Entity model of clients model
Table model of entity model
Tables on disk
3. 实体联系模型 (ERM)概念
ERM ( entity relationship modeling)
实体 存有特定信息的方针和事情 例如: 客户,订单等
特点 描绘实体的特点 例如: 名字,电话号码等
联系 两个实体间的联系 例如:订单和产品等
实体联系模型图表里的约好
Dashed line (虚线) 可选参数 “may be”
Solid line (实线) 必选参数 “must be”
Crows foot (多线) 程度参数 “one or more”
Single line (单线) 程度参数 “one and only one”
4. 实体联系模型比方
每个订单都有必要有一个或几个客户
每个客户或许是一个或几个订单的申请者
5. 实体联系的类型
1:1 一对一 例如: 的士和司机
M:1 多对一 例如: 乘客和飞机
1:M 一对多 例如: 职工和技术
6. 校对实体联系的准则
特点是单一值的, 不会有重复
特点有必要依存于实体, 要有仅有符号
没有非仅有特点依赖于另一个非仅有的特点
7. 界说结构时的留意事项
削减数据冗余
削减完整性束缚发生的问题
承认省掉的实体,联系和特点
8. 完整性束缚的要求
Primary key 主关键字 仅有非NULL
Foreign key 外键 依赖于另一个Primary key,或许为NULL
Column 字段名 契合界说的类型和长度
Constraint 束缚条件 用户自界说的束缚条件,要契合工作流要求
例如: 一个出售人员的提成不能超越它的基本薪酬
Candidate key 候选主关键字 多个字段名可组成候选主关键字, 其组合是仅有和非NULL的
9. 把实体联系图映射到联系数据库目标的办法
把简略实体映射到数据库里的表
把特点映射到数据库里的表的字段, 标明类型和注释
把仅有符号映射到数据库里的仅有关键字
把实体间的联系映射到数据库里的外键
13

J2EE @ zxw
其它的考虑:
规划索引,使查询更快
树立视图,使信息有不同的出现面, 削减杂乱的SQL句子
方案存储空间的分配
从头界说完整性束缚条件
10. 实体联系图里符号的意义
PK 仅有关键字的字段
FK 外键的字段
FK1,FK2 同一个表的两个不同的外键
FK1,FK1 两个字段一同组成一个外键
NN 非null字段
U 仅有字段
U1,U1 两个字段一同组成一个仅有字段
九、创立表
1. ORACLE常用的字段类型
ORACLE常用的字段类型有
VARCHAR2 (size) 可变长度的字符串, 有必要规矩长度
CHAR(size) 固定长度的字符串, 不规矩长度默许值为1
NUMBER(p,s) 数字型p是位数总长度, s是小数的长度, 可存负数
最长38位. 不行位时会四舍五入.
DATE 日期和时刻类型
LOB 超长字符, 最大可达4G
CLOB 超长文本字符串
BLOB 超长二进制字符串
BFILE 超长二进制字符串, 保存在数据库外的文件里是只读的.
数字字段类型位数及其四舍五入的成果
原始数值1234567.89
数字字段类型位数 存储的值
Number 1234567.89
Number 12345678
Number  错
Number(9,1) 1234567.9
Number(9,3) 错
Number(7,2) 错
Number(5,-2) 1234600
Number(5,-4) 1230000
Number(*,1) 1234567.9
2. 创立表时给字段加默许值 和束缚条件
创立表时能够给字段加上默许值
例如 : 日期字段 DEFAULT SYSDATE
这样每次刺进和修正时, 不必程序操作这个字段都能得到动作的时刻
14

J2EE @ zxw
创立表时能够给字段加上束缚条件
例如: 非空 NOT NULL
不允许重复 UNIQUE
关键字 PRIMARY KEY
按条件检查 CHECK (条件)
外键 REFERENCES 表名(字段名)
3. 创立表的比方
CREATE TABLE DEPT(
EPTNO NUMBER(2) CONSTRAINT PK_DEPT PRIMARY KEY,
DNAME VARCHAR2(14),
LOC VARCHAR2(13)) ;
CREATE TABLE region(
ID number(2) NOT NULL PRIMARY KEY,
postcode number default 0 NOT NULL,
areaname varchar2(30) default NOT NULL);
4. 创立表时的命名规矩和留意事项
表名和字段名的命名规矩:有必要以字母最初,能够含符号A-Z,a-z,0-9,_,$,#
巨细写不区别
不必SQL里的保存字, 必定要用时可用双引号把字符串括起来.
用和实体或特点相关的英文符号长度有必定的束缚
留意事项:
建表时能够用中文的字段名, 但最好仍是用英文的字段名
创立表时要把较小的不为空的字段放在前面, 或许为空的字段放在后边
建表时假如有仅有关键字或许仅有的束缚条件,建表时主动建了索引
一个表的最多字段个数也是有束缚的,254个.
5. 束缚名的命名规矩和语法
束缚名的命名规矩束缚名假如在建表的时分没有指明,体系命名规矩是SYS_Cn(n是数字)
束缚名字符串的命名规矩同于表和字段名的命名规矩
6. 运用束缚时的留意事项
束缚里不能用体系函数,如SYSDATE和其他表的字段比较
能够用本表内字段的比较
想在业务处理后, 做束缚的检查
SQL alter session set constraints deferred.
7. 由实体联系图到创立表的比方 s_dept
前提条件:已有region表且含仅有关键字的字段id
SQL CREATE TABLE s_dept
(id NUMBER(7)
CONSTRAINT s_dept_id_pk PRIMARY KEY,
name VARCHAR2(25)
CONSTRAINT s_dept_name_nn NOT NULL,
region_id NUMBER(7)
CONSTRAINT s_dept_region_id_fk REFERENCES region (id),
CONSTRAINT s_dept_name_region_id_uk UNIQUE(name, region_id));
8. 较杂乱的创立表比方
15

J2EE @ zxw
SQL CREATE TABLE s_emp (
id NUMBER(7)   CONSTRAINT s_emp_id_pk PRIMARY KEY,
last_name VARCHAR2(25)  CONSTRAINT s_emp_last_name_nn NOT NULL,
first_name VARCHAR2(25),
userid VARCHAR2 CONSTRAINT s_emp_userid_nn NOT NULL
CONSTRAINT s_emp_userid_uk UNIQUE,
start_date DATE DEFAULT SYSDATE,
comments VARCHAR2(25),
manager_id NUMBER(7),
title VARCHAR2(25),
dept_id NUMBER(7)   CONSTRAINT s_emp_dept_id_fk REFERENCES s_dept(id),
salary NUMBER(11,2),
commission_pct NUMBER(4,2)  CONSTRAINT s_emp_commission_pct_ck CHECK
(commission_pct IN(10,12.5,15,17.5,20))
);
9. 经过子查询建表
经过子查询建表的比方
SQL CREATE TABLE emp_41 AS SELECT id, last_name, userid, start_date
FROM s_emp WHERE dept_id = 41;
SQL CREATE TABLE A as select * from B where 1=2;
只需表的结构.
10. 用子查询建表的留意事项
能够干系多个表及用调集函数生成新表,留意挑选出来的字段有必要有合法的字段称号,且不能重复。
用子查询办法树立的表,只需非空NOT NULL的束缚条件能承继过来,
其它的束缚条件和默许值都没有承继过来.
依据需求,能够用alter table add constraint ……再树立其它的束缚条件,如primary key等.
11. Foreign Key的可选参数ON DELETE CASCADE
在创立Foreign Key时能够加可选参数:
ON DELETE CASCADE它的意义是假如删去外键主表里的内容,子表里相关的内容将一同被删去.
假如没有ON DELETE CASCADE参数,子表里有内容,父表里的主关键字记载不能被删去掉.
12. 假如数据库表里有不满意的记载存在,树立束缚条件将不会成功.
13. 给表创立和删去近义词的比方
SQL CREATE SYNONYM d_sum
2 FOR dept_sum_vu;
SQL CREATE PUBLIC SYNONYM s_dept
2 FOR alice.s_dept;
SQL DROP SYNONYM s_dept;
十、ORACLE里的数据字典
1. 什么是数据字典?ORACLE的数据字典是数据库的重要组成部分之一,它跟着数据库
的发生而发生, 跟着数据库的改动而改动, 体现为sys用户下一切的一些表和视图.
2. 数据字典里存了以下内容:
用户信息
用户的权限信息
一切数据目标信息表的束缚条件核算剖析数据库的视图等
不能手艺修正数据字典里的信息.
16

J2EE @ zxw
3. 常用的数据字典
Dictionary 寄存一切数据表,视图,近义词称号和解说
Dict_columns 数据字典里字段称号的和解说
Dba_users 用户 Dba_tablespaces 表空间
Dba_data_files 数据库的文件 Dba_free_space 闲暇表空间
Dba_rollback_segs 回滚段
User_objects 数据目标 User_constraints 束缚条件
User_sequences 序列号 User_views 视图
User_indexes 索引 User_synonyms 近义词
Session_roles 用户的人物 User_role_privs 用户的人物权限
User_sys_privs 用户的体系权限 User_tab_privs 用户的表级权限
V$session 实时用户状况 V$sysstat 实时体系核算
V$sesstat 实时用户核算 V$sgastat 实时SGA运用
V$locked_object 实时锁 V$controlfile 操控文件
V$logfile 日志文件 V$parameter 参数文件
4. 数据字典的分类
数据字典四大类别
User_ 用户下一切数据库目标
All_ 用户权限规模内一切的数据库目标
Dba_ 一切的数据库目标
V$Content$nbsp; 核算剖析数据库的视图 赋于oem_monitor权限非DBA用户也可查询V$*视图
5. 查询数据字典
SQL select * from dictionary where instr(comments,index)
SQL select constraint_name, constraint_type,
2 search_condition, r_constraint_name
3 from user_constraints
4 where table_name = ‘ table_name;
十一. 操控数据
1 、INSERT(往数据表里刺进记载的句子)
SQL insert into 表名(字段名1, 字段名2, ……) values ( 值1, 值2, ……);
SQL insert into 表名(字段名1, 字段名2, ……) select (字段名1, 字段名2, ……)
from 别的的表名 where 条件;
能够用 符号变量的办法屡次输入记载
快速刺进数据的办法, 一般用于大于128M的数据搬运
SQL insert /*+ append */ into 表名
select * from 别的的用户名 .别的的表名 WHERE 条件;
SQL commit;
留意事项:
用INSERT /*+ APPEND */ 的办法会对target_tablename发生等级为6的独占锁,
假如运转此指令时还有对target_tablename的DML操作会排队在它后边,
对OLTP体系在用的表操作是不合适的。
17

J2EE @ zxw
2. 刺进字符串类型的字段的留意事项:
字符串类型的字段值有必要用单引号括起来, 例如: ’GOOD DAY’
假如字段值里包括单引号’ 需求进行字符串转化, 咱们把它替换成两个 单引号’ ’
字符串类型的字段值超越界说的长度会犯错, 最好在刺进行进行长度校验
‘’ 符号是NULL, user 标明当时用户
日期字段的字段值能够用当时数据库的体系时刻SYSDATE, 准确到秒
用字符串转化成日期型函数TO_DATE(‘2001-08-01’,’YYYY-MM-DD’)
TO_DATE( )还有许多种日期格局, 能够参看ORACLE DOC.
年-月-日 小时:分钟:秒 的格局YYYY-MM-DD HH24:MI:SS
NSERT时最大可操作的字符串长度小于等于4000个单字节,
假如要刺进更长的字符串, 请考虑字段用CLOB类型,
办法借用ORACLE里自带的DBMS_LOB程序包.
3、UPDATE (修正数据表里记载的句子)
SQL UPDATE 表名 SET 字段名1=值1, 字段名2=值2, …… WHERE 条件;
假如修正的值N没有赋值或界说时, 将把本来的记载内容清为NULL,
最好在修正行进行非空校验;
值N超越界说的长度会犯错, 最好在刺进行进行长度校验.
新功能,能够修正子查询后的成果集
比方:SQL update (select * from s_dept) set id=50 where id=60;
4、DELETE (删去数据表里记载的句子)
SQL DELETE FROM 表名 WHERE 条件;
留意:删去记载并不能开释ORACLE里被占用的数据块表空间.
它只把那些 被删去的数据块标成unused.
假如的确要删去一个大表里的悉数记载, 能够用 TRUNCATE 指令, 它能够开释占用的数据块表空间
SQL TRUNCATE TABLE 表名;
此操作不行回退.
5、 SQL句子的分类
数据界说言语(DDL):create、alter、drop(创立、修正结构、删去)(其他:rename)
数据操作言语(DML):insert、delete、select、update(增、删、查、改)(其他:truncate)
数据操控言语(DCL):grant、revoke(授权、收回)、set role
业务操控:commit、rollback、savepoint(其他:lock table、set constraint、set transaction)
审计操控:audit、noaudit
体系操控:alter system 会话操控:alter session
其他句子:comment(添加注释)、explain plan、analyze、validate、call
6、ORACLE里业务操控
Commit 提交业务
Rollback 回退业务
Savepoint 设置断点, 在业务中符号方位, 业务完毕, 断点开释
业务完毕的状况遇到commit或许rollback遇到DDL和DCL句子发现过错,如死锁用户退出
SQL*PLUS体系重启或溃散
7. DML操作的留意事项
18

J2EE @ zxw
以上SQL句子对表都加上了行级锁, 承认完结后,
有必要加上事物处理完毕的指令COMMIT 才干正式收效,
不然改动纷歧定写入数据库里.行级锁也未能得到开释.
假如想撤回这些操作, 能够用指令 ROLLBACK 恢复.
在运转INSERT, DELETE 和 UPDATE 句子前最好预算一下或许操作的记载规模,
应该把它限定在较小 (一万条记载) 规模内,. 不然ORACLE处理这个事物用到很大的回退段.
程序呼应慢乃至失掉呼应. 假如记载数上十万以上这些操作,
能够把这些SQL句子分段分次完结, 其间加上COMMIT 承认事物处理.
过分频频的commit欠好
十二、改动表和束缚条件
1. 改动表的几种状况(1) 运转时会加表级锁
改动表的称号
SQL RENAME 表名1 TO 表名2; SQL ALTER TABLE 表名1 RENAME TO 表名2;
在表的后边添加一个字段
SQL ALTER TABLE 表名 ADD 字段名 字段名描绘
[ DEFAULT expr ][ NOT NULL ][ ,字段名2 ……];
修正表里字段的界说描绘
SQL ALTER TABLE 表名 MODIFY 字段名1 字段名1描绘 [ DEFAULT expr ][ NOT NULL ]
[ ,字段名2 ……]; 记载为空时,能够削减字段长度,
改动字段类型修正DEFAULT值只效果于修正后的INSERT和UPDATE的记载修正NOT NULL
束缚只对现存含非空记载的字段起效果
1. 改动表的几种状况(2) 运转时会加表级锁
删去表里的某个字段
SQL ALTER TABLE 表名 DROP 字段名;
给表里的字段加上/制止/启用束缚条件
SQL ALTER TABLE 表名 ADD | DISABLE | ENABLE CONSTRAINT 束缚名
PRIMARY KEY (字段名1[,字段名2 ……]);
SQL ALTER TABLE 表名 ADD | DISABLE | ENABLE CONSTRAINT 束缚名
UNIQUE (字段名1[,字段名2 ……]);
加仅有关键字或许仅有束缚条件时主动树立索引
阐明:制止仅有关键字和仅有束缚时索引依然存在,能够被运用.
1. 改动表的几种状况(3) 运转时会加表级锁
删去表里的束缚条件
SQL ALTER TABLE 表名 DROP CONSTRAINTS 束缚名 [CASCADE];
会把束缚相关的索引一同删去. CASCADE能一同删去外键的束缚条件.
把表放在或取出数据库的内存区
SQL ALTER TABLE 表名 CACHE;
SQL ALTER TABLE 表名 NOCACHE;
改动表存储的表空间
SQL ALTER TABLE 表名 MOVE TABLESPACE 表空间名 ;
19

J2EE @ zxw
留意: 假如被搬运表空间的表含有索引, 表搬运后索引变得不行用.
咱们要删去旧索引,树立新索引
2. 删去表及表里的数据
删去表
SQL DROP TABLE 表名 [CASCADE CONSTRAINTS];
清空表里的记载
SQL TRUNCATE TABLE 表名;
按时刻清空日志表里的记载,运用从头命名的办法
(应用程序或许有时刻短犯错, 能够挑选在不繁忙的时刻履行)
按本来表A的建表句子创立新表A1,把表A重命名为A2(假如表A上有较频频的DML操作,
会对表加上行级锁,重命名进程用递归的办法循环做,直到DML操作完毕,命名成功).
把创立新表A1重命名为A
历史记载表A2备份或删去
3. 删去表后应该留意的问题
删去表后把表里的索引一同删去.
删去表后会完毕依据它的悬而未决的事物
删去表后依据表创立的views,synonym,stored procedure,stored function依然存在,
但views,synonym变成不合法的. 需求手艺找出它们并删去
假如用了CASCADE CONSTRAINTS会把与它相关的束缚一同删去
此操作不行回退
4. 给表加注释
加注释的语法
SQL COMMENT ON TABLE 表名 | COLUMN表名.字段名 IS ‘text‘
加注释的比方
SQL comment on table s_emp is ‘Enployee information‘;
SQL comment on column s_emp.last_name is ‘‘;
十三、创立序列号
1. 创立序列号里各参数的解说
SQL CREATE SEQUENCE name [INCREMENT BY n]
[START WITH n] [{MAXVALUE n | NOMAXVALUE}]
[{MINVALUE n | NOMINVALUE}] [{CYCLE | NOCYCLE}]
[{CACHE n | NOCACHE}]
INCREMENT BY n 一次增加n 个数字
NOMAXVALUE 缺省值10E+27
NOMINVALUE 缺省值1
NOCYCLE 不循环, 常用于仅有关键字
CACHE n 在内存里缓存n个序列,犯错回退时会丢掉
oracle8i里默许的n是20
序列号的称号一般能够选用“表名_字段名”的命名规矩
2. 刺进主动增加序列号字段的办法
INSERT时假如要用到从1开端主动增加的数字做仅有关键字, 应该先树立一个序列号.
20

J2EE @ zxw
CREATE SEQUENCE 序列号的称号 (最好是表名+序列号符号) INCREMENT BY 1 START WITH 1
MAXVALUE 99999 NOCYCLE NOCACHE;
其间最大的值按字段的长度来定,比方界说的主动增加的序列NUMBER , 最大值为999999
INSERT 句子刺进这个字段值为: 序列号的称号.NEXTVAL
比方: SQL insert into s_dept(id, name, region_id) values (s_dept_id.nextval, finance, 2);
1 row created.
只需运转了序列号的称号. nextval后序列号的称号. currval 才有用才有值.
3. 查询序列号的状况
SQL select sequence_name, min_value, max_value, increment_by, last_number from user_sequences;
其间last_number指的是序列号的下一个值.
4. 改动序列号
SQL ALTER SEQUENCE sequence [INCREMENT BY n] [{MAXVALUE n | NOMAXVALUE}]
[{MINVALUE n | NOMINVALUE}]
[{CYCLE | NOCYCLE}] [{CACHE n | NOCACHE}];
留意: 不能改动它的起始值
假如要改动序列的起始值, 先把序列号删去掉, 再新建一个.
5. 删去序列号
SQL DROP SEQUENCE sequence;
6. 不能用序列号的nextval和currval的当地
视图的查询
有distinct的查询
有group by,having,order by的查询
有子查询的查询
表里的缺省值
十四、创立视图
1. 视图的概念和长处
视图是依据一个或多个表及视图的一些查询句子, 它象显现数据的视窗, 它本身是不存储数据的.
视图能够束缚数据库的拜访, 更好的操控权限
运用户运用简略的查询句子
数据的非依赖性
同一数据的不同表现形式
2. 创立视图的语法
SQL CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW view[(alias[, alias]...)]
AS subquery
[WITH CHECK OPTION [CONSTRAINT constraint]]
[WITH READ ONLY]
参数解说:
FORCE 表不存在时,视图依然能够创立成功
WITH CHECK OPTION 只需契合视图界说的记载才干被刺进或修正
WITH READ ONLY 不允许DML操作
Oracle8i今后创立视图能够用order by
21

J2EE @ zxw
3. 创立修正视图的比方
SQL CREATE OR REPLACE VIEW salvu41 AS SELECT id, first_name FIRST,
last_name LAST, salary MONTHLY_SALARY
FROM s_emp WHERE dept_id = 41;
SQL CREATE VIEW dept_sum_vu (name, minsal, maxsal, avgsal) AS SELECT d.name, MIN(e.salary),
MAX(e.salary),
AVG(e.salary) FROM s_emp e, s_dept d WHERE e.dept_id = d.id GROUP BY d.name;
留意: 假如用select * from table_name创立的视图
table_name的结构改动后 view要重建或compile后才干显现新的字段内容
4. 查询视图的数据字典
SQL set long 1600;
SQL select view_name,text from user_views;
阐明: 能够依据视图text_length来设置set long 数字;
User_updatable_columns视图能查询视图里能被修正的字段
5. 简略和杂乱的视图比照
特 性 简略视图 杂乱视图
表的数量 一个 多个
有函数吗? 没有 有
有分组操作吗? 没有 有
有依据视图的DML操作吗? 有 没有
6. 在视图上能够用DML指令吗?
能够, 但有必定的束缚条件
没有下面的状况, 能够删去view里的记载. group function, group by, distinct
没有上面和下面的状况, 能够修正view里的记载. 字段表达式,
例如: salary*12 含rownum的view
没有上面两种状况, 且view里含基表里一切非空字段的状况, 能够往view里刺进记载.
7. 在视图里运用 WITH CHECK OPTION束缚条件
SQL create or replace view empvu41
as select * from s_emp where dept_id = 41
with check option constraint empvu41_ck;
假如运转下面指令会犯错ora-01402
SQL update empvu41 set dept_id=42 where id=16;
原因: 视图empvu41里规矩只能看部分号为41的记载 修正后会把记载扫除在视图empvu41以外
与它的束缚条件抵触
8. 删去视图
SQL DROP VIEW view_name;
十五、创立索引
1.索引的概念
索引是数据库里的一种数据目标
它运用B*树, hash, bitmap结构直接快速地拜访数据
它和表是分隔寄存的两个实体
索引创立好了后, 由体系主动调用和办理
2. 什么时分创立索引?
22

J2EE @ zxw
主动创立的索引:仅有关键字, 仅有的束缚条件
手艺需求创立的索引:大表查询时, sql句子where后常常用到的字段或字段组合
字段内容不同很大有许多NULL值表很大, 回来记载数较少
3. B*树索引的结构 每个索引由字段值和指针或ROWID组成
4.创立索引的语法
CREATE INDEX 索引名 ON 表名 ( 字段1, [字段2, ……] ) TABLESPACE 表空间名;
5.创立索引的留意事项
创立索引时会加行级独占锁
一个表的索引最好不要超越三个 (特其他大表在外)
最好用单字段索引
索引最好和表分不同的表空间寄存
结合SQL句子的剖析履行状况, 也能够树立多字段的组合索引和依据函数的索引
大表的索引会占用很大的存储空间
不要建仅有的索引, 而应该加仅有的束缚条件
6.查询索引的办法
查询数据字典user_indexes和user_ind_columns
比方:
SQL SELECT ic.index_name, ic.column_name,
2 ic.column_position col_pos,ix.uniqueness
3 FROM user_indexes ix, user_ind_columns ic
4 WHERE ic.index_name = ix.index_name
5 AND ic.table_name = S_EMP;
留意: 数据字典里寄存的字符都是大写的.
7. 不必索引的当地
表很小
where后不常常运用的比较字段
表被频频修正
回来记载数许多
where后含IS NULL /IS NOT NULL/ like ‘%输入符%’等条件
8. 重建索引的语法
ALTER INDEX 索引名 REBUILD TABLESPACE 本来表空间名 NOLOGGING;
定时重建索引能够削减索引的碎片, 更有用地运用表空间.
9. 删去索引
SQL drop index 索引名;
SQL alter table 表名 drop constraint 束缚名;
十六、操控用户拜访
1.权限的类别
体系级权限: 针对整个体系操作的权限
如: 用户名/暗码, 运用表空间的限额等
目标级权限: 针对某个详细object操作的权限
如: 针对某个表, 视图, 表的某个字段的select, update, delete权限
23

J2EE @ zxw
2. 检查当时数据库的用户信息
SQL select username,default_tablespace,temporary_tablespace from dba_users;
检查在线用户信息
SQL select count “number”,username “current username” from v$session group by username;
用户检查自己的缺省表空间SQL select username,default_tablespace from user_users;
3. 创立新用户
SQL create user username identified by password
default tablespace tablespace_name temporary tablespace temp
quota unlimited on tablespace_name
quota 1k on system
[quota 1k on other_tablespace_name ……] ;
给用户赋权限
SQL grant connect, resource to username;
检查当时用户的权限人物
SQL select * from user_role_privs;
检查当时用户的体系权限和表级权限
SQL select * from user_sys_privs;SQL select * from user_tab_privs;
4 、常用的人物及其权限
CONNECT 8 privs 连上Oracle,做最基本操作
RESOURCE 8 privs 具有程序开发最的权限
DBA 114 privs 数据库办理员一切权限
EXP_FULL_DATABASE 5 privs 数据库整个备份输出的权限
IMP_FULL_DATABASE 64 privs 数据库整个备份输入的权限
检查人物明细的体系权限
SQL select * from role_sys_privs;
5、改动老用户 能够改动老用户的暗码, 缺省表空间, 暂时表空间, 空间限额.
SQL alter user username identified by password
default tablespace tablespace_name
temporary tablespace temp
quota unlimited on tablespace_name
quota 1k on system
[quota 1k on other_tablespace_name ……] ;
吊销用户的人物或权限
SQL revoke role_name or priv_name from username;
留意事项
吊销用户的人物dba时, 一同吊销了用户unlimited tablespace的体系权限,
切记要再次赋予resource人物给此用户
SQL grant resource to username;
6、删去用户
假如用户下没有任何数据目标
SQL drop user username;
假如用户下有数据目标
SQL drop user username cascade;
留意事项
假如用户下有含clob,blob字段的表, 应该先删去这些表后,才干用cascade选项彻底删去.
7、人物的概念和办理
24

J2EE @ zxw
人物是命名多个相关权限的组合. 能把它赋于其它的用户或人物咱们能创立人物,
使权限办理更简单一些.
8、赋于体系的权限语法和比方
语法:
SQL GRANT sys_priv TO {user|role|PUBLIC} [WITH ADMIN OPTION];
比方:
SQL GRANT create session TO sue, rich;
SQL GRANT create table To scott, manager;
留意:
假如用WITH ADMIN OPTION经过中心用户赋于的体系权限中心用户删去后, 体系权限依然存在.
9、赋于数据目标级的权限语法和比方
语法:
SQL GRANT object_priv [(columns)] ON object TO {user|role|PUBLIC} [WITH GRANT OPTION];
比方:
SQL GRANT select ON s_emp TO sue, rich;
SQL GRANT update (name, region_id)
ON s_dept TO scott, manager;
留意: 假如用WITH GRANT OPTION经过中心用户赋于的目标权限
中心用户删去后,目标权限就不存在了.
-
-完-
-
弥补:
rownum表中的行号,主动生成的,只能用 , =操作符
rowid用来仅有表明数据库表中的一行
(1)
在oralce上的分页需求用到伪列,只所以这么做,是因为oralce不支持rownum比较
比方说找出第三行纪录,那就是
select last_name, salary
from (select rownum a, b.*
from s_emp b)
where a=3
假如找出第10行到第20行的数据的话,那就有必要用到
select last_name, salary
from (select rownum a, b.*
from s_emp b)
where a 10 and a
这是sql级其他分页,长处是速度快,缺陷是可移植性差;
(2)
COURSEID COURSENAME SCORE

1  java  70
2  oracle  90
3  xml  40
4  jsp 30
5  servlet  80
25

J2EE @ zxw
SQL select courseid, coursename ,score ,decode(sign(score-61),-1,fail,pass) as mark from course_v;
COURSEID  COURSENAME  SCORE  MARK
     
1   java  70  pass
2  oracle   90  pass
3  xml  40   fail
4  jsp   30   fail
5   servlet    80  pass
(3)
现已知道原表
           year       salary
   -
        2000           1000
        2001           2000
        2002           3000
        2003           4000
显现查询成果
           year       salary
   -
        2000           1000
        2001           3000
        2002           6000
        2003           10000
即salary为以前年的薪酬的和;
回答进程如下:
  select b.year,sum(a.salary)
  from test a,test b
  where a.year =b.year
  group by b.year
  order by year

 

 

版权声明
本文来源于网络,版权归原作者所有,其内容与观点不代表亚美娱乐立场。转载文章仅为传播更有价值的信息,如采编人员采编有误或者版权原因,请与我们联系,我们核实后立即修改或删除。

猜您喜欢的文章