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
MySQL Articles
Page 141 of 355
MySQL Stored procedure to declare two values and perform mathematical operation
Let us first create a stored procedure −mysql> delimiter // mysql> create procedure declare_demo_sp() begin declare Value1 int; declare Value2 int; set Value1=100; set Value2=2000; select Value1,Value2,Value1*Value2 as MultiplicationResult; end // Query OK, 0 rows affected (0.00 sec) mysql> delimiter ;Call a stored procedure using CALL command −mysql> call declare_demo_sp();This will produce the following output −+--------+--------+----------------------+ | Value1 | Value2 | MultiplicationResult | +--------+--------+----------------------+ | 100 | 2000 | 200000 | +--------+--------+----------------------+ 1 row in set (0.00 sec) Query OK, 0 rows affected (0.00 sec)
Read MoreCheck if MySQL entry exists and if it does, how to overwrite other columns?
For this, use INSERT ON DUPLICATE KEY UPDATE command. Let us first create a table −mysql> create table DemoTable1891 ( FirstName varchar(20), UNIQUE KEY(FirstName) ); Query OK, 0 rows affected (0.00 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1891 values('Chris') on duplicate key update FirstName='Robert'; Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1891 values('David') on duplicate key update FirstName='Robert'; Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1891 values('Chris') on duplicate key update FirstName='Robert'; Query OK, 2 rows affected (0.00 sec)Display all records from the table ...
Read MoreSelect data and set value to boolean based on timestamp column in MySQL
For this, use IF(). Let us first see the current date −mysql> select curdate(); +------------+ | curdate() | +------------+ | 2019-12-10 | +------------+ 1 row in set (0.00 sec)Let us first create a table −mysql> create table DemoTable1890 ( DueDate timestamp ); Query OK, 0 rows affected (0.00 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1890 values('2017-12-10'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1890 values('2021-12-10'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1890 values('2018-04-24'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1890 ...
Read MoreMySQL query to update all entries with md5 version of name?
For this, you can use MD5(). Let us first create a table −mysql> create table DemoTable1887 ( Password text, HashPassword text ); Query OK, 0 rows affected (0.00 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1887(Password) values('John@9089'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1887(Password) values('90987_Carol'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1887(Password) values('656464_David_4343'); Query OK, 1 row affected (0.00 sec)Display some records in the table using insert command −mysql> select * from DemoTable1887;This will produce the following output −+-------------------+--------------+ | Password ...
Read MoreHow do you get whether a column is a primary key in MySQL?
To get whether a column is a primary key, use COLUMN_NAME and COLUMN_KEY='PRI'. With that, the entire syntax is as follows −select column_name, case when column_key= 'PRI' then 'yourMessage1' else ''yourMessage2' end as anyAliasName from information_schema.columns where table_schema =database() and `table_name` = yourTableName order by `table_name`, ordinal_position;To understand the above syntax, let us create a table −mysql> create table DemoTable1886 ( Id int NOT NULL, FirstName varchar(20), LastName varchar(20), Age int, DateOfBirth datetime, Education varchar(40), PRIMARY KEY(Id) ); Query OK, 0 rows affected (0.00 sec)Here is the query to get whether ...
Read MoreHow to select row when column must satisfy multiple value in MySQL?
For this, you can use GROUP BY HAVING clause along with IN(). Let us first create a table −mysql> create table DemoTable1885 ( FirstName varchar(20), Subject varchar(50) ); Query OK, 0 rows affected (0.00 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1885 values('John', 'MySQL'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1885 values('John', 'MongoDB'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1885 values('Carol', 'MySQL'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1885 values('David', 'Java'); Query OK, 1 row affected (0.00 sec)Display some ...
Read MoreMySQL IF/WHEN/ELSE/OR with ORDER BY FIELD
Let us first create a table −mysql> create table DemoTable1884 ( Marks int ); Query OK, 0 rows affected (0.00 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1884 values(55); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1884 values(97); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1884 values(79); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1884 values(87); Query OK, 1 row affected (0.00 sec)Display some records in the table using insert command −mysql> select * from DemoTable1884;This will produce the following output −+-------+ | ...
Read MoreCan we use a comma between MySQL SELECT statements?
Yes, we can do that. The syntaxes are as follows −Syntax1: select * from yourTableName1, yourTableName2; Syntax2: select * from yourTableName1 cross join yourTableName2;Both the above syntaxes give the same result.Let us first create a table −mysql> create table DemoTable1882 ( Id int ); Query OK, 0 rows affected (0.00 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1882 values(10); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1882 values(20); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1882 values(30); Query OK, 1 row affected (0.00 sec)Display all records ...
Read MoreHow to copy rows from one table to another in MySQL?
For this, use INSERT INTO SELECT statement. Let us first create a table −mysql> create table DemoTable1879 ( Id int, Name varchar(20) ); Query OK, 0 rows affected (0.00 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1879 values(101, 'Chris Brown'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1879 values(102, 'David Miller'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1879 values(103, 'Adam Smith'); Query OK, 1 row affected (0.00 sec)Display all records from the table using select statement −mysql> select * from DemoTable1879;This will produce the ...
Read MoreHow to select a date less than the current date with MySQL?
Let us first create a table −mysql> create table DemoTable1877 ( DueDate datetime ); Query OK, 0 rows affected (0.00 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1877 values('2019-12-10'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1877 values('2019-12-05'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1877 values('2019-12-07'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1877 values('2019-12-09'); Query OK, 1 row affected (0.00 sec)Display all records from the table using select statement −mysql> select * from DemoTable1877;This will ...
Read More