SQL语句-Oracle01

查询表的所需列：
select empno,ename,job from emp;

查询时给列起别名：
select empno 编号,ename 姓名, job 工作 from emp;

消除查询的重复列：(若查询多列则必须保证所查询的所有列均有重复值)
select {distinct} *|具体的列 别名
from 表名称;

控制查询出来的格式：
oracle中的字符串连接操作，用“||”表示。
eg：
要查询的格式为：编号是：7369的雇员，姓名是：SMITH，工作时：CLERK
select '编号是：'||empno||'的雇员,姓名是：'||ename||',工作是：'||job
from emp;

在查询中使用四则运算：
select ename,sal*12 income     //income为起的别名
from emp;

-------------
限定查询where

select {distinct} *|具体的列 别名
from 表名称
{where 条件(s)};

eg:
查询出大于1500的所有雇员信息
select * from emp where sal>1500;

查询每月可以得到奖金的雇员信息
 字段中有内容，则表示此内容不为空(null),如果存在内容，则会显示具体的值。
 不为空的表示：字段is not null
select * from emp where comm is null null;

查询没有奖金的额雇员：
select * from emp where comm is null;

eg:
查询出工资大于1500，可领奖金的雇员：
select * from emp where sal>1500 and comm is not null;
查询出工资大于1500，或者可领奖金的雇员：
select * from emp where sal>1500 or comm is not null;
查询出工资不大于1500，同时不可以领取奖金的雇员信息：
select * from emp where not(sal>1500 and comm is not null);//扩号表示一组
查询出几倍工资大于1500，但小于3000的全部雇员信息：
select * from emp where sal>1500 and sal<3000;

指定范围查询的过滤语句：between...and...
字段 between 最小值 and 最大值
eg:
between...and...操作等价：sal>=1500 and sal<=3000,包含了等于的功能

查询出在1981年1月1日~1981年12月31日之间雇佣的雇员信息
日期表示的时候要加“'”
select * from emp
where hiredate between '1-1 月 -81'and'31-12 月-81';

eg:
查询出姓名是smith的雇员信息
select * from emp where ename='smith';
查询出雇员编号是7369、7499、7521的雇员的具体信息
select * from emp
where empno=7369 or empno=7499 or empno=7521;

或者：
select * from emp
where empno in(7369,7499,7521);//in表示在此范围内

查询出雇员编号不是7369、7499、7521的雇员的具体信息
select * from emp
where empno not in(7369,7499,7521);
查询出姓名是smith、allen、king的雇员的信息：
select * from emp
where ename in ('smith','allen','king');

select * from emp
where ename in('smith','allen','king','#$#$$##$');//在指定查询范围中指定额外的内容，不影响程序运行

----------
模糊查找like
通配符：
"%"  可以匹配任意长度的内容
"_"  可以匹配一个长度的内容

eg：
查询出所有雇员姓名中第二个字母包含“M”的雇员信息
select * from emp
where ename like '_M%';

查询出在任意位置上出现字母M
select * from emp
where ename like '%M%';

查询出在1981年雇佣的雇员信息
select * from emp
where hiredate like '�%';

查询出工资中包含6的雇员信息
select * from emp
where sal like '%6%';

不等于"<>"     "!="
查询出编号不是7369的雇员信息
select * from emp
where empno <>7369;

排序 order by
select{distinct}* | 具体的列 别名
from 表名称
{where 条件(s)}
{order by 排序的字段1，排序的字段2，ASC|DESC};//asc升序desc降序,默认为升序

eg:
select * from emp order by sal asc;

select * from emp
where deptno=10 order by sal desc,hiredate asc;//工资由高到低，如果工资相等，则日期早到晚排序

=============================

单行函数（重点）

每个数据库之间唯一不同的最大区别点在于与函数的支持上，使用函数可以完成一系列的操作功能：
function_name(column|expression,[arg1,arg2,...])

function_name:函数名称
column：数据库列名
expression：字符串或计算表达式
arg1，arg2：在函数中使用参数

单行函数分类：
    字符函数：接收字符输入并且返回字符或数值
    数值函数：接受数值输入并返回数值
    日期函数：对日期型数据进行操作
    转换函数：从一种数据类型转换为另一种数据类型
    通用函数：NVL函数、DECODE函数

 

---------------------------------
字符函数

select upper('smith') from dual;//将小写变为大写

select * from emp where ename=upper('Smith');//

select lower('HELLO WORLD') from dual;//将一个字符串变为小写字母表示。

select initcap('HELLO WORLD') from dual;//将单词的第一个字母大写

select initcap(ename) from emp;//将雇员表中的雇员姓名变为开头字母大写

 

字符串除了可以使用“||”连接之外，还可以使用concat()函数进行连接操作。

select concat('hello','world') from dual;//hello world

字符串截取：substr()
字符串长度：length()
内容替换：replace()

select substr('hello',1,3) 截取字符串,   //hel
     length('hello') 字符串长度,         //5
     replace('hello','1','x') 字符串替换 //hexxo
from dual;

注：Oracle中substr()函数的截取点从0还是从1开始效果是一样的，
因为Oracle比较智能。

eg:
显示所有雇员的姓名及姓名的后三个字符：
（雇员姓名的长度不确定，所以只能求出整个长度再减去2，这样进行截取操作）

select ename,substr(ename,length(ename)-2) from emp;

select ename,substr(ename,-3,3) from emp;
//substr可以倒着截取，第一个-3表示从字符串最后一个字符开始倒数三个，
//第二个3表示获取从-3开始后的3个字符。
-------------------------------------------------
数值函数

四舍五入：round()
截断小数位：trunc()
取舍(取模)：mod

执行四舍五入操作：
select round(789.536) from dual;//790
select round(789.536,2) from dual;//789.54 四舍五入保留两位小数
select round(789.536,-2) from dual;//800 直接对整数进行四舍五入的进位。

select trunc(789.536) from dual;//789  trunc()操作中，不会保留任何的小数，也不会四舍五入。
select trunc(789.536,2) from dual;//789.53  保留两位小数
select trunc(789.536,-2) from dual;//700  使用负数表示位数
select mod(10,3) from dual;//1  使用mod函数进行取余的操作

-------------------
日期函数

Oracle中提供了加减日期：
日期 - 数字 = 日期
日期 + 数字 = 日期
日期 - 日期 = 数字(天数)

eg:
显示10部门雇员进入公司的星期数
1、先求出当前日期：
select sysdate from dual;
2、再求出星期数：当前日期-雇佣日期=天数/7=星期数
select empno,ename,round((sysdate-hiredate)/7) from emp;

MONTH_BETWEEN():求出给定日期范围的月数
ADD_MONTHS():在指定日期上加上指定的月数，求出之后的日期
NEXT_DAY():下一个的今天是哪一个日期
LAST_DAY():求出给定日期的最后一天日期

eg:
select empno,ename,months_between(sysdate,hiredate) from emp;
select add_months(sysdate,4) from dual;
select next_day(sysdate,'星期一') from dual;
select last_day(sysdate) from dual;

 

转换函数：
TO_CHAR():转换成字符串
TO_NUMBER():转换成数字
TO_DATE():转换成日期

select empno,ename,hiredate from emp;

年：y，四位数字，yyyy
月：m，二位数字，mm
日：d，二位数字，dd

Oracle中默认的日期格式：19-04-19
中国喜欢的格式：1987-04-19
select empno,ename,TO_CHAR(hiredate,'yyyy-mm-dd') from emp;//可使用fm去掉前导0

$:表示美元
L:表示Local的缩写，以本地的语言进行金额的显示

select empno,ename,TO_CHAR(sal,'$99,9999')
from emp;

select TO_NUMBER('123')+TO_NUMBER('123') from dual;//把子字符串123转换成数字123然后相加
select TO_DATE('2009-02-16','yyyy-mm-dd')
from dual;

--------
通用函数
select empno,ename,(sal+comm)*12 from emp;//求出每个雇员的年薪

NVL()函数，可将NULL值变为指定的内容
select empno,ename,NVL(comm,0),
(sal+NVL(comm,0))*12 income from emp;//将NULL转换成0
----------------
面试常见DECODE()函数if...else if...else

DECODE(col/expresstion,search1,result1[,search2,result2,...][,default])
说明：
col/expression:为列名或表达式
search1、search2···searchi：为用于比较的条件
result1/result2···resulti为返回值
如果col/expression和searchi相比较，结果相同的话，则返回resulti，如果
没有resulti，如果没有与col/expression相匹配的结果，则返回默认值default。

eg:
select DECODE(1,1,'内容时1',2,'内容时2',3,'内容是3')
from dual;

-------
clerk:业务员
salesman:销售人员
manager:管理员
analyst:分析员
president:总裁

select empno 雇员编号,ename 雇员姓名 ,hiredate 雇佣日期 ,
 DECODE(job,'clerk','业务员','salesman',
  '销售人员','manager','经理',
  'analyst','分析员','president','总裁') 职位
from emp;

--------------------
总结：
 Oracle主要用户：

 超级管理员：sys/change_on_install
 普通管理员：system/manager
 普通用户：scott/tiger
 
 sqlplusw的一些常用命令：

 设置行显示数量：set linesize 长度
 设置页显示数量：set pagesize 长度
 ed及指令
 连接：conn 用户名 / 密码 as sysdba

 sql基础语法的格式
 单行函数，Decode()函数最重要。