5. Lenguajes de bases de datos

5.1 Introducción

Los lenguajes de consulta (query language) son especificaciones formales para representar consultas. Aún cuando son llamados de "consulta" en realidad pueden hacer mucho más que consultas.

5.2 Structured Query Language (SQL)

  • Creado por IBM alrededor de los años 70s
  • Combinación de álgebra relacional y cálculo relacional
  • En 1986 ANSI e ISO lo estandarizan en SQL-86
  • Otras versiones: SQL-92, SQL-99

5.2.1 Data Manipulation Language (DML)

INSERT

insert into table_name (column_name, ..., column_name)
values (value, ..., value);

insert into musicians (musician_id, last_name, first_name, nickname)
values (2,'Lydon','John','Johnny Rotten');

insert into musicians
values (2,'Lydon','John','Johnny Rotten');

 

 INSERT [LOW_PRIORITY | DELAYED] [IGNORE]
        [INTO] tbl_name [(col_name,...)]
        VALUES ((expression | DEFAULT),...),(...),...
        [ ON DUPLICATE KEY UPDATE col_name=expression, ... ]
or  INSERT [LOW_PRIORITY | DELAYED] [IGNORE]
        [INTO] tbl_name [(col_name,...)]
        SELECT ...
or  INSERT [LOW_PRIORITY | DELAYED] [IGNORE]
        [INTO] tbl_name
        SET col_name=(expression | DEFAULT), ...
        [ ON DUPLICATE KEY UPDATE col_name=expression, ... ]
      

UPDATE

update table_name
set column_name= value,
...,
column_name=value
where column_name=value;

update albums
set year=1994
where album_id=4;

update albums
set category='old music'
where year < 1980;

 

UPDATE [LOW_PRIORITY] [IGNORE] tbl_name
    SET col_name1=expr1 [, col_name2=expr2 ...]
    [WHERE where_definition]
    [ORDER BY ...]
    [LIMIT row_count]
      

or:

UPDATE [LOW_PRIORITY] [IGNORE] tbl_name [, tbl_name ...]
    SET col_name1=expr1 [, col_name2=expr2 ...]
    [WHERE where_definition]

DELETE

delete from table_name
where column_name=value

delete from albums
where albums_id=4;

 

DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM table_name
       [WHERE where_definition]
       [ORDER BY ...]
       [LIMIT row_count]
                  

or:

DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
       table_name[.*] [, table_name[.*] ...]
       FROM table-references
       [WHERE where_definition]
                    

or:

DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
       FROM table_name[.*] [, table_name[.*] ...]
       USING table-references
       [WHERE where_definition]
                    

SELECT

select column_name, ..., column_name
from table_name
where column_name=value;

select title
from albums
where category='industrial';

 

SELECT [STRAIGHT_JOIN]
       [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
       [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS] [HIGH_PRIORITY]
       [DISTINCT | DISTINCTROW | ALL]
    select_expression,...
    [INTO {OUTFILE | DUMPFILE} 'file_name' export_options]
    [FROM table_references
      [WHERE where_definition]
      [GROUP BY {unsigned_integer | col_name | formula}
        [ASC | DESC], ... [WITH ROLLUP]]
      [HAVING where_definition]
      [ORDER BY {unsigned_integer | col_name | formula}
        [ASC | DESC] ,...]
      [LIMIT [offset,] row_count | row_count OFFSET offset]
      [PROCEDURE procedure_name(argument_list)]
      [FOR UPDATE | LOCK IN SHARE MODE]]
      

table_name [[AS] alias]
    [[USE INDEX (key_list)]
  | [IGNORE INDEX (key_list)]
  | [FORCE INDEX (key_list)]]

JOIN

select bands.band_name
from bands,albums
where albums.category='alternative'
and bands.band_id=albums.band_id;

SELECT t1.name, t2.salary FROM employee AS t1, info AS t2 -> WHERE t1.name = t2.name;
SELECT table1.* FROM table1 -> LEFT JOIN table2 ON table1.id=table2.id -> WHERE table2.id IS NULL;

 

table_reference, table_reference
table_reference [INNER | CROSS] JOIN table_reference [join_condition]
table_reference STRAIGHT_JOIN table_reference
table_reference LEFT [OUTER] JOIN table_reference [join_condition]
table_reference NATURAL [LEFT [OUTER]] JOIN table_reference
{ OJ table_reference LEFT OUTER JOIN table_reference
    ON conditional_expr }
table_reference RIGHT [OUTER] JOIN table_reference [join_condition]
table_reference NATURAL [RIGHT [OUTER]] JOIN table_reference

UNION

(SELECT a FROM table_name WHERE a=10 AND B=1 ORDER BY a LIMIT 10) UNION (SELECT a FROM table_name WHERE a=11 AND B=2 ORDER BY a LIMIT 10) ORDER BY a;

 

SELECT ...
UNION [ALL | DISTINCT]
SELECT ...
  [UNION [ALL | DISTINCT]
   SELECT ...]

 

SUBQUERIES

select title
from albums,
where band_id in
(select bands.band_id
from bands, band_musician
where band_musician.musician_id=2
and bands.band_id=band_musician.band_id);

INDEX

CREATE INDEX part_of_name ON customer (name(10));
CREATE INDEX two_attributes ON customer (name(10), balance);

 

CREATE [UNIQUE|FULLTEXT] INDEX index_name [index_type]
       ON tbl_name (index_col_name,...)

index_col_name:
        col_name [(length)] [ASC | DESC]

SHOW INDEX FROM tbl_name


DROP INDEX index_name ON tbl_name

 

EXPLAIN

explain table_name;

explain select...;


(carlos) [prueba]> create index iname on auser(last_name,first_name);
Query OK, 1 row affected (0.15 sec)
Records: 1  Duplicates: 0  Warnings: 0


(carlos) [prueba]> show index from auser;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| auser |          0 | PRIMARY  |            1 | id          | A         |           1 |     NULL | NULL   |      | BTREE      |         |
| auser |          1 | iname    |            1 | last_name   | A         |           1 |     NULL | NULL   | YES  | BTREE      |         |
| auser |          1 | iname    |            2 | first_name  | A         |           1 |     NULL | NULL   | YES  | BTREE      |         |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
3 rows in set (0.00 sec)

(carlos) [prueba]> explain select * from auser where last_name='aguilar';
+----+-------------+-------+------+---------------+-------+---------+-------+------+--------------------------+
| id | select_type | table | type | possible_keys | key   | key_len | ref   | rows | Extra                    |
+----+-------------+-------+------+---------------+-------+---------+-------+------+--------------------------+
|  1 | SIMPLE      | auser | ref  | iname         | iname |     256 | const |    1 | Using where; Using index |
+----+-------------+-------+------+---------------+-------+---------+-------+------+--------------------------+
1 row in set (0.08 sec)


5.2.2 Data Definition Language (DDL)

CREATE

create table table_name (
column_name column_type column_modifiers,
...,
column_name column_type column_modifiers);

create table musicians(
musician_id INT,
last_name CHAR(40),
first_name CHAR(40),
nickname CHAR(40));

 

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name [(create_definition,...)]
[table_options] [select_statement]
      

or:

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name [(] LIKE old_tbl_name [)];

create_definition:
    col_name type [NOT NULL | NULL] [DEFAULT default_value] [AUTO_INCREMENT]
            [[PRIMARY] KEY] [COMMENT 'string'] [reference_definition]
  | [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name,...)
  | KEY [index_name] [index_type] (index_col_name,...)
  | INDEX [index_name] [index_type] (index_col_name,...)
  | [CONSTRAINT [symbol]] UNIQUE [INDEX] [index_name] [index_type] (index_col_name,...)
  | FULLTEXT [INDEX] [index_name] (index_col_name,...)
  | [CONSTRAINT [symbol]] FOREIGN KEY [index_name] (index_col_name,...)
            [reference_definition]
  | CHECK (expr)

type:
    TINYINT[(length)] [UNSIGNED] [ZEROFILL]
  | SMALLINT[(length)] [UNSIGNED] [ZEROFILL]
  | MEDIUMINT[(length)] [UNSIGNED] [ZEROFILL]
  | INT[(length)] [UNSIGNED] [ZEROFILL]
  | INTEGER[(length)] [UNSIGNED] [ZEROFILL]
  | BIGINT[(length)] [UNSIGNED] [ZEROFILL]
  | REAL[(length,decimals)] [UNSIGNED] [ZEROFILL]
  | DOUBLE[(length,decimals)] [UNSIGNED] [ZEROFILL]
  | FLOAT[(length,decimals)] [UNSIGNED] [ZEROFILL]
  | DECIMAL(length,decimals) [UNSIGNED] [ZEROFILL]
  | NUMERIC(length,decimals) [UNSIGNED] [ZEROFILL]
  | CHAR(length) [BINARY | ASCII | UNICODE]
  | VARCHAR(length) [BINARY]
  | DATE
  | TIME
  | TIMESTAMP
  | DATETIME
  | TINYBLOB
  | BLOB
  | MEDIUMBLOB
  | LONGBLOB
  | TINYTEXT
  | TEXT
  | MEDIUMTEXT
  | LONGTEXT
  | ENUM(value1,value2,value3,...)
  | SET(value1,value2,value3,...)

index_col_name:
        col_name [(length)] [ASC | DESC]

reference_definition:
        REFERENCES tbl_name [(index_col_name,...)]
                   [MATCH FULL | MATCH PARTIAL]
                   [ON DELETE reference_option]
                   [ON UPDATE reference_option]

reference_option:
        RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT

table_options: table_option [table_option] ...

table_option:
    {ENGINE | TYPE} = {BDB | HEAP | ISAM | InnoDB | MERGE | MRG_MYISAM | MYISAM}
  | AUTO_INCREMENT = #
  | AVG_ROW_LENGTH = #
  | CHECKSUM = {0 | 1}
  | COMMENT = 'string'
  | MAX_ROWS = #
  | MIN_ROWS = #
  | PACK_KEYS = {0 | 1 | DEFAULT}
  | PASSWORD = 'string'
  | DELAY_KEY_WRITE = {0 | 1}
  | ROW_FORMAT = { DEFAULT | DYNAMIC | FIXED | COMPRESSED }
  | RAID_TYPE = { 1 | STRIPED | RAID0 } RAID_CHUNKS=#  RAID_CHUNKSIZE=#
  | UNION = (table_name,[table_name...])
  | INSERT_METHOD = { NO | FIRST | LAST }
  | DATA DIRECTORY = 'absolute path to directory'
  | INDEX DIRECTORY = 'absolute path to directory'
  | DEFAULT CHARACTER SET character_set_name [COLLATE collation_name]

select_statement:
    [IGNORE | REPLACE] [AS] SELECT ...     (Some legal select statement)

FOREIGN CONSTRAINTS

[CONSTRAINT symbol] FOREIGN KEY [id] (index_col_name, ...) 
REFERENCES tbl_name (index_col_name, ...) 
[ON DELETE {CASCADE | SET NULL | NO ACTION | RESTRICT}] 
[ON UPDATE {CASCADE | SET NULL | NO ACTION | RESTRICT}]

 

 CREATE TABLE parent(id INT NOT NULL, PRIMARY KEY (id) ) TYPE=INNODB;

 CREATE TABLE child(id INT, parent_id INT, INDEX par_ind (parent_id), 
  FOREIGN KEY (parent_id) REFERENCES parent(id) ON DELETE CASCADE ) ENGINE=INNODB;

CREATE TABLE product (category INT NOT NULL, id INT NOT NULL,
   price DECIMAL,
   PRIMARY KEY(category, id)) ENGINE=INNODB;
   
CREATE TABLE customer (id INT NOT NULL, PRIMARY KEY (id)) ENGINE=INNODB;
CREATE TABLE product_order (no INT NOT NULL AUTO_INCREMENT, product_category INT NOT NULL, product_id INT NOT NULL, customer_id INT NOT NULL, PRIMARY KEY(no), INDEX (product_category, product_id), FOREIGN KEY (product_category, product_id) REFERENCES product(category, id) ON UPDATE CASCADE ON DELETE RESTRICT, INDEX (customer_id), FOREIGN KEY (customer_id) REFERENCES customer(id)) ENGINE=INNODB;

 

DROP

DROP [TEMPORARY] TABLE [IF EXISTS] tbl_name [, tbl_name,...] [RESTRICT | CASCADE]

ALTER

ALTER TABLE t1 MODIFY b BIGINT NOT NULL;

 

ALTER [IGNORE] TABLE tbl_name alter_specification [, alter_specification] ...

alter_specification:
    ADD [COLUMN] create_definition [FIRST | AFTER column_name ]
  | ADD [COLUMN] (create_definition, create_definition,...)
  | ADD INDEX [index_name] [index_type] (index_col_name,...)
  | ADD [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name,...)
  | ADD [CONSTRAINT [symbol]] UNIQUE [index_name] [index_type] (index_col_name,...)
  | ADD FULLTEXT [index_name] (index_col_name,...)
  | ADD [CONSTRAINT [symbol]] FOREIGN KEY [index_name] (index_col_name,...)
           [reference_definition]
  | ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}
  | CHANGE [COLUMN] old_col_name create_definition
           [FIRST | AFTER column_name]
  | MODIFY [COLUMN] create_definition [FIRST | AFTER column_name]
  | DROP [COLUMN] col_name
  | DROP PRIMARY KEY
  | DROP INDEX index_name
  | DISABLE KEYS
  | ENABLE KEYS
  | RENAME [TO] new_tbl_name
  | ORDER BY col
  | CHARACTER SET character_set_name [COLLATE collation_name]
  | table_options
      

DATABASE

CREATE DATABASE [IF NOT EXISTS] db_name
    [create_specification [, create_specification] ...]

create_specification:
    [DEFAULT] CHARACTER SET charset_name
  | [DEFAULT] COLLATE collation_name
DROP DATABASE db_name 

 

GRANTS

GRANT priv_type [(column_list)] [, priv_type [(column_list)] ...]
    ON {tbl_name | * | *.* | db_name.*}
    TO user_name [IDENTIFIED BY [PASSWORD] 'password']
        [, user_name [IDENTIFIED BY [PASSWORD] 'password'] ...]
    [REQUIRE
        NONE |
        [{SSL| X509}]
        [CIPHER cipher [AND]]
        [ISSUER issuer [AND]]
        [SUBJECT subject]]
    [WITH [GRANT OPTION | MAX_QUERIES_PER_HOUR # |
                          MAX_UPDATES_PER_HOUR # |
                          MAX_CONNECTIONS_PER_HOUR #]]
      
REVOKE priv_type [(column_list)] [, priv_type [(column_list)] ...]
    ON {tbl_name | * | *.* | db_name.*}
    FROM user_name [, user_name ...]

REVOKE ALL PRIVILEGES,GRANT OPTION FROM user_name [, user_name ...]
 


Ejemplos en Postgresql:

Grant insert privilege to all users on table films :

GRANT INSERT ON films TO PUBLIC;

Grant all privileges to user manuel on view kinds :

GRANT ALL PRIVILEGES ON kinds TO manuel;

Ejemplos en Mysql

To revoke the GRANT OPTION privilege from a user, use a priv_type value of GRANT OPTION:

mysql> REVOKE GRANT OPTION ON ... FROM ...;


priv_type

ALL [PRIVILEGES] Sets all simple privileges except GRANT OPTION
ALTER Allows use of ALTER TABLE
CREATE Allows use of CREATE TABLE
CREATE TEMPORARY TABLES Allows use of CREATE TEMPORARY TABLE
DELETE Allows use of DELETE
DROP Allows use of DROP TABLE
EXECUTE Allows the user to run stored procedures (MySQL 5.0)
FILE Allows use of SELECT ... INTO OUTFILE and LOAD DATA INFILE
INDEX Allows use of CREATE INDEX and DROP INDEX
INSERT Allows use of INSERT
LOCK TABLES Allows use of LOCK TABLES on tables for which you have the SELECT privilege
PROCESS Allows use of SHOW FULL PROCESSLIST
REFERENCES Not yet implemented
RELOAD Allows use of FLUSH
REPLICATION CLIENT Gives the right to the user to ask where the slave or master servers are
REPLICATION SLAVE Needed for replication slaves (to read binary log events from the master)
SELECT Allows use of SELECT
SHOW DATABASES SHOW DATABASES shows all databases
SHUTDOWN Allows use of mysqladmin shutdown
SUPER Allows use of CHANGE MASTER, KILL thread, PURGE MASTER LOGS, and SET GLOBAL statements, the mysqladmin debug command; allows you to connect (once) even if max_connections is reached
UPDATE Allows use of UPDATE
USAGE Synonym for ``no privileges''
GRANT OPTION Allows privileges to be granted

MySQL has four privilege levels:

Global level
Global privileges apply to all databases on a given server. These privileges are stored in the mysql.user table. GRANT ALL ON *.* and REVOKE ALL ON *.* will grant and revoke only global privileges.
Database level
Database privileges apply to all tables in a given database. These privileges are stored in the mysql.db and mysql.host tables. GRANT ALL ON db.* and REVOKE ALL ON db.* will grant and revoke only database privileges.
Table level
Table privileges apply to all columns in a given table. These privileges are stored in the mysql.tables_priv table. GRANT ALL ON db.table and REVOKE ALL ON db.table will grant and revoke only table privileges.
Column level
Column privileges apply to single columns in a given table. These privileges are stored in the mysql.columns_priv table. When using REVOKE you must specify the same columns that were granted.
 
 
 

VIEWS

CREATE [ OR REPLACE ] VIEW name [ ( column_name [, ...] ) ] AS query

CREATE VIEW comedies AS
SELECT * FROM films WHERE kind = 'Comedy';

 

5.3 QUEL

5.3.1 Antecedentes

  • Desarrollado por M. Stonebraker en 1976
  • Lenguaje original de "ingres"
  • Basado en el cálculo relacional de tuplas

5.3.2 Componentes

  • Declaración de variables tuplas

    range of t is r

  • recuperación de tuplas

    retrieve (ti.aj... )

  • filtrado

    where P

  • no se permiten queries anidados
  • no provee unión, intersección ni resta.

5.3.2.1 Ejemplos básicos

nombres de estudiantes de ing. en sistemas

range of e is estudiantes
retrieve unique (e.nombre)
where e.carrera='is'

datos de estudiantes de ing. en sistemas

range of e is estudiantes
retrieve (e.all)
where e.carrera='is'

nombres de estudiantes que han reprobado

range of e is estudiantes
range of c is est_cursos
retrieve unique (e.nombre)
where e.id=c.id and c.calif < 7.5

5.3.3 Funciones agregadas: count,sum, max, avg, min

formato

agregado(t.a)
agregado(t.a where P)
agregado(t.a by s.b1, s.b2,..s.bn where P)

5.3.3.1 Ejemplos

Promedio de calificaciones del depto de is

range of t is est_cursos
retrieve avg(t.calif where depto='is')

id de estudiantes con alguna calificacion mayor al promedio

range of e is est_cursos
range of s is est_cursos
retrieve unique(e.id)
where e.calif > avg(s.calif)

5.3.4 Quel tambien posee la manera de agregar, actualizar y eliminar tuplas.

eliminar los estudiantes de is con id menor a 123456

range of t is estudiantes
delete t
where t.id < 123456

5.4 Query by example QBE

5.4.1 Antecedentes

  • Desarrollo de ibm en los 70s
  • Ejemplo de programación visual
  • Sintáxis bidimensional
  • Genera consultas a partir de ejemplos
  • Relación directa con cálculo relacional de tuplas

5.4.2 Estructura

  • plantillas de tablas con renglones
  • variables de dominio (_x, _y, etc)
  • comandos, palabras clave (P., ALL.,...)

5.4.3 Ejemplos

a) id y nombre de los estudiantes de ingenieria en sistemas

estudiantes id nombre carrera
  P._x P._y is

b) todos los datos de todos los estudiantes

estudiantes id nombre carrera
  P._x P._y P._z

o bien

estudiantes id nombre carrera
P.      

c) nombres de los estudiantes de is incluyendo duplicados

estudiantes id nombre carrera
    P.ALL. is

d) ids de estudiantes que han tomado los cursis is 441 y is 323

est_cursos id depto num calif
  P._x is 441  
  _x is 323  

e) nombres de estudiantes de ing en sistemas que han reprobado algun curso

estudiantes id nombre carrera
  _x P.ALL. is

 

est_cursos id depto num calif
  _x     < 7.5

f) ids de estudiantes que han tomado cursos con el estudiante 777

est_cursos id depto num calif
  777 _y _z  
  P._x _y _z  

g) nombres de estudiantes que no son empleados

estudiantes id nombre carrera
  _x P.ALL.  

est_empl id nombre area carrera
¬ _x      

promedio de calificaciones del estudiante 777 en cursos que no son de is

est_cursos id depto num calif
  777 ¬ is   P.AVG.ALL.

5.4.4 QBE en Microsoft Access