PostGres - Advanced
Lesson Objectives - important#
- Joins
- Linking Tables
- Alias
- Indexes
- Constraints
Lesson Objectives - good to know about#
- EER Diagrams
- Unions
- Truncate
- Triggers
- Views
- Functions/Stored Procedures
- Transactions
- Locks
- Privileges
- Denormalization
- Excel -> CSV -> MySQL
Important#
Joins#
- Cross Join
- Inner Join
- Left Join
- Right Join
- Full Join
SELECT table1.column1, table2.column2FROM table1INNER JOIN table2ON table1.common_filed = table2.common_field;Linking Tables#
http://code.tutsplus.com/articles/sql-for-beginners-part-3-database-relationships--net-8561- One to One Relationships
- each user has one address
- only one person at that address
- One to Many/Many to One Relationships
- customer has many orders
- Many to Many Relationships
- actors and movies
- Self Referncing Relationships
- customer referral
Alias#
SELECT t1.column1 as col1, t2.column2 as col2FROM table1 as t1INNER JOIN table2 as t2ON t1.common_filed = t2.common_field;Indexes#
CREATE INDEX index_name ON table_name (column_name);CREATE INDEX index_name ON table_name (column1_name, column2_name);- Primary Key
Constraints#
- NOT NULL
- Unique
- Foreign Keys
CREATE TABLE companies( id SERIAL PRIMARY KEY, name VARCHAR(16) NOT NULL UNIQUE, city VARCHAR(16));INSERT INTO companies ( city ) VALUES ('Palo Alto');CREATE TABLE people( id INT PRIMARY KEY, name VARCHAR(16) NOT NULL, email VARCHAR(32) NOT NULL UNIQUE, company_id INT REFERENCES companies(id));