[日本語]

This is a memo of SQL commands which I learned from workplace and self-study.

If you do not know SQL, please refer to “Database, DBMS and SQL“. Here, SQL commands are classified as follows.

・DDL

・DML

・DCL

・Others

• DDL (Data Definition Language)

These are the commands that define the structure of database objects, such as creation, deletion, and modification.

Prof. Wiki：”A syntax similar to a computer programming language for defining data structures, especially database schemas. DDL statements create, modify, and remove database objects such as tables, indexes, and users.”

• List and Description of DDL Commands

※Checked operation with MySQL and PostgreSQL.

– Create table

CREATE TABLE table_name(
column_name_1 INT NOT NULL PRIMARY KEY,
column_name_2 TEXT NULL,
column_name_3 CHAR(2) NULL
);

– Add table columns

ALTER TABLE table_name
ADD column_name_4 TEXT NULL;

– Change table columns (Checked operation with MySQL)

ALTER TABLE table_name CHANGE column_name_4 column_name_5 INT(2) NOT NULL;

For MySQL data type or size, please refer here.

– Change table columns (checked with PostgreSQL)

ALTER TABLE table_name RENAME column_name_4 TO column_name_5;
ALTER TABLE table_name ALTER COLUMN column_name_5 TYPE CHAR(2); --If compatible with type
ALTER TABLE table_name ALTER COLUMN column_name_5 TYPE SMALLINT USING column_name_5::SMALLINT; --If incompatible with type
ALTER TABLE table_name ALTER column_name_5 SET NOT NULL;

For PostgreSQL data type or size, please refer here.

The following INT type has a fixed size, specification is unnecessary.
・TINYINT：1byte
・SMALLINT：2byte
・INT：4byte
・BIGINT：8byte

– Delete a table column

ALTER TABLE table_name
DROP COLUMN column_name_5;

– Delete all records in the table

TRUNCATE table_name;

– Delete table

DROP TABLE table_name;

20190701