SQL Flashcards
What is SQL?
It is Structure Query Language.
It is mySQL, PostGRSQL, MarinDB
What is ERD?
Entity Relationship Diagram
What is a database?
Besides the HTML and CSS that make up the view of a particular page, and the backend logic that dictates the functionality, there’s the collectin of organized information that can easily be accessed, managed, and updated.
What are the normal forms?
1 - no multiple data points in a column data row
2 - no repeating data points in a column
3 - columns are not dependent on other non-primary key columns
What are the set industry standards?
1) make the table name plural and ALL lowercase
2) use ‘id’ as the primary key - make it auto-increment
3) name foreign keys with singular_table_name_id
4) use created_at and updated_at for the timestamp in EVERY table you create
What are the datatypes that you will use 95% of the time?
varchar, char, int, bigint, tinyint, float, text, datetime
What can SQL statements do?
SQL statements are used to perform tasks; they can SELECT data, SELECT data WHERE some conditions are true, INSERT data, UPDATE data, DELETE data, and JOIN different tables together.
SQL Server?
mySQL server connects first to mySQL Workbench, and later our web applications. This will be a database server, which will be listening for connections on localhost.
How to install mySQL and steps?
brew install mysql
brew services start mysql
mysqladmin -u root password ‘root’
mysql -u root -p
installed, configured, and started a MySQL server
How do I change the port on mySQL server?
1) create a file named my.cnf place in the file (port nums you want): [client] port = 3307 [mysqld] port = 3307
2) save this file to /etc/my.cnf
3) run
brew services restart mysql
What are the two main ways to import structure (tables and columns) or data (rows or records) or both into your MySQL workbench
If you have an SQL file, you can just copy and paste the commands into the editor and click run. If you have an ERD diagram, you can forward engineer into MySQL workbench.
What is a good SQL statement?
SELECT FROM WHERE ORDER BY
Order by must be after WHERE
There are two ways to insert or edit
You can select all from a table and directly edit in the GUI or INSERT INTO (DATABASE).(TABLE) (ATTRIBUTES) VALUES (VALUES FOR ATTRIBUTES)
(assuming you are in the database in MySQL)
INSERT INTO table_name (column_name1, column_name2)
VALUES(‘column1_value’, ‘column2_value’);
There are two ways to update our database
UPDATE table_name SET column_name1 = ‘some_value’, column_name2=’another_value’ WHERE condition(s)
IF WHERE CONDITION IS NOT ADDED TO THE UPDATE STATEMENT< THE CHANGES WILL BE APPLIED TO EVERY RECORD IN THE TABLE
DELETE requires a special condition, what is it?
If you are getting an error regarding SQL SAFE UPDATES, run the following command to let MySQL Workbench know that you know what you are doing and you want to DELETE stuff from the database.
SET SQL_SAFE_UPDATES = 0;
IF WHERE CONDITION IS NOT ADDDED TO THE DELETE STATEMENT, IT WILL DELETE ALL THE RECORDS ON THE TABLE
How do you export a database?
You first need to export with data export, change the location of the export, select the database, create schema..and you should have created the .sql file necessar
When we fetch the data from our database, what returns?
The fetch method returns us an array of dictionaries where each dictionary represents a row in the table. For each dictionary, the keys correspond to the column names and the values correspond to that entry’s value
What are some new things we see in the dictionary?
The ‘u’ that precede each key or value that is a string. The ‘us indicates a Unicode string that is inherently different from the simple string type. Unicode is used to represent a wider variety of languages and symbols.
The ‘L’ append to each value for the ‘id’ field in the dictionary. This represents a ‘long’ which is a type to store numbers in memory. The ‘long’ type is more accurate but will behave exactly as an int.
Is keeping our user data safe part of a full-stack developers responsibility?
Yes, this means we need to store the data safely as well.
What is we store PW’s as is in our DB?
To prevent, we need to mask our passwords BEFORE we put them into our database. This is called hashing.
You really just need to know basic md5 hashing and salt
What is the difference between hashing and encryption?
Hashing, there is no way to reverse the process algorithmically where encryption you can.
Hashing fact: the same input into a hashni algorithm always produces the same output. In practical terms this means that we can store a hashed password, re-hash a users input on login and compare the two hashed values in order to authenticate passwords
Why is salted hashing better?
You can make your data more secure by making the hashing method more random. By random, we mean more unpredictable. md5 hashing is the same no matter what computer runs the code. So to make our hasing more powerful, we will add a salt to our hashed string. A salt is a random unique key used to generate a unique password
What is a salt?
A salt is a string of random characters that will be passed to a hashing method (an md5()) along with the string we are trying to has (the submitted password) via concatenation. The hashing method that uses the salt is designed in such a way that it takes the salt to compute the hashed string, using the salt as an ‘ingredient’ in the hashing ‘recipe’
How do we generate a salt?
import os, binascii
salt = binascii.b2a_hex(os.urrandom(parameter))
The function called os.urandom() returns a string of bytes. The number of bytes is equal to the parameter provided. This string isn’t a normal alphanumeric string, so we turn it into a string using the function b2a_hex(), which will turn the value into a normal alphanumeric string. This new random string will be our salt. The idea is to store this salt during the registration process.
