1
00:00:00,000 --> 00:00:02,000
[Powered by Google Translate] [SQL]

2
00:00:02,000 --> 00:00:04,000
[KRISTOFRU BARTHOLOMEW] [HARVARD UNIVERSITÀ]

3
00:00:04,000 --> 00:00:06,000
[B'DAN QIEGĦED CS50] [CS50 TV]

4
00:00:07,950 --> 00:00:11,620
Allura, programmaturi tuża databases bħala għodda biex jaħżnu u jorganizzaw

5
00:00:11,620 --> 00:00:13,620
data persistenti tagħna fi ħdan it-tabelli.

6
00:00:13,620 --> 00:00:18,960
Dan ifisser li d-data tiegħek hija maħżuna fuq mhux volatili medjani bħal hard drive,

7
00:00:18,960 --> 00:00:22,940
u anke meta l-kompjuter huwa off, id-data hija għadhom intatti.

8
00:00:22,940 --> 00:00:29,530
U, kif programmaturi, programmi tagħna tutilizza databases għal xenarji bħal ħażna

9
00:00:29,530 --> 00:00:32,890
informazzjoni lill-utent minn forom tal-web, tfittex up inventarju,

10
00:00:32,890 --> 00:00:36,210
jew l-aġġornament ta 'informazzjoni dwar dak is-siti display.

11
00:00:37,150 --> 00:00:40,070
Imma kif għandna attwalment jinteraġixxu ma 'database tagħna,

12
00:00:40,070 --> 00:00:43,150
jew dak li nużaw biex jinqraw, jaħżnu, iħassru,

13
00:00:43,150 --> 00:00:46,070
u jaġġornaw data tagħna fi ħdan dawn it-tabelli database?

14
00:00:47,280 --> 00:00:49,700
Ukoll, it-tweġiba hija li nużaw tip speċjali ta 'lingwa database

15
00:00:49,700 --> 00:00:53,400
li jinteraġixxi b'mod dirett ma 'tabelli database tagħna.

16
00:00:53,400 --> 00:00:56,740
Huwa isem huwa Strutturat Mistoqsija Lingwa,

17
00:00:56,740 --> 00:00:58,740
[Strutturat Mistoqsija Lingwa]

18
00:00:58,740 --> 00:01:00,740
jew dak I jirreferu għalih bħala SQL.

19
00:01:00,740 --> 00:01:05,100
>> Issa, [Se-quel], jew SQL, ma tkunx lingwa ta 'programmar,

20
00:01:05,100 --> 00:01:08,580
iżda minflok, huwa lingwa li jipprovdi sett standard ta 'kmandi

21
00:01:08,580 --> 00:01:13,520
sabiex tkun irkuprata u timmanipula data minn varjetà ta 'sistemi ta' ġestjoni ta 'database.

22
00:01:13,520 --> 00:01:17,630
Għall-iskop ta CS50, aħna ser imorru fuq erba 'kmandi bażiċi:

23
00:01:17,630 --> 00:01:21,210
tagħżel, daħħal, jaġġornaw, iħassru u.

24
00:01:21,210 --> 00:01:26,230
Barra minn hekk, aħna ser jutilizzaw web interface database imsejħa phpMyAdmin,

25
00:01:26,230 --> 00:01:29,890
li installati fuq l-appliance li jiktbu dikjarazzjonijiet SQL tagħna.

26
00:01:30,830 --> 00:01:33,050
Allura, biex jgħinek tiftakar dawn il-kmandijiet,

27
00:01:33,050 --> 00:01:37,080
Stajt ippreżentat xi cupcakes fil-cupboard biex jassistu bl xenarji tagħna.

28
00:01:39,650 --> 00:01:42,210
Tgħid li inti għandek database cupcake, l-

29
00:01:42,210 --> 00:01:44,490
fejn int taħżen l-informazzjoni kollha dwar il-cupcakes tiegħek.

30
00:01:44,490 --> 00:01:48,220
Issa, databases jista 'jkun fihom tabelli ħafna

31
00:01:48,220 --> 00:01:50,950
u t-tabelli infushom jista 'jkun fihom kolonni ħafna.

32
00:01:50,950 --> 00:01:57,020
Ġewwa database cupcake tagħna, aħna għandna tabella msejħa cupcake_cupboard.

33
00:01:57,020 --> 00:02:00,500
Din it-tabella se jintużaw biex jaħżnu l-informazzjoni kollha dwar cupcakes

34
00:02:00,500 --> 00:02:02,990
li huma, ukoll, fil-cupboard tiegħek.

35
00:02:02,990 --> 00:02:07,770
Il-kolonni li huma ġewwa mejda tiegħek jirrappreżentaw l-attributi ta 'cupcake.

36
00:02:07,770 --> 00:02:14,560
Per eżempju, kolonni tal-cupcake_cupboard huma

37
00:02:14,560 --> 00:02:15,920
Kejk ID, CakeType, CakeColor, u CakeIsNice.

38
00:02:15,920 --> 00:02:23,040
Din il-kolonna Boolean ttajpjata hija użata sabiex tiddetermina jekk il-kejk huwa sbieħ jew mhux sbieħ.

39
00:02:23,040 --> 00:02:26,560
Aħna ser tibda bil-kitba ta 'stqarrija tagħżel.

40
00:02:26,560 --> 00:02:32,160
Dikjarazzjonijiet Agħżel huma użati biex jirkupraw data ta 'tabella database partikolari.

41
00:02:32,160 --> 00:02:34,890
F'dan ix-xenarju, li rridu nkunu nafu kollox

42
00:02:34,890 --> 00:02:39,080
dwar l cupcakes li jeżistu fil-cupboard tagħna.

43
00:02:39,080 --> 00:02:48,670
Is-sintassi biex isir dan huwa "Select" spazju stilla, jew *, l-ispazju mill-ispazju mejda tagħna,

44
00:02:48,670 --> 00:02:52,050
li huwa cupcake_cupboard.

45
00:02:52,050 --> 00:02:54,640
Ejja imorru quddiem u tesegwixxi dik.

46
00:02:56,670 --> 00:03:00,140
>> Kif nistgħu naraw, dawn huma kollha l-cupcakes fil-cupboard tagħna.

47
00:03:00,140 --> 00:03:05,110
Huwa importanti li wieħed jinnota li *, jew b'asterisk, huwa karattru wild card

48
00:03:05,110 --> 00:03:08,830
li tindika l-ġbir ta 'l-kolonni ta' xi tabella.

49
00:03:08,830 --> 00:03:13,650
Normalment, nistgħu aċċess kolonna speċifika jew kolonni

50
00:03:13,650 --> 00:03:16,950
billi tissostitwixxi * bl-isem kolonna attwali.

51
00:03:16,950 --> 00:03:21,220
Jekk irridu kolonni multipli, iżda mhux kollha, nistgħu niksbu dan

52
00:03:21,220 --> 00:03:25,620
billi tikteb l-ismijiet kolonna jiddelimitaw kull kolonna b'virgola.

53
00:03:25,620 --> 00:03:28,620
Per eżempju, ejja biss irkuprata l-CakeId u CakeType fit-tabella cupcake_cupboard.

54
00:03:28,620 --> 00:03:38,370
Is-sintassi biex isir dan huwa: TAGĦŻEL ispazju virgola CakeID

55
00:03:38,370 --> 00:03:44,370
Ispazju CakeType mit-tabella, tagħna cupcake_cupboard.

56
00:03:44,370 --> 00:03:46,370
Ejja jimxi 'l quddiem u tesegwixxi dan.

57
00:03:49,340 --> 00:03:52,670
U hawn, aħna issa biss il-kolonni 2 we speċifikati

58
00:03:52,670 --> 00:03:54,670
għal kull cupcake fil-cupboard tagħna.

59
00:03:54,670 --> 00:03:57,710
Nistgħu wkoll tirfina r-riżultati mistoqsija tagħna billi tispeċifika

60
00:03:57,710 --> 00:04:00,910
l-"Fejn" klawsola eżatt wara l-isem tabella.

61
00:04:02,000 --> 00:04:05,410
Per eżempju, jidher li hemm cupcake fil-cupboard tagħna

62
00:04:05,410 --> 00:04:08,660
jiġifieri, ukoll, mhux hekk sbieħ tfittex.

63
00:04:08,660 --> 00:04:13,950
Ejja figura l-cupcakes fil-cupboard tagħna li huma, ukoll, mhux hekk sbieħ tfittex

64
00:04:13,950 --> 00:04:16,110
tuża l-"Fejn" klawsola.

65
00:04:16,110 --> 00:04:26,390
Is-sintassi biex isir dan huwa: TAGĦŻEL ispazju * ispazju MILL cupcake_cupboard ispazju

66
00:04:26,390 --> 00:04:34,080
ispazju FEJN ispazju kolonna kondizzjonali tagħna, f'dan il-każ CakeIsNice =,

67
00:04:34,080 --> 00:04:36,900
u l-valur Boolean ta Foloz.

68
00:04:36,900 --> 00:04:42,750
Huwa importanti li wieħed jinnota li jekk inti qed tuża kordi, għandek tehmeż fil-kwotazzjonijiet uniku.

69
00:04:42,750 --> 00:04:49,620
Dan huwa minnu għal kull irbit SQL, jew, f'termini database SQL karattri varjant,

70
00:04:49,620 --> 00:04:51,620
magħrufa bħala varchar datatype.

71
00:04:51,620 --> 00:04:57,660
F'dan il-każ, aħna qed jużaw Veru jew Falz, li hija valur Boolean u mhux string.

72
00:04:59,120 --> 00:05:00,660
Ejja jimxi 'l quddiem u tesegwixxi dan il-kmand.

73
00:05:00,660 --> 00:05:05,340
>> Lo u behold, huwa l-każ li għandna 1 ċikkulata

74
00:05:05,340 --> 00:05:07,920
mhux hekk sbieħ cupcake fil-cupboard tagħna.

75
00:05:09,620 --> 00:05:11,460
Sussegwentement, aħna qed tmur biex tikteb dikjarazzjoni daħħal.

76
00:05:11,460 --> 00:05:15,560
Dikjarazzjonijiet Daħħal huma użati biex jiddaħħal jew iżidu

77
00:05:15,560 --> 00:05:17,770
ringieli addizzjonali ta 'data fis-tabella database tiegħek.

78
00:05:17,770 --> 00:05:23,160
Jiġi reivedut ir xenarju tagħna, ejja nassumu li aħna stajt biss magħmula cupcake ġdida fjamanta.

79
00:05:25,910 --> 00:05:30,080
Peress li aħna ħafna organizzati eaters cupcake, aħna qed tmur biex ikollhom daħħal dan cupcake ġdida

80
00:05:30,080 --> 00:05:32,330
fit-tabella cupcake_cupboard tagħna.

81
00:05:32,330 --> 00:05:40,690
Is-sintassi biex isir dan huwa dan: Daħħal ispazju fl-ispazju mejda tagħna,

82
00:05:40,690 --> 00:05:46,830
, cupcake_cupboard ispazju (,

83
00:05:46,830 --> 00:05:51,060
u hawn aħna jispeċifika l-ismijiet kolonni, delimitata minn virgola,

84
00:05:51,060 --> 00:05:59,790
CakeType virgola CakeColor virgola CakeIsNice) l-ispazju.

85
00:05:59,790 --> 00:06:06,540
Wara dan, aħna tikteb l-ispazju VALURI kelma (u hawn

86
00:06:06,540 --> 00:06:12,170
aħna jidħol il-valur ta 'kull kolonna rispettivament, wkoll delimitati minn virgola.

87
00:06:12,170 --> 00:06:17,830
Kwotazzjoni Uniku, minħabba li huma l-valuri kollha varchar aħna ser jdawru minnhom fil-kwotazzjonijiet wieħed,

88
00:06:17,830 --> 00:06:26,780
Virgola "virgola" BUTIR karawett DAWL BROWN "Veru.

89
00:06:26,780 --> 00:06:30,480
Issa, huwa importanti li tingħata kull ringiela numru uniku li jidentifika innifsu.

90
00:06:30,480 --> 00:06:33,660
Autoincrementing kolonna jipprovdi dan bħala

91
00:06:33,660 --> 00:06:37,410
 "L-ebda 2-IDs istess jista 'qatt jeżisti f'din it-tabella."

92
00:06:37,410 --> 00:06:39,480
Ejja imorru quddiem u tesegwixxi.

93
00:06:39,480 --> 00:06:45,380
Hemm immorru, kollha organizzati.

94
00:06:49,720 --> 00:06:52,100
>> Id-dikjarazzjoni SQL jmiss li aħna se jikteb huwa rendikont aġġornament.

95
00:06:52,100 --> 00:06:55,650
Id-dikjarazzjoni aġġornament jistgħu jintużaw biex jimmodifikaw id-data fi ħdan kolonna

96
00:06:55,650 --> 00:06:58,440
għal kull ringiela eżistenti fit-tabella database tiegħek.

97
00:06:59,670 --> 00:07:03,420
Aktar kmieni fix-xenarju tagħna, bl-użu dikjarazzjoni tagħżel, aħna identifikat cupcake

98
00:07:03,420 --> 00:07:08,300
fi ħdan it-tabella cupcake_cupboard tagħna li l-valur CakeIsNice kien Foloz.

99
00:07:08,300 --> 00:07:12,050
Ejja nassumu li filwaqt cupcake tagħna butir tal-karawett kien fil-forn,

100
00:07:12,050 --> 00:07:15,790
għamilna cupcake mhux daqstant sbieħ tagħna sbieħ ħafna.

101
00:07:18,020 --> 00:07:22,240
Li tant eċċezzjonalment organizzata, irridu cupcake tagħna biex tirrifletti dan il-valur

102
00:07:22,240 --> 00:07:24,240
fit-tabella cupcake_cupboard tagħna.

103
00:07:24,240 --> 00:07:28,710
Għalhekk, ejja taġġorna ċikkulata cupcake tagħna fid-database biex tirrifletti dan.

104
00:07:28,710 --> 00:07:39,720
Is-sintassi hija: l-ispazju AĠĠORNAMENT mejda tagħna, cupcake_cupboard, l-ispazju SET ispazju

105
00:07:39,720 --> 00:07:44,240
il-kolonna li nixtiequ għall-bidla, CakeIsNice =.

106
00:07:44,240 --> 00:07:49,210
Imbagħad hawn aħna post il-ġdid valur tagħna ispazju Veru.

107
00:07:49,210 --> 00:07:54,290
Issa, għaliex aħna ma rridux li taġġorna kollha ringieli ma 'dan il-valur,

108
00:07:54,290 --> 00:07:57,400
irridu li jipprovdu "Fejn" klawsola li se jindikaw us

109
00:07:57,400 --> 00:07:59,830
għall-ringiela eżatt li rridu li timmodifika.

110
00:07:59,830 --> 00:08:03,690
F'dan il-każ, nafu li hemm biss wieħed cupcake

111
00:08:03,690 --> 00:08:06,670
li għandu valur CakeIsNice ta Foloz.

112
00:08:06,670 --> 00:08:11,030
Barra minn hekk, aħna se jiżguraw ukoll li aħna taġġorna l-ringiela korretta

113
00:08:11,030 --> 00:08:13,030
billi tuża l-"U" klawżola.

114
00:08:14,340 --> 00:08:17,270
Aħna nużaw il-"U" klawżola biex jirfinaw mistoqsija tagħna aktar.

115
00:08:17,270 --> 00:08:20,380
F'dan il-każ, għaliex aħna nafu li l-cupcake huwa ċikkulata,

116
00:08:20,380 --> 00:08:23,160
se nużaw il-kolonna CakeType.

117
00:08:23,160 --> 00:08:31,500
FEJN ispazju kondizzjonali kolonna tagħna ispazju ispazju CakeIsNice = False,

118
00:08:31,500 --> 00:08:38,330
u l-ispazju CakeType = "Ċikkulata."

119
00:08:38,330 --> 00:08:41,880
Għalhekk, tqegħid kollha flimkien, din id-dikjarazzjoni aġġornament jgħid

120
00:08:41,880 --> 00:08:44,670
jsibu l-cupcakes fil-cupboard cupcake tagħna,

121
00:08:44,670 --> 00:08:50,520
u jekk ikun hemm cupcake li kolonna CakeIsNice fiha l-valur Foloz

122
00:08:50,520 --> 00:08:54,130
u CakeType fiha l-valur Ċikkulata,

123
00:08:54,130 --> 00:08:58,240
irridu li taġġorna l-valur speċifiku CakeIsNice ringieli għall Veru.

124
00:08:58,240 --> 00:09:01,140
Allura, ejja imorru quddiem u tesegwixxi l-istqarrija.

125
00:09:03,860 --> 00:09:05,860
U issa, aħna qed organizzati.

126
00:09:06,650 --> 00:09:09,220
>> Dan kollu taħdita ta 'cupcakes għamel miegħi daqsxejn bil-ġuħ.

127
00:09:09,220 --> 00:09:11,360
I think I għandhom jgħinu myself għal wieħed.

128
00:09:11,360 --> 00:09:17,670
Imma jekk I attwalment jieklu dan cupcake, I għandhom għall-inqas tneħħi wkoll l-eżistenza tagħha

129
00:09:17,670 --> 00:09:19,670
 mit-tabella cupcake_cupboard tagħna.

130
00:09:20,650 --> 00:09:22,590
Biex tagħmel dan, se nużaw il-"Ħassar" dikjarazzjoni.

131
00:09:22,590 --> 00:09:27,400
Il-"Ħassar" dikjarazzjoni tista 'tintuża sabiex tneħħi kull jew xi ringieli mit-tabella.

132
00:09:27,400 --> 00:09:29,920
Jekk inti tixtieq li tneħħi xi ringiela speċifika mill-tabella,

133
00:09:29,920 --> 00:09:34,360
allura inti trid tipprovdi "Fejn" klawżola, u b'hekk jispeċifika kolonna

134
00:09:34,360 --> 00:09:37,660
li għandha tkun unika għall-filliera li tixtieq tneħħi.

135
00:09:37,660 --> 00:09:47,370
Dan huwa l-sintassi: Ħassar MINN ispazju ispazju tal-mejda, tagħna cupcake_cupboard, l-ispazju.

136
00:09:47,370 --> 00:09:51,760
Issa, f'dan il-punt, kull meta inti s'hawn fid-dikjarazzjoni ħassar tiegħek

137
00:09:51,760 --> 00:09:54,240
inti tixtieq li tkun attent ħafna.

138
00:09:54,240 --> 00:09:59,970
Per eżempju, jekk irrid biex imexxu din il-mistoqsija kif mingħajr ma tipprovdi xi "Fejn" klawsola

139
00:09:59,970 --> 00:10:04,500
I jitilfu d-data kollha f'din it-tabella cupcake_cupboard,

140
00:10:04,500 --> 00:10:09,590
imma minħabba I diġà jafu li IDs kejk tiegħi huma uniċi, jien ser jużaw

141
00:10:09,590 --> 00:10:12,410
l-Aħmar Velvet kejk ID għal "Fejn" klawsola tiegħi.

142
00:10:14,550 --> 00:10:20,670
FEJN ispazju kolonna tagħna, CakeID = 1.

143
00:10:20,670 --> 00:10:25,010
Minħabba dan huwa valur sħiħ m'hemmx bżonn li madwaru fil-kwotazzjonijiet uniku.

144
00:10:25,010 --> 00:10:27,020
Allura, ejja teżegwixxi l-istqarrija.

145
00:10:33,560 --> 00:10:35,990
Well, issa li aħna ve jintilfu l-eżistenza ta 'dan cupcake

146
00:10:35,990 --> 00:10:40,360
mit-tabella cupcake_cupboard tagħna, aħna biss ħaġa waħda xellug:

147
00:10:41,680 --> 00:10:43,680
Jagħmluha jisparixxu.

148
00:10:43,680 --> 00:10:46,990
Jien Christopher Bartholomew. Dan huwa CS50.