1. Create a table by writing query
2. Alter table by writing query
3. Backup DB
4. Access control in the table

Create a table by writing query

Pattern:

CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    column3 datatype,
   ....
); 

Query:

CREATE TABLE `personalinfo` (
  `id` int(11) NOT NULL,
  `Emp_Id` varchar(20) DEFAULT NULL,
  `City` varchar(30) DEFAULT NULL,
  `Address` varchar(50) DEFAULT NULL,
  `PhoneNUmber` varchar(20) DEFAULT NULL,
  `Gender` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Alter table by writing query

Pattern to add new column:

ALTER TABLE table_name
ADD column_name datatype;

Pattern to remove a column:

ALTER TABLE table_name
DROP COLUMN column_name; 

Pattern to modify column:

ALTER TABLE table_name
MODIFY COLUMN column_name datatype; 

Backup DB

BACKUP DATABASE databasename
TO DISK = 'filepath'; 

Access control in the table

Create new user:

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

Grant all privilege:

GRANT ALL PRIVILEGES ON * . * TO 'newuser'@'localhost';

Grant specific privilege to specific database:

GRANT type_of_permission ON database_name.table_name TO 'username'@'localhost';

Grant specific privilege to all database

GRANT type_of_permission ON * . * TO 'newuser'@'localhost';

Revoke privilege:

REVOKE type_of_permission ON database_name.table_name FROM 'username'@'localhost';

Apply privilege;

FLUSH PRIVILEGES;

Remove test user:

DROP USER 'username'@'localhost';

Show users current permission:

SHOW GRANTS FOR 'username'@'localhost';