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 322 of 341
MySQL DATE_FORMAT '%M' is used for short month?
The %M Date Format is not used for displaying short months like Jan for January, Feb for February, etc. You need to use DATE_FORMAT() function with %b format for short month. The syntax is as follows:SELECT DATE_FORMAT(yourColumnName, '%d-%b-%y') AS anyVariableName FROM yourTableName;To understand the above syntax, let us create a table. The query to create a table is as follows:mysql> create table DateFormatMonthDemo -> ( -> Id int NOT NULL AUTO_INCREMENT, -> ShippingDate date, -> PRIMARY KEY(Id) -> ); Query OK, 0 rows affected (0.63 sec)Insert some records in the table using insert command. The ...
Read MoreWhy we mention in MySQL WHERE 1=0?
The condition 1=0 can be used to stop the query from returning any rows. It returns empty set.The syntax is as follows:SELECT *FROM yourTableName WHERE 1=0;To understand the above syntax, let us create a table. The query to create a table is as follows:mysql> create table ConditionDemo -> ( -> Id int NOT NULL AUTO_INCREMENT, -> Name varchar(10), -> PRIMARY KEY(Id) -> ); 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 ConditionDemo(Name) values('Larry'); Query OK, 1 row affected (0.10 sec) mysql> ...
Read MoreCan MySQL INT type be non-zero NULL?
You can set the INT column to value NULL.The column INT type a nullable column. The syntax is as follows:INSERT INTO yourTableName(yourIntColumnName) values(NULL);To understand the above syntax, let us create a table. The query to create a table is as follows:mysql> create table nullableIntDemo -> ( -> Id int NOT NULL AUTO_INCREMENT, -> Price int, -> PRIMARY KEY(Id) -> ); Query OK, 0 rows affected (0.80 sec)Insert the record as NULL for a int column ‘Price’. The query is as follows:mysql> insert into nullableIntDemo(Price) values(NULL); Query OK, 1 row affected (0.11 sec) mysql> insert into ...
Read MoreSELECT not null column from two columns in MySQL?
There are lots of ways to select NOT NULL column from two columns. The syntaxes are as follows:Case 1: Use IFNULL() function.The syntax is as follows:SELECT IFNULL(yourColumnName1, yourColumnName2) as anyVariableName from yourTableName;Case 2: Use coalesce() function.The syntax is as follows:SELECT COALESCE(yourColumnName1, yourColumnName2) as anyVariableName from yourTableName;Case 3: Use CASE statement.The syntax is as follows:SELECT CASE WHEN yourColumnName1 IS NOT NULL THEN yourColumnName1 ELSE yourColumnName2 END AS anyVariableName FROM yourTableName;Case 4: Use only IF().The syntax is as follows:SELECT IF (yourColumnName1 ISNULL, yourColumnName2, yourColumnName1) AS NotNULLValue FROM SelectNotNullColumnsDemo;To understand the above syntax, let us create a table. The query to create a ...
Read MoreAre quotes around tables and columns in a MySQL query really necessary?
If your table name or column name are any reserved words then you need to use quotes around table name and column name in a MySQL query. You need to use backticks around table name and column name. The syntax is as follows:SELECT *FROM `table` where `where`=condition;Here is the query to create a table without quotes with reserved words. You will get an error since they are predefined reserved words. The error is as follows:mysql> create table table -> ( -> where int -> ); ERROR 1064 (42000): You have an error in your SQL syntax; check ...
Read MoreHow do I add to each row in MySQL?
You can add a value to each row in MySQL using UPDATE command.Let us see when your column is an integer. The syntax is as follows:UPDATE yourTableName SET yourIntegerColumnName = yourIntegerColumnName+anyValue; UPDATE yourTableName SET yourIntegerColumnName = anyValue WHERE yourIntegerColumnName IS NULL;You can add a value for a date column name. The syntax is as follows:UPDATE yourTableName SET yourDateColumnName = DATEADD(yourDateColumnName, INTERVAL anyIntegerMonth)To understand the above syntax, let us create a table. The query to create a table is as follows:mysql> create table addEachRowValue -> ( -> Id int NOT NULL AUTO_INCREMENT, -> Amount int, -> ShippingDate ...
Read MoreMySQL SELECT DISTINCT and count?
You need to use GROUP BY command with aggregate function count(*) from MySQL to achieve this. The syntax is as follows:SELECT yourColumnName, COUNT(*) AS anyVariableNameFROM yourTableName GROUP BY yourColumnName;To understand the above syntax, let us create a table. The query to create a table is as follows:mysql> create table selectDistinct_CountDemo -> ( -> Id int NOT NULL AUTO_INCREMENT, -> Name varchar(10), -> AppearanceId int, -> PRIMARY KEY(Id) -> ); Query OK, 0 rows affected (0.63 sec)Insert some records in the table using insert command. The query is as follows:mysql> insert into selectDistinct_CountDemo(Name, AppearanceId) values('Larry', ...
Read MoreChange value from 1 to Y in MySQL Select Statement?
You can use IF() from MySQL to change value from 1 to Y. The syntax is as follows:SELECT IF(yourColumnName, ’Y’, yourColumnName) as anyVariableName FROM yourTableName;To understand the above syntax, let us create a table. The query to create a table is as follows:mysql> create table changeValuefrom1toY -> ( -> Id int NOT NULL AUTO_INCREMENT, -> isValidAddress tinyint(1), -> PRIMARY KEY(Id) -> ); Query OK, 0 rows affected (0.76 sec)Now you can insert some records in the table using insert command. The query is as follows:mysql> insert into changeValuefrom1toY(isValidAddress) values(1); Query OK, 1 row affected (0.22 ...
Read MoreChange value from 1 to Y in MySQL Select Statement using CASE?
You can use CASE from MySQL to change value from 1 to Y. Let us first create a table. The query to create a table is as follows:mysql> create table changeValuefrom1toY -> ( -> Id int NOT NULL AUTO_INCREMENT, -> isValidAddress tinyint(1), -> PRIMARY KEY(Id) -> ); Query OK, 0 rows affected (0.76 sec)Now you can insert some records in the table using insert command. The query is as follows:mysql> insert into changeValuefrom1toY(isValidAddress) values(1); Query OK, 1 row affected (0.22 sec) mysql> insert into changeValuefrom1toY(isValidAddress) values(0); Query OK, 1 row affected (0.16 sec) mysql> insert ...
Read MoreHow to convert Varchar to Double in SQL?
You can convert varchar to double using CAST() function. The syntax is as follows:SELECT yourColumnName1, yourColumnName2, ......N, CAST(yourColumnName AS DECIMAL(TotalDigit, DigitAfterDecimalPoint)) anyVariableName FROM yourtableName ORDER BY anyVariableName DESC;To understand the above syntax, let us create a table. The query to create a table is as follows:mysql> create table VarcharToDouble -> ( -> Id int NOT NULL AUTO_INCREMENT, -> Name varchar(10), -> Amount varchar(10) , -> PRIMARY KEY(Id) -> ); Query OK, 0 rows affected (0.67 sec)Insert some records in the table using insert command. The query is as follows:mysql> insert into VarcharToDouble(Name, Amount) values('John', ...
Read More