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
----------------------------------------------------------------------