Some useful MySQL query commands

This article lists some MySQL commands that may be helpful for you.

I. MySQL commands

1. COUNT and GROUP BY

If you want to display a list of how many tutorials are written by each user, you can use COUNT and GROUP BY.
For example, we have the Blogs table as follows:

SELECT operator_id, count(*) as count FROM blogs group by operator_id

Result:

2. MySQL queries many diferent databases on the same servers

If the query combines 2 Tables on 2 Different Databases (on the same server), everything is very easy with the following command:
Example:

SELECT a.*, b.* FROM SchemaA.blogs as a
LEFT JOIN SchemaB.blogs as b ON a.category_id = b.category_id

3. MySQL root password change

Run in MySql editor:

SET PASSWORD FOR ‘root’@’localhost’ = PASSWORD(‘mypass’);
FLUSH PRIVILEGES;

MySQL root password change

4. MySQL LEFT JOIN with condition:

We have the following 2 tables:

– tblUsers: user_id, user_name
– tblUserHistories: user_id, date_in, date_out

SELECT a.*, b.*
FROM tblUsers as a
LEFT JOIN tblUserHistories as b
ON a.user_id = b.user_id
AND
a.user_id = (SELECT user_id FROM tblUserHistories WHERE user_id = m1.user_id ORDER BY id DESC LIMIT 1)

WHERE
b.date_in >= ‘2021-01-01’
ORDER BY
b.date_in ASC, a.user_name ASC

5. How can I find all the tables in MySQL with specific column names in them?

SELECT DISTINCT TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME LIKE ‘%file_name%’
AND TABLE_SCHEMA = ‘database_name’

II. MySQL multiple databases different servers

– A remote server with a database table, the table type of the remote table may be any type supported by the remote mysqld server, including MyISAM or InnoDB.

– A local server with a database table, where the table definition matches that of the corresponding table on the remote server. There is no data file on the local server. Instead, the table definition includes a connection string that points to the remote table.

To create a FEDERATED table you should follow these steps:

1. Create the table on the remote server. Alternatively, make a note of the table definition of an existing table, perhaps using the SHOW CREATE TABLE statement.

2. Create the table on the local server with an identical table definition, but adding the connection information that links the local table to the remote table.

For example, you could create the following table on the remote server:

CREATE TABLE test_table (
id     INT(20) NOT NULL AUTO_INCREMENT,
name   VARCHAR(32) NOT NULL DEFAULT ”,
other  INT(20) NOT NULL DEFAULT ‘0’,
PRIMARY KEY  (id),
INDEX name (name),
INDEX other_key (other)
)
ENGINE=MyISAM
DEFAULT CHARSET=utf8mb4;

To use the first method, you must specify the CONNECTION string after the engine type in a CREATE TABLE statement.
For example:

CREATE TABLE federated_table (
id     INT(20) NOT NULL AUTO_INCREMENT,
name   VARCHAR(32) NOT NULL DEFAULT ”,
other  INT(20) NOT NULL DEFAULT ‘0’,
PRIMARY KEY  (id),
INDEX name (name),
INDEX other_key (other)
)
ENGINE=FEDERATED
DEFAULT CHARSET=utf8mb4
CONNECTION=’mysql://fed_user@remote_host:9306/federated/test_table’;

The format of the connection string is as follows:

scheme://user_name[:password]@host_name[:port_num]/db_name/tbl_name

Where:

– scheme: A recognized connection protocol. Only mysql is supported as the scheme value at this point.
– user_name: The user name for the connection. This user must have been created on the remote server, and must have suitable privileges to perform the required actions (SELECT, INSERT, UPDATE, and so forth) on the remote table.
– password: (Optional) The corresponding password for user_name.
– host_name: The host name or IP address of the remote server.
– port_num: (Optional) The port number for the remote server. The default is 3306.
– db_name: The name of the database holding the remote table.
– tbl_name: The name of the remote table. The name of the local and the remote table do not have to match.

Refer: https://dev.mysql.com/doc/refman/8.0/en/federated-description.html