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.