????????????????????????????????????????μ???ó???????????? SQL?????????????????ó????????в?????????????Ч???????ó??????????????????????????????????????????????????????????????????????в????????????Щ???????п????????????????????????￡????????????????????????????????????????????????????????????????????????????????ɡ?
?????????????Щ??????? SQL ??????????????????????????Щ???????????? SQL ?????????????????Щ?????????????????????????????????
??????????????
???????????????′??????????????????????????????????????????????·???????????
????CREATE TABLE DB2ADMIN.SALES
????(CUSTOMER_ID INT NOT NULL?? ITEM_ID INT NOT NULL??
????SALE_QUANTITY SMALLINT NOT NULL?? SALE_DATE DATE NOT NULL);
????INSERT INTO SALES
????SELECT
????SYSFUN.RAND()*500 + 1 AS CUSTOMER_ID??
????SYSFUN.RAND()*100 + 1 AS ITEM_ID??
????1 + SYSFUN.RAND()*10 AS SALE_QUANTITY??
????DATE('01/01/2003') + (SYSFUN.RAND()*200) DAYS AS SALE_DATE
????FROM SYSCAT.COLUMNS;
????SALES ???е??????? SYSCAT.COLUMNS ?е???????????????????ж???????????????????磬SALE_QUANTITY ???е????????????? 1 ?? 10 ???? 10% ???????и?????????????????????????????????????????????????? INSERT ???????????????????????y????????CROSS JOIN??????????????????????
????INSERT INTO SALES
????SELECT
????SYSFUN.RAND()*500 + 1 AS CUSTOMER_ID??
????SYSFUN.RAND()*100 + 1 AS ITEM_ID??
????1 + SYSFUN.RAND()*10 AS SALE_QUANTITY??
????DATE('01/01/2003') + (SYSFUN.RAND()*2000) DAYS AS SALE_DATE
????FROM SYSCAT.TABLES T1 JOIN SYSCAT.TABLES T2;
????????????У??? T1 ?? T2 ????????????κ????????????T1 ?е????л???? T2 ?е????С??????????????????????????????????????????????????? Joe Celko ?? SQL for Smarties??顣
??????????? INSERT ??????漰?????????????????????????????????????????????????“log full”???????SQL0964C ?????????????????????????????????????????????????? T1 ?? T2 ???????е? WHERE ?????????????С??????
?????????????÷???????????????????????÷????е??????????????е???????????????????????????????????
??????????
??????????????к?????????????????????????????????????????????????????????????????????
????CREATE TABLE DB2ADMIN.PARENT_TABLE(PARENT_ID INT NOT NULL?? NUM_CHILDREN
????INT NOT NULL);
????INSERT INTO DB2ADMIN.PARENT_TABLE
????SELECT ROW_NUMBER() OVER()?? SYSFUN.RAND()*5 + 1
????FROM SYSCAT.TABLES;
????ALTER TABLE DB2ADMIN.PARENT_TABLE ADD PRIMARY KEY(PARENT_ID);
????CREATE TABLE DB2ADMIN.CHILD_TABLE(PARENT_ID INT NOT NULL?? CHILD_NUM INT
????NOT NULL);
????INSERT INTO DB2ADMIN.CHILD_TABLE
????SELECT PARENT_ID?? SEQUENCE_TABLE.NUM
????FROM DB2ADMIN.PARENT_TABLE
????JOIN
????(SELECT ROW_NUMBER() OVER() AS NUM
????FROM SYSCAT.TABLES) AS SEQUENCE_TABLE
????ON AUXILIARY_TABLE.NUM<NUM_CHILDREN;
????????? INSERT ???????????????????? 1 ?? 6 ????????SEQUENCE_TABLE ??????????????????????????????????? Sheryl Larsen ???????
?????????????????????б
???????????е??Щ????????????????????????????????? ??????б??data skew???????磺
????SELECT CITY?? COUNT(*) FROM CUSTOMER
????GROUP BY CITY
????ORDER BY COUNT(*) DESC
????CHICAGO    236
????MILWAKEE  95
????ROCKFORD  4
????NAPERVILLE  3
????SPRINGFIELD  3
????(snip)
????279 rows selected
??????????????????????????????г?????????б?????????????????????????????????б???????????????д洢???????
????CREATE TABLE COLOR_FREQUENCY(COLOR CHAR(10)?? FREQUENCY SMALLINT);
????INSERT INTO COLOR_FREQUENCY VALUES
????('RED'?? 37)?? ('SILVER'??12)?? ('AMBER'?? 3)?? ('GREEN'?? 3)??
????('WHITE'??2)??('BLACK'?? 1)??('BLUE'??1);
?????????????????????????????????????????б????
????CREATE TABLE CONSECUTIVE_NUMBER(NUM INT NOT NULL);
????INSERT INTO CONSECUTIVE_NUMBER
????SELECT ROW_NUMBER() OVER() AS NUM FROM SYSCAT.COLUMNS;
???????Joe Celko ?? SQL for Smarties ??????????????????????????????????????????????
????SELECT COLOR?? FREQUENCY?? NUM
????FROM COLOR_FREQUENCY JOIN CONSECUTIVE_NUMBER
????ON NUM BETWEEN 1 AND FREQUENCY ORDER BY FREQUENCY?? COLOR;
????COLOR      FREQUENCY NUM        
????---------- --------- -----------
????BLACK              1           1
????BLUE               1           1
????WHITE              2           1
????WHITE              2           2
????AMBER              3           1
????AMBER              3           2
????AMBER              3           3
????(SNIP)