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
MySQL Articles
Page 246 of 355
Get a list of MySQL databases and version?
To get a list of MySQL databases, following is the syntax -show databases;To get the server version, you can use the below syntax -select version();Let us implement the above syntax to get a list of MySQL databases and version -mysql> show databases;This will produce the following output displaying all the databases -+---------------------------+ | Database | +---------------------------+ | bothinnodbandmyisam | | business | | commandline | | customer-tracker | | customer_tracker_database ...
Read MoreHow to group by date regardless of time in MySQL?
When you have identical dates in a table with different time values for each, you can group them easily with GROUP BY DATE.Let us first create a table -mysql> create table DemoTable692 (DueDatetime datetime); Query OK, 0 rows affected (0.97 sec)Insert some records in the table using insert command:mysql> insert into DemoTable692 values('2019-07-21 10:20:00'); Query OK, 1 row affected (0.18 sec) mysql> insert into DemoTable692 values('2019-06-11 11:00:10'); Query OK, 1 row affected (0.24 sec) mysql> insert into DemoTable692 values('2019-07-21 11:00:10'); Query OK, 1 row affected (1.97 sec) mysql> insert into DemoTable692 values('2019-07-21 12:10:10'); Query OK, 1 row affected (0.18 sec)Display ...
Read MoreAdd a column to a MySQL table which is the result of concatenation of text and value from another auto increment column?
For this, you can use LAST_INSERT_ID(). Let us first create a table −mysql> create table DemoTable ( UserId int(6) unsigned zerofill NOT NULL AUTO_INCREMENT, UserAutoIncrement char(100) default null, PRIMARY KEY(UserId) ); Query OK, 0 rows affected (0.72 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values(); Query OK, 1 row affected (0.13 sec)Display all records from the table using select statement −mysql> select *from DemoTable;Output+--------+-------------------+ | UserId | UserAutoIncrement | +--------+-------------------+ | 000001 | NULL | +--------+-------------------+ ...
Read MoreSet a filter on field type to fetch MySQL columns with type text?
To set a filter for type, you can use below syntax −SELECT TABLE_NAME, COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE DATA_TYPE = 'yourDataTypeName';Let us implement the above syntax to show fields only with field type text −mysql> SELECT TABLE_NAME, COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE DATA_TYPE = 'text';This will produce the following output −+---------------------------------------------+--------------------------------+ | TABLE_NAME | COLUMN_NAME | +---------------------------------------------+--------------------------------+ | COLUMNS ...
Read MoreDisplay all deadlock logs in MySQL?
First of all, you need to enable innodb_print_all_deadlocks. Following is the syntax −set global innodb_print_all_deadlocks=1;After executing the above statement, let us execute the below syntax in order to display all deadlock logs −show engine innodb status;Let us implement the above syntax −mysql> set global innodb_print_all_deadlocks=1; Query OK, 0 rows affected (0.00 sec) mysql> show engine innodb status;This will produce the following output −+--------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Type | Name | Status ...
Read MoreSelect last 3 rows from database order by id ASC?
You can use subquery. Following is the syntax −SELECT * FROM ( SELECT * FROM yourTableName ORDER BY yourIdColumnName DESC LIMIT 3 ) anyAliasName ORDER BY yourIdColumnName;Let us first create a table −mysql> create table DemoTable ( ClientId int NOT NULL AUTO_INCREMENT PRIMARY KEY, ClientName varchar(100) ); Query OK, 0 rows affected (0.60 sec)Insert some records in the table using insert command −mysql> insert into DemoTable(ClientName) values('Larry'); Query OK, 1 row affected (0.18 sec) mysql> insert into DemoTable(ClientName) values('Chris'); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable(ClientName) values('Bob'); Query OK, 1 row affected (0.10 sec) mysql> insert into DemoTable(ClientName) values('David'); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable(ClientName) values('Carol'); Query OK, 1 row affected (0.10 sec) mysql> insert into DemoTable(ClientName) values('Robert'); Query OK, 1 row affected (0.19 sec) mysql> insert into DemoTable(ClientName) values('Sam'); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable(ClientName) values('Mike'); Query OK, 1 row affected (0.12 sec)Display all records from the table using select statement −mysql> select *from DemoTable;This will produce ...
Read MoreFind size of text stored in a specific MySQL column?
You can use length() from MySQL to find the size of text stores in a specific column. Let us first create a tablemysql> create table DemoTable ( CustomerName longtext ); Query OK, 0 rows affected (0.67 sec)Insert records in the table using insert command −mysql> insert into DemoTable values('Robert'); Query OK, 1 row affected (0.14 sec)Display all records from the table using select statement −mysql> select *from DemoTable;This will produce the following output−+--------------+ | CustomerName | +--------------+ | Robert | +--------------+ 1 row in set (0.00 sec)Here is the query to find the size ...
Read MoreIs there a MAX function for rows and not for columns in MySQL?
Yes, you can use GREATEST() from MySQL to check maximum from rows (not columns). Let us first create a table −mysql> create table DemoTable ( Value1 int, Value2 int, Value3 int ); Query OK, 0 rows affected (0.58 sec)Insert records in the table using insert command −mysql> insert into DemoTable values(190, 395, 322); Query OK, 1 row affected (0.16 sec)Display all records from the table using select statement −mysql> select *from DemoTable;This will produce the following output−+--------+--------+--------+ | Value1 | Value2 | Value3 | +--------+--------+--------+ | 190 | 395 ...
Read MoreAdd data to existing data in a MySQL Database?
You can use CONCAT() function for this. Let us first create a table −mysql> create table DemoTable ( UserId int NOT NULL AUTO_INCREMENT PRIMARY KEY, UserName varchar(100) ); Query OK, 0 rows affected (0.43 sec)Insert records in the table using insert command −mysql> insert into DemoTable(UserName) values('John'); Query OK, 1 row affected (0.24 sec) mysql> insert into DemoTable(UserName) values('Chris'); Query OK, 1 row affected (0.11 sec) mysql> insert into DemoTable(UserName) values('Robert'); Query OK, 1 row affected (0.11 sec)Display all records from the table using select statement −mysql> select *from DemoTable;This will produce the following output −+--------+----------+ ...
Read MoreHow do you fill in or pad a column with zeros using a MySQL query?
You can use ZEROFILL for column to fill in or pad with zeros. Let us first create a table−mysql> create table DemoTable ( Number int ); Query OK, 0 rows affected (0.58 sec)Following is the query to add zerofill attribute for Number column−mysql> alter table DemoTable change Number Number int(10) zerofill not null; Query OK, 0 rows affected (1.13 sec) Records: 0 Duplicates: 0 Warnings: 0Insert records in the table using insert command −mysql> insert into DemoTable values(1); Query OK, 1 row affected (0.23 sec) mysql> insert into DemoTable values(12); Query OK, 1 row affected (0.53 sec) ...
Read More