数据类型：字符类型：char(标准通用拉丁字符),nchar(汉字等其他字符),varchar2(长度可变字符),nvarchar2,long;
数字类型：number(通用),integer,float
日期和时间：date,timestamps(分秒，时区)
行：rowid(逻辑地址),urowid（逻辑地址，内存地址）;
二进制:raw(size)(原始二进制数据),long raw,blob(二进制大型对象；最大4G字节),clob(字符大型对象),nclob,bfile;
3设置远程测试：tnsping datebasename；远程连接:sqlplus
name/password@datebasename;
4.创建表空间：create tablespace test
datafile 'test.dbf' size 10m autoextend on next 2m
maxsize unlimited
logging
permanent
extent management local autoallocate
blocksize 8k
segment space management manuaL;//段空间
5．创建用户并连接： create user "TEST" identified by "TEST"
default tablespace TEST
temporary tablespace TEMP
quota unlimited on TEST
quota unlimited on TEMP
grant "connect" to test//分配基本权限。
conn test/test;
6．重设用户密码：scott/tiger为默认用户，alter user scott identified by tiger;
解锁：alter user scott account unlock;
7.sql脚本的执行：@路径/filename.sql;
8.创建表：create table t1(c1 type 约束,c2 type 约束（not null,unique,check,primary key））
9.查询：select distinct c1 from t1 where 条件group by c1 having by 子条件order by c1;
drop删除表10．连接字符串：select c1 (as可省) 列1 ||c2 from t1;
11.单行函数：select lower(c1) from t1；upper全大写，initcap第一个字母大写，length；
12．Select Sysdate from dual(系统默认的空表)显示系统时
间,months_between(date,date);
14.nvl(c1,0)把字段为空的值换为0，nvl2(c1,1,0)不空的为1，空的值为0；
15.操作符：比较：=，<>,>=,<=,>,<；逻辑：and,or,not
其他：in/not in,between..and..,is null/is not null,like，exists/not exists；
Eg:select count(distinct c1) as 种类from t1 where c1 like ‘%l%’(模糊查询如
m_n)(c1 between 10 and 100) group by c1 order by c1 desc，c2 desc（不写就默认asc）
16．聚合函数：count(*)返回所有行的记录数,min(c1),max(c1),sum(c1),avg(c1); Eg:select c1,count(*) from t1 group by c1 having by count(*)>1;(having不能用变量申明)；等价于select c1,count(*) as cn from t1 group by c1 where cn>1;
17．声明主键和check：一.create table t1(c1 primary key)
二.创建表的时候指定限制的名称:create table t1(c1 constraint pk_emp primary key);
三：create table t1(emp_no number not null ,constraint pk_emp primary
key(emp_no))；为已经存在的表增加主键:alter table t1 add constraint pk_emp2 primary key (c1)；
创建check:create table t1(budget number(7),constraint CHK_PROJ
check(budget>10000 and budget<1000000))
18.创建外键:create table t1(dept_no varchar2(4) not null, constraint fk_emp foreign key (dept_no) references t2(dept_no)，对已经存在表创建外键:alter table t1
add constraint foreign_work_emp foreign key(c1) references t2(c1)；
删除一个外键alter table t1  drop constraint foreign_work_emp；
19.删除表：drop table t1；查看回收站:show recyclebin；清空回收站 purge recyclebin;
从回收站中恢复数据表:flashback table t1 to before drop;
20.更新数据操作：插入记录：insert into t1(c1,c2)values(‘’,’’)；
插入一字段:insert into t1(c1,c2) select c3,c4 from t2;
更新记录：update t1  set c1=’’ where ;
删除记录：delete from t1 where;truncate table t1;drop table t1;
21.合并查询：intersect(select * intersect select *),union(去掉空),union all(包括空),minus（减集）;
22.多表查询:select * from t1,t2;(笛卡尔集c1行*c2行)；select * from t1 join t2
using(num)；等价于select * from t1 inner join t2 =t2.no);join逻辑连接，
只连接有联系的字段，full join物理机械连接，out join,left out join(右边变成空),right out
join;
23．控制语句：select emp_no, case project_no
when 'p1' then case when enter_date<to_date('1997-10-1','yyyy-mm-dd')then '
三室'
when enter_date>to_date('1998-10-1','yyyy-mm-dd')then '等两年'//时间非字段时
间型
else '两室一厅'end
when 'p2' then case when enter_date<to_date('1997-10-1','yyyy-mm-dd')
then '三室两厅' when enter_date>to_date('1998-10-1','yyyy-mm-dd')
then '看情况再分' else ' 一室一厅'end
end as 分房情况from works_on；
if then end if,loop end loop,declare type *,while( )loop end loop,case when
then end
24.嵌套查询select c1 from t1 where c2 in(select c2 from t2 where c3=(select c3 from
t3))

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。