尚学堂马士兵Oracle学习笔记之二：select子句和常用SQL函数

1. In语句：用于塞选某一个值

例子1：把薪水是800，1250， 1500， 2000 的雇员信息取出来

Select ename, sal from emp where sal in(800,1250, 1500, 2000);

也可以用于字符串操作

例子2：把名字为SMITH,ALLEN, KING 的雇员取出来

Select ename, sal from emp where ename in(‘SMITH’,‘ALLEN’, ‘KING’);

2. 日期处理

Oracle默认的日期格式为：DD-MON-RR

例子1：查询在81年2月20号以后入职的员工

Select ename, hiredate from emp wherehiredate> ’20-2月-81’;

Select ename, hiredate from emp wherehiredate> ’20-2月-1981’;

如果想用自己定义的日期格式，可以使用to_char or to_date函数，

3. AND， OR，NOT

例子1：查询部门标号为10 并且薪水>1000 的员工

Select ename, deptno, sal from empwheredeptno = 10 and sal > 1000;

例子2：查询部门编号为10或者工作岗位为CLERK 的员工

Select ename, deptno, job from emp wheredeptno = 10 or job = ‘CLEARK’;

例子3：查询薪水没有位于800,1500, 2000 之中的员工

Select ename, sal from emp where sal notin(800, 1500, 2000);

7．模糊查询

使用like关键字，和通配符% à 表示0个或多个字符，_ 表示1个字符

例子1：查询名字中含有ALL的人员

Select ename from emp where ename like‘%ALL%’;

例子2：查询第二字母中含有 A的雇员

Select ename from emp where ename like ‘_A%’;

例子3：查询名字中含有 % 等通配符的数据时，使用转义字符 \

Escape à 自定义转移字符, 系统默认的转义字符是 ‘\’

Select ename from emp where ename like‘%\%%’escape‘\’;

 

07_order_by.avi

使用它order by 对数据进行排序

例子1：按照名字的升序进行排序

Select ename, salfrom emp order by ename;

Select ename, salfrom emp order by ename asc;à用asc关键字指出按升序排列

例子2：按照雇员编号的升序排序

Select ename ,deptno from emp order bydeptno;

Select ename, deptnofrom emp order by deptnoasc;

例子3：按照名字降序排列

Select ename, deptnofrom emp order by enamedesc;

例子4：按照部门编号降序排列

Select ename, deptnofrom emp order by deptnodesc;

 

08_sql_function_1.avi

09_sql_function_2.avi

常用SQL函数

1.Lower()函数à将字符串全部转换成小写

例子1：将雇员中名字含有’A’或’a’的人员全部显示出来

Select ename from emp where ename like‘%A%’orename like‘%a%’;

Select ename from emp where lower(ename)like‘%a%’;

2.Upper()函数将字符串全部转换为大写

例子2：将雇员中名字含有’A’或’a’的人员全部显示出来

Select ename from emp where upper(ename)like‘%A%’;

3.substr()函数

例子1：从第一字符开始共截3个子串

Select substr(‘Hello’, 1, 3) from dual; à Hel

Substr à 可以省略第三个参数

例子2：从第二个字符开始，截取到整个字符串结束

Select sbustr(ename, 2) from emp;

4.Chr()函数

例子1：求一个与某个ASCII码值对应的字符

Select chr(65) from dual;

1. Ascii()函数

例子：求一个字符的ASCII码值

Select ascii(‘A’) from dual;

2. round()函数à 对参数值进行四舍五入的操作

例子1：对23.652进行四舍五入操作

Select round(23.652)from dual; à 24

可以指定四舍五入到小数点后几位

例子2：对23.652四舍五入到小数点后2位

Select round(23.652,2) from dual; à 23.65

可以用负数指定小数点前面几位

例子3：对23.652四舍五入到小数点前1位

Select round(23.652,-1) from dual; à 20

*7.to_char()

用于将数字或日期转换成特定的字符串，

To_char()有两个参数：

第一个参数：需要进行转换的日期或数字

第二个参数：特定的转换格式，对于数字有一下几个格式可以指定：

9 代表数字，若果该位没有数字则不进行显示，但对于小数点后面的部分仍会强制显示 0 代表一位数字，如果该位没有数字则强制显示0 $ 显示美元符号 L 显示本地货币符号 . 显示小数点 , 显示千分位符号

例子1：selectto_char(sal, ‘$99,999.9999’) salary from emp where ename = ‘ALLEN’;à $1,600.0000

例子2：selectto_char(sal, ‘$00,000.0000’) salary from emp where ename = ‘ALLEN’;à $01,600.0000

对于日期：to_char()可以指定为下面的常用格式：

格式控制符含义 YYYY、YY -----------------------------à代表4位，2位数字的年份 MM  -----------------------------à用数字表示的月份 MON -----------------------------à月份的缩写对中文月份来说就是全称 DD -----------------------------à数字表示的日 DY -----------------------------à星期的缩写，对中文的星期来说就是全称 HH24、HH12 ---------------------------à12小时或者24小时进制下的时间 MI  -----------------------------à分钟数 SS -----------------------------à秒数

有了这些格式，就可以把日期自定义为任何格式

例子1：selectto_char(sysdate, ‘YYYY-MM-DD-HH24:MI:SS’) from dual; à2008-04-04

*8.to_date()函数

将特定的字符串转换成日期格式，这个函数有两个参数

第一参数：自定义的日期字符串

第二参数：指定这个字符串的格式

例子1：将1981年3月2日中午以后入职的雇员信息取出：

Select * from empwhere hiredate >to_date(‘1981-03-02 12:00:00’, ‘YYYY-MM-DD HH12:MI:SS’);

*9.to_number()函数

讲指定的字符串转换成数字格式，这个函数有两个参数

第一参数：自定义的数字字符串

第二参数：指定这个字符串的格式

例子1：求薪水大于1200的员工信息

Select * from empwhere sal >to_number(‘$1,200.00’,‘$9,999.99’);

*10.nvl()函数

用来处理空值，这个函数有两个参数：

第一参数：字段名或表达式，如果这个参数值为null，就返回第二参数值，否则返回第一参数值

例子：求每个员工每年的年收入(12个月的薪水+津贴)

因为comm的值为null，想要得到正确的结果，必须讲null值转换为0

Select ename,sal*12+nvl(comm, 0) from emp;

*11.组函数包括5个函数：

(1).avg():求平均值

(2).max()：求最大值

(3).min()：求最小值

(4).sum()：求总和

(5).count()：求记录的数量

 

例子1：求薪水的总和、平均值、最大值和最小值

Select sum(sal),avg(sal), max(sal), min(sal)from emp;

例子2：求emp表中记录的数量

Select count(*) fromemp;

Count()可以对单独字段使用，得到的是所有非空记录的数量

例子3：求comm字段中所有非空记录的数量

Select count(comm)from emp;

Count()可以和distinct一起使用，得到所有唯一值记录的数量

例子4：求emp表中deptno唯一的数量

Select count(distinct,deptno) from emp;

注意：函数名不是在所有数据库中通用

 

10_group_function.avi

11_group_by.avi

对表中的数据进行分组

例子1：计算每个部门的平均工资

首先将现有数据按照部门进行分组，然后再计算每个组员工的平均薪水。

Select deptno,avg(sal) from emp group bydeptno;

例子2：计算每个部门的最大工资

Select deptno,max(sal) from emp group bydeptno;

例子3：按照部门，和职位的最大薪水进行分组

Select deptno, job,max(sal) from emp group bydeptno, job;

使用groupby的规律：出现在select列表中的字段，如果没有出现在组函数中，则必须出现在group by子句中

典型错误：selectename, deptno, max(sal) fromemp group by deptno;

例子4：求出每一个部门里赚钱最多的那个人的名字

Select ename,deptno, sal from emp where salin (

Select sal from (

Select deptno,max(sal) sal from emp group bydeptno

)

)

例子4：选出所用部门里工资最高的人的名字

Select ename, salfrom emp where sal =

(

Select max(sal) from emp

);

 

12_having.avi

使用Having对分组进行限制

如果我们要从分组数据中把某些特定的剔除去的时候，使用Having关键字

例子1：将平均薪水大于1000的组的平均薪水从emp这张表中选出来

Select avg(sal),deptno group by deptno havingavg(sal) > 1000;

例子2：求薪水大于1200的雇员，按照部门进行分组，而且这些分分组后组内平均薪水必须大于1500，要查询分组的平均工资

Select avg(sal) fromemp where sal > 1200group by deptno having avg(sal) > 1500

Order by avg(sal);