Structured Query Language (SQL) is the standardized computer language used to interface with database, which is used to retrieve and manage data in the relational database systems. IBM first designed and applied the language in its early development of database system. In 1986, SQL was identified as the standard language by ANSI for the relational database management system; In1987, ISO supported and made it become the international standardized language. In fact, although most database systems use SQL, most of them also have their own additional proprietary extensions that are usually only used on their systems. At present, ANSI SQL 89 is adopted by most of database systems.
SQL is an advanced programming language, which allows users to work on high-level data structure regardless of the way and the method of data storage. Its interface can use the same SQL as input and management of data between database systems with different underlying structures and between different databases. All SQL statements can accept record set for input and send record set for output. The output of a SQL statement can become the input of another SQL statement. It shows that SQL statements can be overlapped to use. So SQL possesses great flexibility and utility. In most cases, just a SQL clause instead of a group of clauses of other programming languages can express a complete event.
The SQL language includes three parts:
1. Data Definition Language (DDL)
2. Data Manipulation Language (DML)
3. Data Control Language (DCL)
1. DDL can be used to define new tables and associated elements. Its most basic items are the CREATE, ALTER, RENAME, TRUNCATE and DROP statements:
For example, when we want to create a customer table, we need to use the create statement as follows:
CREATE TABLE Customer
(First_Name char(50),
Last_Name char(50),
Address char(50),
City char(50),
Country char(25),
Birth_Date date)
2. DML can be used to add, update and delete data and control transactions.
For example, we use update statement to modify the sale amount of $400 to $600 occurred in March 29 in Montreal in a Store_Information table as follow:
UPDATE Store_Information
SET Sales = 600
WHERE Store_name = "Montreal" AND Date = "Mar-29-2008"
3. DCL allows the user to control who has access to see or operate data within a database, including two keywords: Grant and Revoke.
GRANT designates several users to work on an operation or a set of operations.
REVOKE eliminates or limits the right of a user to perform an operation or a set of operations
For example, we use the Grant statement to authorize a user to manipulate data in the Store_Information table mentioned above:
GRANT SELECT, UPDATE
ON Store_Information
TO Joe, Jim;
The SQL language can also be broken down into several language elements, including clauses, expressions, predicates, queries, statements.
In order to retrieve information from a database, we use a query that is the most common operation in SQL language, which is separate from Data Manipulation Language.
For example, if we need to pick up the store_name for sales=$400 from Store_Information mentioned above, we use select statement illustrated as follow:
SELECT Store_name
FROM Store_Information
WHERE Sales=$400
Problem of Security
Using SQL also has the safety difficulty. Now a worse attack to SQL is SQL Injection attack, which can add some destructive command to destroy database systems (DROP TABLE, DROP DATABS, OR DELETE * FROM some table). It will lead to service disruption or paralysis of system. Now the best way to defense against SQL Injection is to enforce the Use of Parameterized Statements.
Reference:
http://kb.iu.edu/data/ahux.html
http://en.wikipedia.org/wiki/SQL
http://www.geocities.com/SiliconValley/Vista/2207/sql1.html
http://msdn.microsoft.com/en-us/library/ms161953.aspx
No comments:
Post a Comment