Microsoft SQL Server Tutorial
Empezando con Microsoft SQL Server

Observaciones

Versiones

INSERTAR / SELECCIONAR / ACTUALIZAR / BORRAR: los conceptos básicos del lenguaje de manipulación de datos

La función de lenguaje de L a D M aip (DML para abreviar) incluye operaciones como INSERT , UPDATE y DELETE :

-- Create a table HelloWorld

CREATE TABLE HelloWorld (
    Id INT IDENTITY,
    Description VARCHAR(1000)
)


-- DML Operation INSERT, inserting a row into the table
INSERT INTO HelloWorld (Description) VALUES ('Hello World')


-- DML Operation SELECT, displaying the table 
SELECT * FROM HelloWorld  


-- Select a specific column from table
SELECT Description FROM HelloWorld


-- Display number of records in the table
SELECT Count(*) FROM HelloWorld


-- DML Operation UPDATE, updating a specific row in the table
UPDATE HelloWorld SET Description = 'Hello, World!' WHERE Id = 1


-- Selecting rows from the table (see how the Description has changed after the update?)
SELECT * FROM HelloWorld


-- DML Operation - DELETE, deleting a row from the table
DELETE FROM HelloWorld WHERE Id = 1


-- Selecting the table. See table content after DELETE operation 
SELECT * FROM HelloWorld

En este script estamos creando una tabla para demostrar algunas consultas básicas.

Los siguientes ejemplos muestran cómo consultar las tablas:

USE Northwind;
GO
SELECT TOP 10 * FROM Customers 
ORDER BY CompanyName

seleccionará los primeros 10 registros de la tabla de Customer , ordenados por la columna CompanyName de la base de datos Northwind (que es una de las bases de datos de muestra de Microsoft, se puede descargar desde aquí ):

Tenga en cuenta que Use Northwind; cambia la base de datos predeterminada para todas las consultas posteriores. Aún puede hacer referencia a la base de datos utilizando la sintaxis completa en forma de [Base de datos]. [Esquema]. [Tabla]:

SELECT TOP 10 * FROM Northwind.dbo.Customers 
ORDER BY CompanyName

SELECT TOP 10 * FROM Pubs.dbo.Authors
ORDER BY City

Esto es útil si está consultando datos de diferentes bases de datos. Tenga en cuenta que dbo , especificado "en medio" se denomina esquema y debe especificarse mientras se usa la sintaxis completamente calificada. Puedes considerarlo como una carpeta dentro de tu base de datos. dbo es el esquema predeterminado. El esquema predeterminado puede omitirse. Todos los otros esquemas definidos por el usuario deben ser especificados.

Si la tabla de la base de datos contiene columnas que se denominan como palabras reservadas, por ejemplo, Date , debe incluir el nombre de la columna entre paréntesis, como este:

-- descending order
SELECT TOP 10 [Date] FROM dbo.MyLogTable
ORDER BY [Date] DESC

Lo mismo se aplica si el nombre de la columna contiene espacios en su nombre (lo cual no se recomienda). Una sintaxis alternativa es utilizar comillas dobles en lugar de corchetes, por ejemplo:

-- descending order
SELECT top 10 "Date" from dbo.MyLogTable
order by "Date" desc 

Es equivalente pero no tan comúnmente usado. Observe la diferencia entre comillas dobles y comillas simples: las comillas simples se usan para cadenas, es decir,

-- descending order
SELECT top 10 "Date" from dbo.MyLogTable
where UserId='johndoe'
order by "Date" desc 

Es una sintaxis válida. Tenga en cuenta que T-SQL tiene un prefijo N para los tipos de datos NChar y NVarchar, por ejemplo,

SELECT TOP 10 * FROM Northwind.dbo.Customers 
WHERE CompanyName LIKE N'AL%'
ORDER BY CompanyName

devuelve todas las compañías que tienen un nombre de compañía que comienza con AL ( % es un comodín, úselo como usaría el asterisco en una línea de comando de DOS, por ejemplo, DIR AL* ). Para LIKE , hay un par de comodines disponibles, mira aquí para conocer más detalles.

Se une

Las combinaciones son útiles si desea consultar campos que no existen en una sola tabla, sino en varias tablas. Por ejemplo: desea consultar todas las columnas de la tabla de Region en la base de datos de Northwind . Pero observa que también necesita la RegionDescription , que se almacena en una tabla diferente, Region . Sin embargo, hay una clave común, RgionID que puede usar para combinar esta información en una sola consulta de la siguiente manera (las Top 5 solo devuelven las primeras 5 filas, omítala para obtener todas las filas):

SELECT TOP 5 Territories.*, 
    Regions.RegionDescription 
FROM Territories 
INNER JOIN Region 
    ON Territories.RegionID=Region.RegionID
ORDER BY TerritoryDescription

mostrará todas las columnas de Territories más la columna Region RegionDescription de Region . El resultado está ordenado por TerritoryDescription .

Alias ​​de tabla

Cuando su consulta requiere una referencia a dos o más tablas, puede que le resulte útil usar un Alias ​​de tabla. Los alias de tabla son referencias abreviadas de tablas que se pueden usar en lugar de un nombre completo de tabla y pueden reducir la escritura y la edición. La sintaxis para usar un alias es:

<TableName> [as] <alias>

Donde as es una palabra clave opcional. Por ejemplo, la consulta anterior se puede reescribir como:

SELECT TOP 5 t.*, 
    r.RegionDescription 
FROM Territories t
INNER JOIN Region r 
    ON t.RegionID = r.RegionID
ORDER BY TerritoryDescription

Los alias deben ser únicos para todas las tablas en una consulta, incluso si usa la misma tabla dos veces. Por ejemplo, si su tabla de Empleado incluyó un campo SupervisorId, puede usar esta consulta para devolver el nombre de un empleado y su supervisor:

SELECT e.*, 
    s.Name as SupervisorName -- Rename the field for output
FROM Employee e
INNER JOIN Employee s
    ON e.SupervisorId = s.EmployeeId
WHERE e.EmployeeId = 111

Uniones

Como hemos visto antes, una unión agrega columnas de diferentes orígenes de tabla. ¿Pero qué pasa si quieres combinar filas de diferentes fuentes? En este caso puedes usar un UNION. Supongamos que está planeando una fiesta y desea invitar no solo a los empleados, sino también a los clientes. Entonces podrías ejecutar esta consulta para hacerlo:

SELECT FirstName+' '+LastName as ContactName, Address, City FROM Employees
UNION
SELECT ContactName, Address, City FROM Customers

Devolverá nombres, direcciones y ciudades de los empleados y clientes en una sola tabla. Tenga en cuenta que las filas duplicadas (si las hubiera) se eliminan automáticamente (si no desea esto, use UNION ALL lugar). El número de columna, los nombres de columna, el orden y el tipo de datos deben coincidir en todas las declaraciones de selección que forman parte de la unión: esta es la razón por la que SELECT combina el FirstName y el LastName del Empleado en el FirstName del ContactName .

Variables de tabla

Puede ser útil, si necesita lidiar con datos temporales (especialmente en un procedimiento almacenado), para usar las variables de la tabla: la diferencia entre una tabla "real" y una variable de la tabla es que solo existe en la memoria para el procesamiento temporal.

Ejemplo:

DECLARE @Region TABLE
(
  RegionID int, 
  RegionDescription NChar(50)
)

crea una tabla en la memoria. En este caso, el prefijo @ es obligatorio porque es una variable. Puede realizar todas las operaciones DML mencionadas anteriormente para insertar, eliminar y seleccionar filas, por ejemplo,

INSERT INTO @Region values(3,'Northern')
INSERT INTO @Region values(4,'Southern')

Pero normalmente, lo rellenarías en base a una tabla real como

INSERT INTO @Region
SELECT * FROM dbo.Region WHERE RegionID>2;

que leería los valores filtrados de la tabla real dbo.Region e insertarlo en la tabla de memoria @Region , donde se puede utilizar para un procesamiento posterior. Por ejemplo, podrías usarlo en una unión como

SELECT * FROM Territories t
JOIN @Region r on t.RegionID=r.RegionID

Lo que en este caso devolvería todos Southern territorios del Northern y Southern . Más información detallada se puede encontrar aquí . Las tablas temporales se discuten aquí , si está interesado en leer más sobre ese tema.

NOTA: Microsoft solo recomienda el uso de variables de tabla si el número de filas de datos en la variable de tabla es inferior a 100. Si trabajará con grandes cantidades de datos, use una tabla temporal o tabla temporal, en su lugar.

IMPRESIÓN

Muestra un mensaje a la consola de salida. Al usar SQL Server Management Studio, esto se mostrará en la pestaña de mensajes, en lugar de la pestaña de resultados:

PRINT 'Hello World!';

SELECCIONE todas las filas y columnas de una tabla

Sintaxis:

SELECT *
FROM table_name

El uso del operador de asterisco * sirve como acceso directo para seleccionar todas las columnas de la tabla. Todas las filas también se seleccionarán porque esta instrucción SELECT no tiene una cláusula WHERE , para especificar ningún criterio de filtrado.

Esto también funcionaría de la misma manera si añadiera un alias a la tabla, por ejemplo e en este caso:

SELECT *
FROM Employees AS e

O si desea seleccionar todo de una tabla específica, puede usar el alias + ". *":

SELECT e.*, d.DepartmentName
FROM Employees AS e
    INNER JOIN Department AS d 
        ON e.DepartmentID = d.DepartmentID

También se puede acceder a los objetos de la base de datos utilizando nombres completos:

SELECT * FROM [server_name].[database_name].[schema_name].[table_name]

Esto no se recomienda necesariamente, ya que cambiar los nombres del servidor y / o la base de datos causaría que las consultas que usan nombres completamente calificados ya no se ejecuten debido a nombres de objetos no válidos.

Tenga en cuenta que los campos antes de table_name se pueden omitir en muchos casos si las consultas se ejecutan en un único servidor, base de datos y esquema, respectivamente. Sin embargo, es común que una base de datos tenga múltiples esquemas y, en estos casos, el nombre del esquema no debe omitirse cuando sea posible.

Advertencia: el uso de SELECT * en el código de producción o en los procedimientos almacenados puede llevar a problemas más adelante (a medida que se agregan nuevas columnas a la tabla, o si las columnas se reorganizan en la tabla), especialmente si su código hace suposiciones simples sobre el orden de las columnas, o el número de columnas devueltas. Por lo tanto, siempre es más seguro especificar explícitamente los nombres de columna en las instrucciones SELECT para el código de producción.

SELECT col1, col2, col3
FROM table_name

Seleccionar filas que coincidan con una condición

En general, la sintaxis es:

SELECT <column names>
FROM <table name>
WHERE <condition>

Por ejemplo:

SELECT FirstName, Age
FROM Users
WHERE LastName = 'Smith'

Las condiciones pueden ser complejas:

SELECT FirstName, Age
FROM Users
WHERE LastName = 'Smith' AND (City = 'New York' OR City = 'Los Angeles')

ACTUALIZAR fila específica

UPDATE HelloWorlds
SET HelloWorld = 'HELLO WORLD!!!'
WHERE Id = 5

El código anterior actualiza el valor del campo "HelloWorld" con "HELLO WORLD !!!" para el registro donde "Id = 5" en la tabla HelloWorlds.

Nota: En una declaración de actualización, se recomienda usar una cláusula "donde" para evitar actualizar la tabla completa a menos que y hasta que su requerimiento sea diferente.

ACTUALIZAR todas las filas

Una forma simple de actualización es incrementar todos los valores en un campo dado de la tabla. Para hacerlo, necesitamos definir el campo y el valor de incremento

El siguiente es un ejemplo que incrementa el campo Score en 1 (en todas las filas):

UPDATE Scores
SET score = score + 1  

Esto puede ser peligroso ya que puede corromper sus datos si accidentalmente realiza una ACTUALIZACIÓN para una fila específica con una ACTUALIZACIÓN para todas las filas en la tabla.

Comentarios en codigo

Transact-SQL admite dos formas de escribir comentarios. Los comentarios son ignorados por el motor de la base de datos y están destinados a que las personas los lean.

Los comentarios están precedidos por -- y se ignoran hasta que se encuentra una nueva línea:

-- This is a comment
SELECT *
FROM MyTable -- This is another comment
WHERE Id = 1;

Los comentarios de Slash Star comienzan con /* y terminan con */ . Todo el texto entre esos delimitadores se considera un bloque de comentarios.

/* This is
a multi-line
comment block. */
SELECT Id = 1, [Message] = 'First row'
UNION ALL
SELECT 2, 'Second row'
/* This is a one liner */
SELECT 'More';

Los comentarios de Slash Star tienen la ventaja de mantener el comentario utilizable si la declaración SQL pierde nuevos caracteres de línea. Esto puede suceder cuando se captura SQL durante la resolución de problemas.

Los comentarios de barra diagonal pueden anidarse y un inicio /* dentro de un comentario de barra diagonal debe finalizar con una */ para que sea válido. El siguiente código dará como resultado un error.

/*
SELECT *
FROM CommentTable
WHERE Comment = '/*'
*/

La barra diagonal, aunque esté dentro de la cita, se considera como el inicio de un comentario. Por lo tanto, debe ser terminado con otra barra de la estrella de cierre. La forma correcta sería

/*
SELECT *
FROM CommentTable
WHERE Comment = '/*'
*/  */

Recuperar información básica del servidor

SELECT @@VERSION

Devuelve la versión de MS SQL Server que se ejecuta en la instancia.

SELECT @@SERVERNAME

Devuelve el nombre de la instancia de MS SQL Server.

SELECT @@SERVICENAME

Devuelve el nombre del servicio de Windows con el que se ejecuta MS SQL Server.

SELECT serverproperty('ComputerNamePhysicalNetBIOS');

Devuelve el nombre físico de la máquina donde se ejecuta SQL Server. Útil para identificar el nodo en un clúster de conmutación por error.

SELECT * FROM fn_virtualservernodes();

En un clúster de conmutación por error, se devuelven todos los nodos donde se puede ejecutar SQL Server. No devuelve nada si no es un cluster.

Uso de transacciones para cambiar datos de forma segura

Siempre que cambie los datos, en un comando del Lenguaje de manipulación de datos (DML), puede ajustar sus cambios en una transacción. DML incluye UPDATE , TRUNCATE , INSERT y DELETE . Una de las maneras en que puede asegurarse de que está cambiando los datos correctos sería utilizar una transacción.

Los cambios en DML llevarán un bloqueo en las filas afectadas. Cuando comienza una transacción, debe finalizar la transacción o todos los objetos que se modifican en el DML permanecerán bloqueados por quien haya iniciado la transacción. Puede finalizar su transacción con ROLLBACK o COMMIT . ROLLBACK devuelve todo dentro de la transacción a su estado original. COMMIT coloca los datos en un estado final en el que no puede deshacer sus cambios sin otra declaración DML.

Ejemplo:

--Create a test table

USE [your database]
GO
CREATE TABLE test_transaction (column_1 varchar(10))
GO

INSERT INTO 
 dbo.test_transaction
        ( column_1 )
VALUES
        ( 'a' )

BEGIN TRANSACTION --This is the beginning of your transaction

UPDATE dbo.test_transaction
SET column_1 = 'B'
OUTPUT INSERTED.*
WHERE column_1 = 'A'
  

ROLLBACK TRANSACTION  --Rollback will undo your changes
           --Alternatively, use COMMIT to save your results

SELECT * FROM dbo.test_transaction   --View the table after your changes have been run

DROP TABLE dbo.test_transaction

Notas:

• Este es un ejemplo simplificado que no incluye el manejo de errores. Pero cualquier operación de base de datos puede fallar y por lo tanto lanzar una excepción. Aquí hay un ejemplo de cómo podría ser un manejo de errores tan requerido. Nunca se debe utilizar transacciones sin un controlador de errores, de lo contrario podría salir de la transacción en un estado desconocido.
• Según el nivel de aislamiento , las transacciones ponen bloqueos en los datos que se consultan o modifican. Debe asegurarse de que las transacciones no se estén ejecutando durante mucho tiempo, ya que bloquearán los registros en una base de datos y pueden provocar interbloqueos con otras transacciones paralelas. Mantenga las operaciones encapsuladas en las transacciones lo más cortas posible y minimice el impacto con la cantidad de datos que está bloqueando.

BORRAR todas las filas

DELETE
FROM Helloworlds

Esto borrará todos los datos de la tabla. La tabla no contendrá filas después de ejecutar este código. A diferencia de DROP TABLE , esto preserva la tabla y su estructura, y puede continuar insertando nuevas filas en esa tabla.

Otra forma de eliminar todas las filas de la tabla es truncarla de la siguiente manera:

TRUNCATE TABLE HelloWords

Diferencia con la operación DELETE son varias:

1. La operación truncada no se almacena en el archivo de registro de transacciones
2. Si existe el campo IDENTITY , este será restablecido
3. TRUNCATE se puede aplicar en una tabla completa y no en parte de ella (en lugar de con el comando DELETE , puede asociar una cláusula WHERE )

Restricciones de TRUNCATE

1. No se puede TRUNCAR una tabla si hay una referencia FOREIGN KEY
2. Si la mesa está participada en una INDEXED VIEW
3. Si la tabla se publica utilizando TRANSACTIONAL REPLICATION o MERGE REPLICATION
4. No disparará ningún GATILLO definido en la tabla

[sic]

TABLA DE TRUNCATOS

TRUNCATE TABLE Helloworlds 

Este código borrará todos los datos de la tabla Helloworlds. Truncar tabla es casi similar a Delete from Table código de Delete from Table . La diferencia es que no se pueden usar las cláusulas where con Truncate. Truncar la tabla se considera mejor que eliminar porque usa menos espacios de registro de transacciones.

Tenga en cuenta que si existe una columna de identidad, se restablece al valor inicial inicial (por ejemplo, la ID auto-incrementada se reiniciará desde 1). Esto puede llevar a una inconsistencia si las columnas de identidad se usan como una clave externa en otra tabla.

Crear nueva tabla e insertar registros de la tabla antigua

SELECT * INTO NewTable FROM OldTable

Crea una nueva tabla con la estructura de la tabla antigua e inserta todas las filas en la nueva tabla.

Algunas restricciones

1. No puede especificar una variable de tabla o un parámetro con valores de tabla como la nueva tabla.
2. No puede utilizar SELECT ... INTO para crear una tabla particionada, incluso cuando la tabla fuente está particionada. SELECT ... INTO no usa el esquema de partición de la tabla fuente; en su lugar, la nueva tabla se crea en el grupo de archivos predeterminado. Para insertar filas en una tabla particionada, primero debe crear la tabla particionada y luego usar la instrucción INSERT INTO ... SELECT FROM.
3. Los índices, las restricciones y los activadores definidos en la tabla de origen no se transfieren a la nueva tabla, ni pueden especificarse en la instrucción SELECT ... INTO. Si estos objetos son necesarios, puede crearlos después de ejecutar la instrucción SELECT ... INTO.
4. La especificación de una cláusula ORDER BY no garantiza que las filas se inserten en el orden especificado. Cuando se incluye una columna dispersa en la lista de selección, la propiedad de la columna dispersa no se transfiere a la columna en la nueva tabla. Si se requiere esta propiedad en la nueva tabla, modifique la definición de la columna después de ejecutar la instrucción SELECT ... INTO para incluir esta propiedad.
5. Cuando se incluye una columna calculada en la lista de selección, la columna correspondiente en la nueva tabla no es una columna calculada. Los valores en la nueva columna son los valores que se calcularon en el momento en que se ejecutó SELECT ... INTO.

[ sic ]

Obtención de recuento de filas de la tabla

El siguiente ejemplo se puede usar para encontrar el número total de filas para una tabla específica en una base de datos si table_name se reemplaza por la tabla que desea consultar:

SELECT COUNT(*) AS [TotalRowCount] FROM table_name;

También es posible obtener el recuento de filas para todas las tablas uniéndose de nuevo a la partición de la tabla basada en el HEAP de las tablas (index_id = 0) o el índice agrupado agrupado (index_id = 1) usando la siguiente secuencia de comandos:

SELECT  [Tables].name                AS [TableName],
        SUM( [Partitions].[rows] )    AS [TotalRowCount]
FROM    sys.tables AS [Tables]
JOIN    sys.partitions AS [Partitions]
    ON  [Tables].[object_id]    =    [Partitions].[object_id]
    AND [Partitions].index_id IN ( 0, 1 )
--WHERE    [Tables].name = N'table name' /* uncomment to look for a specific table */
GROUP BY    [Tables].name;

Esto es posible ya que cada tabla es esencialmente una tabla de partición única, a menos que se le agreguen particiones adicionales. Este script también tiene la ventaja de no interferir con las operaciones de lectura / escritura en las filas de las tablas '.