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 171 of 355
Count NOT NULL values from separate tables in a single MySQL query
To count values from separate tables, the syntax is as follows −Select ( select count(yourColumnName) from yourTableName1) as anyAliasName1, ( select count(yourColumnName) from yourTableName2) as anyAliasName2;Let us first create a table −mysql> create table DemoTable1 -> ( -> Id int -> ); Query OK, 0 rows affected (1.06 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1 values(1); Query OK, 1 row affected (0.16 sec) mysql> insert into DemoTable1 values(NULL); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable1 values(2); Query OK, 1 row affected (0.34 sec) ...
Read MoreReorder keys after deleting a record from MySQL table?
For this, use UPDATE command with some mathematical calculations. To delete an id, use the DELETE. Let us first create a table −mysql> create table DemoTable1476 -> ( -> Id int -> ); Query OK, 0 rows affected (0.81 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1476 values(10); Query OK, 1 row affected (0.18 sec) mysql> insert into DemoTable1476 values(20); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable1476 values(30); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable1476 values(40); Query OK, 1 row affected (0.12 sec)Display all ...
Read MoreA single MySQL select query on two tables is possible?
Yes, it is possible. Following is the syntax −select * from yourTableName1, yourTableName2;Let us first create a table −mysql> create table DemoTable1 -> ( -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY -> ); Query OK, 0 rows affected (0.54 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1 values(), (), (); Query OK, 3 rows affected (0.14 sec) Records: 3 Duplicates: 0 Warnings: 0Display all records from the table using select statement −mysql> select * from DemoTable1;This will produce the following output −+----+ | Id | +----+ | 1 | | 2 ...
Read MoreFetch a value between different values in MySQL
Use MySQL BETWEEN to fetch a value between different values. Let us first create a table −mysql> create table DemoTable1473 -> ( -> EmployeeCode varchar(20) -> ); Query OK, 0 rows affected (0.47 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1473 values('EMP_120'); Query OK, 1 row affected (0.25 sec) mysql> insert into DemoTable1473 values('EMP_125'); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable1473 values('EMP_30'); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable1473 values('EMP_130'); Query OK, 1 row affected (0.10 sec)Display all records from the table using select ...
Read MoreHow to convert char field to datetime field in MySQL?
Let us first create a table. Here, we have declared dates in char type −mysql> create table DemoTable1472 -> ( -> ShippingDate char(35) -> ); Query OK, 0 rows affected (0.46 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1472 values('12/31/2017 10:50'); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable1472 values('01/10/2018 12:00'); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable1472 values('03/20/2019 09:30'); Query OK, 1 row affected (0.14 sec)Display all records from the table using select statement −mysql> select * from DemoTable1472;This will produce the following output ...
Read MoreMySQL query to calculate the days between two dates from different columns but similar rows
Let us first create a table −mysql> create table DemoTable1471 -> ( -> EmployeeJoiningDate date, -> EmployeeRelievingDate date -> ); Query OK, 0 rows affected (0.57 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1471 values('2018-06-21', '2018-12-21'); Query OK, 1 row affected (0.10 sec) mysql> insert into DemoTable1471 values('2017-01-19', '2019-01-31'); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable1471 values('2015-12-31', '2016-03-01'); Query OK, 1 row affected (0.11 sec)Display all records from the table using select statement −mysql> select * from DemoTable1471;This will produce the following output −+---------------------+-----------------------+ | EmployeeJoiningDate | ...
Read MoreSelect a field and if it's null, select another with MySQL?
For this, use COALESCE(). Let us first create a table −mysql> create table DemoTable1470 -> ( -> FirstName varchar(20), -> Age int -> ); Query OK, 0 rows affected (0.57 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1470 values('Robert', 23); Query OK, 1 row affected (0.09 sec) mysql> insert into DemoTable1470 values('Bob', NULL); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable1470 values(NULL, 25); Query OK, 1 row affected (0.15 sec)Display all records from the table using select statement −mysql> select * from DemoTable1470;This will produce the following output ...
Read MoreRemove index from a MySQL table
To remove index from a MySQL table, the syntax is as follows −alter table yourTableName drop index `yourIndexName`;Let us first create a table −Mysql> create table DemoTable1469 -> ( -> StudentId int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> StudentName varchar(40), -> StudentAge int -> ); Query OK, 0 rows affected (0.78 sec)Following is the query to add index on column name −mysql> create index `Student Name_Index` on DemoTable1469(StudentName); Query OK, 0 rows affected (0.33 sec) Records: 0 Duplicates: 0 Warnings: 0Let us check the table description −mysql> desc DemoTable1469;This will produce the following output −+-------------+-------------+------+-----+---------+----------------+ ...
Read MoreDelete rows with duplicate and similar content & get row with maximum number with MySQL select statement?
Let us first create a table −mysql> create table DemoTable1468 -> ( -> Id int, -> Name varchar(20), -> Age int -> ); Query OK, 0 rows affected (1.21 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1468 values(100, 'Chris', 23); Query OK, 1 row affected (0.19 sec) mysql> insert into DemoTable1468 values(101, 'Bob', 25); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable1468 values(102, 'David', 30); Query OK, 1 row affected (0.11 sec) mysql> insert into DemoTable1468 values(100, 'Chris', 23); Query OK, 1 row affected (0.35 sec) mysql> ...
Read MoreGet rows that have common value from the same table with different id in MySQL
For this, you can use GROUP BY HAVING clause. Let us first create a table −mysql> create table DemoTable1467 -> ( -> Id int, -> Name varchar(20) -> ); Query OK, 0 rows affected (0.64 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1467 values(100, 'Chris'); Query OK, 1 row affected (0.18 sec) mysql> insert into DemoTable1467 values(110, 'David'); Query OK, 1 row affected (0.10 sec) mysql> insert into DemoTable1467 values(120, 'Mike'); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable1467 values(100, 'Chris'); Query OK, 1 row affected (0.12 sec) ...
Read More