We have covered most of the important bits of working with existing SQL databases. This exploration will look at creating your own SQL tables. You would do this when you have your own data set you need to store and you are creating a place for it. You almost never want to add or delete tables when you are in the middle of an application. If you are needing to change your table structure in the middle of a program you are going down a path of woe and grief.
The basic syntax for creating a table is as follows
CREATE TABLE table_name (
var_name var_type [constraints],
var2_name var2_type [constraints],
...
varN_name varN_type [consraints],
PRIMARY KEY(var_name)
)Each variable name will turn into a column of the specified type. The constraints you will typically be working with are NOT NULL and AUTO_INCREMENT. NOT NULL means that each row must have a value other than NULL. For example, if you wanted to make sure every employee in a database had a salary you could mark the salary as NOT NULL then the database would throw an error if you tried to add an employee without a salary.
The other place NOT NULL gets used is with relationships. If you mark a foreign key reference as NOT NULL then it means that entity must be related to something. We could, for exmaple, force people to have a homeworld by marking the homeworld column as NOT NULL.
AUTO_INCREMENT is used in the context of integers used as primary keys. When in doubt add an auto incrementing integer as the primary key of a table.
The PRIMARY KEY definition at the ends lets the database know which field will be used as the primary key.
CREATE TABLE `bsg_people` (
`id` int NOT NULL AUTO_INCREMENT,
`fname` varchar(255) NOT NULL,
`lname` varchar(255),
`homeworld` int,
`age` int,
PRIMARY KEY (`id`)
)Above is the syntax for creating the bsg_people table. Notice in this case I included accent graves around all table and field names. That is because this was auto generated from the DBMS. They are not required.
The above definition is actually missing one line and that is the foreign key reference to homeworld. Notice it isn’t specified on the homeworld attribute itself. Instead we specify it on its own line in the list of keys at the end. The syntax first specifies the column in our table, then the name of the table we are referencing and finally the name of the column in that table we are referencing.
CREATE TABLE `bsg_people` (
`id` int NOT NULL AUTO_INCREMENT,
`fname` varchar(255) NOT NULL,
`lname` varchar(255),
`homeworld` int,
`age` int,
PRIMARY KEY (`id`),
FOREIGN KEY (`homeworld`) REFERENCES `bsg_planets` (`id`)
)Table creation can get a lot more complex than this, but it should be all you need for general day to day use. The one reference you will probably want to browse through is the MySQL types. For strings you usually want to use VARCHAR(255) which will make a variable length string up to 255 characters and use only 1 byte to keep track of size. But you can go up to 65,535 characters. For longer strings you will want to use TEXT.
Dates are a pain. We are not going to go into them here, but there are several types built in and you should use them if you want to do date related work.
INT is your basic integer. It comes in different flavors of TINYINT, SMALLINT, and BIGINT. They range from 1 byte up to 8 bytes in size.
FLOAT and DOUBLE are used for floating point numbers, so that is going to be numbers with an unknown number of digits past the decimal point.
DECIMAL is going to be what you used to store exact decimal numbers. The syntax for it is DECIMAL(M,N) where M is the number of digits in the number and N is the number of digits that come after the decimal point. So DECIMAL(8,2) could be used to store numbers from -999,999.99 to 999,999.99.