The various SQL statements can be grouped into several subset languages. The first of which we will look at is the Data Definition Language, which allows us to add, alter and delete databases, tables and indexes.
First, we will create a database called mydatabase. When we execute a statement such as this, the database system won't return any confirmation unless there was an error, such as the database already existing. To see that a new database has been created we can type Show Databases.
Next, we should select the database with the Use statement to make it a default database for the following SQL statements. Now that we have a database, let's create a table in it. Go mytable with three different columns, id, name and history.
Each column name is followed by the datatype of that column and it's length in parenthesis. For example, this id integer can hold three digits for a maximum value of 999. The exact datatypes available and their names varies for different database systems. But there are four general types numbers, strings, dates, and binary objects. They also come in a variety of subtypes depending on how much data do we need to store.
For example, the tinyint in my scale is one byte large and the bigint can store up to 8 bytes. To see the tables in the current database, we can type Show Tables and to see the columns in a table we can type Describe followed by the table name.
If we hadn't selected the database with the Use keyword, we would have to specify what database to use, like this. With the description of the table, we can see the column name, data type and max length of each column. We can also see some additional parameters that we could have defined, such as adding a default value or disallowing Null values by adding Not Null as a modifier. And Null value indicates that the field's value is undefined and is allowed by default.
We could also have made the Integer column, numbered automatically with the Auto_Increment attributes. This modifier also 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 that we will locate rows more quickly and efficiently than other columns. Index columns require more disk space and processing time to update. So they should only be used in columns that are searched often. The Create Index statement can also be used to add the keys off to the table that has been created.
For example, to create the simple index named myindex on the name column, we write it like this. When we describe the table it shows up as MUL, meaning it allows multiple rows to have the same value. We could also have added the Unique keyword to not allow duplicate values.
Transcription by:
Scribe4you Transcription Services