Creating a new table:
CREATE TABLE tablename
(
_Column_name 1 datatype (example: serial) PRIMARY KEY,
Column_name 2 datatype (example: varchar(50) NOT NULL,
Columnname 3 datatype (example: int),
);
Selecting ALL data from a table:
SELECT *
FROM tablename;
Inserting new data into a table:
INSERT INTO tablename (column1, column2, column3)
VALUES (value1, value 2, value 3);
Updating something in table for specific criteria:
UPDATE tablename
SET column1 = value1, column2 = value2
WHERE (some criteria)
RETURNING *;
Ordering data a specific way
SELECT column1, column 2, column 3
FROM tablename
ORDER BY column1 DESC
Deleting data from a table
DELETE FROM tablename
WHERE (some criteria);
*Note - be careful when using a delete command! You may delete more than you want if you don't specify "where"
Using SQL in our Task Manager app
To make the data in our app long-lived, we used SQL to create a database of information
Downloading the "pg" node onto our index.js allowed us to write SQL in JS
Then we can use db.query to insert SQL commands to create the data table, add new tasks or edit/delete tasks
Note that because we are awaiting the db query, the function needs to be asynchronous
Note that we can't simply interpolate the desired columns into our SQL command because we become vulnerable to malicious activity. Instead we use ($1, $2 etc)
Today I Learned
INTRO TO SQL
Creating a new table: CREATE TABLE tablename ( _Column_name 1 datatype (example: serial) PRIMARY KEY, Column_name 2 datatype (example: varchar(50) NOT NULL, Columnname 3 datatype (example: int), );
Selecting ALL data from a table: SELECT * FROM tablename;
Inserting new data into a table: INSERT INTO tablename (column1, column2, column3) VALUES (value1, value 2, value 3);
Updating something in table for specific criteria: UPDATE tablename SET column1 = value1, column2 = value2 WHERE (some criteria) RETURNING *;
Ordering data a specific way SELECT column1, column 2, column 3 FROM tablename ORDER BY column1 DESC
Deleting data from a table DELETE FROM tablename WHERE (some criteria); *Note - be careful when using a delete command! You may delete more than you want if you don't specify "where"
Using SQL in our Task Manager app
db.queryto insert SQL commands to create the data table, add new tasks or edit/delete tasks