瀵?/p>
娴嬭瘯
浜哄憳鑰岃█蹇呴』鎺屾彙涓ょ璇█锛?/p>
绗竴绉嶆槸
DML
锛?/p>
鏁版嵁鎿嶇旱璇█
(Data Manipulation Language)
鏄?/p>
SQL
璇█涓紝
璐熻矗瀵?/p>
鏁版嵁搴?/p>
瀵硅薄杩愯鏁版嵁璁块棶
宸ヤ綔
鐨勬寚浠ら泦锛屼互
INSERT
銆?/p>
UPDATE
銆?/p>
DELETE
涓夌鎸囦护
涓烘牳蹇冿紝鍒嗗埆浠ｈ〃鎻掑叆銆佹洿鏂颁笌鍒犻櫎銆傜浜岀鏄細
DQL
锛屾暟鎹煡璇㈣瑷�
(Data Query Language)
鏄?/p>
SQL
璇█涓紝璐熻矗杩涜鏁版嵁鏌ヨ鑰屼笉浼氬鏁版嵁鏈韩杩涜淇敼鐨勮鍙ワ紝杩欐槸鏈�鍩烘湰鐨?/p>
SQL
璇彞銆傛牳蹇冩寚浠?/p>
涓?/p>
SELECT
锛屼互鍙婁竴浜涜緟鍔╂寚浠わ紝濡?/p>
FROM
銆?/p>
WHERE
绛夛紝
FROM
锛?/p>
琛ㄧず鏉ユ簮锛屽彲浠ユ惌閰?/p>
JOIN
鍋氶摼鎺ユ煡
璇紱
WHERE
锛?/p>
杩囨护鏉′欢锛?/p>
GROUP
BY
锛?/p>
鍦ㄤ娇鐢ㄨ仛鍚堝嚱鏁版椂鐢ㄥ埌锛?/p>
濡?/p>
SUM,COUNT,MAX,AVG
锛?/p>
HAVING
锛?/p>
瀵硅仛鍚堢粨鏋滆繘琛岀瓫閫夛紝杩欐槸鍜?/p>
WHERE
鐨勪笉鍚岀偣锛?/p>
ORDER
BY
锛?/p>
鎺掑簭銆?/p>
浠ヤ笅鏄繀椤绘帉鎻＄殑
SQL
涔犻锛?/p>
1
銆佸垪鍑鸿嚦灏戞湁涓�涓憳宸ョ殑鎵�鏈夐儴闂?/p>
select
d.*,ed.cou
from
dept
d,(select
deptno,count(empno)
cou
from
emp
group
by
deptno
having
count(empno)>1)
ed
where
d.deptno=ed.deptno;
2
銆佸垪鍑鸿柂閲戞瘮
鈥淪MITH鈥?/p>
澶氱殑鎵�鏈夊憳宸ャ�?/p>
路
姹傚嚭
SMITH
鐨勮柂閲?/p>
select
sal
from
emp
where
ename='SMITH';
路
姹傛墍鏈?/p>
select
*
from
emp
where
sal>(select
sal
from
emp
where
ename='SMITH');
3
銆佸垪鍑烘墍鏈夊憳宸ョ殑濮撳悕鍙婂叾鐩存帴涓婄骇鐨勫鍚?/p>
select
e.ename,m.ename
from
emp
e,emp
m
where
e.mgr=m.empno(+);
4
銆佸垪鍑哄彈闆囨棩鏈熸棭浜庡叾鐩存帴涓婄骇鐨勬墍鏈夊憳宸ョ殑缂栧彿锛屽鍚嶏紝閮ㄩ棬鍚嶇О
select
e.empno,e.ename,d.dname
from
emp
e,emp
m,dept
d
where
e.mgr=m.empno
and
e.hiredate<m.hiredate
and
e.deptno=d.deptno;
5
銆佸垪鍑洪儴闂ㄥ悕绉板拰杩欎簺閮ㄩ棬鐨勫憳宸ヤ俊鎭紝鍚屾椂鍒楀嚭閭ｄ簺娌℃湁鍛樺伐鐨勯儴闂紝
select
d.deptno,d.dname,e.empno,e.ename
from
dept
d,emp
e
where
d.deptno=e.deptno(+);
6
銆佸垪鍑烘墍鏈?/p>
鈥淐LERK鈥?/p>
浜哄憳鐨勫鍚嶅強鍏堕儴闂ㄥ悕绉帮紝閮ㄩ棬鐨勪汉鏁?/p>
select
e.ename,d.dname,ed.cou
from
emp
e,dept
d,(select
deptno,count(empno)
cou
from
emp
group
by
deptno)
ed
where
job='CLERK'and
e.deptno=d.deptno
and
ed.deptno=e.deptno;
7
銆佸垪鍑烘渶浣庤柂閲戝ぇ浜?/p>
1500
鐨勫悇绉嶅伐浣滃強浠庝簨姝ゅ伐浣滅殑鍏ㄩ儴闆囧憳浜烘暟
路
鎸夊伐浣滃垎缁?/p>
,
鍒嗙粍鏉′欢鏄渶浣庤柂閲戝ぇ浜?/p>
1500
select
job,min(sal)
from
emp
group
by
job
having
min(sal)>1500;
路
姹傚叏閮ㄧ殑闆囧憳浜烘暟
select
count(e.empno),e.job