ADMINISTRATION

AUTHENTICATION & INTERACTION (LOCAL)

#authentication & interaction
root@oco:~$ mysql -u {username} -p{password}
 * the -u is used to supply the username
 * the -p is used to supply the password
    - -p flag should be passed empty, so we are prompted to enter the 
      password and do not pass it directly on the command line since it could
      be stored in cleartext in the bash_history file, logs or terminal history
    - there shouldn't be any spaces between the -p and the password!
 * when no host is specified, login will default to the localhost server.
 
mysql> show grants
 * view which privileges the logged-in user have

AUTHENTICATION & INTERACTION (REMOTE)

root@oco:~$ mysql -u root -h {docker.cnd.dev} -P 3306 -p 
 * the -h is used to supply the remote host DBMS server
 * the -P refers to the remote port
    - 3306 is the default MySQL/MariaDB port

CREATING USERS

MariaDB [(none)]> CREATE USER 'reader'@'localhost';
 Query OK, 0 rows affected (0.002 sec)
 * add a new MariaDB user named reader who is granted only SELECT privileges on the ports table

MariaDB [(none)]> GRANT SELECT ON ilfreight.ports TO 'reader'@'localhost' IDENTIFIED BY 'p@ssw0Rd!!';
 Query OK, 0 rows affected (0.000 sec)

CREATING DATABASES

mysql> CREATE DATABASE {dbName};
mysql> SHOW DATABASES;
 * this cmd displays all the DBs
mysql> USE {dbName};
 * this cmd switches to the specified DB Name

CREATING TABLES

#creates a table w/ four columns 
mysql> CREATE TABLE {tableName}(
  id INT AUTO_INCREMENT NOT NULL,
  username VARCHAR(100) UNIQUE NOT NULL,
  password VARCHAR(100) NOT NULL,
  dateOfJoining DATETIME DEFAULT NOW()
  PRIMARY KEY (id)
);
 * the DATETIME data type stores the date when an entry was added
 * the auto_increment property automatically increments the id column
 * the not null property ensures that a particular column is never left empty
 * the unique property ensures that the inserted item are always unique
 * the default keyword is used to speficy the default value
    - the default value will be the current DTG
 * the primary key property is used to uniquely identify each redord in the
   table referring to all data of a record within a table for RDB
 
mysql> SHOW TABLES;
 * this cmd lists tables in the current db
 
mysql> DESCRIBE {tableName};
 * this cmd lists the table structure w/ its fields & data types

ADDING RECORDS (SINGLES)

mysql> INSERT INTO {tableName} VALUES (column1Value, column2Value, column3Value, ...);
 * 1, 'admin', 'password', '2020-07-02'


mysql> INSERT INTO logins(username, password) VALUES('admin', 'adminPass');
 * can skip filling in columns with default values since they are auto filled
    - i.e., id and DTG
 * in this example, the password is in cleartext and not hashed or encrypted.
   it is always recommended to hash/encrypted the password prior to storage in the DB

ADDING RECORDS (MULTIPLE)

mysql> INSERT INTO {tableName}(username, password) VALUES ('john', 'johnPasswd'), ('tom', 'tomPasswd');

 * 

REMOVING TABLES & DATABASES

mysql> DROP TABLE {tableName};
 * the DROP statement permanently deletes tables & databased w/o confirmation
    - proceed w/ caution
    
mysql> DROP DATABASES {databaseName};

MODIFICATIONS: ADDING NEW COLUMNS

mysql> ALTER TABLE {tableName} ADD newColumnName INT;
 * the alter keyword is used to rename any tables or to rename any of
   the table's fields as well as to delete or add new columns to
   existing tables

MODIFICATIONS: RENAME COLUMNS

mysql> ALTER TABLE {tableName} RENAME COLUMN newColumnName TO oldColumnName;

ALTERATIONS/MODIFICATIONS: CHANGING COLUMN DATA TYPE

mysql> ALTER TABLE {tableName} MODIFY oldColumnName DATE;
 * date is the new data type for the column

MODIFICATIONS: DROPPING COLUMNS

mysql> ALTER TABLE {tableName} DROP oldColumnName;

MODIFICATIONS: CHANGING TABLE PROPERTIES

mysql> UPDATE {tableName} SET column1=newValue1, column2=newValue2, ...WHERE {condition};
 * UPDATE logins SET password = 'changePassword' WHERE id > 1;
 * this updates all passwords in all records where the id is > 1
 * the UPDATE statement can be used to update specific records within a
   table, based on certain conditions