Oracle学习笔记四:表的创建与管理

时间:2020-10-13 09:52:00 来源:互联网 作者: 神秘的大神 字体:

一、表的创建

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

--查询单表
SELECT * FROM STUDENT01 WHERE ID>=3;

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