9.3.3 Creating tablesThis is NOT the latest copy of this book; click here for the latest version.
The very first skills to learn are how to create tables in a database. Once you have your phpdb database created and you have typed "USE phpdb", you are all set to start entering queries to create tables. First, though, type "show tables;" - this is the command to make MySQL output a list of all tables in the currently selected database. As you can see from the screenshot below, there aren't any tables yet because we just created the database!
So, it is time to get stuck in with our first piece of SQL - how to create a table. Here is the SQL code:
CREATE TABLE usertable (ID INT, FirstName CHAR(255), LastName CHAR(255), Age INT);
The above SQL instructs your DBMS to create a table which you will reference as usertable, and then defines the four fields of data you want to store in that table. If you look back over the data types from earlier, you will recognise that the first field, "ID", is an integer, integer, the second and third fields both hold fixed-length character strings of 255 characters, and the last field is another integer.
Author's Note: Very often people format their SQL code so that each individual part of it is placed on a new line and indented to match the previous line. This can help readability - choose what works for you.
Once that query is executed, MySQL will attempt to create the usertable table for you, with those four fields. If it exists already, or if you somehow do not have permission to create such a table, it will report an error. Here is how it looks the first time around:
Notice that MySQL outputs "Query OK" first, which tells us that the query executed just fine - as expected. However, if I try running the same query again, the following happens:
This time you can see MySQL has flagged an error up because usertable already exists and therefore cannot be created again.
Once a table is created, you can run "DESC usertable" to have MySQL print out a small report about the fields in the table. It will show the following information:
-
Field - the name of the field
-
Type - the data type
-
Null - does this field allow null information?
-
Key - is this field a key?
-
Default - what is the default value of this field?
-
Extra - stores comments about each field
If you are using the new MySQL 4.1, you can also use CREATE TABLE foo LIKE bar, where "bar" is an existing table - it will create foo as a copy of bar's table structure.
|
Want to see this stuff in print? PHP in a Nutshell takes the core topics covered here, adds in thousands of edits from the editorial team and myself, and combines them to make an unbeatable reference for PHP programmers at all levels.
My latest book has hundreds more tips on how to use PHP, Apache, and MySQL, plus Perl, Python, shell scripts, performance tuning, and more!
|
