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 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 =, .
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