1,.,,,., RDBM, SQL. OSS,, SQL,,.

1,.,,,., RDBM, SQL. OSS,, SQL,,.

3 10 10 OSS RDBMS SQL 11 10.1 OSS RDBMS............................ 11 10.1.1 PostgreSQL................................. 11 10.1.2 MySQL................................... 12 10.1.3 Firebird................................... 13 10.1.4 Apache Derby................................ 13 10.1.5.................................... 14 10.2 SQL(SQL92, SQL99)............................. 15 10.3 DDL, DML, DCL.................................. 16 10.3.1 DDL(Data Definition Language)............ 16 10.3.2 DML(Data Manipulation Language)......... 16 10.3.3 DCL(Data Control Language)............. 16 10.4.................................. 17 10.4.1................................... 17 10.4.2.................................... 18 10.5......................................... 21 10.5.1................................... 21 10.5.2................................. 21 10.5.3................................... 21 10.5.4................................... 21 10.6........................................ 22 11 SQL 23 11.1 (SELECT)................................... 23 11.2.................................... 25

4 11.2.1................................. 25 11.2.2................................. 27 11.2.3................................. 33 11.3.................................. 34 11.4............................... 35 11.4.1................................... 36 11.4.2................................. 38 11.4.3........................... 40 11.5................................ 43 11.5.1................................... 43 11.5.2................................... 46 11.6.................................... 53 11.6.1............................ 53 11.6.2............................ 54 11.7,,................................... 56 11.7.1 (INSERT)............................... 56 11.7.2 (UPDATE)............................... 57 11.7.3 (DELETE)............................... 58 11.8............................... 59 11.9................................... 61 11.9.1 (CREATE TABLE)....................... 61 11.9.2 (DROP TABLE)......................... 63 11.10.................................. 64 13 DB 81 13.1.................................... 81 13.1.1.................. 83 13.1.2................. 84 13.1.3........... 88 13.1.4......... 90 13.2 ( )................................... 94 13.3 ( )................................ 97 13.4.............................. 100

5 13.4.1................................. 100 13.4.2............................ 100 13.4.3................................. 101

7

9

10

11 10 OSS RDBMS SQL 10.1 OSS RDBMS ( OSS). RDBMS OSS RDBMS.[1][2][3] OSS RDBMS PostgreSQL MySQL. RDBMS, RDBMS C/C++ Firebird Ingres, Java Apache Derby HSQLDB. PostgreSQL MySQL Firebird Apache Derby. 10.1.1 PostgreSQL PostgreSQL( ), 1970 (UCB) Ingres., PosgreSQL. PostgreSQL Global Development Group.,. PostgreSQL (http://www.postgresql.jp/).[4][5] BSD.,,.[4]

12 10 OSS RDBMS SQL, RDBMS, 10.1.2 MySQL MySQL( ), MySQL 1995 MySQL1.0. 2008 MySQL (Sun Microsystems),., M SQL MySQL (http://www.mysql.gr.jp/).[6] GPL(GNU GeneralPublic License),.[6],, (Web DB )

10.1. OSS RDBMS 13 10.1.3 Firebird Firebird( ), BorlandSoftware 2000 InterBase6. Firebird, InterBase,. Firebird (http://tech.firebird.gr.jp/).[7] OSI MPL(Mozilla Public License) IPL(Interbase Public License), IDPL(Initial Developer s Public License),. IPL InterBase, IDPL Firebird.[7],, 10.1.4 Apache Derby Apache Derby( ), 2004 IBM Apache Cloudscape. Cloudscape, IBM.[8]

14 10 OSS RDBMS SQL Apache Licence 2.0 (Apache (ASF) )[8],, PureJavaDB, Java XML 10.1.5 OSS RDBMS RDBMS. OSS RDBMS,, OS,,,, RDBMS.

10.2. SQL(SQL92, SQL99) 15 10.2 SQL(SQL92, SQL99) RDBMS, RDBMS., RDBMS,. SQL( )., SQL. SQL (ISO) (ANSI), (JIS). SQL92, ANSI ISO 1992. SQL2. SQL, 1999 SQL99 ( SQL3). RDBMS SQL92. PostgreSQL MySQL Firebird Apache Derby SQL92 SQL99. RDBMS, SQL RDBMS.

16 10 OSS RDBMS SQL 10.3 DDL, DML, DCL SQL.. 10.3.1 DDL(Data Definition Language) ) CREATE DROP 10.3.2 DML(Data Manipulation Language) ) SELECT INSERT DELETE UPDATE 10.3.3 DCL(Data Control Language) ) BEGIN COMMIT ROLLBACK

10.4. 17 10.4 SQL92/99. RDBMS. 10.4.1 CHAR(n) (n ). VARCHAR(n) (n, ). ) 123-4567 CHAR(8) 8 VARCHAR(50) 50 (, ). INTEGER (-2147483648 2147483647 ),. NUMERIC(m(,n)). ) 123 INTEGER 12,345 NUMERIC(18) 18 0.05 NUMERIC(3,2) 1, 2

18 10 OSS RDBMS SQL (, ) DATE (,, ). YEAR, MONTH, DAY. TIME (,, ). HOUR, MINUTE, SECOND. TIMESTAMP DATE TIME. ) 2010/04/01 DATE 2010/04/01 15:00:00 TIMESTAMP 10.4.2,. SQL,.. SQL,,. + - * / %

10.4. 19., TRUE( ) FALSE( )]. = <>,!= >, >=, <, <=, BETWEEN AND IN IS NULL (NULL ) IS NOT NULL LIKE [NULL( ) ( ] Null 0,.,,.,. AND OR NOT

20 10 OSS RDBMS SQL, 1 *, / 2 +, - 3 =, >, <, >=, <=, <>,!= 4 IS NULL (IS NOT NULL) BETWEEN (NOT BETWEEN) LIKE 5 NOT 6 AND 7 OR,.,.,.,,. a 5 10 ( a = 5 ) OR ( a = 10 ) a IN ( 5, 10 ) a 5 10 ( a >= 5 ) AND ( a <= 10 ) a BETWEEN 5 AND 10 a 50 200 a 10 ( a * 50 >= 200 ) AND ( a < 10 ) a NULL, b NULL ( a IS NOT NULL ) AND ( b IS NULL )

10.5. 21 10.5,,.. RDBMS. 10.5.1 ABS ROUND TRUNCATE 10.5.2 LTRIM RTRIM SUBSTR LENGTH /LEN 10.5.3 DATE YEAR /MONTH /DAY / / HOUR /MINUTE /SECOND / / DATE ADD ( ) 10.5.4 MAX MIN AVG SUM COUNT

22 10 OSS RDBMS SQL 10.6 RDBMS, SQL. SQL,.

23 11 SQL 11.1 (SELECT) ( ). ( ). [ shohin master ] shohin code shohin name tanka hanbai end char(4) varchar(10) numeric(18) date NOT NULL NOT NULL A001 35,000 2011/12/31 A002 10,000 A003 23,000 A004 10,000 2011/03/31 B001 900 B002 100 NOT NULL SELECT [ ] SELECT 1, 2, FROM ; SQL, (;). *. select * from ; SQL.( ) select 1, 2, from ; SELECT 1, 2, FROM ; 1, 2, ; ( )

24 11 SQL. SQL. SELECT 1, 2, FROM ; SELECT 1, 2, FR (SQL ) OM ; [ 1 ],. SELECT * FROM shohin master; < > A001 35,000 2011/12/31 A002 10,000 A003 23,000 A004 10,000 2011/03/31 B001 900 B002 100 6. [ 2 ],. SELECT shohin name, tanka FROM shohin master; < > 35,000 10,000 23,000 10,000 900 100 6. [ 1 ],.

11.2. 25 11.2 SELECT WHERE. [ ] SELECT FROM WHERE ; SQL,, ( ). ) 123 123 ( 123 ),. 11.2.1 < > + - * / % [ 3 ],,, 1.05. -> ( ),, 1.05

26 11 SQL SELECT shohin name, tanka, tanka*1.05 FROM shohin master; < > 1.05 35,000 36,750 10,000 10,500 23,000 24,150 10,000 10,500 900 945 100 105 6. [ 2 ],,, 2.

11.2. 27 11.2.2 < > = <>,!= >, >=, <, <=, [ 4 ], 10,000, -> ( ), 10,000 SELECT shohin name, tanka FROM shohin master WHERE tanka = 10000; < > 10,000 10,000 2. [ 3 ], 10,000. [ 4 ], 10,000.

28 11 SQL (BETWEEN AND ). WHERE BETWEEN AND [ 5 ], 500 10,000,. -> ( ), 500 10,000 SELECT shohin name, tanka FROM shohin master WHERE tanka BETWEEN 500 AND 10000; < > 10,000 10,000 900 3. [ 5 ], 1.05 10,000 30,000,, 1.05. -> ( ),, 1.05 1.05 10,000 30,000

11.2. 29 (IN ),. WHERE IN( 1, 2, ), 1, 2, [ 6 ], 10,000 23,000 35,000. -> ( ) 10,000 23,000 35,000 SELECT * FROM shohin master WHERE tanka IN (10000,23000,35000); < > A001 35,000 2011/12/31 A002 10,000 A003 23,000 A004 10,000 2011/03/31 4. [ 7 ], B001 B002. -> ( ) B001 B002 SELECT * FROM shohin master WHERE shohin code IN ( B001, B002 );

30 11 SQL < > B001 900 B002 100 2. [ 6 ],.

11.2. 31 (IS NULL, IS NOT NULL ) (NULL ). NULL, NULL. WHERE IS NULL WHERE = NULL [ 8 ], NULL. -> ( ) (NULL) SELECT * FROM shohin master WHERE hanbai end IS NULL; < > A002 10,000 A003 23,000 B001 900 B002 100 4. [ 7 ], NULL.

32 11 SQL (LIKE ) LIKE. % ( ). WHERE LIKE LIKE abc LIKE abc abc LIKE a% abc LIKE b abc LIKE %ab abc LIKE ab abc LIKE c [ 9 ], 1 B. -> ( ) 1 B SELECT * FROM shohin master WHERE shohin code LIKE B% ; < > B001 900 B002 100 2. [ 8 ], 2.

11.2. 33 11.2.3 AND OR NOT. AND OR NOT,. WHERE AND AND WHERE OR OR [ 10 ],. 10,000 NULL -> ( ), 10,000 NULL SELECT shohin name,tanka FROM shohin master WHERE tanka <= 10000 AND hanbai end IS NOT NULL; < > 10,000 1. [ 9 ],,, 1.05,. 1 A 1.05 10,000 NULL

34 11 SQL 11.3 ( ), ORDER BY. [ ] SELECT FROM WHERE ORDER BY 1 ASC[ DESC ], 2 ASC[ DESC ], ORDER BY. ASC ( ASC ) DESC [ 11 ],,. -> ( ), SELECT * FROM shohin master ORDER BY tanka ASC, shohin code ASC; < > B002 100 B001 900 A002 10,000 A004 10,000 2011/03/31 A003 23,000 A001 35,000 2011/12/31 6. [ 10 ],.

11.4. 35 11.4 ( ). [ eigo seiseki ] gakusei no gakusei name class no tensu char(4) varchar(20) char(1) integer NOT NULL NOT NULL NOT NULL Z001 A 1 70 Z002 B 1 98 Z003 C 1 50 Z004 D 2 100 Z005 E 2 80 Z006 F 3 65 Z007 G 3 69 Z008 H 1 45 Z009 I 2 60 Z010 J 3 55 Z011 K 2 90 Z012 L 3 70

36 11 SQL 11.4.1,... < > MAX( ) ( ). MIN( ) ( ). AVG( ) ( ). SUM( ) ( ). COUNT( ). *. NULL * NULL [ 12 ],. -> ( ) SELECT MAX(tensu) FROM eigo seiseki; < > MAX( ) 100 1.

11.4. 37 [ 13 ]. -> ( ) ( ) SELECT COUNT(*) FROM eigo seiseki; < > COUNT(*) 12 1. [ 11 ],. [ 12 ],. [ 13 ], ( ).

38 11 SQL 11.4.2 SQL,,. GROUP BY. [ ] SELECT FROM WHERE GROUP BY ; [ 14 ]. -> ( ) SELECT class no FROM eigo seiseki GROUP BY class no; < > 2 3 1 3.

11.4. 39 [ 15 ] 50. -> ( ) 50 SELECT class no FROM eigo seiseki WHERE tensu <= 50 GROUP BY class no; < > 1 1. 50 1 2,,.

40 11 SQL 11.4.3,. [ 16 ],,,. -> ( ), ( ), SELECT class no, count(gakusei no), MAX(tensu) FROM eigo seiseki GROUP BY class no ORDER BY class no; < > COUNT( ) MAX( ) 1 4 98 2 4 100 3 4 70 3.

11.4. 41 [ 17 ], 80,. -> ( ), ( ) 80 SELECT class no, count(gakusei no) FROM eigo seiseki WHERE tensu >= 80 GROUP BY class no ORDER BY class no ; < > 1 1 2 3 2. [ 14 ],,. [ 15 ],,,,,.

42 11 SQL SELECT [ ] SELECT 1, 2, FROM WHERE 1 AND OR AND 2 GROUP BY 1, 2, ORDER BY 1 ASC[ DESC ], 2 ASC[ DESC ], ( ) SELECT. ( ) (SELECT) (FROM ) (WHERE ) (GROUP BY) (ORDER BY),,.

11.5. 43 11.5.,,. 11.5.1,. < >, FROM INNER JOIN. [ ] SELECT 1, 2, n FROM 1 INNER JOIN 2 ON 1. = 2. ;,..

44 11 SQL FROM INNER JOIN WHERE. [ ] SELECT 1, 2, n FROM 1, 2 WHERE 1. = 2. ; [ 18 ],,.( ) -> ( ),,,, ( ) INNER JOIN SELECT shohin master.shohin code, shohin master.shohin name, juchu.juchu date, juchu.suryo FROM shohin master INNER JOIN juchu ON shohin master.shohin code = juchu.shohin code; WHERE SELECT shohin master.shohin code, shohin master.shohin name, juchu.juchu date, juchu.suryo FROM shohin master,juchu WHERE shohin master.shohin code = juchu.shohin code;

11.5. 45 [ 19 ] (.,. ),.. -> ( ),, ( ), ( ) SELECT shohin master.shohin code, shohin master.shohin name, SUM(juchu.suryo) FROM shohin master INNER JOIN juchu ON shohin master.shohin code = juchu.shohin code GROUP BY shohin master.shohin code, shohin master.shohin name; < > B002 12 B001 3 A002 3 A003 1 4.

46 11 SQL 11.5.2.,,.

11.5. 47 < > [ ] SELECT 1, 2, n FROM 1 LEFT OUTER JOIN 2 ON 1 = 2. ; [ 20 ],,.( ) -> ( ),,,,, ( ) SELECT shohin master.shohin code, shohin master.shohin name, shohin master.tanka, juchu.juchu date, juchu.suryo FROM shohin master LEFT OUTER JOIN juchu ON shohin master.shohin code = juchu.shohin code;

48 11 SQL < > [ ] SELECT 1, 2, n FROM 1 RIGHT OUTER JOIN 2 ON 1. = 2. ; [ 21 ],,.( ) -> ( ),,,, ( ), SELECT shohin master.shohin name, shohin master.tanka, juchu.shohin code, juchu.juchu date, juchu.suryo FROM shohin master RIGHT OUTER JOIN juchu ON shohin master.shohin code = juchu.shohin code;

11.5. 49 < > [ ] SELECT 1, 2, n FROM 1 FULL OUTER JOIN 2 ON 1. = 2. ; [ 22 ],,.( ) -> ( ),,,,,, ( ) SELECT shohin master.shohin code, shohin master.shohin name, shohin master.tanka, juchu.shohin code, juchu.juchu date, juchu.suryo FROM shohin master FULL OUTER JOIN juchu ON shohin master.shohin code = juchu.shohin code;

50 11 SQL [ 23 ], (.,. ) (. ),. -> ( ),, ( ), ( ) SELECT shohin master.shohin code, shohin master.shohin name, SUM(juchu.suryo) FROM shohin master LEFT OUTER JOIN juchu ON shohin master.shohin code = juchu.shohin code GROUP BY shohin master.shohin code, shohin master.shohin name ORDER BY shohin master.shohin code; < > A001 A002 3 A003 1 A004 B001 3 B002 12 6.

11.5. 51 ( ). [ 16 ],. < > Z002 EC01 I Z002 EK01 I Z004 EC01 I Z004 EK01 I Z004 EC02 II Z005 EC01 I Z009 JS01 Z Z011 EC01 I Z011 EK01 I Z012 NC01 A 10.

52 11 SQL [ 17 ],,. < > EC01 I Z002 EC01 I Z004 EC01 I Z005 EC01 I Z011 EK01 I Z002 EK01 I Z004 EK01 I Z011 EK02 II Z004 JS01 Z Z009 KC01 A NC01 A Z012 11.

11.6. 53 11.6 SQL SELECT ( ). 11.6.1 1., (=, <, <=, >, >=,!=). [ ] SELECT FROM WHERE = (SELECT FROM WHERE ); [ 24 ],. -> ( ) SELECT * FROM eigo seiseki WHERE tensu = (SELECT MAX(tensu) FROM eigo seiseki); < > Z004 D 2 100 1. [ 18 ],.

54 11 SQL 11.6.2., (IN). [ ] SELECT FROM WHERE IN (SELECT FROM WHERE ); [ 25 ] EC01 ( I) ( ),. -> ( ) EC01 SELECT * FROM eigo seiseki WHERE gakusei no IN (SELECT gakusei no FROM shutoku shikaku WHERE shikaku code= EC01 ) ORDER BY gakusei no; < > Z002 B 1 98 Z004 D 2 100 Z005 E 2 80 Z011 K 2 90 4.

11.6. 55 [ 19 ] 90,. -> < > Z002 EC01 I Z002 EK01 I Z004 EC01 I Z004 EK01 I Z004 EK02 II Z011 EC01 I Z011 EK01 I 7.

56 11 SQL 11.7,, (SELECT).., 11.7.1 (INSERT) INSERT,. [ ] INSERT INTO ( A, B, n) VALUES ( A, B, n) ; INTO VALUES. [ 26 ],. C001 50 NULL INSERT INTO shohin master(shohin code, shohin name, tanka, hanbai end) VALUES( C001,, 50, NULL); SELECT. SELECT * FROM shohin master WHERE shohin code = C001 ; [ 20 ],. SELECT. + 200 2012/12/31

11.7.,, 57 11.7.2 (UPDATE) UPDATE,. [ ] UPDATE SET = WHERE ;,.,. [ 27 ], C001 60. UPDATE shohin master SET tanka = 60 WHERE shohin code = C001 ; SELECT. SELECT * FROM shohin master WHERE shohin code= C001 ; [ 21 ], +. SELECT.

58 11 SQL 11.7.3 (DELETE) DELETE,. [ ] DELETE FROM WHERE ;,.,. [ 28 ], C001. DELETE FROM shohin master WHERE shohin code = C001 ; SELECT. SELECT * FROM shohin master WHERE shohin code= C001 ; [ 22 ],. SELECT.

11.8. 59 11.8,., (,, ),,,.,, (COMMIT).,,, (ROLLBACK)., DCL(Data Control Language). PostgreSQL BEGIN INSERT UPDATE DELETE.

60 11 SQL [ ] BEGIN; (INSERT, UPDATE, DELETE ) COMMIT; ROLLBACK; [ 29 ], A001 33300, (BEGIN), COMMIT ROLLBACK. BEGIN; UPDATE shohin master SET tanka = 33300 WHERE shohin code = A001 ; ROLLBACK; SELECT SELECT * FROM shohin master WHERE shohin code = A001 ; BEGIN; UPDATE shohin master SET tanka = 33300 WHERE shohin code = A001 ; COMMIT; SELECT SELECT * FROM shohin master WHERE shohin code = A001 ; [ 23 ], B001 500, B002 2015/12/31, (BEGIN), COMMIT ROLLBACK.

11.9. 61 11.9 DDL(Data Definition Language)L, ( ) CREATE, DROP, ALTER. 11.9.1 (CREATE TABLE) CREATE TABLE., ( ) (,NOT NULL. PostgreSQL CREATE. NULL,. [ ] CREATE TABLE ( 1, 2,, CONSTRAINT PRIMARY KEY( )) );

62 11 SQL [ 30 ]. 4 [ shohin master XXXX ] shohin code shohin name tanka hanbai end char(4) varchar(10) numeric(18) date NOT NULL NOT NULL NOT NULL CREATE TABLE shohin master 6789 ( shohin code char(4) NOT NULL, shohin name varchar(20) NOT NULL, tanka numeric(18), hanbai end date, CONSTRAINT shohin master 6789 pkey PRIMARY KEY ( shohin code ) ), SELECT, INSERT DELETE.

11.9. 63 11.9.2 (DROP TABLE) DROP TABLE. [ ] DROP TABLE ; [ 31 ] 4. DROP TABLE shohin master 6789; SELECT. SELECT.

64 11 SQL 11.10 [ 1 ] SELECT shohin name, hanbai end FROM shohin master; [ 2 ] SELECT shohin name, tanka, tanka*0.8 FROM shohin master;

11.10. 65 [ 3 ] SELECT shohin name, tanka FROM shohin master WHERE tanka >= 10000; [ 4 ] SELECT shohin name, tanka FROM shohin master WHERE tanka >10000;

66 11 SQL [ 5 ] SELECT shohin name, tanka, tanka*1.05 FROM shohin master WHERE tanka*1.05 BETWEEN 10000 AND 30000; [ 6 ] SELECT * FROM shohin master WHERE shohin name IN (,, );

11.10. 67 [ 7 ] SELECT * FROM shohin master WHERE hanbai end IS NOT NULL; [ 8 ] SELECT * FROM shohin master WHERE shohin code LIKE %2% ;

68 11 SQL [ 9 ] SELECT shohin name, tanka, tanka*1.05, hanbai end FROM shohin master WHERE shohin code LIKE A% AND tanka * 1.05 >= 10000 AND hanbai end IS NOT NULL; [ 10 ] SELECT * FROM shohin master ORDER BY shohin name DESC;

11.10. 69 [ 11 ] SELECT MIN(tensu) FROM eigo seiseki; [ 12 ] SELECT AVG(tensu) FROM eigo seiseki;

70 11 SQL [ 13 ] SELECT SUM(tensu) FROM eigo seiseki; [ 14 ] SELECT class no,avg(tensu) FROM eigo seiseki GROUP BY class no ORDER BY class no;

11.10. 71 [ 15 ] SELECT class no, count(tensu), MIN(tensu), MAX(tensu), AVG(tensu) FROM eigo seiseki GROUP BY class no ORDER BY class no; [ 16 ] SELECT shutoku shikaku.gakusei no, shutoku shikaku.shikaku code, shikaku master.shikaku name FROM shutoku shikaku INNER JOIN shikaku master ON shutoku shikaku.shikaku code = shikaku master.shikaku code ORDER BY shutoku shikaku.gakusei no, shikaku master.shikaku code;

72 11 SQL

11.10. 73 [ 17 ] SELECT shikaku master.shikaku code, shikaku master.shikaku name, shutoku shikaku.gakusei no FROM shikaku master LEFT OUTER JOIN shutoku shikaku ON shikaku master.shikaku code = shutoku shikaku.shikaku code ORDER BY shikaku master.shikaku code, shutoku shikaku.gakusei no;

74 11 SQL [ 18 ] SELECT * FROM eigo seiseki WHERE tensu > (SELECT AVG(tensu) FROM eigo seiseki);

11.10. 75 [ 19 ] SELECT shutoku shikaku.gakusei no, shutoku shikaku.shikaku code, shikaku master.shikaku name FROM shutoku shikaku INNER JOIN shikaku master ON shutoku shikaku.shikaku code = shikaku master.shikaku code WHERE shutoku shikaku.gakusei no IN (SELECT gakusei no FROM eigo seiseki WHERE tensu >= 90);

76 11 SQL [ 20 ] INSERT INTO shohin master(shohin code, shohin name, tanka, hanbai end) VALUES( 6789,, 200, 2012/12/31 ); SELECT * FROM shohin master WHERE shohin code= 6789 ;

11.10. 77 [ 21 ] UPDATE shohin master SET shohin name = WHERE shohin code = 6789 ; SELECT * FROM shohin master WHERE shohin code = 6789 ;

78 11 SQL [ 22 ] DELETE FROM shohin master WHERE shohin code = 6789 ; SELECT * FROM shohin master WHERE shohin code = 6789 ;

11.10. 79 [ 23 ] BEGIN; UPDATE shohin master SET tanka = 500 WHERE shohin code = B001 ; UPDATE shohin master SET hanbai end = 2015/12/31 WHERE shohin code = B002 ; ROLLBACK; SELECT * FROM shohin master; [ 29 ]

80 11 SQL BEGIN; UPDATE shohin master SET tanka = 500 WHERE shohin code = B001 ; UPDATE shohin master SET hanbai end = 2015/12/31 WHERE shohin code = B002 ; COMMIT; SELECT * FROM shohin master; [ 29 ]

81 13 DB 13.1 (RDB) ( ).,., ( ), RDB.,..,. 1... 2.,.,. 3. (ER ), ( ). ER. 4.,,, (, NOT NULL )..

82 13 DB ( ),,,.,. + =,.

13.1. 83 13.1.1,. [ ],.,.. ( ). [ ( )1 ] (13.2 ( ) )

84 13 DB 13.1.2,.,.,.,,..,..... ( ).,, ( ).. : (Primary Key ) =

13.1. 85 =, ( )..,., ( ).......

86 13 DB.,...,.,.,..,.,. = ( ).,.,.,,.

13.1. 87 [ ( )2 ] (13.2 ( ) )

88 13 DB 13.1.3 (entity )., (relationship ).,, :ER(Entity-Relationship).,. ER.. 1.,...

13.1. 89 2., ( ).,.,, ER. 3. 1 1 1. 1 : = 1.. 1 1, 1,. 1., ER. [ ( )3 ] (13.2 ( ) )

90 13 DB 13.1.4 ( )., (, NOT NULL )... 1.,,. (, ),.. (, ). = NO, BANGO., SQL GROUP BY ( ) GROUP.

13.1. 91 2.,. PostgreSQL.[9]

92 13 DB,. SQL, RDBMS., SQL RDBMS,. = date = serial serial :PostgreSQL,. 3., (PRIMARY KEY) NOT NULL,., NULL,. NOT NULL, NULL. PK Null. Null.

13.1. 93,.. [ ( )4 ] (13.2 ( ) )., (ER )..

94 13 DB 13.2 ( ),. [ ( )1 ]. [ ( )2 ] 1,. [ ( )3 ] ER. [ ( )4 ].

13.2. ( ) 95 [ ( )1 ] [ ( )2 ]

96 13 DB [ ( )3 ] [ ( )4 ]

13.3. ( ) 97 13.3 ( ), ER

98 13 DB [ ( ) 1 ]

13.3. ( ) 99 [ ( ) 2]

100 13 DB 13.4 PC,. 13.4.1 (INSERT)., NOT NULL,., (SELECT)., ROLLBACK. (UPDATE), (DELETE) (INSERT). 13.4.2 (UPDATE) (DELETE), /. SELECT WHERE, UPDATE DELETE. DELETE WHERE,.

13.4. 101 ) 3 -> DELETE FROM shohin master WHERE shohin code = 3;, WHERE -> DELETE FROM SHOHIN MASTER;,., SELECT. -> SELECT * FROM shohin master WHERE shohin code = 3;, SELECT * DELETE,. UPDATE. WHERE,, SELECT, SELECT UPDATE. 13.4.3.( ) < > (SHOHIN MASTER) (JISHA MASTER)

102 13 DB (SHIIRESAKI MASTER) (TOKUISAKI MASTER) (ZAIKO) (BUHIN HYO) (KUBUN) (HATCHU) * (HATCHU HINMOKU) * (JUCHU) * (JUCHU HINMOKU) * *. ER.,. (, ) INSERT, UPDATE,,. PostgreSQL (, ) current date. current time. current timestamp., current timestamp. SELECT. select current date; select current time;

13.4. 103 select current timestamp;.

105 IPA 3 OSS

107 [1],, [2] SQL, [3] ThinkIT-RDBMS -, http://www.thinkit.co.jp/free/compare/8/1/ [4] PostgreSQL, http://www.postgresql.jp/ [5] Let s Postgres, http://lets.postgresql.jp/ [6] -MySQL-, http://jp.sun.com/products/software/mysql/ [7] FireBird, http://firebird.gr.jp [8] IT RDBMS Apache Derby, http://www.itarchitect.jp/enterprise/-/29481.html [9], PostgreSQL,

109 2.1 http://creativecommons.org/licenses/by/2.1/jp/legalcode