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:
Operator | Desc |
= | equal |
<> | not equal |
> | greater than |
<</td> | less than |
>= | |
<= | |
between | between an inclusive range |
like | pattern 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:
Operator | Meaning |
*= | 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