user-and-database.md

User & Database

Database

Show the Database List

SHOW DATABASES;

Use Database

USE company_db;

Create Database

CREATE DATABASE company_db;

Delete database

DROP DATABASE company_db;

Show tables

SHOW TABLES;

Describe table

DESCRIBE employees;

or

DESC employees;

User

Existing Users List

SELECT user, host FROM mysql.user;

Create User

CREATE USER 'app_user'@'localhost' IDENTIFIED BY 'StrongPassword123!';

Create Remote User

CREATE USER 'remote_user'@'%' IDENTIFIED BY 'StrongPassword123!';

% means the user can connect from any host.

Remove Users

DROP USER 'app_user'@'localhost';

Change User Password

ALTER USER 'app_user'@'localhost'
IDENTIFIED BY 'NewStrongPassword!';

Note: MySQL version MySQL 8+ required.

Show current user

SELECT CURRENT_USER();

Grant Permissions

Give all permissions on a database

GRANT ALL PRIVILEGES ON company_db.* TO 'app_user'@'localhost';

Give read-only access

GRANT SELECT ON company_db.* TO 'report_user'@'localhost';

Give required permissions

GRANT SELECT, INSERT, UPDATE, DELETE
ON crm_db.* TO 'crm_user'@'localhost';

Reload privileges

FLUSH PRIVILEGES;

Example Complete Initial Setup

CREATE DATABASE example_db;

CREATE USER 'example_user'@'localhost'
IDENTIFIED BY 'StrongPassword123!';

GRANT ALL PRIVILEGES ON example_db.* TO 'example_user'@'localhost';

FLUSH PRIVILEGES;

Now connect using:

mysql -u crm_user -p

Table

See all the tables

SHOW TABLES;

Search tables by using name

SHOW TABLES LIKE '%searchword%';

or

SELECT table_name
FROM information_schema.tables
WHERE table_schema = 'schema_name'
AND table_name LIKE '%searchword%';

or

SELECT table_name
FROM information_schema.tables
WHERE table_schema = 'schema_name'
AND LOWER(table_name) LIKE '%searchword%';

for case sensitive search.

See all columns of the table

DESCRIBE table_name;

or shorter

DESC table_name;

or

SHOW COLUMNS FROM table_name;

or

SELECT column_name
FROM information_schema.columns
WHERE table_schema = 'schema_name'
AND table_name = 'table_name';

this will show only column names.

or detail strcuture

SHOW CREATE TABLE table_name;

Example

+------------+--------------+------+-----+---------+----------------+
| Field      | Type         | Null | Key | Default | Extra          |
+------------+--------------+------+-----+---------+----------------+
| id         | bigint       | NO   | PRI | NULL    | auto_increment |
| name       | varchar(255) | YES  |     | NULL    |                |
| created_at | datetime     | YES  |     | NULL    |                |
+------------+--------------+------+-----+---------+----------------+