Oracle-SQL-实用基础教程剖析

王忠海2020/2/9OracleSQL实用基础教程SQL概述•历史–SQL:StruceuredQueryLanguage–1974年，由Boyce和Chamber提出–1975-1979年，在SystemR上实现，由IBM的SanJose研究室研制，称为SequelSQL概述•标准化–有关组织•ANSI(AmericanNaturalStandardInstitute)•ISO(InternationalOrganizationforStandardization)–有关标准•SQL-86–“数据库语言SQL”•SQL-89–“具有完整性增强的数据库语言SQL”，增加了对完整性约束的支持•SQL-92–“数据库语言SQL”，是SQL-89的超集，增加了许多新特性，如新的数据类型，更丰富的数据操作，更强的完整性、安全性支持等。•SQL-99–正在讨论中的新的标准，将增加对面向对象模型的支持SQL概述•特点–一体化•集DDL，DML，DCL于一体•单一的结构----关系，带来了数据操作符的统一–面向集合的操作方式•一次一集合–高度非过程化•用户只需提出“做什么”，无须告诉“怎么做”，不必了解存取路径–两种使用方式，统一的语法结构•SQL既是自含式语言（用户使用），又是嵌入式语言（程序员使用）–语言简洁，易学易用SQL概述SQL功能操作符数据查询SELECT数据定义CREATE，ALTER，DROP数据操纵INSERT，UPDATE，DELETE数据控制GRANT，REVOKE1SQL命令基础准备工作•用SQLPLUS来学习SQL•连接到SQLPLUS，创建学习用的用户和数据•SCOTT用户在ORACLE805中默认已经创建，在8i和9i中需要手动运行ORACLE_HOME\rdbms\admin\scott.sql。ORACLE_HOME是ORACLE安装的主目录，在SQLPLUS中可以用?来代替。•例如：•SQLconnect/assysdba•Connected.•SQL@?\rdbms\admin\scott.sql•SQLconnectscott/tiger•Connected.基本的SELECT命令•SELECT命令用于从数据库中获得想要的信息。•语法：SELECT{*,column[alias],……}FROMtable;•一个最简单的查询语句至少要包括SELECT子句和FROM子句：•SELECT后面指定要选择的列•FROM后面指定从哪些表或视图中获取数据SQL语句基本规则•SQL命令是大小写不敏感•SQL命令可写成一行或多行•一个关键字不能跨多行或缩写•子句通常位于独立行，以便编辑，并易读•空格和缩进使程序易读•关键字大写，其他小写使用SELECT*可显示所有的列SQLSELECT*FROMdept;DEPTNODNAMELOC---------------------------------------------------------10ACCOUNTINGNEWYORK20RESEARCHDALLAS30SALESCHICAGO40OPERATIONSBOSTON选择指定的列•我们可以在SELECT后面指定要选择的列。SQLSELECTdeptno,locFROMdept;DEPTNOLOC-------------------------------------10NEWYORK20DALLAS30CHICAGO40BOSTON在SQL*PLUS中查看表具有哪些列•在SQLPLUS中，用DESCTABLENAME命令可以查看表具有的列以及类型等SQLdescdept名称是否为空?类型--------------------------------------------------------------DEPTNONUMBER(2)DNAMEVARCHAR2(14)LOCVARCHAR2(13)从数据字典中获取表的列信息•SQLSELECTTABLE_NAME,COLUMN_NAME,DATA_TYPE,DATA_LENGTH,NULLABLEFROMUSER_TAB_COLUMNSWHERETABLE_NAME='DEPT';•TABLE_NAMECOLUMN_NAMEDATA_TYPEDATA_LENGTHNULLABLE•--------------------------------------------------------------•DEPTDEPTNONUMBER22Y•DEPTDNAMEVARCHAR214Y•DEPTLOCVARCHAR213Y使用算术运算•可以对数字类型的字段进行算术运算。运算的先后顺序是先乘除，后加减，括号优先。•上面例子中，计算的是一年的报酬，月工资乘以12个月，再加上100SQLSELECTename,sal,12*sal+100FROMemp;ENAMESAL12*SAL+100-----------------------------------------------------------KING500060100BLAKE285034300CLERK245029500JONES297535800……14rowsselected。关于空值(NULL)•空值是指不可用，不知道，不适用的值•空值不等于零也不等于空格•对空值进行的任何运算仍然为空值SQLSELECTenameNAME,12*sal＋commFROMempWHEREename＝‘KING’;NAME12*SAL+COMM--------------------------------------------KING定义列的别名•当显示查询结果时，SQL*PLUS通常使列名作为列头。在很多情况下，列名并非是此列的清晰描述。因此我们就可使用列的别名作为列头，。缺省情况下，列的别名是大写的。如果区别大小写，可加双引号，如有特殊字符如‘$’、‘#’也必须使用双引号将其括起来。上面的例子中的AS可以省略SQLSELECTenameASname,salsalaryFROMemp;NAMESALARY----------------------------------------------……SQLSELECTenameName,sal*12AnnualSalaryFROMemp;NAMEAnnualSalary----------------------------------------------……列连接操作•使用双竖条”||”操作符，可将列和运算表达式常量连起来显示，形成一个输出显示SQLSELECTENAME||'''ssalaryis'||salEmployee'sSalaryfromemp;Employee'sSalary-----------------------------------------------------------------------SMITH'ssalaryis800ALLEN'ssalaryis1600WARD'ssalaryis1250JONES'ssalaryis2975………………………..已选择14行。去除重复记录•默认情况下，显示所有行，包括重复记录。如果想去掉重复记录，可以在DISTINCT关键字。如果在DISTINCT后面指定了多个列，则DISTINCT将对所有被选择的列有效，其结果是不同的列的组合。SQLSELECTdeptnoFROMemp;DEPTNO------------------103010……14rowsselected。SQLSELECTDISTINCTdeptnoFROMemp;DEPTNO------------------102030SQL*PLUS程序介绍•SQL*PLUS是是Oracle自带的与Oracle交互的一个工具。你可在SQL*PLUS中做以下操作：•执行SQL命令来修改、查询、增加、删除数据库中的数据•格式化、计算、存储、数据于一定格式的报告中•产生用于存储SQL命令的脚本，以便以后执行•SQL*PLUS专用命令可被分为如下类别：•环境：影响通常的SQL命令•格式化：格式化查询结果•文件处理：存储、调用、运行脚本文件•编辑：修改SQL缓存中的SQL命令•显示列的定义登陆到SQL*PLUS•在命令行提示符下输入SQLPLUS/NOLOG•然后在SQL提示符下输入•CONNECTUSERNAME/PASSWORD@DATABASEC:\DocumentsandSettings\AdministratorSQLPLUS/NOLOGSQL*Plus:Release9.2.0.4.0-Productionon星期四5月2409:51:422007Copyright(c)1982,2002,OracleCorporation.Allrightsreserved.SQLconnectsystem/manager@tlgaxz已连接。SQL*PLUS的编辑命令•SQL*PLUS的命令每次只能键入一行，且不能存于SQL缓存中•A[PPEND]text将text加到当前行的后面•C[HANGE]/old/new将当前行的old改为new•C[HANGE]/text/从当前行中删除text•C[LEAR]BUFF[ER]从缓存中删除所有的行•DEL删除当前行•L[IST]：列出SQL缓存中的所有行•L[IST]n：列出一行(由n指出列出的行)•R[UN]：显示并运行SQL缓存中的SQL语句SQL*PLUS的编辑命令(续)•GETfilename[.ext]：把文件的内容写到SQL缓存•@filename[.ext]：运行文件•ED[IT]：调用编辑器编辑当前缓存中的内容•ED[IT]filename.[ext]：调用编辑器编辑存的文件•SPOOL[filename[.ext]：将查询结果存于文件中•SPOOLOFF结束结果内容输出•EXIT：退出SQL*PLUS2限定和排序数据目的•限制某一查询所取记录•排序查询结果使用选择限定记录在上面的例子中，假定你想要显示部门10的所有员工，这种方式是基于WHERE子句的SQL命令。使用where子句限定返回的记录•WHERE子句在FROM子句后面•Condition:由列名表达式，常量和比较操作符组成。SELECT〔DISTINCT〕{*,column[alias],……}FROMtable〔WHEREcondition(s)];使用WHERE语句•上面例子中返回job=‘CLERK’的所有员工的name,job和deptno•注意：字符的大小写是敏感的。SQLSELECTename,job,deptnoFROMempWHEREjob='CLERK';ENAMEJOBDEPTNO-----------------------------------------------------------KINGCLERK30BLAKECLERK20TURNERCLERK10……14rowsselected。字符串和日期•在WHERE子句中的字符串和日期必须用单引号括起来，所有的字符是大小写敏感的。•Oracle存储日期是以内定的格式存放，它们代表世纪、年、月、日、小时、分钟和秒，缺省显示的日期格式是DD-MON-YY，也可能是其他格式。为了避免日期字段查询条件不同格式下可能造成的错误，通常用TO_DATE函数来进行转换。日期类型查询条件举例•上面第二个例子使用了TO_DATE函数，就不再受日期格式的影响了。否则同样的查询，同样的表数据，不同的系统日期格式会导致结果不正确。SQLSELECTENAME,HIREDATEFROMEMPWHEREHIREDATE='03-DEC-81';ENAMEHIREDATE-------------------JAMES03-DEC-81FORD03-DEC-81SQLSELECTENAM