oracle学习笔记

–创建用户
create user zhangsan identified by mima;

–修改密码
alter user zhangsan identified by mima1;

–删除用户
drop user zhangsan;
drop user zhangsan cascade;

–授予用户权限
grant create session to zhangsan;–授予张三登录权限
grant create table to zhangsan;
grant create user to zhangsan;
grant drop user to zhangsan;
grant select on scott.emp to zhangsan;–授予张三可以访问其他用户中的表

–移除权限
revoke drop user from zhangsan;
revoke create user from zhangsan;
revoke create session from zhangsan;
–授予角色
grant resource to zhangsan;

select * from all_tables;
create table student(
id char(5) not null primary key,
name varchar(10) not null,
sex char(1) not null,
birthday date
);
select * from student;
alter table student add address varchar2(50);
–2.1
–创建一个表空间，并加一个100M的数据文件
create tablespace mytablespace datafile ‘E:\oracle\datafile01.dbf’ size 100m;
–为表空间增加数据文件
alter tablespace mytablespace add datafile ‘E:\oracle\datafile02.dbf’ size 50m;
–创建用户
create user myname identified by 123456;
–修改默认表空间名
alter database default tablespace mytablespace;
–3：创建表空间和临时表空间
create tablespace user_data datafile ‘E:\oracle\user_data.dbf’ size 50m;
create temporary tablespace user_temp tempfile ‘E:\oracle\user_temp1.dbf’ size 50m;
–删除表空间和临时表空间
drop tablespace user_data;
drop tablespace user_temp;
–2.2
–1
create user qst1 default tablespace user_data temporary tablespace user_temp identified by 123456;
alter user qst1 identified by 123;
alter user qst1 account lock;
alter user qst1 account unlock;
drop user qst1;
–2.3
–1
create user qst identified by 123;
grant connect to qst;–为用户qsr赋予连接数据库的权限
grant select on scott.emp to qst;–访问scott中emp表的权限
–2
revoke connect from qst;–收回权限
revoke select on scott.emp from qst;
–3
–为用户qst赋予连接并访问数据库的角色，注意角色
create role s2;
grant connect,resource to s2;
grant s2 to qst;
–4
grant select on scott.emp to qst with grant option;
–5
–创建角色、删除角色
create role tt;
drop role tt;
–6
–为用户qst1赋予连接并访问数据库的角色，注意角色
create role s1;
grant connect,resource to s1;
grant s1 to qst1;
–3.2
drop table s;
drop table c;
create table s(
sno varchar2(10) not null,
sname varchar2(20) null
);
create table c(
cno varchar2(5) not null ,
cname varchar2(20) null,
cteacher varchar2(20) null
);
create table sc(
sno varchar2(10),
cno varchar2(5),
scgrade varchar2(20)
);
select * from sc;
drop table sc;
alter table s add stel varchar2(10);–添加列
alter table c modify cname varchar2(40);–修改列的数据类型
alter table sc rename column scgrade to allscore;–修改列名
drop table c;
rename s to ss;
----序列
–3.1
create sequence ss start with 1000 increment by 1;

create table item(
itemio number(20) primary key,
itemname varchar2(50) not null,
startdate date,
enddate date,
money number(10),
constraint uk unique(itemname),
constraint fk check(money>0)
);
select * from item;
insert into item values(ss.nextval,‘小型监控系统’，‘6-8月-2012’，‘1-1月-2019’，100000);
–3.1
create table clerk(
no char(5) primary key,
name varchar2(20) not null,
sex varchar2(5) default ‘男’,
birthday date,
address varchar2(10),
salary number(20)
);
alter table clerk add constraint s1 check(sex='男’or sex=‘女’);
alter table clerk add constraint s2 check(salary>0);
–3.2
alter table s add constraint t1 primary key(sno);
alter table c add constraint t2 primary key(cno);
alter table sc add constraint t3 foreign key(sno) references s(sno);
alter table sc add constraint t4 foreign key(cno) references c(cno);
alter table sc modify (scgrade not null);–给列添加非空约束！！！
alter table s modify(unique(stel));–给列添加唯一约束！！！
–3.3
create table emptest(
emptno number(3) primary key,
emptname varchar2(20) not null,
sex varchar2(2) default ‘男’,
constrint m1 check(sex='男’or sex=‘女’);
birthday date
);
–删除数据
delete from tt where id=‘1001’;
truncate table tt;–只删除内容，不删除表结构

–删除数据
delete from tt where id=‘1001’;
truncate table tt;–只删除内容，不删除表结构
insert into tt(sid,sname,ssex,sbirthday)
values(‘10015’,‘冯程程’,‘f’,to_date(‘1998-07-22’,‘yyyy-mm-dd’));
–旧表生成新表
create table t_stutemp as select *from tt where 1=2;–永远为假的条件，保留了旧表的结构，无内容
inse rt into t_stutemp select *from tt where sclass=1;
–修改数据
update tt set sbirthday=‘08-1月-88’ where sname=‘许文强’;

---------简单查询------------
select * from t_student;
select *from t_course;
select *from t_score;
---------从学生表中检索出所有班级-------
select sclass from t_student;
select distinct sclass from t_student;–检索不重复的班级
---------从学生表中检索出2班性别为女性的学生的姓名和电话------
select *from t_student where sclass=2 and ssex=‘f’;
---------升序和排序按成绩排序-------
select *from t_score where sid=‘10002’ order by score asc;
select *from t_score where sid=‘10002’ order by score desc;
---------模糊查询
select *from t_student where sname like ‘张%’;–0个或多个
select *from t_student where sname like ‘张_’;–张后面有一个字符
–学生表
create table t_student

(

SID char(5) primary key, --学号

Sname varchar2(10) not null, --姓名

Ssex char(1) not null, --性别

Sbirthday date null, --出生日期

Stel varchar2(13) null, --联系电话

Sclass int null --班级

);

select * from t_student;

insert into t_student values(‘10001’,‘谢娜’,‘f’,‘1-4月-1986’,‘13697855678’,‘1’);

insert into t_student values(‘10002’,‘姚明’,‘m’,‘6-6月-1978’,‘13680888086’,‘2’);

insert into t_student values(‘10003’,‘马云’,‘m’,‘23-5月-1986’,‘13520035611’,‘1’);

insert into t_student values(‘10004’,‘张朝阳’,‘m’,‘8-9月-1971’,‘15597844378’,‘3’);

insert into t_student values(‘10005’,‘杨澜’,‘f’,‘3-7月-1973’,‘13897856666’,‘2’);

insert into t_student values(‘10006’,‘白岩松’,‘m’,‘11-11月-1972’,‘13266668888’,‘3’);

drop table t_course;
–课程表
create table t_course

(

CID char(5) primary key,

Cname varchar2(30) not null,

Ctype char(10) not null,

Chours int null

);

select * from t_course;

insert into t_course values(‘1’,‘操作系统’,‘选修’,32);

insert into t_course values(‘2’,‘数据结构’,‘选修’,48);

insert into t_course values(‘3’,‘教育心理学’,‘选修’,24);

insert into t_course values(‘4’,‘java核心技术’,‘选修’,64);

insert into t_course values(‘5’,‘java web开发技术’,‘选修’,48);

insert into t_course values(‘6’,‘java框架技术’,‘选修’,64);
–分数表
create table t_score

(

SID char(5) not null,

Cid int not null,

Score int not null

);

drop table t_score;

select * from t_score;

insert into t_score values(‘10001’,1,56);

insert into t_score values(‘10001’,2,46);

insert into t_score values(‘10001’,3,37);

insert into t_score values(‘10001’,4,59);

insert into t_score values(‘10001’,5,55);

insert into t_score values(‘10001’,6,49);

insert into t_score values(‘10002’,1,76);

insert into t_score values(‘10002’,2,66);

insert into t_score values(‘10002’,3,57);

insert into t_score values(‘10002’,4,79);

insert into t_score values(‘10002’,5,75);

insert into t_score values(‘10002’,6,69);

insert into t_score values(‘10003’,1,86);

insert into t_score values(‘10003’,2,68);

insert into t_score values(‘10003’,3,58);

insert into t_score values(‘10003’,4,77);

insert into t_score values(‘10003’,5,92);

insert into t_score values(‘10003’,6,89);

insert into t_score values(‘10004’,1,75);

insert into t_score values(‘10004’,2,63);

insert into t_score values(‘10004’,3,87);

insert into t_score values(‘10004’,4,67);

insert into t_score values(‘10004’,5,82);

insert into t_score values(‘10004’,6,96);

insert into t_score values(‘10001’,1,66);

insert into t_score values(‘10001’,2,61);

insert into t_score values(‘10001’,3,54);

insert into t_score values(‘10001’,3,60);

—教师表
create table t_teacher

(

TID char(5) not null,

Tname varchar(15) not null,

Tage int not null

);

drop table t_teacher;

select * from t_teacher;

insert into t_teacher values(‘1’,‘赵老师’,20);

insert into t_teacher values(‘1’,‘王老师’,43);

insert into t_teacher values(‘1’,‘张老师’,33);

insert into t_teacher values(‘1’,‘孙老师’,25);

insert into t_teacher values(‘1’,‘卢老师’,50);

insert into t_teacher values(‘1’,‘徐老师’,35);

create table t_teachercourse

(

TID int not null,

Cid int not null

);
–教师课程表
select * from t_teachercourse;

insert into t_teachercourse values(1,1);

insert into t_teachercourse values(2,2);

insert into t_teachercourse values(3,3);

insert into t_teachercourse values(4,4);

insert into t_teachercourse values(5,5);

insert into t_teachercourse values(7,5);

-------------连接查询---------------
-----------内连接----------
----左表有的才显示出来
select tt.sid,cname,score from t_student tt join t_score on tt.sid=ts.sid
join t_course tc on ts.cid=tc.cid;
-----------自然连接---------等值连接，相同的列，重复的会去掉
select * from t_student natural join t_score t3;
----------外连接-------左表有右表没有的也可以查出来（左外连接），右表有的左表没有的也可以查出来------
select *from t_teacher tt left outer join t_teachercourse ttc on tt.tid=ttc.tid;
select from t_teacher tt full outer join t_teachercourse ttc on tt.tid=ttc.tid;
----------交叉连接------------左表记录右表记录
select * from t_student cross join t_course;
----------自连接-------------
select p1.empno,p1.ename,p1.mgr,p2.ename from emp p1 join emp p2 on p1.mgr=p2.empno order by p1.empno asc;

----------聚合函数-----------
–max、min、avg、sum、count–
–count(*):所有记录的个数
–count(列名)：不为空的个数

---------------子查询-------------
-----查询参加过课程编号为1的考试的学员
select sid from t_score where cid=1;
select *from t_student where sid=(select sid from t_score where cid=1);–失败
select *from t_student where sid in(select sid from t_score where cid=1);
-----查询已经安排教师上课的课程信息—!!!-----
select *from t_course tc
where exists(select * from t_teachercourse ttc where ttc.cid=tc.cid);
-----在成绩表中查询出所有学生的最高平均分（from后面的子查询）
select avg(score) as avgscore from t_score group by sid;
select max(avgscore) from (select avg(score) as avgscore from t_score group by sid;);
-----查询学生的姓名、课程名和成绩（select后面的子查询）

select (select sname from t_student where sid=ts.sid)as 姓名,
(select cname from t_course where cid=ts.cid)as 课程名,
score as 成绩
from t_score ts;
--------子查询的经典应用分页查询--------------------------
---------要求每页5条记录，查询第2页的数据
----rownum(第1种方法)
select rownum as num,sid,cid,score from t_score

select * from (select rownum as num,sid,cid,score from t_score)
where num>=6 and num<=10;–分成几页查其中一页

----rowid
select rowid rid from emp order by sal desc;
select rownum rn,rid from (select rowid rid from emp order by sal desc)
where rownum<=10; —给它一个编号
select rid from () where rn>=6;
select *from emp where rowid in() order by sal desc;

select sysdate,sysdate+1 "日期加1天"from dual;
select sysdate,add_months(sysdate,1) “日期加1个月” from dual;
select sysdate,add_months(sysdate,12) “加1年” from dual;
—加1星期
select sysdate,to_char(sysdate+7,‘yyyy-mm-dd HH24:MI:SS’) from dual;
–加1天
select sysdate,to_char(sysdate+1,‘yyyy-mm-dd HH24:MI:SS’) from dual;
–加1小时
select sysdate,to_char(sysdate+1/24,‘yyyy-mm-dd HH24:MI:SS’) from dual;
–加1分钟,注意是除以60
select sysdate,to_char(sysdate+1/24/60,‘yyyy-mm-dd HH24:MI:SS’) from dual;
–加1秒钟
select sysdate,to_char(sysdate+1/24/60/60,‘yyyy-mm-dd HH24:MI:SS’) from dual;
–相差月数
select months_between(sysdate,to_date(‘2014-5-5’,‘yyyy-mm-dd’))“相差月数” from dual;

select months_between(sysdate,add_months(sysdate,-1))“相差月数” from dual;

—日期所在月份的最后一天
select sysdate,last_day(sysdate) from dual;
—下一个星期一是几号
select sysdate ,next_day(sysdate,2) “下一个星期一是几号” from dual;
select sysdate,next_day(sysdate,‘星期一’)“下一个星期一是几号” from dual;
----下一个星期日是几号
select sysdate,next_day(sysdate,1)“下一个星期日是几号” from dual;
select sysdate,next_day(sysdate,“星期日”)“下一个星期日是几号” from dual;
----下一个星期二是几号
select sysdate,next_day(sysdate,3)“下一个星期二是几号” from dual;

----截取到年（本年的第一天）
select trunc(sysdate,‘year’) from dual;
----截取到季度
select trunc(sysdate,‘q’) from dual;
----截取到月
select trunc(sysdate,‘month’) from dual;
-----分析函数
----给排好序的查询结果中的每一行返回一个唯一的编号，先按薪水排序，再编号
select row_number() over(order by sal desc) as 编号,empno,ename,deptno,sal from emp;
----partition by deptno 按照部门编号分组，组内编号，sal值相同的部门编号也不同
select row_number() over(partition by deptno order by sal desc) as 编号,empno,ename,deptno,sal from emp;
—每组内从1开始编号，组内编号，sal值相同的编号相同，后续编号跳过，如1,2,2,4,5,5,7
select rank() over(order by sal desc)as 编号,empno,ename,deptno,sal from emp;
select rank() over(partition by deptno order by sal desc) as 编号,empno,ename,deptno,sal from emp;
—每组内从1开始编号，组内编号，sal值相同的编号相同，后续编号连续，如1,2,2,3,4,4,5
select dense_rank() over(order by sal desc)as 编号,empno,ename,deptno,sal from emp;
select dense_rank() over(partition by deptno order by sal desc) as 编号,empno,ename,deptno,sal from emp;

----在一张表中可以加好几个行级锁供多个用户使用
----不想让他等待就加wait 10超过10秒之后就不会等了;
----no wait 是不想等----
----commit,rollback 释放锁

—表级锁
lock table 表名 in 锁类型 mode；
lock table emp in share mode;—共享锁
lock table emp in exclusive mode;—排它锁

—创建两个表
drop table a;
drop table b;
create table a as select 1 x from dual;
create table b as select 1 x from dual;

select *from a;
select *from b;

update a set x=x+1;
update b set x=x+1;