《1》子查詢和關聯查詢
建立表如下:
學生基本信息表
CREATE Student(
[Studentid][Int]IDENTITY(1,1)NOT NULL primary key,--主鍵
[StudentName][char]NOT NULL
)
課程信息表
CREATE Subject(
[SubjectID][char]NOT NULL primary key, --主鍵
[SubjectName][char]NOT NULL
)
成績表
CREATE Grade(
[Studentid][Int]NOT NULL, --聯合主鍵
[SubjectID][char]NOT NULL, --聯合主鍵
[Grade] [INT]NOT NULL,
primary key (studentid,subjectid)
)
1.將建表命令改為ORACLE語句在ORACLE中建表
create table student( --學生表
studentid number(3) primary key, --學生編號
studentname varchar2(20) --學生的姓名
);
create table subject( --課程表
subjectid char(3) primary key, --課程編號
subjectname varchar2(20) --課程的名字
);
create table grade( --分數表
studentid number(3) references student(studentid), --學生id
subjectid char(3) references subject(subjectid), --課程id
mark number(3), --分數
primary key (studentid,subjectid) --聯合主鍵
);
insert into student values (101,'張三');
insert into student values (102,'李云');
insert into student values (103,'未');
insert into subject values ('A01','C++');
insert into subject values ('A02','ASP');
insert into subject values ('A03','JAVA');
insert into grade values (101,'A01',59);
insert into grade values (101,'A02',72);
insert into grade values (101,'A03',90);
insert into grade values (102,'A01',75);
insert into grade values (102,'A02',91);
insert into grade values (103,'A01',71);
2.作如下4題
第一問：查詢出以下信息
學號 學生姓名 課程名稱 成績 （要全部學生信息）
關聯查詢 (多張表的)
別名
select a.studentid as "學 號",studentname "學生姓名",
subjectname "課程名稱",mark "成 績"
from student a , subject b , grade c
where a.studentid = c.studentid
and b.subjectid = c.subjectid;
[select a.studentid "學 號",studentname "學生姓名",
subjectname "課程名稱",mark "成 績"
from student a , subject b , grade c] 笛卡爾積
3 * 3 * 6 = 54;
第二問：查詢出以下信息
學號 學生姓名 課程名稱 成績（只顯示每科最高分）
select a.studentid "學 號",studentname "學生姓名",
subjectname "課程名稱",mark "成 績"
from student a,subject b,grade c
where a.studentid = c.studentid
and b.subjectid = c.subjectid
and (subjectname,mark)
in (select subjectname "課程名稱",max(mark) "成 績"
from student a,subject b,grade c
where a.studentid = c.studentid
and b.subjectid = c.subjectid
group by subjectname)
(最高分---分數比我高的學生的人數=0)
select a.studentid "學 號",studentname "學生姓名",
subjectname "課程名稱",mark "成 績"
from student a,subject b,grade c
where a.studentid = c.studentid
and b.subjectid = c.subjectid
and (select count(*) from grade
where subjectid = b.subjectid and
mark > c.mark) = 0
第三問：查詢出以下信息
學號 學生姓名 課程名稱 成績 （成績大于60時的顯示及格，小于60時的顯示不及格）
select a.studentid "學 號",studentname "學生姓名",
subjectname "課程名稱",
decode(sign(mark-60),-1,'不及格','及格') "成 績"
from student a,subject b,grade c
where a.studentid = c.studentid
and b.subjectid = c.subjectid
第四問：查詢出以下信息
學號 學生姓名 （查詢出課程超過1門以上學生的信息）
select a.studentid "學 號",studentname "學生姓名",
count(subjectname)
from student a , subject b , grade c
where a.studentid = c.studentid
and b.subjectid = c.subjectid
group by a.studentid,studentname
having count(subjectname) >= 2
《2》復制一張表的結構
--數據一起復制
create table mydept as select * from dept;
--只復制結構
create table mydept1 as select * from dept where 1=2;
--把數據從一個表復制到另一個結構相同的表
insert into mydept1 select * from dept;
--只復制部分數據
insert into mydept1 select * from dept where deptno>20;
insert into mydept1(deptno,loc) select deptno,loc
from dept;
--表的約束是復制不過來的，需要自己去添加
--列的別名
select ename "員工 姓名" from emp;
a.沒有主鍵的表如何消重復記錄
create table test(
id number(2),
name varchar2(10));
insert into test values (1,'aa');
insert into test values (1,'aa');
insert into test values (1,'aa');
insert into test values (2,'bb');
insert into test values (3,'cc');
create table test1 as select distinct * from
test;
或者
create table test1 as select id,name from test
group by id,name;
rename test to test2;
rename test1 to test;
b.有主鍵消重復行
create table test(
id number(2) primary key,
name varchar2(10));
insert into test values (1,'aa');
insert into test values (2,'aa');
insert into test values (3,'aa');
insert into test values (4,'bb');
insert into test values (5,'cc');
create table test1 as select name from test
group by name;
alter table test1 add (id number(2));
update test1 set id=(select max(id) from test
where name=test1.name);
create table test2 as select id,name from test1;
rename test to testb;
rename test2 to test;
alter table test add primary key (id);
<<2>>SQL*PLUS中的環境設置命令
不需要分號 就可以執行 --- SQL*PLUS的環境命令
需要分號結束 才能執行的 -- SQL命令 (和SQLserver是一樣的)
connect system/manager
show user
spool c:\aa.txt
spool off
clear screen
set escape on 打開轉義功能
set linesize 1000 設置一行顯示多少個字符
set pagesize 20 設置一頁顯示多少行
-------
define aa = 10 定義一個變量 aa = 10
define bb='MANAGER' 定義一個字符 bb = 'MANAGER'
prompt Hello World 原樣輸出prompt后的字符串
set feedback off 關閉系統反饋的信息
set heading off 列標題關閉
set pagesize 0 不分頁
set autocommit on 設置自動提交打開
set timing on 打開時間統計
set time on 打開一個時鐘
------
a) 自動生成一個腳本
利用SQL*PLUS環境命令 生成腳本文件
set heading off --關閉列的標題
set feedback off --關閉反饋信息
spool c:\aa.txt --緩沖寫文件c:\aa.txt
select 'insert into dept values ('||deptno||
','||''''||dname||''''||','||''''||loc||''''||');' from dept; --執行select語句
spool off --關閉緩沖
用處:在備份 恢復 數據庫管理等方面有用處
<4>ORACLE中建立數據庫對象
表
約束
序列 sequence --- 自動編號 ---- IDENTITY (SQLSERVER)
<1>建立序列
create sequence seq1; 從1開始 每次自動增加1 沒有最大值
<2>怎么使用
select seq1.nextval from dual;
nextval 偽列 下一個值
select seq1.currval from dual;
currval 偽列 當前值
create sequence seq2
start with 1000 --起始值1000
increment by 2 --步長2
maxvalue 9000 --最大值9000
minvalue 900 --最小值900
cycle --循環序列
1000開始
每次增加2 一直到9000
回到900 重新開始
<3>和表關聯作為表的字段的值
a) create table student(
xh number(4) primary key, --學號
xm varchar2(20) not null --姓名
);
要求:學號字段 從1000開始每次增加4 最大值9999
--建立序列
create sequence xh_seq
start with 1000 --從1000開始
increment by 4 --每次增加4
maxvalue 9999 --最大值 9999
;
--使用SQL語句關聯
insert into student values
(xh_seq.nextval,'Mike');
insert into student values
(xh_seq.nextval,'John');
insert into student values
(xh_seq.nextval,'Rose');
--特點:能產生唯一的值,但是不能保證值在表中的連續性
b) create table teacher(
teacherxh varchar2(10) primary key,
teachername varchar2(20)
);
要求:教師的編號的格式是TH00001
TH00002
....
--建立序列
create sequence teacher_seq
maxvalue 99999;
--
insert into teacher values ('TH'||
ltrim(to_char(teacher_seq.nextval,'00000')),
'張三');
insert into teacher values ('TH'||
ltrim(to_char(teacher_seq.nextval,'00000')),
'李');
insert into teacher values ('TH'||
ltrim(to_char(teacher_seq.nextval,'00000')),
'王');
視圖 (view)
--建立視圖(用查詢語句 select empno,ename,sal from emp建立了一個emp_v1的視圖 )
create or replace view emp_v1 as --select語句
select empno,ename,sal from emp;
視圖的作用:隱藏數據,增加系統的安全性
視圖中是否存放了數據??? 不存放數據,只存放查詢
保證了 查詢到的數據是和表
中的數據是一致的
select * from emp_v1;
<1>emp_v1先看是否是表
<2>不是表 是否是視圖
<3> select * from (select empno,ename,sal from emp);
<4>執行得到結果
能否通過視圖去改表中的數據呢???
有條件的可以:
<1>建立視圖的select語句必須是簡單的select語句
簡單:不能是多表的查詢
不能有分組函數
<2>建立視圖的時候不能帶with readonly關鍵字
create or replace view emp_v2 as --select語句
select empno,ename,sal from emp
with read only;
可以改的情況:
1)create or replace view emp_v1 as --select語句
select empno,ename,sal,comm from emp;
update emp_v1 set comm = 1000
where empno=7934;
select * from emp; --發現數據修改了
2)create or replace view emp_v3
as
select empno,ename,hiredate,sal,deptno
from emp
where deptno = 10;
update emp_v3 set deptno=20 where empno=7782;
select * from emp_V3; --7782的數據沒有了
3)