MYSQL

Table of contents

• Database
• Types of database
• Relation of database
• Create database
• Make database default
• Drop database
• Make database read only
• Create table
• Insert row
• Select table
• Rename table
• Alter table
• Update table
• Delete table
• Auto commit
• Commit
• Roll back
• Current date, time
• Unique
• Not null
• Check
• Default
• Primary key
• Auto increment

Database

• A set of information that is organized and computerized/electronic

• Field
• Record
• Values (data)

Back To Top ↑

Types of database

• Relational database
• Non-relational database

Back To Top ↑

Key

• Primary key (Unique key across the table)
• Composite primary key ( a key that is generated using more than one key)
• Foreign key ( if a primary key of a table is used in another table is a foreign key)

Back To Top ↑

Relation

• One to one
• One to many
• Many to one

Back To Top ↑

ERD

• Entity

• Attribute

  • Composite
  • Multivalue
  • Derived

• Relationship

Connectivity

Ex-Student and Result table

Cardinality

• One-to-one => 1: 1
• One to many => 1:M
• Many to many => M:N Ex- Student teacher relation Back To Top ↑
  

Database design

Top-down => Model to Data Bottom-up => Data to Model Back To Top ↑

Create Database

CREATE DATABASE "databaseName";

CREATE DATABASE employees;

Back To Top ↑

Make database default

USE "databaseName";

USE employees;

Back To Top ↑

Drop database

DROP DATABASE "databaseName";

DROP DATABASE employees;

Back To Top ↑

Make database read only

ALTER DATABASE "databaseName" READ ONLY = 1;

ALTER DATABASE employees READ ONLY = 1;

NOTE: id the mode is readonly we can not drop the database. In order to drop the database we need to change the readonly mode by ALTER DATABASE "databaseName" READ ONLY = 0;

ALTER DATABASE employees READ ONLY = 0;

Back To Top ↑

Create table

Example CREATE TABLE "tableName" (column name comma separeted)

CREATE TABLE employees(
  employee_id INT,
  first_name VARCHAR(50),
  last_name VARCHAR(50),
  hourly_pay DECIMAL(5,2),
  hire_date DATE
)
//Here "hourly_pay DECIMAL(5,2)" 5,2 is the number of decimal digits with 2 decimal point.  for example - 123.23

Back To Top ↑

Insert row

To insert row we need to add values sequencially one after another following the data types

INSERT INTO employees
VALUES(1,"Md. Ariful","Haque","arif@sebpo.com",400.00,"2020-03-04");

To insert multiple row

INSERT INTO employees
VALUES(1,"Md. Ariful","Haque","arif@sebpo.com",400.00,"2020-03-04"),
      (2,"Md. Abir","Hossain","abir@sebpo.com",400.00,"2020-03-04"),
      (3,"Md. Abdur","Rahman","abdur@sebpo.com",400.00,"2020-03-04") ;

To insert 1 or 2 data we need to specify the column name within first bracket after table name

INSERT INTO employees (employee_id,first_name,last_name)
VALUES(5,"Md. Sabbir","Hossain");

Back To Top ↑

Select table

To get all the column SELECT * FROM "tableName";

SELECT * FROM employees;

To get 1 or n column SELECT column name comma separated FROM "tableName";

SELECT employee_id, first_name, last_name FROM employees;

To get specific column with matching data

SELECT * FROM employees
WHERE hourly_pay = 200.00;

SELECT * FROM employees
WHERE hourly_pay >= 200.00;

SELECT * FROM employees
WHERE hourly_pay != 200.00;

SELECT * FROM employees
WHERE hourly_pay IS NULL;

SELECT * FROM employees
WHERE hourly_pay IS NOT NULL;

Back To Top ↑

Rename table

RENAME TABLE "name of the table" TO "new name of the table";

RENAME TABLE employees TO workers;

Back To Top ↑

Alter table

To add new column

ALTER TABLE employees
ADD phone_number VARCHAR(15);

To rename column

ALTER TABLE employees
RENAME COLUMN phone_number TO enail;

To modify table column

ALTER TABLE employees
MODIFY COLUMN email VARCHAR(100);

To move the column position

ALTER TABLE employees
MODIFY email VARCHAR(100)
AFTER last_name;

Delete column from table

ALTER TABLE employees
DROP COLUMN email;

Move column position to first

ALTER TABLE employees
MODIFY email VARCHAR(100)
FIRST;

Back To Top ↑

Update table

UPDATE "table name"; To update table we can update one or more elements. If want to update more than one element than need to write using comma. And also need specify which one we want to update.

UPDATE employees
SET joining_date="2023-05-12",email="ayan@sebpo.com",salary=25000.00
WHERE employee_id=5;

If we want to set the value to null than just need to set the value equals null

UPDATE employees
SET joining_date=NULL
WHERE employee_id=5;

To update all of the row we need to skip the WHERE clouse. All of the column of joining_date will be updated by the following query.

UPDATE employees
SET joining_date="2023-05-12";

Back To Top ↑

Delete table

DELETE FROM "table name"; If we want to delete the whole table row we need to just write the following query and it will delete everything from the table.

DELETE FROM employees;

To avoid deleting everything we need to follow the below query. It will delete only the row which is sepecified under the WHERE clouse

DELETE FROM employees
WHERE employee_id=5;

Back To Top ↑

Auto commit

Suppose we are going to delete one row from our table and by mistake we forgot to add the WHERE clouse. Then what happen? All the row from the table table will vanish. To get rid from this situation we need to create a safe mode by adding the below query

SET AUTOCOMMIT = OFF;

Back To Top ↑

Commit

When we create a safe mode by adding AUTO COMMIT = OFF then we need to manually commit by COMMIT clouse. After AUTO COMMIT = OFF
Note: we must run the query COMMIt otherwise it will not restore the table data.

COMMIT;

Back To Top ↑

Roll back

When we create a safe mode and delete all the row from table by mistake we can restore them by ROLLABACK clouse

ROLLBACK;

Current date, time

For current date the data type is DATE, current time datat type is TIME, current date time data type is DATETIME

CREATE TABLE date_time(
c_date DATE,
c_time TIME,
current_date_time DATETIME
);

To insert current date, date or date time we have CURRENT_DATE(), CURRENT_TIME(), NOW() function

INSERT INTO date_time
VALUES (CURRENT_DATE(),CURRENT_TIME(),NOW());

We can add also the previous or the next date or time by adding decimal values

INSERT INTO date_time
VALUES (CURRENT_DATE()-1,CURRENT_TIME(),NOW());

If we want to add NULL value we just put null

INSERT INTO date_time
VALUES (null,NULL,NULL);

Back To Top ↑

Unique

CREATE TABLE products(
product_id INT,
product_name VARCHAR(50) UNIQUE,
product_price DECIMAL(10,2)
)

In case if you forgot to set UNIQUE identifier you can set it later by the following query

ALTER TABLE products
ADD CONSTRAINT
UNIQUE(product_id);

Back To Top ↑

Not null

When creating a table you can set a specific column that value must be filled by the following query

CREATE TABLE products(
product_id INT,
product_name VARCHAR(50) NOT NULL,
product_price DECIMAL(10,2)
);

If you forgot to set no null you can add that later by following the below

ALTER TABLE products
MODIFY product_name varchar(20) NOT NULL;

Back To Top ↑

Check

CREATE TABLE employees(
  employee_id INT,
  first_name VARCHAR(50),
  last_name VARCHAR(50),
  salary DECIMAL(8,2),
  hire_date DATE,
  CHECK(salary=>15000)
)

or
CREATE TABLE employees(
  employee_id INT,
  first_name VARCHAR(50),
  last_name VARCHAR(50),
  salary DECIMAL(8,2),
  hire_date DATE,
  CONTRAINT check_salary CHECK(salary=>15000)
)

Add checck to existing table

ALTER TABLE employees
ADD CHECK(salary>=15000);

or
ALTER TABLE employees
ADD CONSTRAINT check_salary CHECK(salary>=15000);

Delete check

ALTER TABLE employees
DROP CHECK check_salary;

Back To Top ↑

Default

To set default value in a new table

CREATE TABLE employees(
  employee_id INT,
  first_name VARCHAR(50),
  last_name VARCHAR(50),
  salary DECIMAL(8,2) DEFAULT 0,
  hire_date DATE,
)

To set default value in an existing table

ALTER TABLE employee
ALTER salary SET DEFAULT 0;

Back To Top ↑

Primary key

A table can have one and only one primary key, can not be null and it should be unique.

CREATE TABLE transactions(
	transaction_id INT PRIMARY KEY,
    amount DECIMAL (8,2) DEFAULT 0.00
);

To add Primary Key in already existing table need

ALTER TABLE transactions
ADD CONSTRAINT
PRIMARY KEY(transaction_id);

Back To Top ↑

Auto increment

CREATE TABLE transactions(
    transaction_id INT PRIMARY KEY AUTO_INCREMENT,
    item VARCHAR(100),
    price DECIMAL(8,2) DEFAULT 0
);

To set defualt value for auto increment

ALTER TABLE transactions
AUTO_INCREMENT = 1000

Back To Top ↑
Back To Top ↑
Back To Top ↑
Back To Top ↑
Back To Top ↑
Back To Top ↑
Source of learning