Introducción a SQL

Este tutorial proporciona una introducción al lenguaje de consulta estructurado (SQL), aprenda a crear tablas con claves primarias, columnas, restricciones, índices y claves externas.

Módulos

1. Crear tablas
2. Crear disparadores/Triggers
3. Insertar datos
4. Columnas de indexación
5. Consulta de datos
6. Agregar columnas
7. Consultar el diccionario de datos de Oracle
8. Actualización de datos
9. Consultas agregadas
10. Comprimir datos
11. Eliminar datos
12. Borrar tablas
13. Deshacer Borrar tablas

Crear tablas

Las tablas son la unidad básica de almacenamiento de datos en una base de datos Oracle. Los datos se almacenan en filas y columnas. Define una tabla con un nombre de tabla, como empleados, y un conjunto de columnas. Le da a cada columna un nombre de columna, como employee_id, last_name y job_id; un tipo de datos, como VARCHAR2, DATE o NUMBER; y un ancho. El ancho puede estar predeterminado por el tipo de datos, como en FECHA. Si las columnas son del tipo de datos NUMBER, defina precisión y escala en lugar de ancho. Una fila es una colección de información de columna correspondiente a un solo registro.
Puede especificar reglas para cada columna de una tabla. Estas reglas se denominan restricciones de integridad. Un ejemplo es una restricción de integridad NOT NULL. Esta restricción obliga a la columna a contener un valor en cada fila.
Por ejemplo:

create table DEPARTMENTS (  
  deptno        number,  
  name          varchar2(50) not null,  
  location      varchar2(50),  
  constraint pk_departments primary key (deptno)  
);

Las tablas pueden especificar relaciones declarativas entre tablas, lo que generalmente se denomina integridad referencial. Para ver cómo funciona esto, podemos crear una tabla «secundaria» de la tabla DEPARTMENTS incluyendo una clave externa en la tabla EMPLOYEES que hace referencia a la tabla DEPARTMENTS.

Por ejemplo:

create table EMPLOYEES (  
  empno             number,  
  name              varchar2(50) not null,  
  job               varchar2(50),  
  manager           number,  
  hiredate          date,  
  salary            number(7,2),  
  commission        number(7,2),  
  deptno           number,  
  constraint pk_employees primary key (empno),  
  constraint fk_employees_deptno foreign key (deptno) 
      references DEPARTMENTS (deptno)  
);

Las claves externas deben hacer referencia a claves primarias, por lo que para crear una tabla «secundaria», la tabla «principal» debe tener una clave primaria para que la clave externa haga referencia.

Crear disparadores/Triggers

Los activadores son procedimientos que se almacenan en la base de datos y se ejecutan o activan implícitamente cuando sucede algo. Tradicionalmente, los desencadenantes admitían la ejecución de un código de procedimiento, en Oracle el procedimiento SQL se denomina bloque PL/SQL. PL significa lenguaje procedimental. Cuando se produjo una INSERCIÓN, ACTUALIZACIÓN o ELIMINACIÓN en una tabla o vista. Los activadores apoyan el sistema y otros eventos de datos en BASE DE DATOS y ESQUEMA.

Los activadores se utilizan con frecuencia para completar automáticamente las claves primarias de la tabla, los ejemplos de activadores a continuación muestran un activador de ejemplo para hacer precisamente esto. Usaremos una función incorporada para obtener un identificador o GUID globalmente único.

create or replace trigger  DEPARTMENTS_BIU
    before insert or update on DEPARTMENTS
    for each row
begin
    if inserting and :new.deptno is null then
        :new.deptno := to_number(sys_guid(), 
          'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX');
    end if;
end;
/
create or replace trigger EMPLOYEES_BIU
    before insert or update on EMPLOYEES
    for each row
begin
    if inserting and :new.empno is null then
        :new.empno := to_number(sys_guid(), 
            'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX');
    end if;
end;
/

Insertar datos

Ahora que tenemos tablas creadas y tenemos activadores para completar automáticamente nuestras claves primarias, podemos agregar datos a nuestras tablas. Debido a que tenemos una relación padre-hijo, con la tabla DEPARTMENTS como tabla principal y la tabla EMPLOYEES como secundaria, primero insertaremos una fila en la tabla DEPARTMENTS.

insert into departments (name, location) values
   ('Finance','New York');

insert into departments (name, location) values
   ('Development','San Jose');

Verifiquemos que la inserción fue exitosa ejecutando una instrucción SQL SELECT para consultar todas las columnas y todas las filas de nuestra tabla.

select * from departments;

Puede ver que se habrá generado una identificación automáticamente. Ahora puede insertar en la tabla EMPLOYEES una nueva fila, pero deberá colocar el valor DEPTID generado en su declaración SQL INSERT.
Los ejemplos a continuación muestran cómo podemos hacer esto usando una consulta SQL, pero simplemente puede ingresar el número de departamento directamente.

insert into EMPLOYEES 
   (name, job, salary, deptno) 
   values
   ('Sam Smith','Programmer', 
    5000, 
  (select deptno 
  from departments 
  where name = 'Development'));

insert into EMPLOYEES 
   (name, job, salary, deptno) 
   values
   ('Mara Martin','Analyst', 
   6000, 
   (select deptno 
   from departments 
   where name = 'Finance'));

insert into EMPLOYEES 
   (name, job, salary, deptno) 
   values
   ('Yun Yates','Analyst', 
   5500, 
   (select deptno 
   from departments 
   where name = 'Development'));

Columnas de indexación

Normalmente, los desarrolladores indexan las columnas por tres razones principales:

Para aplicar valores únicos dentro de una columna
Para mejorar el rendimiento del acceso a los datos
Para evitar la escalada de bloqueos al actualizar filas de tablas que utilizan integridad referencial declarativa
Cuando se crea una tabla y se especifica una CLAVE PRIMARIA, se crea automáticamente un índice para aplicar la restricción de la clave primaria. Si especifica UNIQUE para una columna al crear una columna, también se crea un índice único. Para ver los índices que ya existen para una tabla determinada, puede ejecutar la siguiente consulta de diccionario.

select table_name "Table", 
       index_name "Index", 
       column_name "Column", 
       column_position "Position"
from  user_ind_columns 
where table_name = 'EMPLOYEES' or 
      table_name = 'DEPARTMENTS'
order by table_name, column_name, column_position

Por lo general, es una buena forma indexar claves externas, las claves externas son columnas en una tabla que hacen referencia a otra tabla. En nuestro ejemplo de tabla EMPLOYEES y DEPARTMENTS, la columna DEPTNO en la tabla EMPLOYEE hace referencia a la clave principal de la tabla DEPARTMENTS.

create index employee_dept_no_fk_idx 
on employees (deptno)

También podemos determinar que la tabla EMPLOYEE se buscará con frecuencia por la columna NAME. Para mejorar las búsquedas de rendimiento y garantizar la unicidad, podemos crear un índice único en la columna NOMBRE de la tabla EMPLEADO.

create unique index employee_ename_idx
on employees (name);

Oracle proporciona muchas otras tecnologías de indexación, incluidos índices basados en funciones que pueden indexar expresiones, como una función superior, índices de texto que pueden indexar texto de forma libre, índices de mapas de bits útiles en el almacenamiento de datos. También puede crear tablas organizadas indexadas, puede usar índices de partición y más. A veces es mejor tener menos índices y aprovechar las capacidades de la memoria. Todos estos temas están más allá del alcance de esta introducción básica.

Consulta de datos

Para seleccionar datos de una sola tabla es razonablemente fácil, simplemente use la sintaxis SELECT … FROM … WHERE … ORDER BY …

select * from employees;

Para consultar datos de dos tablas relacionadas, puede unir los datos

select e.name employee,
           d.name department,
           e.job,
           d.location
from departments d, employees e
where d.deptno = e.deptno(+)
order by e.name;

Como alternativa a una combinación, puede utilizar una selección en línea para consultar datos.

select e.name employee,
          (select name 
           from departments d 
           where d.deptno = e.deptno) department,
           e.job
from employees e
order by e.name;

Agregar columnas

Puede agregar columnas adicionales después de haber creado su tabla usando la sintaxis ALTER TABLE … ADD …

Por ejemplo:

alter table EMPLOYEES 
add country_code varchar2(2);

Consultar el diccionario de datos de Oracle

Se puede acceder a los metadatos de la tabla desde el diccionario de datos de Oracle. Las siguientes consultas muestran cómo puede consultar las tablas del diccionario de datos.

select table_name, tablespace_name, status
from user_tables
where table_Name = 'EMPLOYEES';

select column_id, column_name , data_type
from user_tab_columns
where table_Name = 'EMPLOYEES'
order by column_id;

Actualización de datos

Puede usar SQL para actualizar los valores en su tabla, para hacer esto usaremos la cláusula de actualización

update employees
set country_code = 'US';

La consulta anterior actualizará todas las filas de la tabla de empleados y establecerá el valor del código de país en EE. UU. También puede actualizar selectivamente solo una fila específica.

update employees
set commission = 2000
where  name = 'Sam Smith';

Ejecutemos una consulta para ver cómo se ven nuestros datos

select name, country_code, salary, commission
from employees
order by name;

Consultas agregadas

Puede sumar datos en tablas utilizando funciones agregadas. Usaremos alias de columna para cambiar el nombre de las columnas para mayor legibilidad, también usaremos la función de valor nulo (NVL) para permitirnos sumar correctamente columnas con valores nulos.

select 
      count(*) employee_count,
      sum(salary) total_salary,
      sum(commission) total_commission,
      min(salary + nvl(commission,0)) min_compensation,
      max(salary + nvl(commission,0)) max_compensation
from employees;

Comprimir datos

A medida que su base de datos crece en tamaño a gigabytes o terabytes y más, considere usar la compresión de tablas. La compresión de tablas ahorra espacio en disco y reduce el uso de memoria en la caché del búfer. La compresión de tablas también puede acelerar la ejecución de consultas durante las lecturas. Sin embargo, existe un costo en la sobrecarga de la CPU para la carga de datos y DML. La compresión de tablas es completamente transparente para las aplicaciones. Es especialmente útil en sistemas de procesamiento analítico en línea (OLAP), donde hay largas operaciones de solo lectura, pero también se puede utilizar en sistemas de procesamiento de transacciones en línea (OLTP).

La compresión de tabla se especifica con la cláusula COMPRESS de la sentencia CREATE TABLE. Puede habilitar la compresión para una tabla existente utilizando esta cláusula en una instrucción ALTER TABLE. En este caso, los únicos datos que se comprimen son los datos insertados o actualizados después de habilitar la compresión. De manera similar, puede deshabilitar la compresión de tablas para una tabla comprimida existente con la instrucción ALTER TABLE … NOCOMPRESS. En este caso, todos los datos que ya estaban comprimidos permanecen comprimidos y los nuevos datos se insertan sin comprimir.

Para habilitar la compresión de datos futuros, utilice la siguiente sintaxis.

alter table EMPLOYEES compress for oltp; 
alter table DEPARTMENTS compress for oltp;

Eliminar datos

Puede eliminar una o más filas de una tabla utilizando la sintaxis DELETE.

Por ejemplo, para eliminar una fila específica:

delete from employees 
where name = 'Sam Smith';

Borrar tablas

Puede eliminar tablas mediante el comando SQL DROP. Al borrar una tabla, se eliminarán todas las filas y se quitarán los subobjetos, incluidos los índices y los activadores. Las siguientes declaraciones DROP eliminarán las tablas de departamentos y empleados. La cláusula opcional de restricciones en cascada eliminará las restricciones, lo que le permitirá eliminar las tablas de la base de datos en cualquier orden.

drop table departments cascade constraints;
drop table employees cascade constraints;

Deshacer Borrar Tablas

Si el parámetro de inicialización RECYCLEBIN está en ON (el valor predeterminado en 10g), al borrar esta tabla se colocará en la papelera de reciclaje. Para ver si puede deshacer una tabla, ejecute la siguiente consulta de diccionario de datos:

select object_name, 
       original_name, 
       type, 
       can_undrop, 
       can_purge
from recyclebin;

Para deshacer tablas usamos el comando flashback.

por ejemplo:

flashback table DEPARTMENTS to before drop;
flashback table EMPLOYEES to before drop;
select count(*) departments 
from departments;
select count(*) employees
from employees;