Bd: Seguridad
tabla: usuario

%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
MariaDB [prueba]> create table usuario
    -> (nombre char(20) not null,
    -> clave char(10) not null);
Query OK, 0 rows affected (0.11 sec)

MariaDB [prueba]> describe usuario;
+--------+----------+------+-----+---------+-------+
| Field  | Type     | Null | Key | Default | Extra |
+--------+----------+------+-----+---------+-------+
| nombre | char(20) | NO   |     | NULL    |       |
| clave  | char(10) | NO   |     | NULL    |       |
+--------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%

ENCODE: PERMITE ENCRIPTAR UNA CADENA. LA FUNCIÓN RECIBE 2 ARGUMENTOS, EL PRIMERO , LA CADENA A ENCRIPTAR, EL SEGUNDO, UNA CADENA USADA COMO CONTRASEÑA PARA 
DESPUÉS DESENCRIPTAR.
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
mysql> select encode('triste','dia');
+------------------------+
| encode('triste','dia') |
+------------------------+
| isv¥++                 |
+------------------------+
1 row in set (0.04 sec)

%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
MariaDB [prueba]> insert into usuario value('Mauricio Vasquez',encode('mao','123'));
Query OK, 1 row affected (0.03 sec)

MariaDB [prueba]> select * from usuario;
+------------------+-------+
| nombre           | clave |
+------------------+-------+
| Mauricio Vasquez | ?z'   |
+------------------+-------+
1 row in set (0.00 sec)

MariaDB [prueba]> insert into usuario value('Javier Ospina Moreno',encode('jom','456'));
Query OK, 1 row affected (0.05 sec)

MariaDB [prueba]> select * from usuario;
+----------------------+-------+
| nombre               | clave |
+----------------------+-------+
| Mauricio Vasquez     | ?z'   |
| Javier Ospina Moreno | }}   |
+----------------------+-------+

///////////////////////////////////////////
Podemos almacenar la clave en una variable y luego insertar la clave encriptada.


MariaDB [prueba]> select @clave:=encode('sol','145');
+-----------------------------+
| @clave:=encode('sol','145') |
+-----------------------------+
| ¢×                         |
+-----------------------------+
1 row in set (0.00 sec)

MariaDB [prueba]> insert into usuario value('Sol Mireya',@clave);
Query OK, 1 row affected (0.06 sec)

MariaDB [prueba]> select * from usuario;
+----------------------+-------+
| nombre               | clave |
+----------------------+-------+
| Mauricio Vasquez     | ?z'   |
| Javier Ospina Moreno | }}   |
| Sol Mireya           | ½ž   |
+----------------------+-------+
3 rows in set (0.00 sec)


%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
Desencriptando

DECODE: PERMITE DESENCRIPTAR UNA CADENA ENCRIPTADA CON ENCODE. TAMBIÉN RECIBE 2 ARGUMENTOS: EL PRIMERO, LA CADENA A DESENCRIPTAR, EL SEGUNDO
LA CONTRASEÑA.

MariaDB [prueba]> select decode(clave,'123') from usuario where nombre='Mauricio Vasquez';
+---------------------+
| decode(clave,'123') |
+---------------------+
| mao                 |
+---------------------+
1 row in set (0.00 sec)

MariaDB [prueba]> select decode(clave,'456') from usuario where nombre='Javier Ospina Moreno';
+---------------------+
| decode(clave,'456') |
+---------------------+
| jom                 |
+---------------------+
1 row in set (0.00 sec)

MariaDB [prueba]> select decode(clave,'145') from usuario where nombre='Sol mireya';
+---------------------+
| decode(clave,'145') |
+---------------------+
| sol                 |
+---------------------+
1 row in set (0.00 sec)





///////////////////////////////////////////////////////////////////////////////////////////////////


COMANDOS******************************************************************
******************************************************************************
Investigar sobre el comando locate y realizar los siguientes ejercicios

mysql> select locate('r','sergio se rie mucho');
+-----------------------------------+
| locate('r','sergio se rie mucho') |
+-----------------------------------+
|                                 3 |
+-----------------------------------+
1 row in set (0.00 sec)

mysql> select locate('s','sergio se rie mucho');
+-----------------------------------+
| locate('s','sergio se rie mucho') |
+-----------------------------------+
|                                 1 |
+-----------------------------------+
1 row in set (0.00 sec)

mysql> select locate('i','sergio se rie mucho');
+-----------------------------------+
| locate('i','sergio se rie mucho') |
+-----------------------------------+
|                                 5 |
+-----------------------------------+
1 row in set (0.00 sec)


b. 
Investigar sobre el comando position y realizar los siguientes ejercicios
mysql> select position('i' in 'sergio se rie mucho');
+----------------------------------------+
| position('i' in 'sergio se rie mucho') |
+----------------------------------------+
|                                      5 |
+----------------------------------------+
1 row in set (0.03 sec)


c. 
Investigar sobre el comando mid realizar los siguientes ejercicios
mysql> select mid('hola como estas' from 4  for 6);
+--------------------------------------+
| mid('hola como estas' from 4  for 6) |
+--------------------------------------+
| a como                               |
+--------------------------------------+
1 row in set (0.00 sec)


d. 
Investigar sobre el comando ltrim realizar los siguientes ejercicios
mysql> select ltrim('     estudien chicos     ');
+------------------------------------+
| ltrim('     estudien chicos     ') |
+------------------------------------+
| estudien chicos                    |
+------------------------------------+
1 row in set (0.00 sec)


e
Investigar sobre el comando rtrim realizar los siguientes ejercicios
mysql> select rtrim('     estudien chicos     ');
+------------------------------------+
| rtrim('     estudien chicos     ') |
+------------------------------------+
|      estudien chicos               |
+------------------------------------+
1 row in set (0.01 sec)

f.

Investigar sobre el comando trim realizar los siguientes ejercicios
mysql> select trim('     estudien chicos     ');
+-----------------------------------+
| trim('     estudien chicos     ') |
+-----------------------------------+
| estudien chicos                   |
+-----------------------------------+
1 row in set (0.03 sec)

mysql> select trim(leading '0' from '00estudien chicos00');
+----------------------------------------------+
| trim(leading '0' from '00estudien chicos00') |
+----------------------------------------------+
| estudien chicos00                            |
+----------------------------------------------+
1 row in set (0.34 sec)

mysql> select trim(trailing '0' from '00estudien chicos00');
+-----------------------------------------------+
| trim(trailing '0' from '00estudien chicos00') |
+-----------------------------------------------+
| 00estudien chicos                             |
+-----------------------------------------------+
1 row in set (0.00 sec)

mysql> select trim(both '0' from '00estudien chicos00');
+-------------------------------------------+
| trim(both '0' from '00estudien chicos00') |
+-------------------------------------------+
| estudien chicos                           |
+-------------------------------------------+
1 row in set (0.00 sec)

mysql> select trim('0' from '00estudien chicos00');
+--------------------------------------+
| trim('0' from '00estudien chicos00') |
+--------------------------------------+
| estudien chicos                      |
+--------------------------------------+
1 row in set (0.01 sec)