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)
