Diferencia entre revisiones de «Mysql 2019»

Revisión actual del 08:55 3 may 2019

Enlaces

MySQL en Ubuntu 14.04 LTS

Desarrollo de base de datos para catalogo de biblioteca personal

Tutoriales Derek Banas youtube:
- Intro uso MySQL: https://www.youtube.com/watch?v=yPu6qV5byu4 | vídeo transcript i/ code __ Thanks, Derek, amazing tutorial!!!
- DB, Intro a php: https://www.youtube.com/watch?v=7TF00hJI78Y
- DB, Intro a php+MySQL: https://www.youtube.com/watch?v=mpQts3ezPVg

Reference Manual MySQL Server 5.5 https://dev.mysql.com/doc/refman/5.5/en/

Nota: Me gustaría quizás conectarlo vía Python con algun sistema de visualización... poderlo visualizar con Processing... ya veremos. Igual no tengo ni idea...

Contenido

1 Instalación software
2 Nuevo usuario en MySQL Workbench
3 Empezar con la consola de Linux
4 CREATE DATABASE, USE, crear y usar una nueva base de datos
5 Diseño de la base de datos
6 CREATE TABLE, DESCRIBE, crear y ver tablas
7 INSERT, insertar entradas en tablas
8 Modificar variables y datos en tablas ya creadas
9 SELECT ... FROM, ver datos / generar otras tablas
10 JOIN, juntar datos de diferentes tablas
- 10.1 Modelo para catálogo de librería con LEFT JOIN

Instalación software

En Software Center de Ubuntu instalé:

MySQL Server

MySQL Client (no estoy seguro si necesito)

MySQL Workbench (una aplicación con GUI...)

Salvo error de memoria (mía) al instalar el MySQL Server aparecen unos cuadros de diálogo pidiendo que se introduzca una password para el usuario root, y se confirme. Con este usario / password se accede a las aplicaciones luego, independientemente de que se creen otros más adelante.

Nuevo usuario en MySQL Workbench

Viendo el tutorial de D. Banas que no explica bien cómo empezar con usuarios de demás... Parece que el interfaz del MySQL Workbench en Ubuntu es bastante diferente del que aparece en el tutorial, quizás en Windows o Mac.

Al instalar el MYSQL Workbench me aparece un launcher en la barra principal de programas que permite arrancarlo con un click.

Aquí voy a trabajar en la propia máquina / ordenador, que se reconoce como localhost, o por su IP, 127.0.0.1

En mi Workbench, un poco a ciegas, trato de localizar el local host y crear un nuevo usuario. Voy a Database/Connect to Database - ahí creo que sale el localhost por defecto (fig 1), abriéndose una nueva ventana tras pedir el pw de root.

Crear nuevo usuario: En la nueva ventana de workbench (pestañas arriba con las diferentes ventanas abiertas, en la mía dice: Mysql@127.0.0.1:3306x - esto sale sólo, 3306 es el puerto de conexión), en Management/Users and Privileges, se abre nueva ventana; buscamos añadir nuevo usuario, se elige nombre y password, y de momento copié los parámetros del usuario root; lógicamente habrá que ver que permisos tienen los nuevos usuarios, pero eso lo haré más adelante.

Para hacerlo vía consola & MySQL: https://dev.mysql.com/doc/refman/5.5/en/create-user.html _ tendría que mirarlo mejor...

Empezar con la consola de Linux

Ahora me paso a la consola de Linux (terminal), para seguir el primer tutorial citado.

Para abrir mysql con el usuario creado; el nombre de mi nuevo usuario es test:

   ~$ mysql -u test -p

-u es el usuario test, el nombre del usuario con el que queremos acceder a mysql; podríamos hacerlo con root que es el otro que tenemos. -p es que pida el password

La consola responde pidiendo el password que se debe introducir.

Introducido correctamente, salen unas cuantas líneas de código deonde vemos la versión de MySQL instalada, en mi caso: Server version: 5.5.62-0ubuntu0.14.04.1 (Ubuntu)tecler

En el manual de referencia: https://dev.mysql.com/doc/refman/5.5/en/connecting-disconnecting.html

El comando de arriba nos conecta con el mysql en localhost (el ordenador en el que estamos trabajando).

Para conectar a una base de datos remota (en otro ordenador) llevaría el modificador -h con el nombre? o dirección ip? del host, más o menos ~$ mysql -h nombre_host -u nombre_usuario -p

Ahora el "prompt" que aparece es así:

   mysql>

La convención parece ser escribir los comandos en mayúsculas; estos se cierran siempre con ";" - punto y coma.

Para ver la versión puede teclearse el siguiente comando - la segunda parte no hace falta, claro:

   mysql> SELECT VERSION(), CURRENT_DATE;

Para salir de MySQL se teclearía lo siguiente:

   mysql> QUIT

Para salir si está bloqueado, por ejemplo, sirve: Ctrl-C

Para probar que la instalación funciona.

   mysql> SHOW DATABASES;

Mostrará las bases de datos; aparece una tabla con al menos 3 databases por defecto?, - que son del propio sistema.

   mysql> SHOW GRANTS;

Nos muestra los permisos del usuario que estamos usando.

CREATE DATABASE, USE, crear y usar una nueva base de datos

   mysql> CREATE DATABASE catalogo;

Donde catalogo es el nombre de la base de datos a crear

Dando a enter, el sistema debe contestar, Query OK, ..., confirmando que se ha creado.

Si tecleamos de nuevo mysql> SHOW DATABASES; confirmaremos que ya está en la tabla.

Para trabajar en la nueva base de datos creado o en cualquier otra teclearíamos:

   mysql> USE catalogo;

y para comprobar en que base de datos estamos trabajando,

   mysql> SELECT DATABASE();

que muestra la base de datos con la que estamos trabajando.

Diseño de la base de datos

Recomienda el autor del tutorial diseñar "a mano" cómo queremos que se vean finalmente nuestros datos; a partir de ahí, éstos se organizarán o distribuirán en tablas de manera que cada input sea lo más sencillo posible, y las relaciones entre datos también...

Esto parece una parte delicada... y comprender cómo se organizan los datos, importante. Seguramente haré un beta más o menos rápido, y tras ver cómo funciona, una segunda versión que esté mejor.

Conceptos que parecen ser importantes y que tengo que entender mejor...

Primary key

Atomic table

...

Foreign key

CREATE TABLE, DESCRIBE, crear y ver tablas

Recordar que estemos en la base de datos que corresponda:

   mysql> USE catalogo;

Para crear una tabla, le damos nombre, y definimos las variables o campos (fields) que se van incluir en la tabla, así como sus características respectivas:

   mysql> CREATE TABLE nombretabla (
       -> variable1 características,
       -> variable2 características,
       -> ...
       -> variablex características);

Principales tipos de variables (tipos de datos). Los que se usan aquí:

Numéricos (MEDIUMINT UNSIGNED, FLOAT...)
Strings (CHAR(), VARCHAR(), ENUM()...) _ CHAR for character
Fechas y horas (YEAR; TIMESTAMP...)

El manual de referencia aquí (MySQL Server versión 5.5): https://dev.mysql.com/doc/refman/5.5/en/data-types.html

...

Una coma "," tras cada variables. Se parece a una lista de Python, pero con ().

NOT NULL / NULL, nos dice si tiene que dársele un valor obligatoriamente (NOT NULL) o no (NULL).

Una de las variables de la tabla debe ser un identificador único o PRIMARY KEY, que nos servirá para conectar los valores de esta tabla con los de otras tablas de la base de datos. La PRIMARY KEY la definimos aquí mediante el adjetivo sub-variable AUTO_INCREMENT, de manera que cada nueva entrada recibirá un el siguiente, correlativo número de identificación.

La definición en esta primera versión de la tabla libros es así: / al final esto lo hice bastante diferente:

   mysql> CREATE TABLE librosX (
       -> titulo VARCHAR(200) NOT NULL,
       -> autor1_apellido VARCHAR(60) NOT NULL,
       -> autor1_nombre VARCHAR(24) NOT NULL,
       -> autor2_apellido VARCHAR(60) NULL,
       -> autor2_nombre VARCHAR(24) NULL,
       -> mas_autores ENUM('Y') NULL,
       -> traductores VARCHAR(50) NULL,
       -> fecha_publicación YEAR NULL,
       -> fecha_publicacion_original YEAR NULL,
       -> editorial VARCHAR(100) NOT NULL,
       -> ciudad VARCHAR(30) NULL,
       -> notas VARCHAR(200) NULL,
       -> fecha_entrada TIMESTAMP,
       -> libro_id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY);

La variable notas, quizás convendría definirla con un tipo de dato que se llama TEXT tengo que verlo.

Una vez creada la tabla, el siguiente comando nos permite comprobar que se ha hecho correctamente:

   mysql> DESCRIBE libros;

Ya iré viendo si esto es un buen diseño o si convendrá mejor descomponer algunas de las columnas que podrían ser complejas en otras tablas (los autores, los traductores?); así como alguna que no está como podrían ser posibles préstamos y seguimiento; o lecturas y notas...

INSERT, insertar entradas en tablas

   mysql> INSERT INTO nombre_tabla VALUES ('Variable1', número, NULL);

Las variables deben ir en el orden que están en la tabla correspondiente. Ayuda tenerla delante con mysql DESCRIBE nombre_tabla;
Si las variabeles son strings, deben ir entre comillas 'Variable'
Si son números, INT, FLOAT, etc, sin comillas.
El valor NULL es para que la variable que es una Primary Key adjudique el número correlativo.

Se pueden hacer múltiples entradas a la vez, tal que así:

   mysql> INSERT INTO nombre_tabla VALUES
       -> ('Variable1', número, NULL),
       -> ('Variable1', número, NULL),  
       -> ('Variable1', número, NULL);

Modificar variables y datos en tablas ya creadas

ALTER ... ADD, añadir un campo o variable a una tabla

   mysql> ALTER nombre_tabla ADD fecha_pub_original YEAR NOT NULL;

Donde a partir de ADD se define el nuevo campo: nombre, tipo y otras características. Otras variante:

   mysql> ALTER TABLE nombre_tabla
       -> ADD COLUMN nuevo_campo ENUM('T','F') NOT NULL DEFAULT 'F'
       -> AFTER variable_tal;

Lo veo muy parecido al anterior: la columna más bien sería fila... :-) _ aquí se vería una opición booleana T/F _ finalmente el comando AFTER permite elegir la posición en el que se introduce el nuevo campo.

ALTER ... CHANGE ... MODIFY, cambiar nombre y propiedades de un campo existente en una tabla

   mysql> ALTER nombre_tabla CHANGE nombre_antiguo nombre_nuevo
       -> MEDIUMINT UNSIGNED NOT NULL;

Tras CHANGE va el nombre antiguo del campo que vamos a modificar el nombre nuevo que la vamos a dar, y las propiedades que definen el nuevo campo;

UNSIGNED por cierto quiere decir que sólo tomará valores positivos; aquí sería un número entero mediano que sólo tomará valores positivos y que no puede ser nulo; hay que introducir un valor necesariamente.

Cambiar las propiedades de un campo:

   mysql> ALTER TABLE nombre_tabla
       -> MODIFY COLUMN nombre_campo ENUM('T', 'F') NOT NULL DEFAULT 'F';

En la que la segunda línea dice el nombre del campo o variable a modificar y sus nuevas propiedades.

UPDATE, cambiar valor en una entrada

   mysql> UPDATE nombre_tabla SET variablex=5
       -> WHERE student_id=y;

SET variablex=5 éste sería el nuevo valor de la variable en cuestión; en el ejemplo del tutorial la identificación (WHERE) por student_id no es unívoca y se usa la partícula AND para definirla con una segunda valor/variable.

RENAME, cambiar nombre de una (o varias) tabla

   mysql> RENAME TABLE 
       -> nombre_antiguo TO nombre_nuevo;

DELETE, DROP data

   mysql> DELETE FROM nombre_tabla 
       -> WHERE id = x;

Borraría los datos del id=x una entrada completa de la tabla (fila? completa)

   mysql> ALTER TABLE nombre_tabla 
       -> DROP COLUMN nombre_campo;

Borra un campo completo de la tabla

SELECT ... FROM, ver datos / generar otras tablas

SELECT FROM

Ver sólo algunos campos de una cierta tabla

   mysql> SELECT campo1, campo2 FROM nombre_lista;

SELECT FROM WHERE

Con condicionante WHERE (vídeo-tutorial min 22 y siguientes: https://youtu.be/yPu6qV5byu4?t=1319

   mysql> SELECT campo1, campo2, campo3 FROM nombre_lista
       -> WHERE YEAR(campo3) <= 1999;

Se podrán usar otras operaciones como = > < >= <= !=

WHERE ... AND ... OR

Y también se pueden usar en estas queries, es es el nombre, búsquedas los operadores booleanos OR AND NOT (que tienen como equivalentes respectivos: || && ! ). Un ejemplo del uso de operador booleano:

   mysql> SELECT campo1, campo2, campo3 FROM nombre_lista
       -> WHERE campo1 = 2 AND campo2 = "CA";

Los valores 2 y 'CA' son figurados; los datos tipo string creo que pueden ir con comillas simples o dobles " ".

Los operadores booleanos pueden ir anidados; se ve algún ejemplo en el tutorial.

Para ver si algún dato falta hay debe usarse IS NULL o IS NOT NULL - algo especial.

ORDER BY

Ordenar datos de una tabla

   mysql> SELECT first_name, last_name
       -> FROM nombre_lista
       -> ORDER BY last_name;

El anterior ordena ascendente-alfabéticamente.

Para ordenar según dos parámetros y dos criterios ascedente y descendente

   mysql> SELECT first_name, last_name, state
       -> FROM nombre_lista
       -> ORDER BY state DESC, last_name ASC;

Otras funciones relativas a SELECT y constraints:

LIMIT, LIKE

Limitar el número de resultados FROM tabla LIMIT 5; o FROM tabla LIMIT 5, 10;

WHERE variable LIKE ... con el comodín % - por ejemplo: 'D%'que describiría CHARS que empiezan por D

LIKE con underscores, guiones bajos WHERE first_name LIKE '___y' representaría nombres con tres letras (tres underscores) acabados en y.

Otra comando de esta familia sería DISTINCT , que evitaría duplicaciones en los resultados...

SELECT ... FROM ... WHERE ... BETWEEN para definir ámbito del query

SELECT ... FROM ... WHERE ... IN para definir ámbito del query

CONCAT, nombres y otros en tablas

Vídeo-tutorial min 26

   mysql> SELECT CONCAT(first_name, " ", last_name) AS 'Name',
       -> CONCAT(city, " ", state) AS 'Hometown'
       -> FROM nombre_tabla;

Saldrá una tabla con las dos primeras variables encadenadas tipo Pepe Pérez y el título de la columna en la tabla como Name. El ""corresponde al espacio entre Pepe y Pérez en el ejemplo.

COUNT

Vídeo-tutorial min: 28:07 https://youtu.be/yPu6qV5byu4?t=1687

Puede combinarse con DISTINCT, GROUP, HAVING... Ejemplos:

   mysql> SELECT COUNT(*)
       -> FROM students;

Contaría el número de estudiantes (filas?) en la lista students

   mysql> SELECT COUNT(DISTINCT sate)
       -> FROM students;

Contaría el número de estados de procedencia distintos de la lista students

   mysql> SELECT COUNT(*)
       -> FROM students;
       -> WHERE sex = 'Ḿ';

Contaría el número de estudiantes en la lista students, y entre estos, aquellos cuyo campo/variable sex tenga valor 'M'...

++ Combinaciones con GROUP, ORDER BY... y también AS a la hora de visualizar las tablas

Comando HAVING permite añadir condiciones adicionales, tipo HAVING Amount > 1 __ ver ejemplo

MATH OPERATIONS

Intro vídeo-tutorial min xx _ seguro que interesante para otros proyectos... Básicamente equivalente a una hoja de cálculo compleja... :-)

JOIN, juntar datos de diferentes tablas

Vídeo-tutorial min 36: https://youtu.be/yPu6qV5byu4?t=2163

Aquí aparece el interés del desglose de las tablas y el tema interesante de las foreign keys que es la clave de cómo conectar unas tablas y otras...

Ejemplo 1, nueva tabla con variables de dos tablas diferentes, que se relacionan mediante la(s) foreign key(s):

   mysql> SELECT students_id, date, score, maxscore
       -> FROM tabla1, tabla2
       -> WHERE DATE='2014-08-25'
       -> AND tabla1.tests_id = tabla2.tests_id;

En la que en SELECT están los valores de los campos que queremos mostrar;
FROM las dos tablas (en este caso) en las que se encuentran estos campos; y
WHERE la condición - sin tener en cuenta la primera parte - en la que se expresa la relación entre las tablas; diría que una primary key con una foreign key (confirmo en breve).

Parece que resulta más claro si se pone en SELECT la tabla de la que proceden los valores/campos que queremos mostrar, tal que así, por ejemplo:

   mysql> SELECT tabla1.students_id, tabla2.date, tabla1.score, tabla1.maxscore
       -> FROM tabla1, tabla2
       -> WHERE DATE='2014-08-25'
       -> AND tabla1.tests_id = tabla2.tests_id;

Ejemplo con datos de tres tablas y alguna cosa más

   mysql> SELECT CONCAT(tabla1.first_name, " ", tabla1.last_name) AS 'Name', tabla2.score, tabla3.maxscore
       -> FROM tabla1, tabla2, tabla3
       -> WHERE tabla1.tests_id = tabla2.tests_id;
       -> AND tabla1.students_id = tabla3.students_id;

A partir de aquí se pueden hacer combinaciones varias con CONCAT, COUNT, GROUP BY ... ORDER BY ...
También FROM ... LEFT JOIN ... ON, que de momento no he visto bien
Y también FROM ... INNER JOIN ... ON que sería una intersección pura... entre dos tablas...

Hasta aquí de momento. Además:

Una explicación visual de los JOIN en MySQL - con diagramas de VENN. La idea suena interesante, pero no se si funciona demasiado bien: https://blog.codinghorror.com/a-visual-explanation-of-sql-joins/

Modelo para catálogo de librería con LEFT JOIN

Un tío que tenía el mismo problema que yo - como llamar a dos autores para una misma salida... y bueno parece que se puede hacer con una tabla intermedia... Gente maja como siempre resolviendo dudas a los que están intentando aprender. Thx!!

https://stackoverflow.com/questions/9554560/mysql-inner-join-from-two-or-more-variables

Habría que ver las tablas cómo las construí, pero el query tiene bastante truquillo... :-/

Hay tres tablas: books(book_title, book_id), authors(name, author_id) y book_authors(author_id, book_id) que relaciona las dos primeras. Un código que funciona es así:

   mysql> SELECT books.book_id AS ID, books.book_title AS Título, GROUP_CONCAT(authors.name SEPARATOR ', ') AS Autores 
       -> FROM books
       -> LEFT JOIN book_authors ON book_authors.book_id = books.book_id
       -> LEFT JOIN authors ON authors.author_id = book_authors.author_id
       -> GROUP BY books.book_id;

Si no se ponen los dos Group, no salen todos los datos en la visualización / output, sino que cuando por ejemplo hay dos autores, sólo sale el primero, o salen repetidos... debería tener una forma más sencilla de apuntar un campos sobre otro, diría...

Estas son las tres tablas básicas:

   mysql> CREATE TABLE books (
       -> book_title VARCHAR(255) NOT NULL,
       -> book_id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY);

   mysql> CREATE TABLE authors (
       -> name VARCHAR(255) NOT NULL,
       -> author_id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY);

   mysql> CREATE TABLE book_authors (
       -> book_id INT UNSIGNED NOT NULL,
       -> author_id INT UNSIGNED NOT NULL,
       -> PRIMARY KEY (book_id, author_id));

Mi explicación visual del script: