2009ǯ05

ƥ:

ǡ򹹿ˤ update ȤޤǤ update ǥơ֥ lpi ꤷset price ͤ800˹Ƥޤwhere Ȥʤȡ٤Ƥ price 800ˤʤäƤޤΤաselect Ƿ̤ǧޤ

sqlite> update lpi set price=800 where name = 'apple';
sqlite> select name,price from lpi where name = 'apple';
apple|800
apple|800

join ϤäʬŤ餤ǤΥޥɤȤȰۤʤơ֥ΥǡϢ뤵뤳Ȥޤʬ䤹뤿2ĤΥơ֥롢first second ɲäޤξΥơ֥˶̤ column id äƤޤ

sqlite> create table first(id int, name varchar(10));
sqlite> insert into first values(1,'apple');
sqlite> insert into first values(2,'bnana');
sqlite> insert into first values(3,'lemon');

sqlite> create table second(id int, price int);
sqlite> insert into second values(1,150);
sqlite> insert into second values(1,130);
sqlite> insert into second values(2,90);
sqlite> insert into second values(2,95);
sqlite> insert into second values(3,230);

sqlite> select * from first;
1|apple
2|bnana
3|lemon

sqlite> select * from second;
1|150
1|130
2|90
2|95
3|230

join ȤäƤߤޤselect ǥơ֥ first ꤷ left ꤷƤޤ left ˤ꺸¦(ǽ˻ꤷ first)ͥ褵ɽޤjoin ³2ܤΥơ֥ second ꤷƤޤǸ on ³ƥǡ̲ id ꤷƤޤ

sqlite> select * from first left join second on first.id = second.id;
1|apple|1|150
1|apple|1|130
2|bnana|2|90
2|bnana|2|95
3|lemon|3|230

Ť餤ΤǾƤߤޤ

sqlite> select name,price from first
   ...> left join second
   ...> on first.id = second.id;
apple|150
apple|130
bnana|90
bnana|95
lemon|230

Ǹ˺Ǥǡñ̤Ǻˤ delete ޥɤȤޤwhere ǻ򤷤ʤȡ٤ƤιԤƤޤΤդɬס

sqlite> delete from lpi where number = 1;

ơ֥ˤ drop ޥɤȤޤǤϥơ֥ lpi ޤSQLite Ǥ .schema ޥɤǷ̤ǧ뤳ȤǽǤ

sqlite> drop table lpi;

Τ꤬餵СLPI 102 λʤǤ礦ġ

ƥ:

SQLite ưޤޤΥǡ⤢ޤΤǺäƤޤ

lpi
namepricenumber
apple 200 3
orange 300 5
banana 200 2
apple 200 4
orange 300 2
lemon 250 3
banana 200 4

ʴΥǡˤƤߤޤ礦ʤߤɽΤ table columnƹԤ record ȸޤơ֥ˤ create ޥɤȤޤ102λǤϥơ֥κϽФʤ褦ǤǤ table lpi Ȥ name ˤϥƥȡprice number ϿѤȤǻꤷƤޤ

sqlite> create table lpi(name varchar(10), price smallint, number smallint);

ʤߤ˺Ǹ";"(ߥ)դƤޤϥޥɤνλɽƤޤȡΥߥ󤬤ʤȥޥɤϽλ³ΥޥɤԤĤŤޤ

sqlite> create table lpi(name varchar(10), price smallint, number smallint)
   ...> ;

ץץȤ ...> ȤʤäƤΤ³ޥɤԤäƤ֤ɽƤޤñ˺Ǹ˥ߥ˺줿Ȥϡ嵭ͤ˥ߥΤߤϤס

ˤΥơ֥˼ºݤΥǡɲäޤinsert ȤޥɤȤޤʹߤϥƥȤоݥޥɤǤ

sqlite> insert into lpi values('apple',200,3);

insert ޥɤθ塢into ³ơ֥̾(Ǥ lpi)ꤷޤθ values ³ () ˤ줾Υǡ "," Ƕڤäޤapple ͤʸ ' ' dzɬפޤ

sqlite> insert into lpi values('orange',300,5);
sqlite> insert into lpi values('lemon',250,1);
sqlite> insert into lpi values('banana',200,2);
sqlite> insert into lpi values('apple',200,4);
sqlite> insert into lpi values('orange',300,2);
sqlite> insert into lpi values('lemon',250,3);
sqlite> insert into lpi values('banana',200,4);

Τ褦ˤƤХǡɬפʬϤǤޤǤϼϤǡǧƤߤޤ礦select ȤޥɤȤޤ˽פǤ

sqlite> select * from lpi;
apple|200|3
orange|300|5
lemon|250|1
banana|200|2
apple|200|4
orange|300|2
lemon|250|3
banana|200|4

嵭ޥɤǥǡΰɽޤselect ޥɤθ "*" ɽ column λǡ"*" ȤȤ٤(name, price, number)̣ޤfrom θ table ꤷޤͤɽ column ꤹ뤳ȤǤޤ

sqlite> select name,number from lpi;

ǡɽݤ¤ؤˤ order by Ѥޤ

sqlite> select * from lpi order by name;
apple|200|3
apple|200|4
banana|200|2
banana|200|4
lemon|250|1
lemon|250|3
orange|300|5
orange|300|2

by θ name ȤȤ apple, bnana, lemon, orange ν¤Ȥʬޤ price number ꤹ뤳Ȥޤ

Ʊ column ͤޤȤˤ group by ȤޤLinux ޥɤǤ uniq Τ褦ʤΤǤǤ column name Τߤꤹ뤳Ȥˤʣʤ name ɽƤޤorder by ³ƻꤹ뤳ȤǽǤ

sqlite> select name from lpi group by name;
apple
banana
lemon
orange

where ȤȥǡФǽǤǤ name apple ΥǡꤷƤޤʸꤹȤ 'apple' ͤ˳ʤȥ顼ˤʤΤա

sqlite> select * from lpi where name = 'apple';
apple|200|3
apple|200|4

Ǥ number 3礭ΤꤷƤޤʻ name number ΤߤɽоݤȤorder by ¤ؤƤޤĹʤΤ3ԤʬƼ¹Ԥޤ

sqlite> select name,number from lpi
   ...> where number > 3
   ...> order by name;
apple|4
banana|4
orange|5

ƥ:

LPIC ٥1 Ƥ4꿷ʤޤܺ٤ϤǤ

ϰ

ΩȤƤϡ102 SQL ǡɲä줿ȡSQL ϥǡ١뤿θǡǡ١ץȤƤ MySQL PostgreSQL 褯ȤƤޤ

ƽϰϤܺ򸫤ȡSQL ˴ؤƤϤΤ褦ˤʤäƤޤ

105.3 SQLǡ
١ 2


    ǡ١Ȳ񤷡ŪSQLޥɤѤƥǡ롣ˤϡ2ĤΥơ֥䥵֥쥯Ȥηȼ꡼μ¹Ԥޤޤ롣
פμϰ
    ŪSQLޥɤλ
    Ūʥǡ¹Ԥ
פʥե롢Ѹ졢桼ƥƥ
    insert
    update
    select
    delete
    from
    where
    group by
    order by
    join

ʥǡ١򿨤äƤͤ䡢Oracle Master äƤͤˤϴñƤǤ礦SQL ʤΤʤȤäȤΤʤͤˤȤäƤϤäߵ郎⤤Ǥ͡ȤȤǻкȤƤٶˡͤƤߤޤ Linux Ȥޤ

MySQL PostgreSQL 虜虜ѰդʤƤ LPIC ٶӤǤ SQLite Ǥ礦ʤߤ Windows Ǥ⤢ޤ

ʬ Linux ˥󥹥ȡ뤵Ƥ뤫ϲͤˤƳǧޤ(Red Hat Ϥξ)

$ rpm -q sqlite
sqlite-3.3.6-2

äƤʤϡyum apt-get ޥɤȤ󥹥ȡ뤷ޤ礦

󥹥ȡ뤬ä鵯ưޤsqlite3 ޥɤ³()ե̾ꤷޤ lpi.db Ȥޤ

$ sqlite3 lpi.db
SQLite version 3.3.6
Enter ".help" for instructions
sqlite>

sqlite> ȤѤΥץץȤѤޤ.htlp (Ƭ".")ǥإס.quit (Ƭ".")ǽλޤ

ΥڡΥȥåץ

Ф