s1eepercat
10/3/2019 - 2:17 PM

Postrgres

psql -U postgres

CREATE DATABASE test;   = create DB

\c test    = select DB

SELECT * FROM users;  = selects all DB entries

CREATE TABLE table_name (column_1 datatype, column_2 datatype, column_3 datatype);
CREATE TABLE people (name text, age smallint, birthday date);

\d   = get list of relations

\q   = EXIT POSTGRES

INSERT INTO table_name (columnt1, column2, column3) VALUES (value1,'value2',value3);

SELECT name, age, birthday FROM users; = DISPLAY THESE VALUES
SELECT * FROM users;

ALTER TABLE table_name ADD column datatype; = ADD ANOTHER COLUMN TO EXISTING TABLE

UPDATE table_name SET some_column = some_value WHERE some_column = some_value;
UPDATE people SET score = 50 WHERE name = 'Andrei'; --------
UPDATE users SET score=100 WHERE name='Andrei' OR name='Ani';-------
UPDATE users SET score=100 WHERE name='Andrei' AND score='50';-------

DELETE FROM users WHERE name='Sally';  = delete that row

DROP TABLE login;    = remove the whole table;

SELECT * FROM people WHERE name LIKE 'A%'; = GRABS ALL ENTRIES STARTING WITH 'A'
'%y' = ALL ENDING WITH 'y'


SELECT * FROM people ORDER BY score DESC; = makes entries show sorted descendingly

--FUNCTIONS--

SELECT AVG(score) FROM people; = finds average
SUM(age)
COUNT(name)

-----

CREATE TABLE login (
	ID serial NOT NULL PRIMARY KEY,
	secret VARCHAR (100) NOT NULL,
	name text UNIQUE NOT NULL
);

CREATE TABLE users (
	id serial PRIMARY KEY,
	name varchar(100),
	email text UNIQUE NOT NULL,
	entries BIGINT DEFAULT 0,
	joined TIMESTAMP NOT NULL
);

CREATE TABLE login (
	id serial PRIMARY KEY,
	hash varchar(100) NOT NULL,
	email text UNIQUE NOT NULL
);

-----

SELECT * FROM people JOIN login ON people.name = login.name;      
= NAME IS MATCHING IN BOTH TABLES