DML – Data Manipulation Language
Create table:
CREATE TABLE nameoftable(id INTEGER PRIMARY KEY, name TEXT);
Add a row:
INSERT INTO nameoftable (nameOfcolumn1,2,3) VALUES (new1,new2,new3);View the row you just created:
SELECT * FROM nameoftable;Edit information in the table (for only existing records):
UPDATE nameofthetable
SET parameter = 22
WHERE id = 1;
Add new column in the table:
ALTER TABLE nameoftable ADD COLUMN nameofcolumn TEXT;
Delete rows with no value:
DELETE FROM name of t WHERE nameofcolumn IS NULL;
DELETE FROM nameoftable;
DROP TABLE IF EXISTS;
CREATE TABLE IF NOT EXISTS;
Return uniq values:
SELECT DISTINCT name of column FROM name of t;
Filter the result in rows only if condition is true:
SELECT * FROM nameoftable WHERE name of column > 8;
Operators:
= equals
!= not equals
> greater than
< less than
>= greater than or equal to
<= less than or equal to
Filter the result to compare similar values:
SELECT * FROM name of t WHERE name of column LIKE ‘set here your text parameter’;
i.g. ‘Se_en’ or ‘A%’ (begins with “a”) or ‘%a’ (ends with “a”) or %man% (contains this word)
SELECT * FROM name of t WHERE name of column BETWEEN ‘A’ AND ‘J’; (not incl ‘J”)
or BETWEEN 1990 AND 2000; (incl all years)
Combine operator AND:
SELECT * FROM movies
WHERE year BETWEEN 1990 and 2000
AND genre = ‘comedy’;)
OR operator:
SELECT * FROM movies
WHERE genre = ‘comedy’
OR year < 1980;
Sort the result:
SELECT * FROM name of t
ORDER BY name of column DESC;
DESC — from high to low, ASC — low to high
Limiting the results:
SELECT * FROM name of t
ORDER BY name of column ASC
LIMIT 3;
Functions
Calculate the number of rows:
SELECT COUNT(*) FROM nameoftable;
To combine the rows
CONCAT ( || ‘somethting’ ||):
SELECT *, CONCAT(age, name, salary) as concat FROM name of table;
SELECT ‘abc ‘ || ‘def’ || ‘ gh ‘ FROM DUAL;
Round to a whole number:
SELECT ROUND(column name, decimals кол-во знаков после запятой) from nameoftable;
SELECT ROUND(345.156, 2);
If null return your value:
SELECT name, NVL(id, 0) FROM nameoftable;
SUM of all the values in that column:
SELECT SUM(nameofcolumn) from nameoftabl;
Find the largest value in a column:
SELECT MAX(nameofcolumn) FROM nameoftabl;
Find the minimum value in a column:
SELECT MIN(nameofcolumn) FROM nameoftabl;
Find average value:
SELECT AVG(namecolumn) FROM nameoftable;
GROUP BY
is using with COUNT, MIN, MAX, AVG, SUM
AS — rename a column
Multiple tables join
SELECT * FROM nameoftable1 JOIN nameoftable2 ON not1.id = not2.id;
- left JOIN — every row in the lefttable is returned in the result set
- right JOIN — every in the right
- inner join=join
Union
merge the columns
SELECT column_name(s) FROM table1 UNION SELECT column_name(s) FROM table2;
The UNION ALL clause allows us to utilize information from multiple tables in our queries, including duplicate values.
INTERSECT is used to combine two SELECT statements, but returns rows only from the first SELECT statement that are identical to a row in the second SELECT statement.
EXCEPT returns distinct rows from the first SELECT statement that aren’t output by the second SELECT statement
Dates
DATEDIFF
DATE_ADD(date, interval 45, DAY)
SELECT name_city, name_author, DATE_ADD('2020-01-01', INTERVAL (RAND() * 365) DAY) AS Date FROM city, author ORDER BY 1, Date DESC
| Function | how to use | example |
| count(x) | ||
| sum | ||
| max | ||
| CEILING(x) | CEILING(4.2)=5 CEILING(-5.8)=-5 | |
| ROUND(x, k) | ROUND(4.361)=4 ROUND(5.86592,1)=5.9 | |
| FLOOR(x) | FLOOR(4.2)=4 FLOOR(-5.8)=-6 | |
| POWER(x, y) | POWER(3,4)=81.0 | |
| SQRT(x) | SQRT(4)=2.0 SQRT(2)=1.41… | |
| ABS(x) | ABS(-1) = 1 ABS(1) = 1 | |
| RAND() | random | |
