MySQL Basics Flashcards
What does RDBMS stand for?
RDBMS stands for Relational Database Management System.
RDBMS is the basis for SQL, and for all modern database systems such as MS SQL Server, IBM DB2, Oracle, MySQL, and Microsoft Access. The data in RDBMS is stored in database objects called tables. A table is a collection of related data entries and it consists of columns and rows.
What is a field?
Every table is broken up into smaller entities called fields. The fields in the Customers table consist of CustomerID, CustomerName, ContactName, Address, City, PostalCode and Country. A field is a column in a table that is designed to maintain specific information about every record in the table. A column is a vertical entity in a table that contains all information associated with a specific field in a table.
What is a record?
A record, also called a row, is each individual entry that exists in a table. For example, there are 91 records in the above Customers table. A record is a horizontal entity in a table.
SELECT * FROM table_name;
example:
SELECT * FROM Customers;
Selects all the records in the “Customers” table and shows them in a result table, called the result-set.
SELECT column1, column2, …
FROM table_name;
Selects the data from table “table_name” in column1, column2,… and shows the data in a result table.
SELECT DISTINCT column1, column2, …
FROM table_name;
The SELECT DISTINCT statement is used to return only distinct (different) values. Inside a table, a column often contains many duplicate values; and sometimes you only want to list the different (distinct) values.
SELECT COUNT(DISTINCT column_name) FROM table_name;
example:
SELECT COUNT(DISTINCT Country) FROM Customers;
Lists and counts the number of different (distinct) values in the column “Country” of the table “Customers”.
What does the WHERE clause do?
The WHERE clause is used to filter records. It is used to extract only those records that fulfill a specified condition.
SELECT column1, column2, …
FROM table_name
WHERE condition;
example1:
SELECT * FROM Customers
WHERE Country=’Mexico’;
example2:
example1:
Selects all records in the table “Customers” where the value equals “Mexico” in the column “Country”.
example2:
Selects all values in the table “Customers” where the value equals 1 in the column “CostumerID”.
SQL requires single quotes around text values. However, numeric fields should not be enclosed in quotes.
Which operators can be used in the WHERE clause?
= Equal > Greater than < Less than >= Greater than or equal <= Less than or equal <> Not equal. NOTE: in some versions of SQL this operator may be written as != BETWEEN Between a certain range LIKE Search for a pattern IN To specify multiple possible values for a column
SELECT * FROM table_name
WHERE column_name BETWEEN integer AND integer
example:
SELECT * FROM Products
WHERE Price BETWEEN 50 AND 60;
Lists all records of table “Products” where the values in column “Price” are between 50 and 60.
SELECT * FROM table_name
WHERE column_name LIKE ‘,,,’;
example:
SELECT * FROM Customers
WHERE City LIKE ‘s%’;
Lists all records in the table “Customers” where the values in column “City” start with the character ‘s’.
SELECT * FROM table_name
WHERE column_name IN (strings, intgers,…);
example:
SELECT * FROM Customers
WHERE City IN (‘Paris’ , ‘London’);
Lists all records of table “Customers” where the values in column “City” are equal to “Paris” or “London”.
SELECT column1, column2, …
FROM table_name
WHERE condition1 AND condition2 AND condition3 …;
SELECT column1, column2, …
FROM table_name
WHERE condition1 OR condition2 OR condition3 …;
SELECT column1, column2, …
FROM table_name
WHERE NOT condition;
The AND operator displays a record if all the conditions separated by AND are TRUE.
The OR operator displays a record if any of the conditions separated by OR is TRUE.
The NOT operator displays a record if the condition(s) is NOT TRUE.
SELECT * FROM Customers
WHERE Country=’Germany’ AND City=’Berlin’;
Lists all records from table “Customers” where the value in the column “Country” is equal to “Germany” AND the value in the column “City” is equal to “Berlin”.
SELECT * FROM Customers
WHERE City=’Berlin’ OR City=’München’;
Lists all records in table “Costumers” where the value in column “City” equals “Berlin” OR the value in column “City” equals “München”.
SELECT * FROM Customers
WHERE NOT Country=’Germany’;
Lists all records of table “Customers” where the value in column “Country” is NOT equal to “Germany”.
SELECT * FROM Customers
WHERE Country=’Germany’ AND (City=’Berlin’ OR City=’München’);
Lists all records from table “Customers” where the value in column “Country” equals to “Germany” AND the value in column “City” equals to “Berlin” or to “München” (use parenthesis fo form complex expressions).
SELECT * FROM Customers
WHERE NOT Country=’Germany’ AND NOT Country=’USA’;
Lists all records of table “Costumers” where the value in column “Country” equals NOT “Germany” and NOT “USA”.
SELECT column1, column2, …
FROM table_name
ORDER BY column1, column2, … ASC | DESC;
The ORDER BY keyword is used to sort the result-set in ascending or descending order.
The ORDER BY keyword sorts the records in ascending order by default. To sort the records in descending order, use the DESC keyword.
SELECT * FROM Customers
ORDER BY Country DESC, CustomerName DESC;
Lists all records from table “Costumers” and orders them first in descending order by the value in column “Country” and then records with the same value in “Country” are further ordered in descending order by the value in column “CostumerName”.
Numbers are first ordered then letters.
example1:
INSERT INTO table_name (column1, column2, column3, …)
VALUES (value1, value2, value3, …);
example2:
INSERT INTO table_name
VALUES (value1, value2, value3, …);
The INSERT INTO statement is used to insert new records in a table.
It is possible to write the INSERT INTO statement in two ways:
- Specify both the column names and the values to be inserted.
- If you are adding values for all the columns of the table, you do not need to specify the column names in the SQL query. However, make sure the order of the values is in the same order as the columns in the table.
What is a NULL Value?
How to test for NULL Values?
A field with a NULL value is a field with no value.
If a field in a table is optional, it is possible to insert a new record or update a record without adding a value to this field. Then, the field will be saved with a NULL value.
Note: A NULL value is different from a zero value or a field that contains spaces. A field with a NULL value is one that has been left blank during record creation!
It is not possible to test for NULL values with comparison operators, such as =, .
We will have to use the IS NULL and IS NOT NULL operators instead.
SELECT column_names
FROM table_name
WHERE column_name IS NULL;
example:
SELECT CustomerName, ContactName, Address
FROM Customers
WHERE Address IS NULL;
Lists all records in column “CostumerName”, “ContactName” and “Address” of table “Costumers” where the value in column “Address” is empty (NULL).
SELECT CustomerName, ContactName, Address
FROM Customers
WHERE Address IS NOT NULL;
Lists all records in column “CostumerName”, “ContactName” and “Address” of table “Costumers” where the value in column “Address” is NOT empty (NOT NULL).
UPDATE table_name
SET column1 = value1, column2 = value2, …
WHERE condition;
The UPDATE statement is used to modify the existing records in a table.
Be careful when updating records in a table! Notice the WHERE clause in the UPDATE statement. The WHERE clause specifies which record(s) that should be updated. If you omit the WHERE clause, all records in the table will be updated!
UPDATE Customers
SET ContactName = ‘Alfred Schmidt’, City= ‘Frankfurt’
WHERE CustomerID = 1;
This statement updates the first record (CostumerID = 1) of table “Costumers” with the new value “Alfred Schmidt” in column “ContactName” and with the new value “Frankfurt” in the column “City”.
UPDATE table_name
SET column_name1=new_value
WHERE column_name2=value;
example:
UPDATE Customers
SET ContactName=’Juan’
WHERE Country=’Mexico’;
This statement will update the value in column “ContactName” to “Juan” for all records where the value for column “Country” is “Mexico”.
It is the WHERE clause that determines how many records will be updated.
When you omit the WHERE clause, ALL records will be updated!
DELETE FROM table_name WHERE condition;
example:
DELETE FROM Customers WHERE CustomerName=’Alfreds Futterkiste’;
The DELETE statement is used to delete existing records in a table.
The WHERE clause specifies which record(s) should be deleted. If you omit the WHERE clause, all records in the table will be deleted!
example:
This statement deletes in table “Costumers” the value “Alfreds Futterkiste” in column “CustomerName”.
DELETE FROM table_name;
This statement deletes all rows in a table without deleting the table. This means that the table structure, attributes, and indexes will be intact.
SELECT column_name(s)
FROM table_name
WHERE condition
LIMIT number;
example:
SELECT * FROM Customers
WHERE Country=’Germany’
LIMIT 3;
Lists from all records of table “Customers” the first 3 records where the value in column “Country” is “Germany”.
SELECT * FROM Customers
LIMIT 3;
Lists the first 3 records of the table “Customers”.
SELECT MIN(column_name)
FROM table_name
WHERE condition;
SELECT MAX(column_name)
FROM table_name
WHERE condition;
The MIN( ) function returns the smallest value of the selected column where a certain condition is met. The MAX( ) function returns the largest value of the selected column where a certain condition is met.
SELECT MIN(Price) AS SmallestPrice FROM Products;
Finds the smalles value of table “Products” in column “Price” and lists it in result_set (result_table) with columname “SmallestPrice”.
SELECT COUNT(column_name)
FROM table_name
WHERE condition;
The COUNT( ) function returns the number of rows from table “table_name” that matches a specified criterion.
SELECT AVG(column_name)
FROM table_name
WHERE condition;
The AVG( ) function returns the average value of a numeric column where a certain condition is met.
SELECT SUM(column_name)
FROM table_name
WHERE condition;
The SUM( ) function returns the total sum of a numeric column where a certian condition is met.
SELECT * FROM table_name
WHERE column_name LIKE pattern;
- WHERE column_name LIKE ‘a%’
- WHERE column_name LIKE ‘%a’
- WHERE column_name LIKE ‘%or%’
- WHERE column_name LIKE ‘_r%’
- WHERE column_name LIKE ‘a_%’
- WHERE column_name LIKE ‘a__%’
- WHERE column_name LIKE ‘a%o’
- WHERE column_name LIKE ‘h[oa]t’
- WHERE column_name LIKE ‘h[^oat]t’
- WHERE column_name LIKE ‘c[a-b]t’
- WHERE column_name LIKE ‘[!bsp]%’
There are two wildcards often used in conjunction with the LIKE operator:
a) The percent sign (%) represents zero, one, or multiple characters
b) The underscore sign (_) represents one, single character
c) [ ] Represents any single character within the brackets
d) ^ Represents any character not in the brackets
e) - Represents any single character within the specified range
Tip: You can also combine any number of conditions using AND or OR operators.
- Finds any values that start with “a”
- Finds any values that end with “a”
- Finds any values that have “or” in any position
- Finds any values that have “r” in the second position
- Finds any values that start with “a” and are at least 2 characters in length
- Finds any values that start with “a” and are at least 3 characters in length
- Finds any values that start with “a” and ends with “o”
- h[oa]t finds hot and hat, but not hit
- h[^oa]t finds hit, but not hot and hat
- c[a-b]t finds cat and cbt
- Finds any value NOT starting with “b”, “s” or “p”
SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1, value2, …);
example:
SELECT * FROM Customers
WHERE Country IN (‘Germany’, ‘France’, ‘UK’);
The IN operator is a shorthand for multiple OR conditions.
Lists all records of table “Customers” where the value in column “Country” equals “Germany”, “France” or “UK”.
SELECT * FROM Customers
WHERE Country NOT IN (SELECT Country FROM Suppliers);
Lists all records of table “Customers” where the value in column “Country” equals NOT the value in column “Country” of table “Suppliers”.
SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2;
example:
SELECT * FROM Products
WHERE Price BETWEEN 10 AND 20;
The BETWEEN operator selects values within a given range. The values can be numbers, text, or dates.
The BETWEEN operator is inclusive: begin and end values are included.
Example:
Lists all records from table “Products” where the value in column “Price” is between 10 and 20.
SELECT * FROM Products
WHERE Price NOT BETWEEN 10 AND 20;
Lists all records from table “Products” where the value in column “Price” is NOT between 10 and 20.
SELECT * FROM Products
WHERE Price BETWEEN 10 AND 20
AND CategoryID NOT IN (1,2,3);
Lists all records from table “Products” where the value in column “Price” is between 10 and 20 and where the value in column “CategoryID” is not 1, 2, or 3.
SELECT * FROM Products
WHERE ProductName BETWEEN ‘Carnarvon Tigers’ AND ‘Mozzarella di Giovanni’
ORDER BY ProductName;
Lists all records from table “Products” where the value in column “ProducrName” is between “Carnarvon Tigers” AND “Mozzarella di Giovanni” and orders them in ascending alphabetic order by value in column “ProductName”.
SELECT * FROM Orders
WHERE OrderDate BETWEEN #07/01/1996# AND #07/31/1996#;
or:
SELECT * FROM Orders
WHERE OrderDate BETWEEN ‘1996-07-01’ AND ‘1996-07-31’;
Lists all records from table “Orders” where the value in column “OrderDate” is between 07/01/1996 AND 07/31/1996 or between 1996-07-01 AND 1996-07-31.
What are Aliases for?
Examples:
SELECT column_name AS alias_name
FROM table_name;
or:
SELECT column_name(s) FROM table_name AS alias_name;
SQL alisases are used to give a table, or a column in a table, a temporary name. Aliases are often used to make column names more readable. An alias only exists for the duration of that query. An alias is created with the AS keyword.
SELECT CustomerID AS ID, CustomerName AS Customer
FROM Customers;
Lists all values in column “CustomerID” of table “Customers” and displays them as “ID” and lists all values in column “CustomerName” of table “Customers” and displays them as “Customer” in the result_set.
SELECT CustomerName AS Customer, ContactName AS [Contact Person]
FROM Customers;
Lists all values in column “CustomerName” of table “Customers” and displays them as “Customer” and lists all values in column “ContactName” of table “Customers” and displays them as “ContactPerson” in the result_set.
Note: It requires double quotation marks or square brackets if the alias name contains spaces.