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 149 of 355
How to GRANT SELECT ON all tables in all databases on a server with MySQL?
For this, you can use GRANT SELECT statement as in the below syntax −GRANT SELECT ON *.* TO 'yourUserName'@'yourHostName';First list all the user names along with host −mysql> select user, host from mysql.user;This will produce the following output −+------------------+-----------+ | user | host | +------------------+-----------+ | Bob | % | | Charlie | % | | Robert | % | | User2 ...
Read MoreHow to select and display a list of values in one column that are available in two different MySQL columns?
For this, use UNION ALL. Let us first create a table −mysql> create table DemoTable1813 ( Name1 varchar(20), Name2 varchar(20) ); Query OK, 0 rows affected (0.00 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1813 values('John', 'Chris'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1813 values('Adam', 'Robert'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1813 values('Mike', 'Sam'); Query OK, 1 row affected (0.00 sec)Display all records from the table using select statement −mysql> select * from DemoTable1813;This will produce the following ...
Read MoreHow to extract column name and type from MySQL?
To extract column name and type, use INFORMATION_SCHEMA.COLUMNS −select concat(column_name, '=', data_type) as anyAliasName from information_schema.columns where table_schema= yourDatabaseName and table_name= yourTableName;Let us first create a table −mysql> create table DemoTable1812 ( Id int, FirstName varchar(20), Age int, isMarried boolean, status ENUM('ACTIVE', 'INACTIVE') ); Query OK, 0 rows affected (0.00 sec)Here is the query to extract column name and type from MySQL:mysql> select concat(column_name, '=', data_type) as COLUMNNAMEANDTYPE from information_schema.columns where table_schema= 'web' and table_name= 'DemoTable1812';This will produce the following output −+-------------------+ | COLUMNNAMEANDTYPE ...
Read MoreAdding unique constraint to ALTER TABLE in MySQL
Let us first create a table −mysql> create table DemoTable1811 ( FirstName varchar(20), LastName varchar(20) ); Query OK, 0 rows affected (0.00 sec)Here is the query to add indexmysql> alter table DemoTable1811 ADD UNIQUE unique_index_first_last_name(FirstName, LastName); Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0Insert some records in the table using insert command −mysql> insert into DemoTable1811 values('John', 'Smith'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1811 values('John', 'Doe'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1811 values('Adam', 'Smith'); Query OK, 1 ...
Read MoreFetch a specific column value (name) in MySQL
To fetch a specific column value, use LIKE clause. Let us first create a table −mysql> create table DemoTable1809 ( Name varchar(20) ); Query OK, 0 rows affected (0.00 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1809 values('John'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1809 values('David'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1809 values('Mike'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1809 values('Johnson'); Query OK, 1 row affected (0.00 sec)Display all records from the table using select statement ...
Read MoreDisplay all the column values in a single row separated by comma in MySQL?
For this, use GROUP_CONCAT() and CONCAT(). Let us first create a table −mysql> create table DemoTable1807 ( Id int ); Query OK, 0 rows affected (0.00 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1807 values(101); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1807 values(102); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1807 values(103); Query OK, 1 row affected (0.00 sec)Display all records from the table using select statement −mysql> select * from DemoTable1807;This will produce the following output −+------+ | Id | ...
Read MoreHow to select rows in MySQL that are >= 1 DAY from the current date?
To get data greater than equal to 1 day from the current date, use the concept of INTERVAL in MySQL.The current date is as follows −mysql> select curdate(); +------------+ | curdate() | +------------+ | 2019-11-29 | +------------+ 1 row in set (0.00 sec)We will first create a table −mysql> create table DemoTable1806 ( DueDate datetime ); Query OK, 0 rows affected (0.00 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1806 values('2019-11-28'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1806 values('2019-11-29'); Query OK, 1 row affected (0.00 ...
Read MoreHow to use CONTAINS() with CURDATE in MySQL?
For this, you can use CONCAT() with CURDATE().There is no function with the name CONTAINS() in MySQL.Let us first get the current date. The current date is as follows −mysql> select curdate();This will produce the following output −+------------+ | curdate() | +------------+ | 2019-11-28 | +------------+ 1 row in set (0.00 sec)We will now create a table −mysql> create table DemoTable1803 ( Name varchar(20), JoiningYear varchar(20) ); Query OK, 0 rows affected (0.00 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1803 values('Chris', '2020/2017'); Query OK, 1 row ...
Read MoreMySQL group by for separate id without using GROUP BY to remove duplicate column row?
For this, you can use DISTINCT keyword. Let us first create a table −mysql> create table DemoTable1801 ( Name varchar(20), Score int ); Query OK, 0 rows affected (0.00 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1801 values('John', 98); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1801 values('John', 98); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1801 values('John', 99); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1801 values('Carol', 99); Query OK, 1 row affected (0.00 sec)Display all records ...
Read MoreFind sum with MySQL SUM() and give aliases for column heading
For alias, use the following syntax wherein we are display an alias name −select sum(yourColumnName) as anyAliasName from yourTableName;Let us first create a table −mysql> create table DemoTable1800 ( Salary int ); Query OK, 0 rows affected (0.00 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1800 values(18000); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1800 values(32000); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1800 values(50000); Query OK, 1 row affected (0.00 sec)Display all records from the table using select statement −mysql> select * ...
Read More