What version of MySQL are you using?
The version on my Site5 server is 5.1.76
What SQL command is used to make a new database? What command is used to make a new table in a database?
CREATE DATABASE databasename
CREATE TABLE tablename(column1name description, column2name description ...)
What SQL command is used to select the database with which you want to work?
What SQL commands are used for adding records to a table? Hint: There are multiple options.
Naming columns - INSERT INTO tablename (column1, column2,...) VALUES (value1, value2, ...)
Including values for every column - INSERT INTO tablename VALUES (value1, NULL, value2, value3, ...)
Inserting multiple records at once - INSERT INTO tablename (column1, column4) VALUES (valueA, valueB),(valueC, valueD), (valueE, valueF)
What types of values must be quoted in queries? What types of values shouldn’t be quoted?
String values - CHAR, VARCHAR and TEXT and Date and time values must always be quoted
Numeric values, functions and the word NULL must never be quoted
What does the asterisk in SELECT * FROM the tablename mean? How do you restrict which columns are returned by a query?
* means to select every column.
Specify exact column names to restrict which columns are returned. SELECT column1, column3 FROM tablename
What does the NOW() function do?
NOW() returns the current date and time according to MySQL. SELECT NOW()
How do you restrict which rows are returned by a query?
Add conditionals to a query to restrict which rows are returned. SELECT column1, column4 from tablename WHERE condition(s)
How do LIKE and NOT LIKE differ from simple equality comparison? Which type of comparison will be be faster? What are the two LIKE and NOT LIKE wildcard characters?
LIKE and NOT LIKE use wildcards in string searches. SELECT * FROM users WHERE last_name LIKE 'Smith%'
Wildcards underscore _ matches a single character and % matches 0 or more characters.
Queries with LIKE condition are slower since they cannot take advantage of indexes
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?
Use ORDER BY to affect the sorting of the returned records. SELECT * FROM tablename ORDER BY column DESC
The default sorting method is to return records in a meaningless order
SELECT * from tablename ORDER BY column1, column2
What does the LIMIT clause do? How does LIMIT x differ from LIMIT x, y?
The LIMIT clause dictates the number of records that will be returned. SELECT * FROM tablename ORDER BY column LIMIT x will return x records.
LIMIT x returns the x records, LIMIT x,y returns y records, starting at record x SELECT * FROM tablename ORDER BY column LIMIT 10,20 will return records 11-30 that would be returned based on the ORDER BY. Result sets begin at 0 index. ALways use ORDER BY since default sort order is meaningless
What SQL command is used to change the values already stored in a table?
UPDATE is used to change values already stored in a table. UPDATE tablename SET column=value
How do you change multiple columns at once? How do you restrict to which rows the changes are applied?
Update multiple columns at once by separating the columns with a comma. UPDATE tablename SET column1=valueA, column2=valueB
Use WHERE to specify which records should be updated. UPDATE tablename set column1=valueA WHERE column5=value
What SQL command is used to delete rows stored in a table? How do you restrict to which rows the deletions are applied?
DELETE is used to delete rows stored in a table. DELETE FROM tablename
A WHERE clause is used to determine which records to delete. DELETE FROM tablename WHERE condition
What is a SQL alias? How do you create one? Why is an alias useful?
An alias is a symbolic renaming of an item used in a query.
Create an alias using the term AS. SELECT registration_date AS reg FROM users
Once an alias is defined it can be used throughout the SQL statement, even in WHERE conditions SELECT first_name AS name FROM users WHERE name='Sam'