Diferencias

Muestra las diferencias entre dos versiones de la página.

--- sql [2018/10/04 21:57] – lmachuca
+++ sql [2025/06/16 19:14] (actual) – lmachuca
@@ Línea 4: / Línea 4: @@
   * Información General - [[wp>SQL]].
+  * Cosas más específicas para Helpcom, por motor: [[f1:MySQL-sintaxis]], [[f1:PostgreSQL-sintaxis]], [[f1:SQLite-sintaxis]].
-=====Expresiones Generales=====
+=====Expresiones DDL=====
+==== Schemas ====
+Un schema es una unidad lógica estructural que reúne tablas, vistas y funciones. Un schema está bien definido en SQ93 y en PostgreSQL; en MySQL, un schema es equivalente a una base de datos.
+<code sql>
+CREATE SCHEMA helpcomschema OWNER usuario; -- crear un schema
+GRANT USAGE ON SCHEMA helpcomschema TO usuario2; -- permitir conectar
+DROP SCHEMA helpcomschema; -- bajar un schema si no hay dependencias
+DROP SCHEMA helpcomschema CASCADE; -- bajar un schema y todos sus objetos.
+</code>
+En PostgreSQL se muestra los schemas de una conexión con ''\dn'' y los schemas foráneos con ''\det+''.
+En SQLite y MySQL no existe una forma estandarizada de mostrar los schemas de una conexión.
 ==== Tablas ====
@@ Línea 24: / Línea 41: @@
 Por ejemplo:
-<code mysql>
+<code sql>
 CREATE TABLE libro (
-  isbn VARCHAR(20) NOT NULL,
+  isbn VARCHAR(20) NOT NULL UNIQUE,
-  titulo VARCHAR(80) NOT NULL,
+  titulo TEXT NOT NULL,
-  autor INTEGER,
+  autor_id INTEGER,
-  edicion INTEGER,
+  edicion_id INTEGER,
-  anyo INTEGER
+  "año" SMALLINT UNSIGNED,
   hardprint BOOLEAN,
   hardcover BOOLEAN,
@@ Línea 40: / Línea 57: @@
 <code>
                         Table "public.libro"
-  Column   |         Type          | Collation | Nullable | Default
+  Column    |         Type          | Collation | Nullable | Default
------------+-----------------------+-----------+----------+---------
+------------+-----------------------+-----------+----------+---------
- isbn      | character varying(20) |           | not null |
+ isbn       | character varying(20) |           | not null |
- titulo    | character varying(85) |           |          |
+ titulo     | text                  |           |          |
- author    | integer               |           |          |
+ author_id  | integer               |           |          |
- edicion   | integer               |           |          |
+ edicion_id | integer               |           |          |
- anyo      | integer               |           |          |
+ año        | smallint unsigned     |           |          |
- hardprint | boolean               |           |          |
+ hardprint  | boolean               |           |          |
- hardcover | boolean               |           |          |
+ hardcover  | boolean               |           |          |
 Indexes:
     "libro_pkey" PRIMARY KEY, btree (isbn)
@@ Línea 58: / Línea 75: @@
 En MySQL es posible recuperar la sentencia que creó una tabla con ''SHOW CREATE TABLE tabla''.
+En PostgreSQL, SQLite, etc, y en SQ92 no existe una sentencia directa para observar el schema. Aunque la tabla mostrada anteriormente se puede componer usando los registros del schema de sistema ''information_schema'':
-En lenguaje SQL no hay una forma estandarizada para listar tablas. En distintos motores existen distintos mecanismos.
+<code sql>
+select column_name,data_type,column_default as "def?",is_nullable as "null?", (case when data_type like '%character%' then character_maximum_length when data_type like '%int%' then numeric_precision else '0' end ) as "variable", generation_expression
+from information_schema.columns
+where table_catalog='database' and table_schema='helpcomschema' and table_name='libro';
+</code>
-^  ^  PostgreSQL  ^  MySQL  ^  SQLite  ^
-^   Mostrar tablas\\ de la conexión actual | ''\dt'' |  ''show tables;''  | ''SHOW TABLES''  '
-^   Buscar tablas\\ en el sistema   | ''select tablename from pg_catalog.pg_tables\\ where schemaname="midb"''  | ''select table_name from information_schema.tables\\ where table_schema="midb"''  | |
+Lo que resulta en la siguiente salida:
-==== Mostrar ====
+<file>
+ column_name |     data_type     | def? | null? | variable | generation_expression
+-------------+-------------------+------+-------+----------+-----------------------
+ isbn        | character varying |      | NO    |       20 |
+ titulo      | text              |      | NO    |        0 |
+ autor_id    | integer           |      | YES   |       32 |
+ edicion_id  | integer           |      | YES   |       32 |
+ año         | smallint          |      | YES   |       16 |
+ hardprint   | boolean           |      | YES   |        0 |
+ hardcover   | boolean           |      | YES   |        0 |
-La instrucción **SELECT** es para buscar información.
+</file>
-<code sql>
-  SELECT campos
-  FROM tablas
-  WHERE (condicion1 [AND/OR] condicion2 [AND/OR]...)
-  [ORDER BY orden [ASC/DESC] ]
-  ;
-</code>
-En la consulta:
-  * ''campos'' puede ser una lista de uno o más campos, conteos o fórmulas. Asterisco ("''*''") se usa para indicar mostrar todos los campos de una tabla.
+**Mostrar tablas en el schema:**
-  * ''tablas'' puede ser una lista de una o más tablas o uniones de tablas (JOIN, UNION, etc), con o sin su fuente de base de datos (por ejemplo, ''crux.mae_usuarios'' o simplemente ''mae_usuarios'').
-  * ''condicion1, condicion2'' es una lista opcional de condiciones en el lenguaje lógico de SQL. Una condición puede ser comparar el valor de un campo, por ejemplo "''WHERE mae_nombre_empresa LIKE '%Lily%' ''".
-  * ''orden'' es una lista de campos o de fórmulas, del más prioritario al menos prioritario, para ordenar los resultados. Por ejemplo "''ORDER BY cantidad_vendida, WEEKDAY(fecha_venta)''" ordenaría una lista de ventas de la que vendió más a menos y luego por el día de semana de la venta.
-Ojo: Distintos motores como MySQL y PostgreSQL pueden extender la sintaxis.
+En lenguaje SQL no hay una forma estandarizada para listar tablas. En distintos motores existen distintos mecanismos.
-**Ejemplos de búsquedas en bases de datos de Helpcom**
+  * Postgres: ''\dt'' , ''\det'' y similares.
+  * MySQL: ''show tables;'' y similares.
+  * SQite: ''.tables''.
-Buscar aquellos clientes en una tabla de clientes que pertenecen a cierta comuna:
+En SQL92 se puede obtener una vista generalizada de tablas de un schema consultando ''information_schema.tables'':
-<code mysql>
-  SELECT * FROM mae_clientes
+<code sql>
-  WHERE cli_comuna LIKE '%comuna%'
+select table_name, table_type
+from information_schema.tables
+where table_catalog='mibdd' and table_schema='helpcomschema';
 </code>
-Top 5 de las comunas con más clientes:
+Las tablas con tipo ''BASE TYPE'' son creadas con ''CREATE TABLE''. Objetos de otros tipos, como ''LOCAL TABLE'', son creados por otras instrucciones.
-<code mysql>
-  SELECT cli_comuna, COUNT(*) AS cantidad FROM mae_clientes
+**Eliminar una tabla**
-  GROUP BY cli_comuna
-  ORDER BY cantidad DESC LIMIT 5
+<code sql>
+DROP TABLE libro; -- eliminar si no hay depedencias
+DROP TABLE libro CASCADE; -- eliminar la tabla, y todas sus filas y tablas dependientes.
 </code>
-Buscar todos aquellos folios de un CAF (identificado por su tipo de documento y folio de partida de ingreso) que no están en uso:
+==== Vistas ====
-<code mysql>
-  SELECT dte_folios.* FROM dte_folios JOIN dte_folios_ingreso USING foi_id
+Una vista es una tabla virtual cuya estructura calza la definición de tipo de datos de una consulta, es decir está [[#Mostrar Registros|definida en términos de un SELECT]]:
-  WHERE tdo_id = 33 AND foi_desde = 123456 AND fol_usado = 'NO'
+<code sql>
+  CREATE VIEW vista AS
+  SELECT f(x,y) AS columna1, CAST(g(x,y) AS INTEGER) AS columna2
+  FROM x JOIN Y ON ...
+  ORDER BY ...
+  ;
 </code>
-Buscar todos los RUT de los clientes que están mal ingresados porque no corresponden al formato del RUT:
+Para que una vista esté definida, la consulta debe ser resoluble al momento de crear la vista, ergo todos los nombres, funciones y schemas usados en la vista deben existir.
-<code mysql>
-  SELECT * from mae_clientes
+**Eliminar una vista**
-  WHERE cli_rut NOT REGEXP '[1-9][0-9]+\-[0-9kK]'
+<code sql>
+DROP VIEW vista;
 </code>
-Explicación del filtro:
-|  REGEXP   |   Significado   |
+==== Funciones ====
-| ''[1-9]'' |  Un caracter del grupo dígito del 1 al 9 (para iniciar un número)   |
-| ''[0-9]'' |  ...seguido de un caracter dígito    |
-| ''+''     |  ...al menos una vez   |
-| ''\-''    |  ...seguido de un caracter guión  |
-| ''[0-9kK]''  |  ...seguido de un caracter del grupo dígito \\ o del grupo de las letras k y K  |
-|| https://www.regular-expressions.info/tutorial.html  ||
+:FIXME: TBD
-A veces tener comillas simples o dobles como parte de un campo puede causar errores en bases de datos de MySQL no sanitizadas. Busquemos todos los clientes que tengan comillas dobles en la dirección (cosas como O'Brian, O'Higgins pero mal escritas: ''O"Brian'', ''O"Higgins'', como sucede en la base de datos de Rodeo) y veamos cómo se ven si les cambiamos las comillas simples por comillas tipográficas Unicode ("''%%’%%''"):
+==== Tipos de Datos ====
-<code mysql>
-  SELECT cli_id, cli_rut, cli_direccion as nombre_antes, replace(cli_direccion, '"', '’') as nombre_despues FROM mae_clientes
-  WHERE cli_direccion LIKE '%"%'
-</code>
-==== Agregar Datos====
+Los siguientes tipos de datos son globales en SQL:
-Las instrucciones **INSERT** y **UPDATE** agregan información.
+{| class="mwtable inline zebra"
+|-
+! INTEGER || Un número entero, con signo, de hasta 32 o 64 bits. || ''12345''
+|-
+! REAL  || Un número flotante en rango de IEEE754 ''double'' || ''186354.01921''
+|-
+! CHAR(n)  || Un array de exactamente //n// bytes.  || ''CHAR(12) : "abcdefghij  "''
+|-
+! TEXT  || Un blob de tamaño variable, \\ parseado como texto **en el collation de la tabla**. || ''"Hola Mundo"''
+|-
+! BLOB  || Un blob tratado como un binario. ||
+|-
+|}
-<code mysql>
+Cada motor agrega sus tipos de datos propios. Algunos tipos que son bastante comunes son ''VARCHAR(n)'' (un array de tamaño dinámico de caracteres parseado como texto); ''DATETIME'', ''DATE'' y ''TIME'' (estampas de fecha y hora); ''DECIMAL(m,d)'' (un número de punto flotante de //d// decimales) y ''ENUM'' (variable que toma valores dentro de una lista de valores discretos).
-  INSERT INTO tablas VALUES
-  (fila1),(fila2), ...
-  ;
-  UPDATE tablas SET cambios...
+==== UDL ====
+:FIXME: TBD
+===== CRUD =====
+==== Mostrar Registros ====
+La instrucción **SELECT** es para buscar información.
+<code sql>
+  SELECT campos
+  FROM tablas
   WHERE (condicion1 [AND/OR] condicion2 [AND/OR]...)
+  [ORDER BY orden [ASC/DESC] ]
   ;
 </code>
 En la consulta:
-  * ''tablas'' es la lista de tablas que se desea modificar. Se coloca más de una tabla cuando el WHERE depende de un JOIN por ejemplo.
+  * ''campos'' puede ser una lista de uno o más campos, conteos o fórmulas. Asterisco ("''*''") se usa para indicar mostrar todos los campos de la tupla de tablas nombradas en el ''FROM''.
-  * ''cambios'' es la lista de cambios que se desea aplicar, de la forma ''campo = valor, ...'' o ''campo = formula(...), ... ''. Para cambiar campos de más de una tabla se usa el nombre completo del campo por ejemplo ''SET tabla1.campoA = valor, tabla2.campoB = valor, ...''.
+  * ''tablas'' puede ser una lista de una o más tablas o uniones de tablas (JOIN, UNION, etc), con o sin su fuente de base de datos (por ejemplo, ''crux.mae_usuarios'' o simplemente ''mae_usuarios'').
-  * ''condicion1, condicion2'' es una lista opcional de condiciones en el lenguaje lógico de MySQL. Una condición puede ser comparar el valor de un campo, por ejemplo "''WHERE mae_nombre_empresa LIKE '%Lily%' ''".
+  * ''condicion1, condicion2'' es una lista opcional de condiciones en el lenguaje lógico de SQL. Una condición puede ser comparar el valor de un campo, por ejemplo "''WHERE mae_nombre_empresa LIKE '%Lily%' ''".
+  * ''orden'' es una lista de campos o de fórmulas, del más prioritario al menos prioritario, para ordenar los resultados. Por ejemplo "''ORDER BY cantidad_vendida, WEEKDAY(fecha_venta)''" ordenaría una lista de ventas de la que vendió más a menos y luego por el día de semana de la venta.
-**Ejemplos de búsquedas en bases de datos de Helpcom**
+==== Escribir Registros ====
-Actualizar el nombre de una comuna mal escrita:
+Las instrucciones **INSERT** y **UPDATE** agregan o actualizan información.
-<code mysql>
-  UPDATE mae_clientes
-  SET cli_comuna = 'LAUTARO'
-  WHERE cli_comuna = 'LAITARO'
-</code>
-Corregir todos los RUT con "-k" para que tengan mayúsculas:
 <code mysql>
-  UPDATE mae_clientes
+  INSERT INTO tablas VALUES
-  SET cli_rut = replace(cli_rut, '-k', '-K')
+  (fila1),(fila2), ...
-</code>
+  ;
-Cambiar las comillas normales por comillas tipográficas, siguiendo el ejemplo del SELECT en la sección anterior:
+  UPDATE tablas SET cambios...
+  WHERE (condicion1 [AND/OR] condicion2 [AND/OR]...)
+  ;
-<code mysql>
-  UPDATE mae_clientes
-  SET cli_direccion= replace(cli_direccion, '"', '’')
-  WHERE cli_direccion LIKE '%"%';
 </code>
-Marcar un grupo de folios de nota de crédito DTE como no impresos:
+La sintaxis en general es la misma entre MySQL, PostgreSQL y SQLite.
-<code mysql>
-  UPDATE dte_folios
-  SET fol_archivo_generado = 'NO'
-  WHERE tdo_id = 61 AND fol_numero IN (101, 102, 104, 107, 109)
-</code>
+MySQL y SQLite permiten ''INSERT IGNORE'', que inserta una fila si se puede hacer sin errores y termina sin hacer nada y sin errores si no se puede.
 ==== Eliminar Datos====
@@ Línea 211: / Línea 242: @@
 ====Motores====
-  * MySQL Server Community - https://dev.mysql.com/downloads/mysql/ . Usado por productos como [[:Crux]].
+  * MariaDB SQL Server - https://downloads.mariadb.org/ - Usado para [[:Crux]].
+  * MySQL Server Community - https://dev.mysql.com/downloads/mysql/ .
   * PostgreSQL Server - http://postgresql.org/ . Usado por productos como [[:Orión]].
   * SQLite - https://www.sqlite.org/ - usado por productos como [[:Draco]].
@@ Línea 219: / Línea 251: @@
 ====Gestores====
-  * DBeaver Community - https://dbeaver.io/download/ .
+  * [[:f1:Dbeaver|DBeaver Community]] - https://dbeaver.io/download/ .
+  * MySQL Query Browser (desahuciado en 2019).
+  * SQYog Community.
+  * MySQL Workbench.
+  * ''mysql'' - línea de comandos para un servicio de MySQL o MariaDB.
+  * ''psql'' - línea de comandos para un servicio de PostgreSQL.
+  * ''sqlite3'' - línea de comandos para procesar un archivo de SQLite.
+  * SQLitebrowser
-DBeaver es un GUI (interface gráfica) para la gestión de base de datos en varios motores. Es abierto y multiplataforma basado en Java FX y está disponible en los recursos compartidos de Helpcom. Ofrecen descargas tanto para Linux como Windows.
+=====Más Información=====
-Para la instalación ver [[Dbeaver]].
+Páginas que pueden tener información relacionada:
-  * MySQL Query Browser
+{{topic>sql}}
-  * MySQL Workbench
-Query Browser es un GUI (interface gráfica) para la consulta de base de datos MySQL Server 4.1 a 5.5. MySQL Workbench es el sucesor de Query Browser y soporta modelos de bases de datos de MySQL 5.1 a 8.x, y está disponible en Linux en el gestor de paquetes con un nombre como ''mysql-tools-workbench'' o similar.
+{{topic>mysql}}
-Para la instalación ver [[Query Browser]].
-  * ''mysql''
-''mysql'' es la línea de comandos de MySQL. Viene como opción en MySQL Community Server en Windows y en Linux viene disponible como un paquete con un nombre como ''mysql-client'' o similar.
-  * ''psql''
-''psql'' es la línea de comandos de PostgreSQL. En Linux viene disponible como un paquete con un nombre como ''postgresql-client'' o similar.
-  * SQLitebrowser
-SQLitebrowser es una interface gráfica para crear, gestionar y respaldar archivos de base de datos en SQLite2 (no en SQLite3). En Linux está disponible con el nombre de paquete ''sqlitebrowser'' o similar.
+{{topic>postgresql}}