[SQL] table 완성하기

https://bskwak.tistory.com/141 에 이어서 table 완성하기 

[SQL] create table

 

SQL의 INSERT 구문

mysql create row

INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);

 

row 1 

INSERT INTO topic (title, description,created,author,profile) 
VALUES('MYSQL','MYSQL is ...',NOW(),'egoing','developer');

- NOW( ) 함수는 현재 날짜,시간을 불러옴

 

나머지 row도 동일하게 진행

 

 

SQL의 SELECT 구문

mysql select syntax

https://dev.mysql.com/doc/refman/8.0/en/select.html

MySQL :: MySQL 8.0 Reference Manual :: 13.2.10 SELECT Statement

링크에 있는 코드

SELECT
    [ALL | DISTINCT | DISTINCTROW ]
    [HIGH_PRIORITY]
    [STRAIGHT_JOIN]
    [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
    [SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
    select_expr [, select_expr] ...
    [into_option]
    [FROM table_references
      [PARTITION partition_list]]
    [WHERE where_condition]
    [GROUP BY {col_name | expr | position}, ... [WITH ROLLUP]]
    [HAVING where_condition]
    [WINDOW window_name AS (window_spec)
        [, window_name AS (window_spec)] ...]
    [ORDER BY {col_name | expr | position}
      [ASC | DESC], ... [WITH ROLLUP]]
    [LIMIT {[offset,] row_count | row_count OFFSET offset}]
    [into_option]
    [FOR {UPDATE | SHARE}
        [OF tbl_name [, tbl_name] ...]
        [NOWAIT | SKIP LOCKED]
      | LOCK IN SHARE MODE]
    [into_option]

into_option: {
    INTO OUTFILE 'file_name'
        [CHARACTER SET charset_name]
        export_options
  | INTO DUMPFILE 'file_name'
  | INTO var_name [, var_name] ...
}

대괄호 [] 의 경우 : 생략 가능

FROM의 위치 중요

 

모든 데이터 화면에 출력

SELECT * FROM table_name;

 

특정 column 제한하여 출력

SELECT column FROM table_name;
-- SELECT id, title, created, author FROM topic;

 

원하는 정보에 해당하는 행만 출력

WHERE문을 통해 필요한 정보 출력

SELECT column FROM table_name WHERE column2 = "...";
-- SELECT id, title, created,author FROM topic WHERE author = "egoing";

 

어떤 column의 값을 기준으로 오름차순으로 나열

SELECT column FROM table_name ORDER by column DESC;
-- SELECT id, title, created, author FROM topic ORDER by id DESC;

 

위의 기준으로 나열할 때 n 순위 까지 출력

SELECT column FROM table_name ORDER by column DESC LIMIT n;
-- SELECT id, title, created, author FROM topic ORDER by id DESC LIMIT 2;	//2순위

 

SQL의 UPDATE 구문

mysql update syntax

https://dev.mysql.com/doc/refman/8.0/en/update.html

MySQL :: MySQL 8.0 Reference Manual :: 13.2.13 UPDATE Statement

 

링크에 있는 코드

UPDATE [LOW_PRIORITY] [IGNORE] table_reference
    SET assignment_list
    [WHERE where_condition]
    [ORDER BY ...]
    [LIMIT row_count]

value:
    {expr | DEFAULT}

assignment:
    col_name = value

assignment_list:
    assignment [, assignment] ...

WHERE문이 없는 경우, 열 전체가 바뀜

 

예시

UPDATE topic SET description='Oracle is ...',title='ORACLE' WHERE id=2;

 

SQL의 DELETE 구문

mysql delete syntax

https://dev.mysql.com/doc/refman/8.0/en/delete.html

MySQL :: MySQL 8.0 Reference Manual :: 13.2.2 DELETE Statement

링크에 있는 코드

DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name [[AS] tbl_alias]
    [PARTITION (partition_name [, partition_name] ...)]
    [WHERE where_condition]
    [ORDER BY ...]
    [LIMIT row_count]

UPDATE문과 마찬가지로 WHERE문이 없으면 전체가 delete됨

 

예시

DELETE FROM topic WHERE id=5;

 

 

 

 

참고 : https://opentutorials.org/course/3161/19538