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
MySQL Articles
Page 331 of 355
How to order DESC by a field, but list the NULL values first?
To order by a field and list the NULL values first, you need to use the following syntax. This will order in descending order −select yourColumnName from yourTableName group by yourColumnName is null desc, yourColumnName desc;To understand the above syntax, let us first create a table −mysql> create table OrderByNullFirstDemo −> ( −> StudentId int −> ); Query OK, 0 rows affected (0.56 sec)Insert some records in the table with the help of insert command. The query is as follows −mysql> insert into OrderByNullFirstDemo values(100); Query OK, 1 row affected (0.13 sec) mysql> insert into OrderByNullFirstDemo ...
Read MoreSplit the left part of a string by a separator string in MySQL?
You can use substring_index() function from MySQL to split the left part of a string. The syntax is as follows −SELECT yourColumnName1, .....N, SUBSTRING_INDEX(yourColumnName, ’yourSeperatorSymbol’, 1) as anyVariableName from yourTableName;The value 1 indicates that you can get left part of string. To check the above syntax, let us create a table. The query to create a table is as follows −mysql> create table LeftStringDemo -> ( -> Id int, -> Words varchar(100) -> ); Query OK, 0 rows affected (0.92 sec)Insert some records in the table using insert ...
Read MoreWhat 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 MoreMySQL stored-procedure: out parameter?
Here is a stored procedure that takes one parameter for input (IN) and second parameter for output (OUT)mysql> delimiter // mysql> create procedure Sp_SQRT(IN Number1 INT, OUT Number2 FLOAT) -> Begin -> set Number2=sqrt(Number1); -> end; -> // Query OK, 0 rows affected (0.24 sec) mysql> delimiter ;Call the stored procedure and send the value to the user variable. The syntax is as followsCALL yourStoredProcedureName(anyIntegerValue, @anyVariableName);Check what value is stored in the variable @anyVariableName. The syntax is as followsSELECT @anyVariableName;Created the stored procedure with the name ‘Sp_SQRT’. The ...
Read MoreGenerate table DDL via a query on MySQL and SQL Server?
The DDL stands for Data Definition Language. To generate the table DDL via query, you can use show create command.The syntax is as followsSHOW CREATE TABLE yourTableName;The above syntax is MySQL specific. Suppose, we have a table with the name ‘DDLOfTableStudent’.First, create a table with the name ‘DDLOfTableStudent’. The query to create a table is as followsmysql> create table DDLOfTableStudent -> ( -> StudentId int, -> StudentFirstName varchar(100), -> StudentLastName varchar(100), -> StudentAddress varchar(200), -> StudentAge int, -> StudentMarks ...
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 MoreHow to display records vertically in MySQL command line?
You can use backward slash followed by G i.e. \G instead of semicolon(;). The syntax is as follows to show records vertically in MySQL command line.SELECT *FROM yourTableName\GTo understand the above syntax, let us create a table. The query to create a table is as followsmysql> create table showRecordsVertically -> ( -> Id int, -> Name varchar(100), -> Age int -> ); Query OK, 0 rows affected (2.10 sec)Insert some records in the table using insert command. The query is as followsmysql> insert into showRecordsVertically ...
Read MoreHow to declare a variable in MySQL for a normal query?
You can declare a variable using @anyVariablename which is a session variable. To create a session variable, you need to use SET command.The syntax is as followsSET @anyVariableName:=anyValue;You can declare a local variable using DECLARE command. The syntax is as followsDECLARE yourVariableName datatypeYou can set the default value at the time of creating a variable. The syntax is as followsDECLARE yourVariableName datatype default ‘yourValue’Here is the demo of session variable. To understand it, let us create a table.The query to create a table is as followsmysql> create table SessionVariableDemo -> ( -> EmployeeId varchar(10), -> EmployeeName varchar(30), ...
Read MoreCombine INSERT, VALUES, and SELECT in MySQL
You can combine the insert, values and select statement using below syntaxinsert into yourFirstTableName(yourColumnName1, yourColumnName2, .......N) select yourColumnName1, yourColumnName2, .......N from yourSecondTableName where yourCondition;To understand the above syntax, let us create two tables in which first table will get the record from the second table.Let us create the first table without any records. The query to create a table is as followsmysql> create table CombiningInsertValuesSelect -> ( -> EmployeeId varchar(10), -> EmployeeName varchar(100), -> EmployeeAge int -> ); Query OK, 0 rows affected (6.95 sec)Now you can create the second table with some records. The ...
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 More