Oracle终端登陆
以dba的身份登陆
sqlplus “/as sysdba”
创建用户
create user user0611 identified by user0611;
赋予权限
grant resource,connect to briup;
查看当前用户
show user;
以user0611 登陆（conn username/password;）
conn user0611 /user0611 ;
如果是windows系统需要修改日期格式
alter session set nls_date_language=english;
alter session set nls_language=english;
查询当前用户下所有的表
select table_name from user_tables;
oracle基本查询
SELECT *|{[DISTINCT] column|expression [alias],...}
FROM table;
SELECT 标识 选择哪些列。
FROM 标识从哪个表中选择。
SQL 语言大小写不敏感。
SQL 可以写在一行或者多行
关键字不能被缩写也不能分行
各子句一般要分行写。
使用缩进提高语句的可读性。
以oracle自带的scott用户登陆
conn scott/tiger
选择全部列
select * from emp;
选择特定的列
select empno,ename,job,mgr,hiredate,sal,comm,deptno
from emp;
使用 DESCRIBE 命令，表示表结构
DESC[RIBE] tablename
desc dept
oracle中的算术运算符
* / + -
乘除的优先级高于加减
优先级相同时，按照从左至右运算
可以使用括号改变优先级
使用数学运算符
select empno,ename,sal,sal*12,comm,sal*12+comm
from emp;
使用括号
SELECT last_name, salary, 12*(sal+100)
from emp;
SQL中的null值
空值是无效的，未指定的，未知的或不可预知的值
空值不是空格或者0。
1. 包含null的表达式都为null
2. null != null
select empno,ename,sal,sal*12,comm,sal*12+nvl(comm,0)
from emp;
去重DISTINCT
select DISTINCT deptno,job from emp;
列的别名:
重命名一个列。
便于计算。
紧跟列名，也可以在列名和别名之间加入关键字‘AS’，别名使用双引号，以便在别名中包含空格或特殊的字符并区分大小写。
AS可以省略
SELECT ename AS name, job j
FROM emp;
连接符:
把列与列，列与字符连接在一起。
用 ‘||’表示。
可以用来‘合成’列。
SELECT ename||job AS "Employees"
FROM emp;
字符串
字符串可以是 SELECT 列表中的一个字符,数字,日期。
日期和字符只能在单引号中出现。
每当返回一行时，字符串被输出一次。
SELECT ename ||' is a '||job
AS "Employee Details"
FROM emp;
过滤和排序数据
在查询中过滤行,使用WHERE 子句，将不满足条件的行过滤掉
SELECT *|{[DISTINCT] column|expression [alias],...}
FROM table
[WHERE condition(s)];
WHERE 子句紧随 FROM 子句。
查询部门号是10的员工
select *
from emp
where deptno=10;
字符和日期要包含在单引号中。
字符大小写敏感，日期格式敏感。
默认的日期格式是 DD-MON-RR
select *
from emp
where ename ='KING';
select *
from emp
where hiredate='17-11月-81'
修改日期格式
alter session set NLS_DATE_FORMAT='yyyy-mm-dd';
select * from emp where hiredate='1981-11-17';
比较运算
= 等于 > 大于 >= 大于、等于 < 小于 <= 小于、等于
<> 不等于 (也可以是 !=)
SELECT ename, sal
FROM emp
WHERE sal <= 3000;
其它比较运算
BETWEEN ...AND... 在两个值之间 (包含边界)
IN(set) 等于值列表中的一个
LIKE 模糊查询
IS NULL 空值
select *
from emp
where sal between 1000 and 2000;
select *
from emp
where deptno in (10,20);
如果集合中 含有null，不能使用not in，但可以使用in
select *
from emp
where deptno not in (10,20,null)
select *
from emp
where ename like 'S%';
回避特殊符号的：使用转义符。例如：将[%]转为[/%]、[_]转为[_]，然后再加上[ESCAPE ‘/’] 即可
查询名字中含有下划线的员工
select *
from emp
where ename like '%/_%' escape '/'
逻辑运算
AND 逻辑并 要求并的关系为真
OR 逻辑或 要求或关系为真
NOT 逻辑否
SELECT ename, sal
FROM emp
WHERE sal >= 10000
AND ename LIKE '%MAN%';
SELECT ename, sal
FROM emp
WHERE sal >= 10000
OR ename LIKE '%MAN%';
ORDER BY子句
使用 ORDER BY 子句排序
ASC（ascend）: 升序
DESC（descend）: 降序
ORDER BY 子句在SELECT语句的结尾。
select *
from emp
order by sal;
select ename,sal,sal*12 年薪
from emp
order by 3 desc;
select *
from emp
order by deptno,sal;
单行函数:
操作数据对象
接受参数返回一个结果
只对一行进行变换
每行返回一个结果
可以转换数据类型
可以嵌套
参数可以是一列或一个值
select lower('Hello WOrld') 转小写,upper('Hello WOrld') 转大写,
initcap('hello world') 首字母大写
from dual;
--substr(a,b)从a中 第b位开始取
select substr('Hello World',3) from dual;
--substr(a,b,c) 从a中 第b位开始取,取c位
select substr('Hello World',3,4) from dual;
--length 字符数 lengthb 字节数
select length('Hello World') 字符,
lengthb('Hello World') 字节
from dual;
--一个中文字符占两个字节
select length('中国') 字符,
lengthb('中国') 字节
from dual
--instr(a,b) 在a中 查找b
select instr('Hellow World','ll') from dual;
--lpad 左填充 rpad右填充
select lpad('abcd',10,'*') 左,rpad('abcd',10,'*') 右
from dual;
--trim 去掉前后指定的字符
select trim('H' from 'Hello WorldH') from dual;
--replce 替换
select replace('Hello World','l','*') from dual;
--数值函数
select ROUND(45.926, 2) 一,ROUND(45.926, 1) 二,ROUND(45.926, 0) 三,
ROUND(45.926, -1) 四, ROUND(45.926, -2) 五
from dual;
select TRUNC(45.926, 2) 一,TRUNC(45.926, 1) 二,TRUNC(45.926, 0) 三,
TRUNC(45.926, -1) 四, TRUNC(45.926, -2) 五
from dual;
--日期函数
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
--昨天 今天 明天
select (sysdate-1) 昨天,sysdate 今天,(sysdate+1) 明天
from dual;
--30月后
select add_months(sysdate,30) from dual;
select LAST_DAY(sysdate) from dual;
select next_day(sysdate,'星期一') from dual;
select ROUND(SYSDATE,'MONTH'), ROUND(SYSDATE,'YEAR')
from dual;
--查询员工的薪水： 货币符号 两位小数 千位符
select to_char(sal,'L9,999.99') from emp;
--nvl2(a,b,c) 当a=null时 返回c ，否则返回b
select sal*12+nvl2(comm,comm,0) from emp;
--NULLIF(a,b) 当a=b时 返回null；否则返回a
select nullif('abcd','abcd') from dual;
select nullif('abcd','abcde') from dual;
--COALESCE : 从左到右 找到第一个不为null的值
select comm,sal,COALESCE(comm,sal) from emp;
--IF-THEN-ELSE 逻辑
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;
分组函数
分组函数作用于一组数据，并对一组数据返回一个值。组函数忽略空值
语法：
SELECT [column,] group_function(column), ...
FROM table
[WHERE condition]
[GROUP BY column]
[ORDER BY column];
AVG（平均值）和 SUM （合计）函数
COUNT（计数）函数
MIN（最小值） 和 MAX（最大值）函数
select deptno,avg(sal)
from emp
group by deptno;
select sum(sal) from emp;
select count(*) from emp;
select min(sal),max(sal) from emp;
可以使用GROUP BY 子句将表中的数据分成若干组
在SELECT 列表中所有未包含在组函数中的列都应该包含
在 GROUP BY 子句中。
包含在 GROUP BY 子句中的列不必包含在SELECT 列表中
不能在 WHERE 子句中使用组函数（注意）。
可以在 HAVING 子句中使用组函数。
使用 HAVING 过滤分组:
1.行已经被分组。
2.使用了组函数。
3.满足HAVING 子句中条件的分组将被显示。
select deptno,avg(sal)
from emp
group by deptno;
select deptno,avg(sal)
from emp
group by deptno
having avg(sal)>2000;
多表查询
-- 等值连接
select e.empno,e.ename,e.sal,d.dname
from emp e,dept d
where e.deptno=d.deptno;
/*外连接
希望：在最后的结果中 包含某些不成立的记录
左外连接: where e.deptno=d.deptno不成立的时候，等号左边代表的表任然被包含
写法: where e.deptno=d.deptno(+)
右外连接: where e.deptno=d.deptno不成立的时候，等号右边代表的表任然被包含
写法：where e.deptno(+)=d.deptno
*/
select d.deptno 部门号, d.dname 部门名称, count(e.empno) 人数
from emp e,dept d
where e.deptno(+)=d.deptno
group by d.deptno,d.dname;
--通过表的别名，将同一张表视为多张表，自连接
select e.ename||'的老板是'||b.ename
from emp e,emp b
where e.mgr=b.empno;
原创文章，作者：Maggie-Hunter，如若转载，请注明出处：https://blog.ytso.com/4244.html