Home Map Index Search News Archives Links About LF
[Top
Bar]
[Bottom Bar]
[Photo not available]
Manuel Soriano

�P�@���pô


���e�ؿ��G
�ǽ�
�إߪ���
������
�b���椤���J���
��X���
�ϥ� WHERE ���Ӹ`
UPDATE ���O
DELETE ���O
�ѦҸ��

SQL �оǫ��n
Part II

[Illustration]

���e�K�n�G �b�o�� SQL �оǫ��n part II �̭��A �ڭ̱N�|�DzߴX�Ӱ򥻪� SQL ���O�C




�ǽ�

�o�O SQL �ҵ{���ĤG�ӳ����A �b�o�̡A �ڭ̷|�N�J�I�����b�X�� SQL ���O���Ϊk�A �]�t���檺�إߡB ���B �H�ΧR���C

���F�H�W���쪺�o�ǡA �ڭ��ٷ|�S�O�`�N SELECT ���O���Ϊk�A �b�ڪ��P�_�̭��A �o�O�Ҧ����O�̭��̭��n���@�ӡC

�Ʊ�A�|���w�o�g�峹�A �]�Ʊ�A����q����o�@�ǯq�B�C

�إߪ���

�ڭ̦b�e�@�g�A ���w�˪��ɭԬݹL�A�i�H�� CREATE ���O�[�W TABLE �w�q�� (qualifier) �ӫإߪ���A �ƹ�W CREATE ���O�A �i�H�ΨӫإߡG

  • �ϥΪ� (users)�G CREATE USER
  • ���� (tables)�G CREATE TABLE
  • ���� (indexes)�G CREATE INDEX
  • ���� (views)�G CREATE VIEW

CREATE ���O�|�i�D SQL �޲z�̷dzƭn�إ߬Y�ǪF��F�A �ݷ|��A �ڭ̦A�Ӭݬݦ����ǪF��A �H�έn�p��ءC

���L�A �ڭ̲{�b������A �b��Q�� CREATE ���O�ӫإߪ���G

�y�k

CREATE TABLE name ( column type [DEFAULT value] [NOT NULL], ...
      [INHERITS (inherits, ...)]
      [CONSTRAINT constraints CHECK (test), CHECK (test)] );
�䤤�G
name: �������檺�W�١A ���򪺫��O�A ���γo�ӦW�٨ӰѦҳo�Ӫ���C
Column: ��� (column) ���W�١C
Type: ��ƪ����O (varchar, char, int, date, time, timestamp)�A Postgres ���䥦��ƪ����O�A���L�o�ǫ��O�ä��ۮe�� ANSI SQL�C
Value: ��쪺�w�]�ȡC
Inherits: �o�O Postgres �S�����ѼơA �Ψөw�q�ثe����O�q�t�@�Ӫ����~�ӦӨӪ��A �s�ت����氣�F���ڭ̫إߪ����H�~�A�ٷ|���~�ӦӨӪ����C
Nom_cons: �w�q�ק���@����ƮɩһݲŦX������ʳW�h (rule of integrity)�C
Test: �ˬd������C
�d�ҡG
CREATE TABLE countries (
         cod_country integer NOT NULL,
         name varchar(30))
   CONSTRAINT cod_raro CHECK (cod_country > 0 AND cod_country < 154);

�o�ӽd�Ҹ̭��إߤF�@�ӦW�� "countries" ������A�ӥB�C�����J�s���@����ƮɡA �������ŦX���U���o�dzW�h�G

  • cod_country ( ��a�N�X ) ���ର NULL�A ���p�A�n���J����ƨS�����w cod_country ���ܡA SQL �޲z�̷|�^�����U�����~�T���G
    ExecAppend: Fail to add null value in not null attribute cod_country
    
  • cod_country ���ȥ����j�� 0 �B�p�� 154�A ���p�A���J����Ƹ̭��A cod_country ���Ȥ����T���ܡA �|�o��H�U�����~�T���G
    ExecAppend: rejected due to CHECK constraint cod_raro
    

�����G

�ƻ�O NULL �O? �b SQL �̭��A �s�b�ۡu����ơv��u�S����ơv��ت��A�A ��ڤW�A �ڭ̦��ɭԤ]�|����椤�A ���̨S����ƪ����Ǭ����P�쿳��F �ӽѦp�u�s�v (0) �H�Ρu�ťաv (spaces) ���A ����O��ơA ����O�S����ơC SQL �޶i�F NULL �������A �����γo�ذ��k�A ���U�K�O�@�ӹ�ڪ��Ҥl�G

���]�ڦ��@�Ӧs��b�檺����A ���̭����U�C�����G customer, value, date_issued, date_paid

���إߤ@����ƪ��ɭԡA �ڷ|���J�o�Ǹ�ơG customer, value, date_issued

�ҥH�@�}�l���� date_paid ���O�ťժ��A �M���ǵ۩��U���O�A �ڴN�i�H���ݭn�h�I�����b���o�G

SELECT * FROM bills WHERE date_paid IS NULL;

���ǤH�i��|�D�i�A �i�H�b date_paid ����@�ӹs (0) �ӥN���|���I���A �S���A ���L�s (0) �ä��O�@�Ӥ�����榡�A �ιs�ӥN���|���I�����ܡA �N�S����k����� date_paid �]������榡�A �]�N����o���u����v���A���\��F�C

�H NULL �إ߸�ƪ��Ҥl�G

insert into countries values (15, NULL);
�ΡG
insert into countries (cod_country) values (27);

�b���J���O�̭��S�����w "name" ��쪺�ȡA �N�����N�|�Q�]�w�� NULL�C

������

�b PostgreSQL �̭��A ����إߥH��n�A�ܧ󪺸ܡA �N�u��W�[�s�����G

ALTER TABLE table ADD name type;

�䤤�G

Table �n��諸���檺�W�١C
Name �n�W�[�����W�١C
Type ����쪺��ƫ��O (�Ѧ� CREATE TABLE)

�b���椤���J���

�{�b�ڭ̨ӱN��ƴ��J����̭��G

�y�k�G
INSERT INTO table [(column, column, ...)] VALUES (value-1, value-2,
...)
�Ϊ̡G
INSERT INTO table [(column, column, ...)] SELECT ....

�p�P�W�z�ҦC�A �b���椤���J��ƪ��覡����ءA �@�جO���w�U����쪺�ȡB �@��@�檺���J�A �@�جO�Q�Τ@�� SELECT ���O�A ���X���@���Φh������ơA �����J����̭��C

���ڭ̦b����̭����J��ƪ��ɭԡA �ä��@�w�n���w�Ҧ���쪺�ȡA ���L��󨺨ǧڭ̨S�����w���e�����A �t�η|�۰ʪ��⥦���ȳ]�� NULL�C

�S�A �b INSERT ���O�̭��Y�S�����w��쪺�ܡA�t�η|�{���ڭ̱N�|�b VALUES �̭����P�Ҧ���쪺�ȡA�Ҧp�G

INSERT INTO countries VALUES (34, 'Spain');
���U�o�ӫ��O�N�U���F�G
INSERT INTO countries VALUES (34);
�U���o�ˤl�~���T�G
INSERT INTO countries (cod_country) VALUES (34);

�ګ�ij�Ҧ����t�b C �{���B �άO��Ʈw�����禡�A �̭��� SQL ���O�A ��������w�ҭn�Ψ쪺���A �ƹ�W�A ���p���H�b����̭��A �W�[�@�ӷs����쪺�� (ALTER TABLE)�A �S��������쪺 insert ���O�N�|�o�Ϳ��~�A �Ҧp�G


INSERT INTO countries VALUES (34, 'Spain');

INSERT 18301 1

ALTER TABLE countries add population integer

INSERT INTO countries VALUES (34, 'Spain');

�W�������O�N�|�y����Ķ�����~ (parser error)�A �i�D�z�ʤ֤F population ����ơC

�����G

PostgreSQL �ä��|���Ϳ��~�A ���|����� "population" �ܦ� NULL�C �o�u�O PostgreSQL ���S�ʡA ��L�� SQL �{�޲z�{�����|���Ϳ��~�C

�ڭ��٦��t�@�������� INSERT ���O�A ���O�b sub-select �\�त�ϥΡC

�o�������� insert �`�Q�Φb�إ߼Ȧs����A �άO�Φb������թʹB��u�@������W�C

�W�z���\��A �|��ŦX���󪺸�ơA The part replaced is that which touches the data itself, this comes from the SELECT instructions that were carried out previously and the inserting of the data. The instruction SELECT can return one or more rows, this instruction SELECT has the same restrictions of the same SELECT.

��X���

��F�ڴ��满�����a���o ! :-))

�ڭ̤w�g�����F�n�X�ӥ��n�� SQL ���O�A ���L���p SQL �y���ֱ��F SELECT�A ���N�n���Y�o�����t�N��@�ˡA �L���F�C

SELECT ���O���\�ڭ��ǥѤ@�Ƿj�M�W�h�A �������Ʈw������ơA ���L�A �Ѧp�ˬd����B ���涡���p�����Y�B �B�z��ƪ��禡�B �άO����Ψӷj�M���W�h�����A ���|��䲣�ͤ@�ǭ���C

�@�ӨҤl�G

select * from countries;
�t�@�ӨҤl�G
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;

���ڸ����@�U�A �ڷQ�n���D�Ҧ��u��a�v���u�H�f�ơv�A �è̤H�f�h��q�p��j�ƦC�n�C

���F�W�z���\��A �ڦb���� countries ���A �s�W�F�@����� (population)�C

��ӹL�{�j�P�W���o�ˡG
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;

�{�b�A �ڭ̤���H ALTER TABLE �ӧ������A ���O�i�H�ϥ� UPDATE�A �o�\����٨S������A �ҥH�u�n���ϥ� "�ŶK" �N�N�@�U�A �j�a���ӳ��ມ�N�a :-))

�ڭ̥i�H���� QUERY �\��A �Ө䵲�G���ӷ|���o�ˡG

name     |    sum
- ---------+-------
country 1| 705559
country 2|1212418
country 3|2804018
(3 rows)
�ڭ̥i�H����U�C���T�{�ʧ@�G

select sum(population) from counties where cod_country = 1;

���G�|�O�G
   sum
- ------
791986
(1 row)

!!!!!! �~�M���a�褣�@�� !!!!!!

���ڭ̬ݬݪ��� states �̡A �֤F state 3 ����ơA �ҥH�ЦA����G

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');
�A���@���T�{�ˬd�A ���G�p�U�G
name     |    sum
- ---------+-------
country 1| 791986
country 2|1872205
country 3|3003629 

�ڭ̨C�Ӱ�a���֤F state 3 ����ơC

�ѩ�֤F�o�Ǹ�ơA ���椧�������X���Y�O EXACT�A �n�O�o���w���ձ��󬰧����ŦX�A �~����o��ơC

���ڭ̬ݬ� WHERE: b.cod_country = a.cod_country ���Ĥ@�ӳ����C

�o���ܡA �ڱN�� country �P states ��Ӫ��椤�A ��a�N�X�ۦP�������X�_�ӡA �Х���ڭ̿�J�i�h����a��Ƶ��O�_�ӡG

�U�C���d�ҶȨѸ������ΡA �Ф��n�Ӱ��C

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 ����Ƴ����G
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;
All of the states 3 from every country are missing, but in the country table the corresponding data of the states 3, so it is normal that we don't add the country data with the states with code 3 to be discarded in the second part where:
        AND (c.cod_country = b.cod_country
        AND c.state_code = b.state_code)

The state exists in the counties table but NOT in the states table.

For those that haven't understood, take an aspirin, go walk your dog (if you don't have a dog, go walk without a dog), breath a little fresh air and comeback to begin from the first exercise.

Is is very important to understand how the joining of data is carried out, without this the development that we do can have unpredictable results.

Let's shift gears and start with the SELECT command syntax.

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 ...] 
Step by step:
DISTINCT: This is to eliminate duplicate row returned
expression1: what we want returned, normally a column for a table from the list FROM
AS nom-attribute: an alias for the name of a column, example:
manu=> select cod_country from countries;
cod_country
- -----------
          1
          2
          3
(3 rows)          
Now with the alias:
manu=> select cod_country as countr from countries;
countr
- ------
     1
     2
     3
(3 rows)
INTO TABLE: allow the resulting row to be inserted directly into another table (see INSERT ... SELECT...)
FROM: list of input tables
WHERE: selection statement (union and selection criteria).
GROUP BY: group criteria, certain functions that are used in expressions might need grouping
ORDER BY: Order criteria of the returned rows, ASC ascending order, DESC descending order, USING if the column that defines the order is not in the list (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.

�ڭ̨��Ѩ�A SELECT ���O���ȯ���Ǧ^��Ʈw�̪����ءA �٥i�H�ק復�̡G


SELECT SUM(salary * 1.1) - SUM(salary) AS increment FROM employees;

�p�G�N salary �W�[ 10%�A �o�|�Ǧ^��s�W���I�ڳ����C

���ڭ̨Ӭݤ@�ݡA �o�̴��ѤF���Ǩ禡�\��G

COUNT(): �^�Ǩ�Ȥ��� NULL ����Ƶ��ơC
SUM(): �^�ǬY�ӼƦr��쪺�`�M�C
AVG(): �^�ǬY�ӼƦr��쪺�����ȡC
MIN(): �^�ǬY����줤�A �Ʀr�̤p���ȡC
MAX(): �^�ǬY����줤�A �Ʀr�̤p���ȡC
FLOAT(int): �^�Ǥ@�ӭ���ׯB�I�� FLOAT8, FLOAT(12345)�C
FLOAT4(int): �^�Ǥ@�ӳ��ׯB�� FLOAT4, FLOAT4(12345)�C
INT(float): �^�Ǥ@�ӱq���ׯB�I�� (FLOAT4) ��Ӫ���ơA �p INT(123.456)�C
LOWER(text): ��r�� text �ܦ��p�g�C
UPPER(text): ��r�� text �ܦ��j�g�C
LPAD(text, long, char): �� char �������� text ������A ���`���צ��� long �Ӧr���C
RPAD(text, long, char): �� char �������� text ���k��A ���`���צ��� long �Ӧr���C
LTRIM(text, char): ������� text ����Ҧ����r�� char ���������C
RTRIM(text, char): ������� text �k��Ҧ����r�� char ���������C
POSITION(string IN text): �Ǧ^�b��� text ���A string ����m�A ���L�{�b����ΡC
SUBSTR(text,from[,to]): �Ǧ^�b��� text ���A �q��m from ���m to�A �Ψ�r�ꪺ�̫᭱�C
DATETIME(date, hour): ��@�ӡu����ɶ��v�榡�A �ഫ���@�ӡu����v(YYYY-MM-DD) ��@�ӡu�ɶ��v(HH:MM)�C

�o�ȬO SQL ���Ѫ��禡�����@�p�����A �ӥB�o�X�Ө禡�b ANSI SQL �̭����w�q�A �b Postgres95 �̭��]�i�H���C

�ϥ� WHERE ���Ӹ`

��ثe����A �ڭ̤v�g�ݨ�A �b SELECT ���� WHERE �����A �i�H�[�J�o�˪��F��G

AND column = value

�W���O�Ӥp�d�ҡA �i�ѨϥΩβզX���ﶵ�p���G

AND, OR, NOT, IN, IN ALL, =, !=, >, <, (SELECT....), LIKE �Ρu���k�A���v�]�b��C�A ���U�O�d�ҡG

WHERE 
   column IN (SELECT DISTINCT column FROM table WHERE ....)
   column IN ('value1','value2','value3',...)
   (column = 'value' and column = 'other_value' OR column != 'value')
!= �o�ӲŸ��P�q�������� (NOT EQUAL)

LIKE ���\�b��줤�M��r��ɡA �ϥθU�βŸ��G
WHERE column LIKE '%Pepito%'
% �b���Ҥ��O�ӸU�βŸ��A �p�G�r�ꤤ�t�� "Pepito"�A ���|�Ǧ^�u�u�v�C
WHERE column LIKE 'Pepito%'
�p�G�r�ꪺ�}�Y�O "Pepito"�A ���|�Ǧ^�u�u�v�C
WHERE column LIKE '%Pepito'
�p�G�r�ꪺ�����O "Pepito"�A ���|�Ǧ^�u�u�v�C

�b�o�̡A �ڭ̨S���R�����ɶ��A �h�C�X�Ҧ��M WHERE �������ﶵ�A �䭭��O�Ӧ۩�{���]�p�����Q���O�A �άO�U�O���{�Ǥ��R�C

�{�b�ڭ̧� SELECT �M��������y�k�A ���᭱��`�A�ͽסC

UPDATE ���O

UPDATE �o�ӫ��O�A ���\�ڭ̭ק���椤�@�өΦh�ӦC (row)�A ���M�� WHERE ���ҩw�q�����󬰦�C

�y�k�G
UPDATE table SET column-1 = expression-1 
                 [, column-i = expression-i] 
       [WHERE condition] 
�䤤�G
table: �Q�n�ק諸���� (table) �W�١A�P�@�ɶ����u���@�Ӫ���C
column: �Q�n�ק諸��� (column)�C
expression: �Q�n�ǵ���� (column) ���ȡA �ǰe���ȡA �i�H�O��� (function) �����G�άO�T�w���Ʀr�C
condition: condition �w�q�F�i�ק諸�d��A �ӥB�o�̪��W�h (rules) �]�A�Ω� SELECT �C

DELETE ���O

DELETE �o�ӫ��O���\�ڭ̭ק���� (table) ���@�өΦh�ӦC (row) �C

�y�k
DELETE FROM table
       [WHERE condition] 
�䤤�G
table: ���椤���C�|�Q�R�����A �b�P�@�ɶ����u���@�Ӫ���C
condition: condition �w�q�F�i�ק諸�d��A �ӥB�o�̪��W�h (rules) �]�A�Ω� SELECT�C
�`�N�G �p�G�S���[�J WHERE ���ܡA ���b���椤�Ҧ����C�A �N�|�Q�R���C

�ѦҸ��

SQL: Introduction to SQL. Installation of PostgreSQL, ���t�C�峹�����Ĥ@�g�C


����� �G�쩾�B ������ ��½Ķ

�D������ Miguel Ángel Sepúlveda ���@
© Manuel Soriano 1998
LinuxFocus 1998