SQL

¿Qué es SQL?

   SQL – Structured Query Language – – Lenguaje de consulta estructurado “Órdenes” SQL Lenguaje de acceso a bases de datos relacionales Es un estándar para sistemas de gestión de bases de datos relacionales – – Oracle, Sybase, Microsoft SQL Server, Access, Ingres, … “Extensiones propietarias” SQL

Clasificación de sentencias SQL

   DDL (Data Description Language) – Órdenes para definir, modificar o borrar tablas y relaciones entre ellas DCL (Data Control Language) – Elementos para trabajar en entorno multiusuario donde es importante la protección de datos, seguridad, restricciones de acceso, compartición e integridad de datos DML (Data Manipulation Language) – Recuperación de datos, actualización de datos (añadir, suprimir o modificar) SQL

Sintaxis

  Verbo + cláusulas (obligatorias y opcionales) DIAGRAMA SINTÁCTICO Ejemplos válidos SELECT ALL col1,col2,col3 FROM mitabla; SELECT col1,col2,col3 FROM mitabla; SELECT DISTINCT col1 FROM mitabla; SELECT col1,col2 FROM mitabla WHERE col2=0 SQL

select

  La orden select se usa para consultar la base de datos y recuperar datos que cumplan el criterio que se especifique Formato: – Select “column1”[,”column2”,etc] from “tablename” [where “condition”] – Columnas que se devolverán en los resultados “ * ” implica Todas Condiciones que se pueden usar:  =, >, <, >=, <=, <>  LIKE – – – Ejemplo: select nombre, apellido, ciudad from listado where nombre LIKE ‘Je%’; % es un comod ín que significa “cualquier carácter” Más ejemplos:  select nombre, apellido from listado where apellido LIKE ‘%s’;  select * from listado where nombre=‘Jesús’; SQL

Ejercicio

  Tabla empinfo Primero averiguar el resultado y luego ejecutarlo – select nombre, apellido, ciudad from empinfo; – – – – – – select apellido, ciudad, edad from empinfo where edad > 30; select nombre, apellido, ciudad, pais from empinfo where nombre LIKE 'J%'; select * from empinfo; select nombre, apellido from empinfo where apellido LIKE '%z'; select nombre, apellido, edad from empinfo where apellido LIKE '%áre%'; select * from empinfo where nombre = 'Enrique';

nombre

Juan María

apellido

Juárez Juárez Enrique Esteban María Antonia Esteban Beatriz Sebastián Hernández Suárez Gustavo García María Antonia Martínez Erica Leandro Ernesto Zamora Boada Clavel SQL

id

99980 99982 88232 88233 98002 92001 22322 32326 32327 32380 32382

edad ciudad

45 Palencia 25 Palencia

pais

España España 32 Oporto 32 Pontevedra Portugal España 42 Córdoba España 23 Guadalajara España 35 Badajoz 52 Toledo 60 Santander 22 Peñafiel 22 Gandía España España España España España

Ejercicios

 Escribir órdenes select para: 1.

2.

3.

4.

5.

6.

Mostrar el nombre y la edad de todos los de la tabla Mostrar el nombre, apellido y ciudad de todos los que no son de Palencia Mostrar todas las columnas para todos los que son mayores de 40 años Mostrar el nombre y apellido de todos cuyo apellido termina en “a” Mostrar todas las columnas para todos los que su nombre es igual a “María” Mostrar todas las columnas para todos los que su nombre contiene “María”

nombre

Juan María

apellido

Juárez Juárez Enrique Esteban María Antonia Esteban Beatriz Sebastián Hernández Suárez Gustavo García María Antonia Martínez Erica Leandro Ernesto Zamora Boada Clavel SQL

id

99980 99982 88232 88233 98002 92001 22322 32326 32327 32380 32382

edad ciudad

45 Palencia 25 Palencia

pais

España España 32 Oporto 32 Pontevedra Portugal España 42 Córdoba España 23 Guadalajara España 35 Badajoz 52 Toledo 60 Santander 22 Peñafiel 22 Gandía España España España España España

Creación de tablas

   Qué campos la forman De qué tipo van a ser (entero, cadena, fecha, etc) – – – – – – – – – VARCHAR(NMaxChar)  Cadena de 1 a NMaxChar caracteres. NMaxChar entre 1 y 255 CHAR(NMaxChar)  Siempre ocupa NMaxChar caracteres en memoria Las cadenas se escriben entre comillas ‘simples’ INTEGER  Entero de 32 bits SMALLINT  Entero de 16 bits NUMERIC(x,y) o NUMBER(x,y)  Número real con ‘x’ dígitos, ‘y’ de las cuales son decimales DATE  Fecha TIME  Hora TIMESTAMP  Fecha & Hora Restricciones (no nulo, por ejemplo) SQL

create table

    La orden create table se usa para crear una nueva tabla Formato: – create table “tablename” (“column1” “data type” [constraint], “column2” “data type” [constraint], “column3” “data type” [constraint]); – Ejemplo:  create table empleado (nombre varchar(15), apellido varchar(20), edad number(3), direccion varchar(30), ciudad varchar(20), pais varchar(20)); Restricciones (constraints) – Regla asociada con una columna cuyos datos deben seguir    “unique”: No puede haber dos registros con el mismo valor para esa columna “not null”: No puede dejarse vacía esa columna “primary key”: Define una identificación única para cada registro Ejercicio – Crear una tabla de empleados con: nombre, apellido, categoría, edad y salario SQL

insert

  La orden insert se usa para insertar o añadir una fila de datos en la tabla Formato: – insert into “tablename” (first_column, …, last_column) values (first_value, …, last_value); – Ejemplo:  insert into empleado (nombre, apellido, edad, direccion, ciudad, pais) values (‘Juan’, ‘García’, 45, ‘c/ Soto, 12’, ‘Valladolid’, ‘España’); SQL

Ejercicio

  Inserta en la tabla de empleados los siguientes 3 y otros 3 más inventados por ti: – Juana García, Secretaria, 28, 19500.00

– Pablo García, Programador, 32, 45300.00

– Daniel Alvarez, Programador II, 45, 75020.00

Después de insertarlos, escribe órdenes select para lo siguiente: 1.

Selecciona todas las columnas para todos los empleados de la tabla 2.

3.

4.

5.

6.

7.

8.

Selecciona todas las columnas para todos los que tengan un salario mayor de 30000 Selecciona nombre y apellido para todos los que tengan menos de 30 años Selecciona nombre, apellido y salario para todos los que tengan la categoría de “Programador” Selecciona todas las columnas para todos los que su apellido contenga “ar” Selecciona el nombre para todos los que su nombre sea igual a “Pablo” Selecciona todas las columnas para todos los que tengan más de 80 años Selecciona todas las columnas para todos los que su apellido termine en “ez” SQL

update

  La orden update se usa para actualizar o cambiar registros que cumplen un determinado criterio Formato: – update “tablename” set “columname”=“newvalue” [,”nextcolumn”=“newvalue2”…] where “columname” OPERATOR “value” [and|or “column” OPERATOR “value”]; – Ejemplos:    update agenda set codigo_postal=47 where prefijo=983; update agenda set apellido=‘García’, prefijo=983 where apellido=‘Rodríguez’; update empleado set edad=edad+1 where nombre=‘María’ and apellido=‘Pérez’; SQL

Ejercicio

  Despu és de cada update, ejecuta un select para verificar los cambios Realiza: 1.

2.

3.

Juana García se acaba de casar con Benito González. Ella ha solicitado que se cambie su apellido por González El cumpleaños de Daniel Alvarez es hoy; añade 1 a su edad Todas las secretarias pasan a llamarse ahora “Asistente administrativo” 4.

Todos los que ganan por debajo de 30000 reciben un incremento anual de 3500 5.

6.

7.

Todos los que ganan por encima de 33500 reciben un incremento anual de 4500 Todos los de la categoría “Programador II” son promocionados a “Programador III” Todos los de la categoría “Programador” son promocionados a “Programador II” SQL

delete

  La orden delete se usa para borrar registros (o filas) de una tabla Formato: – delete from “tablename” where “columname” OPERATOR “value” [and|or “column” OPERATOR “value”]; – Ejemplos:   delete from empleado; ¡Se borran todos los registros!

delete from empleado where apellido=‘Pérez’;  delete from empleado where nombre=‘Juan’ or nombre=‘Luis’; SQL

Ejercicios

  Usa el comando select para verificar las operaciones de borrado Realiza estos 2, y después, un borrado de todos los registros de la tabla 1.

2.

Juana González ha cesado; elimina su registro de la tabla Época de recorte de presupuesto. Elimina todos los empleados que están ganando más de 70000 SQL

drop table

   La orden drop table se usa para borrar una tabla y todas las filas de la tabla Formato: – drop table “tablename” – ejemplo:  drop table empleado; ¡Se borra la definición de la tabla!

SQL

select avanzado

   select [all | distinct] column1[,column2] from table1[,table2] [where “conditions”] [group by “column list”] [having “conditions”] [order by “column-list” [asc | desc] ]; Por defecto es ALL. DISTINCT descarta registros duplicados para las columnas que se especifiquen Ejemplo: – SELECT DISTINCT edad FROM empinfo; devuelve las edades “únicas” de la tabla SQL

Tablas para ejercicios

clientes

clienteid nombre

10101 Juan 10298 Leandro 10299 Ernesto 10315 Lucía 10325 Bárbara 10329 Carmen 10330 Sancho 10338 Miguel 10339 Antonio 10408 Enrique

apellido

García Boada Calvo Juárez Suárez Mendoza Aragón Hernández Sánchez Clavel 10410 María Antonia Hernández 10413 Eduardo Aznar 10419 Linda 10429 Sara Sevilla García 10438 Clemente 10439 Conrado 10449 Isabel García Martín Martín

ciudad

Lugo Palencia Santander Oviedo Pontevedra Calatayud Cuenca Teruel Pinto Gandía Cuenca Madrid Villalba Sevilla Durango Bilbao Madrid

provincia

Lugo Palencia Santander Oviedo Pontevedra Zaragoza Cuenca Teruel Madrid Valencia Cuenca Madrid Madrid Sevilla Vizcaya Vizcaya Madrid pedidos

clienteid

10330 10101 10298 10101 10299 10339 10449 10439 10101 10449 10439 10298 10410 10438 10298 10449 10101 10330 10101 10299 10438 10413 10410 10315 10449 10101 10298 10449 10298 10330

fecha item

30-jun-1999 Palo de golf 30-jun-1999 Balsa 1-jul-1999 Monopatín 1-jul-1999 Chaleco salvavidas 6-jul-1999 Paracaídas 27-jul-1999 Paraguas 13-ago-1999 Monociclo 14-ago-1999 Palos de esquí 18-ago-1999 Impermeable 1-sep-1999 Botas para nieve 18-sep-1999 Tienda 19-sep-1999 Linterna 28-oct-1999 Saco de dormir 2-nov-1999 Almohada 1-dic-1999 Casco 15-dic-1999 Bicicleta 30-dic-1999 Hoola Hoop 1-ene-2000 Linterna flash 2-ene-2000 Linterna 18-ene-2000 Colchón hinchable 18-ene-2000 Tienda 19-ene-2000 Tumbona 30-ene-2000 Monociclo 2-feb-2000 Compás 29-feb-2000 Linterna flash 8-mar-2000 Saco de dormir 18-mar-2000 Navaja 19-mar-2000 Remo 1-abr-2000 Orejeras 19-abr-2000 Pala

cantidad

1 1 1 4 1 1 1 2 1 1 1 2 1 1 1 1 3 4 1 2 1 1 1 1 1 4 1 1 1 2

precio

28.00

58.00

33.00

125.00

1250.00

4.50

180.79

25.50

18.30

45.00

88.00

29.00

89.22

8.50

22.00

380.50

14.75

28.00

16.00

38.00

79.99

32.00

192.50

8.00

4.50

88.70

22.38

40.00

12.50

16.75

SQL

Ejercicios de repaso

1.

2.

3.

4.

5.

De la tabla pedidos, selecciona una lista de todos los items comprados por el clienteid 10449. Muestra el clienteid, item, y precio Selecciona todas las columnas de la tabla pedidos para cualquiera que haya comprado una Tienda Selecciona el clienteid, fecha e item de la tabla pedidos para cualquier item que empiece por la letra “P” Selecciona items distintos de la tabla pedidos. Realiza tus propias órdenes select y ejecútalas SQL

Funciones de agregación

  Devuelven información de una columna – – – MIN: valor más pequeño de una columna MAX: valor más grande de una columna SUM: suma de los valores numéricos de una columna – – – AVG: media de una columna COUNT: número total de valores de una columna COUNT(*): número de filas de una tabla Ejemplos: – – – select avg(salario) from empleado; select avg(salario) from empleado where categoria=‘Programador’ select count(*) from empleado; SQL

Ejercicios

1.

2.

3.

4.

Selecciona el precio máximo de cualquier item de la tabla pedidos Selecciona el precio medio de los items que fueron comprados en el mes de diciembre ¿Cual es el número total de filas de la tabla pedidos? Para todas las tiendas de la tabla pedidos, ¿cual es el precio de la más barata?

SQL

Cláusula GROUP BY

   La cláusula GROUP BY reúne todas las filas que contienen datos en las columnas especificadas y permite ejecutar funciones de agregación en una o más columnas Formato ejemplo: – select column1,SUM(column2) from “list-of-tables” group by “column list” Ejemplo: – – – – SELECT max(salario), dept FROM empleados GROUP BY dept; Lista de los salarios más altos de

cada

departamento La salida serán los salarios y los departamentos correspondientes SELECT cantidad, max(precio) FROM pedidos GROUP BY cantidad; SQL

Ejercicios

1.

2.

3.

¿Cuántas personas hay de cada provincia en la tabla clientes? Muestra las provincias junto con el número de personas en cada uno De la tabla pedidos, selecciona el item, precio máximo, y precio mínimo para cada item ¿Cuántos pedidos hizo cada cliente? Usa la tabla pedidos. Selecciona el clienteid, número de pedidos que hicieron, y la suma de sus pedidos SQL

Cláusula HAVING

  Permite especificar condiciones para las filas de cada grupo. Va después de GROUP BY Formato: – SELECT column1, SUM(column2) FROM "list-of tables“ GROUP BY "column-list" HAVING "condition"; – Ejemplo:  SELECT dept, avg(salario) FROM empleados GROUP BY dept HAVING avg(salario) > 20000; SQL

Ejercicios

1.

2.

3.

¿Cuántas personas hay en cada provincia en la tabla clientes que tienen más de una persona en la provincia?

De la tabla pedidos, selecciona el item, precio máximo, y precio mínimo para cada item de la tabla. Muestra sólo los resultados si el precio máximo del item es mayor que 190 ¿Cuántos pedidos hizo cada cliente? Usa la tabla pedidos. Selecciona el clienteid, número de pedidos que hicieron, y la suma de sus pedidos si compraron más de 1 item SQL

Cláusula ORDER BY

   Permite ordenar los resultados de una consulta, ordenados por las columnas que se especifique Por defecto es orden ascendente (ASC) Formato: – SELECT column1, SUM(column2) FROM "list-of tables“ ORDER BY "column-list" [ASC | DESC]; – Ejemplo:  SELECT empleado_id, dept, nombre, edad, salario FROM empleados WHERE dept = ‘Ventas' ORDER BY salario;  SELECT empleado_id, dept, nombre, edad, salario FROM empleados WHERE dept = 'Ventas' ORDER BY salario, edad DESC; SQL

Ejercicios

1.

2.

3.

Selecciona el apellido, nombre y ciudad de todos los clientes de la tabla clientes. Muestra los resultados en orden ascendente basado en el apellido Lo mismo, pero en orden descendente Selecciona el item y precio para cada uno de los items de la tabla pedidos en los que el precio sea mayor que 10. Muestra los resultados en orden ascendente basado en el precio SQL

Ejercicios de repaso (combinando condiciones)

1.

2.

Selecciona el clienteid, fecha de pedido, e item de la tabla pedidos para todos los items excepto si son ‘Botas para nieve’ o ‘Orejeras’. Muestra dichas filas Selecciona el item y el precio de todos los items que empiecen por las letras ‘S’, 'P', o ‘L' SQL

Operador condicional IN

   El operador IN se utiliza para comprobar si un valor está contenido en una lista de valores Formato: – SELECT column1, SUM(column2) FROM "list-of tables“ WHERE column3 IN (list-of-values); – Ejemplo:   SELECT empleadoid, apellido, salario FROM empleados WHERE apellido IN ('Hernandez', ‘Juárez', ‘González', 'Ruiz'); Se podría haber escrito con varios OR. Sería equivalente Se puede utilizar NOT IN para excluir filas SQL

Operador condicional BETWEEN

   Se utiliza para comprobar si un valor está comprendido entre dos valores Formato: – SELECT column1, SUM(column2) FROM "list-of tables“ WHERE column3 BETWEEN value1 AND value2; – Ejemplo:   SELECT empleadoid, edad, apellido, salario FROM empleados WHERE edad BETWEEN 30 AND 40; Se podría haber utilizado también <= y >=. Sería equivalente También se puede utilizar NOT BETWEEN para excluir valores dentro de un rango SQL

Ejercicios

1.

2.

Selecciona la fecha, item, y precio de la tabla pedidos para todas las filas que tengan un precio entre 10 y 80 Selecciona el nombre, ciudad, y provincia de la tabla clientes para todas las filas donde la provincia sea: Madrid, Vizcaya, Valencia, Palencia, o Zaragoza SQL

Funciones matemáticas

    – El estándar ANSI SQL-92 soporta los siguientes operadores: +, -, *, /, % (m ódulo) Realmente, el operador módulo no está en el estándar, pero tanto él como las de la tabla suelen estar incluidas en la mayoría de los gestores de bases de datos – Ejemplo: SELECT round(salario), nombre FROM empleados Ejercicio: 1.

Selecciona el item y el precio por unidad para cada item de la tabla pedidos SQL

Uniones de tablas

   Hasta ahora no se ha usado la potencia de las bases de datos

relacionales

, ya que sólo utilizábamos una única tabla. Las uniones nos permiten realizar consultas sobre dos o más tablas “relacionándolas” Formato: – SELECT "list-of-columns" FROM table1,table2 WHERE "search condition(s)“ – Ejemplo:  SELECT cliente_info.nombre, cliente_info.apellido, compras.item FROM cliente_info, compras WHERE cliente_info. cliente _numero = compras. cliente _ numero; La columna común sirve para “unir” las tablas SQL

Ejercicios

1.

2.

Escribe una consulta usando una unión para determinar qué items fueron pedidos por cada cliente de la tabla clientes. Selecciona el clienteid, nombre, apellido, fecha de pedido, item, y precio de todo lo que compró cada cliente en la tabla pedidos Repite lo anterior mostrando los resultados ordenados por provincia en orden descendente SQL

ODBC

 Open DataBase Connectivity – Application Programming Interface (API) – – – – Permite a un programador escribir un programa sin tener en consideración el tipo particular de base de datos El lenguaje ODBC es una combinación de llamadas a funciones API de ODBC y el lenguaje SQL Se necesita un driver ODBC específico para la base de datos que se esté usando En definitiva, es un mediador que facilita la programación de una aplicación que utiliza datos contenidos en una base de datos SQL