BBDD MySQL

Index

1 – Creació i eliminació d’usuaris
1.1 – Instruccions per a comprovacions
2 – Privilegis i rols
3 – Programació en base de dades
4 – Procediments i funcions
5 – Cursors Instrucció LOOP
6 – Disparadors (Triggers)

1 – Creació i eliminació d’usuaris

Per crear o eliminar usuaris es farà servir la següent estructura

CREATE USER 'nom_usuari'@'%' IDENTIFIED BY 'password';

 

Accions USER:

CREATE USER 'nom_usuari' => Per crear un usuari

DROP USER 'nom_usuari' => Per eliminar un usuari

RENAME USER 'nom_usuari' => Per ca un usuari

ALTER USER 'nom_usuari' => Per eliminar un usuari

 

Host:

@’localhost’ => Per el usuari pugui entrar en mode local

@’%’ => Per el usuari pugui entrar amb qualsevol IP

@’10.0.2.15’ => Per el usuari pugui entrar amb una IP determinada

 

Password:

IDENTIFIED BY ‘password’ => Será necessari accedir amb el password

 

Exemples:

CREATE USER 'administrador'@'%' IDENTIFIED BY 'administrador';

CREATE USER 'invitat'@'localhost', 'invitat'@'192.168.0.0/16' IDENTIFIED BY 'invitat';

CREATE USER 'modificacio'@'localhost' IDENTIFIED BY 'modificacio';

CREATE USER 'estructura'@'localhost' IDENTIFIED BY 'estructura';

RENAME USER 'invitat'@'localhost' TO 'convidat'@'localhost';

RENAME USER 'invitat'@'192.168.0.0/16 ' TO 'convidat'@'192.168.0.0/16';

 

Instruccions per a comprovacions:

select user,host from mysql.user; => Veure usuari/host disponibles

show databases; => Veure bases de dades disponibles

show grants; => Veure permisos de l’usuari

show grants for usuari@host; => Veure permisos d’un usuari concret:

show tables; => Veure taules disponibles:

show table status; => Veure info de les taules disponibles:

 

Veure qui pot accedir una determinada BD:

SELECT user,host FROM db WHERE db='ciclisme'

union

SELECT user,host FROM tables_priv WHERE db='ciclisme'

union

SELECT user,host FROM columns_priv WHERE db='ciclisme'

union

SELECT user,host FROM procs_priv WHERE db='ciclisme';

(+ usuaris que poden accedir a totes)

2 – Privilegis i rols

Per crear o eliminar usuaris es farà servir la següent estructura:

CREATE USER ‘nom_usuari’@’%’ IDENTIFIED BY ‘password’;

GRANT [permís] ON [nom de bases de dades]. [Nom de taula] TO ‘[nom d’usuari]’ @ ‘localhost’;

REVOKE [permís] ON [nom de base de dades]. [Nom de taula] FROM ‘[nom d’usuari]’ @ ‘localhost’;

 

Permisos per a usuaris:
ALL PRIVILEGES: com hem esmentat prèviament això permet a un usuari de MySQL accedir a totes les bases de dades assignades en el sistema.
CREATE: permet crear noves taules o bases de dades.
DROP: permet eliminar taules o bases de dades.
DELETE: permet eliminar registres de taules.
INSERT: permet inserir registres en taules.
SELECT: permet llegir registres en les taules.
UPDATE: permet actualitzar registres seleccionats en taules.
GRANT OPTION: permet remoure privilegis d’usuaris.
GRANT [permís] ON [nom de bases de dades]. [Nom de taula] TO ‘[nom d’usuari]’ @ ‘localhost’;
REVOKE [permís] ON [nom de base de dades]. [Nom de taula] FROM ‘[nom d’usuari]’ @ ‘localhost’;

Cada vegada que el teu actualitzes o canvies permisos, assegura’t refrescar els privilegis mitjançant:

FLUSH PRIVILEGES;

Exemples:

CREATE USER 'usuari1' @ 'localhost' identified BY 'usuari1';
GRANT SELECT ON examenuf2. * TO 'usuari1' @ 'localhost';
GRANT UPDATE ON examenuf2.provincies TO 'usuari1' @ 'localhost';
FLUSH PRIVILEGES;

GRANT ALL PRIVILEGES ON examenuf2. * TO 'administrador' @ '%';
GRANT SELECT, UPDATE ON examenuf2. * TO 'modificacions' @ 'localhost';
FLUSH PRIVILEGES;

GRANT ALTER ON examenuf2. * TO 'estructura' @ 'localhost';
FLUSH PRIVILEGES;

GRANT INSERT ON *. * TO 'convidat' @ 'localhost';
FLUSH PRIVILEGES;

GRANT UPDATE ON examenuf2.pobles TO 'convidat' @ 'localhost';
FLUSH PRIVILEGES;

REVOKE SELECT ON examenuf2. * FROM 'consulta' @ 'localhost';
GRANT SELECT ON examenuf2.articles TO 'consulta' @ 'localhost';
GRANT SELECT ON examenuf2.clients TO 'consulta' @ 'localhost';
GRANT SELECT ON examenuf2.linees TO 'consulta' @ 'localhost';
GRANT SELECT ON examenuf2.pobles TO 'consulta' @ 'localhost';
GRANT SELECT ON examenuf2.provincies TO 'consulta' @ 'localhost';
GRANT SELECT ON examenuf2.venedors TO 'consulta' @ 'localhost';
FLUSH PRIVILEGES;

REVOKE UPDATE ON examenuf2.factures FROM 'modificacions' @ 'localhost';
FLUSH PRIVILEGES;

REVOKE ALTER ON examenuf2. * FROM 'estructura' @ 'localhost';
GRANT ALTER ON examenuf2.articles TO 'estructura' @ 'localhost';
GRANT ALTER ON examenuf2.clients TO 'estructura' @ 'localhost';
GRANT ALTER ON examenuf2.linees TO 'estructura' @ 'localhost';
GRANT ALTER ON examenuf2.pobles TO 'estructura' @ 'localhost';
GRANT ALTER ON examenuf2.provincies TO 'estructura' @ 'localhost';
GRANT ALTER ON examenuf2.venedors TO 'estructura' @ 'localhost';
FLUSH PRIVILEGES;
GRANT EVENT, LOCK TABLES, SELECT, SHOW DATABASES ON *. * TO 'consulta' @ 'localhost';
FLUSH PRIVILEGES; FLUSH PRIVILEGES;

CREATE USER 'convidat' @ 'localhost' identified BY 'convidat';
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'convidat' @ 'localhost';
GRANT SELECT ON examenuf2. * TO 'convidat' @ 'localhost';
FLUSH PRIVILEGES;

CREATE USER 'consulta' @ 'localhost' identified BY 'consulta';
GRANT SELECT ON examenuf2. * TO 'consulta' @ 'localhost';
FLUSH PRIVILEGES;

GRANT SELECT, UPDATE ON examenuf2. * TO 'modificacions' @ 'localhost';
FLUSH PRIVILEGES;

GRANT ALTER ON examenuf2. * TO 'estructura' @ 'localhost';
FLUSH PRIVILEGES;

GRANT INSERT ON *. * TO 'convidat' @ 'localhost';
FLUSH PRIVILEGES;

3 – Programació en base de dades

3.1 Tipus de variables, en quins moments es poden utilitzar.

Variable de parametres ()

– En el cas de les funcions només tenen parametres de entrada les cuanls s’an de declarar fora de la funcio.

select ciclisme.edadMinina(33, ‘a’);

CREATE DEFINER=`root`@`localhost` FUNCTION `edadMinina`(xEdad int, indici VARCHAR(15)) RETURNS int(15)

begin

DECLARE countCiclistes int default 0 ;

SELECT count(*) into countCiclistes

FROM ciclisme.ciclista

WHERE ciclista.nombre like concat(‘%’,indici,’%’) or ciclista.nomeq like concat(‘%’,indici,’%’) and ciclista.edad>=xEdad;

return countCiclistes;

end

– En el cas dels procediments tenen parametres de entrada i de sortida

set @numCiclistes = 2;

call ciclisme.consultaEdad(33, @numCiclistes);

select @numCiclistes;

Les d’entrada i les de sortida s’han de declarar fora de la funcio.

CREATE DEFINER=`root`@`localhost` PROCEDURE `consultaEdad`(in xedad int, inout numCiclistes int)

BEGIN

DECLARE countCiclistes int default 0 ;

SELECT count(*) into countCiclistes

FROM ciclisme.ciclista

WHERE edad=xedad;

if countCiclistes=numCiclistes then

SELECT concat(‘«Has estimat que hi ha ‘, numCiclistes ,’ ciclistes de ‘, xedad ,’ anys. Lhas encertat!!»’) missatge;

else

SELECT concat(‘«Has estimat que hi ha ‘, numCiclistes ,’ ciclistes de ‘, xedad ,’ anys, i hi ha ‘, countCiclistes ,’. Thas equivocat»’) missatge;

END IF;

set numCiclistes=countCiclistes;

END

Variables de sesio. Aquestes tenen tres tipos Globals de Sessio i Locals

No es necessari declararles (int, varchar, boolean).

SET @X=1;

SET @X=Descripcio1;

SELECT @X;

Variables emmagatzemades en programes

declare
var_name varchar(30)

declare
var_code int default =0;

SET
var_name =’Riñon Cuajado’;

SET
var_code =0;

show
var_name;

SELECT
var_code;

3.2 Tipus de paràmetres d’un stored procedure en mysql, com es declaren i com cal cridar el procediment en cada cas perquè funcioni correctament.

Els pàrametres son d’entrada (IN), de sortida (OUT) i d’entrada-sortida (INOUT)

Ejemple IN
El countryName es el parámetre IN

Al cridar el procediment (GetOfficeByCountry) es pasa el parametre dentrada (USA)

Exemple OUT
Al cridar el procediment (CountOrderByStatus) es passa el parametre d’entrada (Shipped) i el de sortida ( @total ). I es visualitza amb un SELECT/SHOW (@total)

3.3 Escriure un missatge a pantalla.

Amb la comanda SELECT

CREATE DEFINER=`root`@`localhost` PROCEDURE `consultaEdad`(in xedad int, inout numCiclistes int)

BEGIN

DECLARE countCiclistes int default 0 ;

SELECT count(*) into countCiclistes

FROM ciclisme.ciclista

WHERE edad=xedad;

if countCiclistes=numCiclistes then

SELECT concat(‘«Has estimat que hi ha ‘, numCiclistes ,’ ciclistes de ‘, xedad ,’ anys. Lhas encertat!!»’) missatge;

else

SELECT concat(‘«Has estimat que hi ha ‘, numCiclistes ,’ ciclistes de ‘, xedad ,’ anys, i hi ha ‘, countCiclistes ,’. Thas equivocat»’) missatge;

END IF;

set numCiclistes=countCiclistes;

END

3.4.1 Com es pot declarar una variable en un stored procedure

Amb DECLARE dintre de la Comanda BEGIN i es pot donar un valor per defecte amb DEFAULT.

CREATE DEFINER=`root`@`localhost` PROCEDURE `consultaEdad`(in xedad int, inout numCiclistes int)

BEGIN

DECLARE countCiclistes int default 0;

SELECT count(*) into countCiclistes

FROM ciclisme.ciclista

WHERE edad=xedad;

set numCiclistes=countCiclistes;

END

3.4.1 Com es pot assignar un valor en un stored procedure.

Primer es declara la variable.

DECLARE countCiclistes int default 0;

I despres amb SET de l’hi assigna un valor

SET numCiclistes=countCiclistes;

3.5 Assignar valor a tres variables amb un SELECT.

3.6 Sintaxi de la instrucció IF en mysql

Si l’expressió es compleix, llavors les sentències s’executaran, en cas contrari, el control es passarà a la següent instrucció després de END IF.

3.7 Sintaxis de la instrucció CASE en mysql.

La case_expression pot ser qualsevol expressió vàlida. Es compara el valor de l’case_expression amb la when_expression en cada clàusula WHEN, per exemple, when_expression_1, when_expression_2, etc. Si el valor de l’case_expression i when_expression_n són iguals.
En cas que cap dels when_expression en la clàusula WHEN coincideixi amb el valor de l’case_expression, les ordres de la clàusula ELSE es case_expression. La clàusula ELSE és opcional. Si omet la clàusula ELSE i no es ELSE cap coincidència, MySQL generarà un error.

3.8 Per què cal canviar el delimitador quan es crea un stored procedure?

S’ha de canviar perquè el “;” és el delimitador per defecte del Mysql.
Pel que si no ho canviem a aquest delimitador per defecte l’execució no es guarda.
Al començament pots assignar qualsevol símbol com a delimitador però després cal canviar-ho.

DELIMITER=> Fa que el valor següent es substitueixi pel “;” perquè no s’executi i ho guardi com un mètode

DELIMITER //

CREATE PROCEDURE GetAllProducts()

BEGIN

SELECT * FROM products;

END //

DELIMITER;

4 – Procediments i funcions

En aquest capítol, anem a discutir els procediments en SQL. Un subprograma és una unitat de programa / mòdul que realitza una tasca en particular. Aquests subprogrames es combinen per formar els programes més grans. Això és, bàsicament, anomenat el ‘disseny modular’. El subprograma pot ser invocada per un altre subprograma o programa que es diu el programa de trucada.

El subprograma es pot crear –

A nivell d’esquema
Dins d’un paquet
Dins d’un bloc PL / SQL

A nivell d’esquema, el subprograma és un subprograma independent. Es va crear amb el procediment CREATE o la sentència CREATE FUNCTION. S’emmagatzema a la base de dades i poden ser eliminats amb el procediment DROP o sentència DROP FUNCTION.

El subprograma creat dins d’un paquet és un subprograma envasats. S’emmagatzema a la base de dades i poden ser eliminats només quan el paquet s’elimina amb la instrucció DROP PACKAGE. Anem a discutir els paquets accessible en ‘PL / SQL – Paquets’.

subprogrames PL / SQL s’anomenen blocs PL / SQL que es poden invocar amb un conjunt de paràmetres. PL / SQL proporciona dos tipus de subprogrames –

Funcions – Aquests subprogrames tornen un únic valor; S’utilitza principalment per calcular i tornar a valorar.

Procediments – Aquests subprogrames no retornen un valor directament; S’utilitza principalment per a realitzar una acció.

En aquest capítol es va a cobrir els aspectes importants d’un procediment PL / SQL. Anem a discutir la funció PL / SQL al següent capítol.

L’imprescindible que cal saber de les funcions:

Paràmetres: només d’entrada (no cal indicar-ho)

Sortida: 1 sol valor (return); no paràmetres OUT/INOUT ni SELECTs !

Es permet fer dins d’una funció SELECT … INTO … (Guarda en una Variable)

No es criden amb CALL, sinó simplement amb el nom. Quan s’executen, el seu resultat substitueix la crida (per tant, es pot operar amb funcions)

Es poden usar a qualsevol lloc de la SELECT (select, from, where, group by, having, order by, …)

Cal especificar el tipus de dada que retornen

Cal especificar si el resultat és determinista (a mateixos paràmetres, sempre mateix resultat) o no. Default: No

Exemple Funcions:

CREATE DEFINER=`root`@`localhost` FUNCTION `buscaEmpleat`(indici varchar(10), dataNaixement date) RETURNS int(11)
BEGIN
declare num int;
SELECT count(*) into num
FROM employees
WHERE birth_date=8 then
SELECT ‘«molt bon equip»’ missatge;
elseif total4 then
SELECT ‘«bon equip»’ missatge;
elseif total<4 then
SELECT '«equip modest»' missatge;
elseif total=0 then
SELECT '«lequip no ha gunyat res»' missatge;
END IF;

END

set @numEtapas = 0;
set @portsGuanyats = 0;
call ciclisme.consultaEquip('once', @numEtapas, @portsGuanyats);
select @numEtapas, @portsGuanyats;

set @numEtapas = 0;
set @portsGuanyats = 0;
call ciclisme.consultaEquip('banesto', @numEtapas, @portsGuanyats);
select @numEtapas, @portsGuanyats;

set @numEtapas = 0;
set @portsGuanyats = 0;
call ciclisme.consultaEquip('carrera', @numEtapas, @portsGuanyats);
select @numEtapas, @portsGuanyats;

5 – Cursors Instrucció LOOP

L’imprescindible que cal saber dels cursors:

Un cursor en terminologia de bases de dades l’hem d’entendre com un apuntador al resultat d’una select, que ens permetrà recórrer-la fila a fila, volcant el valor de cadascuna de les seves columnes en variables (a l’estil d’un select … into)

Un cursor s’ha de declarar (donar-li un nom i definir sobre quina select funcionarà)[declare cursor c1 for select dorsal, edad, nombre from ciclista order by edad]

Per començar a treballar amb un cursor, un cop definit, cal obrir-lo[open c1]

Per acabar el treball amb un cursor, és important tancar-lo (per tal d’alliberar recursos; si no ho fem, podem deixar ram ocupada innecessàriament en el servidor)[close c1]

Per anar a la següent fila, desplaçarem el cursor i obtindrem en variables les columnes de la select (cal que hi hagi la mateixa quantitat de variables que de columnes, com en un select … into)[fetch c1 into num, edat, nom]

Es poden usar en procedures, functions i triggers

Se’n pot usar més d’un simultàniament: per això se’ls posa nom!

En general:

els cursors es poden recórrer cap endavant i cap enrere

poden ser una còpia de les dades (de manera que encara que es modifiquin les dades de les taules, disposem d’una còpia anterior, com una “foto fixa”, que no es modificarà fins que tanquem el cursor)

es poden modificar les taules apuntades pel cursor sense haver de fer un update, per exemple

quan s’acaben les dades apuntades pel cursor, s’indica amb alguna variable de sistema que es pot usar com a control de final de bucle

En mysql:

els cursors només es poden recórrer cap endavant

el cursor és insane (no és una còpia de les dades: si aquestes es modifiquen mentre recorrem el cursor, veurem una “foto moguda”)

NO es poden modificar les taules apuntades pel cursor: cal recórrer a instruccions del DML com ara update

Quan s’acaben les dades apuntades pel cursor, es dispara un error que cal atrapar amb un handler

Loop implementa una construcció de bucle simple, que permet l’execució repetida de la llista d’instruccions, que consisteix en un o més estats, cadascun acabat per un punt i coma (;), declaració delimitador. Els estats dins del bucle es repeteixen fins que s’acaba el bucle. En general, això s’aconsegueix amb una sentència LEAVE. Dins d’una funció emmagatzemada, de retorn també es pot utilitzar, que surt de la funció del tot.

Descuidar a incloure una declaració de terminació del bucle resulta en un bucle infinit.

exemple:

CREATE DEFINER=`root`@`localhost` FUNCTION `factoritzar`(num int) RETURNS varchar(100) CHARSET utf8
BEGIN
declare i int;
declare resultat Varchar(100);
declare contador int default 0;
Set i=2;
Set resultat=concat(num,’= ‘);
loop1: while (i=60)then

set diesXJubilarse=(65*365)-(DATEDIFF(NOW(),birth_date));

elseif ((DATEDIFF(NOW(),birth_date)/365)=55)then

set diesXJubilarse=(67*365)-(DATEDIFF(NOW(),birth_date));

elseif ((DATEDIFF(NOW(),birth_date)/365)<50)then

set diesXJubilarse=(70*365)-(DATEDIFF(NOW(),birth_date));

end if;

RETURN diesXJubilarse;

END

6 – Disparadors (Triggers)

L’imprescindible que cal saber sobre els triggers:

Un trigger és un programa lligat a operacions de DML de canvi (inserció, modificació o esborrat de files d’una taula)

Quan es crea un trigger cal especificar si va associat a un INSERT, un UPDATE o un DELETE, sobre quina taula i també el moment en què s’executarà (abans, després o en comptes de l’operació a què va associat)Llavors, l’ordre d’execució, per a cada operació i taula serà: trigger de before, operació, trigger de after.

Els triggers de before s’utilitzen normalment per a impedir l’operació o per a modificar les dades abans de modificar-les. Els triggers d’after se solen utilitzar per a fer modificacions en d’altres taules.

Característiques (en general limitacions) dels triggers en mysql:

No existeixen triggers de instead of (només de before i after)

Només es pot definir un trigger per taula, operació i moment (un per before i un per after per taula i operació com a màxim: en total 6 màxim per taula, doncs)

En un trigger les instruccions s’executen per a cadascuna de les files (en mysql el trigger comença obligatòriament per for each row)

Dins d’un trigger es disposa de dues taules virtuals: old i new. old conté els valors de les columnes que hi havia a la fila abans de l’operació i new conté els valors de les columnes que hi haurà a la fila després de l’operació. Des d’un trigger d’INSERT es pot accedir només a una taula new. Des d’un trigger de DELETE es pot accedir només a una taula old. Des d’un trigger d’UPDATE es pot accedir tant a old com a new

Des de dins d’un trigger no es poden executar instruccions de canvi (INSERT, UPDATE, DELETE) sobre la mateixa taula, ni recorrent a funcions externes. Sí que es pot canviar el valor de les columnes de la fila [SET new.columna=valor]. També es poden executar instruccions de canvi sobre d’altres taules

Des d’un trigger no es pot fer un rollback. Per impedir que l’operació es completi no hi ha més remei que provocar un error (disparar-lo amb la instrucció signal, per exemple)

Exemples:

Crea un trigger tal que quan s’esborri una etapa escrigui totes les dades de les files esborrades a una taula etapaDel. Aquesta taula ha de tenir la mateixa estructura que etapa més una columna data que prendrà el valor de la data i hora en què s’ha esborrat la fila. Crea abans la taula, preferentment amb una instrucció create… select. No t’oblidis de la clau primària!

Recorda que si un error impedeix que es completi l’operació, el trigger no s’executa.

create table etapaDel

(id int not null auto_increment,

data datetime not null,

netapa int not null,

km int not null,

salida varchar(30) not null,

llegada varchar(30) not null,

dorsal int not null,

primary key (id));

BEFORE DELETE

CREATE DEFINER = CURRENT_USER TRIGGER `ciclisme`.`etapa_BEFORE_DELETE` BEFORE DELETE ON `etapa` FOR EACH ROW

BEGIN

insert etapaDel(id, data, dorsal, nombre, edad, nomeq)

values (last_insert_id(), now(), old.netapa, old.km, old.salida, old.llegada, old.dorsal);

END

Quants triggers per taula admet mysql?

Un per cada:

before insert

after insert

before delete

after delete

before update

after update

Com puc accedir, en un trigger d’update, a la informació anterior al canvi.

Inserint un trigger before update.

CREATE DEFINER=`root`@`localhost` TRIGGER `ciclisme`.`ciclista_BEFORE_UPDATE` BEFORE UPDATE ON `ciclista` FOR EACH ROW

BEGIN

if (old.edad>new.edad) then

set new.edad=old.edad;

end if;

END

Com puc impedir dins d’un trigger d’update, que s’efectuï la modificació (dues possibilitats: provocant un error i sense provocar-lo)

Provocant el error:

CREATE DEFINER = CURRENT_USER TRIGGER `ciclisme`.`ciclista_BEFORE_UPDATE` BEFORE UPDATE ON `ciclista` FOR EACH ROW

BEGIN

if (old.edad>new.edad) then

signal sqlstate ‘45000’ set message_text=’No es pot esborrar!’;

end if;

END

Sense provocar-lo:

CREATE DEFINER=`root`@`localhost` TRIGGER `ciclisme`.`ciclista_BEFORE_UPDATE` BEFORE UPDATE ON `ciclista` FOR EACH ROW

BEGIN

if (old.edad>new.edad) then

set new.edad=old.edad;

end if;

END

Anuncios

Responder

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión /  Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión /  Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión /  Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión /  Cambiar )

Conectando a %s