2022年数据库知识点 .pdf

《2022年数据库知识点 .pdf》由会员分享，可在线阅读，更多相关《2022年数据库知识点 .pdf（15页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、伪表： dual；概念：一个里面没有任何数据，并且不允许你进行插入和删除操作的Oracle自带的表，这个表里只有一个较多 dummy的字符字段并且里面没有任何数据，这个表的最大的作用是用来进行一些相关函数以及运算的测试表；单行函数：1、nvl.2、字符串大写转小写，小写转大写：Upper/Lower；例：把 abcdef转为大写，然后再转为小写：例：查询出 emp表中收入大于 3000的员工信息，其中，信息包括雇员编号、雇员姓名、雇员工作岗位和收入信息，并且要求雇员姓名和雇员工作岗位为小写的英文字母，然后再按照他们的姓名进行升序排序； select empno,lower(ename) ena

2、me,lower(job) job,sal+nvl(comm,0) salfrom emp where sal3000 order by ename3、initcap：名字初始化（首字母大写，其他小写）；例：将 emp表中的 ename都是用名字初始化的形式打印出来；例：将一个完整的英文姓名是用名字初始化来进行打印；4、concat（p1，p2）：字符串连接例：将两个字符串进行拼接： concat与|的区别：5、长度的计算： length例：求出 emp表中姓名的长度；例：求出一个带有汉字的字符的长度；6、截取一段长度： substr名师资料总结 - - -精品资料欢迎下载 - - - - -

3、 - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 1 页，共 15 页 - - - - - - - - - 语法： substr（String，start,length）:表示对 String进行截取，从 start位开始，如果没有 length的话，那么截取直到String结束，如果有 length，那么截取的长度为 length长度；注意，如果 start为正数，则是从 String的左边开始数，如果是负数，则是从 String的右边开始数位数，记住，在 Oracle中，下标是从 1开始；例：对字符串 abcdef进行截取，分别从截取的开始

4、位置从左边开始、从右边开始，没有 length，有length这几种情况来示范；select substr(abcdef,2) from dual;select substr(abcdef,2,3) from dual;select substr(abcdef,-2) from dual;select substr(abcdef,-4,3) from dual;7、替代： replace语法： replace（String,if,then）;表示在 String中寻找 if，如果 if存在，则使用then来进行替代，如果没有 if，则不做任何操作，直接输出String；例：将一个 abcdef

5、gfff的字符串中的 f替换为 *；8、填充： Lpad/Rpad语法： Lpad（String，number，str）：表示使用 number减String长度的空白区域使用 str来进行填充，如果使用 Lpad则填充的区域在左边，如果使用Rpad则填充的区域在右边；我们常用于为了统一长度的场合，比如：银行的存折上的*号键；例：将 emp表中的 job设置为 12个长度，多余的部分使用*来填充；9、去掉首位的空白：trim,ltrim,rtrim语法： trim（需要去空白的字符或字段）名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - -

6、- - 名师精心整理 - - - - - - - 第 2 页，共 15 页 - - - - - - - - - 例： 去掉一个字符串中前后的空白注意：此方法不会去除中间的空白10、查找： instr语法： instr（String，char,number）:查找char这个字符串在 String中第一次出现的位置，如果有 number的话， number可以指定它从哪个地方开始查找，若number为正数，则从左往右数 number个位置再进行搜索，如果为负，则从右往左数；例：select instr(abcdefccc,c) from dual; select instr(abcdefccc,

7、c，4) from dual; select instr(abcdefccc,c，3) from dual; select instr(abcdefccc,cde) from dual; select instr(abcdefccc,c,-3) from dual;练习数字函数：1、数值四舍五入运算：round；语法： round(number1,number2);如果只有 number1的话，那么进行舍去小数点位，进行四舍五入运算，如果有 number2的话，那么就分两种情况，如果为正，则保留小数点右边number2个长度的小数，多余的舍去并做四舍五入运算；如果为负，则把小数点左边的numb

8、er2个长度的数字以 0替代并且舍去小数点后的数字；例：select round(123.556) from dual;-; select round(123.456,2) from dual;-; select round(123.456,-2) from dual;-;名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 3 页，共 15 页 - - - - - - - - - 2、取余运算： mod；语法： mod(number1,number2);使用 number1来对 numb

9、er2进行取余运算；注意，如果 number2是负数的话，结果不会为负，相当于是对number2的绝对值进行取余运算例： select mod(10,2) from dual;-0;3、数字截取： trunc；注意：此函数跟 round方法一模一样，只不过进行的是截取，不是四舍五入；4、绝对值： abs 就是求绝对值呗；日期函数：1、last_day：求出某个日期所在月份的最后一天；语法： last_day(date)；例： select sysdate,last_day(sysdate) result from dual; select hiredate,last_day(hiredate)

10、 result from emp;2、next_day：基于一个参数的时间为基准，来求出第二个参数的日期语法： next_day（date，char）；其中， char这个地方必须要放一周中某一天的表现形式，比如你可以放星期一 等。也可以放monday等，也可以放 1-7的数字，记住各种写法的表现形式；例： select sysdate,next_day(sysdate,1) from dual;3、months_between：拿到两个日期之间的月数，注意，返回值是浮点数；语法： months_between(date1,date2);注意：这两个日期，是前面一个日期减后面一个日期，所以，如

11、果你把现在时间放在后面的参数里面的话，会出现负数的结果；名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 4 页，共 15 页 - - - - - - - - - 例：select months_between(sysdate,hiredate) from emp;4、add_months:在指定的日期上面做月份的增减操作；语法： add_months(date,number);注意：当 number为正时，表示在 date的基础上进行月份的追加，如果为负，则代表减少；这个月份的增减代

12、表的是在日期上进行月份的调整，并不是指增加或者减少 30*月份；例： select sysdate,add_months(sysdate,3) from dual; select sysdate,add_months(sysdate,-3) from dual;日期转换函数：1、日期转字符串： to_char；语法： to_char(date,mode);注意：参数中的 mode表示一个字符串，里面用来描述你想要转换成哪个日期格式，其中 mode类似于 java中的simpleDateFormat后的参数，但是需要注意的是，MM 和HH不用再分大小写，并且 Oracle中表示分钟的 mode为

13、mi；其他具体的 mode参考API，需要注意一点，里面的月份跟星期是根据你Oracle上的编码形式来进行的；例select to_char(sysdate,yyyy-MM-dd) from dual;select to_char(sysdate,yyyy-mm-dd) from dual;select to_char(sysdate,yyyy-MM-dd hh:mi:ss) from dual;select to_char(sysdate,MONTH) from dual;2、字符串转日期： to_date;具体语法跟上面类似，只需要注意一点，在mode里面，不在乎你书写的格式，反正 Ora

14、cle都会以标准格式进行 date的输出；例：select to_date(120314,hh24/mi/ss) from dual;名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 5 页，共 15 页 - - - - - - - - - 3、字符串转数字： to_number;注意：要进行合理的转换；例：select to_number(1234) from dual;通用函数：1、nvl;2、nullif ：nullif （表达式 1，表达式 2）：如果两个表达式相等，则返回nu

15、ll，否则返回表达式 1；例：select nullif(2,3) from dual;3、nvl2：nvl2（表达式 1，表达式 2，表达式 3）：当表达式 1不为null的时候，返回 2，否则返回 3；例： select nvl2(comm,sal+comm,sal) sal from emp;4、coalesce ：coalesce （表达式 1，表达式 2，表达式 3，表达式 4.）:依次顺序从前往后进行校验，遇到非 null的时候，就停止校验并且返回那个非null的值；例：select coalesce(sal+comm,sal,0) sal from emp;5、case.when

16、.then*.else.end:用来分段表示的函数，其中case 后面接的是需要分段表示的例：select empno,ename,deptno,case deptno when 10 then 开发部 when 20 then 集成部 when 30 then 销售部 else 其他部门 end as 部门 from emp;6、decode():作用跟上面的类似；语法： decode（列名， 列名有可能出现的值，出现这个值做的处理*,如果出现其他情况所做的处理）；例： select empno,ename,deptno,decode(deptno,0,开发部 ,名师资料总结 - - -精品

17、资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 6 页，共 15 页 - - - - - - - - - 20,集成部 ,30,销售部 ,其他部门 ) 部门 from emp;注意：这里开始用到了双引号，在起别名的时候，如果使用中文的别名的话，可以直接写中文也可以写上双引号，但是不能写单引号；分组函数：1、count：数量统计；我们一般使用 count来统计一张表中的数据数量或者是统计一张表中某个字段的数量（如果你需要统计的字段有重复的情况发生的话，要附带着使用distinct来去重复再进行数量统计）；例： s

18、elect count(*) from emp; select count(distinct(deptno) from emp;2、max、min、avg、sum；注意：在使用分组函数来进行某些操作的时候，会自动忽略掉空值；例：select avg(nvl(comm,0) from emp;3、group by：分组查询；按照你想要的分组规则来对数据进行相同数据为一组并且进行分组的操作；例：查询出不同部门里面的员工数量： select deptno,count(*) from emp group by deptno;如果某一天，你想对不同工作岗位进行分组的话，那么第一想法是把group by

19、后面的字段改为job，但是会报错，因为这违反了分组查询的第一条定律：a）、如果列名（或者是单行函数）和组函数同时出现在查询条件中的话，那么必须在 group by后面跟上这个列名或者是单行函数所使用的列名；名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 7 页，共 15 页 - - - - - - - - - b）、如果你想查出各种job下的员工数量的话，那么如果你不使用groupby，就会报错；c）、如果你想在组函数中去使用带组函数的条件判断的话，需要使用having关键字；后面跟

20、组函数的判断条件；where和having：a）、都是用来在 sql语句中进行条件判断的关键字，后面都是跟条件判断语句；b）、where后面跟的是单行函数的判断语句，having后面跟的是组函数的判断语句；c）、where一般放在 from+表名之后， having一般放在 group by+列名之后；例：select job,deptno,count(*) from emp where sal1000 group byjob,deptno having max(sal)1000;注意：单行函数（或字段名）可以在查询条件中出现多个，如果出现多个，就必须同时也出现在group by中；如果你想在

21、使用分组查询之后再进行排序，那么order by 一定要写于最后面；排序的时候，可以对别名进行排序；order by后面可以接多个排序列名；4、组函数的嵌套：含义：就是使用多个组函数嵌套放在查询条件中；注意：组函数的嵌套不能跟单行函数或者是列名一起使用，并且必须使用group by来进行分组，此时，因为查询条件中没有单行函数，所以分组的条件可以自己根据需求来指定；名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 8 页，共 15 页 - - - - - - - - - 例：select

22、 deptno,avg(sal) from emp group by deptno; select max(avg(sal) from emp group by deptno; 注：第一行语句只是为了方便去理解第二行语句，实际上与嵌套函数无关；这里可以看出：查询列表中出现的单行函数一定要出现在group by中，但是 group by中出现的单行函数不一定需要出现在查询列表中（比如嵌套组函数）；练习：查询包含 1981年5月1日和之后入职的并且平均工资大于1200的部门、工作和平均工资值，并且以部门和工作进行升序排序select deptno,job,avg(sal) from emp whe

23、re hiredate=to_date(1981-05-01,yyyy-mm-dd)group by deptno,job having avg(sal)1200 order by deptno,job;多表查询：一、概念：对多张表进行查询操作；二、笛卡尔积：直接使用两张表作为查询的表来进行查询的话，会产生两张表数据量的乘积数量的数据，这种查询的方式称之为产生了笛卡尔积，在工作中，这种查询的方式是我们尽量进行避免的，只有在很少的时候会特意地去使用笛卡尔积，比如世界杯的小组循环赛；我们通常使用cross join来连接两张需要进行求笛卡尔积的表；例： select * from emp cros

24、s join dept;；三、内连接：1、等值连接：根据几张表中的关联条件来进行连接；例：select * from emp,dept where emp.deptno = dept.deptno;如果你想查询出几张有关联关系的表中的一些字段的话，那么我们的写名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 9 页，共 15 页 - - - - - - - - - 法如下：例：select emp.ename,emp.job,dept.dname,dept.loc from emp,d

25、ept where emp.deptno = dept.deptno;注意：使用上述的写法虽然可以查询你想要的数据，但是这样写oracle觉得太过于臃肿，所以，我们在使用多表查询时，如果想查询出他们某些字段的话，我们通常使用别名的形式 -即在from后面的表名后面跟上你起的自定义名字，然后在查询条件和where后面就可以以别名 +列名的形式来进行查询；例： select e.ename,e.job,d.dname,d.loc from emp e,dept dwhere e.deptno = d.deptno;练习：查询出每个人的姓名、工资以及他领导的姓名以及工资；练习：把上面例子中加上他们各

26、自的部门名称；练习：查询出每个雇员的姓名、工资、部门名称、工资所在等级以及领导的姓名及领导的工资和领导工资所在公司的等级；练习：在上面的基础上，加一个显示工资等级的要求比如：等级是 1的话，那么显示第一等级；.注意：当进行多张表的连接查询时，多张表之间用逗号隔开，对多张表的关联关系进行在 where后面说明时，关联关系之间用and隔开；2、不等值连接使用方法跟等值连接一样，只不过where条件后面的等于号换成了不等于号；3、自然连接（ natrual join）把两张表中有关联关系的所有数据进行自然的连接，而不需要你去名师资料总结 - - -精品资料欢迎下载 - - - - - - - - -

27、 - - - - - - - - - 名师精心整理 - - - - - - - 第 10 页，共 15 页 - - - - - - - - - 写他们的联系条件；它的效果和使用等值连接一样；例：select * from emp natural join dept;四：外连接1、左/右连接（ +）首先，这种连接方式是Oracle独有的连接方式，这种连接方式是直接可以在等值连接的基础上进行修改的连接；然后，你想把那个表展示出来，那么那张表就应该作为一张基表，最后，这种连接的使用方式是直接在等值连接的where后面的联系关系上直接加一个 （+） 在你想作为基表的另外一边；规律：（ +）在“=”左边

28、，表示右连接；（+）在“=”右边，表示左连接；注意：上面规律所说的“=”左右边并不是把加号放在等于号的左边和右边，而是放在等于号连接的那两个连接条件的后面；左连接表示左边的表为基表，右连接表示右边的表为基表，基表则代表着要打印该表的所有数据；2、左外连接 /右外连接含义跟上面的左右连接一样，但是在书写上有很大的差异，不能直接从内连接改动成为外连接，需要在特定的地方加上一些关键字才行；左外连接： left join/left outer join;右外连接： right join/right outer join;使用方式：a、上面的左右连接除了在where后面的连接条件上面后面加一个名师资料总

29、结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 11 页，共 15 页 - - - - - - - - - （+）号之外，其余部分跟内连接没啥区别；b、使用这种左外右外连接的话，需要从from的后面就进行更改，不能使用逗号来隔开几个不同的表名，而需要使用你想把那张表作为基表就使用哪种对应的外连接；并且连接条件不再是 where，而是 on关键字；c、在日常工作中，不推荐第一种，因为无法进行与其他数据库的数据交换；通过这个例子可以看出，在我们日后工作开发过程中，尽量使用通用性较强的语法规范；on

30、与where的区别：当我们使用外连接的时候，on通常会取代 where的位置来进行连接条件的存放，但是 on后面，只能跟连接条件，如果有其他的判断条件的话，请使用where；口诀：左外连接（ +）在右；右外连接（ +）在左；有（ +）不全显；无（ +）才全显；3、全外连接（满外连接）（full outer join ）当你想把两张表中所有没有关联关系的数据都显示出来的话，可以使用全外连接，简单的说，这种连接方式，就是把两张表同时作为基表；例：select * from emp full outer join dept on emp.deptno = dept.deptno;注：全外连接除了在一

31、些特殊的场合需要全部显示多张表的信息之外，在我们不明确需要用左连接还是右连接的时候，可以用来进行表信息的查看然后进名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 12 页，共 15 页 - - - - - - - - - 行判断；记得，使用全外连接也必须有连接条件；五、子查询1、概念：在一个查询的内部还包括了另外一个查询；例一：查询出比雇员编号7654的雇员工资高的雇员的所有信息；select * from emp where sal(select sal from emp wher

32、e empno = 7654);例二：求出每个部门的最低工资的雇员的信息；select * from emp where sal in (select min(sal) from emp group bydeptno)2、单行子查询：子查询里面只会返回一行数据的查询，对于这种查询，我们可以在主查询里面使用 ,=,=, 这些操作符来进行条件的判断；比如上面例子的例一；3、多行子查询：顾名思义，这种子查询代表着在子查询里面的返回值会有多条，那么出现这种情况的时候我们在主查询里面有三种操作符可以使用：a、in：代表主查询中的条件只要满足子查询中返回值的任意一条就可以；b、或+all：代表主查询中的条

33、件需要满足子查询返回值的所有值；c、操作符 +any：代表主查询中的条件只需要满足子查询返回值的任意一个就成立d、exists：含义跟 in一样，它跟的 in的区别主要是体现在效率上，如果子查询的查询量很大的话，那么使用exists，如果主查询的查询量很大的话，使用in，在一般的开发中，子查询的查询量都不会小，使用exists可以极大的提高查询名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 13 页，共 15 页 - - - - - - - - - 效率；4、子查询能够在哪里出现：a

34、、出现在 where条件中，相当于把整个子查询的返回值作为一个视图来进行操作；b、出现在 select列表中，但是需要注意子查询此时只能是单行子查询，用的很少；c、能出现在 having中，出现的非常少；d、出现在 from中；5、子查询的特点：a、子查询必须套上括号，并且能够出现在select、from、where和having中；b、如果子查询的结果为空的话，则主查询也查不到任何值；c、子查询所查询的表可以与主查询不一样，只需要他们之间有关联关系即可（只需要子查询的返回结果能够被主查询所使用就行）；练习：要求查出部门名称、部门的员工数、部门的平均工资和部门的最低收入雇员的姓名；select

35、 d.dname,e2.d,e2.a,e1.ename,e2.m from emp e1, (select deptno,count(*) d,avg(sal+nvl(comm,0) a,min(sal) m fromemp group by deptno) e2, dept d where e2.deptno = d.deptno and e1.sal = e2.m;伪列： rownum在Oracle中，一张表每插入一条数据，都会有一个数字对应进行匹配，这个数字从 1开始，每次递增 1，一直递增，这个列是隐藏的，叫做rownum，并且这个列不需要你在建表的时候特意定义出来，它是建表时系统自带

36、的；作用：我们可以根据这个特点，来对一个表的查询结果进行排序，排序名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 14 页，共 15 页 - - - - - - - - - 之后，这个 rownum还是从 1到无穷大来对应查询的结果从上往下，所以，我们可以使用子查询和 rownum来获取你想要的某几行数据，比如求出收入最高的前面名等等例：select * from ( select * from emp order by sal desc) whererownum=3;名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 15 页，共 15 页 - - - - - - - - -