Documentación My SQL: INSTRUCCIÓN SQL SELECT

INSTRUCCIÓN SQL SELECT_Tomado de DataBase Administrator Amparo Olaya Benavides

SENTENCIA

SINTAXIS

EJEMPLO

SELECT

SELECT column1, column2, ...
FROM table_name;

SELECT CustomerName, City FROM Customers;

SELECT DISTINCT. se usa para devolver solo valores distintos (diferentes).

SELECT DISTINCT column1, column2, ...
FROM table_name;

SELECT DISTINCT Country FROM Customers;

WHERE

Se utiliza para extraer solo aquellos registros que cumplen una condición específica.

SELECT column1, column2, ...
FROM table_name
WHERE condition;

SELECT * FROM Customers
WHERE Country='Mexico';

La cláusula WHERE se puede combinar con los operadores AND, ORy NOT

SELECT column1, column2, ...
FROM table_name
WHERE condition1 AND condition2 AND condition3 ...;

SELECT column1, column2, ...
FROM table_name
WHERE condition1 OR condition2 OR condition3 ...;

SELECT column1, column2, ...
FROM table_name
WHERE NOT condition;

SELECT * FROM Customers
WHERE Country='Germany' AND City='Berlin';

SELECT * FROM Customers
WHERE City='Berlin' OR City='München';

SELECT * FROM Customers
WHERE NOT Country='Germany';

ORDER BY

Se utiliza para ordenar el conjunto de resultados en orden ascendente o descendente

SELECT column1, column2, ...
FROM table_name
ORDER BY column1, column2, ... ASC|DESC;

SELECT * FROM Customers
ORDER BY Country DESC;

LIMIT

se utiliza para especificar el número de registros que se devolverán

SELECT column_name(s)
FROM table_name
WHERE condition
LIMIT number;

SELECT * FROM Customers
LIMIT 3;

MIN

Devuelve el valor más pequeño de la columna seleccionada

SELECT MIN(column_name)
FROM table_name
WHERE condition;

SELECT MIN(Price) AS SmallestPrice
FROM Products;

MAX

Devuelve el valor más grande de la columna seleccionada

SELECT MAX(column_name)
FROM table_name
WHERE condition;

SELECT MAX(Price) AS LargestPrice
FROM Products;

COUNT

devuelve el número de filas que coincide con un criterio específico

SELECT COUNT(column_name)
FROM table_name
WHERE condition;

SELECT COUNT(ProductID)
FROM Products;

AVG

devuelve el valor promedio de una columna numérica

SELECT AVG(column_name)
FROM table_name
WHERE condition;

SELECT AVG(Price)
FROM Products;

SUM

devuelve la suma total de una columna numérica.

SELECT SUM (column_name)
FROM table_name
WHERE condition;

SELECT SUM(Quantity)
FROM OrderDetails;

El operador LIKE se usa en una clausula WHERE para buscar un patrón específico en una columna.

SELECT column1, column2, ...
FROM table_name
WHERE columnN LIKE pattern;

· Selecciona todos los clientes con un CustomerName que comienza con "a"

SELECT * FROM Customers
WHERE CustomerName LIKE 'a%';

· Selecciona todos los clientes con un CustomerName que termina en "a"

SELECT * FROM Customers
WHERE CustomerName LIKE '%a';

· selecciona todos los clientes con un CustomerName que tienen "o" en cualquier posición

SELECT * FROM Customers
WHERE CustomerName LIKE '%or%';

· Selecciona todos los clientes con un CustomerName que tienen "r" en la segunda posición

SELECT * FROM Customers
WHERE CustomerName LIKE '_r%';

· Selecciona a todos los clientes con un CustomerName que comienza con "a" y tiene al menos 3 caracteres de longitud

SELECT * FROM Customers
WHERE CustomerName LIKE 'a__%';

· selecciona a todos los clientes con un ContactName que comienza con "a" y termina con "o"

SELECT * FROM Customers
WHERE ContactName LIKE 'a%o';

· selecciona a todos los clientes con un CustomerName que NO comienza con "a"

SELECT * FROM Customers
WHERE CustomerName NOT LIKE 'a%';

permite especificar varios valores en una clausula WHERE.

es una abreviatura de múltiples ORcondiciones

SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1, value2, ...);

SELECT column_name(s)
FROM table_name
WHERE column_name IN (SELECT STATEMENT);

· selecciona todos los clientes que se encuentran en "Alemania", "Francia" o "Reino Unido"

SELECT * FROM Customers
WHERE Country IN ('Germany', 'France', 'UK');

· selecciona todos los clientes que NO se encuentran en "Alemania", "Francia" o "Reino Unido"

SELECT * FROM Customers
WHERE Country NOT IN ('Germany', 'France', 'UK');

· selecciona todos los clientes que son de los mismos países que los proveedores

SELECT * FROM Customers
WHERE Country IN (SELECT Country FROM Suppliers);

BETWEEN

selecciona valores dentro de un rango determinado. Los valores pueden ser números, texto o fechas

SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2;

SELECT * FROM Products
WHERE Price BETWEEN 10 AND 20;

SELECT * FROM Products
WHERE Price NOT BETWEEN 10 AND 20;

· selecciona todos los productos con un precio entre 10 y 20. Además; no mostrar productos con un CategoryID de 1,2 o 3

SELECT * FROM Products
WHERE Price BETWEEN 10 AND 20
AND CategoryID NOT IN (1,2,3);

· selecciona todos los productos con un ProductName entre Carnarvon Tigers y Mozzarella di Giovanni

SELECT * FROM Products
WHERE ProductName BETWEEN 'Carnarvon Tigers' AND 'Mozzarella di Giovanni'
ORDER BY ProductName;

· selecciona todos los productos con un ProductName que no se encuentra entre Carnarvon Tigers y Mozzarella di Giovanni

SELECT * FROM Products
WHERE ProductName NOT BETWEEN 'Carnarvon Tigers' AND 'Mozzarella di Giovanni'
ORDER BY ProductName;

· selecciona todos los pedidos con un OrderDate entre '01 -Julio-1996 'y '31 -Julio-1996'

SELECT * FROM Orders
WHERE OrderDate BETWEEN '1996-07-01' AND '1996-07-31';

se utilizan para dar un nombre temporal a una tabla, o una columna de una tabla

SELECT column_name AS alias_name
FROM table_name;

SELECT column_name(s)
FROM table_name AS alias_name;

SELECT CustomerID AS ID, CustomerName AS Customer
FROM Customers;

SELECT CustomerName AS Customer, ContactName AS [Contact Person]
FROM Customers;

· crea un alias llamado "Dirección" que combina cuatro columnas (Dirección, Código Postal, Ciudad y País)

SELECT CustomerName, Address + ', ' + PostalCode + ' ' + City + ', ' + Country AS Address
FROM Customers;

SELECT o.OrderID, o.OrderDate, c.CustomerName
FROM Customers AS c, Orders AS o
WHERE c.CustomerName='Around the Horn' AND c.CustomerID=o.CustomerID;

Equivalente:

SELECT Orders.OrderID, Orders.OrderDate, Customers.CustomerName
FROM Customers, Orders
WHERE Customers.CustomerName='Around the Horn' AND Customers.CustomerID=Orders.CustomerID;

Join

se utiliza para combinar filas de dos o más tablas, según una columna relacionada entre ellas

(INNER) JOIN

Devuelve registros que tienen valores coincidentes en ambas tablas

SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;

· selecciona todos los pedidos con información del cliente

SELECT Orders.OrderID, Customers.CustomerName
FROM Orders
INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID;

· selecciona todos los pedidos con información del cliente y de envio

SELECT Orders.OrderID, Customers.CustomerName, Shippers.ShipperName
FROM ((Orders
INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID)
INNER JOIN Shippers ON Orders.ShipperID = Shippers.ShipperID);

SELECT customers.customername, employees.lastname, orders.orderid

from (

(orders inner join customers on orders.customerID = customers.customerId)

orders inner join employees on orders.employeeID = employees.employeeId

);

LEFT JOIN

devuelve todos los registros de la tabla izquierda (tabla1) y los registros coincidentes de la tabla derecha (tabla2)

SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name = table2.column_name;

Seleccionará a todos los clientes y cualquier pedido que puedan tener:

SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID
ORDER BY Customers.CustomerName;

RIGHT JOIN

devuelve todos los registros de la tabla derecha (tabla2) y los registros coincidentes de la tabla izquierda (tabla1)

SELECT column_name(s)
FROM table1
RIGHT JOIN table2
ON table1.column_name = table2.column_name;

Devuelve todos los empleados y cualquier pedido que hayan realizado

SELECT Orders.OrderID, Employees.LastName, Employees.FirstName
FROM Orders
RIGHT JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID
ORDER BY Orders.OrderID;

FULL OUTER JOIN

devuelve todos los registros cuando hay una coincidencia en los registros de la tabla izquierda (tabla1) o derecha (tabla2)

SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2
ON table1.column_name = table2.column_name
WHERE condition;

SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
FULL OUTER JOIN Orders ON Customers.CustomerID=Orders.CustomerID
ORDER BY Customers.CustomerName;

SQL Self Join

Una autocombinación es una combinación normal, pero la tabla se une a sí misma

SELECT column_name(s)
FROM table1 T1, table1 T2
WHERE condition;

Hace coincidir los clientes que son de la misma ciudad

SELECT A.CustomerName AS CustomerName1, B.CustomerName AS CustomerName2, A.City
FROM Customers A, Customers B
WHERE A.CustomerID <> B.CustomerID
AND A.City = B.City
ORDER BY A.City;

UNION

se utiliza para combinar un conjunto de resultados de dos o más SELECT

UNION ALL

Permite valores duplicados

SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;

SELECT column_name(s) FROM table1
UNION ALL
SELECT column_name(s) FROM table2;

SELECT City FROM Customers
UNION
SELECT City FROM Suppliers
ORDER BY City;

SELECT City, Country FROM Customers
WHERE Country='Germany'
UNION
SELECT City, Country FROM Suppliers
WHERE Country='Germany'
ORDER BY City;

SELECT 'Customer' AS Type, ContactName, City, Country
FROM Customers
UNION
SELECT 'Supplier', ContactName, City, Country
FROM Suppliers;

GROUP BY

agrupa las filas que tienen los mismos valores en filas de resumen, como "encontrar el número de clientes en cada país"

SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
ORDER BY column_name(s);

enumera el número de clientes en cada país

SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country;

SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country
ORDER BY COUNT(CustomerID) DESC;

numera el número de pedidos enviados por cada envio

SELECT Shippers.ShipperName, COUNT(Orders.OrderID) AS NumberOfOrders FROM Orders
LEFT JOIN Shippers ON Orders.ShipperID = Shippers.ShipperID
GROUP BY ShipperName;

HAVING

SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
HAVING condition
ORDER BY column_name(s);

enumera el número de clientes en cada país. Incluya solo países con más de 5 clientes

SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country
HAVING COUNT(CustomerID) > 5;

SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country
HAVING COUNT(CustomerID) > 5
ORDER BY COUNT(CustomerID) DESC;

empleados que han registrado más de 10 pedidos

SELECT Employees.LastName, COUNT(Orders.OrderID) AS NumberOfOrders
FROM (Orders
INNER JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID)
GROUP BY LastName
HAVING COUNT(Orders.OrderID) > 10;

SELECT Employees.LastName, COUNT(Orders.OrderID) AS NumberOfOrders
FROM Orders
INNER JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID
WHERE LastName = 'Davolio' OR LastName = 'Fuller'
GROUP BY LastName
HAVING COUNT(Orders.OrderID) > 25;

EXISTS

se utiliza para probar la existencia de cualquier registro en una subconsulta (devuelve VERDADERO si la subconsulta devuelve uno o más registros)

SELECT column_name(s)
FROM table_name
WHERE EXISTS
(SELECT column_name FROM table_name WHERE condition);

SELECT SupplierName
FROM Suppliers
WHERE EXISTS (SELECT ProductName FROM Products WHERE Products.SupplierID = Suppliers.supplierID AND Price < 20);

SELECT SupplierName
FROM Suppliers
WHERE EXISTS (SELECT ProductName FROM Products WHERE Products.SupplierID = Suppliers.supplierID AND Price = 22);

INSERT INTO SELECT

copia datos de una tabla y los inserta en otra tabla

INSERT INTO table2
SELECT * FROM table1
WHERE condition;

INSERT INTO table2 (column1, column2, column3, ...)
SELECT column1, column2, column3, ...
FROM table1
WHERE condition;

INSERT INTO Customers (CustomerName, City, Country)
SELECT SupplierName, City, Country FROM Suppliers;

INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country)
SELECT SupplierName, ContactName, Address, City, PostalCode, Country FROM Suppliers;

INSERT INTO Customers (CustomerName, City, Country)
SELECT SupplierName, City, Country FROM Suppliers
WHERE Country='Germany';

CASE

CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    WHEN conditionN THEN resultN
    ELSE result
END;

SELECT OrderID, Quantity,
CASE
    WHEN Quantity > 30 THEN 'The quantity is greater than 30'
    WHEN Quantity = 30 THEN 'The quantity is 30'
    ELSE 'The quantity is under 30'
END AS QuantityText
FROM OrderDetails;

SELECT CustomerName, City, Country
FROM Customers
ORDER BY
(CASE
WHEN City IS NULL THEN Country
ELSE City
END);

Documentación My SQL

INSTRUCCIÓN SQL SELECT

No hay comentarios.:

Publicar un comentario