TIL #19 Data Base SQL

Joshua Song / 송성현·2019년 12월 27일
0

이머시브_16

목록 보기
22/29

SQL

  • Structured Query Language -> 구조화된 Query 언어
  • Query:
    • 질의문
    • 검색어도 Query의 일종
    • 저장되어 있는 정보를 필터 하기 위한 질문
  • 데이터베이스 용 프로그래밍 언어
  • 데이테베이스에 query를 보내 원하는 데이터만 추출
  • Why do we need this?
    • In-memory
      • 끄면 데이터가 없어짐
    • File I/O
      • 원하는 데이터만 가져올 수 없고 모든 데이터를 가져온 뒤 서버에서 필터 필요
    • Database
      • 필터링 외에도 FS로 구현이 힘든 관리를 위한, 여러 기능을 가지고 있는 데이터에 특화된 서버
      • 엑셀과 비슷. 테이블 - 시트. Column and Row.
      • 필터 기능(쿼리)
        • Select * FROM employee WHERE gender = 'M'

마지막에 ; 가 있어야 끝낼 수 있다.

SELECT

다 가져오는 것

  • used to select data from a database.
  • SELECT column1, column2, ... FROM table_name;

SELECT DISTINCT

다른 것만 가져오기

  • return only distinct (different values)
  • only list different values. Skip duplicate values
  • SELECT DISTINCT column1, column2 FROM table_name;

WHERE

어디어디

  • used to filter records
  • used to extract those records that fulfill a specified condition
  • SELECT column1, colum2 FROM table_name WHERE condition;
  • Text values require single quotes, (most allow double quotes), but numeric fields should not be quoted

SQL AND, OR and NOT Operators

  • WHERE clause can be combined with AND, OR and NOT operators
  • AND Syntax
    • SELECT column1, column2 FROM table_name WHERE condition1 AND condition2;
  • OR Syntax
    • SELECT column1, column2 FROM table_name WHERE condition1 OR condition2;
  • NOT Syntax
    • SELECT column1, column2 FROM table_name WHERE NOT condition1 ;

SQL ORDER BY

순차 정렬

  • used to sort the result-set in ascending or descending order
  • sorts the records in ascending by default. use the DESC keyword if descending order is wanted
  • SELECT column1, column2 FROM table_name ORDER BY column1, column2 ASC|DESC ;
  • ORDER BY Several Columns
    • Two can use two factors to ORDER BY. It orders by the first and if some rows have same info, it orders by the second factor.
    • SELECT * FROM Customers ORDER BY Country, CustomerName;
    • Table can be sorted differently by different factors, using ASC and DESC.
    • SELECT * FROM Customers ORDER BY Country ASC, CustomerName DESC;
      • In this case, rows will be sorted ASC by country, and the rows with same country name will be sorted DESC.

INSERT INTO

값을 삽입

  • used to insert new records in a table. Two ways to carry this out.

  • #1

    • INSERT INTO table_name (column1, column2, column3) VALUES (value1, value2, value3)
  • #2

    • INSERT INTO table_name VALUES (value1, value2, value3)

    없는 부분은 null로 들어간다.

SELECT CustomerName, ContactName, Address FROM Customers WHERE Address IS NOT NULL | IS NULL

SQL Wildcards

편하다. 그 전 이나 후 중간 내용을 몰라도 찾을 수 있다.

  • used to substitute one or more characters in a string.
  • %
    • SELECT * FROM Customers WHERE City LIKE &es&; (selects all customers with a City containing the pattern "es")
  • _
    • SELECT * FROM Customers WHERE City LIKE '_ondon'; (with any character, followed by "ondon")
  • [charlist] : if you put !, it's opposite
    • SELECT * FROM Customers WHERE City LIKE '[bsp]%'; (select customers with a City starting with "b", "s", or "p")
    • SELECT FROM Customers WHERE City LIKE '[!bsp]%'; (select customers with a City not starting with "b", "s", or "p") or SELECT FROM Customers WHERE City NOT LIKE '[bsp]%'; (select customers with a City not starting with "b", "s", or "p")

SQL Aliases

아직 더 써봐야 알 것같다. 데이터를 가명으로 가져와 합쳐 새로운 테이블을 만든다.

  • used to give a table, or a column in a table, a temporary name
  • aliases make column names more readable
  • exists for the duration of the query

SQL Joins

데이터 합치기.

  • (INNER) JOIN: Returns records that have matching values in both tables
  • LEFT (OUTER) JOIN: Returns all records from the left table, and the matched records from the right table
  • RIGHT (OUTER) JOIN: Returns all records from the right table, and the matched records from the left table
  • FULL (OUTER) JOIN: Returns all records when there is a match in either left or right table

SQL INNER JOIN / LEFT JOIN / RIGHT JOIN

  • selects records that have matching values in both tables
  • SELECT column_name FROM table1 INNER JOIN / LEFT JOIN / RIGHT JOIN table2 ON table1.column_name = table2.column_name;

NULL Value

  • a field with no value.
  • It is possible to insert a new record or update a record without adding a value to this field.

Schema and Query Design

Schema: a description of the way data is organized in a database and the relationships among its different entities. "Blueprint for your database"

Entity: District units of information. Data 의 Object같은 것.

Fields: Entity Name? Group Name

Table: Data Table.

Consider the relationship between tables.

  • One-to-many: 하나가 여러 (Prof to Class)
    • Referencing a table's primary key in a different table, the id value is called a foreign key. 데이터가 커지면서 검색이 어려워진다. Can't store multiple values inside one column.
    • 데이터가 중복되는 것도 피해야 함.
  • Many-to-Many: 여러가 여러 (Class to Student)
    • Make a new table in between. That table will have one-to-many relationship with both class and student table.

수강신청 Schema

Now to Data Base Table

When would we use alias?

  • When we have one table, it doesn't matter, but when we use multiple tables, we use it.
  • Subquery:
    • Use query inside query.
    • Use join when conditions are equal.
    • 연결하는 이유: 몇몇 정보를 모르기 때문이다.

느낀점

  • 음 일단 SQL이란 언어를 배우기 시작하는데 기대가 된다. 데이터 베이스를 배울 때 필수인 부분을 잘 배워 잘 활용하고 싶다. 그냥 튜토리얼을 조금 따라가면 아직은 괜찮은 것 같은데 막상 실제로 해봐야 알 것 같다. 데이터 관리를 위한 언어라니. Fancy 하다. 잘만 사용한다면 매우 편리할 것 같다.
  • 오늘 토이 잘 했다. 밀린 토이를 시간이 있다면 하고 싶은데, 리덕스도 정리해야하고 Article Collector도 한번 더 봐야한다.
profile
Grow Joshua, Grow!

0개의 댓글