WEBVTT 00:00.190 --> 00:05.270 OK so we made it through all of that crud that's sort of a joke. 00:05.520 --> 00:08.610 Anyways we're now talking about string functions. 00:08.670 --> 00:14.340 So basically we've seen how to interact with our database create stuff insert it update delete all of 00:14.340 --> 00:14.860 that. 00:15.330 --> 00:21.000 But now we're going to focus more on select or reading the data out because what we've been doing right 00:21.000 --> 00:23.720 now is just reading the data directly as it is. 00:23.790 --> 00:29.610 So I can say you know I select breed and name from cats and that's it. 00:29.610 --> 00:34.630 I get it back and that's great but there are things that I could do with that data. 00:34.650 --> 00:41.250 So with numbers I might want to add them together or I might want to I don't know reverse a string or 00:41.250 --> 00:47.690 replace all spaces with a certain character or conjoin first name and last name or conjoin you know 00:47.790 --> 00:49.700 two pieces of data together. 00:49.800 --> 00:55.490 All of these are different operations different ways of printing or data out that we can do. 00:55.710 --> 01:00.340 And so we're going to start focusing on them in this section we're talking about string functions. 01:00.360 --> 01:04.680 So there's going to be a bunch of things that we see I already mentioned some of them we can concatenate 01:04.680 --> 01:09.300 words we can take certain parts of a string and leave other parts out. 01:09.450 --> 01:13.370 We can reverse we can uppercase lowercase replace characters all sorts of things. 01:13.380 --> 01:19.590 But before we get there first thing you want to do is show you something that I think is pretty useful 01:19.720 --> 01:22.140 and that we'll use pretty often from here on out. 01:22.440 --> 01:28.440 So up until this point all of the sequel that we've been writing has been manually entered into the 01:28.470 --> 01:30.880 command line into the CLIA here. 01:30.900 --> 01:37.620 So if I want to create a table I have to type create table and then the name of the table cat but let's 01:37.620 --> 01:44.580 say that I mess it up so let's say I typed singular cat which technically isn't a problem per se but 01:44.580 --> 01:46.450 it's a good idea to have things pluralized. 01:46.560 --> 01:47.600 It won't break but. 01:47.820 --> 01:49.780 So let's say I don't notice for a little bit though. 01:49.920 --> 01:58.110 So I create a table cat and we want you know name is Vardar 100. 01:58.200 --> 02:00.750 Age is 10. 02:00.810 --> 02:03.770 When are doing age is it just like that. 02:03.930 --> 02:06.450 Let's say we leave it at that. 02:06.450 --> 02:08.790 Now this isn't a great example because it has a short line. 02:08.830 --> 02:17.840 But if I messed it up I have to go all the way back here and edit it right and do cats like this. 02:18.210 --> 02:24.390 But let's now suppose that I have something even longer and I want to put it on separate lines and format 02:24.390 --> 02:33.990 it nicely like this great table cats and then we have printed c there and you do name is var char and 02:33.990 --> 02:41.000 let's say misfile this like that age isn't it. 02:42.180 --> 02:45.140 And then I realized oh shoot I totally messed up. 02:45.300 --> 02:47.840 I don't have a way of getting back there. 02:48.120 --> 02:49.890 I don't have an easy way of getting here. 02:50.190 --> 02:52.320 So I have to retype the whole line or copy it. 02:52.320 --> 02:54.080 There's no simple way of editing it. 02:54.330 --> 03:01.280 So what I want to show you is that we can run code from a file and that's really really useful. 03:01.620 --> 03:04.480 Running sequel files that we're going to talk about here. 03:04.560 --> 03:10.120 So basically by the end we will be working with files that look like this. 03:10.130 --> 03:16.350 They're nicely formatted they have nice syntax highlighting we can edit them make changes and so on 03:17.040 --> 03:21.000 and run them when we're ready rather than typing things manually into the command line. 03:21.240 --> 03:29.160 That doesn't mean that we won't be typing things here because it's very useful to go away Siri. 03:29.340 --> 03:31.200 It's a very useful tool. 03:31.230 --> 03:37.920 I should probably add that out while it's a useful tool that allows us to explore things try things 03:38.010 --> 03:39.660 out if they're short lived. 03:39.660 --> 03:44.640 It's great but if we're really trying to do more serious stuff it's much easier and better to work in 03:44.640 --> 03:45.430 a file. 03:45.540 --> 03:47.700 You can make edits you can share it with people. 03:47.790 --> 03:49.490 So that's what we're going to do. 03:49.500 --> 03:50.910 So let's go ahead. 03:50.910 --> 03:57.270 I mean get out of this and I'll just hit enter and get an error and that's fine. 03:57.750 --> 04:05.130 So right now I'm in Cat's database and if I do show tables we don't know I do have a catch table but 04:05.130 --> 04:06.800 if I look at it it's empty. 04:06.810 --> 04:11.200 So what I'm actually going to do is drop that catch table drop table cats. 04:11.620 --> 04:12.320 OK. 04:12.900 --> 04:19.320 So what I want to do is remake the cat's table and what I could do is what I just did here create table 04:19.560 --> 04:21.450 cat and do the whole thing. 04:21.750 --> 04:26.850 But rather than that I'm going to do it in a file and then all I'll have to do here is run that file 04:27.030 --> 04:31.470 execute it with one short line so I appear to have a new file. 04:31.590 --> 04:33.720 Again you can do file new file. 04:33.720 --> 04:35.940 You can use the shortcut shown here. 04:35.940 --> 04:40.670 You can right click and do new file as well whatever we do though. 04:40.710 --> 04:47.610 We need a new file and I'm just going to put something in there so create table and I am showing you 04:47.610 --> 04:53.610 creating a table but we'll be putting any sequel queery in here creating a table dropping tables inserting 04:53.610 --> 05:00.500 data selecting any sort of query we want to create table cat that's so easy now to format it nicely. 05:00.870 --> 05:05.900 We'll have a name which is Vardar 100. 05:06.180 --> 05:08.220 Let's do an idea as well. 05:08.220 --> 05:09.490 See how easy that was. 05:09.600 --> 05:21.600 If I made a mistake ID will be an ant not no auto increment comma name or char and then we'll do age 05:21.730 --> 05:23.760 isn't and will do. 05:23.760 --> 05:26.270 Primary Key is cat ID. 05:26.640 --> 05:28.370 So this should all be review. 05:29.010 --> 05:32.090 But what's nice is I have this query like this. 05:32.310 --> 05:34.320 I need to save the file. 05:34.830 --> 05:39.480 So I did command us but you can also go file save. 05:39.480 --> 05:41.400 And we need to give it a name any name will do. 05:41.490 --> 05:46.550 I'll just call it first file and this is the most important part as well. 05:46.620 --> 05:49.790 So that's what identifies the contents of the file as secret code. 05:49.920 --> 05:53.950 So we need that click Save make sure changed up there. 05:54.090 --> 05:58.700 Also you can see we got a syntax highlighting now which is also very useful. 05:59.150 --> 06:02.700 Ok so I'm sure by now you can see the value in doing this. 06:02.700 --> 06:04.150 You have a history of your work. 06:04.200 --> 06:06.060 You can edit it share it with people. 06:06.090 --> 06:07.710 It's just a much better way of doing it. 06:07.710 --> 06:09.490 So how do we actually run it. 06:10.080 --> 06:15.140 Well this is a magic source and then a file on it. 06:15.570 --> 06:18.570 So in our case our file name is first filed as well. 06:18.570 --> 06:23.050 So we just need source file name as well. 06:23.160 --> 06:29.760 So I'm going actually quit out of the Seelye and first thing I want to show you is that it matters where 06:29.760 --> 06:34.060 you are when you initialize your Seelye. 06:34.530 --> 06:39.620 So when I type LS here and if you're not familiar with that it just lists the current files. 06:39.660 --> 06:42.090 So this shows me is that I have book data. 06:42.090 --> 06:44.840 First file and problems are. 06:45.120 --> 06:46.860 So we want first file. 06:46.860 --> 06:48.440 Don't worry about these other two. 06:48.540 --> 06:52.010 So we are in the correct directory to access that file. 06:52.020 --> 07:02.050 So if I start up to a lie with my ask you will see T.L. space Selye and I use the cat app database just 07:02.050 --> 07:04.300 like that I'll make some space. 07:04.300 --> 07:10.830 Now all I do is source first file dot as well just like that. 07:11.080 --> 07:12.760 And we'll see what happens. 07:12.790 --> 07:14.130 It says queery OK. 07:14.230 --> 07:15.690 So now how do we know it works. 07:15.760 --> 07:21.340 Well we could do describe cats and here's our table. 07:21.730 --> 07:26.310 So that's all there is to actually executing the file source and the file name. 07:26.560 --> 07:28.780 But what I want to show you two things. 07:28.780 --> 07:30.580 I'll go ahead and make a new directory. 07:30.910 --> 07:36.080 You don't have to do this but I'm just going to call it I don't know testing. 07:36.520 --> 07:43.450 And then instead of testing I'll make a new file and what I'll put inside of that file is a select statement. 07:43.450 --> 07:46.930 So let's start from cat. 07:47.560 --> 07:55.810 Actually let's do an insert insert into cats and we'll just do what we have here. 07:55.990 --> 08:02.820 Name and age which I'm just realizing and capitalized so we could obviously go back and change that 08:02.830 --> 08:03.580 here and rerun it. 08:03.610 --> 08:05.380 But we'll leave it. 08:05.590 --> 08:09.280 So insert into cat's name and age just like that 08:11.770 --> 08:18.790 values and name will be let's do Charlie. 08:19.060 --> 08:23.560 Age will be let's do an old cat 17. 08:23.770 --> 08:24.830 Save the file. 08:27.140 --> 08:37.880 And you file save and make sure the file is as well and so do rename and I'll just call this insert 08:37.920 --> 08:39.640 dot as well. 08:40.340 --> 08:44.330 OK let's actually just to show you we can do more than one thing at a time. 08:44.360 --> 08:46.520 Insert both or let's insert too. 08:46.520 --> 08:48.560 And this one will be called. 08:48.830 --> 08:57.440 I think we already have a Sindee let's do a Khani who is 10 and then at the end we'll also do select 08:57.440 --> 08:59.930 star from cats just like that. 09:00.230 --> 09:04.420 So these three things will be executed at once if we do it correctly. 09:04.430 --> 09:10.670 So now to reference this file our Seelye is running in this folder so we can't just say insert data 09:10.690 --> 09:11.630 as well. 09:11.780 --> 09:19.140 If we did that source in-circuit as well it tells us it can't find that file. 09:19.160 --> 09:28.000 So what we need to do is do a source testing slash insert data as well and there we go. 09:28.490 --> 09:29.350 So that's important. 09:29.370 --> 09:31.540 You have to access the correct path. 09:31.550 --> 09:34.810 You have to refer to this file by its full path to get there. 09:35.060 --> 09:40.000 So if you're in this folder all that we can access are these. 09:40.570 --> 09:43.660 But if we drill to this folder then we have access. 09:43.670 --> 09:48.040 And obviously you could extrapolate that to as many layers as you need. 09:48.320 --> 09:50.330 So now let's make sure it worked. 09:50.390 --> 09:54.600 You can see after it worked we had queery OK one row affected. 09:54.620 --> 09:56.380 That was our first answer. 09:56.600 --> 10:01.990 And then the same thing our second insert and then our select statement gave us this right here. 10:02.510 --> 10:04.830 So that's all there is to running code from a file. 10:05.240 --> 10:06.580 It's really really useful. 10:06.590 --> 10:08.980 We'll be doing it pretty much nonstop. 10:09.110 --> 10:14.150 And the first thing that we're going to do is actually get some new data into our database using this 10:14.300 --> 10:14.950 in the next video.