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
Database Articles
Page 223 of 547
Can I query how much disk space certain rows or columns are taking up in MySQL?
Yes, using the below syntax −select * from information_schema.tables where table_name=yourTableName;Let us first create a table −mysql> create table DemoTable1600 -> ( -> StudentId int, -> StudentFirstName varchar(20) -> ); Query OK, 0 rows affected (0.51 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1600 values(100, 'Bob'); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable1600 values(101, 'David'); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable1600 values(102, 'Carol'); Query OK, 1 row affected (0.19 sec)Display all records from the table using select statement −mysql> select * from ...
Read MoreHow to combine duplicate values into one with corresponding value separated by hyphens in MySQL?
To combine, use GROUP_CONCAT() function to combine some attributes in two rows into one. As a separator, use hyphens.Let us first create a table −mysql> create table DemoTable1386 -> ( -> Title varchar(255), -> Name varchar(60) -> ); Query OK, 0 rows affected (0.67 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1386 values('Introduction to MySQL', 'Paul DuBois'); Query OK, 1 row affected (0.10 sec) mysql> insert into DemoTable1386 values('Java in Depth', 'Khalid Mughal'); Query OK, 1 row affected (0.48 sec) mysql> insert into DemoTable1386 values('Introduction to MySQL', 'Russell Dyer'); Query OK, ...
Read MoreImplement numbering in MySQL GROUP_CONCAT
Let us first create a table −mysql> create table DemoTable1627 -> ( -> FirstName varchar(20), -> LastName varchar(20) -> ); Query OK, 0 rows affected (0.59 sec)Insert some records in the table using insert command.mysql> insert into DemoTable1627 values('John', 'Smith'); Query OK, 1 row affected (0.11 sec) mysql> insert into DemoTable1627 values('John', 'Doe'); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable1627 values('Adam', 'Smith'); Query OK, 1 row affected (0.18 sec) mysql> insert into DemoTable1627 values('Carol', 'Taylor'); Query OK, 1 row affected (0.08 sec)Display all records from the table using ...
Read MoreHow 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 More