Ejemplos de Consultas y Procedimientos Almacenados en Mysql

Use northwind;

-- Consultas de una tabla
-- Consulta de selección a la tabla de clientes

Select Customerid, Companyname
, ContactName, ContactTitle, Country
from customers;

-- Filtrar la tabla
Select Customerid, Companyname
, ContactName, ContactTitle, Country
from customers where CustomerID='BERGS';

Select Customerid, Companyname
, ContactName, ContactTitle, Country
from customers where CustomerID like '_A%';
-- uso del like
Select Customerid, Companyname
, ContactName, ContactTitle, Country
from customers where CustomerID like '%S'
or Country='UK';

-- Uso del between
Select productid, productname, unitprice
from products
where unitprice>=20 and unitprice <=30;

Select productid, productname, unitprice
from products
where unitprice between 20 and 30;

Select orderid, orderdate
from orders
where orderdate between '1997-01-01' and '1997-12-31';

-- Solo mostrar solo valores únicos
Select distinct country, city from customers;

-- Usar Ordenar y limit

Select productid, productname, unitprice
from products
order by unitprice desc
limit 10;

-- Join entre tablas ISO-ANSI 82
Select customers.Customerid, Customers.Companyname,
Customers.Country, Orders.Orderid, Orders.Orderdate
from Customers, Orders
where Customers.Customerid=Orders.Customerid
and customers.country='France';

Select c.Customerid, c.Companyname,
c.Country, o.Orderid, o.Orderdate
from Customers as c inner join Orders as o
on c.Customerid=o.Customerid
where o.orderid is null;

-- Join entre tablas ISO-ANSI 92
Select count(*)
from Orders as o cross join Customers as c;

-- Full join
Select c.Customerid, c.Companyname,
c.Country, o.Orderid, o.Orderdate
from Customers as c inner join Orders as o
on c.Customerid=o.Customerid
UNION
Select c.Customerid, c.Companyname,
c.Country, o.Orderid, o.Orderdate
from Customers as c left outer join Orders as o
on c.Customerid=o.Customerid
UNION
Select c.Customerid, c.Companyname,
c.Country, o.Orderid, o.Orderdate
from Customers as c right outer join Orders as o
on c.Customerid=o.Customerid
;

-- Obtener ventas de northwind
Create view view_sales
as
Select c.companyname, c.country, o.orderid, o.orderdate
,ca.categoryname, p.productname, d.unitprice, d.quantity
, d.unitprice * d.quantity as Sales
from customers as c inner join orders as o
on c.customerid=o.customerid
inner join orderdetails as d
on o.orderid=d.orderid
inner join products as p
on d.productid=p.productid
inner join categories as ca
on p.categoryid=ca.categoryid;

Select country, SUM(Sales) as Sale from view_sales
group by country;

Select companyname, SUM(Sales) as Sale from view_sales
group by companyname;

Select Year(orderdate) as Anio,
Month(orderdate) as Mes,
SUM(Sales) as Sale
from view_sales
group by Year(orderdate), Month(orderdate);

/*
Crear una consulta que devuelva cuantas ordenes despacho el empleado Nancy Davolio al cliente de
código 'ANTON'
*/
Select o.orderid, o.orderdate, e.firstname, e.lastname
, c.companyname, c.customerid
from employees as e inner join orders as o
on e.employeeid=o.employeeid
inner join customers as c
on o.customerid=c.customerid
where e.lastname='Davolio' and c.customerid='Rattc';

--Creación de procedimientos almacenados

Delimiter $$
Create procedure proc_ordenesCliente ( IN par_codigocliente varchar(5))
Begin
Select c.companyname, o.orderid, o.orderdate
from customers as c inner join orders as o
on c.customerid=o.customerid
where c.customerid=par_codigocliente;
End
$$
Delimiter ;

Call proc_ordenesCliente ('ALFKI');

-- Crear un procedimiento que inserte datos a customers

Delimiter $$
Create procedure proc_insert_customers (IN PCustomerID varchar(5),
IN PCompanyname varchar(60) , IN PContactTitle varchar(30)
, IN PAddress varchar(120), IN PCity varchar(50)
, IN PRegion varchar(60), IN PPostalCode varchar(60), IN PCountry varchar(50)
, IN PPhone varchar(60), IN PFax varchar(60))
Begin
Insert into Customers (CustomerID, Companyname, ContactTitle
, Address, City, Region, PostalCode, Country, Phone, Fax)
values
(PCustomerID, PCompanyname, PContactTitle
, PAddress, PCity, PRegion, PPostalCode, PCountry, PPhone, PFax);
End
$$
Delimiter ;

-- Ejecutar el procedimiento que inserta datos

Call proc_insert_customers ('ABCD1','Empresa Inc','Dr.','Ciudad'
,'Guatemala','Central','01005','Guatemala','1020-3040','4050-6070');

-- Consultar el dato insertado

Select * from customers;

/*

1. procedimiento contará y devolverá el numero de ordenes de un cliente
2. Borrará un cliente si no tiene ordenes
   

*/

Delimiter ##
Create procedure proc_contarordenes (In PCodigoCliene varchar(6),
out Pnumero bigint)
Begin
Select count(*)
into Pnumero
from Orders
where customerid=PCodigoCliene;
End
##

Delimiter ;

Delimiter ##
Create procedure proc_eliminarCliente (PCodigoCliente varchar(5))
Begin
Declare var_numero bigint;
Call proc_contarordenes (PCodigoCliente, var_numero);
if var_numero=0 then
Delete from customers where customerid=PCodigoCliente;
else
Select 'No es posible borrar este cliente porque tiene ordenes' as mensaje;
end if;
End
##

Delimiter ;

-- Ejecutar procedimiento

Set sql_safe_updates=0;
call proc_eliminarCliente ('ABCD1');
Select * from customers;