vwiki

User Tools

Site Tools

Trace:
sql_tutorial

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Both sides previous revisionPrevious revision
Next revision		Previous revision
sql_tutorial [2024/10/08 14:51] – kalaschnikowsql_tutorial [2024/10/09 10:19] (current) – [SQL Update] kalaschnikow
Line 1: Line 1:
 +====== SQL Tutorial ======
 +
 +My conspectus of a [[https://www.w3schools.com/sql/default.asp|w3schools SQL tutorial]].
 +
 +
 +===== SQL Home =====
 +...
 +
 +===== SQL Intro =====
 +...
 +
 +===== SQL Syntax =====
 +<code sql>
 +...
 +</code>
 +
 +===== SQL Select =====
 +<code sql>SELECT col1, col2, col3 FROM tab1;</code>
 + 
 +<code sql>SELECT DISTINCT col1, col2, col3 FROM tab1;</code>
 +
 +<code sql>SELECT COUNT (DISTINCT col1) FROM tab1;</code>
 +
 +<code sql>
 +SELECT COUNT (*) 
 +FROM (SELECT DISTINCT Country FROM Customers);</code>
 +
 +===== SQL Where =====
 +<code sql>
 +SELECT * FROM Customers 
 +WHERE Country='Mexico';
 +</code>
 +
 +  * Operators:
 +    * =
 +    * >
 +    * <
 +    * >=
 +    * <=
 +    * <>
 +    * !=
 +    * BETWEEN
 +    * LIKE
 +    * IN
 +
 +===== SQL Order By =====
 +<code sql>
 +SELECT * FROM tab1 
 +ORDER BY col1 ASC | DESC;
 +</code>
 +
 +===== SQL And =====
 +<code sql>
 +...
 +</code>
 +
 +===== SQL Or =====
 +<code sql>
 +...
 +</code>
 +
 +===== SQL Not =====
 +<code sql>
 +...
 +</code>
 +
 +===== SQL Insert Into =====
 +<code sql>
 +INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country)
 +VALUES ('Cardinal', 'Tom B. Erichsen', 'Skagen 21', 'Stavanger', '4006', 'Norway');
 +</code>
 +
 +
 +===== SQL NULL values =====
 +<code sql>
 +...
 +</code>
 +
 +===== SQL Update =====
 +<code sql>
 +UPDATE Customers
 +SET ContactName = 'Alfred Schmidt', City= 'Frankfurt'
 +WHERE CustomerID = 1;
 +</code>
 +
 +===== SQL Delete =====
 +<code sql>
 +...
 +</code>
 +
 +
 +===== SQL Select top =====
 +<code sql>
 +...
 +</code>
 +
 +===== SQL Aggregate functions =====
 +<code sql>
 +...
 +</code>
 +
 +===== SQL MIN() and MAX() functions =====
 +<code sql>
 +...
 +</code>
 +
 +
 +===== SQL COUNT() function =====
 +<code sql>
 +...
 +</code>
 +
 +===== SQL SUM() function =====
 +<code sql>
 +...
 +</code>
 +
 +===== SQL AVG() function =====
 +<code sql>
 +...
 +</code>
 +
 +===== SQL LIKE =====
 +<code sql>
 +...
 +</code>
 +
 +===== SQL Wildcards =====
 +<code sql>
 +...
 +</code>
 +
 +===== SQL IN =====
 +<code sql>
 +...
 +</code>
 +
 +===== SQL Between =====
 +<code sql>
 +...
 +</code>
 +
 +===== SQL Aliases =====
 +<code sql>
 +...
 +</code>
 +
 +
 +===== SQL Joins =====
 +<code sql>
 +...
 +</code>
 +
 +===== SQL Inner Join =====
 +<code sql>
 +...
 +</code>
 +
 +===== SQL Left Join =====
 +<code sql>
 +...
 +</code>
 +
 +===== SQL Right Join =====
 +<code sql>
 +...
 +</code>
 +
 +
 +===== SQL Full Join =====
 +<code sql>
 +...
 +</code>
 +
 +===== SQL Self Join =====
 +<code sql>
 +...
 +</code>
 +
 +===== SQL Union =====
 +<code sql>
 +...
 +</code>
 +
 +
 +===== SQL Group By =====
 +<code sql>
 +...
 +</code>
 +
 +===== SQL Having =====
 +<code sql>
 +...
 +</code>
 +
 +===== SQL Exists =====
 +<code sql>
 +...
 +</code>
 +
 +
 +===== SQL ... =====
 +<code sql>
 +...
 +</code>
 +
 +
 +===== SQL Select Into =====
 +
 +The SELECT INTO statement copies data from one table into a new table.
 +
 +<code sql>
 +SELECT * INTO CustomersBackup2017
 +FROM Customers;
 +</code>
 +
 +===== SQL ... =====
 +<code sql>
 +...
 +</code>
 +
 +
 +===== SQL Stored Procedures =====
 +<code sql>
 +-- create stored procedure
 +CREATE PROCEDURE SelectAllCustomers
 +AS
 +SELECT * FROM Customers
 +GO;
 +
 +-- execute stored procedure
 +EXEC SelectAllCustomers;
 +</code>
 +
 +
 +===== SQL ... =====
 +<code sql>
 +...
 +</code>
 +
 +===== SQL Create DB =====
 +<code sql>
 +CREATE DATABASE testDB; 
 +</code>
 +
 +===== SQL Drop DB =====
 +<code sql>
 +DROP DATABASE testDB;
 +</code>
 +
 +===== SQL ... =====
 +<code sql>
 +...
 +</code>
 +
 +===== SQL Create Table =====
 +<code sql>
 +CREATE TABLE Persons (
 +    PersonID int,
 +    LastName varchar(255),
 +    FirstName varchar(255),
 +    Address varchar(255),
 +    City varchar(255)
 +);
 +</code>
 +
 +
 +===== SQL Drop Table =====
 +<code sql>
 +DROP TABLE Shippers;
 +</code>
 +
 +
 +<code sql>
 +-- truncate table deletes all data in the table, but not the table itself
 +TRUNCATE TABLE table_name;
 +</code>
 +
 +===== SQL Alter Table =====
 +<code sql>
 +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;
 +</code>
 +
 +===== SQL ... =====
 +<code sql>
 +...
 +</code>
 +
 +===== SQL constraints =====
 +<code sql>
 +CREATE TABLE table_name (
 +    column1 datatype constraint,
 +    column2 datatype constraint,
 +    column3 datatype constraint,
 +    ....
 +);
 +</code>
 +
 +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 ... =====
 +<code sql>
 +...
 +</code>
 +
 +===== SQL Primary Key =====
 +<code sql>
 +-- 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)
 +);
 +</code>
 +
 +===== 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.
 +
 +<code sql>
 +CREATE TABLE Orders (
 +    OrderID int NOT NULL PRIMARY KEY,
 +    OrderNumber int NOT NULL,
 +    PersonID int FOREIGN KEY REFERENCES Persons(PersonID)
 +);
 +</code>
 +
 +===== SQL ... =====
 +<code sql>
 +...
 +</code>
 +
 +===== 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.
 +
 +<code sql>
 +CREATE INDEX idx_pname
 +ON Persons (LastName, FirstName); 
 +</code>
 +
 +===== SQL ... =====
 +<code sql>
 +...
 +</code>
 +
 +===== SQL Views =====
 +<code sql>
 +CREATE VIEW view_name AS
 +SELECT column1, column2, ...
 +FROM table_name
 +WHERE condition;
 +</code>
 +
 +===== 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.
 +
 +<code sql>
 +-- this statement delivers not the data only from user 105, but the full table
 +SELECT * FROM Users WHERE UserId = 105 OR 1=1;
 +</code>
 +
 +===== SQL ... =====
 +<code sql>
 +...
 +</code>
 +
 +
 +
 +
 +
 +
 +
 +
 +