Found 4219 Articles for MySQLi

There is no concept of TOP in MySQL. The alternate way to write your query is using LIMIT i.e to select 2 records, you need to use TOP 2. Let us see the syntax for the same in MySQLSELECT *FROM yourTableName ORDER BY yourColumnName DESC LIMIT 2;To understand the above syntax, let us create a table. The query to create a table is as followsmysql> create table Top2Demo    - > (    - > Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,    - > Name varchar(20),    - > Age int    - > ); Query OK, 0 rows ... Read More

To get the server_id, use the system defined variable @@server_id. You cannot use only a single @ as user defined variable for server_id.The syntax is as followsSELECT@@ server_idAs an alternate, you can use SHOW VARIABLES command.The syntax is as followsSHOW VARIABLES LIKE ‘server_id’;Case 1The query is as followsmysql> SELECT @@server_id as SERVER_ID;The following is the output+-----------+ | SERVER_ID | +-----------+ |         1 | +-----------+ 1 row in set (0.00 sec)Case 2The query is as followsmysql> show variables like 'server_id';The following is the output+---------------+-------+ | Variable_name | Value | +---------------+-------+ | server_id | ... Read More

To check if value exists in a comma separated list, you can use FIND_IN_SET() function.The syntax is as followsSELECT *FROM yourTablename WHERE FIND_IN_SET(‘yourValue’, yourColumnName) > 0;Let us first create a table. The query to create a table is as followsmysql> create table existInCommaSeparatedList - > ( - > Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, - > Name varchar(200) - > ); Query OK, 0 rows affected (0.68 sec)Now you can insert some records in the table using insert command.The query is as followsmysql> insert into existInCommaSeparatedList(Name) values('John, ... Read More

The following is the syntax of END IF statement in MySQLIF yourCondition THEN yourStatement ELSE yourStatement END IFHere is the demo of END IF statement while creating a stored proceduremysql> DELIMITER // mysql> CREATE PROCEDURE Sp_Test( IN value INT ) - > BEGIN - > IF value < 10 THEN - > select 'Your value is less than 10'; - > ELSE - > select 'Your value is greater than 10'; - > END IF; ... Read More

To get the longest varchar length, you need to use CHAR_LENGTH().The syntax is as followsSELECT Max(CHAR_LENGTH(yourColumnName)) AS anyAliasName FROM yourTableName;To understand the above syntax, let us create a table. The query to create a table is as followsmysql> create table CharLengthDemo    - > (    - > Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,    - > Sentence varchar(255)    - > ); Query OK, 0 rows affected (0.60 sec)Insert some records in the table using insert command.The query is as followsmysql> insert into CharLengthDemo(Sentence) values('Java is an object oriented programming language' - > ); Query OK, 1 row ... Read More

The ON UPDATE CURRENT_TIMESTAMP defines that an update without an explicit timestamp would result in an update to the current timestamp value.You can remove ON UPDATE CURRENT_TIMESTAMP from a column using ALTER command.The syntax is as followsALTER TABLE yourTableName CHANGE yourTimeStampColumnName yourTimeStampColumnName timestamp NOT NULL default CURRENT_TIMESTAMP;To understand the above syntax, let us create a table. The query to create a table is as followsmysql> create table removeOnUpdateCurrentTimeStampDemo    - > (    - > Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,    - > Name varchar(20),    - > UserUpdateTimestamp timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP   ... Read More

To get the records of the last days from the current date, you need to use DATE_SUB(). We will also use NOW() to get the current date. The syntax for the same is as followsSELECT *FROM yourTableName WHERE yourDateTimeColumnName BETWEEN DATE_SUB(DATE(NOW()), INTERVAL 2 DAY) AND DATE_SUB(DATE(NOW()), INTERVAL 1 DAY);Let us see an examplemysql> create table get2daysAgoDemo - > ( - > Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, - > Name varchar(20), - > PostDateTime datetime - > ); Query OK, 0 rows affected (1.70 ... Read More

You need to use DISTINCT keyword to select distinct dates from datetime column in a table.For an example, let us create a tablemysql> create table PostMesssageDemo    - > (    - > UserId int NOT NULL AUTO_INCREMENT PRIMARY KEY,    - > UserMessage varchar(100),    - > UserPost datetime    - > ); Query OK, 0 rows affected (0.60 sec)Now you can insert some records in the table using insert command.The query is as followsmysql> insert into PostMesssageDemo(UserMessage, UserPost) values('Software Developer', now()); Query OK, 1 row affected (0.17 sec) mysql> insert into PostMesssageDemo(UserMessage, UserPost) values('Software Developer', date_add(now(), interval 3 ... Read More

Here is the syntax of MySQL CASE OR conditionSELECT yourColumnName1, .....N ,    CASE WHEN yourColumnName2=0 or yourColumnName2IS NULL THEN 'yourMessage1' ELSE 'yourMessage2' END AS yourAliasName FROM yourTableName;To understand the above syntax, let us create a table. The query to create a table is as followsmysql> create table ReservationSystems    - > (    - > Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,    - > Name varchar(20),    - > isSeatConfirmed tinyint    - > ); Query OK, 0 rows affected (0.61 sec)Insert some records in the table using insert command.The query is as followsmysql> insert into ReservationSystems(Name, isSeatConfirmed) ... Read More

To select first 10 elements from a database using SQL ORDER BY clause with LIMIT 10.The syntax is as followsSELECT *FROM yourTableName ORDER BY yourIdColumnName LIMIT 10;To understand the above syntax, let us create a table. The query to create a table is as followsmysql> create table Clients    - > (    - > Client_Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,    - > ClientName varchar(20)    - > ); Query OK, 0 rows affected (0.51 sec)Insert some records in the table using INSERT command.The query is as followsmysql> insert into Clients(ClientName) values('Larry'); Query OK, 1 row affected (0.09 ... Read More