Sybase's data manipulation language

1. SELECT statement

Basic syntax:

SELECT[ALL|DISTINCT] Field List

[Into table name]

[From table name]

[where-condition expression]

[GROUP by [All] field list]

[Having filter expression]

[Order By field List [Asc|desc]]

[Compute aggregate Function list [by field List]]

NOTE: The clauses in the SELECT statement must be used in the order described above. In other words, if the statement includes a GROUP BY clause and an ORDER BY clause where,group BY clause must be placed before the ORDER BY clause.

The HAVING clause is similar to a WHERE clause, where there are two points: (1) The HAVING clause must be used in conjunction with the GROUP BY clause, and (2) Where clause cannot use a clustered function and a HAVING clause can.

The general usage of select is described below by an example.

Example 1: Select all columns, syntax for SELECT * FROM Table_list

For example: SELECT * FROM Publishers

Example 2: Select the specified column, syntax is

Select Column_name[,column_name] ...

From table_name

For example, select Pub_id,pub_name from Publishers

Example 3: Renames the column in the query result, syntax is

Select Column_heading= column_name

From table_name

such as: Select publisher=pub_name,pub_id

From publishers

Example 4:select a calculated value in a list that calculates the numeric data in a select list, and the arithmetic operators are listed below.

Symbolic operations

+ Plus

-Minus

/except

* Multiply

% modulo

such as select Title_id,total_sales,total_sales*2 from titles

Example 5: Use distinct to eliminate duplicate query results

An optional keyword eliminates duplicate rows in the results of a SELECT statement. If you do not specify DISTINCT, the default value is all, and all row data that contains duplicate rows is retrieved.

For example: SELECT DISTINCT au_id from titleauthor

Example 6: Select a row--where statement

The criteria for specifying which rows to retrieve are specified in the SELECT statement, and the general format is:

Select select_list from table_list where search_conditions

Search conditions (or limits) in the WHERE clause include:

• comparison operator (=, <,>,!=, etc. =

such as: where Advance*2>total_sales*price

• Range (between and not between)

such as: where Total_sales between 5000 and 10000

• List (in and not in)

such as: Where State in ("CA", "in", "MD")

• Matching characters (like and not)

such as: Where phone like "535%"

• Unknown value (is null and is not NULL)

such as: where advance is null

• Combination of the above (and, or)

such as: where advance<5000 or total_sales between 1000

Example 7: Summarizing query results with aggregate functions

Aggregate functions compute a summary value with data from a specific column.

Aggregate function results

Summation of sum ([all|distinct]expression) values in columns (not duplicates)

AVG ([all|distinct]expression) value in columns (not duplicates)

Count ([all|distinct]expression) columns (not duplicates) number of non-null values

Number of rows selected by Count (*)

Max (expression) expression max value

MIN (expression) expression minimum value

such as: Select AVG (advance), SUM (total_sales)

From titles

where type= "as"

Select COUNT (*) from titles

Select AVG (distinct price) from titles

Select Max from books

Example 8: Grouping organization query results--group BY clause

The GROUP BY clause divides a table into groups in a SELECT statement.

For example: Select Type, advance from titles group by type

Example 9: Select the grouped data--having clause

Having sets the condition for the GROUP BY clause, as in where the SELECT statement is set. Having search conditions are the same as where, but having can include aggregate functions, where the where cannot be included.

The following statement uses an example of a HAVING clause with a set function. It groups the rows in the title table by type, but removes the grouping that contains only one book.

Select type from titles group by type has count (*) >1

The following is an example of a HAVING clause with no aggregate function. It groups the rows in the title table by type, but removes those types that do not begin with the letter "P".

Select type from titles group by type has type like "p%"

Example 10: Query result sort--order BY clause

The ORDER BY clause allows you to sort query results by one or more columns. Each sort can be ascending (ASC) or Descending (DESC). If not specifically specified, in ascending order. The following query returns the results sorted by pub_id:

Select pub_id,type,title_id from the titles by pub_id

Example 11: Connection-Retrieving data from multiple tables

Connecting two or more tables is a procedure that compares the data in a specified field, and makes a new table with qualifying rows based on the comparison.

Example:

Select publishers.pub_id,publishers.pub_name,authors.*

From Publishers,authors

where publishers.city=authors.city

Example 12: Grouped calculation clauses

Compute is Sybase's expansion of the group clause in the SQL standard, which can be viewed as a group clause with a clustered computation. For example:

Select type,price,advance

From titles

ORDER BY Type

Compute sum (price), sum (advance) by type

2. Insert statement

There are two ways to add rows to a database with the INSERT command: Use keyword values or use a SELECT statement.

The basic syntax for an INSERT statement is:

Insert[into] Table name [(Field list)]

{VALUES (value list) |select_statement}

Example: INSERT INTO Publishers

VALUES (' 1622 ', ' jardin,inc ', ' Camden ', ' NJ ')

Insert into Publishers (Pub_id,pub_name)

VALUES (' 1756 ', ' the Health Center ')

Insert Authors SELECT * FROM Newauthors

Insert authors (Au_id,address,au_lname,au_fname)

Select * from Newauthors

3. Delect statement

Delect can operate on one or more lines.

The basic syntax for the Delect statement is:

Delect Table Name

[From table Name list]

[where-condition expression]

Example: Delect Publishers

where Pub_name= "Jardin,inc."

Delect titles

From authors, titles

Where titles.title_id=authors.title_id

4. UPDATE statement

You can use the Update command to change a single row, a group of rows, or all rows in a table.

The basic syntax for the UPDATE statement is:

Update table name

Set column_name1={expression1|null| ( select_statement)}

[, Column_name2={expression2|null| ( select_statement)}]

[......]

[From table Name list]

[where-condition expression]

Example:

Update authors Set_au_lname= "Health", aufname= "Goodbody"

where au_lname= "Bloth"

Update titles

Set Total_sales=total_sales Qty

From Titles,sales

where titles.title_id=sales.title_id