11 7 8 1 : ORDER BY 1 1.1......................................... 1 1.2......................................... 1 1.3................................ 1 1.4 WHERE SELECT ORDER BY.................. 2 2 2 2.1...................................... 2 2.2 SQL.............................. 3 2.3........................... 3 2.4.............................. 4 3 5 3.1........................................... 5 3.2......................................... 6 3.3.......................................... 7 3.3.1...................................... 7 3.3.2 JOIN ON............................... 7 3.3.3 (NATURAL JOIN)..................... 8 3.3.4.................................. 8 4 9 4.1......................................... 9 4.2 ( )........................... 9 5 10 i
1 1 : ORDER BY SELECT ORDER BY 1 1.1 ORDER BY ORDER BY, (ascend) ORDER BY ASC SELECT * FROM spring ORDER BY area; name area --------------+------- 01202 01202 01202 01202 01236 01337 01337 01343 01345 (9 rows) 1.2 (descend) ORDER BY DESC 1.3, ORDER BY 1, 2,... 1 ORDER BY
2, 1, 2 ASC DESC SELECT * FROM spring ORDER BY area DESC, name; name area --------------+------- 01345 01343 01337 01337 01236 01202 01202 01202 01202 (9 rows) name 1.4 WHERE SELECT ORDER BY SELECT WHERE ORDER BY SELECT * FROM area_code WHERE code = 01236 OR code = 01202 OR code = 01337 ORDER BY code; name code --------+------- 01202 01236 01337 (3 rows) 2 2.1 population ( 27 )
3 SELECT * FROM population ; name popul house --------+--------+-------- 265979 123950 46390 18508 28120 11141 4226 1660 15946 6628 name ( ) popul ( ) house ( ) SQL 2.2 SQL SQL 1. ( ) SELECT * FROM population WHERE name = ; ERROR: " " 1: select * from population where name = ; ^ ( ) 2. SELECT * FROM population WHERE popul > 200000 ; name popul house --------+--------+-------- 265979 123950 2.3 psql psql \d population \d SQL ;
4 db_a=> \d population "public.population" --------+-------------------+-------- name character varying popul integer house integer (column) (type) population name character varying ( ) popul house integer ( ) population name SQL ( ) popul house 2.4 area code SELECT * FROM area_code; name code --------+------- 01202 01236 01337 01343 01345 name code db_a=> \d area_code "public.area_code" --------+-------------------+-------- name character varying code character(5) name character varying ( ) code character(5) (5 ) SQL area code code ( ) SELECT * FROM area_code WHERE code = 01202 ; name code --------+------- 01202
5 (1 row) SELECT * FROM area_code WHERE code = 01202; ERROR: : character = integer 1: select * from area_code where code = 01202; ^ HINT: code ( ) like code like ( ) 5 code 5 3 3.1 1. area code name ( ): (varchar) code ( ): (char(5)) SELECT * FROM area_code ; name code --------+------- 01202 01236 01337 01343 01345 (5 rows) 2. n hospital
6 code ( ): (char(5)) num ( 2 ): (int) SELECT * FROM n_hospital ; 01202 204 01236 23 01337 18 01343 2 01345 9 (5 rows) 204 SELECT 3.2 SELECT FROM FROM 1 2... FROM ( 1 2...) 3 SELECT * FROM area_code, n_hospital; name code code num --------+-------+-------+----- 01202 01202 204 01236 01202 204 01337 01202 204 01343 01202 204 01345 01202 204 01202 01236 23 01236 01236 23 01337 01236 23 01343 01236 23 01345 01236 23 01202 01337 18 01236 01337 18 01337 01337 18 01343 01337 18 2 3 ( n )
7 01345 01337 18 01202 01343 2 01236 01343 2 01337 01343 2 01343 01343 2 01345 01343 2 01202 01345 9 01236 01345 9 01337 01345 9 01343 01345 9 01345 01345 9 (25 ) 3.3 3.3.1 WHERE area code n hosptal code.. ( ) SELECT * FROM area_code, n_hospital WHERE area_code.code = n_hospital.code; name code code num --------+-------+-------+----- 01202 01202 204 01236 01236 23 01337 01337 18 01343 01343 2 01345 01345 9 (5 ) 3.3.2 JOIN ON JOIN ON SQL
8 SELECT * FROM area_code JOIN n_hospital ON area_code.code = n_hospital.code; 3.3.3 (NATURAL JOIN) area code n hospital SELECT * FROM area_code NATURAL JOIN n_hospital; 3.3.4 SELECT SELECT area_code.code, area_code.name, n_hospital.num FROM area_code, n_hospital WHERE area_code.code = n_hospital.code; code name num -------+--------+----- 01202 204 01236 23 01337 18 01343 2 01345 9 (5 ) SELECT area_code.code, area_code.name, n_hospital.num FROM area_code, n_hospital WHERE area_code.code = n_hospital.code AND area_code.code = 01202 ; code name num -------+--------+----- 01202 204 (1 )
9 SELECT area_code.name, n_hospital.num FROM area_code, n_hospital WHERE area_code.code = n_hospital.code AND (area_code.code = 01202 OR area_code.code = 01337 ); name num --------+----- 204 18 (2 ) SQL 1. (code) 2. AND OR 4 4.1 name num SELECT SQL AS SELECT area_code.name AS area_name, n_hospital.num AS hospital_num FROM area_code, n_hospital WHERE area_code.code = n_hospital.code AND area_code.code = 01202 ; area_name hospital_num -----------+-------------- 204 (1 ) 4.2 ( ) ( ) SQL 4 FROM AS 5 SELECT WHERE area code a, n hospital h 4 SELECT FROM 5 AS
10 SELECT a.name, h.num FROM area_code AS a, n_hospital AS h WHERE a.code = h.code AND a.code = 01202 ; name num --------+----- 204 (1 ) 5 1. postcode pub auth code 3 1 pub auth code ken kana, town kana 3, (ken kana ), (town kana ) 2. (a) population : name popul house (b) spring : name area 3. SQL name popul --------+-------- 265979 (1 ) 4. SQL ( ) name code name popul house --------+-------+--------+--------+-------- 01343 4226 1660 01337 28120 11141 01345 15946 6628 01202 265979 123950 01236 46390 18508 (5 )
11 5. spring area code SQL name location --------------+---------- (9 ) 6. area code population ( popul) 30000 7. SQL area code, population, n hospital name code name popul house code num --------+-------+--------+--------+--------+-------+----- 01202 265979 123950 01202 204 01236 46390 18508 01236 23 01337 28120 11141 01337 18 01343 4226 1660 01343 2 01345 15946 6628 01345 9 (5 ) 8. SQL name code popul house hospital --------+-------+--------+--------+---------- 01202 265979 123950 204 01236 46390 18508 23 01337 28120 11141 18 01343 4226 1660 2 01345 15946 6628 9 (5 )