SQL培训ppt课件

1、Oracle SQL简单培训2021-07-20内容Basic for Select表关联组与统计函数以及集合操作索引与视图SQL书写需求留意的问题-提高性能一些有用的SQLPL/SQL Developer简单设置和运用Oracle 10g online book192.168.26.62:8080/B19306_01 Select行与列按照条件where获取特定列的集合SelectionTable 1Select运算运算符号“+ “- “* “/ “|函数Abs() round() trim() lpad() length() nvl()别名普通用在子查询中Col1 as first_col

2、umnCol1 “first_column去除结果集中的反复Distinct column_aDistinct column_a, column_b类型转换To_char to_date to_numberDate的格式，yyyy-mm-dd hh24:mi:ssCaseCase when col11 then col2 when col22 then A else col1+col2 end留意与PL/SQL中的case end case 的区别一些函数用法【lpad】lpad,rpadlpad函数将左边的字符串填充一些特定的字符，rpad与之顺序相反lpad(string,n,pad_st

3、ring)string：可是字符或者参数n：字符的长度，是前往的字符串的数量，假设这个数量比原字符串的长度要短，lpad函数将会把字符串截取成从左到右的n个字符;pad_string：是个可选参数，这个字符串是要粘贴到string的左边，假设这个参数未写，lpad函数将会在string的左边粘贴空格。举例lpad(tech, 7) 将前往tech lpad(tech, 2) 将前往te lpad(tech, 8, 0) 将前往0000tech 用途用在填充字段长度。普统统过sequence生成unique值，然后经过lpad填充到指定的长度，用这个值作为主键。一些函数用法【nvl】NVL，NV

4、L2，NULLIF格式NVL(expr1, expr2)当expr1为null时，前往expr2NVL2(expr1, expr2, expr3)当expr1为not null时，前往expr2，否那么expr3NULLIF(expr1, expr2)当expr1=expr2，前往null用途NVL多用在让查询结果不要前往null的情况Select条件Between . And 包含上下界In (enum1, enum2)existsLike “King% 留意效率的影响Is null 和 is not nullTop Nrownum表关联N张表，有N-1个条件结果集中的列名可以不写表名，但是

5、此列名不能出如今多张表中短少条件的后果很严重笛卡尔集内衔接=、外衔接(+)、自衔接出于效率的思索，最好小于4张表分组与集合函数组按照同一个属性，陈列起来的数据集合Group by col1,col2集合函数Avg max min sum count 运用时留意null带来的干扰组过滤Having 统计函数统计函数与集合函数类似，普通多用于需求统计列出现的情况，例如在结果集中需求出现占比和环比的情况。格式sum(credit) over (partition by ACCOUNTTYPE)常用的几个Sum 分组求和avg 求平均数max 求最大值 min 求最小值RATIO_TO_REPORT

6、计算百分比Wm_concat行转列Rollup() ,cube(), grouping()集合操作集合概念数据的集合。查询结果、条件组(col1,col2) = (select col1,col2 from tbl)也可以运用in合并结果集Union，union allUnion all 效率相对高一些，由于不用去除反复记录结果集相减Minus结果集一样内容汇总INTERSECT子查询简单了解就是放在查询内部的查询，就叫做子查询。通长内部查询的结果作为外部的查询的条件。子查询普通放在 select , from , where , having子句的后面，放在select子句后面成为相关子查询

7、，其他的都属于非相关子查询。 SELECTselect_listFROMtableWHEREexpr operator (SELECT select_list FROM table);SELECT(select col from table_in where table_in.col = table.col)FROMtableWHEREexpr operator;子查询相关子查询和非相关子查询的区别：执行顺序不一样。非相关子查询：先查询内部的查询，然后将内部的查询前往给外部查询作为外部查询的条件。内部查询只执行一次，效率高。相关子查询：先查询外部查询，在内部查询。外部查询一次，内部就查询一次，

8、前往一次数据。效率慢。能否可以单独执行。相关子查询不可以单独运转的，它是依赖于外部查询的。非相关子查询可以单独运转。不依赖于外部查询。效率比较非相关效率高，执行速度快。相关效率低，执行速度慢。子查询按照结果集数量，可以分为单行子查询可以运用“=、，配合max、min、any、allAny可以了解为min，all了解为max多行子查询运用in经过子查询可以写出很长的SQL视图EMPLOYEES Table:视图视图的作用为了限制数据访问为了使复杂的查询变得更容易书写和阅读为了提供数据独立性防止同一类数据，出现不同的挑选方法视图运用上，可以把视图看做是一张表两种视图普通视图不存储数据简单视图单表、

9、无函数和组定义普通可以update和delete，而复杂视图通常是不可以的物化视图存储数据简单视图可以更新没有group by索引描画Indexes are optional structures associated with tables and clusters. You can create indexes on one or more columns of a table to speed SQL statement execution on that table.It is used and maintained automatically by the Oracle server

10、作用大幅度提升查询速度减少资源争用留意索引不是万能的，没有他是万万不能的索引建立自动。建立表主键时，自动建议一个索引。手工。Create Index 什么情况下建立索引大表索引列包含很多不同的值unique value预期的查询结果行记录数量，占比在5%以下对于索引列的更新不是很频繁在查询条件中，索引列不是表达式的一部分索引Oracle索引类型B-tree默许索引类型独一索引、组合索引Bitmap有排他性针对数值稀少，不常改动的数据，多用于数据仓库Partitioned 在分区表上建立的索引，有全局索引和分区索引的区分Function-basedDomain 表分区当表中的数据量不断增大，查询

11、数据的速度就会变慢，运用程序的性能就会下降，这时就应该思索对表进展分区。表进展分区后，逻辑上表依然是一张完好的表，只是将表中的数据在物理上存放到多个表空间(物理文件上)，这样查询数据时，不至于每次都扫描整张表。表分区分区类型范围分区 Range列表分区 List散列分区 Hash组合分区表分区分区索引类型本地索引全局分区索引全局索引OLTP表分区运用场所OLTP systems often benefit from improvements in manageability and availability, while data warehousing systems benefit fro

12、m performance and manageability. you should use global indexes for OLTP applications and local indexes for data warehousing or DSS applications 带来的益处大块数据的迁移、删除降低表扫描的本钱综合来说，数据管理本钱，提高数据的可用性什么是运转DML时，Oracle需求很多步骤来完成。这些步骤的组合，叫做执行方案执行的作用经过执行方案，可以看到DML的执行方式，从而了解他的SQL是不是最优化的怎样看执行方案PL/SQL Developer不要出现“TABL

13、E ACCESS FULL在进展表关联时，记录数量少的表，可以有“TABLE ACCESS FULL查询方案随着数据量的变化，会变化执行方案Planv$sql记录曾经parsing过得SQLHint双面剑用途用来强迫查询优化器按照指定的方式运转用法举例select /*+ NO_INDEX(big_table) */ col1 from big_table where col2 = 1000常用种类Index(table index)leading(tbl1 tbl2)运用场景与限制通常情况下，Oracle不建议运用hint，让优化器本人决议Oracle10g的查询优化器曾经比较完善，实践运用

14、中，在简单SQL情况下，会有最优的结果多用在多表关联时，而每张表上的索引较多192.168.26.62:8080/B19306_01/server.102/b14200/sql_elements006.htm 绑定变量变量绑定的作用，可以使效力器不需求再对SQL语句进展硬解析hard parsing一个非常耗费资源的操作，而只进展软解析soft parsing重用之前硬解析的结果，这样可以提高效力器的任务效率可以经过查询v$sqlstats来看到那些SQL曾经被编译，在内存中如何运用变量绑定SELECT fname, lname, pcode FROM cust WHERE id = cust

15、_no; Java中运用prepare，变化变量的值举例绑定变量Java举例String v_id = xxxxx;String v_sql = select name from table_a where id = ? ; /嵌入绑定变量stmt = con.prepareStatement( v_sql );stmt.setString(1, v_id ); /为绑定变量赋值stmt.executeQuery();PL/SQL举例SP编写Insert into tbl (col1,col2,col3) values (ln_pk,ln_col2_value,null)Insert into

16、 tbl (col1,col2,col3) values (ln_pk,null,ln_col3_value)以上两句需求进展改写：ln_col2_value := null; 或者 ln_col3_value := null; Insert into tbl (col1,col2,col3) values (ln_pk, ln_col2_value,ln_col3_value)SQL书写最好不要用的几种情况Trim(col1)=AA函数列，不能运用索引，not in，not exists排除，不能运用索引Truncate table与 delete性能上将，前者不记录日志，后者记录日志存储上

17、讲，前者可以降低water mark，后者不行Count(*) - count(PK)Sp中判别能否有记录Select count(1) into exp1 from dual where exists(select 1 from tbl1 where col1=value)SQL书写In 和 Exists 的运用场景前提是，SQL的查询条件都可以运用索引外表大，内表小，运用in；外表小，内表大，运用exists；都很大时，可以用in。SP中，防止运用cursor，可以采用数组交换，提高效率。交换运用SELECT DISTINCT(SQRT(department_id) col_alias F

18、ROM employees SELECT SQRT(department_id) col_alias FROM ( SELECT DISTINCT department_id FROM employees) Integer交换为PLS_INTEGERSQL书写程序中，where条件两边的数据类型要一致，防止出现数据转换A:=10.0;In 和 exists的交换写法select deptno from deptwhere deptno not in(select deptno from emp)建议写成：select deptno from dept, empwhere dept.deptno

19、 = emp.deptno(+)and emp.deptno is null一些不常用的SQL用法多表插入INSERT ALL INTO sal_history VALUES(EMPID,HIREDATE,SAL) INTO mgr_history VALUES(EMPID,MGR,SAL)SELECT employee_id EMPID, hire_date HIREDATE, salary SAL, manager_id MGR FROM employees WHERE employee_id 200;运用于事物要求性比较高的情况。例如，经过一个SQL实现多表插入，假设胜利就都插入，失败那

20、么都回滚。日常，实现这样的情况，需求启动事物，这里可以经过一个SQL实现，不需求启动事物一些不常用的SQL用法带前往值的UpdateUPDATE cust_data SET cust_no = aa,WHERE cust_add = tp1RETURNING nme, INTO V_nme_J,V_tel 常用于事物密集型操作例如，修正账户余额。通常的做法是，首先update，然后select，但是这中间会存在一个时间差，导致select结果不正确。这时可以按照此法运用，获得准确的前往值一些不常用的SQL用法递归Select select * from emp start with ename=KING connect by prior empno=mgr产生树形构造DirectionTop down Column1 = Parent Ke