Shulou(Shulou.com)05/31 Report--

This article mainly explains "what are the basic interview questions for MySQL". Interested friends may wish to have a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn "what are the basic MySQL interview questions"?

Relational database

What is a relational database?

Relational database refers to a database that uses a relational model to organize data, which stores data in the form of rows and columns for users to understand. A series of rows and columns in a relational database are called tables, and a group of tables make up the database. The user retrieves the data in the database through a query, which is an execution code that defines certain areas of the database.

To put it simply, a relational schema is a two-dimensional tabular model.

Two-dimensional table database

What are the advantages of relational databases?

Advantages of relational databases:

Easy to understand

The structure of relational two-dimensional table is very close to the real world, two-dimensional table, easy to understand.

To support complex queries, you can easily use SQL statements to do very complex data queries between one table and multiple tables.

Support the transaction to handle the transaction reliably and maintain the integrity of the transaction, so that the data access requirements with high security performance can be realized.

MySQL database

What is SQL?

Structured query language (Structured Query Language) referred to as SQL, is a special purpose programming language, is a database query and programming language programming language, used to access data and query, update and manage relational database systems.

What is MySQL?

MySQL is a relational database management system, and MySQL is one of the most popular relational database management systems. Common relational databases include Oracle, SQL Server, Access and so on.

MySQL has become the most popular open source database in the past because of its high performance, low cost and good reliability, and it has been widely used in small and medium-sized websites on Internet.

Mysql icon

MySQL and MariaDB can't tell each other apart?

MySQL was originally developed by the Swedish company MySQL AB, and MySQL was founded by Ulf Mikael Vidnius, commonly known as Monty.

After being acquired by Oracle, it is now owned by Oracle (Oracle). Oracle has sharply increased the price of the commercial version of MySQL, leading the free software community to worry about whether Oracle will continue to support MySQL Community Edition.

The founder of MySQL is the big guy named Monty who set up the branch plan MariaDB based on MySQL.

MariaDB intends to maintain a high degree of compatibility with MySQL, ensuring direct replacement of library binary parity, and exact matching with MySQL API application interfaces and commands. However, some open source software that used to use MySQL gradually turned to MariaDB or other databases.

So if you see that your company is using MariaDB, there is no doubt that it is still MySQL at heart. If you learn MySQL, you will know MariaDB.

A colored egg

MariaDB is named after Monty's youngest daughter Maria, just as MySQL is named after his other daughter My, two famous databases are named after two daughters, your uncle or your uncle, pops!

Father of MySQL

How do I check the current version number of MySQL?

Under the system command line: mysql-V

Connect to the MySQL command line and enter:

> status

Server: MySQL Server version: 5.5.45 Protocol version: 10

Or select version ()

+-+ | version () | +-+ | 5.5.45-xxxxx | +-+

Basic data type

What data types does MySQL have?

MySQL data types are very rich. Common types are briefly introduced as follows:

Integer types: BIT, BOOL, TINY INT, SMALL INT, MEDIUM INT, INT, BIG INT

Floating point type: FLOAT, DOUBLE, DECIMAL

String types: CHAR, VARCHAR, TINY TEXT, TEXT, MEDIUM TEXT, LONGTEXT, TINY BLOB, BLOB, MEDIUM BLOB, LONG BLOB

Date type: Date, DateTime, TimeStamp, Time, Year

Other data types: BINARY, VARBINARY, ENUM, SET...

The difference between CHAR and VARCHAR?

CHAR is a fixed-length character type and VARCHAR is a variable-length character type, which is discussed below based on versions above MySQL5.0.

Common ground

Both CHAR (M) and VARCHAR (M) indicate that the column can store M characters, not bytes!

Characteristics of CHAR types

CHAR can store up to 255 characters (not bytes), and characters have different coding sets, such as UTF8 encoding (3 bytes), GBK encoding (2 bytes), and so on.

For CHAR (M), if the actual data length stored is less than M, MySQL will automatically fill it with space characters on its right, but those filled space characters will be removed in the retrieval operation.

Characteristics of VARCHAR types

The maximum length of VARCHAR is 65535 bytes.

VARCHAR stores the actual string plus one or two bytes to record the actual length of the string. If the string length is less than 255bytes, it takes 1 byte to record, and if it exceeds 255bytes, it needs 2 bytes. [^ 12]

How many UTF8 encoded Chinese characters can be stored in VARCHAR (50)?

The number of Chinese characters stored is related to the version.

In mysql version 4. 0, varchar (50) refers to 50 bytes. If you store Chinese characters encoded in UTF8 format (3 bytes each), you can only store 16.

In mysql version 5.0 or above, varchar (50) refers to 50 characters, which can be stored no matter whether they are stored in numbers, letters or UTF8-encoded Chinese characters.

Can int (10) and bigint (10) store the same amount of data?

The specific reasons are as follows:

Int can store four bytes of signed integers.

Bigint can store eight bytes of signed integers.

So the size of the data that can be stored is different, and the number 10 represents only the display width of the data. [13]

The display width indicates the maximum number of digits that can be displayed in Mysql. When the number of digits is less than the specified width, the left side of the number will be filled with spaces, and the spaces are not easy to see.

If a value greater than the display width is inserted, the value can still be inserted and displayed as long as the value does not exceed the range of values of the type.

If you specify the zerofill option when creating the table, the part of the insufficient display width will be filled with 0, and if it is 1, it will be displayed as 0000000001.

If the display width is not specified, the default width for bigint is 20 and the default width for int is 11.

Storage engine related

What are the types of MySQL storage engines?

The commonly used storage engines are InnoDB storage engine and MyISAM storage engine. InnoDB is the default transaction engine of MySQL.

To view the engines currently supported by the database table, you can view it with the following query statement:

# the Engine field in the query result table indicates the type of storage engine. Show table status from 'your_db_name' where name='your_table_name'

What is the InnoDB storage engine application scenario?

InnoDB is the default "transaction engine" of MySQL and is set to handle a large number of short-term (short-lived) transactions, which are mostly committed normally and rarely rolled back.

What are the features of the InnoDB storage engine?

Multi-version concurrency control (MVCC,MultiVersion Concurrency Control) is used to support high concurrency. And achieve four standard isolation levels, through the gap lock next-key locking strategy to prevent the emergence of phantom reading.

The table of the engine is based on the clustered index, which has high performance for primary key query. However, its secondary index secondary index non-primary key index must contain the primary key column, so if the primary key column is large, all other indexes will be very large. Therefore, if there are more indexes on the table, the primary key should be as small as possible. In addition, the storage format of InnoDB is platform independent.

InnoDB makes a number of optimizations, such as predictable pre-reading of disk data, adaptive hash indexing (adaptive hash index) that automatically creates hash indexes in memory to speed up read operations, and insert buffers (insert buffer) that speed up insert operations.

InnoDB supports true hot backup through some mechanisms and tools. Other storage engines of MySQL do not support hot backup. To get a consistent view, you need to stop writing to all tables, and in a read-write mixed scenario, stopping writing may also mean stopping reading.

What are the four features of the InnoDB engine?

Insert buffer (Insert buffer)

Insert Buffer is used for insert and update operations of nonclustered indexes. First determine whether the inserted nonclustered index is in the cache pool, and if so, insert it directly, otherwise insert it into the Insert Buffer object. Then Insert Buffer and merge operations of secondary index leaf nodes are carried out at a certain frequency to merge multiple inserts into one operation to improve the insertion performance of nonclustered indexes.

Second write (Double write)

The Double Write consists of two parts, one is an in-memory double write buffer with a size of 2MB, and the other is a contiguous 128pages of shared tablespace on a physical disk, also the size of 2MB. When refreshing the dirty pages of the buffer pool, we do not write directly to the disk, but copy the dirty pages to this area of memory first through the memcpy function, and then write them sequentially to the physical disk of the shared tablespace twice through doublewrite buffer, and then immediately call the fsync function to synchronize the disk to avoid the problems caused by the operating system buffered write.

Adaptive hash indexing (Adaptive Hash Index)

According to the frequency and pattern of access, InnoDB will establish a hash index for hot pages to improve query efficiency. The index is constructed from the B + tree pages of the cache pool, so it is very fast to build. The InnoDB storage engine monitors queries on each index page on the table. If it is observed that the establishment of a hash index can lead to a speed increase, a hash index is established, so it is called an adaptive hash index.

Cache pool

In order to improve the performance of database, the concept of cache pool is introduced. The size of cache pool can be set by parameter innodb_buffer_pool_size, and the number of instances of cache pool can be set by parameter innodb_buffer_pool_instances. The cache pool is mainly used to store the following:

The data page types cached in the buffer pool are: index page, data page, undo page, insert buffer (insert buffer), adaptive hash index (adaptive hash index), lock information stored by InnoDB (lock info) and data dictionary information (data dictionary).

What are the application scenarios of MyISAM storage engine?

MyISAM is the default storage engine for MySQL 5.1 and earlier. MyISAM provides a number of features, including full-text indexing, compression, spatial functions (GIS), etc., but MyISAM does not "support transactions and row-level locks" and can still be used for read-only data, or for tables that are small enough to tolerate repair operations.

What are the features of the MyISAM storage engine?

MyISAM "does not support row-level locking, but locks the entire table". Shared locks are added to all tables that need to be read during reading and exclusive locks are added to the table when writing. However, when the table has read operations, it is also possible to insert new records into the table, which is called concurrent insert.

MyISAM tables can be checked and repaired manually or automatically. But unlike transaction recovery and crash recovery, it can result in some "data loss", and the repair operation is very slow.

For MyISAM tables, even long fields such as BLOB and TEXT can create indexes based on their first 500 characters, and MyISAM also supports "full-text indexes," an index based on participle that can support complex queries.

If the DELAY_KEY_WRITE option is specified, the modified index data is not immediately written to disk each time the modification is completed, but to the key buffer in memory, and the corresponding index block is written to disk only when the key buffer is cleaned or the table is closed. This approach can greatly improve write performance, but it can cause "index corruption" in the event of a database or host crash and need to be repaired.

5 differences between MyISAM and InnoDB storage engine

InnoDB supports things, while MyISAM does not support things

InnoDB supports row-level locks, while MyISAM supports table-level locks

InnoDB supports MVCC, but MyISAM does not

InnoDB supports foreign keys, but MyISAM does not

InnoDB does not support full-text indexing, while MyISAM does

A table briefly lists the main differences between the two engines, as shown in the following figure:

Mysql engine comparison

In which engine does SELECT COUNT (*) execute faster?

SELECT COUNT (*) is often used for the total number of rows of statistical tables and executes faster in the MyISAM storage engine, as long as no WHERE conditions are added.

This is because MyISAM optimizes the number of rows in the table, and internally stores the number of rows in the table with a variable. If the query condition does not have a WHERE condition, it is to query the total number of data in the table. MyISAM can quickly return the results, but not if the WHERE condition is added.

InnoDB's table also has a variable that stores the number of rows in the table, but this value is an estimate, so it doesn't make much sense.

Basic knowledge of MySQL

What is the three paradigms of database design?

1 normal form: 1NF is an atomic constraint on attributes, which requires attributes to be atomic and can not be decomposed; (as long as relational databases meet 1NF)

2 Paradigm: 2NF is a unique constraint on the record, which requires a unique identification of the record, that is, the uniqueness of the entity.

3 normal form: 3NF is a constraint on field redundancy, that is, no field can be derived from other fields, and it requires that there is no redundancy in the field. No redundant database design can do this.

However, a database without redundancy is not necessarily the best database. Sometimes, in order to improve operational efficiency, it is necessary to lower the standard of paradigm and retain redundant data appropriately. The specific approach is to follow the third normal form in the design of conceptual data model. The work of reducing the standard of normal form is considered in the design of physical data model. Lowering the normal form is to increase fields and allow redundancy.

What are the categories of SQL statements?

Hongmeng official Strategic Cooperation to build HarmonyOS Technology Community

DDL: data definition language (create alter drop)

DML: data manipulation statement (insert update delete)

DTL: data transaction statement (commit collback savapoint)

DCL: data control statement (grant revoke)

What is the difference among delete, drop, and truncate in database deletion operations?

You can use drop to delete the table when the table is no longer needed

When you still want to keep the table, but you want to delete all records, use truncate to delete the records in the table.

When you want to delete some records (generally with a WHERE clause constraint), use delete to delete some records in the table.

What is the MySql view?

The view is a virtual table that does not store data, but only contains dynamic data of the statement at the time of definition.

Create view syntax:

CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}] [DEFINER = user] [SQL SECURITY {DEFINER | INVOKER}] VIEW view_name [(column_list)] AS select_statement [WITH [CASCADED | LOCAL] CHECK OPTION]

Parameter description:

OR REPLACE: if the view exists, replace the existing view.

ALGORITHM: view selection algorithm. The default algorithm is UNDEFINED (undefined). The MySQL automatically selects the algorithm to use.

DEFINER: specifies the creator or definer of the view. If this option is not specified, the user who created the view is the definer.

SQL SECURITY:SQL security. Default is DEFINER.

Select_statement: a SELECT statement that creates a view, and you can select data from a base table or other view.

WITH CHECK OPTION: indicates that the view guarantees constraints when it is updated, and the default is CASCADED.

What are the advantages of using MySQL views?

Hongmeng official Strategic Cooperation to build HarmonyOS Technology Community

The operation is simple and convenient. The view user does not need to care about the structure, association condition and filter condition of the table corresponding to the view at all, and it is the result set of the filtered compound condition for the user.

The data is more secure. View users can only access the result set in the view, through which access to the table can be restricted to certain rows and columns.

Data isolation. Shielding the influence of the source table structure change on the user, the source table structure change view structure remains unchanged. ^ 1

What is the default port number of the MySql service?

The default port is 3306

View port command: > show variables like 'port'

Use DISTINCT to filter multi-column rules?

DISTINCT is used to de-duplicate selected data, and single-column usage is easy to understand. For example, there is the following data table tamb:

Name number Tencent 1 Alibaba 2 Bytedance 3 Meituan 3

Query statement: the SELECT DISTINCT name FROM table tamb result is as follows:

Name Tencent Alibaba Bytedance Meituan

If you are asked to press the number column to deduplicate and display name at the same time, you may write a query statement:

SELECT DISTINCT number, name FROM table tamb

The rule for removing duplicates in multi-parameter DISTINCT is to treat all parameters after DISTINCT as a filter condition, that is, to remove duplicates of (number, name) as a whole, and to remove duplicates only if this combination is different. The result is as follows:

Number name 1 Tencent 2 Alibaba 3 Bytedance 3 Meituan

From the results, it seems that we did not achieve the desired de-weight effect, so how to achieve "repeat by number column and display name at the same time"? You can use the Group By statement:

The output of SELECT number and name FROM table tamb GROUP BY number is as follows, which is exactly what we want:

Number name 1 Tencent 2 Alibaba 3 Bytedance

What is a stored procedure?

A collection of one or more sql statements with the following characteristics:

Stored procedures can achieve faster execution speed.

The stored procedure can be written with flow control statements, which has strong flexibility and can complete complex judgments and more complex operations.

Stored procedures can be fully utilized as a security mechanism.

Stored procedures can reduce network traffic

Delimiter delimiter create procedure | proc proc_name () begin sql statement end delimiter delimiter;-- restore the delimiter, in order not to affect the use of the following statements-- the default delimiter is; but in order to be reused throughout the stored procedure, you generally need a custom delimiter (except\) show procedure status like ";-- query the stored procedure, you can filter the drop procedure if exists without applying like. -- Delete stored procedures

Stored procedures and functions seem to be similar. What's the difference between them?

Stored procedures and functions are a collection of SQL statements compiled in advance and stored in the database. Calling stored procedures and functions can simplify a lot of work of application developers, reduce the transmission of data between the database and the application server, and improve the efficiency of data processing.

Identical point

Stored procedures and functions are collections of SQL statements that operate on the database for repeatable execution.

Stored procedures and functions are compiled and cached at one time, and the next time they are used, they will directly hit the compiled sql statements, reducing network interaction and improving efficiency.

Differences

The identifier is different, the identifier of the function is function, and the stored procedure is procedure.

The function returns a single value or table object, while the stored procedure does not return a value, but you can return multiple values through the OUT parameter.

There are many restrictions on functions, such as cannot use temporary tables, can only use table variables, and some functions are not available, while stored procedures have relatively few restrictions.

Generally speaking, the function of the stored procedure is a little more complex, but the function of the function is more targeted.

The argument to a function can only be of type IN, and the argument to a stored procedure can be of three types, INOUT INOUT.

Stored functions are called using select, and stored procedures need to be called using call.

At this point, I believe you have a deeper understanding of "what are the basic interview questions for MySQL?" you might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.