MySQL Basics

Background information

MySQL is a relational database system capable of storing multiple databases. Each database contains tables that store data. MySQL is a quick, robust, and cost effective database solution. Queries are performed on the database to insert, update, or delete data. Like PHP and many other languages MySQL queries are delimited with a semicolon.

Creating a Database

The CREATE statement is used to create a database. The CREATE DATABASE <tablename>; syntax is very simple.

CREATE DATABASE database1;

Creates a database named database1.

Creating tables

Creating tables is a little bit more complicated and requires some planning. There are many different data types to format the storage of data. There are storage types for text, numeric values, date and time, etc. Also there are optional settings you can apply to fields such as default settings, allow NULL fields and auto_increment value are some common ones.

CREATE TABLE table1 (
   id INT NOT NULL auto_increment,
   firstname varchar(20) NOT NULL,
   lastname varchar(25) NOT NULL,
   address1 varchar(40) NOT NULL,
   address2 varchar(40),
   city varchar(20) NOT NULL,
   state varchar(2) NOT NULL,
   zipcode varchar(10) NOT NULL,
   email text NOT NULL,
   primary key (id)
);

Here we created a table named table1 with nine fields. The id field is an integer. There are two modifiers to the id, NOT NULL and auto_increment. NOT NULL means that the value of that field cannot be left empty, while auto_increment means that every row the value of the field will be one higher. Firstname, lastname, address1, address2, city, state, and zipcode are all of varchar type. The numbers in parentheses represent the length of the field. The email field is text type. The primary key is a field that is unique to each row. An ID field is the perfect column to set as a primary key because no two columns should have the same ID.

CREATE TABLE table2 (
   id INT NOT NULL auto_increment,
   login varchar(20) NOT NULL,
   password text NOT NULL,
   lastlogin datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
   primary key (id)
);

In this CREATE TABLE statement we created table2. This table is similar to the first but we introduce the datetime field type and the DEFAULT statement. The datetime field type stores the time and date in YYYY-MM-DD HH:MM:SS format. By using the DEFAULT statement you set the default value if one isn't entered during an insert statement.

1 2 3 >> Inserting data into MySQL