Para el examen

-- 3.- Realizar la inserción de, al menos, dos registros por cada una
-- de las tablas a través de la línea de comandos.

-- Incluir algunos registros en los que aparezca una agencia de viajes llamada 'miauto'.


-- Tabla Cliente
INSERT INTO Cliente VALUES ("","48867822X","Manolito Garcia","657228779","Avenida Jose de Mora n8");
INSERT INTO Cliente VALUES ("","58726687V","Antonio Recio Matamoros","677256661","Mirador de Montepinar");

-- Tabla Avala

INSERT INTO Avala VALUES (1,2);
INSERT INTO Avala VALUES (2,1);

-- Tabla Reserva

INSERT INTO Reserva VALUES ("",1,600,True,"miauto");
INSERT INTO Reserva VALUES ("",2,5000,False,"Batmovil");

-- Tabla Fecha_Reserva

INSERT INTO Fecha_Reserva VALUES (1,"2012-05-30","2012-7-3");
INSERT INTO Fecha_Reserva VALUES (2,"2013-08-05",NULL);

-- Tabla Coche

INSERT INTO Coche VALUES ("9871JXD","Honda","Civic","Negro",11);
INSERT INTO Coche VALUES ("7865LOL","Opel","Astra GTC","Rojo",3);

-- Tabla Incluye

INSERT INTO Incluye VALUES (1,"9871JXD",450.50,12);
INSERT INTO Incluye VALUES (2,"7865LOL",320,22);


-- Todo esto lo hacemos identificados como root
--       mysql -u root -p




---4.- Crearse, al menos, dos ficheros de datos para inserción masiva de los mismos en sus correspondientes tablas. Realizar la inserción.

--tabla1
    68912564H    Chuck Norris    670885972    Calle Murillo n5
    71335837T    Amador Rivas    631670091    Calle Corredera n6 2D
    55687803A    Armando Jaleos    669801030    Avenida Jose de Mora n17

--tabla2
    6812WAU    Seat    Leon    \N    1
    5103HAH    Mazda    RX-8    Rojo    3
    9721MNZ    Peugeot    308    Blanco    9

    LOAD DATA LOCAL INFILE '/home/usuario/Escritorio/BD/Ejercicio1/4fichero1'     INTO TABLE Cliente;
    LOAD DATA LOCAL INFILE '/home/usuario/Escritorio/BD/Ejercicio1/4fichero2'     INTO TABLE Coche;

    -- Esto lo ejecutamos una vez identificados como root de la siguiente         -- forma
        mysql -u root -p --local-infile


-- 5.- El cliente de nuestra base datos nos ha llamado y nos dice que necesita
-- saber de los clientes, además, la provincia en que residen que, por defecto,
-- será Granada.

-- Modificar la base de datos para que se contemple esta situación.


ALTER TABLE Cliente ADD (
    Provincia varchar(20) DEFAULT "Granada"
    );

-- Todo esto lo hacemos identificados como root
--       mysql -u root -p



-- 6.- Modificar los precios de los coches de manera que se incremente en un 7%.

UPDATE Incluye SET Precio_Coche=Precio_Coche*1.07;

-- Todo esto lo hacemos identificados como root
--       mysql -u root -p




-- 7.- La agencia 'miauto' ha cambiado de nombre y ahora se llama 'nuestroauto'.
-- Se desea actualizar esta situación en la base de datos.

UPDATE Reserva SET Agencia="nuestroauto" WHERE Agencia="miauto";



-- 8 Crearse un usuario llamado 'cargador_datos' con contraseña de acceso 'mipass'. Este usuario
-- solamente podrá realizar carga de datos masiva desde ficheros.

CREATE USER cargador_datos@'localhost' IDENTIFIED BY 'mipass';
GRANT FILE ON *.* TO cargador_datos@'localhost' IDENTIFIED BY 'mipass';

-- Se aplica el permiso FILE a todas las bases de datos ya que
-- "supùestamente" es un permiso global y da error al aplicarlo
-- a una base de datos especifica.

-- El error 1142 a la hora de ejecutar la inserción desde fichero
-- en el ejercicio 10, se solventa aplicando también permisos de
-- insert.

GRANT INSERT ON *.* TO cargador_datos@'localhost' IDENTIFIED BY 'mipass';


-- Fuerza la aplicación de privilegios en el momento.

FLUSH PRIVILEGES;



--9 Crearse un nuevo fichero de datos para carga masiva para una de las tablas de la base de datos.

69872211K    Ricardin    958112342    Calle Marquez Torres n4
    71395872I    Fulanito    618411267    Calle Cabeza n2

-- Todo esto lo hacemos identificados como root
--       mysql -u root -p




-- 10.- Accediendo con ese usuario, realizar la carga de datos masiva, del fichero
-- creado anteriormente, en nuestra base de datos.

LOAD DATA LOCAL INFILE '/home/usuario/Escritorio/BD/Ejercicio1/9fichero3' INTO TABLE Ejercicio1.Cliente;

-- Identificandonos con cargador_datos:
--       mysql -u cargador_datos -p --local-infile



-- 11.- Comprobar que este usuario no puede realizar una consulta a la base de datos.

SELECT * FROM Ejercicio1.Cliente;

-- Estando identificados como "cargador_datos"

-- Tras ejecutar la consulta la consola devolverá el siguiente error:
-- ERROR 1142 (42000): SELECT command denied to user 'cargador_datos'@'localhost' for table 'Cliente'



-- 12.- Crear un usuario 'mi_otro_yo' con contraseña 'miotropass' que tenga todos los permisos para la base de datos.

CREATE USER mi_otro_yo@'localhost' IDENTIFIED BY 'miotropass';
GRANT ALL PRIVILEGES ON *.* TO mi_otro_yo@'localhost' IDENTIFIED BY 'miotropass';
GRANT GRANT OPTION ON *.* TO mi_otro_yo@'localhost' IDENTIFIED BY 'miotropass';

-- Forzamos la aplicación de privilegios en el momento.

FLUSH PRIVILEGES;

-- Todo esto lo hacemos identificados como root
--       mysql -u root -p



--13 Con este usuario, eliminar el usuario 'cargador_datos' y crearse uno nuevo llamado 'consultor' sin contraseña y que únicamente tenga permisos de realización de consultas sobre la tabla de clientes.


-- meto en consola: mysql -u mi_otro_yo -p --local-infile
    use mysql
    -- para borrar usuarios primero hay que quitarle sus permisos
        REVOKE all privileges ON *.* FROM cargador_datos@localhost;
            -- da error:ERROR 1045 (28000): Access denied for user                 -- 'mi_otro_yo'@'localhost' (using password: YES)

CREATE USER consultor@'localhost';

GRANT select ON *.* TO consultor@'localhost';
    -- da error:mysql> GRANT select ON *.* TO consultor@'localhost';
    -- ERROR 1045 (28000): Access denied for user 'mi_otro_yo'@'localhost' (using password: YES)

    -- Para solucionarlo hay que dar permiso de GRANTS a mi_otro_yo y deja introducirlo.
        -- meto en consola: mysql -u root -p --local-infile
            use mysql
            GRANT grant option ON *.* TO mi_otro_yo@'localhost';

Sentencias básicas MySQL

-->

Conectarse a mysql:

mysql [-h IP] -u usuario -p [nombrebasedatos] [--local-infile]

Mostrar variables del sistema-- show variables;

Utilizar una base de datos----- use nbasedatos;

Mostar todas las bases de datos---- show databases;

Crear una base de datos----- create database nombre;

Borrar una base de datos---- drop database nombre;

Mostrar tablas de una base de datos--- show tables;

Crear tablas----- create table nombre(

campo1 tipodato(X) restricciones,

campo2 tipodato restricciones

);

Definicion de claves foraneas ----------- foreign key (campo) references tablaref(campo)

on delete cascade --- cuando borras borra los registros de la tabla padre de la tabla hija

on delete set null --- Cuando borres o actualices el registro de una tabla padre:

• establece NULL la o las columnas de clave foranea en la tabla hija.
• Solo valido si las columnas de clave foranea no se han definido como not null.

On delete no action – Su borra o actualiza un valor de clave primaria no sera permitido si en la tabla referenciada hay un valor de clave foranea relacionado.

Modificar tablas -- alter table add/modify/drop resto de sentencias;

LTER TABLE PRUEBAS ADD(

OMBRE VARCHAR (5)

lter table PRUEBA modify

ODEC varchar (2) PRIMARY KEY AUTO_INCREMENT;

Renombrar base de datos -------- rename table prueba to prueba2;

Copiar tablas (solo estructura)-- create table nuevatabla like tablavieja;

Copiar tablas (con registros) – create table nombretabla select * from tablavieja;

Cambiar registro de una tabla-- update tabla set id=55 where id=1;

Añadir registros-- insert into ntabla values('valor', 1, NULL)

insert into ntabla(campo1,campo2) values ('valor1','valor2');

Insertar registro desde fichero de texto plano--

load data local infile 'ruta fichero' into table ntabla;

los valores se separan por tabulaciones y los registros null por /N

Borrar tabla--- drop table nombre;

Borrar registros de una tabla---- delete from table;

Mostrar registros de una tabla---- select * from table;

Ver usuarios creados--------- select * from user.mysql;

Crear usuarios locales-------- create user 'nombre'@'localhost' IDENTIFIED BY 'pass';

Crear usuarios globales------ create user 'nombre'@'%' IDENTIFIED BY 'pass';

Borrar usuarios--------------- drop user 'nombre'@'localhost';

para borrarlos se necesita quitarle todos los permisos antes ( revoke all on *.* to usuario; )

Ver privilegios--------------- show grants for usuario;

Dar privilegios-------------- grant privilegio on objeto from usuario identified by 'pass';

con esta sentecia si no existe el usuario te lo crea por ello la parte de la contraseña.

Para dar permisos de asignacion de permisos le añadimos with grant option;

grant select matricula on Ejercicio1.coches to 'usuario'@'localhost';

INDEX--- Indices

Los compos llaves son indices por defecto.

Los campos que hacen referencia a los campos calve no son indices pero se pueden definir como tales.

INDEX [identificador] (campo1,...)

Para comprobar si el index se ha definido en la tabla: show create table tabla;

y entonces en la descripción al lado del campo index tiene que salir “key”.

MODIFICAR TAMAÑO DE CAMPO

si un campo de lonngitud (5) tiene un registro de 5 caracteres o digitos, al cambiar de longitud (3) este sera acortado a tamaño 3.

DEFAULT

no modifica datos existentes

si se crea un campo nuevo con default se aplica a todo.

Principales tipos de datos

Números enteros pequeños---------- tinyint

Números con coma ------------------- float(ent,dec)

Cadena de caracteres----------------- Varchar

Binarios (V – F) ---------------------- Bool

Fecha ----------------------------------Date

unsigned --- no permite signo

autoincrement – autoincrementable (codigos)

PRIMARY KEY

si añades un campo como primary key se anula el anterior

ENUM (tipo de dato)

Solo acepta los valores que se especifican

nombrecampo ENUM('opcion1','opcion2',..)

alter table departamento modify

sede varchar (21) default 'Mates';

Ejemplo de sentencias sobre usuarios

-- 8 crar usuario
USE mysql

CREATE USER cargador_datos@'localhost' IDENTIFIED BY 'mipass';


-- 9
-- dar privilegios de carga masiva

GRANT file on *.* to cargador_datos@'localhost';


-- 10
-- meto en consola: mysql -u cargador_datos -p --local-infile
LOAD DATA LOCAL INFILE '/home/usuario/Escritorio/Apuntes/Base de Datos/Ejercicio1/insert-    cargador.txt' INTO TABLE Ejercicio1.coches;

    -- no me deja cargar datos, para que funcione: añadir permiso INSERT
       
    GRANT insert ON *.* TO cargador_datos@'localhost';

-- 11
 SELECT * FROM Ejercicio1.coches;
    -- ERROR 1142 (42000): SELECT command denied to user 'cargador_datos'@'localhost' for table         -- 'coches'

-- 12
-- meto en consola:mysql -u root -p --local-infile
    use mysql
    CREATE USER mi_otro_yo@'localhost' IDENTIFIED BY 'miotropass';

    GRANT all ON *.* TO mi_otro_yo@'localhost';

-- 13
-- meto en consola: mysql -u mi_otro_yo -p --local-infile
    use mysql
    -- para borrar usuarios primero hay que quitarle sus permisos
        REVOKE all privileges ON *.* FROM cargador_datos@localhost;
            -- da error:ERROR 1045 (28000): Access denied for user                 -- 'mi_otro_yo'@'localhost' (using password: YES)

--14

CREATE USER consultor@'localhost';

GRANT select ON *.* TO consultor@'localhost';
    -- da error:mysql> GRANT select ON *.* TO consultor@'localhost';
    -- ERROR 1045 (28000): Access denied for user 'mi_otro_yo'@'localhost' (using password: YES)

    -- Para solucionarlo hay que dar permiso de GRANTS a mi_otro_yo y deja introducirlo.
        -- meto en consola: mysql -u root -p --local-infile
            use mysql
            GRANT grant option ON *.* TO mi_otro_yo@'localhost';
       

Creacion de una BD

CREATE TABLE cliente (
    codigo INT(4) UNSIGNED AUTO_INCREMENT,
    DNI VARCHAR(9) UNIQUE,
    nombre VARCHAR(30),
    telefono VARCHAR(9),
    direccion VARCHAR(40),
    PRIMARY KEY(codigo)
);

CREATE TABLE avala (
    codigo_avalador INT(4) UNSIGNED,
    codigo_avalado INT(4) UNSIGNED,
    PRIMARY KEY (codigo_avalador,codigo_avalado),
    FOREIGN KEY (codigo_avalador) REFERENCES cliente(codigo),
    FOREIGN KEY (codigo_avalado) REFERENCES cliente(codigo)
);

CREATE TABLE reservas (
    codigo INT(5) AUTO_INCREMENT,
    codigo_cliente INT(4) UNSIGNED NOT NULL,
    entregado BOOL,
    precio_total DEC(3,2),
    agencia VARCHAR(10),
    PRIMARY KEY(codigo),
    INDEX(codigo_cliente),
    FOREIGN KEY (codigo_cliente) REFERENCES cliente(codigo)
);

CREATE TABLE ficha_reserva (
    codigo_reserva INT(5) PRIMARY KEY,
    inicio DATE,
    fin DATE,
    FOREIGN KEY (codigo_reserva) REFERENCES reservas(codigo)
);

CREATE TABLE coches (
    matricula VARCHAR(7) PRIMARY KEY,
    color VARCHAR(10),
    marca VARCHAR(15),
    modelo VARCHAR(10),
    garaje INT(3)
);

CREATE TABLE incluye (
    codigo_reserva INT(5),
    matricula VARCHAR(7),
    litros_gasolina TINYINT UNSIGNED,
    precio_coche DEC(8,2),
    PRIMARY KEY (codigo_reserva, matricula),
    FOREIGN KEY (codigo_reserva) REFERENCES reservas(codigo),
    FOREIGN KEY (matricula) REFERENCES coches(matricula)
);