oracle 基础知识

--create table
  CREATE TABLE tab_lesson(
         ID NUMBER(16) NOT NULL ,
         NAME VARCHAR2(4)
  );
--select , drop , truncate
  SELECT t.rowid,rownum,t.* FROM tab_lesson t;  -- select
  DELETE FROM tab_lesson;
  TRUNCATE TABLE tab_lesson;
-- drop
   DROP TABLE tab_lesson;
-- alter
  ALTER TABLE tab_lesson ADD CONSTRAINTS lesson1Key PRIMARY KEY(ID);
  ALTER TABLE tab_lesson MODIFY NAME VARCHAR2(8);
  ALTER TABLE tab_lesson MODIFY (NAME NULL);
  ALTER TABLE tab_lesson ADD age NUMBER(2);
  ALTER TABLE tab_lesson DROP COLUMN age;
--(1)修改字段名
  ALTER TABLE TEST_TIME RENAME COLUMN time_a TO time_c;
--(2)修改字段名字(新建表，利用别名，然后重命名表名)
  CREATE TABLE TEST_DATE AS SELECT T.ID,T.TIME_F TIME_A,T.TIME_T    TIME_B FROM TEST_TIME T;
  SELECT T.*,T.ROWID FROM TEST_DATE T;
  DROP TABLE TEST_TIME ;
  RENAME TEST_DATE TO TEST_TIME;
--(3)删除列，添加列 drop column ,add 
------------------------------------------------------------------
--insert
      INSERT INTO tab_lesson (ID,NAME) VALUES (1,'程仁银');
--ladies and gentlemen
         INSERT INTO tab_lesson VALUES (2,'ladies');
--sequence
  create sequence seq_lesson
    minvalue 1
    maxvalue 999999999999999999999999999
    start with 1
    increment by 1
    cache 20
    order;

  SELECT seq_lesson.nextval FROM dual;

  INSERT INTO tab_lesson (ID,NAME)VALUES(seq_lesson.nextval,'程雨');
-- 循环插入测试 procedure
  CREATE OR REPLACE PROCEDURE pro_lesson
  AS
  i NUMBER(16);
  BEGIN
      i := 1;
      WHILE i<10 LOOP
          INSERT INTO tab_lesson (ID,NAME)VALUES(i,'程雨');
          COMMIT;
          i := i+1;
      END LOOP;
  END;
-- update
   UPDATE tab_lesson SET NAME = 'up400' WHERE NAME LIKE '%up%';

-- group by ,having ....
  SELECT COUNT(*) SUM_LOCK,t.lock_remark from lock_info t
  WHERE t.lock_remark <> '电动车'
  HAVING  COUNT(*) >2
  GROUP BY t.lock_remark;

-- 子查询
   --1
   SELECT l.* FROM lock_ l
          WHERE l.id IN (SELECT lock_id FROM Lock_Info)
   ;
   --2
   SELECT * FROM
   (
          SELECT l.id,l.lock_name FROM lock_ l WHERE l.lock_factory LIKE '奇瑞%'
   )tempTable
   WHERE id = 4;

-- 连接查询
   --
   SELECT l.* FROM lock_ l;
   SELECT l.* FROM lock_info l;
   --
   --1
   SELECT l.*,o.lock_money FROM lock_ l,Lock_Info o
   WHERE l.id = o.lock_id;
   --2
   SELECT l.*,nvl(o.lock_money,'nothing') lock_money FROM lock_ l LEFT JOIN 
   Lock_Info o
   ON l.id = o.lock_id;
   --3
   SELECT l.*,nvl(o.lock_money,'nothing') lock_money FROM lock_ l RIGHT JOIN 
   Lock_Info o
   ON l.id = o.lock_id;

--常用sql函数 and 其他
   -- to_char
   SELECT to_char(SYSDATE,'YYYY-MM-DD')AS 日期 FROM dual;
   -- ROWNUM
   SELECT l.* FROM lock_ l WHERE ROWNUM < 4;
  -- oracle 分页类似
   SELECT * FROM
   (SELECT t.*,ROWNUM rn FROM lock_ t WHERE ROWNUM < 4)
   WHERE rn > 1;
   -- between
   SELECT l.* FROM lock_ l WHERE l.ID BETWEEN 1 AND 4;
   -- min,max 重复记录
   DELETE FROM locktest l WHERE l.ROWID >
	 (SELECT MIN(t.rowid) FROM locktest t WHERE
	 l.id=t.id AND l.lock_name=t.lock_name);

-- 导入、导出
   $exp system/suypower@rainsky file=d:\upload\photo.dmp tables=(tb_photo);
   $imp system/suypower@rainsky tables=tb_photo file=d:\upload\photo.dmp;

-- 表复制
    --1、只复制表结构的sql
　　create table b as select * from a where 1<>1
　　--2、即复制表结构又复制表中数据的sql
　　create table b as select * from a
　　--3、复制表的制定字段的sql
　　create table b as select row_id,name,age from a where 1<>1
　　--4、复制表的指定字段及这些指定字段的数据的sql
　　create table b as select row_id,name,age from a

　　--以上语句虽然能够很容易的根据a表结构复制创建b表，但是a表的索引等却复制不了
，     需要在b中手动建立
　　--5、insert into 会将查询结果保存到已经存在的表中
　　insert into t2(column1, column2, ....)
          select column1, column2, .... from t1;

SELECT T.*,T.ROWID FROM TEST_TIME T;

SELECT SYSDATE FROM dual;

INSERT INTO TEST_TIME (ID,Time_a,time_b) VALUES (ecc_seq.nextval,SYSDATE,SYSDATE);

INSERT INTO TEST_TIME (ID,Time_a) VALUES (ecc_seq.nextval,SYSDATE);

INSERT INTO TEST_TIME (ID,Time_a) VALUES (ecc_seq.nextval,to_date(to_char(SYSDATE,'yyyy-mm-dd'),'yyyy-mm-dd'));

INSERT INTO TEST_TIME (ID,Time_b) VALUES (ecc_seq.nextval,to_date(to_char(SYSDATE,'yyyy-mm-dd hh24:mi:ss'),'yyyy-mm-dd hh24:mi:ss'));

SELECT to_char(SYSDATE,'yyyy-mm-dd hh24:mi:ss') FROM dual;

select to_date (to_char (SYSTIMESTAMP, 'YYYY-MON-DD HH24.MI.SS'), 'YYYY-MON-DD HH24.MI.SS') AS MY_DATE from DUAL;