So now we're going to actually play with a little bit of Structured Query Language, SQL. Now, at this URL you can grab the instructions that I'm using so you can cut and paste it in. Now, the key thing that you've got to do is you've got to find your way to a shell. If you're using something like PythonAnywhere, you go ahead and open a command line. Now if we're using PythonAnywhere, most of what we're doing is running Django applications, but you can really just use PythonAnywhere as a Unix platform. And so get to a dollar sign prompt. These commands can run on a Mac, I don't know if you can get them working on Windows, but any Linux system, but you can also run them on PythonAnywhere. So there's a command to Linux called sqlite3, and then you give it a filename. So SQLite, as I mentioned, is a very simple database because it does all of its work in one file. It's not a slouch as a database in terms of its performance and scalability. What it's not good at is handling hundreds of people hitting it at the same time and having the database software resolve all those simultaneous accesses. But if you type sqlite, you end up in a command line where you get this prompt sqlite arrow. And there's commands you can type, like dot tables says hey, in this database, which is in this case zip.sqlite3, in this database, what tables exist? And it says there are no tables. And then you create some tables. Now, this is our first SQL statement. And so you'll learn something about SQL is that it looks a little different than other programming languages. And so you say CREATE TABLE. That's one statement. You can create things other than tables. And then you have a table name. And then you have some parentheses, right? And then you have a series of statements about the table that you're working with. And so there's this little language, and there's the name of a column. So these are three columns that we're putting in, id, name, and email. And we have a type. This is an integer. VARCHAR means variable length string field. NOT NULL means it's required. PRIMARY KEY means that it's an auto-increment, meaning it's going to be supplied by the database. It gives us like a handle for every row. And then a VARCHAR 128 says it's up to 128 characters. Now, a key to this is that we're making sort of a contract here. And so when we say you've got to do this with 128 characters, you can't put 129 in. You can put up to 128. And if you put 129, your database query blows up, and you're like, "Well, that's not very nice." And the answer is, well, you told it 128 so it could optimize how it's going to store the data on disk. If you've made this 1,000, it might store it in a different way than if you store 128. And so the key is, you can make any contract you want, but you've got to live by the contract that you make. And so it's not that the database hates you, it's you said I'm going to do 128, and when you do 129, it says you're violating our contract, and so it's up to you. So this whole CREATE TABLE ending in a semicolon, that is a single SQL statement. And so we can type it in. We can type it in multiple lines, and you can kind of see how SQLite is using this dot dot dot thing to tell you that it's in a continuation. And then finally, you finish it all and you put a semicolon at the end, and then it does it. And then we can ask using dot tables, we can say, "Is there a table?" And yes, it says, "Yes, it's Users." And then there's a command, an SQLite command called dot schema and that says, "Tell me the schema of Users." Now, that's in case you hadn't just typed in the CREATE statement, it tells you what CREATE statement it would be. And so part of what the database stores in this file sqlite is not only the data for this table called Users that you just created, but it also stores the schema, and so you can say, "Hey, what schema is it?" So if I sent you one of these files, like you might upload to my autograder, for example, I can inquire into that file using SQLite and say what schema? What tables are here? And I can start looking at the table. As a matter of fact, that's how I grade these assignments inside of the computer. And so this is just sort of going through some, there's a lot of SQL, but the basics is actually surprisingly easy to understand. And so INSERT INTO, one of the things that SQL does is it tends to use more than one word. I've met people who have used SQL in their jobs, and they just like type it, and they don't realize how amazing what they're doing is. They're like, "Well, I just insert, or I delete some stuff, or I select it to read it." So it was designed to be easy because if we go back to the basics of SQL, it was like, "Look, there's a lot of complexity down there. We want to hide that complexity. We want to hand something easy to users." And I write a lot of very complex SQL, and it's a very sophisticated skill. But simple SQL is something I think literally everybody should know how to do. So INSERT INTO, then you have a table name, and then you have a parenthesized list of the columns. Now, these are the columns that you named in your CREATE statement, and then VALUES is a keyword, and then there's a one-to-one correspondence between the values that you're going to put in the columns. And these happen to be strings, so we have little quotes around them. They could be integers, they could be dates, they could be all kinds of things, floating point numbers, etc. And then this has to meet the schema. You can't go putting a number into a string field, or a string into a number field, so that would blow up. You can make all kinds of errors. You can either have syntax errors, like you forget the word INTO or something, or you can have errors that are bad, that your data is bad, right? So that's the INSERT statement, and that adds a row to the end of a table. The DELETE statement says DELETE FROM, and then a table name, and then a WHERE clause. We're going to see WHERE clauses a lot in SQL. And the key thing is that I sort of mentioned in the beginning that there's kind of no loops in SQL, and that's one of the reasons that we sort of love SQL is you don't have to write loops. And that's because there's kind of an implied loop at the beginning of these commands. So when you say DELETE FROM Users, that means delete all rows from Users, right? Delete everything from Users. So if you don't put this WHERE clause on, it actually deletes everything in there because it's like delete all the rows from Users. You can kind of phrase it that way. Delete all rows from Users. Now, the all rows part isn't really there, but that just means that it's really important to have a WHERE clause so that you're not actually deleting all the rows. It's saying delete all the rows from Users where the email is equal to ted@umich.edu. And so what that is is there's an implied loop of all rows on this DELETE statement, and the WHERE clause reduces that down to actually one or a couple of rows depending on those rows where this is like an if statement. So this whole thing is like a loop with an if inside of it, that's how it works. And this is why a lot of people like it, is because you don't have to write loops. Because these things sort of like are loops, they have loops built in. But this would delete one or a couple of rows, depending on whether we had more than one row in this where the email was ted@umich.edu. UPDATE is really kind of cool. You can really zero in. You can zero into rows and columns. And again, UPDATE Users, which is the name of the table, SET, and then name equals, which is the field name and new value. And so you can do all the columns, you can do one of the columns, you can really zone in on this. And then there's a WHERE clause, because if you don't put a WHERE clause on the update, it implies that this is for all rows. You don't really want to set the name to be Charles for all the rows, you just want to set the name to be Charles where this is true. Because, again, you could think of this as like update all rows in Users SET WHERE. And so you've just always got to be thinking that there it's in. So the reading is done with SELECT. I wish that it would be create, read, update, delete. But create is insert, read is select, update is update, and delete is delete. SELECT is the way that we read stuff out. And so SELECT basically takes a list of columns, like SELECT name, comma, email, or SELECT star, and FROM, and then a table name. So this SELECT star FROM Users is quite the, almost always the first thing we type. SELECT star FROM Users, SELECT star FROM books, SELECT star FROM whatever. You can also put a WHERE clause on a SELECT because this again has this implied all rows kind of a thing going on. And so SELECT all rows FROM Users WHERE email equals csev@umich.edu. And that reads much better when you kind of add that all rows logic to it. Databases are good at searching. We can do things like add indexes to the databases that make searching and ordering even better. So we might say that email, please, while you're maintaining this data, in addition to maintaining the data, maintain an index that has a sense of the order of these records, so if I want to sort on ascending or descending, then I can get that, and I can get that to be efficient. ORDER BY is just a thing you throw on the end. You can add a WHERE clause here as well, and you can have ascending and descending sorts, and it works great. So this is sort of the first 60 percent of any SQL is the basic insert, delete, update, and read. So it's not hard. And the beauty is this essence of SQL, the middle part of SQL, is portable across all the databases that I just described, because every one of those databases that's a modern database was invented after the invention of SQL, and so life is pretty good. Now, there are differences because when they first built SQL in like, I think, '87. I don't know, I forgot the time. But when they first built it, they did not realize how powerful they were going to be. So there's a bunch of features that got added by the vendors that were extensions, and some of them are sort of standardized. There's another round of standardization. But then there's a whole bunch of features that are a little different. But because of competition, they have conceptually the same ideas, like a limit clause that limits the number of rows or starts after a certain number of rows, and it's a little different from one database to the other. But this is just a summary. You should just know this forever and ever and ever. And then as you run into a different database, then you'll learn different features for that. A tool that you might want to install on your laptop if you want to start playing with these SQLite files and not just using the command line is called SQLite browser. You can download it. It runs on lots of different systems. You can then open these dot sqlite3 files and you can do stuff to it, including execute the SQL commands right in there. And so a lot of people find this a more interesting and easy way to interact with these files. And so that kind of sums up our really quick introduction to single-table SQL. Up next, we'll talk a little bit about multiple-table SQL and relationships. And then we'll also talk about how we connect this all to Django through an object-relational mapper. So see you soon. [MUSIC]
