Manuel Soriano Contents: ��� ���� �� Table ����� Table �����ϱ� Table�� ����Ÿ �Է��ϱ� ����Ÿ �����ϱ� WHERE�� ���� �ڼ��� ���� UPDATE ���� DELETE ���� ���� |
SQL Tutorial
|
name: | ������ table�� �̸��̴�. |
Column: | �÷��� �̸��̴�. |
Type: | ����Ÿ�� Ÿ���̴�.(varchar, char, int, date, time, timestamp), Postgres �� �ٸ� data type�� ������, ANSI SQL���� ȣȯ���� �ʴ´�. |
Value: | default�� ���� ���� �����Ѵ�. |
Inherits: | Postgre���� �ִ� ������, �ٸ� ���̺��� �ִ� ��� �÷��� ��ӹް� �ȴ�. |
Nom_cons: | row�� ������ ������ ����Ÿ�� ���� ���������� �����Ѵ�. |
Test: | �˻��� �����̴�. |
CREATE TABLE countries ( cod_country integer NOT NULL, name varchar(30)) CONSTRAINT cod_raro CHECK (cod_country > 0 AND cod_country < 154);
���� ������ countries��� table�� �������. ���̺��� ���� �Է��� ������ ���� ���ǿ� �´��� Ȯ���ϰ� �ȴ�.
ExecAppend: cod_country�� �Ӽ��� NULL�� �ƴϱ� ������ �����ϰ� �ȴ�.
ExecAppend: cod_raro �˻縦 �ź��ϰ� �ȴ�.
NULL�� ������ �ǹ��ϴ°�? SQL���� data�� data�� �ƴ� �� �ΰ��� ���°� �ִ�. ��(0)�̳� ���� ���� ����Ÿ�� �ǹ��ϴ� ��ó�� �츮�� ����Ÿ�� ������ �ʴ� �ʵ带 �ʿ�� �� ���� �ִ�. SQL������ NULL�̶�� ������ �����ߴ�. �������� ���� ������ ����.
bill�̶�� ���̺��� �����, customer, value, date_issued, date_paid��� �ʵ带 ������ �ִ�.
���� customer, value, date_issued �ʵ忡 ���� �����ϰ�,
date_paid�� NULL�� �ߴ�. �̷� ������ �����ؾ��� ���� �˼��� �ִ�.
SELECT * FROM bills WHERE date_paid IS NULL ;
date_padi�� ��(0)���� �ص� ���� ȿ���� �� �� �ִٰ� �����ϴ� ����� ���� �ٵ�, ��(0)�� ��¥�� �ƴ� ���� �����ϰ�, �´� ���̴�. ���� ��¥�� ���� �Լ��� �̿��ϰ�, date type�� date_paid�� ����� ���� �����Ѵ�.
(������ : ��� ª�Ƽ� ������ �����ϱ��� ^^; �����Դϴ�.
One can claim that a zero (0) in the field date_paid would do the same thing. It's
true,
except that zero (0) is not a date, and it prevents creating date_paid of date type
and
applying appropriate date functions.)
NULL�� �̿��Ͽ� �����ϴ� ��:
insert into countries values (15, NULL);�Ǵ�:
insert into countries (cod_country) values (27);
'name'�� ���� �������� �ʴ� ���� NULL ���� �� �ٴ� ���� �ǹ��Ѵ�.
PostgreSQL������ Table�� ������ ���ο� �÷��� �߰��ϴ� �ϸ��� �� �� �ִ�.
ALTER TABLE table ADD name type;
Table | �����ϰ��� �ϴ� Table�� �̸� |
Name | �߰��ϰ��� �ϴ� �÷��� �̸� |
Type | data�� Ÿ�� ( CREATE TABLE�� ����) |
���� Table�� ���� �Է��ϴ� �Ϳ� ���� ����.
����:INSERT INTO table [(column, column, ...)] VALUES (value-1, value-2, ...)or else:
INSERT INTO table [(column, column, ...)] SELECT ....
���Ҵ� ���� table�� ����Ÿ�� �Է��ϴ� ����� �� ������ �ִ�. ���� ���� �Է��ϴ� ����� ���� row�� �����ϴ� sub-select�� �̿��ϴ� ����̴�.
table�� ����Ÿ�� �Է��� ���� NULL ���� ������ ��� �÷��鿡 ���� ���� �־�� �Ѵ�.
����Ÿ�� �Է��ϴ� ���ɿ� � �÷��� ���� �Է����� ����ȭ���� ������ ��� �÷��� ����Ÿ�� �Է��ϴ� ������ �����Ѵ�. ���� ���:
INSERT INTO countries VALUES (34, 'Spain');������ Ʋ�� ���̴�.
INSERT INTO countries VALUES (34);������ ������ ���� ���̴�.
INSERT INTO countries (cod_country) VALUES (34);
���ڴ� C ���� DB ���α����� �ϰų� database �Լ��� ����� �� ���ϴ� �÷��� �� ����ȭ �� ���� ��õ�ϴ� ���̴�. �ֳ��ϸ� ALTER TABLE���� �̿��Ͽ� ���ο� �÷��� �߰����� �� ���� ���� insert�� ������ ���� �����̴�.
��:
�̴� 'population' data�� ������ ������ ���� ������ ���� �ȴ�.
PostgreSQL������ ������ ������ �ʰ�, 'population' �ʵ带 NULL�� ä���. ������, �̰��� PostgreSQL�� Ư¡�̰�, �ٸ� SQL������ ������ ���� ���̴�.
sub-select�� �̿��� �ٸ� ����� INSERT�� �ִ�.
�� ����� insert�� �ӽ����� table�� ����ų� ������ ������ ����� �����ϰ� �ϱ� ���ؼ� ���� ���δ�.
insert�� �̿��ؼ� data�� ��ü�� ��ġ�� ���, �̰��� SELECT�� ���ؼ� ��ȯ�ȴ�. SELECT�� �� row �Ǵ� ���� row�� ������ �� �ִ�. SELECT�� SELECT�� ���� ���� ������ ������.
���ڴ� ���� �� �κ����� ���� ���ߴ�.! :-))
SQL ������ �� ���� ���Ҵ���, SELECT���� SQL ���� ���뿡 �Ұ��ϴ�.
SELECT ������ �츮�� data�� �����ϴ� ���� ����Ѵ�. ������ �̷��� ������ ����� �͵�, �� Ž���̳�, table���� ����, data�� ���� �Լ�, Ž�� �Ծ� ���̴�.
��:
select * from countries;
*�� ��� �÷��� �ǹ��Ѵ�.
�ٸ� ��:
SELECT a.name, SUM(population) FROM countries a, states b, counties c WHERE b.cod_country = a.cod_country AND (c.cod_country = b.cod_country AND c.state_code = b.state_code) AND population IS NOT NULL GROUP BY a.name ORDER BY sum ASC;
������, ���ڴ� ��� ������ �α��� �ø��������� ���� ���ߴ�.
�̸� ���Ͽ� 'population'�̶�� �÷��� counties table�� �߰��ߴ�.
������ ����:create table counties (cod_country int, state_code int, county_code int, county_name varchar(60), population int); insert into counties values (1, 1, 1, 'Country 1, State 1, County 1', 5435); insert into counties values (2, 1, 1, 'Country 2, State 1, County 1', 7832); insert into counties values (3, 1, 1, 'Country 3, State 1, County 1', 4129); insert into counties values (1, 2, 1, 'Country 1, State 2, County 1', 76529); insert into counties values (2, 2, 1, 'Country 2, State 2, County 1', 9782); insert into counties values (3, 2, 1, 'Country 3, State 2, County 1', 852); insert into counties values (1, 3, 1, 'Country 1, State 3, County 1', 3433); insert into counties values (2, 3, 1, 'Country 2, State 3, County 1', 7622); insert into counties values (3, 3, 1, 'Country 3, State 3, County 1', 2798); insert into counties values (1, 1, 2, 'Country 1, State 1, County 2', 7789); insert into counties values (2, 1, 2, 'Country 2, State 1, County 2', 76511); insert into counties values (3, 1, 2, 'Country 3, State 1, County 2', 98); insert into counties values (1, 2, 2, 'Country 1, State 2, County 2', 123865); insert into counties values (2, 2, 2, 'Country 2, State 2, County 2', 886633); insert into counties values (3, 2, 2, 'Country 3, State 2, County 2', 982345); insert into counties values (1, 3, 2, 'Country 1, State 3, County 2', 22344); insert into counties values (2, 3, 2, 'Country 2, State 3, County 2', 179); insert into counties values (3, 3, 2, 'Country 3, State 3, County 2', 196813); insert into counties values (1, 1, 3, 'Country 1, State 1, County 3', 491301); insert into counties values (2, 1, 3, 'Country 2, State 1, County 3', 166540); insert into counties values (3, 1, 3, 'Country 3, State 1, County 3', 165132); insert into counties values (1, 2, 3, 'Country 1, State 2, County 3', 0640); insert into counties values (2, 2, 3, 'Country 2, State 2, County 3', 65120); insert into counties values (3, 2, 3, 'Country 3, State 2, County 3', 1651462); insert into counties values (1, 3, 3, 'Country 1, State 3, County 3', 60650); insert into counties values (2, 3, 3, 'Country 2, State 3, County 3', 651986); insert into counties values (3, 3, 3, 'Country 3, State 3, County 3', NULL); commit work;
���� �츮�� ALTER TABLE�� �̿��� �� ������, ���� �������� ���� UPDATE�� ����� �� �ִ�. "cut & paste"�� �̿��϶�. ��� ����� happy ���� ���̴�. :-))
������ ���������μ� ������ ���� ����� ���� �� �ִ�.
name | sum - ---------+------- country 1| 705559 country 2|1212418 country 3|2804018 (3 rows)������ ���� �ϸ�:
sum - ------ 791986 (1 row)
!!!!!! �ϳ��� �ٸ��� !!!!!!
���� states table�� ����. state 3�� �������.
INSERT INTO states VALUES (3, 1, 'State 3, Country 1'); INSERT INTO states VALUES (3, 2, 'State 3, Country 2'); INSERT INTO states VALUES (3, 3, 'State 3, Country 3');���� ������ �ݺ��Ͽ� ������ ���� ����� ��´�.
name | sum - ---------+------- country 1| 791986 country 2|1872205 country 3|3003629
������ country�� state 3�� ����.
(���̺� ���� joindl EXACT���� ����϶�) ������ �͵鿡 ���ؼ� ���ǿ� �´� �͵鸸 ������ �� �ִ�.
�ٽ� �� �κ����� �ö�WHERE b.cod_country = a.cod_country�κ��� ����.
�̰��� country table�� state�� cod_country�� ���� ���� join�ߴٴ� ���� �����ش�. country data�� ���Եƴٴ� ���� ����ض�.
������ �����Ű���� ����. �ܼ��� ���̴�.
create table countries (cod_country integer, name varchar(30)); insert into countries values (1, 'country 1'); insert into countries values (2, 'country 2'); insert into countries values (3, 'country 3'); commit work;���� states data�� ����
create table states (state_code int, cod_country int, state_name varchar(30)); insert into states values (1, 1, 'State 1, Country 1'); insert into states values (2, 1, 'State 2, Country 1'); insert into states values (1, 2, 'State 1, Country 2'); insert into states values (2, 2, 'State 2, Country 2'); insert into states values (1, 3, 'State 1, Country 3'); insert into states values (2, 3, 'State 2, Country 3'); commit work;������ country�� ���ؼ� state 3�� ����. ����, country table���� states 3�� �ִ�. ����, �츮�� where�� �� ��° �κп��� code 3�� �����ϰ�, country data�� states�� �߰����� ���� ���� �翬�� ���̴�.
AND (c.cod_country = b.cod_country AND c.state_code = b.state_code)
state�� counties table���� ������, states table���� ����.
������ ���� ���� ���ذ� �� �� ���ٸ�, �ƽ��Ǹ� �� ���� ��, ���� �Բ� ��å�� ������(���� ������ ȥ�� ����). ��ȣ���� �� �ϸ鼭 �� �Ŀ� ó������ �ٽ� �����϶�
Join�� �� ����� ��� �������� �����ϴ� ���� �ſ� �߿��ϴ�. �̷��� ������ ���� �ʴ´ٸ�, ������ �� ���� ����� ���� �� �ִ�.
���� ������ ���� �ٲ㼭 SELECT ������ ������ ���ؼ� �˾� ����
SELECT [DISTINCT] expression1 [AS nom-attribute] {, expression-i [as nom-attribute-i]} [INTO TABLE classname] [FROM from-list] [WHERE where-clause] [GROUP BY attr_name1 {, attr_name-i....}] [ORDER BY attr_name1 [ASC | DESC ] [USING op1 ] {, nom-attribute-i...}] [UNION {ALL} SELECT ...]�ϳ��� ���캸��
DISTINCT: | row�� �ߺ��Ǿ� ���ϵǴ� ���� ���´�. |
expression1: | �츮�� ���ϵDZ� ���ϴ� ���̴�. ���� FROM �ڿ� ���� Table�� �÷��� �ȴ�. |
AS nom-attribute: | �÷� �̸��� ����(alias)�̴�.���� ���:
manu=> select cod_country from countries; cod_country - ----------- 1 2 3 (3 rows)����(alias)�� �̿��ϸ�: manu=> select cod_country as countr from countries; countr - ------ 1 2 3 (3 rows) |
INTO TABLE: | ����� �ٸ� Table�� ���� ���� �ǰ� �Ѵ�. ( INSERT ... SELECT...�� ����) |
FROM: | Table���� ����̴�. |
WHERE: | ���� ��, selection statement (union �� selection criteria). |
GROUP BY: | group criteria, Ư���� ��ȭ�ϴ� �Լ��� ���δ�. |
ORDER BY: | ���ϵǴ� row�� ������ ���Ѵ�. ASC:���� ����, DESC:���� ����, USING:(expression) ��Ͽ� ������ ���ǵǾ� ���� ���� ��. |
UNION ALL SELECT: | This says to add to the results of the first SELECT this second SELECT that can be different tables, but return the same number of columns. |
SELECT ������ DB�κ��� �������� �����ϴ� �� �Ӹ��ƴ϶�, item�� ������ ���� �ִٴ� ���� ���Ҵ�.
�̰��� ������ 10%�� �����Ѵ�.
��밡���� �Լ��鿡 ���ؼ� �˾� ����:
COUNT(): | NULL�� �ƴ� row�� ���� �����Ѵ�. |
SUM(): | �÷����� ���� �����Ѵ�. |
AVG(): | �÷����� ����� �����Ѵ�. |
MIN(): | �÷����� ���� ���� ���� �����Ѵ�. |
MAX(): | �÷����� ���� ū ���� �����Ѵ�. |
FLOAT(int): | ������ �Ǽ��� �����Ѵ�.,FLOAT(12345) |
FLOAT4(int): | ������ �Ǽ��� �����Ѵ�.FLOAT4(12345) |
INT(float): | �Ǽ��� ������ �����Ѵ�.,INT(123.456) |
LOWER(text): | text�� �ҹ��ڷ� �����Ѵ�. |
UPPER(text): | text�� �빮�ڷ� �����Ѵ�. |
LPAD(text, long, char): | text�� ���ʺ��� long��ŭ�� ���̱��� char�� ä���. |
RPAD(text, long, char): | text�� �����ʺ��� long ��ŭ�� ���̸� char�� ä���. |
LTRIM(text, char): | text�� ���ʺ��� text���� ��� char�� �����. |
RTRIM(text, char): | text�� �����ʺ��� text���� ��� char�� �����. |
POSITION(string IN text): | string�ȿ��� text�� ��ġ�� �����Ѵ�. ������, �̰��� �۵����� �ʴ´�. |
SUBSTR(text,from[,to]): | text�� from��ġ���� to����, Ȥ�� ��Ʈ���� ����������(to�� �����Ǿ��ٸ�) ���ڸ� �����Ѵ�. |
DATETIME(date, hour): | datetime�� ������ date (YYYY-MM-DD) �� hour (HH:MM) �������� �ٲ۴�. |
�̰͵��� SQL�� �ִ� ����� �Լ����̴�. �̵� �Լ��� ANSI SQL�� ���ǵǾ� �ְ�, Postgres95���� ���ǵǾ� �ִ�.
���ݱ��� SELECT������ WHERE�� ���� ���Ұ�, ������ ���� ���� �� �ִ�.
AND column = value
������ ���ϻ��̰�, ������ ���� ���ų�, �ƴϸ� �Բ� ��� �� �� �ִ�.
AND, OR, NOT, IN, IN ALL, =, !=, >, <, (SELECT....), LIKE � ���� ���� �� �ִ�. ��:
WHERE column IN (SELECT DISTINCT column FROM table WHERE ....) column IN ('value1','value2','value3',...) (column = 'value' and column = 'other_value' OR column != 'value')!= '���� ����'�� ��Ÿ����.
WHERE column LIKE '%Pepito%'%�� wildcard�̴�. ���� ��� "Pepito"�� ���ڿ��� �ȿ� ������ ���̴�.
WHERE column LIKE 'Pepito%'"Pepito"�� ���ڿ��� �����̸� ���̴�.
WHERE column LIKE '%Pepito'�̴� "Pepito"�� ���ڿ��� ���̸� ���̴�.
WHERE�� �Բ� ���� �� �ִ� ��� �ɼǵ��� �����Ҹ��� �ð��� ����. �Ѱ�� ���α����� �����̳�, ������ ���� �۾��� �Ѱ迡 �ִ�. ���� SELECT ������ ���ϰ�, ������ �ΰ����� ���ؼ� ������ ����
UPDATE ������ WHERE�� �ִ� ���ǵ��� �����ϴ�, �� row Ȥ�� ���� row�� ���� ������ �� �ִ�.
SYNTAX:UPDATE table SET column-1 = expression-1 [, column-i = expression-i] [WHERE condition]Where:
table: | �����ϰ��� �ϴ� Table�̴�. �ѹ��� �ϳ��� Table���� ������ �� �ִ�. |
column: | �����ǰ��� �ϴ� �÷��̴�. |
expression: | �÷��� �����Ǿ��� ���̴�. �� ���� �����̰ų�, Ȥ�� �Լ��κ��� ���� ��� ���� �� �ִ�. |
condition: | SELECT�� ���� �־��� �����Ǿ��� ���ǵ��̴�. |
DELETE FROM table [WHERE condition]
table: | ������ row�� �ִ� Table�� �̸��̴�. �ѹ��� �ϳ��� Table���� ������ �� �ִ�. |
condition: | SELECT�� ���ؼ� �־��� ������ ���ǵ��̴�. NOTE: WHERE�� �� ���� Table�� ��� row���� ������ ���̴�.
|
����:������
�������� ����: Dallas L. Gale
�� ������Ʈ�� Miguel Ángel Sepúlveda���� ���� �����˴ϴ�. © Manuel Soriano 1998 LinuxFocus 1998 |