数据查询语言
相关的操作：
1.sqlplus访问数据库命令，和数据库建立连接的命令：
sqlplus 用户名/密码
2.显示当前用户的用户名：
show user
3.改变身份：
conn（ect）用户名/密码
特殊：sys用户，要指明以什么身份登录[AS SYSDBA/SYSOPR]。
说明：
一旦使用了超级管理员登录的话，可以通过命令手工的控制数据库实例的打开和关闭：
shutdown immediate;
一旦关闭后，用户无法直接连接sqlplusw命令，此时可以：
A.先使用“/nolog”登录；
B.使用管理员登录：conn sys/SMITHe_on_install as sysdba;
C.然后启动数据库：startup,就正常连接了。
4.sqlpus命令窗口也可以调用本机的操作系统命令，使用“host”作为前缀。
例如：host copy c:\test1.sql c:\test2.txt;
一．单表查询语句
(1).查询系统表：（查询本用户下所拥有的所有表的表明）
select table_name from user_tables;
scott用户下有四张表，使用：
select * from tab; 查询所有的数据表的名称。
(2).查看表的结构：
desc 表明
1．查询某张表的所有数据：
select [distinct] //可无
*{col_n ame1,c ol_n a me2,…}
from tb_name;
一般不要写*，有多少就具体写出来。
2.对指定的列的所有值进行算术运算：
select col_name 运算符数字
from tb_name;
例：select id,last_name,(salary+100)*12
from s_emp;
3.给查询的列取别名：
select old_colunm [as] new_column_name
from tb_name;
例：select id,last_name,salary*12 annual
from s_emp;
4.变换查询显示：（尽量别使用中文）
select job 工作,sal 工资 from scott.emp;
5.使用||可以使得多列的值或者列和特殊的字符串合并到一个列进行显示：
select col_name||’spe_char’||col_name
from tb_name;
例：select id,first_name||’’||last_name||’,’||title name from s_emp;
6.对null值进行替换运算（nvl是通用函数的一种，见单行函数）：
select nvl(col_name,SMITHe_value)
from tb_name;
例：select id,last_name,nvl(commission_pct,0) commission_pct from s_emp;
7.用distinct关键字，可以将显示中重复的记录只显示一条：
select distinct col_name,col_name…
from tb_name;
例：select distinct title,dept_id
from s_emp;
注意1：distinct 关键字只能放在select关键词后面。
如：select id,distinct title from s_emp;错误！！！
注意2：distinct关键词后面如果出现多列，表示多列联合去重，即多列的值都相同的时候才会认为是重复的记录。
1 2 1 2
1 3 结果：1 3
2 4 2 4
2 4
7，sqlplus命令
a:在当前操作的命令行追加内容。
c:在当前操作的命令行修改内容，只能修改一次，修改第一个。
clear buffer：清空当前缓存
del:删除当前操作行。
del line_num,删除指定行。
i:当前操作命令行的下一行插入内容。
l:查看缓存命令
l line_num:查看指定的命令行。
n text:替换第n行的整行内容。
！：后面接终端命令。
！clear：清屏
8.sqlplus设置：
set pause on回车响应，分屏显示，只在本会话中有效。 ctrl+c 停止显示。
set pause off关闭分屏显示。
set pause “please enter key:”设置分屏显示的提示信息。
set head off 提头输出关闭。
set feed off结尾输出关闭。
set echo off回写关闭。
set linesize num; 设置每行显示的字节数。
set pagesize 30; 设置每页显示的行数。
9.文件命令：
没有“；”号
save file_name:将缓存命令（即就上一条）保存到file_name中。
get file_name:将文件中的内容提取到sqlplus。
start file_name:执行文件中的sqlp命令。
@filename:同上。
edit file_name:使用终端设置好的vi编辑器编辑文件。
spool file_name:将接下来的sql语句以及sql的运行结果保存到文件中。
直到遇到spool off 关闭spool功能。
例：sqll
result1
exit:退出。
10.column命令：
column命令列格式的定义
·column 目标列名 //查看这个列是否定义了格式
·column 目标列名 format a.. //设置列宽
·column last_name heading ‘Employee|Name’ format A15 //设置题头
‘|’是换行符。
·column salary justify left format $99,999.00 //定义数字显示格式注意：如果不满足显示的格式，就会把数据显示为“#”。·column salary justify left format $00,000.00会出现$00,928.00,用0补。
·column 列名clear（清除列格式定义）
·$99,999.00中的“$”: 加$；
“L”: 使用本地货币符号；
“.”: 意思是小数计数显示；
“，”: 表示千分位显示。
查看列是否定义了格式：
设置列宽：
取消列的格式定义：
设置题目：column last_name heading ‘Employee|Name’format A15。‘|’为换行。设置后用select empno from scott.emp;查看，如果起了个别名查看，select empno Em from scott.emp;此时看到的是没有格式的原来的内容。
注意：这个很像前面的起别名：select empno Em from scott.emp;但是起别名没有引号的效果，也不能换行，仅仅是起了别名。
定义数字显示格式：--左对齐（left）
设置格式中不够的补0：column salary justify left format $00,000.00
二．选择操作
1.order by排序查询
升序：ASC 降序：DESC
语法：order by 目标列名（别名）排序顺序（不写则默认升序）
注意：有where时，order by在where后面。其实order by是在语句的最后的。2．where子句（条件查询）
(1)单条件查询：
select job,sal from scott.emp where job=’MANAGER’;
select job,sal from scott.emp where sal<=1600;
单条件查询使用的比较运算符表：
说明：
1.like和not like适合于字符型字段的查询，%代表任意长度的字符串，
_下划线代表一个任意的字符。like ‘M%’代表M开头的任意长度的字符
串，like ‘M_’代表M开头的长度为3的字符串。
在开发之中，数据可的模糊查询肯定使用LIKE子句，但是在使用LIKE子句的时候有一个最大的注意点：如果在模糊查询上不设置任何
的查询关键字的话（‘%%’），则表示查询全部记录。这个特点可以帮
助用户节约很多的代码，一定记住！！！！
·下面的例子可以实现：N出现在第三个位置的长度不小于5的ename；
·下面的可以实现：ename中含有“_”（下划线）的名字。用转义字符
和escape ‘\’。
select deptno,job,ename,LENGTH(ename) from scott.emp where job
like '%\_%' escape ‘\’;
2.between and不仅能对数值型操作，还能对日期型操作。是闭区间[].
3.is (not)null的用法：
4.in（在列表）指定一个范围的操作。
如：查询empno=7369，empno=7566，empno=7799的员工。
7799没有就不显示了。
注意：如果现在使用了in操作符，查询的范围之中存在了null，不影响查询；
如果现在使用的是NOT IN ，查询的范围之中存在null（表示查询全部数据），则不会有任何的数据返回。
(2)组合条件的查询：
select job,sal from scott.emp where job<=’MANAGER’and sal<=2000;
select job,sal from scott.emp where job<=’MANAGER’or sal<=2000;
select job,sal from scott.emp where not job=’TOM’ ; //not job即job<>’TOM’。
3.分组查询：
分组查询是指将查询结果按照某列或者某些列相同的值进行分组，然后对改组的数据进行组函数运用。
语法：select…
from…
where…
group by col_name,col_name
having …
order by…
顺序不能改变。
group by col_name:即将数据按照col_name相同值进行分组
组函数常见的5个：
avg:求平均数
count：求总数
max:最大值
min:最小值
sum:求和
1》【avg】函数
用法：avg(字段名) 求平均值。要求字段为数值型。
select dept_id,avg(salary) dept_salary
from s_emp
group by dept_id
order by dept_salary desc;
工作原理：先将相同的id分组，然后对其中的salary分别求和，随后按avg(salary)降序排列。
2》【count】函数
用法：count(字段名) 或count(*) 统计总数。
注意：
COUNT()函数的主要功能是进行数据的统计，但是在进行数据统计的时候，如果一张表中没有统计记录，COUNT()也会返回数据，只是这个数据是“0”。
如果使用的是其他函数，则可能返回null，但是COUNT()永远都会返回一个具体的数字，这一点以后在开发之中都会使用到。
3》【max】函数
用法：max(字段名) 计算数值型字段最大数。
4》【min】函数
用法：min(字段名) 计算数值型字段最小数。
5》【sum】函数
用法：sum(字段名) 计算数值型字段的总和。
select job,sal from scott.emp group by sal,job having sal<=2000;
select job,sal from scott.emp where sal<=2000 group by job,sal;
注意： 1.没有出现在group by子句中和组函数中的列，不能出现在select子句中：
如：
select dept_id,last_name,max(salary)
from s_emp
group by dept_id;
（错误）并不能找到工资最大的员工名字。last_name不能存在。
2.当group by子句中出现多列的时候，表示按照从左到右的顺序进行分组，即按照第一列分组，然后在第一列分号的组里面，按照第二列进行分组，以此类推。
3.如果限制条件中出现了组函数，该条件必须放到having子句中，不能放在where子句中，此时其实where已经管不着了。如：avg(salary)>1000 select dept_id ,avg(salary)
from s_emp
group by dept_id;
having avg(salary)>1000;
4.where在执行group by之前的检测每条记录是否符合条件，having是
执行group by之后，再次检查分组后的各组是否满足条件，having语句只能配合group by 语句使用，没有group by 时不能使用having，但可以使用where。
二．多表查询：尽可能不要用多表查询，用单表性能很差，因为数据量大。
多表查询，又称表联合查询，是相对于单表而言的，指从多个数据表中查询数据，下面主要是从两个表查询数据。
多表查询连接方式：
1》等值连接
2》不等值连接
3》外连接
4》自连接：
5》集合连接
注意：一定要指明查的内容是哪张表的，不能直接写ename,deptno等。除非这个内容在一张唯一的表中。
1.无条件多表查询：
是将各表的记录以“笛卡尔”积的方式组合起来。
例如：scott.emp有14条记录，scott.dept有4条，那么它们的“笛卡尔”
积就是4*14=56条记录。即使只查一个字段，也会显示56条记录。
2.等值多表查询：
等值多表查询将按照等值的条件查询多个数据表中关联的数据。要求关联的多个数据表的某些字段具有相同的属性，即具有相同的数据类
型、宽度和取值，要不没办法比较是否等值。
3.非等值多表查询：
where，运用上面的比较运算符表来组合查询条件。
注意：如果表的名称很长，例如：”yinheng_yazhou_zhongguo_shanghai_ren”,
所以在进行多表查询的时候往往都会先为表起个别名，然后通过“别名.字段”的方式进行查询。
4.外连接---能显示的很全
当一个表的记录在另外一张表中不存在的时候，我们依旧需要显示，使用外连接
需求：查看s_customer中所有客户的id和名字以及该客户所对应的负责
员工的id，last_name表之间的连接列
s_customer 表中的sales_rep_id
s_emp 表中的id
但是发现一张表中多出的内容也不显示了，如果要显示，则使用左、右连接。
左、右连接
关于左、右连接指的是查询判断条件的参考方向，例如：
select * from emp e,dept d where e.deptno=d.deptno;
查询结果少了40这个部门，因为在雇员表之中没有一条记录是属于40部门的，所以现在不会显示40部门的信息，即：现在的查询以emp表为参考，那么如果说现在非要显示40部门，就必须改变这种参考饿方向，就需要使用左、右连接。
select * from emp e,dept d where e.deptno（+）=d.deptno; 就会出现40部门。“（+）”就用于左、右连接的更改，这种符号有以下两种使用情况：·（+）=：放在等号左边，表示右连接；
·=（+）：放在等号右边，表示左连接。
意义：右（左）外连接表示右（左）边表（=号右（左）边的表）中的记录在左（右）边表中不存在的时候，右（左）边表的记录依旧显示。
不用刻意区分左还是右，只是根据查询结果而定，如果发现有些需要的数据没有显示出来，就使用此符号更改连接方向。
这个符号是Oracle数据库独有的，其他数据库不能使用。如果想使用，就看下面的…
5.自连接：
实质就是一张表当多张表用，即一张表中的某列的值取决于自己的某一列。
语法：给同一个表取不同的别名就行。
需求：查看每一个员工的经理的名字。
6.集合连接
union：将上下结果取并集，去除掉重复的记录（重复的只显示一次）。
union all:将上下结果全部显示，包括重复的。
minus：取差集
intersect：取交集
union：
select https://www.doczj.com/doc/867013990.html,st_name,https://www.doczj.com/doc/867013990.html,
from s_emp e,s_dept d
where e.dept_id(+)=d.id
union
select https://www.doczj.com/doc/867013990.html,st_name,https://www.doczj.com/doc/867013990.html,
from s_emp e,s_dept d
where e.dept_id=d.id(+)
minus：
select last_name,salary
from s_emp
where rownum<=10
minus
select last_name,salary
from s_emp
where rownum<5
rownum是记录行号。
intersect:
select last_name,salary
from s_enp
where rownum<=10
intersect
select last_name,salary
from s_emp
where rownum<5
7.SQL:1999语法
除了以上的表连接操作之外，在SQL语法中，也提供了另外一套用于表连接的操作SQL，格式如下：
(1).交叉连接（CROSS JOIN table）:用于产生笛卡尔积。
select * from emp CROSS JOIN dept;
笛卡尔积本身并不是属于无用大的内容，在某些情况下还是需要使用的。
(2).自然连接（NATURAL JOIN）：自动找到匹配的关联字段，消除掉笛卡尔积。
select * from emp NATURAL JOIN dept;
但是，并不是所有的字段都是关键字段，设置关联字段需要通过约束指定。
(3).JOIN…USING子句：用户自己指定一个消除笛卡尔积的关联字段。
select * from emp JOIN dept USING(deptno);
(4).JOIN…ON子句：用户自己指定一个可以消除笛卡尔积的关联条件。
select * from emp JOIN dept ON(emp.deptno=dept.emptno);
(5).连接方向的改变：
select * from emp RIGHT OUTER JOIN dept ON(emp.deptno=dept.deptno);
在Oracle之外的数据库都使用以上的SQL：1999语法操作，所以这个语法必须会一些。
再次强调：多表查询的性能肯定不高，而且性能一定要在大数据量的情况下才能够发现。
三．用SQL进行嵌套查询
在select查询语句里可以嵌入select查询语句，称为嵌套查询。也称为“子查询”，子查询形成的结果又成为父查询的条件。
子查询可以嵌套多层，子查询操作的数据表可以使父查询不操作的数据表。子查询中不能有order by分组语句。
例如：查看工资大于SMITH员工工资的所有员工的empno和ename。分析步骤：
第一步：确定需求
第二步：写父句，写子句
父句：select empno,ename,sal
from scott.emp
where sal>?;
子句：select sal
from emp
where ename='WARD'
第三步：嵌套
select empno,ename,sal
from scott.emp
where sal>
(select sal from emp where ename='WARD');
1.简单嵌套查询
等价于：
select sal from scott.emp where ename=’W ARD’，得出sal=1250,
select emp.empno,emp.ename,emp.job,emp.sal
from scott.emp
where sal>=1250;
2.带【in】的嵌套查询
查询薪水和W ARD相等的员工。
3.带【any】的嵌套查询
等价于：
select sal from scott.emp where job=’MANAGER’;
4.带【some】的嵌套查询
等价于：
select sal from sal from scott.emp where job=’MANAGER’；
any和some的嵌套查询功能是一样的，any是早期的。
5.带【all】的嵌套查询
等价于：
select sal from scott.emp where job=’MANAGER’;
6.带【exists】的嵌套查询
7.并操作的嵌套查询---union
即并集，A或B元素的总和（相同的元素只显示一次）。
8.交操作的嵌套查询---intersect
交操作就是集合中交集的概念。属于A且属于B元素的总和。
9.差操作的嵌套查询---minus
就是集合中差集的概念，属于A且不属于B的元素总和。
注意：并、交和差操作的嵌套查询要求属性具有相同的定义，包括类型和取值范围。
练习：
练习1：查询工资大于smith所在部门平均工资的员工的
select sal,ename
from scott.emp
where sal>(select avg(sal)
from scott.emp
where deptno=(select deptno from emp where ename='SMITH')
group by deptno);
练习2：查看部门平均工资大于30号部门平均工资的部门deptno.
select deptno,avg(sal)
from scott.emp
group by deptno
having avg(sal)>
(select avg(sal)
from scott.emp
where deptno=30);
练习3：查看部门和名字为SMITH的部门相同或者区域ID为2的部门相同的部门所有员工id和名字
父句：
select id,last_name,dept_id
from s_emp
where dept_id = ?
or
dept_id in ?;
子句1：
select dept_id
from s_emp
where last_name = 'SMITH'
子句2：
select id
from s_dept
where region_id = 2;
练习：
1.查看薪资大于SMITH员工薪资的员工信息
2.查看薪资大于SMITH员工薪资或者所在部门在3号区域下的员工的信息
3.查看薪资大于SMITH所在区域平均工资的员工信息
4.查看薪资高于SMITH员工经理薪资的员工信息
5.查看薪资大于SMITH员工经理的经理所在区域的最低工资的员工的信息>2450
6.查看客户负责员工中工资大于SMITH员工的工资的员工信息
练习: