Introduction to MySQL
MySQL is a leading open-source database management system. It is a multi-user, multithreaded database management system. MySQL is especially popular on the web. It is one of the parts of the very popular LAMP platform. Linux, Apache, MySQL, and PHP. MySQL database is available on the most important OS platforms. It runs on BSD Unix, Linux, Windows, or Mac. Wikipedia, YouTube, Facebook use MySQL. These sites manage millions of queries each day. MySQL comes in two versions: MySQL server system and MySQL embedded system. The MySQL server software and the client libraries are dual-licensed: GPL version 2 and proprietary license.
The development of MySQL began in 1994 by a Swedish company MySQL AB. Sun Microsystems acquired MySQL AB in 2008. Sun was bought by Oracle in 2010.
MySQL, PostgreSQL, Firebird, SQLite, Derby, and HSQLDB are the most well-known open-source database systems.
MySQL is developed in C/C++. Except for C/C++, APIs exist for PHP, Python, Java, C#, Eiffel, Ruby, Tcl, or Perl.
1. What is MySQL?
Answer: MySQL is a relational database management system (RDBMS) developed by Oracle that is based on structured query language (SQL). A database is a structured collection of data. It may be anything from a simple shopping list to a picture gallery or a place to hold the vast amounts of information in a corporate network.
2. Can a primary key be dropped in MySQL? If yes, how?
Answer:
Yes, the primary key can be dropped in MySQL with the use of the “ALTER TABLE” statement.
Syntax:
ALTER TABLE table_name DROP PRIMARY KEY
3. How to write an optimized query in MySQL?
Answer: To write an optimized query in MySQL, below are some guidelines:
Functions should not be used in predicates.
At the beginning of predicates, do not use the wildcard symbols such as %.
Use only needed columns in the SELECT clause.
Always use the inner join option.
Use of the “Order by” clause is necessary for SQL, in case of assuming the sorted results.
4. What is the difference between delete, drop and truncate?
Answer:
TRUNCATE
It removes all rows from a table.
It does not require a WHERE clause.
Truncate cannot be used with indexed views.
It is performance-wise faster.
DELETE
It removes Some or All rows from a table.
A WHERE clause is used to remove particular rows according to the matched condition. All rows will be deleted when we did not use the Where condition in Query.
It removes rows one by at a time.
It can be used with indexed views.
DROP
It removes a table from the database.
All table rows, indexes, and privileges will also be removed when we used this command.
The operation cannot be rolled back.
5. Write a query to fetch duplicate records from a table using MySQL?
Answer: Answer: SELECT EmpId, Project, Salary, COUNT() FROM EmployeeSalary GROUP BY EmpId, Project, Salary HAVING COUNT() > 1;
6. What are the different ways to optimize a MySQL query?
Answer:
Don’t use “sp_” while creating a stored procedure.
Don’t use “*” while you are using the SELECT command. Also, avoid unnecessary columns in the SELECT clause.
You can use table aliases while writing queries.
Avoid the usage of the wildcard (%) at the starting of a predicate.
DISTINCT and UNION need to be used solely if it is necessary.
7. What is cardinality in MySQL?
Answer: In MySQL, the time period cardinality refers to the specialty of facts values that can be put into columns. It is a type of property that influences the potential to search, cluster, and kind data.
Cardinality can be of two sorts which are as follows
Low Cardinality − All values for a column have to be the same.
High Cardinality − All values for a column ought to be unique.
8. What is the difference between the primary and unique keys in Mysql?
Answer:
9. What are DDL, DML, and DCL in MySQL?
Answer:
- DDL
In MySQL, DDL is the short form for Data Definition Language, which is used in database schemas and descriptions while deciding how data should reside in the database.
Here’s a list of DDL Queries:
CREATE
ALTER
DROP
TRUNCATE
COMMENT
RENAME
- DML
DML is a short form for Data Manipulation Language which is used in data manipulation and mostly includes common SQL statements to store, modify, retrieve, delete and update data in a database.
Here is the list of DML Queries:
SELECT
INSERT
UPDATE
DELETE
MERGE
CALL
EXPLAIN PLAN
LOCK TABLE
- DCL
DCL is a short form for Data Control Language including commands which are concerned with User rights, permissions and other controls within the database system.
Here’s a list of queries for DCL:
GRANT
REVOKE
10. What is the default port for MySQL and how it can change?
Answer: The default port is 3306. We can change it in /etc/MySQL/my.conf there is a port variable. We can update this port according to our need
11. Explain the difference between primary key and candidate key in Mysql?
Answer: Primary Key
It is a column that uniquely identifies a record. In Mysql, only one candidate key can behave like a Primary Key.
Candidate Key
It can be any column that can qualify as a unique key in the database. In MySQL, there can be multiple candidate keys in one table. Each candidate key can behave as a primary key.
12. What is TRIGGERS and how it can be used in MySQL?
Answer: In Mysql, a trigger is a database object that is directly associated with a table. It will be activated when a defined action is executed for the table. It can be performed when you run one of the following MySQL like INSERT, UPDATE, and DELETE occurred in a table. Its activation time can be BEFORE or AFTER
13. List some comparisons operators used in Mysql?
Answer: Comparisons operators are used to comparing one expression to another value or expression. It is just like = , < , > , => , =<, <>
14. What is the difference between IS NULL & IS NOT NULL?
Answer:
IS NULL checks to see if the cell is empty but IS NOT NULL checks to see if the cell is not empty.
Example :
SELECT id FROM users WHERE ‘user_type’ IS NOT NULL;
SELECT id FROM users WHERE ‘user_type’ NOT IS NULL;
15. How would you select all the users whose phone is null MySQL?
Answer: SELECT id, name FROM users WHERE phone IS NULL;
16. What is the difference between MyISAM dynamic and MyISAM static in MySQL?
Answer: MyISAM Dynamic
This is a table storage format that is basically used when some columns in the existing tables use variable data types such as VARCHAR, BLOB, TEXT, VARBINARY, etc.)
MyISAM static
This is also a table storage format that is basically used when all columns in the existing tables use fixed-width data types.
17. What is the difference between MongoDB and MySQL?
Answer:
18. How to store binary data in MySQL?
Answer:
Binary data can be stored in Mysql in different ways such as
The short length of binary data can be stored in the form of string as a VARCHAR
A BLOB data type allows users to store an arbitrary amount of binary data.
19. How to make copy values from one column to another in Mysql?
Answer: PDATE your_table_name SET new_field=old_field
20. What are the differences between mysql_fetch_array () mysql_fetch_object () mysql_fetch_row ()?
Answer:
21. How to save images in MySQL?
Answer: Images in MySQL can be stored as blobs. For saving them: All the database images are converted into the blobs first. Then, they will get inserted into the database, and later on, it will get stored into the disk.
22. What’s new in MySQL 8?
Answer:
There are a number of unique features that are incorporated in Mysql 8 including-
Unicode 9.0 support
Window functions
Recursive SQL syntax statements
Support Native JSON data
Support for document store Functionality
23. What is federated tables in MySQL?
Answer: It is a table that points to a table in another MySQL database instance. It can be seen as a view of this remote database. Another RDBMS has the same concepts for example database links.
24. Is Mysql is a programming language?
Answer: It is a language but not a Programming Language. It is a relational database management system.
25. What are the advantages of MySQL?
Answer:
- Data Security
- It is used to retrieve large amounts of records from a database quickly and efficiently.
- On-Demand Scalability
- High Performance
- Excellent Uptime
- Reduced Total Cost of Ownership
26. What are Storage Engines in Mysql?
Answer:
Storage engines are Mysql components, that can handle the operations for different table types to store and manage information in a database.
- InnoDB
- MyISAM
- MEMORY
- MERGE
- EXAMPLE
- CSV
- ARCHIVE etc
27. What is the difference between CHAR and VARCHAR in MySQL?
Answer:
- CHAR can have a maximum of 255 characters, but VARCHAR can hold a maximum of 65,535 characters.
- CHAR field is a fixed length, but VARCHAR is a variable-length field.
- CHAR uses static memory allocation, but VARCHAR uses dynamic memory allocation.
28. Write a query to find duplicate rows In the table?
Answer: SELECT std_id, COUNT(std_id) as cnt FROM Student GROUP by std_id having cnt > 1
29. How to display odd rows in the Employee table in Mysql?
Answer: SELECT id, name, department FROM Employee where MOD(id,2) = 1
30. What is the difference between having and where clause in Mysql? Explain
Answer:
WHERE term is used for filtering rows, and it applies to every row but HAVING term is used to filter groups.
WHERE can be used without the GROUP BY but the HAVING clause cannot be used without the GROUP BY
31. What is constraints? Also, explain the different types of constraints?
Answer:
These are the set of rules applied to columns on the table. It is used to bound the type of data that can go into a table.
- Types of constraints
- PRIMARY KEY
- FOREIGN KEY
- UNIQUE:
- Not NULL
32. What is a heap table in MySQL?
Answer: HEAP tables are in memory. No TEXT or BLOB fields are allowed inside HEAP tables. They are typically used for high-speed transient storage.
33. What is a temporary table in MySQL?
Answer: In MySQL, a temporary table is a distinct type of table that approves you to save a transient end result set, in which you can reuse countless instances in a single session. A brief table is created with the aid of the use of the CREATE TEMPORARY TABLE statement.
34. What is the degree of a table in MySQL?
Answer: The degree of a table is the number of columns in the given table. It is also called as Arity. It is the range of entities inside each entity type that can be linked via a given relationship type.
35. How to convert a string to utf 8 in MySQL?
Answer: Here’s a code script to convert string data to UTF 8 in your database:
header(‘Content-Type: text/plain; charset=utf-8’);
$databasesToConvert = [ ‘database1’ */ ];
$typesToConvert = [ ‘char’, ‘varchar’, ‘tinytext’, ‘mediumtext’, ‘text’, ‘longtext’];
CONVERT(CAST(CONVERT({$column} USING english) AS binary) USING utf8)”;
$query = “UPDATE {$table} SET ” . join( ‘, ‘, $converts );
echo “\n”, $query, “\n”;
echo “\n”;
36. What is the difference between timestamp and datetime in MySQL?
Answer:
Timestamp in MySQL
Timestamp in MySQL is used when users want to convert the value from the current time zone to UTC zone for the sake of storage or UTC time zone to the current time zone for retrieval.
DateTime in MySQL
DateTime in MySQL is used to insert values ‘yyyy-mm-dd’ in the table where yyyy is the year, mm is the month and dd is the date. This keyword is used to retrieve or to display DATETIME values in Mysql.
37. What is a view in MySQL?
Answer: The view is basically a virtual table that is used to view certain and preferable rows or columns of the table. In practice, users can add a number of SQL functions, JOIN, and WHERE clauses to view the present data of the table.
38. How do you create the view in MySQL?
Answer:
In MySQL, the CREATE VIEW statement is used to create a new view inside the database.
Here’s the basic syntax of the statement:
CREATE VIEW product list ASSELECT qty, price, totalprice AS value FROM product;
39. What are the advantages of MySQL when compared with Oracle?
Answer:
- MySQL is open-source software that is available at any time and has no cost involved.
- MySQL is portable
- GUI with command prompt.
- The administration is supported using MySQL Query Browser
40. Differentiate CHAR_LENGTH and LENGTH?
Answer: CHAR_LENGTH is character count whereas LENGTH is byte count. The numbers are the same for Latin characters but they are different for Unicode and other encodings.
41. How to represent ENUMs and SETs internally?
Answer: CHAR_LENGTH is character count whereas LENGTH is byte count. The numbers are the same for Latin characters but they are different for Unicode and other encodings.
42. Define REGEXP?
Answer: REGEXP is a pattern match that matches the pattern anywhere in the search value.
43. Give string types available for columns?
Answer:
The string types are:
- SET
- BLOB
- ENUM
- CHAR
- TEXT
- VARCHAR
44. What, if a table has one column defined as TIMESTAMP?
Answer: Timestamp field gets the current timestamp whenever the row gets altered.
45. How can we convert between Unix & MySQL timestamps?
Answer:
UNIX_TIMESTAMP is the command which converts from MySQL timestamp to Unix timestamp
FROM_UNIXTIME is the command which converts from Unix timestamp to MySQL timestamp.
46. What is ISAM?
Answer: ISAM is abbreviated as Indexed Sequential Access Method. It was developed by IBM to store and retrieve data on secondary storage systems like tapes.
47. How many columns can be used for creating an Index?
Answer: Maximum of 16 indexed columns can be created for any standard table.
48. What is the difference between NOW() and CURRENT_DATE()?
Answer:
NOW () command is used to show the current year, month, date with hours, minutes, and seconds.
CURRENT_DATE() shows the current year, month, and date only.
49. What are the nonstandard string types?
Answer: Following are Non-Standard string types:
- TINYTEXT
- TEXT
- MEDIUMTEXT
- LONGTEXT
50. Explain Access Control Lists.
Answer:
An ACL (Access Control List) is a list of permissions that is associated with an object. This list is the basis for the MySQL server’s security model and it helps in troubleshooting problems like users not being able to connect.
MySQL keeps the ACLs (also called grant tables) cached in memory. When a user tries to authenticate or run a command, MySQL checks the authentication information and permissions against the ACLs, in a predetermined order.
- DevOps Certified Professionals (DCP)
- Site Reliability Engineering Certified Professionals (SRECP)
- Master in DevOps Engineering (MDE)
- DevSecOps Certified Professionals (DSOCP)
URL - https://www.devopsschool.com/certification/
My Linkedin - https://www.linkedin.com/in/rajeshkumarin
- Apache Lucene Query Example - April 8, 2024
- Google Cloud: Step by Step Tutorials for setting up Multi-cluster Ingress (MCI) - April 7, 2024
- What is Multi-cluster Ingress (MCI) - April 7, 2024
