Plantilla:PHP/SQL

Contenido

1 SQL
2 SELECT
- 2.1 Claúsula Select
- 2.2 Funciones de agregación o grupo
3 From
4 Where
- 4.1 Ejercicios
5 Concepto de join o reunión natural

SQL

SQL es un lenguaje de consultas estructuradas o Structured Query Language. Es un lenguaje de 4º generación donde el programador o usuario del lenguaje especifica lo qué quiere, pero no establece el procedimiento a seguir para conseguirlo, no dice cómo ha de hacer el sistema para conseguirlo

Lenguaje no procedural Vs lenguaje procedural)

Con lenguaje no procedural (tipo SQL)

Dime todos los empleados de más de 45 años
o 
SELECT *
FROM empleados
WHERE edad>45;

Lenguaje procedural (tipo php o java)

Abre la tabla empelados
Lee desde el primer registro hasta el último (Bucle)
     Verifica si ese empleado tiene mas de 45 
     Si es así añádelo a la lista de resultado
Muestra la lista de resultado

SQL

Lenguaje para gestionar bases de datos relacionales cuyas instrucciones o sentencias especifican lo qué se quiere, no cómo se va a realizar

Lenguajes dentro de SQL

SQL no es, como su nombre indica, solamente un lenguaje de consultas, sino que proporciona todas las instrucciones necesarias para gestionar una base de datos.
SQL incorpora tres tipos de lenguajes: de Definición, de Control, de Manipulación.
Las instrucciones de SQL se conocen como sentencias, y todas se caracterizan por que empiezan por una palabra reservada que identifica a qué lenguaje de los tres que tiene SQL pertenece esa instrucción.

Lenguajes SQL

DDL

Lenguaje de definición de datos.

Las sentencias de este lenguajes permite crear nuevos elementos en la base de datos como usuarios, tablas, índices, etc.

DCL

Lenguaje de control de datos

Las sentencias de este lenguaje permite gestionar permisos sobre los elementos y acciones sobre la consistencia de los datos

DML

Lenguaje de manipulación de datos

Sin duda son las instrucciones o sentencias mas utilizadas en un Gestor

Con ellas podemos manipular lo que se conoce como la extensión o contenido de la base de datos. Es decir actuar sobre las tuplas (insertar, modificar y borrar), y consultar

Sentencias DDL

CREATE

CREATE DATABASE usuarios;
CREATE TABLE usuario(
       password VARCHAR(200),
       nombre varchar2(40)
);

DROP

DROP TABLE usuario;

ALTER

ALTER TABLE usuarios ADD direccion VARCHAR(20);

Instrucción CREATE

Sintaxis de la instrucción

Los nombres de las columnas son identificadores

Tipos de datos para las columnas

Especificación de Restricciones

Definición de Restricciones

Definición de Restricciones

Restricción unique

Restrición Foreign Key

Restricción Ckeck

Creacción de tabla como resultado de una consulta

Crear una base de datos

Crea la base de datos dado el siguiente enunciado

Clientes (DNI, nombre, dirección, edad (mayor de 18 años y menor de 65), sexo (H, M, N_A), por defecto N_A)
Facturas (Número , fecha (por defecto la fecha actual, importe total, cantidad_productos)

Realiza el sql de la siguiente estructura

DROP TABLE cliente;
DROP TABLE facturas;
 
CREATE TABLE cliente(
dni varchar2(10) PRIMARY KEY,
nombre varchar2(10) DEFAULT 'mi nombre'
);
CREATE TABLE factura(
num NUMBER PRIMARY KEY,
fecha DATE DEFAULT sysdate,
cantidad NUMBER DEFAULT 10,
cliente varchar2(10),
CONSTRAINT cliente_factura FOREIGN KEY (cliente) REFERENCES cliente(dni) ON DELETE SET NULL
);
 
INSERT INTO  cliente (dni) VALUES ( 1);
INSERT INTO  cliente (dni) VALUES ( 2);
INSERT INTO  cliente (dni) VALUES ( 3);
INSERT INTO  cliente (dni) VALUES ( 4);
 
INSERT INTO  factura(num, cliente) VALUES ( 1, 1);
INSERT INTO  factura(num, cliente) VALUES ( 2, 1);
INSERT INTO  factura(num, cliente) VALUES ( 3, 1);
INSERT INTO  factura(num, cliente) VALUES ( 4, 2);
INSERT INTO  factura(num, cliente) VALUES ( 5, 3);
INSERT INTO  factura(num, cliente) VALUES ( 6, 3);
INSERT INTO  factura(num, cliente) VALUES ( 7, 4);
INSERT INTO  factura(num, cliente) VALUES ( 8, 4);

Alter table

La sentencia ALTER TABLE se utiliza para añadir, eliminar o modificar las columnas en una tabla existente.

SQL ALTER TABLE

ALTER TABLE TABLE_NAME
ADD column_name datatype

Eliminar una columna de una tabla

ALTER TABLE TABLE_NAME
DROP COLUMN column_name
</sql>
 
Modificar co  cambiar el tipo de datos de una columna de una tabla
<SOURCE lang=sql>
ALTER TABLE TABLE_NAME
MODIFY column_name datatype

(: Ojo no violar restricciones con la modificación de la tabla)

Modifica la tabla anterior

Añade un atributo a cada tabla con un valor por defecto
Modifica el tipo de dato de dni a 15 caracteres.
Elimina los atributos añadidos

Alter table

Supongamos que tenemos el siguiente esquema

En un departamento hay empleados
cada empleado pertenece a un solo departamento
Cada departamento es dirigido por un empleado
Un empleado puede dirigir varios departamentos

En él vemos dos tablas con referencias cruzadas

Excribe las sentencias sql que generen la estructura de base de datos

CREATE TABLE EMPLEADO (
dni varchar2(20),
direccion varchar2(15),
nombre varchar2(20) NOT NULL,
telefono NUMBER,
departamento NUMBER,
CONSTRAINT valor_telefono CHECK(telefono > 600000000),
CONSTRAINT clave_ppal_empleados PRIMARY KEY (dni),
CONSTRAINT nom_tel_unico_emp UNIQUE(nombre, telefono)
);
 
CREATE TABLE DEPARTAMENTO(
numero NUMBER,
nombre varchar2(15),
ubicacion varchar2(20) NOT NULL,
director varchar2(20),
CONSTRAINT numero  PRIMARY KEY (numero),
CONSTRAINT director FOREIGN KEY (director) REFERENCES EMPLEADO(dni)
);
 
ALTER TABLE EMPLEADO
ADD CONSTRAINT pertenece FOREIGN KEY (departamento) REFERENCES DEPARTAMENTO(numero);

Ahora si queremos eliminar las tablas, directamente no lo podremos hacer

Importante a la hora de borrar

No se puede eliminar una tabla si está siendo referenciada por otra

Primero habría que eliminar esa referencia
En general se deben eliminar las tablas en orden inverso en el que se crearon

ALTER TABLE departamento
DROP CONSTRAINT director;
DROP TABLE empleado;
DROP TABLE departamento;

}}

Drop table

Elimina una tabla de forma completa
Si solo quire elminiar su contenido usa la claúsula trunctate

Drop

Prueba a borrar una tabla

Sentencias DCL

GRANT: para asignar privilegios

 GRANT ALL ON DATABASE.* TO 'manuel'@'localhost'
 #Asigna todos los privilegios sobre las tablas 
 #de la base de datos 'database' 
 # al usuario 'manuel'

REVOKE: Elimina privilegios asignados
COMMIT: Confirma una transacción de manera permanente en la base de datos

ROLLBACK: Desace todos los cambios en la base de datos desde la ultima transacción confirmada

Actividad

Prueba a hacer una inserción
Después sal y vuelve a entrar a ver si está la tupla

Esto es por que por defecto se hace un rollback a todo lo hecho

Haz lo mismo haciendo un commit

Ahora los cambios se mantienen

Sentencias DML

INSERT: Inserta tuplas en una tabla; Esta acción pude devolver algún tipo de valor booleano indicando se se ha insertado o no la tupla

INSERT INTO usuarios (nombre, password) 
VALUES("manuel", "password_no_segura");

DELETE: Borra tuplas (0 o más) de una o más tablas

DELETE FROM usuarios WHERE nombre="manuel";

UPDATE: Actuliza tuplas (0 o más) de una o más tablas; Las dos últimas sentencias (UPDATE Y DELETE) suelen devolver un entero.; Este valor indica el número de tuplas actualizadas o borradas (0 o n); Un error se detalla con el valor (-1 o false).

UPDATE usuarios SET nombre="alicia" WHERE nombre="manolo";

SELECT

Esta es una claúsula especial a la que le vamos a dedicar bastante tiempo.
Consiste en hacer consultas de nuestro conjunto de datos y poder recuperarlos.
La cantidad de consultas que se pueden realizar es muy grande y variado, siempre que se pueda establecer la consulta y la lógica matemática de su resolución, va a haber una sentencia SQL que permita implementarla.

Esta sentencia que empieza por la claúsual SELECT va a terner hasta 5 posibles claúsulas, de las cuales solo son obligatorias las dos primeras

 SELECT
 FROM

Las 5 posibles que estudaremos son

  SELECT 
  FROM
  WHERE
  GROUP BY
   HAVING
 (ORDER BY)

Importante (lo estudiaremos)

Having solo se usa como opción en el group by.
Order by es un criterio de ordenación y no se considera como cláusula propiamente dicho

Usando el álgebra relacional y permite recuperar un subconjunto de los datos de la base de datos.
Esta sentencia devuelve un conjunto de tuplas como resultado de ejecutar la consulta.
Las operaciones del algebra relacional son

Proyecciones
Selecciones
Producto cartesiano
Join

Claúsula Select

Uso de funciones

Podemos usar funciones sobre todas las filas y obtener el resultado.
Algunas funciones típicas son

Funciones de carácter

Descripción de las funciones

Ejemplos

Funciones con valores numércios

Funciones con fechas

Funciones de agregación o grupo

Son funciones que se aplican a un conjunto de valores de diferentes filas
Nos darán como resultado un sólo valor
Conviene renombrar la columna

Realiza las siguientes consultas

El usuario que más cobra
El trabajo más remunerado
La remuneración media, en número de trabajos y el que más y menos se paga
El empleado con la fecha de contratación mayor y menor
El total de comisiones pagadas a los empleados

From

Esta es una operación de un producto cartesiano
El producto cartesiano consiste en juntar todas las tuplas de una relación con cada una de las tuplas de otra
Genera gran cantidad de información, por ese motivo los gestores de bases de datos intentan hacerlo lo último

probar from

Realiza un producto cartesiano entre empleados y departamentos

En el from además de una relación o tabla, puede ir cualquier cosa que me devuelva una relación, incluso una consulta

probar from

Realiza un producto cartesiano entre empleados y departamentos, solo nombres, y fuérzalo en el from.

Where

La claúsura where establece la operación de selección.
Es decir de todas las filas, indica cuales son las que queremos seleccionar.

En esta claúsura, utiliza operadores de comparación para establecer el criterio de las filas seleccionadas

Archivo:Dml func 31.png

Ejercicios

Realiza las siguientes consultas

En este caso JOB_ID nos da el tipo de trabajo

Secretari@ es el tipo ST_CLERK

mysql> SELECT *  
       FROM EMPLOYEES 
       WHERE JOB_ID ="ST_CLERK"       
             AND       
             HIRE_DATE>"1997-12-31";

SELECT LAST_NAME, JOB_ID,SALARY, COMMISSION_PCT 
FROM EMPLOYEES
WHERE COMMISSION_PCT IS NOT NULL 
ORDER BY LAST_NAME DESC;

SELECT 'El salario de '||Last_name||
       'va a ser con un incremento del 10% de '||
       round(Salary *1.10) ||
       ' dólares' AS "Nuevo Salario"
FROM EMPLOYEES
WHERE COMMISSION_pCT IS NULL

SELECT last_name, 
       trunc(months_between(sysdate, hire_date)/12) AS "años",
       trunc(MOD(months_between(sysdate, hire_date),12))AS "meses"
FROM employees
ORDER BY hire_date;

SELECT last_name, salary, decode (commission_pct, 
                                                NULL, 'No' ,
                                                'Yes') AS "comisión"
FROM employees

SELECT department_name, location_id,job_id, salary
FROM departments d, employees e
WHERE d.department_id = e.department_id
       AND
       location_id = '&localizacion';

SELECT COUNT(*) 
FROM employees
WHERE last_name LIKE '%n'

a) Contrate menos de tres empleados

b) Tenga el mayor número de empleados

c) Tenga el menor número de empleados

Concepto de join o reunión natural

JOIN

El join consiste en unir filas de diferentes tablas
Con la condición que esas filas tengan una relación directa

Por relación directa vamos a entender que son dos realidades
que en el mundo real están relacionadas

Ejemplo

Las facturas de cada empleados

Un empleado tiene realción directa con sus facturas

La relación directa queda almacenada con la clave extranjera que propagamos.
Se tratará de los clientes sus facturas

es decir para cada cliente, reunirlo con las facturas
cuyo valor en el campo id_cliente coincida con la clave de cliente

Una forma de realizar esta reunión sería

 SELECT campos
 FROM tabla t1 otra_tabla t2
 WHERE t1.clave_ppal = t2.clave_foranea

Por ejemplo:

 SELECT *
 FROM cliente c, factura f
 WHERE c.id = f.cliente_id

Por ser una operación muy frecuente se ha implementado un operador explícito para ello

La sintaxis posible que vamos a estudiar

A continución vemos su significado para analizar cada una de ellas

Creación de uniones naturales

Miramos el ejemplo de employees y departments
En ella vemos que la coincidencia de clave principal y foránea que las relaciona

department_id

Podemos hacer el join natural

Pero en este caso tenemos un pequeño problema
Hay que tener cuidad con el operador natural join si hay más de una columna que coincida los nombres

Veamos el siguiente ejemplo

SELECT DEPARTMENT_NAME, LAST_NAME, EMPLOYEE_ID
FROM DEPARTMENTS d, EMPLOYEES e 
WHERE d.DEPARTMENT_ID=e.DEPARTMENT_ID

Nos da la siguiente salida

Pero si hacemos la unión natura

SELECT DEPARTMENT_NAME, LAST_NAME, EMPLOYEE_ID
FROM DEPARTMENTS
NATURAL JOIN EMPLOYEES

o

SELECT DEPARTMENT_NAME, LAST_NAME, EMPLOYEE_ID
FROM EMPLOYEES
NATURAL JOIN DEPARTMENTS

Vemos la siguiente salida

Por que el operador 'NATURAL JOIN' junta las filas de los campos que coincidan nombres
Si observamos la imagen anterior estas dos tablas tiene las campos en común

DEPARTMENTS_ID
MANAGER_ID

Para esto está el operador USING dentro del JOIN

SELECT DEPARTMENT_NAME, LAST_NAME, EMPLOYEE_ID
FROM DEPARTMENTS JOIN EMPLOYEES
USING (DEPARTMENT_ID)

Ahora vemos que el resultado es igual a los 19 registros

Funciones de conversión de tipos

Funciones de agrupación o grupo

Son funciones que nos va a mostrar un valor por cada conjunto de filas a las que se aplica la función

Actividades de funciones y agregados

Archivo:Ej func 14.png

Plantilla:PHP/SQL

Contenido

SQL

Lenguajes dentro de SQL

Sentencias DDL

Instrucción CREATE

Alter table

Drop table

Sentencias DCL

Sentencias DML

SELECT

Claúsula Select

Funciones de agregación o grupo

From

Where

Ejercicios

Concepto de join o reunión natural

Funciones de conversión de tipos

Funciones de agrupación o grupo

Actividades de funciones y agregados

Menú de navegación

Herramientas personales

Espacios de nombres

Variantes

Vistas

Acciones

Buscar

Navegación

Herramientas