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

CREATE DATABASE `sakila` /*!40100 DEFAULT CHARACTER SET latin1 */;

Name

`sakila`

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

Name (Short)

`actor`

Name (Long)

`sakila`.`actor`

Select All Statement

SELECT `actor`.`actor_id`,
    `actor`.`first_name`,
    `actor`.`last_name`,
    `actor`.`last_update`
FROM `sakila`.`actor`;

Select with References

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

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

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

DELETE FROM `sakila`.`actor`
WHERE <{where_expression}>;

Delete with References

-- 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

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)

`first_name`

Name (long)

`actor`.`first_name`

Select Columns Statement

SELECT `first_name` FROM `sakila`.`actor`;

Insert Statement

INSERT INTO `sakila`.`actor`
(`first_name`)
VALUES
(<{first_name}>);

Update Statement

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)

# actor_id, first_name, last_name, last_update
'1', 'PENELOPE', 'GUINESS', '2006-02-15 04:34:33'

Copy Rows (with names, unquoted)

# actor_id, first_name, last_name, last_update
1, PENELOPE, GUINESS, 2006-02-15 04:34:33

Copy Row (tab separated)

1	PENELOPE	GUINESS	2006-02-15 04:34:33

Copy Field

'GUINESS'