Article Categories
- All Categories
-
Data Structure
-
Networking
-
RDBMS
-
Operating System
-
Java
-
MS Excel
-
iOS
-
HTML
-
CSS
-
Android
-
Python
-
C Programming
-
C++
-
C#
-
MongoDB
-
MySQL
-
Javascript
-
PHP
-
Economics & Finance
MySQLi Articles
Page 325 of 341
Retrieve a large select by chunks in MySQL?
To retrieve large select by chunks, you need to use ORDER BY LIMIT. The syntax is as follows:SELECT *FROM yourTableName ORDER BY yourColumnName LIMIT 0, 10;From the above syntax, you will get 10 rows from the table. In the above syntax, 0 represents the first row from the result set of a table that means it is zero index based. The second value of LIMIT represents the maximum number of rows that can be retrieved from the table.If you want the next rows after 10 to 30, then use in LIMIT like this. The syntax is as follows:SELECT *FROM yourTableName ...
Read MoreHow to get the sum for every distinct value in another column in MySQL?
You can get the sum for every distinct value in another column with the help of aggregate function SUM() with GROUP BY command. To understand the above concept, let us create a table. The query to create a table is as follows:mysql> create table SumOfEveryDistinct -> ( -> Id int not null, -> Amount int -> ); Query OK, 0 rows affected (0.59 sec)Insert some records in the table using insert command. The query is as follows:mysql> insert into SumOfEveryDistinct values(10, 100); Query OK, 1 row affected (0.19 ...
Read MoreMySQL: Insert a row and get the content?
In order to do insert a row and get the content, you need to use stored procedure, First, you need to create a table. After that you need to create a stored procedure that will insert a row and get the content to the end user.To do the above task, let us first create a table. The query to create a table is as follows:mysql> create table InsertRecord_SelectTable -> ( -> Id int NOT NULL AUTO_INCREMENT, -> Name varchar(20), -> PRIMARY KEY(Id) -> ); Query ...
Read MoreWhen the MySQL delimiter error occur?
The MySQL delimiter occurs when you are using a pipe delimiter(|) with semicolon (;) and using MySQL version lower than 8.0.12.MySQL treats the pipe (|) as one delimiter and semicolon (;) is another delimiter. Therefore, do not confuse the MySQL delimiter with pipe as well as semicolon.Note: Here, we are using MySQL version 8.0.12. The pipe delimiter works fine with semicolon. If you are using version lower than 8.0.12, then this leads to a delimiter error.Here is the working of MySQL delimiter:mysql> delimiter |; mysql> create procedure getSumOfTwoNumbers() -> begin -> select 2+3 as ...
Read MoreHow to use GROUP BY to concatenate strings in MySQL and how to set a separator for the concatenation?
To concatenate strings in MySQL with GROUP BY, you need to use GROUP_CONCAT() with a SEPARATOR parameter which may be comma(‘) or space (‘ ‘) etc.The syntax is as follows:SELECT yourColumnName1, GROUP_CONCAT(yourColumnName2 SEPARATOR ‘yourValue’) as anyVariableName FROM yourTableName GROUP BY yourColumnName1;To understand the above syntax, let us create a table. The query to create a table is as follows:mysql> create table GroupConcatenateDemo -> ( -> Id int, -> Name varchar(20) -> ); Query OK, 0 rows affected (0.99 sec)Insert some records in the table using insert command. The query to insert record is as follows:mysql> insert ...
Read MoreHow to implement WHILE LOOP with IF STATEMENT MySQL?
The following is an example to implement MySQL WHILE LOOP with IF statement. We are using in a stored procedureThe following is the query to create our stored procedure:mysql> DELIMITER // mysql> create procedure sp_getDaysDemo() -> BEGIN -> SELECT MONTH(CURDATE()) INTO @current_month; -> SELECT MONTHNAME(CURDATE()) INTO @current_monthname; -> SELECT DAY(LAST_DAY(CURDATE())) INTO @total_numberofdays; -> SELECT CAST(DATE_FORMAT(NOW() ,'%Y-%m-01') as DATE)INTO @check_weekday; -> SELECT DAY(@check_weekday) INTO @check_day; -> SET @count_days = 0; -> SET @workdays = 0; ...
Read MoreHow to find all uppercase strings in a MySQL table?
To find all upper case strings in a MySQL table, you need to use BINARY UPPER() function. The syntax is as follows:SELECT *FROM yourTableName WHERE yourColumnName=BINARY UPPER(yourColumnName);To understand the above syntax, let us create a table. The query to create a table is as follows:mysql> create table FindUpperCaseDemo -> ( -> Id int, -> FirstName varchar(20), -> Age int -> ); Query OK, 0 rows affected (1.04 sec)Insert some records in the table using insert command. The query is as follows:mysql> insert into FindUpperCaseDemo values(1, 'John', 23); Query OK, 1 row affected (0.17 sec) mysql> ...
Read MoreDifference between two selects in MySQL?
You can use subqueries for difference between two selects in MySQL. The syntax is as follows:SELECT *FROM yourTableName where yourColumnName NOT IN(SELECT yourColumnName FROM youTableName WHERE yourCondition;To understand the above syntax, let us create a table. The query to create a table is as follows:mysql> create table DifferenceSelectDemo -> ( -> Id int NOT NULL AUTO_INCREMENT, -> UserId int, -> UserValue int, -> PRIMARY KEY(Id) -> ); Query OK, 0 rows affected (0.87 sec)Insert some records in the table using insert command. The query is as follows:mysql> insert into DifferenceSelectDemo(UserId, UserValue) values(10, 10); Query ...
Read MoreSELECT MySQL rows where today's date is between two DATE columns?
To select MySQL rows where today’s date is between two date columns, you need to use AND operator. The syntax is as follows:SELECT *FROM yourTableName WHERE yourDateColumnName1 = ‘’yourDateValue’;To understand the above syntax, let us create a table. The query to create a table is as follows:mysql> create table selectDates -> ( -> Id int NOT NULL AUTO_INCREMENT, -> StartingDate date, -> EndingDate date, -> PRIMARY KEY(Id) -> ); Query OK, 0 rows affected (0.80 sec)Now you can insert some records in ...
Read MoreMySQL convert timediff output to day, hour, minute, second format?
To understand the MySQL convert timediff output to day, hour, minute, and second format, you need to use CONCAT() from MySQL.Let us create a table. The query to create a table is as follows:mysql> create table convertTimeDifferenceDemo -> ( -> Id int NOT NULL AUTO_INCREMENT, -> StartDate datetime, -> EndDate datetime, -> PRIMARY KEY(Id) -> ); Query OK, 0 rows affected (0.68 sec)Insert some records in the table using insert command. The query to insert record is as follows:mysql> insert into convertTimeDifferenceDemo(StartDate, ...
Read More