1. What is MySQL?
MySQL is a database management system for web servers. It can grow with the website as it is highly scalable. Most of the websites today are powered by MySQL.
2. What are some of the advantages of using MySQL?
- Flexibility: MySQL runs on all operating systems
- Power: MySQL focuses on performance
- Enterprise-Level SQL Features: MySQL had for some time been lacking in advanced features such as subqueries, views, and stored procedures.
- Full-Text Indexing and Searching
- Query Caching: This helps enhance the speed of MySQL greatly
- Replication: One MySQL server can be duplicated on another, providing numerous advantages
Configuration and Security
3. What do you mean by ‘databases’?
A database is a structured collection of data stored in a computer system and organized in a way to be quickly searched. With databases, information can be rapidly retrieved.
4. What does SQL in MySQL stand for?
The SQL in MySQL stands for Structured Query Language. This language is also used in other databases such as Oracle and Microsoft SQL Server. One can use commands such as the following to send requests from a database:
SELECT title FROM publications WHERE author = ‘ J. K. Rowling’;
Note that SQL is not case sensitive. However, it is a good practice to write the SQL keywords in CAPS and other names and variables in a small case.
You can check out this SQL Tutorial to learn more about SQL.
5. What does a MySQL database contain?
A MySQL database contains one or more tables, each of which contains records or rows. Within these rows are various columns or fields that contain the data itself.
6. How can you interact with MySQL?
There are three main ways you can interact with MySQL:
using a command line
via a web interface
through a programming language
7. What are MySQL Database Queries?
A query is a specific request or a question. One can query a database for specific information and have a record returned.
8. What are some of the common MySQL commands?
| Command | Action |
|---|---|
| ALTER | To alter a database or table |
| BACKUP | To back-up a table |
| \c | To cancel Input |
| CREATE | To create a database |
| DELETE | To delete a row from a table |
| DESCRIBE | To describe a table’s columns |
| DROP | To delete a database or table |
| EXIT(ctrl+c) | To exit |
| GRANT | To change user privileges |
| HELP (\h, \?) | Display help |
| INSERT | Insert data |
| LOCK | Lock table(s) |
| QUIT(\q) | Same as EXIT |
| RENAME | Rename a Table |
| SHOW | List details about an object |
| SOURCE | Execute a file |
| STATUS (\s) | Display the current status |
| TRUNCATE | Empty a table |
| UNLOCK | Unlock table(s) |
| UPDATE | Update an existing record |
| USE | Use a database |
9. How do you create a database in MySQL?
Use the following command to create a new database called ‘books’:
CREATE DATABASE books;
10. How do you create a table using MySQL?
Use the following to create a table using MySQL:
CREATE TABLE history (
author VARCHAR(128),
title VARCHAR(128),
type VARCHAR(16),
year CHAR(4)) ENGINE InnoDB;
11. How do you Insert Data Into MySQL?
The INSERT INTO statement is used to add new records to a MySQL table:
INSERT INTO table_name (column1, column2, column3,…)
VALUES (value1, value2, value3,…)
If we want to add values for all the columns of the table, we do not need to specify the column names in the SQL query. However, the order of the values should be in the same order as the columns in the table. The INSERT INTO syntax would be as follows:
INSERT INTO table_name
VALUES (value1, value2, value3, …);
12. How do you remove a column from a database?
You can remove a column by using the DROP keyword:
ALTER TABLE classics DROP pages;
13. How to create an Index in MySQL?
In MySQL, there are different index types, such as a regular INDEX, a PRIMARY KEY, or a FULLTEXT index. You can achieve fast searches with the help of an index. Indexes speed up performance by either ordering the data on disk so it’s quicker to find your result or, telling the SQL engine where to go to find your data.
Example: Adding indexes to the history table:
ALTER TABLE history ADD INDEX(author(10));
ALTER TABLE history ADD INDEX(title(10));
ALTER TABLE history ADD INDEX(category(5));
ALTER TABLE history ADD INDEX(year);
DESCRIBE history;
14. How to Delete Data From a MySQL Table?
In MySQL, the DELETE statement is used to delete records from a table:
DELETE FROM table_name
WHERE column_name = value_name
15. How do you view a database in MySQL?
One can view all the databases on the MySQL server host using the following command:
mysql> SHOW DATABASES;
16. What are the Numeric Data Types in MySQL?
MySQL has numeric data types for integer, fixed-point, floating-point, and bit values, as shown in the table below. Numeric types can be signed or unsigned, except BIT. A special attribute enables the automatic generation of sequential integer or floating-point column values, which is useful for applications that require a series of unique identification numbers.
| Type Name | Meaning |
|---|---|
| TINYINT | Very Small Integer |
| SMALLINT | Small Integer |
| MEDIUMINT | Medium-sized Integer |
| INT | Standard Integer |
| BIGINT | Large Integer |
| DECIMAL | Fixed-point number |
| FLOAT | Single-precision floating-point number |
| DOUBLE | Double-precision floating-point number |
| BIT | Bit-field |
17. What are the String Data Types in MySQL?
| Type Name | Meaning |
|---|---|
| CHAR | fixed-length nonbinary(character) string |
| VARCHAR | variable-length nonbinary string |
| BINARY | fixed-length binary string |
| VARBINARY | variable-length binary string |
| TINYBLOB | Very small BLOB(binary large object) |
| BLOB | Small BLOB |
| MEDIUMBLOB | Medium-sized BLOB |
| LONGBLOB | Large BLOB |
| TINYTEXT | A very small nonbinary string |
| TEXT | Small nonbinary string |
| MEDIUMTEXT | Medium-sized nonbinary string |
| LONGTEXT | Large nonbinary string |
| ENUM | An enumeration; each column value is assigned, one enumeration member |
| SET | A set; each column value is assigned zero or more set members |
| NULL | NULL in SQL is the term used to represent a missing value. A NULL value in a table is a value in a field that appears to be blank. This value is different than a zero value or a field that contains spaces. |
18. What are the Temporal Data Types in MySQL?
| Type Name | Meaning |
|---|---|
| DATE | A date value, in ‘ CCYY-MM-DD ‘ Format |
| TIME | A Time value, in ‘ hh : mm :ss ‘ format |
| DATETIME | Date and time value, in ‘ CCYY-MM-DD hh : mm :ss ‘ format |
| TIMESTAMP | A timestamp value, in ‘ CCYY-MM-DD hh : mm :ss ‘ format |
| YEAR | A year value, in CCYY or YY format |
Example: To select the records with an Order Date of “2018-11-11” from a table:
SELECT * FROM Orders WHERE OrderDate='2018-11-11'19. What is BLOB in MySQL?
BLOB is an acronym that stands for a binary large object. It is used to hold a variable amount of data.
There are four types of BLOB:
- TINYBLOB
- BLOB
- MEDIUMBLOB
- LONGBLOB
A BLOB can hold a very large amount of data. For example – documents, images, and even videos. You could store your complete novel as a file in a BLOB if needed.
20. How to add users in MySQL?
You can add a User by using the CREATE command and specifying the necessary credentials. For example:
CREATE USER ‘testuser’ IDENTIFIED BY ‘sample password’;