Basic Operation for Database and Table

DATABASE: Create, use & drop

For example, the database name is zygnovadb.

To create database: CREATE DATABASE zygnovadb;
To use the created database: USE zygnovadb;
To drop the database: DROP DATABASE zygnovadb;

DATABASE: Set database to read_only

For example, the database name is zygnovadb.

To create database: CREATE DATABASE zygnovadb;
To use the created database: USE zygnovadb;
To drop the database: DROP DATABASE zygnovadb;

TABLE: Create, rename & drop

For example, the table name is employees (in database called zygnovadb), which have the following information:

Employee ID	Name	Salary	Date Hired

To create table (must have at least 1 visible column):

CREATE TABLE employees (
  employee_id VARCHAR(50),
  name VARCHAR(255),
  salary FLOAT,
  date_hired DATE
);

To rename table, for instance, from employees to staff: RENAME TABLE employees TO staff;
To drop table: DROP TABLE employees;

COLUMN: Add, modify, change, rename, drop & move

To add a column, for example, we want to add another column into table employee called email:
ALTER TABLE employee ADD email VARCHAR(255);
To add multiple column, for example, we want to add two column into table employee called email and contact:
ALTER TABLE employee ADD email VARCHAR(255), contact INT ;
To modify data type of a column, let's say we want to modify the data type for column contact into VARCHAR(50):
ALTER TABLE employees MODIFY contact VARCHAR(50);
To rename a column:
ALTER TABLE employees RENAME COLUMN name TO e_name;
To drop a column:
ALTER TABLE employees DROP COLUMN phone_number;
To move a column to first position:
ALTER TABLE employees MODIFY employee_status VARCHAR (255) FIRST;
To move a column specific position (using BEFORE instead of AFTER is not supported in MySQL):
ALTER TABLE employees MODIFY employee_status VARCHAR (255) AFTER salary;

COLUMN: Insert Data Into Column

For example, the data that we want to insert into the column is as shown:

Employee ID	Name	Salary	Date Hired
A1001	John	5000.00	2023-07-01
A1002	Samantha	4500.00	2023-07-03

To insert a data:

INSERT INTO employees (employee_id, name, salary, date_hired)
VALUES ('A1001','John', 'Smith', 5000.00, '2023-07-01'),
        ('A1002', 'Samantha', 4500.00, '2023-07-03');

To insert data into specific column:

INSERT INTO employees (employee_id, name)
VALUES ('A1003', 'Muller');

The updated table will look like this (SELECT * FROM employees):

Employee ID	Name	Salary	Date Hired
A1001	John	5000.00	2023-07-01
A1002	Samantha	4500.00	2023-07-03
A1003	Muller	NULL	NULL

COLUMN: SELECT ~ How to query data?

To describe a table:DESC employees;
To show all column in a table:SELECT * FROM employees;
To selects specific columns from a specific table.
SELECT name, salary FROM employees;
To retrieve all rows from the specified table where the specified column has a NULL value:
SELECT * FROM employees WHERE salary IS NULL;
To select all records from the table where the column has a non-null value:
SELECT * FROM employees WHERE date_hired IS NOT NULL;
To retrieve all rows from a table where a specific column has a matching value.
SELECT * FROM employees WHERE name = 'John';
To retrieve all rows from the specified table where the value in the specific column is not equal to the specific value.
SELECT * FROM employees WHERE date_hired != '2023-07-03';
You may query from to show rows with specific column by specifying the output. Example, to retrieves the values from the column_name column in the table_name table where the column_name is equal to a specific value:
SELECT name FROM employees WHERE date_hired = "2023-07-03";

COLUMN: update data

To update a column:
UPDATE employees SET salary = 3000.00 WHERE employee_id = 'A1003';
To update multiple columns:

UPDATE employees
SET salary = 3000.00
    date_hired = '2023-07-01'
WHERE employee_id = 'AC003';

To update a column to NULL:

UPDATE employees
SET date_hired = NULL
WHERE employee_id = 'AC002';

To update all rows in a column (*Exclude WHERE statement):

UPDATE employees
SET date_hired = "2023-07-01";

To delete all rows from a database table:
DELETE employees;
To delete data from a database table (*DO NOT MISS OUT THE WHERE STATEMENT!)

DELETE employees
WHERE employee_id = "AC002";