SQL �оǫ��n Part II
���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
�����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
|
|