Created by CyanHall.com on 11/13/2020 , Last updated: 04/30/2021.
πŸ‘‰Β Β github shields Star me if it’s helpful.

1. CLI Login

    mysql -h localhost -u root -p
// /usr/local/Cellar/[email protected]/x.xx.xx/bin/mysql -h localhost -u root
  

2. import an SQL file

    mysql -u username -p database_name < file.sql
  

3. List all databases

    show databases;
  

4. List all tables in a database

    use [db name];
show tables;
  

5. List all users

    SELECT User, Host, authentication_string FROM mysql.user;
  

6. Create user and database

    # Create a user
CREATE USER 'db_user'@'localhost' IDENTIFIED BY 'password';

# Create a database
CREATE DATABASE example_db;

# Grant privileges
GRANT ALL ON example_db.* TO 'db_user'@'localhost';
  

7. Delete user and database

    # Delete database
DROP DATABASE example_db;

# Delete user
DROP USER 'db_user'@'localhost';
  

8. Quit CLI

    quit
  

9. sql_mode

    SELECT @@GLOBAL.sql_mode;
SELECT @@SESSION.sql_mode;

# result: 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'

SET GLOBAL sql_mode = '...';
SET SESSION sql_mode = '...';
  

1. CLI Login

    mysql -h localhost -u root -p
// /usr/local/Cellar/[email protected]/x.xx.xx/bin/mysql -h localhost -u root
  

3. List all databases

    show databases;
  

5. List all users

    SELECT User, Host, authentication_string FROM mysql.user;
  

7. Delete user and database

    # Delete database
DROP DATABASE example_db;

# Delete user
DROP USER 'db_user'@'localhost';
  

9. sql_mode

    SELECT @@GLOBAL.sql_mode;
SELECT @@SESSION.sql_mode;

# result: 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'

SET GLOBAL sql_mode = '...';
SET SESSION sql_mode = '...';
  

2. import an SQL file

    mysql -u username -p database_name < file.sql
  

4. List all tables in a database

    use [db name];
show tables;
  

6. Create user and database

    # Create a user
CREATE USER 'db_user'@'localhost' IDENTIFIED BY 'password';

# Create a database
CREATE DATABASE example_db;

# Grant privileges
GRANT ALL ON example_db.* TO 'db_user'@'localhost';
  

8. Quit CLI

    quit
  


Maitained byΒ Cyanhall.com, Copy Rights @ CC BY-NC-SA 4.0