Oracle知识点笔记(一)

1、查看用户的表

select * from tab;

2、查询取反：

SQL> select * from emp where not ( 9sal < 1500 and comm is null);
 

3、指定范围的语句：
between 最小值 and 最大值；
等价于 3000>= sal  and sal >= 1500;
4、in 操作符：

select * from emp where empno in(值1，值2，值3。。。，值n）;

select * from emp where empno not  in(值1，值2，值3。。。，值n）;

5、把字符串第一个字母变成大写的函数：

select initcap('smith') from dual;

6、截取：

substr（'sssss',1,3）；

7、日期函数：
日期 + 数字 =日期；
日期 - 数字 = 日期；
日期 - 日期 = 数字；

8、当前日期 ：

select sysdate from dual;

9、求出雇员入职的星期数：

select empno ,ename, trunc((sysdate - hiredate)/7) from emp;

10、MONTHS_BETWEEN():求出给定日期范围的月数
ADD_MONTHS(); 在指定的日期加上指定的月数，求出之后的日期

select ADD_MONTHS(sysdate ,4) from dual;

11、转换函数：
to_char(); 转换成字符存储
to_number();转换成数字
to_date();转换成日期

select to_char(sysdate,'yyyy-mm-dd') from dual;
to_number('1323');

select to_date('2020-02-23' ,'yyyy-mm-dd') from dual;

 

12、DECODE()函数：

 

13、子查询： 

select d.dname,de.c ,ed.a from 
 dept d,
(select deptno ,count(empno) c, avg(sal) a from emp group by deptno) ed 
 where d.deptno = ed.deptno;

create table test as select * from emp;
to_date():函数，讲一个字符串类型的数据变为date类型的数据
to_date('2009-01-23','yyyy-mm-dd')

 select e.empno ,e.ename,m.empno,m.ename,d.deptno,d.dname,d.loc,s.grade
    from emp e,dept d,emp m ,salgrade s
    where e.sal>(select avg(sal) from emp)
    and e.deptno=d.deptno
    and e.mgr =m.empno(+)
    and e.sal between s.losal and s.hisal;

select d.dname,count(e.empno),avg(e.sal),avg(MONTHS_BETWEEN(sysdate ,hiredate) )
from emp e,dept d where e.deptno=d.deptno 
group by d.dname;

 select e.ename ,em.j,em.pingjun from emp e,(select min(sal) pingjun,job j from emp group by job) em where e.job = em.j;

select deptno ,sum(sal) ,count(empno) 
from emp  where 
deptno in ( select deptno from dept where dname like '%S%') group by deptno;

14、唯一约束：
假设姓名不允许重名,也不能为空

create table person(
name varchar2(23) unique not null
);

 

15、检查约束：

create table person(
name varchar2(23) unique not null,
age number(3) not null check (age between 0 and 150),
sex varchar(2) default '男' check(sex in ('男','女'),
constraint 约束名 约束类型(列名)
);

16、更改约束

 alter table 表名 add constraint 约束名称 约束类型(约束字段);

alter table person add constraint person_pid_pk primary key(pid);
alter table person add constraint person_name_uk unique(name);
alter table person add constraint person_age_ck check(age between 0 and 150 );
alter table person add constraint person_sex_ck check(sex in ('男','女' );

增加主外键约束

alter table book add constraint book_pid_pk primary key(bid);//主键
alter table book add constraint person_book_pid_fk foreign key(pid) references person(pid) on delete cascade;

删除约束

alter table 表名 drop constraint 约束名称;
alter table person drop constraint person_age_ck;
alter table person drop constraint person_sex_ck;

17、完整练习

create table sporter (
  sporterid number(4) primary key not null,
  name varchar2(50) not null ,
 sex varchar2(2) not null ,
  department varchar2(30) not null,
  constraint sporter_sex_ck check(sex in('m','f'))
);

create table item (
itemid varchar2(4) primary key not null,
itemName varchar2(50) not null ,
location varchar2(50) not null );


create table grade(
sporterid number(4) ,
itemid varchar2(4) ,
mark number(2),
constraint  soprter_grade_sporterid_fk foreign key(sporterid ) references 
sporter(sporterid) on  delete cascade,
constraint soprter_grade_itemid_fk foreign key(itemid) 
references item(itemid) on delete cascade,
constraint grade_mark_ck check(mark in (6,4,2,0))
);

测试数据

 

insert into sporter(sporterid,name,sex,department)
 values(1001,'李明','m','计算机系');
insert into sporter(sporterid,name,sex,department)
 values(1002,'张三','m','数学系');
insert into sporter(sporterid,name,sex,department)
 values(1003,'李四','m','计算机系');
insert into sporter(sporterid,name,sex,department)
 values(1004,'王二','m','物理系');
insert into sporter(sporterid,name,sex,department)
 values(1005,'李娜','f','心理系');
insert into sporter(sporterid,name,sex,department)
 values(1006,'孙俪','f','数学系');

update sporter set department ='computer' where department ='计算机系';
update sporter set department ='math' where department ='数学系';
update sporter set name='liming' where sporterid=1001;
update sporter set name='zhangsan' where sporterid =1002;
update sporter set name='lisi' where sporterid =1003;
update sporter set name='wanger',department='Physics' where sporterid =1004;
update sporter set name='lina',department='Psychology' where sporterid =1005;
update sporter set name='sunli' where sporterid =1006;

insert into item (itemid,itemName,location) 
values('x001','男子五千米','一操场');
insert into item (itemid,itemName,location) 
values('x002','男子标枪','一操场');
insert into item (itemid,itemName,location) 
values('x003','男子跳远','二操场');
insert into item (itemid,itemName,location) 
values('x004','女子跳高','二操场');
insert into item (itemid,itemName,location) 
values('x005','女子三千米','三操场');

update item set itemName='man5000' ,location='yicaochang' where itemid='x001';
update item set itemName='man_biaoqiang' ,location='yicaochang' where itemid='x002';
update item set itemName='manJump' ,location='ercaochang' where itemid='x003';
update item set itemName='womanJumpHigh' ,location='ercaochang' where itemid='x004';
update item set itemName='woman3000' ,location='sancaochang' where itemid='x005';

insert into grade( sporterid,itemid,mark) 
values(1001,'x001',6);
insert into grade( sporterid,itemid,mark) 
values(1002,'x001',4);
insert into grade( sporterid,itemid,mark) 
values(1003,'x001',2);
insert into grade( sporterid,itemid,mark) 
values(1004,'x001',0);

insert into grade( sporterid,itemid,mark) 
values(1001,'x003',4);
insert into grade( sporterid,itemid,mark) 
values(1002,'x003',6);
insert into grade( sporterid,itemid,mark) 
values(1004,'x003',2);

insert into grade( sporterid,itemid,mark) 
values(1005,'x004',6);
insert into grade( sporterid,itemid,mark) 
values(1006,'x004',4);

18、练习：

 

select s.department ,sum(g.mark) 
from sporter s,grade g 
where s.sporterid=g.sporterid group by s.department;

select 
from (
select s.department ,sum(g.mark) sum
from sporter s,grade g 
where s.sporterid=g.sporterid group by s.department
order by sum desc) ;

select i.itemName,s.name
 from item i
,grade g ,sporter s where i.location='yicaochang'  
and i.itemid=g.itemid and s.sporterid=g.sporterid;

select distinct s.name
from sporter s,grade g 
where s.sporterid=g.sporterid and s.name!='zhangsan' and 
g.itemid in(
select g.itemid
from sporter s,grade g 
where s.sporterid=g.sporterid and s.name='zhangsan');

update  grade set mark=0 
where sporterid=(
select sporterid from sporter where name='zhangsan');

delete from item where itemName ='womanJumpHigh';

19、视图

create view viewtest as select * from emp;
create or replace view viewtest as ...;
create view viewtest as select * from emp with read only;

 20、序列

 

create sequence myseq;


SQL> create sequence myseq;

Sequence created.


nextVal : 取得序列的下一个内容
currVal : 取得序列当前内容

测试序列

create table testseq(
next  number,
curr number);

insert into testseq(next ,curr)
 values (myseq.nextVal,myseq.currVal);

修改序列增长步长：

 

increment by 长度;

SQL> drop sequence myseq;

Sequence dropped.


SQL> create sequence myseq increment by 2;

Sequence created.

 默认序列是从1开始：

 

SQL>  create sequence myseq increment by 2 start with 1;

Sequence created.

21、用户管理

create user test identified by 123;
grant create session to test;
grant connect ,resource to test;
alter user test identified by hello;//修改密码


alter user test account lock;//锁住用户