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

2
00:00:02,000 --> 00:00:04,000
[Christopher BARTHOLOMEW] [HARVARD UNIVERSITY]

3
00:00:04,000 --> 00:00:06,000
[HII NI CS50] [CS50 TV]

4
00:00:07,950 --> 00:00:11,620
Hivyo, programmers kutumia database kama chombo kuhifadhi na kuandaa

5
00:00:11,620 --> 00:00:13,620
data wetu kuendelea ndani ya meza.

6
00:00:13,620 --> 00:00:18,960
Hii ina maana kwamba data yako ni kuhifadhiwa kwenye medians zisizo tete kama vile gari ngumu,

7
00:00:18,960 --> 00:00:22,940
na hata wakati kompyuta ni mbali, data ni bado intact.

8
00:00:22,940 --> 00:00:29,530
Na, kama programmers, mipango yetu kutumia database kwa matukio kama vile hifadhi ya

9
00:00:29,530 --> 00:00:32,890
user taarifa kutoka fomu za mtandao, akatazama juu hesabu,

10
00:00:32,890 --> 00:00:36,210
au kuongezea maelezo kuhusu nini maeneo kuonyesha.

11
00:00:37,150 --> 00:00:40,070
Lakini jinsi gani sisi kweli kuingiliana na database yetu,

12
00:00:40,070 --> 00:00:43,150
au je, sisi kutumia kusoma, kuhifadhi, kufuta,

13
00:00:43,150 --> 00:00:46,070
na update data zetu ndani ya mbao hizo database?

14
00:00:47,280 --> 00:00:49,700
Naam, jibu ni kwamba sisi kutumia aina maalum ya lugha database

15
00:00:49,700 --> 00:00:53,400
ambayo inaingiliana moja kwa moja na meza yetu database.

16
00:00:53,400 --> 00:00:56,740
Ni jina ni Uliza muundo lugha,

17
00:00:56,740 --> 00:00:58,740
[Uliza muundo lugha]

18
00:00:58,740 --> 00:01:00,740
au nini mimi rejea kama SQL.

19
00:01:00,740 --> 00:01:05,100
>> Sasa, [Se-QueL], au SQL, ni si lugha ya programu,

20
00:01:05,100 --> 00:01:08,580
lakini badala yake, ni lugha ambayo inatoa seti ya kiwango ya amri

21
00:01:08,580 --> 00:01:13,520
retrieve na kuendesha data kutoka kwa aina ya mifumo ya usimamizi wa database.

22
00:01:13,520 --> 00:01:17,630
Kwa madhumuni ya CS50, tutaweza kwenda juu ya nne amri ya msingi:

23
00:01:17,630 --> 00:01:21,210
kuchagua, ingiza, update, na kufuta.

24
00:01:21,210 --> 00:01:26,230
Aidha, tutaweza kutumia mtandao database interface kuitwa phpMyAdmin,

25
00:01:26,230 --> 00:01:29,890
ambayo imewekwa kwenye appliance kuandika yetu kauli SQL.

26
00:01:30,830 --> 00:01:33,050
Hivyo, ili kukusaidia kukumbuka amri hizi,

27
00:01:33,050 --> 00:01:37,080
Nimekuwa kuletwa baadhi cupcakes katika kabati ili kusaidia na matukio yetu.

28
00:01:39,650 --> 00:01:42,210
Sema wewe kuwa na database ya cupcake,

29
00:01:42,210 --> 00:01:44,490
ambapo unaweza kuhifadhi taarifa zote kuhusu cupcakes yako.

30
00:01:44,490 --> 00:01:48,220
Sasa, database unaweza vyenye meza wengi

31
00:01:48,220 --> 00:01:50,950
na meza zenyewe unaweza vyenye nguzo nyingi.

32
00:01:50,950 --> 00:01:57,020
Ndani ya database cupcake wetu, tuna meza inayoitwa cupcake_cupboard.

33
00:01:57,020 --> 00:02:00,500
Meza hii itatumika kuhifadhi taarifa zote kuhusu cupcakes

34
00:02:00,500 --> 00:02:02,990
kwamba ni vizuri, katika kabati yako.

35
00:02:02,990 --> 00:02:07,770
nguzo kwamba ni ndani ya meza yako inawakilisha sifa ya cupcake.

36
00:02:07,770 --> 00:02:14,560
Kwa mfano, cupcake_cupboard ya nguzo ni

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

38
00:02:15,920 --> 00:02:23,040
Safu hii bulin-typed ni kutumiwa kuamua kama keki ni nzuri au si nzuri.

39
00:02:23,040 --> 00:02:26,560
Sisi ni kwenda kuanza na kuandika taarifa ya kuchagua.

40
00:02:26,560 --> 00:02:32,160
Chagua kauli hutumiwa kwa retrieve data ya meza fulani database.

41
00:02:32,160 --> 00:02:34,890
Katika hali hii, tunataka kujua kila kitu

42
00:02:34,890 --> 00:02:39,080
kuhusu yote cupcakes ambazo zipo katika kabati yetu.

43
00:02:39,080 --> 00:02:48,670
syntax ya kufanya hivyo ni "Chagua" nafasi nyota, au *, nafasi kutoka nafasi meza yetu,

44
00:02:48,670 --> 00:02:52,050
ambayo ni cupcake_cupboard.

45
00:02:52,050 --> 00:02:54,640
Hebu kwenda mbele na nitafanya hivyo.

46
00:02:56,670 --> 00:03:00,140
>> Kama tunavyoona, haya ni cupcakes wote katika kabati yetu.

47
00:03:00,140 --> 00:03:05,110
Ni muhimu kutambua kuwa *, au kinyota, ni pori kadi tabia

48
00:03:05,110 --> 00:03:08,830
kwamba inaashiria mkutano wa nguzo zote za meza baadhi.

49
00:03:08,830 --> 00:03:13,650
Kwa kawaida, tunaweza kupata safu maalum au nguzo

50
00:03:13,650 --> 00:03:16,950
kwa kuondoa * kwa jina halisi safu.

51
00:03:16,950 --> 00:03:21,220
Kama tunataka nguzo nyingi, lakini si wote, tunaweza kufikia lengo hili

52
00:03:21,220 --> 00:03:25,620
kwa kuandika majina ya safu usawanisho kila safu na comma.

53
00:03:25,620 --> 00:03:28,620
Kwa mfano, hebu tu retrieve CakeId na CakeType katika meza cupcake_cupboard.

54
00:03:28,620 --> 00:03:38,370
syntax ya kufanya hivyo ni: CHAGUA nafasi CakeID comma

55
00:03:38,370 --> 00:03:44,370
CakeType nafasi kutoka cupcake_cupboard meza yetu,.

56
00:03:44,370 --> 00:03:46,370
Hebu kwenda mbele na nitafanya hili.

57
00:03:49,340 --> 00:03:52,670
Na hapa, sisi sasa tu wana nguzo mbili sisi maalum

58
00:03:52,670 --> 00:03:54,670
kwa kila cupcake katika kabati yetu.

59
00:03:54,670 --> 00:03:57,710
Tunaweza pia kuboresha matokeo yetu swala kwa inayobainisha

60
00:03:57,710 --> 00:04:00,910
"Wapi" kifungu tu baada ya jina meza.

61
00:04:02,000 --> 00:04:05,410
Kwa mfano, kuna inaonekana kuwa cupcake katika kabati wetu

62
00:04:05,410 --> 00:04:08,660
kwamba ni vizuri, si hivyo nzuri kuangalia.

63
00:04:08,660 --> 00:04:13,950
Hebu takwimu nje cupcakes wote katika kabati yetu kwamba ni vizuri, si hivyo nzuri kuangalia

64
00:04:13,950 --> 00:04:16,110
kutumia "wapi" kifungu.

65
00:04:16,110 --> 00:04:26,390
syntax ya kufanya hivyo ni: CHAGUA nafasi * nafasi KUTOKA cupcake_cupboard nafasi

66
00:04:26,390 --> 00:04:34,080
nafasi WAPI nafasi safu yetu ya masharti, katika kesi hii CakeIsNice =,

67
00:04:34,080 --> 00:04:36,900
na thamani ya bulin ya Uongo.

68
00:04:36,900 --> 00:04:42,750
Ni muhimu kutambua kwamba kama wewe ni kutumia masharti, lazima uambatanishe ni katika quotes moja.

69
00:04:42,750 --> 00:04:49,620
Hii ni kweli kwa masharti yote katika SQL, au, katika suala database SQL, wahusika lahaja

70
00:04:49,620 --> 00:04:51,620
inayojulikana kama varchar datatype.

71
00:04:51,620 --> 00:04:57,660
Katika kesi hii, sisi ni kutumia Kweli au Uongo, ambayo ni thamani ya bulin na si kamba.

72
00:04:59,120 --> 00:05:00,660
Hebu kwenda mbele na kutekeleza amri hii.

73
00:05:00,660 --> 00:05:05,340
>> Hakika na tazama, ni kesi ya kwamba tuna 1 chocolate

74
00:05:05,340 --> 00:05:07,920
si hivyo nzuri cupcake katika kabati yetu.

75
00:05:09,620 --> 00:05:11,460
Next, sisi ni kwenda kuandika taarifa ya Insert.

76
00:05:11,460 --> 00:05:15,560
Kauli Insert hutumiwa kuingiza au kuongeza

77
00:05:15,560 --> 00:05:17,770
ziada ya safu za data katika database ya meza yako.

78
00:05:17,770 --> 00:05:23,160
Revisiting mazingira yetu, hebu kudhani kwamba tumekuwa tu alifanya bidhaa mpya cupcake.

79
00:05:25,910 --> 00:05:30,080
Tangu sisi ni sana kupangwa walaji cupcake, sisi itawabidi Insert hii cupcake mpya

80
00:05:30,080 --> 00:05:32,330
katika cupcake_cupboard meza yetu.

81
00:05:32,330 --> 00:05:40,690
syntax ya kufanya hivyo ni hii: Ingiza nafasi katika nafasi ya meza yetu,

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

83
00:05:46,830 --> 00:05:51,060
na hapa sisi kutaja majina nguzo, Delimited na comma,

84
00:05:51,060 --> 00:05:59,790
CakeType comma CakeColor comma CakeIsNice) nafasi.

85
00:05:59,790 --> 00:06:06,540
Kufuatia hali hii, sisi kuandika MAADILI neno nafasi (, na hapa

86
00:06:06,540 --> 00:06:12,170
sisi kuingia thamani kwa kila safu kwa mtiririko huo, pia Delimited na comma.

87
00:06:12,170 --> 00:06:17,830
Quote Single, kwa sababu wote ni maadili varchar tutaweza wanaowazunguka katika quotes moja,

88
00:06:17,830 --> 00:06:26,780
Koma 'comma' karanga siagi MWANGA KAHAWIA 'Kweli.

89
00:06:26,780 --> 00:06:30,480
Sasa, ni muhimu kutoa kila mstari idadi ya kipekee kutambua yenyewe.

90
00:06:30,480 --> 00:06:33,660
Autoincrementing safu hii hutoa kama

91
00:06:33,660 --> 00:06:37,410
 "Hakuna mbili vitambulisho huo unaweza milele kuwepo katika meza hii."

92
00:06:37,410 --> 00:06:39,480
Hebu kwenda mbele na nitafanya.

93
00:06:39,480 --> 00:06:45,380
Kuna sisi kwenda, wote kupangwa.

94
00:06:49,720 --> 00:06:52,100
>> ijayo SQL taarifa kuwa hatujaandika ni kauli update.

95
00:06:52,100 --> 00:06:55,650
Kauli update inaweza kutumika kurekebisha data ndani ya safu

96
00:06:55,650 --> 00:06:58,440
kwa yoyote mstari zilizopo katika database meza yako.

97
00:06:59,670 --> 00:07:03,420
Mapema katika mazingira yetu, kwa kutumia taarifa kuchagua, sisi kutambuliwa cupcake

98
00:07:03,420 --> 00:07:08,300
ndani ya meza cupcake_cupboard wetu ambao CakeIsNice thamani ilikuwa uongo.

99
00:07:08,300 --> 00:07:12,050
Hebu kudhani kuwa wakati karanga wetu siagi cupcake alikuwa katika tanuri,

100
00:07:12,050 --> 00:07:15,790
sisi alifanya cupcake wetu si-hivyo-nzuri nzuri sana.

101
00:07:18,020 --> 00:07:22,240
Kuwa hivyo exceptionellt kupangwa, tunataka cupcake wetu kutafakari thamani hii

102
00:07:22,240 --> 00:07:24,240
katika cupcake_cupboard meza yetu.

103
00:07:24,240 --> 00:07:28,710
Kwa hiyo, hebu update chocolate wetu cupcake katika orodha ya kutafakari hili.

104
00:07:28,710 --> 00:07:39,720
syntax ni: UPDATE nafasi meza yetu, cupcake_cupboard, nafasi SET nafasi

105
00:07:39,720 --> 00:07:44,240
safu kwamba tunataka mabadiliko, CakeIsNice =.

106
00:07:44,240 --> 00:07:49,210
Ndipo hapa sisi mahali thamani wetu mpya wa Kweli nafasi.

107
00:07:49,210 --> 00:07:54,290
Sasa, kwa sababu hatutaki update safu yote na thamani ya hii,

108
00:07:54,290 --> 00:07:57,400
tunataka kutoa "wapi" kifungu kwamba pinpoint sisi

109
00:07:57,400 --> 00:07:59,830
kwa mstari halisi kwamba tunataka kurekebisha.

110
00:07:59,830 --> 00:08:03,690
Katika kesi hiyo, tunajua kwamba kuna Mungu mmoja tu cupcake

111
00:08:03,690 --> 00:08:06,670
ambayo ina thamani CakeIsNice ya Uongo.

112
00:08:06,670 --> 00:08:11,030
Aidha, sisi pia kuhakikisha kwamba sisi ni uppdatering mstari sahihi

113
00:08:11,030 --> 00:08:13,030
kwa kutumia "Na" kifungu.

114
00:08:14,340 --> 00:08:17,270
Sisi kutumia "Na" kifungu kuboresha hoja zetu zaidi.

115
00:08:17,270 --> 00:08:20,380
Katika kesi hiyo, kwa sababu tunajua kwamba cupcake ni chocolate,

116
00:08:20,380 --> 00:08:23,160
tutatumia safu hii CakeType.

117
00:08:23,160 --> 00:08:31,500
WAPI nafasi safu yetu masharti nafasi CakeIsNice nafasi = Uongo,

118
00:08:31,500 --> 00:08:38,330
na nafasi CakeType = 'CHOCOLATE.'

119
00:08:38,330 --> 00:08:41,880
Hivyo, kuweka yote pamoja, kauli hii update anasema

120
00:08:41,880 --> 00:08:44,670
kupata cupcakes wote katika kabati wetu cupcake,

121
00:08:44,670 --> 00:08:50,520
na kama kuna cupcake ambao safu CakeIsNice ina thamani Uongo

122
00:08:50,520 --> 00:08:54,130
na CakeType ina CHOCOLATE thamani,

123
00:08:54,130 --> 00:08:58,240
tunataka update safu maalum CakeIsNice thamani ya kweli.

124
00:08:58,240 --> 00:09:01,140
Hivyo, hebu kwenda mbele na kutekeleza kauli.

125
00:09:03,860 --> 00:09:05,860
Na sasa, sisi ni kupangwa.

126
00:09:06,650 --> 00:09:09,220
>> Majadiliano hayo yote ya cupcakes imefanya mimi kidogo njaa.

127
00:09:09,220 --> 00:09:11,360
Nadhani lazima kusaidia mwenyewe kwa moja.

128
00:09:11,360 --> 00:09:17,670
Lakini kama mimi kwa kweli kula hii cupcake, mimi lazima angalau pia kuondoa kuwepo kwake

129
00:09:17,670 --> 00:09:19,670
 kutoka cupcake_cupboard meza yetu.

130
00:09:20,650 --> 00:09:22,590
Ili kufanya hivyo, tutatumia "Delete" kauli.

131
00:09:22,590 --> 00:09:27,400
"Futa" taarifa zinaweza kutumika kuondoa safu yote au baadhi kutoka meza.

132
00:09:27,400 --> 00:09:29,920
Kama unataka kuondoa baadhi safu maalum kutoka meza,

133
00:09:29,920 --> 00:09:34,360
basi lazima kutoa "wapi" kifungu, hivyo inayobainisha safu

134
00:09:34,360 --> 00:09:37,660
kwamba lazima ya kipekee ya mstari kwamba unataka kuondoa.

135
00:09:37,660 --> 00:09:47,370
Hii ni syntax: FUTA nafasi KUTOKA nafasi meza yetu, cupcake_cupboard, nafasi.

136
00:09:47,370 --> 00:09:51,760
Sasa, katika hatua hii, wakati wewe ni umbali huu katika taarifa yako futa

137
00:09:51,760 --> 00:09:54,240
unataka kuwa makini sana.

138
00:09:54,240 --> 00:09:59,970
Kwa mfano, kama nataka kukimbia hoja hii kama ni bila kutoa baadhi ya "wapi" kifungu

139
00:09:59,970 --> 00:10:04,500
Napenda kupoteza data zote katika meza hii cupcake_cupboard,

140
00:10:04,500 --> 00:10:09,590
lakini kwa sababu tayari najua kwamba keki wangu vitambulisho ni ya kipekee, mimi itabidi kutumia

141
00:10:09,590 --> 00:10:12,410
Red Velvet keki ID kwa kifungu yangu "Wapi".

142
00:10:14,550 --> 00:10:20,670
WAPI nafasi safu yetu, CakeID = 1.

143
00:10:20,670 --> 00:10:25,010
Kwa sababu hii ni thamani integer hakuna haja ya zinazunguka katika quotes moja.

144
00:10:25,010 --> 00:10:27,020
Hivyo, hebu nitafanya taarifa.

145
00:10:33,560 --> 00:10:35,990
Naam, sasa kwamba tumekuwa kuipangusa kuwepo cupcake hii

146
00:10:35,990 --> 00:10:40,360
kutoka cupcake_cupboard meza yetu, sisi tu moja kitu kushoto:

147
00:10:41,680 --> 00:10:43,680
Matokeo yake ni kutoweka.

148
00:10:43,680 --> 00:10:46,990
Mimi nina Christopher Bartholomayo. Hii ni CS50.