oracle总结1

--在Dos命令下进入oracle编辑
sqlplus myOracle/111@ORAC    --myoracle为用户名   111为密码   orac 为公有数据库名 后面不能加;

--连接到某个用户
conn myoracle/111;

----表名为student
----------------------数据定义语言-----------都要带 table ----------------------------
--建立表
create table student
(
stuid number primary key,
stuname varchar2(30),
stuage number,
stubirthday date
);

--建立序列
create sequence studentSeq start with 1
increment by 1 minvalue 1 nomaxvalue nocache nocycle noorder

--删除表
drop table student;

--truncate table 的用法    不能删除表结构
--删除表中的所有内容不能用rollback撤销删除   delete可以
truncate table student;

--添加一列
alter table student add (stuaddress varchar2(50));

--修改一列
alter table student modify (stuaddress number);

----查表结构
desc 表名

--删除列
alter table student drop column stuaddress;

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

--注意 order by 时要指定哪一列， 或几列.   order by 必须在where语句的后面.
--升序方式查
--升序时asc可以不写,因默认为asc
select * from student order by stuage asc;

--降序方式查
select * from student order by stuage desc;

---------利用现有的表创建新表
create table teacher as select * from student

---------利用现有的表创建新表的表结构   当where中的条件不成立时就不会copy表的内容了。
create table teacher1 as select * from student where 1=2

---------我又是如何将其它表结构和我相同的表记录复制过来，怎么做?
----用insert  注意: 字段类型必须匹配.
---1. 插入被复制表的所有记录
insert into teacher1 select * from student
---2. 选择性的复制
--truncate table teacher1　－－我们先将表中的内容删除
insert into teacher1 select *  from student where stuname like 'ch%';
insert into teacher1(stuid,stuage) select stuid, stuage from student where stulevel>8;

--在表中插入数据
insert into student values
(studentSeq.nextval,'bbb',3, to_date('1965-08-23','yyyy-mm-dd'));

insert into student values
(studentSeq.nextval,'ccc',56, to_date('1966-11-23','yyyy-mm-dd'));

 

--查表结构
desc student

--update命令  更新一个列，或多个列  
--注意 update 后面不要加 table 字符
update student set stuage=50 where stuage=33;

--delete命令
--删除某行
delete from student where stuage=23;

---------------------事务控制语言-----------------------------------------
--commit命令
--别忘了提交. 要不数据只会在缓存中。关机就没有了.
commit;

--savepoint命令
savepoint abc;
--保存点类似于标记. 将事务处理划分为较小的部分.  用来标记事务中可以应用的回滚点.
--savepoint 和 rollback 一起用于回滚当前事务的一部分.
--注意： 如果提交了保存点就失效了。 再用rollback就无法返回了。
rollback to abc;
--或者用  rollback to savepoint abc;  同上面的作用是一样的。

---------------------------数据控制语言-----------------------------------------
--数据控制语言为用户提供权限控制命令.
--理解授予的权限可以由所有者随时撤销.
----注意:  可以授予   insert  select   update   delete  但不能授予drop 权限
 
--将myOracle用户中的表student 为abc 用户授予select 的权限.
--在abc用户中只能查看myOracle用户中表student的内容,
--不能做 insett 或 update 或 delete等操作.
grant select on student to abc

--查看myoracle用户中的表student
select * from myoracle.student
--如果此时执行   drop table myoracle.student   将会提示权限不足.
--将myOracle用户中的表student 为abc 用户授予update 的权限.
grant update on student to abc

--在abc用户中更改myoracle用户中的表student
update myoracle.student set stuage=77 where stuage=33

--revoke撤销已授予给用户的权限
-- 注意此处用 from 不是 to
revoke update on student from abc

-------------------SQL操作符------------------------------------

-----算术操作符  + - * /
 alter table student add(stulevel number);

 update student set stulevel=stuid+5;

select stuid, stuage,stulevel, stulevel-stuid abc from student;   --abc为别名
--等同
select stuid, stuage,stulevel, stulevel-stuid as abc from student;

----优先级

select stuid, stuage,stuid*(stuage+stulevel) asname from student;

-------比较操作符
/* 用于比较两个表达式的值 .   
 =,  !=,   <,   >,   <=,   >=,  between... and,    in,   like, is null,
not between,   not like,   is not null
*/

select stuid, stuage from student where stuid*(stuage+stulevel)/100<stuid;

--in 选择列值与表中任何一个值相匹配的行.

select * from student where stubirthday in( '23-8月-66','23-11月-66');

--like  进行模糊匹配
-- "%" 匹配任意个（包括零个)字符
select * from student where stuname like '%e';   --所有以有结尾的字符串
select * from student where stuname like '%n%';
select * from student where stuname like 'wa%';

-- "_" 只匹配一个字符.
select * from student where stuname like '_angjun';

-----------逻辑操作符  and   or    not
--用于组合多个比较运算的结果以生成一个或直或假的结果.

----and
select * from student where  stulevel>16 and stuid<13;

---or
select * from student where stuname like '%e' or stulevel=16;

--not
select * from student where not stuname like '%e' ;
select * from student where not stulevel<18;

------------集合操作符   union(联合)     union all (联合所有)     intersect(交集)     minus(减集)

/*
union(联合)   返回两个查询选定的所有不重复的行.

如何用union操作符将两个 查询结果合并起来，并删除重复的行??
*/

select * from student union select * from teacher;

/*
  union all (联合所有)  合并两个查询选定的所有行, 包括重复的行.
*/
select * from student union all select * from teacher;

select stuname, stuage from student union all select stuname,stuage from teacher;

--查出两个表所有的内容而且以降序的形式显示,如何做?
select * from student union all select * from teacher order by 5 desc;

--如何只显示两列并以升序显示呢？   此处的asc 可以不写.
select stuname,stulevel from student union all select stuname,stulevel from teacher order by 2 asc;

----intersect  返回两个查询都有的行.
select * from student intersect select * from teacher;

select stuname,stulevel from student intersect select stuname,stulevel from teacher order by 2 desc;

--minus(减集)
--查看尚未交付的订单的信息。
select * from teacher minus select * from student;

--连接(||)操作符
--将两个或多个字符串合并在一个字符串，或者将一个字符串与一个数值合并在一起.
select ('供应商'||stuname||'的级别是'||stulevel) from student where stuid>11;
/*结果为：　
供应商chenjie的级别是17
供应商chasfe的级别是18
*/

----操作符的优先级别
算术-->连接-->比较-->not逻辑-->and逻辑-->or逻辑

--*********************************************************************
--******************SQL函数********************************************
--函数接受一个或多个参数并返回一个值.大致划分为单行函数和分组函数  
--*********************************************************************
--注：dual表只有一个数据类型为varchar2(1)的列,而且只包含一个具有值"x"的行.

----------单行函数  也叫标量函数
--分为日期函数,字符函数, 数字函数, 转换函数，其他函数

----1. 日期函数
--add_months(d,n) 其中d 是日期, n表示月数.
--将月份加５  这里可以加 负数  表示减月份
select stubirthday, add_months(stubirthday,5) from student;

--months_between 返回两个日期之间的月数
--months_between(d1,d2)  d1晚于d2则为正数
select stubirthday, months_between(stubirthday,to_date('25-12月-65')) as datemonth from student;

--last_day(d) 返回指定日期当月的最后一天的日期值。
--查系统当前日期
select sysdate from dual;

select last_day(sysdate) as abc from student;
select last_day(sysdate) from dual;
select last_day(to_date('09-12月-06')) from dual;

--round 返回日期值，此日期四舍五入为格式模型指定的单位。
--格式为： round(d,[fmt])   d为日期， fmt是格式模型。 fmt是可选项.

select stubirthday, round(stubirthday,'month') as asName from student;

--  错误的写法  select stubirthday, round(to_date('04-05月-09'),'month') as asName from student
select round(date'04-05-10','month')as abc from dual;    --04-05-10  为年月日

--"day"  则舍入到最靠近的星期日.
select round(date'24-07-05','day')as abc from dual

--next_day 指定下一个星期几的日期
--格式为：  next_day(d,day)

select next_day(date'2006-09-22','星期二') abc from dual;
--或者
select next_day(date'2006-09-22',3) abc from dual;    --1表示星期天

--trunc 将指定日期截断为由格式模型指定的单位日期，与round函数不同的是它只舍不入.
--格式为： trunc(d,[fmt]) 如果不指定 fmt则日期被截断为天.
select trunc(sysdate,'year') from dual;
--如果sysdate是"2006年8月27日" , 则截断后的结果为  "2005年1月1日";
--day 则截到前面的星期日.
select trunc(sysdate,'day') from dual    

select trunc(sysdate) from dual   --显示当天。 截断为天, 日期的时间部分将被截掉.

--extract   提取日期时间类型中的特定部分
--格式： extract(fmt FROM d) d为日期间表达式， fmt是要提取的部分的格式.
--格式的取值可以是 year, month, day, hour, minute, second  此处的格式不用单引号.
select extract(day from date'1999-08-23') asname from dual;
select extract (year from sysdate) asname from dual;

-----------------字符函数----------------
--initcap(char)   首字母大写
select initcap('hello') from dual;
--结果: Hello

--lower  转换为小写
select lower('HELLO') from dual;
--结果： hello

--upper(char)  转换为大写
select upper('hello') from dual;

--ltrim(char,set) 左剪裁
select ltrim('abcddfdsf','abcd') from dual;

--rtrim(char,set) 右剪裁
select rtrim('abasdfsdf','sdf') from dual;

--replace(char,search_str,replace_str) 字符串替换
select replace('jlack and jasdf','j','abcd') from dual;

----instr(char,substr[,pos]) 查找子串位置
select instr('asdfasd','d') from dual;  
--结果: 3

--substr(char,pos,len) 取子字符串
select substr('asdfasd',3,5) from dual;
 --结果： dfasd

--concat(char1,char2)  将两个字符串连接起来
select concat('hello','_Word') from dual;

--CHR 根据ascii 码返回对应的字符
select chr(65) from dual;
--结果： A

 

******************************************
*******************未完***********************
******************************************
******************************************

 

----------分组函数   又称为聚合函数

--avg   返回指定列值的平均值.
select avg(stuid),avg(stuage) from student;

--min 返回指定列中所有值的最小值.
--max 返回指定列中所有值的最大值.

select max(stuid),min(stuage) from student
--sum  总和
select sum(stuid*stuage) from student;

--可以加where条件
select max(stuid),min(stuage) from student where stulevel<17;

--count 为了计算行数可以使用count函数
--为了做测试我们将stulevel 中的一个值设为空(未知)
update student set stulevel=null where stuid=11;

--count(*)  返回所有的行数
select count(*) abc from student;

--count(col_name)   col_name 为列名   函数返回的是非空的行数和
select count(stulevel) as abc from student;

--count(distinct col_name)   统计时除去重复的值. 也不会返回空值
select count(distinct stulevel) abc from student;

--group by 子句，用于将信息表划分为组，按组进行聚合运算。

---------------创建一数生成绩表--------一个学生对应多个学科的分数------------

--建立一个分数的序列 markSeq
create sequence markSeq start with 1 increment by 1 minvalue 1 nomaxvalue nocache nocycle noorder;

create table mark
(
markId number primary key,
stuName varchar2(30),
subject varchar2(30),
stuMark number(4,2)
);

insert into mark values(markSeq.nextval,'wangjun','java',80);
insert into mark values(markSeq.nextval,'wangjun','C++',70);
insert into mark values(markSeq.nextval,'wangjun','oracle',90);

insert into mark values(markSeq.nextval,'chenJie','java',88);
insert into mark values(markSeq.nextval,'chenJie','C++',67);
insert into mark values(markSeq.nextval,'chenJie','oracle',99);

insert into mark values(markSeq.nextval,'qqww','java',88);
insert into mark values(markSeq.nextval,'qqww','C++',40);
insert into mark values(markSeq.nextval,'qqww','oracle',73);

select markid 考分编号, stuname 姓名, subject 学科, stuMark 成绩 from mark;

--更改一下字段长度
alter table mark modify(stuName varchar2(15), subject varchar2(15));

--利用 group by 子句将每个学生考的最高分的学科显示出来??
--不能显示没有分组的字段
select stuname,max(stumark) from mark group by stuname;
select stuname, avg(stumark+10)as abc from mark group by stuname;
--having 子句 用来指定 group by 子句的检索条件，having 位于 group by 的后面。
--注意： having 后的列名别忘了.
select stuname,min(stumark) from mark group by stuname having stuname not in ('wangjun');

-----------分析函数的用法-------------------

未完成

有时间补上.

 

--===============行级锁===========================

--在使用 insert, update, delete 和select ...for update等语句是时，oracle会自动应用行级锁定.
--select...for update 语句允许用户每次选择多行记录进行更新，这些记录会被锁定，且只能由发起查询的用户进行编辑。
--销售部门的用户需要更新 表中的某些行，可以应用锁来防止其他用户在更新完成之前操纵这些行.

--第一个人：
select * from student where stuid>10 for update;

--===========for update wait 的用法.
--第二个人:    等待4秒后自提示。
/* 好处：
1.  不至于一直等待被锁定的行。
2.  允许在应用程序中对锁的等待时间进行更多的控制.
3.  对于交互式应用程序非常有用，因为这些用户不能等待不确定的时间间隔. 
*/
select * from student where stuid>10 for update wait 4;

--=================表级锁================================

--表级锁保护表的数据，在事务处理过程中，表级锁会限制对整个表的访问.
--用lock table语句显式地锁定表。  用来限制对表执行添加，更新和删除等修改操作.

--共享模式锁定表.    防止在事务过程中其他用户修改表中的数据。
--第一个人：
lock table student in share mode;

--第二个人:
delete from student where stuid=11;
--出现锁定。 当第一个人: 执行 rollback 或 commit后，再执行.

--排它锁
lock table student in exclusive mode nowait;

--===========表分区===========

--表分区对用户是透明的，即应用程序可以不知道表已被分区，在更新和查询分区表时当作普通一来操作，
--但Oracle优化程序知道表已被分区.
--注意： 要分区的表不能具有 LONG和LONG RAW数据类型的列。

-----有哪四种分区的方法？-----------
--1. 范围分区
--根据表的某个列或一组列的值范围，决定将该数据存储在哪个分区上.   如: 可以根据序号分区，日期分区等.

create table sales1
(product_id varchar2(5),
sales_date date not null,
sales_cost number(10)
)
partition by range(sales_cost)
(
partition p1 values less than(1000),
partition p2 values less than(2000),
partition p3 values less than(3000)
);
--用日期分区
--第一个分区存2003年以前的数据. 第二个分区 2003年  第三个分区存2004年即以后的数据
create table sales2
(
product_id varchar2(5),
sales_date date not null,
sales_cost number(10))
partition by range(sales_date)
(
partition p1 values less than (to_date('2003-01-01','yyyy-mm-dd')),
partition p2 values less than (to_date('2004-01-01','yyyy-mm-dd')),
partition p3 values less than (maxvalue)
);

--2. 散列分区
--散列分区通过在分区键值上执行一个散列函数来决定数据的物理位置。
--散列分区把记录平均地分布到不同的分区，减少了磁盘I/O争用的可能性。
--第一种方法
create table employee1
(emp_id number(4),
emp_name varchar2(14),
emp_address varchar2(15),
department varchar2(10)
)
partition by hash (department) partitions 4;

--第二种方法
create table my_emp
(
empno number(4),
ename varchar2(10)
)
partition by hash(empno)
(
partition part1,
partition part2,
partition part3
);

--如何插入值呢?
--把scott用户的emp表中的empno,ename 这两列数据都插入到my_emp表中.
insert into my_emp select empno,ename from scott.emp;
--查看.    分区名必须用括号括起来.
select * from my_emp partition (part2);

--又该如何将分区表part2的内容删除呢？
delete from my_emp partition (part2);
delete from my_emp partition(part2) where  empno=7900;

--3. 复合分区
--复合分区是范围分区和散列分区的结合. 首先根据范围对数据进行分区，然后在这些分区布创建散列子分区.
--可以对子分区进行命名，并将其存储在特定的表空间中.

create table sales3
(
product_id varchar2(5),
sales_date date not null,
sales_cost number(10)
)
partition by range(sales_date)
subpartition by hash(product_id)
subpartitions 5
(
partition p1 values less than (to_date('2003-01-01','yyyy-mm-dd')),
partition p2 values less than (to_date('2004-01-01','yyyy-mm-dd')),
partition p3 values less than (to_date('2005-01-01','yyyy-mm-dd')),
partition p4 values less than (maxvalue)
);

--先用范围分区创建4个分区，然后使用散列子分区，给每个范围分区分5个子分区。 共创建20个子分区.

--4. 列表分区
--列表分区允许用户明确地控制行到分区的映射。允许按自然方式对无序和不相关的数据集进行分组和组织。

create table employee2
(
emp_id varchar2(5),
emp_name varchar2(8),
emp_address varchar2(20),
department varchar2(10)
)
partition by list (emp_address)
(
partition north values('黑龙江'),
partition center values('湖北，湖南，江西'),
partition west values('西藏','陕西','四川'),
partition east values('上海'),
partition south values('广东','海南')
);
--如果输入  河南   则oracle将拒绝该值. 因为没创建可以包含此值的分区.

--如何在分区表中插入记录？
--为前面的sales3表插入值.
insert into sales3 values('006',date'2006-12-03',4500);
--和普通表一样的插入法.
--如何查看某个分区中的数据？
select * from sales3 partition(p3);

--=============分区维护操作===================
--向现有的表中添加新的分区，或者将分区移到其它表空间中. 以获取更佳的I/O负载平衡.

--1. 添加分区
--alter table ... add partition是在现有分区的最后添加。
alter table sales1 add partition p4 values less than (4000);
--如果要在开始或中间插入呢？ 或者最高分区的分区边界是maxvalue 则要用split partition语句.

--2. 删除分区
--删除分区是  分区名不能加括号.   查看时要加括号.
alter table sales1 drop partition p4;
--p4中在表中所有的数据都会被删除，不能回滚.

--3. 截断分区
alter table sales3 truncate partition p2;
--此时分区表还存在。 只是里面的内容都被删除了.  不能回滚.

--4. 合并分区
--作用： 可将范围分区表或复合分区表的两个相邻分区连接起来。
--格式：  alter table table_name merge partitions partition_name,partition_name into partition partition_newName;
alter table sales3 merge partitions p1,p2 into partition p0;

--5. 拆分分区
--当分区过大时可以将分区进行拆分。
alter table sales3 split partition p0 at ( date'2003-01-01') into (partition p01, partition p02);
--拆分后还以将p01 改名为p1

alter table sales3 rename partition p01 to p1;