The very nice thing about MySQL is that… it’s very similar to fluent English. So if we want to create a table we must decide in advance the list of fields we need.
For each field we need to specify the type (will we store a number? a date? a name?). A list of fields types is available here.
Remember that MySQL is case-insensitive, while table names and field names are case-sensitive. It’s common to type SQL commands in upper case though, and to choose lowercase names. The “create table” command requires a name and a list of fields.
CREATE TABLE reagents ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100), supplier INT, amount INT, arrived DATE);
A single command ends with “;” in MySQL shell (this is not always true for SQL languages), so that we can split a single command across multiple lines. We choosed a list of fields and some of them store a number, another a date, the name is a string (varchar).
Each table has to have a “Primary Key“, a unique identifier for the record. A common practice is to use an integer with the “auto_increment” keyword that means: every time you create a record, the “id” field will be incremented.
If we later want to add a new field there’s a simple command to do this:
UPDATE reagents ADD (casnumber VARCHAR(20));