Oracle基础操作上 有更新!

  |   0 评论   |   535 浏览

1.简单查询

1.1.基本语法

select * from demp;

SQL语言大小写不敏感
SQL 可以写在一行或多行
关键字不能被缩写也不能分行
各子句一般要分行写

1.2.算数运算符

数字与日期使用的算数运算符

+ - * / 加减乘除

select last_name,salary+30 from emp;

1.3.定义空值

空值是无效的,未指定的,未知的或者不可预知的值,空值不是空格或者0

select last_name,salary,abc from emp;

此时的abc列则为空值(emp表中并没有该列)
select last_name,salary*abc from emp;

包含空值的数学表达式的值都为空值

1.4.列的别名

  1. 重命名一个列
  2. 便于计算
select last_name as name from emp;
select last_name name from emp;
select last_name as "name" from emp;
select last_name "name"  from emp;

以上语句等价,使用双引号时,可以在别名中包含空格或特殊字符并区分大小写

1.5.连接符

  1. 把列与列,列与字符连接在一起
  2. 使用 || 表示,用来合成列
select last_name||jod_id as "Employees" from emp;

连接查询出来的列为一个新的列

1.6.字符串

  1. 字符串可以是SELECT列表中的一个字符,数字,日期
  2. 日期和字符只能在单引号中出现
  3. 每当返回一行是,字符串被输出一次。
select last_name||'is a'||jod_id as "Employees" from emp;

1.7.重复行

默认情况下,查询会返回全部行,包括重复行。

select dept_id from emp;  会有重复行

select distinct dept_id from emp; 去重复

1.8.显示表结构

describe tablename

简写: desc tablename

2.过滤和排序数据

  1. 在查询中过滤行
  2. 在查询中对行进行排序

2.1.where字句

select emp_id,last_name 
from emp 
where dept_id = 90;

select emp_id,last_name 
from emp 
where last_name = 'Tom';

select hire_date,last_name 
from emp 
where hire_date = '7-6月-1997';

字符和日期要包含在单引号中
字符大小写敏感,日期格式敏感
默认的日期格式时 DD-MM月-YY

2.2.比较运算

  1. = 等于 【赋值使用 := 】
  2. > 大于
  3. >= 大于等于
  4. < 小于
  5. <= 小于等于
  6. <> 或 != 不等于
  7. between … and .. 两个值之间(包含边界)
  8. in (set) 等于值列表中的一个
  9. like 模糊查询
  10. is null 空值
select last_name,salary 
from emp 
where salary between 3500 and 4500;

select last_name,dept_id 
from emp 
where dept_id in (11,12,13);

select last_name 
from emp 
where last_name like 'T\_%' escape '\';

escape 回避特 殊符号的转义符
‘T\_%’ escape ’' 会匹配所有 T_ 打头的所有姓名

2.3.逻辑运算

  1. and 逻辑并
  2. or 逻辑或
  3. not 逻辑否
select * 
from emp 
where salary > 2500 and dept_id=90;

运算符优先级: 算术运算符 》连接符 》比较符 》is [not] null,like,[not] in 》[not] between 》not 》and 》or
可以使用括号改变优先级顺序

2.4.order by 子句

  1. asc (ascend) 升序
  2. desc (descend) 降序
  3. order by 子句在select语句的结尾
select last_name,salary 
from emp 
order by salary;

#通过别名排序
select last_name,12*salary annsal 
from emp 
order by annsal; 

#多个列排序
select last_name,salary,dept_id 
from emp 
order by salary,dept_id desc; 

3.单行函数

  1. 操作数据对象
  2. 接受参数返回一个结果
  3. 只对一行进行变换
  4. 每行返回一个结果
  5. 可以转换数据类型
  6. 可以嵌套
  7. 参数可以是一列或一个值

3.1.字符函数

  1. lower(‘SQL Cource’) ==》sql course
  2. upper(‘SQL Cource’) ==》SQL COURSE
  3. initcap(‘SQL Cource’) ==》Sql Cource
  4. cancat(‘Hello’,‘World’) ==》HelloWorld
  5. substr(‘HelloWorld’,1,5) ==》Hello
  6. length(‘HelloWorld’)==》10
  7. instr(‘HelloWorld’,‘w’)==》6
  8. lpad(salary,10,‘*’)==》*****24000
  9. rpad(salary,10,‘*’)==》24000*****
  10. trim(‘H’ form ‘HelloWorld’)==》elloWorld
  11. replace(‘abcd’,‘b’,‘m’)==》amcd

3.2.数字函数

  1. round(45.926,2)==》45.93 四舍五入
  2. trunc(45.926,2)==》45.92 截断
  3. mod(1600,300)==》100 求余

3.3.日期函数

在日期上加上或减去一个数字结果仍为日期
两个日期想减返回日期之间相差的天数(日期之间不允许做加法运算,无意义)
可以用数字除24来向日期中加上或减去天数

  1. months_between(‘01-SEP-95’,‘11-JAN-94’) ==》19.6774194 两个日期相差的月数
  2. add_months(‘11-JAN-94’,6)==》‘11-JUL-94’ 向指定日期中加上若干月
  3. next_day(‘01-SEP-95’,‘FRIDAY’)==》‘08-SEP-95’ 指定日期的下一个星期*对应的日期
  4. last_day(‘01-FEB-95’)==》‘28-FEB-95’ 本月的最后一天
  5. round(‘25-JUL-95’,‘MONTH’) ==》‘01-AUG-95’ 日期四舍五入
  6. trunc(‘25-JUL-95’,‘MONTH’) ==》‘01-JUL-95’ 日期阶段

3.4.显式数据类型转换

sjlxzh.JPG

  1. to_char函数对日期的转换

    select to_char(sysdate,'yyyy-mm-dd  hh:mi:ss') from dual;
    
    注意:必须包含在单引号中而且大小写敏感,可以包含任意有效的日期格式,日期之间用逗号隔开
    
    格式 含义
    YYYY 2004
    YEAR TWO THOUSAND AND FOUR
    MM 02
    MONTH JULY
    MON JUL
    DY MON
    DAY MONDAY
    DD 02
  2. to_date 函数对字符串的转换

    select to_date('2012年10月29日 08:10:21','yyyy"年"mm"月"dd"日"hh:mi:ss') from dual;
    
    使用双引号向日期中添加字符
    
  3. to_char函数对数字的转换

    select to_char(salary, '$99,999.00') salary from emp;
    
    格式 含义
    9 数字
    0
    $ 美元符号
    L 本地货币符号
    . 小数位
    , 千位符
  4. to_number 函数对字符的转换

    slelect to_number('¥1,234,567,890.00','L999,999,999,999.99') from dual;
    

3.5.通用函数

  1. nvl函数

    可以将空值转换为一个已知值
    可以使用的数据类型有日期、字符、数字
    nvl(adbc,0)

  2. nvl2函数

    nvl2(expr1,expr2,expr3) expr1不为null,返回expr2,为null,返回expr3
    expr1!=null?expr2:expr3
    nvl2(commission_pct,‘SAL+COMM’,‘SAL’) income

  3. nullif函数

    nullif(expr1,expr2) 相等返回null,不等返回expr1

  4. coalesce函数

    coalesce与nal相比的优点在于coalesce可以同时处理交替的多个值。
    如果第一个表达式为空,则返回下一个表达式,对其他的参数进行coalesce
    coalesce(abcd, salary, 10)

3.6.条件表达式

  1. case表达式
    CASE  expr  
    WHEN  comparison_expr1  THEN return_expr1
    [WHEN comparison_expr2 THEN return_expr2
    WHEN  comparison_exprn  THEN  return_exprn
    ELSE  else_expr]
    END
    
    SELECT last_name, job_id, salary,
    CASE job_id  WHEN 'IT_PROG' THEN 1.10*salary
    	WHEN 'ST_CLERK' THEN 1.15*salary
    	WHEN 'SA_REP' THEN 1.20*salary
    	ELSE salary 
    END "REVISED_SALARY"
    FROM employees;
    
  2. decode 函数
    #在需要使用  IF-THEN-ELSE 逻辑时:
    
    DECODE(col|expression, search1, result1,[, search2, result2,...,][, default])
    
    SELECT last_name, job_id, salary,
    DECODE(job_id, 'IT_PROG', 1.10*salary, 'ST_CLERK', 1.15*salary, 
    	'SA_REP', 1.20*salary,salary) REVISED_SALARY
    FROM employees;
    

3.7.嵌套函数

单行函数可以嵌套。
嵌套函数的执行顺序是由内到外。

4.多表查询

4.1.笛卡尔乘积

当我们从多个表中查询数据时,如果省略了连接条件、连接条件无效、或者表中所有行互相连接会出现笛卡尔乘积。
为了避免笛卡尔乘积,可以在where中加入有效的连接条件。
在不同表中具有相同列名的列可以用表的别名加以区分。

4.2.连接查询

在 WHERE 子句中写入连接条件。
在表中有相同列时,在列名之前加上表名前缀

SELECT table1.column, table2.column 
FROM table1, table2 
WHERE table1.column1 = table2.column2;

4.3.等值连接

SELECT emp.emp_id,emp.last_name,emp.dept_id, dept.dept_id,dept.location_id
FROM emp, dept
WHERE emp.dept_id = dept.dept_id;

SELECT emp.emp_id,emp.last_name,emp.dept_id, dept.dept_id,dept.location_id
FROM emp, dept
WHERE emp.dept_id = dept.dept_id;
AND last_name = 'Matos';

4.4.表的别名

  1. 使用别名可以简化查询
  2. 使用表名前缀可以提高执行效率
SELECT e.emp_id,e.last_name,e.dept_id, d.dept_id,d.location_id
FROM emp e, dept d
WHERE e.dept_id = d.dept_id;

4.5.非等值连接

SELECT e.last_name,e.salary,j.grade_level
FROM emp e, job_grades j
WHERE e.salary
BETWEEN j.lowest_sal AND j.highest_sal;

4.6.内/外连接

  1. 内连接

    合并具有同一列的两个以上的表的行, 结果集中不包含一个表与另一个表不匹配的行

  2. 外连接:

    两个表在连接过程中除了返回满足连接条件的行以外还返回左(或右)表中不满足条件的行,这种连接称为左(或右) 外连接。
    没有匹配的行时, 结果表中相应的列为空(NULL).
    外连接的 WHERE 子句条件类似于内部连接, 但连接条件中没有匹配行的表的列后面要加外连接运算符, 即用圆括号括起来的加号(+).

  3. 外连接语法

    使用外连接可以查询不满足连接条件的数据。
    外连接的符号是 (+).

    #右外连接
    SELECT table1.column, table2.column
    FROM table1, table2
    WHERE table1.column(+)  = table2.column;
    
    #左外连接
    SELECT table1.column, table2.column
    FROM table1, table2
    WHERE table1.column = table2.column(+);
    
  4. 内连接
    SELECT worker.last_name || ' works for '|| manager.last_name
    FROM employees worker, employees manager
    WHERE worker.manager_id = manager.employee_id ;
    

4.7.叉积

使用CROSS JOIN 子句使连接的表产生叉集。
叉集和笛卡尔集是相同的。

SELECT last_name, department_name
FROM employees
CROSS JOIN departments ;

4.8.自然连接

NATURAL JOIN 子句,会以两个表中具有相同名字的列为条件创建等值连接。
在表中查询满足等值条件的数据。
如果只是列名相同而数据类型不同,则会产生错误。
返回的是,两个表中具有相同名字的列的“且、交集”,而非“或,并集”。即:比如employee类和department类都有department_id和manager_id,返回二者都相同的结果。

SELECT department_id,department_name,location_id, city
FROM departments
NATURAL JOIN locations ;

4.9.USING 子句

在NATURAL JOIN 子句创建等值连接时,可以使用 USING 子句指定等值连接中需要用到的列。
使用 USING 可以在有多个列满足条件时进行选择。
不要给选中的列中加上表名前缀或别名。
JOIN 和 USING 子句经常同时使用。

select last_name,department_name
from employees
natural  join departments ;    
注:会自动匹配表中相同的列,可多列匹配;

select last_name,department_name
from employees 
join departments using (department_id);

4.10.on子句

自然连接中是以具有相同名字的列为连接条件的。
可以使用 ON 子句指定额外的连接条件。
这个连接条件是与其它条件分开的。
ON 子句使语句具有更高的易读性。

SELECT e.employee_id, e.last_name, e.department_id,d.department_id, d.location_id
FROM employees e 
JOIN departments d
ON (e.department_id = d.department_id);

SELECT employee_id, city, department_name
FROM employees e
JOIN departments d
ON d.department_id = e.department_id
JOIN locations l
ON d.location_id = l.location_id;

4.11.左右连接

两个表在连接过程中除了返回满足连接条件的行以外还返回左(或右)表中不满足条件的行,这种连接称为左(或右) 外连接。
两个表在连接过程中除了返回满足连接条件的行以外还返回两个表中不满足条件的行 ,这种连接称为满 外连接。

SELECT e.last_name, e.department_id, d.department_name
FROM employees e
LEFT OUTER JOIN departments d
ON (e.department_id = d.department_id) ;

SELECT e.last_name, e.department_id, d.department_name
FROM employees e
RIGHT OUTER JOIN departments d
ON (e.department_id = d.department_id) ;

SELECT e.last_name, e.department_id, d.department_name
FROM employees e
FULL OUTER JOIN departments d
ON (e.department_id = d.department_id) ;

5.分组函数

5.1.组函数

  1. AVG和SUM函数

    可以对数值型数据使用AVG 和 SUM 函数

    SELECT AVG(salary),SUM(salary)
    FROM employees
    WHERE job_id LIKE '%REP%';
    
  2. MIN和MAX函数

    可以对任意数据类型的数据使用 MIN 和 MAX 函数

    SELECT MIN(hire_date), MAX(hire_date)
    FROM  employees;
    
  3. COUNT函数

    COUNT(*) 返回表中记录总数,适用于任意数据类型

    SELECT COUNT(*)
    FROM  employees
    WHERE department_id = 50;
    

    COUNT(expr) 返回expr不为空的记录总数

    SELECT COUNT(abcd)
    FROM emp
    WHERE dept_id = 50;
    
  4. 组函数与空值

    组函数会忽略空值
    使用NAL函数可使分组函数无法忽略空值

    SELECT AVG(NVL(abcd, 0))
    FROM emp;
    
  5. DISTINCT关键字

    COUNT(DISTINCT expr)返回expr非空且不重复的记录总数

    SELECT COUNT(DISTINCT dept_id)
    FROM emp;
    

5.2.分组数据(GROUP BY)

可以使用GROUP BY子句将表中的数据分成若干组
在SELECT 列表中所有未包含在组函数中的列都应该包含在 GROUP BY 子句中
包含在 GROUP BY 子句中的列不必包含在SELECT 列表中

SELECT dept_id,AVG(salary)
FROM emp
GROUP BY dept_id ;
	
SELECT AVG(salary)
FROM emp
GROUP BY dept_id ;

5.3.过滤分组(HAVING)

使用 HAVING 过滤分组:
1. 行已经被分组。
2. 使用了组函数。
3. 满足HAVING 子句中条件的分组将被显示。

SELECT dept_id, MAX(salary)
FROM emp
GROUP BY dept_id
HAVING MAX(salary)>10000 ;

5.4.嵌套组函数

SELECT MAX(AVG(salary))
FROM emp
GROUP BY dept_id;

5.5.注意事项

  1. 所有包含于SELECT 列表中,而未包含于组函数中的列都必须包含于 GROUP BY 子句中。
  2. 不能在 WHERE 子句中使用组函数。
  3. 可以在 HAVING 子句中使用组函数。

6.子查询

子查询 (内查询) 在主查询之前一次执行完成。
子查询的结果被主查询(外查询)使用 。
子查询要包含在括号内。
将子查询放在比较条件的右侧。
单行操作符对应单行子查询,多行操作符对应多行子查询。

SELECT last_name
FROM emp
WHERE salary > (
	SELECT salary
	FROM emp
	WHERE last_name = 'Abel'
);

6.1.单行子查询

只返回一行。
使用单行比较操作符。

= 等于
> 大于
>= 大于等于
< 小于
<= 小于等于
<> 不等于
SELECT last_name, job_id, salary
FROM emp
WHERE salary =(
	SELECT MIN(salary)
	FROM employees
);

SELECT dept_id, MIN(salary)
FROM emp
GROUP BY dept_id
HAVING MIN(salary) >(
	SELECT MIN(salary)
	FROM employees
	WHERE dept_id = 50
);

6.2.多行子查询

返回多行。
使用多行比较操作符。

IN 等于列表中的任意一个
ANY 和子查询返回的某一个值比较
ALL 和子查询返回的所有值比较
#返回其它部门中比job_id为‘IT_PROG’部门任一工资低的员工的员工号、姓名、job_id  以及salary
SELECT emp_id, last_name, job_id,salary
FROM emp
WHERE salary < ANY (
	SELECT salary
	FROM emp
	WHERE job_id = 'IT_PROG'
)
AND job_id <> 'IT_PROG';

#返回其它部门中比job_id为‘IT_PROG’部门所有工资都低的员工的员工号、姓名、job_id  以及salary
SELECT emp_id, last_name, job_id,salary
FROM emp
WHERE salary < ALL(
	SELECT salary
	FROM employees
	WHERE job_id = 'IT_PROG'
)
AND job_id <> 'IT_PROG';

评论

发表评论