MANUAL SQL

CREATE DATABASE
CREATE DATABASE databaseName;

CREATE TABLE
CREATE TABLE tableName(

field1 VARCHAR(50)

campo2 VARCHAR (50)

INT code

);

Create table only if it does not exist

CREATE TABLE IF NOT EXISTS table_name(fields);

VIEW TABLE STRUCTURE

Show the names and information of each of the fields in the table
DESC tableName

INSERT INTO

The INSERT INTO statement is used to insert new rows into a table.

Insert data specifying fields and values

INSERT INTO tableName (field1, field2, code) VALUES ('value1', 'value2', 0001);

Insert data into the entire table consecutively, it is not necessary to specify columns.
INSERT INTO tableName VALUES('value1','value2',0001);

IMPORT

My Sql

Import a comma-delimited csv file into an existing database with the same amount of
columns, the file must be in the folder 'mysql/databaseName'.
LOAD DATA INFILE 'import.csv' INTO TABLE tableName FIELDS TERMINATED BY ','
ENCLOSED BY '"' LINES TERMINATED BY '\r\n';

PostgreSql

Import a comma-delimited csv file into an existing database with the same amount of
columns, first we change the file extension to '.txt' the file must be in the folder
PostgreSQL e9.3 data
COPY tableName FROM 'import.txt' USING DELIMITERS ','

MODIFY

The SQL statement ALTER is used to add, delete, or modify columns of a table.

Table
Rename table
. MySql
ALTER TABLE tableName RENAME newName;

. PostgreSql
 ALTER TABLE tableName RENAME TO newName;

Add column
. MySql
ALTER TABLE tableName ADD COLUMN (newField VARCHAR (30));

. PostgreSql, MySql
ALTER TABLE tableName ADD newField VARCHAR(30);

Column
Modify name, data_type, constraint of a column (MySql)
ALTER TABLEtableName
CHANGE columnName newColumnName varchar(10)NOTNULL

Modify type, data and constraint of a column without renaming it (MySql)

ALTER TABLE tableName MODIFY columnName BIGINT NOT NULL;

DELETE, DROP, TRUNCATE

Delete

Delete a series of rows from the table. We can use a WHERE clause to limit the rows to be deleted.
meet a condition.

o Delete table data

DELETE FROM tableName

o Delete data table, conditioned

DELETE FROM tableName WHERE tableName = value

Drop

The DROP statement is used to permanently delete an index, table, or database.

Delete entire table
DROP TABLE tableName;

Delete database
DROP DATABASE tableName;

Remove column
ALTER TABLE tableName DROP COLUMN columnName;
Truncate
Delete all rows from the table without deleting the table itself. Also reset the auto-increment counters to 0.
It does not delete the table as such, the so-called structure, so later it can start making insertions.
TRUNCATE TABLE tableName

UPDATE

The UPDATE statement is used to modify values in a table.

Update a record
UPDATE tableName SET fieldName=newValue WHERE condition;
 Update multiple records at once
UPDATE tableName SET column1 = value1, column2 = value2 WHERE condition

QUERIES (SELECT)

The SELECT statement allows you to query information stored in a database.

Show all records from the table

SELECT* FROM tableName;

Show non-repeating values

SELECT DISTINCT field FROM tableName

Order by

ORDER BY is used to sort the results of a query based on the value of the specified column.
it can be in ascending | descending order
SELECT columnName(s) FROM tableName ORDER BY columnName(s) ASC|DESC

Number of rows to display

o SQL Server
SELECT TOP numberOfRows PERCENT columnName(s) FROM tableName

o MySql
SELECT columnName(s) FROM tableName LIMIT numberOfRows

o Oracle
SELECT columnName(s) FROM tableName WHERE ROWNUM <= numberOfRows

Like

The LIKE operator is used in the WHERE clause to search for a pattern.
o Return names that start with AN
SELECT * FROM persons WHERE name LIKE 'AN%'

o Returns names that contain AN

SELECT * FROM persons WHERE name LIKE '%AN%'

o Return names that start with A|N

SELECT * FROM people WHERE last_name1 LIKE '[AN]%'

In

The IN operator allows selecting multiple values in a WHERE clause

Return names that are (juan|pedro|…)
SELECT name FROM table WHERE name IN ('juan', 'pedro', ...)

Between

The BETWEEN operator is used in the WHERE clause to select values within a range of data.

o Returns values within a range

 SELECT * FROM people WHERE surname BETWEEN 'CRUZ' AND 'HUERTAS'

o Returns values outside of a range

SELECT * FROM persons WHERE last_name NOT BETWEEN 'CRUZ' AND 'HUERTAS'

Alias

An alias is another way to refer to a table or a column, and it is used to simplify SQL statements.
when the names of tables or columns are long or complicated.
o Alias for a table
SELECT column FROM tableName AS aliasTable

o Alias for a column

SELECT columnname AS columnalias FROM table

Join

The SQL JOIN statement allows querying data from 2 or more tables, which will be related to each other.
they in some way, through one of their columns.
o Simple union

The SQL UNION statement is used to accumulate the results of two SELECT statements, the
tables must have the same number of columns, with the same data type and in the same order.
SELECT column1, column2 FROM table1
UNION
SELECT column1, column2 FROM table2

o Inner join

The INNER JOIN statement combines each row from one table with each row from the other table, selecting
those rows that meet a certain condition.
SELECT * FROM table1 INNER JOIN table2 WHERE table1.column1 =
table2.column1

o Left join

The LEFT JOIN statement combines the values of the first table with the values of the second table.
It will always return the rows of the first table, even if they do not meet the condition.
SELECT * FROM table1 LEFT JOIN table2 WHERE table1.column1 =
table2.column1

o Right join

The RIGHT JOIN statement combines the values of the second table with the values of the first table.
It will always return the rows from the second table, even if they do not meet the condition.
SELECT * FROM table1 RIGHT JOIN table2 WHERE table1.column1 =
table2.column1

o Full join
 The FULL JOIN statement combines the values of the first table with the values of the second table.
It will always return the rows from both tables, even if they do not meet the condition.

SELECT * FROM table1 FULL JOIN table2 WHERE table1.column1 =

table2.column1

Select into (Backup)

The SQL SELECT INTO statement is used to select data from one table and copy it into another different table.
it can be in the same or in another database. It is used to make a backup.
data.

o Save the selected data in a new table

SELECT column1, column2 INTO newtable FROM currenttable

o Save the selected data in a new database

SELECT * INTO newtable [IN newdatabase] FROM currenttable

o Using where
SELECT * INTO personasBackup FROM personas WHERE name = 'ANTONIO'

o Using join
SELECT persons.first_name, persons.last_name, departments.department INTO
peopleComputer FROM people INNER JOIN departments ON people.dep
INFORMATICS

o For My SQL
INSERT INTO tbl_temp2 (fld_id)

SELECT tbl_temp1.fld_order_id

FROM tbl_temp1 WHERE tbl_temp1.fld_order_id > 100;

Aggregation functions

Count
Return the number of selected data in a field
oCount

Returns the count of non-NULL values in the records received by a

SELECT command.
SELECT COUNT(column) FROM table;

oCount(*)

Returns a counter of the number of records returned, indicating whether they contain NULL values or not.

SELECT COUNT(*) FROM table;

Example
SELECTstateCOUNT(status)
FROM members
 WHERE status = 'Active'
GROUP BY state
ORDER BYCOUNT(status) DESC

Sum
Returns the sum of the selected data in a field
SELECT SUM(column) FROM table;

Max
Return the minimum value contained in the selected data.
SELECT MAX(column) FROM table;

Min
Returns the maximum value contained in the selected data
SELECT MIN(column) FROM table;

Avg
Returns the average of the selected data in a field.
SELECT AVG(column) FROM table;

Standard deviation (stddev)

Returns the standard deviation of expr
SELECT STD(expr) FROM tableName

SELECT STDDEV(expr) FROM tableName

SELECT STDDEV_POP(expr) FROM tableName

Standard deviationsample standard deviation (stddev_samp)

Returns the sample standard deviation of expr
SELECT STDDEV_SAMP(expr) FROM tableName

Variance (var_pop)
Return the standard variance of expr. Consider the records as the complete population, not as a
sample, so it has the number of records as the denominator
SELECT VAR_POP(expr) FROM tableName

SELECT VARIANCE (expr) FROM tableName

Sample variance (var_samp)

Returns the sample variance of expr
SELECT VAR_SAMP(expr) FROM tableName

CONSTRAINTS

CONSTRAINTS are restrictions used to limit the type of data that a column can receive.
A table can be defined when we create the table (CREATE TABLE) or later with the ALTER TABLE statement.

Not null
The NOT NULL constraint is used to specify that this column must always have a value.
it can be empty.

o When creating the table

CREATE TABLE tableName ( column1, column2 NOT NULL,…);

o From an existing table (PostgreSQL)

ALTER TABLE tableName ALTER columnName SET|DROP NOT NULL;

Modify name, data_type, constraint of a column (MySql)

ALTER TABLE tableName
CHANGE columnName newColumnName nameCcolumn NOTNULL

Unique

The UNIQUE constraint will validate that the values in each column do not repeat, there can be several.
UNIQUE constraints on different columns of a table.

Add unique when creating a table

CREATE TABLE example
(
identifier int NOT NULL
name varchar(255) NOT NULL,
UNIQUE (identifier);

Add unique to an existing field

ALTER TABLE example ADD UNIQUE (column);

Primary key

The column defined as the primary key (PRIMARY KEY) must be UNIQUE (unique value) and NOT NULL (not null)
it can contain null values) and each table can only have one primary key.

Add Primary Key with one or more columns when creating the table
. MySql, PostgreSql
CREATE TABLE tableName(field1 int, field2 int,
CONSTRAINT nombrePK PRIMARY KEY (field1)

. Oracle, SqlServer
CREATE TABLE personas
(
identifier int PRIMARY KEY,
PRIMARY KEY (identifier)

Add Primary Key to an existing column

ALTER TABLE example ADD CONSTRAINT pk_unique PRIMARY KEY (unique)

Foreign key

The foreign key, or FOREIGN KEY, is one or several columns that are used to indicate what the primary key is.
from another table, in this way the foreign key can only have values that already exist in the primary key of
the other table will only be created if its reference already exists as a primary key.
Add FOREIGN KEY when creating a table
 . MySql
CREATE TABLE tableName (field1, field2,
FOREIGN KEY (field1) REFERENCES tableName (columnName)

. PostgreSql
CREATE TABLE tableName (field1, field2, CONSTRAINT nameFK FOREIGN
KEY(campo1) REFERENCES tableName(columnName))
Add FOREIGN KEY to an existing table

. Add Foreign Key

ALTER TABLE ADD FOREIGN KEY (dep) REFERENCES departments(dep)

. Add multiple Foreign Key for several columns

ALTER TABLE ADD CONSTRAINT fkpersonas FOREIGN KEY (dep) REFERENCES
departments(dep)

o Delete Foreign Key

. MySql
ALTER TABLE persons DROP FOREIGN KEY dep

. Oracle, SQL Server

ALTER TABLE persons DROP CONSTRAINT dep

Check

The CHECK constraint is used to limit the range of values that a column can have.
Add check when creating table

. MySql
CREATE TABLE departments ( dep int NOT NULL, department
varchar(255), CHECK (dep>0) )

. Oracle, SqlServer
CREATE TABLE departments ( dep int NOT NULL CHECK (dep>0)
department varchar(255), )

Add check to existing field

. My Sql
ALTER TABLE departments ADD CHECK (dep>0)

. Oracle, Sql Server

ALTER TABLE departments ADD CONSTRAINT ckdepart CHECK (dep>0)

Default

The DEFAULT constraint is used to set a default value for a column.

to add

. Mysql
ALTER TABLE tableName ALTER column DEFAULT 'value'

. Oracle, Sql Server, AccessS

ALTER TABLE orders ALTER COLUMN date SET DEFAULT 'value'
 oDelete

. Mysql
ALTER TABLE table_name ALTER column_name DROP DEFAULT

. Oracle, Sql Server, Access

ALTER TABLE table_name ALTER COLUMN column_name DROP DEFAULT

AUTO INCREMENT

AUTO INCREMENT allows generating a unique number when inserting a new record in the table.

It is used to have a primary key for a table by automatically generating a sequential number.
When creating a table

CREATE TABLE tableName (field int NOT NULL AUTO_INCREMENT)

Existing table without primary key

ALTER TABLE tableName ADD columnName INTNOTNULL AUTO_INCREMENT ,
ADD PRIMARY KEY ( columnName );

Add auto_increment to primary key

ALTER TABLE tableName

CHANGE columnName columnName INTNOTNULL AUTO_INCREMENT;

Remove auto_increment from primary key

ALTER TABLE tableName CHANGE columnName columnName INTNOTNULL;

INDEX

An index is used to quickly search for data, without having to go through the entire table sequentially in search.
some specific row.

Simple index
CREATE INDEX indexname ON tablename (columnname)

Duplicate index
CREATE UNIQUE INDEX indexname ON tablename (columnname)

Index on various columns

CREATE INDEX indexname ON tablename (columnname, columnname,...)

UNDO CHANGES (ROLLBACK)

START TRANSACTION;

SQL_code_to_undo;

ROLLBACK;

SAVEPOINT identifier
ROLLBACK TO SAVEPOINT identifier

USERS

Create a user
CREATE USER 'name'@localhost IDENTIFIED BY 'password'

Assign permissions
GRANT SELECT, INSERT, CREATE, UPDATE, DROP
ON database(s) table(s)
TO 'user'@localhost;

Delete user

DATA TYPE

TYPE VALUE
INT NUMERIC -2147483648 to 2147483647
INT(3) integer with 3 digits
FLOAT NUMERIC FLOAT(5,2) five integer digits and two
decimals
BOOLEAN NUMERIC TRUE, FALSE
DATE DATE YYYY-MM-DD
DATETIME DATE AND TIME YYYY-MM-DD HH:MM:SS
TIME HOUR HH:MM:SS
YEAR YEAR YYYY
CHAR STRING CHAIN
VARCHAR TEXT CHAIN
TEXT TEXT CHAIN

OPERATORS

AND Condition1 AND condition2

OR Condition1 OR condition2
=
>
>=
<
<=
<>, !=