Primary Key:
The PRIMARY KEY constraint uniquely identifies each record in a database table.
Primary keys must contain unique values.
A primary key column cannot contain NULL values.
Each table should have a primary key, and each table can have only one primary key.
Foreign Key:
A FOREIGN KEY in one table points to a PRIMARY KEY in another table.
The SELECT statement is used to select data from a database.
The DISTINCT keyword can be used to return only distinct (different) values.
The WHERE clause is used to extract only those records that fulfill a specified criterion.
The AND operator displays a record if both the first condition and the second condition is true.
The OR operator displays a record if either the first condition or the second condition is true.
The ORDER BY keyword is used to sort the result-set by a specified column.
The ORDER BY keyword sort the records in ascending order by default.
If you want to sort the records in a descending order, you can use the DESC keyword.
The INSERT INTO statement is used to insert a new row in a table.
The UPDATE statement is used to update existing records in a table.
The DELETE statement is used to delete records in a table.
The IN operator allows you to specify multiple values in a WHERE clause.
The BETWEEN operator selects a range of data between two values. The values can be numbers, text, or dates.
The COUNT() function returns the number of rows that matches a specified criteria.
EDIT statement is used to manually edit the values in the tables
ALIAS - With SQL, an alias name can be given to a table or to a column.
Aggregate functions often need an added GROUP BY statement.
--------------------------------------------------------------------------------
The GROUP BY Statement
The GROUP BY statement is used in conjunction with the aggregate functions to group the result-set by one or more columns.
SQL GROUP BY Syntax
SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name
The HAVING Clause
The HAVING clause was added to SQL because the WHERE keyword could not be used with aggregate functions.
SQL HAVING Syntax
SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name
HAVING aggregate_function(column_name) operator value
MINUS
MINUS returns all rows from the first SELECT that are not also returned by the second SELECT.
INTERSECT
INTERSECT returns only the rows retrieved by both component queries. Compare this with UNION, which returns the rows retrieved by any of the component queries. If UNION acts like 'OR', INTERSECT acts like 'AND'.
Select Query:
SELECT column_name(s)
FROM table_name (OR)
SELECT * FROM table_name
Distinct Query:
SELECT DISTINCT column_name(s) FROM table_name
Where Query:
SELECT column_name(s) FROM table_name WHERE column_name operator value (CONDITION)
Order By:
SELECT column_name(s) FROM table_name ORDER BY column_name(s) ASC|DESC
Update Query:
UPDATE table_name SET column1=value, column2=value2,... WHERE some_column=some_value
Delete Query:
DELETE FROM table_name
WHERE some_column=some_value
Edit Query:
EDIT table_name
IN Query:
SELECT column_name(s) FROM table_name WHERE column_name IN (value1,value2,...)
BETWEEN Query:
SELECT column_name(s) FROM table_name WHERE column_name BETWEEN value1 AND value2
ALIAS Query:
SELECT column_name(s) FROM table_name AS alias_name
COUNT Query:
SELECT COUNT(column_name) FROM table_name (OR)
SELECT COUNT(*) FROM table_name
COMMIT
ROLLBACK
AND/OR Query:
SELECT column_name(s) FROM table_name WHERE column_name operator value AND/OR column_name operator value
SQL JOIN
The JOIN keyword is used in an SQL statement to query data from two or more tables, based on a relationship between certain columns in these tables.
Tables in a database are often related to each other with keys.
A primary key is a column (or a combination of columns) with a unique value for each row. Each primary key value must be unique within the table. The purpose is to bind data together, across tables, without repeating all of the data in every table.
SQL INNER JOIN Keyword
The INNER JOIN keyword return rows when there is at least one match in both tables.
SQL INNER JOIN Syntax
SELECT column_name(s)
FROM table_name1
INNER JOIN table_name2
ON table_name1.column_name=table_name2.column_name
PS: INNER JOIN is the same as JOIN.
SQL LEFT JOIN Keyword
The LEFT JOIN keyword returns all rows from the left table (table_name1), even if there are no matches in the right table (table_name2).
SQL LEFT JOIN Syntax
SELECT column_name(s)
FROM table_name1
LEFT JOIN table_name2
ON table_name1.column_name=table_name2.column_name
PS: In some databases LEFT JOIN is called LEFT OUTER JOIN.
SQL RIGHT JOIN Keyword
The RIGHT JOIN keyword Return all rows from the right table (table_name2), even if there are no matches in the left table (table_name1).
SQL RIGHT JOIN Syntax
SELECT column_name(s)
FROM table_name1
RIGHT JOIN table_name2
ON table_name1.column_name=table_name2.column_name
PS: In some databases RIGHT JOIN is called RIGHT OUTER JOIN.
SQL FULL JOIN Keyword
The FULL JOIN keyword return rows when there is a match in one of the tables.
SQL FULL JOIN Syntax
SELECT column_name(s)
FROM table_name1
FULL JOIN table_name2
ON table_name1.column_name=table_name2.column_name
The SQL UNION Operator
The UNION operator is used to combine the result-set of two or more SELECT statements.
Notice that each SELECT statement within the UNION must have the same number of columns. The columns must also have similar data types. Also, the columns in each SELECT statement must be in the same order.
SQL UNION Syntax
SELECT column_name(s) FROM table_name1
UNION
SELECT column_name(s) FROM table_name2
Note: The UNION operator selects only distinct values by default. To allow duplicate values, use UNION ALL.
SQL UNION ALL Syntax
SELECT column_name(s) FROM table_name1
UNION ALL
SELECT column_name(s) FROM table_name2
PS: The column names in the result-set of a UNION are always equal to the column names in the first SELECT statement in the UNION.
SQL Constraints
Constraints are used to limit the type of data that can go into a table.
Constraints can be specified when a table is created (with the CREATE TABLE statement) or after the table is created (with the ALTER TABLE statement).
We will focus on the following constraints:
NOT NULL
UNIQUE
PRIMARY KEY
FOREIGN KEY
CHECK
DEFAULT
SQL NOT NULL Constraint
The NOT NULL constraint enforces a column to NOT accept NULL values.
The NOT NULL constraint enforces a field to always contain a value. This means that you cannot insert a new record, or update a record without adding a value to this field.
SQL UNIQUE Constraint
The UNIQUE constraint uniquely identifies each record in a database table.
The UNIQUE and PRIMARY KEY constraints both provide a guarantee for uniqueness for a column or set of columns.
A PRIMARY KEY constraint automatically has a UNIQUE constraint defined on it.
Note that you can have many UNIQUE constraints per table, but only one PRIMARY KEY constraint per table.
The CREATE INDEX statement is used to create indexes in tables.
Indexes allow the database application to find data fast; without reading the whole table.
--------------------------------------------------------------------------------
Indexes
An index can be created in a table to find data more quickly and efficiently.
The users cannot see the indexes, they are just used to speed up searches/queries.
Note: Updating a table with indexes takes more time than updating a table without (because the indexes also need an update). So you should only create indexes on columns (and tables) that will be frequently searched against.
SQL CREATE INDEX Syntax
Creates an index on a table. Duplicate values are allowed:
CREATE INDEX index_name
ON table_name (column_name)
SQL CREATE UNIQUE INDEX Syntax
Creates a unique index on a table. Duplicate values are not allowed:
CREATE UNIQUE INDEX index_name
ON table_name (column_name)
Note: The syntax for creating indexes varies amongst different databases. Therefore: Check the syntax for creating indexes in your database.
A view is a virtual table.
This chapter shows how to create, update, and delete a view.
No comments:
Post a Comment