简单的SQL代码集合汇总

1，基本查询代码 

###########################
基本的查询代码，联接，条件判断
##########################
SELECT e.ename, e.deptno,
       CASE WHEN e.ename LIKE '%k%' THEN 1 ELSE 0 END AS state
FROM emp e
LEFT JOIN deptno d ON e.deptno = d.deptno
GROUP BY e.deptno
ORDER BY CASE WHEN e.ename IS NOT NULL THEN e.deptno DESC ELSE e.deptno END;

2，视图，临时表的操作 

CREATE VIEW V AS 
(SELECT * FROM EMP E 
WHERE E.DEPTNO IS NULL
)
-----------------------------视图的创立
with t as (
select * from emp)
-----------------------------临时表的创立

3，数据去重，差集的操作 

##去重操作，链接多行操作，差集操作

SELECT DISTINCT DEPTNO FROM DEPT WHERE 
DEPTNO IS NOT NULL
----------------------------------------
SELECT ENAME,DEPTNO FROM EMP E
WHERE DEPT LIKE '%T%'
UNION ALL 
SELECT DNAME,DEPTNO FROM DEPT D
WHERE DEPT NOT LIKE '%L'
------------------------------------------
SELECT ENAME FROM EMP 
EXCEPT 
SELECT ENAME FROM DEPT
WHERE DEPTNO IS NULL
------------------------------------------
SELECT ENAME FROM EMP 
MINUS
SELECT ENAME FROM DEPT
WHERE DEPTNO IS NULL
---------------------------------------------
SELECT ENAME FROM EMP
WHERE DEPTNO NOT IN (
SELECT DEPTNO FROM DEPT 
WHERE DEPTNO IS NULL
-------------------------------------------------
)

 4，字符间的操作

translate，replace与concat操作
select concat ('FFFF',deptno) from dept where deptno is not null
-------------------------------------------------------------------
select replace(deptname,'aaa','123') as newdata from dept 
---------------------------------------------------------------------
select translate(deptname,'aaa','123') as newdata from dept
--------------------------------------------------------------------
substr()操作，按字串排序操作
select * from emp 
order by substr（emp，length（ename-2）---db2，mysql，oracle，postgresql
----------------------------------------------------------------------