Chapter 8 Database Development
Prev		Next

Chapter 8 Database Development

Table of Contents

A set of visual tools to create, edit, and manage SQL queries, database connections, and objects.

8.1 Visual SQL Editor

8.1.1 SQL Query Window
8.1.2 SQL Query Window Toolbar
8.1.3 Query and Edit Menus
8.1.4 Results Window
8.1.5 SQL Snippets tab
8.1.6 Context Sensitive Help
8.1.7 Output History Panel
8.1.8 Table Data Search Panel
8.1.9 Export / Import a Table
8.1.10 Tutorial: Adding Data
8.1.11 The MySQL Table Editor
8.1.12 Code Generation Overview

The visual SQL Editor lets you build, edit and run queries, create and edit data, and view and export results. Color syntax highlighting, context sensitive help and auto-complete helps write and debug SQL statements. The integrated EXPLAIN plans provide data to help optimize the your queries.

Figure 8.1 SQL Editor GUI

The following sections describe how to use the visual SQL editor.

8.1.1 SQL Query Window

In this area, you can enter SQL statements directly. The statements entered can be saved to a file or snippet for later use. At any point, you can also execute the statements you have entered.

To save a snippet of code entered into the SQL Query panel, click the Save SQL to Snippets List icon in the Snippets panel, enter a name (optional), and click OK. The snippet can be inserted into the SQL Query panel at any time by double-clicking the snippet in the SQL Snippets panel.

Figure 8.2 SQL Editor - SQL Query Panel

Executing a SELECT query will display the associated result set in the SQL View panel, directly below the SQL Query panel. These cells are editable if MySQL Workbench is able to determine how, as for example they are editable if a Primary or Unique key exists within the result set. If not, MySQL Workbench will display a "read-only" icon at the bottom-right corner of the SQL View panel, and hovering the mouse cursor over this icon will provide a hint as to why it's not editable.

Note

To quickly enter the name of a table, view, or column, double-click the item in the Schemata Palette. The item name will be inserted into the SQL Query panel.

The SQL Editor has several configurable panels and windows, as described in the screenshot above.

8.1.2 SQL Query Window Toolbar

The toolbar features buttons in two locations, in the main toolbar and within the SQL Editor itself. The SQL Editor buttons are described below.

Figure 8.3 SQL Editor - Toolbar

From left to right, these buttons are:

Open an SQL Script File: Loads contents from a saved SQL script into the SQL editor.
Save SQL Script to File: Saves contents from the SQL editor into a file.
Execute SQL Script: Executes the selected portion of the query, or the entire query if nothing is selected.
Execute Current SQL script: Execute the statement under the keyboard cursor.
Explain (All or Selection): Execute the EXPLAIN command on the query under the keyboard cursor.
A "Results Grid" tab is also displayed when executing an EXPLAIN statement. Clicking it will execute the same query, as if Execute SQL Script was selected.
Alternatively, the Visual Explain plan is already available for all executed queries. Select Execution Plan from the results tab to view it.
Stop the query being executed: Halts execution of the currently executing SQL script.

Note

The database connection will not be restarted, and open transactions will remain open.
Toggle whether execution of SQL script should continue after failed statements: If the red “breakpoint” circle is displayed, the script terminates on a statement that fails. If the button is depressed so that the green arrow is displayed, execution continues past the failed code, possibly generating additional result sets. In either case, any error generated from attempting to execute the faulty statement is recorded in the Output tabsheet.
This behavior can also be set from the SQL Execution user preferences panel.
Commit: Commits the current transaction.

Note

All query tabs in the same connection share the same transactions. To have independent transactions, a new connection must be opened.
Rollback: Rolls back the current transaction.

Note

All query tabs in the same connection share the same transactions. To have independent transactions, a new connection must be opened.
Toggle Auto-Commit Mode: If selected, each statement will be committed independently.

Note

All query tabs in the same connection share the same transactions. To have independent transactions, a new connection must be opened.

Auto-commit is enabled by default, and this default behavior can be modified (disabled) under the SQL Execution user preferences panel.
Set Limit for Executed Queries: The default value is 1000, which appends "LIMIT 0, 1000" to SELECT queries.
The default (1000) can be changed from the SQL Execution user preferences panel.
Save Snippet: Save the current statement or selection to the active snippet list.
Beautify SQL: Beautify/reformat the SQL script.
By default, SQL keywords are changed to UPPER CASE. This functionality can be changed from the SQL Editor user preferences panel.
Find panel: Show the Find panel for the editor.
Invisible characters: Toggle display of invisible characters, such as newlines, tabs, spaces.
A new line is represented as [LF], a space as a single dot (.), and a tab as a right arrow.
Wrapping: Toggles the wrapping of long lines in the SQL editor window.

8.1.3 Query and Edit Menus

When an SQL Editor tab is selected, the most important items on the main menu bar are the Query and Edit menus.

SQL Query Menu

The Query menu features the following items:

Execute (All or Selection): Executes all statements in the SQL Query area, or only the selected statements.
Execute (All or Selection) to Text: Executes all statements in the SQL Query area, or only the selected statements, and displays it in plain text like the standard MySQL command line console.
Execute Current Statement: Executes the current SQL statement.
Execute Current Statement (Vertical Text Output): Executes all statements in the SQL Query area, or only the selected statements, and displays it in plain text like the MySQL command line console does vertically (\G).
Explain Current Statement: Describes the current statement by using the MySQL EXPLAIN statement.
Visual Explain Current Statement: Visually describes the current statement, based on EXPLAIN information provided by MySQL Server 5.6 and above. MySQL Workbench parses the EXPLAIN (JSON) output from MySQL server 5.6+, and outputs a visual representation.
For additional information about Visual Explain, see Section 7.3, “Visual Explain Plan” and Section 7.5, “Tutorial: Using Visual Explain to improve query performance”.
Stop: Stops executing the currently running script.
Stop Script Execution On Errors: If enabled, MySQL Workbench stops executing the a query if errors are found. It can be enabled/disabled from this menu.
Limit Rows: By default, the number of returned rows (LIMIT) is 1000. Values defined here affects subsequent statements. The number ranges from 10 to 50000, and "Don't Limit".
Collect Performance Schema Stats: Provides data to the Query Stats result set view, which includes statement specific information about Timing, Rows processed, Temporary tables, Joins per type, Sorting, and Index usage.
Collect Resultset Field Metadata: Provides data to the Form Editor and Field Types result set views.
Reconnect to Server: Reconnects to the MySQL server.
New Tab to Current Server: Creates a duplicate of the current SQL Editor tab.
Auto-Commit Transactions: Enable to auto-commit transactions.
Commit Transaction: Commits a database transaction.
Rollback Transaction: Rolls back a database transaction.
Commit Result Edits: Commits any changes you have made to the server.
Discard Result Edits: Discards any changes you have made.
Export Results: Exports result sets to a file. Selecting this option displays the Export Query Results to File dialog. The dialog enables you to select which result set you wish to export, the file format (CSV, HTML, XML), and the name and location of the output file. Then click Export to export the data.

Edit Menu

The Edit menu features the Format submenu. The Format submenu includes the following menu items:

Beautify Query: Reformats the query selected in the query tab and lays it out in nicely indented fashion.
UPCASE Keywords: Converts keywords to uppercase in the currently selected query in the query tab.
lowercase Keywords: Converts keywords to lowercase in the currently selected query in the query tab.
Un/Comment Selection: Comments the lines currently selected in the query tab. If the lines are already commented, this operation removes the comments.
Auto-complete: Triggers the auto-completion wizard. This is enabled (and triggered) by default, and can be disabled with Preferences, SQL Editor, Automatically Start Code Completion. Auto-completion will list functions, keywords, schema names, table names and column names.

8.1.4 Results Window

The results area of the screen shows the results from executed statements. If the script contains multiple statements, a result tab will be generated for each statemented that returned results.

Note

MySQL Workbench handles quoting and escaping for strings entered into the results grid, so adding quotes and proper escaping here is optional.

Note

It is possible to enter a function, or other expression, into a field. Use the prefix \func to prevent MySQL Workbench from escaping quotation marks. For example, for the expression md5('fred'), MySQL Workbench normally would generate the code md5(\'fred\'). To prevent this, enter the expression as \func md5('fred') to ensure that the quoting is not escaped.

Figure 8.4 SQL Editor - Result Grid

The result grid navigation panel offers the following options:

Reset: Resets all sorted columns.
Refresh: Refreshes all data by re-executing the original statement.
Filter Rows: performs a case-insensitive search of all cells. It automatically refreshes, and there is also the refresh button to perform this action manually.
Edit Current Row: Edit the current row.
Add New Row: Adds a new empty row, and highlights it in edit mode. Click Apply to execute (and review) the insert row query.
Delete Selected Rows: Deletes the selected rows. Click Apply execute (and review) the delete query.
Export: Writes a result set to a CSV, HTML, JSON, SQL INSERT, Excel, XML, or Tab separated file as required.

Note

This exports a result set. To export an entire table or schema, see Data Export.
Import: Import records from an external CSV file.
Wrap Cell Content: If the contents of a cell exceeds the cell width, then the data will be cut off with an ellipses. This option will instead wrap the contents within the cell, and adjust the cell height accordingly.

Note

The "Refresh" button automatically adjusts the column width to match the longest string one of its cells. You may also manually adjust the column width.

Right-clicking on a results grid tab opens the following context-menu:

Figure 8.5 SQL Editor - Result Grid Context Menu

Rename Tab: Customize the name (title) of this tab.
Pin Tab: Pin the results tab to the results grid. Executing additional SQL statements will create new result grid tabs.
Close Tab: Close this tab.
Close Other Tabs: Close all tabs except this one.

8.1.5 SQL Snippets tab

The Snippets tab includes built-in, local, and shared custom snippets. The My Snippets tab stores custom snippets in a file under the MySQL Workbench user's configuration directory. Select the Shared option for shared snippets.

Using Snippets

Snippets can be inserted into the SQL editor or the system's clipboard. To insert (use) a snippet, either use the snippet icons or right-click on the desired snippet and choose Insert.

Figure 8.6 SQL Snippets: Usage

Local Snippets (My Snippets)

Local snippets are stored in the MySQL Workbench user's directory. By default, the "My Snippets" SQL snippets are stored here:

Table 8.1 Default Local Snippet File Location

Operating System	File Path
Windows	%AppData%\MySQL\Workbench\User Snippets.txt
OS X	~username/Library/Application Support/MySQL/Workbench/snippets/User Snippets.txt
Linux	~username/.mysql/workbench/snippets/User Snippets.txt

Editing (or adding) snippets to "My Snippets" in MySQL Workbench edits this plain text file. Optionally, you can edit this file outside of MySQL Workbench or create new files that will also be listed under the snippets selector. For example, adding a file named "More Snippets.txt" will add a "More Snippets" section to the snippets selection box.

Shared Snippets

Shared snippets are saved in a .mysqlworkbench schema on the connected MySQL server. Selecting "Shared" for the first time will request permission for MySQL Workbench to create this shared .mysqlworkbench schema. Users connected to this MySQL server are allowed to create, edit, and use these shared snippets.

Note

Shared snippets were added in MySQL Workbench 6.2.0.

The .mysqlworkbench schema is hidden from within MySQL Workbench as it is considered an internal schema that does not need to be seen or edited.

Built-in Snippets

Several built-in SQL snippets are bundled with MySQL Workbench, and typically show the SQL syntax for MySQL operations. They are divided up into the following categories.

DB Mgmt (Database Management): Syntax examples use SHOW in many forms to provide information about databases, tables, columns, or status information about the MySQL server.
SQL DDL (SQL Data Definition Language): Syntax examples include creating, altering, and dropping tables, indexes, views, and procedures.
SQL DML (SQL Data Manipulation Language): Syntax examples for operations such as SELECT, INSERT, and REPLACE.

The built-in operations are stored in text files in the same directory as the custom snippet files.

Saving and Editing Snippets

To save a snippet, choose the Snippets Insert icon ( ) or right-click in the snippet window and choose Add Snippet from Editor Content from the context-menu. Double-click a snippet to open it, and choose the snippet editor to edit its body or title. This example shows two snippets with only the first having defined a name.

Figure 8.7 SQL Snippets: Editor

8.1.6 Context Sensitive Help

Select a keyword or function in your query, and after a delay it shows formatted help information from the MySQL Server (equivalent to using the help command from the command-line MySQL Client).

Figure 8.8 SQL Editor: Context Sensitive Help

8.1.7 Output History Panel

The Output is located at the bottom of MySQL Workbench. Its select box includes the Action Output, History Output, and Text Output options.

The Action Output panel displays a summary of the communication between the active MySQL connection in MySQL Workbench and the MySQL server, and can refer to errors or general information. Each message displays the time, action, and server response. This output is useful for troubleshooting scripts.

Figure 8.9 SQL Editor: Output: Action Output

The History Output panel provides a history of SQL operations carried out in MySQL Workbench for the active MySQL connection. The time and SQL code for each operation is recorded. To view the executed SQL statement, click the time, and the SQL code executed will be displayed in the SQL column.

Figure 8.10 SQL Editor: History Output

8.1.8 Table Data Search Panel

Find data across a MySQL connection by using the text search feature on any number of tables and schemas.

From the Schema Tree, choose the tables and/or schemas you want to search, and and then select Search Data Table... from the context menu.

Figure 8.11 Table Search Example: Multiple Tables and Schemas

The search options include:

Search for table fields that: "CONTAINS", "Search using =", "Search using LIKE", "Search using REGEXP". These search options are case-insensitive.
Max. matches per table: [100]
Max. total matches: [1000]
[ ] Search columns of all types: If checked, non-text column type columns are casted to CHAR to perform the matches, otherwise only text types (CHAR, VARCHAR, and TEXT) are searched. This is unchecked by default.

8.1.9 Export / Import a Table

Export or Import tables using the table export and import wizard.

Note

These wizards were added in MySQL Workbench 6.3.

Export a Table

Note

Alternatively, use Section 6.5, “Data Export and Import” to export larger sets of data, such as entire tables and databases.

Import into Table

Note

Alternatively, use Section 6.5, “Data Export and Import” to export larger sets of data, such as entire tables and databases.

8.1.10 Tutorial: Adding Data

In the previous section, you created a model, schema, and table. You also forward engineered your model to the live MySQL server. This section uses MySQL Workbench to add data into your MySQL database.

Open a MySQL connection.

Figure 8.12 Getting Started Tutorial - SQL Editor

From the Navigator panel on the left, select the movies table from the dvd_collection schema that we created earlier in this tutorial. Right-click on the movies table and choose Select Rows - Limit 1000 from the context menu.

Note

The Navigator panel has both Management and Schemas tabs.

Figure 8.13 Getting Started Tutorial - Adding Data from the SQL Editor
This displays the query and its associated results grid. The table is empty, and data may be added into the results grid.

Note

The movie_id column is set to AUTO_INCREMENT, so values are not needed for this column.

Input the following data into the movies table:

title release_date
Gone with the Wind 1939-04-17
The Hound of the Baskervilles 1939-03-31
The Matrix 1999-06-11
Above the Law 1988-04-08
Iron Man 2 2010-05-07

Note

Do not modify movie_id column values.
Click Apply to apply these changes to the live MySQL server.
View the data grid again and observe the generated AUTO_INCREMENT values.

Figure 8.14 Getting Started Tutorial - Edit Data
Optionally, you might confirm the changes by checking an external source, such as the MySQL Command Line Client. To check, enter SELECT * FROM movies; from the MySQL Command Line Client to confirm that the data was entered.

Figure 8.15 Getting Started Tutorial - View Data From The Command Line
You can also use MySQL Workbench to perform a similar check. Close the MyFirstConnection tab (or MySQL Workbench) and then open the MyFirstConnection connection from the home page. Execute USE dvd_collection; SELECT * FROM movies; to display the newly entered data.

title	release_date
Gone with the Wind	1939-04-17
The Hound of the Baskervilles	1939-03-31
The Matrix	1999-06-11
Above the Law	1988-04-08
Iron Man 2	2010-05-07

In this section of the tutorial, you have learned how to add data to your database, and also how to execute SQL statements using MySQL Workbench.

For additional information about the SQL editor, see Section 8.1, “Visual SQL Editor”.

8.1.11 The MySQL Table Editor

8.1.11.1 The Main Editor Window
8.1.11.2 The Columns Tab
8.1.11.3 The Indexes Tab
8.1.11.4 The Foreign Keys Tab
8.1.11.5 The Triggers Tab
8.1.11.6 The Partitioning Tab
8.1.11.7 The Options Tab

The MySQL Table Editor is a used to create and modify tables. .ou can add or modify a table's columns or indexes, change the engine, add foreign keys, or alter the table's name.

To access the MySQL Table Editor, right-click on a table name in the Object Viewer and choose ALTER TABLE. This opens a new tab within the main SQL Editor window. You can also access the MySQL Table Editor from an EER Diagram by double-clicking on a table object.

8.1.11.1 The Main Editor Window

Any number of tables may be edited in the MySQL Table Editor at any one time. Adding another table creates a new tab at the top of the editor. By default, the MySQL Table Editor appears docked at the top of the table editor tab, within the SQL editor..

The MySQL Table Editor is shown on top of the following figure.

Figure 8.16 The Table Editor

The MySQL Table Editor provides a work space that has tabs used to perform these actions:

Columns: Add or modify columns
Indexes: Add or modify indexes
Foreign Keys: Add or modify foreign keys
Triggers: Add or modify triggers
Partitioning: Manage partitioning
Options: Add or modify other options, divided in categories named general, row, storage, and merge

8.1.11.2 The Columns Tab

Use the Columns tab to display and edit all the column information for a table. With this tab, you can add, drop, and alter columns.

You can also use the Columns tab to change column properties such as name, data type, and default value.

Figure 8.17 The Columns Tab

Right-click a row under the Column Name column to open a pop-up menu with the following items:

Move Up: Move the selected column up.
Move Down: Move the selected column down.
Copy: Copies the column for a model.
Cut: Copies and then deletes the column for a model.
Paste: Pastes the column. If a column with the same name already exists, then _copy1 is appended to the column name.
Delete Selected Columns: Select multiple contiguous columns by right-clicking and pressing the Shift key. Use the Control key to select separated columns.
Refresh: Update all information in the Columns tab.
Clear Default: Clear the assigned default value.
Default NULL: Set the column default value to NULL.
Default 0: Set the column default value to 0.
Default CURRENT_TIMESTAMP: Available for TIMESTAMP data types.
Default CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP: Available for TIMESTAMP data types.

To add a column, click the Column Name field in an empty row and enter an appropriate value. Select a data type from the Datatype list. Select the column property check boxes as required according to the list of column properties below, and also read the CREATE TABLE documentation for information about what these options mean.

PK: PRIMARY KEY
NN: NOT NULL
UQ: UNIQUE INDEX
BIN: BINARY
UN: UNSIGNED
ZF: ZEROFILL
AI: AUTO_INCREMENT

To change the name, data type, default value, or comment of a column, double-click the value to edit it.

You can also add column comments to the Column Comment field. It is also possible to set the column collation, using the list in the Column Details panel.

To the left of the column name is an icon that indicates whether the column is a member of the primary key. If the icon is a small key, that column belongs to the primary key, otherwise the icon is a blue diamond or a white diamond. A blue diamond indicates the column has NN set. To add or remove a column from the primary key, double-click the icon. You can also add a primary key by checking the PRIMARY KEY check box in the Column Details section of the table editor.

If you wish to create a composite primary key you can select multiple columns and check the PK check box. However, there is an additional step that is required, you must click the Indexes tab, then in the Index Columns panel you must set the desired order of the primary keys.

Note

When entering default values, in the case of CHAR and VARCHAR data types MySQL Workbench will attempt to automatically add quotation marks, if the user does not start their entry with one. For other data types the user must manage quoting if required, as it will not be handled automatically by MySQL Workbench.

Caution

Care must be taken when entering a default value for ENUM columns because a non-numeric default will not be automatically quoted. You must manually add single quote characters for the default value. Note that MySQL Workbench will not prevent you from entering the default value without the single quotation marks. If a non-numeric default value is entered without quotation marks, this will lead to errors. For example, if the model is reverse engineered, the script will contain unquoted default values for ENUM columns and will fail if an attempt is made to run the script on MySQL Server.

Note

ENUM, BIT, and SET must contain at least one value when entering these data types into MySQL Workbench.

8.1.11.3 The Indexes Tab

The Indexes tab holds all index information for your table. Use this tab to add, drop, and modify indexes.

Figure 8.18 The Indexes Tab

Select an index by right-clicking it. The Index Columns section displays information about the selected index.

To add an index, click the last row in the index list. Enter a name for the index and select the index type from the list. Select the column or columns that you wish to index by checking the column name in the Index Columns list. You can remove a column from the index by removing the check mark from the appropriate column.

You can also specify the order of an index by choosing ASC or DESC under the Order column. Create an index prefix by specifying a numeric value under the Length column. You cannot enter a prefix value for fields that have a data type that does not support prefixing.

To drop an index, right-click the row of the index you wish to delete, then select the Delete Selected Indexes menu item.

8.1.11.4 The Foreign Keys Tab

The Foreign Keys tab is organized in much the same fashion as the Indexes tab and adding or editing a foreign key is similar to adding or editing an index.

Figure 8.19 The Foreign Keys Tab

To add a foreign key, click the last row in the Foreign Key Name list. Enter a name for the foreign key and select the column or columns that you wish to index by checking the column name in the Column list. You can remove a column from the index by removing the check mark from the appropriate column.

Under Foreign Key Options, choose an action for the update and delete events. The options are:

RESTRICT
CASCADE
SET NULL
NO ACTION

To drop a foreign key, right-click the row you wish to delete, then select the Delete Selected FKs menu item.

To modify properties of a foreign key, select it and make the desired changes.

8.1.11.5 The Triggers Tab

The Triggers tab opens a textbox to create or edit existing triggers.

To add a new trigger, click the [+] icon next to the trigger section. To delete a trigger, click the associated [-] icon. These icons become visible by hovering over a trigger or trigger section. Click Apply to commit your changes.

Figure 8.20 The Triggers Tab

8.1.11.6 The Partitioning Tab

To enable partitioning for your table, check the Enable Partitioning check box. This enables the partitioning options.

Figure 8.21 The Partitioning Tab

The Partition By pop-up menu displays the types of partitions you can create:

HASH
LINEAR HASH
KEY
LINEAR KEY
RANGE
LIST

Use the Parameters field to define any parameters to be supplied to the partitioning function, such as an integer column value.

Choose the number of partitions from the Partition Count list. To manually configure your partitions, check the Manual check box. This enables entry of values into the partition configuration table. The entries in this table are:

Partition
Values
Data Directory
Index Directory
Min Rows
Max Rows
Comment

Subpartitioning is also available. For more information about partitioning, see Partitioning.

8.1.11.7 The Options Tab

The Options tab enables you to set several types of options.

Figure 8.22 The Options Tab

which are grouped into the following sections:

General Options
Row Options
Storage Options
Merge Table options

The following discussion describes these options in more detail.

General Options Section

In the General Options section, choose a pack keys option. The options are Default, Pack None, and Pack All. You may also encrypt the definition of a table. The AUTO_INCREMENT and delayed key update behaviors apply only to MyISAM tables.

Row Options Section

To set the row format, choose the desired row format from the list. For more information about the different row formats that are available, see MyISAM Table Storage Formats.

These options are:

Default
Dynamic
Fixed
Compressed
Redundant
Compact

When you expect a table to be particularly large, use the Avg. Row, Min. Rows, and Max. Rows options to enable the MySQL server to better accommodate your data. See CREATE TABLE Syntax for more information on how to use these options.

Storage Options Section

The Storage Options section is available only for MyISAM tables. Use it to configure a custom path to the table storage and data files. This can help improve server performance by locating different tables on different hard drives.

Merge Table Options Section

Use the Merge Table Options section to configure MERGE tables. To create a MERGE table, select MERGE as your storage engine and then specify the MyISAM tables you wish to merge in the Union Tables dialog.

You may specify the action the server should take when users attempt to perform INSERT statements on the merge table. You may also select the Merge Method by selecting from the list. For more information about MERGE tables, see The MERGE Storage Engine.

8.1.12 Code Generation Overview

8.1.12.1 Generating SQL Statements
8.1.12.2 Generating PHP Code

This document provides a quick hands-on introduction to using MySQL Workbench to generate code for later use, for either in or outside of MySQL Workbench.

8.1.12.1 Generating SQL Statements

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

Below are common methods 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'

8.1.12.2 Generating PHP Code

MySQL Workbench can be used to generate PHP code with the bundled PHP plugin, by using the Tools, Utilities, Copy as PHP Code menu option.

Below is an example scenario for how to create PHP code. It is a SELECT statement, and optionally uses SET to set variables.

SQL @variables generate PHP variables in the code that then bind to the statement before execution.

Generate or type in the desired SQL query into the SQL editor. This example will use the sakila database, with the query being:
```
SET @last_update = '2006-02-14';

SELECT  actor_id, first_name, last_name, last_update 
  FROM  actor 
  WHERE last_update > @last_update;
```
While in the SQL editor, choose Tools, Utilities, Copy as PHP Code (Iterate SELECT Results) from the main menu. This will copy PHP code to the clipboard.
Paste the code to the desired location.

Additionally, PHP code that connects to the MySQL database can also be generated by choosing Tools, Utilities, Copy as PHP Code (Connect to Server).

After combining the two, the generated PHP code will look like this:


<?php

$host     = "localhost";
$port     = 3306;
$socket   = "";
$user     = "nobody";
$password = "";
$dbname   = "sakila";

$con = new mysqli($host, $user, $password, $dbname, $port, $socket)
    or die ('Could not connect to the database server' . mysqli_connect_error());

//$con->close();

$query = "SELECT actor_id, first_name, last_name, last_update 
          FROM   actor 
          WHERE  last_update > ?";
$last_update = '';

$stmt->bind_param('s', $last_update);

if ($stmt = $con->prepare($query)) {

    $stmt->execute();
    $stmt->bind_result($actor_id, $first_name, $last_name, $last_update);

    while ($stmt->fetch()) {
        // printf("%s, %s, %s, %s\n", 
        //   $actor_id, $first_name, $last_name, $last_update);
    }

    $stmt->close();
}

?>

Note

The generated PHP code uses the mysqli PHP extension for MySQL. This extension must be enabled in your PHP distribution for this code to work. For additional details about this PHP extension, see MySQL Improved Extension.

8.2 Object Management

8.2.1 Object Browser and Editor Navigator
8.2.2 Session and Object Information Panel
8.2.3 Schema and Table Inspector

The Object Browser allows you to navigate database schemas and objects. From here, you can perform common tasks such as selecting tables and fields to query, edit tables, create new or drop tables and databases, perform searches, and more.

8.2.1 Object Browser and Editor Navigator

The Navigator contains options to manage the active MySQL connection, and also lists the schemas available to that connection.

Navigator Schemas Tab

The Schemata list shows available schemata on the currently connected server. These can be explored to show tables, views, and routines within the schema.

Note

Internal schemas, such as "performance_schema", "information"schema", "sys", and "mysql", are hidden by default. Toggle the Show Metadata and Internal Schemas preference to list them in the object browser. Schemas beginning with a "." are also controlled by this setting.

Figure 8.23 SQL Editor - Navigator Schemas Tab

It is possible to set a schema as the default schema by right-clicking the schema and selecting the Set As Default Schema menu item. This executes a USE schema_name statement so that subsequent statements without schema qualifiers are executed against this schema. This setting applies only to the query session. To set a default schema for multiple MySQL Workbench sessions, you must set the default schema for the stored connection. From the Home screen, right-click on a MySQL connection, choose Edit Connection, and set the desired default schema on the Default Schema box.

Note

The selected schema is displayed as bold in the Schema navigator.

Double-clicking a table, view, or column name in the schemata explorer inserts the name into the SQL Query area. This reduces typing significantly when entering SQL statements containing references to several tables, views, or columns.

The Schema Navigator also features a context menu which can be displayed by right-clicking an object. For example, right-clicking a table displays the following menu items:

Select Rows - Limit 1000: Pulls up to 1000 rows of table data from the live server into a Results tabsheet, and enables editing. Data can be saved directly to the live server.
Table Inspector: Displays table information, similar to the Schema Inspector. This also has a simpler and easier to use interface for analyzing and creating indexes for tables.
Table Data Export: Opens the table export wizard to export the table's data to JSON or customized CSV.
Table Data Import: Opens the table import wizard to import JSON or CSV formatted data to the selected or new table.
Copy to Clipboard: There are various submenus, each of which copies information to the clipboard:
- Name (short): Copies the table name.
- Name (long): Copies the qualified table name in the form `schema`.`table`.
- Select All Statement: Copies a statement to select all columns in this form:
```
SELECT
`table`.`column1`,
`table`.`column2`,
...
FROM `schema`.`table`;
```
- Insert Statement: Copies an INSERT statement to insert all columns.
- Update Statement: Copies an UPDATE statement to update all columns.
- Delete Statement: Copies a DELETE statement in the form DELETE FROM `world`.`country` WHERE <{where_condition}>;.
- Create Statement: Copies a CREATE statement in the form DELETE FROM `world`.`country` WHERE <{where_condition}>;.
- Delete with References: Copies a DELETE statement, in the form of a transaction, that deletes all objects that reference the row (directly or indirectly).
  Use Select with References first to preview this operation.
- Select with References: Copies a SELECT statement that selects all objects that reference the row (directly or indirectly).
  Use Delete with References to generate a DELETE statement for this operation.
Send to SQL Editor: Provides functionality similar to Copy to Clipboard. However, this item inserts the SQL code directly into the SQL Query panel, where it can be edited further as required.
Create Table: Launches a dialog to enable you to create a new table.
Create Table Like...: Launches a dialog to enable you to create a new table, and to also apply predefined templates. For additional information, see Section 9.6, “Table Templates”.
Alter Table...: Displays the table editor loaded with the details of the table.
Table Maintenance: Opens a new tab for performing table maintenance operations. Operations include "Analyze Table", "Optimize Table", "Check Table", and "Checksum Table". Additional information about the table may also be viewed from this tab. For additional information, see Schema Inspector.
Drop Table...: Drops the table. All data in the table will be lost if this operation is carried out.
Truncate Table...: Truncates the table.
Search Table Data...: Opens a new tab for performing table searches. It performs a search on all columns, and offers additional options to limit the search.
Refresh All: Refreshes all schemata in the explorer by resynchronizing with the server.

Right-clicking on a schema provides similar options to the table context menu described above, but the operations refer to the Schema. For example, the Table Maintenance in the table context menu selects the table in the Schema Inspector, which is a schema context menu option.

8.2.2 Session and Object Information Panel

This panel summarizes the current connection to the server.

Figure 8.24 SQL Editor - Connection Information Palette

This panel also summarizes information about the object.

Figure 8.25 SQL Editor - Object Info

8.2.3 Schema and Table Inspector

The Schema and Table Inspector includes the ability to analyze and repair tables, and also view table metrics.

Schema Inspector

Use the Schema Inspector to browse general information from schema objects. It allows you to perform maintenance tasks on tables such as ANALYZE, OPTIMIZE, CHECK, and CHECKSUM TABLE. To access the inspector, right-click on a schema and select the Schema Inspector

Figure 8.26 Schema Inspector

Each tab lists topic oriented information, such as "Tables", "Indexes", and "Triggers". From the Tables tab, click Inspect Table to open the Table Inspector, or Maintenance to open the table maintenance tools:

Figure 8.27 Schema Inspector: Table Maintenance

Table Inspector

View table information, similar to the Schema Inspector. This also has a simpler and easier to use interface for analyzing and creating indexes for your tables.

To open, right-click on a table in the object browser and choose Table Inspector from the context menu.

Figure 8.28 Open the Table Inspector

The Table Inspector shows information related to the table.

Figure 8.29 Table Inspector: Info Tab

Prev	Up	Next
Chapter 7 Performance Tools	Home	Chapter 9 Database Design / Modeling