subscribe: Posts | Comments

How to create tables from SQL

In general, most databases have powerful editors that allow the bases quickly and easily create any type of table with any type of format.

However, once the database is hosted on the server, it can happen that we enter a new table with either temporary (to manage a shopping cart for example) or permanent needs of our application .

In these cases, we can, from a SQL statement, create the table with the format you want which we can save more than a headache.

Such judgments are especially useful for databases such as MySQL, which work directly with SQL commands and not by editors.

To create a table must specify various information: The name you want to assign the field names and their characteristics. Moreover, it may be necessary to specify which of these areas will be and what type of index will be.

The syntax of creation can vary slightly from one database to another as the field types are not widely accepted standard.

Below we outline the syntax of this sentence and we propose a series of practical examples:


Create Table tbl_name
nombre_campo_1 tipo_1
nombre_campo_2 tipo_2
nombre_campo_n tipo_n
Key (campo_x ,…)

Let us now as an example the creation of a table in order that we have used previous chapters:

Create Table Orders
id_cliente INT (4) NOT NULL,
id_articulo INT (4) NOT NULL,
date DATE,
quantity INT (4),
INT total (4), KEY (id_pedido, id_cliente, id_articulo)

In this case id believe the areas which are of type integer with a length specified by the number in parentheses. Id_pedido to require that the field is increased automatically (AUTO_INCREMENT) from one unit to each introduction of a new record for in this way to automate their creation. Moreover, to avoid an error message, it is necessary to require the fields to be defined as rates can not be null (NOT NULL).

The date field is stored with date format (DATE) to allow proper operation from the functions provided for that purpose.

Finally, we define the indicators listed in brackets preceded by the word KEY or INDEX.

Similarly we could build the table of items with a sentence like this:

Create Table ARTICLES
title VARCHAR (50),
author VARCHAR (25),
publisher VARCHAR (25),
price REAL,
KEY (id_articulo)

In this case it may be alphanumeric fields are introduced in the same way as numbers. We again recall that tables have common fields is of vital importance to define these fields in the same way for the smooth operation of the database.