Tables and DDL¶

In this section, you will learn about a subset of SQL DDL (Data Definition Language) . You will learn how to create, delete or modify existing structures.

Create¶

To create the basic building unit of databases - tables, the following command is used: CREATE TABLE table_name, wheretable_name is to be the actual name of the table being created. The table names in the basic version may include:

letters
numbers
$, _ characters
characters with codes: U+0080-U+FFFF
lowercase and uppercase letters, however, whether their interpretation will be: case sensitive orincase sensitive depends on the server and database configuration.

However, table names cannot:

consist of numbers only
consist of only SQL keywords (unless they are between single quotes `)
end with a space.

The following example shows the general syntax for the CREATE TABLE command:

CREATE TABLE table_name(
    column_name_1 column_type_1 [atributes],
    column_name_2 column_type_2 [attributes],
    column_name_3 column_type_3 [attributes]
    ...
);

Column names, just like table names, can contain lowercase and uppercase letters. Depending on the database server we use and the settings, these values can be case insensitive orcase sensitive.

The column types define the data type that can be stored in the column.

Example of creating a table:

CREATE TABLE Product(
    id INTEGER,
    Name VARCHAR(20),
    Producer VARCHAR(25)
);

Note that the attributes are optional when creating tables.

Tables can also be created by indicating a specific database to which they should belong. For this, the name of the table is preceded by the name of the database. Both names are separated by the . character, e.g.:

CREATE TABLE shop.Producer(
    id INTEGER,
    Name VARCHAR(20),
    Address VARCHAR(30)
);

Creating a nonexistent table¶

If you try to create a table that is already in the database, the instruction will return an error (with the content, e.g. Table 'Producer' already exists). To create a table in its absence, you can use the following structure: IF NOT EXISTS, e.g .:

CREATE TABLE IF NOT EXISTS table_name(
    column_name_1 column_type_1 [attributes],
    column_name_2 column_type_2 [attributes]
    ...
);

Tworzenie tabeli tymczasowej¶

With the CREATE TEMPORARY TABLE command, you can create a temporary table that will be removed from the system after the connection is closed. The table is also linked to a specific connection and, therefore, two different users can create exactly the same temporary table. Besides adding the keyword TEMPORARY, the syntax is identical to creating a regular table.

CREATE TEMPORARY TABLE table_name(
    column_name_1 column_type_1 [attributes],
    column_name_2 column_type_2 [attributes]
    ...
);

Create one table from another¶

Using the instruction:

CREATE TABLE Producer2 LIKE Producer

you can create a new table that will reflect the structure of the base table. Such a table does not contain the data of the original table.

Attributes of columns¶

Each of the columns declared in the table may have additional attributes. These include:

PRIMARY KEY
NOT NULL
AUTO_INCREMENT
DEFAULT
INDEX
UNIQUE.

PRIMARY KEY¶

The PRIMARY KEY attribute means that the column is a primary key, while forcing indexing of these columns. If a given column is to be the primary key, the name of this attribute should be placed after its basic definition:

CREATE TABLE table_name(
    column_name column_type PRIMARY KEY,
    column_name_2 column_type_2 [attributes],
    column_name_3 column_type_3 [attributes]
    ...
);

CREATE TABLE Person(
    Pesel INTEGER PRIMARY KEY,  
    Name VARCHAR(30),
    LastName VARCHAR(40)
);

The primary key can also be defined after the column definitions (also using the PRIMARY KEY syntax). This syntax is useful when we want to create a primary key from many columns:

CREATE TABLE Book(
    ISBN VARCHAR(20),  
    Publisher VARCHAR(30),
    Title VARCHAR(40),
    PRIMARY KEY(ISBN, Title)
);

NOTE: Create primary key on multiple columns, remember that their combination of values must be unique.

NOT NULL¶

The NOT NULL attribute means that the given column cannot contain null values. In other words, these fields must contain some values. Lack of data definition or entering the value NULL will result in an error in query execution.

CREATE TABLE Product(
    Id INTEGER PRIMARY KEY,  
    Name VARCHAR(30) NOT NULL
);

NULL¶

The NULL attribute is the default attribute used when creating a table. It means that there may be empty values in the given column.

NOTE: In most relational databases, the NULL attribute cannot be used on a column that is part of a primary key.

AUTO_INCREMENT¶

This attribute is responsible for the automatic generation of the next value. It can be used only for integer columns. The column value is automatically incremented by 1.

CREATE TABLE Product(
    Id INTEGER PRIMARY KEY AUTO_INCREMENT,  
    Name VARCHAR(30) NOT NULL
);

When adding a value to such a table, we do not need to provide the value of such a column, or the value for na may be NULL.

UNIQUE¶

The UNIQUE attribute forces the table to contain only non-unique values. We create such a table, e.g .:

CREATE TABLE Product(
    Id INTEGER PRIMARY KEY AUTO_INCREMENT,  
    Name VARCHAR(30) UNIQUE
);

NOTE: The UNIQUE attribute can also be used, like PRIMARY KEY - after column declarations. You can also make combinations of multiple column values unique.

DEFAULT¶

Use this attribute to set a default value for the column field. If the user does not provide any value, it will be set to the default. Specifying a value of NULL for such a column, no will select the default value.

CREATE TABLE Product(
    Id INTEGER PRIMARY KEY AUTO_INCREMENT,  
    Name VARCHAR(30) DEFAULT 'unknown'
);

Indexes¶

Indexes are objects that can be added to existing tables. Their goal is to increase the efficiency of operations such as searching and sorting. In turn, each additional index on the table reduces the efficiency of adding or editing existing records. When the index is created inside the database, a special optimizing structure is created.

The index is created with the following statements:

CREATE [UNIQUE] INDEX index_name ON table_name;

It can be created for both one and many columns. Multiple indexes can be created within one table. For the table:

CREATE TABLE Product(
    Id INTEGER PRIMARY KEY,  
    Name VARCHAR(30),
    Type VARCHAR(15)
);

index declaration may look like the following:

CREATE INDEX ind_name ON Product (Name);
CREATE INDEX ind_type ON Product (Type);

or also:

CREATE INDEX product_index ON Product (Name, Type);

Indexes can also be declared in the table definition itself, e.g .:

CREATE TABLE tab;e_name(
    column_name column_type PRIMARY KEY,
    column_name_2 column_type_2 [attributes],
    column_name_3 column_type_3 [attributes,
    ...
    INDEX index_name_1 (columns),
    INDEX index_name_2 (columns)
);

You can use the SHOW statement to display indexes:

SHOW INDEX FROM Product;

The deletion of an index can be performed with the DROP statement:

DROP INDEX index_name ON table_name;

Data modifications¶

Show¶

Information about the structure of an existing table can be obtained with the command SHOW COLUMNS:

SHOW COLUMNS FROM table [FROM database] [LIKE `table_pattern`];

SHOW COLUMNS FROM Product.sda;
SHOW COLUMNS FROM Product.sda LIKE 'name%';

Instead of using SHOW COLUMNS, we can use the DESCRIBE command, which however has fewer options for selecting details.

Alter¶

If you need to change the structure of an existing table, use the ALTER TABLE command:

ALTER TABLE table_name change1[, change2[, ...]];

With this command, you can:

adding columns
remove columns
modifying type, names
adding indexes
removing indexes.

Adding a column¶

The following command is responsible for adding columns:

ALTER TABLE table_name ADD [COLUMN] column_definition;
ALTER TABLE table_name ADD [COLUMN] column_definition [FIRST | AFTER column_name];

The keywords FIRST and AFTER allow you to place a column in a specific table structure, e.g .:

ALTER TABLE Product ADD product_description VARCHAR(255) AFTER product_price;

Delete a column¶

The DROP command is responsible for removing the column:

ALTER TABLE Product DROP COLUMN product_description;

The command above removes the column named product_description.

Add and remove primary key¶

With the command ALTER and the keywordADD, it is possible to add a primary key:

ALTER TABLE Person ADD PRIMARY_KEY (Pesel);

Using the DROP keyword, we can delete an existing key:

ALTER TABLE Person DROP PRIMARY_KEY (Pesel);

Modifying a field¶

Column modification can also be done with the ALTER command together with the MODIFY COLUMN keyword:

ALTER TABLE Product MODIFY COLUMN Id INTEGER AUTO_INCREMENT;

The above operation modifies the Id column so that it grows automatically for the following records.

ALTER TABLE Product MODIFY COLUMN product_description VARCHAR(200)

Thanks to the above instructions, the product_description column will be able to contain 200 characters.

Renaming the table¶

Changing the name of the table is possible with the keyword RENAME:

ALTER TABLE Product RENAME Product1;

Modifying the default value of a column¶

Adding a default value for a given column can be done using the SET DEFAULT operation:

ALTER TABLE table_name ALTER COLUMN column_name SET DEFAULT 'default_value';

For the example below, the default value for the product_description column will be set:

ALTER TABLE Product1 ALTER COLUMN product_description SET DEFAULT 'missing';

Deleting the default value can be done with the command ALTER COLUMN combined with DROP DEFAULT:

ALTER TABLE table_name ALTER COLUMN column_name DROP DEFAULT;

For the example below, the default value for the product_description column will be removed:

ALTER TABLE Product1 ALTER COLUMN product_description DROP DEFAULT;

Add and remove a table index¶

With the ADD INDEX command, we can create an index in the table:

ALTER TABLE table_name ADD INDEX index_name (column_name_1, column_name_2);

ALTER TABLE Product1 ADD INDEX name_id (name, id);

The command DROP INDEX allows you to remove an index from a table:

ALTER TABLE tab;e_name DROP INDEX index_name;

ALTER TABLE Product1 DROP INDEX name_id;

Drop¶

Deleting a table is performed with the command DROP TABLE with the following form:

DROP TABLE [IF EXISTS] table1, table2, ..., tableN;

You can delete one table or the entire set of tables, as long as the table does exist.

NOTE: Deleting a table is equivalent to deleting data from it.

DROP TABLE Product1;

Truncate¶

With the Truncate command, it is possible to delete data inside the table without removing the structure itself, e.g .:

TRUNCATE TABLE Product1;

The above command removes all data entered in the Product table.

NOTE: When trying to delete all records from a table, TRUNCATE will usually be faster than the DELETE FROM table_name command.