Join Bill Weinman for an in-depth discussion in this video Creating tables, part of SQL Essential Training (2014).
Tables are created using the create table statement. Here I'm using the in memory database. So I've selected that there. And I'm going to create a simple table. Let's asy, create table and I will name it, test. And I'm going to put up some parentheses. And I'm going to close my parentheses and put in a semi-colon. And then inside I'm going to specify what the table looks like. This will have an integer called a. And, these get separated with a comma.
And a text column named b. And because the comma is a separator. And this is actually a strict requirement in the Create Table syntax, I do not put a comma after the last column in the table. Create table statements are normally formatted such that the column declarations are each on a line by themselves. So here we have one column. And here we have another column. This makes it easier to read, and it does not affect the functionality. You could easily put all of this on one line and it would also work.
It just wouldn't be as readable. The part in between the parentheses is called the database schema. The syntax is pretty simple. Each column definition starts with the column name and is followed by the column type declaration. And, each of these columns are separated with a comma. Different data types are supported by different database engines. So it's important that you understand the types that are available on your system. And how you're going to use the data. So let's go ahead and put some data into this table, and see how it works.
So I'm going to use some insert statements. And this will be covered later in this chapter. And I'll just insert three rows, and I'll give them each, different values. And then I'll put in a select statement. So that we can see what comes out. And I'll press the Go button. And because this is the in memory database, it creates the table. And it inserts the values and it runs the select. And then the database is completely destroyed every time we run this. So here we have our table and it has two columns, a and b.
And the values in column a are 1, 2, and 3. And the values in column b are a, b, and c. So the Create Table statement is very simple. It's used to create a table with a particular schema. You'll use this when designing and declaring your database tables.
- 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.