Sql

create database pruebaBD;

use pruebaBD;

create table persona(
doc_persona int not null primary key,
nombre varchar(45) not null,
apellido varchar(45) not null,
edad int
);

create table telefonos(
id_telefono int not null auto_increment primary key,
telefono int not null
);

alter table telefonos
add column doc_persona int not null,
add constraint fk_persona foreign key (doc_persona) references persona(doc_persona);


INSERT

-- muestro las columnas de la table categoria
show columns from categoria;

-- muestro los datos que tiene esa categoria
select * from categoria;

-- inserto un dato en categoria dandole valor a cada campo
insert into categoria values(null,'bebidas');

-- inserto un dato a categoria espeficando columna en la cual voy agregar el valor.
insert into categoria(nombre) values('aceites');

-- inserto multiples datos a la tabla
insert into categoria (nombre) values('detergentes'),('dulce'),('frituras');

-- otra forma de insertar datos
insert into categoria set nombre = "categoria";



show columns from productos;

insert into productos (nombre, precio, idcategoria) values ('Refresco','8.500','1');

insert into productos (nombre, precio, fecha_alta, idcategoria)
values ('paleta', 5, '2016-04-15', 1);

-- se inserta un dato con la fecha que tenga el servidor en ese momento.
insert into productos (nombre, precio, fecha_alta, idcategoria)
values ('helado', 5, curdate(), 1);

select * from productos;


CRENADO RELACIONES


create table categoria(
id int auto_increment primary key,
nombre varchar(50) not null
);


-- agreo una columna idcategoria en la tabla productos
alter table productos add column idcategoria int not null;


-- agrego la llave foranea de la tabla productos
alter table productos add
constraint fk_catPro foreign key (idcategoria) references categoria(id)
on update cascade
on delete cascade;

-- elimino la llave foranea de la tabla productos
alter table productos drop foreign key fk_catPro;

-- elimino la columna idcategoria de la tabla productos
alter table productos drop column idcategoria;

-- creo la tabla proveedores
create table proveedores(
id int auto_increment not null primary key,
nombre varchar(45) not null
);

-- creo la relacion de muchos a muchos
create table detalle_producto_proveedor(
id int not null auto_increment primary key,
idproducto int not null,
idproveedor int not null,
constraint pk_prov foreign key (idproveedor) references proveedores(id)
on update cascade
on delete cascade,
constraint pk_prod foreign key (idproducto) references productos(idproducto)
on update cascade
on delete cascade
);


OPERADORES

select * from productos where idCategoria=1 and precio=10; -- hago una consulta filtrando mas de dos campos

select * from productos where idCategoria=1 or precio=10;  -- hago una consulta diciendo que si la categoria es 1 o el precio sea 10

select * from productos where precio is null; -- llamo a todos los datos del campon precio que sean igual a null

select * from productos where precio is not null; -- llamo a todos los datos del campo precio que no esten null

select * from productos where precio between 5 and 10; -- busco en el campo precio en el rango de 5 a 10

select * from productos where precio not between 5 and 10; -- busco todos los datos que no se encuentra en el rango de 5 a 10

select * from productos where precio in(5,6,7,8,9,10); -- hago un listado de los datos que voy a buscar en precio

select * from productos where precio not in(5,6,7,8,9,10); -- busco datos en el campo precio y especifico que datos no me busque

select * from productos where fecha_alta between"2015-01-01" and curdate();

select * from productos where nombre like "Limonada"; -- like se utiliza para comprar cadenas de testo

select * from productos where nombre like "Limon%"; -- busco dentro del campo nombre todos los datos que contegan limon al final

select * from productos where nombre like "%Limon"; -- busco dentro del campo nombre todos los datos que contegan limon al inicio

select * from productos where nombre like "%Limon%"; -- busco dentro del campo nombre todos los datos que contegan la palabra limon

-- hago una consulta con varias restricciones
select * from productos where idCategoria=2 and precio>10 and existencia between 5 and 20 and fecha_alta is not null;


select * from productos where idCategoria in(select id from categoria where id>2); -- genero una sub consulta


CONSULTAS BÁSICAS


SELECT * FROM categoria limit 5;
SELECT * FROM categoria limit 3,10;

select * from productos order by nombre asc;

select nombre from categoria;

SELECT * FROM productos;

select nombre,precio,existencia from productos;

select nombre as Nombre,precio as Precio,existencia from productos;


select nombre as Nombre,precio as Precio,existencia from productos where existencia=50;


select count(idProducto) as Cantidad, idCategoria from productos group by idcategoria;



select min(existencia) as Menor_Existencia, max(existencia) as Maxima_Existe, idcategoria from productos group by idCategoria;

Disparadores

CREATE DEFINER=`root`@`localhost` trigger actuailizar_totalventa_insert after
 insert on detalles_venta
 for each row
 update ventas
 set saldo_total = (select sum(total) from detalles_venta where id_venta=new.id_venta)
 where id_venta = new.id_venta


CREATE DEFINER=`root`@`localhost` trigger actuailizar_existencia_producto after insert on detalles_venta
for each row
update productos set existencia=(existencia-new.cant) where id_producto = new.id_producto


CREATE DEFINER=`root`@`localhost` trigger actuailizar_totalventa_update after
 update on detalles_venta
 for each row
 update ventas
 set saldo_total = (select sum(total) from detalles_venta where id_venta=new.id_venta)
 where id_venta = new.id_venta

Patrones de Diseño

import java.sql.SQLException;
import java.util.List;
import personas.dto.PersonaDTO;

/**
 * Esta interfaz contiene los métodos abstractos con las operaciones básicas
 * sobre la tabla de Persona CRUD (Create, Read, Update y Delete) Se debe crear
 * una clase concreta para implementar el código asociado a cada método
 *
 * @author Ubaldo
 *
 */
public interface PersonaDao {

    public int insert(PersonaDTO persona)
            throws SQLException;

    public int update(PersonaDTO persona)
            throws SQLException;

    public int delete(PersonaDTO persona)
            throws SQLException;

    public List<PersonaDTO> select() throws SQLException;
}

_________________________________________________________________________________

import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import personas.dto.PersonaDTO;

/**
 * Esta clase implementa la clase PersonaDao es una implementacion con la
 * tecnologia JDBC podría haber otro tipo de implementaciones con tecnologias
 * como Hibernate, iBatis, SpringJDBC, etc.
 *
 * @author Ubaldo
 *
 */
public class PersonaDaoJDBC implements PersonaDao {

    private Connection userConn;
    private final String SQL_INSERT = "INSERT INTO persona(nombre, apellido) VALUES(?,?)";
    private final String SQL_UPDATE = "UPDATE persona SET nombre=?, apellido=? WHERE id_persona=?";
    private final String SQL_DELETE = "DELETE FROM persona WHERE id_persona = ?";
    private final String SQL_SELECT = "SELECT id_persona, nombre, apellido FROM persona";

    public PersonaDaoJDBC() {
    }

    public PersonaDaoJDBC(Connection conn) {
        this.userConn = conn;
    }

    /**
     * El metodo insert recibe como argumento un objeto DTO el cual viene de
     * otra capa, y se extraen sus valores para crear un nuevo registro
     *
     * @param persona
     * @throws java.sql.SQLException
     */
    @Override
    public int insert(PersonaDTO persona) throws SQLException {
        Connection conn = null;
        PreparedStatement stmt = null;
        int rows = 0;
        try {
            conn = (this.userConn != null) ? this.userConn
                    : Conexion.getConnection();
            stmt = conn.prepareStatement(SQL_INSERT);
            int index = 1;
            stmt.setString(index++, persona.getNombre());
            stmt.setString(index, persona.getApellido());
            System.out.println("Ejecutando query:" + SQL_INSERT);
            rows = stmt.executeUpdate();
            System.out.println("Registros afectados:" + rows);
        } finally {
            Conexion.close(stmt);
            if (this.userConn == null) {
                Conexion.close(conn);
            }
        }
        return rows;
    }

    @Override
    public int update(PersonaDTO persona)
            throws SQLException {
        Connection conn = null;
        PreparedStatement stmt = null;
        int rows = 0;
        try {
            conn = (this.userConn != null) ? this.userConn
                    : Conexion.getConnection();
            System.out.println("Ejecutando query:" + SQL_UPDATE);
            stmt = conn.prepareStatement(SQL_UPDATE);
            int index = 1;
            stmt.setString(index++, persona.getNombre());
            stmt.setString(index++, persona.getApellido());
            stmt.setInt(index, persona.getId_persona());
            rows = stmt.executeUpdate();
            System.out.println("Registros actualizados:" + rows);
        } finally {
            Conexion.close(stmt);
            if (this.userConn == null) {
                Conexion.close(conn);
            }
        }
        return rows;
    }

    @Override
    public int delete(PersonaDTO persona) throws SQLException {
        Connection conn = null;
        PreparedStatement stmt = null;
        int rows = 0;
        try {
            conn = (this.userConn != null) ? this.userConn
                    : Conexion.getConnection();
            System.out.println("Ejecutando query:" + SQL_DELETE);
            stmt = conn.prepareStatement(SQL_DELETE);
            stmt.setInt(1, persona.getId_persona());
            rows = stmt.executeUpdate();
            System.out.println("Registros eliminados:" + rows);
        } finally {
            Conexion.close(stmt);
            if (this.userConn == null) {
                Conexion.close(conn);
            }
        }
        return rows;
    }

    @Override
    public List<PersonaDTO> select() throws SQLException {
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        PersonaDTO personaDTO = null;
        List<PersonaDTO> personas = new ArrayList<PersonaDTO>();
        try {
            conn = (this.userConn != null) ? this.userConn : Conexion.getConnection();
            stmt = conn.prepareStatement(SQL_SELECT);
            rs = stmt.executeQuery();
            while (rs.next()) {
                int idPersonaTemp = rs.getInt(1);
                String nombreTemp = rs.getString(2);
                String apellidoTemp = rs.getString(3);
                personaDTO = new PersonaDTO();
                personaDTO.setId_persona(idPersonaTemp);
                personaDTO.setNombre(nombreTemp);
                personaDTO.setApellido(apellidoTemp);
                personas.add(personaDTO);
            }
        } finally {
            Conexion.close(rs);
            Conexion.close(stmt);
            if (this.userConn == null) {
                Conexion.close(conn);
            }
        }
        return personas;
    }

}

_________________________________________________________________________________


package personas.dto;


public class PersonaDTO {

    private int id_persona;
    private String nombre;
    private String apellido;

    public PersonaDTO() {
    }

    public PersonaDTO(int id_persona) {
        this.id_persona = id_persona;
    }

    public int getId_persona() {
        return id_persona;
    }

    public void setId_persona(int id_persona) {
        this.id_persona = id_persona;
    }

    public String getNombre() {
        return nombre;
    }

    public void setNombre(String nombre) {
        this.nombre = nombre;
    }

    public String getApellido() {
        return apellido;
    }

    public void setApellido(String apellido) {
        this.apellido = apellido;
    }

    @Override
    public String toString() {
        return "Persona{" + "id_persona=" + id_persona + ", nombre=" + nombre + ", apellido=" + apellido + '}';
    }
}