Operaciones CRUD de SQL Server

Posted on by Rudeus Greyrat

Las declaraciones Transact SQL se pueden clasificar en declaraciones DDL, DML y DCL y, en términos de programación informática, las declaraciones DML se pueden denominar operaciones CRUD. En este artículo, podemos ver con más detalle las operaciones CRUD y cómo podemos usar esas declaraciones en SQL Server con ejemplos.

Operaciones CRUD:

CRUD es un acrónimo de las declaraciones CREATE, READ(SELECT), UPDATE y DELETE en SQL Server. CRUD en términos de base de datos también se puede mencionar como declaraciones de lenguaje de manipulación de datos (DML). El lenguaje de manipulación de datos se utiliza para administrar o manipular los datos presentes dentro de las tablas de la base de datos. Aunque las operaciones CRUD son compatibles con la vista de objetos de la base de datos, las vistas tienen varias restricciones y, por lo tanto, en este artículo, podemos probar las operaciones CRUD en las tablas. Antes de profundizar en las operaciones CRUD individuales o las declaraciones DML con más detalle, podemos crear una tabla llamada Empleado en la base de datos del geek para probar nuestras diversas consultas relacionadas con la operación CRUD.

Primero creemos una base de datos llamada geeks usando el siguiente comando en SQL Server Management Studio abriendo una nueva ventana de consulta:

Consulta:

CREATE DATABASE geeks;

Producción:

 

En la ventana de consulta, conéctese a la base de datos del geek recién creada usando el siguiente comando.

Consulta:

USE geeks

Ahora, cree una tabla de prueba llamada Empleado usando el siguiente comando.

Consulta:

 CREATE TABLE Employee (EMPId Int Identity NOT NULL,
 EmpNo varchar (10), SSN varchar (10), DOB DATE, 
 CreatedDt Datetime, CreatedBy varchar(10));

CREAR:

CREATE a menudo se refiere a la declaración INSERT que se usa para insertar nuevos datos en cualquiera de las tablas de SQL Server. Para INSERTAR registros en una tabla, necesitamos usar el comando INSERTAR y la sintaxis para el comando INSERTAR sería:

Consulta:

INSERT INTO TABLE_NAME (COLUMNS_NAMES_LIST)
VALUES (COLUMNS_VALUES_LIST);

En la sintaxis del comando INSERT anterior, debemos especificar el nombre de la tabla junto con la lista de nombres de columna y algunas cosas a tener en cuenta:

  • La lista de nombres de columna proporcionada debe coincidir con el orden de los valores de columna proporcionados.
  • No es necesario que la lista de nombres de columna especificados coincida con el orden de los nombres de columna disponibles en la tabla.
  • Si la lista de nombres de columnas incluye las columnas de identidad, entonces debemos especificar la opción SET IDENTITY_INSERT establecida en ON antes de intentar insertar valores para las columnas de identidad.
  • Para las columnas de tipo de datos de string y fecha, debemos especificar los valores entre comillas simples.
  • En el comando INSERT, no es necesario especificar obligatoriamente la lista de nombres de columna; sin embargo, si no se especifica, los valores deben especificarse en el orden en que las columnas están disponibles en la tabla.

Ahora, intentemos INSERTAR algunos valores en la tabla de empleados creada anteriormente con varios escenarios posibles como se enumeran a continuación. Los escenarios predeterminados que especifican valores en las columnas de orden están presentes, excluyendo la columna EmpId de tipo de datos de identidad.

Consulta: 

INSERT INTO Employee (EmpNO, SSN, DOB, CreatedDt, CreatedBy)
VALUES (1,'1234567890','2000-01-01', GETDATE(), 'system');

Producción:

 

Intentemos Insertar registros en la tabla en un orden de columna diferente con EmpNo al final como se muestra a continuación.

Consulta:

INSERT INTO Employee (SSN, DOB, CreatedDt, CreatedBy, EmpNO)
VALUES ('0123456789','1999-01-01', GETDATE(), 'system', 2);

Producción:

 

Para insertar registros en la tabla para la columna Tipo de datos de identidad, debemos usar la opción SET IDENTITY_INSERT como se muestra a continuación:

Consulta:

SET IDENTITY_INSERT Employee ON;
INSERT INTO Employee (EmpId, SSN, DOB, CreatedDt, CreatedBy, EmpNO)
VALUES (3, '0123456789','1999-01-01', GETDATE(), 'system', 2);
SET IDENTITY_INSERT Employee OFF;

Producción:

 

Para insertar valores múltiples en la columna Empleado con una sola instrucción INSERTAR, debemos usar la siguiente sintaxis:

Consulta:

INSERT INTO Employee (EmpNo, SSN, DOB)
 VALUES 
('4', '1231544984', '2000-02-01')
,('5', '5487946598', '2001-01-01')
,('6', '8789453115', '2002-01-01');

Producción:

 

Para insertar registros de alguna otra tabla, también podemos usar la declaración SELECT como se muestra a continuación:

Consulta:

INSERT INTO Employee (EmpNo, SSN, DOB)
SELECT EmpNo, SSN, DOB
FROM Employee_history;

Producción:

 

Al seleccionar registros de alguna otra tabla, también podemos usar la cláusula TOP o la cláusula DISTINCT u otros criterios.

También podemos insertar registros en una tabla pasando los resultados de la ejecución del procedimiento almacenado, digamos sp_Employee_History usando la siguiente sintaxis:

Consulta:

INSERT INTO Employee (EmpNo, SSN, DOB)
EXEC sp_Employee_History;

Producción:

 

LEER:

READ a menudo se refiere a la declaración SELECT o la operación de recuperación de datos de tablas o vistas. La sintaxis predeterminada de una instrucción SELECT sería:

Consulta:

SELECT * 
FROM object_name;

Donde object_name puede ser una tabla, una vista o funciones. Necesitamos especificar las llaves al intentar SELECCIONAR registros de funciones. La declaración SELECT se puede combinar con varias operaciones posibles como:

  • Operaciones agregadas como operaciones MIN, MAX, SUM y COUNT con la operación GROUP BY.
  • SELECCIONE TOP número de registros
  • SELECCIONAR conjuntos de resultados DISTINTOS
  • SELECCIONE registros filtrados usando condiciones DONDE
  • Cláusula HAVING para filtrar registros basados ​​en columnas agregadas.
  • Cláusula ORDER BY para ordenar el conjunto de resultados en función de las columnas seleccionadas.
     

Para SELECCIONAR registros de la tabla de empleados, podemos usar la siguiente sintaxis.
Consulta: 

SELECT * 
FROM Employee;

Producción:

 

Aunque el uso del símbolo * en la cláusula SELECT devuelve todas las columnas de la tabla, se recomienda especificar explícitamente los nombres de las columnas requeridas en la cláusula SELECT para evitar roturas posteriores en el código de la aplicación debido a cambios en la estructura o las columnas de la tabla. Para SELECCIONAR columnas específicas como EmpNo, SSN y DOB solo de la tabla de empleados, podemos usar la siguiente consulta:

Consulta:

SELECT EmpNo, SSN, DOB
FROM Employee;

Producción:

 

Para SELECCIONAR los 5 registros PRINCIPALES de la tabla de empleados, podemos usar la siguiente consulta.

Consulta:

SELECT TOP 5 EmpNo, SSN, DOB
FROM Employee;

Producción:

 

Para SELECCIONAR una combinación única de registros de la tabla de Empleados para ciertas columnas, podemos usar la cláusula DISTINCT junto con las columnas donde necesitamos obtener los valores únicos como se muestra a continuación:

Consulta:

SELECT DISTINCT EmpNo, SSN
FROM Employee;

Producción:

 

Para SELECCIONAR registros específicos de la tabla de empleados, podemos usar la cláusula WHERE como se muestra a continuación:

Consulta:

SELECT EmpNo, SSN, DOB
FROM Employee
WHERE EmpNo = '1';

Producción:

 

Para agregar el conjunto de resultados usando cualquier operación de agregado, necesitaríamos usar la siguiente sintaxis:

Consulta:

SELECT EmpNo, SSN, COUNT(*) cnt
FROM Employee
GROUP BY EmpNo, SSN;

Producción:

 

Para ordenar el conjunto de resultados anterior en función de EmpNo, podemos usar la cláusula ORDER BY como se muestra a continuación:

Consulta:

SELECT EmpNo, SSN, COUNT(*) cnt
FROM Employee
GROUP BY EmpNo, SSN
ORDER BY EmpNo;

Producción:

 

Para realizar el filtrado de valores agregados, necesitamos usar la cláusula HAVING como se muestra a continuación. 

Consulta:

SELECT EmpNo, SSN, COUNT(*) cnt
FROM Employee
GROUP BY EmpNo, SSN
HAVING COUNT(*) = 2
ORDER BY EmpNo;

Producción:

 

Para SELECCIONAR registros de varias tablas, necesitaríamos usar la cláusula JOIN, que puede ser INNER JOIN o LEFT OUTER JOIN o RIGHT OUTER JOIN u otros tipos, como se muestra a continuación.

Consulta:

SELECT e.EmpNo, e.SSN, e.DOB, eh.EmpNo as Older_EmpNo
FROM Employee e
INNER JOIN Employee_history eh on e.EmpNo = eh.EmpNo
WHERE e.EmpNo = 1
ORDER BY e.EmpNo;

Usando la declaración SELECT, incluso podemos crear una tabla y mientras creamos una tabla podemos crear la tabla con o sin datos usando las siguientes opciones
 

  • Crear una tabla con datos: 

Consulta:

SELECT * 
INTO Employee_backup
FROM Employee;

Producción:

 

Al seleccionar de la tabla de respaldo, se mostrarán los registros como se muestra a continuación:

Consulta:

SELECT * 
FROM Employee_backup;

Producción:

 

  • Crear tabla sin datos (solo esquema):

Consulta:

SELECT * 
INTO Employee_backup
FROM Employee
WHERE 1 = 2;

Producción:

 

Como podemos ver arriba, 0 filas se ven afectadas, lo que indica que no se produjeron cambios en los datos y, al seleccionar de la tabla de copia de seguridad, no se mostrarán registros, como se muestra a continuación:

Consulta:

SELECT * 
FROM Employee_backup;

Producción:

 

ACTUALIZAR:

La operación ACTUALIZAR se refiere a cambiar los datos existentes en la tabla y la operación o instrucción ACTUALIZAR en SQL Server incluye la cláusula SET para especificar qué columnas se actualizarán y usar la cláusula WHERE para ACTUALIZAR registros específicos. La sintaxis general para actualizar registros dentro de una tabla será:

Consulta:

UPDATE Table_Name
SET column_Name = column_value
WHERE Column_name = Filter_condition;

Producción:

 

Para actualizar el registro con EmpId = 3 para que EmpNo sea único en la tabla de empleados, podemos usar la siguiente consulta:

 Consulta:

UPDATE Employee
SET EmpNo = '3'
, SSN = '4984564512'
, DOB = '1998-01-01';

Luego de una verificación cuidadosa de la consulta anterior, podemos notar que no se especifica una «condición DONDE», y sin una condición DONDE, la ejecución de la consulta anterior ACTUALIZARÁ todos los registros en la tabla Empleado. Por lo tanto, debemos tener más cuidado al ejecutar una instrucción UPDATE al asegurar las cláusulas WHERE apropiadas como se muestra a continuación:

Consulta:

UPDATE Employee
SET EmpNo = '3'
, SSN = '4984564512'
, DOB = '1998-01-01'
WHERE EmpId = 3;

Producción:

 

 

 

Podemos ver el EmpId valorado en 3 actualizado correctamente con los valores correctos ahora. También podemos usar Common Table Expression para hacer la operación equivalente usando la siguiente consulta.

 Consulta:

; with cte as (
SELECT * 
FROM Employee
WHERE EmpId = 3)
UPDATE cte
SET EmpNo = '3'
, SSN = '4984564512'
, DOB = '1998-01-01';

Similar a la cláusula INSERT, también podemos ACTUALIZAR solo registros limitados usando la cláusula TOP como se muestra a continuación:

Consulta:

UPDATE TOP (5) Employee
SET CreatedDt = GETDATE()
, CreatedBy = 'system'
WHERE CreatedDt IS NULL;

Producción:

 

 

Podemos notar que la consulta de ACTUALIZACIÓN anterior ha actualizado solo 5 registros a pesar de que tenemos muchos registros con CreatedDt como valores NULL.
 

Ahora, vamos a crear algunos valores predeterminados para las columnas CreatedDt y CreatedBy usando la consulta a continuación.

Consulta:

ALTER TABLE Employee ADD CONSTRAINT 
CK_CreatedDt DEFAULT GETDATE() FOR CreatedDt;
ALTER TABLE Employee ADD CONSTRAINT
CK_CreatedBy DEFAULT GETDATE() FOR CreatedBy;

Producción:

 

Ahora, intentemos ACTUALIZAR todos los registros usando los valores predeterminados con la siguiente consulta:

Consulta:

UPDATE Employee
SET CreatedDt = DEFAULT
, CreatedBy = DEFAULT
WHERE CreatedDt IS NULL;

Producción:

 

 

Para ACTUALIZAR registros en una tabla usando valores de otra tabla, la instrucción ACTUALIZAR admite la cláusula JOIN con otra tabla, como se muestra en la consulta a continuación. Podemos especificar el nombre de alias de la tabla que debe actualizarse en la cláusula UPDATE y UNIR cualquier número de tablas en la cláusula FROM.

Consulta:

UPDATE e
SET CreatedDt = eh.CreatedDt
, CreatedBy = eh.CreatedBy
FROM Employee e
INNER JOIN Employee_Backup eh on e.EmpId = eh.EmpId;

ELIMINAR:

La operación DELETE se utiliza para eliminar o eliminar cualquier registro existente de la tabla. De manera similar a la operación UPDATE, se debe tener mucho más cuidado al ejecutar la instrucción DELETE y asegurarse de que tenga la cláusula WHERE necesaria para evitar la eliminación accidental de toda la tabla. 

Para ELIMINAR registros de una tabla sería:

Consulta:

DELETE FROM Table_name
WHERE column_name = Filter_condition;

Para eliminar registros de la tabla de empleados, podemos usar la siguiente consulta: 

Consulta:

DELETE FROM Employee
WHERE EmpId = 10;

Producción:

 

Podemos usar la Cláusula TOP para eliminar solo registros limitados para la condición WHERE seleccionada como se muestra a continuación.

Consulta:

DELETE TOP(1) FROM Employee
WHERE EmpId = 11;

Producción:

 

También podemos usar Common Table Expression para eliminar registros con la cláusula WHERE adecuada, como se muestra a continuación.
 

Consulta:

; with cte as (
SELECT * 
FROM Employee
WHERE EmpId = 12
)
DELETE FROM cte;

Producción:

 

Similar a la operación ACTUALIZAR, también podemos ELIMINAR registros de una tabla realizando una operación JOIN con otras tablas usando la consulta a continuación.

 Consulta:

DELETE e
FROM Employee e
JOIN Employee_Backup eh on e.EmpId = eh.EmpId
WHERE e.EmpId = 10;

Producción:

 

Operaciones CRUD como procedimientos almacenados
Hasta ahora, hemos analizado las operaciones CRUD en detalle y para realizar cambios en estas operaciones CRUD más rápido en SQL Server, se recomienda crear 4 procedimientos almacenados, uno para cada tabla con la plantilla de nombre del procedimiento como se muestra a continuación
• CREAR/INSERTAR – Nombre_esquema.Nombre_tabla_INSERTAR
• LEER/SELECCIONAR – Nombre_esquema.Nombre_tabla_SELECCIONAR
• ACTUALIZAR – Nombre_esquema.Nombre_tabla_ACTUALIZAR
• ELIMINAR – Nombre_esquema.Nombre_tabla_ACTUALIZAR
Si tenemos estas operaciones CRUD individuales creadas como Procedimientos para cualquier cambio de lógica durante las operaciones DML, simplemente podemos modifique los procedimientos almacenados involucrados sin cambios importantes en el código y evite los tiempos de reconstrucción del código en la mayoría de los casos.
Conclusión
Hoy, hemos entendido las operaciones CRUD y cómo realizar operaciones CRUD con varias sintaxis posibles en SQL Server con demostración también. Además de eso, también hemos discutido sobre los beneficios de realizar estas operaciones CRUD a través de procedimientos almacenados que pueden facilitar el proceso de desarrollo/mejora y también durante la resolución de problemas de lógica. Podemos utilizar la herramienta SQL Complete para realizar la función Autocompletar de cualquier instrucción Transact SQL o declaración DML para mejorar la productividad de los desarrolladores.

Publicación traducida automáticamente

Artículo escrito por rrjegan17 y traducido por Barcelona Geeks. The original can be accessed here. Licence: CCBY-SA

Deja una respuesta

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *