Data Definition and Manipulation

By data definition, we mean the creation of the storage structures: creating databases, creating tables, indexes, procedures, etc. Also deleting and modifying them.

By data manipulation, we mean everything that has to do with the content of these structures: querying, inserting rows, deleting rows, modifying data...

As a summary of what is going to be developed from here on, we will see:

• CREATE TABLE tableName (columns and constraints)

  • create a table defining its columns and data types, and with the necessary restrictions (primary key, foreign, non-null, etc.).

    • the tables persist in the schema until they are deleted.

• DROP TABLE tableName

  • drop a table from the schema (any rows in it also disappear)

• INSERT INTO tableName VALUES (values)

  • add a row to the table with corresponding values in each row component

• INSERT INTO tableName querySelect

  • variant of the previous one that gets the values from a query to the same or another database

• DELETE FROM tableName [WHERE condition].

  • delete one or more rows, depending on the conditions we require

• UPDATE tableName SET assignments [WHERE conditions]

  • modify specific values in one or several rows, depending on the conditions we require.

 Create table

The definition of tables is the first step in the building of a database. The set of table descriptions makes up the schema of the database and it is representing a specific information system

Besides, in order to fulfill the restrictions of the relational model, we must choose the appropriate primary keys: DNI for profesores and código for asignaturas. Obviously, the way we build these tables is our decision as database designers. Maybe, in other situation, the definition of the tables could be different.

The statement CREATE TABLE allows us to create each table we need for our database:

CREATE TABLE nameTable( {listColumns} [,{restrictions}] )

The list of columns, in its simplest form, is a set of expressions (as many as columns we want, and separated with commans) like:

column dataType[,column dataType[, ...]]

All the types managed by MySQL, being most of them common between database engines except slight differences, can be found in https://mariadb.com/kb/en/data-types/.

Restrictions

Restrictions are rules, usually established in the moment of the creation of a table, to guarantee the integrity of data.

Basically, restrictions oblide to follow certain rules when a row is inserted, deleted or updated, and the operation will be performed only if the restrictions of the table are fulfilled.

We have the following types of restrictions for data integrity:

• • NOT NULL: specifies that the column must have a value.

  • PRIMARY KEY: identifies each row uniquely using one or more columns, the columns that are part of a primary key can not have null value.

  • FOREIGN KEY: establishes a relation between one/more column(s) of a table and another column(s) of the referenced table, being this/these last column(s) the PRIMARY KEY.

  • CHECK: specifies a condition that must be evaluated as "true" .

From these restrictions, only the one referring the primary key, containing as many columns as necessary, will be used:

PRIMARY KEY (column[,column[, ...]])

Drop table

Catalog information

Remember that executing DESC nombreTabla or DESCRIBE nombreTabla, all the information related to the columns of the table must be shown, the internal order of them in the table, and their data types.

In the moment of creating a restriction, apart from specifying the rules that must be fulfilled, we can give it a name. To establish the names of the restrictions, the following agreement is used: being descriptive names, starting with, for example,

Obviously, this agreement is only a recommendation.

We are not going to study in depth the name of the restrictions nor the syntax to create them, but we need to know that if we don't assign them a name, the DBMS will do.

Insert

In order to insert new data in a database we will use the statement INSERT of SQL. With the syntax shown next we will be able to add new data in each table of a database. At first, we will see the minumum statement syntax, formed by INTO and VALUES clauses.

INSERT INTO nombreTabla VALUES ( listaExpresiones )

Add a new teacher with the following data:

• • dni 55555555

  • nombre PATRICIO MARTÍNEZ,

  • categoría TU

  • fecha de incorporación 01/01/2000

insert into profesores values ('55555555','PATRICIO MARTINEZ','TU','2000-1-1')

 Add a new teacher with the following data:

• • dni 66

  • nombre ERNESTO PEREZ

  • categoría ASO

  • fecha de incorporación 01/01/1985

insert into profesores values ('66','ERNESTO PEREZ','ASO','1985-1-1');

The result of the statement INSERT, will always be the number of added rows, in case of a properly execution. For the cases in the statement breaking any restriction of the database, and therefore, the execution is not correct, the result will indicate which restriction has been broken. The DBMS, each time we add a new data in a table, is in charge of checking the active restrictions, in our case, the primary keys, that as we already know, do not admit nulls or duplicates.

Add a new teacher with the following data:

• • dni 66

  • nombre JUAN JUANÍTEZ

  • categoría XXX

  • fecha de incorporación 01/01/1905

insert into profesores values ('66','JUAN JUANITEZ','XXX','1905-1-1')

The system will try to assign to the non indicate columns a default value, or in case it was defined, a null value. 

It is advisable to indicate always the columns with values, being all of them or not part of the table. The reason are:

• • There is no need to take into account if we are giving values to all of the columns or only some of them in the INSERT statement.

  • If the structure of the table is modified by any reason, for example new columns are added, and we usually do not indicate the name of the columns, the statement won't work anymore

Adding more than one row

Suppose that in the BD Ejemplo there exists a table called OPTATIVAS with the code and credits of the optative subjects.

There exists the possibility to insert the result of a column instead of indicating a list of values to be inserted. This allows us to insert more than one row in a table in a unique operation, specifically, as many rows as tuples are returned in the SELECT statement.

INSERT INTO nombreTabla [ ( listaColumnas ) ] selectOrder 

Suppose that all the subjects with less than 9 credits are optative. We need to insert the code of these subjects in the table OPTATIVAS. In this case, as we have the table ASIGNATURAS, we have to possible options. One option is performing the SELECT and introducing one by one the subjects using INSERT, copying the data of the rows obtained. 

The result fo the SELECT statement must be coherent with the number of columns and the data types. The following statement will provoke compiling errors:

insert into optativas (asignatura) select codigo, creditos from asignaturas where creditos < 9;

insert into optativas (asignatura,creditos) select codigo from asignaturas where creditos < 9;

insert into optativas (asignatura,creditos) select dni,ingreso from profesores;

Delete

The DELETE sentece allows us to delete the rows of the table.

DELETE [FROM] nombreTabla [WHERE condición]

In order to delete rows of various tables we need to execute as many DELETE statements as rows we want to delete.

(Note: in MySQL is possible to delete over various tables but it can be assure that other DBMS allows that also)

Update

The statement UPDATE will allow us to modify the information stored in a table.

UPDATE nombreTabla [aliasTabla]

SET { {columna=expresion | columna=subconsulta} | listaColumnas=subconsulta}

[WHERE condición]

It is not possible to modify more than one table in a single sentence. To modify the values of various tables it is necessary to perform as many UPDATE sentences as tables. (NOTE:the same as with delete ).

There exists the possibility of modifying the information stored in a table assigning as new value/s the result of a query.

The result of the query can be assign to one single column or a list of columns. In the first case, the SELECT statement will only return one value that must match with the data type of the column being assigned.