分类： Oracle
2006-04-17 14:38:39
Upper(au_fname)转成大写;
Where条件下是区分大小写的;可以用lower;upper;initcap进行界定;
这就是需要引入大小写转换的原因吧!^0^
第一章
数字系列
number,pls_integer和binary_integer此三种数字类型
number类型的变量可以存储整数或实数值
而binary_integer或pls_integer类型的变量仅可以存储整数
number(p,s) P是精度,S是刻度范围.精度是数值中所有的数字位的个数,而刻度范围是
小数点右边数字位的个数.
还有等价的类型是:dec,decimal,double precision,float,numeric,real
BINARY_INTEGER 这种数据类型用来存储带符号的整数值.
算术运算不能直接在NUMBER上进行.如果要使用数字进行计算,NUMBER也必须要转换为二进制类型.如果表达式中有NUMBER类型,PL/SQL会自动进行转换,并在必要时将结果转换回NUMBER;
字符系列
varchar(l)最大长度是32767 一个varchar2数据库列公能存储4000字节.
Long可以存2GB字节. Clob 可以存储4GB的字节.
Varchar是以字节为单位而不是以字符为单位的;
实际的数据是根据数据库所采用的字符集进行存储的.字符集可以是单字节字符集如ASCLL或EBCDIC code page 500,也可以是变长多字节字符集如Unicode
char变量的最大长度是32767
原始系列
原始类型中的类型用来存储二进制数据。如果有必要的话，Oracle会自动将字符变量在字符集之间进行转换.
日期/区间系列
日期/区间系列有三种类型:date, timestame和interval 主要是说明这三种类型的区别;
PL/SQL中的复合类型有记录、表（包括嵌套表和索引表）和数组。
Select * from kong.authors where au_id like ‘1%’;--因跟其无匹配性我先建立表;
使用%TYPE
属性而不是将变量类型硬性编码.这个属性被其他在表列引用或者另一个变量上;
V_roomed classes.room_id%type
V_roomed1 v_rommid%type --此语句也是合法的;因为它可以为null的值
Create table authors
(au_id int)
tablespace kong;
select t1.stor_id,t1.stor_name,t2.qty
from kong.store t1,kong.sales t2
where t1.stor_id=t2.stor_id;
SQL PLUS数字栏位以居右排列;
字符和日期以居左的方式排列;的方式来进行排列对齐;
在select 中可以进行算术的运算;选择+ / - /* /
更新update kong.authors
Set address=null where au_id like ‘1%’;-- 更新一行数据条件为以1开始的显示为空白;
Select address|| ‘kong’ from kong.authors –当中的||为连接字符串;栏的显示为”address || ‘kong’
Where au_id like ‘1%’;
改变栏位的头/别名
select au_id author_id from kong.authors; -- 改变栏位的显示; 在au_id 和author_id当中加一个as 所得到结果集是一样的;
双引号的用法: “”起到引用的作用.不作大小写的改变;
select au_lname||’ ’||au_fname from kong.authors;--串起两个字符串! 是对实际中的输出;
literal 文字,照片上的字,无夸张的;
select au_id,’’’s name is ‘, au_lname||’ ‘||au_fname from kong.author – ‘s name is 代表引入的.
产生如下结果:
648-92-1872 ‘s name is blotchet-halls Reginald –对应的方式应该很清楚你的意思了;
压缩掉结果集中的重复函;
distinct 清楚的,明显的,截然不同的,独特的;
select distinct quy from employees --显示一行中的不重复
PROMPT classes table... –此处说明的意思,显示,无实际的意思;
DROP TABLE classes CASCADE CONSTRAINTS; --删除表之间的约束/关联
CREATE TABLE classes (
department CHAR(3),
course NUMBER(3),
description VARCHAR2(2000),
max_students NUMBER(3),
current_students NUMBER(3),
num_credits NUMBER(1),
room_id NUMBER(5),
CONSTRAINT classes_department_course
PRIMARY KEY (department, course),
CONSTRAINT classes_room_id
FOREIGN KEY (room_id) REFERENCES rooms (room_id)
); -- 新建一个表时系统会自动默认到系统表空间;
第二章
CREATE SEQUENCE student_sequence
START WITH 10000
INCREMENT BY 1; --此句是定义一个表中的一个字段在下面的列中的增加;
Restricting and sorting data
Oracle引入字符串是区别大小写的.
select * from students where first_name like 'S%' –注明当中是一个like
当中的条件有:
between … and …大于等于且小于等于的两个值;
in (set) 离散的最值不是连
select * from students where current_credits in('4','8');
like 匹配一个字符
is null
_(下划线匹配一个字符)
%(百分号匹配一个或多个字符)
逻辑条件:
and
select employee_id,last_name,job_id,salary from employees
where salary>=10000 and job_id like ‘%main%’
or
not
select * from authors
where address is not null; --显示非空的栏位;
order by 基于某个或者是某些栏位进行排序,默认的方式为升序;它是按照ascll的方式进
升顺排列的;
按照降顺的方式进行排序是在后面加desc ;asc
第三章
single-row functions(单行函数)
单行对单行的结果集;
Number
Data
Conversion
General
Character
multiple-row functions(多行函数)
多行对单行的结果集;
select sum(qty) from sales
group by stor_id;(聚合求合的结果集)
单行函数只能对一个表达式或者是
函数就是用来计算一个值;
过程的主要目的是用来完成一个动作;
lower (‘转换为小写)
upper(‘sql course’) –转换为大写
initcap (‘sql course’) --第一个转换为大写,其余的为小写
SQL> select lower(first_name),upper(first_name),initcap(first_name) from students;
LOWER(FIRST_NAME) UPPER(FIRST_NAME) INITCAP(FIRST_NAME)
-------------------- -------------------- --------------------
scott SCOTT Scott
scott SCOTT Scott
margaret MARGARET Margaret
joanne JOANNE Joanne
manish MANISH Manish
patrick PATRICK Patrick
timothy TIMOTHY Timothy
barbara BARBARA Barbara
david DAVID David
ester ESTER Ester
rose ROSE Rose
function result
concat (‘hello’,’world’) helloworld
substr(‘helloworld’,1,5) hello 第一个字符的正负决定你是否从左还是从右;
负数就是从右边开始
length(‘helloworld’) 10
instr(‘helloword’,’w’) 6
lpad(salary,10,’*’) *****24000 指定插入的值;星号是可以指定的;
rpad(salary,10,’*’) 24000***** 在后面插入;这里所有位数相加是10
trim(‘h’ from ‘helloworld’) elloword (压缩在字符串中字符和字符串)
Number Functions -- 数字函数
Round (45.926, 2) -- 45.93 (四舍五入) ;负数也是向左
Trunc(45.926,2) -- 45.92(不考虑四舍五入的问题)
Mod(1600,300) -- 100 (求余)
SQL> select round(45.926,2),trunc(45.926,2) from dual;
ROUND(45.926,2) TRUNC(45.926,2)
--------------- ---------------
45.93 45.92
SQL> select round(45.92,-1) from dual;
ROUND(45.92,-1) ――小数的正负
---------------
50
Ｍouths_between(’01-SEP-95’,’11-JAN-94)
Add_months(’11-jan-94’,6)
Next_day(’01-sep-95’,’Friday’)
Last_day(’01-feb-95’) 当中的说明只是针对于月来说明的;
也可以对月份等进入四舍五入的方式；^0^
转换函数!
第四章
Display Data From Multiple Table
什么相等连接;
非等值连接;
要实现外连接的话.要在等值连接的当中使用+号做出;
假定我们要连接n张表;我们的连接条件是n-1个;
第五章 Aggregating Data using Group Functions(使用组函数);最多的还是叫做聚合函数!
Column 列
select sum(id) from students; (合)
AVG(ID) 平均值
MAX
MIN
STDDEV
VARIANCE
Select sum(id) from students group by major –分组求合
在where 条件下面是不允许引入聚合函数的;
1 select sum(id),major from students group by major --分组的意思!
2* order by major desc 排序是以升序还是降顺的方式;
在where
count的用法;
select count(*) from students; --聚合函数,计算这个表上有多少行;
count(major) 此为聚合函数;它会忽略字段中的空值;其意义是计算major栏位的有效值有多少行!
Having子句的作用,过滤作用.
句法如下:
select column,group_function(column)
from table
[where condition]
[group by group_by_expression]
[having group_condition]
[order by column];
SELECT avg(current_credits),major FROM STUDENTS group by major having avg(current_credits)>7
第六章 Sub Queries(子查询)
例子1: select * from students where current_credits>(select avg(current_credits) from students);
其where 子句中舍有一个子查询;这就是不同么!^0^
子查询的语法就是用括号括起来的select 语句;
如果子查询返回的结果集为多行,则如何处理其结果集呢?
重复执行子查询;(关联子查询的方式)
在使用子查询语句时不得使用order by语句
这样可以把子查询分为单值子查询和多值子查询(多行子查询会出问题)
多值处理的话有别的方式(IN/ANY/ALL)
IN 运算符是毫无意义的;
如果子查询中的结果集为空;则where子句没有行被选中;
一般使用join
第七章 producing readable output with isql*plus
(最主要替换变量的作用)
web isqlplus的替换变量的方式;
select * from sales where stor_id=&a
对于字符和日期型变量,变理两边则放上单引号!
select * from sales where stor_id like ’&a’
每一个参数都可以是变化的.
第八章 Manipulation Date(操作数据)
inert into table [(olumn[,…column…])] values (value,[….value…])
字符函数时要用单引号进行介定.
显示的给一个空的值;NULL可以作为一个空值
日期的填充格式可以是别的样子; 月日当中有一个逗号; 例子:FEB 3 1999
这个也可以是替换变量;
例子:” insert into employee (id) values (&id);如果是字符型变量记得&号两边加上引号.
把查询的结果送一张表中.
Insert into sales_reps(id,name,salary,commission_pct)
Select employee_id,last_name,salary,commission_pct from employee where job_id like ‘%erp%’;
Create table test1 as select * from authors where 1=2
这个动作是抄authors的表结构呀!
数据更新:update sales set qty=10 where stor_id=7066;
update子句也可以引用子查询语句,子查询结果必须返回是唯一的,不然一定出错;
alter table test1
add constraint ch_1 check(au_lname like ‘k%’);
建立的约束条件; column必须是大写的K开始的;
添加一个栏位的话可以不给括弧
数据删除
也可以将子查询建立其中;
alter table stores
add constraint pk_1 primary key(stor_id);
删除表:
Delete from employees
Where department_id=(select department_id from departments
Where department_name like ‘%public%’)
Alter table sales
Add constraint fk_1 foreign key(stor_id) references store(stor_id);建立参照主键的外键
Create table test2
(int ini,
lname varchar(20) default ‘unknown’)
insert into test2
values(1,default) --此default值与前面相关连.
MARGE合并
Marge into table_name table_alias
Using (table|view|sub_query) alias
No (join condition)
When matched then
Update set
Col1=col_val1,
Col2=col2_val
When not matched then
Insert (column_list)
Values(column_vales)
例子:
Merge into copy_emp c
Using exployees e
on (c.employee_id=e.employee_id)
When matched then
Update set
c.first_name=e.first_name,
c.last_name=e.last_name
……
c.department_id=e.department_id
when not matched then
insert values(e.employee_id,e.first_name,e.last_name,e.email,e.phone_number,e.hire_date,e.job_id,e.salary,e.commission_pct,e.manager_id,e.department_id);
--说明,上面的结果很简单,条件为: (c.employee_id=e.employee_id)
savepoint 回滚的段
回滚段的作用是实现读一致性!
第九章 creating and Managine Tables
table(表)
非标准的命名方法是用双引号选取table的表名!
创建表的用户具有创建表的权限而且具有创建存储空间的权限;
有一个用户名拥有者的写法;
select * from kong.sales; --kong是它的owner
view (视图)
sequence(顺序)
index(索引)
synonym
查看此用户下的数据对象
select * from user_tables; 当中是可以替换的ALL与DBA
select * from user_object;
DBA所有
ALL可以访问
USER自已拥有的
Varchar
As subquery
子查询….
Create table tt3
As
Select * from kong.authors
Desc tt3 --显示表的结构 结果集也是跟其authors表一样的
--其实这也是一个表的拷的动作;
alter table tt2
add (fname varchar2(20) default ‘unknown’,
address varchar2(30) null);
添加表的内容
alter table dept80
drop column job_id
set unuset --此方式的使用方法
alter table tt2
set unused column fname;
alter table tt2
drop unused columns; --如果删除表的结构要较长时间用unused 集中之后再删除
改变名
rename tt3 to tt2; --表已经被重新命名了;
truncate --是一个DDL command 在做表的删除动作时不做日志记录以及回滚等动作; 提高效率;不可以被回滚;
delete 可以被后悔
conmment on table authors
is ‘these are authors information’ --注释的动作
all_col_comments
user_col_comments
all_tab_comments
all_tab_comments
第十章 Including Constraints(约束条件)
The following constraint types are valid:
--- NOT NULL
--- UNIQUE 行的唯一
---PARMARY KEY
---FOREIGN KEY
---CHECK 确认cloumn的value要值的详细情况如:
alter table test3
add constraint ck_test3_1 check(name like ‘k%’);
--确认value 的值是以K开头的.不然不许添回.
以上五种是对栏位的约束.
Unique 是实现行的唯一性.
约束条件可以是针对column或者表的
约束条件可以是作为表一级的结构存在.
Create table test1
(id int not null,
lname varchar(20), --定义栏位不能为空
fname varchar(20),
constraint uk_test1_1 unique(lname,fname)); --唯一的约束条件 ,这种组合是唯一的.
主键是自动创建索引的
外键称之为值表,外键的值只能为主键表的内容. 外键取的值只能是主键已经有的值.
删除约束条件
alter table employees
drop constraint emp_manager_fk
如果删除主键,外键极连的也可以将其删除.
Alter table departments
Drop primary key cascade;
也可以暂时关闭
alter table authors
desable constraint pk_authors;
相反:alter table authors
enable constraint authors
alter table test1
drop (pk) cascade constraints;
alter table test1
drop (pk,fk,col1) cascade constraints;
查看constraint的条件.
Select constraint_name,constraint_type,search_condition from user_constraints
Where table_name=’employee’
Constraint_name 约束名称
Constraint_type 查询类型
Search_codition 查询条件
Select constraint_name,column_name
from user_cons_columns where table_name=’employees’;
第十一章 creating Views(创建视图)
视图本身是一个逻辑结构,视图可以看到数据,但数据都保存在底成表中.视图相当于一个眼镜.通过它可以看到东西,但这些东西事实上它不在里面.
Create/(create or replace) view testview1
As
Select stor_id,qyt from sales;
这样做了之后可以通过select * from testview1;查看sales;
为什么要使用view方法;(权限问题;) view 是一种虚拟的组合.
Simple views complex views
Create force view testview4
As
Select * from ttt --TTT这个视图不存在但也可以创建成功
Create view testview5
As
Select stor_id,sum(qty) as sumqty from sales
Group by stor_id --必须计算的栏位要用别名
更必数据视图
create or replace view testview1
as
select * from sales;
发生数据改变必须满足条件
create or replace view testview1
as
select * from sales
where qty>10;
with check option; --验证在视图中改变的条件选项;
constraint 基表 --满足基表的约束条件;
update testview1
set qty=5 where stor_id_7066;
with read only; --可以加上只能图的视图选项
drop view view --视图可以被删除
TOP-N
Select qty,rownum from (select * from sales order by qty desc)
Where rownum<5;
第十二章 Other Database Objects(其它的几个数据对象)
sequence index 同义词(HAHA)
create sequence sequence
[increment by n]
[start with n]
[{maxvalue n | nomaxvalue}]
[{minvalue n | nominvalue}]
[{cycle | nocycle}]
[{cache n | nocache}];
create sequence testse1
increment by 1
start with 1; --建立一个排序从1开始.每次增加1;
查找select sequence_name,min_value,max_value,increment_by,last_number from user_sequences;
Drop sequence dept_deptid_seq; --删除掉sequence
INDEX(索引,可以加速数据的查询,是独立于表的物理结构,减慢数据的写;)
Create index testindex1
On authoers(au_id); --建立索引
索引的2-4%(这个是挺好的)索引对数据读有好处,对数据写有坏处.
Create index testindex2
On authors(upper(au_fname));
Select * from authors
Where upper(au_fname) like ‘B%’; --如何利用索引.我所建的条件是匹配我建立在索引里的表的
Drop index testindex2 --删除索引
缩短数据对像的连子符.
Create public synonym t1
For kong.sales; --建立的同义词t1等于kong.sales;
公有的与私有的.public代表公有; 不代public的话,代表私有的.
第十三章 Controlling User Access(控制用户存取)
Privileges 特权的.
系统权限大概有100多种.
Create user kxf
by kxf_001; --创建用户是kxf 口令是kxf_001,此时是不进行数据库的连接
grant create session to kxf; --给权限创建实例给到用户;如果不创建实例给用户,此用户将不能做连接之动作;
…… create table to kxf --给权限创建表给到用户
…… create sequence to kxf --Sequence到用户,
…… create view to kxf --view 到用户
…… create procedure to kxf -- procedure 到用户
role------是一个命了名的权限的集合;role的作用简化授传的动作以及过程;
创建role
create role manager;
给role权限
grant create table,create view to manager
将权role manager 给user
grant manager to dehaan,kochhar;
改变用户的口令
alter user kxf identified by kxf_002
grant select on authors to kxf;
with grant option --把权限给你,而且你可以把权限给别人;
grant update(stor_id,qty) on sales to kxf;
第十四章 Using set Operations
Union/union all –-进行排序而且对重复结果进行压缩的;
Select * from employee
Union
Select * from employee; --进行重复性的排序等动作
Union all 显示所有.
Intersect --显示交集
Select stor_id,qty from sales where qty>20
Intersect
Select stor_id,qty from sales where stor_id like ‘7%’; --显示这两个条件的交集
Minus --减掉交集.
Select stor_id,qty from sales where qty>20
minus
