Print service provided by iDogiCat: http://www.idogicat.com/
home logo





Home > IT > Programming > SQL > Sql In Brief

Sql In Brief

by iDog

SQL: Structured Query Language

  • DML (Data Manipulation Language):
    • select
    • update
    • delete
    • insert into
  • DDL (Date Definition Language):
    • create table
    • alter table
    • drop table
    • create index
    • drop index

DML

select [distinct] field_name1[,  [, ...]] from table_name
where condition

Operators used in where clause:

OperatorDesc
=equal
<>not equal
>greater than
<</td>less than
>= 
<= 
betweenbetween an inclusive range
likepattern matching

Wild card in 'like' pattern: %

insert into table_name
[(field_name1 [, field_name2 [, ...]] ]
values (value1 [, value2 [, ...]])

update table_name set
field_name1 = value1
[,field_name2 = value2 [, ...]]
where condition

delete from table_name
[where condition]

Deletes all rows in the table if no where clause.

select ...
order by field_name1 [, field_name2 [, ...]] [asc|desc]

  • asc - ascending order
  • desc - descending order

where clause:

condition1 {and|or} condition2

field_name in (value1 [, value2 [, ...]])

field_name between (value1, value2)

aliases:

select field_name as field_alias
from table_name as table_alias

select into:

select field_list into new_table_name [in another_db]
from table
[where condition

Join:

select field_list
from table1
{inner|left|right} join table2
on table1.key_field_name = table2.foreign_key_field_name

  • inner join returns all rows from both tables where there is a match. If there are rows in one table that do not have matches in the other table, those rows will not be listed.
  • outer join
    • left join returns all the rows from the first table, no matter if there are matches in the second table or not. If there is no match, the rows are still listed, with fields in second table being empty.
    • right join returns all the rows from the second table, no matter if there are matches in the first table or not. If there is no match, the rows are still listed, with fields in first table being empty.

[NOTE] Besides supporting ANSI SQL standard, Sybase also supports a convenient way of doing outer joins: using '*=' or '=*' operators:

OperatorMeaning
*=Include in the results all the rows from the first table, not just the ones where the joined columns match.
=*Include in the results all the rows from the second table, not just the ones where the joined columns match.

In short, if the '*' is at the left side of '=', then it's a left join, otherwise, it's a right join.

union: select distinct values from two queries. all selected columns need to be of the same data type.

union all: selects all values.

sql_statement1 union sql_statement2

sql_statement1 union all sql_statement2

group by: used when we are selecting multiple columns from a table or tables and at least one arithematic operator appears in the select statement. In this case, we need to group by all the other selected columns, i.e., all columns except the one(s) operated on by the arithematic operator.

select staff_name, sum(sales)
from work_rec
group by staff_name

having clause is similar with where clause, but it is used for aggregate functions. where clause cannot contain aggregate functions.

select staff_name, title, sum(sales)
from work_rec
group by staff_name, title
having sum(sales) >= 10000

sub-query:

select sum(sales) from work_rec
where staff_name in (
   select staff_name from employees
)

select id, name, 
        (select sum(amount)
         from sales
         where sales.id = employees.id)
from employees
where role = 'Sales'
order by id

select id, name
from employees
where id = (
        select id
        from sales
        having amount = (select max(amount) from sales)
)

DDL

create database db_name

create table table_name (
   field_name1 data_type1,
   field_name2 data_type2,
...
)

create view view_name [col_name_list] as select_statement

create [unique] index index_name
on table_name (field_name_list [desc])

drop database db_name

drop table table_name

drop index table_name.index_name

truncate table table_name

alter table table_name {add|drop} field_name data_type