 |
| TABLE NAME : 'Customers' |
- SELECT
- Used ALMOST ALWAYS! Select the specific columns you want or '*' for every columns in a table
SELECT column_name1, column_name2
FROM table_name1;
or
SELECT *
FROM table_name1;
-- everything!
- DISTINCT
- Select only the different(distinct values) among many duplicate
SELECT DISTINCT column_name, column_name
FROM table_name1;
SELECT column_name, column_name
FROM table_name1;
- WHERE
- filter records; extract clause is used to extract only a specified criterion
SELECT column_name, column_name
FROM table_name1
WHERE column_name operator value;
ex) SELECT *
FROM Customers
WHERE Country='Mexico';
SELECT *
FROM Customers
WHERE ID=5;
-- you do not need single quotes('') in a number
Operators in the WHERE Clause
WHERE column_name operator value;
ex) SELECT *
FROM Customers
WHERE Country='Mexico';
SELECT *
FROM Customers
WHERE ID=5;
-- you do not need single quotes('') in a number
Operators in the WHERE Clause
- = : equal
- <> / != : not equal
- > : greater than
- < : less than
- >= : greater than or equal
- <= : less than or equal
- BETWEEN: Between an inclusive range
- LIKE: Search for a pattern
- IN : multiple possible values including 'the value'
- AND
- Satisfies both the first AND second condition
SELECT * FROM Customers
WHERE Country = 'Germany' AND City = 'Berlin';
WHERE Country = 'Germany' AND City = 'Berlin';
- OR
- Satisfies EITHER the first OR second condition
SELECT * FROM Customers
WHERE City = 'London' OR City = 'Berlin';
WHERE City = 'London' OR City = 'Berlin';
- Combining AND & OR
SELECT * FROM Customers
WHERE Country = 'Germany' AND (City = 'London' OR City = 'Berlin');
WHERE Country = 'Germany' AND (City = 'London' OR City = 'Berlin');
- Order By
- Sort the data by one or more columns
- could be ascending | descending
SELECT * FROM table_name
ORDER BY column_name ASC|DESC, column_name ASC|DESC;
SELECT * FROM Customers
ORDER BY Country ASC, City DESC;
ORDER BY column_name ASC|DESC, column_name ASC|DESC;
SELECT * FROM Customers
ORDER BY Country ASC, City DESC;
- INSERT INTO
- Insert new records in a table
INSERT INTO table_name
VALUES (value1, value2, value3, ...);
INSET INTO table_name(column1, column2, column3, ...)
VALUES(value1, value2, value3, ...);
INSERT INTO Customers(CustomerName, ContactName, Address, City, PostalCode, Country)
VALUES('Sue', 'Park', 'TeheranRo', 'Seoul', '15320', South Korea)
VALUES (value1, value2, value3, ...);
INSET INTO table_name(column1, column2, column3, ...)
VALUES(value1, value2, value3, ...);
INSERT INTO Customers(CustomerName, ContactName, Address, City, PostalCode, Country)
VALUES('Sue', 'Park', 'TeheranRo', 'Seoul', '15320', South Korea)
