8.1.11.1 Generating SQL Statements

MySQL Workbench can be used to generate SQL, most typically as either INSERT statements or SELECT statements.

The following common methods are for generating SQL statements in MySQL Workbench.

Note

All of the MySQL Workbench Export options include the option to export as SQL.

Context-menu options after right-clicking on a schema in the schema view, using the sakila column as an example.

Create Statement

Press CTRL+C to copy
 
CREATE DATABASE `sakila` /*!40100 DEFAULT CHARACTER SET latin1 */;

Name

Press CTRL+C to copy
 
`sakila`

Context-menu options after right-clicking on a table in the schema view, using the sakila.actor column as an example:

Name (Short)

Press CTRL+C to copy
 
`actor`

Name (Long)

Press CTRL+C to copy
 
`sakila`.`actor`

Select All Statement

Press CTRL+C to copy
 
SELECT `actor`.`actor_id`,
    `actor`.`first_name`,
    `actor`.`last_name`,
    `actor`.`last_update`
FROM `sakila`.`actor`;

Select with References

Press CTRL+C to copy
 
SET @actor_id_to_select = <{row_id}>;
SELECT film_actor.*
    FROM film_actor, actor
    WHERE `actor`.`actor_id` = `film_actor`.`actor_id`
          AND actor.actor_id = @actor_id_to_select;
SELECT actor.*
    FROM actor
    WHERE actor.actor_id = @actor_id_to_select;

Insert Statement

Press CTRL+C to copy
 
INSERT INTO `sakila`.`actor`
  (`actor_id`,
  `first_name`,
  `last_name`,
  `last_update`)
VALUES
  (<{actor_id: }>,
  <{first_name: }>,
  <{last_name: }>,
  <{last_update: CURRENT_TIMESTAMP}>);

Update Statement

Press CTRL+C to copy
 
UPDATE `sakila`.`actor`
SET
`actor_id` = <{actor_id: }>,
`first_name` = <{first_name: }>,
`last_name` = <{last_name: }>,
`last_update` = <{last_update: CURRENT_TIMESTAMP}>
WHERE `actor_id` = <{expr}>;

Delete Statement

Press CTRL+C to copy
 
DELETE FROM `sakila`.`actor`
WHERE <{where_expression}>;

Delete with References

Press CTRL+C to copy
 
-- All objects that reference that row (directly or indirectly)
-- will be deleted when this snippet is executed.
-- To preview the rows to be deleted, use Select Row Dependencies
START TRANSACTION;
-- Provide the values of the primary key of the row to delete.
SET @actor_id_to_delete = <{row_id}>;

DELETE FROM film_actor
    USING film_actor, actor
    WHERE `actor`.`actor_id` = `film_actor`.`actor_id`
          AND actor.actor_id = @actor_id_to_delete;
DELETE FROM actor
    USING actor
    WHERE actor.actor_id = @actor_id_to_delete;
COMMIT;

Create Statement

Press CTRL+C to copy
 
CREATE TABLE `actor` (
  `actor_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `first_name` varchar(45) NOT NULL,
  `last_name` varchar(45) NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`actor_id`),
  KEY `idx_actor_last_name` (`last_name`)
) ENGINE=InnoDB AUTO_INCREMENT=201 DEFAULT CHARSET=utf8;

Context-menu options after right-clicking on a column in the schema view, using the sakila.actor.first_name column as an example:

Name (short)

Press CTRL+C to copy
 
`first_name`

Name (long)

Press CTRL+C to copy
 
`actor`.`first_name`

Select Columns Statement

Press CTRL+C to copy
 
SELECT `first_name` FROM `sakila`.`actor`;

Insert Statement

Press CTRL+C to copy
 
INSERT INTO `sakila`.`actor`
(`first_name`)
VALUES
(<{first_name}>);

Update Statement

Press CTRL+C to copy
 
UPDATE `sakila`.`actor`
SET
`first_name` = <{first_name}>
WHERE <{where_expression}>;

Context-menu options after right-clicking on a field in the results view, using record #1 in the sakila.actor table as an example:

Copy Rows (with names)

Press CTRL+C to copy
 
# actor_id, first_name, last_name, last_update
'1', 'PENELOPE', 'GUINESS', '2006-02-15 04:34:33'

Copy Rows (with names, unquoted)

Press CTRL+C to copy
 
# actor_id, first_name, last_name, last_update
1, PENELOPE, GUINESS, 2006-02-15 04:34:33

Copy Row (tab separated)

Press CTRL+C to copy
 
1	PENELOPE	GUINESS	2006-02-15 04:34:33

Copy Field

Press CTRL+C to copy
 
'GUINESS'