{"id":438,"date":"2007-12-30T22:43:56","date_gmt":"2007-12-30T14:43:56","guid":{"rendered":"http:\/\/blog.smallken.ink\/2007\/12\/30\/uncategorized\/438.html"},"modified":"2007-12-30T22:43:56","modified_gmt":"2007-12-30T14:43:56","slug":"db2-9-sql-%e7%ad%86%e8%a8%98","status":"publish","type":"post","link":"https:\/\/blog.smallken.site\/?p=438","title":{"rendered":"DB2 9 SQL \u7b46\u8a18"},"content":{"rendered":"

SELECT<\/strong> <\/p>\n

\/\/ ‘ABC’ \u56fa\u5b9a\u503c<\/font>
SELECT deptno ,admrdept ,‘ABC’<\/strong> AS abc FROM department WHERE deptname LIKE ‘%ING%’ ORDER BY 1; <\/p>\n

\/\/ department.* department \u7684\u6240\u6709 Columns
<\/font>SELECT deptno ,department.*<\/strong> FROM department WHERE deptname LIKE   ‘%ING%’ ORDER BY 1;<\/p>\n

Fetch<\/strong> <\/p>\n

\/\/ fetch first 3 rows only \u53ea\u9078\u524d\u97623\u7b46, \u96a8\u6a5f3\u7b46<\/font>
SELECT years ,name ,id FROM staff FETCH FIRST 3 ROWS ONLY<\/strong>; <\/p>\n

\/\/ \u4f9d years \u905e\u6e1b\u6392\u5e8f, \u9078\u524d\u97623\u7b46<\/font>
SELECT years ,name ,id FROM staff WHERE years IS NOT NULL ORDER BY years DESC<\/strong> FETCH FIRST 3 ROWS ONLY; <\/p>\n

\/\/ \u4f9d years \u53ca id \u905e\u6e1b\u6392\u5e8f, \u9078\u524d\u97623\u7b46
<\/font>SELECT years ,name ,id FROM staff WHERE years IS NOT NULL ORDER BY  years DESC ,id DESC<\/strong> FETCH FIRST 3 ROWS ONLY;<\/p>\n

Correlation Name<\/strong><\/p>\n

\/\/ a \u548c b \u53eb Correlation Name, \u82e5\u6709\u8981\u91cd\u8986\u4f7f\u7528 table \u7684\u8a71,\u53ef\u4ee5\u4f7f\u7528 Correlation Name
<\/font>SELECT a.empno ,a.lastname FROM employee a<\/strong> ,(SELECT MAX(empno) AS  empno FROM employee) AS b<\/strong> WHERE a.empno = b.empno<\/strong>;<\/p>\n

Renaming Fields<\/strong> <\/p>\n

\/\/ AS \u7528\u4f86 Renaming Fields, \u5c31\u662f\u5217\u51fa\u4f86\u6b04\u4f4d\u7684\u6a19\u984c\uff0cRenaming Fields \u4e0d\u53ef\u4ee5\u4f7f\u7528\u5728GROUP BY\u3001WHERE\u3001HAVING<\/strong><\/font>
SELECT empno AS e_num<\/strong> ,midinit AS “m int”<\/strong> ,phoneno AS “…” <\/strong>FROM employee WHERE empno < ‘000030’ ORDER BY 1; <\/p>\n

Null Values<\/strong> <\/p>\n

\/\/ Null Values\uff0c\u4e0d\u7b49\u65bc\u7a7a\u767d\uff0c\u4f7f\u7528AVG()\u3001SUM()\u3001MAX()\u3001MIX() \u6642\u6703\u7565\u904eNULL<\/font>
SELECT AVG(comm) AS a1 ,SUM(comm) \/ COUNT(*) AS a2 FROM staff WHERE id < 100; <\/p>\n

\/\/ null \u662f\u7279\u6b8a\u7684\u4e00\u500b\u503c\uff0c\u662f\u4f7f\u7528 IS NULL\uff0cIS NOT NULL \u4f86\u6bd4\u8f03\uff0c\u800c\u975e\u4f7f\u7528 ‘=’<\/font>
SELECT id, comm FROM staff WHERE id < 100 AND id IS NOT NULL<\/font> AND omm IS NULL<\/font> AND NOT comm IS NOT NULL <\/font>ORDER BY id; <\/p>\n

Quotes<\/strong> <\/p>\n

\/\/ \u5728\u5b57\u4e32\u8868\u793a\u88e1\u97622\u500b\u55ae\u5f15\u865f’\u624d\u7b971\u500b\uff0c\u2192 JOHN JOHN’S ‘JOHN’S’ “JOHN’S”<\/font>
SELECT ‘JOHN’ AS J1 ,’JOHN”S’ AS J2 ,”’JOHN”S”’ AS J3 ,'”JOHN”S”‘ AS J4 FROM staff WHERE id = 10; <\/p>\n

Double-quotes<\/strong> <\/p>\n

<\/p>\n

\/\/ Renaming fields \u4f7f\u7528\u96d9\u5f15\u865f”\uff0c\u4e5f\u662f2\u500b\u96d9\u5f15\u865f\u624d\u7b971\u500b\u2192 USER ID D# #Y ‘TXT’ “quote” fld<\/font>
SELECT id AS “USER ID” ,dept AS “D#” ,years AS “#Y” ,’ABC’ AS “‘TXT'” ,'”‘ AS “””quote”” fld” FROM staff s WHERE id < 40 ORDER BY “USER ID”; <\/p>\n

ANY\u3001ALL<\/strong> <\/p>\n

\/\/ ANY\uff0cjob \u53ea\u8981\u7b26\u5408\u5f8c\u9762\u5168\u90e8\u6e05\u55ae\u5176\u4e2d\u4e00\u500b\uff1bALL\uff0cid \u5fc5\u9808\u5c0f\u65bc\u6216\u7b49\u65bc\u5f8c\u9762\u6e05\u55ae\u5168\u90e8
<\/font>SELECT id, job FROM staff WHERE job = ANY<\/strong> (SELECT job FROM staff) AND id <= ALL<\/strong> (SELECT id FROM staff) ORDER BY id; <\/p>\n

Multi-value Check<\/strong> <\/p>\n

\/\/ \u4e00\u6b21\u6bd4\u8f032\u500b\u503c
<\/font>SELECT id, dept, job FROM staff WHERE (id,dept) = (30,28)<\/strong> OR (id,years) = (90, 7)<\/strong> OR (dept,job) = (38,’Mgr’)<\/strong> ORDER BY 1; <\/p>\n

\/\/ \u4e00\u6b21\u6bd4\u8f032\u500b\u503c\u4e5f\u53ef\u4ee5\u5beb\u6210\u5982\u4e0b
<\/font>SELECT id, dept, job FROM staff WHERE (id = 30 AND dept = 28<\/strong>) OR (id = 90 AND years = 7<\/strong>) OR (dept = 38 AND job = ‘Mgr’<\/strong>) ORDER BY 1; <\/p>\n

BETWEEN<\/strong> <\/p>\n

\/\/ id \u5728\u4e0d\u5728 10~30 \u9019\u500b\u7bc4\u570d\uff0c\u5305\u542b10\u820730\u672c\u8eab\uff0c\u901a\u5e38\u8981\u524d\u9762\u6578\u5b57\u5c0f\uff0c\u5f8c\u9762\u6578\u5b57\u5927<\/font>
SELECT id, job FROM staff WHERE id BETWEEN 10 AND 30<\/strong> AND id NOT BETWEEN 30 AND 10 AND NOT id NOT BETWEEN 10 AND 30 ORDER BY id;<\/p>\n

<\/font> <\/p>\n

id BETWEEN 10 AND 30<\/strong> \u7b49\u65bc id>=10 and id<=30<\/strong> <\/p>\n

EXISTS<\/strong> <\/p>\n

\/\/ \u53ea\u8981\u53e6\u5916\u4e00\u500b table \u6709\u5b58\u5728\u7684\u8a71<\/font>
SELECT id, job FROM staff a WHERE EXISTS<\/strong> (SELECT * FROM staff b WHERE b.id = a.id AND b.id < 50) ORDER BY id; <\/p>\n

IN<\/strong><\/p>\n

\/\/ id \u6709\u7121\u5728 10 or 20 or 30<\/font>
SELECT id, job FROM staff a WHERE id IN (10,20,30)<\/strong> AND id IN (SELECT id FROM staff)<\/strong> AND id NOT IN 99<\/strong> ORDER BY id;<\/p>\n

\/\/ \u540c\u6642\u6bd4\u5c0d2\u6b04\u4f4d\uff0c\u6709\u7121\u7b26\u5408\u5728 select \u8a9e\u6cd5\u627e\u51fa\u4f86\u7684\u8cc7\u6599\u88e1\u9762<\/font>
SELECT empno, lastname FROM employee WHERE (empno, ‘AD3113’)<\/strong> IN (SELECT empno, projno FROM emp_act WHERE emptime > 0.5)<\/strong> ORDER BY 1;<\/p>\n

LIKE<\/strong><\/p>\n

\/\/ \u6bd4\u5c0d\u5b57\u4e32\uff0c_ \u4ee3\u88681\u500b\u5b57\u5143\uff0c% \u4ee3\u88680\u6216\u591a\u500b\u5b57\u5143<\/font>
SELECT id, name FROM staff WHERE name LIKE ‘S%n’<\/strong> OR name LIKE ‘_a_a%’<\/strong> OR name LIKE ‘%r_%a’<\/strong> ORDER BY id; <\/p>\n

\/\/ \u53ef\u81ea\u8a02 ESCAPE<\/font>
SELECT id FROM staff WHERE id = 10
AND ‘ABC’ LIKE ‘AB%’
AND ‘A%C’ LIKE  A\/%C’ ESCAPE ‘\/’<\/strong>
AND ‘A_C’ LIKE ‘A\\_C’ ESCAPE ‘\\’
<\/strong>AND ‘A_$’ LIKE ‘A$_$$’ ESCAPE ‘$’;<\/strong><\/p>\n

\/\/ \u5e7e\u500b match \u7684\u7bc4\u5217<\/font>
LIKE ‘AB%’<\/strong> Finds AB, any string
<\/strong>LIKE ‘AB%’<\/strong> ESCAPE ‘+’ Finds AB, any string
<\/strong>LIKE ‘AB+%’<\/strong> ESCAPE ‘+’ Finds AB%
<\/strong>LIKE ‘AB++’<\/strong> ESCAPE ‘+’ Finds AB+
<\/strong>LIKE ‘AB+%%’<\/strong> ESCAPE ‘+’ Finds AB%<\/strong>, any string
LIKE ‘AB++%’<\/strong> ESCAPE ‘+’ Finds AB+<\/strong>, any string
LIKE ‘AB+++%’<\/strong> ESCAPE ‘+’ Finds AB+%
<\/strong>LIKE ‘AB+++%%’<\/strong> ESCAPE ‘+’ Finds AB+%<\/strong>, any string
LIKE ‘AB+%+%%’<\/strong> ESCAPE ‘+’ Finds AB%%<\/strong>, any string
LIKE ‘AB++++’<\/strong> ESCAPE ‘+’ Finds AB++
<\/strong>LIKE ‘AB+++++%’<\/strong> ESCAPE ‘+’ Finds AB++%
<\/strong>LIKE ‘AB++++%’<\/strong> ESCAPE ‘+’ Finds AB++<\/strong>, any string
LIKE ‘AB+%++%’ <\/strong>ESCAPE ‘+’ Finds AB%+<\/strong>, any string <\/p>\n

And vs OR<\/font><\/strong><\/p>\n

\/\/ AND \u6bd4 OR \u5148\u505a\uff0c\u82e5\u6709()\u88e1\u9762\u5148\u505a<\/font>
SELECT * FROM table1 WHERE col1 = ‘C’ AND<\/strong> col1 >= ‘A’ OR <\/strong>col2 >= ‘AA’ ORDER BY col1; <\/p>\n

SELECT * FROM table1 WHERE col1 = ‘C’ AND<\/strong> (<\/strong>col1 >= ‘A’ OR<\/strong> col2 >= ‘AA’) <\/strong>ORDER BY col1; <\/p>\n

CAST<\/strong> <\/p>\n

\/\/ CAST \u578b\u614b\u8f49\u63db\uff0c\u82e5\u5f37\u5236\u8f49\u6210 INTEGER \u6703\u7121\u689d\u4ef6\u6368\u53bb\u5c0f\u6578\u9ede
<\/font>SELECT id ,salary ,CAST(salary AS INTEGER)<\/strong> AS sal2 FROM staff WHERE id < 30 ORDER BY id; <\/p>\n

\/\/ CHAR \u7684\u578b\u614b\u8f49\u63db\uff0c\u82e5\u9577\u5ea6\u592a\u9577\u6703\u88ab\u5207\u6389
<\/font>SELECT id ,job ,CAST(job AS CHAR(3))<\/strong><\/font> AS job2 FROM staff WHERE id < 30 ORDER BY id; <\/p>\n

VALUES<\/strong><\/p>\n

\/\/ \u4f7f\u7528 VALUES \u505a\u51fa\u7684\u66ab\u6642<\/strong>\u8cc7\u6599\u8868<\/font>
VALUES 6 <= 1 row, 1 column
VALUES (6) <= 1 row, 1 column
VALUES 6, 7, 8 <= 3 row, 1 columns
VALUES (6), (7), (8) <= 3 rows, 1 column
VALUES (6,66), (7,77), (8,NULL) <= 3 rows, 2 columns<\/p>\n

\/\/ WITH…VALUES \u53ef\u4ee5\u505a\u51fa\u66ab\u5b58\u7684 table\uff0c\u57f7\u884c\u7d50\u675f table \u5c31\u5931\u6548\uff0cex.\u641c\u5c0b\u822a\u73ed\u8868\u3000<\/font>
WITH<\/strong> temp1 (col1, col2) AS (VALUES ( 0, ‘AA’) ,( 1, ‘BB’) ,( 2, NULL))<\/strong>SELECT * FROM temp1; <\/p>\n

\/\/ temp2 \u7684\u8cc7\u6599\u81e8\u6642\u7531 temp1 \u5f97\u4f86<\/font>
WITH temp1<\/strong> (col1, col2, col3) AS
(VALUES ( 0, ‘AA’, 0.00),( 1, ‘BB’, 1.11),( 2, ‘CC’, 2.22))
,temp2 <\/strong>(col1b, colx) AS
(SELECT col1 ,col1 + col3 FROM temp1<\/strong>)
SELECT * FROM temp2<\/strong>; <\/p>\n

CASE<\/strong> <\/p>\n

\/\/ \u5728\u57f7\u884c\u904e\u7a0b\u4e2d\u9078\u64c7\u6539\u8b8a\u6307\u5b9a\u6b04\u4f4d\u7684\u503c\uff0c\u6709\u9ede\u50cfswitch<\/font>
SELECT Lastname ,sex AS sx
,CASE sex
   WHEN ‘F’ THEN ‘FEMALE’
   WHEN ‘M’ THEN ‘MALE’
   ELSE NULL
END<\/strong> AS sexx
FROM employee WHERE lastname LIKE ‘J%’ ORDER BY 1; <\/p>\n

\/\/ \u4e5f\u53ef\u4ee5\u5beb\u6210\u5982\u4e0b<\/font> sex = ‘F’<\/font>
<\/strong>SELECT lastname ,sex AS sx
,CASE
    WHEN sex = ‘F’ THEN ‘FEMALE’
    WHEN sex = ‘M’ THEN ‘MALE’
    ELSE NULL
END<\/strong> AS sexx
FROM employee WHERE lastname LIKE ‘J%’ ORDER BY 1; <\/p>\n

\/\/ midinit \u8207 sex \u6bd4\u8f03\uff0c\u53d6\u5927\u503c<\/font>
SELECT lastname ,midinit AS mi ,sex AS sx
,CASE
    WHEN midinit > sex   THEN midinit
    ELSE sex
END<\/strong> AS mx
FROM employee WHERE lastname LIKE ‘J%’ ORDER BY 1; <\/p>\n

\/\/ \u914d\u5408SUM()\u8070\u660e\u5730\u8a08\u7b97 Femail\u3001Male \u500b\u6578
<\/font>SELECT COUNT(*) AS tot
,SUM(CASE sex WHEN ‘F’ THEN 1 ELSE 0 END)<\/strong> AS #f
,SUM(CASE sex WHEN ‘M’ THEN 1 ELSE 0 END)<\/strong> AS #m
FROM employee WHERE lastname LIKE ‘J%’; <\/p>\n

ANSWER
=========
TOT #F #M
—   —  —
  3    1    2 <\/p>\n

\/\/ \u8f49\u63db\u503c\u904e\u7a0b\u4e2d\u78ba\u4fdd\u4e86 sex \u4e0d\u6703\u662f NULL \u503c
<\/font>SELECT lastname ,sex FROM employee WHERE lastname LIKE ‘J%’ AND CASE sex
    WHEN ‘F’ THEN ”
    WHEN ‘M’ THEN ”
    ELSE NULL
    END<\/strong> IS NOT NULL ORDER BY 1; <\/p>\n

\/\/ \u5de2\u72c0\u7d50\u69cb\uff0c\u4ee5\u90e8\u9580 dept \u8207\u5e74\u8cc7 years\uff0c\u6c7a\u5b9a comm \u503c<\/font>
UPDATE staff SET comm = <\/p>\n

CASE dept
     WHEN 15 THEN comm * 1.1
     WHEN 20 THEN comm * 1.2
     WHEN 38 THEN
          CASE
             WHEN years < 5 THEN comm * 1.3
             WHEN years >= 5 THEN comm * 1.4
             ELSE NULL
          END
     ELSE comm
END
<\/strong>WHERE comm IS NOT NULL
AND dept < 50;<\/p>\n

\/\/ \u907f\u514dNULL\u503c\u8b8a\u6210\u88ab\u9664\u6578\u800c\u9020\u6210\u932f\u8aa4<\/font>
WITH temp1 (c1,c2) AS (VALUES (88,9),(44,3),(22,0),(0,1))
SELECT c1 ,c2,
CASE c2
    WHEN 0 THEN NULL
    ELSE c1\/c2
END<\/strong> AS c3
FROM temp1;<\/p>\n

DML Statements ———–<\/strong><\/p>\n

INSERT<\/strong><\/p>\n

\/\/ \u63d2\u5165\u4e00\u7b46\u8cc7\u6599
<\/font>INSERT INTO<\/strong> emp_act VALUES <\/strong>(‘100000′ ,’ABC’ ,10 ,1.4 ,’2003-10-22′, 2003-11-24′);<\/p>\n

\/\/ \u4e00\u6b21\u63d2\u5165\u591a\u7b46\u8cc7\u6599\uff0c\u5176\u4e2d\u53ea\u8981\u4e00\u7b46\u5931\u6557\u5c31\u5168\u90e8\u5931\u6557\u5931\u6548<\/font>
INSERT INTO<\/strong> emp_act VALUES
<\/strong>(‘200000′ ,’ABC’ ,10 ,1.4 ,’2003-10-22′, ‘2003-11-24’)
,(‘200000′ ,’DEF’ ,10 ,1.4 ,’2003-10-22′, ‘2003-11-24’)
,(‘200000′ ,’IJK’ ,10 ,1.4 ,’2003-10-22′, ‘2003-11-24’); <\/p>\n

\/\/ NULL and DEFAULT \u662f\u95dc\u9375\u5b57\uff0cDEFAULT \u503c\u5728 CREATE table \u6642\u8a2d\u5b9a<\/font>
INSERT INTO<\/strong> emp_act VALUES
<\/strong>(‘400000′ ,’ABC’ ,10 ,NULL<\/strong> ,DEFAULT<\/strong>, CURRENT DATE) <\/p>\n

\/\/ \u53ef\u4ee5\u9078\u64c7\u6027\u5730\u53ea\u7d664\u500b\u6b04\u4f4d\u503c
<\/font>INSERT INTO<\/strong> emp_act (projno, emendate, actno, empno)<\/strong> VALUES
(‘ABC’ ,DATE(CURRENT TIMESTAMP) ,123 ,’500000′)<\/strong>; <\/p>\n

\/\/ INSERT \u7684\u8cc7\u6599\u662f\u5f9e\u53e6\u5916\u4e00\u500b table select \u51fa\u4f86\u7684<\/font>
INSERT INTO emp_act
SELECT LTRIM(CHAR(id + 600000)) ,SUBSTR(UCASE(name),1,6) ,salary \/ 229 ,123 ,CURRENT DATE ,’2003-11-11′ FROM staff WHERE id < 50<\/strong>; <\/p>\n

UPDATE<\/strong> <\/p>\n

\/\/ UPDATE table<\/em> SET \u7d66\u503c<\/font>
UPDATE<\/strong> emp_act
SET<\/strong> emptime = NULL
     ,emendate = DEFAULT
     ,emstdate = CURRENT DATE + 2 DAYS
     ,actno = ACTNO \/ 2
     ,projno = ‘ABC’
WHERE empno = ‘100000’; <\/p>\n

\/\/ SET \u7d66\u7684\u503c\u662f\u7528 select \u51fa\u4f86\u7684\u503c<\/font>
UPDATE<\/strong> emp_act
SET<\/strong> actno = (SELECT MAX(salary) FROM staff)
<\/strong>WHERE empno = ‘200000’; <\/p>\n

DELETE<\/strong> <\/p>\n

\/\/ DELECT FROM \u522a\u9664\u67d0\u4e00\u7b46\u8cc7\u6599\uff0c\u6307\u5b9a\u660e\u78ba\u8981\u522a\u54ea\u4e00\u7b46\u8cc7\u6599<\/font>
ELETE FROM<\/strong> emp_act WHERE <\/strong>empno = ‘000010’ AND projno = ‘MA2100’  AND actno = 10; <\/p>\n

\/\/ \u6307\u5b9a\u4e0d\u660e\u78ba\u689d\u4ef6\uff0c\u6703\u5168\u522a\u9664
<\/font>DELETE FROM<\/strong> emp_act; <\/p>\n

\/\/ \u914d\u5408\u53e6\u5916\u4e00\u500b table \u4f86\u6c7a\u5b9a\u522a\u4e0d\u522a
<\/font>DELETE FROM<\/strong> staff s1
WHERE id NOT IN (SELECT MAX(id) FROM staff s2 WHERE s1.dept =  2.dept);<\/strong><\/p>\n

\/\/ OLD TABLE<\/strong> \u770b\u9084\u6c92INSERT\u4e4b\u524d\u7684table
NEW TABLE<\/strong> \u770bINSERT\u4e4b\u5f8c\u7684table
FINAL TABLE<\/strong> \u770bINSERT\u5b8c\uff0c\u540c\u6642triggers\u4e5f\u505a\u5b8c\u5f8c\u7684table<\/font>
SELECT<\/strong> empno ,projno AS prj ,actno AS act
FROM FINAL TABLE<\/strong>
<\/font>(INSERT INTO emp_act
VALUES (‘200000′,’ABC’,10 ,1,’2003-10-22′,’2003-11-24′)
           ,(‘200000′,’DEF’,10 ,1,’2003-10-22′,’2003-11-24′))
<\/strong>ORDER BY 1,2,3;<\/p>\n

\/\/ \u5b9a\u7fa9 procedure\uff0cescape \u662f @
<\/font>create procedure proc()
begin
.
.
.
end
@<\/p>\n

\/\/ in \u7684var_id\u662f\u50b3\u5165\u7684\u53c3\u6578\uff0cout \u7684 name \u662freturn\u7684\u503c<\/font>
db2 create procedure proc(in<\/strong> var_id int, out<\/strong> name char(10));<\/p>\n

\/\/ \u7e8c\u4e0a\uff0cCALL procdure \u8a9e\u6cd5<\/font>
db2 call proc(20,?);<\/p>\n

DECLARE Variables<\/strong><\/p>\n

\/\/ DECLARE \u8b8a\u6578\u5ba3\u544a<\/font>
BEGIN ATOMIC
<\/strong>   DECLARE aaa, bbb, ccc SMALLINT DEFAULT 1;
<\/strong>   DECLARE ddd CHAR(10) DEFAULT NULL;
   DECLARE eee INTEGER;
   SET eee = aaa + 1;
   UPDATE staff
   SET comm = aaa
        ,salary = bbb
        ,years = eee
   WHERE id = 10;
END<\/strong><\/p>\n

GET DIAGNOSTICS <\/strong><\/p>\n

\/\/ GET \u8655\u7406\u4e86\u591a\u5c11\u7b97\u8cc7\u6599<\/font>
BEGIN ATOMIC
   DECLARE numrows INT DEFAULT 0;
   UPDATE staff
   SET salary = 12345
   WHERE id < 100;
   GET DIAGNOSTICS numrows = ROW_COUNT; \/\/\u8981\u63a5\u8457\u4e0a\u9762\u52d5\u4f5c\u5b8c\u57f7\u884c
<\/font><\/strong>   UPDATE staff
   SET salary = numrows
   WHERE id = 10;
END<\/p>\n

IF STATEMENT<\/strong>
BEGIN ATOMIC
   DECLARE cur INT;
   SET cur = MICROSECOND(CURRENT TIMESTAMP);
   IF cur > 600000 THEN
      UPDATE staff
      SET name = CHAR(cur)
      WHERE id = 10;
   ELSEIF cur > 300000 THEN
      UPDATE staff
      SET name = CHAR(cur)
      WHERE id = 20;
   ELSE
      UPDATE staff
      SET name = CHAR(cur)
      WHERE id = 30;
   END IF;
END <\/p>\n

AVG Function<\/strong><\/p>\n

<\/font> <\/p>\n

\/\/ \u81ea\u52d5\u5ffd\u7565\u8a08\u7b97 NULL<\/font>
SELECT AVG(dept) AS a1
          ,AVG(ALL dept) AS a2
          ,AVG(DISTINCT dept) AS a3 \/\/ \u91cd\u8986\u7684\u53ea\u53d6\u4e00\u7b46
<\/font>          ,AVG(dept\/10) AS a4 \/\/ \u8f03\u4e0d\u7cbe\u78ba\uff0c\u56e0int\u9664\u6cd5\u6703\u6cd5\u9664\u5c0f\u6578\u9ede
<\/font>          ,AVG(dept)\/10 AS a5
FROM staff
HAVING AVG(dept) > 40; <\/p>\n

\/\/ \u82e5 comm \u70ba 0 \u7684\u8a71\u5c31\u8b8a\u6210NULL\uff0c\u4e5f\u5c31\u662f\u4e0d\u52a0\u5165\u5e73\u5747\u8a08\u7b97<\/font>
SELECT AVG(salary) AS salary
          ,AVG(comm) AS comm1
          ,AVG(CASE comm
                    WHEN 0 THEN NULL
                    ELSE comm
                 END)<\/strong> AS comm2
FROM staff; <\/p>\n

COUNT Function<\/strong><\/p>\n

\/\/ \u8a08\u7b97\u7b46\u6578\uff0cNULL \u4e0d\u7b97
<\/font>SELECT COUNT(*) AS c1
   ,COUNT(INT(comm\/10)) AS c2
   ,COUNT(ALL INT(comm\/10)) AS c3
   ,COUNT(DISTINCT INT(comm\/10)) AS c4
   ,COUNT(DISTINCT INT(comm)) AS c5
   ,COUNT(DISTINCT INT(comm))\/10 AS c6
FROM staff;<\/p>\n

ANSWER
=================
C1 C2 C3 C4 C5 C6
— — — — — —
35 24 24 19 24 2 <\/p>\n

MAX Function<\/strong> <\/p>\n

\/\/ \u53d6\u6700\u5927\u503c\uff0c\u5148 CHAR(id) \u8f49\u6210 char \u518d\u6392\u5e8f
<\/font>SELECT MAX(id) AS id
          ,MAX(CHAR(id)) AS chr
          ,MAX(DIGITS(id)) AS dig
FROM staff; <\/p>\n

\/\/ \u53d6\u6700\u5c0f\u503c
<\/font>SELECT MIN(dept)
          ,MIN(ALL dept)
          ,MIN(DISTINCT dept)
          ,MIN(DISTINCT dept\/10)
FROM staff; <\/p>\n

\/\/ \u52a0\u7e3d
<\/font>SELECT SUM(dept) AS s1
          ,SUM(ALL dept) AS s2
          ,SUM(DISTINCT dept) AS s3
          ,SUM(dept\/10) AS s4
          ,SUM(dept)\/10 AS s5
FROM staff; <\/p>\n

\/\/ RANK() \u91cd\u8986\u8cc7\u6599\u6709gap\u7684\u6392\u5e8f\uff0cDENSE_RANK() \u91cd\u8986\u8cc7\u6599\u7121gap\u7684\u6392\u5e8f\uff0cROW_NUMBER() \u4f9d\u7167\u524d\u5f8c\u6392\u5e8f<\/font>
SELECT id
          ,years
          ,salary
          ,RANK()<\/strong> OVER(ORDER BY years)<\/strong> AS rank#
          ,DENSE_RANK()<\/strong> OVER(ORDER BY years)<\/strong> AS dense#
          ,ROW_NUMBER()<\/strong> OVER(ORDER BY years)<\/strong> AS row#
FROM staff
WHERE id < 100
          AND years IS NOT NULL
ORDER BY years; <\/p>\n

ANSWER
===================================
ID YEARS SALARY RANK# DENSE# ROW#
— —– ——– —– —— —-
30 5 17506.75 1 1 1
40 6 18006.00 2 2 2
90 6 18001.75 2 2 3
10 7 18357.50 4 3 4
70 7 16502.83 4 3 5
20 8 18171.25 6 4 6
50 10 20659.80 7 5 7<\/p>\n

\/\/ \u591a\u6b21\u6392\u5e8f
<\/font>SMALLINT(RANK() OVER(ORDER BY job ASC ,years ASC ,id ASC<\/font>)) AS asc3<\/p>\n

\/\/ \u6c7a\u5b9a NULL \u6392\u5728\u524d\u6216\u6392\u5728\u5f8c<\/font>
,DENSE_RANK() OVER(ORDER BY years ASC NULLS FIRST) AS AF
,DENSE_RANK() OVER(ORDER BY years ASC NULLS LAST ) AS AL <\/p>\n

Partition Usage<\/strong> <\/p>\n

\/\/ PARTITION\uff0cGROUP \u5316
<\/font>SELECT id
          ,years AS YR
          ,salary
          ,RANK() OVER(PARTITION BY years ORDER BY salary<\/strong>) AS r1
FROM staff
WHERE id < 80 AND years IS NOT NULL
ORDER BY years ,salary; <\/p>\n

ANSWER
=================
ID YR SALARY R1
— — ——– —
30 5 17506.75 1
40 6 18006.00 1
70 7 16502.83 1
10 7 18357.50 2
20 8 18171.25 1
50 0 20659.80 1 <\/p>\n

\/\/ \u53d6\u5f97\u90e8\u9580\u4e2d\u85aa\u8cc7\u6700\u9ad8\u8005<\/font>
SELECT id
          ,salary
          ,dept AS dp
FROM (SELECT S1.*
           ,RANK() OVER(PARTITION BY dept
                              ORDER BY salary DESC<\/strong>) AS r1
<\/strong>           FROM staff s1
           WHERE id < 80 AND years IS NOT NULL)AS xxx
WHERE r1 = 1<\/strong> ORDER BY dp; <\/p>\n

\/\/ \u914d\u5408 BETWEEN \u53ef\u4ee5\u627e\u51fa 3~6 \u7b46\u8cc7\u6599
<\/font>SELECT * FROM (SELECT id ,name ,
                       ROW_NUMBER() OVER(ORDER BY id) AS r<\/strong>
              FROM staff WHERE id < 200 AND years IS NOT NULL)AS xxx WHERE r BETWEEN 3 AND 6
<\/strong> ORDER BY id; <\/p>\n

\/\/ \u6bcf5\u7b46\u5217\u51fa\u4f86
<\/font>SELECT * FROM (SELECT id ,name
                                      ,ROW_NUMBER() OVER(ORDER BY id) AS r
              FROM staff WHERE id < 200 AND years IS NOT NULL )AS xxx WHERE (r – 1) = ((r – 1) \/ 5) * 5<\/strong> ORDER BY id; <\/p>\n

\/\/ \u8981\u6709 OVER() \u624d\u80fd\u628a rows \u5217\u51faSUM(),AVG(),MAX(),MIX()…<\/font>
SELECT id
          ,name
          ,salary
          ,SUM(salary) OVER()<\/strong> AS sum_sal
          ,AVG(salary) OVER()<\/strong> AS avg_sal
          ,MIN(salary) OVER()<\/strong> AS min_sal
          ,MAX(salary) OVER()<\/strong> AS max_sal
          ,COUNT(*) OVER()<\/strong> AS #rows
FROM staff
WHERE id < 60
ORDER BY id;\"18\" <\/p>\n

COALESCE Function<\/strong> <\/p>\n

\/\/ \u5982\u679c comm \u662f NULL \u7684\u8a71\u6703\u8f49\u6210 0<\/font>
SELECT id
          ,comm
          ,COALESCE(comm,0)<\/strong>
FROM staff
WHERE id < 30
ORDER BY id; <\/p>\n

CONCAT Function<\/strong> <\/p>\n

\/\/ \u5169\u5b57\u4e32\u4f75\u5728\u4e00\u8d77\uff0c\u4e0b\u9762\u4e09\u7a2e\u7d50\u679c\u90fd\u4e00\u6a23
\u2022 “AB” || “CD”
\u2022 “AB” CONCAT “CD”
\u2022 CONCAT(“AB”,”CD”)<\/font>
SELECT ‘A’ || ‘B’
<\/strong>         ,’A’ CONCAT ‘B’
<\/strong>          ,CONCAT(‘A’,’B’)
<\/strong>          ,’A’ || ‘B’ || ‘C’
<\/strong>          ,CONCAT(CONCAT(‘A’,’B’),’C’)
<\/strong>FROM staff
WHERE id = 10; <\/p>\n

DECIMAL Function<\/strong> <\/p>\n

\/\/ DECIMAL \u662f\u6709\u5c0f\u6578\u9ede\u7684\u8cc7\u6599\u578b\u614b
<\/font>WITH temp1(n1,n2,c1,c2) AS
(VALUES (123
            ,1E2
            ,’123.4′
            ,‘567$8’<\/strong>))
SELECT DEC(n1,3)<\/strong> AS dec1
            ,DEC(n2,4,1)<\/strong> AS dec2
            ,DEC(c1,4,1)<\/strong> AS dec3
            ,DEC(c2,4,1,’$’)<\/strong> AS dec4 \/\/ \u6307\u5b9a\u5c0f\u6578\u9ede\u70ba $
<\/font>FROM temp1; <\/p>\n

ANSWER
==========================
DEC1 DEC2 DEC3 DEC4
—– —— —— ——
123. 100.0 123.4 567.8 <\/p>\n

GENERATE_UNIQUE Function<\/strong> <\/p>\n

\/\/ \u7522\u751f\u552f\u4e00\u503c\uff0c\u8207\u6642\u9593\u6709\u95dc
<\/font>SELECT id
          ,GENERATE_UNIQUE() AS unique_val#1
<\/font>          ,DEC(HEX(GENERATE_UNIQUE()),26) AS unique_val#2
FROM staff
WHERE id < 50
ORDER BY id; <\/p>\n

ANSWER
================= ===========================
ID UNIQUE_VAL#1 UNIQUE_VAL#2
— ————– —————————
10 20000901131648990521000000.
20 20000901131648990615000000.
30 20000901131648990642000000.
40 20000901131648990669000000. <\/p>\n

RAND Function<\/strong><\/p>\n

\/\/ RAND() \u5f97\u5230 0~1 \u4e4b\u9593\u7684\u4e82\u6578\uff0c\u4ee5\u4e0b\u5217\u5b50\u8868\u793a\u53d6 table 1\/10 \u7684\u8cc7\u6599
<\/font>SELECT id
          ,name
FROM staff
WHERE RAND() < 0.1<\/strong>
ORDER BY id;<\/p>\n","protected":false},"excerpt":{"rendered":"

SELECT \/\/ ‘ABC’ \u56fa\u5b9a\u503cSELECT deptno ,admrdept …<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[],"tags":[],"class_list":["post-438","post","type-post","status-publish","format-standard","hentry"],"_links":{"self":[{"href":"https:\/\/blog.smallken.site\/index.php?rest_route=\/wp\/v2\/posts\/438","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blog.smallken.site\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blog.smallken.site\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blog.smallken.site\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.smallken.site\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=438"}],"version-history":[{"count":1,"href":"https:\/\/blog.smallken.site\/index.php?rest_route=\/wp\/v2\/posts\/438\/revisions"}],"predecessor-version":[{"id":1430,"href":"https:\/\/blog.smallken.site\/index.php?rest_route=\/wp\/v2\/posts\/438\/revisions\/1430"}],"wp:attachment":[{"href":"https:\/\/blog.smallken.site\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=438"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.smallken.site\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=438"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.smallken.site\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=438"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}