13-5 使用MATLAB對??庫??修改

W@`WFpϥMATLABƮwidߡA`NpƮwiܡA]tsWBקPRC

²aAMATLAB g ODBC MƮwiޱA]Ҧ SQL ROF쪺\AMATLAB ]gѤUF SQL ROӰC~AMATLAB Ʈwuc]ѤFt~@ǩROAƮwiקC

bե`ҴѪdҫeAAHʪ覡ӳ]w DSNAбNƨӷW dsnScore02 쥻m score02.mdbCPɦbHUdҤACbiƮwseAڭ̷|N score01.mdb e score02.mdbAHקK]hӽdҵ{XᶶǤPAyұoGMѴyz@PpC

Aڭ̨Ӭݬݦpg SQL ROӷsW@ƨƮwCUoӽdҡANsW@mWujOvơA SQL ROO

INSERT INTO score (studentID, studentName, final) VALUES (''00'', ''jO'', 100)

{XpUC

Example 1: 13-PƮwX/insertData01.mcopyfile('score01.mdb', 'score02.mdb'); % N score01.mdb score02.mdb dsn = 'dsnScore02'; % ]wƨӷW١] score02.mdb^ logintimeout(5); % ]wճsƮwɶ conn = database(dsn, '', ''); % sƮw % ]wsWƩҥΪ SQL RO sql = 'INSERT INTO score (studentID, studentName, final) VALUES (''00'', ''jO'', 100)'; cursor = exec(conn, sql); % SQL RO % ]wd߸ƥΪ SQL RO sql = 'select * from score'; cursor = exec(conn, sql); cursor = fetch(cursor); newScore = cursor.data % ܧs final 쪺 close(cursor); % cursor close(conn); % ƮwsnewScore = '914340' 'ū䬰' [82] [ 93] [ 87] [ 77] [ 80] [0] '916310' '' [83] [ 93] [ 93] [ 99] [ 80] [0] '882545' '' [86] [ 96] [ 92] [ 99] [ 77] [0] '8802142' '\F' [86] [ 96] [ 92] [ 90] [ 78] [0] '914358' '}Z' [87] [ 93] [ 87] [ 90] [ 80] [0] '898316' 'T' [87] [ 95] [ 94] [ 99] [ 88] [0] '914370' '' [88] [ 93] [ 93] [ 95] [ 79] [0] '916716' '' [88] [ 94] [ 87] [ 99] [ 77] [0] '914307' '©p' [89] [ 93] [ 93] [ 90] [ 77] [0] '882548' 'Ĭs' [89] [ 94] [ 90] [ 99] [ 78] [0] '916701' 'ù' [90] [ 94] [ 90] [ 90] [ 77] [0] '916717' 'LCz' [90] [ 97] [ 92] [ 99] [ 80] [0] '00' 'jO' [ 0] [NaN] [NaN] [NaN] [100] [0]

ѩڭ̨èSwC@쪺ȡAҥHo쪺ȷ|ѸƮwҳ]ww]ȩҨNApGoǸƮwSw]ȡAMATLAB ^ӪƴN|X{ NaNC

ۡAڭ̨Ӭݬݦpg SQL ROӭקƮwCUoӽdҡANҦҤơ]final ^jε 80 PǡAզ 100 A SQL ROO

UPDATE score SET final=100 WHERE final>=80

{XpUC

Example 2: 13-PƮwX/updateData01.mcopyfile('score01.mdb', 'score02.mdb'); % N score01.mdb score02.mdb dsn = 'dsnScore02'; % ]wƨӷW١] score02.mdb^ logintimeout(5); % ]wճsƮwɶ conn = database(dsn, '', ''); % sƮw % ]wsƩҥΪ SQL RO sql = 'UPDATE score SET final=100 WHERE final>=80'; cursor = exec(conn, sql); % SQL RO % ]wiƬdߪ SQL RO sql = 'select studentName, final from score'; % ]w SQL RO cursor = exec(conn, sql); cursor = fetch(cursor); newScore = cursor.data % ܧs final 쪺 close(cursor); % cursor close(conn); % ƮwsnewScore = 'ū䬰' [100] '' [100] '' [ 77] '\F' [ 78] '}Z' [100] 'T' [100] '' [ 79] '' [ 77] '©p' [ 77] 'Ĭs' [ 78] 'ù' [ 77] 'LCz' [100]

bWzdҤA|ӤHҦZ]final ^Qקאּ 100 C

ڭ̤]iwgSQL ROӧRơCUoӽdҡANҦҤơ]final ^p 80 PǡARA SQL ROO

DELETE FROM score WHERE final<80

{XpUC

Example 3: 13-PƮwX/deleteData01.mcopyfile('score01.mdb', 'score02.mdb'); % N score01.mdb score02.mdb dsn = 'dsnScore02'; % ]wƨӷW١] score02.mdb^ logintimeout(5); % ]wճsƮwɶ conn = database(dsn, '', ''); % sƮw % ]wRƩҥΪ SQL RO sql = 'DELETE FROM score WHERE final<80'; cursor = exec(conn, sql); % ]wiƬdߪ SQL RO sql = 'select studentName, final from score'; cursor = exec(conn, sql); cursor = fetch(cursor); newScore = cursor.data % ܧs final 쪺 close(cursor); % cursor close(conn); % ƮwsnewScore = 'ū䬰' [80] '' [80] '}Z' [80] 'T' [88] 'LCz' [80]

bWzdҤAƮw 12 ơAgLRAuѤU 5 ơC

~ASQL RO]iHΨӲͤ@ӸƪA]bUoӽdҤAڭ̪ͤ@ӷsƪAMihⵧơAMƪܦbùWAdҦpUC

Example 4: 13-PƮwX/createTable01.mcopyfile('score01.mdb', 'score02.mdb'); % N score01.mdb score02.mdb dsn = 'dsnScore02'; % ]wƨӷW١] score02.mdb^ logintimeout(5); % ]wճsƮwɶ conn = database(dsn, '', ''); % sƮw % ͷsƪ friend sql = 'CREATE TABLE friend (fullName char(6), birthday date)'; exec(conn, sql); % JĤ@ sql = 'INSERT INTO friend (fullName, birthday) VALUES (''ã'', ''1983/11/03'')'; exec(conn, sql); % JĤG sql = 'INSERT INTO friend (fullName, birthday) VALUES (''μz'', ''1982/09/22'')'; exec(conn, sql); % CXҦ cursor = exec(conn, 'select * from friend'); cursor = fetch(cursor); friend = cursor.data % ܧs friend ƪ close(cursor); % cursor close(conn); % Ʈwsfriend = 'ã' '1983-11-03 00:00:00.0' 'μz' '1982-09-22 00:00:00.0'

`̫@ӽdҡAO@Ӥ㪺dҡA\pUG

  1. ŪJ score02.mdb score ƪ
  2. pC@Ӿǥͪ`ZA@~H 30%A[WҦZ]midterm ^H 30%AA[Wҡ]final ^ZH 40%C
  3. NC@ӾǥͪZeƮw overall C
  4. N̫ᦨZp⵲Gg@ӺAHKǥͬݡC

bdҪlXAڭ̤wg[JjqѡA]A{Ӹ`AdҦpUC

Example 5: 13-PƮwX/computeScore01.mcopyfile('score01.mdb', 'score02.mdb'); % N score01.mdb score02.mdb dsn = 'dsnScore02'; % ]wƨӷW١] score02.mdb^ logintimeout(5); % ]wճsƮwɶ conn = database(dsn, '', ''); % sƮw % XҦ cursor = exec(conn, 'select * from score'); % SQL ROAöǦ^ cursor cursor = fetch(cursor); % g cursor A score = cursor.data; % NƶǦ MATLAB ܼ score temp = columnnames(cursor); % ƮwW eval(['fieldNames = {', temp, '}'';']); % NW٫w fieldNames ܼ score = cell2struct(score, fieldNames, 2); % N}C score নc}C % C@ƶiBAñNGs^Ʈw for i=1:length(score) homework=(score(i).homework1+score(i).homework2+score(i).homework3)/3; overallScore=homework*0.3+score(i).midterm*0.3+score(i).final*0.4; % NƼgJƮw sql = ['UPDATE score SET overall=', num2str(overallScore), ' where studentID=''', score(i).studentID, '''']; exec(conn, sql); end % CX score ƪ cursor = exec(conn, 'select * from score'); cursor = fetch(cursor); newScore = cursor.data; % s᪺ temp = columnnames(cursor); % ƮwW eval(['fieldNames = {', temp, '}'';']); % NW٫w fieldNames ܼ newScore = cell2struct(newScore, fieldNames, 2);% N}C newScore নc}C close(cursor); % cursor close(conn); % Ʈws struct2html(newScore); % ܵc}C newScore s

ұo쪺pUA䤤̫@NOpXӪZC


MATLAB{]pGig