必須要開啟的Oracle服務
OracleDBConsoleorcl
OracleOraDb11g_home1TNSListener
OracleServiceORCL
Win 7：
Win 10：
建立表空間
create tablespace j83_db1
datafile ‘D:/xt_java83/Oracle/j83_db1.dbf’
size 100M autoextend
on next 20M maxsize unlimited
建立使用者
create user SNKIOD identified by 111;
賦予權限
注意：後來發現賦權有錯：賦予權限時可能會出現報錯（缺失或過時），直接采用評論區的方法賦權即可，複制粘貼Ok~！
GRANT
CONNECT, 連接配接
RESOURCE, 資源
DBA, 資料庫管理
unlimited tablespace, 無限表空間
CREATE SESSION, 建立會話
CREATE ANY SEQUENCE, 建立任何序列
CREATE ANY TABLE, 建立任何表
CREATE ANY VIEW , 建立任何視圖
CREATE ANY INDEX, 建立任何索引
CREATE ANY PROCEDURE, 建立任何過程
CREATE ANY DIRECTORY, 建立任何目錄
ALTER ANY TRIGGER, 修改任何觸發器
ALTER SESSION, 修改會話
ALTER ANY SEQUENCE, 修改任何序列
ALTER ANY TABLE, 修改任何表
ALTER ANY VIEW , 修改任何視圖
ALTER ANY INDEX, 修改任何索引
ALTER ANY PROCEDURE, 修改任何過程
ALTER ANY DIRECTORY, 修改任何目錄
DROP SESSION, 删除會話
DROP ANY SEQUENCE, 删除任何序列
DROP ANY TABLE, 删除任何表
DROP ANY VIEW , 删除任何視圖
DROP ANY INDEX, 删除任何索引
DROP ANY PROCEDURE, 删除任何過程
DROP ANY DIRECTORY, 删除任何目錄
SELECT ANY TABLE, 查詢任何表
SELECT ANY DICTIONARY, 查詢任何目錄
INSERT ANY TABLE, 增加任何表
UPDATE ANY TABLE, 修改任何表
DELETE ANY TABLE, 删除任何表
DEBUG ANY PROCEDURE, 調試任何過程
DEBUG CONNECT SESSION, 調試連接配接會話
exp_full_database, 導出
imp_full_database 導入
TO SNKIOD;
建立學生表
create table tb_student
(
id number(10) not null PRIMARY KEY,
stu_name VARCHAR2(50),
stu_gender VARCHAR2(2),
stu_birthday DATE DEFAULT SYSDATE
);
添加資料
INSERT INTO tb_student (id,stu_name,stu_gender) VALUES(1,‘張三’,‘男’);
INSERT INTO tb_student (id,stu_name,stu_gender) VALUES(2,‘李四’,‘男’);
INSERT INTO tb_student (id,stu_name,stu_gender) VALUES(3,‘王五’,‘男’);
建立成績表
create table tb_exam
(
id number(10) not null PRIMARY KEY,
exam_score number(20),
stu_id number(10) not null,
sub_id number(10) not null
);
添加資料
INSERT INTO tb_exam (id,exam_score,stu_id,sub_id) VALUES(1,90,1,1);
INSERT INTO tb_exam (id,exam_score,stu_id,sub_id) VALUES(2,65,2,1);
INSERT INTO tb_exam (id,exam_score,stu_id,sub_id) VALUES(3,78,1,2);
INSERT INTO tb_exam (id,exam_score,stu_id,sub_id) VALUES(4,83,3,1);
INSERT INTO tb_exam (id,exam_score,stu_id,sub_id) VALUES(5,100,2,2);
INSERT INTO tb_exam (id,exam_score,stu_id,sub_id) VALUES(6,21,3,2);
建立課程表
create table tb_subject
(
id number(10) not null PRIMARY KEY,
sub_name VARCHAR2(50)
);
添加資料
INSERT INTO tb_subject (id,sub_name) VALUES (1,‘Java’);
INSERT INTO tb_subject (id,sub_name) VALUES (2,‘Oracle’);
建立班級表
create table tb_class
(
id number(10) not null PRIMARY KEY,
class_name VARCHAR2(50)
);
添加資料
INSERT INTO tb_class (id,class_name) values (1,‘J83’);
INSERT INTO tb_class (id,class_name) values (2,‘W21’);
ALTER TABLE tb_student ADD ( class_id number(10) );
SELECT * FROM tb_student;
建立student序列:
create sequence STU_SEQUENCE
minvalue 1
maxvalue 9999999999999
start with 4
increment by 1
cache 20;
建立student觸發器:
CREATE OR REPLACE TRIGGER STU_TRIGGER
before insert on tb_student for each row
begin
select STU_SEQUENCE.nextval into :new.ID from dual;
end;
建立subject序列
create sequence SUB_SEQUENCE
minvalue 1
maxvalue 9999999999999
start with 3
increment by 1
cache 20;
建立subject觸發器
CREATE OR REPLACE TRIGGER SUB_TRIGGER
before insert on tb_subject for each row
begin
select SUB_SEQUENCE.nextval into :new.ID from dual;
end;
建立exam序列
create sequence EXAM_SEQUENCE
minvalue 1
maxvalue 9999999999999
start with 7
increment by 1
cache 20;
建立exam觸發器
CREATE OR REPLACE TRIGGER EXAM_TRIGGER
before insert on tb_exam for each row
begin
select EXAM_SEQUENCE.nextval into :new.ID from dual;
end;
建立class序列
create sequence CLASS_SEQUENCE
minvalue 1
maxvalue 9999999999999
start with 3
increment by 1
cache 20;
建立class觸發器:
CREATE OR REPLACE TRIGGER CLASS_TRIGGER
before insert on tb_class for each row
begin
select CLASS_SEQUENCE.nextval into :new.ID from dual;
end;
再次插入資料,不再指定id
INSERT INTO tb_student (stu_name,stu_gender，class_id) VALUES(‘春麗’,‘女’,2);
INSERT INTO tb_student (stu_name,stu_gender，class_id) VALUES(‘吉米’,‘女’,3);
INSERT INTO tb_exam (exam_score,stu_id,sub_id) VALUES(90,4,3);
INSERT INTO tb_exam (exam_score,stu_id,sub_id) VALUES(72,5,1);
INSERT INTO tb_subject (sub_name) VALUES (‘Web’);
INSERT INTO tb_class (class_name) VALUES (‘U12’);
增加
insert into tb_subject (sub_name) values (‘Ps’);
修改
update tb_subject set sub_name=‘Html’ where id =4;
删除
delete from tb_subject where id=4;
常量列與别名
select s.stu_name As 學生姓名,‘J83’ As 所屬班級 from tb_student s;常量列就是列名為所屬班級,值均為J83
單表查詢
SELECT * from tb_student
SELECT * from tb_student WHERE id=4;
SELECT * from tb_student s where s.id=2;
新增列(年齡)
ALTER TABLE tb_student ADD ( stu_age number(30) );
UPDATE tb_student s set stu_age=19 WHERE s.id=1;
UPDATE tb_student s set stu_age=21 WHERE s.id=2;
UPDATE tb_student s set stu_age=25 WHERE s.id=3;
UPDATE tb_student s set stu_age=18 WHERE s.id=4;
UPDATE tb_student s set stu_age=17 WHERE s.id=5;
SELECT * from tb_student s where s.stu_age>20;查找出年齡大于20歲的
SELECT * from tb_student s where s.stu_age<>25;查找出年齡不等于25歲的
SELECT * from tb_student s where s.stu_age>20 and s.stu_age<23;查找出年齡大于20歲且年齡小于23歲的
新增列(住址)
ALTER TABLE tb_student ADD ( stu_address VARCHAR2(50));
UPDATE tb_student s set stu_address=‘合肥市’ WHERE s.id=1;
UPDATE tb_student s set stu_address=‘南京市’ WHERE s.id=4;
SELECT * from tb_student s where stu_address is null and stu_age>20;查找出位址是空的且年齡大于20歲的
SELECT * from tb_student s where stu_age BETWEEN 20 and 25;查找出年齡在20~25歲之間的
SELECT * from tb_student s where (stu_age BETWEEN 20 and 25) and (stu_address is null);查找出年齡在20~25歲之間，且位址是空的
SELECT * from tb_student s where stu_name like ‘%張%’;模糊查詢
SELECT * from tb_student s WHERE stu_address in (‘北京市’,‘合肥市’);查找出位址在北京市或合肥市的
SELECT * from tb_student s ORDER BY s.stu_age asc;将年齡按照升序排列（預設升序）
SELECT * from tb_student s ORDER BY s.stu_age DESC;将年齡按照降序排列
SELECT * from tb_student s ORDER BY s.stu_age DESC,s.id asc;先按照年齡降序，再按id升序
分組查詢：按照位址分組，統計同一位址的學生人數小于2的城市
SELECT stu_address ,COUNT()
FROM tb_student GROUP BY stu_address
HAVING COUNT()<2;
分組查詢：按照班級id分組，統計同一班級的學生人數大于1的班級
SELECT class_id,COUNT()
FROM tb_student GROUP BY class_id
HAVING COUNT()>1;
SELECT DISTINCT stu_address FROM tb_student;去重
分頁查詢：查詢學生表的前2條資料（1、2），從1開始計算,不包括0
select * from ( select s., rownum RN from tb_student s ) where RN > 0 and RN < 3;
select * from ( select s., rownum RN from tb_student s ) where RN > 3 and RN <= 5;
設定外鍵
ALTER TABLE tb_student ADD CONSTRAINT FK_student_class FOREIGN KEY(class_id ) REFERENCES tb_class(id);
ALTER TABLE tb_exam ADD CONSTRAINT FK_exam_student FOREIGN KEY(stu_id )
REFERENCES tb_student(id);
ALTER TABLE tb_exam ADD CONSTRAINT FK_exam_subject FOREIGN KEY(sub_id )
REFERENCES tb_subject(id);
多表查詢:
三表交集查詢（内連接配接）：
SELECT s.stu_name,sub.sub_name ,e.exam_score
FROM tb_student s join tb_exam e ON (s.id=e.stu_id)
JOIN tb_subject sub ON (e.sub_id=sub.id);
左外連接配接：
SELECT s.stu_name,e.exam_score
FROM tb_student s LEFT OUTER JOIN tb_exam e on (s.id=e.stu_id);
SELECT s.stu_name,e.exam_score
FROM tb_student s,tb_exam e where (s.id(+)=e.stu_id);
右外連接配接：
SELECT s.stu_name,e.exam_score
FROM tb_student s RIGHT OUTER JOIN tb_exam e on (s.id=e.stu_id);
SELECT s.stu_name,e.exam_score
FROM tb_student s ,tb_exam e where (s.id=e.stu_id(+));
内連接配接：
SELECT c.class_name,s.stu_name
FROM tb_class c JOIN tb_student s on (c.id=s.class_id);
SELECT s.stu_name,e.exam_score
FROM tb_student s ,tb_exam e where (s.id=e.stu_id);
子查詢（嵌套查詢）
查詢課程名為Java成績大于等于75分的學生
select s.id , s.stu_name , temp.sub_id , temp.exam_score
from tb_student s join (
select e.stu_id , e.sub_id , e.exam_score from tb_exam e
where e.sub_id in (
select sub.id from tb_subject sub where sub.sub_name=‘Java’
) And e.exam_score>=75
) temp on (s.id = temp.stu_id);
聯合查詢
UNION— : 并集查詢，删除重複
—UNION ALL— : 并集查詢，不删除重複，是以快于union
select s.stu_name from tb_student s where id=4;
UNION all
select s.stu_name from tb_student s where id=3;
建立視圖:視圖可以主動增加原表資料，但是受限（多表連接配接）
GRANT CREATE ANY VIEW TO SNKIOD;權限
create or replace view vw_subjects As select * from tb_subject;
select * from vw_subjects;
INSERT INTO vw_subjects (id,sub_name) VALUES (4,‘Ps’);
create or replace view vw_stuSubExam As
select s.stu_name,sub.sub_name,e.exam_score from tb_student s join tb_exam e on (s.id=e.stu_id)
join tb_subject sub on (e.sub_id=sub.id);
select * from vw_stuSubExam ;