01、Oracle基本select语句

OCP学习笔记

笔记下载链接 https://download.youkuaiyun.com/download/omaidb/16822501

1.1 【学习目标】

主要知识点：

1. select 语句的基本功能
2. 数学表达式在 select 语句中的应用
3. select 语句中 null 值的处理
4. 定义列的别名、 使用连接运算符
5. 在 select 语句中消除重复行

1.2 【select 语句基本功能】

select 语句可以从数据库表中检索信息
选择、 投影、 连接

选择： 查询符合条件的行， 过滤掉不符合条件的行
投影： 查询符合条件的列， 不显示不符合条件的列
连接： 从多个表中查询数据
连接是关系型数据库的核心， 把数据放在不同的表里， 通过关联获得完整信息。
表是由行和列组成的， 列构成表的结构， 行组成表的数据。select 语句并不改变数据库中的数据， 只是将数据从数据库中提取显示出来。

1.3 【select 语句基本语法】

[] 代表可选； * 代表所有列
语句： 一个语句的结束以； 为标志。
子句： 一个语句可以由多个子句组成
最基本的 select 语句包含两个子句： select 子句和 from 子句。

1.4 【SQL 语句的书写规范】

1.5 【选择所有列】

*** **代表所有列写比较方便， 但是要注意：
1.如果不需要查询所有列， 用会把所有数据都查出来， 增加 oracle 处理的代价， 性能就会降低。
2.写*时， oracle 需要查询数据字典， 转换为具体列名称。
3.sql 开发建议写具体字段名。

1.5.1 【熟悉 scott 用户方案】

用 scott 用户登录数据库user_tables;
存储的是当前用户所有表的信息——数据字典表（视图）

SCOTT@orcl>select table_name from user_tables;

SCOTT@orcl>select * from emp;

SCOTT@orcl>select * from dept;

1.6 【选择特定列】

-- 选择特定列
SCOTT@orcl>select empno,ename from emp;

1.6.1 【伪列： rownum】

select 子句中还可以选择伪列。
伪列从功能上看像是表中的一列， 实际上并不存储在实际的表中。
常见伪列： rownum,rowid(对象号,文件号,块号,行号),currval,nextval
伪列上不能进行插入和删除操作。

官方文档： 伪列SQL Language Reference -> 2 Pseudocolumns
**rownum **伪列反映的是每一行在当前查询的结果集中的位置

select rownum,deptno,dname,loc from dept;

1.7 【描述表的结构】 describe

不知道具体列名时， 可使用 describe 命令查看表结构。

-- 查看表结构
SCOTT@orcl>desc dept

DESCRIBE :SQLPlus命令
官方文档：
SQLPlus **命令
**SQLPlus User’s Guide and Reference -> 12 SQL*Plus Command Reference

1.8 【列标题的缺省格式】

SCOTT@orcl>select * from emp;

ENAME 是字符类型，
HIREDATE 是日期类型:左对齐；
EMPNO、 MGR、 SAL 等是数值类型： 右对齐。
数据是区分大小写的。

1.9 【格式化列的宽度】 column … format

a接数字表字符长度，9999是数字长度（0为自动补充）

SCOTT@orcl>column hiredate format a10
SCOTT@orcl>col mgr for 9999
SCOTT@orcl>select * from emp;

SCOTT@orcl>col sal for 0999
SCOTT@orcl>select * from emp;

【补充说明】
如果列宽度设置不够， 会出现###。

1.10 【数学表达式在 select 中】

select 语句功能强大， 可以在 **SELECT **语句中用数学表达式对列值进行计算

需求： 涨工资， 查询每个员工工资增加 **300 **以后的值

-- 查询每个员工工资增加 300 以后的值 
SCOTT@orcl>select ename,sal,sal+300 from emp;

1.10.1【表达式的优先级】

1.10.2【圆括号改变优先级】

需求： 查询员工全年的收入（年终奖 **100/**每月奖励 100）

-- 查询员工全年的收入（年终奖 100/每月奖励 100） 
SCOTT@orcl>select ename,sal,12*sal+100,12*(sal+100) from emp;

1.11 【空值的定义和处理】

需求： 查询员工工资**+**奖金总收入

-- 查询员工工资+奖金总收入 
SCOTT@orcl>select ename,sal,comm,sal+comm from emp;

1.11.1 【空值处理 nvl 函数】

**nvl **函数， 如果是空值， 就转换为 0： nvl(comm,0)

-- 如果是空值就转换为0
SCOTT@orcl>select comm,nvl(comm,0) from emp;

SCOTT@orcl>select empno,ename,sal,comm,sal+nvl(comm,0) from emp;

SCOTT@orcl>select empno,ename,sal,comm,sal+comm from emp；

1.12 【定义列别名 as、 空格、 双引号】

需求： 查询员工编号、 姓名、 工资。
未使用别名时的显示：

SCOTT@orcl>select empno,ename,sal from emp;

【使用别名目的】 为了以友好的方式显示， 可以定义列的别名 。

-- 使用别名显示
SCOTT@orcl>select empno,ename,sal+nvl(comm,0) year from emp;

当别名中有特殊字符（如空格、 / 等）、 别名需要区分大小写时， 要用双引号。
【使用别名注意事项】列的别名不能在数学表达式中运算

1.13 【字符串、 连接符||、 引用运算符 q】

1.13.1字符串

SCOTT@orcl>select 'ena' from emp;

1.13.2连接符

Oracle 使用单引号作为封装或包含其他字符的特殊符号。
需求： SMITH is a CLERK

SCOTT@orcl>select ename,job,ename||' is a '||job from emp;

【特殊情况】
需要显示单引号时， 通常在字符串中应该出现的单引号前面再加一个单引号进行转义。需求： SMITH’s job is CLERK
【错误用法】

SCOTT@orcl>select ename ||''s job is '|| job from emp;

【正确用法】 其他逃逸符是不能用的（如escape）

SCOTT@orcl>select ename ||'''s job is '|| job from emp;

此处 ’ 符号为转移符 其他逃逸符是不能用的（不能用escape）

1.13.3引用运算符

SCOTT@orcl>select ename||q'<'s job is >'||job from emp;

<可以用其它特殊符号代替

{} [] \ <> 等符号内部的字符作为普通字符正常显示。

1.14 【消除重复行】（distinct）

需求： 查询 **emp **表中员工工作在哪几个部门

-- 查询 emp 表中员工工作在哪几个部门 
SCOTT@orcl>select distinct deptno from emp；
SCOTT@orcl>select distinct(deptno) from emp；

1.14.1对多个列去重

-- 对多个列去重 
SCOTT@orcl>select distinct deptno,job from emp;

1.14.2 distinct 的误用

**distinct **关键字前不能查询其他列；

SCOTT@orcl>select empno,distinct deptno from emp;

一个 **select **子句中不能出现多个 distinct

SCOTT@orcl>select distinct empno,distinct deptno from emp;

1.15 【save 保存查询语句】
执行查询语句

SCOTT@orcl>select distinct deptno,job from emp;

查看历史命令

-- list或 者 l 
SCOTT@orcl>list
SCOTT@orcl>1

-- 保存此 sql 语句到操作系统中 /home/oracle/p1_01.sql 
SCOTT@orcl>save /home/oracle/p1_02.sql

-- 在 sqlplus 中查看保存在操作系统中文件
SCOTT@orcl>get /home/oracle/p1_02.sql
SCOTT@orcl>!cat /home/oracle/p1_02.sql

-- sqlplus 中执行操作系统中的 sql 脚本/home/oracle/p1_01.sql
SCOTT@orcl>@/home/oracle/p1_02.sql