【转载】oracle（一）

是本笔记以orcal数据库为例,其它数据库略有不同.
一、写子句顺序
    Select [ALL | DISTINCT]  column_name [, column_name]...
    From  {table_name | view_name}
              [, {table_name | view_name}]...
    [Where  search_conditions]
    [Group By  column_name [, column_name]
      [Having  search_condition]]
    [Order By  {column_name | select_list_number} [ASC | DESC]
        [,  {column_name | select_list_number} [ASC | DESC]]... ];    --最后
二、常用SQL：
  1.建表前检查语句:
    MySQL的: drop table if exists 表名;
    SQL Server的: IF EXISTS (SELECT name FROM sysobjects  WHERE name = '表名' AND type = 'U') DROP TABLE 表名;
    Oracle的: create or replace table 表名 ...; -- 直接写建表语句
  2.建表语句: create table 表名(memid int , points numeric(10,1) default 0,
        primary key (`memid`, `courseid`),
        FOREIGN KEY (`memid`) REFERENCES 表名2 (`memid`) on delete cascade on update cascade ,
        CHECK ( points>=0 and points<=100 ) );
  3.复制表: CREATE TABLE 新表名 AS SELECT *  FROM 旧表名; -- 仅复制数据,没复制表结构(自增主键等不会复制)
            CREATE TABLE 新表名 like 旧表名; -- 使用旧表创建新表,复制表结构(数据不会复制)
  4.插入语句: INSERT INTO 表名(id,name,price,vend_name) VALUES(11,'TV',222,'US'),(22,'ss',12.22,'kk');
              INSERT INTO 表名(id,name,price,vend_name) SELECT id,name,price,vend FROM 表名2;
  5.更新语句: UPDATE 表名 SET column_name = expression, prod_name = 'NEWCOMPUTER' [WHERE];
              UPDATE 表1, 表2 SET 表2.column_name = expression, 表1.prod_name = 'NEWCOMPUTER' [WHERE];
  6.删除语句: DELETE FROM 表名 WHERE search_conditions;
  7.清空表格: TRUNCATE TABLE 表名;
  8.修改表结构
    修改字段: ALTER TABLE 表名 Modify col_name varchar(100);
    添加字段: ALTER TABLE 表名 Add col_name varchar(100) default NULL COMMENT '匯款帳號或者匯款人' after col_name0;
    减少字段: Alter Table 表名 Drop (column [,column]…);
    添加约束: Alter TABLE 表名 Add FOREIGN KEY(column1) REFERENCES 表名2(column2); -- 添加非空约束时,要用Modify语句
    删除约束: ALTER TABLE 表名 Drop FOREIGN KEY 表名_ibfk_1;
              Alter Table 表名 Drop CONSTRAINT column;
    添加主键: Alter table 表名 add primary key(col);
    删除主键: Alter table 表名 drop primary key(col);
    唯一约束: ALTER [IGNORE] TABLE 表名 ADD UNIQUE INDEX (column [,column]…); -- IGNORE:删除重复; 没这个则重复时报错
  9.创建索引: create [unique] index 索引名 on 表名(column [,column]…);
    删除索引: drop index 索引名;
  10.创建视图:create view 视图名 as select statement;
     删除视图:drop view 视图名;
三、注意事项：
    大小写不敏感，即不区分大小写。提倡关键字大写，便于阅读和调试。
    SQL语句是由简单的英语单词构成；这些英语单词称为关键字/保留字，不做它用。SQL由多个关键字构成。
    SQL语句由子句构成，有些子句是必须的，有些是可选的。
    在处理SQL语句时，其中所有的空格都被忽略(空格只用来分开单词，连续多个空格当一个用)。
    SQL语句可以在一行上写出，建议多行写出，便于阅读和调试。
    多条SQL语句必须以分号分隔。多数DBMS不需要在单条SQL语句后加分号，但特定的DBMS可能必须在单条SQL语句后加分号。
    SQL语句的最后一句以 “；”号结束。不同的数据库会有不同的结束符号。(go也会作结束符)
    {} 大括号包起来的单字或词组，表示至少从中选一个。
    [] 中括号包起来的部分，表示可选可不选的。
    () 小括号，表示一定要输入的。与大括号、中括号不同。
    ｜ 表示最多只能从选项中选取一个。
    ， 表示可按需选择多个选项，并且这些选项之间必须以逗号隔开。
    ... 表示可以重复地使用同样的语法部分。
    !  在SQL环境下执行Unix命令。
四、兼顾各数据库的 SQL 语句
    1.自增列:
       Oracle:     建立 Sequence
       MySQL:      create table test_t(id int primary key AUTO_INCREMENT, name varchar(80)); -- AUTO_INCREMENT 是自增关键字
       SQL Server: create table test_t(id int primary key identity(1,1), name varchar(80)); -- identity(1,1) 是自增函数
       access：    create table test_t(id Integer primary key Counter(1,1), name varchar(80));

       通用的: 使用表自身的自增列的最大值+1，如：
       insert into test_t(id, name) values((select nvl(max(id),0)+1 from test_t),'holer');  -- 这里 id 是表的自增列
     2.伪列(序号):
       SELECT (SELECT Count(表名.aa) AS AutoNum FROM xlh WHERE (表名.aa <= 表名_tem.aa)) AS 序号, 表名.aa
       FROM 表名 AS 表名_tem INNER JOIN 表名 ON 表名_tem.aa=表名.aa ORDER BY 表名.aa;
Rownum:纬列。内存里排序的前N个。
    在where语句中，可以用＝1，和<=N 或 <N；但不能用＝N 或 >N。
    因为这是内存读取，没有1就丢弃再新建1。只能从1开始。需要从中间开始时，需二重子rownum语句需取别名。
经典应用： Top-n Analysis  (求前N名或最后N名)
          Select [查询列表], Rownum
          From (Select  [查询列表(要对应)]
                   From 表
                   Order by  Top-N_字段)
          Where Rownum <= N   -- 不写这行则全部显示并排名。
SQL server的用法：
          Select  top N 查询列表
                  From 表
                  Order by  Top-N_字段
分页显示：
    --取工资第5～10名的员工(二重子rownum语句，取别名)
    select * From (
    select id,last_name,salary,Rownum rn
           From (Select id,last_name,salary
                     from s_emp
                     order by salary desc)
    where rownum <= 10)
    where rn between 5 and 10;