Unique in MySQL means we cannot add duplicate records. Let us now see how to create a unique constraint in the column at the time of creating a table.mysql> create table UniqueConstDemo - > ( - > name varchar(100) unique - > ); Query OK, 0 rows affected (0.72 sec)Now, we cannot have same value more than once for column ‘name’.Inserting some records with duplicate values to check the error.mysql> insert into UniqueConstDemo values('John'); Query OK, 1 row affected (0.19 sec) mysql> insert into UniqueConstDemo values('John');On running the above query, the following error is visible.mysql> insert into UniqueConstDemo values('John'); ... Read More

To add not null constraint to an existing column in MySQL, we will use the ALTER command. This is a type of validation to restrict the user from entering null values. Let us see an example. Firstly, we will create a table. The CREATE command is used to create a table. mysql> create table AddNotNUlldemo - > ( - > name varchar(100) - > ); Query OK, 0 rows affected (0.44 sec) To insert records. mysql> insert into AddNotNUlldemo values('John'); Query OK, 1 row affected (0.19 sec) ... Read More

To list running queries, we need to use the ‘show processlist’ command. The following is the query. mysql> SHOW processlist; The following is the output of the above query. +----+-----------------+-----------------+----------+---------+-------+------------------------+------------------+ | Id | User | Host | db | Command | Time | State | Info ... Read More

To insert current date/ time in MySQL, use the now() function. Let us now see an example.At first, we will create a table. The CREATE command is used to create a table.mysql > create table CurrentDateTime -> ( -> CurrentTime datetime -> ); Query OK, 0 rows affected (1.14 sec)Syntax to insert the current date/time with the help of insert command and now()mysql> insert into CurrentDateTime values(now()); Query OK, 1 row affected (0.11 sec)To check that the current date/ time is inserted in the table or not, use the select command.mysql> select *from CurrentDateTime; The following is the output that ... Read More

Let us see an example to get the count of each distinct value in a column. Firstly, we will create a table. The CREATE command is used to create a table. mysql> create table DistinctDemo1 - > ( - > id int, - > name varchar(100) - > ); Query OK, 0 rows affected (0.43 sec) Inserting records mysql> insert into DistinctDemo1 values(1, 'John'); Query OK, 1 row affected (0.34 sec) mysql> insert into DistinctDemo1 values(2, 'John'); Query OK, 1 row affected (0.20 sec) ... Read More

To return Sum as ‘0’ if no values are found, use IFNULL or COALESCE commands. The following is the syntax for IFNULL. SELECT IFNULL(SUM(NULL), 0) AS aliasName; Let us now implement the above syntax in the following query. mysql> SELECT IFNULL(SUM(NULL), 0) AS SUMOFTWO; The following is the output of the above query, which returns 0. +----------+ | SUMOFTWO | +----------+ | 0 | +----------+ 1 row in set (0.00 sec) Here is the syntax for COALESCE. mysql> SELECT COALESCE(SUM(NULL),0) as SUMOFTWO; The following is the output that returns 0 using the SUM() function. +----------+ | SUMOFTWO | +----------+ | 0 | +----------+ 1 row in set (0.00 sec)

Let us see some of the ways to check if a table exist in MySQL or not.Using SHOWThe SHOW command displays all the tables.SHOW tables;Here is the output.+--------------------------+ | Tables_in_business       | +--------------------------+ | addcheckconstraintdemo   | | addcolumntable           | | addconstraintdemo        | | alphademo                | | autoincrement            | | autoincrementtable       | | backticksymbol           | | bookindexes              | | chardemo         ... Read More

The composite index can be used when we are using sub queries. The advantages of using composite index are in case of. Joining Filtering Selecting The following is the syntax of index. index(column_name1, column_name2, column_name3, column_name4, ............................N) Let us create a table first and within that we have set index. mysql> create table MultipleIndexDemo - > ( - > id int, - > FirstName varchar(100), - > LastName varchar(100), - > Address varchar(200), - > index(id, LastName, ... Read More

To do a batch insert, we need to use all column names with parenthesis, separated by ‘, ’. Let us see an example. First, we will create a table. The following is the CREATE command to create a table. mysql> CREATE table MultipleRecordWithValues - > ( - > id int, - > name varchar(100) - > ); Query OK, 0 rows affected (0.88 sec) The following is the syntax of batch insert. INSERT into yourTableName values(column1, column2, ....N), (column1, column2, ....N), (column1, column2, ....N), ...........N; ... Read More

We can show the result as a comma separated list with the help of the ‘concat()’ function with the parameter ‘, ’. Let us see an example now. Firstly, we will create a table. The CREATE command is used to create a table. mysql> create table CommaSeperateDemo - > ( - > Id int, - > FirstName varchar(100), - > LastName varchar(100) - > ); Query OK, 0 rows affected (0.93 sec) Inserting records mysql> insert into CommaSeperateDemo values(1, 'John', 'Taylor'); Query ... Read More