51CTO下载-Oracle 11g学习笔记基础篇

SQL语句中不区分大小写，习惯性把关键字用大写表示，其他用小写。在Oracle里字符串数组小标都是从1开始。在SQL*PLUS中必须用“;”结束语句，也可以用“/”，否则只会当作换行。null值表示未知状态，但不代表0或者空。当null跟算术表达式一起就等于null。当null与逻辑运算表达式一起就等于空。语句类型DML:INSERT,UPDATE,DELETEDDL:对表的管理操作DCL:设置数据库用户，权限等主要数据类型CHARACTER类型CHARVARCHAR2LONGNUMBER类型NUMBER(p,s)p代表数的模型（有没有小数点之类），s代表位数DATE类型RAW类型和LONGRAW类型用于存储二进制数据LOB类型CLOB超级long字符集，最长达到4GBLOB二进制字符集，最长达到4GBFILE指针，指向多媒体文件ROWID基于64位操作系统设定每个表的都有一列作标记每一行数据库的对象：Table表View视图（一般只读），存储查询语句，现用现查Sequence序列Index索引Synonym别名Procedure存储过程Trigger触发器数据库命名规范1.必须以字母开头2.最大长度30字符3.只能按有A-Z,a-z，0-9,_,$和#(通常不用$和#)4.相同类型的数据名称不能重复5不能包含oracle的关键字基本语法2011年8月16日下午01:18基础第1頁4.相同类型的数据名称不能重复5不能包含oracle的关键字数据定义命令CREATE命令ALTER命令DROP命令TRUNCATE命令数据操纵命令INSERTSELECTUPDATEDELETE事务处理和数据控制命令事务处理控制命令COMMITSAVEPOINTROLLBACK数据控制语言GRANTRREVOKE语句优先级从高到低：SELECT——FROM——WHERE——GROUPBY——组合函数——HAVING——ORDERBYWHERE,GROUPBY,HAVING语句中都不能使用别名select语法SELECT*FROMTable创建现有表的副本CREATEtabletable_nameASSELECTcolumn_namefromexisting_table_name插入来自其他表达记录INSERTINTOtable_name(SELECTcolumn_namefromexisting_table_name)为列显示不同名称SELECTcolumn_namealias_namefromtable_name可以使用算术表达式,只有数字才能使用乘除法例如：Selectlast_name,salary,3*salary+300Fromemployees在程序查询中为了提高速度通常不用*，而是把表中所有的字段名罗列出来SQL*PLUS中的运算符算术运算符基础第2頁算术运算符用于执行数学计算，+，-,*,/比较运算符用于将一个表达式与另一个表达式进行比较。=,!=,,,=,=,IN,LIKE,ISNULL,BETWEEN,NOTBETWEENBETWEENa（包含a）ANDb（包含b）判断某值包含在一组集合里LIKE模糊查询LIKE''%——代表多个字符_——代表单个字符例如：查找以S开头例如：查找第二字字母是‘o’SELECTlast_nameFROMemployeeWHERElast_nameLIKE'_o%'如果查找字段本身包含_或者%，要用\_或者\%.(|_或|%也可以）基础第3頁如果查找字段本身包含_或者%，要用\_或者\%.(|_或|%也可以）判断字段中是否有NULLSELECTlast_name,managere_idFROMemployeesWHEREmanager_idISNULL不含有NULLSELECTlast_name,managere_idFROMemployeesWHEREmanager_idISNOTNULL逻辑运算符用于合并两个条件的结果以产生单个结果。AND,OR,NOT。集合运算符用于合并两个独立查询结果。UNION,UNIONALL,INTERSECT,MINUS连接运算符用于将两个或多个字符串并在一起。||表示连接运算符。例如：SELECTlast_name||job_idASEmployeesFROMemployee表达式优先级序号表达式1算术表达式2链接表达式3比较表达式4IS[NOT],NULL,LIKE,[NOT]IN5[NOT]BETWEEN基础第4頁6不等于7NOT8AND9ORWHERE语句条件判断SELECT*FROMtableWHEREdepartment=90(当使用了WHERE就不能使用别名）用WHERE使用查询字符串内容时，需要用''，例如WHEREhire_date='17-FEB-96'当不能确认查询内容的大小写可以用以下两种方法来进行查询1.WHEREUPPER(last_name)='WILL'将查询字段的内容全部转换成大写再对比，其字段内容自身不发生改变2.WHERELOWER(las_name)='will'将查询字段的内容全部转换成小写再对比，其字段内容自身不发生改变Aliases列的别名保留别名的大小写或别名中含有其他非英文数字的符号，需要用双引号包起来。例如：SELECTlast_nameASnameFROMemployee或者：SELECTlast_namenameFROMemployeeLiteralCharcterStrings字符串使用单引号''例如：SELECTlast_name||'isa'||job_idASEmployeeDeailsFROMemployeeAlternativeQuoteOperator引用操作符如果要输出显示关键字或操作符的内容，需要用q'[]'包起来，也可以用感叹号，要成对。例如：q'['sManagerId:]'SELECTdepartment_name||'Department'||FROMdepartments基础第5頁FROMdepartments显示不重复的列SELECTDISTINCTdepartment_id,tv,vcFROMemployeesDESCRIBE显示数据表的基本结构（不是Oracle的语句，是SQLPLUS的命令）不能显示主键，外键DESC[RIBE]tablenameORDERBY排序语句（默认升序）可以使用别名。ASC升序，DESC降序。排序中升序时NULL在最后，降序时NULL在最前。可以用NULLFIRST或者NULLLAST修改NULL的位置。例如：SELECTemployee_id,last_name,salary*12annsalFROMemployeesORDERBYannsal按照第3个字段排序，在这个例子中是按照department_id排序SELECTlast_name,job_id,department_id,hire_dateFROMemployeesORDERBY3按照两种方式排序：例：按departmen_id升序排列并且按salary降序排列SELECTlast_name,department_id,salaryFROMemployeesORDERBYdeparment_id,salaryDESC基础第6頁SubstitutionVariables变量（客户端独有）&变量名，即使变量名相同，每次引用都是新的变量，也就是多次引用，类似形参&&变量名，同一个变量可以多次使用若变量为字符串的时候必须用'',如'&变量名'或者'&&变量名'DEFINE定义常量，反之UNDEFINEDEFINEemployee_num=200可以被同名变量直接引用VERIFY指令查看变量改变情况SETVERIFYON显示变化，SETVERIFYOFF不显示变化GROUPBY分组用于将信息划分为较小的组，每一组返回针对每组的单个结果。语句中不能使用别名.ORDERBY只能在复合查询中使用一起，并且在最后才使用。语法SELECTcolum,group_function(column)FROMtable[WHEREcondition][GROUPBYgroup_by_expression][ORDERBYcolumn]例子：（进行两次分组）SELECTdepartment_iddept_id,job_id,SUM(salary)FROMemployeesGROUPBYdepartment_id,job_idORDERBYdepartment_id注意：SELECT里面包含的字段如果不是计算函数，要写在GROUPBY字句当中。基础第7頁HAVING语句条件判断用于指定针对行的某些条件，它限定组中的行。语句中不能使用别名语法：SELECTcolumn,group_functionFROMtable[WHEREcondition][GROUPBYgroup_by_expression][HAVINGgroup_condition][ORDERBYcolumn]ROLLUPROLLUP(column_name1,column_name2)结果集包含非分组行和小计行CUBECUBE(column_name1,column_name2,column_name3)产生交叉表格报表临时表只在用户会话期间存在的表。数据在用户事务处理期间持续存在，使用CREAEGLOBALTEMPORARYTABLE命令，不会自动获得空间，插入的值仅在此特定会话中可用。基础第8頁单行函数对每一行返回一个值，也成为标量函数，在SELECT和WHERE子句中使用Function_name*(arg1,arg2,…..)+函数名称结果LOWER('SQLCourse')sqlcourseUPPER('SQLCourse')SQLCOURSEINITCAP('SQLCourse')SqlCourse字符函数函数名称结果解释CONCAT('Hello','World')HelloWorld合并字符串SUBSTR('HelloWorld',1,5)Hello从第1个字母开始抽5个字母LENGTH('HelloWorld')10求字符串的长度INSTR('HelloWorld','W')6查找首次出现w的位置返回小标LPAD(salary,10,'*')*****24右填充*使得字符串是10个字符RPAD(salary,10,'*')24000*****左填充*使得字符串是10个字符REPLACE('JACKandJUE','J','BL')BLACKandBLUE把字符串里的J替换成BLTRIM('H'FROM'HelloWorld')ehlloWorld去掉H和空格数字函数函数名称结果解释ROUND(45.925,2)45.93保留小数点后2位TRUNC(45.926,2)45.92保留2位有效数字MOD(1600,300)100求余数基本函数2011年8月17日上午10:41基础第9頁日期函数（查看当前日期）SELECTsysdateFROMdual例子：计算员工在公司呆了多少周SELECTlast_name,(SYSDATE-hire_date)/7ASWEEKSFROMemployeesWHEREdepartment_id=90函数名称结果解释MONTHS_BETWEEN（'01-SEP-95','11-JAN-94')19.6774194两个日期相差多少个月ADD_MONTHS('31-JAN-96')29-FEB-96加多个月基础第10頁ADD_MONTHS('31-JAN-96')29-FEB-96加多个月NEXT_DAY('01-SEP-95','FRIDAY')08-FEB-95指定时间的下一个星期几LAST_DAY('01-FEB-95')28-FEB-95某月的最后一天假设现在的日期是：SYSDATE='25=JUL-03'函数名称结果ROUND(SYSDATE,'MONTH')01