SQL Super SQL SELECT target list FROM WHERE

  • Slides: 26
Download presentation

SQL Super. SQL SELECT <target list> FROM … WHERE … GENERATE <Medium> <TFE> FROM

SQL Super. SQL SELECT <target list> FROM … WHERE … GENERATE <Medium> <TFE> FROM … WHERE … Super. SQL 2016/10/06 Super. SQLとは • <Medium> … HTML, XML, PDF, etc. • <TFE> … Target Form Expression • 文章の構造を記述する 3

SELECT s. city, d. floor, d. name, i. name FROM shop s, dept d,

SELECT s. city, d. floor, d. name, i. name FROM shop s, dept d, item i WHERE i. dept=d. id AND d. shop=s. id SQL出力は… ➡ Flat table | city | floor | name | +—————+——————————————+———————+ | Toronto | 1 F | Men’s casual | jacket | | Toronto | 1 F | Men’s casual | jeans | | Toronto | 2 F | Men’s suits | shirt | | Toronto | 3 F | Women’s dresses | slacks | | Toronto | 3 F | bedclothes | sheet | | Toronto | 3 F | bedclothes | luxury sheet | | Toronto | 3 F | bedclothes | bath towel | | Toronto | 4 F | book | romance | | Montreal | 1 F | jewelry | earrings | | Montreal | 1 F | jewelry | ring | | Montreal | 2 F | toy | block | | Montreal | 2 F | children’s clothes | pajamas | | Montreal | 2 F | children’s clothes | one-piece | Super. SQL 2016/10/06 Super. SQLとは 4

GENERATE HTML [ s. city@{align=“center”, bgcolor=“navy”, color=“white”}! [d. floor, [d. name, [i. name]! ]!

GENERATE HTML [ s. city@{align=“center”, bgcolor=“navy”, color=“white”}! [d. floor, [d. name, [i. name]! ]! ]! ], @{bgcolor=“cyan”, color=“blue”} FROM shop s, dept d, item i WHERE i. dept=d. id AND d. shop=s. id Super. SQL 2016/10/06 Super. SQLとは Super. SQLの場合… ➡ 様々なレイアウト表現 5

Super. SQL Query Database Interface Parser Data Constructor Layout Expression Tree Structured Data Flat

Super. SQL Query Database Interface Parser Data Constructor Layout Expression Tree Structured Data Flat Table SQL Query Super. SQL 2016/10/06 Super. SQLのアーキテクチャ DBMS Code Generator RDB HTML XML PDF EXCEL … 6

研究内容 null_valueの代入 ツリーの変換 Database Interface Parser Data Constructor Layout Expression Tree Structured Data Flat

研究内容 null_valueの代入 ツリーの変換 Database Interface Parser Data Constructor Layout Expression Tree Structured Data Flat Table SQL Query 16/12/24 Super. SQL Query DBMS Code Generator RDB HTML ctab XML PDF EXCEL … 11

16/12/24 研究内容 -null_value代入- 17

16/12/24 研究内容 -null_value代入- 17

補足 • タプル群 クエリ SELECT c. day, c. name, s. age, s. gender, s.

補足 • タプル群 クエリ SELECT c. day, c. name, s. age, s. gender, s. name, c. day, c. name, p. performance, c. name, c. day FROM student 2 s , class c , performance p WHERE s. id = p. s_id AND c. id = p. c_id ; 16/12/24 • SQLクエリの結果の集合 タプル群 [[tue, Webapp, 13, male, tabata, tue, Webapp, C, Webapp, tue], [mon, DM, 13, male, tabata, mon, DM, B, DM, mon], [mon, DBS, 13, male, tabata, mon, DBS, A, DBS, mon], [tue, Webapp, 13, female, kenji, tue, Webapp, C, Webapp, tue] 21

研究内容 -順序指定アルゴリズム- //tfeを探索する FUNCTION tfe_anlysis(tfe) IF table_aliasを発見 THEN IF table_aliasの前に順序指定が無い THEN RETURN 順序指定を追加したtfe ELSE

研究内容 -順序指定アルゴリズム- //tfeを探索する FUNCTION tfe_anlysis(tfe) IF table_aliasを発見 THEN IF table_aliasの前に順序指定が無い THEN RETURN 順序指定を追加したtfe ELSE 順序指定の情報を保持 RETURN tfe ENDIF ELSE tfeの一つ下の要素を参照しtmpに保存 RETURN tfe_anlysis(tmp) ENDIF 16/12/24 input tfe文 output 順序指定を追加したtfe 24

デモ • student(id, name, gender, age) • class(id, name, day) • performance(id, s_id, c_id,

デモ • student(id, name, gender, age) • class(id, name, day) • performance(id, s_id, c_id, performance(評定), score(点数)) 16/12/24 • 用意したテーブル • 用意したクエリ • 全員の各授業に対する評定 cross_tab( [(asc)c. day![c. name], ], , [(asc)s. age, [(desc)s. gender, [(desc)s. name]!]!]! , p. performance) • 性別、年齢別の各科目のA, B, Cの人数 cross_tab([s. gender! [s. age], ], , [c. name, [p. performance]!]! , {count[s. id]}) • 各テストの平均点と各自の点数 cross_tab([c. day ! [c. name! avg[p. score] ], ], , [s. gender, [s. name]!]! , p. score) 25

3 Super SQL Super SQL SQLSELECTGENERATE SQL SELECT3 Super SQL Super SQL SQLSELECTGENERATE SQL SELECT
SQL SQL SELECT SQL INSERT SQL UPDATE SQLSQL SQL SELECT SQL INSERT SQL UPDATE SQL
EVENT SELECT WSAEvent Select WSAEvent Select WSAEvent SelectEVENT SELECT WSAEvent Select WSAEvent Select WSAEvent Select
SUPER CLEAN SUPER WAX Super Clean and SuperSUPER CLEAN SUPER WAX Super Clean and Super
Super SQL Super SQL 4 PHP SQL HTMLSuper SQL Super SQL 4 PHP SQL HTML
list data list nil list link list listlist data list nil list link list list
Advanced Select Statements Select List Variations SELECT ColumnAdvanced Select Statements Select List Variations SELECT Column
Advanced Select Statements Select List Variations SELECT ColumnAdvanced Select Statements Select List Variations SELECT Column
Open SQL SELECT SELECT INTO SELECT field 1Open SQL SELECT SELECT INTO SELECT field 1
05 Chapter SQL SELECT SELECT BNF SELECT statement05 Chapter SQL SELECT SELECT BNF SELECT statement
SQL SELECT Basic form of the SQL SELECTSQL SELECT Basic form of the SQL SELECT
The SQL Select Statement The SQL Select StatementThe SQL Select Statement The SQL Select Statement
SQL SELECT Statement Syntax The SQL SELECT statementSQL SELECT Statement Syntax The SQL SELECT statement
3 Super SQL n Super SQL n SQLSELECTGENERATE3 Super SQL n Super SQL n SQLSELECTGENERATE
SELECT Query SELECT FROM SELECT DISTINCTALL columnexpression tableSELECT Query SELECT FROM SELECT DISTINCTALL columnexpression table
SELECT ROWNUM B FROM SELECT A FROM SELECTSELECT ROWNUM B FROM SELECT A FROM SELECT
Select Case if Select Case Select Case ScoreSelect Case if Select Case Select Case Score
Now continue ADVANCED SELECT FROM Select SELECT selectlistNow continue ADVANCED SELECT FROM Select SELECT selectlist
Initial Login Screen Select Carrier Select Tractor SelectInitial Login Screen Select Carrier Select Tractor Select
Select 4 n select int select int nfdsSelect 4 n select int select int nfds
Select Post Select Post Select Post lifestyle dataSelect Post Select Post Select Post lifestyle data
l SELECT SELECT FROM ORDER BY SELECT FROMl SELECT SELECT FROM ORDER BY SELECT FROM
l SELECT SELECT FROM ORDER BY SELECT FROMl SELECT SELECT FROM ORDER BY SELECT FROM
SELECT 1 SELECT v SELECT DISTINCT column AliasSELECT 1 SELECT v SELECT DISTINCT column Alias