Database meta information, views and administration Flashcards
How can you show all databases available
SHOW DATABASES;
Which query shows all the info for all the catalogs and tables
SELECT * FROM INFORMATION_SCHEMA.COLUMNS;
which query shows all the tables in all a databases?
SHOW TABLES FROM database_name;
or
USE database_name
SHOW TABLES;
or
EXPLAIN table_name
How can you see all the metadata of a table?
DESCRIBE table_name;
what is an index?
a data structure that stores the values for a specific column.
e.g.
on column name.
now if you have to look for all John names you dont have to look at all rows. An index will store all column names alphabetically and you will only need to search in that
How do you create an index?
CREATE INDEX name_index ON User (Name)
What is a view
It is a virtual table derived from other tables that contain data from those tables - a virtual table that refers to some data in a database.
Does the DBMS store the views result or the definition of the view?
The definition
How do you create a view?
CREATE VIEW view_name AS select_statement
Does the view duplicate data?
No it has references to the real value.
Why would you use a view rather than the normal tables?
To restrict access or to avoid complicated sql queries to get data.
What are the problems with views?
have to be re-run anytime they are referenced because each time you use the name of a view, its table is recreated from existing data - a solution is to store the view as a temporary table making the view last a session - but you have to add the data to the table
What is a temporary table
it is a temporary table that lasts for one session only- You have to create and add data to them every time.
Stored procedures can help you create them easily
What happens when you update a view
The updates are retained in the database
What happens when you update a temporary table
the updates are not saved anywhere else than in the temporary table
How can you manage access to a DB?
- authorization identifier: user & psw
- ownership: can be granted or revoked to users
- privileges: actions users are allowed to carry out on a DB
How can you show all the users?
SELECT user FROM mysql.user;
How can you show users, hosts they can connect from and password?
SELECT user,host,password FROM mysql.user;
SELECT * FROM mysql.user;
How can you show all the privileges given to a user?
SHOW GRANTS FOR USER;
How do you grant and revoke privileges?
GRANT INSERT on Employee
to receptionist
How can you show the current user logged in?
SELECT current_user();
How can you log into a user in MySQL?
In Workbench, when you chose the database to connect to, create a new connection and put in the user and password instead of root.
How do you create users?
Do you need a privilege?
You need the CREATE USER and GRANT privileges.
The syntax is:
CREATE USER ‘newuser’@’localhost’ IDENTIFIED BY ‘user_password’;
How can you modify users?
Do you need a privilege?
With the ALTER USER statement:
ALTER USER ‘user’@’hostname’ IDENTIFIED BY ‘newPass’;
You need the CREATE USER or the UPDATE privilege for the MYSQL system schema
