por Srimal Priyanga 2 anos atrás
363
Mais informações
An SQL Injection can destroy your database.
SQL injection is a technique where malicious users can inject SQL commands into an SQL statement, via web page input.
Injected SQL commands can alter SQL statement and compromise the security of a web application.
SQL has many built-in functions for performing calculations on data.
SQL scalar functions return a single value, based on the input value.
FORMAT()
Formats how a field is to be displayed
NOW()
Returns the current system date and time
ROUND()
Rounds a numeric field to the number of decimals specified
LEN()
Returns the length of a text field
MID()
Extract characters from a text field
LCASE()
Converts a field to lower case
UCASE()
Converts a field to upper case
SQL aggregate functions return a single value, calculated from values in a column.
SUM()
Returns the total sum of a numeric column.
SELECT SUM(column_name) FROM table_name;
SELECT SUM(Quantity) AS TotalItemsOrdered FROM OrderDetails;
MIN()
Returns the smallest value
MAX()
Returns the largest value
LAST()
Returns the last value
FIRST()
Returns the first value
COUNT()
Returns the number of rows that matches a specified criteria.
SELECT COUNT(column_name) FROM table_name;
SELECT COUNT(CustomerID) AS OrdersFromCustomerID7 FROM Orders
WHERE CustomerID=7;
AVG()
Returns the average value of a numeric column.
SELECT AVG(column_name) FROM table_name
SELECT ProductName AS Pr-name, Price FROM Products
WHERE Price > (SELECT AVG(Price) FROM Products);
In SQL, a view is a virtual table based on the result-set of an SQL statement.
A view contains rows and columns, just like a real table. The fields in a view are fields from one or more real tables in the database.
You can add SQL functions, WHERE, and JOIN statements to a view and present the data as if the data were coming from one single table
Note: A view always shows up-to-date data! The database engine recreates the data, using the view's SQL statement, every time a user queries a view.
DROP VIEW view_name
CREATE OR REPLACE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition
CREATE VIEW [Current Product List] AS
SELECT ProductID,ProductName,Category
FROM Products
WHERE Discontinued=No
CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition
CREATE VIEW [Products Above Average Price] AS
SELECT ProductName,UnitPrice
FROM Products
WHERE UnitPrice>(SELECT AVG(UnitPrice) FROM Products)
An SQL JOIN clause is used to combine rows from two or more tables,
based on a common field between them.
The most common type of join is: SQL INNER JOIN (simple join).
An SQL INNER JOIN return all rows from multiple tables where the join condition is met.
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.
SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;
The FULL OUTER JOIN keyword returns all rows from the left table (table1) and from the right table (table2).The FULL OUTER JOIN keyword combines the result of both LEFT and RIGHT joins.
SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2
ON table1.column_name=table2.column_name;
The RIGHT JOIN keyword returns all rows from the right table (table2), with the matching rows in the left table (table1).
The result is NULL in the left side when there is no match.
SELECT column_name(s)
FROM table1
RIGHT JOIN table2
ON table1.column_name=table2.column_name;
The LEFT JOIN keyword returns all rows from the left table (table1), with the matching rows in the right table (table2).
The result is NULL in the right side when there is no match.
PS! In some databases LEFT JOIN is called LEFT OUTER JOIN.
SELECT column_name(s)
FROM table1
LEFT OUTER JOIN table2
ON table1.column_name=table2.column_name;
The INNER JOIN keyword selects all rows from both tables as long as there is a match between the columns in both tables.
SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name=table2.column_name;
SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
INNER JOIN Orders
ON Customers.CustomerID=Orders.CustomerID
ORDER BY Customers.CustomerName;
AUTO INCREMENT
Very often we would like the value of the primary key field to be created automatically every time a new record is inserted.
We would like to create an auto-increment field in a table.
CREATE INDEX
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.
INSERT INTO SELECT
With SQL, you can copy information from one table into another.
The INSERT INTO SELECT statement copies data from one table and inserts it into an existing table.
SELECT INTO
With SQL, you can copy information from one table into another.
The SELECT INTO statement copies data from one table and inserts it into a new table.
Aliases
SQL aliases are used to temporarily rename a table or a column heading.
SQL aliases are used to give a database table, or a column in a table, a temporary name.Basically aliases are created to make column names more readable.
SELECT TOP
The SELECT TOP clause is used to specify the number of records to return.
The SELECT TOP clause can be very useful on large tables with thousands of records. Returning a large number of records can impact on performance.
Note: Not all database systems support the SELECT TOP clause.
SELECT TOP number|percent column_name(s)
FROM table_name;
SELECT TOP 2 * FROM Customers;
SELECT TOP 50 PERCENT * FROM Customers;
ORDER BY
The ORDER BY keyword is used to sort the result-set.
The ORDER BY keyword is used to sort the result-set by one or more columns.
The ORDER BY keyword sorts the records in ascending order by default.
To sort the records in a descending order, you can use the DESC keyword.
AND & OR
The AND & OR operators are used to filter records based on more than one condition.
The AND operator displays a record if both the first condition AND the second condition are true.
The OR operator displays a record if either the first condition OR the second condition is true.
WHERE
The WHERE clause is used to filter records.
The WHERE clause is used to extract only those records that fulfill a specified criterion.
LIKE
The LIKE operator is used in a WHERE clause to search for a specified pattern in a column.
SELECT * FROM Customers
WHERE City LIKE '%s';
Wildcards
A wildcard character can be used to substitute for any other character(s) in a string.
In SQL, wildcard characters are used with the SQL LIKE operator.
SQL wildcards are used to search for data within a table.
[^charlist] or [!charlist]
Matches only a character NOT specified within the brackets
SELECT * FROM Customers
WHERE City LIKE '[!bsp]%';
or
SELECT * FROM Customers
WHERE City NOT LIKE '[bsp]%';
[charlist]
Sets and ranges of characters to match
SELECT * FROM Customers
WHERE City LIKE '[bsp]%';
_
A substitute for a single character
SELECT * FROM Customers
WHERE City LIKE 'L_n_on';
%
A substitute for zero or more characters
SELECT * FROM Customers
WHERE City LIKE '%es%';
BETWEEN
The BETWEEN operator is used to select values within a range.
The values can be numbers, text, or dates.
SELECT * FROM Products
WHERE Price NOT BETWEEN 10 AND 20;
IN
The IN operator allows you to specify multiple values in a WHERE clause.
SELECT * FROM Customers
WHERE City IN ('Paris','London');
SELECT DISTINCT
In a table, a column may contain many duplicate values; and sometimes you only want to list the different (distinct) values.
The DISTINCT keyword can be used to return only distinct (different) values.
Data Control Language
For database authorization, role control operation
used to control access to database by securing it.
REVOKE
Withdraws user’s access privileges to database given with the GRANT command
GRANT
Gives user’s access privileges to database
Transactional Control Language
used to manage different transactions occurring within a database
SAVE TRANSACTION
Sets a savepoint within a transaction
ROLLBACK
Restores database to original state since the last COMMIT command in transactions
COMMIT
Saves work done in transactions
Data Manipulation Language
DML modifies the database instance by inserting, updating and deleting its data. DML is responsible for all data modification in databases
DELETE
Deletes dat records from a table
Subtopic
UPDATE
Updates existing records into a table
UPDATE table_name
SET column1=value1,column2=value2,...
WHERE some_column=some_value;
INSERT
Inserts data into a table
SELECT
Retrieves data from a table
Data definition Language
used to create and modify the structure of database objects in database
TRUNCATE
Deletes all records from a table and resets table identity to initial value.
Rename
DROP
Deletes objects of the database
ALTER
Alters objects of the database
CREATE
Creates objects in the database
The DEFAULT constraint is used to insert a default value into a column.
The default value will be added to all new records, if no other value is specified.
A FOREIGN KEY in one table points to a PRIMARY KEY in another table.
MySQL:
CREATE TABLE Orders (
O_Id int NOT NULL,
OrderNo int NOT NULL,
P_Id int,
PRIMARY KEY (O_Id),
FOREIGN KEY (P_Id) REFERENCES Persons(P_Id)
)
SQL Server / Oracle / MS Access:
CREATE TABLE Orders (
O_Id int NOT NULL PRIMARY KEY,
OrderNo int NOT NULL,
P_Id int FOREIGN KEY REFERENCES Persons(P_Id)
)
* Functional Dependencies
EmpNum --> EmpEmail
* Determinant
Attribute on the LHS is known as the
*
For a relation to be in Third Normal Form, it must be in Second Normal form and the following must satisfy:
* Second normal form says, that every non-prime attribute should be fully functionally dependent on prime key attribute.
Prime/Key attribute :
an attribute, which is part of prime-key, is prime attribute.
Non-prime/key attribute:
an attribute, which is not a part of prime-key, is said to be a non-prime attribute.
This is defined in the definition of relations (tables) itself.
This rule defines that all the attributes in a relation must have atomic domains.
Values in atomic domain are indivisible units.
* Each attribute must contain only single value from its pre-defined domain.
* Determinant
Attribute on the LHS is known as the
EmpNum --> EmpEmail
* Functional Dependencies
EmpNum --> EmpEmail
* Subclass / Superclass
* Generalization / Specialization,
* Categories(union)
* Attribute & relationship inheritance
Specilaization
Rule 9
Mapping of Unions
Rule 8
* Subclass / Superclass
Option D
Create 1 table for Whole Super Class Sub Class Relationship
With Flag values
Option C
Create 1 table for Whole Super Class Sub Class Relationship
Option B
Total Participation :
Create every Sub Class as a entity with Super Class Primary Key & Super class attributes
Option A
Disjoint Participation :
Create Super Entity Table
Create every Sub class Entity with super class Primary key
Entity relationship model defines the conceptual view of database.
It works around real world entity and association among them. At view level, ER model is considered well for designing databases.
Partcipations
Partial
Total
Relationships Constraints
Cardinality
Cardinality defines the number of entities in one entity set which can be associated to the number of entities of other set via relationship set.
m : n
1 : m
1:1
Relationships
Complex - Ternary
Binary
Unary / Recursive
Attributes
Keys
Key is an attribute or collection of attributes that uniquely identifies an entity among entity set.
Composite key
Foreign Key
Primary key
This is one of the candidate key chosen by the database designer to uniquely identify the entity set.
Candidate / Alternate Key
Minimal super key is called candidate key that is, supers keys for which no proper subset are a superkey.
An entity set may have more than one candidate key.
Super Key
Set of attributes (one or more) that collectively identifies an entity in an entity set.
Base / Derived
Derived attributes are attributes, which do not exist physical in the database, but there values are derived from other attributes presented in the database.
Complex
Multi-Value
Multi-value attribute may contain more than one values.
Composite
Composite attributes are made of more than one simple attribute.
Simple
Simple attributes are atomic values, which cannot be divided further.
Entity
Weak
Strong
