[CS] Database Day-74

SQL Introduction

A database language used by relational databases. MySQL, Oracle, PostgreSQL, etc.

Can send query to the database to get or insert the data. SQL requires a fixed data structure.

Unlike SQL, database which the structure of data is not fixed is NoSQL. Database like MongoDB is called NoSQL.

What is Query?
Question to filter data.

---

Basic query

Basic grammar required to use SQL

• Select

• Where

• And, Or, Not

• Order By

• Insert Into

• Null Values

• Update

• Delete

• Count

• Like

• Wildcards

• Aliases

• Joins

  • Inner Jin
  • Left Join
  • Right JOin

• Group By

---

Database Commands

• Create Database

CREATE DATABASE (Database_Name);

• Use Database

USE (Database_Name);

• Create Table

CREATE TABLE user (
	id int PRIMARY KEY AUTO_INCREMENT,
    name varchar(255),
    email varchar(255),
);

• Describe (database_name);
  Can check table information.

DESCRIBE user;

---

SQL Commands

• From
  If you are working with tables, you need to write the name of the database table name from which to find the result after FROM.

SELECT (characteristic) FROM (Table_name);

SELECT * FROM (Table_name);
// * mean find all

• WHERE
  WHERE allows you to find a specific selected value.

SELECT (characteristic) FROM (Table_name) WHERE (Specific_Value);

You can find the result of comparing if Specific_Value is greater than or less than a 100.

SELECT (characteristic) FROM (Table_name) WHERE (Specific_Value <= 100);

When searching for data about NULL, you have to use IS together.

SELECT * FROM (Table_name) WHERE (Specific_Value) IS NULL

• ORDER BY
  You can check the results by sorting the data.

SELECT * FROM (Table_name) ORDER BY (Specific);

Descending

SELECT * FROM (Table_name) ORDER BY (Specific) DESC;

• Inner Join
  Join two or more tables based on a common part.

SELECT * FROM (Table_name1) JOIN (Table_name2) ON (Table_name1.Specific_Value) = (Table_name2.Specific_Value)

---

ACID

• Atomity
• Consistency
• Isolation
• Durability

This property is necessary to ensure the safety of the database.

Atomity

All operations in a transaction either all succeed or all fail, resulting in predictable outcomes.
(If you fail due to a small mistake, all progress will be turned into failure.)

Consistency

The database must always be consistent. In a database with rules, data that breaks the rules cannot be generate.

Isolation

All transactions are independent of other transactions.

ex) You have $10, If you send $10 to B and C at the same time. It looks like it will be -$10, but actually transfer money to only one of B and C.

Durability

Even if an error, the error also logged in system. Record must be permanent.

---

Difference between SQL and NoSQL

SQL

In a relational database, the structure and data type of a table are defined in advance, and only enter data in the appropriate format for the table.

It stores data in a table organized into rows and columns.

When SQL database expansion. Expand vertically. Complex and take times a lot.

• MySQL
• Oracle
• SQLite
• PostgreSQL
• MariaDB

NoSQL

Compared to SQL, Data entry is easy. For SQL, data must be entered according to the schema. However, in NoSQL, if a schema does not exist, data can be entered by creating a new schema.

NoSQL databases expand horizontally. The cost also low.

• MongoDB
• Casandra

---

NoSQL

• Key-Value: Key stands for property name and Value stands for data value.
  NoSQL is stored in key-value format.

• Document Database: It stores data like document. Stores data like JSON format.

• Wide-Column Database: Free to add any columns you need.

---

Which database is good?

There is no answer. There are pros and cons to SQL and NoSQL, so take a look at your uses case and choose the right database for you.

Case using SQL database

If you care about ACID, I recommend using a SQL database.

Case using NoSQL database

If you need to utilize a lot of storage space, NoSQL is good expand. Build service quickly, or if update data structures frequently, NoSQL is a good choice.