Estudio/Laboratorio - Aprendiendo SQL (con RDBMS MySQL) - Parte 1

Instalando MySQL y creando mi primera base de datos

 
Instalando mySQL en ubuntu 12.04:

sudo apt-get install mysql-server

Durante la instalación te pedirá que introduzcas un password para la cuenta raíz (root). Iniciamos el terminal de mysql con usuario root (-uroot) con su correspondiente password (-p****):

mysql -uroot -p*****

Creo una base de datos para practicar llamada practiceDB y le concedo permisos completos de accesos a mi usuario habitual en ubuntu (acceso sin password):

mysql> CREATE DATABASE practiceDB;
mysql> GRANT ALL ON practiceDB.* TO astwin@localhost IDENTIFIED BY '';

Ahora ya puedo acceder a la base de datos practiceDB de una forma sencilla:

mysql practiceDB

Creación de tablas (CREATE)

Crear tabla:

mysql> CREATE TABLE comerciales(num_empleado INT, apellido VARCHAR(40),nombre VARCHAR(30), comision TINYINT);

Ver tablas:

mysql> SHOW TABLES;

Análisis de estructura de tabla:

mysql> DESCRIBE comerciales;  

Inserción de registros en tablas (INSERT)

 
Insertar registro a registro:

mysql> INSERT INTO comerciales(num_empleado,apellido,nombre,comision) VALUES (1,'Rive','Sol',10);
mysql> INSERT INTO comerciales(num_empleado,apellido,nombre,comision) VALUES (2,'Gordimer','Charlene',15);
mysql> INSERT INTO comerciales(num_empleado,apellido,nombre,comision) VALUES (3,'Serote','Mike',10);
mysql> select * from comerciales;
mysql> TRUNCATE comerciales;

Insertar de forma más sencilla siguiendo el orden de definición de campos:

mysql> INSERT INTO comerciales VALUES (1,'Rive','Sol',10);
mysql> INSERT INTO comerciales VALUES (2,'Gordimer','Charlene',15);
mysql> INSERT INTO comerciales VALUES (3,'Serote','Mike',10);
mysql> select * from comerciales;
mysql> TRUNCATE comerciales;

Insertar múltiples registros:

mysql> INSERT INTO comerciales(num_empleado,apellido,nombre,comision) VALUES (1,'Rive','Sol',10),(2,'Gordimer','Charlene',15),(3,'Serote','Mike',10); mysql> TRUNCATE comerciales;

Insertar registros desde archivos de texto:

Para habilitar a mySQL a leer archivos locales se necesita tener activada la propiedad  --local-infile=1 (por defecto a 0). En este caso abrimos una sesión con esta propiedad:  mysql practiceDB --local-infile

mysql> LOAD DATA LOCAL INFILE 'tabla_comerciales.txt' INTO TABLE comerciales FIELDS TERMINATED BY ',';

Archivo 'tabla_comerciales.txt'

1,Rive,Sol,10
2,Gordimer,Charlene,15
3,Serote,Mike,10

Recuperación de información en una tabla (SELECT)

 
Comando SELECT, WHERE  y clausulas condicionales:

mysql> SELECT * FROM comerciales;
mysql> SELECT nombre FROM comerciales;
mysql> SELECT nombre,apellido FROM comerciales;
mysql> SELECT num_empleado,nombre,apellido FROM comerciales WHERE apellido='Gordimer';

mysql> SELECT * FROM comerciales WHERE comision > 11 OR apellido='Rive' AND nombre='Sol' ;
mysql> SELECT * FROM comerciales WHERE (comision > 11) OR (apellido='Rive' AND nombre='Sol');
mysql> SELECT * FROM comerciales WHERE (apellido='Rive') OR (apellido='Rive' AND comision>11);

Correspondencia de patrones (instrucción LIKE):

mysql> SELECT * FROM comerciales WHERE apellido LIKE 'Sero%';
mysql> SELECT * FROM comerciales WHERE apellido LIKE '%e%';
mysql> SELECT * FROM comerciales WHERE apellido LIKE 'e%';
mysql> SELECT * FROM comerciales WHERE apellido LIKE '%e%e'; 

Ordenación de los resultados (clausula ORDER BY):

mysql> INSERT INTO comerciales VALUES (4,'Rive','Mongane',10),(5,'Smith','Mike',12);              
mysql> SELECT * FROM comerciales ORDER BY apellido;
mysql> SELECT * FROM comerciales ORDER BY apellido,nombre;
mysql> SELECT * FROM comerciales ORDER BY comision DESC;
mysql> SELECT * FROM comerciales ORDER BY comision DESC, apellido ASC, nombre ASC; 

Limitación del número de resultados (clausula LIMIT):

mysql> SELECT nombre , apellido , comision FROM comerciales ORDER BY comision DESC LIMIT 1;
mysql> SELECT nombre , apellido , comision FROM comerciales ORDER BY comision DESC LIMIT 0,1;
mysql> SELECT nombre , apellido , comision FROM comerciales ORDER BY comision DESC LIMIT 1,1; 

Utilización de funciones para ajustar las consultas ( SUM(),AVG(),MIN(),MAX() ):

mysql> SELECT MAX(comision) FROM comerciales;
mysql> SELECT AVG(comision) FROM comerciales;
mysql> SELECT MIN(comision) FROM comerciales;
mysql> SELECT SUM(comision) FROM comerciales; 

Recuperación de los resultados con registros únicos (clausula DISTINCT):

mysql> SELECT DISTINCT apellido FROM comerciales ORDER BY apellido;

Contar el número de resultados obtenidos (clausula COUNT):

mysql> SELECT COUNT(*) FROM comerciales;
mysql> SELECT COUNT(*) FROM comerciales WHERE comision > 10;
mysql> SELECT COUNT(*) FROM comerciales WHERE comision >= 10;
mysql> SELECT COUNT(DISTINCT apellido) FROM comerciales;
mysql> SELECT COUNT(DISTINCT apellido) FROM comerciales WHERE comision=10;

Cálculos en consultas:

mysql> SELECT nombre, apellido, comision + 5 FROM comerciales;

Eliminación de registros (DELETE)

mysql> DELETE FROM comerciales WHERE num_empleado = 5;

Cambiar registros de una tabla (UPDATE)

mysql> UPDATE comerciales SET comision = 12 WHERE num_empleado = 1 ;
mysql> UPDATE comerciales SET comision = 12 WHERE comision = 10 ;
mysql> UPDATE comerciales SET comision = 10 WHERE comision = 12 ;

Eliminación de tablas y bases de datos (DROP)

mysql> CREATE TABLE comision (id INT);
mysql> SHOW TABLES;
mysql> DROP TABLE comision ;
mysql> SHOW TABLES;

Desde la cuenta de root:      mysql -uroot -p****      

mysql> CREATE DATABASE aux;
mysql> SHOW DATABASES;
mysql> DROP DATABASE aux;
mysql> SHOW DATABASES;

Modificar estructura de la tabla (ALTER TABLE)

 
Agregar columna (ADD):

mysql> ALTER TABLE comerciales ADD fecha_incor DATE;
mysql> DESCRIBE comerciales;
mysql> ALTER TABLE comerciales ADD año_nacimiento YEAR;
mysql> DESCRIBE comerciales;

Modificar definición de una columna (CHANGE o MODIFY):

mysql> ALTER TABLE comerciales CHANGE año_nacimiento cumple DATE;
mysql> ALTER TABLE comerciales CHANGE cumple cumpleaños DATE;
mysql> ALTER TABLE comerciales MODIFY cumpleaños YEAR;
mysql> ALTER TABLE comerciales MODIFY cumpleaños DATE; 

Cambiar nombre de tabla (RENAME):

mysql> ALTER TABLE comerciales RENAME agentes_ventas;
mysql> DESCRIBE agentes_ventas;
mysql> ALTER TABLE agentes_ventas RENAME comerciales;

Eliminar columna (DROP):

mysql> ALTER TABLE comerciales ADD aux INT;
mysql> DESCRIBE comerciales;
mysql> ALTER TABLE comerciales DROP aux;
mysql> DESCRIBE comerciales;

Funciones de fecha

 
Formato de la fecha ( DATE_FORMAT() ):

mysql> SELECT DATE_FORMAT(fecha_incor,'%d/%m/%Y') FROM comerciales WHERE num_empleado = 1; 

Fecha y hora actual ( CURRENT_DATA()  NOW() ):

mysql> SELECT NOW(), CURRENT_DATE();

Funciones de fecha YEAR(), MONTH(), DAYOFMONTH():

mysql> SELECT YEAR(cumpleaños),MONTH(cumpleaños), DAYOFMONTH(cumpleaños) FROM comerciales;

Operaciones con fechas:

Intentamos obtener la edad restando el año de la fecha actual a el año del nacimiento, pero así no se tiene en cuenta la fecha de verdad (puedes estar en abril y hasta septiembre no tener la edad). Hay que tener también en cuenta el dia y mes:

mysql> SELECT YEAR(NOW())-YEAR(cumpleaños) FROM comerciales;
mysql> SELECT RIGHT(CURRENT_DATE(),5) < RIGHT(cumpleaños,5) FROM comerciales;
mysql> SELECT nombre, apellido, YEAR(NOW())-YEAR(cumpleaños)- ( RIGHT(CURRENT_DATE(),5) < RIGHT(cumpleaños,5) ) AS edad FROM comerciales;

Consultas más avanzadas

Renombrar campo al realizar una consulta (operador AS):

mysql> SELECT apellido ,nombre, MONTH(cumpleaños) AS mes, DAYOFMONTH(cumpleaños) AS dia FROM comerciales ORDER BY mes;

Combinación de columnas (CONCAT):

mysql> SELECT CONCAT(nombre,' ',apellido) as nombre_completo,  MONTH(cumpleaños) AS mes, DAYOFMONTH(cumpleaños) AS dia FROM comerciales ORDER BY mes; 

Creamos dos tablas más: 

Tabla_ventas.txt 
1,1,1,2000
2,4,3,250
3,2,3,500
4,1,4,450
5,3,1,3800
6,1,2,500

Tabla_clientes.txt
1,Yvonne,Clegg
2,Johnny,Chaka
3,Winston,Powers
4,Patricia,Mankunku

mysql> CREATE TABLE clientes(id INT,nombre VARCHAR(30), apellido VARCHAR(40));
mysql> CREATE TABLE ventas(codigo INT,comercial INT, cliente INT, valor INT);
mysql> LOAD DATA LOCAL INFILE 'tabla_clientes.txt' INTO TABLE clientes FIELDS TERMINATED BY ',';
mysql> LOAD DATA LOCAL INFILE 'tabla_ventas.txt' INTO TABLE ventas FIELDS TERMINATED BY ',';

Combinación de varias tablas (se unen por las instancias comunes de cada una):

mysql> SELECT comercial,cliente,valor,nombre,apellido FROM ventas,comerciales WHERE codigo=1 and comerciales.num_empleado=ventas.comercial;
mysql> SELECT codigo,cliente,valor FROM comerciales, ventas WHERE nombre='Sol' AND apellido ='Rive' AND ventas.comercial=comerciales.num_empleado;
mysql> SELECT codigo,cliente,valor FROM comerciales, ventas WHERE nombre='Sol' AND apellido ='Rive' AND comercial=num_empleado ;

Agrupación de una consulta  (GROUP BY)

mysql> SELECT comercial,SUM(valor) FROM ventas GROUP BY comercial;
mysql> SELECT comercial,SUM(valor) AS total_ventas, COUNT(*) as num_ventas FROM ventas GROUP BY comercial ORDER BY total_ventas DESC;
mysql> SELECT nombre,apellido,comercial,COUNT(*) as num_ventas FROM ventas,comerciales WHERE ventas.comercial=comerciales.num_empleado GROUP BY comercial;