Oracle学习笔记--day03

Bertha 。 2022-06-17 14:13 298阅读 0赞

oracle的scott用户的表的结构：  
dept 部门表：  
DEPTNO  NUMBER(2) pk 表示部门编号，由两位数字所组成   
DNAME VARCHAR2(14) 部门名称，最多由14个字符所组成  
LOC VARCHAR2(13) 部门所在的位置  
emp 雇员表：  
EMPNO NUMBER(4) pk 雇员的编号，由四位数字所组成  
ENAME VARCHAR2(10) 雇员的姓名，由10位字符所组成  
JOB VARCHAR2(9) 雇员的职位  
MGR NUMBER(4) fk 雇员对应的领导编号，领导也是雇员 manager  
HIREDATE DATE  雇员的雇佣日期  
SAL NUMBER(7,2) 基本工资，其中有两位小数，五倍整数，一共是七位 salary  
COMM NUMBER(7,2) 奖金，佣金 commision \[kəˈmɪʃn\]   
DEPTNO   NUMBER(2) 雇员所在的部门编号  
salgrade 工资等级表：   
GRADE NUMBER 工资的等级  
LOSAL NUMBER 此等级的最低工资  
HISAL NUMBER 此等级的最高工资  
  
\*bonus 奖金表：  bonus  \[ˈbəʊnəs\]   奖金，额外津贴;   
ENAME   VARCHAR2(10)    雇员姓名  
JOB     VARCHAR2(9)     雇员工作  
SAL     NUMBER          雇员工资  
COMM    NUMBER          雇员奖金  
               
查询  
简单查询  
select 查询的内容   
from 查询的目标  
where 查询的条件  
  
对每一行应用查询条件，查询条件结果为true时，才会去获取要查询的内容，生成结果集中的一行  
一行一行的慢动作  
  
查询的内容  
用于指定选择哪些内容返回  
  
\*   
所有的列  
当使用一个单独的\*作为查询项时，不允许再指定其他的查询项  
当前查询涉及的表的所有的列  
某个表的所有列 t.\* 以表名或表别名作为前缀来限定哪个表的所有列  
列名  
明确指定要查询的列  
列名 \[as\] 别名  
给列取别名  
as关键字可省略  
如果别名中出现空格、关键字,必须用""引起来  
  
例：  
select  empno "雇员 编号",ename as 姓名,sal 工资,hiredate "date" from scott.emp  
  
常量  
对于查询结果中的每一行中的这一列的值是：该常量  
distinct   
去除查询结果中，完全一样的行，只留一行  
||  
字符串连接运算符  
字符串是用单引号引起来的  
例：  
用以下的格式显示员工信息：工号是:xxxx,姓名是:xxxx,工资是:xxxx,雇佣日期是:xxxx  
select '工号是:'|| empno "雇员 编号",'姓名是:'||ename as 姓名,'工资是:'||sal 工资,'雇佣日期是:'||hiredate "date" from scott.emp  
  
\+ - \* /  
算术运算符  
例：  
假设税率为总工资的10%，查出每个员工应交的税是多少元  
select ename,(sal + comm) \* 0.1 tax from emp;   tax 税额  
null参与的任何运算，结果都为null，后面讲函数再解决  
函数  
对于查询结果中的每一行中的这一列的值是：取决于函数的返回值  
子查询  
对于查询结果中每一行这一列的值是：取决于select子查询的查询结果  
由于是作为一行一列的值，所以该子查询最多只能返回一行一列  
  
  
子查询  
select语句中的select语句  
子查询要用()括起来  
可以给子查询取别名  
  
关联子查询  
内层查询需要依赖外层查询的字段才能运行  
按照自外而内的顺序运行  
oracle的文档的解释:  
内层查询会引用外层查询中的一列或者多列，这种子查询之所以称为关联子查询，是因为子查询的确和外部查询有关，查询的结果需要依赖于外部查询中包含的每一行中的值  
  
例：  
查询编号为xxx员工的所有信息和工资等级  
select emp.\*,(select grade from salgrade where emp.sal >= losal and emp.sal <= hisal) salgrade  
from emp   
where empno = 7369;  
  
嵌套子查询  
内层子查询不需要依赖外层查询的字段就可以运行，外层查询需要依赖内存查询的结果才能运行  
自内而外的顺序运行  
例：  
select emp.\*,(select 1 from dual) dummy from emp;  
select \* from (select \* from emp);  
仅仅为了演示  
select \* from emp where deptno = (select deptno from dept where dname = 'ACCOUNTING');  
  
查询的目标  
用于指定从哪儿查  
  
表名  
表名 别名  
给表取别名  
如果别名中出现空格、关键字,必须用""引起来  
子查询  
从一个子查询的查询结果中，再去检索  
  
查询的条件  
运算符  
比较运算符  
> >= < <= !=、<> =    
between x and y 等价于 >=x and <=y  
逻辑运算符  
and or not  
算术运算符  
\+ - \* /  
模糊匹配  
like   
%  
0个或多个任意字符 '%a%' 'a' 'aaa' 'axxx' 'abc' 'bac'  
\_  
1个任意字符 '\_a' 'xa'  
null判断  
在数据库中，null = null、null != null --> false  
is null  
is not null  
列表  
in (val1, val2,...)  
子查询  
&no 从外部输入值，no是提示信息  
单行函数  
默认为ture  
  
例：  
查询出工资大于1500同时有奖金的雇员信息  
select \* from scott.emp where sal>1500 and comm is not null  
    
查询出工资大于等于1000同时小于等于3000的雇员信息  
select \* from scott.emp where sal>=1000 and sal<=3000;  
select \* from scott.emp where sal between 1000 and 3000  
查询出在1981年入职的雇员信息  
select \* from scott.emp where hiredate between '1-1月-81' and '31-12月-81'  
select \* from scott.emp where hiredate like '%81'  
查询出名字是SMITH,ALLEN,SCOTT的雇员信息  
select \* from scott.emp where ename='SMITH' or ename='ALLEN' or ename='SCOTT'  
select \* from scott.emp where ename in('SMITH','ALLEN','SCOTT')  
  
查询排序  
order by 列名1 ,列名2,...  
先根据列1的值排序，当该列的值相等时，根据列2的值排序,...  
order by 列名1 asc, 列名2 desc ...   
默认asc  
例：  
select ename,sal from scott.emp order by sal,ename  
  
分组查询  
group by 列名1,列名2,...  
列1,列2,...的值都一样的行，会被当成一组  
在执行select子句时，对于每一个组，对应生成查询结果中的一行  
在执行select子句之前，在中间过程的虚表中，还是以一组多行的形式存在的  
在分组查询的select子句中，最终只能出现作为分组条件的列、组函数，  
因为  
对于某个组，会对应生成查询结果中的一行  
在一个分组中，作为分组条件的列的值都是相等的，所以对应于查询结果中，该列的值是确定的  
组函数会把一个分组中，某个列的所有值作为输入，输出一个结果  
  
如果要查询的列，不是作为分组条件的列，这个列的值对于一个组中的每一行来说可能各不相同，  
所以在生成查询结果中的一行的时候，该列的值就无法确定  
  
MySQL中：  
如果要查询的列，不是作为分组条件的列，这个列的值对于一组中的每一行来说可能各不相同，  
默认显示查到的第一行数据的这些列的值  
但在逻辑上是不对的  
  
having 条件  
对分组结果，进行过滤  
having子句中只能出现作为分组条件的列、组函数  
例：   
按部门来分组，查询出部门的总人数，最高工资以及部门名称  
select count(1),max(e.sal),d.dname  
from scott.emp e,scott.dept d  
where e.deptno=d.deptno  
group by d.dname  
  
函数  
单行函数  
按行处理  
输入一行中的某一列的值，输出一个值  
select子句、where条件中  
1.数字函数  
abs(n)      
返回数字n的绝对值  
power(m,n)  
m的n次方  
mod(m,n)    
m除以n的余数, + - \* /  
ceil(n)     
ceil天花板  
大于等于n的最小整数   
floor(n)    
floor 地板  
小于等于n的最大整数   
round(m\[,n\])  
四舍五入，n四舍五入的位数  
n  
正数，小数后第n位后的数字要四舍五入；  
负数，表示从小数点前第n位开始舍入  
trunc(m\[,n\])  
截断数据，n截断到第几位，负数表示截断到小数点之前第几位  
sqrt(n) n的平方根  
正余弦、正余切、对数  
2.字符串函数  
concat(str1,str2)  
连接两个字符串  
|| 字符串的连接运算符  
initcap(str)  
用于将字符串str中每个单词的首字母大写，其余字母小写，每个单词用空格分隔  
instr(str1,str2)  
子串str2在str1中第一次出现的位置，  
字符串中字符位置从1开始，  
返回0表示不存在  
length(str)  
返回字符串字符的个数  
不是字节数，注意char，一个空格字符一个字节  
str如果为''、null，则length函数返回null  
lower(str)   
转小写  
upper(str)   
转大写  
  
replace(str, serach, replace)  
用replace替换str中的所有search子串  
str 原始字符串  
searching 要查找的子串  
replace 要替换成的子串  
substr(str, m, n)  
获取子串，  
m取的第一个字符的位置，n取的字符的个数；  
字符位置从1开始  
m为负数的时候，从字符串末尾开始数m个位置，从该位置开始取，从前往后取n个  
trim(c from str)  
删除首尾的所有字符c  
c只能是一个字符  
没有字符类型，想表示字符用只包含一个字符的字符串来表示  
3.日期时间函数  
add\_months(d,n)  
用于获取指定日期d之前或之后n个月的日期时间  
months\_between(d1,d2)  
返回两个日期之间的月份差  
add\_months(sysdate,1);  
current\_date  
current\_timestamp  
last\_day(d)  
返回指定日期的月份的最后一天的日期时间  
  
日期算数运算  
允许日期 - 日期，两个日期之间的天数差  
允许日期 - 整数，减去相应的天数  
不允许日期 + 日期  
允许日期 + 整数，加上相应的天数  
  
  
4.转换函数  
to\_date  字符串转date  
to\_timestamp 字符串转timestamp  
to\_char  日期时间(date、timestamp)转字符串  
to\_char 数字转数字格式符串  
123456.789  
￥123456.789  
$123456.789  
123,456.789  
￥123,456.789  
$123,456.789  
  
to\_char(number,format)  
format:  
9 代表一个数字  
$ 美元符号  
('$123,321','$999,999')  
L 本地货币符号  
. 用于匹配一个小数点  
, 用于匹配一个,，不能用于匹配小数位  
  
to\_number(str,format)  
将一个数字格式的字符串，转换成数字number  
format:  
x,表示16进制数的1位  
('11','xx')用于将16进制数的字符串，转成10进制数  
  
5.通用函数(参数类型没有限制)  
null与任何数据进行算术运算的时候，结果都是null  
nvl(expr1,expr2)  
如果expr1为null,返回expr2，  
如果expr1不为null，返回expr1  
  
例：员工税额  
  
nullif(expr1, expr2)   
expr1 == expr2 ? null : expr1  
nvl2(expr1, expr2, expr3)   
expr1 == null ? expr3 : epxr2  
  
decode(expr,case1,result1,case2,result2,....,defaultValue)  
根据expr，查找匹配的searchx，返回对应的resultx，  
一旦找到匹配的值，则直接返回对应的result  
不会再继续往下查找  
如果没有找到匹配的，则返回defaultValue  
如果没有指定defaultValue，返回null  
  
组函数(多行函数)  
按组处理  
输入一个组中(所有行的)某个列的所有值，输出一个结果  
sum 和  
avg 平均数  
count 个数  
count(\*)    返回行的个数  
count(列名) 返回列的值的个数，会忽略null count(常量)  
  
max 最大数  
min 最小数  
组函数的参数是列名时，组函数会忽略值为null的数据  
如果select子句中使用了分组函数，但语句中没有使用group by指定作为分组条件的列，则会把当前查询出的所有行作为一组，且没有作为分组条件的列  
例：  
查询出雇员表中雇员的人数，平均工资，最高工资，最低工资  
select count(1),avg(sal),max(sal),min(sal) from scott.emp  
  
create table t\_student\_a(name varchar2(20),course varchar2(20),score number(3));  
  
  
insert into t\_student\_a values('zhangsan','Chinese',78);  
insert into t\_student\_a values('lisi','Chinese',88);  
insert into t\_student\_a values('zhangsan','Math',87);  
insert into t\_student\_a values('lisi','Math',94);  
insert into t\_student\_a values('zhangsan','English',90);  
insert into t\_student\_a values('lisi','English',89);  
commit;  
  
select \* from t\_student\_a;  
  
  
Oracle中执行sql脚本：@路径  
  
课程名显示为中文  
name course score  
zhangsan  语文 78  
zhangsan  数学 87  
zhangsan  英语 90  
lisi 语文 88  
lisi 数学 94  
lisi 英语 89  
  
select name,decode(course,'Chinese','语文','Math','英语','English','英语') as course,score  
from t\_student\_a;  
  
  
列转行  
name course score  
zhangsan  语文 78  
zhangsan  数学 87  
zhangsan  英语 90  
lisi 语文 88  
lisi 数学 94  
lisi 英语 89  
\-->  
name   语文   数学  英语  
zhangsan    78     87    90  
lisi      88     94    89  
  
从效果上来看，把某一列中的值，显示在同一行中  
如  
course列的值显示为标题行  
score列的值，显示在了同一行  
  
从本质上来看，3行记录合并成1行记录  
分组  
单行函数会针对每个分组中的每一行进行处理，一个组中可能会有多行，会有多个结果，可继续使用组函数处理，null值会被忽略  
  
select name,max(decode(course,'Chinese',score)) 语文,sum(decode(course,'Math',score)) 数学,sum(decode(course,'English',score)) 英语  
from t\_student\_a  
group by name;  
  
行转列  
name   语文   数学  英语  
zhangsan    78     87    90  
lisi      88     94    89  
\-->  
name course score  
zhangsan  语文 78  
zhangsan  数学 87  
zhangsan  英语 90  
lisi 语文 88  
lisi 数学 94  
lisi 英语 89  
  
把某一行中的值，显示在同一列中  
  
1行记录拆分成3行  
先单独查出，所有学生各个学科的成绩，查的时候知道查的是哪门课的成绩，可以加个常量，作为课程名这一列的值  
然后将每次查出的结果合并在一起  
select name,语文 score, 'Chinese' course from t\_student\_b  
union  
select name,数学 score, 'Math' course from t\_student\_b  
union  
select name,英语 score , 'English' course from t\_student\_b;  
  
查询结果集的集合运算  
\{1, 2,3\} ∩ \{5, 2 ,3\} -->交集：取出两个集合共有的元素  
\{1,2,3\} ∪ \{3, 2,1,4\}-->并集：元素合并，去除重复元素，保留1条  
\{1,2,3,4\} - \{5,6\} -->补集：\{1,2,3,4\}，差运算  
\{1,2,3,4\} - \{1,2,3\} -->补集：\{4\}  
intersect 两个结果集的交集运算   
union 并集  
union all 并集，但保留重复行  
minus 差，补集  
  
用于把多个单独查询的多个结果集，合并成1个结果集  
select1 intersect select2  
参与集合运算的两个结果集的对应的列要兼容：  
列的个数  
对应列的类型要兼容：date、timestamp|varchar2、char  
集合运算后的结果集，列名以指定的第一个结果集的列名为准  
  
  
多表查询(连接查询)  
需要查询的数据，在一个表中，查不全，数据分布在多个表中  
不想查多次，只想查一次，就能获得到所有需要的数据  
  
create table t\_dept (  
 id number primary key,  
 name varchar2(60)  
);  
create table t\_emp(  
 id number primary key,  
 name varchar2(60),  
 age number(2),  
 sex char(2),  
 dept\_id number references t\_dept(id),  
 leader\_id number references t\_emp(id)  
);  
  
多表查询(连接查询)指的是把多个表的多条记录，连接成一条记录，从而把多个表，连接成一个表(中间过程中的虚表)  
  
SQL99语法：  
\[left\_table\] <join\_type> join <right\_table> on <join\_condition>  
不指定连接条件的话，得到的结果集是两个结果集的笛卡尔积：  
两个结果集中的记录，分别两两组合  
得到的数据，在逻辑上是不正确的  
所以一般都需要指定连接条件  
Oracle：  
必须指定，否则语法错误  
MySQL：  
可以不指定，得到的是笛卡尔积  
外连接  
left|right|full \[outter\] join ... on   
左外  
依然会查出左表中不满足连接条件的记录  
不满足连接条件  
意味着另一个表中，没有与之对应的记录，连接后，另一个表的对应记录字段值全为null  
outter可省略  
右外  
依然会查出右表中不满足连接条件的记录  
全外  
依然会查出左右表中不满足连接条件的记录  
内连接  
\[inner\] join  
只会查出满足连接条件的记录  
inner可省略  
  
  
SQL92语法：  
内连接：  
from t1, t2, t3...  
where t1.xx\_id = t2.yyy\_id and t1.xx\_id = t2.zzz\_id  
  
把需要查询的表放在from后用逗号分隔  
连接条件在where子句中指定  
外连接：  
(+)，表示外连接，  
在(+)所在的那个列的表中加一个所有列的值都为null的万能行，用于和另外一个表中所有不满足连接条件的行进行连接  
MySQL不支持这种外连接语法  
不好区分左外、还是右外，所以不区分  
  
SELECT语句完整语法：  
(7)select <select\_list>--查询的内容  
  
(1)from <left\_table> <join\_type> join <right\_table> --指定查询的表   
  
(3)on <join\_condition>  --指定连接条件  
  
(2)where <select\_condition> --查询条件  
  
(4)group by <group\_column\_list>--指定分组列  
  
(5)having <select\_group\_condition> --对分组结果的过滤条件  
  
(6)order by <order\_column\_list> --排序列