Category: mysql

MySQL – Stored Functions Table of content MySQL Stored Functions Calling Stored Function From Stored Procedure ”; Previous Next MySQL Stored Functions A Stored Function is a set of SQL statements that perform a specific operation and then return a single value. Similar to built-in functions in MySQL, a stored function can be called from within any MySQL statement. The MySQL CREATE FUNCTION statement is used to create both stored functions and user-defined functions. By default, a stored function is associated with the default database. In order to use the CREATE FUNCTION statement, the user must have the CREATE ROUTINE database privilege. Syntax Following is the syntax for creating a new stored function − CREATE FUNCTION function_name( parameters… ) RETURN datatype [characteristics] func_body; where, function_name: It is the name of the function that we are creating. The name must not be same as the MySQL built-in function names. parameters: These are the list of all parameters for the function. All the parameters are IN parameters by default. We cannot specify the IN, OUT or INOUT modifiers to the parameters. datatype: This is the datatype of the value returned by the function. characteristics: The CREATE FUNCTION statement will only be accepted if at least one of the characteristics (DETERMINISTIC, NO SQL, or READS SQL DATA) are specified in it”s declaration. fun_body: This contains set of MySQL statements that defines the behaviour of the function between the BEGIN and END commands. Example First, let us create a table with the name CUSTOMERS using the following query − CREATE TABLE CUSTOMERS ( ID INT NOT NULL, NAME VARCHAR (20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR (25), SALARY DECIMAL (18, 2), PRIMARY KEY(ID) ); Here, we are inserting rows into the CUSTOMERS table − INSERT INTO CUSTOMERS VALUES (1, ”Ramesh”, 32, ”Ahmedabad”, 2000.00), (2, ”Khilan”, 25, ”Delhi”, 1500.00), (3, ”Kaushik”, 23, ”Kota”, 2000.00), (4, ”Chaitali”, 25, ”Mumbai”, 6500.00), (5, ”Hardik”, 27, ”Bhopal”, 8500.00), (6, ”Komal”, 22, ”Hyderabad”, 4500.00), (7, ”Muffy”, 24, ”Indore”, 10000.00); The table is displayed as − ID NAME AGE ADDRESS SALARY 1 Ramesh 32 Ahmedabad 2000.00 2 Khilan 25 Delhi 1500.00 3 Kaushik 23 Kota 2000.00 4 Chaitali 25 Mumbai 6500.00 5 Hardik 27 Bhopal 8500.00 6 Komal 22 Hyderabad 4500.00 7 Muffy 24 Indore 10000.00 Creating a Function − With the following CREATE FUNCTION query, we are creating a function that returns the year of birth of the customers based on their AGE − DELIMITER $$ CREATE FUNCTION DATE_OF_BIRTH(AGE INT) RETURNS INT DETERMINISTIC BEGIN DECLARE currentdate DATE; SELECT CURDATE() INTO currentdate; RETURN year(currentdate)-AGE; END $$ DELIMITER ; Now, we are calling the DATE_OF_BIRTH function using the following query − SELECT ID, NAME, DATE_OF_BIRTH(AGE) AS ”YEAR_OF_BIRTH” FROM CUSTOMERS; Output The output for the above query is produced as given below − ID NAME YEAR_OF_BIRTH 1 Ramesh 1991 2 Khilan 1998 3 Kaushik 2000 4 Chaitali 1998 5 Hardik 1996 6 Komal 2001 7 Muffy 1999 Calling Stored Function From Stored Procedure In MySQL, we can call a stored function from a stored procedure. The following statement creates a stored procedure with the name StudentDetails() that calls the DATE_OF_BIRTH() stored function. DELIMITER $$ CREATE PROCEDURE CustomerDetails() BEGIN SELECT ID, NAME, DATE_OF_BIRTH(AGE) AS ”YEAR_OF_BIRTH” FROM CUSTOMERS; END $$ DELIMITER ; Here, we are calling the CustomerDetails() stored procedure using CALL keyword − CALL CustomerDetails(); Output The output for the above query is produced as given below − ID NAME YEAR_OF_BIRTH 1 Ramesh 1991 2 Khilan 1998 3 Kaushik 2000 4 Chaitali 1998 5 Hardik 1996 6 Komal 2001 7 Muffy 1999 Print Page Previous Next Advertisements ”;

MySQL – Drop Database Table of content MySQL DROP Database Statement Dropping a Database using mysqladmin Dropping Database Using a Client Program ”; Previous Next MySQL DROP Database Statement The DROP DATABASE statement in MySQL is used to delete a database along with all the data such as tables, views, indexes, stored procedures, and constraints. While deleting an existing database − It is important to make sure that we have to perform the backup of the database that we are going to delete because once the “DROP DATABASE” statement is executed, all the data and database objects in the database will be permanently deleted and cannot be recovered. It is also important to ensure that no other user or application is currently connected to the database that we want to delete. If we try to delete the database while others users are connected to it, then it can cause data corruption or other issues. In addition to these we need to make sure we have the necessary privileges before deleting any database using the DROP DATABASE statement. Syntax Following is the syntax to delete a database in MySQL − DROP DATABASE DatabaseName; Here, the “DatabaseName” is the name of the database that we want to delete. Example First of all, let us create a database named TUTORIALS into database system using the following query − CREATE DATABASE TUTORIALS; Once the database is created, execute the following query to verify whether it is created or not − SHOW DATABASES; As we can see the list of databases below, the TUTORIALS database has been created successfully − Database information_schema mysql performance_schema tutorials Now, let us delete the existing database <TUTORIALS> using the following DROP DATABASE statement − DROP DATABASE TUTORIALS; Output On executing the given query, the output is displayed as follows − Query OK, 0 rows affected (0.01 sec) Verification Once we have deleted the TUTORIALS database, we can verify whether it is deleted or not using the following query − SHOW DATABASES; As we can in the output, the database has been deleted successfully. Database information_schema mysql performance_schema Dropping a Database using mysqladmin You would need special privileges to create or to delete a MySQL database. So, assuming you have access to the root user, you can create any database using the mysql mysqladmin binary. Note: Be careful while deleting any database because you will lose your all the data available in your database. Example Here is an example to delete a database(TUTORIALS) created in the previous chapter − [root@host]# mysqladmin -u root -p drop TUTORIALS Enter password:****** This will give you a warning and it will ask you to confirm (Y/N) that you really want to delete this database or not. If you enter ”y”, the database will be deleted, else no − Dropping the database is potentially a very bad thing to do. Any data stored in the database will be destroyed. Do you really want to drop the ”TUTORIALS” database [y/N] y Output The TUTORIALS database has been deleted successfully. Database “TUTORIALS” dropped Dropping Database Using a Client Program Besides using MySQL queries to perform the DROP DATABASE operation, we can also use client programs like Node.js, PHP, Java, and Python to achieve the same result. Syntax Following are the syntaxes of this operation in various programming languages − PHP NodeJS Java Python To drop a database through a PHP program, we need to execute the ”DROP DATABASE” statement using the mysqli function query() as follows − $sql = “DROP DATABASE DatabaseName;”; $mysqli->query($sql); To drop a database through a Node.js program, we need to execute the ”DROP DATABASE” statement using the query() function of the mysql2 library as follows − sql = “DROP DATABASE DatabaseName;”; con.query(sql, function (err, result) { if (err) throw err; console.log(result); }); To drop a database through a Java program, we need to execute the ”DROP DATABASE” statement using the JDBC function executeUpdate() as follows − String sql = “DROP DATABASE DatabaseName;”; st.execute(sql); To drop a database through a Python program, we need to execute the ”DROP DATABASE” statement using the execute() function of the MySQL Connector/Python as follows − sql = “DROP DATABASE DatabaseName;” cursorObj.execute(sql) Example Following are the programs − PHP NodeJS Java Python $dbhost = ”localhost”; $dbuser = ”root”; $dbpass = ”root@123”; $mysqli = new mysqli($dbhost, $dbuser, $dbpass); if($mysqli->connect_errno ) { printf(“Connect failed: %s<br />”, $mysqli->connect_error); exit(); } printf(”Connected successfully.<br />”); if ($mysqli->query(“Drop DATABASE TUTORIALS”)) { printf(“Database dropped successfully.<br />”); } if ($mysqli->errno) { printf(“Could not drop database: %s<br />”, $mysqli->error); } $mysqli->close(); Output The output obtained is as follows − Connected successfully. Database dropped successfully. var mysql = require(”mysql2”); var con = mysql.createConnection({ host: “localhost”, user: “root”, password: “Nr5a0204@123″ });   //Connecting to MySQL   con.connect(function (err){   if (err) throw err;   console.log(“Connected!”);   console.log(“————————–“);   //Creating a Database   sql = “DROP DATABASE TUTORIALS”   con.query(sql, function(err){     if (err) throw err     console.log(“Database Dropped successfully…!”)   }); }); Output The output produced is as follows − Connected! ————————– Database Dropped successfully…! import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet;

MySQL – Change Column Type Table of content The MySQL ALTER TABLE Command Changing Column type Using Client Program ”; Previous Next In MySQL, users have the flexibility to modify the data type of a field. This is useful when you initially set the wrong data type for a column in a new database table, or when you need to adjust the storage size for the values in a particular column. You can modify or update the column type in a MySQL table, using the ALTER TABLE command. The MySQL ALTER TABLE Command The ALTER TABLE command is used to modify any data related to a database table. This data can either be records in the table or the table definition itself. Changing the data type of a column is one such modification that can be performed using the ALTER TABLE command. There are two methods to change a column”s data type: the ALTER TABLE… MODIFY command and the ALTER TABLE… CHANGE command. The MySQL ALTER TABLE… MODIFY Command You can use MODIFY method with the ALTER TABLE statement to change the column/field data type. Syntax Following is the basic syntax to change the column type using ALTER TABLE… MODIFY command − ALTER TABLE table_name MODIFY column_name new_datatype; Example Suppose you have created a table named ”test_table” using the following query − CREATE TABLE test_table ( field1 INT, field2 VARCHAR(100), field3 DATE, PRIMARY KEY(field1) ); To check if the table is successfully created or not, use the DESC command as shown below − DESC test_table; This will display the table structure as follows − Field Type Null Key Default Extra field1 int NO PRI NULL field2 varchar(100) YES NULL field3 date YES NULL Now, let us say you want to change the data type of ”field2” from VARCHAR(100) to TEXT for more simplicity. You can do this using the ALTER TABLE… MODIFY query as follows − ALTER TABLE test_table MODIFY field2 TEXT; Following is the output obtained − Query OK, 0 rows affected (0.04 sec) Records: 0 Duplicates: 0 Warnings: 0 To check if the column data type is successfully changed, use the DESC command as shown below − DESC test_table; This will display the modified table structure as follows − Field Type Null Key Default Extra field1 int NO PRI NULL field2 text YES NULL field3 date YES NULL The MySQL ALTER TABLE… CHANGE Command You can also use the CHANGE method with the ALTER TABLE statement to modify a column”s data type. This method is used to alter all the data related to table after it is created. The only difference between CHANGE and MODIFY methods is that the MODIFY method cannot rename a column whereas the CHANGE method can. Syntax Following is the basic syntax to change the column type using ALTER TABLE… CHANGE command − ALTER TABLE table_name CHANGE column_name column_name new_datatype; You must remember to specify the column name twice in the query whenever the CHANGE method is used. Example In this example, we are changing the datatype of ”field3” in the ”test_table” using the CHANGE method in ALTER TABLE command. Firstly, let us observe current definition of the table using DESC command − DESC test_table; Following is the table produced − Field Type Null Key Default Extra field1 int NO PRI NULL field2 text YES NULL field3 date YES NULL Now, using ALTER TABLE… CHANGE method, change the column type of ”field3” − ALTER TABLE test_table CHANGE field3 field3 VARCHAR(20); Output of the above code is as follows − Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0 You can verify whether the column data type is changed using the DESC command as shown below − DESC test_table; We get the following table as an output − Field Type Null Key Default Extra field1 int NO PRI NULL field2 text YES NULL field3 varchar(20) YES NULL Changing Column type Using Client Program We can also change column type using client program. Syntax PHP NodeJS Java Python To change column type through a PHP program, we need to execute the “ALTER TABLE” statement using the mysqli function query() as follows − $sql = “ALTER TABLE test_table MODIFY field2 TEXT”; $mysqli->query($sql); To change column type through a JavaScript program, we need to execute the “ALTER TABLE” statement using the query() function of mysql2 library as follows − sql = “ALTER TABLE test_table MODIFY field2 TEXT”; con.query(sql) To change column type through a Java program, we need to execute the “ALTER TABLE” statement using the JDBC function executeUpdate() as follows − String sql = “ALTER TABLE test_table MODIFY field2 TEXT”; statement.executeUpdate(sql);

MySQL – SubQuery Table of content Subquery with the SELECT Statement Subquery with the INSERT Statement Subquery with Comparison Operator Subquery with IN or NOT-IN Operator Subquery Using a Client Program ”; Previous Next The MySQL subquery, also known as an inner query or nested query, is a query inside another query. It allows you to retrieve data from one or more tables based on the results of another query. Subqueries can be used in various parts of SQL statements, including SELECT, INSERT, UPDATE, and DELETE. Subquery with the SELECT Statement A subquery within a SELECT statement is used to filter the results of the main query based on the values retrieved from the subquery. Syntax Following is the basic syntax of a subquery within a SELECT statement − SELECT column1, column2, … FROM table1 WHERE columnN operator (SELECT column_name FROM table2 WHERE condition); Example First, let us create a table with the name CUSTOMERS using the following query − CREATE TABLE CUSTOMERS( ID INT NOT NULL, NAME VARCHAR(20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(25) NOT NULL, SALARY DECIMAL(18, 2), PRIMARY KEY(ID) ); Now, let us insert values into the above-created table using the INSERT statement as shown below − INSERT INTO CUSTOMERS VALUES (1, ”Ramesh”, 32, ”Ahmedabad”, 2000.00 ), (2, ”Khilan”, 25, ”Delhi”, 1500.00 ), (3, ”kaushik”, 23, ”Kota”, 2000.00 ), (4, ”Chaitali”, 25, ”Mumbai”, 6500.00 ), (5, ”Hardik”, 27, ”Bhopal”, 8500.00 ), (6, ”Komal”, 22, ”Hyderabad”, 4500.00 ), (7, ”Muffy”, 24, ”Indore”, 10000.00 ); The CUSTOMERS table displayed is as shown below − ID NAME AGE ADDRESS SALARY 1 Ramesh 32 Ahmedabad 2000.00 2 Khilan 25 Delhi 1500.00 3 Kaushik 23 Kota 2000.00 4 Chaitali 25 Mumbai 6500.00 5 Hardik 27 Bhopal 8500.00 6 Komal 22 Hyderabad 4500.00 7 Muffy 24 Indore 10000.00 The following query retrieves the salaries of all customers from the CUSTOMERS table whose ID”s match with the ID”s in the same table − SELECT SALARY FROM CUSTOMERS WHERE ID IN (SELECT ID FROM CUSTOMERS); Output The output for the query above is produced as given below − SALARY 2000.00 1500.00 2000.00 6500.00 8500.00 4500.00 10000.00 Subquery with the INSERT Statement We can also use the subqueries with the INSERT statements in MySQL. The INSERT statement will use the data returned from the subquery to insert into another table. Syntax Following is the basic syntax of a subquery within an INSERT statement − INSERT INTO target_table (column1, column2, …) SELECT source_column1, source_column2, … FROM source_table WHERE condition; Example Before performing the subqueries with INSERT statement, let us create a table named “CUSTOMERS_BKP” with a similar structure as CUSTOMERS table − CREATE TABLE CUSTOMERS_BKP( ID INT NOT NULL, NAME VARCHAR(20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(25) NOT NULL, SALARY DECIMAL(18, 2), PRIMARY KEY(ID) ); Now, let us insert all records from CUSTOMERS table into the CUSTOMERS_BKP table using the following query − INSERT INTO CUSTOMERS_BKP SELECT * FROM CUSTOMERS WHERE ID IN (SELECT ID FROM CUSTOMERS); Output The records of CUSTOMERS table has successfully inserted into CUSTOMERS_BKP table − Query OK, 7 rows affected (0.01 sec) Records: 7 Duplicates: 0 Warnings: 0 Verification Let us verify whether the CUSTOMERS_BKP table have records using the following SELECT statement − SELECT * FROM CUSTOMERS_BKP; As we can see in the table below, all the records in CUSTOMERS table is inserted into CUSTOMERS_BKP table − ID NAME AGE ADDRESS SALARY 1 Ramesh 32 Ahmedabad 2000.00 2 Khilan 25 Delhi 1500.00 3 Kaushik 23 Kota 2000.00 4 Chaitali 25 Mumbai 6500.00 5 Hardik 27 Bhopal 8500.00 6 Komal 22 Hyderabad 4500.00 7 Muffy 24 Indore 10000.00 Subquery with Comparison Operator The MySQL Subquery with comparison operator allows us to use a query inside another query and compare its result with the outer query using comparison operators. Syntax Following is the basic syntax of a subquery with comparison operators − SELECT column_name [, column_name ] FROM table1 [, table2 ] WHERE column_name OPERATOR (SELECT column_name [, column_name ] FROM table1 [, table2 ] [WHERE] …..) Example The following query retrieves all the CUSTOMERS from the table CUSTOMERS_BKP with an AGE greater than 23 and returns their IDs. SELECT * FROM CUSTOMERS_BKP WHERE ID IN (SELECT ID FROM CUSTOMERS_BKP WHERE AGE > 23); Output The output for the query above is produced as given below − ID NAME AGE ADDRESS SALARY 2 Khilan 25 Delhi 1500.00 4 Chaitali 25 Mumbai 6500.00 7 Muffy 24 Indore 10000.00 Subquery with IN or NOT-IN Operator The MySQL

MySQL – Create Database Table of content MySQL CREATE Database Statement CREATE Database with IF NOT EXISTS clause Create Database Using mysqladmin Creating Database Using a Client Program ”; Previous Next After establishing connection with MySQL, to manipulate data in it you need to connect to a database. You can connect to an existing database or, create your own. You would need special privileges to create or to delete a MySQL database. So, if you have access to the root user, you can create any database using the MySQL CREATE DATABASE statement. MySQL CREATE Database Statement The CREATE DATABASE statement is a DDL (Data Definition Language) statement used to create a new database in MySQL RDBMS. If you are creating your database on Linux or Unix, then database names are case-sensitive, even though keywords SQL are case-insensitive. If you are working on Windows then this restriction does not apply. Syntax Following is the syntax to create a database in MySQL − CREATE DATABASE DatabaseName; Where, the “DatabaseName” is just a placeholder representing the name of the database that we want to create. Example Let us create a database TUTORIALS in MySQl using the CREATE DATABASE statement as follows − CREATE DATABASE TUTORIALS; Make sure you have the necessary privilege before creating any database. Verification Once the database TUTORIALS is created, we can check it in the list of databases using the SHOW statement as shown below − SHOW DATABASES; Following are the list of databases present in the server − Database information_schema mysql performance_schema tutorials CREATE Database with IF NOT EXISTS clause If you try to create a database with an existing name an error will be generated. Suppose there is an existing database in MySQL with the name mydb and if we try to create another database with the same name as − CREATE DATABASE myDatabase An error will be generated as shown below − ERROR 1007 (HY000): Can”t create database ”mydb”; database exists If you use the IF NOT EXISTS clause along with the CREATE statement as shown below a new database will be created and if a database with the given name, already exists the query will be ignored. CREATE DATABASE IF NOT EXISTS myDatabase Create Database Using mysqladmin You would need special privileges to create or to delete a MySQL database. So assuming you have access to the root user, you can create any database using the mysql mysqladmin binary. Example Here is a simple example to create a database named TUTORIALS using mysqladmin − [root@host]# mysqladmin -u root -p create TUTORIALS Enter password:****** This will create a MySQL database called TUTORIALS. Creating Database Using a Client Program Besides creating a database in MySQL RDBMS with a MySQL query, you can also use a client program in programming languages such as Node.js, PHP, Java, and Python to achieve the same result. Syntax Following are the syntaxes of this operation in various programming languages − PHP NodeJS Java Python To create a database in MySQL RDBMS through a PHP program, we need to execute the ”CREATE DATABASE” statement using the mysqli function named query() as shown below − $sql = “CREATE DATABASE DatabaseName”; $mysqli->query($sql); To create a database in MySQL RDBMS through a Node.js program, we need to execute the ”CREATE DATABASE” statement using the query() function of the mysql2 library as follows − sql = “CREATE DATABASE DatabaseName”; con.query(sql, function (err, result) { if (err) throw err; console.log(result); }); To create a database in MySQL RDBMS through a Java program, we need to execute the ”CREATE DATABASE” statement using the JDBC function executeUpdate() as follows − String sql = “CREATE DATABASE DatabaseName”; st.executeUpdate(sql); To create a database in MySQL RDBMS through a Python program, we need to execute the ”CREATE DATABASE” statement using the execute() function of the MySQL Connector/Python as follows − sql = “CREATE DATABASE DatabaseName” cursorObj.execute(sql) Example Following are the programs − PHP NodeJS Java Python $dbhost = ”localhost”; $dbuser = ”root”; $dbpass = ”root@123”; $mysqli = new mysqli($dbhost, $dbuser, $dbpass); if($mysqli->connect_errno ) { printf(“Connect failed: %s<br />”, $mysqli->connect_error); exit(); } printf(”Connected successfully.<br />”); if ($mysqli->query(“CREATE DATABASE TUTORIALS”)) { printf(“Database created successfully.<br />”); } if ($mysqli->errno) { printf(“Could not create database: %s<br />”, $mysqli->error); } $mysqli->close(); Output The output obtained is as follows − Connected successfully. Database created successfully. var mysql = require(”mysql2”); var con = mysql.createConnection({ host: “localhost”, user: “root”, password: “Nr5a0204@123″ }); //Connecting to MySQL con.connect(function (err){ if (err) throw err; console.log(“Connected!”); console.log(“————————–“); //Creating a Database sql = “create database TUTORIALS” con.query(sql, function(err){ if (err) throw err console.log(“Database created successfully…”) }); }); Output The output produced is as follows − Connected! ————————– Database created successfully… import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.Statement; public class createDatabase { public static void main(String[] args) { String url = “jdbc:mysql://localhost:3306/”; String user = “root”; String password = “password”; ResultSet rs; try { Class.forName(“com.mysql.cj.jdbc.Driver”); Connection con = DriverManager.getConnection(url, user, password); Statement st = con.createStatement(); //System.out.println(“Connected successfully…!”); String sql = “CREATE DATABASE TUTORIALS”; st.execute(sql); System.out.println(“Database created successfully…!”); }catch(Exception e) { e.printStackTrace(); } } } Output The output obtained is as shown below − Database created successfully…! import mysql.connector # creating the connection object

MySQL – Check Constraint Table of content The MySQL Check Constraint Check Constraint with a Trigger Adding Check Constraint on Single Column Adding Check Constraint on Multiple Columns Adding Check Constraint on an Existing Table Dropping Check Constraint Check-Constraints Using a Client Program ”; Previous Next The MySQL Check Constraint The MySQL Check Constraint is a condition that can be applied to a column to ensure that the inserted or updated data in that column meets the specified condition. The database rejects the operation if the condition is not met to maintain data integrity. Check Constraint with a Trigger A trigger in MySQL is used to automatically execute a set of SQL statements in response to specific events in the database, such as an INSERT, UPDATE, or DELETE operation. A check constraint with a trigger allows us to perform actions automatically based on data changes. Example Assume we have created a table with name CUSTOMERS in the MySQL database using CREATE TABLE statement as shown below − CREATE TABLE CUSTOMERS ( ID INT NOT NULL, NAME VARCHAR (20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR (25), SALARY DECIMAL (18, 2), PRIMARY KEY (ID) ); Following query inserts values into CUSTOMERS table using the INSERT statement − INSERT INTO CUSTOMERS VALUES (1, ”Ramesh”, 32, ”Ahmedabad”, 2000.00), (2, ”Khilan”, 25, ”Delhi”, 1500.00), (3, ”Kaushik”, 23, ”Kota”, 2000.00), (4, ”Chaitali”, 25, ”Mumbai”, 6500.00), (5, ”Hardik”, 27, ”Bhopal”, 8500.00), (6, ”Komal”, 22, ”Hyderabad”, 4500.00), (7, ”Muffy”, 24, ”Indore”, 10000.00); The table obtained is as shown below − ID NAME AGE ADDRESS SALARY 1 Ramesh 32 Ahmedabad 2000.00 2 Khilan 25 Delhi 1500.00 3 Kaushik 23 Kota 2000.00 4 Chaitali 25 Mumbai 6500.00 5 Hardik 27 Bhopal 8500.00 6 Komal 22 Hyderabad 4500.00 7 Muffy 24 Indore 10000.00 Now, we will add a check constraint to ensure that the age of customers in the CUSTOMERS table should be greater than or equal to 18. Additionally, we will create a trigger that, when an attempt is made to insert a record with an age less than 18, it will raise an error and prevent the insertion − — Creating a Trigger DELIMITER // CREATE TRIGGER check_age_trigger BEFORE INSERT ON CUSTOMERS FOR EACH ROW BEGIN IF NEW.AGE < 18 THEN SIGNAL SQLSTATE ”45000” SET MESSAGE_TEXT = ”Age must be 18 or older”; END IF; END; // DELIMITER ; — Adding a Check Constraint ALTER TABLE CUSTOMERS ADD CONSTRAINT check_age_constraint CHECK (AGE >= 18); Output We get the output as shown below − Query OK, 7 rows affected (0.05 sec) Records: 7 Duplicates: 0 Warnings: 0 Adding Check Constraint on Single Column We can apply a check constraint on a column by specifying the check constraint after the column name at the time of table creation. Syntax Following is the syntax to specify the check constraint on column − CREATE TABLE table_name ( column1 datatype(size), column datatype(size) constraint constraintName CHECK Check(columnName condition value),…, column datatype (size) ); Example In this example, we are creating a table named EMPLOYEES and specifying a column-level check constraint on one column − CREATE TABLE EMPLOYEES( EID INT NOT NULL, NAME VARCHAR(40), AGE INT NOT NULL CHECK(AGE>=20), CITY VARCHAR(30), C_Phone VARCHAR(12) NOT NULL UNIQUE ); We can verify if the check constraint is working correctly by inserting a value into the EMPLOYEES table which does not satisfy the condition − INSERT INTO EMPLOYEES VALUES (1, ”John”, 19, ”New York”, ”09182829109”); Output The output obtained is as follows − ERROR 3819 (HY000): Check constraint ”employees_chk_1” is violated. Adding Check Constraint on Multiple Columns We can add check constraint on multiple columns of a table by specifying the constraints for each column after the column name. Example In the following example, we are creating a table named STUDENTS and specifying a column-level check constraint on multiple columns (AGE and FEE) − CREATE TABLE STUDENTS( SID INT NOT NULL, NAME VARCHAR(20), AGE INT NOT NULL CHECK(AGE<=24), CITY VARCHAR(30), FEE NUMERIC NOT NULL CHECK(FEE>=15000) ); Now, we can insert records, but if we attempt to insert a record that violates these constraints, the database will reject it. Here, we are inserting a valid record − INSERT INTO STUDENTS VALUES (001, ”Robert”, 21, ”LA”, 17000); We can see in the output below that the insertion is successful because the age is within the allowed range, and the fee meets the specified condition − Query OK, 1 row affected (0.01 sec) In here, we are attempting to insert a record violating constraints − INSERT INTO STUDENTS VALUES (002, ”James”, 25, ”Barcelona”, 10000); We can see that the insertion fails since the age exceeds 24, violating the constraint. ERROR 3819 (HY000): Check constraint ”students_chk_1” is violated. Adding Check Constraint on an Existing Table We can also add a check constraint on an existing table in MySQL by using the ALTER statement. We must ensure that the constraint satisfy for the existing records in the table. Syntax ALTER TABLE table_name ADD CONSTRAINT ConstraintName CHECK(ColumnName condition Value); Example In the following example, we are adding a check constraint to the AGE column of the CUSTOMERS table created above − ALTER

MySQL – Storage Engines Table of content The MySQL Storage Engines Common Storage Engines SHOW ENGINES Statement Setting a Storage Engine Changing Default Storage Engine Altering Storage Engine Storage Engines Using a Client Program ”; Previous Next The MySQL Storage Engines As we already know, a MySQL database is used to store data in the form of rows and columns. The MySQL storage engine is a component that is used to handle the SQL operations performed to manage this data. They work with simple tasks like creating a table, renaming it, updating or deleting it; which is necessary to increase the database performance. There are two categories of storage engines used: transactional engines and non-transactional engines. Many common storage engines fall into either type of these categories. In MySQL, however, the default storage engine is InnoDB. Common Storage Engines Various common storage engines that are used to work with MySQL are as follows − InnoDB Storage Engine ACID Compliant − InnoDB is the default storage engine in MySQL 5.5 and later versions. It is a transactional database engine, ensuring ACID compliance, which means it supports operations like commit and rollback. Crash-Recovery − InnoDB offers crash-recovery capabilities to protect user data. Row-Level Locking − It supports row-level locking, which enhances multi-user concurrency and performance. Referential Integrity − It also enforces FOREIGN KEY referential-integrity constraints. ISAM Storage Engine Deprecated − ISAM, which stands for Indexed Sequential Access Method, was supported by earlier MySQL versions but has been deprecated and removed from recent versions. Limited Size − ISAM tables were limited to a size of 4GB. MyISAM Storage Engine Portability − MyISAM is designed for portability, addressing ISAM”s non-portable nature. Performance − It offers faster performance compared to ISAM and was the default storage engine before MySQL 5.x. Memory Efficiency − MyISAM tables have a small memory footprint, making them suitable for read-only or read-mostly workloads. MERGE Storage Engine Logical Combination − MERGE table enables a MySQL developer to logically combine multiple identical MyISAM tables and reference them as one object. Limited Operations − Only INSERT, SELECT, DELETE, and UPDATE operations are allowed on MERGE tables. If DROP query is used, only the storage engine specification gets reset while the table remains unchanged. MEMORY Storage Engine In-Memory Storage − MEMORY tables store data entirely in RAM, optimizing access speed for quick lookups. Hash Indexes − It uses hash indexes for faster data retrieval. Decreasing Use − Its use cases are decreasing; other engines, like InnoDB”s buffer pool memory area provide better memory management. CSV Storage Engine CSV Format − CSV tables are text files with comma-separated values, useful for data exchange with scripts and applications. No Indexing − They are not indexed, and generally used during data import or export alongside InnoDB tables. NDBCLUSTER Storage Engine Clustering − NDBCLUSTER, also known as NDB, is a clustered database engine suitable for applications that require the highest possible degree of uptime and availability. ARCHIVE Storage Engine Historical Data − ARCHIVE tables are ideal for storing and retrieving large amounts of historical, archived, or secure data. The ARCHIVE storage engines support supports non-indexed tables BLACKHOLE Storage Engine Data Discard − BLACKHOLE tables accept data but do not store it, always returning an empty set. Usage − Used in replication configurations, where DML statements are sent to replica servers, but the source server does not keep its own copy of the data. FEDERATED Storage Engine Distributed Databases − FEDERATED allows linking separate MySQL servers to create a logical database from multiple physical servers, useful in distributed environments. EXAMPLE Storage Engine Development Tool − EXAMPLE is a tool in the MySQL source code that serves as an example for developers to start writing new storage engines. You can create tables with this engine, but it doesn”t store or retrieve data. Even though there are so many storage engines that can be used with databases, there is no such thing called a perfect storage engine. In some situations, one storage engine could be a better fit to use whereas in other situations, other engines perform better. Therefore, one must carefully choose what Storage engine to use while working in certain environments. To choose an engine, you can use the SHOW ENGINES statement. SHOW ENGINES Statement The SHOW ENGINES statement in MySQL will list out all the storage engines. It can be taken into consideration while choosing an engine that are supported by the database and are easy to work with. Syntax Following is the syntax of the SHOW ENGINES statement − SHOW ENGINESG where, the ”G” delimiter is used to vertically align the result-set obtained from executing this statement. Example Let us observe the result-set obtained by executing the SHOW ENGINES statement in a MySQL database using the following query − SHOW ENGINESG Output Following is the result-set obtained. Here, you can check which storage engines are supported by the MySQL database and where they can be best used − *************************** 1. row ************************ Engine: MEMORY Support: YES Comment: Hash based, stored in memory, useful for temporary tables Transactions: NO XA: NO Savepoints: NO *************************** 2. row ************************ Engine: MRG_MYISAM Support: YES Comment: Collection of identical MyISAM tables Transactions: NO XA: NO Savepoints: NO *************************** 3. row ************************ Engine: CSV Support: YES Comment: CSV storage engine Transactions: NO XA: NO Savepoints: NO *************************** 4. row ************************ Engine: FEDERATED Support: NO Comment: Federated MySQL storage engine Transactions: NULL XA: NULL Savepoints:

MySQL – Upsert Table of content The MySQL UPSERT Operation UPSERT Using INSERT IGNORE UPSERT Using REPLACE UPSERT Using INSERT with ON DUPLICATE KEY UPDATE ”; Previous Next The MySQL UPSERT Operation The MySQL UPSERT operation combines INSERT and UPDATE into a single statement, allowing you to insert a new row into a table or update an existing row if it already exists. We can understand in the name (UPSERT) itself, where UP stands for UPDATE and SERT stands for INSERT. This tutorial covers three common methods to perform UPSERT operations in MySQL: INSERT IGNORE, REPLACE, and INSERT with ON DUPLICATE KEY UPDATE. UPSERT Using INSERT IGNORE The INSERT IGNORE statement in MySQL allows you to insert a new record into a table. If a record with the same primary key already exists, it ignores the error and doesn”t insert the new record. Example First, let us create a table with the name COURSES using the following query − CREATE TABLE COURSES( ID int, COURSE varchar(50) primary key, COST int ); Here, we are inserting records into the COURSES table − INSERT INTO COURSES VALUES (1, “HTML”, 3000), (2, “CSS”, 4000), (3, “JavaScript”, 6000), (4, “Node.js”, 10000), (5, “React.js”, 12000), (6, “Angular”, 8000), (7, “Php”, 9000); The COURSES table obtained is as follows − ID COURSE COST 6 Angular 8000 2 CSS 4000 1 HTML 3000 3 JavaScript 6000 4 Node.js 10000 7 Php 9000 5 React.js 12000 Now, we attempt to insert a duplicate record using the INSERT INTO statement in the following query − INSERT INTO COURSES VALUES (6, ”Angular”, 9000); This results in an error because a duplicate record cannot be inserted − ERROR 1062 (23000): Duplicate entry ”Angular” for key ”courses.PRIMARY” Using INSERT IGNORE − Now, let us perform the same operation using INSERT IGNORE statement − INSERT IGNORE INTO COURSES VALUES (6, ”Angular”, 9000); Output As we can see in the output below, the INSERT IGNORE statement ignores the error − Query OK, 0 rows affected, 1 warning (0.00 sec) Verification We can verify the COURSES table to see that the error was ignored using the following SELECT query − SELECT * FROM COURSES; The table obtained is as follows − ID COURSE COST 6 Angular 8000 2 CSS 4000 1 HTML 3000 3 JavaScript 6000 4 Node.js 10000 7 Php 9000 5 React.js 12000 UPSERT Using REPLACE The MySQL REPLACE statement first attempts to delete the existing row if it exists and then inserts the new row with the same primary key. If the row does not exist, it simply inserts the new row. Example Let us replace or update a row in the COURSES table. If a row with COURSE “Angular” already exists, it will update its values for ID and COST with the new values provided. Else, a new row will be inserted with the specified values in the query − REPLACE INTO COURSES VALUES (6, ”Angular”, 9000); Output The output for the query above produced is as given below − Query OK, 2 rows affected (0.01 sec) Verification Now, let us verify the COURSES table using the following SELECT query − SELECT * FROM COURSES; We can see in the following table, the REPLACE statement added a new row after deleting the duplicate row − ID COURSE COST 6 Angular 9000 2 CSS 4000 1 HTML 3000 3 JavaScript 6000 4 Node.js 10000 7 Php 9000 5 React.js 12000 UPSERT Using INSERT with ON DUPLICATE KEY UPDATE The INSERT … ON DUPLICATE KEY UPDATE statement in MySQL attempts to insert a new row. If the row already exists, it updates the existing row with the new values specified in the statement. Example Here, we are updating the duplicate record using the following query − INSERT INTO COURSES VALUES (6, ”Angular”, 9000) ON DUPLICATE KEY UPDATE ID = 6, COURSE = ”Angular”, COST = 20000; Output As we can see in the output below, no error is generated and the duplicate row gets updated. Query OK, 2 rows affected (0.01 sec) Verification Let us verify the COURSES table using the following SELECT query − SELECT * FROM COURSES; As we can see the table below, the INSERT INTO… ON DUPLICATE KEY UPDATE statement updated the duplicate record − ID COURSE COST 6 Angular 20000 2 CSS 4000 1 HTML 3000 3 JavaScript 6000 4 Node.js 10000 7 Php 9000 5 React.js 12000 Print Page Previous Next

MySQL – Workbench Table of content Functionalities of MySQL Workbench MySQL Workbench Editions MySQL Workbench Administration Tool Create Database in MySQL Workbench Drop Database in MySQL Workbench MySQL Workbench Create, Alter, and Drop Table MySQL Workbench Insert and Delete Table Rows MySQL Workbench Export and Import Database ”; Previous Next The MySQL workbench is a graphical tool for working with MySQL servers and databases. It is developed and maintained by Oracle. This application includes various features such as data modelling, data migration, SQL development, server administration, database backup, database recovery and many more. MySQL Workbench supports the MySQL versions 5.7 and above. The versions of MySQL prior to version 5.7 are deprecated and they are incompatible with MySQL workbench. Therefore, we need to ensure that they should be upgraded before we make a connection. MySQL Workbench is a recommended application for database developers and administrators. We can download this application on Windows, macOS, and Linux operating systems. Functionalities of MySQL Workbench Following are the five main functionalities of MySQL workbench − SQL Development − This functionality allows to create and manage connections to database servers. SQL queries can be executed on the database connections using the built-in SQL editor in MySQL workbench. Data Modelling (Design) − This functionality allows to create models of our database schema graphically, reverse and forward engineer between a schema and a live database, and edits all aspects of the database using the comprehensive table. The table editor provides facilities to edit Tables, Columns, Indexes, Triggers, Partitioning, Options, Inserts, Privileges, Routines and Views. Server Administration − It allows us to administer the MySQL sever instances by administering users, viewing database health, performing backup and recovery, inspecting audit data, and monitoring the MySQL server performance. Data Migration − It allows us to migrate from the Microsoft SQL server, Microsoft Access, Sybase ASE, SQLite, SQL Anywhere PostreSQL, and other RDBMS tables, objects and data to MySQL. Migration also allows us to migrate from prior versions of MySQL to the latest releases. MySQL Enterprise support − This functionality provides the support for Enterprise products such as MySQL Enterprise backup, MySQL Firewall and MySQL Audit. MySQL Workbench Editions The MySQL Workbench is offered in three editions. The same is described below − MySQL Workbench Community Edition – Open Source (GPL License) MySQL Workbench Standard Edition – Commercial MySQL Workbench Enterprise Edition – Commercial Community Edition This is an open-source and freely downloadable version of the database system. It comes under the GPL (General public license) and it is supported by a large community of developers. Standard Edition This is the commercial edition that gives the capability to deliver high-performance and scalable OLT (online transaction processing) applications. Enterprise Edition The Enterprise edition includes a set of advanced features, management tools, and technical support to achieve the highest scalability, security, uptime and reliability. This edition will reduce the risk, cost, complexity in the development, deployment, and managing MySQL applications. Comparison Chart The following is the comparison chart of the above discussed functionalities − Functionality Community Edition Standard Edition Enterprise Edition Visual SQL Development Yes Yes Yes Visual Database Administration Yes Yes Yes Performance Tuning Yes Yes Yes User and Session Management Yes Yes Yes Connection Management Yes Yes Yes Object Management Yes Yes Yes Data Management Yes Yes Yes Visual Data Modelling Yes Yes Yes Reverse Engineering Yes Yes Yes Forward Engineering Yes Yes Yes Schema Synchronization Yes Yes Yes Schema & Model Validation No Yes Yes DBDoc No Yes Yes GUI for MySQL Enterprise Backup No Yes Yes GUI for MySQL Enterprise Audit No Yes Yes GUI for MySQL Enterprise Firewall No Yes Yes Scripting & Plugins No Yes Yes Database Migration No Yes Yes MySQL Workbench Administration Tool The administration tool in MySQL workbench plays a vital role in securing the data. Following are some administration tools provided by MySQL workbench − User Administration This tool allows to create, modify and delete the user related accounts. Using this we can manage the user”s privileges and permissions. Server Configuration It allows us to configure the server paramerters. It shows various details about the sever and status variable, number of threads, buffer allocation size, fine-tuning for optimal performance, etc. Database Backup and Restorations This tools is used for importing and exporting the MySQL dump files. These dump files contains the SQL script for table creation, view creation, stored procedure creation etc. Server Logs This tool shows log information for the MySQL server by each connection tab. For every tab connection, it includes an additional tab for the general error logs. Performance Dashboard This tab provides real-time statistical view and analysis of server performance such as CPU usage, memory usage, query execution time, etc. Create Database in MySQL Workbench We can create a database in MySQL Workbench, without explicitly using SQL statements. Following steps show how to create a database in MySQl Workbench − Step 1 − Open the MySQL workbench

MySQL – Character Set Table of content The MySQL Character Set The MySQL Show Character-Set The MySQL Set Character-set ”; Previous Next The MySQL Character Set The MySQL Character set is used to determine the set of permissible characters within a string. It allows for the storage of data in various character encodings. By default, MySQL uses the “utf8mb4” character set. These character sets provides several functionalities − Storage Variety − MySQL allows the storage of strings in various character sets. String Comparison − Collations help in comparing strings based on the chosen character set. Mixed Character Sets − It is possible to combine strings with different character sets or collations within the same server, database, or table. Specifying Character Set and Collation − You can define the character set and collation at different levels of the database structure. The MySQL Show Character-Set You can use the MySQL SHOW CHARACTER SET statement to view the list of all the available character sets. Syntax Following is the syntax of the SHOW CHARACTER SET statement − SHOW CHARACTER SET [LIKE ”pattern” | WHERE expr] Example The following query retrieves a list of available character sets, along with their descriptions, default collations, and maximum lengths in a MySQL database − SHOW CHARACTER SET; Output Following is the output obtained − Charset Description Default collation Maxlen armscii8 ARMSCII-8 Armenian armscii8_general_ci 1 ascii US ASCII ascii_general_ci 1 big5 Big5 Traditional Chinese big5_chinese_ci 2 binary Binary pseudo charset binary 1 cp1250 Windows Central European cp1250_general_ci 1 cp1251 Windows Cyrillic cp1251_general_ci 1 cp1256 Windows Arabic cp1256_general_ci 1 cp1257 Windows Baltic cp1257_general_ci 1 cp850 DOS West European cp850_general_ci 1 cp852 DOS Central European cp852_general_ci 1 cp866 DOS Russian cp866_general_ci 1 cp932 SJIS for Windows Japanese cp932_japanese_ci 2 dec8 DEC West European dec8_swedish_ci 1 eucjpms UJIS for Windows Japanese eucjpms_japanese_ci 3 euckr EUC-KR Korean euckr_korean_ci 2 gb18030 China National Standard GB18030 gb18030_chinese_ci 4 gb2312 GB2312 Simplified Chinese gb2312_chinese_ci 2 gbk GBK Simplified Chinese gbk_chinese_ci 2 geostd8 GEOSTD8 Georgian geostd8_general_ci 1 greek ISO 8859-7 Greek greek_general_ci 1 hebrew ISO 8859-8 Hebrew hebrew_general_ci 1 hp8 HP West European hp8_english_ci 1 keybcs2 DOS Kamenicky Czech-Slovak keybcs2_general_ci 1 koi8r KOI8-R Relcom Russian koi8r_general_ci 1 koi8u KOI8-U Ukrainian koi8u_general_ci 1 latin1 cp1252 West European latin1_swedish_ci 1 latin2 ISO 8859-2 Central European latin2_general_ci 1 latin5 ISO 8859-9 Turkish latin5_turkish_ci 1 latin7 ISO 8859-13 Baltic latin7_general_ci 1 macce Mac Central European macce_general_ci 1 macroman Mac West European macroman_general_ci 1 sjis Shift-JIS Japanese sjis_japanese_ci 2 swe7 7bit Swedish swe7_swedish_ci 1 tis620 TIS620 Thai tis620_thai_ci 1 ucs2 UCS-2 Unicode ucs2_general_ci 2 ujis EUC-JP Japanese ujis_japanese_ci 3 utf16 UTF-16 Unicode utf16_general_ci 4 utf16le UTF-16LE Unicode utf16le_general_ci 4 utf32 UTF-32 Unicode utf32_general_ci 4 utf8mb3 UTF-8 Unicode utf8mb3_general_ci 3 utf8mb4 UTF-8 Unicode utf8mb4_0900_ai_ci 4 The MySQL Set Character-set The MySQL SET CHARACTER SET Statement is used to assign a value to the character set attribute. It maps all the strings between the server and the current client with the specified mapping set. This statement changes values of the “character_set_client” and “character_set_results” variables. Syntax Following is the syntax of the MySQL SET CHARACTER SET Statement − SET {CHARACTER SET | CHARSET} {”charset_name” | DEFAULT} Where, ”charset_name” is the name of the character set. Example The query given below sets the character set to “macroman” − SET CHARACTER SET macroman; Output The output produced is as shown below − Query OK, 0 rows affected (0.10 sec) Verification You can verify the character set values using the SHOW VARIABLES LIKE statement as shown below − SHOW VARIABLES LIKE “character_set_client”; Following is the output obtained − Variable_name Value character_set_client macroman Now verifying the current value of the “character_set_results” variable − SHOW VARIABLES LIKE “character_set_results”; The result produced is as shown below − Variable_name Value character_set_results macroman Print Page Previous Next Advertisements ”;