Advance MYSQL tutorial

How to create a temporary table, clone table in MySQL. Indexes, Cursor, View, Regex is the more tricky part while playing with the MYSQL database.

In this tutorial, I am going to cover some advanced topics form MySQL databse. That will help you in MYSQL. below are the list of topics that we are going to cover in this MYSQL tutorial

Let’s Dive Mode Deep in MYSQL Database

  • Temporary table in MYSQL
  • Clone tables in MYSQL
  • Handle duplicates in MYSQL
  • Stored procedures in MYSQL
  • Indexes in MYSQL
  • Cursor in MYSQL
  • Views in MYSQL
  • Regex in MYSQL
  • Error handling in MYSQL
  • How to import database in MYSQL
  • How to export database in MYSQL
  • Triggers in MYSQL
  • Transactions in MYSQL
  • Full text search in MYSQL

How to create a Temporary table in MYSQL

It is useful in keeping temporary data, and it will be automatically deleted when the client sessions end. This feature is available in MySql after 3.23 versions, and in the older version of MySql you cannot find, temporary table, there you will get heap tables.

Example: – If you are using Temporary tables in PHP, then the table will be deleted automatically when the code executed successfully.

CREATE TEMPORARY TABLE student(
std_name VARCHAR(50) NOT NULL
, marks DECIMAL(12,2) NOT NULL DEFAULT 0.00
, avg_marks DECIMAL(7,2) NOT NULL DEFAULT 0.00
, total_marks INT UNSIGNED NOT NULL DEFAULT 0);
INSERT INTO student
(product_name, marks , avg_marks, total_marks)
VALUES
('rahul', 100.25, 90, 200);
SELECT * FROM student;

Clone tables in MYSQL

When you need an exact copy of any table and you do not find comfortable using SELECT or CREATE command, then you can use a clone table feature in MySql. This will let you create an exact copy of your table, in two steps.

  • SHOW CREATE TABLE will be used instead of CREATE TABLE which will specify source table structure and its index.
  • Change the statement, to the clone table created, and executes the statement.

Handle duplicates in MYSQL

This command is used to handle the table such that it does not produce duplicate values.

Example:

Step 1: Get complete structure about table.

mysql> SHOW CREATE TABLE tutorials_tbl \G;
*************************** 1. row ***************************

Table: tutorials_tbl
Create Table: CREATE TABLE `tutorials_tbl` (
`tutorial_id` int(11) NOT NULL auto_increment,
`tutorial_title` varchar(100) NOT NULL default '',
`tutorial_author` varchar(40) NOT NULL default '',
`submission_date` date default NULL,
PRIMARY KEY (`tutorial_id`),
UNIQUE KEY `AUTHOR_INDEX` (`tutorial_author`)
) TYPE=MyISAM
1 row in set (0.00 sec)

ERROR:
No query specified

Step 2: Rename this table and create another table.

mysql> CREATE TABLE `clone_tbl` (
-> `tutorial_id` int(11) NOT NULL auto_increment,
-> `tutorial_title` varchar(100) NOT NULL default '',
-> `tutorial_author` varchar(40) NOT NULL default '',
-> `submission_date` date default NULL,
-> PRIMARY KEY (`tutorial_id`),
-> UNIQUE KEY `AUTHOR_INDEX` (`tutorial_author`)
-> ) TYPE=MyISAM;
Query OK, 0 rows affected (1.80 sec)

Step 3: After executing step 2, you will create a clone table in your database. If you want to copy data from the old table then you can do it by using INSERT INTO… SELECT statement.

INSERT INTO clone_tbl (tutorial_id,
tutorial_title,
tutorial_author,
submission_date);
SELECT tutorial_id,tutorial_title,
tutorial_author,submission_date,
FROM tutorials_tbl;

OUTPUT

Query OK, 3 rows affected (0.07 sec)
Records: 3 Duplicates: 0 Warnings: 0
Finally, you will have an exact clone table as you wanted to have.

Stored procedures in MYSQL

It is the segments of SQL statements in the database catalog or dictionary. It can be invoked by triggers from other languages also, like PHP, Java, and Python. It is mostly used by community or enterprise, which is an open-source RDBMS.

Example:

DELIMITER //
CREATE PROCEDURE city
(IN con CHAR(20))
BEGIN
SELECT Name, HeadOfState FROM City
WHERE state= con;
END //
DELIMITER ;

Test that the stored procedure works as expected by typing the following into the MySQL command interpreter:

CALL country_hos('Europe');

Indexes in MYSQL

It is used in MySql to quickly find the number of rows, when WHERE conditions satisfied, and also used for eliminating rows from column.

In short, it makes the MySql access easy and easy retrieval of data from the database. When you are using JOIN condition it can help in retrieving rows from other tables also.

Cursor in MYSQL

The main feature of the cursor in MySql is that it is a read-only process only, and it cannot be scrollable, and it is sensitive.

Views in MYSQL

It is a virtual table which gets created by the query in term of joining one or more tables.
Example:

CREATE TABLE teacher (teacherID INT, salary INT, bonus INT );
INSERT INTO teacher VALUES (3, 5000,200);
CREATE VIEW V AS SELECT teacherID, salary, salary+ bonus AS Total_salary FROM teacher;
SELECT * FROM V;

Regex in MYSQL

Error handling in MYSQL

MySql error handling is used during stored procedure, which is used to handle the error or exceptions during the execution of statements. To declare the error DECLARE HANDLER statements will be useful in MySql.

Syntax:-

DECLARE action HANDLER FOR condition_value statement;

When the condition is matched during the program execution, the program will be executed otherwise it will exit the current code, based on the action provided in the statement.

CREATE PROCEDURE handlerdemo ()
BEGIN
DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET @x2 = 1;
SET @x = 1;
INSERT INTO test.t VALUES (1);
SET @x = 2;
INSERT INTO test.t VALUES (1);
SET @x = 3;
END;
//

CALL handlerdemo()//

How to import database in MYSQL

In MySql import it provides a command line to the interface to the client, to load the data in the file. It responds directly to the client to load the data file.

Syntax: 

Shell>mysqlimport [options] db_name textfile1 [textfile 2….]

When you use this syntax, you can load more than one data file at a time. For example, if you want to load a student. text then it will be invoked in the database Student.

How to export database in MYSQL

It is a backup method that is used to exports the result in the file which can be stored in the server host.
This command can be used to export the table in any text file. SELECT ..INTO OUTFILE can be used to export the table from the text file.

Steps to export MySQL database

  1. Choose the database.
  2. Select the Export option from the list.
  3. Select the file for back up, even user can select the completed folder for back up.
  4. Enable drop table or drop view in the table, options.
  5. Select save as option from the available menu.
  6. The final step is to save the file in your localhost, computer, or server, and by this, you will be able to back up your file in MYSQL.

Triggers in MYSQL

It is a database object which is related to the table of the database. It will be executed when you run the MySql statements, like INSERT, UPDATE or DELETE, which can be invoked before or after the event.

Example:

CREATE TABLE students(std_num INT, marks DECIMAL(10,2));

CREATE TRIGGER ins_sum BEFORE INSERT ON students
FOR EACH ROW SET @sum = @sum + NEW.marks;

Transactions in MYSQL

When the logical unit of work is of one or more SQL statements can be measured in transactions it is known as MYSQL transactions. It can be committed or rolled back in the query when required.

When the multiple changes are being done in MySQL statement, then the transactions which have been succeeded.

It will come under Committed transactions and those transactions which can be undone or not finished yet can be rolled back in MySql transactions.

It is the sequence of database, and until all the operations do not complete the transaction process will not end.
Properties of MySql transactions are consistency, durability, atomicity, isolation.

Isolation is the property which let transactions to be executed independently.

Full text search in MYSQL

This command in MySql helps user to search any file from the database, and it use BOOLEAN MODE modifier. This modifier tells sometimes that it has a special meaning, on the start or at the end.

It can be created for CHAR, VARCHAR, or TEXT columns. The major use of full text speech in MySql is to allow users to run full-text queries in MySql tables, on character based reading.

The only condition of using full text speech is that you have to create a full text index before running the full text queries in the table.

It can include one or more character based columns at a time.
With the help of CREATE TABLE statement, users can create FULL TEXT index, or if the user has missed it then they can add it later with the help of the ALTER TABLE or CREATE INDEX.