02. sql note.txt

在达内培训java笔记

先登陆服务器：    telnet 192.168.0.23    公帐号： openlab-open123    tarena-tarena
再进入SQL：sqlplus sd0807/sd0807        帐号：sd0807－密码同样  公帐号：openlab-open123 

设置环境变量：
   ORACLE_SID=oral10g\    --变局部变量
   export ORACLE_SID      --变全局变量
   unset ORACLE_SID       --卸载环境变量
   ORACLE_HOME=...        --安装路径；直接用一句语句也可以，如下
   export ORACLE_HOME=/oracledata/.../bin:

一、注意事项：
    大小写不敏感，即不区分大小写。提倡关键字大写，便于阅读和调式。
    “！”在SQL环境下执行Unix命令。
    SQL语句是由简单的英语单词构成；这些英语单词称为关键字/保留字，不做它用。SQL由多个关键字构成。
    SQL语句由子句构成，有些子句是必须的，有些是可选的。
    在处理SQL语句时，其中所有的空格都被忽略(空格只用来分开单词，连续多个空格当一个用)。
    SQL语句可以在一行上写出，建议多行写出，便于阅读和调试。
    多条SQL语句必须以分号分隔。多数DBMS不需要在单条SQL语句后加分号，但特定的DBMS可能必须在单条SQL语句后加分号。
    SQL语句的最后一句要以 “；”号结束

二、写子句顺序
    Select column,group_function
    From  table
    [Where  condition]
    [Group by  group_by_expression]
    [Having    group_condition]
    ……
    [Order by  column];    --最后

三、常用简单语句：
    clear screen：清屏
    edit：编辑刚才的一句。
    desc/describe：(列出所有列名称)
        用法： DESCRIBE [schema.]object[@db_link]
    dual：亚表，临时用。如：desc dual；／from dual；
    rollback：回溯，回溯到上次操作前的状态，把这次事务操作作废，只有一次(DDL和DCL语句会自动提交，不能回溯)。
        可以用commit语句提交，这样就回溯不回了。
	set pause on\off ：设置分屏(设置不分屏) 
	set pause "please put an enter key" 且 set pause on：设置带有提示的分屏
	oerr ora 904 ：查看错误
	set head off ：去掉表头
	set feed off ：去掉表尾
	保存在oracle数据库中的所有操作细节：
	    spool oracleday01.txt ：开始记录
	    spool off ：开始保存细节

四、SELECT语句：选择操作、投影操作。
select：从一个或多个表中检索一个或多个数据列。包含信息：想选择什么表，从什么地方选择。必须要有From子句。(最常用)
        当从多张表里查询的时候，会产生笛卡尔积；可用条件过滤它。
        当两个表有相同字段时必须加前缀，列名前需加表名和“.”，如“s_emp.id”。
    1、用法：SELECT  columns,prod2,prod3<列>  FROM Table1,table2<表名>  分号结束
       如： select id from s_emp;
           select last_name,name from s_emp,s_dept where s_emp.dept_id=s_dept.id;--列表每人所在部门
           SELECT *  FROM Products;    --检索所有列。
           数据太多时，最好别使用上句，会使DBMS降低检索和应用程序的性能。(*通配符)
    2、对数据类型的列可进行运算(如加减乘除)。
    3、对列起别名：有直接起别名，加AS起别名，用双引号起别名等三种方法
       (单引号，引起字符串；双引号，引起别名。起别名有符号，或者区分大小写时，必须用双引号)
        多表查询时，可给表起别名。(给列起别名，列<空格>列别名；给表起别名，表<空格>表别名；)。
        如：Select first_name EMPLOYEES, 12*(salary+100) AS MONEY, manager_id "ID1" From s_emp E;
    4、字段的拼接,可用双竖线(双竖线只能用于select语句里)。不同的DBMS可能使用不同的操作符;拼接的字段同样可以起别名。
        如：Select  first_name ||' '|| last_name || ', '|| title "Employees" From s_emp;

排他锁：Select id,salary  From s_emp where id=1  For Update;
   可以阻止他人并发的修改，直到你解锁。
   如果已有锁则自动退出：Select id,salary From s_emp where id=1 For Update NoWait;
   FOR UPDATE ：可以再加 OF 精确到某格。如：   ... For Update  OF salary ...  
   注意要解锁。

五、ORDER BY 子句，排序
Order by：按某排序列表(默认升序 asc，由低到高；可加 desc，改成降序由高到低)
    检索返回数据的顺序没有特殊意义，为了明确地排序用 SELECT 语句检索出的数据，可使用 ORDER BY 子句。
    ORDER BY 子句取一个或多个列的名字。
    对空值，按无穷大处理(升序中，空值排最后；降序中排最前)。
    1、用法：Select prod_id,prod_price,prod_name From Products  Order By  prod_price,prod_name; 
      (从左到右执行排序，先排price)
       ORDER BY子句中使用的列将是为显示所选择的列，但是实际上并不一定要这样，用非检索的列排序数据是完全合法的。
       为了按多个列排序，列名之间用逗号分开。
    2、支持按相对列位置进行排序。
       输入 SELECT prod_id,prod_price,prod_name
       FROM  Products
       ORDER BY 2，3    --(2指price，3指name)
    3、升序、降序。默认是升序(asc，从小到大排序)，想降序时用desc。
       如：SELECT prod_id,prod_price,prod_name FROM  Products ORDER BY prod_price DESC;
      注意：DESC 关键字只应用到直接位于其前面的列名。如果想在多个列上进行排序，必须对每个列指定DESC关键字。
         升序是默认的，可不写，但降序必须写。


六、WHERE子句，选择、过滤
    其后只能跟逻辑语句，返回值只有ture或false
    如： select last_name,salary from s_emp where salary＝1000;--找出工资1000的人

WHERE子句操作符：
    1、逻辑比较运算符
        ＝        等于
        ！=       不等于，还有(<>  ^=   这两个同样表示不等于)
        >         大于
        >=        大于等于
        <         小于
        <=        小于等于

    2、SQL 比较运算符
    between…and…    ：在两者之间。(BETWEEN 小值 AND 大值)
        如：select last_name,salary from s_emp where salary between 1000 and 1500；
          --工资1000到1500的人，包括1000和1500。
    in(列表)：在列表里面的。
        如：select last_name,dept_id from s_emp where dept_id in(41,42);第41、42部门的人
    like    ： 包含某内容的。模糊查询
        可以利用通配符创建比较特定数据的搜索模式，通配符只能用于文本，非文本数据类型不能使用通配符。
        通配符在搜索模式中任意位置使用，并且可以使用多个通配符。
        通配符%表示任何字符出现任意次数；还能代表搜索模式中给定位置的0个或多个字符。下划线匹配单个任意字符。
        如：select table_name from user_tables where table_name like 'S\_%' escape'\';
        '  找出“S_“开头的,由于下划线有任意字符的含义，故需另外定义转移符。
           但习惯用“\”，为方便其他程序员阅读和检测，一般不改用其他的。
        like 'M%'：M开头的        like '_a%'：第二个字符是a的    like '%a%'所有含a的
            (“_”表示一个任意字符；“%”表示任意多个任意字符。)
        单引号里面的内容，大小写敏感。单引号用来限定字符串，
        如果将值与串类型的列进行比较，则需要限定引号；用来与数值列进行比较时，不用引号。
    is null：是空。(NULL表示不包含值。与空格、0是不同的。)
        如：SELECT prod_name,prod_price FROM Products WHERE prod_price IS NULL;


七、高级检索(逻辑运算符)：
    通常我们需要根据多个条件检索数据。可以使用AND或OR、NOT等连接相关的条件
    计算次序可以通过圆括号()来明确地分组。不要过分依赖默认计算次序，使用圆括号()没有坏处，它能消除二义性。
    
    and：条件与
       如 SELECT prod_id,prod_price,prod_name FROM Products WHERE prod_price<4 AND vend_id=‘DELL’
    or：条件或    (注： and 的优先级比 or 更高，改变优先级可用括号)
       如 SELECT prod_id,prod_price,prod_name FROM Products WHERE prod_price<4 OR vend_id=‘DELL’
    not：条件非。否定它之后所跟的任何条件
        否定的SQL 比较运算符： NOT BETWEEN； NOT IN； NOT LIKE； IS NOT NULL：
         (注意，按英语习惯用 is not，而不是 not is)
        NOT 与 IN 在一起使用时，NOT 是找出与条件列表不匹配的行。
        IN 列表里有 NULL 时不处理，不影响结果；用 NOT IN 时，有 NULL 则出错，必须排除空值再运算。
    in ：选择列表的条件
        使用IN操作符的优点： 在长的选项清单时,语法直观； 计算的次序容易管理；
        比 OR 操作符清单执行更快；最大优点是可以包含其他 SELECT 语句，使用能够动态地建立 WHERE 子句。
     如 SELECT prod_id,prod_price,prod_name FROM Products WHERE vend_id IN(‘DELL’,’RBER’,’TTSR’);


八、单行函数：
    函数一般在数据上执行，它给数据的转换和处理提供了方便。不同的DBMS提供的函数不同。
    函数可能会带来系统的不可移植性(可移植性:所编写的代码可以在多个系统上运行)。
    加入注释是一个使用函数的好习惯。
    大多数SQL实现支持以下类型的函数： 文本处理， 算术运算， 日期和时间， 数值处理。

Null：空值
    空值当成无穷大处理，所有空值参与的运算皆为空。
    空值与空值并不相等，因为空值不能直接运算。
    如：prod_price=""     这种写法是错的(不要受到corejava的影响)
    prod_price=NULL      这种写法是错的(不要受到corejava的影响)
    prod_price IS NULL   这种写法才是对的
NVL：处理空值，把空值转化为指定值。可转化为日期、字符、数值等三种(注意：转化时，两参数必须要同类型)
    如：NVL(date, '01-JAN-95')    NVL(title,'NO Title Yet')        NVL(salary,0)
    错误写法：
     Select last_name,title,salary*commission_pct/100 COMM From s_emp;--没提成的人没法显示工资
    正确写法：
     Select last_name,title,salary*NVL(commission_pct,0)/100 COMM From s_emp;--把提成是空值的转化为0

DISTINCT：过滤重复
    把重复的行过滤掉；多个字段组合时，只排除组合重复的。
    DISTINCT必须使用列名，不能使用计算或者表达式。
    所有的聚合函数都可以使用。如果指定列名，则DISTINCT只能用于COUNT(列名)，DISTINCT不能用于COUNT(*)。
    如：Select  Distinct  name  From  s_dept; Select Distinct dept_id,title  From s_emp;

文本处理：
TRIM()/LTRIM()/RTIRM()：去空格。只能去掉头和尾的空格，中间的不理。
     trim('   heo Are  fdou   ')  -->  heo Are  fdou
     输入：select trim('   heo Are  fdou   ')  from dual; -->：heo Are  fdou
LOWER：转小写
    lower('SQL Course') --> sql course
UPPER：转大写
    upper('    SQL Course') --－>SQL COURSE
INITCAP：首字母转大写，其余转小写
    initcap(SQL Course')  '--> Sql Course  

CONCAT：合成。双竖线只能在select语句里面用，这个可用于任何语句。
    Concat('Good','String') --> GoodString
SUBSTR：截取。
    Substr('String', 1 ,3)  --> Str
        第一个数字“1”，表示从第几个开始截取；若要从倒数第几个开始，用负数，如“－2”表示倒数第2个。
        上式中第2个数字“3”表示截取多少个。
LENGTH：统计长度。
    Length('String') --> 6
NVL：转换空值

日期和时间处理：
    Oracle日期格式：DD-MMM-YYYY        (D代表日期date，M代表月month，Y代表年year)
    如：SELECT prod_name              (DAY表示完整的星期几，DY显示星期的前三个字母)
        FROM Products
        WHERE prod_time BETWEEN
            to_date(’01-JAN-2008’)
        AND to_date(’31-DEC-2008’);
    日期可以进行加减，默认单位是1天。日期与日期可以相减，得出天数；日期与日期但不能相加。
sysdate   －>  系统的当天
Months_Between('01-Sep-95','11-Jan-94')  --> 19.774194    相差多少个月,Between里面也可以填函数。
Add_months('11-Jan-94',6)                -->  11-Jul-94   增加多少个月
Next_day('01-Sep-95','Friday')   --> '08-Sep-95'    下一个星期五。其中的'Friday'可用6替代，因为星期日＝1
Last_day('01-Sep-95')            -->  '30-Sep-95'   这个月的最后一天


数值处理：可以运用于代数，三角，几何
ROUND：四舍五入
    Round(45.925,2)  －> 45.93        Round(45.925,0)  －> 46        Round(45.925,-1)  －> 50
    逗号前一个数是要处理的数据源，后一个参数表示保留多少位小数。
    后一参数是负数时，表示舍去小数点前的几位，例3是舍去个位及其后的。不写后一参数时，默认不保留小数。
TRUNC：舍去末位。直接舍去，不会进位。
    Trung(45.925,2)  －> 45.92       Trung(45.925,2)  －> 45.92       Trung(45.925,2)  －> 45.92
日期的舍取：


常用的数值处理函数有：
    ABS()    绝对值        ABS(-5741.5854) --> 5741.5854
    PI()     圆周率        注意：oracle中不支持 PI()函数；MYSql 支持PI()函数。
    SIN()    正统值             Oracle还支持COS()、ASIN()、ACOS()函数
    SQRT()   平方根 


  转化：
TO_CHAR(number,'fmt')：把数值转换成字符串
    显示数字的命令
    9：正常显示数字；
    0：显示包括0的数值形式，空位强制补0；
    $：以美元符号显示货币；
    L：按当前环境显示相关的货币符号；
    . 和，：在固定位置出现“.”点 和“，”逗号；不够位时，四舍五入。
   例题：SQL> select 'Order'||To_char(id)||
  2  'was filled for a total of'
  3  ||To_char(total,'fm$9,999,999')
  4  from s_ord
  5  where ship_date ='21-SEP-92';

TO_NUMBER(char)：把字符转换成数字


九、链接
内链接：严格匹配两表的记录。