Docstoc

SQL tutorial

Document Sample
SQL tutorial Powered By Docstoc
					SQL tutorial




-------------------------------------------------- -----------------------

SQL is a standard - but ...




SQL is an ANSI standard computer language for accessing and manipulating

database systems. The SQL statement used to retrieve and update data in the

database. SQL and database programs work together, such as in MS Access,

DB2, Informix, of MS SQL Server, the Oracle, the Sybase and other database

systems.




Unfortunately, there are many different versions of the SQL language, but

compatible with the ANSI standard, they must be in a similar manner to

support some of the major key words (such as Select, Update, Delete, the

Insert, Where etc.) .




Note: In addition to the SQL standard, most of the SQL database programs

have their own proprietary extensions!

-------------------------------------------------- -----------------------

RDBMS




RDBMS refers to relational database management system.
RDBMS is the foundation of SQL, it is also the basis of all modern database

systems, such as the MS SQL Server, IBM DB2, Oracle, MySQL, as well as

Microsoft Access.




RDBMS data stored in the database object known as a table (tables).




The table is a collection of related data items, which consists of columns and

rows.




-------------------------------------------------- -----------------------

SQL statement behind the semicolon?




Some database systems require a semicolon at the end of each SQL

command. Do not use a semicolon in our tutorials.




The semicolon is a standard method to separate each SQL statement in the

database system so that it can perform more than one statement in the same

request to the server.




If you are using MS Access and SQL Server 2000, you do not have to use a

semicolon after each SQL statement, but some of database software required
to use a semicolon.




-------------------------------------------------- -----------------------

SQL DML and DDL

SQL can be divided into two parts: a data manipulation language (DML) and

Data Definition Language (DDL).




SQL (Structured Query Language) is used to execute the query syntax. But

the SQL language also contains the syntax used to update, insert and delete

records.




Query and update commands form the DML part of SQL:




      * Select - obtain data from a database table

      * The Update - Update data in the database table

      * Delete - to delete data from a database table

      * The Insert INTO - insert data to database table




SQL data definition language (DDL) part gives us the ability to create or delete

tables. We can also define indexes (keys) to provide the link between the

tables, as well as the constraints imposed by the table.
The most important DDL statements in SQL:




      * Create DATABASE - create a new database

      * The Alter DATABASE - modify the database

      * Create TABLE - create a new table

      * Alter TABLE - change (change) database table

      * Drop TABLE - delete table

      * Create the INDEX - creates an index (search key)

      * The Drop the INDEX - delete the index




-------------------------------------------------- -----------------------

SQL Select statement




Select statement to select data from the table.




The results are stored in a result table (called the result set).

SQL Select syntax




Select the column name FROM table name




And:
Select * FROM table name




Note: SQL statements are not case sensitive. Select is equivalent to select.




-------------------------------------------------- -----------------------

SQL Select DISTINCT statement




In the table may contain duplicate values. This is not a problem, but sometimes

you may want to just list the different (of distinct) value.




Keywords DISTINCT is used to return only different values.

Syntax:

Select the DISTINCT column name FROM table name




-------------------------------------------------- -----------------------

Where Clause




For conditionally select data from the table, you can add a Where clause to the

Select statement.

Grammar

The name of the Select column names FROM table Where column operator
value




The following operators can be used in the Where clause: <> = <> <=> =

between like

Such as: Select * FROM Persons the Where the City = 'Beijing'

-------------------------------------------------- -----------------------

The use of quotation marks




Please note that the value of our example, the conditions around the use of

single quotation marks.




SQL uses single quotes around text values (most database systems also

accept double quotes). If the value, please do not use quotation marks.

Text value:




This is correct:

Select * FROM Persons the Where the FirstName = 'Bush,'




This is wrong:

Select * FROM Persons the Where the FirstName = Bush,




Values:
This is correct:

Select * FROM Persons the Where Year> 1965




This is wrong:

Select * FROM Persons Where Year> '1965 '

-------------------------------------------------- -----------------------

Example of the AND operator

Use AND to display all the surname "Carter," and named "Thomas":

Select * FROM Persons the Where the FirstName = 'Thomas' AND the

LastName = 'Carter,'




Example of the OR operator

Use OR to display all the surname "Carter" or, "Thomas":

Select * FROM Persons the Where firstname = 'Thomas' OR lastname =

'Carter,'

-------------------------------------------------- -----------------------

ORDER BY statement




The ORDER BY statement is used to sort the result set according to the

specified column.

The ORDER BY clause to sort the records in ascending order by default.
If you want to sort the records in descending order, you can use the DESC

keyword. (ASC is ascending)




Company Name displayed in alphabetical order:

Select Company, the OrderNumber the FROM the Orders the ORDER BY

Company,




Company Name (Company), displayed in alphabetical order and numeric

order sequence number (OrderNumber):

The Select Company, OrderNumber FROM Orders the ORDER BY Company,,

OrderNumber




The company name is displayed in reverse alphabetical order:

The Select Company, OrderNumber FROM Orders the ORDER BY Company,

the DESC




Displayed in reverse alphabetical order of company name and sequence

number to the numeric order:

Select Company, OrderNumber FROM Orders ORDER BY Company DESC,

OrderNumber ASC
Note: The above results have two equal company names (W3Schools you).

Only this time, have the same value in the first column, second column is in

ascending order. If some of the first column when the value of nulls, the

situation is like this.

-------------------------------------------------- -----------------------

Insert INTO statement




Insert INTO statement is used to insert a new row to the table.

Grammar

The name of the Insert INTO table VALUES (value 1, value 2, ....)




We can also specify you want to insert a column of data:




The Insert an INTO table_name (column 1, column 2, ...) VALUES (value 1,

value 2, ....)




Insert INTO Persons VALUES ('Gates', 'Bill', 'Xuanwumen 10', 'Beijing')

The     Insert    INTO       Persons       (LastName,         Address)       VALUES   ('Wilson',

'Champs-Elysees')




-------------------------------------------------- -----------------------

Update statement
Update statement is used to modify the data in the table.

Syntax:

Update table name SET column name = value Where the column name = a

value




Updating a column in a row

We lastname "Wilson" add firstname:

Update the Person SET the FirstName = 'Fred' Where the LastName =

'Wilson,'




Updating several columns in a row

We will modify the address (address), and add the city name (city):

Update the Person SET Address = 'Zhongshan, 23', the City = 'Nanjing'

Where the LastName = 'Wilson'

-------------------------------------------------- -----------------------

Delete statement




Delete statement to delete rows in the table.

Grammar

The Delete FROM table name Where the column name = value
The Delete the FROM the Person the Where the LastName = 'Wilson,'




Delete all the rows

You can not delete the table, delete all the rows. This means that the structure,

properties, and the index of the table is complete:




The Delete the FROM table_name




Or:




The Delete * the FROM table_name

-------------------------------------------------- -----------------------

TOP clause




The TOP clause is used the provisions to return the number of records.




The TOP clause is very useful for large tables with thousands of records.




Note: Not all database systems support the TOP clause.

SQL Server syntax:




Select TOP number | percent column_name is (s) FROM table_name
MySQL and Oracle SQL Select TOP is equivalent

MySQL syntax

Column_name is the Select (s) FROM table_name the LIMIT number

Example

Select * FROM Persons the LIMIT 5




Oracle syntax

Select column_name is (s) FROM table_name the Where of ROWNUM <=

number

Example

Select * FROM Persons the Where of ROWNUM <= 5




The Select TOP 2 * FROM Persons

-------------------------------------------------- -----------------------

SQL TOP PERCENT instance




Now we want to select 50% of the records from the above, "Persons" table.




We can use the following Select statement:




The Select TOP 50 PERCENT * FROM Persons
-------------------------------------------------- -----------------------

LIKE operator




LIKE operator used in the Where clause specified pattern in the search

column.

SQL LIKE operator syntax




Select column_name is (s)

FROM table_name

Where column_name is the LIKE pattern




Example 1

Now we want to select the people who live in cities beginning with "N" from

above, "Persons" table:

We can use the following Select statement:

Select * the FROM Persons

Where the City LIKE 'N%'




Next, we want to select the people living in the city that contains the "lon," from

the "Persons" table:

We can use the following Select statement:

Select * the FROM Persons
Where the City LIKE '% lon,%'

-------------------------------------------------- -----------------------

SQL wildcard




Data search in the database, the SQL wildcard can replace one or more

characters.




SQL wildcard must be used in conjunction with the LIKE operator.




In SQL, you can use the following wildcards:

Wildcard description

% Substitution of one or more characters

_ Only replace one character

Any single character in the charlist character columns

^ The charlist any single character not in the character column

   Or

[! The charlist]




Now we want the first character after the selected name from the above,

"Persons" table "eorge:

We can use the following Select statement:
Select * the FROM Persons

Where FirstName LIKE '_eorge'




Next, we hope that the last name of the selected records from the "Persons"

table "C" at the beginning, then an arbitrary character, and then the "r", then

any character, then the "er":

We can use the following Select statement:

Select * the FROM Persons

Where LastName LIKE 'C_r_er'




Now we want to select the city live in the "A" or "L" or "N" beginning from above,

"Persons" table:

We can use the following Select statement:

Select * the FROM Persons

Where the City LIKE '[the ALN]%'




Now we want to live in the city from above, "Persons" table, select the

beginning of the "A" or "L" or "N":

We can use the following Select statement:

Select * the FROM Persons

Where the City LIKE '[! The ALN]%'

-------------------------------------------------- -----------------------
IN operator




The IN operator allows us to require more than one value in the Where clause.

SQL the IN syntax




Select column_name is (s)

FROM table_name

Where column_name is IN (value1, value2, ...)




Now we want to select a last name of Adams and Carter from the table:

We can use the following Select statement:

Select * the FROM Persons

Where the LastName IN ('Adams', 'Carter,')

-------------------------------------------------- -----------------------

BETWEEN operator




The operator BETWEEN ... AND is selected between the range of data

between the two values. These values can be numeric, text, or date.

SQL BETWEEN syntax




Select column_name is (s)

FROM table_name
Where column_name is

BETWEEN value1 AND value2




For more information, in alphabetical order between the "Adams" (included)

and "Carter" (not included), please use the following SQL:

Select * the FROM Persons

Where the LastName

BETWEEN 'Adams,' AND 'Carter'




Important: The database BETWEEN ... AND operator handling differences.

Some databases will be listed from Adams, "and" Carter ", but does not

include" Adams, "and" Carter "; some of the database will be listed between"

Adams, "and" Carter "and includes "Adams," and "Carter" person; some

databases will be listed between Adams, "and" Carter "," Adams, "but does not

include the" Carter ".




So, please check your database is how to deal with BETWEEN .... AND

operation breaks!




For the example above, outside the scope of people, please use the NOT

operator:

Select * the FROM Persons
Where the LastName

NOT BETWEEN 'Adams,' AND 'Carter'




-------------------------------------------------- -----------------------

Through the use of SQL, you can specify an alias for the column names and

table names (Alias).

The SQL the Alias

SQL of the table the Alias syntax




Select column_name is (s)

FROM table_name

The AS alias_name




SQL column the Alias syntax




The Select column_name is the AS alias_name

FROM table_name




Alias Example: using table name aliases




Suppose we have two tables: the "Persons" and "Product_Orders. We assign

them an alias "p" and "po".
Now we want to list "John Adams," all orders.




We can use the following Select statement:

Select po.OrderID, p.LastName, p.FirstName

FROM Persons AS p, Product_Orders AS po

Where p.LastName = 'Adams'

Where p.FirstName = 'John'




Do not use the alias of the Select statement:

Select Product_Orders.OrderID, Persons.LastName, Persons.FirstName

FROM Persons, Product_Orders

Where Persons.LastName = 'Hansen'

Where Persons.FirstName = 'Ola'




You can see from the above two Select statement, aliases, query the program

easier to read and write.




Example:

The Select the LastName AS the Family, the FirstName AS the Name

FROM Persons

-------------------------------------------------- -----------------------
SQL the join is used to query data from these tables based on the relationship

between two or more tables in the column.

JOIN and Key




Sometimes in order to get complete results, we need to get results from two or

more tables. We need to perform the join.




The table in the database can be the key contact with one another. Primary

key (Primary Key) is a column, the value of each row in this column only. In the

table, each primary key value is unique. The purpose of doing so is not going

to repeat all the data in each table, cross-bundled data in the table.




Look at the "Persons" table:

P_Id the LastName the FirstName the Address City

1 Adams, John Oxford Street London

2 Bush George Fifth Avenue, New York

3 Carter, Thomas Changan Street, Beijing




Please note that the "P_Id" column is the primary key of the Persons table.

This means that no two rows can have the same P_Id. The P_Id can also

distinguish between them even if the names of two people are identical.
Next, please see the "Orders" table:

Id_O OrderNo Id_P

1778953

2446783

3224561

4245621

53,476,465




Please note, "Id_O column is the primary key of the Orders table, at the same

time, the" Orders "table" P_Id "column is used to refer to people in the"

Persons "table without using their exact name.




Please note that the "P_Id" column to link up the two tables above.




Reference two tables




By reference to the two tables to get data from two tables:

Who ordered the product, and they ordered product?

Select Persons.LastName, Persons.FirstName, Orders.OrderNo

The FROM Persons, the Orders

Where Persons.Id_P = Orders.Id_P
SQL JOIN - use the Join

In addition to the above method, we can also use the keywords JOIN to get

data from the two tables.

If we want the list of all ordered, you can use the following Select statement:

Select Persons.LastName, Persons.FirstName, Orders.OrderNo

FROM Persons

INNER JOIN Orders

ON Persons.Id_P = Orders.Id_P

ORDER BY Persons.LastName




SQL JOIN

In addition to that we used in the example above INNER JOIN (connection),

we can also in several other connections.

The following is a list you can use the JOIN type, as well as the differences

between them.




    * The JOIN: If the table has at least one match, it returns OK

    * LEFT JOIN: Even if the right table does not match, but also from the left

table to return all rows

    * RIGHT JOIN: even left the table does not match, but also from right table
to return all the rows

      * The JOIN FULL: one table, there is a match, it returns OK




-------------------------------------------------- -----------------------

SQL INNER JOIN Keyword




The existence of at least one match in the table, an INNER JOIN keyword

return rows.

The INNER JOIN keyword syntax




Select column_name is (s)

FROM table_name1

INNER JOIN table_name2

ON table_name1.column_name = table_name2.column_name




Note: INNER JOIN and JOIN is the same.




Now we want to list all order.

You can use the following Select statement:

Select Persons.LastName, Persons.FirstName, Orders.OrderNo

FROM Persons

INNER JOIN Orders
ON Persons.Id_P = Orders.Id_P

ORDER BY Persons.LastName




The INNER JOIN keyword in the table exists at least one matching rows are

returned. "Persons" in line "Orders" does not match, does not list the line.

-------------------------------------------------- -----------------------

SQL LEFT JOIN keyword




From the left table (table_name1) LEFT JOIN keyword returns all rows, even if

no matching rows in the table on the right (table_name2).

The LEFT JOIN keyword syntax




Select column_name is (s)

FROM table_name1

LEFT JOIN table_name2

ON table_name1.column_name = table_name2.column_name




Note: In some databases, the LEFT JOIN is called LEFT OUTER the JOIN.




Instance of the left join (LEFT JOIN)

Now, we want to list all people, and their order - if any.

You can use the following Select statement:
Select Persons.LastName, Persons.FirstName, Orders.OrderNo

FROM Persons

LEFT the JOIN the Orders

ON Persons.Id_P = Orders.Id_P

ORDER BY Persons.LastName




-------------------------------------------------- -----------------------

SQL RIGHT JOIN keyword




RIGHT JOIN keyword table on the right (table_name2) returns all rows, even if

there is no matching row in the table on the left (table_name1).

RIGHT JOIN keyword syntax




Select column_name is (s)

FROM table_name1

RIGHT JOIN table_name2

ON table_name1.column_name = table_name2.column_name




Note: In some databases RIGHT JOIN is called RIGHT OUTER the JOIN.




Instance of the right connection (RIGHT JOIN)

Now, we want to list all the orders, and ordered their people - if any.
You can use the following Select statement:




Select Persons.LastName, Persons.FirstName, Orders.OrderNo

FROM Persons

RIGHT JOIN Orders

ON Persons.Id_P = Orders.Id_P

ORDER BY Persons.LastName




-------------------------------------------------- -----------------------

The SQL FULL JOIN keyword




FULL the JOIN keyword will return OK as long as one table there is a match.

FULL JOIN keyword syntax




Select column_name is (s)

FROM table_name1

FULL JOIN table_name2

ON table_name1.column_name = table_name2.column_name




Note: In some databases, FULL JOIN known as the FULL OUTER the JOIN.




Instance of the connection (FULL JOIN)
Now we want to list all the people, and their orders, and all orders, and order

them.

You can use the following Select statement:




Select Persons.LastName, Persons.FirstName, Orders.OrderNo

FROM Persons

FULL the JOIN the Orders

ON Persons.Id_P = Orders.Id_P

ORDER BY Persons.LastName




-------------------------------------------------- -----------------------

SQL UNION operator




UNION operator to merge two or more of the Select statement of the result set.




Please note that the UNION within the Select statement must have the same

number of columns. Column must also have similar data types. At the same

time, each order of the columns in the Select statement must be the same.

SQL UNION Syntax




Select column_name (s) FROM table_name1

UNION
Select column_name (s) FROM table_name2




Note: By default, the UNION operator to select different values. If you 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




In addition, the always equal to the first Select statement in the UNION, the

column names in the UNION result set column name.




Instance of




List all different employee names in China and the United States:

Select E_Name FROM Employees_China

UNION

Select E_Name FROM Employees_USA




List all employees in China and the United States:

Select E_Name FROM Employees_China
UNION ALL

Select E_Name FROM Employees_USA




-------------------------------------------------- -----------------------

SQL Select INTO statement can be used to create a table of the backup

copies.

Select INTO statement




Select INTO statement to select data from a table, then insert another table.




Select INTO statement is commonly used to create a table of the backup

copies or for archiving records.

The SQL the Select an INTO syntax




You can put all the columns to insert a new table:




Select *

The INTO new_table_name [IN externaldatabase The]

FROM old_tablename




Or only the desired columns to insert a new table:
Select column_name is (s)

The INTO new_table_name [IN externaldatabase The]

FROM old_tablename




SQL Select an INTO instance - make a backup copy




The following example will make backup copies of the "Persons" table:




Select *

INTO Persons_backup

FROM Persons




The IN clause can be used to copy tables to another database:

Select *

INTO Persons IN 'Backup.mdb'

FROM Persons




If we want to copy some of the domain, these domains are listed in the Select

statement:

The Select the LastName, the FirstName

INTO Persons_backup

FROM Persons
SQL Select an INTO instance - with a Where Clause




We can also add a Where clause.

The following example from the "Persons" table, extract the information of the

people living in "Beijing", created with two columns named "Persons_backup"

table:

The Select the LastName, Firstname

INTO Persons_backup

FROM Persons

Where the City = 'Beijing'




SQL Select INTO instance - connection table




Data also can be selected from more than one table.




The following example creates a the "Persons_Order_Backup new table,

which contains information obtained from the Persons and the Orders table:




Select Persons.LastName, Orders.OrderNo

INTO Persons_Order_Backup

FROM Persons
INNER JOIN Orders

ON Persons.Id_P = Orders.Id_P

-------------------------------------------------- -----------------------

Create DATABASE statement




Create DATABASE is used to create the database.

SQL Create DATABASE syntax




Create DATABASE database_name The




SQL Create DATABASE instance




Now we want to create a database called the "my_db".




We use the following Create DATABASE statement:




Create DATABASE my_db




By the Create TABLE to add a database table.




-------------------------------------------------- -----------------------

Create TABLE statement is
Create TABLE statement is used to create a database table.

SQL Create TABLE syntax




Create TABLE table name

(

Column name of a data type,

Column name data type,

Column name data type,

....

)




Data type (data_type The) specifies what data type column can hold. The

following table contains the most commonly used data types in SQL:




       Data Type Description

    integer (size)

    int (size)

    smallint (size)

    tinyint (size) only hold an integer. Maximum number of digits specified in

brackets.
    decimal (size, d)

    numeric (size, d) hold with a decimal. The "size" specifies the maximum

number of digits. "D" specifies the maximum number of digits to the right of the

decimal point.

    char (size) to accommodate a fixed-length string (which can contain letters,

numbers and special characters). The length of the string specified in

parentheses.

    varchar (size) to accommodate variable-length string (which can contain

letters, numbers and special characters). The maximum length of the string

specified in parenthesis.

    the date (yyyymmdd) to accommodate the date.




SQL Create TABLE instance

This example demonstrates how to create a table named "Person".

The table contains five columns, column names are: "P_Id", "LastName",

"FirstName", "Address" and "Age":

Create TABLE Persons

(

P_Id an int, and

The LastName varchar (255)

The FirstName varchar (255)
Address varchar (255)

City varchar (255)

)




Id_P column data type is int, contains an integer. The remaining four data

types varchar, the maximum length is 255 characters.

-------------------------------------------------- -----------------------

SQL constraints




The constraints used to restrict the join table data type.

Constraints (through the Create TABLE statement), or in the table can also be

created (by Alter TABLE statement) can be specified in the table is created.




We will mainly explore the following constraints:




      * The NOT NULL

      * A UNIQUE

      * PRIMARY KEY

      * FOREIGN KEY

      * The CHECK

      * The DEFAULT
Note: In the following sections, we will explain in detail each type of constraint.

-------------------------------------------------- -----------------------

SQL NOT NULL constraint

NOT NULL constraint to force the column does not accept NULL values.

NOT NULL constraint to force field always contains the value. This means that

if you do not add to the field value, you can not insert new records or update

records.




The following SQL statement to force the "P_Id" column and "LastName"

column does not accept NULL values:

Create TABLE Persons

(

P_Id an int NOT NULL, the

The LastName varchar (255) NOT NULL,

The FirstName varchar (255)

Address varchar (255)

City varchar (255)

)

-------------------------------------------------- -----------------------

SQL UNIQUE constraint




UNIQUE constraint uniquely identifies the database table for each record.
UNIQUE and PRIMARY KEY constraints are column or set of columns

provides the only guarantee.

PRIMARY KEY has automatic definition of a UNIQUE constraint.




Please note that each table can have multiple UNIQUE constraints, but each

table can have only one PRIMARY KEY constraint.

SQL a UNIQUE Constraint on the Create TABLE




The following SQL P_Id "column in the" Persons "table created to create a

UNIQUE constraint:

MySQL:




Create TABLE Persons

(

P_Id an int NOT NULL, the

The LastName varchar (255) NOT NULL,

The FirstName varchar (255)

Address varchar (255)

City varchar (255)

A UNIQUE (P_Id)

)
SQL Server / Oracle / MS Access:




Create TABLE Persons

(

P_Id int NOT NULL UNIQUE,

The LastName varchar (255) NOT NULL,

The FirstName varchar (255)

Address varchar (255)

City varchar (255)

)




If you need to name a UNIQUE constraint, and define a UNIQUE constraint for

multiple columns, use the following SQL syntax:

MySQL / SQL Server / Oracle / an MS Access:




Create TABLE Persons

(

P_Id an int NOT NULL, the

The LastName varchar (255) NOT NULL,

The FirstName varchar (255)

Address varchar (255)

City varchar (255)
The CONSTRAINT uc_PersonID a UNIQUE (P_Id LastName)

)




Revocation of a UNIQUE constraint

To drop a UNIQUE constraint, use the following SQL:

MySQL:




Alter TABLE Persons

Drop INDEX uc_PersonID




SQL Server / Oracle / MS Access:




Alter TABLE Persons

Drop CONSTRAINT uc_PersonID

-------------------------------------------------- -----------------------

SQL PRIMARY KEY constraint




PRIMARY KEY constraint uniquely identifies the database table for each

record.

The primary key must contain unique values.

The primary key column can not contain NULL values.

Each table should have a primary key, and each table can have only one
primary key.

SQL PRIMARY KEY Constraint on the Create TABLE




The following SQL P_Id "column in the" Persons "table is created in the Create

a PRIMARY KEY constraint:

MySQL:




Create TABLE Persons

(

P_Id an int NOT NULL, the

The LastName varchar (255) NOT NULL,

The FirstName varchar (255)

Address varchar (255)

City varchar (255)

PRIMARY KEY (P_Id)

)




SQL Server / Oracle / MS Access:




Create TABLE Persons

(

P_Id int NOT NULL PRIMARY KEY,
The LastName varchar (255) NOT NULL,

The FirstName varchar (255)

Address varchar (255)

City varchar (255)

)




If you need to name PRIMARY KEY constraint, and define PRIMARY KEY

constraint to multiple columns, use the following SQL syntax:

MySQL / SQL Server / Oracle / an MS Access:




Create TABLE Persons

(

P_Id an int NOT NULL, the

The LastName varchar (255) NOT NULL,

The FirstName varchar (255)

Address varchar (255)

City varchar (255)

CONSTRAINT uc_PersonID PRIMARY KEY (Id_P, LastName)

)

SQL PRIMARY KEY Constraint on the Alter TABLE




If the table already exists "P_Id" column to create a PRIMARY KEY constraint,
use the following SQL:

MySQL / SQL Server / Oracle / an MS Access:




Alter TABLE Persons

ADD PRIMARY KEY (P_Id)




If you need to name PRIMARY KEY constraint, and define PRIMARY KEY

constraint to multiple columns, use the following SQL syntax:

MySQL / SQL Server / Oracle / an MS Access:




Alter TABLE Persons

ADD CONSTRAINT pk_PersonID PRIMARY KEY (Id_P, LastName)




Note: If you use the Alter TABLE statement to add primary key must declare

the primary key column does not contain a NULL value (when the table is first

created).

Revocation of PRIMARY KEY constraint




To drop a PRIMARY KEY constraint, use the following SQL:

MySQL:




Alter TABLE Persons
The Drop a PRIMARY KEY




SQL Server / Oracle / MS Access:




Alter TABLE Persons

Drop CONSTRAINT pk_PersonID




-------------------------------------------------- -----------------------

SQL FOREIGN KEY constraint

FOREIGN KEY in a table to point to the PRIMARY KEY in another table.




Please note that the "P_Id", "Orders" column point to the "Persons" table

"P_Id" column.

"Persons" table "P_Id" column is a PRIMARY KEY in the "Persons" table.

"Orders" table "P_Id" column "Orders" table in the FOREIGN KEY.

FOREIGN KEY constraints for the connection between the prevention of

destruction of the table action.

FOREIGN KEY constraints can also insert foreign key column, because it is

one of the values in the table that it points to prevent illegal data.




SQL FOREIGN KEY Constraint on the Create TABLE
The following SQL in the "Orders" table is created for the "P_Id" column to

create a FOREIGN KEY:

MySQL:




Create TABLE the Orders

(

O_Id int NOT NULL,

OrderNo an int NOT NULL, the

P_Id an int, and

PRIMARY KEY (O_Id),

FOREIGN KEY (P_Id) the REFERENCES Persons (P_Id)

)




SQL Server / Oracle / MS Access:




Create TABLE the Orders

(

O_Id int NOT NULL PRIMARY KEY,

OrderNo an int NOT NULL, the

P_Id an int FOREIGN KEY the REFERENCES Persons (P_Id)

)
If you need to name the FOREIGN KEY constraints, and defined for more than

one column FOREIGN KEY constraints, please use the following SQL syntax:

MySQL / SQL Server / Oracle / an MS Access:




Create TABLE the Orders

(

O_Id int NOT NULL,

OrderNo an int NOT NULL, the

P_Id an int, and

PRIMARY KEY (O_Id),

CONSTRAINT fk_PerOrders FOREIGN KEY (Id_P)

The REFERENCES Persons (P_Id)

)




SQL FOREIGN KEY Constraint on the Alter TABLE




If in the case of the "Orders" table already exists for P_Id "column to create a

FOREIGN KEY constraints, use the following SQL:

MySQL / SQL Server / Oracle / an MS Access:




Alter TABLE the Orders

ADD FOREIGN KEY (P_Id)
The REFERENCES Persons (P_Id)




If you need to name the FOREIGN KEY constraints, and defined for more than

one column FOREIGN KEY constraints, please use the following SQL syntax:

MySQL / SQL Server / Oracle / an MS Access:




Alter TABLE the Orders

ADD CONSTRAINT fk_PerOrders

FOREIGN KEY (P_Id)

The REFERENCES Persons (P_Id)




Revocation of FOREIGN KEY constraints




To drop the FOREIGN KEY constraints, please use the following SQL:

MySQL:




Alter TABLE the Orders

Drop FOREIGN KEY fk_PerOrders




SQL Server / Oracle / MS Access:




Alter TABLE the Orders
Drop CONSTRAINT fk_PerOrders

-------------------------------------------------- -----------------------

SQL the CHECK constraint




CHECK constraint is used to limit column in the range of values.




If the CHECK constraint defined on a single column, then the column only

allows certain value.




If you define a CHECK constraint on a table, then this constraint will restrict the

values in a specific column.

SQL the CHECK Constraint on the Create TABLE




The following SQL in the "Persons" table is created for the P_Id "column to

create a CHECK constraint. CHECK constraint requires P_Id "column must

contain only integer greater than 0.

The My the SQL:




Create TABLE Persons

(

P_Id an int NOT NULL, the

The LastName varchar (255) NOT NULL,
The FirstName varchar (255)

Address varchar (255)

City varchar (255)

CHECK (P_Id> 0)

)




SQL Server / Oracle / MS Access:




Create TABLE Persons

(

P_Id an int NOT NULL CHECK (P_Id> 0)

The LastName varchar (255) NOT NULL,

The FirstName varchar (255)

Address varchar (255)

City varchar (255)

)




If you need to name a CHECK constraint, and define a CHECK constraint to

multiple columns, use the following SQL syntax:

MySQL / SQL Server / Oracle / an MS Access:




Create TABLE Persons
(

P_Id an int NOT NULL, the

The LastName varchar (255) NOT NULL,

The FirstName varchar (255)

Address varchar (255)

City varchar (255)

CONSTRAINT chk_Person CHECK (Id_P> 0 AND City = 'Sandnes')

)




SQL CHECK Constraint on the Alter TABLE




If the table already exists "P_Id" column to create a CHECK constraint, use the

following SQL:

MySQL / SQL Server / Oracle / an MS Access:




Alter TABLE Persons

ADD CHECK (P_Id> 0)




If you need to name a CHECK constraint, and define a CHECK constraint to

multiple columns, use the following SQL syntax:

MySQL / SQL Server / Oracle / an MS Access:
Alter TABLE Persons

ADD CONSTRAINT chk_Person CHECK (Id_P> 0 AND City = 'Sandnes')




Revocation of CHECK constraints




To drop a CHECK constraint, use the following SQL:

SQL Server / Oracle / MS Access:




Alter TABLE Persons

Drop CONSTRAINT chk_Person

-------------------------------------------------- -----------------------

SQL the DEFAULT constraint




DEFAULT constraint is used to insert the default values in the column.

If you do not require other values, it will default to the value added to all new

record.

SQL the DEFAULT Constraint on the Create TABLE




The following SQL in the "Persons" table is created as the "City" column to

create a DEFAULT constraint:

My SQL / SQL Server / Oracle / an MS Access:
Create TABLE Persons

(

P_Id an int NOT NULL, the

The LastName varchar (255) NOT NULL,

The FirstName varchar (255)

Address varchar (255)

City varchar (255) DEFAULT 'in Sandnes'

)




Through the use of similar GETDATE () this function, DEFAULT constraint can

also be used to insert system values:




Create TABLE the Orders

(

Id_O int NOT NULL,

OrderNo an int NOT NULL, the

P_Id an int, and

The OrderDate the date DEFAULT GETDATE ()

)




SQL the DEFAULT Constraint on the Alter TABLE
If the table already exists for the "City" column to create a DEFAULT constraint,

use the following SQL:

MySQL:




Alter TABLE Persons

Alter City SET DEFAULT 'SANDNES'




SQL Server / Oracle / MS Access:




Alter TABLE Persons

Alter COLUMN City SET DEFAULT 'SANDNES'




Revoke DEFAULT constraint




To drop a DEFAULT constraint, please use the following SQL:

MySQL:

Alter TABLE Persons

The Alter the City the Drop the DEFAULT




SQL Server / Oracle / MS Access:

Alter TABLE Persons

Alter the COLUMN the City the Drop the DEFAULT
-------------------------------------------------- -----------------------

Create INDEX statement is used to create the index in the table.




Not read the entire table, index, database applications can quickly find data.

Index




You can create an index in the table, in order to more quickly and efficiently

query the data.




Users can not see the index, they can only be used to speed up the search /

query.




Note: to update a table that contains the index update a table with no indexes

more time, this is because the index itself also needs to be updated. Therefore,

the ideal approach is to create an index only in the column (and table) above

often search.

SQL Create the INDEX syntax




Create a simple index on the table. Allows the use of duplicate values:




Create the INDEX index_name The

ON table_name (column_name)
Note: "column_name is required by the columns of the index.

SQL Create a UNIQUE the INDEX syntax




Create a unique index on the table. Unique index means that the two lines can

not have the same index value.




Create UNIQUE INDEX index_name The

ON table_name (column_name)




Create the INDEX instance




The regular meeting to create a simple index, named "PersonIndex" LastName

column in the Person table:




Create the INDEX PersonIndex

ON Person (LastName)




If you wish to values in a column of descending indexes, you can add the

reserved word column name the DESC:




Create the INDEX PersonIndex
ON Person (LastName DESC)




If you want to index more than one column, you can list the name of the

column in parentheses, separated by commas:




Create the INDEX PersonIndex

ON Person (LastName, FirstName)

-------------------------------------------------- -----------------------

By using the Drop statement, you can easily remove indexes, tables and

databases.

The SQL the Drop INDEX statement




We can use the Drop the INDEX command to delete the index table.

Used to syntax Microsoft SQLJet (as well as Microsoft Access):




Drop the INDEX index_name The the ON table_name




For MS SQL Server syntax:




Drop INDEX table_name.index_name




For IBM DB2 and Oracle syntax:
The Drop the INDEX index_name The




For MySQL the syntax:




The Alter TABLE table_name Drop the INDEX index_name The




The SQL the Drop TABLE statement




Drop TABLE statement is used to delete the table (the table structure,

attributes, and indexes will be deleted):

Drop TABLE table name




The SQL the Drop DATABASE statement




Drop DATABASE statement to delete the database:

Drop DATABASE database name




SQL TRUNCATE TABLE statement

If we only need to remove the data within the table, but does not delete the

table itself, then we do it?

Please use the TRUNCATE TABLE command (just delete the data in tabular
form):

TRUNCATE TABLE table name

-------------------------------------------------- -----------------------

Alter TABLE statement




Alter TABLE statement is used to add, modify, or delete columns in existing

tables.

TABLE syntax of SQL the Alter




For more information about adding columns to tables, use the following syntax:




The Alter TABLE table_name

The ADD column_name is datatype




To delete a column in the table, use the following syntax:




The Alter TABLE table_name

The Drop the COLUMN column_name is




Note: Some database systems do not allow this to delete the column in the

database table (Drop the COLUMN column_name).
To change the data type column in the table, use the following syntax:




The Alter TABLE table_name

The Alter the COLUMN column_name is the datatype




Now we want to add a new column called "Birthday" in the "Persons" table.

We use the following SQL statement:

Alter TABLE Persons

The ADD Birthday date




Please note that the new column type of "Birthday" is the date, you can store

the date. Data type provides the type of data can be stored in the column.




Change the data type instance




Now we want to change the "Persons" table "Birthday" column data type.

We use the following SQL statement:

Alter TABLE Persons

Alter the COLUMN Birthday year




Please note that, "Birthday" column data type is the year, you can store two or
four year format.




Next, we remove the "Birthday" column in the "Person" table:

Alter TABLE the Person

The Drop the COLUMN Birthday

-------------------------------------------------- -----------------------

Auto-increment in the new record is inserted into the table to generate a

unique number.

AUTO INCREMENT fields is




We usually want to automatically create the primary key field value every time

you insert a new record.




We can create an auto-increment field in the table.

For the MySQL syntax




The following SQL statement in the "Persons" table "P_Id" column defines the

auto-increment primary key:




Create TABLE Persons

(

P_Id an int NOT NULL is the AUTO_INCREMENT
The LastName varchar (255) NOT NULL,

The FirstName varchar (255)

Address varchar (255)

City varchar (255)

PRIMARY KEY (P_Id)

)




MySQL AUTO_INCREMENT key tasks to perform auto-increment.




By default, the beginning of the value of the AUTO_INCREMENT is

incremented by 1 for each new record.




Let the AUTO_INCREMENT sequence start to other values, use the following

SQL syntax:




Alter TABLE Persons the AUTO_INCREMENT = 100




To insert a new record in the "Persons" table, we do not have to "P_Id" column

specified value (a unique value will be automatically added):




Insert an INTO Persons (FirstName, LastName)

VALUES ('Bill', 'Gates,')
The above SQL statement will insert a new record in the "Persons" table. "P_Id

will be given a unique value. "FirstName" will be set to "Bill", "LastName"

column would be set to the "Gates".




The syntax for SQL Server




The following SQL statement in the "Persons" table "P_Id" column defines the

auto-increment primary key:




Create TABLE Persons

(

P_Id int PRIMARY KEY the IDENTITY,

The LastName varchar (255) NOT NULL,

The FirstName varchar (255)

Address varchar (255)

City varchar (255)

)




MS SQL to use the IDENTITY keyword to perform auto-increment tasks.




Default, the start value of the IDENTITY is incremented by 1 for each new
record.




To require the "P_Id" column to the starting 20 and increments of 10, please

change the identity to IDENTITY (20,10)




To insert a new record in the "Persons" table, we do not have to "P_Id" column

specified value (a unique value will be automatically added):




Insert an INTO Persons (FirstName, LastName)

VALUES ('Bill', 'Gates,')




The above SQL statement will insert a new record in the "Persons" table. "P_Id

will be given a unique value. "FirstName" will be set to "Bill", "LastName"

column would be set to the "Gates".

For the syntax of Access




The following SQL statement in the "Persons" table "P_Id" column defines the

auto-increment primary key:




Create TABLE Persons

(

P_Id int PRIMARY KEY AUTOINCREMENT,,
The LastName varchar (255) NOT NULL,

The FirstName varchar (255)

Address varchar (255)

City varchar (255)

)




MS Access AUTOINCREMENT keyword task to perform auto-increment.




By default, the AUTOINCREMENT start value is 1, each new record is

incremented by 1.




To require P_Id "column increments of 10 and 20 start, the the autoincrement

changed the AUTOINCREMENT (20,10)




To insert a new record in the "Persons" table, we do not have to "P_Id" column

specified value (a unique value will be automatically added):




Insert an INTO Persons (FirstName, LastName)

VALUES ('Bill', 'Gates,')




The above SQL statement will insert a new record in the "Persons" table. "P_Id

will be given a unique value. "FirstName" will be set to "Bill", "LastName"
column would be set to the "Gates".

For Oracle syntax




In Oracle, the code a little more complicated.




You must pass the sequence to create auto-increment field (the object to

generate a sequence of numbers).




Please use the following Create the SEQUENCE syntax:




Create SEQUENCE seq_person

MINVALUE is specified 1

START WITH 1

INCREMENT BY, 1

CACHE 10




The above code creates a sequence object called seq_person, it is a start and

an increment. The object cache 10 values to improve performance. The

CACHE option specifies the order to improve the access speed to store the

number of sequence values.




To insert a new record, we must use of the nextval function (the function
seq_person sequence to retrieve the next value) "Persons" table:




Insert an INTO Persons (P_Id, FirstName, LastName)

VALUES (seq_person.nextval, 'Lars', 'Monsen')




The above SQL statement will insert a new record in the "Persons" table. P_Id

"assignment under a number from seq_person sequence. "FirstName" will be

set to "Bill", "LastName" column would be set to the "Gates".




-------------------------------------------------- -----------------------

SQL date




When we deal with the date, the most difficult task is probably to ensure that

the date format inserted to match with a date column in the database format.

As long as the data contains only the date portion, run a query will not go

wrong. If, however, with respect to time, the situation is a bit complicated.

Before discussion of the date of query complexity, we take a look at the most

important built-in date handler.




SQL Server a Date function




The following table lists the most important SQL Server built-in date functions:
Function describes the

GETDATE () returns the current date and time

The DATEPART () returns a separate part of the date / time

DATEADD () in the date to add or subtract a specified time interval

DATEDIFF () returns the time between two dates

CONVERT () with a different format to display date / time




SQL Server uses the following data types are stored in the database date or

date / time value:




      * DATE - format YYYY-MM-DD

      * DATETIME - format: YYYY-MM-DD HH: MM: SS

      * A SMALLDATETIME - format: YYYY-MM-DD HH: MM: SS

      * TIMESTAMP - Format: unique number

-------------------------------------------------- -----------------------

NULL value is missing and unknown data.




By default, the table column can store NULL values.




This chapter explains the IS NULL and IS NOT NULL operator.

SQL NULL value
If a column in the table is optional, then we can add value to the column to

insert new records or update existing records. This means that the field will be

NULL value preservation.




NULL value of the approach is different from other values.




NULL used as a placeholder for unknown or not applicable values.




Note: can not be compared to NULL and 0; they are not equivalent.




So how do we test for NULL values?

Can not be used to test the NULL value comparison operators, such as =, <, or

<>.

We must use the IS NULL and IS NOT NULL operator.




How do we select with a NULL value recorded in the "Address" column?

We must use IS NOT NULL operator:




Select LastName, FirstName, Address the FROM Persons

Where the Address IS NOT NULL




-------------------------------------------------- -----------------------
Microsoft Access data types




Data type descriptions are stored

Text used for text or text and numbers. Up to 255 characters.

Memo Memo for a greater number of the text. Store up to 65,536 characters.

Note: Can not sort memo field. However, they are searchable.

Byte allows the figures of 0-255. 1 byte

The Integer allows between a number between -32,768 to 32,767. 2 bytes

Long allows all numbers between -2,147,483,648 and 2,147,483,647 4 bytes

Single single-precision floating point. Handles most of the decimal. 4-byte

Double A double-precision floating point. Handles most of the decimal. 8-byte

Currency used for currency. Support for 15 yuan, plus four decimal places. Tip:

You can choose which country's currency. 8-byte

The AutoNumber the AutoNumber field automatically for each record is

assigned a number, usually from a 4-byte

Date / Time 8 bytes for the date and time

Yes / No logical fields can be displayed as Yes / No, True / False or On / Off. In

the code, use the constants True and False (equivalent to 1 and 0) Comments:

Yes / No field does not allow Null value a bit




Ole the Object can store photos, audio, video or other BLOBs (the Binary the

Large OBjects) up to 1GB
The Hyperlink contain links pointing to other documents, including the web.

Lookup Wizard allows you to create a list of options to choose from the

following list. 4-byte




Not the whole ...

==================================================

==================================================

=========================

==================================================

==================================================

=========================

MIN () MAX () function




The MIN function returns the minimum value of one. NULL values are not

included in the calculation.

SQL MIN () syntax

Select MIN (column_name is) FROM table_name




Note: MIN and MAX can be used for columns of text in order to obtain

alphabetical order highest or lowest value.




Now, we want to find the minimum value of the "OrderPrice" column.
We use the following SQL statement:




Select MIN (OrderPrice) AS SmallestOrderPrice FROM Orders

-------------------------------------------------- -----------------------

AVG the definition and usage




The AVG function returns the average of the numeric column. NULL values

are not included in the calculation.

SQL AVG () syntax




The Select the AVG (column_name is) FROM table_name




Now, we want to calculate the average of the the "OrderPrice fields.

We use the following SQL statement:

Select AVG (OrderPrice) AS OrderAverage FROM Orders




Now we want to find OrderPrice value than OrderPrice the average customer.

We use the following SQL statement:

The Select the Customer FROM Orders

Where OrderPrice> (Select AVG (OrderPrice) FROM Orders)

-------------------------------------------------- -----------------------

COUNT () function returns the number of rows matching the specified criteria.
SQL the COUNT () syntax

SQL COUNT (column_name is) syntax




COUNT (column_name) function returns the number of values to specify the

column (NULL are not included):




The Select the COUNT (column_name is) FROM table_name




SQL COUNT (*) syntax.




COUNT (*) function returns the number of records in the table:




Select the COUNT (*) FROM table_name




The syntax of the SQL COUNT (DISTINCT column_name is)




COUNT (DISTINCT column_name) function returns the number of different

values of the specified column:




Select COUNT (DISTINCT column_name) FROM table_name




Note: COUNT (DISTINCT) for the ORACLE and Microsoft SQL Server, but can
not be used in Microsoft Access.




-------------------------------------------------- -----------------------

FIRST (), LAST () function




The FIRST () function returns the specified field in a record value.




Tip: You can use the ORDER BY statement to sort the records.

SQL FIRST () syntax




The Select the FIRST (column_name is) FROM table_name




Now, we want to find a value of "OrderPrice" column.

We use the following SQL statement:

Select FIRST (OrderPrice) AS FirstOrderPrice FROM Orders

-------------------------------------------------- -----------------------

SUM () function




The SUM function returns the value out of a total (gross).

SQL the SUM () syntax




The Select the SUM (column_name) FROM table_name
Now, we want to find the total number of the "OrderPrice fields.

We use the following SQL statement:

The Select SUM (OrderPrice,) AS OrderTotal the FROM the Orders

-------------------------------------------------- -----------------------

GROUP BY statements




The GROUP BY statement is used in conjunction with aggregate functions

grouped under one or more columns of the result set.

The SQL the GROUP BY syntax




Select column_name is, aggregate_function The (column_name)

FROM table_name

Where column_name is the operator value

GROUP BY, column_name is




Now we want to find each customer the total amount (total order).




We want to use GROUP BY statement to combine the customer.




We use the following SQL statement:
The Select the Customer, SUM (OrderPrice,) the FROM the Orders

GROUP BY, the Customer




-------------------------------------------------- -----------------------

HAVING clause




Increase in SQL HAVING clause because the Where keyword can not be used

together with the aggregate function.

SQL the HAVING syntax




Select column_name is, aggregate_function The (column_name)

FROM table_name

Where column_name is the operator value

GROUP BY, column_name is

HAVING aggregate_function The (column_name is) the operator of value




Now we want to find customers "Bush" or "Adams," has more than 1500 orders

for the total amount of.




In an ordinary SQL statement Where clause:




The Select the Customer, SUM (OrderPrice,) the FROM the Orders
Where the Customer = 'Bush,' OR, the Customer = 'Adams,'

GROUP BY, the Customer

HAVING SUM (OrderPrice)> 1500




-------------------------------------------------- -----------------------

UCASE () function




The UCASE function field values are converted to uppercase.

SQL UCASE () syntax

The Select UCASE (column_name is) FROM table_name




Now we want to select the column "LastName" and "FirstName", "LastName"

column converted to uppercase.




We use the following SQL statement:




The Select UCASE (LastName) as LastName, FirstName, the FROM Persons

-------------------------------------------------- -----------------------

MID () function




The MID function is used to extract characters from a text field.

SQL MID () syntax
Select MIDs (column_name is, start [, length]) FROM table_name




Parameter Description

The column_name necessary. To extract a character field.

start necessary. Provides for the start position (start value is 1).

length optional. To return the number of characters. If omitted, the MID ()

function returns the remainder of the text.




Now we want to extract the first three characters from the "City" column.




We use the following SQL statement:




Select MID (City, 1,3) as SmallCity FROM Persons

-------------------------------------------------- -----------------------

ROUND () function




The ROUND function for numeric fields rounded to the specified number of

decimal places.

SQL ROUND () syntax




The Select the ROUND (column_name, decimals) FROM table_name
Parameter Description

The column_name necessary. Rounding the field.

The decimals necessary. Required to return the number of decimal places.




Now we want to name and price rounded to the nearest whole number.

We use the following SQL statement:

Select ProductName, the ROUND (UnitPrice) as UnitPrice FROM Products

-------------------------------------------------- -----------------------

LEN () function




The LEN function returns the length of the value in the text field.

The SQL the LEN () syntax

Select LEN (column_name is) FROM table_name




Now we want to obtain the length of the value in the "City" column.

We use the following SQL statement:

Select LEN (City) as LengthOfAddress FROM Persons

-------------------------------------------------- -----------------------

FORMAT () function




FORMAT function is used to format the display of fields.
SQL FORMAT () syntax

Select FORMAT (column_name is format) FROM table_name




Now we want to show every day the date corresponding to the name and price

(the date format is YYYY-MM-DD ").

We use the following SQL statement:

Select ProductName, UnitPrice, FORMAT (Now (), 'YYYY-MM-DD') as

PerDate

FROM Products

				
DOCUMENT INFO
Shared By:
Tags:
Stats:
views:30
posted:2/14/2012
language:English
pages:78
Description: Classic SQL tutorial, let you simple master SQL data knowledge, is a comprehensive and clear tutorial.