I'm using SQL for data manipulation. Some of the processes are shared here.
Thanks to Alex
- Finding Data Queries.
- Data Modification Queries.
- Reporting Queries.
- Join Queries.
- View Queries.
- Altering Table Queries.
- Creating Table Query.
- Finding Data Queries
SELECT
*FROM
table_name;
SELECT DISTINCT
column_name;
SELECT
column1, column2FROM
table_nameWHERE
condition;SELECT
*FROM
table_nameWHERE
condition1AND
condition2;SELECT
*FROM
table_nameWHERE
condition1OR
condition2;SELECT
*FROM
table_nameWHERE NOT
condition;SELECT
*FROM
table_nameWHERE
condition1AND
(condition2OR
condition3);SELECT
*FROM
table_nameWHERE EXISTS
(SELECT
column_nameFROM
table_nameWHERE
condition);
SELECT
*FROM
table_nameORDER BY
column;SELECT
*FROM
table_nameORDER BY
columnDESC
;SELECT
*FROM
table_nameORDER BY
column1ASC
, column2DESC
;
SELECT TOP
number columns_namesFROM
table_nameWHERE
condition;SELECT TOP
percent columns_namesFROM
table_nameWHERE
condition;- Not all database systems support
SELECT TOP
. The MySQL equivalent is theLIMIT
clause SELECT
column_namesFROM
table_nameLIMIT
offset, count;
- % (percent sign) is a wildcard character that represents zero, one, or multiple characters
- _ (underscore) is a wildcard character that represents a single character
SELECT
column_namesFROM
table_nameWHERE
column_nameLIKE
pattern;LIKE
‘a%’ (find any values that start with “a”)LIKE
‘%a’ (find any values that end with “a”)LIKE
‘%or%’ (find any values that have “or” in any position)LIKE
‘_r%’ (find any values that have “r” in the second position)LIKE
‘a_%_%’ (find any values that start with “a” and are at least 3 characters in length)LIKE
‘[a-c]%’ (find any values starting with “a”, “b”, or “c”
- essentially the IN operator is shorthand for multiple OR conditions
SELECT
column_namesFROM
table_nameWHERE
column_nameIN
(value1, value2, …);SELECT
column_namesFROM
table_nameWHERE
column_nameIN
(SELECT STATEMENT
);
SELECT
column_namesFROM
table_nameWHERE
column_nameBETWEEN
value1AND
value2;SELECT
*FROM
ProductsWHERE
(column_nameBETWEEN
value1AND
value2)AND NOT
column_name2IN
(value3, value4);SELECT
*FROM
ProductsWHERE
column_nameBETWEEN
#01/07/1999# AND #03/12/1999#;
SELECT
*FROM
table_nameWHERE
column_nameIS NULL
;SELECT
*FROM
table_nameWHERE
column_nameIS NOT NULL
;
SELECT
column_nameAS
alias_nameFROM
table_name;SELECT
column_nameFROM
table_nameAS
alias_name;SELECT
column_nameAS
alias_name1, column_name2AS
alias_name2;SELECT
column_name1, column_name2 + ‘, ‘ + column_name3AS
alias_name;
- Each SELECT statement within UNION must have the same number of columns
- The columns must have similar data types
- The columns in each SELECT statement must also be in the same order
SELECT
columns_namesFROM
table1UNION SELECT
column_nameFROM
table2;UNION
operator only selects distinct values,UNION ALL
will allow duplicates
INTERSECT: set operator which is used to return the records that two SELECT statements have in common
- Generally used the same way as UNION above
SELECT
columns_namesFROM
table1INTERSECT SELECT
column_nameFROM
table2;
EXCEPT: set operator used to return all the records in the first SELECT statement that are not found in the second SELECT statement
- Generally used the same way as UNION above
SELECT
columns_namesFROM
table1EXCEPT SELECT
column_nameFROM
table2;
- The
ANY
operator returns true if any subquery values meet the condition - The
ALL
operator returns true if all subquery values meet the condition SELECT
columns_namesFROM
table1WHERE
column_name operator (ANY
|ALL
) (SELECT
column_nameFROM
table_nameWHERE
condition);
GROUP BY: statement often used with aggregate functions (COUNT, MAX, MIN, SUM, AVG) to group the result-set by one or more columns
SELECT
column_name1, COUNT(column_name2)FROM
table_nameWHERE
conditionGROUP BY
column_name1ORDER BY
COUNT(column_name2) DESC;
HAVING: this clause was added to SQL because the WHERE keyword could not be used with aggregate functions
SELECT
COUNT
(column_name1), column_name2FROM
tableGROUP BY
column_name2HAVING
COUNT(
column_name1)
> 5;
WITH: often used for retrieving hierarchical data or re-using temp result set several times in a query. Also referred to as "Common Table Expression"
WITH RECURSIVE
cteAS
(
SELECT
c0.*FROM
categoriesAS
c0WHERE
id = 1# Starting point
UNION ALL
SELECT
c1.*FROM
categoriesAS
c1JOIN
cteON
c1.parent_category_id = cte.id
)
SELECT
*
FROM
cte
INSERT INTO
table_name (column1, column2)VALUES
(value1, value2);INSERT INTO
table_nameVALUES
(value1, value2 …);
UPDATE
table_nameSET
column1 = value1, column2 = value2WHERE
condition;UPDATE
table_nameSET
column_name = value;
DELETE FROM
table_nameWHERE
condition;DELETE
*FROM
table_name;
SELECT COUNT (DISTINCT
column_name)
;
SELECT MIN (
column_names) FROM
table_nameWHERE
condition;SELECT MAX (
column_names) FROM
table_nameWHERE
condition;
SELECT AVG (
column_name) FROM
table_nameWHERE
condition;
SELECT SUM (
column_name) FROM
table_nameWHERE
condition;
SELECT
column_namesFROM
table1INNER JOIN
table2ON
table1.column_name=table2.column_name;SELECT
table1.column_name1, table2.column_name2, table3.column_name3FROM
((table1INNER JOIN
table2ON
relationship)INNER JOIN
table3ON
relationship);
LEFT (OUTER) JOIN: returns all records from the left table (table1), and the matched records from the right table (table2)
SELECT
column_namesFROM
table1LEFT JOIN
table2ON
table1.column_name=table2.column_name;
RIGHT (OUTER) JOIN: returns all records from the right table (table2), and the matched records from the left table (table1)
SELECT
column_namesFROM
table1RIGHT JOIN
table2ON
table1.column_name=table2.column_name;
SELECT
column_namesFROM
table1FULL OUTER JOIN
table2ON
table1.column_name=table2.column_name;
SELECT
column_namesFROM
table1 T1, table1 T2WHERE
condition;
CREATE VIEW
view_nameAS SELECT
column1, column2FROM
table_nameWHERE
condition;
SELECT
*FROM
view_name;
DROP VIEW
view_name;
ALTER TABLE
table_nameADD
column_name column_definition;
ALTER TABLE
table_nameMODIFY
column_name column_type;
ALTER TABLE
table_nameDROP COLUMN
column_name;
CREATE TABLE
table_name(
column1
datatype
,
column2
datatype
,
column3
datatype
,
column4
datatype
,
);