5.Lenguaje de Bases de Datos

7/21/2019 5.Lenguaje de Bases de Datos

1/17

TEORIA Y DISEO DE BASE DE DATOS

[email protected] Pgina 1

5. Lenguajes de bases de datos

5.1 Introduccin

Los lenguajes de consulta (query language) son especificaciones formales pararepresentar consultas. An cuando son llamados de "consulta" en realidadpueden hacer mucho ms que consultas.

5.2 Structured Query Language (SQL)

Creado por IBM alrededor de los aos 70s Combinacin de lgebra relacional y clculo 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_nameSET col_name=(expression | DEFAULT), ...[ ON DUPLICATE KEY UPDATE

col_name=expression, ... ]

UPDATE

update table_nameset column_name= value,

...,column_name=value


2/17



where column_name=value;update albumsset year=1994where album_id=4;update albums

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

UPDATE [LOW_PRIORITY] [IGNORE] tbl_nameSET 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_namewhere column_name=value

delete from albumswhere albums_id=4;

DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROMtable_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]


3/17



SELECT

select column_name, ..., column_name

from table_namewhere column_name=value;select titlefrom albumswhere 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 OFFSEToffset]

[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_namefrom bands,albumswhere albums.category='alternative'and bands.band_id=albums.band_id;

SELECT t1.name, t2.salary FROM employee AS t1, info ASt2 -> WHERE t1.name = t2.name;

SELECT table1.* FROM table1 -> LEFT JOIN table2 ONtable1.id=table2.id -> WHERE table2.id IS NULL;


4/17



table_reference, table_referencetable_reference [INNER | CROSS] JOIN table_reference[join_condition]table_reference STRAIGHT_JOIN table_referencetable_reference LEFT [OUTER] JOIN table_reference[join_condition]

table_reference NATURAL [LEFT [OUTER]] JOINtable_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]] JOINtable_reference

UNION

(SELECT a FROM table_name WHERE a=10 AND B=1ORDER BY a LIMIT 10) UNION (SELECT a FROMtable_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 titlefrom albums,where band_id in(select bands.band_idfrom bands, band_musicianwhere 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]


5/17


6/17



(carlos) [prueba]> create index iname onauser(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; Usingindex |

+----+-------------+-------+------+---------------+-------+---------+-------+------+--------------------------+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,


7/17



...,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


8/17



| 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 |


9/17



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, INDEXpar_ind (parent_id),FOREIGN KEY (parent_id) REFERENCES parent(id) ON

DELETE CASCADE ) ENGINE=INNODB;

CREATE TABLE product (category INT NOT NULL, id INTNOT 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 NULLAUTO_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 | AFTERcolumn_name ]


10/17



| 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]]


11/17


12/17



INDEX

INSERT Allows use of INSERT

LOCK TABLES Allows use of LOCK TABLESon tables forwhich you have the SELECTprivilege

PROCESS Allows use of SHOW FULL PROCESSLIST

REFERENCES Not yet implemented

RELOAD Allows use of FLUSH

REPLICATIONCLIENT

Gives the right to the user to ask wherethe slave or master servers are

REPLICATIONSLAVE

Needed for replication slaves (to readbinary log events from the master)

SELECT Allows use of SELECT

SHOWDATABASES SHOW DATABASESshows all databases

SHUTDOWN Allows use of mysqladmin shutdown

SUPER

Allows use of CHANGE MASTER, KILLthread, PURGE MASTER LOGS, and SETGLOBALstatements, the mysqladmin debugcommand; allows you to connect (once)even if max_connectionsis reached

UPDATE Allows use of UPDATE

USAGE Synonym for ``no privileges''GRANT OPTION Allows privileges to be granted

MySQL has four privilege levels:

Global levelGlobal privileges apply to all databases on a givenserver. These privileges are stored in the mysql.usertable. GRANT ALL ON *.*and REVOKE ALL ON *.*willgrant and revoke only global privileges.

Database levelDatabase privileges apply to all tables in a givendatabase. These privileges are stored in themysql.dband mysql.hosttables. GRANT ALL ON db.*and REVOKE ALL ON db.*will grant and revoke onlydatabase privileges.

Table levelTable privileges apply to all columns in a given table.These privileges are stored in themysql.tables_privtable. GRANT ALL ON db.table

and REVOKE ALL ON db.tablewill grant and revoke


13/17



only table privileges.Column level

Column privileges apply to single columns in a giventable. These privileges are stored in the

mysql.columns_privtable. When using REVOKEyoumust specify the same columns that were granted.

VIEWS

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

CREATE VIEW comedies ASSELECT * 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 clculo relacional de tuplas

5.3.2 Componentes

Declaracin de variables tuplas

range of t is r

recuperacin de tuplas

retrieve (ti.aj... )

filtrado

where P

no se permiten queries anidados no provee unin, interseccin ni resta.

5.3.2.1 Ejemplos bsicos

nombres de estudiantes de ing. en sistemas


14/17



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

datos de estudiantes de ing. en sistemas

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

nombres de estudiantes que han reprobado

range of e is estudiantesrange of c is est_cursosretrieve 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_cursosretrieve avg(t.calif where depto='is')

id de estudiantes con alguna calificacion mayor al promedio

range of e is est_cursosrange of s is est_cursosretrieve unique(e.id)where e.calif > avg(s.calif)

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

eliminar los estudiantes de is con id menor a 123456

range of t is estudiantesdelete twhere t.id < 123456


15/17



5.4 Query by example QBE

5.4.1 Antecedentes

Desarrollo de ibm en los 70s Ejemplo de programacin visual Sintxis bidimensional Genera consultas a partir de ejemplos Relacin directa con clculo 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


P._x P._y P._z

o bien


P.

c) nombres de los estudiantes de is incluyendo duplicados


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


16/17



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


_x P.ALL. is


_x < 7.5

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


777 _y _z

P._x _y _z

g) nombres de estudiantes que no son empleados


_x P.ALL.

est_empl id nombre area carrera

_x

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


777 is P.AVG.ALL.

5.4.4 QBE en Microsoft Access


17/17



5.Lenguaje de Bases de Datos

Documents

Transcript of 5.Lenguaje de Bases de Datos