Oracle常用SQL总结

Oracle常用SQL

创建索引

CREATE UNIQUE INDEX index_name ON table_name (column_name)

create unique index idx_emp_temp on emp_temp(empno);

Oracle内连接

内连接（Inner Join）是指表连接的结果只包含那些完全满足连接条件的记录。

SELECT a.运单编号 AS deliverId, c.网点编号 AS brId, 
	b.备注 AS signatureRemark
FROM table1 b, table2 a, table3 c
WHERE a.运单编号 = b.运单编号
	AND b.寄件网点 = c.网点名称

按字段分组

SELECT 
/*+INDEX(t T_EMPLOYEE_LOGIN_LOG_FIRST_N4)*/
b.province as province,nvl(count(1),0) as provinceNum
FROM t_Employee_Login_Log_First t ,t_base_organize b
WHERE t.company_id = b.id
      and t.SOURCE = #{source}
GROUP BY b.province

HAVING

• 查询部门工资总和大于10000

SELECT deptno,sum(sal) FROM emp GROUP BY deptno HAVING sum(sal)>10000

数据库三范式

• 数据库三范式

SQL注入

• 一般情况（查询一条记录）

SELECT * FROM db_access.dbo.sys_user WHERE login_name = 'sstic' AND PASSWORD = '1'

• SQL注入（查询所有记录）

SELECT * FROM db_access.dbo.sys_user WHERE login_name = 'sstic' AND PASSWORD = '1' OR '1' = '1'

• MyBatis辅助SQL

SELECT *
FROM SUBSIDY_ORDER_DETAIL 
--只返回表结构，条件永远不成立
--WHERE 1 <>1

--返回表中所有数据，条件永远成立，常用于拼接SQL
--WHERE 1=1