Vincec's Dimension

SQL Notes

Word count: 554 / Reading time: 3 min
2019/01/25 Share

Database Management Systems (DBMS)

  • oracle
  • sql server
  • DB2
  • Mysql
  • PostgreSQL
  • SQLite
  • MS Access
  • MongoDB
    (express verison is free)

  • Database -> table -> data
  • Build table by col, unique col and non unique col
  • Read table by row -> use key

Primary Key is generated by table, the unique key

One to many

Many to many

Using junction or linking table, Composite Key

One to one

No common use

Transaction - ACID Test

  • Atomic
  • Consistent
  • Isolated
  • Durable

SQL

Deleting

Cascading Delete

Cascading Nullify

No Action

Will not allow to delete until delete all orders or replace a new customer.

Normalization

Fix redundancy, not performance

Apply FIrst Nirmal Form (1NF):

Each col in each table contains one value, no repeating groups

Apply Second Normal Form (2NF)

Any non-key field should be dependent on the entire primary keys. (only happened when with composite keys)


Break the 2NF


Fixed with 2NF, break 3NF

Apply Third Normal Form (3NF)

No non-key field is dependent on any other non-key field.


Break 3NF


Fixed with 3NF


Break 3NF


Fixed with 3NF by deleting calculate

Denomalizated

For performance (zipcode, multi-emails…)

SQL Language

Select

SELECT <col_name>, <col2_name> / * / SELECT COUNT(*) / SELECT MAX(<col_name>), MIN(), AVG(), SUM()

From

FROM <table_name>

Where

Condition:
WHERE <col_name> = ... AND .... OR .... IN ('A', 'B') ... LIKE 'Green%' / LIKE 'Sm_th' ... IS NULL ... IS NOT NULL...

Order by

ORDER BY <col_name> DESC, ORDER BY <col_a>, <col_b>

Group by

Use with select count:
SELECT COUNT(*), Color FROM <Product> GROUP BY <Color>

(inner) Join

join two table, one to many, SELECT table1.xxxx, table2.xxxx ... FROM table1 JOIN table2 On table1.id = table2.id

  • Only join if is in another table with inner join.
  • LEFT OUTER JOIN / RIGHT OUTER JOIN / FULL OUTER JOIN for outer join

    Insert into

    INSERT INTO <table_name> (<col_name1>, <col_name2>, <col_name3>) VALUES (a, b, c)

    Update

    For one row strictly:
    UPDATE <table_name> SET <col_name = ''> WHERE <col = xxxx>

    Delete

    DELETE FROM <table_name> WHERE <col=xxxx>

    Having

    In

End

With ;

String in SQL

With single quote \`

equal in SQL

With single =

Cross Database Example

SELECT <col_name> FROM <HumanResources.Emplyee> WHERE <col = xxx>;

Good Habit for delete

1
2
SELECT * FROM Employee WHERE EmployeeID = 734;
DELETE FROM Employee WHERE EmployeeID = 734;

SQL Statement Types

  • Data Manipulation
    • SELECT
    • INSERT
    • UPDATE
    • DELETE
  • Data Definition
    • CREATE
    • ALTER
    • DROP
  • DATA CONTROL
    • GRANT
    • REVOKE

CREATE

1
2
3
4
5
Create table_name
(<col_name> INTEGER PrimaryKey,
<FirstName> VARCHAR(35) NOT NULL,
...
)

ALRER

1
2
ALTER TABLE <table_name>
ADD col_name VARCHAR(100)

DROP

DROP TABLE <table_name>

Clustered INDEX

  • Default is PK
  • Each table can only have one clusterd index, but can have many non-clustered(secondary) index.
  • Faster reads and slower wirtes

Transaction to atonic

Pessimistic Locking / optimisitc Locking

Stored Procedures

Aviod SQL injection

1
2
3
4
5
6
CREATE PROCEDURES procedures(IN dept VARCH(50))
...
...
...
END;
CALL procedures('Accounting');

Other Database Systems

XML Database Systems (XQuery)

  • BaseX
  • Sedna
  • eXist

Object-Oriented Database Systems

  • Objectivity/DB
  • VelocityDB
  • Versant

Object-Relational Mapping (ORM)

  • Hibernate / java
  • Core Data / Objective-c
  • ActiveRecord / Ruby
  • NHibernate / C#/ VB.NET

NoSQL Categroy, aren’t relatioanl database

  • CouchDB
  • MongoDB
  • Apache Cassandra
  • Hypertable
  • HBase
  • Neo4k
  • BigTable
  • Riak
  • Prokecy Voldemort
  • Redis

features including

  • No SQL / No table / No relationships oriented/ No ACID/ No formal, fixible schema/ Oriented to web/ large-scale / open source/ document, XML or JSON
  • Key-Value Stores, key-value pairs
  • Graph Database, nodes connections

Reference

Author: VINCEC

Permalink: https://vince-amazing/blog/2019/01/25/sql-notes/

Date: January 25th 2019, 2:50:14

Copyright license: The article usingCC licensing 4.0

CATALOG
  1. 1. Database Management Systems (DBMS)
  2. 2. One to many
  3. 3. Many to many
  4. 4. One to one
  5. 5. Transaction - ACID Test
  6. 6. SQL
  7. 7. Deleting
    1. 7.1. Cascading Delete
    2. 7.2. Cascading Nullify
    3. 7.3. No Action
  8. 8. Normalization
    1. 8.1. Apply FIrst Nirmal Form (1NF):
    2. 8.2. Apply Second Normal Form (2NF)
    3. 8.3. Apply Third Normal Form (3NF)
  9. 9. Denomalizated
  10. 10. SQL Language
    1. 10.0.1. Select
    2. 10.0.2. From
    3. 10.0.3. Where
    4. 10.0.4. Order by
    5. 10.0.5. Group by
    6. 10.0.6. (inner) Join
    7. 10.0.7. Insert into
    8. 10.0.8. Update
    9. 10.0.9. Delete
    10. 10.0.10. Having
    11. 10.0.11. In
  11. 10.1. End
  12. 10.2. String in SQL
  13. 10.3. equal in SQL
  14. 10.4. Cross Database Example
  15. 10.5. Good Habit for delete
  • 11. SQL Statement Types
    1. 11.1. CREATE
    2. 11.2. ALRER
    3. 11.3. DROP
  • 12. Clustered INDEX
  • 13. Transaction to atonic
  • 14. Stored Procedures
  • 15. Other Database Systems
    1. 15.1. XML Database Systems (XQuery)
    2. 15.2. Object-Oriented Database Systems
    3. 15.3. Object-Relational Mapping (ORM)
    4. 15.4. NoSQL Categroy, aren’t relatioanl database
      1. 15.4.1. features including
  • Reference