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) sumfrom sporter s,grade g where s.sporterid=g.sporterid group by s.departmentorder 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.namefrom sporter s,grade g where s.sporterid=g.sporterid and s.name!='zhangsan' and g.itemid in(select g.itemidfrom 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;//锁住用户