Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Economics & Finance
To generate serial number i.e. row count in MySQL query, use the following syntax.SELECT @yourVariableName − = @yourVariableName+1 anyAliasName, yourColumnName1, yourColumnName2, yourColumnName3, ....N from yourTableName , (select @yourVariableName − = 0) as yourVariableName;To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table tblStudentInformation -> ( -> StudentName varchar(20), -> StudentAge int, -> StudentMathMarks int -> ); Query OK, 0 rows affected (0.68 sec)Insert some records in the table using insert command. The query is as follows −mysql> insert into tblStudentInformation values('Carol', ... Read More
There are various trees used for furniture, depending on their desired characteristics. For example, in order to get the durable material, some particular type of wood is used, while in order to get a luster and shine in the product, a different wood is used.Different Trees Used for FurnitureOak Trees: These trees are famous for hard-grade wood red, or white color. Its property of absorbing any color is evident from the fact that it has large annual rings inside.Maple Trees: The good thing about Maple is that it is less expensive than most of the alternatives of the same hardness ... Read More
To create column names to be used in a query, you need to use a user-defined variable with the set command. The syntax is as follows −SET @anyVariableName := ( SELECT CONCAT ( "SELECT", GROUP_CONCAT(CONCAT(" 1 as ", COLUMN_NAME) SEPARATOR ', '), " FROM DUAL") FROM INFORMATION_SCHEMA_COLUMNS WHERE TABLE_NAME= ‘yourTableName’ );Now prepare the statement using the PREPARE command. The syntax is as follows −PREPARE anyVariableName from @anyVariableName;Execute statement using EXECUTE command. The syntax is as follows −EXECUTE anyVariableName;Deallocate the prepared statement using DEALLOCATE command. The syntax is as follows −DEALLOCATE PREPARE anyVariableName; ... Read More
You can query between dates with the help of BETWEEN statement. The syntax is as follows −select *from yourTableName where yourColumnName between ‘yourStartingDate’ and curdate().Use curdate() or now(), both these functions will work. To understand the above syntax, let us create a table −mysql> create table BetweenDateDemo −> ( −> StartDate datetime −> ); Query OK, 0 rows affected (0.78 sec)Insert some records in the table with the help of the following query −mysql> insert into BetweenDateDemo values(date_add(now(), interval -1 year)); Query OK, 1 row affected (0.11 sec) mysql> insert ... Read More
You can remove trailing zeros using TRIM() function. The syntax is as follows.SELECT TRIM(yourColumnName)+0 FROM yourTableName;To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table removeTrailingZeroInDecimal -> ( -> Id int not null auto_increment, -> Amount decimal(5, 2), -> PRIMARY KEY(Id) -> ); Query OK, 0 rows affected (1.01 sec)Insert some records in the table using insert command. The query is as follows −mysql> insert into removeTrailingZeroInDecimal(Amount) values(405.50); Query OK, 1 row affected (0.22 sec) mysql> insert into removeTrailingZeroInDecimal(Amount) values(23.05); Query OK, ... Read More
To get the nth record from MySQL query, you can use LIMIT. The syntax is as follows −select *from yourTableName order by yourColumnName limit n, 1;To understand the above syntax, let us create a table. The following is the query to create a table −mysql> create table NthRecordDemo −> ( −> Id int, −> Name varchar(200) −> ); Query OK, 0 rows affected (0.92 sec)Insert some records in the table using the following query −mysql> insert into NthRecordDemo values(100, 'John'); Query OK, 1 row affected (0.09 sec) ... Read More
In PHP, the empty string equals to a NULL value, but in MySQL, the case is the different i.e. empty string is not equal to NULL value. To understand the above syntax, let us create a column with NOT NULL constraint while you can insert an empty string.Let us create a table. The query to create a table is as follows −mysql> create table EmptyStringNotNullDemo -> ( -> Id int NOT NULL AUTO_INCREMENT, -> Name varchar(10) not null, -> PRIMARY KEY(Id) -> ); Query OK, 0 rows affected (0.83 sec)Now you can insert some records ... Read More
You can OR two like statements using the following syntax −SELECT *FROM yourTableName WHERE (yourColumnName like '%yourValue1%' OR yourColumnNamelike '%yourValue2%') AND yourColumnName = yourValue;To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table ORLikeDemo -> ( -> Id int not null auto_increment, -> FirstName varchar(15), -> LastName varchar(15), -> Primary Key(Id) -> ); Query OK, 0 rows affected (1.19 sec)Insert some records in the table using insert command. The query is as follows −mysql> insert into ORLikeDemo(FirstName, LastName) values('John', 'Smith'); Query OK, ... Read More
Home is where the heart is! And one feels like calling a house as a home if the home is well decorated or is made attractive using flowers or fragrances that please the senses. But, gone are the days when only flowers or bonsai plants could decorate home. The latest trend is the use of electronic devices for home decor.Some of them areClocks: Clocks were used earlier just to note the time. But, these have actually become a luxury symbol now. Not only the brand makes it look costly, but also the design. The other day, I went to a ... Read More
No, MySQL won’t improve search performance whenever you have leading wildcards because MySQL will be unable to use the index. If you change to ‘anyLetter%’ then it will be able to use indexThe below syntax is better to use with trailing wildcards. The syntax is as follows −SELECT *FROM yourTableName WHERE yoorColumnName LIKE ‘anyLetter%’;The query to create a table is as follows −mysql> create table TrailingWildCardDemo -> ( -> Id int NOT NULL AUTO_INCREMENT, -> Name Varchar(20), -> PRIMARY KEY(Id) -> ); Query OK, 0 ... Read More