Category: mysql

Aug 10

MySQL – Connection

MySQL – Connection Table of content Set Password to MySQL Root MySQL Connection Using MySQL Binary MySQL Connection Using PHP Script ”; Previous Next While working with MySQL database, we use a client program to communicate with the database server. To do so, we must first establish a connection between them. To connect a client program with MySQL server, we must ensure all the connection parameters are properly used. These parameters work just like any other login parameters: consisting of a username and a password. Where, a username is the name of the host where the server is running and a password needs to be set according to the user. Generally, each connection parameter holds a default value, but we can override them either on the command line or in an option file. This tutorial only uses the mysql client program to demonstrate the connection, but these principles also apply to other clients such as mysqldump, mysqladmin, or mysqlshow. Set Password to MySQL Root Usually, during the installation of MySQL server, we will be asked to set an initial password to the root. Other than that, we can also set the initial password using the following command − mysql -u root password “new_password”; Where, new_password is the password set initially. Reset Password We can also change the existing password using the SET PASSWORD statement. However, we can only do so after logging in to the user account using the existing password. Look at the query below − SET PASSWORD FOR ”root”@”localhost” = PASSWORD(”password_name”); FLUSH PRIVILEGES; Every time a connection is needed to be established, this password must be entered. MySQL Connection Using MySQL Binary We can establish the MySQL database using the mysql binary at the command prompt. Example Here is a simple example to connect to the MySQL server from the command prompt − [root@host]# mysql -u root -p Enter password:****** This will give us the ”mysql>” command prompt where we will be able to execute any SQL query. Following is the result of above command − The following code block shows the result of above code − Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 2854760 to server version: 5.0.9 Type ”help;” or ”h” for help. Type ”c” to clear the buffer. In the above example, we have used root as a user but we can use any other user as well. Any user will be able to perform all the SQL operations, which are allowed to that user. We can disconnect from the MySQL database any time using the exit command at mysql> prompt. mysql> exit Bye MySQL Connection Using PHP Script We can open/establish connection to MySQL database using the PHP mysqli() constructor or, mysqli_connect() function. This function takes six parameters and returns a MySQL link identifier on success or FALSE on failure. Syntax Following is the syntax to open a MySQL connection using the constructor mysqli() − $mysqli = new mysqli($host, $username, $passwd, $dbName, $port, $socket); Parameters Following are its parameters − Sr.No. Parameter & Description 1 $host Optional − The host name running the database server. If not specified, then the default value will be localhost:3306. 2 $username Optional − The username accessing the database. If not specified, then the default will be the name of the user that owns the server process. 3 $passwd Optional − The password of the user accessing the database. If not specified, then the default will be an empty password. 4 $dbName Optional − database name on which query is to be performed. 5 $port Optional − the port number to attempt to connect to the MySQL server. 6 $socket Optional − socket or named pipe that should be used. Closing the Connection We can disconnect from the MySQL database anytime using another PHP function close(). Following is the syntax − $mysqli->close(); Example Try the following example to connect to a MySQL server. Save the file as mysql_example.php − <html> <head> <title>Connecting MySQL Server</title> </head> <body> <?php $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 />”); $mysqli->close(); ?> </body> </html> Output Access the mysql_example.php deployed on apache web server and verify the output. Connected successfully. Print Page Previous Next Advertisements ”;

Aug 10

MySQL – Show Database

MySQL – SHOW Databases Table of content MySQL SHOW Databases Statement MySQL SHOW SCHEMAS Statement Showing Databases Using a Client Program ”; Previous Next MySQL SHOW Databases Statement To display the list of all databases present in a MySQL server, we need to use the SHOW DATABASES statement. It returns the result in a tabular form with one column. The databases in the result set will be sorted in alphabetical order. Syntax Following is the syntax to list all databases in MySQL sever − SHOW DATABASES [LIKE ”pattern” | WHERE expr] Example First of all, let us create a database with a name TUTORIALS using the following query − CREATE DATABASE TUTORIALS; Make sure you have the admin privilege before creating any database. Once a database is created, you can check it in the list of databases by using the following query − SHOW DATABASES; Output The above query displayed all the databases present in the current MySQL server. Database information_schema mysql performance_schema tutorials MySQL SHOW SCHEMAS Statement We can also use the SHOW SCHEMAS statement to list out the databases in MySQL. Syntax Following is the syntax of the MySQL SHOW SCHEMAS statement − SHOW SCHEMAS [LIKE ”pattern” | WHERE expr] Example Lets try to verify the list of databases once again you can using the following query − SHOW SCHEMAS; Output The output of the above query will be as shown below − Database information_schema mysql performance_schema tutorials Showing Databases Using a Client Program Besides fetching the list of all databases in current MySQL server with a MySQL query, we can also use a client program to perform the SHOW DATABASES operation. Syntax Following are the syntaxes of this operation in various programming languages − PHP NodeJS Java Python To show the list of all databases present in current MySQL through PHP program, we need to execute the SHOW DATABASES statement using the mysqli function query() as follows − $sql = “SHOW Database_name”; $mysqli->query($sql); To show the list of all databases present in current MySQL through Node.js program, we need to execute the SHOW DATABASES statement using the query() function of the mysql2 library as follows − sql= “SHOW {DATABASES | SCHEMAS} LIKE ”pattern” | WHERE expr]”; con.query(sql); To show the list of all databases present in current MySQL through Java program, we need to execute the SHOW DATABASES statement using the JDBC function executeUpdate() as follows − String sql = “SHOW Database_name”; st.executeQuery(sql); To show the list of all databases present in current MySQL through Python program, we need to execute the SHOW DATABASES statement using the execute() function of the MySQL Connector/Python as follows − sql = “SHOW Database_name”; cursorObj.execute(sql) Example Following are the programs − PHP NodeJS Java Python $dbhost = ”localhost”; $dbuser = ”root”; $dbpass = ”password”; $mysqli = new mysqli($dbhost, $dbuser, $dbpass); if($mysqli->connect_errno ) { printf(“Connect failed: %s”, $mysqli->connect_error); exit(); } //printf(”Connected successfully.”); if ($result = $mysqli->query(“SHOW DATABASES”)) { printf(“Show Database executed successfully..!”); echo “Database list are: “; while($row = mysqli_fetch_array($result)){ print_r($row); } } if ($mysqli->errno) { printf(“Could not find database: %s”, $mysqli->error); } $mysqli->close(); Output The output obtained is as follows − Show Database executed successfully..!Database list are: Array ( [0] => bank [Database] => bank ) Array ( [0] => company [Database] => company ) Array ( [0] => information_schema [Database] => information_schema ) Array ( [0] => mydb [Database] => mydb ) Array ( [0] => mysql [Database] => mysql ) Array ( [0] => performance_schema [Database] => performance_schema ) Array ( [0] => sys [Database] => sys ) Array ( [0] => testdb [Database] => testdb ) Array ( [0] => tutorials [Database] => tutorials ) Array ( [0] => usersdb [Database] => usersdb ) 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 testDB1″ con.query(sql); sql = “create database testDB2″ con.query(sql); sql = “create database testDB3″ con.query(sql); sql = “create database testDB4″ con.query(sql); //Displaying Databases sql = “show databases;” con.query(sql, function(err, result){ if (err) throw err console.log(result) }); }); Output The output produced is as follows − Connected! ————————– [ { Database: ”customers” }, { Database: ”information_schema” }, { Database: ”mysql” }, { Database: ”performance_schema” }, { Database: ”testdb1” }, { Database: ”testdb2” }, { Database: ”testdb3” }, { Database: ”testdb4” } ] import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.Statement; public class ShowDatabase { public static void main(String[] args) { String url = “jdbc:mysql://localhost:3306/TUTORIALS”; String user = “root”; String password = “password”; ResultSet rs; try { Class.forName(“com.mysql.cj.jdbc.Driver”); Connection con = DriverManager.getConnection(url, user, password); Statement st1 = con.createStatement(); //System.out.println(“Database connected successfully…!”); String sql = “SHOW DATABASES”; rs = st1.executeQuery(sql); System.out.println(“Show query executed successfully…!”); System.out.println(“Databases are: “); while(rs.next()) { String db = rs.getNString(1); System.out.println(db); } }catch(Exception e) { e.printStackTrace(); } } } Output The output obtained is as shown below − Show query executed successfully…! Databases are: bank company information_schema mydb mysql performance_schema sys testdb tutorials usersdb import mysql.connector # Creating the connection object connection = mysql.connector.connect( host =”localhost”, user =”root”, password =”password” ) #

Aug 10

MySQL – Show Processlist

MySQL – Show Processlist Table of content What is MySQL Process List? The MySQL SHOW PROCESSLIST Command Showing Process List Using Client Program ”; Previous Next MySQL database provides a multi-user environment, that allows multiple clients to access the database at the same time. A process is defined as the operations performed by a user on the MySQL Server. Multiple processes can be run on a MySQL Server concurrently by multiple users. What is MySQL Process List? The MySQL process list is defined as the list of operations currently being performed by the set of user threads executing within the server. If a user has the PROCESS privilege, they can see all threads in a server, including threads of other users. But if a user does not have such privilege, non-anonymous users have access to information about their own threads only; while anonymous users have no access to thread information. To retrieve information about these processes running on a MySQL Server, we can use the SHOW PROCESSLIST command. The MySQL SHOW PROCESSLIST Command The MySQL SHOW PROCESSLIST command is used to display information about the current processes running on a MySQL Server. This statement is especially useful when dealing with a “too many connections” error, as it provides details about these connections and their operations. Additionally, MySQL reserves one extra connection for administrators with CONNECTION_ADMIN privilege (or SUPER privilege in older versions), to ensure they can always access the system. Syntax Following is the syntax of the SHOW PROCESSLIST Command − SHOW [FULL] PROCESSLIST Here, the FULL keyword is optional. But if you omit the FULL keyword, SHOW PROCESSLIST displays only the first 100 characters of each statement in the Info field. Example Let us see an example to show the usage of SHOW PROCESSLIST command. We will use the ”G” delimiter to print the information table vertically − SHOW PROCESSLISTG Output Following is the output obtained − *************************** 1. row *************************** Id: 5 User: event_scheduler Host: localhost db: NULL Command: Daemon Time: 1065209 State: Waiting on empty queue Info: NULL *************************** 2. row *************************** Id: 56 User: root Host: localhost:51995 db: customers Command: Query Time: 0 State: init Info: SHOW PROCESSLIST 2 rows in set (0.00 sec) Example Now, let us also use the FULL keyword with the SHOW PROCESSLIST command as shown in the following example − SHOW FULL PROCESSLISTG Output The output obtained is as shown below − *************************** 1. row *************************** Id: 5 User: event_scheduler Host: localhost db: NULL Command: Daemon Time: 1065138 State: Waiting on empty queue Info: NULL *************************** 2. row *************************** Id: 56 User: root Host: localhost:51995 db: customers Command: Query Time: 0 State: init Info: SHOW FULL PROCESSLIST 2 rows in set (0.00 sec) Output Explanation The output result-set obtained from the SHOW PROCESSLIST command has the following columns − Id − It is the identity of a connection. User − This holds the name of a MySQL user who issued the statement. Host − The host name of the client issuing the statement (except for system user, as there is no host for it). The host name for TCP/IP connections is represented in “host_name:client_port” format to make it easier to determine the actions of a client. db − This is the default database for the thread, or NULL if none has been selected. Command − Shows the type of command the corresponding thread is executing on behalf of the client, or shows Sleep if the session is idle. Time − The time in seconds that the thread has been in its current state. State − An action, event, or state that indicates what the thread is doing. Most states correspond to very quick operations. If a thread stays in a given state for many seconds, there might be a problem that needs to be investigated. Info − The statement the thread is executing. If it is executing no statement, NULL is shown. Showing Process List Using Client Program We can also show the process list using Client Program. Syntax PHP NodeJS Java Python To retrieve information about processes running on a MySQL Server, through a PHP program, we need to execute the “SHOW PROCESSLIST” command using the mysqli function query() as follows − $sql = “SHOW PROCESSLIST”; $mysqli->query($sql); To retrieve information about processes running on a MySQL Server, through a JavaScript program, we need to execute the “SHOW PROCESSLIST” command using the query() function of mysql2 library as follows − sql = “SHOW PROCESSLIST”; con.query(sql) To retrieve information about processes running on a MySQL Server, through a Java program, we need to execute the “SHOW PROCESSLIST” command using the JDBC function executeQuery() as follows − String sql = “SHOW PROCESSLIST”; statement.executeQuery(sql); To retrieve information about processes running on a MySQL Server, through a Python program, we need to execute the “SHOW PROCESSLIST” command using the execute() function of the MySQL Connector/Python as follows − show_processlist_query = “SHOW PROCESSLIST” cursorObj.execute(show_processlist_query) Example Following are the programs − PHP NodeJS Java Python $dbhost = ”localhost”; $dbuser = ”root”; $dbpass = ”password”; $db = ”TUTORIALS”; $mysqli = new mysqli($dbhost, $dbuser, $dbpass, $db); if ($mysqli->connect_errno) { printf(“Connect failed: %s”, $mysqli->connect_error); exit(); } //printf(”Connected successfully.”); $sql = “SHOW PROCESSLIST”; if($result = $mysqli->query($sql)){ printf(“Show query executed successfully…!n”); printf(“Process list: n”); while($row = mysqli_fetch_array($result)){ print_r($row); } } if($mysqli->error){ printf(“Error message: “, $mysqli->error); } $mysqli->close(); Output The output obtained is as shown below − Show query executed

Aug 10

MySQL – SubQuery

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

Aug 10

MySQL – Create Database

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

Aug 10

MySQL – Check Constraints

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

Aug 10

MySQL – Storage Engines

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:

Aug 10

MySQL – Upsert

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

Aug 10

MySQL – Workbench

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

Aug 10

MySQL – Character Set

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 ”;