oracle数据库基本操作教程

/*
Oracle客户端远程连接服务器，在客户端配置TNS文件，配置TNS文件所需要的信息：
数据库服务器的IP、
数据库实例名称、
数据库实例端口(默认端口是1521)
登陆数据还需要用户名和密码
参考视频"客户端远程连接服务器.exe"
*/

/*
数据库对象：
表、试图、触发器、存储过程、函数、数据类型、用户等等
选择My objects，只显示当前用户所拥有的对象
最关心的对象是：表(相当于一个Excel中的sheet页)
*/

--dcl数据库控制语句
/*新增用户
create user 用户名 identified by 密码;
*/
create user liangxianfu identified by liangxianfu;

/*授权
grant 角色1,角色2,角色3 to 用户名;
角色中有很多权限
*/
grant connect,dba,resource to liangxianfu;

--ddl：数据库定义语句，定义表的结构(表就相当于excel中sheet页)
/*创建表
create table 表名(
列1 数据类型 [constraint 主键别名 primary key],
列2 数据类型 [not null | null(默认是null)],
...
列n 数据类型 [default 默认值]
);

数据类型：number、varchar2(长度)或char(长度)、date，即数字、字符串、日期
varchar2(长度)：可变长，varchar2(12),存储内容的长度不能超过12个字节,若实际存储3个字节,内容占用3个字节
char(长度)：定长，char(12)，存储内容的长度不能超过12个字节，若实际存储3个字节，内容仍然占用12个字节

约束：可以理解为限制条件，常见的约束：非空、唯一等等
主键：
主键相当于身份证号码，唯一识别一行数据(通过主键只能找到一行数据)，在一个表中只能有一个主键,
主键属于表中的对象
主键默认有非空约束、唯一约束
在创建一张表时，建议增加主键列

默认值
*/
create table t_student(
id number constraint pk_t_student_id primary key,
name varchar2(50) not null,
sex char(12) default '女',
birthday date
);

--增加注释
comment on table t_student is '学员基本信息表';
comment on column t_student.sex is '性别';
comment on column t_student.birthday is '出生日期';

/*修改表结构
1)、修改数据类型、非空约束、增加主键、默认值等等
2)、修改列的名字
*/
alter table t_student
modify name null;

alter table t_student
modify sex varchar2(12);

alter table t_student
modify sex default null;

alter table t_student
modify sex varchar2(12) default null;

alter table t_student
modify id number constraint pk_t_student_id primary key;

alter table t_student
rename column sex to ssex;

--修改表名
ALTER TABLE old_table_name RENAME TO new_table_name;
/*新增列*/
alter table t_student
add classid number not null;

/*删除列*/
alter table t_student
drop column classid;

--删除表结构
drop table t_student;

/*
Data definition language (DDL) statements define, alter the structure of, and drop
schema objects. DDL statements enable you to:
Create, alter, and drop schema objects and other database structures, including
the database itself and database users (CREATE, ALTER, DROP)
Change the names of schema objects (RENAME)
Delete all the data in schema objects without removing the objects’structure
(TRUNCATE)
Grant and revoke privileges and roles (GRANT, REVOKE)
Turn auditing options on and off (AUDIT, NOAUDIT)
Add a comment to the data dictionary (COMMENT)
DDL statements implicitly commit the preceding and start a new transaction
*/

/*dml：数据库操作语句
增、删、改、查操作

事务：做一件事情的过程，最终的结果要么成功、要么失败，在数据库中，执行一条dml语句即发起一个事务(自动开启事务)，发起了事务就要结束事务，
结束事务的两种方式：
commit;提交，意味着所有的dml操作结果都生效
rollback;回滚，意味着所有的dml操作结果不会生效

新增：
insert into 表名(列1,列2,列3...列N) values(value1,value2,value3...valueN);
commit;

sysdate：获取系统当前时间,格式是:年月日时分秒
*/
insert into t_student(id,name,sex,birthday) values(1,'一号学员','男',sysdate);
commit;

--没有指定name列,name列的值为空值，但在定义表结构时，name列有非空约束，因此以下sql会执行失败
insert into t_student(id,sex,birthday) values(2,'女',sysdate);
commit;

--没有指定sex列，但在定义表结构时，sex列有一个默认值是'女',因此以下sql执行后，其sex列的值是'女'
insert into t_student(id,name,birthday) values(2,'二号学员',sysdate);
commit;

--修改表结构，给name列增加默认值
alter table t_student
modify name default '未知的姓名';

--没有指定name列,name列的值为空值，但在定义表结构时，name列有非空约束和默认值，因此以下sql会执行成功，name列取默认值
insert into t_student(id,sex,birthday) values(3,'女',sysdate);
commit;

--id列是number类型，因此只能是数字，不能是字符串
insert into t_student(id,sex,birthday) values('a','女',sysdate);
commit;

/*修改数据
update 表名
set 字段1=值1,
字段2=值2,
...
字段N=值N
[where 过滤条件]
commit;
*/
update t_student
set name = '蔡10',sex = '男';
commit;

/*删除数据
delete [from] 表名
[where 过滤条件]
commit;
*/
delete from t_student;

/*truncate table 表名
截断表，即删除表中的所有数据，且不会发起事务(要理解该含义，oracle内部隐式发起并结束事务)，该操作是非常危险的(除非是100%确定表中的所有数据不需要，可以用，否则决不能用truncate来截断表)
不能有where条件，不可恢复
*/
truncate table t_student;

/*dml查询语句
select 列1,列2...列N
from 表
[where 过滤条件]
[group by 分组]
[having]
[order by]
*/

--查询所有的行，显示所有列
select * from student;

--查询所有的行，显示指定列
select sname, sex, birthday from student;

--where过滤(实际就是比较运算)：>、<、=、<>或!=、>=、<=
select * from student where studentid > 10;

select * from student where sex = '男';

--to_date函数：将字符串类型的日期转换成对应的date类型
--to_date(日期字符串,格式字符串)，如to_date('2013-12-12 08:20:38' 'yyyy-mm-dd HH:mi:ss')
--trunc:忽略date类型的时分秒，返回的还是一个date类型
select * from student where birthday > to_date('2013-6-25', 'yyyy-mm-dd');
select *
from student
where trunc(birthday) > to_date('2013-6-25', 'yyyy-mm-dd');
--等效于
select *
from student
where birthday > to_date('2013-6-25 23:59:59', 'yyyy-mm-dd HH24:mi:ss');

--to_char()：将date转换成对应的字符串
select to_char(birthday, 'yyyy-mm-dd HH24:mi:ss') birthday from student;

--日期类型的比较，要用date进行比较，而不要用字符串比较
--'2013-09-9 10:31:57' > '2013-09-25'
select *
from student
where to_char(birthday, 'yyyy-mm-dd HH24:mi:ss') < '2013-06-25';

select to_char(to_date('2013-6-25 23:59:59', 'yyyy-mm-dd HH24:mi:ss'),
'yyyy-mm-dd') my_date
from dual;

--and(且)、or(或)，若想要明确指定优先级，用括号括起指定的条件
--and会减少查询结果的数据量
--or会增加查询结果的数据量
select * from student where sex = '男' and sname = '刘海生' and studentid = 3;
select * from student where sex = '男' or sname = '刘红';

--in、not in
select * from student where studentid in(10, 20);
--相当于
select * from student where studentid = 10 or studentid = 20;

select * from student where studentid not in(10, 20);
--相当于
select * from student where studentid != 10 and studentid <> 20;

-->all(是且的关系)、>any(是或的关系)、<>=all、<=any
select * from student where studentid >=all(10, 20);
--相当于
select * from student where studentid >=10 and studentid >= 20;
--相当于
select * from student where studentid >= 20;

select * from student where studentid >=any(10, 20);
--相当于
select * from student where studentid >=10 or studentid >= 20;
--相当于
select * from student where studentid >= 10;

--between....and...
select * from student where studentid between 10 and 20;
--相当于
select * from student where studentid >= 10 and studentid <= 20;

select * from student where studentid > 10 and studentid <= 20;

/*dml查询语句，多表查询一定要避免笛卡尔积的产生
select 列1,列2...列N
from 表1,表2...表N
[where 过滤条件]
[group by 分组]
[having]
[order by]

内连接
select 列1,列2...列N
from 表1 inner join 表2 on 关联条件1
inner join 表3 on 关联条件2
[where 过滤条件]
[group by 分组]
[having]
[order by]
*/

--两张表查询，产生笛卡尔积
select * from student, class;

--内连接
select * from student st, class cl where st.classid = cl.classid;
--相当于
select * from student st inner join class cl on st.classid = cl.classid;

--左连接
select * from student st left join class cl on st.classid = cl.classid;
--相当于
select * from student st, class cl where st.classid = cl.classid(+);

--造数据
insert into student values(999,'999感冒灵','李玉刚',sysdate,999);
commit;

--右连接
select * from student st right join class cl on st.classid = cl.classid;
--相当于
select * from student st right join class cl on st.classid(+) = cl.classid;

--造数据
insert into class values(888,'发财班');
commit;

--查询527班学员信息
select *
from class cl, student st
where cl.classid = st.classid
and cl.classname = '527';
--等同于
select *
from class cl
inner join student st on cl.classid = st.classid
where cl.classname = '527';

--查询527班学员的分数
select st.sname, st.sex, sc.score
from stscore sc, class cl, student st
where sc.studentid = st.studentid
and st.classid = cl.classid
and cl.classname = '527';
--等同于
select st.sname, st.sex, sc.score
from stscore sc
inner join student st on sc.studentid = st.studentid
inner join class cl on st.classid = cl.classid
where cl.classname = '527';

--null值的处理：它是个未知数x，你不要指望它是个固定的值
--方式一：is null或is not null
--方式二：nvl(列,默认值)，如果"列"的值是null,则给它赋一个"默认值"
-- ' '是空字符串，是个固定的值
insert into class(classid) values(111);
commit;

insert into class(classid,classname) values(112,' ');
commit;

select * from class;
select * from class cl where cl.classname = '826';
select * from class cl where cl.classname <> '826';
select * from class cl where cl.classname = null;
-- null = '826' 结果是null
-- null <> '826' 结果是null
-- null == null 结果是null

select * from class cl where cl.classname is null;
select * from class cl where cl.classname is not null;
select * from class cl where nvl(cl.classname, '826') = '826';

--查询学员"刘红"所有课程的分数，显示信息如下：姓名、性别、出生日期、班级名称、课程名称、分数
select st.sname, st.sex, st.birthday, cl.classname, co.cname, sc.score
from student st, stscore sc, class cl, course co
where st.studentid = sc.studentid
and sc.courseid = co.courseid
and st.classid = cl.classid
and st.sname = '刘红';

/*分组查询
聚合函数：
sum()求和
avg()求平均数据
min()求最小值
max()求最大值
count()求总数

聚合函数可以单独使用
*/
--以下sql会报错,思考为什么会报错
select st.sex,st.sname from student st group by st.sex;

--用聚合函数最数据进行聚合
select st.sex, max(st.sname) from student st group by st.sex;

select st.sex, count(st.sname) "人数" from student st group by st.sex;

--查询527班每个学员的总分
--desc降序排序、asc升序排序(默认排序方式)
select st.studentid, min(st.sname) "姓名", sum(sc.score) "总分"
from class cl, stscore sc, student st
where cl.classid = st.classid
and st.studentid = sc.studentid
and cl.classname = '527'
group by st.studentid
order by sum(sc.score) desc;


--查询每门课程的总分、最高分、最低分、平均分
--round()四舍五入函数
select max(co.cname) "课程名称",
sum(sc.score) "总分",
max(sc.score) "最高分",
min(sc.score) "最低分",
round(avg(sc.score), 1) "平均分"
from stscore sc, course co
where sc.courseid = co.courseid
group by co.courseid;

--查询826班oracle课程的总分、平均分、最高分、最低分
--lower()转换成小写
--upper()转换成大写
select sum(sc.score) "总分",
round(avg(sc.score), 1) "平均分",
max(sc.score) "最高分",
min(sc.score) "最低分"
from class cl
inner join student st on cl.classid = st.classid
inner join stscore sc on st.studentid = sc.studentid
inner join course co on sc.courseid = co.courseid
where cl.classname = '826'
and lower(co.cname) = 'oracle';

--查询826班所有课程的总分、平均分、最高分、最低分
select min(co.cname) "课程名称",
sum(sc.score) "总分",
round(avg(sc.score), 1) "平均分",
max(sc.score) "最高分",
min(sc.score) "最低分"
from class cl
inner join student st on cl.classid = st.classid
inner join stscore sc on st.studentid = sc.studentid
inner join course co on sc.courseid = co.courseid
where cl.classname = '826'
group by co.courseid;

--查询每个学员的总分、平均分、最高分、最低分
select max(st.sname) "姓名",
sum(sc.score) "总分",
round(avg(sc.score), 1) "平均分",
max(sc.score) "最高分",
min(sc.score) "最低分"
from student st, stscore sc
where st.studentid = sc.studentid
group by st.studentid
order by sum(sc.score) desc;

/*
having过滤，是对分组之后的数据进行过滤，where是对数据分组之前进行过滤
having只能紧跟group by，没有group by就没有having
*/
--查询每个学员的总分、平均分、最高分、最低分，只显示总分在200分以上的学员
select max(st.sname) "姓名",
sum(sc.score) "总分",
round(avg(sc.score), 1) "平均分",
max(sc.score) "最高分",
min(sc.score) "最低分"
from student st, stscore sc
where st.studentid = sc.studentid
group by st.studentid
having sum(sc.score) > 200
order by sum(sc.score) desc;

--查询每个班级没门课程的总分、平均分、最高分、最低分
--多个字段进行分组
select min(cl.classname) "班级名称",
max(co.cname) "课程名称",
sum(sc.score) "总分",
round(avg(sc.score), 1) "平均分",
max(sc.score) "最高分",
min(sc.score) "最低分"
from course co, stscore sc, class cl, student st
where co.courseid = sc.courseid
and sc.studentid = st.studentid
and cl.classid = st.classid
group by cl.classid, co.courseid
order by cl.classid;

/*
聚合函数会忽略null值
*/
--造数据
insert into student values(998,'998','男',sysdate,5);
commit;

insert into stscore(stscoreid,studentid,courseid) values(999,998,1);
insert into stscore values(998,998,2,90);
insert into stscore values(997,998,3,80);
commit;

--查数据，比较一下3条sql的输出结果
select count(*) from stscore sc;
select count(sc.score) from stscore sc;
select count(sc.studentid) from stscore sc;

--假设学员没有考Linux科目，在分数表中的分数填写的是null值
--以下聚合函数忽略掉null值
select max(st.sname) "学员姓名",
sum(sc.score) "总分",
round(avg(sc.score), 1) "平均分",
max(sc.score) "最高分",
min(sc.score) "最低分"
from stscore sc, student st
where st.studentid = sc.studentid
--and sc.score is not null
group by st.studentid
--having sum(sc.score) > 200
order by sum(sc.score) desc;

/*子查询：用sql语句代替固定值
select 列1,列2...列N
from 结果集1,结果集2...结果集N
[where 过滤条件]
[group by 分组]
[having]
[order by]
*/
select classid from class cl where cl.classname in('527','529');

select *
from student
where classid in
(select classid from class cl where cl.classname in ('527', '529'));

--单行子查询：>、<、=、<>或!=、>=、<=
select * from student where classid = 1;

--以下sql报错，思考为什么会报错，从而理解子查询
select *
from student
where classid = (select * from class cl where cl.classname = '527');

--以下sql报错，思考为什么会报错，从而理解子查询
select *
from student
where classid =
(select classid from class cl where cl.classname in ('527', '529'));

--in、not in、>all、 select *
from student
where classid not in
(select classid from class cl where cl.classname in ('527', '529'));

--查询每个班级"总人数" "男生人数" "女生人数"
select count(*)
from (select ww.studentid
from student qq
left join (select * from student sq where sq.sex = '男') ww
on qq.studentid = ww.studentid)
where studentid is null;

select cl.classid, count(st.studentid) t_total
from student st, class cl
where st.classid = cl.classid
group by cl.classid;

select cl.classid, count(st.studentid) t_total
from student st, class cl
where st.classid = cl.classid and st.sex = '男'
group by cl.classid;

select t1.t_total "总人数",
t2.t_total "男生总人数",
(t1.t_total - t2.t_total) "女生总人数"
from (select cl.classid, count(st.studentid) t_total
from student st, class cl
where st.classid = cl.classid
group by cl.classid) t1,
(select cl.classid, count(st.studentid) t_total
from student st, class cl
where st.classid = cl.classid
and st.sex = '男'
group by cl.classid) t2
where t1.classid = t2.classid;

--查询全校总人数、男生总人数、女生总人数
select (select count(student.studentid) from student) t_total,
(select count(student.studentid) from student where sex = '男') t_total_01,
(select count(student.studentid) from student) -
(select count(student.studentid) from student where sex = '男') t_total_02
from dual;

select t1.t_total "总人数",
t1.t_total_boy "男生总人数",
(t1.t_total - t1.t_total_boy) "女生总人数"
from (select count(st.studentid) t_total,
max((select count(studentid)
from student st1
where st1.sex = '男')) t_total_boy
from student st) t1;


/*伪列
rowid：属于表中的列，是由数据库自动增加的列，是真实存在的一个列，唯一标识一条数据的物理位置（查询语句首先得到数据的rowid，然后根据rowid到数据文件中唯一定位数据）
因此，根据rowid查询的效率是最高的，数据在入库时(即成功插入数据)，Oracle数据就给这条数据分配一个唯一的rowid

rownum：是给结果集增加的一个序号，不属于任何的表，现有结果集，才能有rownum，始终是从1开始编号(不能跨过1而存在)
*/
--根据rowid查询的效率是最高的，但通常情况下不知道rowid的值，rowid是整个数据库唯一(在不同的数据库中不一定唯一)
select t.*, t.rowid from student t where t.rowid = 'AAAM1iAAEAAAAGcAAA';

--不属于任何的表
--select t.*,t.rowid,t.rownum from student t;

--始终是从1开始编号(不能跨过1而存在)
select * from student t where rownum < 10;
select * from student t where rownum > 10;
select * from student t where rownum between 10 and 20;
select * from student t where rownum between 1 and 10;

--查询第10条到第20条数据，用子查询，常用的分页查询(每一个结果集都对应一个rownum)
select t.*
from (select t.*, rownum rn from student t where rownum < 21) t
where t.rn > 9;


--步骤一：查询每个学员的总分
select st.studentid, sum(sc.score) t_score
from stscore sc, student st
where sc.studentid = st.studentid
group by st.studentid;

--步骤二：查询前3名的总分是多少
--造数据
select * from stscore sc where sc.studentid in (6,7) for update nowait;

select *
from (select distinct sum(sc.score) t_score
from stscore sc, student st
where sc.studentid = st.studentid
group by st.studentid
order by t_score desc) t
where rownum < 4;

--distinct的用法：去除重复的数据(所有的列全部相同，则取一条)，比较以下sql语句
select distinct st.sname from student st where st.sname = '刘红';
select distinct st.studentid, st.sname from student st where st.sname = '刘红';
select distinct st.sex, st.sname from student st where st.sname = '刘红';

--步骤三：步骤一、步骤二进行关联查询，关联条件是总分数相等
select st1.sname "姓名", t1.t_score "总分", t2.rn "名次"
from (select st.studentid, sum(sc.score) t_score
from stscore sc, student st
where sc.studentid = st.studentid
group by st.studentid) t1,
(select t.*, rownum rn
from (select distinct sum(sc.score) t_score
from stscore sc, student st
where sc.studentid = st.studentid
group by st.studentid
order by t_score desc) t
where rownum < 4) t2,
student st1
where t1.t_score = t2.t_score
and t1.studentid = st1.studentid
order by t2.rn;

--试图：虚拟表，保存了sql的中间结果，只能发出select语句，不能发insert、update、delete等语句
--试图所涉及的表中的数据发生变化，则试图中的数据也随之发生更改
create or replace view v_view01 as
select st1.sname "姓名", t1.t_score "总分", t2.rn "名次"
from (select st.studentid, sum(sc.score) t_score
from stscore sc, student st
where sc.studentid = st.studentid
group by st.studentid) t1,
(select t.*, rownum rn
from (select distinct sum(sc.score) t_score
from stscore sc, student st
where sc.studentid = st.studentid
group by st.studentid
order by t_score desc) t
where rownum < 4) t2,
student st1
where t1.t_score = t2.t_score
and t1.studentid = st1.studentid
order by t2.rn;

--试图的使用
select * from v_view01

--索引：相当于书的目录，提高查询效率，属于表对象
--若表中已有的数据重复了，则不能创建唯一性索引
drop index i_index_01
create index i_index_01 on student (sname);

--以下sql执行失败
create unique index i_index_01 on student (sname);

select st1.sno "学号",
st1.sname "姓名",
st1.sex "性别",
t3.t_degree "总成绩"
from student st1,
(select max(t.t_degree) m_t_degree
from student st,
(select sc.sno, sum(sc.degree) t_degree
from score sc
group by sc.sno) t
where t.sno = st.sno
group by st.sname, st.sex) t3,
(select sc.sno, sum(sc.degree) t_degree from score sc group by sc.sno) t2
where t3.m_t_degree = t2.t_degree
and t2.sno = st1.sno;

select 部门
from A
group by 部门
having min(业绩) = 7 and count(月份) = (select count(distinct 月份) t_月份
from A);

select max(co.cname) "课程名称",
count(sc.studentid) "总人数",
sum(decode(sign(sc.score - 60), -1, 1, 0)) "[<60)人数",
sum(decode(sign(sc.score - 60),
-1,
0,
decode(sign(sc.score - 70), 1, 0, 1))) "[60,70]人数",
sum(decode(sign(sc.score - 70), 1, 1, 0)) "[>70)人数"
from stscore sc, course co
where sc.courseid = co.courseid
group by co.courseid
order by co.courseid;




select A.name, A.kecheng, A.fenshu
from A,
(select name
from A
group by name
having min(fenshu) >= 60 and count(kecheng) = (select count(distinct
kecheng)
from A)) t
where A.name = t.name;

select *
from A
group by 部门
having min(业绩) > 7 and count(月份) = (select count(distinct 月份) from A);

select t3.作者姓名, t2.图书名, t2.出版社
from (select t1.作者编号, t1.作者姓名
from t1
where t1.年龄 < (select avg(t1.年龄) from t1)) t3,
t2
where t3.作者编号 = t2.作者编号;

 

select st.sno "学号",
st.sname "姓名",
co.cname "课名",
co.cteacher "课程老师",
sc1.score "成绩"
from student st,
(select sc.sno from score sc group by sc.sno having count(sc.cno) > 2) t,
course co,
score sc1
where st.sno = t.sno
and co.cno = sc1.cno
and t.sno = co.sno;


select 日期,
sum(decode(结果, '胜', 1, 0)) "胜",
sum(decode(结果, '负', 1, 0)) "负"
from A
group by 日期;

select max(t.t_score) m_t_score
from student st,
(select sc.sno, sum(sc.score) t_score from scour sc group by sc.sno) t
where st.sno = t.sno
group by st.sname, st.sex;


select st1.sno "学号",
st1.sname "姓名",
st1.ssex "性别",
t1.t_score "总成绩"
from student st1,
(select sc.sno, sum(sc.score) t_score from scour sc group by sc.sno) t1,
(select max(t.t_score) m_t_score
from student st,
(select sc.sno, sum(sc.score) t_score
from scour sc
group by sc.sno) t
where st.sno = t.sno
group by st.sname, st.sex) t2
where st1.sno = t1.sno
and t1.t_score = t2.m_t_score;