02/07/2024, 15:46 about:blank
SQL Cheat Sheet: CREATE TABLE, ALTER, DROP, TRUNCATE
Command Syntax Description Example
MySQL/DB2: CREATE TABLE table_name (col1
MySQL/DB2: CREATE TABLE employee (
datatype optional keyword, col2 datatype CREATE TABLE statement is to create the table. Each column
employee_id char(2) PRIMARY KEY,
CREATE TABLE optional keyword,col3 datatype optional in the table is specified with its name, data type and an optional
first_name varchar(30) NOT NULL, mobile
keyword,..., coln datatype optional keyword which could be PRIMARY KEY, NOT NULL, etc.,
int);
keyword)
MySQL/DB2:
MySQL/DB2
Option 1. ALTER TABLE table_name ADD
column_name_1 datatype....ADD COLUMN Option 1. ALTER TABLE employee ADD income
ALTER TABLE - column_name_n datatype; ALTER TABLE statement is used to add the columns to a bigint;
ADD COLUMN table.
Option 2. ALTER TABLE table_name ADD Option 2. ALTER TABLE employee ADD COLUMN
COLUMN column_name_1 datatype....ADD income bigint;
COLUMN column_name_n datatype;
MySQL: ALTER TABLE MODIFY COLUMN MODIFY
MySQL: ALTER TABLE table_name MODIFY COLUMN MySQL: ALTER TABLE employee MODIFY COLUMN
COLUMN clause is used with the ALTER TABLE statement to
ALTER TABLE - column_name_1 new_data_type; mobile SET DATA TYPE CHAR(20);
modify the data type of columns.
ALTER COLUMN DB2: ALTER TABLE table_name ALTER COLUMN
DB2: ALTER TABLE employee ALTER COLUMN
Db2: ALTER TABLE ALTER COLUMN statement is used to
column_name_1 SET DATA TYPE datatype; mobile SET DATA TYPE CHAR(20);
modify the data type of columns.
ALTER TABLE - MySQL/DB2: ALTER TABLE table_name DROP ALTER TABLE DROP COLUMN statement is used to remove MySQL/DB2:
DROP COLUMN COLUMN column_name_1 ; columns from a table.
ALTER TABLE employee DROP COLUMN mobile ;
MySQL:ALTER TABLE table_name CHANGE COLUMN MySQL: ALTER TABLE CHANGE COLUMN CHANGE
MySQL: ALTER TABLE employee CHANGE COLUMN
current_column_name new_column_name
ALTER TABLE - COLUMN clause is used to rename the columns in a table. first_name name VARCHAR(255);
RENAME datatype [optional keywords];
COLUMN DB2: ALTER TABLE RENAME COLUMN statement is used DB2: ALTER TABLE employee RENAME COLUMN
DB2: ALTER TABLE table_name RENAME COLUMN
to rename the columns in a table. first_name TO name;
current_column_name TO new_column_name;
MySQL: TRUNCATE TABLE statement is used to delete all of
MySQL: TRUNCATE TABLE table_name; the rows in a table. MySQL: TRUNCATE TABLE employee;
TRUNCATE
TABLE
DB2: TRUNCATE TABLE table_name IMMEDIATE; Db2: The IMMEDIATE specifies to process the statement DB2: TRUNCATE TABLE employee IMMEDIATE ;
immediately and that it cannot be undone.
Use the DROP TABLE statement to delete a table from a MySQL/DB2:
DROP TABLE MySQL/DB2DROP TABLE table_name ; database. If you delete a table that contains data, by default the
data will be deleted alongside the table. DROP TABLE employee ;
Author(s)
Himanshu Birla
Niveditha Pandith TS
about:blank 1/2
02/07/2024, 15:46 about:blank
Changelog
Date Version Changed by Change Description
2023-05-04 1.1 Benny Li Formatting changes made
2021-07-27 1.0 Himanshu Birla Initial Version
about:blank 2/2
