sql基础笔记整理

一.准备

**数据库：**本质是一个工具，用来存储数据
安装数据库 配置监听程序(1521端口号) 配置数据库
解锁用户并修改用户密码,SYSDBA SYSTEM不需要解锁

数据库软件：用来操作数据库中数据的工具，比如plsql、sqlplus…

–通过数据库软件操作数据库中的数据
报错：
1.监听和数据库服务未打开（LISTENER 和数据库名结尾的服务）
2.重启一下两个服务，listener.ora(主要)和tnsnames.ora有关,类似报错：无监听程序、无法解析连接符、可能是oracle net管理错误
路径：E:\app\用户名\product\11.2.0\dbhome_1\NETWORK\ADMIN

3.删除监听文件，重新配置监听程序即可
4.若配置监听程序时，在服务中没有对应服务，一般是杀毒软件和防火墙的原因

二.简单查询

1.语法

  SELECT 列名 FROM 表名;

2.表

行 列
列：存储的是表中数据的相关属性，多个列的数据组成一行完整的数据
行：一行数据完整的描述该表的信息，每行数据为一个整体

示例： 员工信息表：存储员工的信息
包含：员工编号 姓名 工作 领导编号 入职日期 工资 奖金 部门编号 --在后面的条件、排序、分组、子查询、联合查询… 数据过滤以每条数据为准

三.别名

若别名以数字或特殊字符开头、别名中包含{ [ (等特殊字符时需要用英文格式下的双引号（“”）引起–双引号用于此处
表别名不允许有AS

四.连接符（函数）

五.DUAL表

伪表，本身含有一行一列的数据，值为X，用于验算公式、函数结果、未指定具体的表名和数据集合
两个单引号显示一个单引号

–往往是在字符串中使用单引号

三.条件查询

1.语法

  SELECT 列名  FROM 表名 WHERE 条件;
  --条件由  字段名+连接符+数值  组成
  

2.运算符

1.逻辑运算符：

< > <= >= <>/!=

  SELECT 列名  FROM 表名 WHERE 列名 </>/<>..值;

2.数学运算符：

3.BETWEEN AND

1.值1>值2
2.包含边界值
3.在…之间（不包含边界值，类似区间，因为between and始终包含边界值，所以想要不包含边界值建议使用</>）

SELECT 列名 FROM 表名 WHERE 列名 BETWEEN 值1 ADN 值2

4. AND/OR

1.AND优先级高于OR
2.AND/OR用于连接多个条件

求不是  10号部门的职员 和 20号部门的销售
select * from emp where deptno=10 and job='CLERK' or deptno=20 and job='SALESMAN' ----做差(MINUS)
select * from emp
minus
(select * from emp where deptno=10 and job='CLERK' or deptno=20 and job='SALESMAN')
-----思路1:分别求出10号部门不是CLERK,20号部门不是SALESMAN,其余部门所有工作 ,将所有集合求并集
select *
  from emp
 where deptno = 10
   and job <> 'CLERK'----10号部门不是CLERK
    or deptno = 20
   and job <> 'SALESMAN'------20号部门不是SALESMAN
    or deptno <> 10
   and deptno <> 20-----其余部门所有工作
-----思路2:分别求出职员不是10号部门的,销售不是二十号部门的,其余工作的部门,将所有集合求并集

5.null空值判断

空值参与比较恒为空，作为条件恒不成立
–查询列为空或非空的数据
。。。。列名 is null
null 可用nulls first/last 控制顺序
示例：

select * from  emp order by comm desc  nulls first

select * from  emp order by comm desc  nulls last

–往往搭配去控制函数使用

求没有提成的员工信息
select * from emp where comm is null
求有提成的员工信息
select * from emp where comm is not null
select * from emp where comm=null-----=null,没有值
select 1 from dual where 1>null
select 1 from dual where 1<=null  ----空值不参与比较,任何值与空值比较,结果仍为空

6.IN查询

1).in后为一个数据集合
SELECT 列名 FROM 表名 WHERE 列名 in（数据集合）
——in 在…里面
2).in在使用时可以比较多列，但是集合中必须为多列配对
3).not in后集合中若含有空值，结果为空(也就是查出集合以外的数据)
示例：

select * from emp where empno not in ( select empno from emp where deptno=10 and job='CLERK')

7.ANY/ALL/SOME

ANY 集合中的任意一个数据
ALL 集合中全部数据
Some标识满足其中一个的意义，是用or串起来的从句

列名=ALL（数据集合）–查询列等于all集合中全部的数据信息

Any：小于任意一个（不是全部的）的值，小于最大值，大于最小值
All：小于全部的值 小于最小的，大于最大的
----往往用最大最小的值代替

假设集合B,有三个值(1000,1500,2000)
求大于集合B中所有值的员工工资
select * from emp where sal>all(1000,1500,2000)--->all:大于最大

求小于集合B中所有值的员工工资
select * from emp where sal<all(1000,1500,2000)---<all:小于最小

求大于集合B中任意值的员工工资
select * from emp where sal>any(1000,1500,2000)--->any:大于最小

求小于集合B中任意值的员工工资
select * from emp where sal<any(1000,1500,2000)---<any:小于最大

求等于集合B中所有值的员工工资
select * from emp where sal=all(1000,1500,2000)---=all:等于所有

--求工资和佣金一致的员工信息
select * from emp where sal=all(select sal,comm from emp)

求等于集合B中任意值的员工工资
select * from emp where sal=any(1000,1250,2000)---=any:等于任意

8.EXISTS

存在,后面集合中含有数据，执行前面的查询语句
A exists(B)
当B返回空值,则整个结果为空,当B返回非空,则输出A的值

  select * from emp where exists(select 1 from dual )
select sal from emp where exists(select 1 from dual where 1=0)
select * from emp where not exists(select 1 from dual ) ---not exists 与exists相反

in和exists执行结果一样，但是执行的是顺序不一样，in先执行的是首先会执行from语句找出student表，然后执行 in 里面的子查询，再然后将查询到的结果和原有的user表做一个笛卡尔积，再根据我们的student.stuid IN score.stuid的条件，将结果进行筛选（既比较stuid列的值是否相等，将不相等的删除）。最后，得到符合条件的数据

Exists 先执行的是查主查询的表，然后，根据表的每一条记录，执行以下语句，依次去判断where后面的条件是否成立：

1. 比较使用 EXISTS 和 IN 的查询。注意两个查询返回相同的结果。
   

select * from TableIn where exists(select BID from TableEx where BNAME=TableIn.ANAME)
select * from TableIn where ANAME in(select BNAME from TableEx)

2. 比较使用 EXISTS 和 = ANY 的查询。注意两个查询返回相同的结果。

select * from TableIn where exists(select BID from TableEx where BNAME=TableIn.ANAME)
select * from TableIn where ANAME=ANY(select BNAME from TableEx)

9.模糊查询

1). Like：
通配符: % ,’_’
转义符关键字：escape‘可以是任意字符，一般是/’

 select * from emp where ename like '/%%/%%/%%/_' escape '/' and ename like '/%%/_%/_%/_%/_'  escape '/'

做模糊查询时,尽量保证不以%开头,如果以%开头,用instr替换like

%:表示任意长度的字符 _:表示一位长度的字符

2.INSTR

SELECT * from EMP where INSTR(job,'A')>=1(标识从第一位开始大于第一位A的其他A)
SELECT INSTR('ALLEN','L',2,2) FROM EMP （表示从第二位L，开始查出来的第二位以后）

10.集合查询

1.并集：
UNINO：将两个查询语句的结果并到一张表中，去重
UNINO ALL：将两个查询语句的结果并到一张表中，不去重（查询结果与集合的顺序无关，但是数据展示会第一个集合优先）
两个sql查询语句，将两个数据并到一张表中，字段名以第一个查询为主
前后两个语句的字段和数据类型要一致否则报错。

2.交集

关键字：INTERSECT

两个sql查询语句，求两个集合中共有的部分，字段名以第一个查询语句为主

3.差集

关键字：MINUS

两个sql查询语句，将第一个数据聚合中含有第二个数据集合的数据去除，字段名以第一个查询语句为主（要注意查询语句的前后顺序）

四. 排序分组

1.语法

 SELECT 列名 FROM 表名 WHERE 条件  GROUP BY 列名 HAVING 条件 ORDER BY 列名

2.执行顺序

 FROM->WHERE->GROUP BY->HAVING ->SELECT ->ORDER BY  

3.分组中SELECT 后的字段

1).group BY后含有的字段
2).聚合函数/分组函数
4.分组中GROUP BY后的字段
1).表中的字段
2).函数，运算
3).不能加别名

4.分组中GROUP BY后的字段

1).表中的字段
2).函数，运算
3).不能加别名

5.分组中HAVING后的字段

1).聚合函数
2).group BY后含有的字段

6.排序ORDER BY后的字段

1).表中的字段
2).函数 运算
3).数字(不能超出SELECT 后字段个数)
4).ROWID
5).别名
6).ROWNUM

五 ROWID/ROWNUM

1.ROWID

数据库自动为表生成一个用于存储每行数据的物理地址的列，列名为ROWID

特点：
1.18位不重复的字符串
2.查询速度比较快，多出一步获取rowID值的过程

2.ROWNUM

伪列，按照查询结果生成的一组由1递增的数组
–在条件比较时，ROWNUM值不能大于或等于一个比1大的数值

六 sql数据过滤的几种方法

Sql数据过滤的几种方法（ where,having,逻辑运算符,比较运算符，通配符过滤，）
Sql过滤重复数据的几种方法（distinct（select distinct * from table(表名) where (条件)），group by）

七TO_DATE/TO_CHAR

转换格式 :to_date 就是非日期类型转为日期型
To_char就是非字符串类型转为字符串