Module 02 · Introduction to Relational Databases and Tables
SQL Cheat Sheet: CREATE TABLE, ALTER, DROP, TRUNCATESQL 速查表：CREATE TABLE，ALTER，DROP，TRUNCATE
| Command 命令 | Syntax 语法 | Description 描述 | Example 示例 |
|---|---|---|---|
| CREATE TABLE | MySQL/DB2: CREATE TABLE table_name (col1 datatype ``optional keyword``, col2 datatype ``optional keyword``,col3 datatype ``optional keyword``,..., coln datatype ``optional keyword``) |
CREATE TABLE statement is to create the table. Each column in the table is specified with its name, data type and an optional keyword which could be PRIMARY KEY, NOT NULL, etc.,CREATE TABLE 语句用于创建表。表中的每一列都通过其名称、数据类型以及可选的关键字来指定，这些关键字可以是 PRIMARY KEY、NOT NULL 等， |
MySQL/DB2: CREATE TABLE employee ( employee_id char(2) PRIMARY KEY, first_name varchar(30) NOT NULL, mobile int); |
| ALTER TABLE - ADD COLUMN ALTER TABLE - 添加列 |
MySQL/DB2:Option 1. ALTER TABLE table_name ADD column_name_1 datatype....ADD COLUMN column_name_n datatype; Option 2. ALTER TABLE table_name ADD COLUMN column_name_1 datatype....ADD COLUMN column_name_n datatype; |
ALTER TABLE statement is used to add the columns to a table.ALTER TABLE 语句用于向表中添加列。 |
MySQL/DB2Option 1. ALTER TABLE employee ADD income bigint; Option 2. ALTER TABLE employee ADD COLUMN income bigint; |
| ALTER TABLE - ALTER COLUMN | MySQL: ALTER TABLE table_name MODIFY column_name_1 new_data_type;DB2: ALTER TABLE table_name ALTER COLUMN column_name_1 SET DATA TYPE datatype; |
MySQL: ALTER TABLE MODIFY MODIFY clause is used with the ALTER TABLE statement to modify the data type of columns.MySQL: ALTER TABLE MODIFY MODIFY 子句用于与 ALTER TABLE 语句一起使用，以修改列的数据类型。Db2: ALTER TABLE ALTER COLUMN statement is used to modify the data type of columns.Db2: ALTER TABLE ALTER COLUMN 语句用于修改列的数据类型。 |
MySQL: ALTER TABLE employee MODIFY mobile CHAR(20);DB2: ALTER TABLE employee ALTER COLUMN mobile SET DATA TYPE CHAR(20); |
| ALTER TABLE - DROP COLUMN | MySQL/DB2: ALTER TABLE table_name DROP COLUMN column_name_1 ; |
ALTER TABLE DROP COLUMN statement is used to remove columns from a table.ALTER TABLE DROP COLUMN 语句用于从表中删除列。 |
MySQL/DB2:ALTER TABLE employee DROP COLUMN mobile ; |
| ALTER TABLE - RENAME COLUMN | MySQL:ALTER TABLE table_name CHANGE COLUMN current_column_name new_column_name datatype [optional keywords]; MySQL： ALTER TABLE table_name CHANGE COLUMN current_column_name new_column_name datatype [optional keywords];DB2: ALTER TABLE table_name RENAME COLUMN current_column_name TO new_column_name; DB2： ALTER TABLE table_name RENAME COLUMN current_column_name TO new_column_name; |
MySQL: ALTER TABLE CHANGE COLUMN CHANGE COLUMN clause is used to rename the columns in a table.MySQL： ALTER TABLE CHANGE COLUMN CHANGE COLUMN 子句用于重命名表中的列。DB2: ALTER TABLE RENAME COLUMN statement is used to rename the columns in a table.DB2： ALTER TABLE RENAME COLUMN 语句用于重命名表中的列。 |
MySQL: ALTER TABLE employee CHANGE COLUMN first_name name VARCHAR(255);DB2: ALTER TABLE employee RENAME COLUMN first_name TO name; |
| TRUNCATE TABLE | MySQL: TRUNCATE TABLE table_name;DB2: TRUNCATE TABLE table_name IMMEDIATE; |
MySQL: TRUNCATE TABLE statement is used to delete all of the rows in a table.MySQL: TRUNCATE TABLE 语句用于删除表中的所有行。Db2: The IMMEDIATE specifies to process the statement immediately and that it cannot be undone.Db2: IMMEDIATE 指定立即处理语句，且无法撤销。 |
MySQL: TRUNCATE TABLE employee;DB2: TRUNCATE TABLE employee IMMEDIATE ; |
| DROP TABLE | MySQL/DB2DROP TABLE table_name ; |
Use the DROP TABLE statement to delete a table from a database. If you delete a table that contains data, by default the data will be deleted alongside the table.使用 DROP TABLE 语句从数据库中删除一个表。如果你删除一个包含数据的表，默认情况下，数据将连同表一起被删除。 |
MySQL/DB2:DROP TABLE employee ; |