1 00:00:00,150 --> 00:00:00,510 All right. 2 00:00:00,510 --> 00:00:04,800 So as I mentioned, we want to have the same data set, the same table, the same rows, so that we 3 00:00:04,800 --> 00:00:05,670 get the same results. 4 00:00:05,670 --> 00:00:11,550 Throughout this section, we're going to be working with a very simple books data set where a bookshop, 5 00:00:11,550 --> 00:00:16,170 a bookstore, and there's a file that I'd like you to download attached to this video. 6 00:00:16,200 --> 00:00:19,350 It's called Book Data or Books Data SQL. 7 00:00:19,380 --> 00:00:21,690 It looks like this right here. 8 00:00:21,690 --> 00:00:27,720 So I've actually opened it inside of SQL Workbench or you could do it inside of DB gate, or you don't 9 00:00:27,720 --> 00:00:29,040 even have to open it at all. 10 00:00:29,040 --> 00:00:34,920 But I want to show two options for getting this into a database for actually running this. 11 00:00:35,010 --> 00:00:39,450 So what I'm going to do is start by making a new database. 12 00:00:39,450 --> 00:00:40,710 So I'll call this. 13 00:00:41,790 --> 00:00:43,110 How about just book shop? 14 00:00:44,810 --> 00:00:45,480 Great. 15 00:00:45,500 --> 00:00:47,570 Let's use that database here. 16 00:00:48,080 --> 00:00:49,040 Book shop. 17 00:00:49,280 --> 00:00:50,510 And of course, you could. 18 00:00:50,510 --> 00:00:53,210 Oops, I didn't need to do use database. 19 00:00:53,210 --> 00:00:54,710 It's just use bookshop. 20 00:00:55,490 --> 00:00:59,600 We could do this all from within SQL Workbench or something like that. 21 00:00:59,600 --> 00:01:00,590 It doesn't matter. 22 00:01:00,590 --> 00:01:03,740 But what I'm going to do is refresh over here. 23 00:01:03,740 --> 00:01:09,770 The first way I'll show you to run this is the simplest, which is just to click run here and make sure 24 00:01:09,770 --> 00:01:11,930 we're using our bookshop database. 25 00:01:11,930 --> 00:01:15,290 So just double click over here and now I'm going to run this script. 26 00:01:16,140 --> 00:01:21,420 And if I head over here, select star from books, we should see our books data. 27 00:01:21,420 --> 00:01:26,310 It doesn't print out nicely when my font is so zoomed in so that you guys can read it, but it's all 28 00:01:26,310 --> 00:01:26,750 there. 29 00:01:26,760 --> 00:01:28,830 Let's describe books as well. 30 00:01:29,430 --> 00:01:30,960 So that's one option. 31 00:01:31,350 --> 00:01:33,400 Let's just talk about this data briefly, right? 32 00:01:33,450 --> 00:01:37,560 We've got a book ID, which is a primary key auto increment. 33 00:01:37,560 --> 00:01:38,400 It's an integer. 34 00:01:38,400 --> 00:01:41,520 Then we have title author, first name, author, last name. 35 00:01:41,520 --> 00:01:44,880 Those are all text or VAR card fields 100 characters. 36 00:01:45,000 --> 00:01:51,480 And then we have the release here, the quantity we have in stock and the length in pages of each book. 37 00:01:52,080 --> 00:01:57,600 And then we have what, 16 books that we've inserted based off of this file that I gave you. 38 00:01:58,020 --> 00:01:59,430 So that's one option. 39 00:01:59,430 --> 00:02:05,020 And remember, you could do all of this from within a SQL workbench or DB gate or any competitor or 40 00:02:05,040 --> 00:02:06,210 competing tool. 41 00:02:06,660 --> 00:02:11,910 It's just a pain for me to show the results in here because you know, it's so tiny I can't increase 42 00:02:11,910 --> 00:02:16,680 this font size, whereas here I can get something really immediate and obvious. 43 00:02:16,800 --> 00:02:23,760 Now another option for running a file is instead of opening it directly in a gooey and then running 44 00:02:23,760 --> 00:02:28,710 it from that gooey, what we can do is run the file from the command line. 45 00:02:28,710 --> 00:02:33,150 So I'm actually going to drop that table drop table books. 46 00:02:33,150 --> 00:02:34,920 I'm going to recreate it in just a moment. 47 00:02:34,920 --> 00:02:42,480 So now we have notebooks, and this file that I gave you is called Book Data SQL, and we have a special 48 00:02:42,480 --> 00:02:46,290 command we can run from within the MySQL shell called Source. 49 00:02:46,290 --> 00:02:50,250 Now, I warned you right now, if you don't know how to navigate in your terminal, if you don't know 50 00:02:50,250 --> 00:02:56,040 how to use LZ and CD, then this is not worth your time because you can just use the gooey and it works 51 00:02:56,040 --> 00:02:57,150 very, very easily. 52 00:02:57,150 --> 00:02:57,360 Right? 53 00:02:57,360 --> 00:02:58,530 That was like one click. 54 00:02:58,530 --> 00:02:59,610 That's all we had to do. 55 00:02:59,730 --> 00:03:05,280 But if you are using the command line and you're comfortable with it, you can use this command. 56 00:03:05,280 --> 00:03:08,430 So I'm actually going to leave my SQL just for a moment. 57 00:03:08,760 --> 00:03:10,530 If I can remember, quit. 58 00:03:10,830 --> 00:03:12,510 I have the file right here. 59 00:03:12,510 --> 00:03:14,190 I put it in this folder. 60 00:03:14,490 --> 00:03:16,590 It was in my downloads, I believe. 61 00:03:16,590 --> 00:03:17,100 Yeah. 62 00:03:17,100 --> 00:03:19,320 So I'm going to go back into that folder. 63 00:03:19,350 --> 00:03:20,790 My SQL. 64 00:03:21,240 --> 00:03:21,900 Whoops. 65 00:03:22,800 --> 00:03:23,490 What did I call it? 66 00:03:23,490 --> 00:03:25,020 My SQL course, I think. 67 00:03:25,670 --> 00:03:26,550 There it is. 68 00:03:26,570 --> 00:03:32,150 Just to show you, I navigate where this file is, and then I'll start up my SQL. 69 00:03:32,300 --> 00:03:34,040 Dash you route. 70 00:03:34,040 --> 00:03:35,690 Dash P for password. 71 00:03:35,720 --> 00:03:36,980 I'll put in my password. 72 00:03:36,980 --> 00:03:40,010 This is all recap, except I spelled my password wrong. 73 00:03:40,040 --> 00:03:40,880 Of course. 74 00:03:42,540 --> 00:03:46,140 And now I'm in here, and now I can source that file. 75 00:03:46,140 --> 00:03:47,490 But there's going to be a problem. 76 00:03:47,490 --> 00:03:49,230 Book data SQL. 77 00:03:49,650 --> 00:03:56,820 The problem is that I'm not using a database, so I have to tell my SQL what database to use. 78 00:03:56,820 --> 00:04:01,080 And this is, if I do a select database, we'll see. 79 00:04:01,080 --> 00:04:02,610 I'm not using a database. 80 00:04:02,610 --> 00:04:05,750 It's set to null, meaning the lack of a value. 81 00:04:05,760 --> 00:04:07,230 There is no current database. 82 00:04:07,470 --> 00:04:13,200 So I already made a database called Book Shop previously, but if you didn't have that yet, you could 83 00:04:13,200 --> 00:04:17,220 create DB or create database bookshop or something like that. 84 00:04:17,220 --> 00:04:19,620 I'll just use bookshop now. 85 00:04:19,620 --> 00:04:20,910 Bookshop has nothing in it. 86 00:04:20,910 --> 00:04:23,220 If I look at the tables, it's empty. 87 00:04:23,220 --> 00:04:32,130 But now I can run my source command book, data SQL, and it created a new table and 16 rows. 88 00:04:32,130 --> 00:04:34,860 If I describe books, there it is. 89 00:04:34,860 --> 00:04:38,370 If I select star from books, it's going to be ugly. 90 00:04:38,370 --> 00:04:40,110 But 16 rows. 91 00:04:40,440 --> 00:04:46,050 So if you are not comfortable using and navigating via the command line, don't bother worrying about 92 00:04:46,050 --> 00:04:47,580 any of what I just showed you. 93 00:04:47,610 --> 00:04:52,770 It's way easier for most people to open up a script in one of these gooey tools and then run it from 94 00:04:52,770 --> 00:04:53,220 there. 95 00:04:53,250 --> 00:04:57,300 The only thing is you have to make sure to create the database first and use the right database. 96 00:04:57,300 --> 00:05:00,930 Because my script that I give you only says to make a table. 97 00:05:00,930 --> 00:05:04,260 It doesn't say what database to use or to create a database. 98 00:05:04,470 --> 00:05:11,070 And I didn't show this, but the way I opened this in here, I actually dragged the file into the graphical 99 00:05:11,070 --> 00:05:11,550 editor. 100 00:05:11,550 --> 00:05:16,350 But you can also just click that little folder and then navigate to the file and open it. 101 00:05:16,350 --> 00:05:17,700 And that's really all there is to it. 102 00:05:17,700 --> 00:05:19,500 We now have our data set loaded. 103 00:05:19,500 --> 00:05:24,510 Let's move on with all these string functions, except we're only covering a handful, but let's move 104 00:05:24,510 --> 00:05:25,500 on to talk about them.