In this post, we will understand the difference between MySQL and PostgreSQL.MySQLIt is a relational database management system.It is the product developed by Oracle Corporation.It is supported by Windows, Mac OS X, Linux, BSD, UNIX, z/OS, Symbian, AmigaOS.It can’t be extended.In this system, the phpMyAdmin tool gives the GUI.Mysqldump and XtraBackup provide backup in MySQL.It provides temporary table.It doesn’t provide a materialized view.It doesn’t provide Data Domain Object to the system.PostgreSQLIt is an object-relational database management system.It was developed by the Global Development Group.It is supported by Windows, Mac OS X, Linux and BSD but not by UNIX, z/OS, Symbian, ... Read More

In this post, we will understand the difference between WHERE clause and HAVING clause in SQL.WHERE ClauseIt is used to filter the records from the table based on a specific condition.It can be used without the ‘GROUP BY’ clause.It can be used with row operations.It can’t contain the aggregate functions.It can be used with the ‘SELECT’, ‘UPDATE’, and ‘DELETE’ statements.It is used before the ‘GROUP BY’ clause if required.It is used with a single row function such as ‘UPPER’, ‘LOWER’.HAVING ClauseIt is used to filter out records from the groups based on a specific condition.It can’t be used without the ... Read More

In this post, we will understand the difference between COMMIT and ROLLBACK in SQL.COMMITIt validates the modifications that are made by the current transaction.Once the COMMIT statement has been executed, the transaction can’t be rolled back using ROLLBACK.It occurs when the transaction is successfully executed.SyntaxCOMMIT;ROLLBACKIt removes the modifications that were made by the current transaction.Once ROLLBACK is executed, the database would reach its previous state.This is the state where the first statement of the transaction would be in execution.ROLLBACK happens when the transaction is aborted in between its execution.SyntaxROLLBACK;

In this post, we will understand the difference between SQL and T-SQL.SQLIt is a non-procedural language.Relational databases use SQL.It stands for structured query language.It uses query to view and manipulate data.DML and DDL operations are used- Data manipulation language, and data definition language.It is considered as an open-source language.It helps in the manipulation of data and data controlling.Transfer of data happens one by one when SQL is used.T-SQLIt is a Microsoft product.It is known as Transact Structure Query language.It gives a high degree of control to the developers/programmers.It works its best, and provides good performance with Microsoft SQL server.It is ... Read More

MySQL uses port number 3306 by default.3306 Port Number3306 port number is used by MySQL protocol to connect with the MySQL clients and utilities such as ‘mysqldump’. It is a TCP, i.e Transmission Control Protocol.VulnerabilitiesLet us see if there are any vulnerabilities while using this default port −In general, port 3306 shouldn’t be opened since it could make the server vulnerable to attack. If the user needs to connect to the database remotely, there are many other secure options, instead of opening the port 3306.One of the secure options includes using an SSH tunnel. On the other hand, if it ... Read More

To determine the connection method that is used by MySQL connection, the below command can be used −netstat −ln | grep 'mysql'On Unix, MySQL programs treat the host name ‘localhost’ in a special manner. Hence, it behaves differently than what is expected of it.Type of ConnectionTo know the type of connection from within the mysql CLI, the below command can be used −mysql> \sOutput −Connection: 127.0.0.1 via TCP/IP (or) Connection: Localhost via UNIX socketTCP/IP connection to the local serverTo ensure that the client makes a TCP/IP connection to the local server, the --host or -h can be used. This will ... Read More

Let us look at the administrative and utility programs in MySQL and understand how they can be used −ibd2sdiIt is a utility to extract serialized dictionary information (SDI) from InnoDB tablespace files. SDI data is present all persistent InnoDB tablespace files. ibd2sdi can be used at runtime or when the server is offline.innochecksumIt prints the checksums for InnoDB files. It reads an InnoDB tablespace file, calculates the checksum for every page, compares the calculated checksum to the stored checksum, and reports mismatches, which show the damaged pages. It was originally developed to speed up the verification of the integrity of ... Read More

The mysqlshow client can be used to see what databases exist, their tables, or a table's columns or indexes.It provides a command-line interface for several SQL SHOW statements.Invoke mysqlshowThe mysqlshow utility can be invoked as shown below −shell> mysqlshow [options] [db_name [tbl_name [col_name]]]Here, If no database is provided, a list of database names are displayed.If no table is given, all matching tables in the database are displayed.If no column is provided, all matching columns and column types in the table are shown.The output displays the names of only those databases, tables, or columns for which the user has certain privileges.Optionsmysqlshow ... Read More

The mysqlpump client utility performs logical backups, thereby producing a set of SQL statements that would be executed to reproduce the original database object definitions and table data. It helps dump one or more MySQL databases for backup or transfer to another SQL server.Features of mysqlpumpLet us understand the features of mysqlpump −Parallel processing of databases, as well as that of objects within databases, thereby helping speed up the dump process.It provides better control over which databases and database objects (tables, stored programs, user accounts) need to be dumpedDumping of user accounts as account-management statements (CREATE USER, GRANT) instead of ... Read More

The mysqlimport client comes with a command-line interface that helps with the LOAD DATA SQL statement. Most options to mysqlimport respond directly to clauses of LOAD DATA syntaxInvoking mysqlimportThe utility mysqlimport can be invoked as shown below −shell> mysqlimport [options] db_name textfile1 [textfile2 ...]For every text file that is named on the command line, mysqlimport strips any extension from the file name and uses the result to figure out the name of the table into which the file's contents have to be imported.ExampleLet us take an example: Files named sample.txt, sample.text, and sample all would be imported into a table ... Read More