MySQL Commands

pi@raspberrypi:~ $ mysql -uroot -ppassword -h 192.168.86.160
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 3872
Server version: 10.3.22-MariaDB-0+deb10u1 Raspbian 10

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

MariaDB [(none)]> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [mysql]>

Show all existing databases:

SHOW DATABASES;

Create new database;

CREATE DATABASE dbname;

Delete database;

DROP DATABASE dbname;

See all existing users:

SELECT user, host, password FROM user;

Add a new user:

CREATE USER 'Toto'@'localhost' IDENTIFIED BY 'password';

To specify any valid hosts:

CREATE USER 'Toto'@'%' IDENTIFIED BY 'password';

Grant all privileges to this user:

GRANT ALL PRIVILEGES ON database_name.* TO 'Toto'@'localhost';
or
GRANT ALL PRIVILEGES ON *.* TO 'Toto'@'localhost'; (for all existing databases)

Flush privileges:

FLUSH PRIVILEGES;

To verify that privileges have been applied as expected:

SHOW GRANTS FOR 'username'@'localhost';
or
SHOW GRANTS FOR 'username'@'%';

To Grant specific privileges refer to:

https://yann.mine.nu/html/index.php/my-sql-privileges/

Change a User password:

ALTER USER 'userName'@'localhost' IDENTIFIED BY 'New-Password';

Delete a user:

DROP USER Toto; 
or
DROP USER IF EXISTS Toto;

Create a table:

CREATE TABLE test (a INT NOT NULL, b CHAR(10));

Delete a table:

DROP TABLE table_name;
or
DROP TABLE IF EXISTS table_name;

Insert a new record in a table:

INSERT INTO table_name (field1, field2, ..., fieldn) VALUES (field1_value, field2_value, fieldn_value);

Delete one or several records from a table:

DELETE FROM table_name WHERE some_field = some_value;
(Note: if no WHERE condition, all records will be deleted)

Update / Modify one or several records in a table:

UPDATE table_name SET some_field1=some_value1, some_field2=some_value2, ...., some_fieldn=some_valuen;
or
UPDATE table_name SET some_field1=some_value1, some_field2=some_value2, ...., some_fieldn=some_valuen WHERE some_field=some_value;