-- 查询表结构
DESC DEPT;
别名查询:使用as 关键字 ,可以省略
表名中不能有特殊字符或者关键字,如果有就加双引号
--dual
Oracle中的虚表,伪表,主要是用来补齐语法结构
select SYSDATE from dual;
-- distinct 去除重复数据
多列去除重复:每一列都一样才能够算作是重复
select DISTINCT job,DEPTNO from emp; //多列去除重复的
--日期函数
select SYSDATE from dual;
select CURRENT_DATE from dual;
-- 日期可以进行加减
select SYSDATE+1 from dual;
select SYSDATE-e.HIREDATE from EMP e
-- ADD_MONTHS 添加指定的月份
select ADD_MONTHS(SYSDATE, 1) from dual;
-- 获取最后一天
select LAST_DAY(SYSDATE) from dual;
--TO_CHAR 转换函数
select * from EMP where TO_CHAR(HIREDATE,'yyyy-mm-dd') ='1980-12-17'
--BETWEEN
select * from EMP where SAL<500 or SAL>2000;
select * from EMP where SAL not BETWEEN 500 and 2000;
--MONTHS_BETWEEN
--查询出所有员工入职距离现在多长时间
select MONTHS_BETWEEN(SYSDATE, e.HIREDATE) from EMP e
--查询出所有员工入职距离现在多少年
select MONTHS_BETWEEN(SYSDATE, e.HIREDATE)/12 from EMP e
-- ESCAPE用法 转义字符
-- 通用函数 nvl函数(将空值转换成一个已知的值)
如果第一位是null,则使用第二位,否则第一位
可以使用的数据类型有日期,字符,数字
函数的一般形式:
nvl(emp.sal,0)
-- VNL2函数
VNL2(expr1,expr2,expr3) //expr1不为空,返回expr2;若为null,返回expr3
-- NULLIF函数
NULLIF(expr1,expr2):相等返回NULL,不等返回expr1
--length 长度
select LENGTH('tom') from dual;
--ROUND 保留几位小数(进位)
select ROUND(3.56, 1) from dual;
--取模
select mod(10,4) from dual;
select mod(-10,4) from dual;
--向上取整
select ceil(12.12) from dual;
--向下取整
select FLOOR(12.12) from dual;
-- 绝对值
select abs(-100) from dual;
--获取正负值
select sign(100) from dual;
-- 多行函数
多行函数就是聚合函数 sum count min avg
单行函数:作用于单行,返回一个值
多行函数:作用于多行,返回一个值
--STDDEV
--VARIANCE
--GROUP BY(分组查询)
select DEPTNO,SUM(sal) from emp HAVING SUM(sal) >9000 GROUP BY DEPTNO;
select DEPTNO,SUM(sal) from emp GROUP BY DEPTNO HAVING SUM(sal) >9000;
--HAVING(过滤)
HAVING语句通常与GROUP BY语句联合使用,用来过滤由GROUP BY语句返回的记录集。
HAVING语句的存在弥补了WHERE关键字不能与聚合函数联合使用的不足
条件表达式
在sql语句中使用if-then-else逻辑
使用两种方法:
-case表达式
-decode函数
-- 解锁scott 用户
alter user scott account unlock;
--解锁 scott 用户的密码(也可以用来重置密码)
alter user scott identified by tiger;
-- 创建表空间
create tablespace lixiaoyao datafile 'd:\lixiaoyao.dbf' size 100m autoextend on next 10m;
-- 删除表空间
drop tablespace lixiaoyao;
--创建用户
create user lixiaoyao identified by lixiaoyao default tablespace lixiaoyao;
-- 给用户授权
-- oracle 数据库中常用角色
connect --连接角色,基本角色
resource --开发者角色
dba --超级管理员角色
--给lixiaoyao用户授权dba
grant dba to lixiaoyao;
--修改表结构
--添加一列
alter table person add(gender number(1));
--修改列类型
alter table person modify gender char(1);
--修改列名称
alter table person rename column gender to sex;
--删除一列
alter table person drop column sex;
--隐式内连接和显式内连接:
隐式内连接:通过where条件进行条件
显式内连接:通过on进行条件 inner可以省略
select e1.ENAME,d1.LOC from SCOTT.emp e1 INNER JOIN SCOTT.dept d1 on e1.DEPTNO=d1.DEPTNO //显示内连接
select e1.ENAME,d1.LOC from SCOTT.emp e1,SCOTT.dept d1 where e1.DEPTNO=d1.DEPTNO //隐式内连接
--外连接
左外连接:left outer join 左表中所有的记录,如果右表没有对应记录,就显示为空
右外连接:right outer join 右表中的所有记录,如果左表没有对应记录,就显示为空
outer 关键字可以省略
Oracle中的外连接:(+)实际上是如果没有对应的记录就加上空值
select e1.ENAME,d1.LOC from SCOTT.emp e1 , SCOTT.dept d1 where e1.DEPTNO=d1.DEPTNO(+)
-- 查询语句创建表
create table emp2 as select * from SCOTT.emp;
select * from emp2;
-- 多表查询
笛卡尔积:实际上是两张表的乘积,但是在实际开发中没有太大意义
--分页查询
rownum 前面不能带别名(固定格式)
SELECT
*
FROM
( select ROWNUM rn, tt.* FROM ( SELECT * FROM SCOTT.emp ORDER BY SAL DESC ) tt WHERE ROWNUM < 11 )
WHERE
rn >5
空值不同于0,凡是空值参与的运算,结果都为空(null)
给别名加双引号 字段不会变
日期和字符串只能在单引号中出现
--Sequence
定义好sequence后,你就可以用currVal,nextVal取得值。
CurrVal:返回 sequence的当前值
NextVal:增加sequence的值,然后返回 增加后sequence值
得到值语句如下: SELECT Sequence名称.CurrVal FROM DUAL;
如得到上边创建Sequence值的语句为: select seqtest.currval from dual
--查询表里有多少张表
SELECT COUNT(1) FROM USER_TABLES;
-- 可以查询表使用的编码格式
select userenv('language') from dual;
-- 可以查询一个汉字占用的字节长度
select lengthb('好') from dual;
Oracle学习总结
于 2023-04-12 23:01:24 首次发布