MySQL Flashcards
What connection method is used for a local MySQL server?
Standard TCP/IP.
What is the default host name for a local MySQL connection?
localhost
What is the default port number for MySQL?
3306.
What SQL command is used to create a database?
CREATE DATABASE.
How should SQL statements be concluded?
With a semicolon.
What icon is used to execute a command in MySQL Workbench?
The lightning bolt icon.
What happens when you refresh the Schemas tab after creating a database?
The newly created database will appear.
How do you select set a database as the default schema?
- Right-click on the database
- Select ‘set as default schema’.
What command is used to switch to a specific database?
USE [database_name];
What command is used to drop a database?
DROP DATABASE [database_name];
What does the ‘sys’ database represent in MySQL?
The internal database used by MySQL.
What SQL command is used to set a database to read-only mode?
ALTER DATABASE [database_name] READ ONLY = 1;
What happens when a database is set to read-only mode?
Modifications aren’t allowed, but data can still be accessed.
What SQL command is used to remove the read-only status of a database?
ALTER DATABASE [database_name] READ ONLY = 0;
What is the SQL command to create a table?
CREATE TABLE [table_name];
What are the components of a table in a relational database?
Rows and columns.
A folder acts as a ______.
container
Tables are like the files contained within that folder.
How is MySQL designed to be flexible and user-friendly?
- In MySQL, keywords like
SELECT,FROM,WHERE, and others are not case-sensitive. - This means you can write them in uppercase, lowercase, or a mix of both, and MySQL will interpret them the same way.
What should you do if you want to recreate a database after dropping it?
CREATE DATABASE [database_name];
What protocols can clients use to connect to MySQL Server?
Clients can connect using:
- TCP/IP sockets
- named pipes (on Windows)
- shared memory (on Windows)
- Unix domain socket files
Named pipes and shared memory connections require specific server startup configurations.
Which programming languages are supported for writing MySQL client programs?
C, C++, Eiffel, Java, Perl, PHP, Python, Ruby, Tcl
Client libraries are available for various languages, including C and C++.
What is the purpose of the Connector/ODBC interface?
(ODBC) is a protocol that you can use to connect a Microsoft Access database to an external data source such as M̶i̶c̶r̶o̶s̶o̶f̶t̶ SQL Server
Open Database Connectivity
What does Connector/J support?
- Connector/J is a Java driver that supports connections to various database engines including MySQL.
- It implements the Java Database Connectivity (JDBC) API and extensions to the API
Can be run on both Windows and Unix.
What is the function of MySQL Connector/NET?
It enables developers to create .NET applications requiring secure, high-performance data connectivity with MySQL implementation.
It is a fully managed ADO.NET driver written in 100% pure C#.
How does MySQL support localization?
The server can provide error messages in many languages and supports various character sets.
Includes support for Scandinavian characters and several Unicode character sets.
What happens to data in MySQL regarding character sets?
All data is saved in the chosen character set
Sorting and comparisons are done according to the default character set and collation.
How can the server time zone be managed in MySQL?
The server time zone can be changed dynamically, and individual clients can specify their own time zone
This provides flexibility in handling time-related data.
Name two types of client and utility programs included in MySQL.
Command-line programs (e.g., mysqldump, mysqladmin) and graphical programs (e.g., MySQL Workbench)
These programs assist in database management and operations.
What built-in support does MySQL Server provide for table management?
SQL statements to check, optimize, and repair tables
These statements can be accessed through the mysqlcheck client.
What utility is available for performing operations on MyISAM tables?
myisamchk
It is a fast command-line utility for managing MyISAM tables.
What command can be used to obtain online assistance for MySQL programs?
–help or -?
This option provides help information for using MySQL programs.
What are two tools that allow the user to type SQL statements?
MySQL Workbench and MySQL Command-Line Client
What feature does MySQL Workbench have to reduce typing?
Automatically generate some SQL statements
When are SQL statements executed in MySQL Workbench?
When the lightning bolt is clicked
What keyboard shortcut executes all SQL statements in MySQL Workbench on Windows?
Ctrl+Shift+Enter
What keyboard shortcut executes all SQL statements in MySQL Workbench on a Mac?
Command+Shift+Enter
What does the Navigator sidebar in MySQL Workbench show after connecting to the MySQL server?
Browse Documentation and Local instance MySQL8O
What administrative options are available in the Administration tab of MySQL Workbench?
Checking server’s status, importing/exporting data, starting/stopping the MySQL server
What does the Schemas tab show in MySQL Workbench?
A list of available databases
Fill in the blank: A database can be expanded to show the database’s _______.
tables
What SQL statement is used to add a new column to an existing table?
ALTER TABLE table_name ADD COLUMN column_name data_type constraints;
Replace table_name with the name of your table, column_name with the desired name for the new column, data_type with the appropriate data type (e.g., VARCHAR, INT, DATE), and optionally add constraints.
Provide an example of adding a new column to a table.
ALTER TABLE Customers ADD COLUMN email VARCHAR(255);
This example adds an ‘email’ column of type VARCHAR with a maximum length of 255 characters to the ‘Customers’ table.
What SQL statements is used to modify an existing column’s definition?
ALTER TABLE table_name
MODIFY COLUMN column_name new_data_type new_constraints;
or
ALTER TABLE table_name
ALTER COLUMN column_name new_data_type new_constraints;
The choice between MODIFY COLUMN and ALTER COLUMN depends on the specific database system.
Provide an example of modifying an existing column.
ALTER TABLE Products MODIFY COLUMN price DECIMAL(10, 2);
This example modifies the ‘price’ column in the ‘Products’ table to be of type DECIMAL with a precision of 10 and scale of 2.
What SQL statement is used to delete a column from a table?
ALTER TABLE table_name DROP COLUMN column_name;
This statement removes the specified column from the table.
Provide an example of deleting a column from a table.
ALTER TABLE Employees DROP COLUMN department;
This example deletes the ‘department’ column from the ‘Employees’ table.
What SQL statement is used to add a new row to a table?
~~~
INSERT INTO table_name (column1, column2, …) VALUES (value1, value2, …);
This statement is used to insert a new record into the specified table.
Provide an example of adding a new row to a table.
INSERT INTO Products (name, price, category) VALUES (‘Laptop’, 1200, ‘Electronics’);
This example inserts a new product with the name ‘Laptop’, price of 1200, and category ‘Electronics’ into the ‘Products’ table.
Why isn’t the syntax for ALTER TABLE consistent across all database systems?
The exact syntax for ALTER TABLE might vary slightly depending on the specific database system being used (e.g., MySQL, PostgreSQL, SQL Server).
What should you do before making significant changes to a table?
Back up your data
It is recommended to back up your data before modifying existing tables to prevent data loss.
It is similar to asking someone to ‘choose’ or ‘identify’.
Example: ‘Select your favorite fruits’ translates to the SQL command SELECT fruits FROM table_name;
What does the SQL command SELECT do?
It specifies ‘where’ something comes from in a conversation.
Example: ‘From which store did you buy this item?’ corresponds to FROM store_table.
What is the purpose of the FROM clause in SQL?
It asks for a condition.
Example: ‘Where can I find the red apples?’ translates to WHERE color = 'red'.
What does the WHERE clause indicate in SQL?
It fetches records that have matching values in both tables.
Comparable to a group coming together to share information.
How does INNER JOIN function in SQL?
It organizes data.
Example: ‘Can you sort these books by title?’ translates to ORDER BY title.
What is the purpose of the ORDER BY clause in SQL?
It categorizes items.
Example: ‘Group the apples by their types’ corresponds to GROUP BY apple_type.
What does GROUP BY do in SQL?
It adds something into a list.
Example: ‘Add this item to my shopping list’ translates to INSERT INTO shopping_list VALUES (...).
What does the INSERT INTO command do in SQL?
It changes existing data.
Example: ‘Change the address for my subscription’ relates to UPDATE subscriptions SET address = 'new_address'.
What is the function of the UPDATE command in SQL?
It removes data from a table.
Example: ‘Remove this item from my cart’ translates to DELETE FROM cart WHERE item_id = ....
What does the DELETE command do in SQL?
Only those items that are unique
Example: SELECT DISTINCT customer_name FROM customers;
What does the SQL keyword DISTINCT represent?
Filter results after aggregation
Example: HAVING COUNT(item_id) > 5
What is the purpose of the HAVING clause in SQL?
Find values resembling a pattern
Example: WHERE name LIKE 'John%';
What does the LIKE operator do in SQL?
Select values within a specified range
Example: WHERE price BETWEEN 10 AND 20
What does the BETWEEN operator specify in SQL?
Restrict the number of results returned
Example: LIMIT 5
What does the LIMIT clause do in SQL?
Combine results from two or more queries
Example: SELECT item FROM list1 UNION SELECT item FROM list2
What does UNION do in SQL?
Include all items from one side regardless of matches
Example: LEFT OUTER JOIN or RIGHT OUTER JOIN
What is the function of an OUTER JOIN in SQL?
Join tables based on equivalent values
Example: SELECT * FROM students INNER JOIN grades ON students.id = grades.student_id
What is an EQUIJOIN?
Relate rows within the same table
Example: SELECT a.name, b.name FROM employees a, employees b WHERE a.supervisor_id = b.supervisor_id
What does a SELF JOIN achieve in SQL?
Combine all possible pairs of rows from two tables
Example: SELECT shirts.color, pants.color FROM shirts CROSS JOIN pants
What is the purpose of a CROSS JOIN?
Evaluate different conditions and return values
Example: CASE WHEN weather = 'rainy' THEN 'bring umbrella' ELSE 'wear jacket' END
What does the CASE statement do in SQL?
A query nested inside another query
Example: SELECT name FROM customers WHERE id = (SELECT customer_id FROM orders GROUP BY customer_id ORDER BY COUNT(*) DESC LIMIT 1)
What is a SUBQUERY?
Remove all records from a table
Example: TRUNCATE TABLE shopping_cart
What does the TRUNCATE command do?
Create a quick reference guide for faster searches
Example: CREATE INDEX idx_favorite_recipes ON recipes (favorite)
What is the purpose of an INDEX in SQL?
A reference that links rows in different tables
Example: Links information to a student
What is a FOREIGN KEY?
The main identifier for records in a table
Example: Uniquely identifies each user by email
What is a PRIMARY KEY?
Undo the last transaction
Example: Reverse changes if a mistake is made
What does ROLLBACK do in SQL?
Confirm and save changes made
Example: Save all changes made to the profile
What does COMMIT signify in SQL?
Remove a table or database entirely
Example: DROP TABLE old_contacts
What does DROP do in SQL?
Modify an existing database object
Example: ALTER TABLE contacts ADD COLUMN phone_number VARCHAR(15);
What is the function of the ALTER command?
Change the data type of a value
Example: CAST(number AS VARCHAR)
What does CAST do in SQL?
What is a database system instance?
A single executing copy of a database system.
What is the purpose of the CREATE DATABASE statement?
Used to create a new database.
What does the DROP DATABASE statement do?
Deletes a specified database along with its tables.
What is the SHOW DATABASES command used for?
Lists all databases in the system.
What information does the SHOW TABLES command provide?
Lists tables in the selected database.
What does the SHOW COLUMNS command do?
Shows the columns in a specified table.
What is the purpose of the SHOW CREATE TABLE command?
Displays the SQL command used to create a particular table.
What is the function of the USE statement?
Required to select the active database before executing other SHOW commands.
What algorithm does the InnoDB buffer use?
Least Recently Used (LRU) algorithm
This algorithm discards the block that has not been accessed for the longest time.
What does architecture describe in the context of MySQL?
Components of a computer system and the relationships between components
Other relational databases have similar components, but details and relationships vary.
How is Oracle Database different from MySQL in terms of storage engines?
Oracle Database has a built-in storage engine that cannot be changed, while MySQL has multiple storage engines
This is considered unusual for relational databases.
What are the four main parts organized in MySQL architecture?
- Tools Layer
- Query Processor Layer
- Storage Engine Layer
- File System Layer
What is the role of the query processor?
Manages connections and compiles queries
It translates SQL into low-level storage engine instructions.
What does a connection in MySQL authorize?
A user to access a database
What is the main function of the storage engine?
Transaction management and data access
What does the audit feature in MySQL do?
Tracks all database changes, including the time of change and who made the change
What are the two main functions of the query processor layer?
- Manage connections
- Compile queries
What is an execution plan?
A detailed, low-level sequence of steps that specify how to process a query
What is the purpose of the cache manager in the query processor layer?
Stores reusable information in main memory for optimal performance
What components are included in transaction management?
- Concurrency system
- Recovery system
- Lock manager
What types of data does the file system layer contain for each database?
- User data
- Log files
- Data dictionary
What does the MySQL data dictionary include?
Roughly 30 tables, including tables, table_stats, columns, foreign_keys, indexes, routines, and triggers
True or False: InnoDB is the default storage engine in MySQL.
True
Fill in the blank: The _______ manager retains data blocks from the file system for possible reuse.
Buffer
What does the recovery system use to restore data in the event of a failure?
Log files
What are Connectors and APIs in MySQL?
Groups of application programming interfaces linking applications to the query processor layer
What is the function of the buffer manager?
Retains data blocks in an area of main memory called the buffer
What does the query optimizer do?
Generates alternative execution plans, estimates execution times, and selects the fastest plan
What is the primary role of utility programs in MySQL?
Perform various administrative functions such as upgrading databases, backing up, and importing data
