Basics

Create Database
1
CREATE DATABASE tutorial;
Copied!
Access Database
1
# Login to database (-u username, -p authenticate with password)
2
mysql -u root -p 
3
# Enter Password: **********
4
​
5
# You can also login
6
sudo mysql
Copied!
Backup Database
1
# Login to database
2
sudo mysql
3
​
4
# Create a new database
5
mysql > CREATE DATABASE tutorial_backup;
6
​
7
# Exit mysql with exit command and export table tutorial into a file
8
mysqldump -u root -p tutorial > tutorial.sql
9
# Enter Password: **********
10
​
11
# Import the tutorial table into a backup table, for example tutorial_backup
12
mysql -u root -p tutorial_backup < tutorial.sql
13
# Enter Password: **********
Copied!
Create Table
1
CREATE TABLE service (id INTEGER PRIMARY KEY, name TEXT);
Copied!
Insert Data Into Table
1
INSERT INTO service (id, name) 
2
  VALUES
3
(1, 'metor'),
4
(2, 'silos'),
5
(3, 'rectan')
6
(4, 'belkar');
Copied!
Deleting Data
1
DELETE FROM services WHERE id = 3;
Copied!
Updating Data
1
UPDATE services SET name = 'meteor' WHERE id = 1;
Copied!
Add Extra Column
1
ALTER TABLE table_name ADD column_name datatype;
2
​
3
# example 1
4
ALTER TABLE services ADD service_sla_date DATETIME;
5
​
6
#  example 2
7
ALTER TABLE services ADD service_type VARCHAR(255);
Copied!
Example Query
1
SELECT DISTINCT column, AGG_FUNC(column_or_expression), …
2
FROM mytable
3
    JOIN another_table
4
      ON mytable.column = another_table.column
5
    WHERE constraint_expression
6
    GROUP BY column
7
    HAVING constraint_expression
8
    ORDER BY column ASC/DESC
9
    LIMIT count OFFSET COUNT;
Copied!
Aggregate Function
COUNT(*), COUNT(column)
MIN(column)
MAX(COLUMNS)
AVG(column)
SUM(column)
SQL Joins
INNER JOIN
Produces the set of records that match in both table1 and table2 (matching part of both tables).
1
SELECT * FROM table1
2
INNER JOIN table2
3
ON table1.name = table2.name;
Copied!
FULL OUTER JOIN
Produces the set of records in table1 and table2, that matches records from both tables (all records from both tables, null value if there is no record in one of them).
1
SELECT * FROM table1
2
FULL OUTER JOIN table2
3
ON table1.name = table2.name;
Copied!
 LEFT OUTER JOIN
Produces a set of records from table1 + matching records in table2 (full table 1 + common part with table 2, null values if there is no record in table 2).
1
SELECT * FROM table1
2
LEFT OUTER JOIN table2
3
ON table1.name = table2.name;
Copied!
If you want just records from table1 without the matching part with table2 (without null values) you can use the following query:
1
SELECT * FROM table1
2
LEFT OUTER JOIN table2
3
ON table1.name = table2.name
4
WHERE table2.id IS null;
Copied!
On the other hand, if you need both tables records but no matching part you can use:
1
SELECT * FROM table1
2
FULL OUTER JOIN table2
3
ON table1.name = table2.name
4
WHERE table1.id IS null
5
OR table2.id IS null;
Copied!

JavaScript - Previous

Extra Material

Next - SQL

SQLAlchemy and Alembic

Last modified 6mo ago

Copy link

Contents

Insert Data Into Table