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.column2
FROM table1
INNER JOIN table2
ON 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 col2
FROM table1 as t1
INNER JOIN table2 as t2
ON 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)
);