1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

-- functions
REPEAT('@', 10) UPPER() LOWER()
CURDATE() DAY(CURDATE()) NOW()
LENGHT('str') CHARACTER_LENGHT('str') CHAR_LENGHT('str')
CONCAT('Hello '. 'world')
REVERSE('abc') -- result: cba
REPLACE('Hi my name is Firman', 'Firman', 'Azka') -- replace 'Firman' with 'Azka'
LTRIM('    Azka      ') -- remove all spaces on the left
RTRIM('    Azka      ') -- remove all spaces on the right
TRIM('    Azka      ') -- remove all spaces on the left and right
POSITION('Azka' in 'My name is Azka') -- result: 12
ASCII('a') -- result: ascii value of a, let say 97

-- aggregate
SUM() MAX() MIN() AVG() COUNT(*) COUNT(col)

SELECT * DISTINCT COUNT(col) (10-5) AS
FROM table_name
GROUP BY col1, col2
HAVING condition
ORDER BY col DESC
LIMIT 5 OFFSET 2

WHERE BETWEEN, IN ('a', 'b'), NOT IN ('a', 'b'), IS NULL, IS NOT NULL, ABS()

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37

-- add new rows
INSERT INTO table_name
VALUES (val1, val2)
-- update row data
UPDATE table_name
SET column = value_or_expr,
	other_col = value_or_expr
WHERE condition -- specified the row here
-- deleting rows
DELETE FROM table_name
WHERE condition

-- creating table
CREATE TABLE IF NOT EXISTS table_name (
	column1 DataType TableConstraint DEFAULT default_val,
	column2 DataType TableConstraint DEFAULT default_val
)
-- example
CREATE TABLE movies ( 
	id INTEGER PRIMARY KEY, 
	title TEXT, 
	director TEXT, 
	year INTEGER, 
	length_minutes INTEGER 
)
-- data type
INTEGER, BOOLEAN, FLOAT, DOUBLE, REAL
CHARACTER(num_chars), VARCHAR(num_chars), TEXT
DATE, DATETIME
BLOB
-- constraint
PRIMARY KEY
AUTOINCREMENT
UNIQUE
NOT NULL
CHECK (expression)
FOREIGN KEY

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

CREATE TABLE table_name (
	-- Uniquely identify the row
	id INTEGER PRIMARY KEY,
	
	-- Columns have a different value for every row 
	name TEXT UNIQUE
	
	-- Columns must have a value
	grade INTEGER NOT NULL
	
	-- Assigns a default value when no value is specified
	age INTEGER DEFAULT 10
);

1
2
3
4
5
6
7

-- Insert into columns in order
INSERT INTO table_name
VALUES (value1, value2);

-- Insert into columns by name
INSERT INTO table_name (column1, column2)
VALUES (value1, value2);

1
2
3
4

-- Modify the columns of an existing table
-- When combined with ADD clause, it used to add a new column
ALTER TABLE table_name
ADD column_name datatype;

1
2
3
4
5

-- Delete records (rows) in table
-- WHERE clause specifies which records that should be deleted
-- If WHERE clause omitted, all records will be deleted
DELETE FROM table_name
WHERE some_column = some_value;

1
2
3
4
5
6

-- Edit records (rows) in table
-- It includes a SET clause that indicate the column to edit
-- and a WHERE clause for specifying the records
UPDATE table_name
SET column1 = value1, column2 = value2
WHERE some_column = some_value;

1
2
3
4
5
6

show databases;

use database_name;
show tables;
select * from city;
describe city;

1
2
3
4
5

create database database_name;
create table table_name (Name varchar(25), Age int, Gender char(1), tanggal date, salary float)

insert into table_name
values("Azka", 22, "M", "2022-03-12", 7000000)