Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
For the lowest values in a MySQL column, use the MIN() method and for highest, use the MAX() method. Let us first create a table −mysql> create table DemoTable ( CustomerName varchar(20), ProductAmount int ) ; Query OK, 0 rows affected (1.03 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('Chris', 3599); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable values('David', 7843); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable values('Mike', 97474); Query OK, 1 row affected (0.20 sec) mysql> insert into DemoTable values('Bob', 65884); Query OK, ... Read More
Use MySQL IN() to avoid too many OR statements. Let us first create a table −mysql> create table DemoTable ( Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, Name varchar(40) ); Query OK, 0 rows affected (0.89 sec)Insert some records in the table using insert command −mysql> insert into DemoTable(Name) values('Chris'); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable(Name) values('Robert'); Query OK, 1 row affected (0.21 sec) mysql> insert into DemoTable(Name) values('Mike'); Query OK, 1 row affected (0.10 sec) mysql> insert into DemoTable(Name) values('Sam'); Query OK, 1 row affected (0.09 sec) mysql> insert into DemoTable(Name) values('David'); ... Read More
To select different values on the basis of condition, use CASE statement. Let us first create a table −mysql> create table DemoTable ( Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, Name varchar(40), Score int ) ; Query OK, 0 rows affected (0.54 sec)Insert some records in the table using insert command −mysql> insert into DemoTable(Name, Score) values('Chris', 45); Query OK, 1 row affected (0.24 sec) mysql> insert into DemoTable(Name, Score) values('David', 68); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable(Name, Score) values('Robert', 89); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable(Name, Score) ... Read More
To randomly select rows, use ORDER BY RAND() with LIMIT. Use DISTINCT for distinct rows. Let us first see an example and create a table −mysql> create table DemoTable ( Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, Name varchar(40) ); Query OK, 0 rows affected (0.54 sec)Insert some records in the table using insert command −mysql> insert into DemoTable(Name) values('John Doe'); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable(Name) values('Chris Brown'); Query OK, 1 row affected (0.11 sec) mysql> insert into DemoTable(Name) values('Adam Smith'); Query OK, 1 row affected (0.09 sec) mysql> insert into DemoTable(Name) ... Read More
Such errors arise when you avoid using the DELIMITER concept. Let us see an example and run a query for stored procedure −mysql> DELIMITER // mysql> CREATE PROCEDURE correct_procedure() BEGIN SELECT 'Hello MySQL !!!'; END // Query OK, 0 rows affected (0.12 sec) mysql> DELIMITER ;Following is the syntax to call the stored procedure −call yourStoredProcedureName();Call the stored procedure using CALL command −mysql> call correct_procedure();This will produce the following output −+-----------------+ | Hello MySQL !!! | +-----------------+ | Hello MySQL !!! | +-----------------+ 1 row in set (0.00 sec) Query OK, 0 rows affected (0.02 sec)
To set new delay time, use INTERVAL and update the column wth SETa clause and UPDATE command. Let us first create a table −mysql> create table DemoTable ( DelayTime time ); Query OK, 0 rows affected (1.21 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('11 :30 :10'); Query OK, 1 row affected (0.21 sec) mysql> insert into DemoTable values('12 :40 :00'); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable values('05 :45 :24'); Query OK, 1 row affected (0.10 sec) mysql> insert into DemoTable values('09 :00 :10'); Query OK, 1 row ... Read More
Let us first create a table −mysql> create table DemoTable ( Id int NOT NULL AUTO_INCREMENT PRIMARY KEY , Title text ); Query OK, 0 rows affected (0.88 sec)Insert some records in the table using insert command −mysql> insert into DemoTable(Title) values('This is; a; MySQL;Tutorial'); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable(Title) values('Java is; an;Object Oriented'); Query OK, 1 row affected (0.16 sec) mysql> insert into DemoTable(Title) values('MongoDB ; is;a; database'); Query OK, 1 row affected (0.12 sec)Display all records from the table using select statement −mysql> select *from DemoTable;This will produce the following ... Read More
To find column names, use information_schema.columns. Following is the syntax −select distinct table_name from information_schema.columns where column_name like '%yourSearchValue%' and table_schema=database();Let us implement the above syntax in order to find column names across various table. Here, we want only table names with a specific column name word “Client” −mysql> select distinct table_name from information_schema.columns where column_name like '%Client%' and table_schema=database();This will produce the following output −+----------------+ | table_name | +----------------+ | demotable449 | | demotable450 | | demotable461 | | demotable517 | | demotable529 | ... Read More
To add characters to an existing int column values, use MySQL CONCAT(). Let us first create a table −mysql> create table DemoTable ( Amount int ); Query OK, 0 rows affected (1.44 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values(709); Query OK, 1 row affected (0.67 sec) mysql> insert into DemoTable values(34560); Query OK, 1 row affected (0.30 sec) mysql> insert into DemoTable values(90854); Query OK, 1 row affected (0.28 sec) mysql> insert into DemoTable values(3456); Query OK, 1 row affected (0.15 sec)Display all records from the table using select statement −mysql> select ... Read More
To pull records for the last 60 minutes, use MySQL INTERVAL as shown in the below syntax −select *from yourTableName where yourColumnName > now() - interval 60 minute;Let us first create a table −mysql> create table DemoTable ( ArrivalTime datetime ); Query OK, 0 rows affected (0.61 sec)Let us find the current date −mysql> select now(); +-----------------------+ | now() | +-----------------------+ | 2019-09-17 00 :04 :54 | +-----------------------+ 1 row in set (0.00 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('2019-09-16 08 :00 :00'); Query ... Read More