湖濱散記部落格的樹心幽徑[login][主頁]
763:20220322在LibreOffice的BASE資料庫執行SQL指令

744:20220126電腦軟體應用乙檢術科圖解一SQL指令
操作過程: 20220322在LibreOffice的BASE資料庫執行SQL指令.docx
下載jdk-18_windows-x64_bin.exe並安裝JDK才能使用HSQLDB資料庫引擎https://download.oracle.com/java/18/latest/jdk-18_windows-x64_bin.exe
結果試算表:freeB.xlsx
結果資料庫:freeB.odb
結果資料庫:freeHB.odb
寫入freeHB.odb:jsXML1SQL.php

(1)用ms-access的「外部資料/XML檔案」功能匯入如下三個XML資料表 student.xml, records.xml, conduct.xml到d:a.accdb資料庫中



(2)用如下指令新增一查詢:SELECT * FROM STUDENTS where 班級座號<"10130"; 初步結果如:uploads/a.accdb

(3)用如下指令新增一查詢:SELECT [科別], [班級座號], [學號], [姓名], [身分證號碼] , mid([身分證號碼],2,1) as sex, [出生年月日],mid([出生年月日],1,4)-1911 as yy , mid([出生年月日],6,2) as mm, mid([出生年月日],9,2) as dd,[畢業國中] ,right([畢業國中],2) as 入學資格 FROM STUDENTS

(4)用如下指令新增一查詢:

(5)用如下指令再新增一查詢: SELECT A.[班級座號], A.[姓名], B.[導師評分] FROM STUDENTS AS A, CONDUCT AS B WHERE ((A.[班級座號]= B.[班級座號]));

(6)用如下指令再新增一查詢: Select sum([事假]) as T, sum([病假]) as S, sum([公假]) as P , sum([曠課]) as X from records where left([班級座號],3)="101" and mid([年月日],3,2)="03"


(7A)用如下指令再新增一查詢: SELECT left([班級座號],3) as 班級, sum([事假]) AS 事, sum([病假]) AS 病, sum([公假]) AS 公, sum([曠課]) AS 曠 FROM records WHERE mid([年月日],3,2)="03" group by left([班級座號],3) order by left([班級座號],3);


(7B)同(7A)但增加into classCount將查詢結果寫入為新資料表classCount,指令如下: SELECT left([班級座號],3) AS 班級, sum([事假]) AS 事, sum([病假]) AS 病, sum([公假]) AS 公, sum([曠課]) AS 曠 into TSPXcount FROM records WHERE mid([年月日],3,2)="03" GROUP BY left([班級座號],3) ORDER BY left([班級座號],3);

(8A)用如下指令再新增一查詢: SELECT left([班級座號],3) as 班級,count([學號]) as 人數, 156*count([學號]) as 總節數人數 FROM STUDENTS group by left([班級座號],3) order by left([班級座號],3);


(8B)同(8A)但增加into classCount將查詢結果寫入為新資料表classCount,指令如下: SELECT left([班級座號],3) AS 班級, count([學號]) AS 人數, 156*count([學號]) AS 總節數人數 into classCount FROM STUDENTS GROUP BY left([班級座號],3) ORDER BY left([班級座號],3);

(9)用如下指令再新增一查詢: SELECT a.班級,[事],[病],[公],[曠],[人數],[總節數人數],1-([事]+[病]+[公]+[曠])/[總節數人數] as 到課率 FROM TSPXcount AS a, classcount AS b WHERE a.班級=b.班級 order by a.班級;


(A)參考結果:uploads/1-1.docx


(B) 20190204用C++連線access資料庫
(C)連接Access數據庫應註意的幾點 https://www.itread01.com/articles/1476374748.html

select id,article_id,topic,text from lt_articles_text where article_id =763; ok. update lt_articles set num_reads=num_reads +1 where id=763; ok.