SQL Manipulation

이 글은 SQL(Structured Query Language)의 간단한 사용방법을 정리하기 위해 작성하였습니다.

---

< What is a Relational Database Management System (RDBMS)? >

A relational database management system (RDBMS) is a program that allows you to create, update, and administer a relational database. Most relational database management systems use the SQL language to access the database.

Relational databases store data in tables. Tables can grow large and have a multitude of columns and records. Relational database management systems (RDBMSs) use SQL (and variants of SQL) to manage the data in these large tables. The RDBMS you use is your choice and depends on the complexity of your application.

 

• Popular Relational Database Management Systems :
  1. MySQL
  2. PostgreSQL
  3. Oracle DB
  4. SQLite
  5. SQL Server

 

 

---

< Introduction to SQL >

SQL, Structured Query Language, is a programming language designed to manage data stored in relational databases.

The SQL language is widely used today across web frameworks and database applications. Knowing SQL gives you the freedom to explore your data, and the power to make better decisions.

 

Reference : https://www.youtube.com/watch?v=MZdO1UbTG4U&list=PLwvrYc43l1MxAEOI_KwGe8l42uJxMoKeS 

 

 

---

< Relational Databases >

A relational database is a database that organizes information into one or more tables.

A table is a collection of data organized into rows and columns.
Tables are sometimes referred to as relations.

Relational database is when two or more tables have some kind of relationship between them.

 

• A row(record) is a single record in a table.
• A column is a set of data values of a particular type.

 

All data stored in a relational database is of a certain data type.
Some of the most common data types are :
1. INTEGER, a positive or negative whole number
2. TEXT, a text string
3. DATE, the date formatted as YYYY-MM-DD
4. REAL, a decimal value

 

ex)

- column : id, name, age

- row : The first row in the celebs table has :

• An id of 1
• A name of Justin Bieber
• An age of 22

 

 

---

< Statements >

A statement is text that the database recognizes as a valid command.
Statements always end in a semicolon ;

 

Example)

CREATE TABLE table_name (
   column_1 data_type, 
   column_2 data_type, 
   column_3 data_type
);

1. CREATE TABLE is a clause. Clauses perform specific tasks in SQL.
By convention, clauses are written in capital letters.

2. table_name refers to the name of the table that the command is applied to.

3. (column_1 data_type, column_2 data_type, column_3 data_type) is a parameter.
A parameter is a list of columns, data types, or values that are passed to a clause as an argument.

 

 

---

< Create >

To create a new table in the database.

 

Example : creates a new table named celebs.

CREATE TABLE celebs (
   id INTEGER, 
   name TEXT, 
   age INTEGER
);

1. CREATE TABLE is a clause that tells SQL you want to create a new table.

2. celebs is the name of the table.

3. (id INTEGER, name TEXT, age INTEGER) is a list of parameters defining each columns, and its data type.

 

 

---

< Insert >

To insert a new row into a table.

 

Example : enters a record for Justin Bieber into the celebs table.

INSERT INTO celebs (id, name, age) 
VALUES (1, 'Justin Bieber', 22);

1. INSERT INTO is a clause that adds the specified row or rows.

2. celebs is the table the row is added to.

3. (id, name, age) is a parameter identifying the columns that data will be inserted into.

4. VALUE is a clause that indicates the data being inserted.

5. (1, 'Justin Bieber', 22) is a parameter identifying the values being inserted.

 

 

---

< Select >

To fetch data from a database.

You will use SELECT every time you query data from a database.

 

Example : returns all data in the name column of the celebs table.

SELECT name FROM celebs;

1. SELECT is a clause that indicates that the statement is a query.

2. name specifies the column to query data from.

3. FROM celebs specifies the name of the table to query data from.

 

plus)

SELECT * FROM celebs;

: * is a special wildcard character that allows you to select every column in a table

 

 

---

< Alter >

To alter the table.

 

Example : adds a new column twitter_handle to the celebs table.

ALTER TABLE celebs 
ADD COLUMN twitter_handle TEXT;

1. ALTER TABLE is a clause that lets you make the specified changes.
2. celebs is the name of the table that is being changed.
3. ADD COLUMN is a clause that lets you add a new column to a table :

• twitter_handle is the name of the new column being added
• TEXT is the data type for the new column

 

+. NULL is a special value in SQL that represents missing or unknown data. Here, the rows that existed before the column was added have NULL (∅) values for twitter_handle.

&amp;amp;amp;amp;amp;amp;lt; Null example &amp;amp;amp;amp;amp;amp;gt;

 

 

---

< Update >

To edits a row in a table.

 

Example : updates the record with an id value of 4 to have the twitter_handle @taylorswift13.

UPDATE celebs 
SET twitter_handle = '@taylorswift13' 
WHERE id = 4;

1. UPDATE is a clause that edits a row in the table.
2. celebs is the name of the table.
3. SET is a clause that indicates the column to edit.

• twitter_handle is the name of the column that is going to be updated
• @taylorswift13 is the new value that is going to be inserted into the twitter_handle column.

4. WHERE is a clause that indicates which row(s) to update with the new column value.

 

 

---

< Delete >

To deletes one or more rows from a table.

 

Example : deletes all records in the celeb table with no twitter_handle

DELETE FROM celebs 
WHERE twitter_handle IS NULL;

1. DELETE FROM is a clause that lets you delete rows from a table.

2. celebs is the name of the table we want to delete rows from.

3. WHERE is a clause that lets you select which rows you want to delete. Here we want to delete all of the rows where the twitter_handle column IS NULL

4. IS NULL is a condition in SQL that returns true when the value is NULL and false otherwise.

 

 

---

< Constraints >

Constraints : To add information about how a column can be used.
They can be used to tell the database to reject inserted data that does not adhere to a certain restriction.

Constraints are invoked after specifying the data type for a column.

 

Example : sets constraintson the celebs table.

CREATE TABLE celebs (
   id INTEGER PRIMARY KEY, 
   name TEXT UNIQUE,
   date_of_birth TEXT NOT NULL,
   date_of_death TEXT DEFAULT 'Not Applicable'
);

1. PRIMARY KEY columns can be used to uniquely identify the row. Attempts to insert a row with an identical value to a row already in the table will result in a constraint violation which will not allow you to insert the new row.

2. UNIQUE columns have a different value for every row. This is similar to PRIMARY KEY except a table can have many different UNIQUE columns.

3. NOT NULL columns must have a value. Attempts to insert a row without a value for a NOT NULL column will result in a constraint violation and the new row will not be inserted.

4. DEFAULT columns take an additional argument that will be the assumed value for an inserted row if the new row does not specify a value for that column.

 

 

---

< Execution Order >

The SELECT statement that retrieves data operates in a fixed order. The execution order of the SELECT statement is as follows.

 

SELECT CustomerId, AVG(Total)
FROM invoices
WHERE CustomerId >= 10
GROUP BY CustomerId
HAVING SUM(Total) >= 30
ORDER BY 2

1. FROM invoices: Access the invoices table.

2. WHERE CustomerId >= 10: Retrieve records with CustomerId field greater than or equal to 10.

3. GROUP BY CustomerId: Group by CustomerId.

4. HAVING SUM(Total) >= 30: Filters only results with a total of 30 or more in the Total field.

5. SELECT CustomerId, AVG(Total): Gets the average of the CustomerId and Total fields from the search results.

6. ORDER BY 2: Returns the results sorted in ascending order by the AVG(Total) field.