: ORDER BY

Size: px

Start display at page:

Download ": ORDER BY"

ふじよしごちょう
4 years ago
Views:

1 : ORDER BY WHERE SELECT ORDER BY SQL JOIN ON (NATURAL JOIN) ( ) i

2 1 1 : ORDER BY SELECT ORDER BY ORDER BY ORDER BY, (ascend) ORDER BY ASC SELECT * FROM spring ORDER BY area; name area (9 rows) 1.2 (descend) ORDER BY DESC 1.3, ORDER BY 1, 2,... 1 ORDER BY

3 2, 1, 2 ASC DESC SELECT * FROM spring ORDER BY area DESC, name; name area (9 rows) name 1.4 WHERE SELECT ORDER BY SELECT WHERE ORDER BY SELECT * FROM area_code WHERE code = OR code = OR code = ORDER BY code; name code (3 rows) population ( 27 )

4 3 SELECT * FROM population ; name popul house 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 > ; name popul house psql psql \d population \d SQL ;

5 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 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 = ; name code

6 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 area code name ( ): (varchar) code ( ): (char(5)) SELECT * FROM area_code ; name code (5 rows) 2. n hospital

7 6 code ( ): (char(5)) num ( 2 ): (int) SELECT * FROM n_hospital ; (5 rows) 204 SELECT 3.2 SELECT FROM FROM FROM ( ) 3 SELECT * FROM area_code, n_hospital; name code code num ( n )

8 (25 ) WHERE area code n hosptal code.. ( ) SELECT * FROM area_code, n_hospital WHERE area_code.code = n_hospital.code; name code code num (5 ) JOIN ON JOIN ON SQL

9 8 SELECT * FROM area_code JOIN n_hospital ON area_code.code = n_hospital.code; (NATURAL JOIN) area code n hospital SELECT * FROM area_code NATURAL JOIN n_hospital; 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 (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 = ; code name num (1 )

10 9 SELECT area_code.name, n_hospital.num FROM area_code, n_hospital WHERE area_code.code = n_hospital.code AND (area_code.code = OR area_code.code = ); name num (2 ) SQL 1. (code) 2. AND OR 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 = ; area_name hospital_num (1 ) 4.2 ( ) ( ) SQL 4 FROM AS 5 SELECT WHERE area code a, n hospital h 4 SELECT FROM 5 AS

11 10 SELECT a.name, h.num FROM area_code AS a, n_hospital AS h WHERE a.code = h.code AND a.code = ; name num (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 (1 ) 4. SQL ( ) name code name popul house (5 )

12 11 5. spring area code SQL name location (9 ) 6. area code population ( popul) SQL area code, population, n hospital name code name popul house code num (5 ) 8. SQL name code popul house hospital (5 )

,, create table drop table alter table

,, create table drop table alter table PostgreSQL 1 1 2 1 3,, 2 3.1 - create table........................... 2 3.2 - drop table............................ 3 3.3 - alter table............................ 4 4 - copy 5 4.1..................................