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