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
Articles by George John
Page 70 of 79
What is the difference between int and integer in MySQL?
The int is the synonym of integer in MySQL 5.0. Here is the demo display both int and integer internally represents int(11).Creating a table with int datatypemysql> create table IntDemo -> ( -> Id int -> ); Query OK, 0 rows affected (1.04 sec)Here is description of the table. The query is as followsmysql> desc IntDemo;The following is the output+-------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+-------+ | Id | int(11) | YES | | NULL | | +-------+---------+------+-----+---------+-------+ 1 row in ...
Read MoreConditional NOT NULL case MySQL?
For conditional NOT NULL case, you do not need to use and = operator. You need to use IS NULL and IS NOT NULL property because NULL is a special case in MySQL.To understand the conditional NOT NULL case, let us create a table. The query to create a table is as follows:mysql> create table ConditionalNotNullDemo -> ( -> Id int NOT NULL AUTO_INCREMENT, -> SendMessage longtext, -> PRIMARY KEY(Id) -> ); Query OK, 0 rows affected (0.62 sec)Insert some records in the table ...
Read MoreSort by order of values in a MySQL select statement IN clause?
You can use field() function with ORDER BY clause to sort by order of values. The syntax is as followsSELECT *FROM yourTableName WHERE yourColumnName IN(Value1, Value2, Value3, .......N); ORDER BY FIELD(yourColumnName ,Value1, Value2, Value3, .......N);To understand the above syntax, let us create a table. The query to create a table is as followsmysql> create table SelectInDemo -> ( -> StudentId int, -> StudentName varchar(100), -> StudentAge int -> ); Query OK, 0 rows affected (1.04 sec)Insert records in the table using insert command. The query is as followsmysql> insert into SelectInDemo values(1, 'Mike', 23); Query ...
Read MoreMySQL stored procedure return value?
To return a value from stored procedure, you need to use user defined session specific variable. Add @ symbol before variable name.For example, use @symbol for variable valido. The syntax for the same is as follows:SELECT @valido;Whenever you use select statement, you need to use @anyVariableName. The syntax is as follows:SELECT @anyVariableName;Here is the demo of stored procedure of return value. The query to create a stored procedure is as follows:mysql> create procedure ReturnValueFrom_StoredProcedure -> ( -> In num1 int, -> In num2 int, -> out valido int ...
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 output MySQL query results in CSV format and display it on the screen, not a file?
To get the output MySQL query result in CSV format, use concat(). The syntax is as follows −mysql> select concat(StudentId, ', ', StudentName, ', ', StudentAge) as CSVFormat from CSVFormatOutputs;To understand the above syntax, let us create a table. The query to create a table is as follows−mysql> create table CSVFormatOutputs -> ( -> StudentId int not null auto_increment, -> StudentName varchar(20), -> StudentAge int, -> PRIMARY KEY(StudentId) -> ); Query OK, 0 rows affected (1.15 sec)Insert some records in the table using insert command. The query is as follows −mysql> insert into CSVFormatOutputs(StudentName, ...
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 can a query multiply 2 cells for each row in MySQL?
You can use multiplication operator (*) between two cells. The syntax is as followsSELECT yourColumnName1, yourColumnName2, yourColumnName1*yourColumnName2 as ‘anyVariableName’ from yourTableName;To understand the above syntax, let us create a table. The query to create a table is as followsmysql> create table MultiplicationDemo -> ( -> FirstPrice int, -> SecondPrice int -> ); Query OK, 0 rows affected (0.63 sec)Now you can display all records from the table using insert command. The query is as followsmysql> insert into MultiplicationDemo values(10, 2); Query OK, 1 row affected (0.17 sec) mysql> insert into MultiplicationDemo values(4, 2); Query OK, ...
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 MoreHow to set sql_mode permanently in MySQL?
If you are using Windows Operating System, check your directory my.cnf or my.ini file.mysql> select @@datadir;The following is the output+---------------------------------------------+ | @@datadir | +---------------------------------------------+ | C:\ProgramData\MySQL\MySQL Server 8.0\Data\ | +---------------------------------------------+ 1 row in set (0.00 sec)Reach the above location ‘C:\ProgramData\MySQL\MySQL Server 8.0\Data\”. The screenshot is as follows for my.cnf fileOpen the my.cnf file and write the sql_mode="TRADITIONAL". The syntax is as followssql_mode="TRADITIONAL".After that start your server once again.
Read More