[pegjs] node-sql-parser 예제

1. npm install node-sql-parser

 

const opt = {

database: "MySQL", // MySQL is the default database

};

 

const { Parser } = require("node-sql-parser/build/mysql");

const parser = new Parser();

 

const ast = parser.astify("SELECT * FROM t", opt);

const sql = parser.sqlify(ast, opt);

 

console.log(JSON.stringify(ast, null, 2));

console.log(sql); // SELECT * FROM `t`

 

 

1. Selector

const ast = parser.astify("SELECT empno as ENO FROM emp", opt);

 

SELECT `empno` AS `ENO` FROM `emp`

 

1-2). 한글 alias 

const ast = parser.astify("SELECT empno as 번호 FROM emp", opt);

SELECT empno as 번호 FROM emp

 

2. 서브쿼리 UPDATE 문 SET 절

UPDATE TEAM A SET A.E_TEAM_NAME = (SELECT X.STADIUM_NAME FROM STADIUM X WHERE X.STADIUM_ID = A.STADIUM_ID)

 

 

 

됨

3.) 서브쿼리 INSERT 문의 VALUES 절

 

 

4.  FULL OUTER JOIN 

select * from dept FULL OUTER join dept_team on dept.deptno = dept_team.deptno

 

 

5. self join은 inner join으로 해야 됨

select worker.empno, worker.ename, manager.ename from emp worker inner join emp manager on(worker.mgr = manager.empno)

 

 

6. UNION 

SELECT DEPTNO FROM EMP UNION SELECT DEPTNO FROM DEPT

 

 

 

7. UNION ALL

 

 

8. ROLLUP

SELECT DNAME, JOB, MGR, SUM(SAL) FROM EMP, DEPT WHERE DEPT.DEPTNO = EMP.DEPTNO GROUP BY ROLLUP(DNAME, (JOB, MGR))

 

8-1. 간단한 ROLLUP 예제

SELECT ENAME, DEPTNO FROM EMP GROUP BY ROLLUP(ENAME, DEPTNO)

 

 

9. CUBE

SELECT DNAME, JOB, MGR, SUM(SAL) FROM EMP, DEPT WHERE DEPT.DEPTNO = EMP.DEPTNO GROUP BY CUBE(DNAME, (JOB, MGR))

 

9-1. 간단한 CUBE 예제

SELECT ENAME, DEPTNO FROM EMP GROUP BY CUBE(ENAME, DEPTNO)

 

안되는 것 

 

1. INTERSECT, MINUS

2. 계층형 질의