Chapter 5 - Review
On my localhost, I am running MySQL version 5.5.25
This site is using MySQL version 5.5.30
CREATE is used in both instances.
CREATE DATABASE database_name; CREATE TABLE table_name( ..define columns here);
USE database_name
To add a single record at a time into particular columns use:
INSERT INTO tablename (column3, column5…) VALUES (value3, value5 …)Or insert values into all columns without naming them first:
INSERT INTO tablename VALUES (value1,NULL, value3, value4, …)To add multiple records at once, MySQL allows this shortcut(not standard in SQL):
INSERT INTO tablename (column1,column4) VALUES (valueA, valueB),(valueC, valueD),(valueE, valueF)
ALWAYS QUOTE: Strings, Dates and Times.
NEVER QUOTE: Numbers, Functions, NULL.
To include a quotation mark in a string value, either encapsulate the string in the other quote(single in a double) or escape it with a backslash.
In a SELECT query, * means SELECT ALL columns FROM table. To restrict returned results use
SELECT columnName1, columnName2 FROM tableName
SELECT column1 FROM table WHERE columnX=value
LIKE and NOT LIKE differ from simple equality comparisons because they allow the use of wildcards
for string matching. Use the _ to match any single character, use the % to match any number of characters. The wildcards
can be used in the beginning or end of a string so LIKE /act% will match pact, cactus, actor,acts,Lactose etc...
LIKE and NOT LIKE do not rely on indexes for searching a column so they are a little slower and should only be used
when absolutely necessary.
To sort a query use the ORDER BY keyword followed by the column name to sort.(default is ascending).To change the order to descending, simply append the DESC attribute. To sort by multiple columns simply separate them with a comma...
SELECT * FROM table ORDER BY col1 DESC, col2
UPDATE tablename SET columnName=newValue, column2Name=anotherValueAn UPDATE command can also be limited to particular fields by using the WHERE clause.
UPDATE tablename SET columnName=newValue, column2Name=anotherValue WHERE columnX=ValueX
DELETE FROM tablename WHERE columnName=value LIMIT 1
<?php $review = array( 1=>array( 'q'=>'What version of MySQL are you using? If you don’t know, find out now!', 'a'=>'<p>On my localhost, I am running MySQL version 5.5.25</p><p>This site is using MySQL version 5.5.30</p>' ), 2=>array( 'q'=>'What SQL command is used to make a new database? What command is used to make a new table in a database?', 'a'=>"<p><b>CREATE</b> is used in both instances. <pre>CREATE DATABASE database_name;\nCREATE TABLE table_name( ..define columns here);</pre></p>" ), 3=>array( 'q'=>'What SQL command is used to select the database with which you want to work?', 'a'=>'<p><b>USE</b> database_name</p>' ), 4=>array( 'q'=>'What SQL commands are used for adding records to a table? Hint: There are multiple options.', 'a'=>'<p>To add a single record at a time into particular columns use: <pre>INSERT INTO tablename (column3, column5…) VALUES (value3, value5 …)</pre> Or insert values into all columns without naming them first: <pre>INSERT INTO tablename VALUES (value1,NULL, value3, value4, …)</pre> To add multiple records at once, MySQL allows this shortcut(not standard in SQL): <pre>INSERT INTO tablename (column1,column4) VALUES (valueA, valueB),(valueC, valueD),(valueE, valueF)</pre></p>' ), 5=>array( 'q'=>'What types of values must be quoted in queries? What types of values shouldn’t be quoted?', 'a'=>'<p>ALWAYS QUOTE: Strings, Dates and Times.<br />NEVER QUOTE: Numbers, Functions, NULL.<br/> To include a quotation mark in a string value, either encapsulate the string in the other quote(single in a double) or escape it with a backslash.</p>' ), 6=>array( 'q'=>'What does the asterisk in SELECT * FROM table name mean? How do you restrict which columns are returned by a query?', 'a'=>'<p>In a SELECT query, <b>*</b> means SELECT <b>ALL</b> columns FROM table. To restrict returned results use<br/> SELECT columnName1, columnName2 FROM tableName</p>' ), 7=>array( 'q'=>'What does the NOW( )function do?', 'a'=>'NOW() returns the current date and time formatted as a timestamp in the current time zone YYYY-MM-DD HH:MM:SS.' ), 8=>array( 'q'=>'How do you restrict which rows are returned by a query?', 'a'=>'To restrict the rows returned by a query use:<br /> SELECT column1 FROM table <b>WHERE</b> columnX=value' ), 9=>array( 'q'=>'How do LIKE and NOT LIKE differ from simple equality comparisons? Which type of comparison will be faster? What are the two LIKE and NOT LIKE wildcard characters? ', 'a'=>'<p>LIKE and NOT LIKE differ from simple equality comparisons because they allow the use of wildcards for string matching. Use the <b>_</b> to match any single character, use the <b>%</b> to match any number of characters. The wildcards can be used in the beginning or end of a string so LIKE /act% will match pact, cactus, actor,acts,Lactose etc...<br /> LIKE and NOT LIKE do not rely on indexes for searching a column so they are a little slower and should only be used when absolutely necessary.</p>' ), 10=>array( 'q'=>'How do you affect the sorting of the returned records? What is the default sorting method? How do you inverse the sort? What is the syntax for sorting by multiple columns?', 'a'=>'<p>To sort a query use the ORDER BY keyword followed by the column name to sort.(default is ascending).To change the order to descending, simply append the DESC attribute. To sort by multiple columns simply separate them with a comma... <pre>SELECT * FROM table ORDER BY col1 DESC, col2</pre></p>' ), 11=>array( 'q'=>'What does the LIMIT clause do? How does LIMIT x differ from LIMIT x, y?', 'a'=>'The LIMIT clause truncates the result set to the number specified. To select a specific range in the results, use LIMIT x,y where x is the start of the range and y is the number of records to return. Results are numbered similarly to arrays, starting with zero, so LIMIT 10,10 will return records 11-20.' ), 12=>array( 'q'=>'What SQL command is used to change the values already stored in a table? How do you change multiple columns at once? How do you restrict to which rows the changes are applied?', 'a'=>'To change a value that has already been stored, use the UPDATE command.<pre>UPDATE tablename SET columnName=newValue, column2Name=anotherValue</pre> An UPDATE command can also be limited to particular fields by using the WHERE clause. <pre>UPDATE tablename SET columnName=newValue, column2Name=anotherValue WHERE columnX=ValueX</pre>' ), 13=>array( 'q'=>'What SQL command is used to delete rows stored in a table? How do you restrict to which rows the deletions are applied?', 'a'=>'When deleting data, it is a good idea to be extra careful. Use the DELETE clause with a WHERE statement and LIMIT constraint to prevent any unexpected data loss.<pre>DELETE FROM tablename WHERE columnName=value LIMIT 1</pre>' ), 14=>array( 'q'=>'What is an SQL alias? How do you create one? Why is an alias useful?', 'a'=>'An alias is a shorthand reference to a tablename or column name using the AS keyword. An alias allows the query to return the alias name instead of the column/table name in the result set. It also helps organize and shorten your query for readability if multiple columns have similar names. ' ) ); include('templates/review.php'); ?>