After getting a new database in place, the next step is to create tables. In this article, Robert Sheldon walks you through creating tables with SQL Workbench and with scripts. Show
Entry in Robert Sheldon's series on Learning MySQL. The series so far:
Tables lie at the heart of any MySQL database, providing a structure for how data is organized and accessed by other applications. Tables also help to ensure the integrity of that data. The better you understand how to create and modify tables, the easier it will be to manage other database objects and the more effectively you can work with MySQL as a whole. Having a solid foundation in tables can also help you build more effective queries so that you’re retrieving the data you need (and only that data), without compromising database performance. This article is the second in a series on MySQL. I recommend that you review the first article before launching into this one, if you haven’t done so already. In this article, I focus primarily on how to create, alter, and drop tables, demonstrating how to use both SQL statements and the GUI features in MySQL Workbench. As with the first article, I used the MySQL Community edition on a Windows computer to create the examples for this article. All the examples were created in Workbench, which comes with the Community edition. Using the MySQL Workbench GUI to create a databaseBefore creating any tables, you need a database for those tables, so I’ll spend a little time on databases first. Creating a database in MySQL is a relatively straightforward
process. As you saw in the first article in this series, you can run a simple The
Both statements instruct MySQL to create a database that uses the default collation and character set. You can run either statement from the MySQL command prompt or from within MySQL Workbench. To run a statement in Workbench, you need only open a query tab, type, or paste the statement onto the tab, and click one of the execute buttons on the toolbar. MySQL does the rest. Instead of using a You can also use the GUI features built into Workbench to create a database. Although this might seem
overkill, given how easy it is to run a To use the GUI to create a database, start by clicking the create schema button on the Workbench toolbar. (The button looks like a standard database icon and displays the tooltip Create a new schema in the connected server.) When the Schema tab opens, you need only provide a database name, as shown in Figure 1. Figure 1. Adding a database to a MySQL instance If you want to use a character set or collation other than the defaults, you can select them from the drop-down lists. For example, you might select utf8 for the character set and utf8_unicode_ci for the collation. With MySQL, you can set the character set and collation at multiple levels: server, database, table, column, or string literal. The default server character set is utf8mb4, and the default collation is utf8mb4_0900_ai_ci. Before deviating from the defaults, I suggest that you first review the MySQL documentation on character sets and collations. The Schema tab also includes the Rename References option. However, this is disabled and applies only when you’re updating a database model. Workbench sometimes includes interface options that don’t apply to the current circumstances, which can be confusing when you’re first getting started with MySQL or Workbench. However, you’re usually safe to stick with the default values if you’re not sure about an option, at least until you better understand how it works and whether it’s even applicable. For this article (assuming that you want to follow along with the examples), you can stick with the default character set and collation and click Apply. This will launch the Apply SQL Script to Database wizard, shown in Figure 2. The wizard’s first screen displays the SQL statement that Workbench generated but not yet applied against the MySQL instance. Figure 2. Verifying the CREATE SCHEMA statement The screen also includes the Algorithm option and Lock Type option. Both options are related to MySQL’s online DDL feature, which provides support for in-place table alterations and concurrent DML. You do not need to be concerned about these options right now and can stick with the defaults. (This is another example of Workbench’s sometimes confusing options.) However, if you’re interested in learning more about these features, you can find information in the MySQL documentation that covers InnoDB and online DDL. To create the database, click Apply, which takes you to the next screen, shown in figure 3. This screen essentially confirms that the database has been created. You can then click Finish to close the dialog box. Be sure to close the original Schema tab as well. Figure 3. Finalizing the new schema (database) The database should now be listed in the Schemas panel in Navigator. If it is not, click the refresh button in the panel’s upper right corner. The travel database (schema) should then show up along with other databases on the MySQL instance. On my system, the only other database is the default sys database, as shown in Figure 4. Figure 4. Viewing the new database in Navigator At this point, MySQL has created only the database structure. You can now add tables to the database, along with views, stored procedures, and functions. Using the MySQL Workbench GUI to create a tableYou can also use the Workbench GUI to add a table a database. For this approach, start by selecting the travel database node in Navigator. You might need to double-click the node to select it. The database name should be bold once selected. With the database selected, click the create table button on the Workbench toolbar. (The button looks like a standard table icon and includes the tooltip Create a new table in the active schema in connected server.) When you click the button, Workbench opens the Table tab, as shown in Figure 5. Figure 5. Adding a table through the Workbench GUI The tab provides a detailed form for adding columns to the table and configuring table and column options. The tab also includes several of its own tabs (near the bottom of the interface). The Columns tab is selected by default, which is where you’ll be doing most of your work. Start by providing a name for the table. For this article, I used manufacturers. I also stuck with the default character set and collation, as well as the default storage engine, InnoDB. The InnoDB engine is considered a good general-purpose storage engine that balances high reliability and high performance. MySQL also supports other storage engines, such as MyISAM, MEMORY, CSV, and ARCHIVE. Each one has specific characteristics and uses. For now, I recommend that you stick with the default InnoDB until you better understand the differences between storage engines. I also recommend that you review the MySQL documentation for detailed information about the different engine types. At this point, you can also add a table-level comment if you’re so inclined. Although that’s not necessary for this article, information of this sort can be useful when building a database for production. Once you have the basics in place, you can add the first column, which will be named manufacturer_id. It will also be the primary key and include the AUTO_INCREMENT option, which tells MySQL to automatically generate a unique number for that column’s value, similar to the IDENTITY property in SQL Server. To add the column, double-click the first cell in the grid’s Column Name column and type manufacturer_id. In the Datatype column for that row, type INT or select INT from the drop-down list. Next, select the following check boxes:
With regard to the UNSIGNED option, MySQL lets you specify whether an integer data type is signed or unsigned. If signed, a column’s values can include negative numbers. If unsigned, the values cannot include negative numbers. Integer data types are signed by default. Older MySQL versions permitted you to configure the DECIMAL, DOUBLE, and FLOAT data types as unsigned, but that feature has been deprecated. Integer signing affects the range of supported values. Consider the INT data type. If a column is defined with a signed INT data type, the column’s values must be between -2147483648 and 2147483647. However, if the data type is unsigned, the values must be between 0 and 4294967295. If you know that a column will never need to store a negative integer, you can define the data type as unsigned to support a greater range of positive integers. As you configure a column, Workbench updates the option settings in the section below the grid. This bottom section reflects the settings of the column selected in the grid and can be handy when defining multiple columns. The bottom section also provides several additional optional. For example, you can add a comment specific to the selected column. You can also set the character set and collation at the column level (for character data types). Figure 6 shows the manufacturer_id column as it’s been defined so far. Notice that the bottom section reflects all the settings specified in the column grid. Figure 6. Adding a column to a table definition You can repeat a similar process for each additional column you want to include, choosing the data type and configurable options. When specifying the data type, you can type it or select it from the drop-down list. However, some of the data types in the drop-down-list, including TIMESTAMP, are listed with trailing parentheses that should not be there. This is apparently a Workbench bug. You’ll have to manually remove the parentheses for the data type to be listed correctly. You can also specify a default value for any columns you’re defining. For this article, I added three more columns:
Figure 7 shows the Table tab after I added the three columns. The grid includes a row for each column, with each row reflecting the column’s configuration. Figure 7. Adding multiple columns to the new table definition There’s one other step to take to complete the table’s definition. For this, you need to go to the Options tab and set the initial AUTO_INCREMENT seed value. In this case, I used 1001, as shown in Figure 8. As a result, the first record added to the table will be assigned a manufacturer_id value of 1001, with each subsequent row incremented by 1. Figure 8. Setting the seed value for the AUTO_INCREMENT option As you can see, there are plenty of other table options that you can configure, and there are other tabs on which you can configure additional options. But for now, we’ll stop here and add the table to the database. To do so, click the Apply button, which launches the Apply SQL Script to Database wizard, shown in Figure 9. Figure 9. Verifying the CREATE TABLE statement On this screen, you can review the SQL statement that has been generated and select an algorithm and lock type, if desired. You can also edit the SQL statement directly on this screen. (Just be sure not to introduce any errors.) Notice that the manufacturer_id column is configured with the To complete the table creation process, simply click Apply and then click Finish on the next screen. You should then be able to confirm in Navigator that the table has been created, as shown in Figure 10. Figure 10. Viewing the new table in Navigator Notice that an index is created for the primary key column. MySQL automatically names primary key indexes PRIMARY, which might be different from what you’ve seen in other database systems. Because a table can include only one primary key, there is no problem with duplicate index names. Using SQL to create a table in a MySQL databaseThe Workbench GUI features can be handy for creating database objects, especially if you’re new to MySQL or database development. They can also be useful when trying to understand the various options available when creating an object. However, most developers prefer to write the SQL code themselves, and if you already have at least some experience with SQL, you’ll likely have little problem adapting to MySQL. With this in mind, the next step will be to create a second table in the travel database. For this, you can use the following
For the most part, the One item worth pointing out is the max-weight column, which is configured with the MEDIUMINT data type (unsigned). As you’ll recall from the first article, the data type falls between SMALLINT and INT data types in terms of the supported numeric range. In this way, you have more granular options for working with integer values. Neither SQL Server nor Oracle Database support the MEDIUMINT data type. What you haven’t seen before (at least not in this or the previous article) is the foreign key constraint that’s defined on the The table definition also includes two table options. The At this point, the Figure 11. Viewing the airplanes table in Navigator As you can see, Navigator lists the foreign key beneath the Foreign Keys node. Notice that MySQL also adds an index for the foreign key, which is assigned the same name as the foreign key. We’ll be covering indexes later in this series. Altering a table definition in a MySQL table databaseYou can also use SQL to modify a table definition in MySQL. For example, the following
Both columns are configured with the Each column definition also includes an When you run this You can also use the Workbench GUI to alter a table definition. To do so, right-click the table in Navigator and then click Alter Table, which opens the Table tab. Here you can modify the column definitions or table options. You can also add or delete columns. Figure 12 shows the Table tab with the wingspan and plane_length columns selected, which are the columns you just added above. Figure 12. Viewing the new columns in the table editor The next step will be to add a generated column to the table. A generated column is one in which the value is computed from an expression, similar to computed columns in SQL Server and Oracle Database. To add the column, double-click the first cell in the first empty line of the table grid (beneath the last_update column definition) and then type parking_area for the column name. On the same line, type INT for the data type, select the G option (for GENERATED), and type wingspan * plane_length in the Default/Expression column. The expression multiplies the wingspan value by the plane_length value to arrive at the total area. When you create a generated column in the GUI, Workbench automatically selects the Virtual option in the column detail area (near the bottom of the tab). This means that the column values will be generated on demand, rather than being stored in the database. The Stored option does just the opposite. The value is calculated when a row is inserted into the table, where the value remains until the row is updated or deleted. For this article, I used the Stored option. After you create a column, you can move it to a new location in the list of columns by dragging it to the desired position. In this case, I moved the parking_area column to after the plane_length column, as shown in Figure 13. Figure 13. Adding a generated column to the airplanes table That’s all you need to do to add a generated column to the table. To complete the process, click Apply, which launches the Apply SQL Script to Database wizard. Here you can review the SQL script, as shown in Figure 14. Figure 14. Verifying the new column being added to the airplanes table When Workbench
generates the Workbench also adds the If this all looks good to you, click Dropping a table from a MySQL databaseAs with other DDL actions in Workbench, you can use SQL or the GUI to drop a table from a database. For example, you can remove the airplanes table by running the following
You can also drop a table through Navigator. To do so, right-click the table and then click Drop Table. This launches the Drop Table dialog box, shown in Figure 15. Click Drop Now to remove the table. Figure 15. Deleting a table in Workbench Notice that the dialog box also includes the Review SQL
option. Click this instead if you want to review the Working with tables in a MySQL databaseMySQL tables support a variety of options at both the column and table level, far more than can be covered reasonably in a single article. You can also create temporary tables or partition tables. You’ll find it well worth your while to review the MySQL documentation on the CREATE TABLE statement. There you can see for yourself the many ways in which you can define a MySQL table. That said, what I’ve covered here should provide you with a good foothold for getting started with tables, whether you use SQL or the MySQL GUI. What you’ve learned here will also provide you with a foundation for working with other types of database objects, which could prove useful as we advance through this series. How can I see columns in MySQL table?You can list a table's columns with the mysqlshow db_name tbl_name command.. Field. The name of the column.. Type. The column data type.. Collation. ... . Null. ... . Key. ... . Default. ... . Extra. ... . Privileges.. Which command is used for viewing the table definition in MySQL?The DESC is the short form of DESCRIBE command and used to dipslay the information about a table like column names and constraints on column name. The DESCRIBE command is equivalent to the following command − SHOW columns from yourTableName command.
What are views in MySQL Workbench?MySQL supports views, including updatable views. Views are stored queries that when invoked produce a result set. A view acts as a virtual table. The following discussion describes the syntax for creating and dropping views, and shows some examples of how to use them.
|