MySQLi Articles - Page 325 of 422

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

Let us now see the following methods to calculate the time difference between two timestamps in seconds. Method The following is the query to calculate the difference between two timestamps. mysql> SELECT TIMESTAMPDIFF(SECOND, '2018-10-17 11:51:55', '2018-10-17 11:51:58'); The following is the output in seconds. +---------------------------------------------------------------------+ | TIMESTAMPDIFF(SECOND, '2018-10-17 11:51:55', '2018-10-17 11:51:58') | +---------------------------------------------------------------------+ | ... Read More

The Trim() function is used to remove new line characters from data rows in MySQL. Let us see an example. First, we will create a table. The CREATE command is used to create a table.mysql> create table tblDemotrail - > ( - > id int, - > name varchar(100) - > ); Query OK, 0 rows affected (0.57 sec)Let us now insert some records.mysql> insert into tblDemotrail values(1, 'John '); Query OK, 1 row affected (0.15 sec) mysql> insert into tblDemotrail values(2, ' Carol'); Query OK, 1 row affected (0.32 sec) mysql> insert into tblDemotrail values(3, ' Sam ... Read More

To store username and passwords safely in MySQL database, we can use MD5().Let us see an example. First, we will create a table. The CREATE command is used to create a table.mysql> create table UserNameAndPasswordDemo - > ( - > U_Id int(10) unsigned NOT NULL AUTO_INCREMENT, - > UserId varchar(255) DEFAULT NULL, - > UserPassword varchar(255) DEFAULT NULL, - > primary key(U_Id), - > UNIQUE KEY `UserId` (`UserId`) - > ); Query OK, 0 rows affected (0.61 sec)Inserting records and safely storing passwords with the help of MD5().mysql> INSERT INTO UserNameAndPasswordDemo(UserId, UserPassword) VALUES ('John@gg.com', MD5('john123')); Query OK, 1 row affected ... Read More

The following is the process to simulate MySQL’s ORDER BY FIELD() in PostgreSQL.We have used an Online Compiler to run PostgreSQL.Let us now see what we did above to get the output.Firstly, we created a table.create table PostgreOrderIdDemo (    countryName varchar(20) );Inserted records with the help of INSERT command.insert into PostgreOrderIdDemo values('IND'); insert into PostgreOrderIdDemo values('US'); insert into PostgreOrderIdDemo values('UK');The following is the syntax in PostgreSQL.SELECT * FROM yourTableName ORDER BY CASE WHEN columnName='IND' THEN 1 WHEN columnName='US' THEN 2 WHEN columnName='UK' THEN 3 ELSE 0 END, columnName;ExampleLet us now use the above syntax to get the output.SELECT * ... Read More

We cannot use a function for default value in MySQL, but we can use triggers. Let us see an example. First, we will create a table. The CREATE command is used to create a table. mysql> CREATE table TbLFunctionTrigger - > ( - > id int, - > username varchar(100) - > ); Query OK, 0 rows affected (0.55 sec) The following is the syntax to create a trigger and include a default value. CREATE TRIGGER anyName BEFORE INSERT ON yourTableName FOR EACH ROW ... Read More

In order to generate a 10 character string, we can use inbuilt functions ‘rand()’ and ‘char()’. The following is the query to generate random 10 character string. mysql> SELECT concat( - > char(round(rand()*25)+97), - > char(round(rand()*25)+97), - > char(round(rand()*25)+97), - > char(round(rand()*25)+97), - > char(round(rand()*25)+97), - > char(round(rand()*25)+97), - > char(round(rand()*25)+97), - > char(round(rand()*25)+97), - > char(round(rand()*25)+97), - > char(round(rand()*25)+97) - > )AS Random10CharacterString; ... Read More

In an If-Else statement, the condition is evaluated to be true or false depending on the value. Let us see an example. Firstly, we will create a table. The CREATE command is used to create a table. mysql> create table IfelseDemo - > ( - > id int, - > FirstName varchar(100) - > ); Query OK, 0 rows affected (0.46 sec) Records are inserted with the help of INSERT command. mysql> insert into IfelseDemo values(1, 'John'); Query OK, 1 row affected (0.13 sec) ... Read More