1 00:00:00,000 --> 00:00:02,000 [SQL] 2 00:00:02,000 --> 00:00:04,000 [CHRISTOPHER BARTHOLOMEW] [HARVARD UNIVERSITY] 3 00:00:04,000 --> 00:00:06,000 [THIS IS CS50] [CS50 TV] 4 00:00:07,950 --> 00:00:11,620 So, programmers use databases as a tool to store and organize 5 00:00:11,620 --> 00:00:13,620 our persistent data within tables. 6 00:00:13,620 --> 00:00:18,960 This means that your data is stored on non-volatile medians such as a hard drive, 7 00:00:18,960 --> 00:00:22,940 and even when the computer is off, the data is still intact. 8 00:00:22,940 --> 00:00:29,530 And, as programmers, our programs utilize databases for scenarios such as storing 9 00:00:29,530 --> 00:00:32,890 user information from web forms, looking up inventory, 10 00:00:32,890 --> 00:00:36,210 or updating information about what sites display. 11 00:00:37,150 --> 00:00:40,070 But how do we actually interact with our database, 12 00:00:40,070 --> 00:00:43,150 or what do we use to read, store, delete, 13 00:00:43,150 --> 00:00:46,070 and update our data within these database tables? 14 00:00:47,280 --> 00:00:49,700 Well, the answer is that we use a special type of database language 15 00:00:49,700 --> 00:00:53,400 which interacts directly with our database tables. 16 00:00:53,400 --> 00:00:56,740 It's name is Structured Query Language, 17 00:00:56,740 --> 00:00:58,740 [Structured Query Language] 18 00:00:58,740 --> 00:01:00,740 or what I refer to as SQL. 19 00:01:00,740 --> 00:01:05,100 >> Now, [Se-QueL], or SQL, isn't a programming language, 20 00:01:05,100 --> 00:01:08,580 but instead, it's a language that provides a standard set of commands 21 00:01:08,580 --> 00:01:13,520 to retrieve and manipulate data from a variety of database management systems. 22 00:01:13,520 --> 00:01:17,630 For the purpose of CS50, we'll go over four basic commands: 23 00:01:17,630 --> 00:01:21,210 select, insert, update, and delete. 24 00:01:21,210 --> 00:01:26,230 Furthermore, we'll utilize a database web interface called phpMyAdmin, 25 00:01:26,230 --> 00:01:29,890 which installed on the appliance to write our SQL statements. 26 00:01:30,830 --> 00:01:33,050 So, to help you remember these commands, 27 00:01:33,050 --> 00:01:37,080 I've brought some cupcakes in a cupboard to assist with our scenarios. 28 00:01:39,650 --> 00:01:42,210 Say you have a cupcake's database, 29 00:01:42,210 --> 00:01:44,490 where you store all information about your cupcakes. 30 00:01:44,490 --> 00:01:48,220 Now, databases can contain many tables 31 00:01:48,220 --> 00:01:50,950 and the tables themselves can contain many columns. 32 00:01:50,950 --> 00:01:57,020 Inside our cupcake's database, we have a table called cupcake_cupboard. 33 00:01:57,020 --> 00:02:00,500 This table will be used to store all information about cupcakes 34 00:02:00,500 --> 00:02:02,990 that are, well, in your cupboard. 35 00:02:02,990 --> 00:02:07,770 The columns that are inside your table represent the attributes of a cupcake. 36 00:02:07,770 --> 00:02:14,560 For example, the cupcake_cupboard's columns are 37 00:02:14,560 --> 00:02:15,920 Cake ID, CakeType, CakeColor, and CakeIsNice. 38 00:02:15,920 --> 00:02:23,040 This boolean-typed column is used to determine if the cake is nice or not nice. 39 00:02:23,040 --> 00:02:26,560 We're going to start with writing a select statement. 40 00:02:26,560 --> 00:02:32,160 Select statements are used to retrieve data of a particular database table. 41 00:02:32,160 --> 00:02:34,890 In this scenario, we want to know everything 42 00:02:34,890 --> 00:02:39,080 about all cupcakes that exist in our cupboard. 43 00:02:39,080 --> 00:02:48,670 The syntax to do this is "Select" space star, or *, space from space our table, 44 00:02:48,670 --> 00:02:52,050 which is cupcake_cupboard. 45 00:02:52,050 --> 00:02:54,640 Let's go ahead and execute that. 46 00:02:56,670 --> 00:03:00,140 >> As we can see, these are all the cupcakes in our cupboard. 47 00:03:00,140 --> 00:03:05,110 It's important to note that *, or asterisk, is a wild card character 48 00:03:05,110 --> 00:03:08,830 that denotes the gathering of all the columns of some table. 49 00:03:08,830 --> 00:03:13,650 Normally, we can access a specific column or columns 50 00:03:13,650 --> 00:03:16,950 by replacing * with the actual column name. 51 00:03:16,950 --> 00:03:21,220 If we want multiple columns, but not all, we can achieve this 52 00:03:21,220 --> 00:03:25,620 by writing the column names delimiting each column by a comma. 53 00:03:25,620 --> 00:03:28,620 For example, let's only retrieve the CakeId and CakeType in the cupcake_cupboard table. 54 00:03:28,620 --> 00:03:38,370 The syntax to do this is: SELECT space CakeID comma 55 00:03:38,370 --> 00:03:44,370 CakeType space from our table, cupcake_cupboard. 56 00:03:44,370 --> 00:03:46,370 Let's go ahead and execute this. 57 00:03:49,340 --> 00:03:52,670 And here, we now only have the two columns we specified 58 00:03:52,670 --> 00:03:54,670 for each cupcake in our cupboard. 59 00:03:54,670 --> 00:03:57,710 We can also refine our query results by specifying 60 00:03:57,710 --> 00:04:00,910 the "Where" clause just after the table name. 61 00:04:02,000 --> 00:04:05,410 For example, there appears to be a cupcake in our cupboard 62 00:04:05,410 --> 00:04:08,660 that is, well, not so nice looking. 63 00:04:08,660 --> 00:04:13,950 Let's figure out all the cupcakes in our cupboard that are, well, not so nice looking 64 00:04:13,950 --> 00:04:16,110 using the "Where" clause. 65 00:04:16,110 --> 00:04:26,390 The syntax to do this is: SELECT space * space FROM space cupcake_cupboard 66 00:04:26,390 --> 00:04:34,080 space WHERE space our conditional column, in this case CakeIsNice =, 67 00:04:34,080 --> 00:04:36,900 and the boolean value of False. 68 00:04:36,900 --> 00:04:42,750 It is important to note that if you are using strings, you must enclose it in single quotes. 69 00:04:42,750 --> 00:04:49,620 This is true for all strings in SQL, or, in SQL database terms, variant characters 70 00:04:49,620 --> 00:04:51,620 known as Varchar datatype. 71 00:04:51,620 --> 00:04:57,660 In this case, we are using True or False, which is a boolean value and not a string. 72 00:04:59,120 --> 00:05:00,660 Let's go ahead and execute this command. 73 00:05:00,660 --> 00:05:05,340 >> Lo and behold, it is the case that we have 1 chocolate 74 00:05:05,340 --> 00:05:07,920 not so nice cupcake in our cupboard. 75 00:05:09,620 --> 00:05:11,460 Next, we're going to write an insert statement. 76 00:05:11,460 --> 00:05:15,560 Insert statements are used to insert or add 77 00:05:15,560 --> 00:05:17,770 additional rows of data into your database table. 78 00:05:17,770 --> 00:05:23,160 Revisiting our scenario, let's assume that we've just made a brand new cupcake. 79 00:05:25,910 --> 00:05:30,080 Since we are very organized cupcake eaters, we're going to have to insert this new cupcake 80 00:05:30,080 --> 00:05:32,330 in our cupcake_cupboard table. 81 00:05:32,330 --> 00:05:40,690 The syntax to do this is this: Insert space into space our table, 82 00:05:40,690 --> 00:05:46,830 cupcake_cupboard, space ( , 83 00:05:46,830 --> 00:05:51,060 and here we specify the columns names, delimited by a comma, 84 00:05:51,060 --> 00:05:59,790 CakeType comma CakeColor comma CakeIsNice) space. 85 00:05:59,790 --> 00:06:06,540 Following this, we write the word VALUES space (, and here 86 00:06:06,540 --> 00:06:12,170 we enter the value for each column respectively, also delimited by a comma. 87 00:06:12,170 --> 00:06:17,830 Single quote, because they are all Varchar values we'll surround them in single quotes, 88 00:06:17,830 --> 00:06:26,780 PEANUT BUTTER' comma 'LIGHT BROWN' comma True. 89 00:06:26,780 --> 00:06:30,480 Now, it's important to give each row a unique number to identify itself. 90 00:06:30,480 --> 00:06:33,660 Autoincrementing a column provides this as 91 00:06:33,660 --> 00:06:37,410 "No two the same IDs can ever exist in this table." 92 00:06:37,410 --> 00:06:39,480 Let's go ahead and execute. 93 00:06:39,480 --> 00:06:45,380 There we go, all organized. 94 00:06:49,720 --> 00:06:52,100 >> The next SQL statement that we will write is an update statement. 95 00:06:52,100 --> 00:06:55,650 The update statement can be used to modify data within a column 96 00:06:55,650 --> 00:06:58,440 for any existing row in your database table. 97 00:06:59,670 --> 00:07:03,420 Earlier in our scenario, using a select statement, we identified a cupcake 98 00:07:03,420 --> 00:07:08,300 within our cupcake_cupboard's table whose CakeIsNice value was False. 99 00:07:08,300 --> 00:07:12,050 Let's assume that while our peanut butter cupcake was in the oven, 100 00:07:12,050 --> 00:07:15,790 we made our not-so-nice cupcake very nice. 101 00:07:18,020 --> 00:07:22,240 Being so exceptionally organized, we want our cupcake to reflect this value 102 00:07:22,240 --> 00:07:24,240 in our cupcake_cupboard table. 103 00:07:24,240 --> 00:07:28,710 Therefore, let's update our chocolate cupcake in the database to reflect this. 104 00:07:28,710 --> 00:07:39,720 The syntax is: UPDATE space our table, cupcake_cupboard, space SET space 105 00:07:39,720 --> 00:07:44,240 the column that we wish to change, CakeIsNice =. 106 00:07:44,240 --> 00:07:49,210 Then here we place our new value True space. 107 00:07:49,210 --> 00:07:54,290 Now, because we don't want to update all rows with this value, 108 00:07:54,290 --> 00:07:57,400 we want to provide a "Where" clause that will pinpoint us 109 00:07:57,400 --> 00:07:59,830 to the exact row that we want to modify. 110 00:07:59,830 --> 00:08:03,690 In this case, we know that there is only one cupcake 111 00:08:03,690 --> 00:08:06,670 that has a CakeIsNice value of False. 112 00:08:06,670 --> 00:08:11,030 Furthermore, we will also ensure that we are updating the correct row 113 00:08:11,030 --> 00:08:13,030 by using the "And" clause. 114 00:08:14,340 --> 00:08:17,270 We use the "And" clause to refine our query further. 115 00:08:17,270 --> 00:08:20,380 In this case, because we know that the cupcake is chocolate, 116 00:08:20,380 --> 00:08:23,160 we will use this CakeType column. 117 00:08:23,160 --> 00:08:31,500 WHERE space our conditional column space CakeIsNice space = False, 118 00:08:31,500 --> 00:08:38,330 and space CakeType = 'CHOCOLATE.' 119 00:08:38,330 --> 00:08:41,880 So, putting it all together, this update statement says 120 00:08:41,880 --> 00:08:44,670 find all the cupcakes in our cupcake cupboard, 121 00:08:44,670 --> 00:08:50,520 and if there is a cupcake whose column CakeIsNice contains the value False 122 00:08:50,520 --> 00:08:54,130 and CakeType contains the value CHOCOLATE, 123 00:08:54,130 --> 00:08:58,240 we want to update the specific rows CakeIsNice value to True. 124 00:08:58,240 --> 00:09:01,140 So, let's go ahead and execute the statement. 125 00:09:03,860 --> 00:09:05,860 And now, we're organized. 126 00:09:06,650 --> 00:09:09,220 >> All this talk of cupcakes has made me a bit hungry. 127 00:09:09,220 --> 00:09:11,360 I think I should help myself to one. 128 00:09:11,360 --> 00:09:17,670 But if I actually eat this cupcake, I should at least also remove its existence 129 00:09:17,670 --> 00:09:19,670 from our cupcake_cupboard table. 130 00:09:20,650 --> 00:09:22,590 To do this, we will use the "Delete" statement. 131 00:09:22,590 --> 00:09:27,400 The "Delete" statement can be used to remove all or some rows from the table. 132 00:09:27,400 --> 00:09:29,920 If you wish to remove some specific row from the table, 133 00:09:29,920 --> 00:09:34,360 then you must provide a "Where" clause, thus specifying a column 134 00:09:34,360 --> 00:09:37,660 that should be unique to the row that you wish to remove. 135 00:09:37,660 --> 00:09:47,370 This is the syntax: DELETE space FROM space our table, cupcake_cupboard, space. 136 00:09:47,370 --> 00:09:51,760 Now, at this point, whenever you are this far in your delete statement 137 00:09:51,760 --> 00:09:54,240 you want to be very careful. 138 00:09:54,240 --> 00:09:59,970 For example, if I want to run this query as is without providing some "Where" clause 139 00:09:59,970 --> 00:10:04,500 I would lose all the data in this cupcake_cupboard table, 140 00:10:04,500 --> 00:10:09,590 but because I already know that my cake IDs are unique, I'll use 141 00:10:09,590 --> 00:10:12,410 the Red Velvet cake ID for my "Where" clause. 142 00:10:14,550 --> 00:10:20,670 WHERE space our column, CakeID = 1. 143 00:10:20,670 --> 00:10:25,010 Because this is an integer value there is no need to surround it in single quotes. 144 00:10:25,010 --> 00:10:27,020 So, let's execute the statement. 145 00:10:33,560 --> 00:10:35,990 Well, now that we've wiped the existence of this cupcake 146 00:10:35,990 --> 00:10:40,360 from our cupcake_cupboard table, we only have one thing left: 147 00:10:41,680 --> 00:10:43,680 Make it disappear. 148 00:10:43,680 --> 00:10:46,990 I'm Christopher BartholOmew. This is CS50.