Lab Notebook: MySQL Workbench Basics
Objective:
By the end of this lab, students will be able to:
Create a schema (database)
Create tables within that schema
Do some CRUD: Insert records into the table, and display the contents of the table using the SQL EDITOR PANEL of MySQL Workbench.
Tools Required:
MySQL Workbench (Ensure it's installed and connected to a MySQL server instance.)
Instructions:
1. Creating a Schema (Database):
1.1. Open MySQL Workbench and connect to your MySQL server instance by clicking on the desired connection.
1.2. Once connected, in the Navigator pane on the left, right-click in the "SCHEMAS" area.
1.3. Choose "Create Schema..."
1.4. In the "Name" field, enter the desired name for your schema (e.g., classlab).
1.5. Adjust other settings if necessary, but the defaults are typically sufficient for this exercise.
1.6. Click "Apply". Review the SQL statement that will be executed, then click "Apply" again.
CREATE SCHEMA `classlab` ;
1.7. Click "Finish". Your new schema should now appear in the "SCHEMAS" list.
2. Creating a Table:
2.1. In the "SCHEMAS" (which is the ‘databases’) pane, double-click on the schema you've just created (e.g., classlab) to set it as the default schema.
2.2. Right-click on the "Tables" folder within your schema and select "Create Table..."
2.3. In the "Table Name" field, enter the desired name (e.g., customers).
2.4. In the "Columns" tab, start defining your table columns by entering column names, data types, and other attributes. For our example:
customer_id: INT, PK (Primary Key), NN (Not Null), AI (Auto Increment) first_name: VARCHAR(50), NN last_name: VARCHAR(50), NN ... (Continue for other columns as per our previous discussions) 2.5. Once all columns are defined, click "Apply". Review the SQL statement, then click "Apply" again.
#DDL
CREATE TABLE `classlab`.`customers` (
`customer_id` INT NOT NULL,
`customer_name` VARCHAR(45) NULL,
PRIMARY KEY (`customer_id`))
COMMENT = 'customer data';
CREATE TABLE `classlab`.`orders` (
`order_id` INT NOT NULL,
`order_date` DATE NOT NULL,
`order_amount` INT NOT NULL,
PRIMARY KEY (`order_id`)
) COMMENT = 'Order data';
CREATE TABLE `classlab`.`products` (
`product_id` INT NOT NULL,
`product_name` varchar(50) NOT NULL,
`price` INT NOT NULL,
PRIMARY KEY (`product_id`)
) COMMENT = 'Products data';
CREATE TABLE `classlab`.`order_details` (
`order_id` INT NOT NULL,
`product_id` INT NOT NULL,
`quantity` INT NOT NULL,
PRIMARY KEY (`order_id`)
) COMMENT = 'Relator Table connecting the Order Table and Product Table';
CREATE TABLE `monthly_targets`.`order_details` (
`month` INT NOT NULL,
`target_amount` INT NOT NULL
) COMMENT = 'Relator Table connecting the Order Table and Product Table';
2.6. Click "Finish".
3. Inserting a Record into the Table:
3.1. In the "SCHEMAS" pane, expand your schema and the "Tables" folder, then right-click on your table (e.g., customers) and select "Insert Data...".
3.2. A grid will appear. Click into the cells to enter data for a new record. For our example:
Leave customer_id blank (it will auto-increment). ... (Continue for other columns) 3.3. Once you've entered all data for the record, click the floppy disk icon (or "Apply Changes") at the top-left of the grid to save.
4. Displaying Data from the Table:
4.1. In the main query editor (the large white space in the center), type the following SQL command:
SELECT * FROM customers;
4.2. Click the lightning bolt icon (or "Execute the selected portion of the script") to run the query.
4.3. The results will be displayed in the "Result Grid" below the query editor. You should see the record(s) you've inserted.
Conclusion:
You've successfully created a schema, designed a table, inserted a record, and queried data using MySQL Workbench. Practice these steps to become proficient in basic database operations.
Note: Always remember to save your work and ensure you are working in the correct schema to avoid any unintended changes or data loss.