[2021.02.16] SQL 공부

SQL

SQL stands for Structured Query Language.

It lets you access and manipulate databases.

 

SQL이란 데이터베이스의 언어다. 관계형 데이터베이스에서 데이터를 조작하고 쿼리하는 표준언어다. 

사용자가 필요하고 원하는 것을 RDBMS에게 요청(쿼리)을 하는 것

Query(쿼리)란?

데이터베이스에 정보를 요청하는 것이다.

웹 서버에 특정한 정보를 보여달라는 웹 클라이언트 요청에 의한 처리이다.

대개 데이터베이스로부터 특정한 주제어나 어귀를 찾기 위해 사용된다. 주제어가 검색엔진의 검색필드 내에 입력된 다음, 그 내용이 웹서버로 넘겨진다. 

 

What can SQL do?

excute queries against a database

retrieve and delete data from a database

insert, update, records in a data base

create new databases

create new tables in a database

 

RDBMS

RDBMS stands for Relational Database Management System

RDBMS에는 여러 가지 종류가 있다. 서로 약간씩 다른 문법이나 사용법이 있지만 

The data in RDBMS is stored in database objects called tables. A table is a collection of related data entries and it consist of columns and rows.

 

---

Database Tables

A database most often contains one or more tables. Each table is identified by a name. (e.g. "Customers")

Tables contain records(row) with data. 

 

Every table is broken up into smaller entities called fields. The fields in Customers table consist of CustomerID, CustomerName, ContactName, Address, City, PostalCode and Country.

A field is a column in a table that is designed to maintain specific information about every record in the table.

A record, also called row, is each individual entry that exists in a table.

It's a horizontal entity in a table.

A column is a vertical entity in a table that contains all information associated with a specific field in a table.

 

field = 분류

record = row = horizontal entity

column = vertical entity

 

Example) Customer table

CustomerID	CustomerName	ContactName	Address	City	PostalCode	Country
1	Alfreds Futterkiste	Maria Anders	Obere Str. 57	Berlin	12209	Germany
2	Ana Trujillo Emparedados y helados	Ana Trujillo	Avda. de la	México D.F	05021	Mexico
3	Antonio Moreno Taquería	Antonio Moreno	Mataderos 2312	México D.F	05023	Mexico
4	Around the Horn	Thomas Hardy	120 Hanover Sq.	London	WA1 1DP	UK
5	Berglunds snabbköp	Christina Berglund	Berguvsvägen 8	Luleå	S-958 22	Sweden

 

 

---

SQL Statements

The following SQL statement selects all the records in the "Customers" table:

SELECT * FROM Customers;

 **SQL keywords are NOT case sensitive: select is the same as SELECT

 

Some of the Most Important SQL Commands

• SELECT - extracts data from a database
• UPDATE - updates data in a database
• DELETE - deletes data from a database
• INSERT INTO - inserts new data into a database
• CREATE DATABASE - creates a new database
• ALTER DATABASE - modifies a database
• CREATE TABLE - creates a new table
• ALTER TABLE - modifies a table
• DROP TABLE - deletes a table
• CREATE INDEX - creates an index (search key)
• DROP INDEX - deletes an index

 

SELECT Statement

The SELECT statement is used to select data from a database.

The data returned is stored in a result table, called the result-set.

 

SELECT column1, column2, ...
FROM table_name;

column1, column2,... are the field names of the table you want to select data from.

 

If you want to select all the fields available in the table,

SELECT * FROM table_name;

 

SELECT Column Example

The following SQL statment selects the "CustomerName" and "City" columns from the "Customers" table:

SELECT CustomerName, City FROM Customers;

 

SELECT * Example

selects all the columns from the "Customers" table:

SELECT * FROM Customers;

 

SELECT DISTINCT Statement

The SELECT DISTINCT statement is used to return only distinct (different) values.

Inside a table, a column often contains many duplicate values; and sometimes you only want to list the differenct (distinct) values.

 

SELECT DISTINCT column1, column2,...
FROM table_name;