[SQL]: Note

Notes on SQL

What is a Database(DB)?

1. Collection of data

• Ex) medical records, to-do list, shopping list, etc

2. A method for accessing and manipulating that data
3. A structured set of computerized data with an accessible interface

DB vs Database Management System(DBMS)

• App -> DBMS -> DB
• DBMS allows us to interact with DB
• DB often refers to both DBMS and DB
• Common DBMS includes PostgreSQL, Oracle DB, MySQL, SQLite
  • They all use SQL language but have slight differences in syntax
  • They follow SQL Standard

MySQL vs SQL

SQL:

• Structured Query Language
• Language we use to talk to our DB
• Ex) Find all users who are 18 or older

SELECT * FROM users WHERE AGE >= 18; -- Commend with '--'

MySQL:

• Working with MySQL is primarily writing SQL(the language)

DBMS

• Once you learn SQL, it's quite easy to switch to another DB(DBMS) that uses SQL
• What makes DBMS unique are the features tey provide, not the language

Tables

• A DB is just a bunch of tables (in a relational db at least)

• Tables hold the data

• Ex) Dog table

  TEXT

  Copy

  +--------------------+
  | Name | Breed | Age |
  +--------------------+
  | Grey | Corgi |  1  |
  |--------------------|
  | Red  | Frise | 11  |
  +--------------------+

• Columns (headers)

  TEXT

  Copy

  +--------------------+
  | Name | Breed | Age |
  +--------------------+

• Rows (actual data)

  TEXT

  Copy

  +--------------------+
  | Grey | Corgi |  1  |
  |--------------------|
  | Red  | Frise | 11  |
  +--------------------+

• Not allowed Column (inconsistency of data types)

  TEXT

  Copy

  +-----+
  | AGE |
  +-----+
  |  3  |
  |-----|
  |  5  |
  |-----|
  | ten |
  |-----|
  |young|
  +-----+

  • To avoid inconsistency, when creating tabe we need to specify data types for each column

Data Types

• Numeric Types

  • INT
  • SMALLINT
  • TINYINT
  • ...

• String Types

  • CHAR
  • VARCHAR
  • TEXT
  • ENUM
  • ...

• Data Types

  • DATE
  • DATETIME
  • TIME
  • ...