Creating Databases and Tables
The creation of a new database is relatively simple:
CREATE DATABASE <dbname>CREATE SCHEMA <dbname>
These two forms are equivalent to each other— is the name of the new database you want to create. As you can see, there is no mechanism for providing security or access control—this is, indeed, a shortcoming of SQL that is solved by each database system in its own way. The creation of a table is somewhat more complex, given that you need to declare its structure as well:
CREATE TABLE <tablename> ( <col1name> <col1type> [<col1attributes>], [... <colnname> <colntype> [<colnattributes>]])
As you can see, it is necessary to declare a column’s data type—as you probably have guessed, most database systems are very strict about data typing, unlike PHP. Here’s the declaration for a simple table that we’ll use throughout the remainder of this chapter:
CREATE TABLE book ( id INT NOT NULL PRIMARY KEY, isbn VARCHAR(13), title VARCHAR(255), author VARCHAR(255), publisher VARCHAR(255))
Here, we start by declaring a column of type INT that cannot contain NULL values and is the primary key of our table. The other columns are all VARCHARs of varying length (note how we are using 255 as the maximum allowable length; this is a “safe bet” and it is true in many, but not all, database systems).