??ЧSQL???????
???????????? ???????[ 2013/6/18 10:28:10 ] ????????
????-->??Ч??
SELECT deptno?? AVG( sal )
FROM emp
WHERE deptno = 20
GROUP BY deptno;
scott@CNMMBO> SELECT deptno?? AVG( sal )
2 FROM emp
3 WHERE deptno = 20
4 GROUP BY deptno;
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
583 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
????11??С??????????
????-->???????????SQL????У????????????????
????-->??Ч??
SELECT *
FROM employees
WHERE department_id = (SELECT department_id
FROM departments
WHERE department_name = 'Marketing')
AND manager_id = (SELECT manager_id
FROM departments
WHERE department_name = 'Marketing');
????-->??Ч??
SELECT *
FROM employees
WHERE ( department_id?? manager_id ) = (SELECT department_id?? manager_id
FROM departments
WHERE department_name = 'Marketing')
????-->?????????е?????
????-->??Ч??
UPDATE employees
SET job_id = ( SELECT MAX( job_id ) FROM jobs )?? salary = ( SELECT AVG( min_salary ) FROM jobs )
WHERE department_id = 10;
????-->??Ч??
UPDATE employees
SET ( job_id?? salary ) = ( SELECT MAX( job_id )?? AVG( min_salary ) FROM jobs )
WHERE department_id = 10;
????12?????????
????-->??????????????????????????????????????????????Щ????????????????????
????13????EXISTS???IN
???????Щ?????????????У????????????????????????????????????????.??????????£????EXISTS????NOT EXISTS??????????????Ч???
????-->??Ч??
SELECT *
FROM emp
WHERE sal > 1000
AND deptno IN (SELECT deptno
FROM dept
WHERE loc = 'DALLAS')
????-->??Ч??
SELECT *
FROM emp
WHERE empno > 1000
AND EXISTS
(SELECT 1
FROM dept
WHERE deptno = emp.deptno AND loc = 'DALLAS')
????14????NOT EXISTS???NOT IN
???????????У?NOT IN???????????????????????????????????NOT IN??????Ч???????????????е???????????????????????????Σ?????????д?????????OUTTER JOIN????????NOT EXISTS
????-->??Ч??
SELECT *
FROM emp
WHERE deptno NOT IN (SELECT deptno
FROM dept
WHERE loc = 'DALLAS');
????-->??Ч??
SELECT e.*
FROM emp e
WHERE NOT EXISTS
(SELECT 1
FROM dept
WHERE deptno = e.deptno AND loc = 'DALLAS');
????-->??Ч???????????????Ч??????????????????loc????????????????dept???????????loc?д?????????????????????Ч??
SELECT e.*
FROM emp e LEFT JOIN dept d ON e.deptno = d.deptno
WHERE d.loc <> 'DALLAS'
??????
???·???
??????????????????
2023/3/23 14:23:39???д?ò??????????
2023/3/22 16:17:39????????????????????Щ??
2022/6/14 16:14:27??????????????????????????
2021/10/18 15:37:44???????????????
2021/9/17 15:19:29???·???????·
2021/9/14 15:42:25?????????????
2021/5/28 17:25:47??????APP??????????
2021/5/8 17:01:11