MySQL Flashcards
How do you get a list of users on MYSQL?
As root:
SELECT User FROM mysql.user;
How do you see all the databases in MySQL?
SHOW databases;
How do you select a database to use?
USE databasename;
How do you see the tables in a database?
SHOW tables;
How do you create a table called admins?
CREATE TABLE admins ( id INT(11) NOT NULL AUTO_INCREMENT, username VARCHAR(50) NOT NULL, hashed_password VARCHAR(60) NOT NULL, PRIMARY KEY (id) );
How do you set a primary key?
Inside a create statement PRIMARY KEY (fieldname)
What datatype is a string?
VARCHAR
What datatype would you normally set an ID to?
INT
How could you just show the fields from a table called admins?
SHOW FIELDS FROM admins;
Delete a row with the field menu_name is equal to “Delete Me” from table subjects:
DELETE FROM subjects WHERE menu_name=”Delete Me”;
How do you update an existing menu_name in table subjects that has an id of 23?
UPDATE subjects SET menu_name=’Foobar’ WHERE id=23 LIMIT 1;
What command can you use in MySQL to determine which user you are logged in as?
SELECT USER(); CURRENT_USER();
How do you create a new user on a MySQL database?
CREATE USER ‘foobar’@’localhost’ IDENTIFIED BY ‘password’;
How do you grant create privileges to a user in MySQL?
GRANT CREATE ON sandbox TO foo@’localhost’ IDENTIFIED BY ‘password’;
What must you run after setting privileges as root?
FLUSH PRIVILEGES;
How do you display the structure of a database table in order to verify it’s makeup?
DESCRIBE tablename;
What function do you use in PHP to change the way PHP saves sessions (i.e. to a database)?
session_set_save_handler()
How would you had a field ‘admin’ to a table in MySQL?
ALTER TABLE users ADD admin tinyint(1) AFTER email;
What datatype can you use as BOOLEAN?
tinyint(1) - zero is false, any positive number is true (BOOLEAN is an alias to tinyint apparently)
How do you see what permissions a user has on a mysql system?
SHOW GRANTS FOR ‘username’@’localhost’;
How do you make sure a column in mysql is unique?
Use the UNIQUE constraint on the field.
A table was created, but you missed the fact that one field should be unique, how do you fix this?
ALTER TABLE users ADD UNIQUE (username);
How would you add a primary key to an existing table (assuming no pre-existing links)?
ALTER TABLE my_table ADD id INT NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST;
What does ZEROFILL do when creating an INT type column?
The zerofill option fills empty slots in the display of the number column.
How do you escape a “ ‘ “ character in MySQL?
Use two together.. ‘’ will escape the second apostrophe. i.e. (‘Ray’’s shop’);
What does the \G option do at the end of an SQL statement (in MySQL)?
Provides the results in a vertical grouping style.
What does a LEFT JOIN do?
Returns all the rows from the left table (table1) with the matching rows in the right table (table 2).
If there is no match on the right hand side, what is returned?
NULL
What is the syntax for a LEFT JOIN (table 1 and table 2)?
SELECT columns
FROM table1
LEFT JOIN table2
ON table1.column = table2.column;
What is another name for LEFT JOIN?
LEFT OUTER JOIN
How can you improve efficiency of joins in MySQL?
Ensure the columns you are joining on are indexed (keys), and make sure the columns are of the exact same type.
When using mysqli_fetch_array, you can pass as a second parameter a predefined variable that indicates the type of array. What are they?
MYSQLI_NUM
MYSQLI_ASSOC
MYSQLI_BOTH
What does the CONCAT_WS function in mysql do?
Concatenates results.
CONCAT_WS(separator, w1, w2)
Why might you use the CONCAT_WS function in msyqli?
For instance when returning two address fields where either one could be omitted. The behavior of the function is to only add the separator if both fields exist, which prevents the output looking something like…
, address2
when address1 doesn’t exist.
When creating a stored function, declarations must be made where?
Between a BEGIN and END code block and before any other statements (i.e. straight after the BEGIN keyword)
How do you declare a variable in a stored function?
DECLARE var1 INT
What do the stored function datatypes correspond to?
MySQL data types
Once a function has been declared, how do you set it?
SET var1 = value
What is the syntax for creating a stored function in MySQL?
CREATE FUNCTION myfunc (myvar1 INT, myvar2 CHAR) RETURNS type code
Where in a stored function do statements end with a semicolon?
Between the BEGIN and END blocks
What is the basic syntax for a full stored function?
CREATE FUNCTION name (arguments) RETURNS type BEGIN statement1; statement2; END
What is the issue with using MySQL stored functions in PHP?
The delimiter for the statements is also the delimiter for MySQL functions (i.e. will try to execute half way through the MySQL function).
How do you get around the issue with delimiters in PHP?
Use the MySQL keyword DELIMITER
Change the delimiter to something else, but make sure you change it back.
DELIMITER $$
.. do stored functions
DELIMITER ;
Can you return a list of data from a stored function?
No - Return types are scalar in nature.
Delimiter has been set to $$ and you have finished your stored function definition. You type END; and nothing happens, what is wrong with this?
You have set the delimiter to $$, you must type:
END $$ to finish the stored function. (Remember to set the DELIMITER back to ; if you have finished)
How do you query mysql to show you a stored function definition?
SHOW CREATE FUNCTION name;
How do you query mysql to show you a list of stored functions?
SHOW FUNCTION STATUS;
What are stored routines associated with?
The database - so if you drop a database, you will also drop the routines.
How do you create an enumeration in a MySQL table?
In the create clause:
userType ENUM ('public', 'author', 'admin'),Rows can also be referred to as…
tuples or records
What is the name for the ‘layout’ of the database?
schema (usually in the following form)
Customers(CustomerID, Name, Address)
Primary keys in a schema are usually…
underlined
Foreign keys in a schema are usually represented by…
italics
What are the three different relationships between two tables?
one-one
one-many
many-many
An update anomaly is…
When you update a database and end up with inconsistent data - i.e. a persons address is stored in multiple areas, changing it once doesn’t get all of them - causing an anomaly.
What are the three kind of anomalies?
Deletion
Insertion
Update
A deletion anomaly is?
Deleting a row removes data that should be stored (for example if customers address was stored against the order, instead of it’s own table).
An insertion anomaly is?
If you have data that should be stored in one place (i.e. address) and instead store it against a row that changes frequently (i.e. the order) - the user may enter different information that conflicts with previous entries.
A well designed database will have columns with what kind of data?
Atomic (i.e. not multiple types of data in one field)
To avoid a design with many empty attributes, what should you do?
Create another table for those attributes (i.e. instead of having an empty “reviews” column against books, make it another table which only gets a new row if a review exists)
what is the problem with having null values in a table?
It wastes space, and it also creates ambiguity. Was the value forgotten, or does it not exist? Also not good for working out totals.
How do you set a password for a user?
SET PASSWORD FOR ‘cain’@’locahost’ = PASSWORD(‘cleartextpassword’);
How do you load an existing sql document into a mysql database?
mysql -h host -u username -D dbname -p < mydb.sql
What is the difference between char and varchar in mysql?
Char will always use the amount allocated (i.e. you specify 50, it will use 50 and pad out the spaces). Varchar uses only what is passed in plus one byte. But char is faster.
How do you get information about the installation of mysql?
from command line:
mysqladmin -h hostname -u root -p variables
What is DDL?
Data Definition Language, used for defining the data in the database.
What is DML?
Data Manipulation Language - used for querying or modifying the database.
General rule of thumb when doing equi joins is that you need one less … for each pair of tables you want to join?
One less join condition for each pair of tables that you want to join.
Left Joins are useful for…
finding rows where there is no match (i.e. left join will keep everything in the left table regardless of whether there is a match - you can then check for NULL to find the rows that perhaps haven’t bought something).
An equi join uses the ‘where’ clause to specify join conditions - what does a left join use?
on
Using a comma to seperate two tables is the same as an…
inner join
How do you specify an alias?
select c.name
from customers as c;
When is using an alias mandatory?
When joining a table to itself.
What is a cartesian product?
All combinations of all the rows in all the tables in the join. Used by specifying a comma between table names and not specifying a WHERE clause
What is a full join?
Same as a cartesian product.
What is a cross join?
Same as a cartesian product and full join - can be achieved by using “CROSS JOIN” keywords between two tables
Inner join is semantically equivalent to?
Using the ‘,’.
What needs to be specified to make an INNER JOIN a true INNER JOIN?
A where clause.
LEFT JOIN is the same as…
LEFT OUTER JOIN
RIGHT JOIN is the same as…
RIGHT OUTER JOIN
Which table does RIGHT JOIN keep the records for regardless of match?
The right table
What two values define the sort order in MYSQL?
ASC and DESC
In ANSI SQL, the only things that can apppear in the select clause when using GROUP BY are?
The aggregate functions and the columns named in the group by clause.
How do you test the results of an aggregate function?
Using the HAVING function SELECT customerid, avg(amount) FROM orders GROUP BY customerid HAVING avg(amount) > 50;
What MYSQL extension (non ANSI) can you use to deal with pagination in an application (i.e. high score tables)?
LIMIT 2, 3;
The first number describes which row to start from, the second how many results to return.
Using subqueries, we have access to subquery operators. What are the operators?
ANY, (Returns true if the comparison is true for any of the rows in the subquery)
SOME, (alias for any)
IN, (Equivalent to =Any)
ALL (Returns true if the comparison is true for ALL of the rows in the subquery)
What is a correlated subquery?
Where data from the outer query is used in the inner query.
What is a row subquery?
Where an entire row is returned instead of single values - typically used to find duplicate rows.
What is the difference between ANSI SQL and MySQL when it comes to ALTERING tables?
You can only update none alteration per ALTER TABLE call in ANSI SQL
