Notas SQL – Database

CREATE DATABASE testDB; Crea una base de datos llamada testDB
DROP DATABASE testDB; Elimina la base de datos testDB.
CREATE TABLE table_name (column1 datatype, dolumn2 datatype, column3 datatype); Crea una tabla con las siguientes columnas:

• 
  
• CHAR(size) Holds a fixed length string (can contain letters, numbers, and special characters). The fixed size is specified in parenthesis. Can store up to 255 characters
• VARCHAR(size) Holds a variable length string (can contain letters, numbers, and special characters). The maximum size is specified in parenthesis. Can store up to 255 characters. Note: If you put a greater value than 255 it will be converted to a TEXT type
• TINYTEXT Holds a string with a maximum length of 255 characters
• TEXT Holds a string with a maximum length of 65,535 characters
• BLOB For BLOBs (Binary Large OBjects). Holds up to 65,535 bytes of data
• MEDIUMTEXT Holds a string with a maximum length of 16,777,215 characters
• MEDIUMBLOB For BLOBs (Binary Large OBjects). Holds up to 16,777,215 bytes of data
• LONGTEXT Holds a string with a maximum length of 4,294,967,295 characters
• LONGBLOB For BLOBs (Binary Large OBjects). Holds up to 4,294,967,295 bytes of data
• ENUM(x,y,z,etc.) Let you enter a list of possible values. You can list up to 65535 values in an ENUM list. If a value is inserted that is not in the list, a blank value will be inserted. Note: The values are sorted in the order you enter them. You enter the possible values in this format: ENUM(‘X’,’Y’,’Z’)
• SET Similar to ENUM except that SET may contain up to 64 list items and can store more than one choice
• TINYINT(size) -128 to 127 normal. 0 to 255 UNSIGNED. The maximum number of digits may be specified in parenthesisSMALLINT(size) -32768 to 32767 normal. 0 to 65535 UNSIGNED. The maximum number of digits may be specified in parenthesis
• MEDIUMINT(size) -8388608 to 8388607 normal. 0 to 16777215 UNSIGNED. The maximum number of digits may be specified in parenthesisINT(size) -2147483648 to 2147483647 normal. 0 to 4294967295 UNSIGNED. The maximum number of digits may be specified in parenthesis
• BIGINT(size) -9223372036854775808 to 9223372036854775807 normal. 0 to 18446744073709551615 UNSIGNED*. The maximum number of digits may be specified in parenthesis
• FLOAT(size,d) A small number with a floating decimal point. The maximum number of digits may be specified in the size parameter. The maximum number of digits to the right of the decimal point is specified in the d parameter
• DOUBLE(size,d) A large number with a floating decimal point. The maximum number of digits may be specified in the size parameter. The maximum number of digits to the right of the decimal point is specified in the d parameter
• DECIMAL(size,d) A DOUBLE stored as a string , allowing for a fixed decimal point. The maximum number of digits may be specified in the size parameter. The maximum number of digits to the right of the decimal point is specified in the d parameter
• DATE() A date. Format: YYYY-MM-DD
• Note: The supported range is from ‘1000-01-01’ to ‘9999-12-31’
• DATETIME() *A date and time combination. Format: YYYY-MM-DD HH:MI:SS
• Note: The supported range is from ‘1000-01-01 00:00:00’ to ‘9999-12-31 23:59:59’
• TIMESTAMP() *A timestamp. TIMESTAMP values are stored as the number of seconds since the Unix epoch (‘1970-01-01 00:00:00’ UTC). Format: YYYY-MM-DD HH:MI:SS
• Note: The supported range is from ‘1970-01-01 00:00:01’ UTC to ‘2038-01-09 03:14:07’ UTC
• TIME() A time. Format: HH:MI:SS
• Note: The supported range is from ‘-838:59:59’ to ‘838:59:59’
• YEAR() A year in two-digit or four-digit format.
• Note: Values allowed in four-digit format: 1901 to 2155. Values allowed in two-digit format: 70 to 69, representing years from 1970 to 2069

DROP TABLE table_name; Elimina la tabla.
TRUNCATE TABLE table_name; Elimina la información de la tabla, pero no la tabla.
ALTER TABLE table_name ADD column_name datatype; Añade una columna a la tabla.
ALTER TABLE Customers ADD email varchar(255); ej.
ALTER TABLE Customers DROP COLUMN email; Elimina la columna email de la tabla Customers.
ALTER TABLE Customers MODIFY COLUMN email varchar(255); Modifica la columna email de la tabla customers a varchar de 255

CONSTRAINTS, (Restricciones) Son usadas para especificar reglas de fecha en la tabla. Diferencia si la creación ha sido con CREATE TABLE o con ALTER TABLE.
CREATE TABLE table_name ( column1 datatype CONSTRAINT, column2 datatype CONSTRAINT …);
NOT NULL, por defecto las columnas aceptan valores nulos. Podemos crear not null al crear o al modificar.
CREATE TABLE Persons (id int NOT NULL, Name varchar(255) NOT NULL, Age int);
ALTER TABLE Persons MODIFY Age int NOT NULL;

UNIQUE: La restricción unique asegura que los valores de todas las columnas son diferentes (CIF, TFNO, …)
CREATE TABLE Persons (id int NOT NULL, Nombre varchar(255) NOT NULL, UNIQUE (id));
ALTER TABLE Persons (id int NOT NULL, Name varchar(255) NOT NULL, Age int, CONSTRAINT UC_Person UNIQUE (id, Name));

PRIMARY KEY: La restricción primary key identifica de forma única cada registro en una tabla. Las claves primarias deben contener valores únicos(UNIQUE), y no pueden contener valores nulos (NULL). Sólo puede haber una clave primaria.
CREATE TABLE Persons (ID int NOT NULL, Name varchar(255) NOT NULL, Age int, PRIMARY KEY (ID));
FOREIGN KEY, la restricción FOREIGN KEY es una clave usada para enlazar dos tablas. La clave es un campo (o varios) en una tabla que se refiere a la clave primaria de otra tabla. La tabla que contiene la clave externa se denomina tabla secundaria y la tabla que contiene la clave «candidata» se denomina tabla de referencia o principal.
CREATE TALBE Orders (OrderID int NOT NULL, Order Number int NOT NULL, PersonID int, PRIMARY KEY (OrderID), FOREIGN KEY (PersonID) REFERENCES (Persons(PersonID));
ALTER TABLE Orders ADD FOREIGN KEY (PersonID) REFERENCES Persons(PersonID);

CHECK, la restricción check es usada para limitar el rango de valores que se pueden colocar en una columna. La definición en una sola columna, sólo se permiten ciertos valores. Si se define en una tabla, puede limitar los valores en ciertas columnas basándose en valores en otras columnas en la fila.
CREATE TABLE Persons (ID int NOT NULL, Name varchar(255) NOT NULL, Age int, CHECK (Age>=18));
ALTER TABLE Persons ADD CHECK (Age>=18);
ALTER TABLE Persons ADD CONSTRAINT CHK_PersonAge CHECK (Age>=18);
ALTER TABLE Persons DROP CHECK CHK_PersonAge;
DEFAULT: La restricción «por defecto» es usada para dar un valor por defecto. El valor por defecto será dado si no se introduce ningún dato.
CREATE TABLE Person (ID int NOT NULL, Name varchar(255) NOT NULL, Age int, City varchar(255) DEFAULT ‘Bilbao’);
La restricción por defecto es usada en ocasiones para insertar valores del sistema, como la fecha.
CREATE TABLE Orders ( … OrderDate date DEFAULT GETDATE());
ALTER TABLE Persons ALTER City SET DEFAULT ‘Bilbao’;
ALTER TABLE Persons ALTER City DROP DEFAULT;

INDEX Syntax.
CREATE INDEX index_name ON table_name (coluna1, columna2, …);
CREATE INDEX idx_lastname ON Persons (nombre, CIF);
ALTER TABLE Persons DROP INDEX index_name;

AUTO INCREMENT:
CREATE TABLE Person (ID int NOT NULL AUTO_INCREMENT, Nombre varchar(255) NOT NULL, Age int, PRIMARY KEY (ID));
ALTER TABLE Persons AUTO_INCREMENT=100; Para definir que los valores serán a partir de 100.

DATES; Fechas.
DATE : YYYY-MM-DD
DATETIME: YYYY-MM-DD HH:MI:SS
TIMESTAMP: YYYY-MM-DD HH:MI:SS
YEAR: YYYY o YY
SELECT * FROM Orders WHERE OrderDate=’2018-11-14′;
VIEWS Una vista es una tabla virtual basada en los resultados de una sentencia SQL.

CREATE VIEW [EH Customers] AS SELECT CustomerName, ContactName FROM Customers WHERE Country=’Euskadi’;