Some
of the frequently used SQL Commands
SELECT
- extracts data from a database
Syntax:
SELECT
* FROM table_name;
SELECT
TOP - specifies the number of records to return
SQL
Server Syntax:
SELECT
TOP number|percent column_name(s)
FROM
table_name;
MySQL
Syntax:
SELECT
*
FROM
table_name LIMIT
number;
Oracle
Syntax:
SELECT
column_name(s)
FROM
table_name WHERE
ROWNUM <= number;
DISTINCT
- returns only distinct values
Syntax:
SELECT
DISTINCT column_name,column_name
FROM
table_name;
WHERE
- extracts only those records that fulfill a specified criterion
Syntax:
SELECT
* FROM
table_name
WHERE
column_name operator value;
LIKE
- searches for a specified pattern in a column
Syntax:
SELECT
* FROM
table_name
WHERE
column_name LIKE pattern;
IN -
specifies multiple values in a WHERE clause
Syntax:
SELECT
* FROM
table_name
WHERE
column_name IN (value1,value2,...);
AND
& OR - filters records based on more
than one condition
AND
Syntax:
SELECT
* FROM table_name
WHERE
column_name operator value
AND
column_name operator value;
OR
Syntax:
SELECT
* FROM table_name
WHERE
column_name operator value
OR
column_name operator value;
BETWEEN
- selects values within a range. The values can be numbers, text, or dates.
Syntax:
SELECT
column_name(s) FROM
table_name
WHERE
column_name BETWEEN value1 AND value2;
NOT
BETWEEN - displays the values outside the range
Syntax:
SELECT
* FROM
table_name
WHERE
column_name NOT BETWEEN value1 AND value2;
WILDCARDS
- substitute for any other character(s) in a string
% - A substitute for zero or more characters
Syntax:
SELECT
* FROM table_name
WHERE
column_name LIKE '%xyz%';
returns
all values for selected column containing the pattern "xyz"
_ (underscore) - A substitute for a single character
Syntax:
SELECT
* FROM table_name
WHERE
column_name LIKE 'L_n_on';
returns
all values for selected column starting with "L", followed by any
character, followed by "n", followed by any character, followed by
"on"
[charlist]
- Sets and ranges of characters to match
Syntax:
SELECT
* FROM table_name
WHERE
column_name LIKE '[xyz]%';
returns
all values for selected column starting with "x", "y", or
"z"
or
SELECT
* FROM table_name
WHERE
column_name LIKE '[x-z]%';
returns
all values for selected column starting with "x", "y", or
"z"
[^charlist]
or [!charlist] - Matches only a character NOT specified within the brackets
Syntax:
SELECT
* FROM table_name
WHERE
column_name LIKE '[!xyz]%';
or
SELECT
* FROM table_name
WHERE
column_name NOT LIKE '[xyz]%';
returns
all values for selected column NOT starting with "x", "y",
or "z"
or
SELECT
* FROM table_name
WHERE
column_name LIKE '[^a-c]%';
returns
all values for selected column NOT starting with "a", "b",
or "c"
ORDER
BY - sorts the result-set by one or more columns
Syntax:
SELECT
column_name, column_name FROM
table_name
ORDER
BY column_name ASC|DESC, column_name ASC|DESC;
UPDATE
- updates data in a database
Syntax:
UPDATE
table_name SET
column1=value1,column2=value2,...
WHERE
some_column=some_value;
DELETE
- deletes data from a database
Syntax:
UPDATE
table_name SET
column1=value1,column2=value2,...
WHERE
some_column=some_value;
INSERT
INTO - inserts new data into a database
Syntax:
INSERT
INTO table_name
VALUES
(value1,value2,value3,...);
ALIASES
- gives a database table, or a column in a table, a temporary name
Syntax:
SELECT
column_name AS alias_name
FROM
table_name;
or
SELECT
column_name1 alias_name1, column_name2 alias_name2, ...
FROM
table_name;
No comments:
Post a Comment