SQL语言基础

一．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语句

１．

     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显示了该列在当前条件下的顺序号。

２．算术表达式

＋　－　×　／

可以对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值。对空值进行连接字符串的操作之后，返回为被连接的串。

３．去除相同的行

如：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 7^th,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是惯例。