Oracle简介
1、sqlplus连接远程Oracle命令
sqlplus(需要设置环境变量) scott(用户名)/5456(密码)@192.168.230.128:1521(IP)/orcl(数据库名) -- 如果是本地的Oracle,@后面的不用写
2、查看当前用户
show user -- sqlplus命令
3、查看当前用户下的表
select * from tab;
4、执行上一条SQL语句
/ -- sqlplus命令
1、基本查询
1)查看员工表结构——desc
desc emp;
2)查询员工表中的数据
select * from emp;
eg.查询员工表中的员工号,姓名,月薪,年薪(月薪*12)
SELECT EMPNO,ENAME,SAL,SAL*12 FROM EMP;
3)取别名的3种方式
/**
* 取别名的3种方式:
* 1.加不加as是一样的
* 2.如果起的别名中没有特殊字段,可以不用加""
*/
SELECT
EMPNO "员工id",
ENAME AS "员工姓名",
SAL 月薪,
SAL * 12 "年 薪", -- 有空格,要加""
NVL( COMM, 0 ) "奖select金", -- 有特殊字段,要加""
SAL * 12+NVL ( COMM, 0 ) 年收入
FROM
EMP;
4)去重——distinct
SELECT DISTINCT
JOB,
EMPNO
FROM
EMP; -- 去重,distinct修饰的后面的所有字段(JOB和EMPNO两者搭配不重复),只要字段们不重复就展示
5)字符串连接
连接符 “||”
/**
* 字符串连接
* MySQL版:select concat('hello',' world'); -- 要用 单引号(单引号表示日期和字符串,双引号表示列的别名)
* Oracle版:select concat('hello',' world') from dual; -- 如果前面的操作,和任何表都没有关系就用dual表(伪表)
* select 'hello' || ' world' from dual;
*/
6)排序
order by后面可以+列、表达式、别名、序号
SELECT
EMPNO,
ENAME,
SAL,
SAL * 12 "年薪"
FROM
EMP
ORDER BY
"年薪", -- 我们还可以使用列号来代替字段,比如此处就可以写4(第4列)
SAL;-- 多个列排序,order by先按照第一个排序,在按照后面的列排序
desc只作用于离他最近的字段
SELECT
EMPNO,
ENAME,
SAL,
SAL * 12 "年薪"
FROM
EMP
ORDER BY
EMPNO,
"年薪" desc; -- desc只作用于离他最近的字段
空值的5大特性
1)包含null的表达式结果都为null
SELECT
EMPNO,
ENAME,
SAL,
SAL * 12,
COMM,
SAL * 12+COMM
FROM
EMP;
此时我们需要一个方法NVL
nvl(a,b) -- 判断a是否为null,如果为null则取b的值
修改后的:
SELECT
EMPNO,
ENAME,
SAL,
SAL * 12,
NVL(COMM, 0),
SAL * 12+NVL(COMM, 0)
FROM
EMP;
2)null永远不为null
空值是无效的,未指定的,未知的或不可预知的值;空值不是0或者空格
SELECT
*
FROM
EMP
WHERE
COMM IN ( 300, 500, NULL );
3)如果集合中含有null,不能使用not in,可以使用in
SELECT
*
FROM
EMP
WHERE
COMM NOT IN ( NULL );
4)null值的排序问题
SELECT
*
FROM
emp
ORDER BY
COMM DESC; -- 原因:在Oracle中,空值最大
为了让null值放到最后,采用nulls last进行操作:
SELECT
*
FROM
emp
ORDER BY
COMM DESC NULLS LAST;
5)分组/多行/组函数会自动滤空
SQL与SQLPLUS
SQL优化
1.*和列名,尽量使用列名 -- 列名:减少了分析过程
2.where解析顺序是从右-->左,尽量把假的放前面
2、条件查询
使用where语句对查询结果进行过滤
1)非空和空的限制
- 非空:is not null
- 为空:is null
eg.查询每个月能得到奖金的雇员
SELECT
*
FROM
EMP
WHERE
comm IS NOT NULL;
2)范围限制
优先级问题
eg1.查询基本工资大于1500但是小于3000的全部雇员
SELECT * FROM EMP WHERE SAL>1500 AND SAL<3000;
或
SELECT * FROM EMP WHERE SAL BETWEEN 1500 AND 3000; -- 这种方式包括1500和3000
eg2.查询1981-1-1到1981-12-31入职的雇员(between and还可以用于日期的区间)
SELECT
*
FROM
EMP
WHERE
HIREDATE BETWEEN to_date( '1981-1-1', 'yyyy-mm-dd' )
AND to_date( '1981-12-31', 'yyyy-mm-dd' ); -- 字符串==>日期
eg3.查询雇员编号为7369,7499,7521的雇员标号的具体信息
SELECT * FROM EMP WHERE EMPNO IN (7369,7499,7521);
3)模糊查询
SQL中采用LIKE关键字表示模糊查询,它有以下通配符:
- “%”:可以匹配任意长度的内容
- “_”:匹配一个长度的内容
eg.查询雇员名中第二个字符包含M的雇员
SELECT * FROM EMP WHERE ENAME LIKE '_M%';
3、单行函数
1)字符函数
转小写——lower
SELECT
LOWER( 'HELLO,WORLD' ) 转小写
FROM
dual;
转大写——upper
SELECT
UPPER( 'hello,world' ) 转大写
FROM
dual;
首字母大写——initcap
SELECT
initcap( 'hello,world' ) 首字母大写
FROM
dual;
>> Hello,World
取子串——substr
SELECT
substr( 'Hello World', 4 ) 子串
FROM
dual;--substr(a,b) 从a中,第b位开始取
SELECT
substr( 'Hello World', 4, 3 ) 子串
FROM
dual;--substr(a,b,c) 从a中,第b位开始取,取c位
取字符数与字节数——length,lengthb
SELECT
length( 'Hello 中国' ) 字符, -- 8
lengthb ( 'Hello 中国' ) 字节 -- 10
FROM
dual;
在a中,查找b的位置——instr
SELECT
instr( 'Hello World', 'lo' ) 位置
FROM
dual;
>>3
左填充与右填充——lpad,rpad
SELECT
lpad( 'abcd', 10, '*' ) 左,
rpad( 'abcd', 10, '*' ) 右
FROM
dual;
>>左:******abcd,右:abcd******
去掉前后指定的字符
SELECT
trim( 'H' FROM 'Hello WorldH' )
FROM
dual;
>>ello World
替换——replace
SELECT REPLACE
( 'Hello World', 'l', '*' )
FROM
dual;
>>He**o Wor*d
2)数值函数
四舍五入——round(数字+日期)
SELECT
round( 45.926, 2 ) 一,
round( 45.926, 1 ) 二,
round( 45.926, 0 ) 三,
round( 45.926,- 1 ) 四,
round( 45.926,- 2 ) 五 -- round(55.926,-2) ==> 100
FROM
dual;
一 二 三 四 五
---------- ---------- ---------- ---------- ----------
45.93 45.9 46 50 0
SELECT
round( SYSDATE, 'month' ), -- 时间按月来4舍5入
round( SYSDATE, 'year' ) -- 按年来4舍5入
FROM
dual;
>>2018-05-01 00:00:00 2018-01-01 00:00:00
截断——trunc
SELECT
trunc( 45.926, 2 ) 一,
trunc( 45.926, 1 ) 二,
trunc( 45.926, 0 ) 三,
trunc( 45.926,- 1 ) 四,
trunc( 45.926,- 2 ) 五
FROM
dual
一 二 三 四 五
---------- ---------- ---------- ---------- ----------
45.92 45.9 45 40 0
3)日期函数
显示当前时间
SELECT SYSDATE
FROM
dual;
>>2018-05-01 15:10:03
格式化时间(日期-->字符串)
SELECT
to_char( SYSDATE, 'yyyy-mm-dd hh24:mi:ss' )
FROM
dual;
>>2018-05-01 15:10:43
eg.打印昨天,今天,明天
SELECT
( SYSDATE - 1 ) 昨天,
SYSDATE 今天,
( SYSDATE + 1 ) 明天
FROM
dual;
昨天 今天 明天
-------------- -------------- --------------
28-9月 -16 29-9月 -16 30-9月 -16
eg.计算员工的工龄:天,星期,月,年
SELECT
ename,
hiredate,
( SYSDATE - hiredate ) 天,
( SYSDATE - hiredate ) / 7 星期,
( SYSDATE - hiredate ) / 30 月,
( SYSDATE - hiredate ) / 365 年
FROM
emp;
返回两个日期之间的月份数
-- 查询ename的入职时间,与当前时间相差几个月
SQL> select months_between(sysdate,hiredate) from emp where ename='FORD';
MONTHS_BETWEEN(SYSDATE,HIREDATE)
--------------------------------
439.12001
-- 查询系统时间与指定字符串(必须按照这个格式写,当然可以修改Oracle的日期格式)相差几个月
SQL> select months_between(sysdate,'06-6月-18') from emp where ename='FORD';
MONTHS_BETWEEN(SYSDATE,'06-6月-18')
-----------------------------------
1
计算当前系统时间加上53个月后的日期
SELECT
add_months( SYSDATE, 53 )
FROM
dual;
>>2022-10-01 15:41:31
本月的最后一天
SELECT
last_day( SYSDATE )
FROM
dual;
>>2018-05-31 15:42:53
获取下个星期三的日期
SELECT
next_day( SYSDATE, 'wed' ) -- 下个星期三的日期
FROM
dual;
select next_day(sysdate,'星期五') from dual; -- 牛B:竟然可以用中文
NEXT_DAY(SYSDA
--------------
13-7月 -18
4)转换函数
TO_CHAR
a.日期到字符串的转换
TO_CHAR(date,'format_model')
SELECT
EMPNO,
ENAME,
TO_CHAR( HIREDATE, 'YYYY"年"MM"月"DD' ) -- 日期转字符串
FROM
emp;
b.数字到字符串的转换
TO_CHAR(number,'format_model')
SELECT
EMPNO,
ENAME,
TO_CHAR(SAL,'$99,999') -- 格式化成的字符串格式
FROM
emp
TO_NUMBER 将字符串转换成数值
SELECT
TO_NUMBER('10')
FROM
dual
TO_DATE 将字符串转换成日期格式
SELECT
TO_DATE('1985-04-22', 'YYYY-MM-DD')-- 以后面的格式进行转换
FROM
dual
5)通用函数
NVL(a,b):判断a是否为空,为空返回b,不为空返回a
NVL2(a,b,c):当a=null时,返回c,否则返回b
NULLIF(a,b):a和b的值相等时,返回null;否则返回a
COALESCE(a,b,c,...):从左往右找到第一个不为空的值
6)条件表达式
条件表达式就是SQL中的判断,他有以下两种实现方式:
- CASE表达式,SQL99语法,较复杂(MySQL等都可以用)
- DECODE函数,Oracle的方法
eg.实现给员工涨工资,总裁1000 经理800 其他400
CASE表达式版
SELECT
ename,
job,
sal 涨前,
CASE
job WHEN 'PRESIDENT' THEN sal + 1000
WHEN 'MANAGER' THEN sal + 800
ELSE sal + 400
END 涨后
FROM
emp;
上面的相当于等号,这个相当于范围
DECODE函数版
SELECT
ename,
job,
sal 涨前,
decode( job, 'PRESIDENT', sal + 1000, 'MANAGER', sal + 800, sal + 400 ) 涨后
FROM
emp;
4、多行/(分)组函数
1)统计记录数——count()
2)最小值查询——min()
3)最大值查询——max()
4)查询平均值——avg()
5)求和函数——sum()
6)分组查询——group by
having后面的条件可以加多行函数
SELECT
DEPTNO,
MIN( SAL )
FROM
EMP
GROUP BY
DEPTNO
HAVING
MIN( SAL ) > 900;
5、多表查询
笛卡尔积
Oracle连接条件的类型
- 等值连接
- 不等值连接
- 外连接
- 自连接
查询语法
内连接
外连接
左连接