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
Articles by Chandu yadav
Page 71 of 81
How 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 MoreHow to specify Decimal Precision and scale number in MySQL database using PHPMyAdmin?
You need to select a database when you are creating a table. Right now, I have a sample database. The snapshot is as follows:Now you need to give the table name as well as the number of columns you want:After that you need to press Go button. Now, the following section would be visible:The DECIMAL requires two parameter i.e. Total Number of Digit and second one is DigitAfterDecimalPoint.The structure of DECIMAL is as follows:DECIMAL(X, Y)Here, X is TotalNumberOfDigit and Y is DigitAfterDecimalPoint.Let us see an example:DECIMAL(6, 4)Above, we will be having 6 digit sand 2 digit safter decimal point. For ...
Read MoreSelect last 20 records ordered in ascending order in MySQL?
To select last 20 records in ascending order, you can use subquery LIMIT clause. The syntax is as followsSELECT *FROM ( SELECT *FROM yourTableName ORDER BY yourColumnName desc limit 20 ) anyVariableName order by anyVariableName.yourColumnName;To understand the above syntax, let us create a table. The query to create a table is as followsmysql> create table ProductInformation -> ( -> ProductId int, -> ProductName varchar(100), -> ProductPrice int -> ); Query OK, 0 rows affected (0.50 sec)Insert some records in the table using insert command. The query is as followsmysql> insert into ProductInformation values(101, 'Product-1', ...
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 MoreCreate a procedure in MySQL with parameters?
You can create a parameter using IN and OUT. IN is used to take input parameter and OUT can be used for output.The syntax is as followsDELIMITER // CREATE PROCEDURE yourProcedureName(IN yourParameterName dataType, OUT yourParameterName dataType ) BEGIN yourStatement1; yourStatement2; . . N END; // DELIMITER ;First, we will create a table. The query to create a table is as followsmysql> create table SumOfAll -> ( -> Amount int -> ); Query OK, 0 rows affected (0.78 sec)Insert some records in the table using insert command. The query ...
Read MoreHow to find nth highest value of a MySQL column?
To find the nth highest value of a column, you need to use ORDER BY DESC with LIMIT clause. If you want the second highest value of a column, use the below syntax:SELECT *FROM yourTableName ORDER BY DESC yourColumnName LIMIT 1, 1;If you want the fourth highest value of a column, use the below syntax:SELECT *FROM yourTableName ORDER BY DESC yourColumnName LIMIT 3, 1;If you want the first highest value of a column, use the below syntax:SELECT *FROM yourTableName ORDER BY DESC yourColumnName LIMIT 1;As discussed in the above syntax, you need to change only in LIMIT clause. To understand ...
Read MoreCheck if a string contains numbers in MySQL?
To check a string contains numbers, you can use regexp i.e. Regular Expressions. The syntax is as follows −SELECT *FROM yourTableName where yourColumnName REGEXP ‘[0-9]’;To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table StringContainsNumber -> ( -> Id int not null auto_increment, -> Words text, -> primary key(Id) -> ); Query OK, 0 rows affected (0.53 sec)Insert some records in the table using insert command. The query is as follows −mysql> insert into StringContainsNumber(Words) values('He12345llo'); Query OK, 1 row affected (0.19 sec) ...
Read MoreHow do you OR two MySQL LIKE statements?
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 MoreSHOW TABLE statement with multiple LIKE values in MySQL?
You can use WHERE clause and OR operator to show table with multiple LIKE. The syntax is as follows:show table from yourDatabaseName where tables_in_yourDatabaseName Like ‘%anyTableName%’ or tables_in_yourDatabaseName Like ‘%anyTableName2%’ or tables_in_yourDatabaseName Like ‘%anyTableName3%’ . . . . or tables_in_yourDatabaseName Like ‘%anyTableNameN%’In the above syntax, only the table name in the database is displayed.Here the database ‘test’ and the tables in the same database is considered. The query to show tables with multiple LIKE is as follows -mysql> show tables from test -> where tables_in_test like '%userrole%' -> or tables_in_test like '%view_student%' -> or tables_in_test like '%wholewordmatchdemo%';The following is the ...
Read MoreHow can I merge two MySQL tables?
To merge two MySQL tables, use the following syntax −INSERT IGNORE INTO yourTableName1 select *from yourTableName2;We will create two tables with some records. After that the merge process will begin using the above syntax.Creating first table −mysql> create table MergeDemo1 -> ( -> id int, -> primary key(id), -> Name varchar(200) -> ); Query OK, 0 rows affected (1.00 sec)Inserting records into the table −mysql> insert into MergeDemo1 values(1, 'John'); Query OK, 1 row affected (0.21 sec)Displaying records from the tablemysql> select *from MergeDemo1;The following ...
Read More