Summarize knowledge related to PHP interview question "Database"

This article summarizes the knowledge related to the PHP interview question "Database". It has certain reference value. Friends in need can refer to it. I hope it will be helpful to everyone.

What are the common relational database management system products?
Answer: Oracle, SQL Server, MySQL, Sybase, DB2, Access, etc.

What parts does SQL language include? What are the action keywords for each section?
Answer: SQL language includes four parts: 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 do integrity constraints include?
Data integrity (Data Integrity) refers to the accuracy (Accuracy) and reliability (Reliability) of data.
Includes:
(1) Entity integrity: stipulates that each row of the table is a unique entity in the table.
(2) Domain integrity: It means that the columns in the table must meet certain data type constraints, which include value range, precision and other regulations.
(3) Referential integrity: means that the data of the primary key and foreign key of the two tables should be consistent, ensuring the consistency of data between tables and preventing data loss or meaningless data from being stored in the database. medium spread.
(4) User-defined integrity: Different relational database systems often require some special constraints based on their application environments. User-defined integrity is a constraint for a specific relational database, which reflects the semantic requirements that a specific application must meet.

What is a transaction? and its characteristics?
Transaction: It is a series of database operations and the basic logical unit of database applications.
Features:
(1) Atomicity: that is, indivisibility, either all transactions are executed or none are executed.
(2) Consistency or stringability. The execution of a transaction converts the database from one correct state to another correct state
(3) Isolation. Before the transaction is correctly committed, any changes to the data by the transaction are not allowed to be provided to any other transaction,
(4) Durability. After a transaction is submitted correctly, its results will be permanently saved in the database. Even if there are other failures after the transaction is submitted, the processing results of the transaction will be saved.
Simple understanding: All operations in a transaction either succeed or fail.

What is a lock?
The database is a shared resource used by multiple users. When multiple users access data concurrently, multiple transactions simultaneously access the same data in the database. If concurrent operations are not controlled, incorrect data may be read and stored, destroying the consistency of the database.

Locking is a very important technology to achieve database concurrency control. Before a transaction operates on a data object, it first sends a request to the system to lock it. After locking, the transaction has certain control over the data object. Before the transaction releases the lock, other transactions cannot update the data object.
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. Views can be added, modified, checked, and operated. A view is usually a subset of rows or columns of one table or multiple tables. Modifications to the view do not affect the underlying tables. It makes it easier for us to obtain data compared to multi-table queries.

Cursor: It effectively processes the query result set as a unit. The cursor can be positioned on a specific row in the cell to retrieve one or more rows from the current row in the result set. You can make changes to the current row of the result set. Cursors are generally not used, but when data needs to be processed one by one, cursors are very important.

What is a stored procedure? What to call?
The stored procedure is a precompiled SQL statement. The advantage is that it allows a modular design, which means that it only needs to be created once and can be called multiple times in the program later. If a certain operation requires multiple executions of SQL, using stored procedures is faster than executing simple SQL statements. Stored procedures can be called using a command object.

What is the role of index? And what are its advantages and disadvantages?
An index is a special query table that the database search engine can use to speed up data retrieval. It is very similar to the table of contents of a book in real life. You can find the data you want without querying the entire book. Indexes can be unique. Creating an index allows you to specify a single column or multiple columns.

The disadvantage is that it slows down the speed of data entry and also increases the size of the database.

How to understand the three paradigms in a popular way?
First normal form: 1NF is an atomic constraint on attributes, which requires attributes to be atomic and cannot be decomposed;
Second normal form: 2NF is a unique constraint on records, which requires records to have unique identifiers , that is, the uniqueness of the entity;
Third normal form: 3NF is a constraint on field redundancy, that is, any field cannot be derived from other fields, and it requires that the fields are not redundant. .

What is a basic table? What is a view?
The basic table is a table that exists independently. In SQL, a relationship corresponds to a table.
A view is a table derived from one or several basic tables. The view itself is not stored independently in the database, but is a virtual table.
How to describe the advantages of views?
(1) Views can simplify user operations
(2) Views enable users to view the same data from multiple perspectives;
(3) Views provide a certain degree of logical independence for the database;
(4) Views can provide security protection for confidential data.

What does NULL mean?
The value NULL represents UNKNOWN (unknown): it does not represent "" (empty string).
Any comparison of the NULL value will produce a NULL value.
You cannot compare any value to a NULL value and logically expect to get an answer.
Use IS NULL for NULL judgment

What are the differences between primary keys, foreign keys and indexes?
The difference between primary key, foreign key and index
Definition:
Primary key – uniquely identifies a record, cannot be duplicated, and is not allowed to be empty
Foreign key – the foreign key of a table belongs to another table Primary key and foreign key can have duplicates and can be empty values
Index – this field has no duplicate values, but can have a null value
Function:
Primary key – used to ensure data integrity
External Key – used to establish connections with other tables
Index – is to increase the speed of query sorting
Number:
Primary key – there can only be one primary key
Foreign key – a table can have multiple Foreign Key
Indices – A table can have multiple unique indexes

What can you use to ensure that a field in a table only accepts values in a specific range?
Check restriction, which is in the database table is defined to limit the values entered into this column.

Tell me what methods are there to optimize SQL statements? (Select a few)
(1) In the Where clause: The connection between the where tables must be written before other Where conditions, and those conditions that can filter out the maximum number of records must be written at the end of the Where clause .HAVING last.
(2) Replace IN with EXISTS and NOT IN with NOT EXISTS.
(3) Avoid using calculations on index columns
(4) Avoid using IS NULL and IS NOT NULL on index columns
(5) To optimize queries, full table scans should be avoided as much as possible. First of all Consideration should be given to creating indexes on the columns involved in where and order by.
(6) Try to avoid making null value judgments on fields in the where clause, otherwise the engine will give up using the index and perform a full table scan
(7) Try to avoid making null value judgments on fields in the where clause. Expression operation, which will cause the engine to give up using the index and perform a full table scan

What is the difference between 'correlated subquery' and 'non-correlated subquery' in a SQL statement?
(1) Non-correlated subquery is a subquery that is independent of the external query. The subquery is executed once in total, and the value is passed to the external query after execution.
(2) The execution of the relevant subquery depends on the data of the external query. When the external query executes a row, the subquery is executed once.
So non-correlated subqueries are more efficient than correlated subqueries

What is the difference between char and varchar?
char is a fixed-length type, and varchar is a variable-length type.
Difference:
In a char(M) type data column, each value occupies M bytes. If a certain length is less than M, MySQL will supplement it with space characters on the right. (The padded space characters will be removed during the search operation).
In the varchar(M) type data column, each value only occupies just enough bytes plus one byte to record its length (that is, the total length is L 1 byte).

Mysql storage engine, the difference between myisam and innodb.
MyISAM is a non-transactional storage engine; suitable for applications with frequent queries; table locks, no deadlock; does not support transactions. Suitable for small data and small concurrency
Innodb is a storage engine that supports transactions; suitable for applications with a lot of insertion and update operations; if designed properly, row locks (the biggest difference lies in the level of the lock); suitable for big data, large concurrent.

What are the data table types?
MyISAM, InnoDB, HEAP, BOB, ARCHIVE, CSV, etc.

MyISAM: Mature, stable, easy to manage, fast to read. Some functions do not support (transactions, etc.), table-level locks.
InnoDB: Supports transactions, foreign keys and other features, and data row locking. It takes up a lot of space and does not support full-text indexing, etc.

The MySQL database is used as the storage for the publishing system. More than 50,000 entries are added per day. The operation and maintenance is expected to last three years. How to optimize it?
(1) Design a well-designed database structure, allow partial data redundancy, and try to avoid join queries to improve efficiency.
(2) Select the appropriate table field data type and storage engine, and add indexes appropriately.
(3) Separate mysql master-slave replication from reading to writing.
(4) Divide the data table into tables to reduce the amount of data in a single table and improve query speed.
(5) Add caching mechanism, such as redis, memcached, etc.
(6) For pages that do not change frequently, generate static pages (such as ob cache).
(7) Write efficient SQL. For example, SELECT * FROM TABEL is changed to SELECT field_1, field_2, field_3 FROM TABLE.

For a website with high traffic, what method do you use to solve the problem of statistics of page visits?
(1) Confirm whether the server can support the current traffic.
(2) Optimize database access.
(3) Prohibit external access to links (hotlinking), such as hotlinking of pictures.
(4) Control file download.
(5) Do load balancing and use different hosts to offload traffic.
(6) Use browsing statistics software to understand the number of visits and perform targeted optimization.

Series Recommended: "PHP Interview Questions (Favorites)

#

The above is the detailed content of Summarize knowledge related to PHP interview question "Database". For more information, please follow other related articles on the PHP Chinese website!