Chapter 5-7 Synopsis

Chapter 5

• SQL, structured query language, is a group of special words used exclusively for interacting with databases.
•  CREATE DATABASE is used for creating a new database.
• CREATE TABLE is used for creating a new table.
• SQL is case-insensitve
• INSERT is used to populate tables.
• SHA1() function is one way to encyrpt data. Creates an encrypted string that is always exactly 40 characters long.
• NOW() function populates date, time, and timestamp columns.
• A SELECT query returns rows of records.
• To limit what rows are return based on certain criteria when using SELECT, conditionals may be added to the query.
• Conditionals use the term WHERE.
• LIKE and NOT LiKE are used—primarily with strings—in conjunction with two wildcard characters: the underscore ( _ ), which matches a single character, and the percentage sign (% ), which matches zero or more characters.
•  To give a meaningful order to a query's results, use an ORDER BY clause.
• LIMIT states how many records are returned.
• UPDATE syntax is used to update records.
• DELETE command is used to delete data from the database.
• An alias is a symbolic renaming of an item used in a query.

Chapter 6

• Normalization is the process in which a database designer eliminates redundancies and other problems that would undermine the integrity of the database.
• There are two types of keys, primary and foreign.
• A primary key is a unique identifier that has to abide by certain rules.
• Foreign keys are the representation in Table B of the primary key from Table A
• Database relationships refer to how the data in one table relates to the data in another.
• A one-to-one relationship applies if one item in Table A coincides with one item in Table B.
• A one-to-many relationship is if one item in Table A can apply to multiple items in Table B.
• A many-to-many relationship is multiple items in Table A can apply to multiple items in Table B.

Chapter 7

• To find messages in a MySQL form, one would need to first find the forum_id for MySQL.
• A join is an SQL query that uses two or more tables and produces a virtual table of results
• A inner join returns all of the records from the named tables wherever a match is made.
• An outer join will return records that are matched by both tables and will return records that don't match.
• A right outer join does the opposite of a left outer join: it returns all of the applicable records from the right hand table, along with matches from the left-hand table.
• FULLTEXT searches require a FULLTEXT index.
• The SHOW TABLE STATUS query returns a fair amount of information about each table in the database, including the table’s storage engine.