SQl: Create table with variables

Let us create another more interesting table artiste to illustrate some common query methods in the next few posts.

We use create table command as in the sql code below.

CREATE TABLE artiste 
     (
 id int auto_increment primary key, 
 artiste varchar(30), 
 album varchar(50),
 year int(10)  
 );

INSERT INTO artiste
(artiste, album, year)
VALUES
('Rod Stewart', 'Maggie May', 1973),
('New Order', 'Substance', 1987),
('Nirvana', 'Smell Like Teen Spirit', 1992);

In the table artiste, we define a table with 4 columns – id, artiste, album and year.

Variables in the table

VariableDescription
idpositive integer, auto increment
artistevariable-length string of up to 30 characters
albumvariable-length string of up to 50 characters
yearinteger of up to 10 characters

To display the whole table, we can use a very common query statement:

select * from artiste

The * will select all columns, thus displaying the whole table.

Auto_Increment

We set the column id to auto_increment, with default starting value of 1.

When you insert a NULL (recommended) (or 0, or a missing value), into an auto_increment column, the maximum value of that column plus 1 would be inserted. You can also insert a valid value to an auto_increment column, bypassing the auto-increment.

Series Navigation<< SQL: The first example
SQL: CRUD >>