basic SQL (Query Language)

Okay buddy this time I will discuss a basic tutorial of SQL, for those of you lovers of course you need to know programming ​QUERY  language, and I will guide you to learn from the basic to the application of the code, let's start...

 SQL (Structured Query Language) is a language used to access data in a relational database. This language is the de facto standard language used in relational database management. Currently, almost all existing database server supports this language to perform data management.
HistorySQL history starts from the article, a researcher at IBM named Jhonny Oracle that discusses the idea of ​​making a relational database in June 1970. This article also discusses the possibility of manufacturing a standard language for accessing data in the database. Language is then given the name of the sequel (Structured English Query Language).
After the publication of the article, IBM held a project of the relational database language based sequel. However, due to legal issues regarding the naming Sequel, IBM also turn it into SQL. Implementation of relational database known as the System / R.
In the late 1970s, the emerging company called Oracle that makes popular database server named after the company. With the rising popularity John Oracle, SQL too popular then so is currently a de facto standard language in database management.
StandardizationSQL standardization began in 1986, marked by the release of the ANSI SQL standard. This standard is often referred to as SQL86.Standar is then repaired in 1989 and then improved again in 1992. The latest version is known as SQL92. In 1999, a new standard called the SQL99 or SQL99, but most of the SQL92 reference implementation.
Currently, there is actually no database server is 100% supported SQL92. This is because each server has their dialect.
Basic usageIn general, SQL consists of two languages, namely the Data Definition Language (DDL) and Data Manipulation Language (DML). Implementation of DDL and DML different for each database management system (DBMS) [4], but in general the implementation of each of these languages ​​have a form set ANSI standards. This article will use the most common form that can be used on most DBMS. 
Data Definition Language
DDL is used to define, modify, and delete databases and objects needed in the database, such as tables, views, users, and so on. In general, DDL CREATE is used to create new objects, USE to use objects, ALTER to modify an existing object, and DROP to remove the object. DDL is typically used by database administrators in the creation of a database application.

CREATE

CREATE is used to create a database and database objects. SQL commonly used are:

CREATE DATABASE nama_basis_data

CREATE DATABASE create a new database.

CREATE TABLE table_name

CREATE TABLE create a new table in the current database. In general, this command has the form

CREATE TABLE [table_name]
(
nama_field1 tipe_data [constraints] [,
nama_field2 tipe_data,
...]
)

or

CREATE TABLE [table_name]
(
nama_field1 tipe_data [,
nama_field2 tipe_data,
...]
[CONSTRAINT nama_field constraints]
)

with:

nama_field is the name of the column (field) to be made. Some database management systems allow the use of space and character nonhuruf column name.

type_data depending on the implementation of data base management systems. For example, in MySQL, can be any data type VARCHAR, TEXT, BLOB, ENUM, and so on.

constraints are limits given for each column. It also depends on the implementation of the database management system, such as NOT NULL, UNIQUE, and so on. This can be used to define the primary key (primary key) and a foreign key (foreign key).

The table may not have a primary key at all, but it is advisable to define at least one column as the primary key.

example:

CREATE TABLE user
(
username VARCHAR (30) CONSTRAINT PRIMARY KEY,
passwd VARCHAR (20) NOT NULL,birth_date DATETIME
);

will create a user table as follows:
passwd| username| birth_date

Data Manipulation Language
DML is used to manipulate data in a table. The common commands are:

    SELECT to display data
    INSERT to add new data
    UPDATE to modify existing data
    DELETE to delete data

SELECT
SELECT is the most frequently used commands in SQL, so sometimes the term referred to a SELECT query. SELECT is used to display data from one or more tables, usually in the same database. In general, a complete SELECT statement has the form: (QUERY Budin) Cilegon.
SELECT [table_name | aka.] Name_field1 [AS aka1] [, name_field2 ...]FROM name_tabel1 [AS aka1] [INNER | LEFT | RIGHT JOIN ON tabel2 condition[, Nama_tabel3 [AS alias3], ...][WHERE condition][ORDER BY name_field1 [ASC | DESC] [, name_field2 [ASC | DESC], ...]][GROUP BY name_field1 [, name_field2, ...]][HAVING condition_aggregat]
with:

    conditions is a requirement that must be met in order to display the data.
    kondisi_aggregat are special requirements for aggregate functions.
The condition can be associated with logical operators such as AND, OR, and so on.
Example:
It is assumed there is a user table that contains the following data.passwd username tanggal_lahir jml_transaksi total_transaksiAris 6487AD5EF 09-09-1987 6 10 000Budi 97AD4erD 01-01-1994 0 0Charlie 548794654 06-12-1965 24 312 150Daniel FLKH947HF 24-04-1980 3 0Erik 94RER54 17-08-1945 34 50 000Example 1: Show all data.
SELECT *FROM user
Example 2: Show users who never transact.
SELECT *FROM userWHERE total_transaksi = 0
Example 3: Display user's username to a transaction value of less than 10 and more than 1,000.
SELECT usernameFROM userWHERE jml_transaction <10 AND total_transaction> 1000Example 4: Show the total nominal transaction has occurred.
SELECT SUM (total_transaction) AS total_nominal_transactionFROM userExample 5: Show all data sorted by largest to the smallest number of transactions.
SELECT *FROM userORDER BY DESC jml_transaction
  
 Aggregate functions

Some DBMS have aggregate function, ie the specific functions that involve a bunch of data (aggregate). In general, aggregate functions are:

     SUM to calculate the total nominal Data
     COUNT to count the number of occurrences of data
     AVG to compute an average group of data
     MAX and MIN to get the maximum / minimum of a bunch of data.

Aggregate function is used in the SELECT. Requirements to be added to the aggregate function HAVING, not WHERE.
subquery

Sometimes queries can be complex, especially if it involves more than one table and / or aggregate functions. Some DBMS allow the use of subquery. example:

Show username users who have the greatest number of transactions.

SELECT username
FROM user
WHERE jml_transaction =
(
SELECT MAX (jml_transaction)
FROM user
)

INSERT

To save the data in the table is the syntax:

INSERT INTO [NAMA_TABLE] ([DAFTAR_FIELD]) VALUES ([DAFTAR_NILAI])

example:

INSERT INTO TEST (NAME, ADDRESS, PASSWORD) VALUES ('test', 'address', 'pass');

UPDATE

To change the data using the syntax:

UPDATE [NAMA_TABLE] SET [field_name] = [VALUE] WHERE [CONDITIONS]

example:

UPDATE Muser set password = "123456" where username = "abc"

DELETE

To delete the data used syntax:

DELETE FROM [nama_table] Where [EVENT]

example:

DELETE FROM TEST WHERE NAME = 'test';

This is the basis for determining SQL then please wait for my next post and continue to see other tutorial in my blog .Thanks
Source : id.wikipedia.org