文章目录


1. 中文乱码问题解决


1.查看服务器端编码
select userenv(‘language’) from dual;
我实际查到的结果为:AMERICAN_AMERICA.ZHS16GBK
2.执行语句 select * from V$NLS_PARAMETERS
查看第一行中PARAMETER项中为NLS_LANGUAGE 对应的VALUE项中是否和第一步得到的值一样。
如果不是，需要设置环境变量.
否则PLSQL客户端使用的编码和服务器端编码不一致,插入中文时就会出现乱码.
3.设置环境变量
计算机->属性->高级系统设置->环境变量->新建
设置变量名:NLS_LANG,变量值:第1步查到的值， 我的是 AMERICAN_AMERICA.ZHS16GBK
4.重新启动PLSQL,插入数据正常


2.基本查询

2-1 SQL简介

SQL : 结构化查询语言


SQL的分类以及每类常见的操作符
DDL : 数据定义语言 create alter drop truncate
DML : 数据操纵语言 insert update delete
DCL : 数据控制语言 安全 授权 grant revoke
DQL : 数据查询语言 select from子句 where子句


2-2 查询语句的结构


​​select [列名] [*] from 表名 [where 条件] [group by 分组条件] [having 过滤] [order by 排序]​​ select 1+1; --在Oracle等于报错 ,在MYSQL中输出结果是2


虚表、伪表


dual : oracle中的虚表 ,伪表, 主要是用来补齐语法结构
例：
 select 1+1 from dual;


别名查询


使用as 关键字, 可以省略
别名中不能有特殊字符或者关键字, 如果有就加双引号


select ename 姓名, sal 工资 from emp;
select ename "姓       名", sal 工资 from emp;

去除重复数据 distinct

--单列去除重复
select distinct job from emp;

--多列去除重复的
select distinct job,deptno from emp;

查询员工年薪 = 月薪×12


select sal*12 from emp;


查询员工年薪+奖金


select sal12 + comm from emp;
注意: null值 , 代表不确定的 不可预知的内容 , 不可以做四则运算
nvl 函数 : 如果参数1为null 就返回参数2
select sal12 + nvl(comm,0) from emp;


字符串拼接


java : + 号拼接
Oracle 特有的连接符: || 拼接

在Oracle 中 ,双引号主要是别名的时候使用, 单引号是使用的值, 是字符

concat(str1,str2) 函数, 在mysql和Oracle中都有


--使用拼接符
select '姓名:' || ename from emp;

--使用函数拼接
select concat('姓名:',ename) from emp;

条件查询


关系运算符: > >= = < <= != <>
逻辑运算符: and or not
其它运算符:
  like 模糊查询
  in(set) 在某个集合内
  between…and… 在某个区间内
  is null 判断为空
  is not null 判断不为空


模糊查询


如果有特殊字符, 需要使用escape转义
例：
–查询员工姓名中,包含%的员工信息
select * from emp where ename like ‘%%%’ escape ‘’;


排序查询


排序注意null问题 : nulls first | last
同时排列多列, 用逗号隔开
例如：
–查询员工信息,按照奖金由高到低排序
select * from emp order by comm desc nulls last;


3.函数

3-1 单行函数


单行函数: 对某一行中的某个值进行处理


数值函数

select ceil(45.926) from dual;  --46
select floor(45.926) from dual; --45
--四舍五入
select round(45.926,2) from dual; --45.93
select round(45.926,1) from dual; -- 45.9
select round(45.926,0) from dual; --46
select round(45.926,-1) from dual; --50
select round(45.926,-2) from dual; --0
select round(65.926,-2) from dual; --100

--截断
select trunc(45.926,2) from dual; --45.92
select trunc(45.926,1) from dual; -- 45.9
select trunc(45.926,0) from dual; --45
select trunc(45.926,-1) from dual; --40
select trunc(45.926,-2) from dual; --0
select trunc(65.926,-2) from dual; --0

--求余
select mod(9,3) from dual; --0
select mod(9,4) from dual; --1

字符函数

-- substr(str1,起始索引,长度) 
--注意: 起始索引不管写 0 还是 1 都是从第一个字符开始截取
select substr('abcdefg',0,3) from dual; --abc
select substr('abcdefg',1,3) from dual; --abc
select substr('abcdefg',2,3) from dual; --bcd
--获取字符串长度 24 28
select length('abcdefg') from dual;
--去除字符左右两边的空格
select trim('  hello  ') from dual;
--替换字符串
Select replace('hello','l','a') from dual;

日期函数

--查询今天的日期
select sysdate from dual;
--查询3个月后的今天的日期
select add_months(sysdate,3) from dual;
--查询3天后的日期
select sysdate + 3 from dual;


--查询员工入职的天数
select sysdate - hiredate from  emp;

select ceil(sysdate - hiredate) from  emp;

--查询员工入职的周数
select (sysdate - hiredate)/7 from emp;

--查询员工入职的月数
select months_between(sysdate,hiredate) from emp;

--查询员工入职的年份
select months_between(sysdate,hiredate)/12 from emp;

转换函数


数值转字符 字符转数值 日期


--字符转数值 to_number(str) 鸡肋
select 100+'10' from dual;  --110  默认已经帮我们转换
select 100 + to_number('10') from dual; --110

--数值转字符
select to_char(sal,'$9,999.99') from emp;

select to_char(sal,'L9,999.99') from emp;
--日期转字符 to_char()  
select to_char(sysdate,'yyyy-mm-dd hh:mi:ss') from dual;
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
--只想要年
select to_char(sysdate,'yyyy') from dual;  --2017

--只想要日
select to_char(sysdate,'d') from dual; --2  代表一个星期中第几天
select to_char(sysdate,'dd') from dual;  --10  代表一个月中的第几天
select to_char(sysdate,'ddd') from dual; --100 代表一年中的第几天


select to_char(sysdate,'day') from dual;  --monday
select to_char(sysdate,'dy') from dual;   --mon  星期的简写


--字符转日期
select to_date('2017-04-10','yyyy-mm-dd') from dual;


例子：
--查询1981年 -- 1985年入职的员工信息
select * from emp where hiredate between to_date('1981','yyyy') and to_date('1985','yyyy');

通用函数


nvl(参数1,参数2) 如果参数1 = null 就返回参数2
nvl2(参数1,参数2,参数3) 如果参数1 = null ,就返回参数3, 否则返回参数2
nullif(参数1,参数2) 如果参数1 = 参数2 那么就返回 null , 否则返回参数1
coalesce: 返回第一个不为null的值


select nvl2(null,5,6) from dual; --6;

select nvl2(1,5,6) from dual; --5;

select nullif(5,6) from dual; --5
select nullif(6,6) from dual; --null

select coalesce(null,null,3,5,6) from dual;  --3

3-2 多行函数


对某一列的所有行进行处理
max() min count sum avg
1.直接忽略空值


--统计员工工资总和
select sum(sal) from emp;

--统计员工奖金总和  2200
select sum(comm) from emp;

--统计员工人数 14
select count(1) from emp;

--统计员工的平均奖金  550  错误    2200/14 =
select avg(comm) from emp;


--统计员工的平均奖金 157.
select sum(comm)/count(1) from emp;
select ceil(sum(comm)/count(1)) from emp;

4.条件表达式


格式
case 字段名
 when 值1 then 值
 when 值2 then 值
else
 默认值
end
这种写法oracle和mysql中都可以使用


select case ename 
  when 'SMITH' then '小二'
  when 'ALLEN' then '诸葛'
  else
       '无名氏'
  end "姓名"
from emp;


oracle特有写法
decode(字段,if1，then1，if2，then2，else1);


select decode(ename,'SMITH','啊啊啊啊','ALLEN','诸葛','无名') from emp;

5.练习题

--使用HR用户登录 默认密码是HR
select * from employees;

--1. 查询工资大于12000的员工姓名和工资
select concat(first_name,last_name),salary from employees where salary > 12000;

--2. 查询员工号为176的员工的姓名和部门号
select  concat(first_name,last_name),department_id from employees where employee_id = 176;

--3. 选择工资不在5000到12000的员工的姓名和工资
select concat(first_name,last_name),salary from employees where salary not between 5000 and 12000;

--4. 选择雇用时间在1998-02-01到1998-05-01之间的员工姓名，job_id和雇用时间
select concat(first_name,last_name),job_id,hire_date from employees where hire_date between to_date('1998-02-01','yy-mm-dd') and to_date('1998-05-01','yy-mm-dd');

--5. 选择在20或50号部门工作的员工姓名和部门号
select concat(first_name,last_name),department_id from employees where  department_id in (20,50);

--6. 选择在1994年雇用的员工的姓名和雇用时间
select concat(first_name,last_name),hire_date from employees where to_char(hire_date,'yyyy') = '1994';

--7. 选择公司中没有管理者的员工姓名及job_id
select concat(first_name,last_name),job_id from employees where manager_id is null;

--8. 选择公司中有奖金的员工姓名，工资和奖金级别
select concat(first_name,last_name),salary,commission_pct from employees where commission_pct is not null;

--9. 选择员工姓名的第三个字母是a的员工姓名
select concat(first_name,last_name) from employees where concat(first_name,last_name) like '__a%';

--10. 选择姓名中有字母a和e的员工姓名
select concat(first_name,last_name) from employees where concat(first_name,last_name) like '%a%e%' or concat(first_name,last_name) like '%e%a%';

--11. 显示系统时间
select sysdate from dual;

--12. 查询员工号，姓名，工资，以及工资提高百分之20%后的结果（new salary）
select concat(first_name,last_name),employee_id,salary,salary * 0.2 as "new salary" from employees;

--13. 将员工的姓名按首字母排序，并写出姓名的长度（length）
select  concat(first_name,last_name),length(concat(first_name,last_name)) as "length" from employees order by concat(first_name,last_name);

--14. 查询各员工的姓名，并显示出各员工在公司工作的月份数
select  concat(first_name,last_name),months_between(sysdate,hire_date) from employees;

--15. 查询员工的姓名，以及在公司工作的月份数（worked_month），并按月份数降序排列
select  concat(first_name,last_name),months_between(sysdate,hire_date) as "worked_month" from employees order by months_between(sysdate,hire_date) desc;