preface

MySQL about the knowledge summarized a mind map, I hope to help you all!

MySQL > select * from user where MySQL > select * from user where MySQL >

(1) Table lock: low overhead, fast lock; No deadlocks occur; The lock granularity is large, and the probability of lock conflict is high and the concurrency is low.

(2) Row-level locking: high overhead and slow locking; Deadlocks occur; The lock granularity is the lowest, the probability of lock conflict is the lowest, and the concurrency is the highest.

(3) page lock: the cost and lock time are between table lock and row lock; Deadlocks occur; The locking granularity is between table locks and row locks, and the concurrency is average.

2, What are the different tables in MySQL?

There are five types of tables:

(1) the MyISAM

(2) the Heap

(3) the Merge

(4) the INNODB

(5) the ISAM

3. Briefly describe the difference between MyISAM and InnoDB in MySQL database

MyISAM:

(1) Transactions are not supported, but each query is atomic;

(2) Support table-level locking, that is, each operation is to lock the entire table;

(3) The total number of rows stored in the table;

(4) A MYISAM table has three files: index file, table structure file, data file;

(5) Using the Phenanthrene clustered index, index file data domain stores Pointers to data files. Secondary indexes are basically the same as primary indexes, but secondary indexes are not guaranteed to be unique.

InnoDb:

(1) ACID-supported transactions, which support four isolation levels of transactions;

(2) Support row-level locking and foreign key constraints: therefore, it can support write concurrency;

(3) Total number of rows not stored:

(4) a InnoDb engine is stored in a file space (Shared table space, table size is not controlled by the operating system, a table may be distributed in multiple files), it is also possible for multiple (set the table for independence, empty table size by the operating system, file size limit for 2 g) commonly, limited by the operating system file size;

(5) Primary key index adopts clustered index (the index’s data domain stores the data file itself), and secondary index’s data domain stores the primary key value; Therefore, to search data from the secondary index, you need to find the primary key through the secondary index and then access the secondary index. It is best to use auto-increment primary keys to prevent large file adjustments when inserting data to maintain the B+ tree structure.

4, MySQL InnoDB supports four transaction isolation levels, and the difference between each level? The four isolation levels defined by the SQL standard are:

(1) Read uncommited: uncommitted data is read

(2) Read COMMITTED: Indicates dirty reads and cannot be committed

Repeatable read: repeatable read

(4) serializable: serial things

5, What is the difference between CHAR and VARCHAR?

(1) The CHAR and VARCHAR types differ in storage and retrieval

When CHAR values are stored, they are filled with Spaces to a specified length. Trailing Spaces are removed when retrieving CHAR values.

What is the difference between a primary key and a candidate key?

Each row of a table is uniquely identified by a primary key, and a table has only one primary key.

Primary keys are also candidates. By convention, candidate keys can be specified as primary keys and can be used for any foreign key reference.

7. What is Myisamchk used for?

It is used to compress MyISAM tables, which reduces disk or memory usage.

What is the difference between MyISAM Static and MyISAM Dynamic?

All fields on MyISAM Static have fixed widths. Dynamic MyISAM tables will have fields like TEXT, BLOB, and so on to accommodate data types of different lengths.

MyISAM Static is easier to recover from damage.

8. What happens if a table has a column defined as TIMESTAMP?

The timestamp field gets the current timestamp whenever the row is changed.

What happens if the maximum value in the table is reached when the column is set to AUTO INCREMENT?

It stops incrementing, and any further inserts will generate an error because the key has already been used.

How do I find out which automatic increment was allocated on the last insert?

LAST_INSERT_ID returns the last value assigned by Auto_increment and does not specify the table name.

9. How do you see all the indexes defined for the table?

Indexes are defined for tables in the following way:

SHOW INDEX FROM ;

10, What do % and _ in LIKE declarations mean?

% corresponds to zero or more characters, and _ is just one character in a LIKE statement.

How to convert between Unix and MySQL timestamps?

UNIX_TIMESTAMP is a command that converts MySQL timestamp to Unix timestamp

FROM_UNIXTIME is the command to convert a Unix timestamp to a MySQL timestamp

What is the column comparison operator?

Use the =, <>, <=, <, > =, >, <<, >, <=>, AND, OR, OR LIKE operators in column comparisons in SELECT statements.

What’s the difference between a BLOB and a TEXT?

A BLOB is a binary object that can hold a variable amount of data. TEXT is a case insensitive BLOB.

The only difference between BLOB and TEXT types is that BLOB values are case-sensitive when sorting and comparing, while TEXT values are case-insensitive.

What is the difference between MySQL_fetch_array and MySQL_fetch_object

MySQL_fetch_array differs from MySQL_fetch_object:

MySQL_fetch_array () – Returns the result row as an associative array or as a regular array from the database.

MySQL_fetch_object – Returns the result row as an object from the database.

14. Where will MyISAM tables be stored and their storage format provided?

Each MyISAM table is stored on disk in three formats:

(1) · “. FRM “file storage table definition

(2) · The data file has the extension “.myd “(MYData)

(3) The index file has the extension “.myi “(MYIndex)

MySQL: How to optimize DISTINCT?

DISTINCT is converted to GROUP BY on all columns and is used in combination with the ORDER BY clause.

SELECT DISTINCT t1.a FROM t1,t2 where t1.a=t2.a;

16. How to display the first 50 lines?

In MySQL, query to display the first 50 lines using the following code:

SELECT*FROM

LIMIT 0, 50;

17, How many columns can be used to create an index?

Up to 16 index columns can be created for any standard table.

What’s the difference between NOW () and CURRENT_DATE ()?

The NOW () command is used to display the current year, month, date, hour, minute, and second.

CURRENT_DATE () displays only the current year, month, and date.

What is a nonstandard string type?

(1) TINYTEXT

(2) the TEXT

(3) the MEDIUMTEXT

(4) LONGTEXT

What are generic SQL functions?

(1) CONCAT(A, B) – Concatenate two string values to create A single string output. Usually used to combine two or more fields into a single field.

(2) FORMAT(X, D)- FORMAT the valid numbers from X to D.

(3) CURRDATE(), CURRTIME()- Returns the current date or time.

(4) NOW () – Returns the current date and time as a value.

(5) MONTH (), DAY (), YEAR (), WEEK (), WEEKDAY () – Extracts the given data from the date value.

(6) HOUR (), MINUTE (), SECOND () – Extract the given data from the time value.

(7) DATEDIFF (A, B) — Determine the difference between two dates, usually used to calculate age

(8) SUBTIMES (A, B) — Determine the difference between two times.

(9) FROMDAYS (INT) – Converts integer days to date values.

Does MySQL support transactions?

By default, MySQL is in autoCOMMIT mode. All database updates are committed immediately, so MySQL does not support transactions by default.

SETAUTOCOMMIT=0 allows MySQL to use non-autoCOMMIT mode. In non-autocommit mode, you must COMMIT your changes using COMMIT, or ROLLBACK your changes with ROLLBACK.

22, what is the best field type to record currency in MySQL

The NUMERIC and DECIMAL types are implemented by MySQL as the same type, which is allowed by the SQL92 standard. They are used to hold values whose precise accuracy is extremely important, such as data relating to money. Precision and size can be (and usually are) specified when declaring a class to be one of these types.

Such as:

Salary is a DECIMAL (9, 2)

In this example, 9(precision) represents the total number of decimal places that will be used to store values, while the 2(scale) surrogate table will be used to store the number of places after the decimal point.

So, in this case, the range of values that can be stored in the SALARY column is from -9999999.99 to 9999999.99.

MySQL > select * from ‘MySQL’;

The MySQL server controls user access to the database through the permission table, which is stored in the MySQL database and initialized by the MySQL_install_db script. These permission tables are user, DB, table_priv, columns_priv, and host.

24. What can be the string type of a column?

The string type is:

(1) SET2

(2) the BLOB

(3) the ENUM

CHAR (4)

(5) the TEXT

MySQL database as the storage of the release system, more than 50,000 increments a day, is expected to operate and maintain for three years, how to optimize?

(1) Well-designed database structure allows partial data redundancy, avoids join query as far as possible, and improves efficiency.

(2) Select the appropriate table field data type and storage engine, and add indexes appropriately.

(3) MySQL database master separated from read and write.

(4) Find the regular table, reduce the amount of data in a single table to improve the query speed.

(5) Add caching mechanisms such as memcached, APC, etc.

(6) do not often change the page, generate static pages.

(7) Write efficient SQL. SELECT field_1, field_2, fieldd_3 FROM TABLE.

Lock optimization strategy

(1) Read and write separation

(2) section lock

(3) Reduce the lock holding time

(4) Multiple threads try to acquire resources in the same order

The granularity of the lock should not be too refined, otherwise there may be too many times of locking and releasing threads, but the efficiency is not as good as adding a large lock at a time.

The underlying implementation principle and optimization of index

B+ tree, optimized B+ tree

InnoDB recommends using the default primary key increment as the primary index for most tables by adding a pointer to the next leaf.

When an index is set but cannot be used

(1) LIKE statement starting with “%”, fuzzy matching

(2) do not use index before OR after OR statement

(3) Data type implicit conversion (such as vARCHar without single quotation marks may be automatically converted to int)

29, How to optimize MySQL in practice

It is best to optimize in the following order:

(1) OPTIMIZATION of SQL statement and index

(2) optimization of database table structure

(3) Optimization of system configuration

(4) Hardware optimization

30. Methods to optimize the database

(1) Select the most applicable field attributes, minimize the width of the defined field, and try to set the field as null as possible. For example, ‘province’ and ‘gender’ are best applied to ENUM

(2) Use JOIN instead of subquery

(3) Use unions instead of manually created temporary tables

(4) Transaction processing

(5) Lock the table and optimize transaction processing

(6) Apply foreign key, optimize lock table

(7) Index building

(8) Optimize the query statement

31, simple description in MySQL, index, primary key, unique index, joint index, the difference between what is the effect on the performance of the database (from two aspects of reading and writing)

Indexes are special files (indexes on InnoDB tables are part of the table space) that contain Pointers to all the records in the table.

The only job of a plain INDEX (an INDEX defined by the KEY or INDEX keyword) is to speed up access to data.

Normal indexes allow indexed data columns to contain duplicate values. If you can determine that a column will contain only values that differ from each other, you should define it as a UNIQUE index when creating an index for that column using the keyword UNIQUE. In other words, a unique index guarantees the uniqueness of the data record.

A PRIMARY KEY is a special unique index. Only one PRIMARY KEY can be defined in a table. A PRIMARY KEY is used to uniquely identify a record.

An INDEX can cover multiple data columns, such as the INDEX(columnA, columnB) INDEX, which is a federated INDEX.

Indexes can greatly improve the speed of querying data, but slow down the speed of inserting, deleting, and updating tables because of the need to manipulate index files while performing these writes.

What is a transaction in a database?

A transaction is an ordered set of database operations as a unit. The transaction is considered successful if all operations in the group succeed, even if only one operation fails. If all operations are complete, the transaction commits and its changes apply to all other database processes. If an operation fails, the transaction is rolled back and the effects of all operations on the transaction are cancelled.

Transaction features:

(1) Atomicity: that is, indivisibility. All transactions are either executed or not executed.

(2) consistency or seriability. The execution of the transaction causes the database to transition from one correct state to another.

(3) Isolation. Any changes made to the data by that transaction are not allowed to be made available to any other transaction until the transaction has committed correctly.

(4) Persistence. Once a transaction is committed correctly, its results are permanently stored in the database, even if other failures occur after the transaction is committed.

Or to put it this way:

A transaction is a group of SQL statements that are bound together as a logical unit of work. If any statement fails, the entire operation fails, and the operation is then rolled back to its previous state or has a node on it. Transactions can be used to ensure that either they are executed or they are not. To consider groups of statements as transactions, you need to pass the ACID test, which is atomicity, consistency, isolation, and persistence.

33, The cause of SQL injection vulnerability? How to prevent it?

The cause of SQL injection: During the process of program development, the client can submit some SQL statements through the global variables POST and GET for normal execution without paying attention to the standard writing of SQL statements and filtering of special characters.

Ways to prevent SQL injection:

Enable the magic_QUOtes_GPC and magic_QUOtes_Runtime Settings in the configuration file

Addslashes is used for SQL statement conversion when executing SQL statements

Do not omit double and single quotation marks when writing Sql statements.

Filter out some keywords in SQL statements: UPDATE, INSERT, delete, select, *.

Improve the database table and field naming skills, some important fields according to the characteristics of the program named, not easy to guess.

34, Select the appropriate data type for the fields in the table

Field type Priority: Integer > Date,time>enum,char> VARCHar >blob,text

The numeric type is preferred, followed by the date or binary type, and finally the string type. In the same level of data type, the data type that occupies less space should be preferred

35. Storage period

Datatime:

Timestamp: Saves the time period in yyyY-MM-DD HH:MM:SS format, accurate to second, occupying 8 bytes of storage space. Datatime type is independent of time zone Timestamp: Saves the time in Timestamp format, occupying 4 bytes, smaller 1970-1-1 to 2038-1-19. The display depends on the time zone specified. By default, the value of the TIMESTAMP column can be changed automatically when the data in the first column is changed

Date:

The number of bytes used is less than the string.datatime.int, which takes only 3 bytes, stores the date and month, and can be calculated using the date and time function

Time:

Store time – part data

Note:

Do not use string type to store date and time data (usually takes up less storage space than string, you can use the date function in search filtering)

Using int to store dates and times is not as good as using timestamp

Indexing is a very important concept for relational databases. Please answer some questions about indexing:

(1) What is the purpose of the index?

Fast access to specific information in data tables to improve retrieval speed

Create unique indexes to ensure the uniqueness of each row in a database table.

Accelerometers and connections between tables

When using grouping and sorting clauses for data retrieval, the grouping and sorting time in queries can be significantly reduced

(2) What is the negative impact of index on database system?

Negative effects:

Creating and maintaining indexes takes time, which increases with the volume of data; Indexes take up physical space. Not only tables take up data space, but each index takes up physical space. Indexes are maintained dynamically when tables are added, deleted, or modified, which slows down data maintenance.

(3) What are the principles for indexing data tables?

Build indexes on the most frequently used fields to narrow the query.

Build indexes on frequently used fields that need to be sorted

(4) Under what circumstances should not establish index?

Indexes are not appropriate for columns that are rarely involved in a query or that have a lot of duplicate values.

For some special data types, such as text fields, indexes are not appropriate

Explain the difference between external join, internal join and self-join in MySQL

Cross join: A cross join, also called a Cartesian product, matches all the records in one table with all the records in another table without using any criteria.

In the connection

Is the cross-join with only conditions, according to a condition to screen out the records that meet the conditions, the records that do not meet the conditions will not appear in the result set, that is, the inner join only connects the matched rows.

Outer join

The result set contains not only the rows that meet the join criteria, but also all data rows in the left table, right table, or two tables, which in turn are called left outer join, right outer join, and full outer join.

The left outer join

Also known as left join, the left table is the primary table, all the records in the left table will appear in the result set, for those records that do not match in the right table, will still be displayed, the corresponding fields on the right are filled with NULL values. Right outer join, also known as right join, the right table is the main table, and all records in the right table will appear in the result set. Left join and right join are interchangeable, MySQL does not currently support full external join.

Overview of transaction rollback mechanism in Myql

A transaction is a sequence of database operations defined by users. These operations either do all or do not do all, which is an inseparable unit of work. Transaction rollback refers to the cancellation of the update operation to the database that has been completed by the transaction.

When you want to modify two different tables in the database at the same time, if they are not a transaction, when the first table is modified, there may be an exception in the process of modifying the second table and it cannot be modified. At this time, only the second table is still in the state before the modification, and the first table has been modified. When you set them as a transaction, when the first table is modified and the second table fails to be modified, both the first table and the second table return to the unmodified state. This is called transaction rollback

What are the parts of SQL language? What are the operation keys for each section?

SQL language includes data definition (DDL), data manipulation (DML), data control (DCL) and data query (DQL).

Data Definition:

Create Table,Alter Table,Drop Table, Craete/Drop Index, etc

Data manipulation:

Select ,insert,update,delete,

Data control:

grant,revoke

Data query:

select

What are integrity constraints?

Data Integrity refers to the Accuracy and Reliability of Data.

Divided into the following four categories:

(1) Entity integrity:

Specifies that each row of a table is a unique entity in the table.

(2) Domain integrity:

Columns in a table must meet certain data type constraints, including value range and precision.

(3) Referential integrity:

The data of the primary and external keywords of two tables should be the same. This ensures data consistency between tables and prevents data loss or meaningless data from spreading in the database.

(4) User-defined integrity:

Different relational database systems often need some special constraints according to their different application environments. User-defined integrity is a constraint for a specific relational database, which reflects the semantic requirements that a specific application must meet.

Table related constraints:

These include column constraints (NOT NULL) and table constraints (PRIMARY KEY, Foreign KEY, check, and UNIQUE).

What is a lock?

A database is a shared resource used by multiple users. When multiple users concurrently access data, multiple transactions simultaneously access the same data in a database. Uncontrolled concurrent operations can read and store incorrect data, damaging the consistency of the database.

Locking is a very important technology to realize database concurrency control. A transaction makes a request to the system to lock a data object before operating on it. After the lock is locked, the transaction has some control over the data object, and no other transaction can update the data object until the transaction releases the lock.

Basic lock types: Locks include row-level locks and table-level locks

What is a view? What is a cursor?

A view is a virtual table that has the same functions as a physical table. You can add, modify, query, and manipulate views, which are usually rows or subsets of columns in one or more tables. Changes to the view do not affect the base table. It makes it easier for us to get data compared to multi-table queries.

Cursor: the effective processing of the result set as a unit. Cursors can be fixed to specific rows in the cell, retrieving one or more rows from the current line of the result set. You can modify the current row of the result set. Cursors are not generally used, but they are important when you need to process data item by item.

What is a stored procedure? With what?

A stored procedure is a precompiled SQL statement that has the advantage of allowing modular design, meaning that it is created once and can be called many times later in the program. If an operation requires multiple SQL executions, using stored procedures is faster than simply executing SQL statements. A stored procedure can be invoked with a command object.

44. How to understand the three paradigms popularly?

The first normal form: 1NF is the atomicity constraint on attributes, which requires attributes to have atomicity and cannot be decomposed again.

Second normal form: 2NF is a constraint on the uniqueness of records, requiring records to have a unique identity, that is, the uniqueness of entities;

Third normal form: 3NF is a constraint on field redundancy, that is, no field can be derived from another field, it requires that the field have no redundancy.

Advantages and disadvantages of formal design:

Advantages: Data redundancy can be reduced as far as possible, making the update fast and small

Disadvantages: Multiple tables are required for query association, which reduces write efficiency and increases read efficiency, and makes index optimization more difficult

Antiformalization:

Advantages: can reduce table association, can better index optimization

Disadvantages: Data redundancy and data anomalies, data modification needs more cost

What is a basic table? What is a view?

A base table is a table that stands on its own. In SQL, a relationship corresponds to a table. A view is a table exported from one or more base tables. The view itself is not stored independently in the database and is a virtual table

46. Describe the advantages of view?

(1) View can simplify user operations

(2) Views enable users to view the same data from multiple perspectives;

(3) Views provide a degree of logical independence for the database;

(4) View can provide security protection for confidential data.

47. What does NULL mean

NULL This value stands for UNKNOWN: it does not represent “” (an empty string). Any comparison of the NULL value produces a NULL value. You cannot compare any value to a NULL value and logically expect an answer.

Use IS NULL for NULL determination

What is the difference between a primary key, a foreign key, and an index?

Differences between primary keys, foreign keys, and indexes

Definition:

Primary key – uniquely identifies a record, cannot be duplicated, and cannot be empty

Foreign key – The foreign key of a table is the primary key of another table. Foreign keys can have duplicate or null values

Index – This field has no duplicate value, but can have a null value

Function:

Primary key – used to ensure data integrity

Foreign key – used to establish relationships with other tables

Index – speed up query sorting

The number:

Primary key – There can only be one primary key

Foreign keys – A table can have more than one foreign key

Indexes – A table can have multiple unique indexes

49. What can you use to ensure that fields in a table accept only values in a specified range?

The Check limit, which is defined in the database table, is used to limit the values entered for that column.

Triggers can also be used to limit the values that fields in a database table can accept, but this approach requires triggers to be defined in the table, which can affect performance in some cases.

What are the methods of SQL statement optimization? (Select a few)

Joins between Where tables must precede other Where conditions, and conditions that filter out the maximum number of records must precede the end of the Where clause. HAVING finally.

(2) replace IN with EXISTS and NOT EXISTS with NOT IN.

(3) Avoid using calculations on index columns

(4) Avoid using IS NULL and IS NOT NULL on index columns

(5) To optimize the query, avoid full table scan as far as possible, and first consider establishing indexes on where and order by columns.

(6) Avoid null values for fields in the WHERE clause, otherwise it will cause the engine to abandon the use of indexes and perform full table scan

(7) Avoid expression operations on fields in the WHERE clause, which will cause the engine to abandon the use of indexes and perform full table scans

The last

The latest Java interview questions manual for 2020 (200 + pages PDF document).