SQLite

s.5 mm^rnrn

8.6 MfKfêASMfM

8.7 SQL A^MMTO

8.1 MM^m mw^M^-ff (Relational Database)[email protected]±ÄSg * ft^faßm^Ä '

^mmmm^m^mmmmmmmmm ° 1970 ^ftm • nmmmm mmrnnmm^m^^Mimmmm > • mrn^mrnmrn mmmMftftQ°

(Relational Database) ° ^gfM SQLite ' ^Igtimtl^ifl^l

SQL (Structured Query Language) ' Ä&^MmftÄWa -

m ß m f4 rn ^ ' m n m m m f4 * « m m f4 # ° n ^ ^ ß Oracle *

create table table_name

(column_name1 column_type, column_name2 column_type, ...);

create table Student(stud_no text primary key, stud_name text);

msm Mmsw^MmmsQute 195

Student

stud_no

stud_name

101

Mike

102

Linda

103

H 8-1 ummmmmmmj%-immm(mm.Net

-mmrnmn • • ymmmmmmmmmrn; aa • a-ihma mm SQL m^mmmmmmmmmm select stud_no, stud_name from Student;

Mffl SQL : Student AAWow)mSf4 ° AH

select stud no, stud name from Student where stud no = '102';

stud no stud name

Linda

select stud_no, stud_name from Student where stud_no <> '102' ;

stud no stud name stud no stud name

101

Mike

103

Tom

select stud_name from Student where stud no <> '101' and stud name <> 'Linda';

stud name

Tom msm Mmsw^MmmsQute 197

8.4 Data Model)

Course

course_name

stud_no

DATABASE

101

ART

101

DATABASE

Teacher

couse_name

teacher_name

DATABASE

ART

MS OFFICE

HISTORY

8.5 HMTO

° mm ' Student (stud_no, stud_name, sex, age, grade, phone, ...) A®® ' A^fflffstud_no - stud_name - sex - grade - phone

WAmimmrmm * aa * AA * AM * mm * misA-PA'^sAttM ° A

mm'•m-ffiiimAWffA^-ffiö^Hrara•M "H^'WW'^M i:N mm °

g' wmmmmmBmmAmwmmtmmm ° 8.6

MM SQL iftfi JOIN ftAAffiAmffiAi#WIBÄ ' A^AAMAA^ W-MitSPAmiSm ' M^A-ffi^A^ ° OTS^-T Student m Course

Student

stud_no

stud_name

ioi

Mike

i02

Linda

i03

Tom

msm Mmsw^mmmmSQLite 199

Course

course_name

stud_no

DATABASE

101

ART

101

DATABASE

select Student.stud_name, Course.course_name from Student, Course where Student.stud_no = Course.stud_no;

Student.stud name Course.course name

Mike

DATABASE

Mike

ART

Linda

DATABASE

NewCourse

course_name

stud_no

scorel

score2

DATABASE

101

70.0

74.5

ART

101

60.0

45.0

DATABASE

102

95.0

90.5

select SUM(score1), SUM(score2) from New_Course;

SUM(scorel)

SUM(score2)

225.0

210.0

select AVG(score1), AVG(score2) from New_Course m uj |±j

AVG(scorel) 73.0

A VG(score2) 70.0

GROUP BY AAAMAA^Amfi(Group) ' MM^fflM^M^M

select course_name, SUM(score1), SUM(score2) from New_Course group by course_name

course name SUM(scorel)_ SUM(score2)

DATABASE

165.0

165.0

ART

60.0

45.0

0 0

Post a comment