SQL语言是一种非过程语言；
SQL语句接受集合作为输入，返回集合作为输出；
SOL的集合特性允许将一条SQL语句的结果作为另一条SQL语句的输入；
SQL不要求用户指定对数据的存放方法，用户无需了解存取路径及物理地址，存取的路径由DBMS优化来完成；
查找可以是集合，插入、删除更新也可以是元组；
同一语法结构有两种使用方法，用户在终端命令提示符下输入SQL命令时数据库服务器立即执行的交互式应答使用，和把SQL命令嵌入到应用程序中执行的预编译SQL。

1建立数据库表格，并设置表格可以使用的空间；
2改变数据库系统的环境设置；
3针对某个数据库或表格授予用户存取权限；
4对数据库表格建立索引值；
5修改数据库表格结构（新建、删除、修改表格字段）；
6对数据库进行数据的新建、修改、删除、查询。

1数据查询语言-DQL-select 语句
2数据操作语言-DML-insert（插入数据） update（更新数据） delete（删除数据） 语句
3事务控制语言-TCL-commit（提交对数据库的更改） rollback（取消对数据库的更改） savepoint（设置保存点）
4数据定义语言-DDL-create table（创建表） alter table（修改表结构） drop table（删除表）
5数据控制语言-DCL-grant（授予用户或者角色权限） revoke（收回用户或者角色权限）

1关键字不区分大小写
2字符值区分大小写

1模式是一个数据库对象的合集
2模式为一个数据库用户所拥有，并且具有和该用户相同的名称，如system模式、scott模式
3在一个模式内部不能直接访问其他模式的数据库对象，即使在具有访问权限的情况下，也需要指定模式名称才可以访问其他模式的数据库对象
4模式对象是用户创建的逻辑结构，用以储存或引用数据
5模式拥有模式对象，模式对象被模式所拥有

scott模式示例(还有很多其他模式)
emp:
empno|NUMBER
ename|VARCHAR2
job|VARCHAR2
mgr|NUMBER
hiredate|DATE
sal|NUMBER
comm|number
salgrade:
grade|NUMBER
losal|NUMBER
hisal|NUMBER
dept:
empno|NUMBER
dname|VARCHAR2
loc|VARCHAR2
bonus:
ename|VARCHAR2
job|VARCHAR2
sal|NUMBER
comm|NUMBER

SQL>connect scott/tiger
已连接
SQL>select table_name from user_tables;--查询数据库的所有表名

SQL>connect system/1qaz2wsx
已连接
SQL>select table_name from dba_tables where owner='SCOTT';--在system模式下查询数据库的所有表名

本文代码基于PL/SQL Developer1开发工具与Oracle进行交互（此外还有TOAD和oracle自带工具SQL*Plus）

基本语法格式
select{[distinct|all]columns|*}--用于选择数据表、视图中的列【4】
[into table_name]--用于将原表中的结构和数据插入新表中
from{tables|views|other select}--用于指定数据来源，包括表、视图和其他select语句【1】
[where conditions]--用于对检索的数据进行筛选【2】
[group by columns]--用于对检索结果进行分组显示【3】
[having conditions]--用于从使用group by子句分组后的查询结果中筛选数据行【5】
[order by columns]--用来对结果进行排序【6】

以上语句的执行顺序：【4】【1】【2】【3】【5】【6】

select * from emp;--检索一个表的所有列
select * from emp,dept;--检索多个表的所有列，检索结果呈现在同一个窗口，且和表名的输入顺序有关

注：select子句中可跟具体字段（列名）、常量、*、函数
    常量-可用于使查询结果明晰
    select '姓名：',name from student;--查询结果如 姓名：张张
    select '张简简' from student;--suanzuo

select column1，column2 from table_name;--从单个表中查询多个制定的列
select column1，column2 from table_name1,table_name2;--从多个表中查询多个特定的列
select column2，column1 from table_name1,table_name2;--语句中不要求列名和表名顺序对应

在oracle数据库中有的行标识符ROWID
是Oracle数据库中使用隐藏列，实际上并不是定义在表中
所以被称为伪列，长度为18个字符
包含该行数据在Oracle数据库中的物理地址，使用select语句可以查询到
select rowid from table_name;
--查询伪列
--与索引相关

非真实存在于表中
与查询结果相关
为每条记录赋序号
查询结果是一行一行出现的，查询一行赋一个号

select rownum,s.name
from student s
where rownum=1;
--可运行
select rownum,s.name
from student s
where rownum=2;
--不可运行
--因为行无法出现，无法跳过1赋值2
select rownum,s.name
from student s
where rownum<3;
--可运行,显示前两条记录
select rownum,s.name
from student s
where rownum>3;
--不可运行
--行无法出现

解决办法（用子查询）：
select *
from （select rownum r,s.name,s.age
      from student s）
where r>3;

ROWNUM通常用于分页：
例
--分页
--每一页有10行，分别查询页和第二页内容
--注意用公式的形式（如10*(1-1)+1 and 10*1）
select * from 
(select rownum r,s.name,s.age from student s)
where r between 10*(1-1)+1 and 10*1;
select * from 
(select rownum r,s.name,s.age from student s)
where r between 10*(2-1)+1 and 10*2;

日期列指数据类型为DATA的列，默认显示格式为DD-MON-RR
------
（1）以简体中文显示日期结果
alter session set nls_date_language='SIMPLIFIED CHINESE';--报错‘ora-12705:无法访问NLS数据文件,或者指定的环境’
--原因是多次重复安装数据库--解决办法暂无，建议一开始一次性安装好数据库--原来是ＳＩＭＰＬＩＦＩＥＤ拼写错误（可恶）
（select * from V$NLS_PARAMETERS--可查看系统参数，如NLS_LANGUAGE  |  SIMPLIFIED CHINESE）
select ename,hiredate from emp;--即使步错误解决，仍无法显示‘17-12月-80’格式的结果
------
（2）以美国英语显示日期结果
alter session set nls_date_langeuage='AMERICAN';--但是这样的修改不会有ora-12705，可以修改
select ename,hiredate from emp;--仍然无法得到'17-DEC-80'格式的查询结果
-----
（3）以特定格式显示日期结果
alter session set nls_date_format='YYYY''年''MM''月''DD''日''';--显示‘a-01821日期格式无法识别’报错
select ename,hiredate from emp;--即使成功修改后，在PL/SQL中也无法显示自定义日期格式

alter session set nls_date_format='YY-DD-MM';--可运行
select ename,hiredate from emp;--无法显示自定义日期格式

alter session set nls_date_format='YY';--也可运行
select ename,hiredate from emp;--无法显示自定义日期格式
----
（4）TO_CHAR函数自定义日期（看函数部分）

select sal*(1+.1),sal from emp;--查询结果是两列sal*(1+0.1),sal
在select语句中可以使用算术运算符+-*/和括号，不仅可以执行单独的数学运算，还可以执行单独的日期运算以及与列名关联的运算

（1）列别名
select empno as 员工编号，ename as '员工名称'，job 职务 from emp;--报错-原因'员工名称'外有引号
select empno as 员工编号,ename yuangongmingcheng from emp;--可以不需要as,别名不能被引号选中，别名可以是中文、字母
（2）表别名-可快捷选取列名（养成习惯）-另建student数据表
①select   from student s;
②select s.______[出现各列名] from student s;--选中需要的列名
③select s.birthday,tel from student s;--不必须用s.tel

select distinct job from emp;--减少冗余
select distinct XO,gender from student s;--另建student数据表，对组合列去重，以每行每单位内容全重复的才算重复

null表示未知量，不是空格也不是，当插入数据时，如果没有为特定列提供数据，并且该列没有默认值，则结果为null
select ename,sal,comm,sal+comm from emp;
--sal+comm会出现值为‘值+null=null’的错误
select ename,sal,comm,sal+nvl(comm,) from emp;
--nvl(comm,0)如果comm存在数值则返回数值，否则为0
####
含有null的条件查询
select * from student s where s.major like'%会计%' and (s.home not like '%济宁%' or s.home is null);
--查询学会计的家乡不是济宁的同学
####
nvl2(参数1，参数2，参数3)
--参数1不为空时，返回参数2，否则返回参数3

### 查询xx为空或者错误的XX 
select s.name,d.dormno
from student s
left join dorm d
on s.dormid=d.id
where d.id is null;
-- 用 is null 非=''或者=null

语法select col1,col2... from tbname where 条件要求；

大于    >
大于等于    >=
小于    <
小于等于    <=
不等于    <>  或者 ！=

特殊
A {operator} any B 表示元素A与B中任何元素进行operator运算符的比较，只要有一个比较值为TRUE则返回数据行
例：select empno,ename,sal from emp where sal = any(840,1300,1155);
A {operator} all B 表示元素A与B中所有元素进行operator运算符的比较，全部比较值都为TRUE才返回数据行
例：select empno,ename,sal from emp where sal <> all(840,1300,1155);

select * from student s where name like '张张%';--可得张张、张张张等人的信息，%可匹配0个及任意长度字符串

select * from student s where name like '张_';--可得张张、张海等名字为两个长度且姓张的人名，‘_’仅仅且必须匹配一个字符

select * from student s where s.bulidid not like '%楼%';--like关键字无法查询到空白记录null值
select * from student s where not s.bulidid like '%楼%';--‘非’操作，查询结果和上面一样

select * from student s where not s.bulidid like '小红楼';--则无结果，应当用‘=’号

转义字符（不是oracle自带的，需要自己定义）
select * from dept_temp where dname like 'IT\_%'escape'\';--自定义‘\’为转义符，例子中‘_’已经不是通配符了

select * from student s where s.xo [not] in ('A','AB');
--查询血型[不为A和AB]为A或AB的同学的所有信息
in 后面要跟一个集合

考虑 与或非、交并补
select * 
from student s 
where s.name not like '孙%' and s.name not like '王%';
--查询不姓孙也不姓王的学生
select * 
from student s 
where not (s.name  like '孙%' or s.name  like '王%');--查询不姓孙也不姓王的学生

[not]between and 
select * from student s where s.age>=22 and s.age<=24;
等同
select * from student s where s.age between 22 and 24;

select s.groupno,s.name,s.age from student s order by s.groupno,s.age desc;
--检索组号、姓名、年级并按组号升序排序（默认ASC）再按年级降序（DESC）

select ename,sal from emp order by (3+sal)*8;
--检索依照的条件可以是特定列或表达式

注意了解以下两种用法但不建议使用（不直观）：
select empno,ename,sal*12 AnnualSalary from emp order by 3 desc;
--用列名‘3’指代行名，‘Annual Salary’取别名时两个单词间不能有空格
select ename from emp order by sal;
--用非选择列表列进行排序

ASCII码：计算机中每一个字符都有一个数字与之对应
         10 \n 换行符  65 A 97 a
         排序时先按字符中个字符的ASCII码比较，若相同再比较第二个字符的ASCII码 

select deptno,job from emp;
select deptno,job from emp group by deptno,job;
select ename,deptno,job from emp group by deptno,job;--报错
--对于含group by子句的select语句中仅允许出现统计函数、常量、group by子句中已经出现过的字段列
select ename,deptno,job from emp group by ename,deptno,job;

select avg(sal) from emp group by job;--group by 中的列可以不出现在select列表中

聚合函数：
AVG|返回一个数字列或者计算列的平均值
COUNT|返回查询结果中的记录数（注意不统计null值）--count(1)可以用来计数
MAX|返回一个数字列或是计算列的大值
MIN|返回一个数字列或是计算列的小值
SUM|返回一个数字列或者计算列的总合
select sum(sal),sum(distinct sal),sum(all sal)
from emp;
--都可以加 distinct 或 all（默认为all）


select job,avg(sal),sum(sal),max(sal),min(sal),count(job) 
from emp 
group by job;
--分组的意义在于对分组后的内容进行处理
--查询各类job的平均工资、总工资、高工资、低工资、人数
JOB AVG(SAL) SUM(SAL) MAX(SAL) MIN(SAL) COUNT(JOB)
CLERK   1037.5    4150    1300      800     4
SALESMAN 1400     5600    1600     1250     4
PRESIDENT 5000    5000    5000     5000     1
MANAGER 2758.33   8275    2975     2450     3
ANALYST 3000      6000    3000     3000     2

####
补充：
①wm_concat
select wm_concat(s.name)
from student s;
--用逗号将所有的字符串连接在一起
②variance统计方差
③stddev标准方差

select job,avg(sal) from emp;--error:不是单组分组函数
select job,avg(sal) from emp group by job;
条语句select子句中的列名称job告诉Oracle系统显示每行数据的职位，在emp表中有很多条数据
avg(sal)告诉Oracle显示emp表中所有数据行的平均工资，只能产生一个平均工资
两者矛盾，因此报错

select deptno,job,avg(sal),max(sal) from emp group by deptno,job;
--与单列分组的区别在于多列分组是把多个列中相同的组合列作为分组的标准
--查询同一部门中的同类工作的平均工资和高工资

DEPTNO  JOB AVG(SAL) MAX(SAL)
20  CLERK      950  1100
30  SALESMAN  1400  1600
20  MANAGER   2975  2975
30  CLERK      950   950
10  PRESIDENT 5000  5000
30  MANAGER   2850  2850
10  CLERK     1300  1300
10  MANAGER   2450  2450
20  ANALYST   3000  3000

select deptno,job,avg(sal),max(sal) from emp group by deptno,job  order by deptno desc;

用having子句对分组结果做进一步筛选
如果不使用group by子句，having子句和where子句一样都具有定义搜索条件的作用
having子句可以包括聚合函数（COUNT SUM AVG） where子句不能包含聚合函数

select子句中的处理顺序：
首先由from子句找到数据表，where子句则接收from子句输出的结果，having子句接收来自group by、where、from子句的输出

select deptno as 部门编号，avg(sal) from emp group by deptno having avg(sal)>2000;
--先通过分组计算得到每个部门的平均工资，然后通过having过滤得到平均工资大于2000的记录信息
--聚合函数的条件必须放在having之后

select deptno as 部门编号 from emp  where sal>2000;--此处的where并不能用having替代

（1）使用 ROLLUP 操作符执行数据统计
（直接用group by只能生成的简单的数据统计结果，用ROLLUP生成数据统计、横向小计、总计统计）
select deptno as 部门编号，job as 岗位，avg(sal) as 平均工资 from emp group by rollup(deptno,job); 
--可得到各部门全部岗位的平均工资

部门编号 岗位 平均工资
10  CLERK   1300
10  MANAGER 2450
10  PRESIDENT 5000
10      2916.66666666667--得到部门10的全部岗位的平均工资
20  CLERK   950
20  ANALYST 3000
20  MANAGER 2975
20      2175
30  CLERK   950
30  MANAGER 2850
30  SALESMAN 1400
30      1566.66666666667
        2073.21428571429
----
（2）使用 CUBE 操作符执行数据统计
select deptno 部门编号,job 岗位,avg(sal) 平均工资 from emp group by cube(deptno,job);

部门编号  岗位  平均工资
                 2073.21428571429--全部部门的全体员工的平均工资
           CLERK 1037.5--所有岗位为CLERK的员工的平均工资
           ANALYST 3000
           MANAGER 2758.33333333333
           SALESMAN  1400
           PRESIDENT 5000
10                2916.66666666667--所有部门为10的员工的平均工资
10         CLERK 1300
10         MANAGER 2450
10         PRESIDENT 5000
20                  2175
20         CLERK 950
20         ANALYST 3000
20         MANAGER 2975
30                  1566.66666666667
30         CLERK 950
30         MANAGER 2850
30         SALESMAN  1400

思考一：select deptno 部门编号,job 岗位,avg(sal) 平均工资 from emp group by cube(job,deptno);

部门编号 岗位 平均工资
        2073.21428571429--全部部门的全体员工的平均工资
10      2916.66666666667--所有部门为10的员工的平均工资
20      2175
30      1566.66666666667
    CLERK   1037.5--所有岗位为CLERK的员工的平均工资
10  CLERK   1300
20  CLERK   950
30  CLERK   950
    ANALYST 3000
20  ANALYST 3000
    MANAGER 2758.33333333333
10  MANAGER 2450
20  MANAGER 2975
30  MANAGER 2850
    SALESMAN    1400
30  SALESMAN    1400
    PRESIDENT   5000
10  PRESIDENT   5000

思考二：select deptno 部门编号,job 岗位,mgr 上级,avg(sal) 平均工资 from emp group by cube(job,deptno,mgr);
--出现多个复合组合的结果，复杂度提升，个人认为不建议使用

部门编号 岗位 上级  平均工资
            5000
            2073.21428571429
        7566    3000--上级是7566的员工的平均工资
        7698    1310
        7782    1300
        7788    1100
        7839    2758.33333333333
        7902    800
10          5000
10          2916.66666666667
10      7782    1300
10      7839    2450
20          2175
20      7566    3000
20      7788    1100
20      7839    2975
20      7902    800
30          1566.66666666667
30      7698    1310
30      7839    2850
    CLERK       1037.5
    CLERK   7698    950
    CLERK   7782    1300
    CLERK   7788    1100
    CLERK   7902    800
10  CLERK       1300
10  CLERK   7782    1300
20  CLERK       950
20  CLERK   7788    1100
20  CLERK   7902    800
30  CLERK       950
30  CLERK   7698    950
    ANALYST     3000
    ANALYST 7566    3000
20  ANALYST     3000
20  ANALYST 7566    3000
    MANAGER     2758.33333333333
    MANAGER 7839    2758.33333333333
10  MANAGER     2450
10  MANAGER 7839    2450
20  MANAGER     2975
20  MANAGER 7839    2975
30  MANAGER     2850
30  MANAGER 7839    2850
    SALESMAN        1400
    SALESMAN    7698    1400
30  SALESMAN        1400
30  SALESMAN    7698    1400
    PRESIDENT       5000
    PRESIDENT       5000
10  PRESIDENT       5000
10  PRESIDENT       5000
----
（3）使用 GROUPING 函数确定统计结果是否用到了特定列
select deptno 部门编号,job 岗位,mgr 上级,avg(sal) 平均工资,grouping(deptno),grouping(job),grouping(mgr)
from emp group by cube(job,deptno,mgr);

部门编号 岗位 上级  平均工资 GROUPING(DEPTNO) GROUPING(JOB) GROUPING(MGR)
            5000    1   1   
            2073.21428571429    1   1   1
        7566    3000    1   1   
        7698    1310    1   1   
        7782    1300    1   1   
        7788    1100    1   1   
        7839    2758.33333333333    1   1   
        7902    800 1   1   
10          5000       1   
10          2916.66666666667       1   1
10      7782    1300       1   
10      7839    2450       1   
20          2175       1   1
20      7566    3000       1   
20      7788    1100       1   
20      7839    2975       1   
20      7902    800    1   
30          1566.66666666667       1   1
30      7698    1310       1   
30      7839    2850       1   
    CLERK       1037.5  1      1
    CLERK   7698    950 1      
    CLERK   7782    1300    1      
    CLERK   7788    1100    1      
    CLERK   7902    800 1      
10  CLERK       1300          1
10  CLERK   7782    1300          
20  CLERK       950       1
20  CLERK   7788    1100          
20  CLERK   7902    800       
30  CLERK       950       1
30  CLERK   7698    950       
    ANALYST     3000    1      1
    ANALYST 7566    3000    1      
20  ANALYST     3000          1
20  ANALYST 7566    3000          
    MANAGER     2758.33333333333    1      1
    MANAGER 7839    2758.33333333333    1      
10  MANAGER     2450          1
10  MANAGER 7839    2450          
20  MANAGER     2975          1
20  MANAGER 7839    2975          
30  MANAGER     2850          1
30  MANAGER 7839    2850          
    SALESMAN        1400    1      1
    SALESMAN    7698    1400    1      
30  SALESMAN        1400          1
30  SALESMAN    7698    1400          
    PRESIDENT       5000    1      
    PRESIDENT       5000    1      1
10  PRESIDENT       5000          
10  PRESIDENT       5000          1
--此查询语句可能存在无意义的情况，本处只用做grouping示例
----
（4）在 RULLUP 操作符中使用复合列
复合列被看作一个逻辑单元的列组合，可以略过RULLUP的某些统计结果。
例如：group by rollup(a,b,c)的统计结果等同于group by(a,b,c) 、group by(a,b) 、group by a 、group by()的并集
将（b,c）作为复合列，group by(a,(b,c))的结果等同于group by(a,b,c)、group by a、group by()的并集

select deptno as 部门编号，job as 岗位，avg(sal) as 平均工资 from emp group by rollup(deptno,job); 
部门编号 岗位 平均工资
10  CLERK   1300
10  MANAGER 2450
10  PRESIDENT 5000
10      2916.66666666667
20  CLERK   950
20  ANALYST 3000
20  MANAGER 2975
20      2175
30  CLERK   950
30  MANAGER 2850
30  SALESMAN 1400
30      1566.66666666667
        2073.21428571429

select deptno as 部门编号，job as 岗位，avg(sal) as 平均工资 from emp group by rollup((deptno,job)); 

部门编号 岗位 平均工资
10  CLERK   1300
10  MANAGER 2450
10  PRESIDENT 5000
20  CLERK   950
20  ANALYST 3000
20  MANAGER 2975
30  CLERK   950
30  MANAGER 2850
30  SALESMAN 1400
        2073.21428571429
----
（5）在 CUBE 操作符中使用复合列
例如：group by cube(a,b,c)的统计结果等同于group by(a,b,c) 、group by(a,b) 、group by(a,c)、group by(b,c)、group by a、group by b、group by c 、group by()的并集
将（a,b）作为复合列，group by((a,b),c)的结果等同于group by(a,b,c)、group by(a,b)、group by c、group by()的并集

select deptno,job,avg(sal)
from emp
group by grouping sets(deptno,job);
--得到依部门和工资两个平均工资

①在多表关联查询时，如果多个表之间存在同名的列，则必须用表名限定列的引用
 如dept.deptno,emp.deptno
②为使语句简洁，使用表别名，表别名在from子句中定义如 emp e
③表别名一经定义，在整个查询语句中就只能用表别名，而不能用表名 emp.ename × e.ename √
④表别名只在所定义的查询语句中有效

####
select d.dormno, s.name
from dorm d
[inner]join student s--默认是inner内查询，不用写
on d.id=s.dormid;
--用相同的ID来进行表之间连接

####
select b.buildname,d.dormno,s.name
  from building b
  join dorm d
    on b.id = d.buildid
  join student s
    on d.id = s.dormid;
--三个及以上连接

####
select d.dormno, s.name
  from dorm d
  join student s
    on d.id = s.dormid
 where s.gender = ;
 --条件语句放在后面
 
####
内连接的另一种写法（了解即可，千万别用）
select e.ename,d.dname,e.sal
 from emp e,dept d--省去了join on 语句
 where sal>=3000;--忘记写where子句会导致笛卡尔积任意关联，会导致非常严重的数据库崩溃
 --笛卡尔积：两个表做任意关联，无关联条件，导致数据暴增

select e.ename,d.dname,e.comm
from dept d
left join emp e
on e.deptno=d.deptn;--此处应该在建表时用id而不用no

####
select s.name,d.dormno 
from student s, dorm d 
where s.dormid=d.id(+);
--左连接另种写法，仅作了解，千万别用，会造成笛卡尔积数据库崩溃或数据丢失
####
▲如果左连接的从表添加where条件，则左连接失效
例：查询所有人的姓名，如果其他宿舍在2楼，显示宿舍号
select s.name,d.dormno
from student s
left join dorm d
on d.id=s.dormid
where d.floor=2;--此处where应改为and，把选择条件变成关联条件

right用法和左连接一样

full同上

自然连接指在检索多个表时
oracle会将个表中的列和第二个表中具有相同名称的列进行自动连接
语法： natural join
select empno,ename,job,dname
from emp 
natural join dept
where sal>2000;
--由于自然连接要求表之间必须有相同名称的列，这样容易在出现不可预知的错，故几乎不用

####
select em2.ename 上层管理者,em1.ename 下属员工
from emp em1
join emp em2
on em1.mgr=em2.empno--关键在于找到对应关系
order by em1.mgr;

####
如果左连接后使用自连接，则自连接会失效
例：
select d.dormno,s.name,s1.name
from student s
left join dorm d
on d.id=s.dormid
left join student s1
on d.headno=s1.stuno;
--仅作语法示例

语法： cross join
select count(*) --计算查询结果的行数
from dept
cross join emp;
--执行结果是一个笛卡尔积

等同于

select count(*)
from dept,emp;
--执行结果是一个笛卡尔积

又称嵌套查询

在执行数据操作时
某个操作要依赖另一个select语句的查询结果
可以select语句嵌入该语句中

注意：
①子查询子句必须用括号括起来
②子查询不能包括order by
③子查询允许多层（不超过255行）嵌套
④子查询比多表关联查询更灵活、功能更强大、更容易理解，但效率更低

指子查询语句的返回结果只有一行
当在where子句中引用单行子查询时可以用单行比较运算符（= > < >= <= <>）

①关联子查询在select语句（查询结果中）
select s.name,s.gender,s.groupno,
       (select avg(t.age)from student t)--此处的平均年龄只作为常量，不是聚合函数
from student s;
--查询每个人的姓名、年龄和班级平均年龄

②关联子查询在条件语句中
select empno, ename, sal--外查询
  from emp
 where sal > (select min(sal) from emp)--括号内是内查询
   and sal < (select max(sal) from emp);
--查询emp表中不是高或低工资的员工编号、姓名、工资

指子查询语句的返回结果不止一行
当在where子句中引用多行子查询时必须用多行比较符（in any all）

在多行子查询中使用in运算符时
外查询会尝试与子查询结果中任何一个结果进行匹配
只要有一个匹配成功则外查询会返回当前的检索记录
select empno,ename,job
from emp
where deptno in
(select deptno from dept where dname<>'SALES');
--查询emp表中不是销售部门的员工信息

exists存在（前面可以加not）

--查询有预备党员的小组
select s.groupno
  from student s
 where s.groupno in 
       select t.groupno
       from student t
       where t.political = '预备党员');
select distinct s.groupno
  from student s
 where exists (select t.groupno
          from student t
         where t.political = '预备党员'
           and s.groupno = t.groupno);
--查询没有预备党员的小组
select distinct s.groupno
  from student s
 where not exists (select t.groupno
          from student t
         where t.political = '预备党员'
           and s.groupno = t.groupno);
           
 工作中常用 exists代替 in（速度更快）
 in关联子查询先子查询全部
 再主查询（速度慢，但容易想）
 exists：关联子查询先主查询一条一条查询
 再子查询（子查询不需要查询全部，速度更快）
①将原 SQL中 in改为 exists
②在子查询中添加条件
 （原子查询的结果=原主查询中 in的前面一致）

any运算符必须与单行操作符结合使用
并且返回行只要匹配子查询的任何一个结果即可
select deptno,ename,sal
from emp
where 
sal>any (select sal from emp where deptno=30)
and deptno<>30;--and与sal语句是并列的
--查询emp表中工资大于30号部门的任意一个员工工资的其他部门的员工信息
--实质上查询emp表中工资大于30号部门的低的一个员工工资的其他部门的员工信息

all运算符必须与单行操作符结合使用
并且返回行必须匹配子查询的所有结果
select deptno,ename,sal
from emp
where 
sal>all (select sal from emp where deptno=30);
----查询emp表中工资大于30号部门的所有员工工资的其他部门的员工信息
--与any相比较

select *
from student s 
where (s.age,s.gender)=(select t.age,t.gender from student t where name='张简简');
--查询年龄和性别都和张简简相同的人
--注意s.age,s.gender和t.age,t.gender前后顺序要一致

在单行或多行子查询中内查询和外查询是分开执行的
外查询仅仅使用内查询的终结果

在一些特殊需求的子查询中内查询和外查询相互关联
被称为关联子查询

①关联子查询在select语句（查询结果中）
select s.name,s.gender,s.groupno,
       (select avg(t.age)from student t where t.groupno=s.groupno)
from student s;
--使用了两个表别名
--不需要使用分组函数
--例 select avg(t.age) from student t where t.groupno*=1
    即可查询1组的平均年龄

②关联子查询在条件语句中
select s.name,s.age
from student s
where s.age>(select avg(t.age) from student t where t.groupno=s.groupno);
--查询比小组平均年龄大的人

--查询A型血人数比B型血人数多的宿舍
select ssaxx.dormno
from (select d.dormno,count(1) ssaxxrs 
      from student s 
      join dorm d 
      on s.dormid=d.id 
      where s.xo='A' 
      group by d.dormno) ssaxx--把A血型的寝室和人数看作一个表
join (select d.dormno,count(1) ssbxxrs 
      from student s 
      join dorm d 
      on s.dormid=d.id 
      where s.xo='B' 
      group by d.dormno) ssbxx--把B血型的寝室和人数看作一个表
on ssaxx.dormno=ssbxx.dormno
where ssaxx.ssaxxrs>ssbxx.ssbxxrs;
--当问题不清晰的时候拆解成多个表来解决问题即可

也称分析函数
窗，就是范围
在over子句所限定的范围内进行查询
速度优于子查询

select s.name,s.groupno,s.age,
	   max(s.age) over(partition by s.groupno) age1
from student s;
--查询所有同学姓名、组号年龄和内大年龄
--partition by 类似于 group by具有分组的作用
--【max(s.age) over(partition by s.groupno) age1】是一个整体
  使用多个聚合函数时每个要单独加over语句
--由于受开窗范围的影响别名（此处为age1）要放在 order by后

select s.name,s.groupno,s.age,max(s.age)
	   over(partition by s.groupno order by s.age)
from student s;
--查询所有同学姓名、组号年龄和窗口内大年龄
--order by
  不仅仅具有排序的作用
  只有 order by 没有 partition by 则仅有排序功能 
  会使窗口发生变化
  窗口变化为从 partition by选定的窗口的行数据开始
  到与被查询主体 order by后的列名（此处为s.age）相同值的所有数据行为止
  （不明白就运行代码试试）

在窗口范围内对 order by 后指定的数据进行排序

select s.name,s.groupno,s.age,
	   row_number() over(partition by s.groupno order by s.age)
from student s;
--相同年龄也分先后顺序
  如：排序 1  2  3  4  5
     年龄 18 19 19 19 20
select s.name,s.groupno,s.age,
	   rank() over(partition by s.groupno order by s.age)
from student s;
--并列第二然后第五
  如：排序 1  2  2  2  5
     年龄 18 19 19 19 20
select s.name,s.groupno,s.age,
	   dense_rank() over(partition by s.groupno order by s.age)
from student s;
--并列第二然后第三
  如：排序 1  2  2  2  3
     年龄 18 19 19 19 20

select s.name,s.groupno,s.age,
       lag(s.age) over(partition by s.groupno order by s.age)
from student s;
--lad(参数1，参数2，参数3)
  参数1（列名）所处位置的数据往上偏移参数2个位置
  如果偏移后数据为空则用参数3的数据填充
--lead(参数1，参数2，参数3)
  往下偏移

--不可和聚合函数同用
select s.name,s.groupno,s.age,
       first_value(s.age) over(partition by s.groupno order by s.age),
       last_value(s.name) over(partition by s.groupno order by s.age)
from student s;    
--注意要写逗号
--first_value(col_name)返回该窗口中某列的个值
--last_value(col_name)返回该窗口中某列的后一个值

range between 参数1 and 参数2
在原来的窗口范围内再进行选定

select q.realname,q.groupno,q.age,
       max(q.age) over(partition by q.groupno order by q.agerange between 参数1 and 参数2) 
from qqinfo q;

参数可以替换为以下：
unbounded preceding 组内首行
current row 当前行
unbounded following 组内末行
1 preceding 组内当前行前面1行
1 following 组内当前行后面1行
range 值比较--不了解
rows 行比较--不了解

两个集合间 交 并 差
（1）并 
    union （自带去重效果）
          （去重必带排序）
          （oracle中去重和排序都非常慢）
    union all （不去除重复内容）
              （执行速度更快）
例：
  select s.name from student s
  union
  select e.name from emp e
此外要注意多列同时运算的情况：
  对应列如s.age和e.age的属性和类型要一致
  select s.name，s.age from student s
  union
  select e.name,e.age from emp e
（2）交（ intersect ）
（3）差 （ minus ）

查询结果根据类别不同，查询方式随之不同
select s.name，--逗号不能少
       case--开始
       when s.gender=1 then '男生'
       when s.gender= then '女生'--也可以只有一个 when then语句 
       else '其他'
       end--结束
from student s;

需要注意的是 case when 是顺序执行的
如果前面的条件包含了后面的条件
则后面的条件
如：
  select s.name,
         case
         when s.score>60 then '及格'
         when s.score>80 then ''
         else '不及格'
         end
  from student s;
  --则60分以上的都是及格，即使是100也是及格而不是

当 case when 中的条件是确定值（即用等号=）时
    可等价于decode如：
    select s.name,--逗号不能少
       decode(s.gendere,1,'男生',,'女生','其他')
    from student s;

将同一列内容分成多列
① group by
② case when
③ 聚合函数

例（查询班内全部人数，男生数量，女生数量）：
  select count(1),
         count(case when s.gender=1 then '是' end) nans,
         count(case when s.gender= then '是' end) nvs
  from student s;
例（查询各组全部人数，男生数量，女生数量）：
  select s.groupno，
         count(1),
         count(case when s.gender=1 then '是' end) nans,
         count(case when s.gender= then '是' end) nvs
  from student s
  group by s.groupno;

将不同列的内容汇总到同一列
用 union[all]（并-操作）

select name,'男' gender from stul where nans=1
union
select name,'女' from stu1 where nvs=1;

整型（整数型）int/integer
浮点型（小数）float
在oracle中常用NUMBER（包括以上两类）定义数据的数字类型

####
定义数据类型 number（10，2）指定义一个长度为10精度为2的数字类型
键入 1234567890 报错
键入 1234 得 1234.00
键入 .123456788890 得.12
--思考：如果把该类型转换成字符串，那么会不会存在看不见的空格？like关键字能使用吗？（目前不知道怎么转换-可以用to_char转换）

####
to_number把字符串变成数字
注意：
select *
from student s 
where from s.gender=1;--隐式类型转换
等同于
select *
from student s 
where from to_number(s.gender)=1;
--等号两边类型不一致的时，会将左边转换成右边类型，从而报错ORA-01722

select * from dual;--dual表用于进行试验性操作,得一行一列的结果
select 1/3 from dual;--得3.333333333....

select mod(10,3) from dual;--得1

select trunc(1.2345) from dual;--得1
select trunc(1.2345,3)from dual;--得1.234
select trunc(125341.2345,-3)from dual;--得120000
 
select round(1.2345) from dual;--得1
select round(1.2345,3)from dual;--得1.235 
select round(125341.2345,-3)from dual;--得130000
--把小数换成分数一样可以运行

特别注意：
题|随机返回1-10的整数
select trunc(dbms_random.value(1,11)) from dual;
select round(dbms_random.value(1,10)) from dual;--但是此处1和10的几率小于其他数

select abs(-1) from dual;得1

select power(3,4) from dual;--3的4次方

select sqrt(4) from dual;--得2（注意和数学运算有所区别）

select power(4,1/2) from dual;--得2（也可）

select ceil(1.2) from dual;--得2

select floor(1.2) from dual;--得1

select dbms_random.value() from dual;
--得到值在[0,1)之间，没有括号也可以
select dbms_random.value(1,5) from dual;
--得到在[1,5）之间

例：随机得到1-10的整数
select trunc(dbms_random.value(1,11)) from dual;
select round(dbms_random.value(1,10))from dual;

exp(n)返回e的n次幂
sin(n)返回n的正弦值（n为弧度）
cos(n)返回n的余弦值（n为弧度）
log(n1，n2)返回以n1为底n2的对数
sign(n)若n为负则返回-1，为正返回1，为返回

char()固定长度字符串， varchar()可变长度字符串
当用通配符‘%’检索时
'6,7,8' char(10) 实际上8后面还存在空格 '%,8' 查询不出来
'6,7,8' varchar(10) 此处8后面就不存在空格 '%,8' 因此可以查询出来--你以为是小问题，其实是大问题，不能不求甚解

select '8'+5 from dual;--字符串类型的数字和数字类型的数字可以进行运算

连接字符串时，如果在字符串中加入数值，那么可以直接指定数字值；
如果在字符串中加入字符值或者日起值，那么必须用单引号引住
（1）用||连接字符串
select ename|| ' ''s job '||job from emp;--注意 ''两个单引号等同于一个单引号字符
select 1||2||3||4||5 from dual;--也可以连接数字变成字符串

（2） 用函数CONCAT连接字符（只了解即可）
select concat(concat(ename,'''s salary is'),sal)from emp;

题|
select * from test1 where ','||cs||',' like'%,8,%';
--可查找列中仅数字8但不要28，88等，如找到‘1，3，8’，‘4，566，7，8’，‘8，95’，‘7，8，88，9’

select s.name,length(s.name) from student s;

select 'zhangzhang',substr('zhangzhang',6,2) from dual;--从第六位开始截取2个字符得'zh'
select 'zhangzhang',substr('zhangzhang',6) from dual;--从第六位开始截取剩余的全部字符得'zhang'
select 'zhangzhang',substr('zhangzhang',-3) from dual;--从倒数第三位开始截取剩余的全部字符得'ang'

select 
replace('zhangjianjian','jianjian','hhh')
from dual;
--得zhanghhh
--若第三个参数为空则替换为空

语法：instr(s1,s2[,i][,j])
     s2在s1中从第i个字符第j次出现的位置，i,j默认是1
select 
instr('zhangjianjian','an',5,2)
from dual;
--返回12
select 
instr('zhangjianjian','an',1,2)
from dual;    
--返回8

####
select 
instr('zhangjianjian','an')
from dual;--查询个‘an’的位置

select 
instr('zhangjianjian','an',4)
from dual;--查询从第四位开始往后的个‘an’的位置，返回8

select 
instr('zhangjianjian','an',3)
from dual;--注意：返回3

select 
instr('zhangjianjian','an',-1)
from dual;
--得12
--当第三个参数为负数时，搜索将从右向左进行，但是返回位置还是从左向右计算的

（1）大小写转换
select upper('zhangJIANJINA')
from dual;
select lower('zhangJIANJINA')
from dual;


（2）去除空格
select trim('    zhangjianjian    ')
from dual;
--去左边
select ltrim('    zhangjianjian    ')
from dual;
--去两边
select rtrim('    zhangjianjian    ')
from dual;
--去两边


（3）填充
select lpad('jian',10,'a')
from dual;
--左填充，使字符串长度达到10，不足的用第三个参数补齐，没有第三个参数则用空格补齐
--rpad右填充
--如果字符大于二个参数要求则从左到右截取第二个参数要求的长度



####
（4）返回字符的ASCII值、返回ASCII值的字符串
select ascii('\n'),ascii('A'),ascii('a')
from dual;
--得
ASCII('\N') ASCII('A')  ASCII('A')
92          65          97
--换行键是 10
--92是 \

select chr(92),chr(65),chr(97)
from dual;
--得
CHR(92) CHR(65) CHR(97)
\       A        a


（5）字符串每个单词首字母大写（INITCAP）（用空格、标点符号、控制字符来区分单词）
select initcap('zhang jian jian')
from dual;

select to_char(sysdate,'yyyy/mm/dd hh[24]:mi:ss q day')
from dual;
--分别对应年、月、日、时（如果是hh24则是24小时制，否则默认12小时制）、分、秒、季度、星期
--顺序可以调整
--sysdate是关键字，表示当前服务器的时间（非网络时间）
--中间的符号和空格可以用其他符号替换，但是目前不清楚怎么用汉字或者字母替换
--此外注意如果to_char('mm')只能知道是哪个月，不能知道是哪年哪月

select to_date('0101','mmdd') 
from dual;
--不带年份则默认为当年
--可依照日期转字符串反向思考
####
注意：
--如果条件添加为时间等于时间，应将时间转换成字符串
select *　
from　student s
where to_char(s.birthday,'yyyymmdd')='20220101';
--查询2022年1月1日出生的人
 
--如果把字符串变成时间会导致不
select *　
from　student s
where to_date('20220101','yyyymmdd')=s.birthday;
--因为日期实际上有很多位，包括时分秒等，所以无法相等

select sysdate+.5,sysdate-1 
from dual;
--加半天，减一天
--两个时间只能想减，不能相加，得天数

（1）trunc函数（得到个时刻）
select trunc(sysdate)
from dual;
--得到当天凌晨的时间
--截取后仍然是date格式
select turnc(sysdate,'mm')
from dual;
--yyyy截取到当年个时刻
--mm截取到当月第个一时刻
--dd截取到当日个时刻
--day截取到当周个时刻
--（Oracle按照美国的习惯，每周的天为周日，如果得到周一则需要后+1，trunc(sysdate,'day')+1）
--其他类推

select last_day(sysdate) 
from dual;
--返回当月的后一天的同一时刻，如1.31，2.28等（注意不是准点时刻）
####
--如果要上个月的后一天，建议先查找上个月，然后再查找后一天
select last_day(add_months(sysdate,-1))
from dual;

select add_months(sysdate,1),add_months(sysdate,-2)
from dual;

（1）两个时间相差的月数
select months_between(add_months(sysdate,5),sysdate)
from dual;
--前一个减去后一个时间相差的月份
（2）从某时刻开始算的下一个周几
select next_day(sysdate,1)
from dual;
--1代表星期天
--注意：如果今天是周三，那下一个周四是明天

####
（3）时区
new_time(d1,t1,t2)
--d1是日期类型
--返回t1时区的d1时间在t2时区的时间

DML数据库操作语言
注意：多台电脑一起操作要及时提交或回滚
     不能挂起
     否则其他人无法操作
     在PL/SQL Developer界面上面有绿色（提交）和红色（回滚）的类箭头符号
有人未提交或回滚导致无法操作的处理：
     在PL/SQL Developer界面
     -工具-回话-点击下面窗口-锁定
     -点击上方窗口中的数据行找到下方窗口有数据的行
     -右键-关掉
     即可

select s.*,rowid 
from student s;
-- 可以在PL/SQL Developer中直接修改表数据

select * 
from V$NLS_PARAMETERS;
-- 查询表的部分数据类型的形式

insert语句
注意：
①为数字列增加数据时，可以直接提供数字值或者用单引号引住
②为字符列或日期列增加数据时必须用单引号引住
③增加数据列时，数据必须满足约束条件并且必须为主键列和NOT NULL列提供数据
④增加数据时，数据必须与列的个数和顺序保持一致

insert into 
table_name(column_name1,column_name2···)
values(express1,express2···)
--如果不指定列名则需要为每一列都提供数据且顺序必须与表列顺序完全一致（不推荐）

###知识例
insert into
emp(empno,ename,job,hiredate，sal)
values(1234,'MARY','CLERK',to_date('1999-01-01','YYYY-MM-DD'),default);
--日期值的处理
--指定 DEFAULT时，如果存在默认值则使用默认值，否则自动使用NULL

insert into
table_name(column_name1,column_name2,column_3···)
selectSubquery--任何合法的select语句
--可用于批量插入数据

###仅例
insert into
jobs_temp
select * from jobs where jobs.max_salary>10000;

update table_name
set cloumn_name1=express1,cloumn_name2=express2···--express也可以是select语句
[where condition]
--只有符合条件的记录才会被修改
--如果没有where子句则修改全部记录
--修改数据为空 where col=''（不是 is null）

###知识例
update cs
set job='aaaa',mgr=1234,hiredate=to_date('19990101','yyyymmdd'),deptno=default
where empno=9999;
--日期处理
--default应用（如果存在默认值则使用默认值修改数据，否则使用NULL）

--把emp表中工资小于3000的员工工资调整为管理者的平均水平
update emp
set sal=(select avg(sal)
         from emp 
         where job='MANAGER')
where sal<2000; 

delete from 
table_name
where condition;
--无where子句则删除表的全部数据
--可以用ROLLBACK回滚

truncate table table_name;
--删除表的全部内容
--修改了表结构 属于DDL语句
--无法回滚
--REUSE STORAGE删除记录仍保存占用的空间
--DROP STORAGE删除记录立刻回收空间
--大型数据表的全部数据删除应该用TRUNCATE
--用DELETE会导致回滚段磁盘耗光，ORACLE数据库挂起

delete 删除过程慢，容易恢复（更安全），DML，不影响高水位线
truncate 删除过程快，很难恢复，DDL，高水位线归零

高水位线：表中历史插入数据量的大高度

delete 删除后的数据恢复【了解】
    闪回： alter table able_name enable row movement;--重置行
          flashback table table_name to timestamp to timestamp('20220220 10:10:10','yyyymmdd hh24:mi:ss')

结合insert和update
如果目标表有数据则更新数据为源表的数据
没有数据则将源表中对应数据插入目标表

语法：
merge into tbDst（目标表）
using tbSrc（源表）/select子句
on ________  (源表和目标表关联关系)
when matched then --如果匹配到
  update set tbDst.col=tbSrc.col --则更新
when not matched then--如果匹配不到
  insert (tbDst.cols....) values (tbSrc.cols)--则更新（此处不必是表中的全部列）