SQL Basic Data difinition Language

Build a Table statement

CREATE TABLE table_name
(
column_name DATATYPE [null| Not NULL],
column_name DATATYPE [null| Not NULL],
...
[CONSTRAINT]
);

Example:

CREATE Tabletest
(
ID VARCHAR () not NULL,
T_date DATE not NULL,
T_number number not NULL note at the end do not add commas
);

View Table Structure statements

DESC table_name;

Modify Table Statements

ALTER TABLE table_name
ADD column_name DATATYPE [null| Not NULL] | MODIFY column_name DATATYPE [null| Notnull] | DROP Cloumn_name [CASCADE CONSTRAINT]

For example

ALTER TABLE TEST
ADD T_add Vachar () not
MODIFY t_number Number (2,2)
DROP t_date;

Delete a table statement

DROP TABLE table_name;

To add a primary key constraint in a build table statement

PRIMARY KEY (column_name1, column_name1 ...)

For example:

CREATE Tabletest
(
ID VARCHAR () not NULL,
T_date DATE not NULL,
T_number number is not NULL,
PRIMARY KEY (ID)
);

To add a primary KEY constraint in a modify table statement

ALTER TABLE table_name
ADD CONSTRAINTS constraints_name PRIMARY KEY (column_name1, column_name1 ...);

Remove a PRIMARY KEY constraint

ALTER TABLE table_name
DROP CONSTRAINTS Constraints_name;

To add a foreign key constraint in a build table statement

CONSTRAINTS constraints_name FOREIGN Key (Column_nume)//This table which is a foreign key
Peference table_name (COLUMN_NUME)//foreign key in which table which field
On DELETE CASCADE;

To add a foreign key constraint in a modify table statement

ALTER TABLE table_name

CONSTRAINTS constraints_name FOREIGN KEY (column_nume)
Peference table_name (Column_nume)
On DELETE CASCADE;

Move excepted key constraint

ALTER TABLE table_name
DROP CONSTRAINTS Constraints_name;

Add a conditional constraint in a Build table statement

CONSTRAINTS Constraints_namecheck (column_condition)

To add a conditional constraint in a modified statement

ALTER TABLE table_name

ADD CONSTRAINTS Constraints_namecheck (column_condition);

Delete a conditional constraint

ALTER TABLE table_name

DROP CONSTRAINTS Constraints_name;

Dml

Data additions

INSERT into table_name (column_name1,column_name2 ...) VALUES (Data1,data2 ...);

Add data to a table from another data table

INSERT into table_name (column_name1,column_name2 ...) VALUES (select Column_name3 from table_name1, select Column_name4 fromtable_name2 ...);

Create tables and extract the required data directly from other data tables

CREATE TABLE table_name as SELECTCOLUMN_NAME1, column_name2,... Column_namen FORM source_table;

Data modification

UPDATE table_name SET column_name1=data1,... Column_name2=data2[wherecondition];

Data deletion

DELETE from table_name [WHERE comdition];

Data query

SELECT column_nume1,... Column_nume2 Fromtable_name [Wherw Conditon];

Querying all fields of a table

SELECT * from Table-name [WHERE CONDITION];

Other database manipulation statements

The TRUNCATE statement is used to delete all data from a data table, faster than delete

TRUNCATE TABLE table_name;

SQL Basic Data difinition Language