MySQL: creating a new table

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));

 

Tagged ,

Leave a Reply

Your email address will not be published. Required fields are marked *