-
-基本的
select查询语句
-
-查询所有的部门
select
*
from departments;
-
-查询所有的部门,只看部门号和位置号
select department_id,location_id
from departments;
-
-在
select子句中,可以使用算术表达式,对查找到的列
-
-做各种计算
-
-查看雇员薪水加上
300以后的结果
select last_name,salary,salary
+
300
from employees;
-
-使用小括号可以改变运算的优先级
-
-先把雇员的薪水加上
100,然后计算年薪并显示
select last_name,salary,
12
*(salary
+
100)
from employees;
-
-
null值
-
-一些列具有空值(
null)。
null值是未知的值。
-
-
null值是如何进入到表中的?当向表里插入行时,
-
-如果不给某个列明确地赋值,那么它将自动得到
null值。
-
-(注意,
null值和列的数据类型无关)
-
-在算术表达式中如果包含
null值,那么整个表达式的结果就为
null
select last_name,salary,commission_pct
from employees;
select last_name,salary,commission_pct
,
12
*salary
*commission_pct
from employees;
-
-使用列别名
-
-直接跟在列名后面来定义。可以选用
as关键字也可以不用
-
-列别名会将查询返回的结果集的列标题改名字。
-
-以下
3种情况,列别名必须加双引号:
-
-
1)列别名中包含空格
-
-
2)列别名中包含特殊字符
-
-
3)列别名是大小写敏感的
select last_name
as
name,commission_pct comm
from employees;
select last_name
as
"Name",
12
*salary
"Annual Salary "
from employees;
-
-使用串联操作符:||
-
-该操作符将字符串首尾相连。
select last_name||job_id
"Employees"
from employees;
-
-在
select语句中可以使用字面量(常量):
-
-定义:对于字符串和日期字面量,必须用单引号括起来。
-
-对于数字字面量,直接写。
-
-字面量用在
select子句中,那么他会对查询返回的每一行输出一次
select last_name ||
' is a ' || job_id
"employees details"
from employees;
-
-以下查询的结果集?
select
100
from employees;
select last_name 姓名
from employees;
-
-使用
distinct关键字去掉查询结果集中的重复的行
-
-何谓重复的行?
-
-任意两行在列或者列的组合上有完全相同的值
-
-
distinct影响其后所有的列
-
-查询所有雇员所分布的部门的部门号.有重复的
select department_id
from employees;
-
-查询所有雇员所分布的部门的部门号.去掉重复的部门
select
distinct department_id
from employees;
select
distinct department_id,job_id
from employees;
-
-使用
desc命令查看表的结构
desc departments;
-
-使用
where子句限制查询返回的行
-
-
where子句又叫做过滤条件,其后的条件表达式必须返回
-
-
true或者
false。
-
-工作过程:对于表中的每一行,都取出某个或某些特定列的值,
-
-带入到
where条件中进行比较判断,如果比较结果为
true,则
-
-这一行就出现在最终的结果集中;否则,这一行被抛弃。
-
-查询部门
90 的所有雇员
select last_name,salary,department_id
from employees
where department_id
=
90;
-
-在
where中使用字面量
-
-
1)对于字符串和日期字面量,一定要用单引号括起来
-
-
2)对于字符串字面量,特别注意:它是大小写敏感的
-
-查询雇员惠伦的信息
-
-返回
0行
select last_name,salary
from employees
where last_name
=
'whalen';
-
-查询雇员惠伦的信息
-
-返回
1行
select last_name,salary
from employees
where last_name
=
'Whalen';
-
-对于日期字面量,一定注意它是格式敏感的。
-
-日期值的格式一定要满足默认的日期显示格式。
-
-查询
2007年
2月
7号入职的雇员
select last_name,hire_date
from employees
where hire_date
=
'2007-02-07';
-
-查询
2007年
2月
7号入职的雇员
-
-ORA
-
01858: 在要求输入数字处找到非数字字符
select last_name,hire_date
from employees
where hire_date
=
'07-feb-07';
-
-查看数据字典得到客户端默认的日期显示格式
select NLS_DATE_FORMAT
from nls_session_parameters;
-
-在
where条件中使用各种操作符
-
-
1)
=、
>,
<,
<
=,
>
=,不等于:
<>,!
=,^
=
-
-查询薪水小于等于
3000的雇员
select last_name,salary
from employees
where salary
<
=
3000;
-
-
2)
between 下限值
and 上限值:
-
-测试某个列值在一个取值范围之内
-
-查询薪水在
2500和
3500之间的雇员
select last_name,salary
from employees
where salary
between
2500
and
3500;
-
-
3)
in(值的列表):
-
-测试某个列值是否等于列表中的任何一个值。如果相等则返回
true
-
-
in操作在数据库内部等同于
or操作
-
-
IN操作符可用于任何数据类型
-
-查询经理编号为
101或者
102或者
201的雇员的信息
select last_name,salary,manager_id
from employees
where manager_id
in (
101,
102,
201);
-
-
4)
like
'匹配模式':
-
-
like操作符后面必须跟一个字符串字面量,该字面量作为
-
-一个匹配模式来使用。
like又叫做模糊查询或者通配符查询
-
-匹配模式中只有两个特殊字符:
-
- %:可表示
0个或任意多个字符
-
- _:可表示任意一个字符
-
-查询姓名以大S开头的雇员的姓名
select first_name
from employees
where first_name
like
'S%';
-
-查询姓名的第二个字母为o的雇员的姓名
select last_name
from employees
where last_name
like
'_o%';
-
-如果要搜索的字符串本身就包含_或%字符,那么
-
-需要使用
escape选项先指定一个转义字符,然后把
-
-转义字符放在_或%字符前面即可
-
-查询工作编号包含字符“SA_”的雇员的信息
select last_name,job_id
from employees
where job_id
like
'%SA\_%'
escape
'\';
-
-
5)
is
null操作符:
-
-测试一个列值是否为
null值
-
-查询经理编号为
null的雇员
select last_name,manager_id
from employees
where manager_id
is
null;
-
-切记不能这么写:
select last_name,manager_id
from employees
where manager_id
=
null;
-
-在单个的
WHERE子句中,通过使用
AND和
OR操作符,
-
-可以把多个条件连接起来
-
-
1)
and:逻辑与操作
-
-只有当每个子条件都返回
true时,
and的结果才为
true。否则,
-
-
and的结果都是
false
-
-查询月薪大于等于
10000元并且工作编号包含
-
-字符串MAN的雇员的信息
select last_name,salary,job_id
from employees
where salary
>
=
10000
and job_id
like
'%MAN%';
-
-
2)
or:逻辑或操作
-
-只要有一个子条件返回
true,
or的结果就为
true。否则,
-
-
or的结果都是
false
-
-查询月薪大于等于
10000元或者工作编号包含字符串MAN的雇员的信息
select last_name,salary,job_id
from employees
where salary
>
=
10000
or job_id
like
'%MAN%';
-
-
-
-
3)
not:逻辑非操作
-
-如果子条件返回
true,
not的结果就为
false。否则,
-
-
not的结果是
true
-
-
not操作符一般不单独使用,它和其它操作符一起用。例如:
-
-
not
in,
not
like,
not
between
and,
is
not
null,
-
-查询工作编号不是IT_PROG或者 ST_CLERK或者 SA_REP的雇员
-
-的姓和工作编号
select last_name,job_id
from employees
where job_id
not
in (
'IT_PROG',
'ST_CLERK',
'SA_REP');
-
-当
where子句中条件较多时,使用小括号来决定计算的优先级。
-
-这样写同时使得代码可读性很高
-
-选择雇员是总裁或者销售代表,并且月薪大于
15000的行
select last_name,salary,job_id
from employees
where (job_id
=
'SA_REP'
or job_id
=
'AD_PRES')
and salary
>
15000;
-
-使用
order
by子句对查询返回的结果集进行排序
-
-语法:
-
-[
ORDER
BY {
column,expr,numeric_position} [
ASC|
DESC]];
-
-
order
by子句是最后执行的子句
-
-
asc:指明是升序排序,默认值
-
-
desc:指明是降序排序
-
-将所有雇员按照入职日期做升序排序
-
-
1)直接指定排序列
select last_name,salary,hire_date
from employees
order
by hire_date;
-
-
-
-将所有雇员按照入职日期做降序排序
select last_name,salary,hire_date
from employees
order
by hire_date
desc;
-
-
2)可以使用表达式或者列别名来排序
-
-按照雇员的年薪大小进行排序
select last_name,salary,
12
*salary annsal
from employees
order
by annsal;
select last_name,salary,
12
*salary annsal
from employees
order
by
12
*salary;
-
-
3)指定一个数字位置来排序
-
-该数字表示
select列表的的第几个列
-
-
-
-按照雇员的部门号进行排序
SELECT last_name,salary,department_id,job_id
from employees
order
by
3;
-
-
4)排序列可以有多个
-
-将雇员先按照部门排序,部门相同的雇员按照薪水做降序排序
select last_name,department_id,salary
from employees
order
by department_id,salary
desc;
-
-在
sql语句中,可以使用替代变量接收用户的输入
-
-替代变量有两种语法形式:
-
-带一个&符号的和带两个&&符号的
-
-使用替代变量输入数字时,不需要给替代变量加单引号
-
-根据用户输入的雇员编号,查找雇员的信息
select employee_id,last_name,salary
from employees
where employee_id
= &emp_id;
-
-使用替代变量输入字符串或者日期时,必须给替代变量加单引号
-
-根据用户输入的工作编号,查找相应的雇员
select last_name,salary,job_id
from employees
where job_id
=
'&job_title';
-
-根据用户输入的工作编号,查找相应的雇员
-
-假设输入SA_REP
-
-ORA
-
00904:
"SA_REP": 标识符无效
select last_name,salary,job_id
from employees
where job_id
= &job_title;
-
-以f5执行以上语句,可以看到替代变量的赋值过程
select last_name,salary,job_id
from employees
where job_id
= &job_title;
-
-使用
set verify
off命令可以隐藏替代变量的替换过程
set verify
off
-
-观察以下查询,有两个同名的替代变量:
-
-输入几次?
select employee_id,last_name,salary,&
column_name
from employees
order
by &
column_name;
-
-使用带两个&&符号的替代变量,同名变量只需要输入一次
select employee_id,last_name,salary,&&
column_name
from employees
order
by &
column_name;
-
-和替代变量有关的两条命令:
-
-define:定义一个替代变量
-
-undefine:销毁一个替代变量
-
-定义一个名为employee_num的替代变量。注意变量名前面没有&符号
define employee_num
=
200;
select employee_id,last_name,salary
from employees
where employee_id
= &employee_num;
undefine employee_num;
-
-在
select语句中使用函数
-
-单行函数:这种函数对查询返回的每一行会执行一次,并
-
-返回一个结果
-
-可以分为
5种:
-
-字符串函数、数字函数、日期函数、通用函数和类型转换函数
-
-
1、字符串函数
-
-
1)大小写转换函数:
lower、
upper、initcap
-
-直接使用
select语句来调用函数
select
lower(
'SQL Course'),
upper(
'SQL Course'),
initcap(
'SQL Course')
from dual;
-
-dual表是oracle中的一个特殊的系统表,它属于sys用户的。
-
-它永远只有一行一列。主要设计用来完成
select语句的语法。
desc dual;
select
*
from dual;
-
-大小写转换函数可用在
where条件中,对列值做转换
-
-查找雇员Higgins
select last_name,salary
from employees
where
lower(last_name)
=
'higgins';
-
-
2)concat:将两个字符串参数首尾相连返回。等同于||操作符
select concat(
'Hello',
'World')
from dual;
-
-
3)substr:从一个源字符串中取出一个子串返回
select substr(
'Hello World',
3,
3)
from dual;
select substr(
'Hello World',
5)
from dual;
-
-
4)instr:从一个源字符串中找到指定的子串出现的位置
select instr(
'Hello World',
'o',
1)
from dual;
select instr(
'Hello World',
'o',
1,
2)
from dual;
-
-
5)
length:返回字符串的长度
select
length(
'hello')
from dual;
select
length(
'你好')
from dual;
-
-
6)lpad和rpad:在一个源字符串的左边或者右边填充指定数量的字符并返回
select lpad(
'hello',
10,
'*') rs1,
rpad(
'hello',
10,
'*') rs2
from dual;
-
-
7)
trim:去掉字符串的首尾空格
select
trim(
' hello ') rs
from dual;
-
-去掉字符串首部或者尾部的
1个字符
select
trim(
leading
'h'
from
'hello') rs
from dual;
select
trim(
trailing
'o'
from
'hello') rs
from dual;
-
-
-
-
8)ltrim
/rtrim:去掉字符串的首部或者尾部空格
select ltrim(
' hello ') rs1,rtrim(
' hello ') rs2
from dual;
-
-
9)replace:在源字符串中查找一个子串,找到后把子串替换成
-
-另一个子串
select replace(
'Jack and Jue',
'J',
'Bl')
from dual;
-
-数字函数:
3个
-
-round:将数字四舍五入到指定的小数位
-
-trunc:将数字截断到指定的小数位(不四舍五入)
-
-
mod:返回两个整数相除后的余数
-
-注意:小数位为负的意味着从小数点的左边进行四舍五入或截断
select round(
45.
923,
2),round(
45.
923,
0),
round(
45.
923,
-
1),round(
45.
923,
-
2)
from dual;
select trunc(
45.
923,
2),trunc(
45.
923,
0),
trunc(
45.
923,
-
1),trunc(
45.
923,
-
2)
from dual;
select
mod(
5,
2)
from dual;
-
-日期函数
-
-
1)sysdate:返回数据库的当前日期和时间
select sysdate
from dual;
-
-ORA
-
00923: 未找到要求的
FROM 关键字
select sysdate()
from dual;
-
-
2)
current_date:返回用户会话(客户端)的当前日期和时间
select
current_date
from dual;
-
-因为数据库把日期作为数字存储,因此可以对日期进行加减运算。
-
-给日期加
3天
select sysdate
+
3
from dual;
-
-给日期减
3天
select sysdate
-
3
from dual;
-
-
-
-给日期加
2个小时
select sysdate
+
2
/
24
from dual;
select to_char(sysdate,
'YYYY-MM-DD HH24:MI:SS'),
to_char(sysdate
+
2
/
24,
'YYYY-MM-DD HH24:MI:SS')
from dual;
-
-两个日期相减返回相差的天数
-
-查询雇员在公司一共工作了几周
select last_name,
(sysdate
- hire_date)
/
7 weeks
from employees;
-
-MONTHS_BETWEEN(date1, date2):
-
-返回两个日期差几个月。结果的小数部分代表月的一部分
select months_between(sysdate,hire_date)
from employees;
-
-ADD_MONTHS(
date, n): 给日期加几个月。N是整数可以为负数
select add_months(sysdate,
5)
from dual;
-
-NEXT_DAY(
date, ‘
char’): 找到从
date开始的下一个星期几的日期。
char表示星期几
-
-找到下一个星期三是几月几号
select next_day(sysdate,
'星期三')
from dual;
-
-ORA
-
01846: 周中的日无效
select next_day(sysdate,
'Wensday')
from dual;
-
-ROUND(
date[,
'fmt']):
-
-TRUNC(
date[,
'fmt']):
-
-将日期按照给定的格式模型
month或者
year进行四舍五入或者截断
select round(sysdate,
'month'),trunc(sysdate,
'month')
from dual;
select round(sysdate,
'year'),trunc(sysdate,
'year')
from dual;
select trunc(sysdate)
from dual;
-
-类型转换函数
-
-oracle中的类型转换有两种:
-
-隐式类型转换:oracle自动做的
-
-显式类型转换:使用函数来实现的
-
-尽量避免在
sql语句中发生自动类型转换,因为
-
-它可能会对执行计划的生成带来负面影响
-
-类型转换函数:
3个
-
-
1)to_char(
date,
'fmt model'):将日期
-
-按照指定的日期格式模型转换成字符串
-
-常用的日期格式元素:YYYY、MM、MON、DD、
DAY等等
select last_name,hire_date,
to_char(hire_date,
'YYYY/MM/DD')
from employees;
select last_name,hire_date,
to_char(hire_date,
'fmDD MONTH YYYY')
from employees;
select last_name,hire_date,
to_char(hire_date,
'fmday month year')
from employees;
-
-
2)to_char(
number,
'fmt model'):将数字
-
-按照指定的数字格式模型转换成字符串
-
-常用的数字格式元素:
9、
0、$、L、. ,等等
-
-注意:
9的个数决定了数字的宽度
-
-将salary列格式化显示
select last_name,salary,
to_char(salary,
'L99,999.00')
from employees;
-
-数字宽度不够显示#号
select last_name,salary,
to_char(salary,
'L9,999.00')
from employees;
-
-
3)to_numer(
char[,
'fmt model']):
-
-将字符串转成数字
select to_number(
'123')
from dual;
select to_number(
'$1,123.78',
'$9,999.99')
from dual;
-
-
-
-
4)to_date(
char[,
'fmt model']):
-
-将字符串转成日期
select to_date(
'2005/05/12',
'YYYY/MM/DD')
from dual;
-
-考虑到转换的通用性,月份不要使用字母来表示,而是使用数字
-
-ORA
-
01843: 无效的月份
select to_date(
'2005/oct/12',
'YYYY/MON/DD')
from dual;
-
-在
where条件中,如果要对日期值做比较,一般都是用
-
-to_date函数提供一个真正的日期值来进行比较,避免
-
-隐式类型转换
-
-查询
2005
-
01
-
01之前入职的雇员
select last_name,hire_date
from employees
where hire_date
< to_date(
'2005 01 01',
'YYYY MM DD');
-
-函数的嵌套使用
-
-把一个函数调用作为参数直接传给另一个函数,叫做函数嵌套。
-
-单行函数可以嵌套任何深度
select last_name,
upper(concat(substr(last_name,
1,
8),
'_us'))
from employees;
-
-通用函数
-
-都可以对
null值做转换处理
-
-有:nvl、nvl2、
nullif、
coalesce
-
-nvl(列名,
value):
-
-如果第一个参数的值不为
null,则直接返回它;
-
-如果第一个参数的值为
null,则返回第二个参数值
value
-
-要求两个参数的类型必须相同
-
-计算每个雇员的年收入(年薪
+年佣金)
select last_name,commission_pct,
(
12
*salary)
+(
12
*salary
*nvl(commission_pct,
0)) ann_sal
from employees;
-
-NVL2(expr1, expr2, expr3)
-
-NVL2函数解释第一个表达式。如果它的值不为
null,
-
-函数返回第二个表达式。如果第一个表达式的值为
null,
-
-函数返回第三个表达式。
-
-显示雇员的收入构成
select last_name,commission_pct,
nvl2(commission_pct,
'SAL+COMM',
'SAL') income
from employees;
-
-
3
/
NULLIF (expr1, expr2):
-
-
NULLIF比较expr1
and expr2。如果它们相等则函数
-
-返回
null。如果它们不等,函数返回expr1。
-
-但是,你不能指定expr1是一个字面量
null。
select
nullif(
'hello',
'hello')
from dual;
select
nullif(
'hello1',
'hello')
from dual;
-
-ORA
-
00932: 数据类型不一致: 应为
-, 但却获得
CHAR
select
nullif(
null,
'hello')
from dual;
-
-
COALESCE (expr1, expr2, ... exprn)
-
-返回列表中第一个非
null的表达式
-
-注意,所有的表达式必须是一样的类型
-
-ORA
-
00932: 数据类型不一致: 应为
NUMBER, 但却获得
CHAR
select last_name,
coalesce(commission_pct,manager_id,
'no comm and no manager')
from employees;
select last_name,
coalesce(to_char(commission_pct),
to_char(manager_id),
'no comm and no manager')
from