Unlock the Secrets of MySQL: A Hands-on Tutorial

Introduction

In this project, you will learn how to access the MySQL database, convert a date string to a date type object, and save the SQL statement to a script file.

👀 Preview

🎯 Tasks

In this project, you will learn:

How to access the MySQL database using the sudo command without a password
How to write an SQL statement to convert a date string to a date type object
How to save the SQL statement to a script file and run it in the MySQL client

🏆 Achievements

After completing this project, you will be able to:

Understand how to access the MySQL database and execute SQL statements
Convert date strings to date type objects using SQL
Create and run SQL script files to automate database tasks

Skills Graph

%%%%{init: {'theme':'neutral'}}%%%% flowchart RL mysql(("MySQL")) -.-> mysql/BasicKeywordsandStatementsGroup(["Basic Keywords and Statements"]) mysql(("MySQL")) -.-> mysql/DatabaseFunctionsandDataTypesGroup(["Database Functions and Data Types"]) mysql(("MySQL")) -.-> mysql/SystemManagementToolsGroup(["System Management Tools"]) mysql/BasicKeywordsandStatementsGroup -.-> mysql/use_database("Database Selection") mysql/BasicKeywordsandStatementsGroup -.-> mysql/select("Data Retrieval") mysql/BasicKeywordsandStatementsGroup -.-> mysql/source("External Code Execution") mysql/DatabaseFunctionsandDataTypesGroup -.-> mysql/database("DB Function - Info Retrieval") mysql/DatabaseFunctionsandDataTypesGroup -.-> mysql/date("Date Type") mysql/SystemManagementToolsGroup -.-> mysql/mysqladmin("Admin Utility") subgraph Lab Skills mysql/use_database -.-> lab-301302{{"Columbus Discovery of the New World"}} mysql/select -.-> lab-301302{{"Columbus Discovery of the New World"}} mysql/source -.-> lab-301302{{"Columbus Discovery of the New World"}} mysql/database -.-> lab-301302{{"Columbus Discovery of the New World"}} mysql/date -.-> lab-301302{{"Columbus Discovery of the New World"}} mysql/mysqladmin -.-> lab-301302{{"Columbus Discovery of the New World"}} end

Access MySQL Database

In this step, you will learn how to access the MySQL database using the sudo command without any password.

Open a terminal on your system.
Start the MySQL service by running the following command:

sudo service mysql start

Access the MySQL client by running the following command:

sudo mysql

This will allow you to access the MySQL database without a password.

Convert Date String to Date Type

In this step, you will learn how to write an SQL statement to convert the date string 14921012 to a date type object and display it.

In the MySQL client, run the following SQL statement:

SELECT STR_TO_DATE('14921012', '%Y%m%d') AS `Columbus's Discovery`;

This statement uses the STR_TO_DATE() function to convert the date string 14921012 to a date type object, and the AS keyword is used to set an alias for the result as Columbus's Discovery.

The output of this query will be:

+----------------------+
| Columbus's Discovery |
+----------------------+
| 1492-10-12           |
+----------------------+

Save the SQL Statement to a Script File

In this step, you will learn how to save the SQL statement to a script file named Discovery.sql and place it in the /home/labex/project directory.

Open a text editor on your system.
Copy the following SQL statement into the text editor:

SELECT STR_TO_DATE('14921012', '%Y%m%d') AS `Columbus's Discovery`;

Save the file as Discovery.sql in the /home/labex/project directory.

Now, you have completed the project. You can run the script file in the MySQL client by using the following command:

SOURCE /home/labex/project/Discovery.sql;

This will execute the SQL statement in the Discovery.sql file and display the result.

You should see the following output:

MariaDB [(none)]> SOURCE /home/labex/project/Discovery.sql;
+----------------------+
| Columbus's Discovery |
+----------------------+
| 1492-10-12           |
+----------------------+
1 row in set (0.000 sec)

✨ Check Solution and Practice

Summary

Congratulations! You have completed this project. You can practice more labs in LabEx to improve your skills.