Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
In this movie, I am going to show you how to use the SQLite 3 Command Line tool on both the Mac and the PC. First, we'll start with the Mac. So we are going to talk a little bit about using the SQLite 3 Command Line application on a Mac. We are going to open the Terminal application, because that's how we get to the command line on a Mac, and this will put us with a command line in my Home directory here, and so it will be your Home directory on your Mac. So I am going to try not to assume that you know very much about the command line. In UNIX, this is the Bash shell, which is a default on a Mac, and the commands here are very similar to what you might find in most UNIXes.
In particular, this is based on BSD UNIX, and if you're familiar with UNIX at all, it should all be very, very familiar. So the first thing we are going to do is we are going to make a symbolic link, because getting to the exercise files in the XAMPP directory is a little bit circuitous on a Mac. So I'm going to type 'ln,' which is the command that will make a symbolic link, and that's a lowercase l and a lowercase n; UNIX command line is case-sensitive. I am going to say -s, which will make it a symbolic link, and then we are going to navigate to the applications folder, so I am going to type a slash and a capital A and maybe a couple of ps and hit the Tab key, and it will complete that for me, so I don't have to type it all out and perchance misspell it, and then a capital X and a capital A, and that's probably enough for XAMPP.
I will hit the Tab again, and it will complete that. And then HT, and that's probably enough, hit Tab. You'll notice it doesn't put the slash in for me there because that itself is a symbolic link. So I am going to put in the slash for it, and then a capital E, lowercase x, and that should be enough for our exercise files. Now I am going to backspace over that last slash because I don't really want that in my symbolic link, and press the Spacebar and the period. And so you should have a command that looks exactly like this. And then press Enter, and that has now created a symbolic link.
So if I bring up a listing of my directory, which is with ls - a, like that, you'll see that I have ExerciseFiles right here, and that is a symbolic link. So I am going to cd into that, and again, I can just type a couple of letters, and the Tab, and now ls-al, and I can see my entire ExerciseFiles folder right here. That makes it easy for me to get into my ExerciseFiles folder. So now I am going to navigate from here into the SQL folder, and we will look in there, and we see that we have these SQL files and these db files.
The db files are already made database files, and the SQL files are actually the source SQL for creating those files. So one of the things we are going to do here is we are going to use the command line application to create one of these db files, just so we can see how that's done. So I am going to type 'sqlite 3,' like that, all lowercase. So this is the command line application that comes with SQLite 3 and is designed for managing these databases. So if I give it the name of a database, say like test.db, now I have that database open. And if I want to see what's in it, I can type select * from sqlite_master; all SQL statements are terminated with a semicolon, and there is the schema for this database.
It's got a table named customer, and here is the create statement that made that table, and here is the one that created the item table, and here's one that created the sale table. So we have all that in there, and if I want to say select * from customer;, then I get the contents of that. At anytime, you can type help with a dot in front of it, .help. You have to scroll back to see the whole thing. We get all the dot commands that are available, plus you can just type any SQL directly into the command line application.
So I am going to Exit and type .quit, to get out of this and show you one other way to use this. If I want to type sqlite3, like that, and give it the name of a database, I can then just put a command in quotes. I can say, select * from customer, and I get the listing right there, without having to go into the application and type it on the command line inside the application. You can also redirect commands from a file. So if I want to create a new file that's just like test.db, I can say sqlite3 newtest.db and use the redirect operator from the command line, which is the left angle bracket.
If you press Shift on the Comma key, you will get that left angle bracket, at least on an American keyboard, and then type test-sqlite3.sql, and this is the source SQL for the test.db file. So if I do that, it has now created a new database file. If I look at the listing of the directory here, we have this new test.db. That's a new file created from the test-sqlite3 sql. So we can look inside that test-sqlite3. sql and use the less command, and we can see what's in there.
I am scrolling with j. You can scroll with j and k in here and just press q to get out of it, and so you can see all the SQL in there that's used to create the test.db. So that's how you use the SQLite 3 command line application on a Mac. Now I am going to show you how to use the SQLite 3 command line utility on the PC. First, we are going to locate the utility, and so I am going to open my Computer and go in the C drive, and I have installed XAMPP in the default location, and so I am going to go in the apache directory.
This is not intuitive where it is. And it's in the bin directory under the apache directory. Scroll down here, and there it is. So what I want is this path up here. So I am going to do my little trick here that works on Windows 7 - I believe it also works on Windows Vista, where I put my mouse in this little box up there, and it will give me that path, and I can just press Ctrl+C and copy it. And this is going to come in really handy here in a minute. Now I am going to right-click on the desktop and say New > Shortcut and type 'cmd.exe,' and that will give me a shortcut to a command shell.
When I press Enter, it will ask me for a name, and I am just going to say, SQL, because what this is going to be is it's going to be a command prompt in the folder that has the default SQL stuff in it. Press Enter for finish, and now we have that. But before you run it, I'm going to right-click on that shortcut and bring up Properties. I am going to do a couple of things first here. I am going to just move this out of the way, and I am going to get another Explorer window, that's computer over here, and I am going to go to my ExerciseFiles and click on SQL, and bring my cursor up there and copy that path.
And that's the path that I am going to paste in here. So the Start in directory is going to be there, because that's where we have our SQL stuff, and that's where we're going to want the command line utility. So I will say OK, and now we have this shortcut that brings us right there, and there is that directory. So I will quit that, and then I am going to put this back in my Copy buffer. And now I am going to add this to the command execution path, so I can just type sqlite3, and it will bring that up. So I am going to go to my Control panel, and System and Security, and down here to System. Then I am going to click Advanced system settings.
For some reason, that's where they stuck this. There is a button down here that says Environment Variables, and I am going to scroll down here to the one that says Path, and I am just going to double-click on that. And then all the way out at the end, I am pressing my End key, I press a semicolon and paste my copy buffer there. Press OK and OK and OK and red X. And now when I bring this up, I am there in my SQL directory, and I can type 'sqlite3' and press Enter, and I have got the SQLite command line utility.
So all that setup stuff, you don't ever need to do that again; all you need to do is double-click on this shortcut icon, and type in sqlite3 up here, and there it is. Now in this utility, you can type .help, and you get a full bunch of help here. I can scroll up and see all of that, and that's all the dot commands that work inside the SQLite 3 command line utility. I am going to type '.quit' and get out of it, and dir, and we see our databases.
So the way this works is you type sqlite3 and the name of a database. So in this case I am going to bring up the test.db database and press Enter, and that's been open now and just select * from sqlite master, sqlite_master; and so there is the sqlite master pseudo table for the test.db database. So if I want to, I can say select * from customer;, and I get the customer table, and .quit.
I can also do this from the command line without actually going into the utility interactive mode. I can say sqlite3 test.db and then put in quotes "select * from customer;" and I can get it right there as well. One more thing that I can do that's useful with this, if I want to create a new database, what I have here is I have these little SQL scripts, if I bring one of these up in my editor, you can see here's test sqlite3.
You see this is the actual script that created the database. And if I want to recreate the database, I can say sqlite3, and give it a new name, so testworking.db and redirect from, which is the left angle bracket and then the name of the file, so test-sqlite3.sql, and now I have created a new database, test-working.db, and that one will work just like the other one.
Sqlite3test-working.db and "select * from customer;" and there we have the same data. So I am just going to delete that file, test-working.db, and that's gone now. In this movie, I have shown you how to use the SQLite 3 command line utility on both the PC and the Mac, so that you're able to work with your databases from the command line, and even recreate the databases from the raw SQL, should you need to do that.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 90290 Viewers
80 Video lessons · 137809 Viewers
59 Video lessons · 56534 Viewers
52 Video lessons · 70162 Viewers
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.