Java, NoSQL, SQL, REST API and other scary words

Simple SQL commands

Term Description Syntax
SELECT The SELECT statement is used to select data from a database. The result is stored in a result table, called the result-set. SELECT column_name,column_name
FROM table_name;
DISTINCT In a table, a column may contain many duplicate values; and sometimes you only want to list the different (distinct) values.
The DISTINCT keyword can be used to return only distinct (different) values.
SELECT DISTINCT column_name,column_name
FROM table_name;
WHERE The WHERE clause is used to extract only those records that fulfill a specified criterion. SELECT column_name,column_name
FROM table_name
WHERE column_name operator value;
AND/OR
ORDER BY The ORDER BY keyword is used to sort the result-set by one or more columns.
The ORDER BY keyword sorts the records in ascending order by default. To sort the records in a descending order, you can use the DESC keyword.
INSERT INTO he INSERT INTO statement is used to insert new records in a table. INSERT INTO table_name
VALUES (value1,value2,value3,…);INSERT INTO table_name (column1,column2,column3,…)
VALUES (value1,value2,value3,…);
UPDATE The UPDATE statement is used to update existing records in a table.
DELETE
SELECT TOP The SELECT TOP clause is used to specify the number of records to return.
LIKE The LIKE operator is used to search for a specified pattern in a column.
IN The IN operator allows you to specify multiple values in a WHERE clause.
BETWEEN The BETWEEN operator is used to select values within a range.
Joins SQL joins are used to combine rows from two or more tables:
INNER JOIN: Returns all rows when there is at least one match in BOTH tables
LEFT JOIN: Return all rows from the left table, and the matched rows from the right table
RIGHT JOIN: Return all rows from the right table, and the matched rows from the left table
FULL JOIN: Return all rows when there is a match in ONE of the tables
INNER JOIN The INNER JOIN keyword selects all rows from both tables as long as there is a match between the columns in both tables. INNER JOIN is the same as JOIN.
Общие значения
LEFT JOIN The LEFT JOIN keyword returns all rows from the left table (table1), with the matching rows in the right table (table2). The result is NULL in the right side when there is no match. SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name=table2.column_name;
RIGHT JOIN The RIGHT JOIN keyword returns all rows from the right table (table2), with the matching rows in the left table (table1). The result is NULL in the left side when there is no match. SELECT column_name(s)
FROM table1
RIGHT OUTER JOIN table2
ON table1.column_name=table2.column_name;
FULL JOIN The FULL OUTER JOIN keyword returns all rows from the left table (table1) and from the right table (table2).
The FULL OUTER JOIN keyword combines the result of both LEFT and RIGHT joins.
SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2
ON table1.column_name=table2.column_name;
UNION The SQL UNION operator combines the result of two or more SELECT statements.
The UNION operator selects only distinct values by default. To allow duplicate values, use the ALL keyword with UNION.
SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;
———————————————————
SELECT column_name(s) FROM table1
UNION ALL
SELECT column_name(s) FROM table2;
SELECT INTO The SELECT INTO statement copies data from one table and inserts it into a new table. SELECT *
INTO newtable [IN externaldb]
FROM table1;
INSERT INTO SELECT With SQL, you can copy information from one table into another.
The INSERT INTO SELECT statement copies data from one table and inserts it into an existing table
INSERT INTO table2
SELECT * FROM table1;
CREATE DATABASE The CREATE DATABASE statement is used to create a database.
CREATE TABLE NOT NULL – Indicates that a column cannot store NULL value
UNIQUE – Ensures that each row for a column must have a unique value
PRIMARY KEY – A combination of a NOT NULL and UNIQUE. Ensures that a column (or combination of two or more columns) have an unique identity which helps to find a particular record in a table more easily and quickly
FOREIGN KEY – Ensure the referential integrity of the data in one table to match values in another table
CHECK – Ensures that the value in a column meets a specific condition
DEFAULT – Specifies a default value when specified none for this column
INCREMENT TABLE
DROP INDEX Indexes, tables, and databases can easily be deleted/removed with the DROP statement. DROP INDEX index_name ON table_name
DROP INDEX table_name.index_name
DROP INDEX index_name
ALTER TABLE table_name DROP INDEX index_name
DROP TABLE table_name
DROP DATABASE database_name
TRUNCATE TABLE table_name
ALTER TABLE The ALTER TABLE statement is used to add, delete, or modify columns in an existing table. ALTER TABLE table_name
ADD column_name datatype
Views In SQL, a view is a virtual table based on the result-set of an SQL statement.
A view contains rows and columns, just like a real table. The fields in a view are fields from one or more real tables in the database.
You can add SQL functions, WHERE, and JOIN statements to a view and present the data as if the data were coming from one single table.
CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition
Date Data Types MySQL comes with the following data types for storing a date or a date/time value in the database:DATE – format YYYY-MM-DD
DATETIME – format: YYYY-MM-DD HH:MM:SS
TIMESTAMP – format: YYYY-MM-DD HH:MM:SS
YEAR – format YYYY or YY
SQL Server comes with the following data types for storing a date or a date/time value in the database:DATE – format YYYY-MM-DD
DATETIME – format: YYYY-MM-DD HH:MM:SS
SMALLDATETIME – format: YYYY-MM-DD HH:MM:SS
TIMESTAMP – format: a unique number
SQL Functions SQL Aggregate Functions
SQL aggregate functions return a single value, calculated from values in a column.Useful aggregate functions:AVG() – Returns the average value
COUNT() – Returns the number of rows
FIRST() – Returns the first value
LAST() – Returns the last value
MAX() – Returns the largest value
MIN() – Returns the smallest value
SUM() – Returns the sum
SQL Scalar functions
SQL scalar functions return a single value, based on the input value.Useful scalar functions:UCASE() – Converts a field to upper case
LCASE() – Converts a field to lower case
MID() – Extract characters from a text field
LEN() – Returns the length of a text field
ROUND() – Rounds a numeric field to the number of decimals specified
NOW() – Returns the current system date and time
FORMAT() – Formats how a field is to be displayed

Examples

Term Example
SELECT SELECT CustomerName,City FROM Customers;
SELECT LastName,FirstName,Address FROM Persons
WHERE Address IS NULL
DISTINCT
WHERE
AND/OR SELECT * FROM Customers
WHERE Country=’Germany’
AND City=’Berlin’;
ORDER BY SELECT * FROM Customers
ORDER BY Country;
INSERT INTO INSERT INTO Customers (CustomerName, Country)
SELECT SupplierName, Country FROM Suppliers;
UPDATE UPDATE Customers
SET ContactName=’Alfred Schmidt’, City=’Hamburg’
WHERE CustomerName=’Alfreds Futterkiste’;
DELETE DELETE FROM Customers
WHERE CustomerName=’Alfreds Futterkiste’ AND ContactName=’Maria Anders’;
SELECT TOP SELECT *
FROM Persons
LIMIT 5;
LIKE SELECT * FROM Customers
WHERE City LIKE ‘s%’;SELECT * FROM Customers
WHERE City LIKE ‘_erlin’;
IN The following SQL statement selects all customers with a City of “Paris” or “London”:
SELECT * FROM Customers
WHERE City IN (‘Paris’,’London’);
BETWEEN SELECT * FROM Products
WHERE Price BETWEEN 10 AND 20;
Joins
INNER JOIN SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
INNER JOIN Orders
ON Customers.CustomerID=Orders.CustomerID
ORDER BY Customers.CustomerName;
LEFT JOIN SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
LEFT JOIN Orders
ON Customers.CustomerID=Orders.CustomerID
ORDER BY Customers.CustomerName;
RIGHT JOIN SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
RIGHT JOIN Orders
ON Customers.CustomerID=Orders.CustomerID
ORDER BY Customers.CustomerName;
FULL JOIN
UNION SELECT City FROM Customers
UNION
SELECT City FROM Suppliers
ORDER BY City;
SELECT INTO SELECT *
INTO CustomersBackup2013
FROM Customers;
INSERT INTO SELECT INSERT INTO Customers (CustomerName, Country)
SELECT SupplierName, Country FROM Suppliers
WHERE Country=’Germany’;
CREATE DATABASE CREATE DATABASE my_db;
CREATE TABLE CREATE TABLE Persons
(
PersonID int,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255)
);
DROP INDEX
ALTER TABLE ALTER TABLE Persons
DROP COLUMN DateOfBirth
Views CREATE VIEW [Current Product List] AS
SELECT ProductID,ProductName
FROM Products
WHERE Discontinued=No
Date Data Types SELECT * FROM Orders WHERE OrderDate=’2008-11-11′
SQL Functions
Advertisements
Standard

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s