Join Bill Weinman for an in-depth discussion in this video Creating a table, part of MySQL Essential Training.
- View Offline
Just like a database contains tables, a table contains the rows and columns for a given set of data. The table definition contains all of the details of a dataset. Because of this, the CREATE TABLE statement has a lot more options than the CREATE DATABASE statement. We'll be working with the scratch database here in this lesson. And I'm going to start by copying and pasting the statement out of the exercise files here in the Chapter 03 Exercise Files. Under 02 Create Table, I'm just going to grab this whole Create Table statement all the way down to the semicolon.
And I'll paste that into the SQL box here in SID. The Create Table statement is pretty simple, it has a lot of options; but it's basically just CREATE TABLE followed by the name of the table and then the table definition inside the parenthesis there. So inside the parenthesis here is the definition of the table. And each of these lines is the definition of a particular column in the table, of course because SQL doesn't care about white space, these don't need to be on separate lines, they can all just be listed there on one line and it would work exactly the same.
I spread them out to different lines so that it's easier to read. So we'll get into all the column types later in this course but briefly this is how it works. A relational database table has a set of columns with particular characteristics. The definition of these columns is the part within the parentheses in the create table statement. For example, the id column is of an INTEGER type and that's followed by a comma. The name column is of type VARCHAR(255). Which means that it is a 255 character long string.
And likewise for address and city, state and zip are fixized where as the VARCHAR types can be larger or smaller. And each of these column definitions is separated by a comma, with no comma after the last one. And that's actually important. If you put a comma after that last one, you'll get an error. So, when I press Go here, that table will be created with those definitions. So, we can take a look at that by using the DESCRIBE statement.
This is a MySQL specific statement, this is not part of the standard. If I say DESCRIBE test. It will give me a definition of the test table, give me all of the column definitions. We can see more detail about the table with the SHOW TABLE STATUS statement. If I say, SHOW TABLE STATUS, semicolon. This shows that all the tables in the. Database, if you want to see just a particular one you can say SHOW TABLE STATUS LIKE and the name of the table and it will just show you that one.
So this tells us a few things about the table, it tells us the name of the table, the data base engine that's being used, the InnoDB. Database engine is the default form modern versions of MySQL. Actually versions later than 5.5 so, this is the 5.6 version and so, it defaults to InnoDB. Other interesting information, you have the create time of the table, and the collation, you notice it says latin1_swedish_ci. CI stands for case insensitive.
Which means that lowercase and uppercase letters are treated the same in sorting and searching. And latin1, it's closely related to the old ASCII standard. It's not the modern UTF standard. I'll show you how to change that a little later. And you notice that the language defaults to Swedish, and that's true in most MySQL implementations. I'm not sure if it's a compile option, or something. But it seems to be the default for MySQL. Probably has to do with how and where it was created. So, that's the show table status.
You can also see the create table statement and this is not the actual create table statement that you entered when you created the table, but it's rather. A create table statement that MySQL creates that will create a table exactly like this. So if I say SHOW CREATE TABLE, and the name of the table, then it gives us this create table statement and you notice that it says int 11 rather than just integer because 11 is the default size for an integer.
And a few things like that. It says default null after each one. Then we'll get to some of these other options later on. You notice also that it says ENGINE equals InnoDB, and default character set equals latin-1. So if we go back to our original create table statement, and I just. Pressed Paste because it was still on my copy buffer. We can actually add these options after, we can say ENGINE equals InnoDB and default character set equals.
And actually I want to put utf8. Because that's a more modern character set, it has more capability with international characters with non-Latin characters. And before I execute this, of course, I need to drop the table. If I try to create a table where there already is a table, I'll get an error. So I can say before that. I can say DROP TABLE IF EXISTS test. And then I can execute this and now if I do this SHOW CREATE TABLE, you'll notice that the default character set is now utf8 and if I say SHOW TABLE STATUS.
You notice, our collation is now utf8_general-ci, which is a more reasonable default, actually. So, it's a good idea to always specify the database engine and the default character set. The database engine defaults to InnoDB for MySQL 5.5 and later. InnoDB versus MyISAM which is the original database engine for MySQL. MyISAM is not transactional, it's not very fast for large data sets.
InnoDB has been under development for a number of years, it's just recently become the default. It's fully asset compliant which means that it supports transactions can be committed and rolled back. It has better durability and consistency. Its got much better speed with larger datasets. It's definitely a superior engine for most purposes. So you're going to want to use InnoDB if you can. Once you no longer need a table you can delete it with the drop statement. DROP TABLE or if I don't know if the table exists or not, if the table doesn't exist, this will give us an error.
So we'll go ahead and drop the table, and then we can see the error if I try to drop it again, we get this Base table review not found, unknown table. I can say DROP TABLE IF EXISTS. Which is the common way to do this. And press Go. And now I don't get an error. Even when, the table is already dropped. So a table is created in MySQL using the create table statement. This is where you define your columns and specify any configuration options for your table.
And you can delete a table with DROP TABLE. Or DROP TABLE IF EXISTS.
- Writing queries
- Creating and updating databases and tables
- Using MySQL built-in functions
- Sorting and filtering data
- Updating tables with triggers
- Working with subselects and views
- Creating and using a stored function