LDD - Tables

Create tables

CREATE TABLE [schema.]table
    (column datatype [DEFAULT expr][, ...]);

create table dept(
    deptno number(2),
    dname varchar2(14),
    loc varchar2(13)
);

describe dept

Default

create table test (
    nom             varchar2(10)    default 'toto',
    salaire         number(7,2)     default 1000,
    date_embauche   date            default sysdate
);

Create table à partir d'une sous-requête

CREATE TABLE table
    [(column, column...)]
    AS subquery;

CREATE TABLE dept30 AS
    SELECT empno, ename, sal*12 ANNSAL, hiredate
    FROM emp
    WHERE deptno = 30;

CREATE TABLE dept30(numero_employe, nom, salaire_annuel, date_embauche) AS
    SELECT empno, ename, sal*12, hiredate
        FROM emp
        WHERE deptno = 30;

Alter table

Ajouter une colonne

ALTER TABLE table ADD (
    column datatype [DEFAULT expr]
    [, column datatype]...
);

ALTER TABLE dept30 ADD (
    job VARCHAR2(9) DEFAULT '',
    sex CHAR(1)
);

Modifier une colonne

ALTER TABLE table MODIFY (
    column datatype [DEFAULT expr]
    [, column datatype]...
);

ALTER TABLE dept30 MODIFY (
    ename VARCHAR2(15),
    sex CHAR(2)
);

Supprimer une colonne

ALTER TABLE table DROP COLUMN column_name;

ALTER TABLE dept30 DROP COLUMN job;

Drop table

DROP TABLE table_name;

DROP TABLE dept30;

Rename

RENAME old_name TO new_name;

RENAME dept TO department;

Truncate

TRUNCATE TABLE table_name;

TRUNCATE TABLE dept;

DELETE from dept;