SQL Notes
Database
A software application used to manage our DB is called DBMS (Database Management System).
Database is a collection of data in a format that can be easily accessed (Digital).
Types of Databases
Non-relational (NoSQL): data not stored in tables
Relational: data stored in tables
We use SQL to work with relational DBMS
What is SQL?
SQL = Structured Query Language
SQL is a programming language used to interact with relational databases.
It is used to perform CRUD operations: Create, Read, Update, Delete
Database Structure
Database → Table 1 → Data, Table 2 → Data
Creating our First Database
CREATE DATABASE db_name;
DROP DATABASE db_name;
Creating our First Table
USE db_name;
CREATE TABLE table_name ( column_name1 datatype constraint, column_name2 datatype
constraint );
SQL Datatypes
Define the type of values stored in a column
TINYINT (-128 to 127), TINYINT UNSIGNED (0 to 255)
Types of SQL Commands
DDL: create, alter, rename, truncate, drop
DML: select, insert, update, delete
DCL: grant, revoke
DQL: select
TCL: start transaction, commit, rollback
Database Related Queries
CREATE DATABASE db_name;
DROP DATABASE db_name;
SHOW DATABASES;
SHOW TABLES;
CREATE DATABASE IF NOT EXISTS db_name;
DROP DATABASE IF EXISTS db_name;
Table Related Queries
CREATE TABLE table_name ( column_name1 datatype, column_name2 datatype );
SELECT * FROM table_name;
INSERT INTO table_name (colname1, colname2) VALUES (col1_v1, col2_v1), (col1_v2, col2_v2);
UPDATE table_name SET col1 = val1 WHERE condition;
DELETE FROM table_name WHERE condition;
Keys & Constraints
Primary Key: uniquely identifies each row (only 1 PK, NOT NULL)
Foreign Key: refers to primary key in another table (can have duplicates & NULLs)
NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, DEFAULT, CHECK
SELECT, WHERE, ORDER BY, LIMIT
SELECT col1, col2 FROM table_name;
SELECT * FROM table_name;
WHERE condition
ORDER BY col_name ASC;
LIMIT number;
Operators
Arithmetic: +, -, *, /, %
Comparison: =, !=, >, >=, <, <=
Logical: AND, OR, NOT, IN, BETWEEN, LIKE, ANY
Bitwise: &, |
Aggregate Functions
COUNT(), MAX(), MIN(), SUM(), AVG()
GROUP BY & HAVING
GROUP BY groups rows having same values
HAVING applies condition after grouping
ALTER TABLE
ADD COLUMN, DROP COLUMN, RENAME TABLE, MODIFY COLUMN, CHANGE COLUMN
TRUNCATE
TRUNCATE TABLE table_name;
Joins
INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN, SELF JOIN
UNION
Combines results of multiple SELECT statements (removes duplicates)
Subqueries
Nested SELECT statements inside another query
Views
Virtual table based on a query, always shows up-to-date data