SQL - Data Definition - Revision history

Root: Created page with "= SQL: Data Definition Language (DDL) = In this section, we will focus on the use of SQL to create database and table structures, using SQL as a Data Definition Language (DDL). == Database creation and deletion == The main SQL DDL statements are: * CREATE/DROP DATABASE * CREATE/DROP/ALTER TABLE === Database creation - CREATE === The SQL statement CREATE is used to create the database and the structure of the tables. Syntax of the CREATE DATABASE statement:
2026-01-14T13:34:23Z

Created page with "= SQL: Data Definition Language (DDL) = In this section, we will focus on the use of SQL to create database and table structures, using SQL as a Data Definition Language (DDL). == Database creation and deletion == The main SQL DDL statements are: * CREATE/DROP DATABASE * CREATE/DROP/ALTER TABLE === Database creation - CREATE === The SQL statement CREATE is used to create the database and the structure of the tables. Syntax of the CREATE DATABASE statement: <synta..."

New page
= SQL: Data Definition Language (DDL) =

In this section, we will focus on the use of SQL to create database and table structures, using SQL as a Data Definition Language (DDL).

== Database creation and deletion ==

The main SQL DDL statements are:

* CREATE/DROP DATABASE
* CREATE/DROP/ALTER TABLE

=== Database creation - CREATE ===

The SQL statement CREATE is used to create the database and the structure of the tables.

Syntax of the CREATE DATABASE statement:

<syntaxhighlight lang="sql">
CREATE DATABASE databasename;
</syntaxhighlight>

Example: Example of creating a new DB named Traders:

Example of use:

<syntaxhighlight lang="sql">
CREATE DATABASE Traders;
</syntaxhighlight>

Once the database has been created, the next step is to create its tables.

=== Database deletion - DROP ===

Syntax of the DROP DATABASE statement:

<syntaxhighlight lang="sql">
DROP DATABASE [IF EXISTS] databasename;
</syntaxhighlight>

Example: Example of deleting an existing database:

Example of use:

<syntaxhighlight lang="sql">
DROP DATABASE IF EXISTS Traders;
</syntaxhighlight>

Note: It is common to use the deletion and creation instructions of a database together, using the conditional (IF EXISTS) to avoid errors.

=== Database modification - ALTER DATABASE ===

Syntax of the ALTER DATABASE statement:

<syntaxhighlight lang="sql">
ALTER {DATABASE | SCHEMA} db_Name
alter specification...

alter specification:
[DEFAULT] CHARACTER SET [=] charset_name
[DEFAULT] COLLATE [=] collation_name;
</syntaxhighlight>

Where:

* ALTER DATABASE allows changing the general characteristics of a database. These characteristics are stored in the db.opt file in the database directory. To use the ALTER DATABASE command, you need the ALTER privilege on the database. ALTER SCHEMA is a synonym for ALTER DATABASE.
* The CHARACTER SET clause changes the default character set of the database.
* The COLLATE clause changes the default collation set of the database.

Note:
* You can see which character sets and collations are available using, respectively, the SHOW CHARACTER SET and SHOW COLLATION instructions.
* Changing the default character set or collation of a database does not affect the character set or collation of any stored procedure or stored function created previously that depended on the default values. These must be deleted and recreated to apply the changes in the character set or collation.

Example: Modification of the character set and collation of the database.

Example of use:

<syntaxhighlight lang="sql">
ALTER DATABASE laMevaBD
CHARACTER SET utf8mb4
COLLATE utf8mb4_spanish_ci;
</syntaxhighlight>

== Table creation - CREATE TABLE ==

Syntax of the CREATE TABLE statement:

<syntaxhighlight lang="sql">
CREATE TABLE nom_taula (
nom_columna tipus_de_dada restriccions_de_columna,
nom_columna tipus_de_dada restriccions_de_columna,
...
restriccions_de_taula
);
</syntaxhighlight>

Where:
* nom_taula is the table name of the database, such as, for example, Employees.
* Each field of CREATE TABLE has three parts:
Column name
Data type
** Optional column constraints

=== Column names ===

the name of a column must be unique within the table. Some examples of column names are FirstName and LastName.

=== Data types ===

The data type must be one of the types defined by MySQL/MariaDB. It must be taken into account that many data types have a length, such as: CHAR(35) or DECIMAL(8,2).

The main data types we can use are the following:

{| class="wikitable"
! Class !! Type !! Description !! Range
|-
| '''Numeric''' || TINYINT || Very small integers (1 Byte) || -128 to 127 or 0 to 255
|-
| || SMALLINT || Small integers (2 Bytes) || -32718 to 32767 or 0 to 65535
|-
| || MEDIUMINT || Medium integers (3 Bytes) || -8388608 to 8388607 or 0 to 16777215
|-
| || INT or INTEGER || Integers (4 Bytes) || -2147483648 to 2147483647 or 0 to 4294967295
|-
| || BIGINT || Very large integers (8 Bytes) || -2^63 to 2^63-1 or 0 to 2^64-1
|-
| || FLOAT || Floating point numbers and simple precision ||
|-
| || DOUBLE or REAL || Floating point numbers and double precision ||
|-
| || DECIMAL or NUMERIC (M,D) || Floating point numbers stored as a string, where M indicates the display width and D the number of decimals ||
|-
| '''String''' || CHAR(D) || Fixed length string, where D indicates the length || 1 to 255
|-
| || VARCHAR(D) || Variable length string, where D indicates the length || 0 to 65535
|-
| || TEXT || Text || 0 to 65535
|-
| || ENUM || Enumerates types (limits to a list) || Example: ENUM('a','b','c')
|-
| '''Date and time''' || DATE || Date in YYYY-MM-DD format || 1000-01-01 to 9999-12-31
|-
| || TIME || Time in hh:mm:ss format ||
|-
| || DATETIME || Date and time in YYYY-MM-DD hh:mm:ss format || 1000-01-01 00:00:00 to 9999-12-31 23:59:59
|-
| || TIMESTAMP || Date and time in YYYYMMDDhhmmss format ||
|}

==== Numeric data types ====

* '''Integers''':
From small to large: TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT. You can see how much they occupy and their range of values at: Integer types.
Optionally, integer types can be UNSIGNED. In this case, only positive numbers can be saved.

Note: Furthermore, integer types can carry a length, for example, INT(5). This length does not modify the range and size of the data type; it is only an indication of how applications should display the data. If we put a value that exceeds the data range, it will give us an error.

* '''Reals''':
The DECIMAL (or NUMERIC) type allows saving numbers with a certain precision and scale. DECIMAL guarantees that the numbers will be saved exactly with the specified precision.

The declaration, for example, DECIMAL(5,2), indicates that up to 5 figures will be saved, of which two are decimals. Thus, the range of values that can be saved in a DECIMAL(5,2) is from -999.99 to 999.99. DECIMAL(5) is a synonym for DECIMAL(5,0), and DECIMAL is a synonym for DECIMAL(10).

The FLOAT and DOUBLE types allow saving approximate decimal numbers and are equivalent to the float and double data types of some programming languages, such as Java.

* '''Bit sequences''':
The BIT type allows saving bit sequences. For example, BIT(8) saves 8-bit sequences. To indicate a literal bit value, it is done with the prefix "b", for example: b'100101'.

* '''Booleans''':
To save a true or false value, we can use the BOOL (or BOOLEAN) type. Actually, these words are synonyms for TINYINT(1). Values different from 0 are considered true and 0 is considered false.
You can also use a BIT(1) to save a boolean value.

==== Text data types ====

* '''Short text strings''':
The CHAR and VARCHAR types are used for the most common text strings. Both need to be declared indicating the amount of characters to save, for example, CHAR(30) or VARCHAR(30).

The difference lies in the fact that CHAR(30) will always save 30 characters. If the string saved is shorter, spaces are added to complete the indicated amount of characters (these spaces are removed again when the data is retrieved). In contrast, for VARCHAR(30), what is indicated is the maximum characters that can be saved, but shorter strings are saved without occupying extra space.

In general, CHAR should be used for data that we know will always have the same length and VARCHAR for data of variable length. For data that always has the same length, VARCHAR occupies more space internally. Furthermore, if all the fields of a table are of fixed length, searching for data on this table is faster.

* '''Byte strings''':
BINARY and VARBINARY are used to save byte sequences that are not text or that are not to be treated as text.

* '''Long strings''':
TEXT is used for long text strings and BLOB for long binary strings. The types resemble VARCHAR and VARBINARY respectively but have a much higher storage capacity.

TEXT and BLOB each have 4 variants: TINYTEXT, TEXT, MEDIUMTEXT and LONGTEXT, and TINYBLOB, BLOB, MEDIUMBLOB and LONGBLOB respectively. The only difference between them is the maximum length they accept.

* '''String ranges (enumerations)''':
The ENUM type allows specifying a fixed set of text strings that are valid for a certain field. For example, a field that saves the name of a day of the week could be declared as:

Example of use:

<syntaxhighlight lang="sql">
ENUM('dilluns','dimarts','dimecres','dijous','divendres','dissabte','diumenge');
</syntaxhighlight>

Internally, ENUM data is saved as integers, so an ENUM occupies much less than using, for example, a VARCHAR to save the same information. Using an ENUM is similar to using an integer number with the advantage that with an ENUM queries and responses are clearer.

The SET type also allows defining a set of valid text strings for a field but, unlike ENUM, a record can take none, one, or more values from the values defined in the set.

==== Time and date data types ====

* '''Dates''':
The DATE type allows saving dates. Dates are saved in year-month-day format, for example, '2016-10-05'.
The DATETIME type allows storing combinations of a specific date and time, for example, '2016-10-05 15:40:32'. Seconds also accept fractions.

* '''Time''':
The TIME type is used to save a specific time, without a date, for example '15:30:32'.
Finally, the YEAR type is used to save years, for example: '2016'.
The TIMESTAMP type is similar to DATETIME, but is always stored in the UTC time zone and has a more reduced range of values. It is often used to mark events related to the database itself; for example, if we want to save the moment a row was last updated, we would use a TIMESTAMP type field.

== Optional column constraints ==

The declaration of a column can be accompanied by a series of modifiers that specify the behavior it must have.

=== NULL and NOT NULL ===

The NULL modifier indicates that the column can have NULL values, while NOT NULL prohibits NULL values from being saved. If neither is indicated, it is assumed that NULL values can be saved.

=== DEFAULT ===

The DEFAULT modifier allows specifying a default value for the column, so that if when inserting a row the value for this column is not indicated, the default value is taken.

=== AUTO_INCREMENT ===

The AUTO_INCREMENT modifier indicates that it is an integer column to which we want a different value to be assigned for each row. It is used mainly to generate synthetic primary keys that receive a valid value automatically.

=== UNIQUE ===

The UNIQUE modifier indicates that there cannot be two equal NOT NULL values in that column for the whole table.

Declaring a column as UNIQUE causes an index to be automatically created for the selected column. This index speeds up searches and sortings that use the column.

=== PRIMARY KEY ===

Indicates that this column is the primary key of the table. If the primary key consists of more than one column, it must be specified after the declaration of all columns.

A primary key is, automatically, NOT NULL.

MySQL/MariaDB automatically creates an index for all primary keys.

Example: To illustrate the creation of a table and the use of the various types and modifiers, we are going to create an example Employees table:

Example of use:

<syntaxhighlight lang="sql">
CREATE TABLE Employees (
Id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
DNI CHAR(9) NOT NULL UNIQUE,
DepartmentId INT UNSIGNED NOT NULL,
FirstName VARCHAR(50) NOT NULL,
LastName VARCHAR(100) NOT NULL DEFAULT '',
Active TINYINT(1) NOT NULL DEFAULT TRUE,
SupervisorId INT UNSIGNED,
BirthDate DATE NOT NULL
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_spanish_ci;
</syntaxhighlight>

Where:
* The first field is an identifier of the workers, of type unsigned INT. The identifier of each worker will be created automatically if we do not indicate otherwise, and constitutes the primary key of the table.
* The second field is the DNI, which always occupies 9 characters. NULL values are not accepted and there cannot be two repeated DNI in the table.

Example: Similarly, we can create a Departments table, a Projects table and an Assignments table using the SQL command CREATE TABLE as shown in the following examples.

Example of use:

<syntaxhighlight lang="sql">
CREATE TABLE Departments (
Id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
DepartmentName VARCHAR(30) NOT NULL UNIQUE,
OfficeNumber CHAR(5) NOT NULL,
Phone CHAR(13) NOT NULL
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_spanish_ci;

CREATE TABLE Projects (
Id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
ProjectName VARCHAR(100) NOT NULL,
DepartmentId INT UNSIGNED NOT NULL,
MaxHours DECIMAL(8,2) NOT NULL DEFAULT 100,
StartDate DATETIME,
EndDate DATETIME
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_spanish_ci;

CREATE TABLE Assignments (
Id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
EmployeeId INT UNSIGNED NOT NULL,
HoursWorked DECIMAL(6,2) UNSIGNED
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_spanish_ci;
</syntaxhighlight>

== Table constraints - CONSTRAINT PRIMARY KEY ==

Table constraints are identified by the keyword CONSTRAINT and can be used to define various constraints, as described below.

=== Primary key constraints ===

The primary key (PRIMARY KEY) can also be specified as a table constraint. This is especially useful when the key is formed by more than one column.

Example: For example, in a hotel chain, a room is identified by the hotel key and the room number:

Example of use:

<syntaxhighlight lang="sql">
CREATE TABLE Rooms (
HotelNo INT UNSIGNED NOT NULL,
RoomNo INT UNSIGNED NOT NULL,
Type CHAR(9),
Price DECIMAL(6,2),
CONSTRAINT PRIMARY KEY (HotelNo, RoomNo)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_spanish_ci;
</syntaxhighlight>

=== Foreign key constraints - CONSTRAINT FOREIGN KEY ===

The foreign key constraint (FOREIGN KEY) defines a column, or a combination of columns, whose value matches the primary key of another table.

The use of foreign keys guarantees referential integrity between the two tables: any insertion or modification of the column or columns that are foreign keys must necessarily match valid primary key values in the main table, or the operation will fail.

The behavior to be adopted in case the referenced elements in the main table are modified or deleted can be chosen at the moment of creating the foreign key constraint.

The data type of the foreign key must match the data type of the primary key it refers to.

Example: For example, we can create the tables from the previous section adding the appropriate foreign key constraints:

Example of use:

<syntaxhighlight lang="sql">
CREATE TABLE Projects (
Id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
ProjectName VARCHAR(100) NOT NULL,
DepartmentId INT UNSIGNED NOT NULL,
MaxHours DECIMAL(8,2) NOT NULL DEFAULT 100,
StartDate DATETIME,
EndDate DATETIME,
CONSTRAINT FOREIGN KEY (DepartmentId) REFERENCES Departments(Id)
ON DELETE NO ACTION ON UPDATE CASCADE
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_spanish_ci;
</syntaxhighlight>

In the Projects table, we have considered that a department cannot be deleted while it has assigned projects.

Example of use:

<syntaxhighlight lang="sql">
CREATE TABLE Assignments (
Id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
EmployeeId INT UNSIGNED NOT NULL,
HoursWorked DECIMAL(6,2) UNSIGNED,
CONSTRAINT FOREIGN KEY (EmployeeId) REFERENCES Employees(Id)
ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_spanish_ci;
</syntaxhighlight>

In contrast, in the Assignments table, we have considered it appropriate to delete all tasks assigned to an employee in case the employee is deleted.

Note: If the foreign key is formed by more than one field, the foreign key constraints must include all the columns of the primary key of the referenced table.

Example: For example, a bookings table on the hotel rooms from the previous section could look like this:

Example of use:

<syntaxhighlight lang="sql">
CREATE TABLE Bookings (
BookingNumber INT UNSIGNED PRIMARY KEY,
CheckIn DATE NOT NULL,
CheckOut DATE NOT NULL,
CustomerId INT UNSIGNED NOT NULL,
HotelNo INT UNSIGNED NOT NULL,
RoomNo INT UNSIGNED NOT NULL,
CONSTRAINT FOREIGN KEY (CustomerId) REFERENCES Customers(Id)
ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT FOREIGN KEY (HotelNo, RoomNo) REFERENCES Rooms(HotelNo, RoomNo)
ON DELETE NO ACTION ON UPDATE CASCADE
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_spanish_ci;
</syntaxhighlight>

=== Check constraints - CHECK ===

The CHECK constraint limits the values that can be stored in a column or set of columns.

Example:

Example of use:

<syntaxhighlight lang="sql">
CREATE TABLE Sales (
SalesId INT PRIMARY KEY,
SalesPersonId INT CHECK (SalesPersonId BETWEEN 100 AND 199),
Name CHAR(15),
Age INT UNSIGNED CHECK (Age >= 21),
Quota DECIMAL(7,2) CHECK (Quota >= 0.0),
HireDate DateTime,
CHECK ((HireDate < '2016-01-01') OR (Quota<=300000))
);
</syntaxhighlight>

=== UNIQUE constraints ===

If the set formed by several columns cannot be repeated, we can define a UNIQUE constraint at the table level.

Example: In this case, although we admit that several projects are named the same, we do not let the same department have two projects with the same name.

Example of use:

<syntaxhighlight lang="sql">
CREATE TABLE Projects (
Id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
ProjectName VARCHAR(100) NOT NULL,
DepartmentId INT UNSIGNED NOT NULL,
MaxHours DECIMAL(8,2) NOT NULL DEFAULT 100,
StartDate DATETIME,
EndDate DATETIME,
CONSTRAINT FOREIGN KEY (DepartmentId) REFERENCES Departments(Id)
ON DELETE NO ACTION ON UPDATE CASCADE,
CONSTRAINT UNIQUE (ProjectName, DepartmentId)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_spanish_ci;
</syntaxhighlight>

=== ENGINE, CHARSET and COLLATE ===

It is important to understand the difference between ENGINE, CHARSET and COLLATE when defining a table in a MySQL or MariaDB database.

* '''ENGINE''' (Storage Engine): ENGINE specifies the storage engine that the table will use. Each storage engine has its own characteristics and optimizations. For example, InnoDB is a transactional engine that supports foreign keys and transactions, while MyISAM is simpler and can be faster for some operations.

Example:

<syntaxhighlight lang="sql">
CREATE TABLE exemple (
id INT,
nom VARCHAR(50)
) ENGINE=InnoDB;
</syntaxhighlight>

* '''CHARSET''' (Character set): CHARSET refers to the character set that the table will store. Each character has a specific numerical representation in a character set, and this will affect how textual data is saved and retrieved. For example, the utf8 character set allows storing Unicode characters, while latin1 is limited to a smaller set of characters.

Example:

<syntaxhighlight lang="sql">
CREATE TABLE exemple (
id INT,
nom VARCHAR(50)
) ENGINE=InnoDB CHARSET=utf8;
</syntaxhighlight>

* '''COLLATE''' (Sort order): COLLATE determines the sort order and string comparison in the table. Different languages and regions can have different sorting and comparison rules. COLLATE can be used to ensure that string comparisons are done in a specific way.

Example:

<syntaxhighlight lang="sql">
CREATE TABLE exemple (
id INT,
nom VARCHAR(50)
) ENGINE=InnoDB CHARSET=utf8 COLLATE=utf8_general_ci;
</syntaxhighlight>

Summary:
* ENGINE: determines the storage engine that manages the table.
* CHARSET and COLLATE: influence how the data is stored and compared.

== Table modification - ALTER TABLE ==

Statements for table modification:

Example:

Example of use:

<syntaxhighlight lang="sql">
ALTER TABLE Hotels ADD Name VARCHAR(100);
</syntaxhighlight>

Example output:

<syntaxhighlight lang="text">
> desc Hotels;
+---------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+------------------+------+-----+---------+-------+
| HotelNo | int(10) unsigned | NO | PRI | NULL | |
| Name | varchar(100) | YES | | NULL | |
+---------+------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

> ALTER TABLE Hotels ADD CONSTRAINT UNIQUE (Name);
Query OK, 0 rows affected (0.38 sec)
Records: 0 Duplicates: 0 Warnings: 0

> desc Hotels;
+---------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+------------------+------+-----+---------+-------+
| HotelNo | int(10) unsigned | NO | PRI | NULL | |
| Name | varchar(100) | YES | UNI | NULL | |
+---------+------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

> ALTER TABLE Hotels MODIFY Name VARCHAR(100) NOT NULL;
Query OK, 0 rows affected (1.33 sec)
Records: 0 Duplicates: 0 Warnings: 0

> desc Hotels;
+---------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+------------------+------+-----+---------+-------+
| HotelNo | int(10) unsigned | NO | PRI | NULL | |
| Name | varchar(100) | NO | UNI | NULL | |
+---------+------------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

> ALTER TABLE Hotels DROP COLUMN Name;
Query OK, 0 rows affected (0.97 sec)
Records: 0 Duplicates: 0 Warnings: 0

> desc Hotels;
+---------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+------------------+------+-----+---------+-------+
| HotelNo | int(10) unsigned | NO | PRI | NULL | |
+---------+------------------+------+-----+---------+-------+
1 row in set (0.00 sec)
</syntaxhighlight>

== Table deletion - DROP TABLE ==

The DROP TABLE statement allows deleting a table from the database.

Syntax of the statement:

Syntax:

<syntaxhighlight lang="sql">
DROP TABLE [IF EXISTS] nomTaula;
</syntaxhighlight>

Example: This statement would delete the Hotels table from the database:

Example of use:

<syntaxhighlight lang="sql">
DROP TABLE Hotels;
</syntaxhighlight>

Note: It is common to use DROP TABLE with CREATE TABLE including IF EXISTS to avoid errors.

== Emptying table content - TRUNCATE ==

In contrast, the TRUNCATE statement allows emptying the content of a table, without deleting the table itself:

Example:

Example of use:

<syntaxhighlight lang="sql">
TRUNCATE Hotels;
</syntaxhighlight>

== Indexes ==

Creating indexes in MySQL or MariaDB database tables is an important practice for improving query performance. An index is like an ordered list that points to the locations of the data within a table, similar to how a word would be found in a book thanks to the alphabetical index.

An index is similar to how a book index works; it allows finding information without needing to read the entire book.

An index is a structure that provides fast access to the rows of a table. In SQL, it is a structure associated with a table that speeds up the retrieval of rows from the table.

Indexes will be created for the columns that are used for queries (those used to search for data). Tables with indexes need more space; therefore, the insertion, update, or deletion of data will require more time.

SQL automatically creates indexes for values with constraints of type PRIMARY KEY and UNIQUE.

Why create indexes?

* Improves performance: queries are executed more quickly as the index provides more efficient access.

=== Creating an index ===

Example:

Example of use:

<syntaxhighlight lang="sql">
CREATE INDEX xdepartament ON materia(departament);
</syntaxhighlight>

=== Deleting an index ===

Example:

1. Delete the index you created in the previous section (xdepartament).
2. Attempt to delete the indexes that were created automatically by the program.

The solution is:

Example of use:

<syntaxhighlight lang="sql">
DROP INDEX xdepartament ON materia;

DROP INDEX materia ON materia;

DROP INDEX primary ON materia;
</syntaxhighlight>

Note: You cannot delete this index because it is a primary key.

== Creating views - CREATE VIEW ==

A view is similar to a table, but it does not store real data; instead, the data shown is the result of executing a SELECT query on other tables.

On a view, we can execute any SELECT, but we cannot execute most data modification statements because it is complicated to know which real tables the modifications should be applied to.

To create a view, the CREATE VIEW statement is used.

A view does not store new data; instead, what is seen is the result of executing a SELECT query that has been saved as part of the view definition (we can see the SELECT in question by executing SHOW CREATE TABLE film_list).

Example: For example, on the Chinook database, we could create a view that summarizes the information for each album:

Example of use:

<syntaxhighlight lang="sql">
CREATE VIEW AlbumInfo AS
SELECT al.Title AS Album, a.Name AS Artist, GROUP_CONCAT(t.Name) AS Tracks
FROM Album al
JOIN Artist a ON al.ArtistId=a.ArtistId
JOIN Track t ON al.AlbumId=t.AlbumId
GROUP BY al.AlbumId, al.Title, a.Name;
</syntaxhighlight>

Where:
* This view contains the title of the album, the name of the artist, and a list with the name of the included songs.
* The GROUP_CONCAT() function joins all the values of a column that have been joined due to a GROUP BY into a single text string.

We can perform queries on this view:

Example of use:

<syntaxhighlight lang="sql">
SELECT * FROM AlbumInfo WHERE Artist LIKE 'The Rolling Stones';

* 1. row *
Album: Hot Rocks, 1964-1971 (Disc 1)
Artist: The Rolling Stones
Tracks: Under My Thumb,Play With Fire,Get Off Of My Cloud,Paint It Black, Let's Spend The Night Together,Heart Of Stone,As Tears Go By, 19th Nervous Breakdown,Ruby Tuesday,Time Is On My Side,Satisfaction, Mother's Little Helper
...
</syntaxhighlight>

This view will not allow modifications because the GROUP BY makes it impossible to know what the original rows to modify were.

Example: In this example, we create a view that shows the information of the tracks and the name of their genre:

Example of use:

<syntaxhighlight lang="sql">
CREATE VIEW TrackGenre AS
SELECT TrackId, Track.Name AS TrackName, Genre.Name AS GenreName
FROM Track
JOIN Genre ON Track.GenreId=Genre.GenreId;
</syntaxhighlight>

In this case, we can, for example, execute UPDATE because the DBMS can deduce which is the real data it must modify:

Example of use:

<syntaxhighlight lang="sql">
SELECT * FROM TrackGenre WHERE TrackId=10;
+---------+------------+-----------+
| TrackId | TrackName | GenreName |
+---------+------------+-----------+
| 10 | Evil Walks | Rock |
+---------+------------+-----------+
1 row in set (0.00 sec)

UPDATE TrackGenre SET GenreName='Blues' WHERE TrackId=10;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
</syntaxhighlight>

ATTENTION: The change that has been made might not be what we expected: the name of the Rock genre has been modified and renamed to Blues; the GenreId of the track has not been modified.

== Deleting views ==

The syntax for deleting views is the following:

Syntax:

<syntaxhighlight lang="sql">
DROP VIEW [IF EXISTS]
view_name [, view_name] ...
[RESTRICT | CASCADE]
</syntaxhighlight>

Deleting more than one view at the same time is allowed.

Example of use:

<syntaxhighlight lang="sql">
DROP VIEW IF EXISTS AlbumInfo, TrackGenre;
</syntaxhighlight>
[[Category:IT Databases]]