When it comes to a relational database, we can create three things: a database, a table, and a specific entry. The commands for these operations are all quite straightforward – the real complexity comes when you learn about the best settings for your structure.
To clarify this statement, say you have a database containing all user accounts. Creating the database is easy (make up a name and run a single command), as well as inserting a row (provide data and run a single command). But, the structure of the table must be constrained – for example, it should not be possible that a user has no username, or no password, or that his email address is a number instead of text.
Creating a Database
Let’s start very, very simple. To create a new database, use
CREATE DATABASE someDatabaseName;
-- Creates a database called users, with nothing in it (yet) CREATE DATABASE users;
Creating a Table
To create a table, we must first navigate to the correct database. (If we don’t, SQL doesn’t know in which database to place the table.)
- If you have an interface to work with, you can simply click on the database and perform the query there.
- If you’re working with a programming language (such as PHP), you should be able to connect to the right database with some function, and all will be fine.
- Otherwise, you can select the database with
Now that the right database is selected, we can create a table with
CREATE TABLE someTableName (column_name_1 type attributes, column_name_2 type attributes, | column_name_n type attributes);
The type parameter specifies the type of data the column will hold, and is required. Examples of data types are
The attributes parameter is optional, and can set properties for a certain column. Examples of attributes are
NOT NULL (it’s never allowed to have an empty value) and
AUTO INCREMENT (the value of this column is automatically incremented with 1 every time a new entry is added).
USE users; /* Creates a table to hold usernames and corresponding email address INT(11) just tells it to hold an integer of at most 11 digits VARCHAR(255) means the column should hold strings of at most 255 characters */ CREATE TABLE accounts (id INT(11) PRIMARY KEY, username VARCHAR(255), email VARCHAR(255) );
|No entries yet.|
As promised, you’ll learn all about structure, constraints and data types in later chapters.
Creating a new Entry
When we create a new entry, we usually call it inserting an entry. It’s a more intuitive way of looking at it, as we’re not really creating anything, we’re just inserting data in an already existing table. Not surprisingly, the command for inserting an entry is
INSERT INTO someTableName (column_1, …, column_n) VALUES (value_1, …, value_n);
As you can see, you don’t necessarily have to insert values for all columns. If you leave out some columns, they will be set to the default value you specified when you created the table structure. This saves time and effort from your side.
Additionally, if you don’t specify any columns at all, SQL is going to assume you want to insert data into all columns.
INSERT INTO accounts (id, username, email) VALUES (1,"Panda","[email protected]"); INSERT INTO accounts VALUES (2,"Ginty",""); INSERT INTO accounts VALUES ("Juan","[email protected]"); INSERT INTO accounts VALUES ("Gandalf");