一、練習的表結構
?
emp empno ?ename ?job mgr hiredate sal comm deptno
dept deptno dname loc
salgrade grade losal hisal
?
二、創建表
?
CREATE TABLE EMP
? ? ? ?(EMPNO NUMBER(4) NOT NULL,
? ? ? ? ENAME VARCHAR2(10),
? ? ? ? JOB VARCHAR2(9),
? ? ? ? MGR NUMBER(4),
? ? ? ? HIREDATE DATE,
? ? ? ? SAL NUMBER(7, 2),
? ? ? ? COMM NUMBER(7, 2),
? ? ? ? DEPTNO NUMBER(2));
?
?
INSERT INTO EMP VALUES
? ? ? ? (7369, 'SMITH', ?'CLERK', ? ? 7902,
? ? ? ? TO_DATE('17-DEC-1980', 'DD-MON-YYYY','NLS_DATE_LANGUAGE=American'), ?800, NULL, 20);
INSERT INTO EMP VALUES
? ? ? ? (7499, 'ALLEN', ?'SALESMAN', ?7698,
? ? ? ? TO_DATE('20-FEB-1981', 'DD-MON-YYYY','NLS_DATE_LANGUAGE=American'), 1600, ?300, 30);
INSERT INTO EMP VALUES
? ? ? ? (7521, 'WARD', ? 'SALESMAN', ?7698,
? ? ? ? TO_DATE('22-FEB-1981', 'DD-MON-YYYY','NLS_DATE_LANGUAGE=American'), 1250, ?500, 30);
INSERT INTO EMP VALUES
? ? ? ? (7566, 'JONES', ?'MANAGER', ? 7839,
? ? ? ? TO_DATE('2-APR-1981', 'DD-MON-YYYY','NLS_DATE_LANGUAGE=American'), ?2975, NULL, 20);
INSERT INTO EMP VALUES
? ? ? ? (7654, 'MARTIN', 'SALESMAN', ?7698,
? ? ? ? TO_DATE('28-SEP-1981', 'DD-MON-YYYY','NLS_DATE_LANGUAGE=American'), 1250, 1400, 30);
INSERT INTO EMP VALUES
? ? ? ? (7698, 'BLAKE', ?'MANAGER', ? 7839,
? ? ? ? TO_DATE('1-MAY-1981', 'DD-MON-YYYY','NLS_DATE_LANGUAGE=American'), ?2850, NULL, 30);
INSERT INTO EMP VALUES
? ? ? ? (7782, 'CLARK', ?'MANAGER', ? 7839,
? ? ? ? TO_DATE('9-JUN-1981', 'DD-MON-YYYY','NLS_DATE_LANGUAGE=American'), ?2450, NULL, 10);
INSERT INTO EMP VALUES
? ? ? ? (7788, 'SCOTT', ?'ANALYST', ? 7566,
? ? ? ? TO_DATE('09-DEC-1982', 'DD-MON-YYYY','NLS_DATE_LANGUAGE=American'), 3000, NULL, 20);
INSERT INTO EMP VALUES
? ? ? ? (7839, 'KING', ? 'PRESIDENT', NULL,
? ? ? ? TO_DATE('17-NOV-1981', 'DD-MON-YYYY','NLS_DATE_LANGUAGE=American'), 5000, NULL, 10);
INSERT INTO EMP VALUES
? ? ? ? (7844, 'TURNER', 'SALESMAN', ?7698,
? ? ? ? TO_DATE('8-SEP-1981', 'DD-MON-YYYY','NLS_DATE_LANGUAGE=American'), ?1500, NULL, 30);
INSERT INTO EMP VALUES
? ? ? ? (7876, 'ADAMS', ?'CLERK', ? ? 7788,
? ? ? ? TO_DATE('12-JAN-1983', 'DD-MON-YYYY','NLS_DATE_LANGUAGE=American'), 1100, NULL, 20);
INSERT INTO EMP VALUES
? ? ? ? (7900, 'JAMES', ?'CLERK', ? ? 7698,
? ? ? ? TO_DATE('3-DEC-1981', 'DD-MON-YYYY','NLS_DATE_LANGUAGE=American'), ? 950, NULL, 30);
INSERT INTO EMP VALUES
? ? ? ? (7902, 'FORD', ? 'ANALYST', ? 7566,
? ? ? ? TO_DATE('3-DEC-1981', 'DD-MON-YYYY','NLS_DATE_LANGUAGE=American'), ?3000, NULL, 20);
INSERT INTO EMP VALUES
? ? ? ? (7934, 'MILLER', 'CLERK', ? ? 7782,
? ? ? ? TO_DATE('23-JAN-1982', 'DD-MON-YYYY','NLS_DATE_LANGUAGE=American'), 1300, NULL, 10);
?
CREATE TABLE DEPT
? ? ? ?(DEPTNO NUMBER(2),
? ? ? ? DNAME VARCHAR2(14),
? ? ? ? LOC VARCHAR2(13) );
?
INSERT INTO DEPT VALUES (10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO DEPT VALUES (20, 'RESEARCH', ? 'DALLAS');
INSERT INTO DEPT VALUES (30, 'SALES', ? ? ?'CHICAGO');
INSERT INTO DEPT VALUES (40, 'OPERATIONS', 'BOSTON');
?
CREATE TABLE SALGRADE
? ? ? ? (GRADE NUMBER,
? ? ? ? ?LOSAL NUMBER,
? ? ? ? ?HISAL NUMBER);
?
INSERT INTO SALGRADE VALUES (1, ?700, 1200);
INSERT INTO SALGRADE VALUES (2, 1201, 1400);
INSERT INTO SALGRADE VALUES (3, 1401, 2000);
INSERT INTO SALGRADE VALUES (4, 2001, 3000);
INSERT INTO SALGRADE VALUES (5, 3001, 9999);
?
?三、SQL練習
?
1 、desc+表名 查看表結構
2、
?
SQL> desc dual
?名稱 ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?是否為空? 類型
?----------------------------------------- -------- ----------------------------
?
?DUMMY ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?VARCHAR2(1)
3、select ename,sal*12 sal_year from emp;//查詢結果列名為大寫
? ? ?select ename,sal*12 "sal_year" from emp;//查詢結果列名引號內“sal_yeal”小寫，ename大寫
4、
任何含有空值的數學表達式計算結果都為空值
5、
字符串連接符?
? select ename||sal from emp;//結果為ename和sal拼接成的字母串 如SMITH800；列名為ENAME||NAME
? ??
? ?select ename||'pinjiezifuchuan'from emp;//結果為SMITHpinjiezifuchuan 列名為ENMAE||'PINJIEZIFUCHUAN'
? ? ? ?select ename||‘pinjie
''
zfuchuan’ from emp;//如果拼接的字符串中有單引號 則要進行類似轉義 即要寫兩個單引號
? ??
? ? 結果為SMITHpinjie'zifuchuan ?列名為ENAME||PINJIE''ZIFUCHUAN
6、去除相同記錄select distinct deptno from emp;//去除deptno相同的記錄
? ??
? ?select distinct deptno,job from emp;//去除deptno job均相同的記錄
7、是否為空 ?select ename from emp where comm is null;
8、日期的處理
? ? select ?ename,hiredate from emp ?where hiredate>'20-2月-81'
9、通配符
? ? select ename from emp where ename like '%\%%';//選中名字中含有%的人的名字 中間用了轉移字符
? ? select ename from emp where ename like '%$%%' escape '$'//自己制定轉義字符 $
10、字符截取
? ? select substr(ename,1,3) from emp;//列名SUBSTR
11、
? ? select count(distinct deptno) from dept 去除重復值之后所剩的記錄數
12、薪水最高的員工的名字
? ? select ename from emp where sal=(select max(sal) from emp);
13、每個部門薪水最高的員工的名字
? ? 出現在查詢列表中的列名必須在組函數或者Group By中,因此這里需要用到連接表
? ? select ename,deptno from emp join?
(select max(sal) max_sal,deptno from emp group by deptno) t
on (emp.sal=t.max_sal and emp.deptno=t.deptno);
14、查詢平均工資大于2000的部門的平均工資和部門編號
? ? select avg(sal) deptno from emp group by deptno having avg(sal)>2000;
15、（自連接）求某位員工及其經理人的名字(mgr是經理人編號)
? ? select ?e1.name ,e2.name from emp e1,emp e2 where e1.mgr=e2.empno;
16、
? ??? select ename,dname,grade from emp e,dept d, salgrade s
? ? ? where e.deptno = d.deptno and e.sql between s.losal and s.hisal and
? ? ? job<>'CLERK';
? ? ??
? ? ??
有沒有辦法把過濾條件和連接條件分開來?
?出于這樣考慮,Sql1999標準推出來了.有許多人用的還是
? ? ? 舊的語法,所以得看懂這種語句.
? ? ? select ename,dname from emp,dept;(舊標準).
? ??
??
?交叉連接 笛卡爾乘積
? ? ? select ename,dname from emp cross join dept;(1999標準)
? ? ? ?新的語法，連接條件不會再放在where語句中，where語句中只寫數據過濾條件
? ? ? select ename,dname from emp,dept where emp.deptno=dept.deptno (舊)?
? ? ? select ename,dname from emp join dept on(emp.deptno = dept.deptno); 1999標準.沒有Where語句.
? ? ? select ename,dname from emp join dept using(deptno);等同上句,但不推薦使用.因為使用using有很多假設，假設兩個表必須有deptno 而且類型必須相同
? ? ??
? ? ?
?select ename,grade from emp e join salgrade s on(e.sal between s.losal and s.hisal);?
? ? ? join 連接語句, on過濾條件。連接，條件一眼分開。如果用Where語句較長時，連接語句和過濾語句混在一起。
? ? ??
? ? ? 三張表連接：
? ? ? slect ename,dname, grade from?
? ? ? emp e join dept d on(e.deptno=d.deptno)
? ? ? join salgrade s on(e.sal between s.losal and s.hisal)
? ? ? where ename not like '_A%';
? ? ? 把每張表連接 條件不混在一起，然后數據過濾條件全部區分開來。讀起來更清晰，更容易懂一點。
? ? ??
? ? ? select e1.ename,e2.ename from emp e1 join emp e2 on(e1.mgr = e2.emptno);
?
? ? ? 左外連接：
會把左邊這張表多余數據顯示出來，也就是不能與另外一張表產生連接的表連接起來。
? ? ? select e1.ename,e2,ename from emp e1 left join emp e2 on(e1.mgr =e2.empno);left 后可加outer
? ? ? 右外連接：
? ? select ename,dname from emp e right outer join dept d on(e.deptno =d.deptno); outer可以取掉。
? ? ? ??
? ? ? 即把左邊多余數據，也把右邊多余數據拿出來，全外連接。
? ? ? select ename,dname from emp e full join dept d on(e.deptno =d.deptno);?
17、求部門平均薪水的等級
? ? select deptno,avg_sal,grade from (seclet deptno , avg(sal) avg_sal from emp group by ?deptno ) t ?join salgrade ?s on (t.avg_sal between s.losal and s.hisal )
18、求部門平均的薪水等級
? ? select deptno,avg(grade) from (select deptno,grade from
?salgrade?join?emp?on(emp.sal between losal and hisal)) group by deptno.
19、雇員中有哪些人是經理人
? ? select ename from emp where empto in(select ?distinct mgr from emp);
20、不準用組函數，求薪水的最高值
? ? select sal from emp where sal not in ( select distinct e1.sal from emp e1 join emp e2 ?on( e1.sal<e2.sal));
21、求平均薪水最高的部門的部門編號
?
? ??
select deptno,avg_sal from (select deptno, avg(sal) avg_sal from emp group by deptno )?
where avg_sal=
(select max(avg_sal) from (?select deptno,avg(sal) avg_sal from emp group by deptno
)) 藍色部分求出的所有部門中平均薪水最高的的部門的平均薪水?
紅色部分是求出平均工資等于這個最高平均薪水的部門的編號和平均工資
?
22、求平均薪水最高的部門的部門的部門名稱(利用上面求出的部門編號)
select dname from dept where deptno=(
select deptno from (select deptno, avg(sal) avg_sal from emp group by deptno )?
where avg_sal=
(select max(avg_sal) from (?select deptno,avg(sal) avg_sal from emp group by deptno
)))
?
23、求平均薪水的等級最低的部門的部門名稱
? ? select dname, grade,t1.deptno,avg_sal from
(
select ?deptno, avg(sal) avg_sal ,grade from
? ? (
select deptno, avg(sal) avg_sal from
?emp ?group by deptno?) t//表t是每個部門的平均工資
? ? join
? ??
? ? salgrade s on(t.avg_sal between losal and hisal)//表t與表s連接求出部門平均工資所在的等級
)t1//該表t1現在求出每個部門的平均工資和平均工資等級 部門編號 但是沒有部門名稱因此需要在連接一個表
join dept ?on (t1.deptno=dept.deptno)//至此求出了每個部門的平均薪水 薪水等級 部門編號 部門名稱 但是薪水等級不是最低
//因此需要再次求出薪水等級最低的那個等級號碼
where t1.grade=
(
? ??
select min(grade) from//從上面的表t1便可以了,但是不能直接寫t1,后面可以用創建視圖的方法簡化
? ??
(
?
? ???? select deptno ,avg(sal) avg_sal,grade from
??
? ??
??
?
(
select deptno, avg(sal) avg_sal from
?emp ?group by deptno?) ?t
??
? ??? join
? ??
? ??? ? salgrade s on(t.avg_sal between losal and hisal)
? ??
)
)
24、創建視圖, 虛表保存部門平均工資 部門編號，平均工資等級
create view?
v$
_dept_avg_sal_info as
select ?deptno, avg(sal) avg_sal ,grade from
? ? (
select deptno, avg(sal) avg_sal from
?emp ?group by deptno?) t
? ? join
? ??
? ? salgrade s on(t.avg_sal between losal and hisal)
則上面23的查詢語句可以改為：
? ? select dname,grade, t1.deptno,avg_sal from v$_dept_avg_sal_info t1 join dept on(t1.deptno=dept.deptno)
where t1.grade=(select min(grade) from v$_dept_avg_sal_info
)
24、求部門經理人中平均薪水最低的部門名稱
25、求比普通員工最高薪水還高的經理人名稱
首先求出普通員工的最高薪水
?
?select max(sal) from emp ?where empno not in(select distinct mgr from emp where mgr is ?not ?null)
再求出經理人名稱
select ename from emp where ?empno ?in
( select distinct mgr from emp where mgr is not null) and
sal>
(
? ??select max(sal) from emp ?where empno not in(select distinct mgr from emp where mgr is ?not ?null)
)
26、? 創建新用戶
backup scott 備份scott用戶的所有表 數據資源
exp 導出
create user liuchao identified by liuchao default tablespace users ?quota 10M on users
//用戶名密碼均為liuchao 默認表空間users?
grant create session，create table,create vision to liuchao//給予登錄 創建表、創建視圖權限
imp 導入
27、回滾
rollback
28、復制表
create table dept2 as select * from dept;
29、求薪水最高的前五名雇員
?
? ??SQL> select empno,ename from emp;
?
? ? ?EMPNO ENAME ?ROWNUM(這個字段是默認隱藏的給的是行號)
---------- ----------
? ? ? 7369 SMITH
? ???
? ? ? 7499 ALLEN
? ? ? 7521 WARD
? ? ? 7566 JONES
? ? ? 7654 MARTIN
? ? ? 7698 BLAKE
? ? ? 7782 CLARK
? ? ? 7788 SCOTT
? ? ? 7839 KING
? ? ? 7844 TURNER
? ? ? 7876 ADAMS
?
? ? ?EMPNO ENAME
---------- ----------
? ? ? 7900 JAMES
? ? ? 7902 FORD
? ? ? 7934 MILLER
取前五行，但是只能<或者<= 不能用>或者>= 或者=設計原因不詳
select empno,ename from emp where rownum<=5
取第十行
select empno,empname from
(
? ? select rownum r,ename,empno from emp
)where r>=10;
將工資由高到低排序
select ename,sal ?from emp order by sal desc;
select ename,sal from
(
select ename,sal from emp order by sal desc
)where rownum<=5;
30、求薪水最高的第六個到第十個人
首先下面語句中R值還是原來的值，并不因為新的查詢結果而更新R的值
SQL> select ename,sal,rownum r ?from emp order by sal desc;
?
ENAME ? ? ? ? ? ? SAL ? ? ? ? ?R
---------- ---------- ----------
KING ? ? ? ? ? ? 5000 ? ? ? ? ?9
FORD ? ? ? ? ? ? 3000 ? ? ? ? 13
SCOTT ? ? ? ? ? ?3000 ? ? ? ? ?8
JONES ? ? ? ? ? ?2975 ? ? ? ? ?4
BLAKE ? ? ? ? ? ?2850 ? ? ? ? ?6
CLARK ? ? ? ? ? ?2450 ? ? ? ? ?7
ALLEN ? ? ? ? ? ?1600 ? ? ? ? ?2
TURNER ? ? ? ? ? 1500 ? ? ? ? 10
MILLER ? ? ? ? ? 1300 ? ? ? ? 14
WARD ? ? ? ? ? ? 1250 ? ? ? ? ?3
MARTIN ? ? ? ? ? 1250 ? ? ? ? ?5
?
ENAME ? ? ? ? ? ? SAL ? ? ? ? ?R
---------- ---------- ----------
ADAMS ? ? ? ? ? ?1100 ? ? ? ? 11
JAMES ? ? ? ? ? ? 950 ? ? ? ? 12
SMITH ? ? ? ? ? ? 800 ? ? ? ? ?1
只有這樣才會重新更新行號 很坑爹啊
SQL> select ename,sal ,rownum r from(select ename,sal from emp order by sal desc);
?
ENAME ? ? ? ? ? ? SAL ? ? ? ? ?R
---------- ---------- ----------
KING ? ? ? ? ? ? 5000 ? ? ? ? ?1
FORD ? ? ? ? ? ? 3000 ? ? ? ? ?2
SCOTT ? ? ? ? ? ?3000 ? ? ? ? ?3
JONES ? ? ? ? ? ?2975 ? ? ? ? ?4
BLAKE ? ? ? ? ? ?2850 ? ? ? ? ?5
CLARK ? ? ? ? ? ?2450 ? ? ? ? ?6
ALLEN ? ? ? ? ? ?1600 ? ? ? ? ?7
TURNER ? ? ? ? ? 1500 ? ? ? ? ?8
MILLER ? ? ? ? ? 1300 ? ? ? ? ?9
WARD ? ? ? ? ? ? 1250 ? ? ? ? 10
MARTIN ? ? ? ? ? 1250 ? ? ? ? 11
?
ENAME ? ? ? ? ? ? SAL ? ? ? ? ?R
---------- ---------- ----------
ADAMS ? ? ? ? ? ?1100 ? ? ? ? 12
JAMES ? ? ? ? ? ? 950 ? ? ? ? 13
SMITH ? ? ? ? ? ? 800 ? ? ? ? 14
因此這是可以求第六行到第十行
SQL> select ename,sal from
? 2 ?(
? 3 ? ? ? select ename,sal ,rownum r from(select ename,sal from emp order by sal desc)
? 4 ?)where r>=6 and r<=10;
?
ENAME ? ? ? ? ? ? SAL
---------- ----------
CLARK ? ? ? ? ? ?2450
ALLEN ? ? ? ? ? ?1600
TURNER ? ? ? ? ? 1500
MILLER ? ? ? ? ? 1300
WARD ? ? ? ? ? ? 1250
31、創建表
?create table stu
?(
?sno number(6) not null,
?sname varchar2(20),
?sex number(1),
?age number(3),
?sdate date,
?grade number(2) default 1
?classno number(4),
?email varchar2(50)
?);
32、oracle數據字典表
- user_tables
- user_views
- user_constraints
存儲數據字典表的表dictionary
?
SQL> select table_name from dictionary;查詢字典表
?
SQL> select table_name from user_tables;查詢表名
SQL> select view_name from user_views;查詢視圖名
SQL> select constraint_name from user_constraints;查詢約束名。
SQL> select constraint_name,table_name from user_constraints;查詢約束和約束加在那張表上
33、序列sequence
創建論壇的發帖紀錄表：
SQL> create table article(
? 2 ?id number,
? 3 ?title varchar2(1024),
? 4 ?content long
? 5 ?);
?
SQL> create sequence S_ARTICLE_ID
? 2 ?minvalue 1
? 3 ?maxvalue 9999999999999999999999
? 4 ?start with 1
? 5 ?increment by 1
? 6 ?nocache;
?
SQL> insert into article values (S_ARTICLE_ID.nextVal,'title','content');
SQL> select * from article;
?
? ? ? ? ID ? ??TITLE ? ? ?CONTENT
--------------------------------------------------------------------------------
? ? ? ? ?1 ? ?title ? ? ? ? ? ??content
?
?
?
?
?
?
?
?
更多文章、技術交流、商務合作、聯系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號聯系： 360901061
您的支持是博主寫作最大的動力，如果您喜歡我的文章，感覺我的文章對您有幫助，請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點擊下面給點支持吧，站長非常感激您！手機微信長按不能支付解決辦法：請將微信支付二維碼保存到相冊，切換到微信，然后點擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對您有幫助就好】元
