Oracle學習筆記四：表的創建與管理

一、表的創建

1.1、創建表

CREATE TABLE STUDENT 
(
    ID INT NOT NULL,
    NAME VARCHAR2(20),
    BIRTHDAY DATE,
    STATUS VARCHAR2(10),
    CONSTRAINT STUDENT_PK PRIMARY KEY 
    (
        ID 
    )
    ENABLE 
);

1.2、插入資料

INSERT INTO STUDENT (ID,NAME,BIRTHDAY,STATUS) VALUES (1,'HELLO',TO_DATE('2000-01-01','yyyy-mm-dd'),'GOOD');
INSERT INTO STUDENT (ID,NAME,BIRTHDAY,STATUS) VALUES (2,'WORLD',TO_DATE('2000-02-01','yyyy-mm-dd'),'GOOD');
COMMIT;

1.3、查看所有表

SELECT * FROM TAB;
SELECT * FROM USER_TABLES;

二、表的更改

2.1、更改表名

RENAME STUDENT TO STUDENT1;

2.2、更改表結構

--增加列
ALTER TABLE STUDENT ADD (EMAIL VARCHAR2(50));

--增加列默認值
ALTER TABLE STUDENT  MODIFY (BIRTHDAY DEFAULT SYSDATE);

--更改列名
ALTER TABLE STUDENT RENAME COLUMN STATUS TO INITLEVEL;

--更改列型別
ALTER TABLE STUDENT  MODIFY (NAME VARCHAR2(10 BYTE));

--洗掉列
ALTER TABLE STUDENT DROP COLUMN STATUS;

三、表的復制

CREATE TABLE STUDENT01 AS SELECT * FROM STUDENT;

四、表的截斷

TRUNCATE TABLE STUDENT01;

五、表的洗掉

--洗掉表
TRUNCATE TABLE STUDENT01;

--洗掉表（包含約束）
DROP TABLE STUDENT01 CASCADE CONSTRAINTS;

--洗掉表（釋放資源，不經過回收站，）
DROP TABLE STUDENT01 PURGE;

六、表的增刪改查

6.1、insert

--直接插入
INSERT INTO STUDENT01 (ID,NAME,BIRTHDAY,STATUS) VALUES (3,'HI',TO_DATE('2000-03-01','yyyy-mm-dd'),'GOOD');
INSERT INTO STUDENT01 (ID,NAME,BIRTHDAY,STATUS) VALUES (4,'MAN',TO_DATE('2000-04-01','yyyy-mm-dd'),'GOOD');
COMMIT;

--從其它表插入
INSERT INTO STUDENT (ID,NAME,BIRTHDAY,STATUS)
SELECT ID,NAME,BIRTHDAY,STATUS FROM STUDENT01 WHERE NOT EXISTS (SELECT 1 FROM STUDENT WHERE ID=STUDENT01.ID);
COMMIT;

6.2、delete

--按條件洗掉
DELETE FROM STUDENT01 WHERE ID>=3;
COMMIT;

--洗掉所有記錄
DELETE FROM STUDENT01;
COMMIT;

--截斷表
TRUNCATE TABLE STUDENT01;
COMMIT;

6.3、update

--直接更新
UPDATE STUDENT01 SET STATUS='VERY GOOD' WHERE ID>=3;
COMMIT;

--從其它表更新
ALTER TABLE STUDENT01
ADD CONSTRAINT STUDENT01_PK PRIMARY KEY 
(
  ID 
)
ENABLE;

UPDATE (SELECT A.STATUS STATUS1,B.STATUS STATUS2 FROM STUDENT A INNER JOIN STUDENT01 B ON A.ID=B.ID WHERE B.ID>=3)
SET STATUS1=STATUS2;
COMMIT;

6.4、select

--單表查詢

--01.查詢所有列
SELECT * FROM STUDENT01;

--02.查詢指定列
SELECT ID,NAME FROM STUDENT01;

--03.算術運算子 +,-,*,/
SELECT ID+1 NEWID FROM STUDENT01;

--04.比較符 >,<,=,>=,<=,<>
SELECT * FROM STUDENT01 WHERE ID>=3;

--05.列連接 ||
SELECT NAME,STATUS,NAME||' '||STATUS CONCATCOLOUMN FROM STUDENT01 WHERE ID<>3;

--06.字串 ''
SELECT NAME||' IS A STUDENT' AS NAME FROM STUDENT01;

--07.去除重復行 DISTINCT
SELECT DISTINCT NAME FROM STUDENT01;

--08.別名 AS
SELECT NAME AS NEWNAME FROM STUDENT01;

--09.構建運算式
SELECT 'DROP TABLE '||TABLE_NAME||';' EXPRESSION FROM ALL_TABLES WHERE OWNER='SYSTEM' AND TABLE_NAME LIKE 'STU%';

--10.模糊查詢及通配符 LIKE,%,_
SELECT * FROM STUDENT01 WHERE NAME LIKE 'HE%';
SELECT * FROM STUDENT01 WHERE NAME LIKE 'H_LLO';

--11.復合條件 AND,OR,IN
SELECT * FROM STUDENT01 WHERE NAME LIKE 'H%' AND STATUS='GOOD';
SELECT * FROM STUDENT01 WHERE NAME LIKE 'H%' OR STATUS='GOOD';
SELECT * FROM STUDENT01 WHERE ID IN (1,3);

--12.變數 &
SELECT * FROM STUDENT01 WHERE ID=&ID;

--13.排序 ASC,DESC
SELECT * FROM STUDENT01 ORDER BY ID ASC;
SELECT * FROM STUDENT01 ORDER BY ID DESC;

--多表查詢 INNER JOIN,LEFT JOIN,RIGHT JOIN
SELECT A.ID,A.NAME,A.BIRTHDAY,B.STATUS FROM STUDENT A INNER JOIN STUDENT01 B ON A.ID=B.ID;
SELECT A.ID,A.NAME,A.BIRTHDAY,B.STATUS FROM STUDENT A LEFT JOIN STUDENT01 B ON A.ID=B.ID;
SELECT A.ID,A.NAME,A.BIRTHDAY,B.STATUS FROM STUDENT A RIGHT JOIN STUDENT01 B ON A.ID=B.ID;

標籤：Oracle

上一篇：Oracle學習筆記四：表的創建與管理

下一篇：Oracle學習筆記五：資料庫實體管理