一.SQL
SQL 语文共分为三大类:数据操纵语言DML,数据定义语言DDL,数据控制语言DCL。
数据操纵语言DML主要有四种形式:
1) 插入:INSERT 2)更新:UPDATE 3)删除:DELETE 4)选择:SELECT
数据定义语言DDL用来创建数据库中的各种对象—
表、视图、索引、同义词、聚簇等(TABLE/VIEW/INDEX/SYN/CLUSTER)
语句:
CREATEDATABASE 创建数据库
CREATE TABLE 创建表
DROP TABLE 删除表
ALTER TABLE 修改表的结构
CREATE VIEW 创建视图
DROP VIEW 删除视图
CREATE INDEX 创建索引
DROP INDEX 删除索引
数据控制语言DCL用来授予或回收访问数据库的某种特权,并控制,数据库操纵事务发生的时间及效果,对数据库实行监视等。如
1)GRANT :授权 2)REVOKE:取消权限 3)ROLLBACK[WORK]TO[SAVEPOINT]:回退到某一点。
回滚 –rollback 回滚命令使数据库状态回到上次最后提交的状态,其格式为:
SQL>ROLLBACK;
4)COMMIT[WORK]:提交
在数据库的插入、删除和修改操作时,只有当事务在提交到数据库时才算完成,在事务提交前,只有操作数据库的这个人才能有权看到所做的事情,别人只有在最后提交完成后才可以看到。提交数据有三种类型:显式提交、隐式提交及自动提交。
1) 显式提交
用COMMIT命令直接完成的提交为显式提交,其格式为:SQL >COMMIT
2) 隐式提交
用SQL命令间接完成的提交为隐式提交,这些命令是:
ALTER/AUDIT/COMMENT/CONNECT/CREATE/DISCONNECT/DROP/EXIT/GRANT/NOAUDIT/QUIT/REVOKE/RENAME
3) 自动提交
若把AUTOCOMMIT设置为ON,则在插入、修改、删除语句执行后,系统将自动进行提交,这就是自动提交。其格式为:
SQL>SET AUTOCOMMIT ON;
二、基本SELECT语句
1.
SELECT*|{[DISTINCT]column|expression[alias],…} FROM table;
是一个或多个字段的列表、选择所有的列、禁止重复、选择指定的字段或表达式、给所选择的列不同的标题、指定包含列的表
例如:select *from tal; select distinct col1,col2 from tab1; select col1 c1,col2 c2 fromtab2;
一个指定被显示的列,列名不能重复;
一个from子句,指定表,该表包含select子句中的字段列表,可以指定相同的表,但是需要用表的缩写区分开来。
选择指定的列:select department_id,department_name from test;
另外,一些可用的伪列是CURRVAL、NEXTVAL、LEVEL、ROWID和ROWNUM。CURRVAL、NEXTVAL来自于序列(SEQUENCE),LEVEL来自于分析函数,ROWID显示了该列的实际存储位置,ROWNUM显示了该列在当前条件下的顺序号。
2.算术表达式
+ - × /
可以对number、date、timestamp使用加减操作。
如:select2+department_id,dpartment_id-2,department_id*3,department_id/5 from test;
乘法和除法比加法和减法的优先级高;相同优先级的运算符从左到右计算,圆括号用于强制优先计算,并且使语句更清晰。
任何数据类型的列都可以包含空,但是存在某些约束,如 NOT NULL 和 PRIMARY KEY,防止在列中使用空。
空值无法和数字型的列和常量进行计算,但是可以和字符串类型的列和常量进行连接。如:null 减去 number型、null 加上 number型、null值连接上一个字符、null值乘以2、null值除以2、2除以null值。对空值进行连接字符串的操作之后,返回为被连接的串。
3.去除相同的行
如:select * distinct *from numtest;
4.约束和排序
条件:在语句where子句之后,限制查询的行数。
排序:在语句的order by子句后,决定了行的输出顺序。
5.比较条件:
Araclesql 中的比较条件包含以下几种运算符号: = > >= < <= <> != ^=(不等于) between ..and..(在两个值之间包含) In(set) (匹配一个任意值列表) like(匹配一个字符模板) isnull (空值) is not null (不是一个空值)
如:selectlast_name,salary from emp where salary between 3000 and 3500;
Select employee_id,last_name,salary from emp where manager_idin(100,101,201);
Select employee_id,manager_id,department_idfrom emp where last_name in(“har”,”varg”);
Select first_name from emp where first_name like ‘S%’;
如何搜索实际的%和_ 符号,使用escape标识符可以搜索实际的%和_符号。
如果你想要搜索包含”sa_”的字符串,可以用下面的sql语句;
Select employee_id,last_name,job_id from emp where job_id like ‘%SA\_%AN’Escape ‘\’;
如果你想搜索包含%SA的字符串,可以使用下面的sql语句:
Select * from test where department_name like ‘\%SA%’ escape ‘\’;
Escape 选项指定反斜线(\)为换码符,在模板中,换码字符在下划线(_)和百分号(%)的前面,原因是oracle服务器逐字地解释字符串。
6.使用null/not null条件
Selectlast_name,manager_id from emp where manager_id is null;
Null条件,包括 null条件和is notnull条件,is null 条件用于空值测试。空值的意思是难以获得的,未指定的,未知的或者不适用的。因此,不能用 = ,因此null不能等于或不等于任何值。
Select last_name,job_id,commission_pct fromemp where commission_pct is null;
Select last_name,job_id,commission_pct fromemp where commission_pct is not null and rownum<10;
7.逻辑条件
SQL的三个逻辑运算符是: and 、or 、not
8.优先规则
优先规则定义表达式求值和计算的顺序,表中列出了默认的优先顺序,你可以用圆括号括住你想要先计算的表达式来覆盖默认的优先顺序。
1.算术运算。2.连字操作。3.比较操作。 4.IS[NOT]NULL,LIKE,[NOT]IN 5.[NOT]BETWEEN 6.NOT逻辑条件 7.AND逻辑条件 8.OR逻辑条件
如:selectlast_name,job_id,salary from emp where job_id=’SA_REP’ OR job_id=”AD_PRES’ andsalary>15000;
9.排序
升、降序排序。升序:按列值由小到大,降序:按列值由大到小
用列别名排序
多列排序。多个排序列由逗号分隔,首先以最左边列的顺序为准,如遇到重复值再以接下来的一列为准。
语法:select expr from table [where condition(s)] [order by{column,expr}[asc|desc]];
Order by 指定排序显示返回的行,asc以升序排序(默认);desc以降序排序行。Order by 子句在select 语句的最后,可以指定一个表达式,或者一个别名,作为排序条件。
默认排序为升序,对于数字值,小的前面显示;1-99;日期,早的在前面显示:01-jan-92在 01-jan-95.
对于字符串,依字母顺序显示,如 a-z; 对于空值,升序最后显示,降序显示在最前面。
用列别名的排序:selectemployee_id,last_name,salary * 12 ann from emp order by ann;
能够在order by 子句中使用列别名,例子用年薪排序数据,在内部,select 语句的执行顺序如下:from 子句,where子句,select 子句,order by 子句;
如:selectlast_name,department_id,salary from emp order by department_id,salary desc;
想要倒序排序一个列,就在该列名后面指定desc,也可以用没有包括在select子句中的列排序。
三、oracle sql 单行函数
1.sql函数分类
单行函数和多行函数
单行函数:这些函数仅对单个行进行运算,并且每行返回一个结果,有不同类型的单行函数,其函数类型有:字符、数字、日期、转换
多行函数:能够操纵成组的行,每个行组给出一个结果,这些函数也被称为组函数。
2.单行函数
只对一行值进行操作,如有多行值,将分别对多行值进行操作,而不会将多行值作为一个整体进行操作;
语法:function_name[(arg1,arg2,…)]
特性:作用于查询中返回的每一行,每行返回一个结果,可能返回一个与参数不同类型的数据值,可能需要一个或多个参数,能够用在select/where和order by 子句中,可以嵌套。
在语法中:function_name是函数的名字。
Arg1.arg2是由函数使用的任意函数,可以由一个列名或者一个表达式提供。
单行函数:
字符函数:接受字符输入,可能返回字符或者数字值
数字函数:接受数字输入,返回数字值
日期函数:对date数据类型的值进行运算(除了months_between函数返回一个数字,所有日期函数都返回一个date数据类型的值)。
转换函数:从一个数据类型到另一个数据类型转换一个值
通用函数:
NVL NVL2 NULLIF COALSECE CASE DECODE
DUAL表通常用于select 子句语法的完整,因为不管是select还是from 子句都是强制的,并且一些计算不需要从实际的表中选择。
3.字符函数
大小写处理函数:
数量较少、常用的只有三个:LOWER(c)、UPPER(c)、INITCAP(c);
作用:对大小写进行转换处理
字符串处理函数:
CONCAT / SUBSTR / LENGTH/ INSTR / LPAD|RPAD / TRIM / REPLACE
数量很多,且功能强大,对字符串进行连接、搜索、截取等操作
4. 大小写处理函数:
LOWER(c) 将指定字符串内字符变成小写,支持CHAR,VARCHAR2,NCHAR,NVARCHAR2,CLOB,NCLOB类型;
如:selectLower(“What is tHis ‘) from dual;
UPPER(c) 将指定字符串内字符变成大写,支持CHAR,VARCHAR2,NCHAR,NVARCHAR2,CLOB,NCLOB类型;
如:selectUpper(‘whaT is tHis’) from dual;
INITCAP(c) 将字符串中单词的第一个字母转换为大写,其他则转换为小写
如:selectInitcap(“what is tHis’) from dual;
5.字符处理函数
CONCAT(c1,c2) 连接字符串,等同于||
例如: select Conncat(‘aa’,’bb’)from dual;
SUBSTR(c1,n1[,n2])截取指定长度的字符串,稍不注意就可能充满了陷阱的函数.
,n1 >0,则 oracle从左向右确认起始位置。
Select Substr(‘what this’,5,3) fromdual;
,n1<0,则oracle从右向左确认起始位置;
Select Substr(‘what this’,-5,3)fromdual;
,n1>c1.length则返回空
Select substr(‘what this’,50,3)fromdual;
LENGTH(c)返回指定字符串的长度。
例:selectLength(‘a1234中’) from dual;
INSTR(c1,c2[,n1[,n2]])返回c2在c1中位置
C1,原字符串 c2:要寻找的字符串 n1查询起始位置,正值表示从左到右,负值表示从右到左。如果为0的话,则返回的也是0;n2第几个匹配项,大于 0
如:selectInstr(‘abcd’,’e’,-3)from dual;
LPAD(c1,n[,c2])返回指定长度=n的字符串,需要注意的有几点:
如果n<c1.length则从右到左截取指定长度返回;
如果n>c1.lengthand c2 is null, 以空格从左向右补充字符长度至n并返回;
如果n>c1.lengthand c2 is not null, 以指定字符c2从左向右补充c1长度至n并返回。
Select Lpad(‘whaT is tHis’,5),Lpad(‘whaTis tHis’,25),Lpad(‘whaT is tHis’,25,’-‘) from dual;
RPAD(c1,n[,c2])返回指定长度=n的字符串,基本与上同,不过补充字符是从右向左方向正好与上相反。
如:selectRpad(‘what is tHis’,5),Rpad(‘whaT is tHis’,25),Rpad(‘what is this’,25,’-‘) fromdual;
TRIM ([[LEADING||TRAILING||BOTH]c2 FROM] c1),
如果没有指定任何参数则oracle去除c1头尾空格;
如:selectTrim( ‘ what is this ‘) from dual;
指定了c2参数,则oracle去掉c1头尾c2
如:selecttrim(‘w’ from ’ what is this w W’)from dual;
如果指定了leading参数则会去掉c1头部c2;
如:selectTrim(leading ‘W’ from ‘what is this w W‘)from dual;
如果指定了trailing参数则会去掉c1尾部c2
如:selectTrim(Trailing ‘W’ from ‘what is this w W’) from dual;
如果指定了both参数则会去掉c1头尾c2
如:selectTrim(Both ‘W’ from ‘what is tHis w W’)from dual;
REPLACE(c1,c2[,c3])将c1字符串中的c2替换为c3,如果c3为空,则从c1中删除所有c2;
如:selectreplace(‘WWhhhat is tHis w W’,’W’,’-‘) from dual;
6.数字函数;
数字函数包括 ROUND/ TRUNC/MOD;
ROUND(n1[,n2]) TRUNC(n1[,n2]) MOD(n1,n2)
7.日期函数
系统日期:每个系统日期都被分为7部分进行存储;世纪、年份/月/日/小时/分钟/秒,对于任何日期默认显示和输入格式是DD-MOD-RR。有效的Oracle日期在公元前4712年1月1日和公元9999年12月31日之间。
完整的日期可能是1994年7月7日 5:10:43 p.m(june 7th,1994 5:10:43 p.m).
该日期在内部存储如下:
Century year month day hour minute second
19 94 06 07 5 10 43
Oracle 数据库存储年为1996或2001,而不是仅仅存 96或 01
系统日期: alter session set nls_date_format =’mm-dd-yyyy’;
Select Sysdate,Current_Date from dual;
用日期计算:日期可以加减少数字,两个日期间可以相减
Select Last_Name,(Sysdate-hire_date)/7 asweeks from Emp where Department_id=90;
Months_between(d1,d2) 返回d1和d2间的月份差,视d1,d2的值大小,结果可正可负,当然也有可能为0
Add_Months() 返回指定日期月份+n之后的值,n可以为任何整数
Next_Day(D,n)返回指定日期后第一个n的日期,n为一周中的某一天,但是,需要注意的是n如果为字符的话,它的星期开工需要与当前session默认时区中的星期形式相同。
Last_Day(D)返回指定时间所在月的最后一天。
Round(d[,fmt])前面讲数值型函数的时候介绍过Round,此处与上功能基本相似,不过此处操作的是日期。如果不指定fmt,则默认返回距离指定日期最近的日期。
Trunc(d[,fmt]) 与前面介绍的数值型trunc原理相同,不过此处也是操作的日期型;
TRUNC(number,num_digits) Number需要截尾取整的数字。 Num_digits 用于指定取整精度的数字。Num_digits的默认值为0.TRUNC()函数截取时不进行四舍五入
例如:
SelectMonths_between(Sysdate,Sysdate),Months_Between(Sysdate,Add_Months(Sysdate,-1)),Moths_Between(Sysdate,Add_Months(Sysdate,-1))from dual;
SelectAdd_Months(Sysdate,12),Add_Months(Sysdate,-12) from dual;
Select Next_Day(Sysdate,5) from dual; select next_day(sysdate,’星期四’)fromdual;
Select last_day(Sysdate) from dual;
Select Round(Sysdate,’HH24’) from dual;
Select Trunc(sysdate,’HH24’) from dual;
英文的日期格式:
JAN FEB MAR APR MAY JUN JUL AUG SEP OCT NOV DEC
8.转制函数
1.显式数据类型转换(另外一种是隐身类型转换)
SQL 提供三种函数来从一种数据类型转换值到另一种:To_Char,TO_Number,To_Date;
To_Char() 函数又可以分为三小类,分别是:
转换字符->字符To_char(c):将nchar,nvarchar2,clob,nclob类型转换为char类型。
如:select to_cahr(‘aabbcc’)from dual;
转换时间-> 字符to_char(d[,fmt]):将指定的时候(date,timestamp,timestamp with time zone)按照指定格式转换为varchar2类型。
必须加单引号,并且区分大小写,能够包含任一有效的日期格式元素,有一个fm元素来删除填补的空,或者前导零,用一个逗号与日期值分开。
如:selectto_char(Sysdate,’yyyy-mm-dd hh24:mi:ss’) from dual;
转换数值->字符to_char(n[,fmt]):将指定数值n按照指定格式fmt转换为varchar2类型并返回;
如:select to_char(-100,’L99G999D99MI’)from dual;
TO_Date(c[,fmt[,nls]])将char,nchar,varchar2,nvarchar2转换为日期类型,如果fmt参数不为空,则按照fmt中指定格式进行转换。注意这里的fmt参数,如果fmt为“j”则表示按照公元制(julian day)转换,c则必须为大于 0 并小于5373484的正整数。
如:selectto_date(2454336,’J’) from dual;
Select To_date(‘2007-5-23 23:25:00’,’yyyy-mm-dd hh24:mi:ss’) from dual;
To_Number(c[,fmt[,nls]]) 将char,nchar,varchar2,nvarchar2型字串按照fmt中指定格式转换为数值类型并返回。
如:select To_Number(‘-100.00’,’9G999D9’) from dual;
2.日期格式模板
YYYY数字全写年 YEAR 年的拼写 MM/ MONTH/MON 月 DY/ DAY/ DD 日
3.时间格式模板
AM/PM正午指示
A.M. / P.M. 带句点的正午指示
HH或 HH12 或 HH24 天的小时,或小时(1-12),或小时(0-23);
MI分钟(0-59)
SS 秒(0-59)
SSSSS 午夜之后的秒(0-86399)
4.数字格式模板
9表示一个数 0强制显示为零 $放置一个浮动美元符号 L使用浮动本地符号
.打印一个小数点 ,打印一个千位指示 MI 右边减号(负值)
PR 将负数加上括号 EEEE科学计数法(格式化必须指定四个E )
V 乘10,N次(n=v后面9的个数)
B 将0 显示为空格
9.嵌套函数
单行函数能够被嵌套任意层次,嵌套函数的计算是从最里层到最外层。单行函数可以嵌套任意深度。嵌套的函数按从最里层到最外层的顺序求值。
SelectLast_name,Nvl(To_char(Manager_id),’No Manager’) from Emp where manager_id isnull;
10.通用函数
可以几乎对所有的数据类型进行操作,常用的有:
NVL 、 NVL2 、NULLIF 、COALESCE
如:selectnvl(null,’12’) from dual;
Select nvl2(‘a’,’b’,’c’),nvl2(null,’b’,’c’) from dual;
Select nullIf(‘a’,’b’),nullif(‘a’,’a’) from dual; 如果c1=c2,则null,如果c1!=c2,则 c1.;
Select coalesce(null,3,6,null,9) from dual;
11.条件表达式
常用的条件表达式有case表达式,Decode函数
Case是SQL标准协会支付宝的条件表达式,每种数据库中都提供case,但各自的使用可能会略有差异;
Decode 是Oracle独有的条件表达式,功能和case类似。
在SQL 语句中提供IF _then_else 逻辑的使用,两种用法 case 表达式,decode函数。Case表达式与 Ansi SQL 兼容,decode是特殊的Oracle语法;
Case表达式语法:
EndCaceexpr when comparison_exprlTHEN return_expr1 [WHENcomparison_expr2 then return_expr2 when comparison_exprn then return_exprn ELSEelse else_expr] end
Case 表达式可以让你在SQL语句中使用If_then_else逻辑,而不必调用过程。在简单的case表达式中,Oracle查找每一个when_then对,如果expr等于comparison_expr,返回相应的return_expr.如果没有when..then 对满足条件,并且else子句存在,Oracle返回else_expr.否则,Oracle返回null.你不能对所有的return_exprs 和else_expr 指定文字null;
所有的表达式expr/comparison_expr/return_expr必须是相同的数据类型,可以是char/varchar2/nchar/nvarchar2;
还有一种case查找表达式,Oracle从左到右查找进到找到一个为真的条件,然后返回return_expr,如果没有找到为真的条件,而且Else子句存在,Oracle返回else_expr.否则Oracle返回为空;
如:selectLast_name,Job_id,salary,case Job_id
When ‘iT_Prog’ then
1.10 * salary
When ‘st_clerk’ then
1.15 * salary
When ‘sa_rep’ then
1.20 * salary
Else
Salary
End “Revised_salary”
From emp;
Decode函数: 实现行转列等功能;
语法:decode(exp,r1,s2,r2..s,r[,def])可以把它理解成一个增强型的if else,只不过它并不通过多行语句,而是在一个函数内实现if else 的功能。
Selectdecode(‘a2’,’a1’,’true1’,’a2’,’true2’,’default’) from dual;
Select last_name,job_id,salary,decode(Job_id,’it_prog’,1.10* salary,’st_clerk’, 1.15 * salary ,’ sa_rep’,1.20*salary,salary)revised_salary from emp;
总结:
单行函数可以进行以下操作:
字符数据:lower ,upper,initcap, concat , substr, instr, length
数字数据:round, trunc,mod
日期数据:months_between,add_months,next_day,last_day,round,trunc.
日期值也可以用算术运算;
转换函数可以转换字符、日期和数字值:to_char/to_date/to_number;
有几个函数处理空值:它们包括nvl/nvl2/nullif和 coalesce;
If_then_else逻辑可以在sql语句中用case表达式或decode函数来代替。
Sysdate 和 dual:sysdate是一个日期函数,它返回当前日期和时间,从一个称为dual的虚拟表中选择 sysdate是惯例。