SELECT
select a column from a table
* for all columns
DISTINCT
Only unique values
ORDER BY
Put things in order (ascending, descending)
WHERE
Search with a condition/filter, like an IF
INSERT
Adds new row to table
- INSERT INTO table (col1, col2, …)
- VALUES (val1, val2, …)
UPDATE
Update data of existing rows in table, you can use SET to change the actual value and WHERE for the condition
- Ex: Someone got married, change their last name
- UPDATE employees
- SET lastname = 'Smith'
- WHERE employeeid = 3;
DELETE
Delete a row FROM table WHERE condition
GROUP BY
rows that have the same values into summary rows, use with something like COUNT
JOIN
Combining two tables
LEFT JOIN
All rows from Left table are included in set, fills entries without data with NULL
RIGHT JOIN
All rows in Right table are included in set, fills entries without data with NULL
INNER JOIN
All rows in only both tables are included in set
OUTER/FULL JOIN
Combines all tables, kind of creates a mess
NULL
No value
SELECT TOP
Shows the top n results, same as LIMIT n
List of (Statistical) Functions
- SUM
- AVG
- MAX
- MIN
- COUNT
What is an Index?
Speed up getting data from tables
Assigning an index number to a set of values (column) in a table
- Ex:
- CREATE [UNIQUE] INDEX index_name
- ON table (column)
Transactions
Group multiple SQL statements into a single logical unit of work that either succeeds or fails as a whole.
- BEGIN TRANSACTION;
- SQL queries
- COMMIT
Views
Virtual tables stored by a SELECT statement, stored queries
- CREATE VIEW
Stored Procedures
Stored procedures are a set of SQL statements that are stored in the database and can be called by applications,
like a script/transactions?
Normalization
Normalization is the process of organizing data in a database so that it is structured according to a set of rules that eliminate redundancy and improve data consistency.
LIKE
Used with WHERE to find patterns in a query with a wildcard
Wildcards
% Represents zero or more characters
_ Represents a single character
Like Regex
IN
Used to specify values in a WHERE clause, a lot like OR
BETWEEN
query in a range of values
Aliases
Give a table/column a temporary name, uses AS
UNION
returns distinct values SELECTed from both tables
HAVING
WHERE but for aggregate functions (COUNT)
EXISTS
test for the existence of a record in a subquery
ANY
Returns boolean if any one record/value matches the subquery
ALL
Returns boolean if all records/values matches the subquery
SELECT INTO
copies data from one table into a new table
INSERT INTO SELECT
copies data from one table and inserts it into another table
CASE
Uses WHEN - THEN conditions and ELSE as a default
Null functions
IFNULL(), ISNULL(), COALESCE(), and NVL()
COALESCE
returns the first non-null value in a list
NVL
Oracle version of ISNULL
Comment
-- single line
/* */ multiline
Operators
All the same as Python basically
CREATE DATABASE, DROP DATABASE
creates a new SQL database, drops SQL database
BACKUP DATABASE
create a full back up of an existing SQL database
CREATE TABLE, DROP TABLE
create new table, drops table
ALTER TABLE
add, delete, or modify columns in an existing table
Ex: ALTER TABLE table_name ADD column_name
Constraints
Used with CREATE TABLE or ALTER TABLE to specify some rules
List of constraints
- NOT NULL
- UNIQUE
- PRIMARY KEY
- FOREIGN KEY
- CHECK
- DEFAULT
- INDEX/CREATE INDEX
NOT NULL
Ensures that a column cannot have a NULL value
UNIQUE
Ensures that all values in a column are different
PRIMARY KEY
A combination of a NOT NULL and UNIQUE. Uniquely identifies each row in a table
FOREIGN KEY
Prevents actions that would destroy links between tables
CHECK
Ensures that the values in a column satisfies a specific condition
DEFAULT
Sets a default value for a column if no value is specified
CREATE INDEX/INDEX
Used to create and retrieve data from the database very quickly
AUTO INCREMENT
allows a unique number to be generated automatically when a new record is inserted into a table
Dates
YYYY-MM-DD (HH:MM:SS)
Injection
Can be used for hacking? Ex: OR 1=1
Hosting DBs
MS SQL Server, Oracle, MySQL, and MS Access
Data types
char, varchar = string, int, smallint, tinyint, bigint, BLOB (big object)