MySQL 5.0

1. MySQL 5.0 Milton Labanda 1000ton.lab@gmail.com UNIVERSIDAD NACIONAL DE LOJA Carrera de Ingeniería en Sistemas Enero 2006

2. Objetivos • Actualizar conocimientos relacionados al currículum. • Revisar algunas novedades en la v 5.0 • Explotar el uso de herramientas relacionadas

3. ¿Ventajas de MySQL? • Es un Sistema Gestor de Bases de Datos disponible en la mayoría de plataformas. • El servidor de Bases de Datos Open Source más popular. • Liviano y rápido: adecuado para aplicaciones Web. • Cuenta con APIs disponibles para C, C++, Perl, PHP, Python, Java, .NET

4. Estado de MySQL • Anterior versión estable: • 4.0 y 4.1 • Actual versión estable: • 5.0 • Futura versión estable: • 5.1 • Se sigue utilizando las versiones 4.0 y 4.1

5. Novedades de la Versión 5.0 • Múltiples Instancias del Servidor • Stored Procedures (Procedimientos Almacenados) • Triggers (Disparadores) • Views (Vistas) • Acceso al Metadata de las BD

6. Configuración de Múltiples Instancias • Archivo ‘my.ini’ ####################################### # Servidor 1 # ###################################### [mysqld1] datadir = "C:/mydata1/" port = 3307 enable-named-pipe socket = pipe1 ####################################### # Servidor 2 # ####################################### [mysqld2] datadir = "c:/mydata2/" port = 3308 enable-named-pipe socket = pipe2

7. Configuración de Múltiples Instancias • Instalación de las instancias como servicios de windows: • mysqld-nt - -install mysqld1 • mysqld-nt - -install mysqld2 • Arranque de las Instancias del servidor: • NET START mysqld1 • NET START mysqld2

8. Stored Procedures Un procedimiento almacenado es una rutina (procedimiento o función) que es almacenada dentro de la base de datos. Sentencias: CREATE PROCEDURE/FUNCTION ALTER PROCEDURE/FUNCTION DROP PROCEDURE/FUNCTION

9. Stored Procedures: Sintaxis • Sintaxis de Creación: CREATE PROCEDURE nombre_proc ([parametro …]) [característica …] cuerpo_proc CREATE FUNCTION nombre_func ([parametro …]) RETURNS tipo_dato [característica …] cuerpo_func

10. Stored Procedures • Cuerpo de la Rutina • Especifica que es lo que hará la rutina • Puede contener una o más sentencias SQL • Puede incluir sentencias DECLARE • Sintaxis del cuerpo BEGIN [DECLARE variables, cursores, condiciones] [Sentencias SQL] END

11. Stored Procedures: Ejemplos CREATE PROCEDURE p1 () BEGIN DECLARE v1 INT DEFAULT 10; SET v1 = 5; INSERT INTO t VALUES (v1); END;

12. Stored Procedures: Ejemplos CREATE PROCEDURE copiar () BEGIN DECLARE var1 VARCHAR(34); DECLARE c1 CURSOR FOR SELECT name FROM test.t; OPEN c1; REPEAT FETH c1 INTO var1; …. END;

13. Stored Procedures CREATE FUNCTION saludar (nombre CHAR(20)) RETURNS CHAR (40) BEGIN DECLARE saludo CHAR(40); SET saludo = CONCAT('Hola, ‘ ,UPPER(nombre), ', como estas?'); RETURN saludo; END

14. Stored Procedures • Invocar a un Procedimiento almacenado: • call nombre_proc([params, …]) • Ejemplo: • CALL proc1( ); • Invocar a una función almacenada: • Generalmente: select nombre_func([params, …]) • Ejemplo: • SELECT saludar(‘Enrique’);

15. Stored Procedures: Sentencias de Control • IF – si <condicion> hacer <acción> • CASE – selección alternativa, dependiente • LOOP – hacer <acción> repetidamente • LEAVE – terminación de un bucle o bloque • ITERATE – reinicia un bucle • REPEAT – repetir <acción> hasta <condición> sea verdadera • WHILE – repetir <action> mientras <condición> sea verdadera

16. Stored Procedures • Ventajas: • Mejorar el Desempeño • Ahorrar tiempo, dinero y recursos

17. Triggers Un Trigger es una acción en cadena que empieza cuando un evento específico ocurre sobre una tabla específica.

18. Triggers: Sintaxis CREATE TRIGGER nombre_trigger {BEFORE | AFTER} {INSERT | UPDATE | DELETE} ON nombre_table FOR EACH ROW acción_trigger | sentencias SQL

19. Triggers: Usos • Registrar, auditar y monitorear la actividad de cambio de datos • Validar datos, cambiando o negando acciones como INSERT, UPDATE, DELETE en una tabla • Preservar la consistencia y claridad de los datos ejecutando acciones relacionadas en tablas relacionadas

20. Triggers: Ejemplos CREATE TRIGGER info AFTER INSERT ON test.t FOR EACH ROW SET @ultimo = NEW.id;

21. Triggers: Ejemplos CREATE TRIGGER update_check BEFORE UPDATE ON test.t FOR EACH ROW BEGIN IF LENGTH(NEW.name) = 0 THEN SET NEW.name = 'Nombres'; END IF; END;

22. Vistas • Una Vista es una tabla virtual • Provee otra manera de buscar en los datos existentes • No usan espacio extra en disco • La sintaxis de las Vistas MySQL son 100% compatibles con el SQL standard.

23. Vistas: Sintaxis CREATE [OR REPLACE] VIEW nombre_vista [(lista_columnas)] AS sentencia_select [WITH [CASCADED | LOCAL] CHECK OPTION]

24. Vistas: Ejemplos

25. Vistas: Ejemplos • WITH CHECK OPTION CREATE VIEW v1 AS SELECT col1 FROM t1 WHERE col1 <> 'A' WITH CHECK OPTION; mysql> UPDATE v1 SET col1 = 'A'; ERROR 1369 (HY000): CHECK OPTION failed ‘test.v1'

26. Vistas: Ejemplos • WITH LOCAL CHECK OPTION CREATE VIEW v2 AS SELECT * FROM v1 WHERE col1 <> 'B' WITH LOCAL CHECK OPTION; mysql> INSERT INTO v2 VALUES ('B'); ERROR 1369 (HY000): CHECK OPTION failed ‘test.v2' mysql> INSERT INTO v2 VALUES ('A'); Query OK, 1 row affected (0.01 sec)

27. Vistas: Ejemplos • WITH CASCADED CHECK OPTION CREATE VIEW v3 AS SELECT * FROM v2 WHERE col1 <> 'C' WITH CASCADED CHECK OPTION; mysql> INSERT INTO v3 VALUES ('B'); ERROR 1369 (HY000): CHECK OPTION failed ‘test.v3' -- porque las condiciones de v2 no permiten 'B' y v3 depende de v2 mysql> INSERT INTO v3 VALUES ('A'); ERROR 1369 (HY000): CHECK OPTION failed ‘test.v3' -- porque las condiciones de v1 no permiten 'A' and v3 depende de v1 y v2

28. Vistas: Actualización • 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.

29. Vistas: Usos • 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.

30. INFORMATION_SCHEMA El Esquema de Información es la forma SQL standard para acceder el metadata de la base de datos. • Contiene una serie de tablas que describen los objetos de la base de datos • Es una base de datos virtual visible a través de sentencias SELECT

31. INFORMATION_SCHEMA • Ventajas: • Standarización del manejo del metadata, quedando atrás el uso de comandos SHOW específicos de MySQL • No se necesita aprender un nuevo conjunto de comandos para acceder el metadata.

32. INFORMATION_SCHEMA • Visualización:

33. INFORMATION_SCHEMA • Objetos mas importantes: • SCHEMATA • TABLES • VIEWS • ROUTINES • COLUMNS

34. INFORMATION_SCHEMA • Objetos mas importantes:

35. MySQL Tips • Mostrar resultados verticalmente: \G

36. MySQL Tips • Prevenir resultados inesperados al usar sentencias de modificación: Usar la opción -U al conectarse C:>mysql –u usuario –p –U Obliga a usar la cláusula WHERE al realizar un UPDATE O DELETE.

37. MySQL Tips • Cambiar la contraseña del superusuario ‘root’ y renombrar el usuario c:> mysqladmin -u root password ‘nueva_clave’ mysql> update mysql.user -> set user = ‘admin’ -> where user = ‘root’

38. MySQL Tips • Proteger el directorio de instalación del acceso de otros usuarios. • Apegarse al ANSI SQL si se requiere portabilidad • No mezclar el código de la interfaz del usuario con código de bases de datos. • Usar pool de conexiones en Servidores Web y Servidores a Aplicaciones

39. MySQL Tips • Ejecutar sentencias sin entrar al cliente mysql C:> mysql –u root –p –e “select * from test.t” • Exportar a XML C:> mysql –u user –p –e “sentencias” –X > archivo.xml • Exportar a HTML C:> mysql –u user –p –e “sentencias” –H > archivo.html

40. GRACIAS!