How to add tables to a database?

You can add tables to any database, whether it’s a new, empty database that you just created or an existing database that already has tables and data in it. You use the CREATE query to add tables to a database. Because a table is created in a database, you must indicate the database name where you want the table created. That is, when using the form , you must type a database name in the top field. If you don’t, you see the error message No Database Selected.
The query to add a table begins with

CREATE TABLE tablename

Next comes a list of column names with definitions. The information for each column is separated from the information for the next column by a comma. The entire list is enclosed in parentheses. Each column name is followed by its data type and any other definitions required. Here are some definitions that you can use:

• NOT NULL: This column must have a value; it can’t be empty.
  
• DEFAULT value: This value is stored in the column when the row is created if no other value is given for this column.
• AUTO_INCREMENT: You use this definition to create a sequence number. As each row is added, the value of this column increases by one integer from the last row entered. You can override the auto number by assigning a specific value to the column.
• UNSIGNED: You use this definition to indicate that the values for this numeric field will never be negative numbers.

The last item in a CREATE TABLE query indicates which column or combination of columns is the unique identifier for the row — the primary key.
Each row of a table must have a field or a combination of fields that is different for each row. No two rows can have the same primary key. If you attempt to add a row with the same primary key as a row already in the table, you get an error message, and the row is not added. The database design identifies the primary key. You specify the primary key by using the following format:

CREATE TABLE Member (
loginName VARCHAR(20) NOT NULL,
createDate DATE NOT NULL),
PRIMARY KEY(columnname) )

The columnname is enclosed in parentheses. If you’re using a combination of columns as the primary key, include all the column names, separated by commas. For instance, you would designate the primary key for the Login table in the MemberDirectory database by using this in the CREATE query:
PRIMARY KEY (loginName,loginTime)
Listing below shows the CREATE TABLE query used to create the Member table of the MemberDirectory database. You could enter this query on a single line if you wanted to. MySQL doesn’t care how many lines you use. However, the format shown in Listing below makes it easier to read. This human-friendly format also helps you spot typos.

CREATE TABLE Member (
loginName VARCHAR(20) NOT NULL,
createDate DATE NOT NULL,
password CHAR(255) NOT NULL,
lastName VARCHAR(50),
firstName VARCHAR(40),
street VARCHAR(50),
city VARCHAR(50),
state CHAR(2),
zip CHAR(10),
email VARCHAR(50),
phone CHAR(15),
fax CHAR(15),
PRIMARY KEY(loginName) )

Notice that the list of column names in Listing above is enclosed in parentheses (one on the first line and one on the last line), and a comma follows each column definition.
Remember not to use any MySQL reserved words for column names. If you do, MySQL gives you an error message that looks
like this:
You have an error in your SQL syntax near ‘order var(20))’ at line 1
Note that this message shows the column definition that it didn’t like and the line where it found the offending definition. However, the message doesn’t tell you much about what the problem is. The error in your SQL syntax that it refers to is the use of the MySQL reserved word order as a column name. After a table has been created, you can query to see it, review its structure, or remove it.

• To see the tables that have been added to a database, use this query: SHOW TABLES
• To see the structure of a table, use this query: DESCRIBE tablename
• To remove any table, use this query: DROP TABLE tablename

Use DROP carefully because it is irreversible. After a table is dropped, it is gone forever. And any data that was in it is gone as well.