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连接条件的类型
- 等值连接
- 不等值连接
- 外连接
- 自连接
查询语法
内连接
外连接
左连接
