- Slow down. The more you understand, the less you have to memorize. Don’t just read. Stop and think. When the book ask you a question, don’t just skip to the answer. Imagine that someone really is asking the question. The more deeply you force your brain to think, the better chance you have of learning and remembering.
- Do the exercises. Write your own notes.
- Read the “There are No Dumb Questions”
- Make this the last thing you read before bed. Or at least the last challenging thing.
- Drink water. Lots of it.
- Talk about it. Out loud.
- Listen to your brain.
- Feel something!
- Create something.
- A database is a container that holds tables and other SQL structures related to those tables.
- A table is the structure inside your database that contains data, organized in columns and rows.
- Each category becomes a column in your table. A table row contains all the information about one object in your table.
- The information inside the database is organized into tables.
- The SQL language requires all tables to be inside of databases
- The semicolon is there to indicate that the command has ended
- All of the tables in a database should be connected in some way
- DESC your_table_name to view your table
- Drop table will work whether or not there is data in your table. Once your table is deleted, it’s gone, along with any data that was in it.
- You can change the order of your column names, as long as the matching values for each column come in that same order
- If we have a column that we know is usually a specific value, we can assign it a DEFAULT value. The value that follows the DEFAULT keywords is automatically inserted into the table each time a row is added if no other value is specified. The default value has to be of the same type of value as the column.
- A NULL value is an undefined value. It does not equal zero or an empty value. A column with a NULL value IS NULL, but does not EQUAL NULL.
- Columns that are not assigned values in your insert statements are set to NULL by default
- The * is telling the RDBMS to give you back the values from all of the columns in your table.
- The varchar, char, blog, date, and time data types need single quotes. The numeric types, dec and int, do not
- Single quotes are special characters， handle quotes with a backslash.
- You can't select a null value directly, but you can select it using keywords
- Like, used with a wildcard, looks for part of a text string and returns any matches
- Percent sign %, which can stand in for any number of unknown characters
- Underscore, _ which stands for just one unknown character
- The smaller number must always be first for the BETWEEN to be interpreted the way you expect
- Use IN with a set of values in parentheses, when the value in the column matches one of the values in the set, the row or specified columns are returned.
- A table is all about relationships
- When it’s ATOMIC, that means that it's been broken down into the smallest pieces of data that can't or shouldn’t be divided
- A column with atomic data can't have several values of the same type of data in that column.
- A table with atomic data can't have multiple columns with the same type of data.
- Making your data atomic is the first step in creating a NORMAL table
- Normal tables won't have duplicate data, which will reduce the size of your database
- With less data to search through, your queries will be faster
- To be 1NF, a table must follow these two rules: Each row of data must contain atomic values; each row of data must have a unique identifiers, known as a primary key
- A primary key is a column in your table that makes each record unique. The primary key is used to uniquely identify each record, which means that the data in the primary key column can't be repeated.
- A primary key can't be null, if it's null, It can't be unique because other records can also be NULL
- The primary key must be given a value when the record is inserted
- The primary key must be compact
- The primary key values can't be changed
- If you change the data type to something new, you may lose data.
- Alter table drop column yourcolumn;
- Use change when you want to change both the name and the data type of a column.
- Use modify when you wish to change only the data type
- Drop column does just that: it drops the named column from the table.
- Use rename to change the name of your table.
- You can change the order of your columns using first, last before column_name
- Use right() and left() to select a specified number of characters from a column.
- Use substring_index() to grab part of the column, or substring. This one wull find everything in front of a specific character or string.
- String functions do not change the data stored in your table, they simply return the stored strings as a result of your query.
- Select something and order the data it returns by another column from the table.
- By default, SQL returns your order by columns in ASCEDDING order
- Use the keyword DESC after your column name in order by clauses to reverse the order of your results.
- Sum all of them at once with group by.
- Count will return the number of rows in a column.
- Get the second position: select first_name, sum(sales) from cooie_sales group by first_name order by sum(sales) desc limit 2
- ORDER BY: alphabetically orders your results based on a column you specify
- GROUP BY: Consolidate rows based on a common column.
- DISTINCTL: Returns each unique value only once, with no duplicates
- LIMIT: Lets you specify exactly how many rows to return and which row to start with.
- A description of the data(the columns and tables) in your database, along with any other related objects and the way they all connect is known as a SCHEMA.
- The foreign key is a column in a table that references the primary key of another table.
- A foreign key can have a different name than the primary key it comes form
- The primary key used by a foreign key is also known as a parent key. The table where the primary key is from is known as a parent table.
- The foreign key can be used to make sure that the rows in one table have corresponding rows in another table
- Foreign key values can be null, even though primary key value can’t
- Foreign key don’t have to be unique—in fact, they often aren't .
- A null foreign key means that there’s no matching primary key in the parent table. But we can make sure that a foreign key contains a meaningful value, one that exists in the parent table, by using a constraint.
- You can use a foreign key to reference a unique value in the parent table. It doesn’t have to be the primary key of the parent table, but it must be unique.
- One-to-One: exactly one row of a parent table is related to one row of a child table.
- Many-to-many: a junction table holds a key from each table.
- 1NF, rule1: columns contain only atomic values; no repeating groups of data.
- A key made of two or more columns is known as a composite keys.
- A composite key is a primary key composed of multiple columns, creating a unique key
- Your 1NF table is also 2NF if all the columns in the table are part of the primary key or it has a single column primary key.
- If your table has an artificial primary key and no composite primary key, it’s in 2NF.
- 3NF: rule1: be in 2NF, rule2: have no transitive dependencies.
- SQL let’s you assign an alias for a column name so you won't get confused
- The cross join returns every row from one table crossed with every row from the second.
- An inner join combines the records from two tables using comparison operators in a condition. Columns are returned only when the joined row match the condition.
- Non-equijoin inner joins test for inequality
- Natural join inner joins identify matching column names.
- A subquery is a query that is wrapped within another query. It’s also called an inner query.
- If a subquery is used as a column expression in a select statement, it can only return one value from one column.
- A left outer join takes all the rows in the left table and matches them to rows in the RIGHT table. It’s useful when the left and the right table have a one-to-many relationship.
- In a left outer join, the table that comes after from and before the join is the LEFT table, and the table comes after join is the RIGHT table.
- The difference is that an outer join gives you a row whether there’s a match with the other table or not.
- A NULL value in the results of a left outer join means that the right table has no values that correspond to the left table.
- The right outer join evaluates the right table against the left table.
- The self-referencing foreign key is the primary key of a table used in that same table for another purpose.
- A UNION combines the result of two or more queries into one table, based on what you specify in the column list of the SELECT.
- UNION can only take one order by at the end of the statement.
- SQL’s rule of UNION: the number of columns in each select statement must match; the data types in the columns need to either be the same, or be convertible to each other.
- Union all works exactly the same way as UNION, except it returns all the values from the columns, rather than one instance of each value that is duplicated.
- Intersect returns only those columns that are in the first query and also in the second query
- Except returns only those columns that are in the first query, but not in the second query.
- A check constraint restricts what values you can insert into a column. It uses the same conditionals as a WHERE clause.
- A constraint is a restriction on what you can insert into a column. Constraints are added when we create a table.
- Check doesn't enforce data integrity in MySQL.
- A view is basically a table that only exists when you use the view in a query, It’s considered as a virtual table because it acts like a table, and the same operations that can be performed on a table can be performed on a view. But the view table doesn't stay in the database. It gets created when we use the view and then deleted.
- Check option checks each query you try to insert or update to see if it’s allowed according to the where clause in your view.
- An updatable view includes all the NOT NULL columns from the table it references.
- A transaction is a set of SQL statements that accomplish a single unit of work.
- During a transaction, if all the steps can't be completed without interference, none of them should be completed.
- ACID—ATOMICITY: all of the pieces of the transaction must be completed, or none of them will be completed, you can't execute part of a transaction.
- ACID—CONSISTENCY: a complete transaction leaves the database in a consistent state at the end of the transaction.
- ACID—ISOLATION: it means that every transaction has a consistent view of the database regardless of other transactions taking place at the same time.
- ACID—DURABILITY: after the transaction, the database needs to save the data correctly and protect it from power outages or other threats.
- No changes will occur to the database until you commit.
- InnoDB and BDB are two possible ways that your RDBMS can store your data behind the scenes. Alter table your_table type=InnoDB;
- You can control exactly what uses can do to tables and columns with the grant statement
- Role, a role is a group of privileges. Roles let you group together specific privileges and assign them to more than one user.
- Greater than ALL finds any values larger than the biggest value in the set.
- Greater than ANY finds any values larger than the smallest value in the set.
- A temporary table exists from the time you create it until you drop it, or until the user session ends.