• Schema
  • Create Schema
  • Drop Schema
• Table
  • Create Table
  • Rename Table
  • Truncate Table
  • Drop Table
  • Column
    • Add Column
    • Change Data Type
    • Drop Column
• View
  • Create View
  • Drop View
• Index
  • Create Index
  • Drop Index
• Trigger
  • Create Trigger
  • Drop Trigger
• Constraint
  • Create Primary Key
  • Create Foreign Key
  • Create Unique Constraint
  • Drop Primary Key
  • Drop Foreign Key
  • Drop Unique Constraint
• Procedure
  • Create Procedure
  • Drop Procedure
• Function
  • Create Function
  • Drop Function

Schema

Create Schema

MySQL allows to create a new schema. After entering the schema name, DtSQL can generate and execute the SQL to create the new schema.

Sample : CREATE DATABASE TEST_SCHEMA

Drop Schema

MySQL allows to drop an existing schema. After entering the schema name, DtSQL can generate and execute the SQL to drop the schema.

Sample : DROP DATABASE TEST_SCHEMA

Table

Create Table

MySQL allows to create a new table. After entering the table name and column information, DtSQL can generate and execute the SQL to create the table.

• Column Name
• Column Type
• Length/Precesion
• Scale (decimal)
• Default Value
• Nullable
• Primary Key
• Unique

Sample :
CREATE TABLE TEST_SCHEMA.TEST_TABLE (
a bigint NOT NULL DEFAULT 1,
b char ( 12 ) NOT NULL DEFAULT 'abc',
c decimal ( 12, 2 ) ,

PRIMARY KEY ( a, b ),
UNIQUE ( b,c )
)

Rename Table

MySQL allows to rename an existing table. After entering the table name, DtSQL can generate and execute the SQL to rename the table.

Sample : ALTER TABLE TEST_SCHEMA.TEST_TABLE RENAME TO TESTTABLE

Truncate Table

MySQL does not allows to truncate an table and deleting table is used instead. After entering the table name, DtSQL can delete all data in the table.

Sample : DELETE FROM TEST_SCHEMA.TEST_TABLE

Drop Table

MySQL allows to drop an existing table. After entering the table name, DtSQL can generate and execute the SQL to drop the table.

Sample : DROP TABLE TEST_SCHEMA.TEST_TABLE

Column

Add Column

MySQL allows to add a new column to an existing table. After selecting the table and entering column information, DtSQL can generate and execute the SQL to add the new column to the table.

• Column Name
• Column Type
• Length/Precesion
• Scale (decimal)
• Default Value
• Nullable

Sample :
ALTER TABLE
TEST_SCHEMA.TEST_TABLE
ADD
d varchar(20) NOT NULL DEFAULT 'def'

Change Data Type

MySQL allows to change the column data. After selecting the new data type, DtSQL can generate and execute the SQL to change the column data type.

Sample :
ALTER TABLE TEST_SCHEMA.TEST_TABLE
MODIFY COLUMN F BIGINT

Drop Column

MySQL allows to delete an existing column. After selecting the column, DtSQL can generate and execute the SQL to delete the column.

Sample :
ALTER TABLE TEST_SCHEMA.TEST_TABLE
DROP COLUMN F

View

Create View

MySQL allows to create a new view. After entering the view name and select SQL, DtSQL can generate and execute the SQL to create the view.

Sample :
CREATE VIEW TEST_SCHEMA.TEST_VIEW
AS
SELECT * FROM TEST_SCHEMA.TEST_TABLE

Drop View

MySQL allows to drop an existing view. After selecting the view name, DtSQL can generate and execute the SQL to delete the view.

Sample : DROP VIEW TEST_SCHEMA.TEST_VIEW

Index

Create Index

MySQL allows to create a new index. After selecting the table columns and entering the view name, DtSQL can generate and execute the SQL to create the index.

Sample :
CREATE UNIQUE INDEX TEST_SCHEMA.TEST_INDEX
ON TEST_SCHEMA.TEST_TABLE ( A, B, C )

Drop Index

MySQL allows to drop an existing index. After selecting the index name, DtSQL can generate and execute the SQL to delete the index.

Sample : DROP INDEX TEST_SCHEMA.TEST_INDEX

Trigger

Create Trigger

MySQL allows to create a new trigger. After entering the trigger name and trigger actions, DtSQL can generate and execute the SQL to create the trigger.

Sample :
CREATE TRIGGER TEST_SCHEMA.TEST_TRIGGER
AFTER INSERT
ON TEST_SCHEMA.TEST_TABLE
FOR EACH ROW
BEGIN
UPDATE TEST_SCHEMA.TEST_TB SET COL1 = COL1 + 1;
END

Drop Trigger

MySQL allows to drop an existing trigger. After selecting the trigger name, DtSQL can generate and execute the SQL to delete the trigger.

Sample : DROP TRIGGER TEST_SCHEMA.TEST_TRIGGER

Constraint

Create Primary Key

MySQL allows to create a primary key for a table. After selecting the table columns, DtSQL can generate and execute the SQL to create the primary key.

Sample :
ALTER TABLE
TEST_SCHEMA.TEST_TABLE
ADD
PRIMARY KEY ( B, C )

Create Foreign Key

MySQL allows to create a foreign key for a table to reference to another table. After selecting the table columns and referenced table columns, DtSQL can generate and execute the SQL to create the foreign key.

Sample :
ALTER TABLE
TEST_SCHEMA.TEST_TABLE
ADD
FOREIGN KEY ( A, C )
REFERENCES TEST_SCHEMA.TEST_TB (B, A )

Create Unique Constraint

MySQL allows to create an unique constraint for a table columns. After entering the unique constraint name and selecting table columns, DtSQL can generate and execute the SQL to create the unique constraint.

Sample :
ALTER TABLE
TEST_SCHEMA.TEST_TABLE
ADD CONSTRAINT TEST_UNIQUE
UNIQUE ( B, C )

Drop Primary Key

MySQL allows to drop an existing primary key. After selecting the primary key, DtSQL can generate and execute the SQL to delete the primary key.

Sample : ALTER TABLE TEST_SCHEMA.TEST_TABLE DROP PRIMARY KEY

Drop Foreign Key

MySQL allows to drop an existing foreign key. After selecting the foreign key, DtSQL can generate and execute the SQL to delete the foreign key.

Sample :
ALTER TABLE TEST_SCHEMA.TEST_TABLE
DROP FOREIGN KEY TEST_TABLE_IBFK_1

Drop Unique Constraint

MySQL allows to drop an existing unique constraint. After selecting the unique constraint, DtSQL can generate and execute the SQL to delete the unique constraint.

Sample :
ALTER TABLE
TEST_SCHEMA.TEST_TABLE
DROP INDEX TEST_UNIQUE

Procedure

Create Procedure

MySQL allows to create procedures. After entering the procedure name, parameter names and procedure body, DtSQL can generate and execute the SQL to create the procedure.

Sample :
CREATE PROCEDURE TEST_SCHEMA.TEST_PROC
( IN PARAM1 VARCHAR(20), INOUT PARAM2 INT, OUT PARAM3 INT)
BEGIN
SELECT COUNT(*) INTO PARAM3 FROM information_schema.TABLES;
END

Drop Procedure

MySQL allows to drop an existing procedure. After selecting the procedure, DtSQL can generate and execute the SQL to delete the procedure.

Sample : DROP PROCEDURE TEST_SCHEMA.TEST_PROC

Function

Create Function

MySQL allows to create functions. After entering the procedure name, parameter names and function body, DtSQL can generate and execute the SQL to create the function.

Sample :
CREATE FUNCTION TEST_SCHEMA.TEST_FUNC
( PARAM1 VARCHAR(20), PARAM2 BIGINT, PARAM3 DATE )
RETURNS VARCHAR(30)
RETURN CONCAT('Hello, ',s,'!')

Drop Function

MySQL allows to drop an existing function. After selecting the function, DtSQL can generate and execute the SQL to delete the function.

Sample : DROP FUNCTION TEST_SCHEMA.TEST_FUNC