SQL Tutorial – 03 – Create
The various SQL statements can be grouped into several subset languages. The first of which will be looked at is the Data Definition Language (DDL), which allows you to add, alter, and delete databases, tables, and indexes.
Creating databases
First, create a database called “mydatabase”.
CREATE DATABASE mydatabase
When you execute a statement such as this the database system won’t return any confirmation unless there was an error, for example if the database already existed. To see that a new database has been created you can type SHOW DATABASES.
SHOW DATABASES
Next, you can select the database with the USE statement. This will make it the default database for the following SQL statements.
USE mydatabaseCreating tables
With the database selected, create a table in it called “mytable” with three different columns: id, name, and history. Each column name is followed by the data type of that column and its length in parenthesis. For example, this id integer below can hold 3 digits for a maximum value of 999.
CREATE TABLE mytable(id int(3), name varchar(20), history text(50))
Datatypes
The exact datatypes available and their names varies for different database systems, but there are 4 general types – numbers, strings, dates, and binary objects. They also come in a variety of subtypes depending on how much data you need to store. For example, a tinyint in MySQL is 1 byte large and a bigint can store up to 8 bytes.
| Data type | Description |
|---|---|
| bigint(size) int(size) smallint(size) tinyint(size) | Holds integers Size is the maximum number of digits |
| real(size,dec) double(size,dec) float(size,dec) | Holds numbers with fractions (floating-point) Size is the number of digits before the decimal sign Dec is the number of decimals |
| decimal(size,dec) numeric(size,dec) | Holds numbers with fractions (fixed-point) |
| char(size) varchar(size) | Holds a fixed length string Holds a variable length string |
| date time | Holds date Holds time |
| binary(size) varbinary(size) blob(size) text(size) | Holds binary data (ex. pictures) |
The tables in the current database can be viewed by typing SHOW TABLES.
SHOW TABLES
To see the columns in a table you can type DESCRIBE followed by the table name.
DESCRIBE mytableIf you hadn’t selected the database with the USE keyword, you would have to explicitly specify what database to use, like this:
DESCRIBE mydatabase.mytable
With the description of the table you can see the column name, data type, and max length of each column. You can also see some additional parameters that could have been defined. For example, adding a DEFAULT value or disallowing null values with the NOT NULL modifier. A null value indicates that a field’s value is undefined and is allowed by default.
CREATE TABLE mytable(id int(3), name varchar(20) DEFAULT 'John', history text(50) NOT NULL)
Primary key
You could have made the integer column numbered automatically with the AUTO_INCREMENT attribute. This modifier requires the column to be the primary key of the table. A table can only have one primary key and it must have unique values for each row. Keys are used to uniquely identify the records in a table so that tables can relate to each other. The key field also indicates that the column is indexed, which means it will locate rows more quickly and efficiently than other columns. Indexed columns require more disk space and processing time to update, so they should only be used on columns that are searched often.
CREATE TABLE mytable(id int(3) AUTO_INCREMENT PRIMARY KEY, name varchar(20), history text(50))
Creating indexes
The CREATE INDEX statement can be used to add keys after the table has been created. For example, to create a simple key named “myindex” on the “name” column, you send the following command:
CREATE INDEX myindex ON mytable (name)
When you describe the table the index shows up as MUL, meaning it allows multiple rows to have the same value. You could have added the UNIQUE keyword to not allow duplicate values.
CREATE UNIQUE INDEX myindex ON mytable (name)
