SQL Tutorial
operators in the WHERE clause 1. not equal 2. between an inclusive range 3. search for a pattern 4. specify multiple possible values for a column 5. filter records based on more than one condition
1. <>, != 2. BETWEEN 3. LIKE 4. IN 5. AND, OR
delete all data
DELETE * FROM Customers; DELETE FROM Customers;
delete the customer "Alfreds Futterkiste" from the "Customers" table
DELETE FROM Customers WHERE CustomerName="Alfreds Futterkiste";
insert a new row, but only insert data in the "CustomerName", "City", and "Country" columns
INSERT INTO Customers (CustomerName, City, Country) VALUES(......);
insert a new row in the "Customers" table
INSERT INTO Customers VALUES(......);
select all customers from the "Customers" table, sorted ascending by the "Country" and descending by the "CustomerName" column
SELECT * FROM Customers ORDER BY Country, CustomerName DESC;
select all customers from the "Customers" table, sorted by the "Country" column
SELECT * FROM Customers ORDER BY Country;
select all customers from the "Customers" table, sorted DESCENDING by the "Country" column
SELECT * FROM Customers OREDER BY Country DESC;
select all customers from the city "Berlin" OR "München", in the "Customers" table
SELECT * FROM Customers WHERE City="Berlin" OR City="Munchen";
select all customers from the country "Germany" AND the city must be equal to "Berlin" OR "München", in the "Customers" table
SELECT * FROM Customers WHERE Country="Germany" AND (City="Berline" OR City="Munchen");
select all customers from the country "Germany" AND the city "Berlin", in the "Customers" table
SELECT * FROM Customers WHERE Country="Germany" AND City="Berlin";
select all the customers from the country "Mexico", in the "Customers" table
SELECT * FROM Customers WHERE Country="Mexico";
select all from the "Customers" table
SELECT * FROM Customers;
select the "CustomerName" and "City" columns from the "Customers" table
SELECT CustomerName, City FROM Customers;
select only the distinct values from the "City" columns from the "Customers" table
SELECT DISTINCT City FROM Customers;
update the customer "Alfreds Futterkiste" with a new contact person and city
UPDATE Customers SET ContactName="", City="" WHERE CustomerName="Alfreds Futterkiste";
create a select statement by adding a variable (txtUserId) to a select string. The variable is fetched from the user input (Request) to the page
txtUserId = getRequestString("UserId"); txtSQL = "SELECT * FROM Users WHERE UserId = " + txtUserId;
UPDATE
updates data in a database
protect a web site from SQL injection attacks
use SQL parameters (values that are added to an SQL query at execution time, in a controlled manner)
CREATE DATABASE
creates a new database
CREATE INDEX
creates an index (search key)
DROP TABLE
deletes a table
DROP INDEX
deletes an index
DELETE
deletes data from a database
ALTER DATABASE
modifies a database
SELECT DISTINCT
return only distinct (different) values
ORDER BY
sort the result-set in ascending order by default
INSERT INTO
inserts new data into a database
RDBMS (2)
1. Relational Database Management System 2. the basis for SQL 3. the data is stored in tables
SQL injection (2)
1. a technique where malicious users can inject SQL commands into an SQL statement, via web page input 2. can alter SQL statement and compromise the security of a web application
SELECT ()
1. extracts data from a database 2. the result is stored in a result table
SQL
Structured Query Language
WHERE
filter records
