MySQL Tutorial

MySQL Tutorial MySQL Features MySQL Database Introduction MySQL Environmental Setup MySQL Data Types MySQL variable MySQL Advance table Query MySQL database queries MySQL Entity-Relationship Model MySQL Table Query MySQL Operators MySQL logical conditions MySQL Queries MySQL Clauses Clustered vs Non-Clustered Index MySQL Full text index MySQL Descending Index MySQL Invisible Index MySQL Composite Index MySQL Prefix index MySQL Index MySQL Create index MySQL Drop Index MySQL Show index MySQL Unique index MySQL Table MySQL Variable MySQL View MySQL Constraints MySQL Command Line Client Basic Queries MySQL Stored Procedure MySQL IF Statement MySQL Subquery MySQL Triggers

MySQL Join

MySQL Join MySQL CROSS JOIN MySQL DELETE JOIN MySQL EQUI JOIN MySQL INNER JOIN MySQL Union MySQL NATURAL JOIN MySQL RIGHT JOIN MySQL SELF JOIN MySQL UPDATE JOIN

MySQL Function

MySQL Function MySQL AVG() Function MySQL SUM() Function MySQL String() Function MySQL Advance() Function MySQL Aggregate() Function MySQL COALESCE() Function MySQL Control Flow Function MySQL COUNT() Function MySQL Date And Time Function MySQL GREATEST() Function MySQL ISNULL() Function MySQL LEAST() Function MySQL Math() Function MySQL MAX() Function MySQL MIN() Function MySQL find_in_set() function MySQL ASIN() Function MySQL CEIL() function MySQL CEILING() function MySQL TAN() Function MySQL Truncate() Function MySQL FLOOR() function MySQL LN() function MySQL LOG2() function MySQL LOG10() function MySQL MOD() function MySQL PI() function MySQL POW() function MySQL RADIANS() function MySQL RAND() function MySQL ROUND() function MySQL Character Length Function MySQL Current Date Function MySQL Date Add Function MySQL Date Format Function MySQL Datediff Function MySQL Day Function MySQL Elt Function MySQL Export Set Function MySQL Field Function MySQL Format Function MySQL From Base64 Function MySQL Hex Function MySQL Insert Function MySQL Instr Function MySQL Length Function MySQL CONCAT() function MySQL FIND_IN_SET() function MySQL LIKE() function MySQL LOAD_FILE() function MySQL LOCATE() function MySQL LOG() function MySQL MONTHNAME() function MySQL NOW() function MySQL PERIOD_ADD() function MySQL PERIOD_DIFF() function MySQL POWER() function MySQL QUARTER() function MySQL REVERSE() function MySQL RIGHT() Function MySQL RPAD() function MySQL RTRIM() function MySQL SEC_TO_TIME() function MySQL SOUNDEX() function

Questions

Which Clause is Similar to Having Clause in MySQL

Misc

MySQL Error 1046 - No Database Selected Failed to Start MySQL Service Unit MySQL Service Unit not Found Import MySQL Connector Mudule not Found Error No Module Named MySQL Joins Available in MySQL MySQL Docs MySQL Download For Windows 7 64 Bit MySQL Error Code 1064 MySQL Export MySQL History MySQL Host MySQL Import MySQL Drop All Tables MySQL Drop MySQL Error Code 1175 MySQL Events MySQL Except MYSQL Foreign Key Constraint MySQL If Exists MySQL IndexOf MySQL List All Tables json_extract in MySQL

MySQL Database Introduction

MySQL - Database Introduction: The database plays an essential role in MySQL for storing and modifying the data. The database creates and keeps multiple tables. The database organizes and manipulates data files using a table and its index, row, and column.

MySQL management system handles databases using two methods. The first method is a Command-line client of MySQL. The second method is MySQL workbench. In this chapter, you will learn databases using command line client queries and workbench interface.

MySQL workbench creates a database to save schemas and tables as per requirement. The Schemas saves various tables for storing data. After, the table can create, save, delete, and update the information of the application. This interface does not use query due to the automation of software.

MySQL command-line client requires a database. We cannot access data files without a database. MySQL has several queries to access and handle data.

MySQL - Database Queries

The important command-line client queries are given below.

Database QueriesDescription
Create database Create a new database in the MySQL interfaces.
Show database Display available database in the MySQL interfaces.
Use database Use database in the MySQL interfaces.
Delete database Remove unwanted databases in the MySQL interfaces.
Copy database Create a duplicate database in the MySQL interfaces.

Prerequisites

The command-line client interfaces use passwords and start to operate the data. If a database is available, then the interface must use the available database. If the database does not exist in the system; then, the interface creates a new database.

Create a database in MySQL command-line client

MySQL requires a database to access old data and store new data. If a database is not present, then the command-line client creates a new database. The database can save various tables and manage table data.

The database helps to operate the information of the table. If you cannot use a database, then the MySQL interface does not work. Creating a new database is the primary step of operating data using the MySQL system.

Syntax

The below syntax is to create a new database. The database name must be unique or different from another database.

CREATE DATABASE database_name;

If you are confused about the database name, then use the "if not exists" command. If the given name available in the system, then the output shows an error. If the given name does not exist in the system, then you get the new database. The "if not exists" command is optional.

CREATE DATABASE IF NOT EXISTS database_name;

If you want to show the format of the newly created database, then use the following syntax.

SHOW CREATE DATABASE database_name;

Examples of creating a database.

1) Example: create a new database.

Execute the below query to get the new database.

 mysql> create database tutorials;
 Query OK, 1 row affected (1.11 sec) 
mysql> create database tutorials;

As shown in the above image, the command-line client has created a new thread for MySQL query. It would be best if you start using the available database. You are ready to use other queries.

2) Example: create a new database using the "IF NOT EXISTS" command.

If you want to get the database's exact name, then use the below statement. Execute the below query in the command-line client.

 mysql> create a database if not exists tutorials;
 Query OK, 1 row affected, 1 warning (0.23 sec) 
mysql> create a database if not exists tutorials;

As shown in the above image, the command-line client has created a new thread for MySQL query.

3) Example: display created database.

If you want to show created database, then use the below query. The following syntax helps to analyze the created database. Execute the below query in the command-line client.

mysql> show create database tutorial;
mysql> show create database tutorial;

As shown in the above image, the command-line client has created a new thread for MySQL query. You are ready to use other queries.

Show database in MySQL command-line client

MySQL helps in creating a database by using the queries. If you want to display the available database, then use the following query to show the entire database.

Syntax

The following syntax shows databases in the MySQL data management system. Execute the below syntax.

Show databases;

The database applies filters or conditions to display the required database. It operates the "WHERE" clause with the condition and "LIKE" operator with a pattern.

The syntax uses the LIKE operator with the required pattern.

Show databases LIKE pattern;

The syntax uses the "WHERE" clause with the required expression.

Show databases WHERE expression;

Examples of the show database.

1) Example: show available database.

Execute the below query to get the database.

mysql> show databases;

OUTPUT

mysql> show databases;

As shown in the above image, the available database has been displayed. The MySQL interface has displayed the entire list of the database.

2) Example: show database using LIKE operator.

Execute the below query to get the database. The following query uses the LIKE operator with the required pattern.

mysql> show databases like '%tut%';

OUTPUT

mysql> show databases like '%tut%';

As shown in the above image, the available database has been displayed. The MySQL interface has displayed the required list of the database.

3) Example: show database using WHERE clause.

Execute the below query to get the database. The following query uses the WHERE clause with the necessary condition.

mysql> show databases like '%tut%';

OUTPUT

mysql> show databases like '%tut%';

As shown in the above image, the available database has been displayed. The MySQL interface has displayed the required list of the database.

Use database in MySQL command-line client

MySQL is a database management system that can contain several databases. If the user wants some specific data from the database, then the MySQL command-line client will use the database. If the interface does not use a database, then you do not execute data operations. The database helps to operate tables and their data.

The command-line client executes a Create database, show database, and delete database queries before executing the "use database" query.

Syntax

The following syntax helps to utilize a required database.

Use database_name;

Examples of Use Database

1) Example: Use the existence database.

Execute the below query to get the new database.

mysql> use tutorial;

OUTPUT

mysql> use tutorial;

The above image displays the output after executing the "use database" query. The command-line client uses the database to manage the information.

2) Example: Use a non-existence database.

Execute the below query to get the new database.

mysql> use tutorials;

OUTPUT

mysql> use tutorials;

As shown in the above image, the command-line client resulted in an error.

Delete database in MySQL command-line client

MySQL interface helps in creating a new database as per the new project.  Sometimes, an unwanted database gets stored in MySQL. This database is never removed until the user deletes it.

You must remove unnecessary databases to maintain data and memory. The delete MySQL database syntax is below.

Syntax

The given syntax helps to remove known databases.

Drop database database_name;

The given syntax helps to remove unknown databases.

Drop database IF EXISTS database_name;

Examples of delete database

1) Example: Delete unwanted database.

Execute the below query to delete a database.

 mysql> drop database tutorials;
 Query OK, 0 rows affected (1.31 sec) 

OUTPUT

Execute the following MySQL query to get output.

mysql> show databases;
mysql> show databases;

As shown in the above image, the command-line client interface has removed the database to manage the memory size.

2) Example: Delete unwanted database with a statement.

Execute the below query to delete an existing database.

 mysql> drop database IF EXISTS tutorials;
 Query OK, 0 rows affected (0.18 sec) 

OUTPUT

Execute the following MySQL query to get output.

mysql> show databases;
mysql> show databases;

As shown in the above image, the command-line client interface has removed the database to manage the memory size.

3) Example: Delete a non-existence database with a statement.

Execute the below query to delete a non-existence database.

mysql> drop database tutorials;

You can refer to the below image.

mysql> drop database tutorials;

As shown in the above image, the command-line client interface cannot remove the non-existence database.

After performing the query, MySQL shows an ERROR or gives a warning. If you use the "IF EXISTS" statement, then the MySQL interface shows a warning. If you use a basic query, then the MySQL interface gives an error.

Copy database in MySQL command-line client

MySQL manages data of the database in table format. Every time you cannot work with the original database. The database must be safe and easy to operate. It stores multiple tables and creates interconnection with each other.

The database MySQL provides a feature to create a duplicate database. You can work on the clone database and then apply it to the original database. The cloning of a database helps to access a similar database for a different operation.

Syntax

The copy MySQL database syntax is given below.

CREATE DATABASE old-database-name_copy;

Examples of the copy database

1) Example: create a duplicate database.

Execute the following query.

 mysql> create database tutorial_copy;
 Query OK, 1 row affected (0.17 sec) 

Output

Execute the following query to display the same database.

mysql> show databases;
mysql> show databases;

As shown in the above image, the "tutorial" database is an original database, and "tutorial_copy" is a clone of the actual database.

MySQL Workbench Procedure

MySQL workbench interface works for automation. The workbench does not use a query or command. The database creates a framework of the schemas and tables.

If you want to create a database in MySQL, then you should create schemas. The workbench schemas can create, store, operate the table. MySQL workbench said "schemas," and MySQL command-line client said "database." Both works in a similar way, like storing multiple tables.

Show Schemas

MySQL workbench shows a database list on the front page. MySQL schemas display the left side column of the workbench. This column is called Navigator.

MySQL workbench

You can see the above output image. The database list has been displayed in the navigator columns. You can search database name or schema. Click on the "tutorial" database.

Click on the "tutorial" database

The workbench handles tables, views, stored Procedures, and Functions of the available schema.

Create Schemas

 To create new schemas in MySQL workbench, go to navigator columns of the home page and

click on the right button of the mouse. Next, click on the "create schema..." option.

create schema

 You can refer to the above image as it is the first step that creates the new schema. After this, you are required to give a name to the schema.

creates the new schema

You see the "Name:  mysql_data," you can place the required name of the schema. The mysql_data is the name of the new schema. Next, proceed by clicking on the Apply button. However, make sure that the schema name is written in the lowercase.

MySQL workbench interface executes the SQL statement such as "create database schema_name;" as per requirement.

create database schema_name

As you can see in the above image, a new schema is now available in the MySQL workbench interface.

Alter Schemas

You can change the schema name easily using the workbench interface. Click on the required schema name and press the right button of the mouse.

Alter Schemas

As shown in the above image, you will get many options to operate and manage schemas. After you change the charset/collations of the schemas, proceed by clicking on the "Finish" button.

Delete Schemas

The workbench interface creates and stores multiple schemas in the system. Some schemas are essential, but few schemas cannot be used. The multiple schemas store numerous data. This data affects the memory size of the MySQL management system.

Removing unwanted schemas is necessary to manage MySQL memory size. You can remove the entire schema using the below procedure. You can see the following image for reference.

Delete Schemas

Click on the respective schemas to delete. Right-click on the database or schema

Click on the respective schemas to delete. Right-click on the database or schema

Press the drop schema row of the palette.

Press the drop schema row of the palette.

Next, click on either Review SQL to verify the query or click on "Drop Now" to delete the unwanted data.

The above procedure helps to delete schema in the workbench interface. MySQL workbench works automatically as well as supports SQL queries.