domingo, 20 de mayo de 2012


VISTAS Y COMANDOS EN MYSQL.

Una vista es un objeto de la base de datos que se define mediante una SELECT que agrupa o selecciona un conjunto de datos. Vamos a ver cómo usarlas.
Creando una vista: Se emplea la sentencia CREATE VIEW, que incluye una su consulta (subquery) para determinar los datos a ser mostrados a través de la vista.
Sintaxis:
CREATE [OR REPLACE] [FORCE | NOFORSE] VIEW <vista>
[(<alias>[, <alias>] … )]
AS <subconsulta>
[WITH CHECK OPTION [CONSYTAINT <restricción>]]
[WITH READ ONLY [CONSTRAINT <restricción>]];
Donde: OR REPLACE Se utilice por si la vista por si la vista ya estuviera creada anteriormente. En ese caso, la sustituye por la nueva definición.
FORCE Crea la vista sin comprobar si las tablas base existen.
NO FORCE Crea la vista sólo si las Tablas base de donde se extraen los datos existen realmente (es la opción por defecto).

<vista> Es el nombre de la vista.

< alias> Especifica alias para las expresiones /columnas seleccionadas por la subconsulta. El numero de alias debe coincidir con el numero de expresiones seleccionadas por la vista.

<subconsulta> Es una sentencia SELECT completa. Se pueden emplear alias para las columnas en la lista que sigue SELECT.
WITH CHECK
OPTION Especifica que solo las filas accesibles para la vista pueden ser insertadas o modificadas.
READ ONLY.
WHITH READ
ONLY Asegura que no podrán ejecutarse operaciones de DML a través de la vista. La vista solo permite consultas.
Visualizar la Estructura de Una Vista:
DESCRIBE <vista >;
Donde: <vista > Es el Nombre de la vista.
Listar las vistas existentes: SELECT¨ * FROM USER_VIEWS;
Modificando La Definición de Una Vista:
Para modificar no es necesario eliminarla previamente, basta con ejecutar una nueva sentencia CREATE VIEW que incluya la opcion “OR REPLACE” y la subconsulta Modificada.
Ejemplo: Cambiar la Vista EmpDepVentas creada en el apartado 3 para que incluya también el salario.
CREATE OR REPLACE VIEW EmpDepVentas
As SELECT apelidos, nombre, salario
FROM Empleados
WHERE dep=”Ventas”
WHIT CHECH OPTION;
Eliminando Una Vista.
Cuando ya no se emplea mas. Una Vista puede ser eliminada del esquema de la base de datos mediante la siguente orden:
DROP VIEW <vista >;
Donde <vista > Es el Nombre de la vista.
Ejemplo
DROP VIEW EmpDepVentas;
COMANDOS DE MySQL
mysql envía comandos SQL al servidor para que sean ejecutados. También hay un conjunto de comandos que mysql interpreta por sí mismo. Para obtener una lista de estos comandos, se escribe help o \h en el prompt mysql>:
mysql> help
MySQL commands:
? (\h) Synonym for `help'.
clear (\c) Clear command.
connect (\r) Reconnect to the server.
Optional arguments are db and host.
delimiter (\d) Set query delimiter.
edit (\e) Edit command with $EDITOR.
ego (\G) Send command to mysql server,
display result vertically.
exit (\q) Exit mysql. Same as quit.
go (\g) Send command to mysql server.
help (\h) Display this help.
nopager (\n) Disable pager, print to stdout.
notee (\t) Don't write into outfile.
pager (\P) Set PAGER [to_pager].
Print the query results via PAGER.
print (\p) Print current command.
prompt (\R) Change your mysql prompt.
quit (\q) Quit mysql.
rehash (\#) Rebuild completion hash.
source (\.) Execute an SQL script file.
Takes a file name as an argument.
status (\s) Get status information from the server.
system (\!) Execute a system shell command.
tee (\T) Set outfile [to_outfile].
Append everything into given outfile.
use (\u) Use another database.
Takes database name as argument.
warnings (\W) Show warnings after every statement.
nowarning (\w) Don't show warnings after every statement.
A continuación, intentaré nombrar algunos comandos útiles para ejecutar en MySQL. Cabe destacar, que estos comandos pueden ser ejecutados desde el shell del sistema operativo ($) y/o desde el shell de MySQL (mysql>). Por una cuestión de orden, los comandos serán clasificados en:
Descripción del sistema, bases de datos y tablas.
·         mysql> show databases;

·         mysql> show tables;

·         mysql> show columns from nombre_de_tabla;

·         mysql> show variables;

·         mysql> show grants for usuario@host;

·         mysql> show columns from base_de_datos.nombre_de_tabla;

·         mysql> show privileges;

·         mysql> show character set;

·         mysql> describe nombre_de_tabla

Definición de datos:
mysql> drop database nombre_de_base_de_datos;
mysql> drop table nombre_de_tabla;
mysql> alter table nombre_de_tabla drop column nombre_de_columna;
mysql> alter table nombre_de_tabla add column nombre_de_columna tipo_de_dato;
mysql> alter table nombre_de_tabla change nombre_de_columna_original nombre_de_columna_nuevo tipo_de_dato;
mysql> alter table nombre_de_tabla add unique (nombre_de_columna);
mysql> alter table nombre_de_tabla modify nombre_de_columna tipo_de_dato;

Administración
$ mysqladmin -u root -h host -p password ‘nuevo_password
$ mysqladmin extended-status
$ mysqladmin status
$ mysqladmin variables
$ mysqladmin version
$ mysqladmin create base_de_datos
$ mysqladmin drop base_de_datos
$ mysqladmin flush-privileges
$ mysqladmin ping
$ mysqladmin reload
$ mysqladmin kill id_proceso, id_proceso
$ mysqladmin shutdown

Backups e importación de datos
$ mysql base_de_datos< backup.sql
$ mysqldump base_de_datos [tablas] > backup.sql
$ mysqldump –no-data base_de_datos [tablas] > backup.sql
$ mysqldump –add-drop-table base_de_datos [tablas] > backup.sql
$ mysqldump –compatible=mysql40 base_de_datos [tablas] > backup.sql
mysql> LOAD DATA INFILE ‘/tmp/archivo.csv’ REPLACE INTO TABLE [nombre_de_tabla] FIELDS TERMINATED BY ‘,’ LINES TERMINATED BY ‘\n’ (campo1,campo2,…)



MANEJO DE BASES DE DATOS MYSQL:
drop table [table]; – Elimina la tabla, incluyendo registros y estructura.
drop table if exists [table]; – Elimina la tabla de la base de datos, pero antes verifica que exista.
truncate table [table]; – Elimina los registros, pero mantiene la esrtuctura de la tabla.
rename table [table] to [nuevo nombre de tabla]; – Renombra una tabla de la base de datos.
ALGUNOS COMANDOS UTILES PARA CUNSULTAS MYSQL:
ü select * from [table] limit [numero]; – Muestra los registros desde el 1 hasta [numero].
Ej. select * from tabla limit 10; – Muestra los 10 primeros registros.
select * from [table] limit [numero inicio],[numero]; – Muestra los registros desde el numero de inicio hasta numero inicio + numero.
Ej. select * from tabla limit 11,10; – Muestra desde registro 11 hasta el 20.
BASES DE DATOS MYSQL EN CONSOLA:
$ mysqladmin -u -p create – crear base de datos.
$ mysqladmin -u -p drop – borrar la base de datos.
$ mysqladmin -u root -p proc – listar procesos en ejecucion en el servidor de bases de datos Mysql.
$ mysqladmin -u root -p -i 5 status – verificar status cada 5 segundos.
$ mysqldump –opt -u -h -p > /path/to/file – Exportar base de datos a un archivo.
$ mysqldump –opt -u -h –all-databases -p > /path/to/file – Exportar TODAS las bases de datos a un archivo.
$ mysql -h -u -p < /path/to/file – Importar un archivo a la base de datos a mysql
$ mysqlcheck -o -u root -p –all-databases – Optimizar las bases de datos mysql.
VERIFICACION Y REPARACION DE BASES DE DATOS ERRONEAS:
check table [table]; – Verificar la tabla.
repair table [table]; – Reparar la tabla

 CONTROL DE TRANSACCIONES.
QUE ES UNA TRANSACCIÓN. Una transacción es una unidad de la ejecución de un programa. Puede consistir en varias Operaciones de acceso a la base de datos. Está delimitada por constructoras como begin-transaction y end-transaction.
ESTADOS DE UNA TRANSACCIÓN.
Activa: Durante su ejecución
Parcialmente comprometida: Después de ejecutar su última instrucción.
Fallida: Imposible de continuar su ejecución normal.
Abortada: Transacción retrocedida y base de datos restaurada al estado anterior a su
 PROPIEDADES FUNDAMENTALES DE UNA TRANSACCIÓN:
1. Atomicidad: Se refiere al hecho de que una transacción se trata como una unidad de operación. . Por lo tanto, o todas las acciones de la transacción se realizan o ninguna de ellas se lleva a cabo. La atomicidad requiere que si una transacción se interrumpe por una falla, sus resultados parciales sean anulados.
2. Consistencia: La consistencia de una transacción es simplemente su correctitud.
En otras palabras, una transacción es un programa correcto que lleva a la base de datos de un estado consistente a otro con la misma característica.
Debido a esto, las transacciones no violan las restricciones de integridad de una base de datos.
3. Aislamiento: Una transacción en ejecución no puede revelar sus resultados a otras transacciones concurrentes antes de finalizar. Más aún, si varias transacciones se ejecutan concurrentemente, los resultados deben ser los mismos que si ellas se hubieran ejecutado de manera secuencial.
4. Permanencia Es la propiedad de las transacciones que asegura que una vez que una transacción finaliza exitosamente, sus resultados son permanentes y no pueden ser borrados de la base de datos por alguna falla posterior.
Por lo tanto, los sistemas manejadores de base de datos aseguran que los resultados de una transacción sobrevivirán a fallas del sistema.
Esta propiedad motiva el aspecto de recuperación de base de datos, el cual trata sobre cómo recuperar la base de datos a un estado consistente donde todas las acciones que han finalizado con éxito queden reflejadas en la base.
5. Confiabilidad: Puesto que los sistemas de base de datos en línea no pueden fallar.
6. Disponibilidad: Debido a que los sistemas de base de datos en línea deben estar actualizados correctamente todo el tiempo.
7. Permanencia: No se permite la eliminación en la base de datos de los efectos de una transacción que ha culminado con éxito.
 GRADOS DE CONSISTENCIA
Consistencia. En aquellos casos en los que no se ha logrado eliminar la redundancia, será necesario vigilar que aquella información que aparece repetida se actualice de forma coherente, es decir, que todos los datos repetidos se actualicen de forma simultánea. Por otra parte, la base de datos representa una realidad determinada que tiene determinadas condiciones, por ejemplo que los menores de edad no pueden tener licencia de conducir. El sistema no debería aceptar datos de un conductor menor de edad. En los SGBD existen herramientas que facilitan la programación de este tipo de condiciones.
Consistencia. Una transacción mantendrá la consistencia de la base de datos. Esto es, si la base de datos se encuentra en un estado consistente antes de ejecutar la transacción, una vez que ésta termine la consistencia de la base de datos deberá conservarse. Por consistente se debe entender, internamente consistente. En términos de base de datos esto significa que se satisfacen todas las restricciones en cuanto a su integridad que incluyen:
1.     Todos los valores de la llave primaria son únicos.
2.     La base de datos mantiene integridad referencial lo que significa que los registros solo referencian información que existe.
3.     Ciertos predicados se mantienen. Por ejemplo, la suma de los gastos es menor o igual al presupuesto.
A diferencia de la atomicidad, el aislamiento y la durabilidad, la consistencia es una práctica de programación. La atomicidad, el aislamiento y la durabilidad están aseguradas estén o no programadas para preservar la consistencia. Es responsabilidad del desarrollador de la aplicación asegurar que su programa preserva la consistencia.
Consistencia de datos: Eliminando o controlando las redundancias de datos se reduce en gran medida el riesgo de que haya inconsistencias. Si un dato está almacenado una sola vez, cualquier actualización se debe realizar sólo una vez, y está disponible para todos los usuarios inmediatamente. Si un dato está duplicado y el sistema conoce esta redundancia, el propio sistema puede encargarse de garantizar que todas las copias se mantienen consistentes.
NIVELES DE AISLAMIENTO.
Las transacciones especifican un nivel de aislamiento que define el grado en que se debe aislar una transacción de las modificaciones de recursos o datos realizados por otras transacciones.
Los niveles de aislamiento se describen en cuanto a los efectos secundarios de la simultaneidad que se permiten como las lecturas desfasadas o ficticias
NIVELES DE AISLAMIENTO:
*lectura no comprometida o nivel menor.
*lectura comprometida.
*lectura repetible.
*secuenciable o nivel mayor.
NIVEL DE AISLAMIENTO MENOR: Significa que muchos usuarios pueden tener acceso a los datos. Simultáneamente, pero también aumenta los efectos de simultaneidad que pueden experimentar, como lecturas no confirmadas o perdida de actualizaciones.
NIVEL DE AISLAMIENTO MAYOR: reduce los tipos de efectos de simultaneidad, pero requiere más recursos de sistema y aumenta las posibilidades de que una transacción bloque otra.
NIVEL DE AISLAMIENTO SUPERIOR: Garantiza que una transacción recuperara los mismos datos cada vez que repita una operación de lectura, aunque para ello aplicara un nivel de bloqueo que puede afectar a los demás usuarios en los sistemas multiusuario.
LOS NIVELES DE AISLAMIENTO DE TRANSACCIÓN CONTROLAN LOS SIGUIENTES ASPECTOS.
1.-si se realizan bloqueos cuando se leen los datos y que tipos de bloqueos se solicitan.
2.-la duración de los bloqueos de lectura.
3.-si una operación de lectura que hace referencia a filas modificadas por otra transacción
 INSTRUCCIONES COMMIT Y ROLLBACK
Una transacción tiene dos posibles salidas:
– Committed
  • Todas las modificaciones quedan en firme
– Rolled back
  • Las modificaciones retornan a su estado inicial
Para realizar transacciones de base de datos en Visual Basic, puede utilizar el BeginTrans, CommitTrans, Rollback instrucciones. BeginTrans comienza una nueva transacción.
CommitTrans finaliza la transacción actual. Deshacer termina la transacción actual y restaura la base de datos de estado que tenía justo antes de que comenzó la transacción actual.

Una transacción es una serie de cambios que realice a una base de datos que desea tratar como una unidad completa. Una transacción comienza cuando se utiliza la instrucción BeginTrans. Utilizar Deshacer para deshacer los cambios realizados durante la transacción actual y CommitTrans para aceptar los cambios y finalizar la transacción actual. Deshacer y CommitTrans finalizar una transacción. Una vez utiliza CommitTrans, no se puede deshacer los cambios realizados durante esta transacción.
Si utiliza instrucciones CommitTrans o Rollback sin primero utilizando BeginTrans, se produce un error. Si utiliza Rollback, debe utilizar la actualización en cualquier control de datos que hace referencia a datos que haya cambiado desde que comenzó la transacción.

Una transacción tiene dos finales posibles, COMMIT y ROLLBACK. Por defecto, MySQL trae activado el modo autocommit, es decir, realizada una transacción (por ejemplo un INSERT, UPDATE o DELETE) el mismo es confirmado apenas es ejecutado. Para desactivar el autocommit, se puede desactivar el autocomit ejecutando el comando:
SET AUTOCOMMIT=0;
Una vez deshabilitado el autocommit, tendremos que utilizar obligatoriamente el COMMIT para confirmar o ROLLBACK para deshacer la transacción.
Si se quiere deshabilitar el autocommit para una serie de comandos, lo ideal es utilizar START TRANSACTION (sin necesidad de setear el AUTOCOMMIT en 0).
Al ejecutar una transacción, el motor de base de datos nos garantizará la atomicidad, consistencia, aislamiento y durabilidad (ACID) de la transacción (o conjunto de comandos) que se utilice.
DEFINICIÓN Y OBJETIVO DE LAS VISTAS.
Una vista de base de datos es un resultado de una consulta SQL de una o varias tablas; también se le puede considerar una tabla virtual.
Las vistas tienen la misma estructura que una tabla: filas y columnas. La única diferencia es que sólo se almacena de ellas la definición, no los datos. Los datos que se recuperan mediante una consulta a una vista se presentarán igual que los de una tabla. De hecho, si no se sabe que se está trabajando con una vista, nada hace suponer que es así. Al igual que sucede con una tabla, se pueden insertar, actualizar, borrar y seleccionar datos en una vista. Aunque siempre es posible seleccionar datos de una vista, en algunas condiciones existen restricciones para realizar el resto de las operaciones sobre vistas.
Una vista se especifica a través de una expresión de consulta (una sentencia SELECT) que la calcula y que puede realizarse sobre una o más tablas. Sobre un conjunto de tablas relacionales se puede trabajar con un número cualquiera de vistas.
La mayoría de los DBMS soportan la creación y manipulación de vistas.
Las vistas, además de reducir la complejidad permitiendo que cada usuario vea sólo la parte de la base de datos que necesita, tienen otras ventajas:
  • Las vistas proporcionan un nivel de seguridad, ya que permiten excluir datos para que ciertos usuarios no los vean.
  • Las vistas proporcionan un mecanismo para que los usuarios vean los datos en el formato que deseen.
  • Una vista representa una imagen consistente y permanente de la base de datos, incluso si la base de datos cambia su estructura.
 INSTRUCCIONES PARA LA ADMINISTRACIÓN DE VISTAS.
INSTRUCCIONES PARA REALIZAR UNA VISTA.
create [or replace]
view nombre_vista
[(lista_columnas)]
as sentencia_select
[with [cascaded | local] check option]
SINTAXIS PARA CREATE WIVE.
CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
    VIEW
 nombre_vista  [(
 columnas )]
    AS
 sentencia_select
    [WITH [CASCADED | LOCAL] CHECK OPTION]

Esta sentencia crea una vista nueva o reemplaza una existente si se incluye la cláusula OR REPLACE . La sentencia_select es una sentencia SELECT que proporciona la definición de la vista. Puede estar dirigida a tablas de la base o a otras vistas.
Se requiere que posea el permiso CREATE VIEW para la vista, y algún privilegio en cada columna seleccionada por la sentencia SELECT . Para columnas incluidas en otra parte de la sentencia SELECT debe poseer el privilegio SELECT . Si está presente la cláusula OR REPLACE , también deberá tenerse el privilegio DELETE para la vista.
Toda vista pertenece a una base de datos. Por defecto, las vistas se crean en la base de datos actual. Pera crear una vista en una base de datos específica, indíquela con base_de_datos.nombre_vista al momento de crearla.
        mysql> CREATE VIEW test.v AS SELECT * FROM t;
   

Las tablas y las vistas comparten el mismo espacio de nombres en la base de datos, por eso, una base de datos no puede contener una tabla y una vista con el mismo nombre.
Al igual que las tablas, las vistas no pueden tener nombres de columnas duplicados. Por defecto, los nombres de las columnas devueltos por la sentencia SELECT se usan para las columnas de la vista . Para dar explícitamente un nombre a las columnas de la vista utilice la clásula columnas para indicar una lista de nombres separados con comas. La cantidad de nombres indicados en columnas debe ser igual a la cantidad de columnas devueltas por la sentencia SELECT .
ACTUALIZACIÓN DE UNA VISTA.
ü Una Vista actualizable puede aceptar sentencias UPDATE, DELETE
ü Algunas vistas pueden permitir INSERT
ü Los cambios de los datos se propagan automáticamente a la tabla base.
USOS DE LAS VISTAS.
ü Las vistas pueden ser usadas en lugar de definir restricciones utilizando por ejemplo NOT NULL, BETWEEN, etc.
ü Pueden usarse en la reestructuración de tablas, ocultando o moviendo columnas.
ü Permiten implementar seguridad en los datos al ocultar registros.
SINTAXIS DE ALTER VIEW.
alter [algorithm = {undefined | merge | temptable}]
    view
 nombre_vista  [(
 columnas )]
    as
 sentencia_select
    [with [cascaded | local] check option]
SINTAXIS DE SHOW CREATE VIEW
SHOW CREATE VIEW
 nombre_vista
Muestra la sentencia que se utilizó para crear la vista.
mysql> SHOW CREATE VIEW v;
+-------+----------------------------------------------------+
| Table | Create Table                                       |
+-------+----------------------------------------------------+

No hay comentarios:

Publicar un comentario