posts - 13,comments - 0,trackbacks - 0

详细解读可参考oracle官方文档:https://docs.oracle.com/en/
选择列:SELECT Column1,Column2 from tablename  ;
SQL 选择行:SELECT * FROM tablename
 
SELECT语句语法: 
 
 
SELECT * | {[DISTINCT] column|expression [alias],...} FROM table;
 
SQL语句中的数学表达式:对于数值和日期型字段,可以进行 “加减乘除
例:SELECT last_name ,salary ,salary+300 FROM  Employess
 
NULL 表示不可用,未赋值,不知道,不适用 ,他既不是0 也不是空格,
记:一个数值与NULL进行四则运算时,结果是 NULL
 
简单查询
SELECT salary * 11,
emp.last_name NAME
FROM employees emp;
 
去重查询
 
SELECT DISTINCT emp.employee_id
FROM employees emp
 
 
等于判断
 
SELECT *
FROM employees emp
WHERE emp.manager_id = 100
 
 
大于等于
 
SELECT emp.first_name || ' ' || emp.last_name || '的工资是' || emp.salary "薪资情况"
FROM employees emp
WHERE emp.salary >= 2400
区间,between and
SELECT emp.first_name || ' ' || emp.last_name "中等薪资",
emp.salary
FROM employees emp
WHERE emp.salary BETWEEN 2500 AND 3500
 
 
in 用法,在参数之内有的
 
SELECT emp.employee_id AS "工号",
emp.last_name "Name",
emp.salary 工资
FROM employees emp
WHERE emp.manager_id IN (100, 101,102);
 
 
字符匹配 (% 多个字符 ,_ 单个字符)
 
SELECT emp.first_name,salary
FROM employees emp
WHERE emp.first_name LIKE '_e%'
 
匹配匹配字符
 
SELECT emp.last_name,
emp.job_id
FROM employees emp
WHERE emp.job_id LIKE '%\_%' ESCAPE '\'
SELECT emp.last_name,
emp.job_id
FROM employees emp
WHERE emp.job_id LIKE '%K_%' ESCAPE 'K'
is NULL 非空
SELECT *
FROM employees emp
WHERE emp.manager_id IS NULL
 
多条件查询
 
SELECT *
FROM employees emp
WHERE emp.salary > 3500
AND emp.job_id LIKE '%MAN%'
SELECT *
FROM employees emp
WHERE emp.salary > 3500
AND emp.job_id NOT IN ('SA_MAN', 'ST_MAN');
 
 
排序
 
SELECT emp.last_name,
emp.salary,
emp.hire_date
FROM employees emp
ORDER BY emp.last_name,
emp.hire_date DESC
 
 
按别名排序
 
SELECT emp.last_name,
emp.salary * 12 annsal
FROM employees emp
ORDER BY annsal
 
 
单行函数
 
大小写转换
SELECT employee_id,
last_name,
department_id
FROM employees
WHERE last_name = 'higgins';
SELECT employee_id,
last_name,
department_id
FROM employees
WHERE lower(last_name) = 'higgins';
 
concat (,) 将两个参数进行连接相当于 ||
length 计算字符大小
instr 判断字符在那个位置
 
SELECT employee_id,
concat(first_name ||' ',
last_name) NAME,
job_id,
length(last_name),
instr(last_name,
'a') "Contains 'a'?"
FROM employees
WHERE substr(job_id,
4) = 'REP';
 
 
 
SELECT employee_id,
concat(first_name ||' ',
last_name) NAME,
job_id,
length(last_name),
instr(last_name,
'a') "Contains 'a'?"
FROM employees
WHERE substr(job_id,
4) = 'REP';
SELECT employee_id,
concat(first_name ,concat(' ',last_name)) NAME,
job_id,
length(last_name),
instr(last_name,
'a') "Contains 'a'?"
FROM employees
WHERE substr(job_id,
4) = 'REP';
 
 
/*进行四舍五入*/
 
SELECT round(45.923, 2),
round(45.923, 0),
round(45.923, -1),
round(44.923, -1),
round(44.923, -2),
round(54.923, -2)
FROM dual;
 
 
/*不进行四舍五入 -1 的倒数第最后一位用0 代替*/
 
SELECT trunc(45.923, 2),
trunc(45.923),
trunc(45.923, -2),
trunc(89.923, -1),
trunc(01.923, -1)
FROM dual
 
 
/*取余*/
 
SELECT last_name,
salary,
MOD(salary, 5000)
FROM employees
WHERE job_id = 'SA_REP';
 
 
去除空格 ,字符
 
select TRIM(' Hello world ') from dual;
select TRIM('H' FROM 'Hello World') from dual;
 
 
日期函数:
 
 
数据类型转换
 
分组函数:
 
 
SUM、AVG、COUNT、STDDEV、VARIANCE、MAX、MIN
 
 
SELECT AVG(salary), MAX(salary), MIN(salary), SUM(salary) FROM employees WHERE job_id LIKE '%REP%';
 
COUNT 函数
 
分组:GROUP BY
 
1、可以按照某一个字段分组,也可以按照多个字段的组合进行分组
2、SELECT 查询语句中同时选择分组计算函数表达式和其他独立字段时 ,其他字段必须出现在Group By子 句中,否则不合法
3、不能在Where 条件中使用分组计算函数表达式,当出现这样的需求的时候,使用Having 子句
4、分组计算函数也可嵌套使用
子查询:
单行比较必须对应单行子查询(返回单一结果值的查询); 比如=  , >  多行比较必须对应多行子查询(返回一个数据集合的查询);比如 IN  , > ANY, > ALL 等
 
DML 语句:
DML: Data Manipulation Language , 数据操纵语言;简单的说就是SQL中的增、删、改 等语句。
 
 
 
 
 
 

posted on 2018-07-16 09:31 电杆 阅读(...) 评论(...) 编辑 收藏