[DB/SQL] DML

SELECT command

base way how to use SELECT command is,

SELECT <field name>, <field name>, ..., FROM <table name>;

Like this, finding records by selecting certain field, we call it Projection. Then, how to find records of all of columns? We can use *.

SELECT * FROM <table name>

eleminate duplicate entry

use DISTINCT keyword.

SELECT DISTINCT <field name> FROM <table name>

calculated field

when you use SELECT, you can project new column by entering certain mathematical expression even though there are not the column in the table. When you do this, vertual table named DUAL stores those columns you made.

SELECT (5*10) + 2 FROM DUAL;
SELECT (5*10) + 2;// FROM DUAL is default

alias

you can temporarily change columns name when you show records by SELECT. You just need to add AS keyword.

SELECT <field name> <field name>*10 AS <new name> FROM <table name>;

sort

you can temporarily sort records by ORDER BY keyword.

SELECT <field name> FROM <table name> ORDER BY <field name you want to sort> <DESC or ASC>;

limit

when you print records, you can limit the amount of data by LIMIT keyword.

SELECT * FROM <table name> LIMIT <number>;

filtering

you can add conditions at WHERE phrase.

SELECT * FROM <table name> WHERE <conditions ex: id=155>;

IS, IN, BETWEEN…AND, LIKE

those operators are frequently used.

1. IS
   • compare with TRUE, FALSE, UNKNOWN.
2. IN
   • compare with set of values in (). cannot compare NULL or set which is mixed with strings and numbers.
3. BETWEEN <min> AND <max>
   • extract records which satisfies certain range
4. LIKE
   • extract records with wild card(% responding to every string or _ responding to one single string)

The End.