Create a New Schema

How to create a new Schema?

We have seen how we can install and connect to the database via the MySQL toolkit and the MySQL workbench. Now we will see how we can create a new schema by using a toolkit and workbench.

1. Create a new Schema using the workbench:

Open the MySQL workbench and click on create new schema button as shown below.

Once clicked on the above button new window will be opened as shown in the below image. Give the name of the schema as ‘test_schema1’ and click on the ‘Apply’ button.

Following confirmation will be opened with the create query. Check the name and online DDL type (change type if required).

Once we click on Apply button new schema will be created and a success message window will appear as below.

Once the database schema is created we can see it from the schemas tab as shown below.

2. Create new Schema using MySQL toolkit:

If MySQL Workbench is not installed in the system, we can also create a schema by using the command line.

Open the mysql toolkit from the below command

mysql -u root -p

Once the toolkit is opened successfully check the schemas available from the below command.

show databases;

We can create a new database schema using the following command.

create schema test_schema2;

We can see the all created schemas as shown in the below image from the mysql toolkit. Note that schema created by using MySQL workbench will also be shown (since the database server is the same).

As we can see from the above image that for those 2 test schemas no table is present same we can check in the toolkit by using the following command it will show an Empty Set message.

show tables;

Creating new table

Since we have created database schemas let’s create one user table using both the workbench and toolkit.

1. Creating a table from the workbench.

Go to the schema in which the table needs to be created. Right Click on the tables and select create table option. We will have a table having the name ‘users’ which will have columns such as user_id, user_name, user_email, user_number, and user_status.

Once we click on the Create table following window will get open.

1. We can give the table name as ‘users’ which is as shown in the below image selected with red color.
2. Once the table name is given we can add multiple required columns as shown below.
3. We can also select different datatypes for our columns as highlighted in orange color, and also change the varchar value as varchar(50) or varchar(10) based on the required column size.
4. The area selected in yellow color is where we can set the storage/property type of that column.
   1. PK: Primary Key
   2. NN: Not Null (if selected we can not add a null value for that column)
   3. UQ: Unique
   4. B: Binary
   5. UN: Unsigned
   6. ZF: Zero Fill
   7. AI: Auto Increment
   8. G: Generated
5. Default/Expression: here we can set any default value or an expression for our column. So if no value is given for that column, that default value will be updated.
6. We can also give comments to our column just in case any special instructions need to give to that column so that any other developer using will follow the same, we can add comments for that column as shown in the selected blue color.
7. Once all changes are done click on ‘Apply’.

Once clicked on apply we will get the new window with generated SQL query, click on the Apply button from that window. Once the table is created successfully, we will get the success message window.

Once the table is created we can able to see a list of table details as shown below.

If you click on the selected icon above, we will be able to see a list of details (if present) in the table, and we can also edit or add new details from the same list.

Since we don’t have any details in our newly created table data will be shown below.

If we click on each column value we will able to edit the same as below.

Note that user_status we set the default value as inactive so if we do not add anything(as in row 2) then that value will be set as ACTIVE.

Once changes are done click on the ‘Apply’ button as shown below.

One popup window will appear with insert queries like below. 

Click on the ‘Apply’ button if the queries are correct. We will get the success window, click on ‘Finish’. Our data will be added successfully to the table.

We will able to see the newly added data.

Follow the same process for any update for the existing row.

To delete any row simply select the rows to be deleted, right-click on it and select Delete Row(s) option and then click on ‘Apply’ as shown below.

2. Creating a new table with MySQL toolkit.

Open the command window and Login to MySQL. The same table with the same columns and settings will create in ‘test_schema2’. Enter the following command to select the schema.

use test_schema2;

Once the schema is selected enter the following query once the MySQL toolkit is opened.

CREATE TABLE `users` (
  `user_id` int NOT NULL,
  `user_name` varchar(50) NOT NULL,
  `user_email` varchar(45) DEFAULT NULL,
  `user_number` varchar(15) DEFAULT NULL,
  `user_status` varchar(10) NOT NULL DEFAULT 'ACTIVE' COMMENT 'this is user status for active or inactive users',
  `user_password` varchar(45) NOT NULL,
  `created_on` datetime NOT NULL,
  PRIMARY KEY (`user_id`),
  UNIQUE KEY `user_id_UNIQUE` (`user_id`)
);

We can select from the table and see if any data is present or not by using the select query below

Select * from users;

To insert data into the table enter the following query:

INSERT INTO users (`user_id`, `user_name`, `user_email`, `user_number`, `user_status`, `user_password`, `created_on`) 
VALUES ('1', 'ugtworld', 'ugtworld.com', '9999999999', 'INACTIVE', 'abcd123', '2022-10-10 16:26:35');

Let’s also add a row without giving user_status.

INSERT INTO users (`user_id`, `user_name`, `user_email`, `user_number`, `user_password`, `created_on`) 
VALUES ('2', 'ugt', 'ugt.com', '8888888888', 'abc123', '2022-10-10 16:26:35');

If we want to update any details we can use the following query.

update users set user_email = 'ugt2.com' where user_id = 2;

If we want to delete any row we can use the following query from the toolkit.

 delete from users where user_id = 2;

-A blog by Shwetali Khambe