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 36 of 355
How to find all tables that contains columnA and columnB in MySQL?
To find specific column names, use information_schema.columns Here, I am using Id in place of columnA and Name in place of columnB −mysql> select table_name as TableNameFromWebDatabase -> from information_schema.columns -> where column_name IN ('Id', 'Name') -> group by table_name -> having count(*) = 3;This will produce the following output. Following are the table with columns Id and Name −+--------------------------+ | TableNameFromWebDatabase | +--------------------------+ | student | | distinctdemo | | secondtable | | groupconcatenatedemo ...
Read MoreMySQL query to count the dates and fetch repeated dates as well
To display the count, use aggregate function COUNT(*). Let us first create a table −mysql> create table DemoTable1321 -> ( -> ArrivalDatetime timestamp -> ); Query OK, 0 rows affected (0.50 sec)ExampleInsert some records in the table using insert command −mysql> insert into DemoTable1321 values(now()); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable1321 values('2019-01-10 12:34:00'); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable1321 values('2019-06-12 11:34:00'); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable1321 values('2019-06-12 04:50:00'); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable1321 values('2019-09-18 10:50:45'); Query OK, 1 ...
Read MoreHow to search dob in a table, which is in the yyyy-mm-dd structure and compare only with a specific yyyy format (year) in MySQL?
For this, use MySQL YEAR() as in the below syntax −select * from yourTableName where year(yourColumnName)=’yourYearValue’;Let us first create a table −mysql> create table DemoTable1322 -> ( -> DOB date -> ); Query OK, 0 rows affected (0.55 sec)ExampleInsert some records in the table using insert command −mysql> insert into DemoTable1322 values('1999-04-12'); Query OK, 1 row affected (0.68 sec) mysql> insert into DemoTable1322 values('2010-12-01'); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable1322 values('2015-03-09'); Query OK, 1 row affected (0.25 sec) mysql> insert into DemoTable1322 values('2007-05-24'); Query OK, 1 row affected (0.08 sec)Display all records from the table ...
Read MoreHow to detect if a MySQL database structure changed (not content)?
Let us first see an example and create a table −mysql> create table DemoTable ( StudentId int NOT NULL AUTO_INCREMENT PRIMARY KEY, StudentName varchar(40), StudentAge int, StudentMarks int ); Query OK, 0 rows affected (0.76 sec)Following is the query to know the database structure −mysql> show create table DemoTable;This will produce the following output −+---------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table ...
Read MoreChange only dates, ignoring time records in MySQL
To change only dates, not time, use the MySQL INTERVAL and YEAR. Since, we will be updating the records, therefore, use UPDATE and set a new value with INTERVAL.Let us see an example and create a table −mysql> create table DemoTable ( DueDate datetime ); Query OK, 0 rows affected (0.56 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('2017-08-12 10 :30 :45'); Query OK, 1 row affected (0.18 sec) mysql> insert into DemoTable values('2015-09-21 12 :00 :00'); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable values('2018-12-31 11 :45 :56'); Query ...
Read MoreHow to count rows from two tables in a single MySQL query?
Let us first create a table −mysql> create table DemoTable1 ( Name varchar(40) ); Query OK, 0 rows affected (0.81 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1 values('Chris'); Query OK, 1 row affected (0.48 sec) mysql> insert into DemoTable1 values('Robert'); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable1 values('Mike'); Query OK, 1 row affected (0.10 sec)Display all records from the table using select statement −mysql> select *from DemoTable1;This will produce the following output −+--------+ | Name | +--------+ | Chris | | Robert | | Mike | +--------+ ...
Read MoreMySQL query to generate row index (rank) in SELECT statement?
To generate a row index, use ROW_NUMBER(). Let us first create a table −mysql> create table DemoTable ( Name varchar(40) ); Query OK, 0 rows affected (0.49 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('Chris'); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable values('Chris'); Query OK, 1 row affected (0.22 sec) mysql> insert into DemoTable values('Chris'); Query OK, 1 row affected (0.10 sec) mysql> insert into DemoTable values('Robert'); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable values('Robert'); Query OK, 1 row affected (0.14 sec) mysql> insert into ...
Read MoreSearch text containing a new line in MySQL?
You can use REGEXP. Let us first create a table −mysql> create table DemoTable ( Name varchar(100) ); Query OK, 0 rows affected (1.61 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('JohnSmith'); Query OK, 1 row affected (0.73 sec) mysql> insert into DemoTable values('John Doe'); Query OK, 1 row affected (0.11 sec) mysql> insert into DemoTable values('DavidMiller'); Query OK, 1 row affected (0.24 sec) mysql> insert into DemoTable values('Carol Taylor'); Query OK, 1 row affected (0.27 sec)Display all records from the table using select statement −mysql> select *from DemoTable;This will produce the following ...
Read MoreMySQL query to count records that begin with specific letters
Let us first create a table −mysql> create table DemoTable775 ( FirstName varchar(100) ); Query OK, 0 rows affected (0.78 sec)Insert some records in the table using insert command −mysql> insert into DemoTable775 values('Adam') ; Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable775 values('Carol'); Query OK, 1 row affected (0.22 sec) mysql> insert into DemoTable775 values('John'); Query OK, 1 row affected (0.16 sec) mysql> insert into DemoTable775 values('Sam'); Query OK, 1 row affected (0.19 sec) mysql> insert into DemoTable775 values('Adan'); Query OK, 1 row affected (0.15 sec)Display all records from the table using select statement −mysql> select ...
Read MoreORDERBY word in MySQL?
To order by word in MySQL, you need to use ORDER BY FIELD(). Let us first create a table −mysql> create table DemoTable ( StudentId int NOT NULL AUTO_INCREMENT PRIMARY KEY, StudentFirstName varchar(20), StudentFavouriteSubject varchar(100) ); Query OK, 0 rows affected (0.52 sec)Insert some records in the table using insert command −mysql> insert into DemoTable(StudentFirstName, StudentFavouriteSubject) values('Larry', 'Java'); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable(StudentFirstName, StudentFavouriteSubject) values('Sam', 'C'); Query OK, 1 row affected (0.11 sec) mysql> insert into DemoTable(StudentFirstName, StudentFavouriteSubject) values('Bob', 'MongoDB'); Query OK, 1 row affected (0.17 sec) mysql> ...
Read More