Join Bill Weinman for an in-depth discussion in this video Inserting data, part of SQL Essential Training.
The database isn't just about queries. Reading data from the database is only one facet of database management. The four basic functions of any complete database system are: create, retrieve, update and delete. CRUD, often pronounced CRUD. In this lesson, I'll show you how to create new rows in a table using the insert statement. We're going to be using the test.db database here so I'll select that here in SID. And, we're going to use the customer table, so let's just take a look at that real quick.
And there's our customer table. It only has three rows in it and you see it has these columns, id, name, address, city, state, and zip. So, if we want to insert something into this table, I'm going to add this above the select statement so we can see our result right after we insert. So, I'm going to say, insert into customer. And then, in parenthesis, I'm going to put the names of the columns that I'm going to be inserting. Name, address, city, state, and zip.
And I'll give it some values. You can give it whatever values you like, I'm just typing these. And so, my insert statement looks like this, I use insert into. And the name of the table, which is customer. And then, in parenthesis, I list the names of the columns that I'm going to be specifying values for. And then I have the keyword values and then the values themselves. So Fred Flintstone will go in name. 123 Cobblestone Way will go in address. Bedrock will go in city. CA will go in state and 91234 will go in zip.
And then I have the select statement. So, after the insert, we'll see our results again displayed down here. And, I'll press Go. And you see, it says two queries performed. Because the first one was the insert and the second one was the select statement. It says, one row's affected because we inserted one row and four rows returned. So we got four rows back in the select statement, and there's our row that we added at the bottom. Fred Flintstone, 123, Cobblestone Way, Bedrock, California, 91234.
You notice that we didn't insert a value for id, but there is a value there. This column has a value that's automatically generated on insert, and we'll talk more about automatic values later in the course. Data in a relational database does not typically have a default order. The fact that these records appear to be in numerical order by id is not something you should rely upon. If your application requires that data records are presented in a particular order, you'll need to create that order with some combination of order by clauses, indexes or other means and we'll talk more about this later in the course.
Now, suppose you don't have complete data for a row, you may insert only some values, so I'm going to select this insert statement, I'm going to type a new one, insert into customer and I'm just going to provide name, city and state. And I'll give it values, and we'll put Jimi Hendrix in there this time. And when I press Go, you'll see we have this new row down here and it has null values for the address and the zip because I didn't provide them.
Null is a special value in SQL. It's different than a zero, and it's different than a zero-length string. Null is a lack of data. You can think of it as an indeterminate value or I don't know. For example, if somebody asks you how many books you've ever read, you may not know the answer. So you would say I don't know. That answer's not zero. It's not false. It's simply a piece of data that you do not have. It's missing information. If you want some columns to have default values, there's ways to do that in SQL and we'll talk abut that later in the course.
The insert into statement is used to insert rows into a table. In the next movie, I'll continue with the test database as we've modified it in this lesson. And I'll show you how to update data in the existing rows, using the update statement.
- Understanding SQL terminology and syntax
- Creating new tables and records
- Inserting and updating data
- Writing basic SQL queries
- Sorting and filtering
- Accessing related tables with JOIN
- Working with strings
- Finding the numeric type of a value
- Using aggregate functions and transactions
- Updating a table with triggers
- Creating views
Skill Level Beginner
Q: For Mac OS X: When I try to start the Apache Web Server from the XAMPP control panel, it doesn't start, and when I open "localhost" in my web browser, I see a white screen that says "It Works!" instead of the XAMPP page.
sudo apachectl stop
Q: I'm on a Mac, and I get an error in SID that says "attempt to write a read only database." How can I fix this?
A: This usually means that the database folder does not have sufficient permissions for writing by the web user. This can happen if you create the SQL folder new, rather than copying it from the Exercise Files. Here's how to fix this:
- Open a Finder window and Navigate to /Applications/XAMPP/htdocs/SQL
- Control-click on the SQL folder and select "Get Info" from the context menu.
- Under "Sharing and Permissions" (you may need to open the disclosure triangle), in the "everyone" row, select "Read & Write."Then you can close the Info window.
- Now repeat the process for the three *.db files inside the folder.