Oracleb总结笔记(一)

一. 前置知识 (全部内容基于OracleXE版本)

1. 解锁账户

解锁HR用户：开始>运行 > cmd >
sqlplus /nolog > conn sys as sysdba > alter user hr identified by 密码 account unlock;
登录：C: sqlplus hr/123456
登录：SQL> conn hr > 123456
查表：select table_name from user_tables;

1. 设置用户回话特权和操作权限
   

   user test lacks create session privilege logon denied 缺少特权
   I. grant connect to hr;
   II. grant resource to hr;
   III. grant create session to hr;

二.SQL（Structured Query Language）结构化查询语言

1. 基本查询：
   I. 语法：select 列名 from 表名
   II. 详解：
   select：指定要查询的列。
   from ：指定要查询的目标表。
   III. 用例：
   1). 查询部分列：
   //查询员工表中所有员工的编号、名字、邮箱。

select employee_id,first_name,email 
from employees;

2). 查看所有列：

//查询员工表中所有员工的所有信息（所有列）。
select 所有列的列名 from employees;
select * from employees;
注：生产环境下，优先使用列名查询。*的方式需转换成全列名，效率低，可读性差。

3). 对列中的数据进行运算： + - * / （%是通配符）

select employee_id , first_name , salary*12 
from employees;

4). 列的别名： 列 as “别名”

//查询员工表中所有员工的编号、名字、年薪（列名均为中文）。
select employee_id as "编号" , first_name as "名字" , salary*12 as "年薪" 
from employees;

5). 拼接字符串： 字符串结果1 || 字符串结果2

//查询所有员工的工号和"姓名"（列名均为中文）。
select employee_id as "编号" ,  first_name || '.' || last_name as "姓名" 
from employees;
注：SQL中的字符串使用 '（单引号）表示；双引号在列名中使用，常用于起别名。

6). 查询结果去重：

//查询员工表中所有经理的ID。
select distinct manager_id 
from employees;

2 . 排序：对查询后的结果数据进行排序。

I. 语法：select 列名 from 表名 order by 排序列 （默认升序）
II. 详解：order by 排序列列名 [asc] [desc] （asc升序、desc降序，作用于前面字段）
III. 用例：

1). 依据单列排序

//查询员工的编号，名字，薪资。按照工资高低进行升序排序。
select employee_id , first_name , salary
from employees
order by salary desc;

2). 依据多列排序

//查询员工的编号，名字，薪资。按照工资高低进行降序排序（薪资相同时，按照编号进行升序排序）。
select employee_id , first_name , salary
from employees
order by salary desc , employee_id asc;

3 . 条件查询：筛选符合条件的查询结果。

I. 语法：select … from … where 布尔表达式
II. 详解：where 布尔表达式 //可执行条件筛选
III. 用例：
1). 等值判断：（=）

//查询薪资是11000的员工信息（编号、名字、薪资）
select employee_id , first_name , salary
from employees
where salary = 11000;
注：与Java不同，Oracle中的等值使用 = 。

2). 多条件判断：（and / or）

//查询薪资是11000并且提成是0.30的员工信息（编号、名字、薪资）
select employee_id , first_name , salary
from employees
where salary = 11000 and commission_pct = 0.30;

3). 不等值判断：（> < >= <= != <>）

//查询员工的薪资在6000~10000之间的员工信息（编号，名字，薪资）
select employee_id , first_name , salary
from employees
where salary >= 6000 and salary <= 10000; // where salary < 6000 or salary > 10000;

4). 区间判断：（between and）

//查询员工的薪资在6000~10000之间的员工信息（编号，名字，薪资）
select employee_id , first_name , salary
from employees
where salary between 6000 and 10000; //闭区间，包含区间边界的两个值

5). null值判断：（is null / is not null）

//查询没有提成的员工信息（编号，名字，薪资 , 提成）
select employee_id , first_name , salary , commission_pct
from employees
where commission_pct is null;

6). 枚举查询：（in(v1,v2,v3) ）

//查询部门编号为70、80、90的员工信息（编号，名字，薪资 , 部门编号）
select employee_id , first_name , salary , department_id
from employees
where department_id in(70,80,90);
注：in的查询效率较低，可通过多条件拼接。

7). 模糊查询：（ _：一个任意字符 / %：任意长度的任意字符 ）

//查询名字以"L"开头的员工信息（编号，名字，薪资 , 部门编号）
select employee_id , first_name , salary , department_id
from employees
where first_name like 'L%';

//查询名字以"L"开头并且长度为4的员工信息（编号，名字，薪资 , 部门编号）
select employee_id , first_name , salary , department_id
from employees
where first_name like 'L___';

4 . 时间查询：
I. 语法：select 系统时间 from 表名[虚表]

II. 详解：
sysdate：当前系统时间（年、月、日、时、分、秒、星期、上下午），常见格式。
systimestamp：当前系统时间（日、月、年、时、分、秒、毫秒），默认格式。（世界时+8即北京时间）
dual表：虚表（一行一列），维护SQL语法完整性。

III. 用例：
1). 系统时间（星期/毫秒）

//查询当前时间，包含星期
select sysdate from dual;

//查询当前时间，包含毫秒
select systimestamp from dual;

5 . 单行函数：
I. 语法：select 单行函数(列名) from 表名

II. 详解：
to_char(日期,’日期格式’)：将日期按照指定格式转换成字符串。
to_date(字符串,’日期格式’)：将字符串按照指定格式转换成日期。

III. 用例：
1). 日期转字符串

//将当前系统时间转换成（yyyy-mm-dd hh:mi:ss day）格式。
select to_char(sysdate,'yyyy-mm-dd hh:mi:ss')
from dual;

2). 字符串转日期

//将和日期兼容的字符串按照（yyyy-mm-dd hh24:mi:ss:ff day）转换成日期。
select to_date('20101231235959','yyyy-mm-dd hh24:mi:ss:ff')
from dual;

//查询2008.08.08是星期几？
select to_char( to_date('20080808','yyyy/mm/dd') , 'day' )
from dual;

补充mod(值1,值2) //取模   巩固单行函数
select mod(5,3) from dual;

注：单行函数作用于某列，查询后，一行原数据仍旧得到一行结果。

7 . 分组查询：有几组就有几行数据，聚合函数会为每组进行统计

I. 语法：select … from … where … group by 分组依据（列）

II. 详解：group by 分组依据（列） //在where之后生效

III. 用例：
//查询各个部门的总人数
思路：

1). 按照部门编号进行分组（分组依据department_id）。
2). 针对每个部门进行人数统计（count）。
SQL：
select department_id,count(employee_id)
from employees
group by department_id; //部门编号相同的多条数据，返回一行数据

//查询各个部门的平均工资
思路：

1). 按照部门编号进行分组（分组依据department_id）。
2). 针对每个部门进行平均工资统计（avg）。
SQL：
select department_id , avg(salary)
from employees
group by department_id

//查询各个部门、各个岗位的人数
思路：

1). 按照部门编号进行分组（分组依据 department_id）。
2). 按照岗位名称进行分组（分组依据 job_id）。
3). 针对每个部门中的各个岗位进行人数统计（count）。
SQL：
select department_id , job_id , count(employee_id)
from employees
group by department_id , job_id;

问题：查询各个部门id、总人数、first_name

select department_id , count(*) , first_name
from employees
group by department_id; //error

注：分组查询中，select显示的列只能是分组依据列，或者聚合函数列，不能出现其他列。

8 . 分组过滤：注意having的例子

I. 语法：select … from … where … group by … having 过滤规则

II. 详解：having 过滤规则（对分组后的数据进行过滤）

III. 用例：
//统计60、70、90号部门的最高工资
思路：
1). 确定分组依据（department_id）
2). 对分组后的数据，过滤出部门编号是60、70、90信息
3). max()函数处理

SQL：先where过滤，再group by分组    
select department_id,max(salary)
from employees
where department_id in (60,70,90)
group by department_id; 

结果正确，逻辑错误：from确定表--where过滤出60 70 90部门--group确定分组依据department_id --select查看部门编号和max函数。

SQL：先group by分组，再having过滤
select department_id , max(salary)
from employees
group by department_id
having department_id in (60,70,90)

结果正确，逻辑正确：from确定表-- group确定分组依据department_id --having过滤出60 70 90部门--select查看部门编号和max函数。

//查询平均工资超过7000的部门编号和人数（可附加：平均工资，并且不显示没有部门编号的）

select department_id , count(*) ,avg(salary) 
from employees
group by department_id
having avg(salary) >= 7000 and department_id is not null

注：where仅能判断表中存在的字段，having还可判断表中不存在的字段（聚合函数计算出来的数据）。
9 . SQL顺序：
I. 书写顺序：select … from … where … group by … having … order by [asc|desc]
II. SQL执行顺序：
1). from： 指定数据来源表
2). where： 对查询数据第一次过滤（现有列）
3). group by： 分组
4). having： 对分组后的数据进行第二次过滤
5). select： 获得各字段的值
6). order by： 排序