Java相关课程系列笔记之二Oracle学习笔记(建议用WPS打开)

Oracle学习笔记Java相关课程系列笔记之二笔记内容说明Oracle数据库、SQL（薛海璐老师主讲，占笔记内容100%）； PAGE4目录TOC\o"1-4"\h\u638一、数据库介绍 1276051.1表是数据库中存储数据的基本单位 1253571.2数据库标准语言 120751.3数据库（DB） 1193291.4数据库种类 1126851.5数据库中如何定义表 1222441.6createdatabasedbname的含义 1265901.7安装DBMS 1234601.8宏观上是数据-->database 1202601.9远程登录：telnetIP地址 1326001.10TCP/IP通信协议 2105131.11数据库建连接必须提供以下信息 2116731.12一台机器可跑几个数据库，主要受内存大小影响 2186491.13源表和结果集 2129931.14几个简单命令 2244471.15tarena给jsd1304授权 24021.16课程中使用的5个表 311106二、selectfrom语句 589702.1select语句功能 5115742.2select语句基本语法 525522.3列别名 5219712.4算术表达式 541042.5空值null的处理 5316242.6nvl(p1,p2)函数 5205762.7拼接运算符|| 668502.8文字字符串 6297062.9消除重复行 6177492.10其他注意事项 626299三、SQL语句的处理过程 7319133.1SQL语句处理过程 7242423.2处理一条select语句 722157四、where子句 8145404.1where子句后面可以跟什么 824434.2语法和执行顺序 8291874.3字符串是大小写敏感的，在比较时严格区分大小写 894564.4where子句后面可以跟多个条件表达式 845764.5betweenand运算符 866194.6in运算符（多值运算符） 8137544.7like运算符 997524.8isnull运算符 9109374.9比较和逻辑运算符（单值运算符） 9303614.10多值运算符all、any 9135384.11运算符的否定形式 918106五、orderby子句 10214515.1语法和执行顺序 10109425.2升降序 1065635.3null值在排序中显示 10264095.4orderby后面可以跟什么 1040115.5多列排序 10986六、单行函数的使用 11151956.1数值类型 112836.2日期类型 11127406.3字符类型 1315326.4转换函数 14212326.5其他注意事项 1423216七、SQL语句中的分支 15218367.1分支表达式 15222267.2分支函数 1518788八、组函数 16273128.1报表统计常用 16165028.2缺省情况组函数处理什么值 16256178.3当组函数要处理的所有值都为null时 16216738.4行级信息和组级信息 1621012九、groupby子句 1720259.1语法和执行顺序 17187119.2分组过程 17221999.3常见错误 1737859.4多列分组 171650十、having子句 181428810.1语法和执行顺序 181302110.2执行过程 181937610.3where和having区别 1831989十一、非关联子查询 192683811.1语法 19341211.2子查询的执行过程 192916111.3常见错误 192833011.4子查询与空值 192223611.5多列子查询 2010522十二、关联子查询 212440112.1语法 21346112.2执行过程 211876412.3exists 212252512.4exists执行过程 212618512.5notexists 221919012.6notexists执行过程 222923112.7in和exists比较 2212534十三、多表查询 233141813.1按范式要求设计表结构 232478713.2多表连接的种类 23903813.3交叉连接 2318513.4内连接 231237413.5外连接 252625913.6非等值连接 272421413.7表连接总结 2710378十四、集合 282819214.1表连接主要解决的问题 281810814.2集合运算 281785714.3集合运算符 282440214.4子查询、连接、集合总结 2931534十五、排名分页问题 301039815.1什么是rownum 301157115.2whererownum<=5的执行过程 30229015.3whererownum=5的执行过程 3018605十六、约束constraint 31877016.1约束的类型 312205016.2primarykey：主键约束 31610316.3notnull：非空约束 31746316.4uniquekey：唯一建约束 311807816.5referencesforeignkey：外键约束 32511416.6check：检查约束 3411471十七、事务 35128117.1transaction 352561017.2定义 352483217.3事务的特性：ACID 352690717.4事务的隔离级别 351133617.5数据库开发的关键挑战 35178417.6锁的概念 36978117.7Oracle的锁机制 36992517.8事务不提交的后果 36145317.9回滚事务rollback 36534317.10保留点savepoint 3614973十八、数据库对象：视图view 37286818.1带子查询的createtable 372529318.2带子查询的insert 37498418.3定义缺省值：default 372380818.4视图view 381023718.5视图的应用场景 382501418.6视图的分类 392871718.7视图的维护 3911985十九、数据库对象：索引index 41767619.1创建index 412141519.2扫描表的方式 412350319.3索引的结构 412862219.4为什么要使用索引 423037219.5哪些列适合建索引 4228019.6索引的类型 42371919.7哪些写法会导致索引用不了 43711二十、数据库对象：序列号sequence 441013120.1什么是sequence 442841020.2创建sequence 44206320.3缺省是nocycle（不循环） 443197020.4缺省cache20 4429801二十一、其他注意事项 46236821.1删除表，删除列，删除列中的值 46822521.2多对多关系的实现 461746921.3一对多（两张表） 46892321.4一对一 462217421.5数据库对象 463153612.6缺省（默认）总结： 46勿传网上！严禁谋利！ Oracle学习笔记 常彦博PAGE48数据库介绍1.1表是数据库中存储数据的基本单位1.2数据库标准语言结构化查询语言SQL：StructureedQueryLanguage1）数据定义语言DDL：DataDefinitionLanguage createtable列表结构、altertable修改列、droptable删除列2）数据操作语言DML：DataManipulationLanguage insert增加一行，某些列插入值、update修改一行，这一行的某些列、delete删除一行，跟列无关3）事务控制语言TCL：TransactionConrtolLanguage commit确认，提交（入库）、rollback取消，回滚，撤销4）数据查询语言DQL：DataQueryLanguage select语句5）数据控制语言DCL：DataControlLanguage 系统为多用户系统因此有隐私权限问题：grant授权、revoke回收权限1.3数据库（DB）DATABASE关系数据库使用关系或二维表存储信息。关系型数据库管理系统（EDBMS）：RelationshipDatabaseManagementSystem是一套软件，用于在数据库中存储数据、维护数据、查询数据等。1.4数据库种类Oracle10g（Oracle）、DB2（IBM）、SQLSERVER（MS）1.5数据库中如何定义表先画列即表头（列名，数据类型及长度，约束）；数据类型有字符、数值number、日期date。1.6createdatabasedbname的含义创建数据库即创建可用空间，创建出一堆数据文件datafile1.7安装DBMS职位：DBA数据库管理员（DataBaseAdministrator）1.8宏观上是数据-->database开发流程：createtabaleDMLTCL->DQLselect1.9远程登录：telnetIP地址sqldeveloper在linux系统>连接>database在solaris系统1.10TCP/IP通信协议两台机器上的两个应用程序要通信，必须依赖网络，依赖TCP/IP通信协议。IP：IP协议包中提供要连接机器的IP地址，用于标识机器。TCP：TCP协议包中提供与机器上的哪个具体应用程序通信，通过端口号实现，oracle数据库服务缺省端口为1521，用于标识Oracle此数据库应用。1.11数据库建连接必须提供以下信息ip地址（确认机器）、port号（确认进程（程序）确认Oracle）SID：一个端口可以为多个oracle数据库提供监听，因此还需要提供具体的数据库名。（确认数据库里的哪个数据库）username、password：要想访问数据库，必须是该数据库上一个有效的用户。（确认身份）1.12一台机器可跑几个数据库，主要受内存大小影响1.13源表和结果集源表：被查询的表 结果集：select语句的查询结果1.14几个简单命令showuser：查看当前用户 desc表名：查看表结构droptable表名purge;删除表，Oracle中删除表不是真正的删除，而是占空间的移动到别的地方，因为为了不占空间，真正的删除需要用purge。deletefrom表名：删除表中所有值；若加上where列名=value则删除某列中的值1.15tarena给jsd1304授权connecttarena/tarenagrantselectonaccounttojsd1304; grantselectonservicetojsd1304;grantselectoncosttojsd1304; jsd1304selecttarena的表connectjsd1304/jsd1304createsynonym创建同义词 createsynonymaccountfortarena.account;createsynonymservicefortarena.service; createsynonymcostfortarena.cost;1.16课程中使用的5个表

selectfrom语句2.1select语句功能1）投影操作：结果集是源表中的部分“列”2）选择操作：结果集是源表中的部分“行”3）选择操作+投影操作：结果集是源表中的部分“行”部分“列”4）连接操作join：多表查询，结果集来自多张表，把多张的记录按一定条件组合起来2.2select语句基本语法1）selectcolname(列名) fromtabname（表名）2）select中指定多个列名，则用“逗号”分隔：selectcolname1,colname2fromtabname3）*号表示所有列：select* fromtabname4）select语句：可有多个子句5）select子句：投影操作（列名）、列表达式、函数、from子句等2.3列别名1）给列起一个别名，能够改变一个列、表达式的标识。2）不写的话默认都是转成大写。 3）适合计算字段。4）在原名和别名之间可以使用as关键字。5）别名中包含空格、特数字符或希望大小写敏感的，用“”双引号将其括起来。2.4算术表达式在number类型上使用算术表达式（加减乘除）。eg：一个月使用了250小时,每种资费标准下应缴纳的费用(首次实现)selectbase_cost+(250-base_duration)*unit_costfeefromcost;2.5空值null的处理未知的，没写数1）空值不等于02）空值不等于空格3）在算术表达式中包含空值导致结果为空4）在算术表达式中包含空值需要用空值转换函数nvl处理2.6nvl(p1,p2)函数空值转换函数1）两个参数类型要一致！2）参数的数据类型可以是数值number、字符character、日期date3）但null转成字符串，null也要用to_char()转化。4）实现过程：ifp1isnullthen returnp2elase returnp1endif5）实现空值转换：null－>非null值0eg：一个月使用了250小时,每种资费标准下应缴纳的费用(再次实现)selectnvl(base_cost,0)+(250-nvl(base_duration,0))*nvl(unit_cost,0)feefromcost;2.7拼接运算符||表达字符（串）的拼接，可以将某几列或某列与字符串拼接在一起。selectcolname1||colname2fromtabname2.8文字字符串select语句后面可以包含的文字值：字符、表达式、数字。1）字符常量（或字符串）必须用‘’单引号括起来，作为“定界符”使用。2）表达单引号本身，需要两个单引号''''1，4定界2，3表单引号。3）对于文字值每行输出一次。eg：显示客户姓名的身份证号是……selectreal_name||'''sIDCARDNOis'||idcard_no||'.'cilentfromaccount;4）函数转换大小写，尽量在进入数据时操作。2.9消除重复行distinct去重复行（对整条记录返回的结果去重，不是对后面的某个列去重），若后面有多列，则所有列联合起来唯一，即每列的值都可以重复，但组合不能重复。eg1：哪些unix服务器提供远程登录业务selectdistinctunix_hostfromservice;eg2：每一台unix服务器在哪些天开通了远程登录业务selectdistinctunix_host,create_datefromservice;2.10其他注意事项1）调常量时用单行单列的dual表，系统提供的表。2）invalididentifier无效标识名，列名不。3）tableorviewdoesnotexist表名不对。

SQL语句的处理过程3.1SQL语句处理过程用户进程sqlplus→建立连接→服务进程ServerprocessoracleSID↑--创建会话--Oracleserver3.2处理一条select语句1）分析语句： ①搜索是否有相同语句 ②用hashvalue计算select语句是否长得一样：大小写，关键字，空格要都一样，不一样则为两条语句，则服务进程会重新分析。若为统一语句，则直接从内存拿执行计划，计算结果 ③检查语法、表名、权限 ④在分析过程中给对象加锁 ⑤生成执行计划2）绑定变量：给变量赋值3）执行语句：4）获取数据：将数据返回给用会进程

where子句用where子句对表里的记录进行过滤，where子句跟在from子句后面。4.1where子句后面可以跟什么跟条件表达式：列名、常量、比较运算符（单、多值运算符）、文字值；不能跟组函数！不能跟列别名！注意事项：对列不经过运算的条件表达式效率会更高，建议在写where子句时尽量不要对列进行运算。eg：一年的固定费用为70.8元，计算年包在线时长selectbase_duration*12ann_durationfromcostwherebase_cost*12=70.8;没下面效率高selectbase_duration*12ann_durationfromcostwherebase_cost=70.8/12;4.2语法和执行顺序语法顺序：selectfromwhere 执行顺序：fromwhereselect4.3字符串是大小写敏感的，在比较时严格区分大小写1）upper()：函数将字符串转换成大写。2）lower()：函数将字符串转换成小写。3）initcap()：函数将字符串转换成首字符大写（是将列中的值大小写转换然后去和等号后的字符串比，而不是把转字符串转换去和列比）。eg：哪些unix服务器上开通了os帐号huangrselectunix_host,os_usernamefromservicewhereos_username='huangr';(有结果) wherelower(os_username)='HUANGR';(无结果)wherelower(os_username)='huangr';(有结果)whereupper(os_username)='HUANGR';(有结果)4.4where子句后面可以跟多个条件表达式条件表达式之间用and、or连接，也可用（）改变顺序。4.5betweenand运算符表示一个范围，是闭区间，含义为大于等于并且小于等于。eg：哪些资费的月固定费用在5元到10元之间selectbase_duration,base_cost,unit_costfromcostwherebase_cost>=5andbase_cost<=10; wherebase_costbetween5and10;4.6in运算符（多值运算符）表示一个集合，是离散值，含义为等于其中任意一个值，等价于any。eg：哪些资费的月固定费用是5.9元,8.5元,10.5元selectbase_duration,base_cost,unit_costfromcostwherebase_cost=5.9orbase_cost=8.5orbase_cost=10.5;wherebase_costin(5.9,8.5,10.5); wherebase_cost=any(5.9,8.5,10.5);4.7like运算符在字符串比较中，可用like和通配符进行模糊查找。1）通配符：%表示0或多个字符；_表示任意“一个”字符（要占位的）。注意事项：若要查找%和_本身，则需要escape进行转移。eg：哪些unix服务器上的os帐号名是以h开头的selectos_usernamefromservicewhereos_usernamelike'h%'eg：哪些unix服务器上的os帐号名是以h_开头的selectos_usernamefromservicewhereos_usernamelike'h\_%'escape'\';4.8isnull运算符测试null值需要用isnull。null不能用等于号“＝”和不等于号“<>”跟任何值比较，包括它自身。所以不能用“＝”和“<>”来测试是否有空值。即：null=null是不成立的；null不等于null也不成立；null和任何值比较都不成立。eg：列出月固定费用是5.9元，8.5元，10.5元或者没有月固定费。selectbase_duration,base_cost,unit_costfromcostwherebase_costin(5.9,8.5,10.5,null);(错误)wherebase_costin(5.9,8.5,10.5)orbase_costisnull;(正确)4.9比较和逻辑运算符（单值运算符）1）比较运算符：= > >= < <=2）SQL比较运算符：betweenand、in、like、isnull3）逻辑运算符：and、or、not4.10多值运算符all、any1）>all：大于所有的，等价于>(selectmax()…)。2）>any：大于任意的，等价于>(selectmin()…)。4.11运算符的否定形式1）比较运算符：<> ！=^=2）SQL比较运算符：notbetweenand notin notlike isnotnull注意事项：in相当于=or=or=or等价于anynotin等价于<>and<>and<>and等价于<>allnotbetweenand小于下界or大于上界集合中有null，对in无影响；但对notin有影响，有一个就没有返回值！eg：哪些资费信息的月固定费用不是5.9元,8.5元,10.5元selectbase_duration,base_cost,unit_costfromcostwherenvl(base_cost,0)<>5.9andnvl(base_cost,0)<>8.5andnvl(base_cost,0)<>10.5;wherenvl(base_cost,0)notin(5.9,8.5,10.5);

orderby子句select语句输出的结果安记录在表中的存储顺序显示，orderby子句能够改变记录的输出顺序。orderby子句对查询出来的结果集进行排序，即对select子句的计算结果排序。5.1语法和执行顺序语法顺序：selectfromwhereorderby 执行顺序：fromwhereselectorderby5.2升降序ASC－升序，可以省略，默认值 DESC－降序orderbynvl(base_cost,0); orderbyunix_host,create_datedesc;注意事项：orderby是select语句中最后一个子句5.3null值在排序中显示1）被排序的列如果包含null值，用ASC方式null值的在最后；2）用DESC方式null在最前面；5.4orderby后面可以跟什么可以跟列名、列别名、列位置（数字）、表达式、函数。orderby1：表示列位置为1的列select1from：表示常量1eg：按年固定费用从大到小的顺序显示资费信息方式一：selectid,base_cost*12ann_cost,base_durationann_durationfromcost orderbybase_costdesc;方式二：selectid,base_costann_cost,base_durationann_durationfromcostorderbybase_cost*12desc;排序的效果和上面是一样的，但前一个效率高。5.5多列排序orderby子句后面可以跟多列，而orderby后面的列可以不出现在select后面。结果集先按第一列升序排列，若列值一样，再按第二列降序排列。eg：按unix服务器ip地址升序，开通时间降序显示业务帐号信息selectid,unix_host,os_username,create_datefromserviceorderbyunix_host,create_datedesc;

单行函数的使用SQL函数的两种类型：单行函数、多行函数（组函数）。单行函数：数值类型、日期类型、字符类型、转换函数。处理一列数据，返回一个结果。6.1数值类型1）定义：createtabletabname(c1number,c2number(6),c3number(4,3),c4number(3,-3),c5number(2,4));2）数值类型说明①number：不写数值，表可写38位数②number(6)：6位整数 999999.1 999999③number(4,3)：数字4位，小数点占3位，四舍五入 1.234567 1.235④number(3,-3)：小数点前三位不写数，四舍五入，然后有效位3位 12341000⑤number(2,4)：小数点后4位，有效位2位 0.00991 0.00993）数值函数：参数类型为number①round()：四舍五入函数，“缺省转成数字”；也可对日期 ②trunc()：截取函数（不管多大值直接舍去）；也可对日期eg：round和truncround(45.923，2)：45.92 round(45.923，0)：46 round(45.923，-1)：50trunc(45.923，2)：45.92 trunc(45.923)：45 trunc(45.923，-1)：406.2日期类型1）Oracle用7个字节来存储日期和时间：世纪、年、月、日、时、分、秒。Date不存在定宽度，就是7个字节。2）缺省（默认）日期格式为DD-MON-RR，格式敏感。3）sysdate是一个系统函数，返回当前系统时间和日期。4）改变session（会话）中的日期格式：session和connection是同时建立的，两者是对同一件事情的不同层次的描述。connection是物理上的客户机同服务器端的通信链路；session是逻辑上的用户同服务器的通信交互，SQL语句的运行环境。eg：显示的日期包含世纪、年、月、日、时、分、秒altersessionsetnls_date_format='yyyymmddhh24:mi:ss';日期格式yyyy用数字表达的四位年（2013年）mm用数字表达的两位月（01月）dd用数字表达的两月日（01日）hh24用数字表达的24进制的小时（20点）h12用数字表达的12进制的小时（8点）mi用数字表达的分钟（30分）ss用数字表达的小时（30秒）D用数字表达的一周内的第几天（周日：1）day用全拼表达的星期几（sunday）month用全拼表达的月（march）mon用简拼表达的月（mar）eg：案例selectto_char(sysdate,'DDD')fromdual;年中的第几天selectto_char(sysdate,'DD')fromdual;月中的第几天selectto_char(sysdate,'D')fromdual;星期中的第几天6）在数据库中如何处理日期类型 createtabletest(c1date);insertintotestvalues('01-JAN-08');insertintotestvalues('2008-08-08');（报错）insertintotestvalues(to_date('2008-08-08','yyyy-mm-dd'));selectc1fromtest;selectto_char(c1,'yyyy-mm-dd')fromtest;在createtable中定义日期类型date时一定不能指定宽度。日期在数据库中用固定的7个字节存储，表示世纪、年、月、日、时、分、秒。缺省的日期格式为'DD-MON-RR'，'01-JAN-08'符合缺省日期格式可以插入表中，因为系统会自动调用to_date函数将它转成日期。 '2008-08-08'插入时报错，原因是不符合缺省格式，需要手工使用函数to_date对字符串的格式进行说明，如'2008-08-08'的格式说明串为'yyyy-mm-dd'。select时日期按缺省日期格式显示，若用指定日期格式，需要使用to_char函数。7）日期与字符串相互转换：to_date(char,date)函数：将字符串转换成一个日期值。对应java中parse。to_char(date,char)函数：第一个参数为要处理的日期，第二个参数为格式；可获取一个日期的任意一部分信息；对应java中format。eg：创建一张表，包含date类型的列，插入2008年8月8日8点8分8秒并显示。insertintotestvalues(to_date('2008-08-0808:08:08','yyyy-mm-ddhh24:mi:ss'));selectto_char(c1,'yyyy-mm-ddhh24:mi:ss')fromtest;注意事项：格式必须用单引号括起来，并且大小写敏感。必须是有效的日期格式。fm能去掉前导0和两端的空格。对日期去重复问题，to_char获取当天日期即可，时分秒忽略，加上distinct即可做到eg：案例whereto_char(create_date,'mm')='03';若等式右边写成‘3’，‘03’=‘3’不成立，需要在‘mm’前增加‘fm’。whereto_char(create_date,'fmmm')='3'whereto_number(to_char(create_date,'mm'))=3;若等式右边写成3，‘03’=3成立，‘03’是字符类型，3是数字类型，等式两边相等，说明系统做了隐式数据转换，缺省做法将字符转化为number。8）日期函数：参数类型为date①add_months()：一个日期加、减一个月。②months_between()：两个日期之间相差多少个月。③last_day()：同一个月的最后一天 ④next_day()：根据参数，出现下一个的日期。eg1：昨天，今天，明天altersessionsetnls_date_format='yyyymmddhh24:mi:ss';selectsysdate-1,sysdate,sysdate+1fromdual;eg2：十分钟之后altersessionsetnls_date_format='yyyymmddhh24:mi:ss';selectsysdate,sysdate+1/144fromdual;eg3：每台unix服务器上的os帐号开通了多长时间（以天为单位）selectunix_host,os_username,create_date,round(sysdate-create_date)daysfromservice;eg4：上个月的今天，今天，下个月的今天altersessionsetnls_date_format='yyyymmddhh24:mi:ss';selectadd_months(sysdate,-1),sysdate,add_months(sysdate,1)fromdual;eg5：当前月的最后一天altersessionsetnls_date_format='yyyymmddhh24:mi:ss';selectlast_day(sysdate)fromdual;eg6：用户注册多长时间了selecttrunc(months_between(sysdate,create_date))fromservice;6.3字符类型1）定义：createtabletabname(c1 char(10),c2varchar2(10));2）char和varchar2区别：①varchar2必须定义长度，按字符串的实际长度存，最大长度4000字节，更省空间。②char可以不定义长度，默认为1，按定义长度存，最大长度2000字节，操作更快。③列的取值是定长，定义成char类型。④列的取值长度不固定，定义成varchar2。注意事项：在字符串比较中，varchar2按实际字符串比，对空格是敏感的，对大小些敏感。char会将短字符串补齐后，再与字符串比，对空格不敏感。varchar类型是ANSI定义的，varchar2类型是Oracle定义的，目前是等价的。但如果ANSI对varchar类型定义有变化，则Oraclevarchar2类型不变。eg：案例 varchar2(10)：'abc'='abc'yes;'abc'='abc'no char(10)：'abc'='abc'yes; 'abc'='abc'yes3）字符函数：参数类型为字符①upper()：函数将字符串转换成大写。②lower()：函数将字符串转换成小写。③initcap()：函数将字符串转换成首字符大写（是将列中的值大小写转换然后去和等号后的字符串比，而不是把转字符串转换去和列比）。④length()：字符串的长度。⑤rpad()、lpad()：将字符补成同样长度，l和r表左右。⑥rtrim()、ltrim()：压缩字符，l和r表左右，与fm相同效果。⑦concat()：拼接函数与“||”相似。⑧substr()：求子串函数。eg：相关操作selectrpad('FEBRARY',9,'*')fromdual;whereto_char(create_date,'fmMONTH')='MARCH';wherertrim(to_char(create_date,'MONTH'))='MARCH';selectconcat('ab','c')fromdual;↓从左往右 ↓从右往左selectos_username,substr(os_username,1,2),substr(os_username,-2,2)fromservice; 6.4转换函数1）to_number()函数：将字符（串）转换成number数值类型，这也是系统的缺省做法，即to_number('03')=3。注意事项：若to_number函数处理的字符串为'ab'，则系统报错，若转换后的值是十进制的，则要求字符串必须是数字字符。selectto_number('ab')fromdual;（报错invalidnumber）2）to_char(date,char)函数：第一个参数为要处理的日期，第二个参数为格式；可获取一个日期的任意一部分信息；对应java中format。 3）函数格式说明：9代表数字位0定义宽度大于实际宽度时，0会被强制显示在前面，以补齐位数$美元符号L本地货币符号．小数点，每千位显示一个逗号注意事项：如果显示位数不足（定义宽度小于实际宽度），用#代替。eg1：相关操作selectto_char(base_cost,'L99.99')fromcost;selectto_char(base_cost,'L00.00')fromcost;selectto_char(base_cost,'$00.00')fromcost;eg2：显示月固定费用，单位费用，单位费用为null，显示nounitcostselectbase_cost,nvl(to_char(unit_cost),'nounitcost')unit_cost3）to_date(char,date)函数：将字符串转换成一个日期值。对应java中parse。4）number、字符、data间的转化①to_char()：number->字符date->字符②to_number()：字符->number③to_date()：字符->date5）显式隐式转换①隐式数据类型转换，系统调用转换函数wherecreate_datelike'%3%';隐式②显式数据类型转换，用户调用转换函数whereto_char(create_date,'mm')='03';显式6.5其他注意事项1）insertinto表名values(1，2，3，4，null)有多列时，插入值必须都写，没值的也要写null2）insertinto表名(C5)values(1234)表名最多30个字符且不能有特殊字符3）altersessionsetnls_language='AMERICAN';28-MAY-13altersessionsetnls_language='SIMPLIFIEDCHINESE';28-5月-134）altersessionsetnls_territory='AMERICA';altersessionsetnls_territory='CHINA';

SQL语句中的分支7.1分支表达式1）casewhen(then)，用于解决不同记录需要不同处理方式的问题。when后面跟条件表达式，当所有when条件都不满足时，若有else，表达式的返回结果为其后的值，否则返回null值。2）寻找when的优先级：从上到下再多的when，也只有一个出口，即其中有一个满足了表达式expr就马上退出case。3）elseexpr和returnexpr的数据类型必须相同。eg：当月包在线时长为20小时，单位费用涨5分，为40小时涨3分，其他不变（用CASEWHEN实现）selectbase_duration,unit_cost,casewhenbase_duration=20thenunit_cost+0.05whenbase_duration=40thenunit_cost+0.03elseunit_costendnew_nuit_costfromcost;7.2分支函数decode，是简版的casewhen。1）decode(value,if1,then1,if2,then2,……,else)标识如果value等于if1时，返回then1。如果不等于任何一个if值，则返回else。eg：当月包在线时长为20小时，单位费用涨5分，为40小时涨3分，其他不变（用decode实现）selectbase_duration,unit_cost,decode(base_duration,20,unit_cost+0.05,40,unit_cost+0.03,unit_cost)n_base_costfromcost;

组函数操作在一组行（记录）上，每组返回一个结果。8.1报表统计常用1）avg(distinct|all|n)：平均值，参数类型只能为number。2）sum(distinct|all|n)：求和，参数类型只能为number。3）count(distinct|all|expr|*)：计数，参数类型为number、字符、date。4）max(distinct|all|expr)：最大值，参数类型为number、字符、date。5）min(distinct|all|expr)：最小值，参数类型为number、字符、date。注意事项：distinct去重复时，会保留一个。 selectcount(distinctbase_duration)fromcost;//4，distinct保留一个空，但count统计时不算count（*）不管null，统计“记录”数。count（列名）返回的是列中非null值的数量。8.2缺省情况组函数处理什么值所有的非空值。8.3当组函数要处理的所有值都为null时count函数返回0，其他函数返回null。8.4行级信息和组级信息返回的结果集包含多条记录，是行级信息；返回的结果集包含一条记录，是统计汇总信息，是组级别的信息；两者不能同时显示出来！处理方式：将行级信息变成组标识或进行组函数处理。eg1：单位费用的总和、平均值、最大值、最小值个数selectsum(unit_cost)sum1,avg(unit_cost)avg1,max(unit_cost)max1,min(unit_cost)min1,count(unit_cost)cntfromcost;eg2：若null值参与运算，必须将null值转换成非null值selectavg(nvl(unit_cost,0)),sum(unit_cost)/count(*)fromcost;eg3：若unit_cost列中参与运算的数据都为null，avg(unit_cost)的函数值为null，count(unit_cost)的函数值为0。selectavg(unit_cost),count(unit_cost)fromcostwhereunit_costisnull;eg4：每台unix服务器上开通的os帐号数即开户数？selectunix_host,count(os_username)fromservicegroupbyunix_host;eg5：tarena26（192.168.0.26）上开通的os帐号数即开户数？selectmax(unix_host),count(os_username)cntfromservicewhereunix_host='192.168.0.26'; 用min(unix_host)也可

groupby子句将表中的记录进行分组9.1语法和执行顺序语法顺序：selectfromwheregroupbyorderby 执行顺序：fromwheregroupbyselectorderby9.2分组过程根据groupby子句指定的表达式，将要处理的数据分成若干组（若有where子句即为通过条件过滤后的数据）。每组有唯一的组标识，组内有若干条记录，根据select后面的组函数对每组的记录进行计算，每组对应一个返回值。9.3常见错误若没有groupby子句，select后面有一个是组函数，则其他都必须是组函数（记录（行）信息和组信息不能放一起，要么都是组函数，要么都是单行函数）。若有groupby子句，select后面跟groupby后面跟的表达式以及组函数，其他会报错。9.4多列分组包含多列用“，”分开，分组的个数多了，每组的记录少了。eg：根据unix服务器ip地址、开通时间统计开通的os帐号数即开户数selectunix_host,to_char(create_date,'yyyymmdd')create_date,count(os_username)cntfromservicegroupbyunix_host,to_char(create_date,'yyyymmdd');

having子句对分组过滤。10.1语法和执行顺序语法顺序：selectfromwheregroupbyhavingorderby执行顺序：fromwheregroupbyhavingselectorderby10.2执行过程行被分组，将having子句的条件应用在每个分组上，只有符合having条件的组被保留，再应用select后面的组函数对每组的数据进行处理。10.3where和having区别1）where：过滤的是行（记录），后面可跟任意列名，单行函数，不能跟组函数（无法对应到具体记录），先执行，不允许用列别名。2）having：过滤的是分组（组标识、每组数据的聚合结果），后面只能包含groupby后面的表达式和组函数（能表达组信息的），后执行，不允许用列别名。eg1：哪些unix服务器开通的os帐号数即开户数多于2个selectunix_host,count(os_username)cntfromservicegroupbyunix_hosthavingcount(os_username)>2;eg2：哪些unix服务器在哪几天的开户数多于1个selectunix_host,to_char(create_date,'yyyymmdd')create_date,count(os_username)cntfromservicegroupbyunix_host,to_char(create_date,'yyyymmdd')havingcount(os_username)>1;

非关联子查询子查询就是在一条SQL（DDL、DML、TCL、DQL、DCL）语句中嵌入select语句。11.1语法selectcolname,…fromtabnamewhereexproperator(selectcolname2fromsubtabname);11.2子查询的执行过程先执行子查询，子查询的返回结果作为主查询的条件，再执行主查询。子查询只执行一遍。若子查询的返回结果为多个值，Oracle会自动去掉重复值后，再将结果返回给主查询。注意事项：不需要distinct，会自动去重的。eg1：哪些os帐号的开通时间是最早的selectunix_host,os_username,create_datefromservicewherecreate_date=(selectmin(create_date)fromservice);eg2：哪些os帐号的开通时间比unix服务器192.168.0.26上的huangr晚selectunix_host,create_date,os_usernamefromservicewherecreate_date>(selectcreate_datefromservicewhereos_username='huangr'andunix_host='192.168.0.26');eg3：哪些os帐号的开通时间比huangr晚？（多台unix服务器上都有名为huangr的os帐号）selectunix_host,create_date,os_usernamefromservicewherecreate_date>all(selectcreate_datefromservice whereos_username='huangr');大于所有的wherecreate_date>(selectmax(create_date)fromservice whereos_username='huangr');大于最大的wherecreate_date>any(selectcreate_datefromservice whereos_username='huangr');大于任意一个wherecreate_date>(selectmin(create_date)fromservice whereos_username='huangr');大于最小的11.3常见错误单行子查询返回多条记录！此时要注意运算符的选择：1）若子查询的返回结果仅为一个值，可用单值运算符，如“=”号。2）若子查询的返回结果可能为多个值，必须用多值运算符，如in等。eg：哪些客户是推荐人selectreal_namefromaccountwhereidin(selectrecommender_idfromaccount);11.4子查询与空值若子查询的返回结果中包含空值null，并且运算为notin，那么整个查询不会返回任何行。notin等价于<>all，任何值跟null比（包括null本身），结果都不为true。eg：哪些客户不是推荐人selectreal_namefromaccountwhereidnotin(selectrecommender_idfromaccountwhererecommender_idisnotnull);11.5多列子查询where子句后面可以跟多列条件表达式。eg1：哪些os帐号的开通时间是所在unix服务器上最早的？（每台unix服务器上最早开通的os帐号）selectunix_host,os_username,create_datefromservicewhere(unix_host,create_date)in(selectunix_host,min(create_date)fromservice groupbyunix_host);eg2：哪些os帐号的开通时间比所在unix服务器上最早开通时间晚九天selectunix_host,os_username,create_datefromservicewhere(unix_host,to_char(create_date,'yyyymmdd'))in(selectunix_host,to_char(min(create_date)+9,'yyyymmdd')fromservice groupbyunix_host);

关联子查询关联子查询采用的是循环（loop）的方式。12.1语法selectcolumn1,…fromtable1owherecolumn1operator(selectcolu