一、in的用法。
select ename,deptno from emp where sal in(800,1250)
in 里面可以是一个结果集，是一个sql语句.
当然也可用not in
二、操作符优先级，* / + - 用括号可以改变这种优先级，
在sql中 .... and (d_isactve is null or d_isactive ==0)
这个在项目有遇到过，一个字段需要两种不同的条件判断，
用可以加个括号。
三、like
%表示零个或者多个字符，_表示一个字符，均可以表示汉字和字母。
查询first的第三个字母是a的员工
select * from emp where first like '__a%';
使用ESCAPE 标识符来查找带特殊符号的字符串
如果需要模糊查询的字符串中包含了作为通配符的”%”和”_”，
在这种情况下需要使用escape标识符来说明哪些是字符串的字符，哪些是通配符号
select * from feng where name like '%\%f%' escape '\' ;
select * from feng where name like '%\%f\_%' escape '\';
--escape ‘\‘ 表示‘’\’后边的是字符串中的内容, ‘’\’ 可以用其它的字符来代替
四、空值，一定是 is null 或者 is not NULL.绝对没有==null这种东西。
五、逻辑运算符：and or not.用括号可以改变其优先级，在项目中遇到过。
not ->and -->or 运算顺序。用括号可以改变其优先级，在项目中遇到过。这点十分重要，
一定要谨记。
select * from emp where dep = 't' or dep = 'q' and salary >= 1500;
一定要明白这个结果是什么，先运算and ，再运算Or。
数学运算符和逻辑运算符，而且括号都可以改变运算顺序，十分好的。
六、著名的 order by
1、它一定是出现select语句的最后，有什么其它条件肯定要插在它的前面。
2、使用ORDER BY 子句根据某个字段所得结果记录排序ASC: 升序,缺省DESC: 降序
3、order by 后面所根据的字段，可以使用列的别名排序。
4、对多字段进行的方式，十分重要，十分好。
在进行排序的时候，也可以按照多个字段进行排序，
但是多字段排序要注意，必须对每个字段设置排序方式，
也就是说，不管是ASC还是DESC，都只是针对单个字段的设置，
所以在多字段排序中，需要设置每个字段的排序方式，
而且每个字段的排序方式不同也是可以的。
SELECT last_name, department_id, salary
FROM employees
ORDER BY department_id desc, salary asc;
首先会根据部门ID把部门结果按照部门的降序排列，
然后在每一个相同的ID部门里面，其工资又是按照升序
排列的，十分好的，十分有效。
六、函数。
当你寻找不到答案的时间，就去看官方的文档 。
常用的分组函数(各个数据库通用)
AVG ([DISTINCT|ALL]n)
COUNT ({ *|[DISTINCT|ALL]expr})--返回的是不重复的，这个字段非空的集合。
MAX ([DISTINCT|ALL]expr)
MIN ([DISTINCT|ALL]expr)
SUM ([DISTINCT|ALL]n)
--**********************************************************************************************************
--聚合函数
--在数字类型数据使用AVG and SUM 函数
--AVG：计算平均值
--SUM：计算总和
--输出员工工资的总和,工资的平均值
select sum(salary),avg(salary)
from employees
--输出员工表10号部门员工工资的总和，工资的平均值
select sum(salary),avg(salary)
from employees
where department_id=10
--MIN and MAX适用于任何数据类型
--MIN： 计算最小值
--MAX：计算最大值
--输出员工表中工资的最大值和最小值
select max(salary),min(salary) from employees
--输出20号部门中工资的最大值和最小值
select max(salary),min(salary) from employees where department_id=20
--COUNT(*)返回表中所有符合条件的记录数
--查询有多少员工
select count(*) from employees
--COUNT(字段) 返回所有符合条件并且字段值非空的记录
--带条件查询
--查询10号部门有多少员工
select count(employee_id) from employees
select count(employee_id) from employees where department_id=10
--count(distinct(expr))返回不重复的，非空值的数量
--查询10号部门的工种数量
select count(distinct(job_id)) from employees where department_id=10
--分组函数在计算时省略列中的空值
--计算30号部门员工工资的平均工资
select avg(salary)
from employees where department_id=30
--NVL函数迫使分组函数包括空值
--计算30号部门员工工资的平均工资
select avg(nvl(salary,0))
from employees where department_id=30
--*****************************************************************************************************************
--分组的语法结构
SELECT column, group_function
FROM table
[WHERE condition]
[GROUP BY group_by_expression]
[ORDER BY column]; order by 始终是在最后的。
--查询每个部门工资的最大值,最小值
需求:输出结果如下
department_id max(salary),min(salary)
10 5000 1000.00
20 6000 1000.00
30.....
select department_id,max(salary),min(salary)
from employees
group by department_id --你会发现select后面的必须出现在group by后面。
order by department_id asc
--这个语句太厉害了，找出的各个部门中，所有工资的最大值，和最小值。
--pw
--使用groupby子句的注意事项
--1、出现在SELECT列表中的字段，如果出现的位置不是在组函数中，那么必须出现在GROUP BY子句中
select department_id,job_id,max(salary),min(salary)
from employees
group by department_id,job_id --这里必须加上job_id，否则出错，也就是说在select之后的字段，必须要在 group by后面出现。
--2、在GROUP BY 子句中出现的字段，可以不出现在SELECT列表中
select max(salary),min(salary)
from employees
group by department_id
--对多列分组
--一查询部门编号和工种并按 部门编号 和 工种分组
select department_id,job_id
from employees
group by department_id,job_id
--不能在 WHERE 子句中限制组.
--限制组必须使用 HAVING 子句.
--不能在 WHERE 子句中使用组函数
语法结构:
--使用HAVING子句对分组的结果进行限制
SELECT column, group_function
FROM table
[WHERE condition]
[GROUP BY group_by_expression]
[HAVING group_condition]
[ORDER BY column];
--select语句的执行流程
* 先执行where子句，对数据进行过滤
* 过滤后的数据再用group by子句分组
* 分组后的数据再用 HAVING子句进行组函数过滤
* 最后,对查询的数据排序。
--按部门进行分组,输出部门id,部门的平均工资,要求平均工资>2000
select department_id,avg(salary)
from employees
group by department_id
having avg(salary)>2000
order by department_id
--按部门进行分组,输出部门id,部门的平均工资,要求平均工资>2000，并且部门不为null,并且不是10号部门
select department_id,avg(salary)
from employees
where department_id is not null and department_id<>10
group by department_id
having avg(salary)>2000
order by department_id
大多数集合函数都能在计算时消除空值;COUNT函数则属于例外。
对包含空值的一个列使用COUNT函数，空值会从计算中消除。
但假如COUNT函数使用一个星号，它就计算所有行，而不管是否存在空值。
如果希望COUNT函数对给定列的所有行(包括空值)进行计数，
请使用ISNULL函数。ISNULL函数会将空值替换成有效的值。
事实上，对集合函数来说，如果空值可能导致错误结果，
ISNULL函数就非常有用。记住在使用一个星号时，COUNT函数会对所有行进行计算。
---多表连接和子查询。
--自连接与基本连接是一样的，只是把它看成两张表就OK了。
连接的概念:
连接分为条件连接、等值连接和自然连接三种。
1、条件连接就是在多个表的笛卡尔积中选取满足条件的行的连接，例如 select * from A,B where A.a > A.b 之类的有条件的查询。
2、等值连接就是特殊的条件连接，当条件为某字段=某字段时，即为等值连接。如SELECT ename,sal,dname FROM emp,dept WHERE emp.deptno=dept.deptno;
3、自然连接是一种特殊的等值连接，他要求多个表有相同的属性字段，然后条件为相同的属性字段值相等，
最后再将表中重复的属性字段去掉，即为自然连接。如A中a,b,c字段，B中有c,d字段，
则select * from A natural join B 相当于 select A.a,A.b,A.c,B.d from A.c = B.c 。
内连接与等值连接的区别：
内连接：两个表（或连接）中某一数据项相等的连接称为内连接。等值连接一般用where字句设置条件，内连接一般用on字句设置条件，但内连接与等值连接效果是相同的。
内连接与等值连接其实是一回事情（等效）。
经常有人会问到select a.id,b.name from a,b where a.id=b.pid 与
select a.id,b.name from a inner join b on a.id=b.pid 有什么区别，哪个效率更高一些。
实际上一回事情了。只是内连接是由SQL 1999规则定的书写方式。两个说的是一码事。
--支持SQL1999的新连接标准
包括以下新的TABLE JOIN的句法结构
CROSS JOIN——它在两个表格中创建了一个笛卡尔积，就象是在Oracle8i中没写WHERE时一样
NATURAL JOIN——它通过从WHERE子句中自动连接标准来改善SQL的稳定性，自然连接。
USING子句——它可以通过名字来具体指定连接
ON子句——这个句法允许在两个表中为连接具体指定列名
LEFT OUTER JOIN——它返回表格中左边的行和右边的数值，如果没有搭配的行的话，则返回空
RIGHT OUTER JOIN——它返回表格中右边的行和左边的数值，如果没有搭配的行的话，则返回空
FULL OUTER JOIN——它返回的是两个表格中所有的行，用空填满每一个空格。这在Oracle8i中则没有相应的此种句法
CROSS JOIN产生了一个笛卡尔积，就象是在连接两个表格时忘记加入一个WHERE子句一样，没有什么用处。
NATURAL JOIN 子句基于两个表中列名完全相同的多个列产生连接，必须要有两个重名的字段，才行。平时用的少。
从两个表中选出连接列的值相等的所有行
如果两个列的名称相同,但是具有不同的数据类型，则查询会返回一个错误
自然连接的条件是基于表中所有同名列的等值连接
为了设置任意的连接条件或者指定连接的列，需要使用ON子句
连接条件与其它的查询条件分开书写
使用ON 子句使查询语句更容易理解
select department_name, city
from department d JOIN location l
ON (d.location_id = l.id);
--子查询
在使用select语句查询数据时,有时候会遇到这样的情况，在where查询条件中的限制条件不是一个确定的值，而是一个来自于另一个查询的结果。
SELECT select_list
FROM table
WHERE expr operator
(SELECT select_list
FROM table);
1、子查询在主查询前执行一次--且记，它先执行一次。注意执行顺序，是它先执行一次，然后另外一个才执行。
2、主查询使用子查询的结果
--注意事项
使用子查询的注意事项
1、子查询要用括号括起来
2、将子查询放在比较运算符的右边(增强可读性)
3、只有在执行Top-N分析时，子查询中才需要使用Order by子句，也就是分页。
4、在Oracle8i之前的版本中，子查询不能包含Order by子句
5、对单行子查询使用单行运算符
6、对多行子查询使用多行运算符
--子查询的种类
单行单列子查询：只包含一个字段的查询，返回的查询结果也只包含一行数据，一列数据。
多行单列子查询：只包含了一个字段，但返回的查询结果可能多行或者零行，但只有一列。
多列子查询：包含多个字段的返回，查询结构可能是单行或者多行。
--单行查询，用= => <> 等这个比较符号，因为我们已经知道结果只有一个，如果不确定有几个的话，要用多选子查询运算符号。
SELECT employee_id, last_name
FROM employees
WHERE salary =
(SELECT MIN(salary)
FROM employees
GROUP BY department_id);
ERROR at line 4:
ORA-01427: single-row subquery returns more than one row
错误原因：对多行子查询使用了单行比较操作符
--多行子查询
1、返回多行
2、使用多行比较运算符
IN --与列表中的任意一个值相等
ANY -- 与子查询返回的任意一个值比较
ALL --与子查询返回的每一个值比较
SELECT employee_id, last_name, job_id, salary
FROM employees
WHERE salary < ANY
(SELECT salary
FROM employees
WHERE job_id = 'IT_PROG')
AND job_id <> 'IT_PROG';
ANY 通常与大小写符号搭配使用，不单独使用。可以是<ANY 和>ANY ：
分别代表着下面的含义
<ANY 小于子查询数据中的最大值
>ANY 大于子查询数据中的最小值
SELECT employee_id, last_name, job_id, salary
FROM employees
WHERE salary < ALL
(SELECT salary
FROM employees
WHERE job_id = 'IT_PROG')
AND job_id <> 'IT_PROG';
ALL 通常与大小写符号搭配使用，不单独使用。可以是<ALL 和>ALL ：分别代表着下面的含义
>ALL 指大于子查询数据中的最大值
<ALL 指小于子查询数据中的最小值
--其实，对有些条件，连接查询和子查询是可以互换的。
4. 查询在loc为NEW YORK的部门工作的员工的员工号，ename,deptno,job(使用连接查询,子查询两种查询方式)
--子查询
select empno,ename,deptno,job from emp where deptno =
(select deptno from dept where loc='NEW YORK')
--连接查询
select e.empno,e.ename,d.deptno,e.job from emp e join dept d on(e.deptno=d.deptno) and d.loc='NEW YORK'
--总结：在写复杂的SQL语句的时间，要学会分解，把复杂的东西分解到某个子条件中。然后再去查询。
--研究一下SQL语句的执行效率问题？
---事务问题
先来谈一上JDBC的事务
获取连接
Connection conn = null;
...
try{
1、设置连接的提交方式为非自动提交
con.setAutoCommit(false);
创建statement对象
Statement stmt = conn.createStatement();
执行insert
stmt.executeUpdate(insertsql);
2、提交。
conn.commit();
}catch{
3、回滚
conn.rollback();
}finally{
关闭资源。
}
--使用子查询创建表
--******************************************************************************************
使用子查询创建表的语法
CREATE TABLE table
[column(, column...)]
AS subquery;
--带数据的
create table departments01
as
select department_id,department_name,manager_id,location_id from departments
--不带数据的
create table departments02
as
select department_id,department_name,manager_id,location_id from departments where 1>2
select department_id,department_name,manager_id,location_id from departments where 1=1
--不省略字段列表
create table departments03
(
id,
name,
mid,
lid
)
as
select department_id,department_name,manager_id,location_id from departments where 1>2
----省略字段列表
create table departments04
as
select department_id id,department_name name,manager_id mid,location_id lid from departments
--******************************************************************************************
--修改表中的字段(了解)
--在test表中增加字段
alter table test
add sex varchar2(30)
--修改test表中的字段，当表中没有字段的时间，你想怎么做就怎么做，但是当有数据的时间，并且你要缩小就要小心了。增大肯定没有问题。
alter table test
modify sex varchar2(5)
--删除表中的字段
alter table test
drop column sex
--******************************************************************************************
--删除表的内容
1、TRUNCATE TABLE 语句
清除表中所有的记录，delete可以选择删除表中的一部分
是DDL语句，不可以回滚，delete可以使用rollback回滚，放弃修改。
释放表的存储空间，delete不释放空间
2、 是删除数据的方法之一
3、TRUNCATE TABLE table_name;
--删除departments04表中的数据
truncate table departments04
--******************************************************************************************
--删除表
DROP TABLE table_name;
--删除departments04表
drop table departments04
--******************************************************************************************
约束是在表上强制执行的数据校验规则.
当表中数据有相互依赖性时,可以保护相关的数据不被删除.
Oracle 支持下面五类完整性约束:
1、NOT NULL 非空
2、UNIQUE Key 唯一键
3、PRIMARY KEY 主键
4、FOREIGN KEY 外键
5、CHECK 检察
Check约束条件是一种比较特殊的约束条件，通过check定义，
强制定义在字段上的每一记录都要满足check中定义的条件。
在check中定义检查的条件表达式，进入表中的数据必须符合
check中设置的条件
条件表达式不允许使用:
1、SYSDATE, USER等函数
2、参照其他记录的值
..., salary NUMBER(2)
CONSTRAINT emp_salary_min
CHECK (salary > 0),...
--********************************************************************--
--数据优化与索引有关？？且记，在优化的时间用。
--视图，十分重要。
视图也就是虚表，实际上视图就是一个命名的查询，用于改变基表数据的显示。
可以限制对数据的访问
可以使复杂的查询变的简单
提供了数据的独立性
提供了对相同数据的不同显示
--视图: --为sql语句起的别名 给予表之上的一个查询语句
--语法:
--在CREATE VIEW语句后加入子查询.
CREATE [OR REPLACE] VIEW view_name
[(alias[, alias]...)]
AS subquery
[WITH READ ONLY];
create or replace view v_emp
as
select * from employees
--查询视图
select * from v_emp;
--它是为一些比较的复杂的sql语句，添加一个别名。这样在程序中就能够直接调用
--一个简单的视图，而不是要写复杂的SQL语句。
--视图的作用
--* select 语句比较复杂
--* select语句在开发的程序中可能多次使用
--* 在程序中直接使用视图 select * from v_emp
create or replace view v_emp
as
select "EMPLOYEE_ID","FIRST_NAME","LAST_NAME","EMAIL","PHONE_NUMBER",
"HIRE_DATE","JOB_ID","SALARY","COMMISSION_PCT","MANAGER_ID","DEPARTMENT_ID" from employees
--描述视图的结构(命令行执行)
desc v_emp
describe v_emp
--创建复杂视图
create or replace view v_emp_dept
as
select d.department_name,min(salary) mins,max(salary) mass,avg(salary) avgs,sum(salary) sums,count(salary) counts
from employees e,departments d
where e.department_id=d.department_id
group by d.department_name
--查询视图
select * from v_emp_dept
--通过视图插入数据到表中
create or replace view v_dept
as
select deptno,dname,loc from dept
--查询视图
select * from v_dept
--通过 v_dept视图插入数据到dept表中
insert into v_dept(deptno,dname,loc) values(89,'xxx','ss')
--通过设置WITH READ ONLY选项可以禁止对视图执行DML操作.
create or replace view v_dept
as
select deptno,dname,loc from dept
with read only
--删除视图
--删掉视图不会导致数据的丢失，因为视图是基于数据库的表之上的一个查询定义.
DROP VIEW view_name;
--删除v_dept视图
drop view v_dept
--******************************************************************************************************
TOP分析法:oracle 分页
行内视图
行内视图是在SQL语句中使用的一个带有别名的子查询.
在主查询FROM 子句中的子查询就是行内视图.
行内视图不是数据库的对象,所以不需要显式的创建.
Top-N分析的语法注意事项:
1、使用了rownum这个伪列，这个伪列将会返回行号，可以作为返回记录的序列号显示。
2、在from后面使用了子查询，这是标准的行内视图的使用。
3、在子查询中使用了order by进行排序，在前面的子查询中不需要使用。
4、在主查询中通过where条件中的rownum伪列定义过滤条件，只返回最什么的前几行数据
-- 查询员工表中 employee_id为10001 10002 10003 不能使用表中的任何字段作为查询条件
select employee_id,first_name from employees where rownum<4
---它只能查询小于的，不能查询大于的，此时里面的已经变成了，逆序的排列，然后再用<这种做就OK了。
-- 查询员工表中 employee_id为100010 10009 10008 不能使用表中的任何字段作为查询条件
select employee_id,first_name from(
select employee_id,first_name from employees order by employee_id asc
)
where rownum < 4
--分页 每页显示3条记录 rownum rank也是oracle中的隐藏字段。
-- 第一页 查询员工表中 employee_id为10001 10002 10003 不能使用表中的任何字段作为查询条件
select employee_id,first_name from(
select rownum rank,employee_id,first_name from (
select employee_id,first_name from employees order by employee_id asc
)
where rownum <4
) where rank>0
-- 第二页 查询员工表中 employee_id为10004 10005 10006 不能使用表中的任何字段作为查询条件
select employee_id,first_name from(
select rownum rank,employee_id,first_name from (
select employee_id,first_name from employees order by employee_id asc
)
where rownum <7
) where rank>3
-- 第三页 查询员工表中 employee_id为10007 10008 10009 不能使用表中的任何字段作为查询条件
select employee_id,first_name from(
select rownum rank,employee_id,first_name from (
select employee_id,first_name from employees order by employee_id asc
)
where rownum <10
) where rank>6
-- 第四页 查询员工表中 employee_id为100010 不能使用表中的任何字段作为查询条件
select employee_id,first_name from(
select rownum rank,employee_id,first_name from (
select employee_id,first_name from employees order by employee_id asc
)
where rownum <13
) where rank>9
--******************************************************************************************************
--同义词:
同义词是数据库中一个对象的别名,可以简化对对象的访问
通过使用同义词，可以:
1、简化了引用另一个用户对象的方法
2、缩短了对象名称的长度
CREATE [PUBLIC] SYNONYM synonym
FOR object;
--创建同义词，其实就是给表起了一个别名。
create synonym xx
for departments
--使用同义词查询
select * from xx
--删除同义词.
DROP SYNONYM s_emp;
--删除xx
drop synonym xx
--*****************************************************************************************************