====== SQL Tutorial ======
My conspectus of a [[https://www.w3schools.com/sql/default.asp|w3schools SQL tutorial]].
===== SQL Home =====
...
===== SQL Intro =====
...
===== SQL Syntax =====
...
===== SQL Select =====
SELECT col1, col2, col3 FROM tab1;
SELECT DISTINCT col1, col2, col3 FROM tab1;
SELECT COUNT (DISTINCT col1) FROM tab1;
SELECT COUNT (*)
FROM (SELECT DISTINCT Country FROM Customers);
===== SQL Where =====
SELECT * FROM Customers
WHERE Country='Mexico';
* Operators:
* =
* >
* <
* >=
* <=
* <>
* !=
* BETWEEN
* LIKE
* IN
===== SQL Order By =====
SELECT * FROM tab1
ORDER BY col1 ASC | DESC;
===== SQL And =====
...
===== SQL Or =====
...
===== SQL Not =====
...
===== SQL Insert Into =====
INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country)
VALUES ('Cardinal', 'Tom B. Erichsen', 'Skagen 21', 'Stavanger', '4006', 'Norway');
===== SQL NULL values =====
...
===== SQL Update =====
UPDATE Customers
SET ContactName = 'Alfred Schmidt', City= 'Frankfurt'
WHERE CustomerID = 1;
===== SQL Delete =====
...
===== SQL Select top =====
...
===== SQL Aggregate functions =====
...
===== SQL MIN() and MAX() functions =====
...
===== SQL COUNT() function =====
...
===== SQL SUM() function =====
...
===== SQL AVG() function =====
...
===== SQL LIKE =====
...
===== SQL Wildcards =====
...
===== SQL IN =====
...
===== SQL Between =====
...
===== SQL Aliases =====
...
===== SQL Joins =====
...
===== SQL Inner Join =====
...
===== SQL Left Join =====
...
===== SQL Right Join =====
...
===== SQL Full Join =====
...
===== SQL Self Join =====
...
===== SQL Union =====
...
===== SQL Group By =====
...
===== SQL Having =====
...
===== SQL Exists =====
...
===== SQL ... =====
...
===== SQL Select Into =====
The SELECT INTO statement copies data from one table into a new table.
SELECT * INTO CustomersBackup2017
FROM Customers;
===== SQL ... =====
...
===== SQL Stored Procedures =====
-- create stored procedure
CREATE PROCEDURE SelectAllCustomers
AS
SELECT * FROM Customers
GO;
-- execute stored procedure
EXEC SelectAllCustomers;
===== SQL ... =====
...
===== SQL Create DB =====
CREATE DATABASE testDB;
===== SQL Drop DB =====
DROP DATABASE testDB;
===== SQL ... =====
...
===== SQL Create Table =====
CREATE TABLE Persons (
PersonID int,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255)
);
===== SQL Drop Table =====
DROP TABLE Shippers;
-- truncate table deletes all data in the table, but not the table itself
TRUNCATE TABLE table_name;
===== SQL Alter Table =====
ALTER TABLE Customers
ADD Email varchar(255);
ALTER TABLE Customers
DROP COLUMN Email;
-- change data type
ALTER TABLE table_name
ALTER COLUMN column_name new_datatype;
===== SQL ... =====
...
===== SQL constraints =====
CREATE TABLE table_name (
column1 datatype constraint,
column2 datatype constraint,
column3 datatype constraint,
....
);
Constraints:
* 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 - Used to create and retrieve data from the database very quickly
===== SQL ... =====
...
===== SQL Primary Key =====
-- one column as PK
CREATE TABLE Persons (
ID int NOT NULL PRIMARY KEY,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int
);
-- multiple columns as PK
CREATE TABLE Persons (
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
CONSTRAINT PK_Person PRIMARY KEY (ID,LastName)
);
===== SQL Foreign Key =====
FOREIGN KEY constraint prevents invalid data from being inserted into the foreign key column, because it has to be one of the values contained in the parent table.
CREATE TABLE Orders (
OrderID int NOT NULL PRIMARY KEY,
OrderNumber int NOT NULL,
PersonID int FOREIGN KEY REFERENCES Persons(PersonID)
);
===== SQL ... =====
...
===== SQL Index =====
Indexes are used to retrieve data from the database more quickly than otherwise. The users cannot see the indexes, they are just used to speed up searches/queries.
CREATE INDEX idx_pname
ON Persons (LastName, FirstName);
===== SQL ... =====
...
===== SQL Views =====
CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;
===== SQL Injection =====
SQL injection is a code injection technique that might destroy your database.
SQL injection is one of the most common web hacking techniques.
SQL injection is the placement of malicious code in SQL statements, via web page input.
-- this statement delivers not the data only from user 105, but the full table
SELECT * FROM Users WHERE UserId = 105 OR 1=1;
===== SQL ... =====
...