Fun fact about MySQL: How to get the status of your MySQL environment: use the following command:
# mysqladmin -u root -ptmppassword status
Now, step 1 on how to create a SQL Table in MySQL using MySQL Workbench, open your version of Workbench. Before we continue, you need MySQL installed on your localhost and need a user already setup with full rights. You also need a database to create the table within, if you don't, go to URL How to create a MySQL Database/Schema using MySQL Workbench, this post has a step by step guide on how to create a database/schema within MySQL using MySQL Workbench.
Right click on the database you want to create your SQL Table in and click on Create Table
Now, you will start to complete you new SQL Table. First you need a name for your table, complete field Table Name.
As explained on top of this post, a Table consists of columns and rows. This step you will define your column names and all properties you want to assign to your columns. For this example, lets create a table where you will save the user details including the password the user uses to log into your application. I normally use the following fields:
- Username
- Email Address
- Password
It depends on your requirements, you can combine your Email Address and Username if they have to log into your application using the Email Address. Now that we have our columns, we need to workout the properties for these columns, for example, the type of data we will store. Find below an example table design:
As per the above print screen, I've included another column called create_time because I have a business requirement for my application to track when the user was created. Now, lets go through the data types per column. I've made all my column datatype VARCHAR() because I will save string values in these columns and made the create_time column datatype TIMESTAMP because I need to save the date and time in the column.
This Users table will be linked to Profile table where we can determine what type of rights the user will have in our application. The best way to create the link is to use identifiers and in this scenario, we need a Primary Key column and I find it easier to use IDs. To use IDs, add a ID Column and move the column to the top of the column list and then select the SMALLINT(1) as the datatype. Now to apply the following properties to the ID column:
- PK = Primary Key
- NN: Not Null
- UQ: Unique Identifier
- AI: Auto Increment
By setting all of these properties, the column will auto populate the column with Integer values adding 1 every time you INSERT a row into your Users table. This will be your unique identifier for your links between SQL tables (example: ID in Users is your Primary Key and UserID in your Profile table will be your Foreign Key). When you click on Apply, MySQL Workbench will generate a SQL Script for you that will create the SQL Table, an example below:
Click on Apply to run the SQL query and create your Table. Afterwards you can expand your Database used and will find your new Users table.
I will create another post where we will ALTER your table.
# mysqladmin -u root -p ping
: