Notes on Databases and SQL Commands for Reference

• Databases are a great tool to outsource the work of making a ruby file, CSV, or PStore
• Databases are seperate from apps themselves, so any language apps can use any databases (MySQL, PostgreSQL, SQLite, etc.)
• The interface to databases is text
  • Dont usually come with a GUI (graphical user interface) Regional databses (which powers most apps) use a special language called SQL (stuctural query language)

Working directly with Postgres

• Great one to use cause it is already installed on codespaces in github.
• Commands to set up a table
  • psql
    • If this command went well, we should see postgres=#
  • \list
    • displays all current databases on this computer
  • CREATE DATABASE my-db
    • the name of the database should be snake_case
  • \connet my-db
    • to enter databases
  • \dt
    • lists all the tables in the db, this means “describe tables”. the realtion comes from the term “relational databases” a ‘relation’ is a set of multiple records. not related to “relationships”, in the context of one to many, or many to many, or one to one
  • CREATE TABLE contacts (
    • id SERIAL PRIMARY KEY,
    • first_name TEXT,
    • last_name TEXT,
    • date_of_birth DATE )
    • standard creation of a contact for use
• SELECT first_name, last_name FROM contacts
  • use * instead to see all
• INSERT INTO contacts(all the values above)VALUES ('mary', 'webby', '1999, 29, 12')
  • place single quotes around each intserted value.