programación orientada a base de datos - sql

7/26/2019 Programación Orientada a Base de Datos - SQL

http://slidepdf.com/reader/full/programacion-orientada-a-base-de-datos-sql 1/67

MySQL

ySQL

Docente:

Julio Reinoso V.

Programación Orientadaa Base de Datos

Carrera:

Técnico Universitario

en Informática



Indice

Índice de Contenidos1.- Conceptos de MySQL2.- Conceptos de XAMPP

3.- Conceptos de SQL4.- Uso de consola Mysql5.- Creación de la estructura6.- Ingreso de datos7.- Mostrar datos8.- Borrado de datos

9.- Modificación de datos10.- Operaciones matemáticas11.- Funciones de cadena12.- Valores nulos13.- Grupos14.- Subconsultas

15.- Joins16.- Union, Vistas17.- Triggers18.- Procedimientos almacenados19.- Gestión de Usuarios y Privilegios en MySql20.- Respaldo y Restauración en MySql

21.- Uso de phpMyAdmin

MySql y SQL



Contenido

1.- ¿Qué es MySQL?

Es un sistema de gestión de bases de datos relacional, fue creada por la empresa suecaMySQL AB, la cual tiene el copyright del código fuente del servidor SQL, así como tambiénde la marca.MySQL es un software de código abierto, licenciado bajo la GPL de la GNU, aunque MySQL

AB distribuye una versión comercial, en lo único que se diferencia de la versión libre, es en

el soporte técnico que se ofrece, y la posibilidad de integrar este gestor en un softwarepropietario, ya que de otra manera, se vulneraría la licencia GPL.El lenguaje de programación que utiliza MySQL es Structured Query Language (SQL) quefue desarrollado por IBM en 1981 y desde entonces es utilizado de forma generalizada enlas bases de datos relacionales.

MySql



Contenido

Historia de MySQL

MySQL surgió alrededor de la década del 90, Michael Windenis comenzó a usar mSQL para

conectar tablas usando sus propias rutinas de bajo nivel (ISAM). Tras unas primeraspruebas, llegó a la conclusión de que mSQL no era lo bastante flexible ni rápido para lo quenecesitaba, por lo que tuvo que desarrollar nuevas funciones. Esto resulto en una interfazSQL a su base de datos, totalmente compatible a mSQL.El origen del nombre MySQL no se sabe con certeza de donde proviene, por una lado se

dice que en sus librerías han llevado el prefijo “my” durante los diez últimos años, por otraparte, la hija de uno de los desarrolladores se llama My. Así que no está claramente definidocual de estas dos causas han dado lugar al nombre de este conocido gestor de bases dedatos.

MySql



Contenido

Características principales

Inicialmente, MySQL carecía de algunos elementos esenciales en las bases de datosrelacionales, tales como integridad referencial y transacciones. A pesar de esto, atrajo a losdesarrolladores de páginas web con contenido dinámico, debido a su simplicidad, de talmanera que los elementos faltantes fueron complementados por la vía de las aplicacionesque la utilizan. Poco a poco estos elementos faltantes, están siendo incorporados tanto por

desarrolladores internos, como por desarrolladores de software libre.En las últimas versiones se pueden destacar las siguientes características principales:El principal objetivo de MySQL es velocidad y robustez.Soporta gran cantidad de tipos de datos para las columnas.Gran portabilidad entre sistemas, puede trabajar en distintas plataformas y sistemasoperativos.

Cada base de datos cuenta con 3 archivos: Uno de estructura, uno de datos y uno de índicey soporta hasta 32 índices por tabla.

Aprovecha la potencia de sistemas multiproceso, gracias a su implementación multihilo.Flexible sistema de contraseñas (passwords) y gestión de usuarios, con un muy buen nivelde seguridad en los datos.El servidor soporta mensajes de error en distintos idiomas

MySql



Contenido

Ventajas

Velocidad al realizar las operaciones, lo que le hace uno de los gestores con mejorrendimiento.Bajo costo en requerimientos para la elaboración de bases de datos, ya que debido a subajo consumo puede ser ejecutado en una máquina con escasos recursos sin ningúnproblema.

Facilidad de configuración e instalación.Soporta gran variedad de Sistemas OperativosBaja probabilidad de corromper datos, incluso si los errores no se producen en el propiogestor, sino en el sistema en el que está.Conectividad y seguridad

Desventajas

Un gran porcentaje de las utilidades de MySQL no están documentadas.No es intuitivo, como otros programas (ACCESS).

MySql



Contenido

InnoDB

InnoDB es una tecnología de almacenamiento de datos de fuente abierta para MySQL,incluido como formato de tabla estándar en todas las distribuciones de MySQL AB a partirde las versiones 4.0. Su característica principal es que soporta transacciones de tipo

ACID(Atomicity, Consistency, Isolation and Durability) y bloqueo de registros e integridadreferencial. InnoDB ofrece una fiabilidad y consistencia muy superior a MyISAM, la anteriortecnología de tablas de MySQL, si bien el mejor rendimiento de uno u otro formatodependerá de la aplicación específica.

MyISAMMyISAM es la tecnología de almacenamiento de datos usada por defecto por el sistemaadministrador de bases de datos relacionales MySQL. Este tipo de tablas están basadas en

el formato ISAM pero con nuevas extensiones. En las últimas versiones de Mysql, el motorInnoDB está empezando a reemplazar a este tipo de tablas por su capacidad de ejecutartransacciones de tipo ACID y bloqueo de registros e integridad referencial.

MySql



Contenido MySql



Contenido

Tipos de datos

Después de la fase de diseño de una base de datos, y una vez se ha realizado el paso atablas del mismo, en necesario crear las tablas correspondientes dentro de la base de datos.

Para cada campo de cada una de las tablas, es necesario determinar el tipo de datos quecontiene, para de esa forma ajustar el diseño de la base de datos, y conseguir unalmacenamiento óptimo con la menor utilización de espacio.

Los tipos de datos que puede haber en un campo, se pueden agrupar en tres grandesgrupos:

Tipos numéricos

Tipos de Fecha

Tipos de Cadena

MySql



Contenido

Tipos numéricos:

Existen tipos de datos numéricos, que se pueden dividir en dos grandes grupos, los queestán en coma flotante (con decimales) y los que no.

TinyInt: es un número entero con o sin signo. Con signo el rango de valores válidos vadesde -128 a 127. Sin signo, el rango de valores es de 0 a 255

Bit ó Bool: un número entero que puede ser 0 ó 1

SmallInt: número entero con o sin signo. Con signo el rango de valores va desde -32768 a

32767. Sin signo, el rango de valores es de 0 a 65535.

MediumInt: número entero con o sin signo. Con signo el rango de valores va desde -8.388.608 a 8.388.607. Sin signo el rango va desde 0 a16777215.

Integer, Int: número entero con o sin signo. Con signo el rango de valores va desde -2147483648 a 2147483647. Sin signo el rango va desde 0 a 429.4967.295

BigInt: número entero con o sin signo. Con signo el rango de valores va desde -9.223.372.036.854.775.808 a 9.223.372.036.854.775.807. Sin signo el rango va desde 0 a18.446.744.073.709.551.615.

MySql



Contenido

Float: número pequeño en coma flotante de precisión simple. Los valores válidos van desde-3.402823466E+38 a -1.175494351E-38, 0 y desde 1.175494351E-38 a 3.402823466E+38.

xReal, Double: número en coma flotante de precisión doble. Los valores permitidos vandesde -1.7976931348623157E+308 a -2.2250738585072014E-308, 0 y desde2.2250738585072014E-308 a 1.7976931348623157E+308

Decimal, Dec, Numeric: Número en coma flotante desempaquetado. El número sealmacena como una cadena

MySql



Contenido MySql

Tipo de Campo Tamaño de Almacenamiento

TINYINT 1 byte

SMALLINT 2 bytesMEDIUMINT 3 bytes

INT 4 bytes

INTEGER 4 bytes

BIGINT 8 bytes

FLOAT(X) 4 ú 8 bytes

FLOAT 4 bytesDOUBLE 8 bytes

DOUBLE PRECISION 8 bytes

REAL 8 bytes

DECIMAL(M,D M+2 bytes sí D > 0, M+1 bytes sí D = 0

NUMERIC(M,D) M+2 bytes if D > 0, M+1 bytes if D = 0



Contenido

Tipos fecha:

A la hora de almacenar fechas, hay que tener en cuenta que Mysql no comprueba de una

manera estricta si una fecha es válida o no. Simplemente comprueba que el mes estacomprendido entre 0 y 12 y que el día esta comprendido entre 0 y 31.

Date: tipo fecha, almacena una fecha. El rango de valores va desde el 1 de enero del 1001al 31 de diciembre de 9999. El formato de almacenamiento es de año-mes-dia

DateTime: Combinación de fecha y hora. El rango de valores va desde el 1 de enero del

1001 a las 0 horas, 0 minutos y 0 segundos al 31 de diciembre del 9999 a las 23 horas, 59minutos y 59 segundos. El formato de almacenamiento es de año-mes-diahoras:minutos:segundos

TimeStamp: Combinación de fecha y hora. El rango va desde el 1 de enero de 1970 al año2037. El formato de almacenamiento depende del tamaño del campo

Time: almacena una hora. El rango de horas va desde -838 horas, 59 minutos y 59segundos a 838, 59 minutos y 59 segundos. El formato de almacenamiento es de'HH:MM:SS'

Year: almacena un año. El rango de valores permitidos va desde el año 1901 al año 2155.El campo puede tener tamaño dos o tamaño 4 dependiendo de si queremos almacenar elaño con dos o cuatro dígitos.

MySql



Contenido

Tipos de cadena:

Char(n): almacena una cadena de longitud fija. La cadena podrá contener desde 0 a 255

caracteres.

VarChar(n): almacena una cadena de longitud variable. La cadena podrá contener desde 0a 255 caracteres.

Dentro de los tipos de cadena se pueden distinguir otros dos subtipos, los tipo Test y lostipo BLOB (Binary large Object)

La diferencia entre un tipo y otro es el tratamiento que reciben a la hora de realizarordenamientos y comparaciones. Mientras que el tipo test se ordena sin tener en cuenta lasMayúsculas y las minúsculas, el tipo BLOB se ordena teniéndolas en cuenta.

Los tipos BLOB se utilizan para almacenar datos binarios como pueden ser ficheros.

MySql



Contenido

TinyText y TinyBlob: Columna con una longitud máxima de 255 caracteres.

Blob y Text: un texto con un máximo de 65535 caracteres.

MediumBlob y MediumText: un texto con un máximo de 16.777.215 caracteres.

LongBlob y LongText: un texto con un máximo de caracteres 4.294.967.295. Hay quetener en cuenta que debido a los protocolos de comunicación los paquetes pueden tener unmáximo de 16 Mb.

Enum: campo que puede tener un único valor de una lista que se especifica. El tipo Enumacepta hasta 65535 valores distintos

Set: un campo que puede contener ninguno, uno ó varios valores de una lista. La listapuede tener un máximo de 64 valores.

MySql



Contenido

Resumen

Tipos de datosNuméricos

tinyint, smallint, mediumint, int, integer, bigintdecimal, float, numericFecha y hora

date, time, datetime, year, timestampCadena

char, varchartinytext, text, mediumtext, longtext

tinyblob, blob, mediumblob, longblobenum, set

Operadores Aritméticos

+, -, *, /Comparación

=, !=, <=, <, >=, >, IS NULL, IS NOT NULLLógicosnot (!), and (&&), or (||), xor

FuncionesFunciones de cadenaFunciones de comparación de cadenasFunciones numéricas

Funciones de fecha y horaFunciones de agregado

MySql



Contenido

2.- ¿Qué es XAMPP?

XAMPP es un servidor independiente

de plataforma, software libre, queconsiste principalmente en la base dedatos MySQL, el servidor web Apachey los intérpretes para lenguajes descript: PHP y Perl. El nombreproviene del acrónimo de X (paracualquiera de los diferentes sistemasoperativos), Apache, MySQL, PHP,Perl.El programa está liberado bajo lalicencia GNU y actúa como un

servidor web libre, fácil de usar ycapaz de interpretar páginasdinámicas. Actualmente XAMPP estádisponible para Microsoft Windows,GNU/Linux, Solaris y Mac OSX.

XAMPP Server



Contenido

Características y requisitos

XAMPP solamente requiere descargar y ejecutar un archivo zip, tar, exe o fkl, con unas

pequeñas configuraciones en alguno de sus componentes que el servidor Web necesitará.XAMPP se actualiza regularmente para incorporar las últimas versiones de

Apache/MySQL/PHP y Perl. También incluye otros módulos como OpenSSL y phpMyAdmin.Para instalar XAMPP se requiere solamente una pequeña fracción del tiempo necesario paradescargar y configurar los programas por separado. Puede encontrarse tanto en versióncompleta, así como en una versión más ligera que es portátil.Oficialmente, los diseñadores de XAMPP sólo pretendían su uso como una herramienta dedesarrollo, para permitir a los diseñadores de sitios webs y programadores testear sutrabajo en sus propios ordenadores sin ningún acceso a Internet. En la práctica, sinembargo, XAMPP es utilizado actualmente como servidor de sitios Web, ya que, con algunasmodificaciones, es generalmente lo suficientemente seguro para serlo. Con el paquete se

incluye una herramienta especial para proteger fácilmente las partes más importantes.

XAMPP Server



Contenido

3.- ¿Qué es SQL?

La sigla que se conoce como SQL corresponde a la expresión inglesa Structured Query

Language (entendida en español como Lenguaje de Consulta Estructurado), la cualidentifica a un tipo de lenguaje vinculado con la gestión de bases de datos de carácterrelacional que permite la especificación de distintas clases de operaciones entre éstas.Gracias a la utilización del álgebra y de cálculos relacionales, el SQL brinda la posibilidad derealizar consultas con el objetivo de recuperar información de las bases de datos de manerasencilla.El científico Edgar Frank Codd (1923-2003) fue quien propuso un modelo relacional para lasbases de datos y creó un sublenguaje para acceder a los datos a partir del cálculo depredicados. En base al trabajo de Codd, IBM (International Business Machines) definió ellenguaje conocido como Structured English Query Language (SEQUEL).El SEQUEL se considera el antecesor de SQL, un lenguaje de cuarta generación que se

estandarizó en 1986. La versión más primitiva de SQL, por lo tanto, fue la que se bautizócomo SQL-86 (también conocida como SQL1).En esencia, el SQL es un lenguaje declarativo de alto nivel ya que, al manejar conjuntos deregistros y no registros individuales, ofrece una elevada productividad en la codificación y enla orientación a objetos. Una sentencia de SQL puede resultar equivalente a más de unprograma que emplee un lenguaje de bajo nivel.

Lenguaje SQL



Contenido

SQL (Structured Query Language ) es el lenguaje que se utiliza paracomunicarse con la base de datos

Las instrucciones más habituales son SELECT, INSERT, UPDATE, DELETE

Procedimiento de comunicación con la base de datos:

PáginaPHP Base

de

datos

orden SQL

resultado

Lenguaje SQL



Contenido

4.- Uso de consola MySQLCon la instalación típica, MySQL debería haber quedado dentro de "Archivos de programa“.Para llegar hasta allí, ingresar al intérprete de comandos de Windows (por ejemplo, desde el

menú de Inicio, en la opción "Ejecutar", tecleando la orden "cmd"), entonces usar la orden"cd" para llegar hasta la carpeta "bin", en la que están los "binarios" (programasejecutables) de MySQL. Para entrar al entorno de MySQL, indicar un nombre de usuario conla opción "-u". Como por ahora sólo existe el usuario "root", digitar:

mysql -u root

Luego de esta instrucción, aparece un mensaje similar al siguiente:

Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1Server version: 5.0.45-community-nt MySQL Community Edition (GPL) Type 'help;' or '\h' for

help. Type '\c' to clear the buffer. mysql>

Para salir de la consola de MySql se utiliza la instrucción “quit”

Ahora se puede teclear órdenes directamente en lenguaje SQL y ver los resultados.

Lenguaje SQL



Contenido

5.- Creación de la estructura

Lo primero es crear la base de datos, para eso se utiliza la instrucción:

Create Database

CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name [create_specification [,create_specification] ...]

create_specification: [DEFAULT] CHARACTER SET charset_name | [DEFAULT] COLLATEcollation_name

Ejemplo:

create database ejemplo1;

Una vez creada la base de datos y para poder trabajar con ella, se utiliza la instrucción USE

USE db_name;

Lenguaje SQL



Contenido

5.1 Creación de tablas

Una base de datos, está formada tablas. Por lo tanto, una vez creada la base de datos es

necesario crear las correspondientes tablas:

Ejemplo:

CREATE TABLE ciudades ( codigo int Auto_Increment,

nombre varchar(30) not null,comuna varchar(20) null,region varchar(20),

primary key (codigo) );

CREATE TABLE crea una tabla con el nombre dado. Debe tener el permiso CREATE para latabla.Por defecto, la tabla se crea en la base de datos actual. Ocurre un error si la tabla existe, sino hay base de datos actual o si la base de datos no existe.

Lenguaje SQL



Contenido

5.2 Creación de tablas incluyendo integridad referencial

Ejemplo:

CREATE TABLE autor (cod_autor int(4),nombre_autor VARCHAR(50),cod_nacionalidad INT NOT NULL,PRIMARY KEY (cod_autor),

INDEX (cod_autor),INDEX (cod_nacionalidad),FOREIGN KEY (cod_nacionalidad) REFERENCESnacionalidad(cod_nacionalidad)ON DELETE restrict ON UPDATE cascade)

ENGINE=INNODB

Lenguaje SQL



Contenido

6.- Ingreso de Datos

Para introducir datos se usa la orden "insert", y se indica tras la palabra "values" los valores

para los campos de texto entre comillas, y los valores para campos numéricos sin comillas:

Sintaxis:INSERT [INTO] nombre_tabla [(nombre_columna,...)]

VALUES ((expresión | DEFAULT),...), (...),...INSERT [INTO] nombre_tabla

SET nombre_columna=(expresión | DEFAULT), ...

Ejemplo:

INSERT INTO noticias (id, titulo, texto, categoria, fecha) VALUES (37, “Nueva promoción en

Nervión”, “145 viviendas de lujo en urbanización ajardinada situadas en un entornoprivilegiado”, “promociones”, CURDATE())

Inserta una noticia con los valores indicados. En el caso de la fecha se muestra el uso de lafunción CURDATE() que extrae la fecha actual del sistema, en su lugar se puedeespecificar una fecha en particular, para lo cual se ingresa en formato “aaaa-mm-dd”

Lenguaje SQL



Contenido

7.- Mostrando datos

Para ver los datos almacenados en una tabla se usa la instrucción "SELECT campos FROM

tabla ". Si se quieren ver todos los campos, se utiliza un asterisco:

Sintaxis:SELECT expresión FROM tabla[WHERE condición][ORDER BY {unsigned_integer | col_name | formula} [ASC | DESC] ,...]

[LIMIT [offset,] row_count | row_count OFFSET offset]

Ejemplo:

SELECT * FROM noticias WHERE fecha=CURDATE() LIMIT 10 ORDER BY fecha DESC

Obtiene las noticias del día con un tope máximo de 10, ordenadas de la más reciente a lamás antigua.

Lenguaje SQL



Contenido

8.- Borrar información

8.1 ¿Qué información hay?

Un primer paso antes de ver cómo borrar información es saber qué información existealmacenada.Se puede saber que bases de datos hay creadas en el servidor mediante la instrucción:SHOW DATABASES;

Una vez que se está trabajando con una base de datos concreta (con la orden "use"), se

puede saber las tablas que contiene mediante:SHOW TABLES;

Y para una tabla concreta, se puede saber los campos (columnas) que la forman con "showcolumns from":

SHOW COLUMNS FROM personas;

Para ver detalles específicos de tablas (por ejemplo nombre de CONSTRAINTS):SHOW CREATE TABLE tabla;

Lenguaje SQL



Contenido

8.2 Borrar toda la base de datos

En alguna ocasión, puede interesar borrar toda la base de datos. La orden para conseguirlo

es:

DROP DATABASE ejemplo2;

Si esta orden es parte de una secuencia larga de órdenes, que se ha cargado con la orden"source" (por ejemplo) y la base de datos no existe, se obtendría un mensaje de error y se

interrumpiría el proceso. Se puede evitarlo añadiendo "if exists", para que se borre la basede datos sólo si realmente existe:

drop database ejemplo2 if exists;

Lenguaje SQL



Contenido

8.3 Borrar una tabla

Es más frecuente que se cree alguna tabla de forma incorrecta. La solución razonable es

corregir ese error, cambiando la estructura de la tabla, pero también se puede borrar latabla completa. Para esto la instrucción es:

DROP TABLE personas;

Al igual que para las bases de datos, se puede hacer que la tabla se borre sólo cuando

realmente existe:

DROP TABLE personas IF EXISTS;

Lenguaje SQL



Contenido

8.4 Borrar datos de una tabla

También se pueden borrar los datos que cumplen una cierta condición. La instrucción para

esto es "delete from", y con "where" se indican las condiciones que se deben cumplir, deforma similar a como se hace con la orden "select":

DELETE FROM personas WHERE nombre = 'juan';

Esto borraría todas las personas llamadas "juan" que estén almacenadas en la tabla

"personas".Cuidado: si no se indica "where", no se borrarían los datos que cumplen una condición, sinoTODOS los datos. Si es eso lo que se pretende, una forma más rápida de conseguirlo esusar:

TRUNCATE TABLE personas;

Lenguaje SQL



Contenido

9.- Modificar información

9.1 Modificación de datos

Existe una operación más frecuente que el borrado (aunque también ligeramente máscomplicada): modificar los datos existentes. El formato habitual para modificar datos de unatabla es "update tabla set campo=nuevoValor where condicion".Por ejemplo, si se ha escrito "Alberto" en minúsculas ("alberto"), se puede corregir con:

UPDATE personas SET nombre = 'Alberto' WHERE nombre = 'alberto';

Y si se quieren corregir todas las edades para sumarles un año se haría conupdate personas set edad = edad+1;(al igual que para "select" y para "delete", si no se indica "where", los cambios se aplicarána todos los registros de la tabla).

Lenguaje SQL



Contenido

9.2 Modificar la estructura de una tabla

Algo más complicado es modificar la estructura de una tabla: añadir campos, eliminarlos,

cambiar su nombre o el tipo de datos. En general, se usará la orden "alter table".

Para añadir un campo se usa "add":

ALTER TABLE ciudades ADD habitantes decimal(7);

ALTER TABLE ciudades ADD PRIMARY KEY (cod_Ciudad); (Añadir Clave primaria)

ALTER TABLE t1 add FOREIGN KEY(cod1) REFERENCES t2(cod1)ON DELETE RESTRICT ON UPDATE CASCADE; (Añadir clave foránea e integridadreferencial)

Si no se indica otra cosa, el nuevo campo se añade al final de la tabla. Para que sea elprimer campo, se añade "first" al final de la orden. También se puede añadir después decierto campo, con "after nombreCampo".

Lenguaje SQL



Contenido

Para modificar el tipo de datos de un campo usar "modify“:

ALTER TABLE ciudades MODIFY habitantes bigint;

ALTER TABLE ciudades MODIFY cod_ciudad int Auto_Increment; (Agregar extra a campo)

Para cambiar el nombre de un campo, se usa "change“, ejemplo: ALTER TABLE ciudades CHANGE habitantes numhabitantes bigint;

Para borrar un campo se usa "drop column":

ALTER TABLE ciudades DROP COLUMN numhabitantes;

Para borrar una restricción de una clave foránea: ALTER TABLE ciudades DROP FOREIGN KEY “nombre_constraint”;

Finalmente, para cambiar el nombre de una tabla se usa "rename": ALTER TABLE ciudades RENAME ciudad;

Lenguaje SQL



Contenido

10.- Operaciones matemáticas

10.1 Operaciones matemáticas

Desde SQL se pueden realizar operaciones a partir de los datos antes de mostrarlos. Porejemplo, se puede mostrar cual era la edad de una persona hace un año, con:

SELECT edad-1 FROM personas;

Los operadores matemáticos que se pueden emplear son los habituales en cualquier

lenguaje de programación, ligeramente ampliados: + (suma), - (resta y negación), *(multiplicación), / (división) . La división calcula el resultado con decimales; para trabajarcon números enteros, también existen los operadores DIV (división entera) y MOD (resto dela división):

SELECT 5/2, 5 DIV 2, 5 MOD 2;

Daría como resultado+--------+---------+---------+| 5/2 | 5 div 2 | 5 mod 2 |+--------+---------+---------+

| 2.5000 | 2 | 1 |+--------+---------+---------+

Lenguaje SQL



Contenido

10.2 Funciones de agregación

Se pueden aplicar ciertas funciones matemáticas a todo un conjunto de datos de una tabla.

Por ejemplo, para saber cual es la edad más baja de entre las personas que existen en labase de datos:

SELECT MIN(edad) FROM personas;

Las funciones de agregación más habituales son:

min = mínimo valormax = máximo valorsum = suma de los valoresavg = media de los valorescount = cantidad de valoresLa forma más habitual de usar "count" es pidiendo con "count(*)" que se muestren todoslos datos que cumplen una condición. Por ejemplo, saber cuantas personas tienen unadirección que comience por la letra "s", así:

SELECT COUNT(*) FROM personas WHERE direccion LIKE 's%';

Lenguaje SQL



Contenido

11.- Funciones de cadena

En MySQL existen muchas funciones para manipular cadenas: calcular su longitud, extraer

un fragmento situado a la derecha, a la izquierda o en cualquier posición, eliminar espaciosfinales o iniciales, convertir a hexadecimal y a binario, etc.

11.1 Funciones de conversión a mayúsculas/minúsculas

LOWER o LCASE convierte una cadena a minúsculas: SELECT LOWER('Hola'); -> hola

UPPER o UCASE convierte una cadena a mayúsculas: SELECT UPPER('Hola'); -> HOLA

LEFT(cadena, longitud) extrae varios caracteres del comienzo de la cadena: SELECTLEFT('Hola',2); -> Ho

RIGHT(cadena, longitud) extrae varios caracteres del final (la parte derecha) de la cadena:

MID(cadena, posición, longitud), SUBSTR(cadena, posición, longitud) o SUBSTRING(cadena,posición, longitud) extrae varios caracteres de cualquier posición de una cadena, tantoscomo se indique en "longitud": SELECT SUBSTRING('Hola',2,2); -> ol

Lenguaje SQL



Contenido

CONCAT une varias cadenas para formar una nueva: SELECT CONCAT('Ho', 'la'); -> Hola

CONCAT_WS une varias cadenas para formar una nueva, usando un separador que se

indique (With Separator): SELECT CONCAT_WS('-','Ho','la','Que','tal'); -> Ho-la-Que-tal

LTRIM devuelve la cadena sin los espacios en blanco que pudiera contener al principio (ensu parte izquierda)

RTRIM devuelve la cadena sin los espacios en blanco que pudiera contener al final

TRIM devuelve la cadena sin los espacios en blanco que pudiera contener al principio ni alfinal.

Lenguaje SQL



Contenido

12.- Valores nulos

11.1 Cero y valor nulo

Para las cadenas de texto, la forma de anular un valor es con una cadena vacía, indicadacon dos comillas que no contengan ningún texto entre ellas: ''En cambio, para los números, no basta con guardar un 0 para indicar que no se sabe elvalor: no es lo mismo un importe de 0 euros que un importe no detallado. Por eso, existeun símbolo especial para indicar cuando no existe valor en un campo.Este símbolo especial es la palabra NULL. Por ejemplo:

INSERT INTO personas (nombre, direccion, edad) VALUES ( 'pedro', '', NULL );

INSERT INTO personas (nombre, direccion, edad) VALUES ( 'pedro', NULL, NULL );

Para saber si algún campo está vacío, se compara su valor con NULL, pero de una forma untanto especial: no con el símbolo "igual" (=), sino con la palabra IS. Por ejemplo, para sabercuales de las personas de la base de datos tienen dirección usando:SELECT * FROM personas WHERE direccion IS NOT NULL;

Y, de forma similar, quien no tiene dirección, así:

SELECT * FROM personas WHERE direccion IS NULL;

Lenguaje SQL



Contenido

13.– Grupos

13.1 Agrupando los resultados

Puede ocurrir que no se requiera un único valor agrupado (el total, la media, la cantidad dedatos), sino el resultado para un grupo de datos. Por ejemplo: saber no sólo la cantidad declientes que hay registrados en la base de datos, sino también la cantidad de clientes queviven en cada ciudad.La forma de obtener subtotales es creando grupos con la orden "group by", y entoncespidiendo una valor agrupado (count, sum, avg, ...) para cada uno de esos grupos. Por

ejemplo, en la tabla "personas", podemos saber cuantas personas aparecen de cada edad,con:

SELECT COUNT(*) AS “Personas por Edad”, edad FROM personas GROUP BY edad;

que daría como resultado+----------+------+| count(*) | edad |+----------+------+| 1 | 22 || 1 | 23 |

| 1 | 25 |+----------+------+

Lenguaje SQL



Contenido

13.2 Filtrando los datos agrupados

La condición que se aplica a los grupos no se indica con "where", sino con "having" (que se

podría traducir como "los que tengan..."). Un ejemplo:

SELECT COUNT(*), edad FROM personas GROUP BY edad HAVING edad > 24;

que mostraría+----------+------+

| count(*) | edad |+----------+------+| 1 | 25 |+----------+------+

Lenguaje SQL



Contenido

14.- Subconsultas

14.1 ¿Qué es una subconsulta?

A veces hay que realizar operaciones más complejas con los datos, operaciones en las queinteresaría utilizar una primera consulta auxiliar que extrajera la información en la que senecesita basarse. Esta consulta auxiliar recibe el nombre de "subconsulta" o "subquery".Por ejemplo, si se quiere saber qué clientes hay en la ciudad que más habitantes tenga, laforma "razonable" de conseguirlo sería saber en primer lugar cual es la ciudad que máshabitantes tenga, y entonces lanzar una segunda consulta para ver qué clientes hay en esa

ciudad. Ejemplo:

SELECT MAX(codigo) FROM ciudades;

SELECT * FROM personas WHERE codciudad= maxCodigo;

Estos dos pasos se pueden dar en uno: al final de la "segunda" consulta (la "grande") seincluye la primera consulta (la "subconsulta"), entre paréntesis, así

SELECT * FROM personas WHERE codciudad= ( SELECT MAX(codigo) FROM ciudades );

Lenguaje SQL



Contenido

14.2 Subconsultas que devuelven conjuntos de datos

Si la subconsulta no devuelve un único dato, sino un conjunto de datos, la forma de trabajar

será básicamente la misma, pero para comprobar si el valor coincide con uno de la lista, nose usa el símbolo "=", sino la palabra "in".Por ejemplo, una consulta que muestre las personas que viven en ciudades cuyo nombretiene una "a" en segundo lugar (por ejemplo, serían ciudades válidas Madrid o Barcelona,pero no Alicante).Para consultar qué letras hay en ciertas posiciones de una cadena, se usa SUBSTRING (en el

próximo apartado veremos las funciones más importantes de manipulación de cadenas). Así, una forma de saber qué ciudades tienen una letra A en su segunda posición sería:

SELECT codigo FROM ciudades WHERE SUBSTRING(nombre,2,1)='a';

Como esta subconsulta puede tener más de un resultado, se usa IN para incluirla en laconsulta principal, que quedaría de esta forma:

SELECT * FROM personas WHERE codciudad IN ( SELECT codigo FROM ciudades WHERESUBSTRING(nombre,2,1)='a' );

Lenguaje SQL



Contenido

15.- JOIN, Consultas combinadas

Un join es una combinación de dos o más tablas de una base de datos relacional, es una

instrucción de lo más imprescindible si se quiere realizar una aplicación que realice un usocorrecto e intensivo de un gestor de Bases de datos cómo MySQL. MySQL soporta muchassintaxis diferentes para la instrucción JOIN en comandos de SELECT, DELETE y UPDATE.

Ejemplo:CREATE DATABASE ejemploJoins; USE ejemploJoins;

En ella se crea una primera tabla en la que se almacenarán "capacidades" de personas(cosas que saben hacer):

CREATE TABLE capacidad( codigo varchar(4), nombre varchar(20), primary key(codigo) );

También se crea una segunda tabla con datos básicos de personas:

CREATE TABLE persona( codigo varchar(4), nombre varchar(20), codcapac varchar(4), primary key(codigo) );

Lenguaje SQL



Contenido

Ejemplo:INSERT INTO capacidad VALUES ('c','Progr.C'), ('pas','Progr.Pascal'), ('j','Progr.Java'),('sql','Bases datos SQL');

INSERT INTO persona VALUES ('ju','Juan','c'), ('ja','Javier','pas'), ('jo','Jose','perl'),('je','Jesus','html');

Con instrucciones "join" se puede afinar como se quieren enlazar (en inglés, "join", unir) lastablas.

Para ver los datos que coinciden en ambas tablas, se puede usar un "inner join" (unióninterior; se puede abreviar simplemente "join"):

SELECT persona.nombre, capacidad.nombre FROM persona INNER JOIN capacidad ON persona.codcapac = capacidad.codigo;

Lenguaje SQL



Contenido

Si se quieren ver todas las personas y sus capacidades, incluso para aquellas personas cuyacapacidad no está detallada en la otra tabla, se usaría un "left join" (unión por la izquierda,también se puede escribir "left outer join", unión exterior por la izquierda, para dejar claro

que se van a incluir datos que están sólo en una de las dos tablas):

SELECT persona.nombre, capacidad.nombre FROM persona LEFT JOIN capacidad ON persona.codcapac = capacidad.codigo;

Por ejemplo para ver todas las personas y todas las capacidades, aunque no estén

relacionadas, como en el ejemplo anterior, se usa "cross join":

SELECT persona.nombre, capacidad.nombre FROM persona CROSS JOIN capacidad;

Lenguaje SQL



Contenido

16.- Union, Vistas

El propósito del comando SQL UNION es combinar los resultados de dos consultas juntas.

En este sentido, UNION es parecido a Join, ya que los dos se utilizan para informaciónrelacionada en múltiples tablas. Una restricción de UNION es que todas las columnascorrespondientes necesitan ser del mismo tipo de datos. También, cuando utilizamosUNION, sólo se seleccionan valores distintos (similar a SELECT DISTINCT).

Ejemplos:

SELECT rut, nombre FROM personas UNION SELECT rutempresa, nombreempresa from empresas;

SELECT persona.nombre, capacidad.nombre FROM persona RIGHT OUTER JOIN capacidadON persona.codcapac = capacidad.codigo UNION SELECT persona.nombre,capacidad.nombre FROM persona LEFT OUTER JOIN capacidad ON persona.codcapac =capacidad.codigo;

Lenguaje SQL



Contenido

Por otra parte, se pueden crear "vistas", que permitan definir la cantidad de información a laque se requiere que ciertos usuarios tengan acceso:

CREATE VIEW personasycapac AS SELECT persona.nombre nompres, capacidad.nombre nomcapac FROM persona LEFT OUTER JOIN capacidad ON persona.codcapac =capacidad.codigo;

Y esta "vista" se utiliza igual que si fuera una tabla:

SELECT * FROM personasycapac;

Para modificar una vista se utiliza la instrucción: ALTER VIEW

ALTER VIEW nombrevistaexistente AS SELECT …(nueva consulta)

Cuando una vista deje de ser útil, se puede eliminar con: DROP VIEW

DROP VIEW nombrevistaexistente;

Lenguaje SQL



Contenido

17.– Triggers

En MySQL (a partir de la versión 5.0.2) se permite utilizar "disparadores" (triggers), que son

una serie de pasos que se pondrán en marcha cuando ocurra un cierto evento en una tabla.Los eventos pueden ser un INSERT, un UPDATE o un DELETE de datos de la tabla, ypodemos detallar que los pasos se den antes (BEFORE) del evento o después (AFTER) delevento.Como ejemplo habitual, se puede hacer un BEFORE INSERT para comprobar que los datosson válidos antes de guardarlos realmente en la tabla.

Sintaxis Trigger básico:

CREATE TRIGGER nombretrigger [AFTER] [INSERT] ON[BEFORE][UPDATE]

[DELETE]Tabla1 FOR EACH ROW INSERT INTO Tabla2

VALUES (campo1,…,campon)

Lenguaje SQL



Contenido

Ejemplo:

CREATE DATABASE ejemplotriggers;USE ejemplotriggers;CREATE TABLE persona ( codigo varchar(10), nombre varchar(50), edad decimal(3),PRIMARY KEY (`codigo`) );CREATE TABLE nuevosDatos ( codigo varchar(10), cuando date, tipo char(1) );

Para que se añada un dato en la segunda tabla cada vez que se inserte en la primera, crear

un TRIGGER que saltará con un AFTER INSERT. Para indicar los pasos que debe hacer, seusa la expresión "FOR EACH ROW" (para cada fila), así:

CREATE TRIGGER modificacion AFTER INSERT ONpersona FOR EACH ROW INSERT INTOnuevosDatos VALUES (NEW.codigo, CURRENT_DATE, 'i');

(Los datos que se introducen son: el código de la persona, la fecha actual y una letra "i"para indicar que el cambio ha sido la "inserción" de un dato nuevo).

Lenguaje SQL



Contenido

Si ahora se introduce un dato en la tabla personas:

INSERT INTO persona VALUES ('1','Juan',20); La tabla de "nuevosDatos" habrá cambiado: SELECT * FROM nuevosDatos;

Ejemplo de trigger que registra información de cada evento de borrado en la tabla persona.

CREATE TRIGGER registraborrado AFTER DELETE ON personaFOR EACH ROW INSERT INTO AuditoriaBorrado VALUES (OLD.codigo, CURRENT_USER,NOW(), ‘borrado');

Lenguaje SQL



Contenido

Si se quiere indicar que se deben dar secuencias de pasos más largas, hay que tener encuenta dos cosas: cuando sean varias órdenes, deberán encerrarse entre BEGIN y END;además, como cada una de ellas terminará en punto y coma, se debe cambiarmomentáneamente el "delimitador" (DELIMITER) de MySQL:

DELIMITER // CREATE TRIGGER validacionPersona BEFORE INSERT ON personas FOR EACH ROWBEGINSET NEW.nombre = UPPER(NEW.nombre);

SET NEW.edad = IF(NEW.edad < 1, NULL, NEW.edad);END

// DELIMITER ;

Primero cambiamos a mayúsculas el nombre y luego validamos si el valor es menor que

uno entonces lo transformamos a NULL, para que se gatille un error que no permita grabarel nuevo registro.

Para ver los triggers de la base de datos: SHOW TRIGGERS\G (al agregar \G se despliegaordenado)

Para eliminar un trigger: DROP TRIGGER nombretrigger

Lenguaje SQL

C id



Contenido

18.- Procedimientos almacenados en MySQL

Desde MySQL 5 los procedimientos almacenados empezaron a ser soportados, como suelesuceder en MySQL las sentencias se ejecutan luego de escribir el signo punto y coma (;),por esta razón antes de escribir el procedimiento almacenado la función del punto y comase asigna a otros caracteres usando la sentencia DELIMITER seguida de un carácter talcomo |, de esta manera el procedimiento puede ser escrito usando los punto y comas sinque se ejecute mientras se escribe; después de escrito el procedimiento, se escribenuevamente la sentencia DELIMITER ; para asignar al punto y coma su función habitual.

Algunas situaciones en que los procedimientos almacenados pueden ser particularmenteútiles:• Cuando múltiples aplicaciones cliente se escriben en distintos lenguajes o funcionan en

distintas plataformas, pero necesitan realizar la misma operación en la base de datos.• Cuando la seguridad es muy importante. Los bancos, por ejemplo, usan procedimientos

almacenados para todas las operaciones comunes. Esto proporciona un entorno seguro yconsistente, y los procedimientos pueden asegurar que cada operación se logueaapropiadamente. En tal entorno, las aplicaciones y los usuarios no obtendrían ningúnacceso directo a las tablas de la base de datos, sólo pueden ejecutar algunosprocedimientos almacenados.

Lenguaje SQL

C t id



Contenido

El siguiente es un ejemplo de procedimiento almacenado en MySQL:

DELIMITER //

CREATE PROCEDURE autos(IN velocidad INT,IN marca VARCHAR(50))BEGINIF velocidad < 120 THENINSERT INTO familiares VALUES(velocidad,marca);

ELSEINSERT INTO deportivos VALUES(velocidad,marca);END IF;END

//

DELIMITER ;

CALL autos(10,”Ford”);

Lenguaje SQL

C t id



Contenido

DELIMITER //

CREATE PROCEDURE simpleproc (OUT param1 INT)BEGINSELECT COUNT(*) INTO param1 FROM tablaejemplo;END

//

DELIMITER ;

CALL simpleproc(@contador);

SELECT @contador;+---------------+

| @contador |

+---------------+| 3 |

+--------------+

Lenguaje SQL

C t id



Contenido

DELIMITER $$

DROP PROCEDURE IF EXISTS my_sqrt$$

CREATE PROCEDURE my_sqrt(IN input_number INT, OUT out_number FLOAT)BEGINSET out_number=SQRT(input_number);END

$$

DELIMITER ;

CALL my_sqrt(4, @out_value); // Para ejecutar el procedimiento

SELECT @out_value; // Para ver el resultado

Lenguaje SQL

Contenido



Contenido

Para ver los procedimientos en el ambiente actual:

SHOW PROCEDURE STATUS \G;

Para ver un procedimiento específico:

SHOW CREATE PROCEDURE nombre_procedimiento \G;

Para borrar un procedimiento almacenado:

DROP PROCEDURE nombre_procedimiento;

Lenguaje SQL

Contenido



Contenido

Uso de Variables y funciones de Sistema

SELECT … INTO

Select también se puede utilizar para almacenar resultados de consultas en una variable.

EjemplosSELECT id, data INTO @x, @y FROM test.t1 LIMIT 1;

SELECT COUNT(*) INTO variable1 FROM Personas;

Algunas Funciones de Sistema

CURRENT_USER(); Usuario Actual de base de datos CURRENT_DATE(); Fecha actual

CURTIME(); Hora actual NOW(); Fecha y hora actual DATABASE(); Base de datos actual

Lenguaje SQL

Contenido



Contenido

19.- Gestión de usuarios y privilegios en MySql

Una de los puntos más importantes cuando trabajamos con una base de datos MySQL es laasignación de permisos y usuarios. De esta manera administraremos los accesos y laseguridad de nuestra base de datos.

A continuación se explica como crear y borrar usuarios en MySQL así como tambiénasignarles permisos.

Comando GRANT

Permite asignar privilegios y crear usuarios.

Ejemplo:

GRANT SELECT, INSERT ON *.* TO tester@'%' IDENTIFIED BY ‘blah’;

Con este comando creamos un usuario llamado tester que tiene el derecho de seleccionar einsertar datos en todas las bases de datos y tablas (*.*), con la contraseña blah.

El signo % después del nombre de usuario significa que tester podrá hacer login desdecualquier máquina. Si en su lugar hubiéramos escrito tester@localhost solo podría conectar

en modo local; y si solo queremos dar permiso a tester desde una máquina remota,deberíamos haber escrito el nombre de dicha máquina: [email protected]

Lenguaje SQL

Contenido



Contenido

El comando GRANT no solo concede permisos, sino que sirve para crear nuevos usuarios.Basta con conceder permisos a un usuario para que si este no existía, sea creado en la tablauser.

GRANT select, insert, update, create, alter, delete, drop ON directorio.* TO tester@localhost IDENTIFIED BY 'blah';

En este otro ejemplo damos permisos a tester si accede por localhost para seleccionar,insertar datos, actualizar, crear, modificar y borrar tablas en todas las tablas de la base de

datos directorio (directorio.*)

GRANT all ON directorio.alfa To tester@localhost IDENTIFIED BY 'Blah‘ with GRANTOPTION;

Concede todos (all) los permisos disponibles para la tabla alfa dentro de la base de datos

directorio a tester.

GRANT USAGE ON *.* To tester@localhost IDENTIFIED BY 'Blah';

Permite la conexión a tester a cualquier base de datos y tablas, pero no concede ningún

otro privilegio.

Lenguaje SQL

Contenido



Contenido

Comando REVOKEEs similar a Grant un su sintaxis, pero se usa para revocar o eliminar los privilegios.

REVOKE ALL ON *.* FROM tester@localhost;

Elimina todos los privilegios para el usuario tester en todas las bases de datos.

Otras instrucciones

Ver usuario actualselect current_user();

Ver Usuarios creadosselect user,host from mysql.user;

Ver privilegios de un usuarioshow grants for nombre_usuario@localhost

Eliminar usuariodrop user nombre_usuario@localhost

Lenguaje SQL

Contenido



Contenido

20.- Respaldo y restauración de MySql

MySql proporciona un utilitario llamado “mysqldump”. Básicamente lo que hace laherramienta es crear un archivo que contiene instrucciones SQL para restaurar su base dedatos. Ejemplos de uso de mysqldump:

mysqldump -u username -p database_name > file.sql

Para restaurar un respaldo creado con mysqldump , deberá usar el comando mysql . Si suarchivo de vuelco SQL no contiene ninguna sentencia “create database”, puede usar elsiguiente comando:

mysql -u username -p database_name < file.sql

Para que esto funcione, la base de datos debe estar creada, aunque sea sin datos.

Lenguaje SQL

Contenido



Contenido

21.- phpMyAdmin

21.1 DefiniciónphpMyAdmin es un programa de libre distribución en PHP, creado por una comunidad sinánimo de lucro, que sólo trabaja en el proyecto por amor al arte. Es una herramienta muycompleta que permite acceder a todas las funciones típicas de la base de datos MySQL através de una interfaz web muy intuitiva.

21.2 Historia

Tobias Ratschiller, por entonces un consultor IT y después fundador de Maguma, unacompañía de software, comenzó a trabajar en la elaboración de una red administrativabasada en PHP cliente-servidor enMySQL en 1998 e inspirado por Peter Kuppelwieser ysu MySQL-Webadmin. Cuando Ratschiller dejó el proyecto por falta de tiempo, así comoel phpAdsNew del cual es también su inventor, el phpMyAdmin se había convertido en unade las aplicaciones PHP más populares, y las herramientas de administración MySQL

constituían una gran comunidad de usuarios y administradores. Es de anotar que estoincluía una buena contribución por parte de distribuidores de Linux.Para coordinar el creciente número de parches, tres desarrolladores de software, OlivierMüller, Marc Delisle y Loïc Chapeaux, registraron el proyecto phpMyAdminen SourceForge.net y continuó su crecimiento en 2001

phpMyAdmin

Contenido



Contenido

21.3 Características

• Interfaz web intuitiva• Apoyo a la mayoría de las características de MySQL:

• buscar y eliminar bases de datos, tablas, vistas, campos e índices• crear, copiar, gota, cambiar el nombre y modificar bases de datos, tablas, campos e

índices• mantenimiento de servidores, bases de datos y tablas, con propuestas sobre la

configuración del servidor•

ejecutar, editar y marcar cualquier SQL -declaración, incluso por lotes-consultas• administrar usuarios y privilegios de MySQL• gestionar los procedimientos almacenados y disparadores

• Importar datos desde CSV y SQL• Exportar datos a varios formatos: CSV , SQL , XML , PDF , ISO / IEC 26300 -

OpenDocument Text y hoja de cálculo, Word , L A T E X y otros• La administración de múltiples servidores• Crear PDF de gráficos de su diseño de base de datos• Creación de consultas complejas utilizando Consulta-por-Ejemplo (QBE)• Buscando a nivel mundial en una base de datos o un subconjunto de ésta• La transformación de los datos almacenados en cualquier formato utilizando un conjunto

de funciones predefinidas, como mostrar-datos BLOB como imagen o el link de descarga• Y mucho más ...

phpMyAdmin

Contenido



Contenido

21.4 InterfacephpMyAdmin

Contenido



Contenido

21.4 Interface

(1) El botón de inicio, que siempre nos traerá a esta primera página, el “home”. Hacia laderecha tenemos los botones para salir de phpMyAdmin,

(2) Pestañas para acceder a diferentes acciones, estás van a variar dependiendo del trabajoque estemos efectuando, no ejecutan acciones por si mismas.(3) Lista de las bases de datos, en algunas versiones se muestran en una lista desplegable(combo), los números entre paréntesis indica el número de tablas en cada BD.(4) Acciones, para cambiar la contraseña y para salir de esta sesión de phpMyAdmin.

(5) Formulario para comenzar a diseñar una nueva base de datos(6) Opciones para personalizar la interfaz de tu phpMyAdmin.(7) Información y documentación de los servicios MySQL, Apache y phpMyAdmin.

phpMyAdmin

Contenido



Contenido Ejemplo Base de Datos Libros (dblibros)



Instituto Tecnológico

de La Universidad de

Los Lagos

Técnico Universitario en

Computación

programación orientada a base de datos - sql

Documents