2-Interview-database

MySQL built-in 1, views, usage scenarios

A view is a virtual table that has the same functionality as a physical table. The view can be increased, changed, checked, manipulated,
A view is typically a subset of rows or columns that have a table or multiple tables.
Changes to the view affect the base table.
It makes it easier for us to get data, compared to multiple table queries.

Pros and cons of views
Advantages:
1) access to the database, because the view can have a selective selection of the part of the database.
2) Users can get results from complex queries through simple queries.
3) Maintain the independence of data and try to retrieve data from multiple tables.
4) Different views can be generated for the same data.

Disadvantages:
1) Performance: When querying a view, you must turn the query of the view into a query to the base table.
If the view is defined by a complex multi-table query, then the data cannot be changed
2) Strong coupling: The SQL used in our program relies too much on the views in the database

The following two scenarios are typically used in the view:
(1) Do not want visitors to get information about the whole table, only to expose some fields to the visitor, so build a virtual table, that is, the view.
(2) The data of the query originates from different tables, and the query person wants to query in a unified way, so it can also set up a view, combine the results of multiple table query, the query only need to get the data directly from the view, do not have to consider the difference of the data from the different tables.
Note: This view is created in the database, not in code.

SQL statements

#语法： CREATE VIEW 视图名称 AS  SQL语句create view course_view as select * from course;  #创建表course的视图

2. Cursors

A cursor is actually a mechanism that can extract one record at a time from a result set that includes multiple data records.
A cursor is an efficient processing of a query-out result set as a unit.
A cursor can be set to a specific row in that cell, retrieving one or more rows from the current row of the result set.
You can make modifications to the current row of the result set.
Cursors are generally not used, but cursors are important when you need to process the data individually.

3. What is a stored procedure? What are the pros and cons?

The stored procedure contains a series of pre-compiled SQL statements that can be executed.
The stored procedure is stored in MySQL and can be executed by calling its name inside a bunch of SQL
A more straightforward understanding:
A stored procedure can be said to be a recordset, which is a block of code consisting of some T-SQL statements.
These T-SQL statement codes implement functions like a method (for adding and removing changes to single-or multiple-table additions),
Then give the code block a name and call him when it comes to using this function.

Advantages of using Stored procedures:

1. A stored procedure replaces a large number of T_SQL statements, implementing a program with SQL decoupling
2. A stored procedure is a precompiled block of code that performs efficiently.
3. Based on the network transmission, the data volume of the alias is small, and the amount of direct SQL data is large.
4. To a certain extent, to ensure data security, the execution of stored procedures requires a user with certain permissions.

Disadvantages

1. Programmer extension is not a convenient feature
2. Poor portability

Three ways to use a program with a database

#方式一：    MySQL：存储过程    程序：调用存储过程#方式二：    MySQL：    程序：纯SQL语句#方式三：    MySQL:    程序：类和对象，即ORM（本质还是纯SQL语句）

Using Stored Procedures

# 创建存储过程delimiter //create procedure p3(    in n1 int,    out res int)BEGIN    select * from blog where id > n1;    set res = 1;END //delimiter ;#在mysql中调用set @res=0; #0代表假（执行失败），1代表真（执行成功）call p3(3,@res);select @res;#在python中基于pymysql调用cursor.callproc('p3',(3,0)) #0相当于set @res=0print(cursor.fetchall())    #查询select的查询结果cursor.execute('select @_p3_0,@_p3_1;') #@p3_0代表第一个参数，@p3_1代表第二个参数，即返回值print(cursor.fetchall())

4. What is a trigger?

A trigger is a special stored procedure that is executed primarily by triggering an event.
It can enforce constraints to maintain the integrity and consistency of data, and can track operations within the database without permitting unauthorized updates and changes.
Can be associated with a level operation. For example, a trigger on a table contains data operations on another table, and that action causes the table trigger to be triggered.

Use triggers to customize the behavior of the user in the "Add, delete, change" operation of the table, note: No query
Triggers cannot be called directly by the user, but are known to be passively triggered by the "Add/delete/change" operation on the table.

#创建触发器delimiter //CREATE TRIGGER tri_after_insert_cmd AFTER INSERT ON cmd FOR EACH ROWBEGIN    IF NEW.success = 'no' THEN #等值判断只有一个等号            INSERT INTO errlog(err_cmd, err_time) VALUES(NEW.cmd, NEW.sub_time) ; #必须加分号      END IF ; #必须加分号END//delimiter ;

What does a trigger do?
A trigger is a special stored procedure that is executed primarily by triggering an event.
It can enforce constraints to maintain the integrity and consistency of data, and can track operations within the database without permitting unauthorized updates and changes. Can be associated with a level operation.
For example, a trigger on a table contains data operations on another table, and that action causes the table trigger to be triggered.

5. Transaction: Stored Procedure implementation

A transaction (Transaction) is the basic unit of concurrency control.
The so-called transaction, which takes the multiple SQL of some operations as an atomic operation, which either executes or is not executed, is an inseparable unit of work. Once an error occurs, the database data integrity is guaranteed by rolling back to its original state.
For example, bank transfer is a typical scenario for a business.

Four characteristics

1. Atomicity means that all operations contained in a transaction are either all successful, or all fail back,
2. Consistency means that a transaction must be in a consistent state before and after execution. Data totals are consistent before and after transactions
   
3. Isolation is when multiple users concurrently access the database, such as when the same table operation, the database for each user-opened transactions, can not be disturbed by the operation of other transactions, multiple concurrent transactions to be isolated from each other.
4. Persistence refers to the fact that once a transaction is committed, changes to the data in the database are permanent, even if the database system encounters a failure, and the commit transaction is not lost.

Three common commands for library transactions
Begin Transaction, Commit Transaction, RollBack Transaction.

6. Lock:

In the DBMS, locks are the key to implementing transactions, and locks guarantee the integrity and concurrency of transactions. Like a real-life lock, it enables certain data owners to be unable to use certain data or structures for a certain period of time. Of course, locks are also divided into levels.

Optimistic lock, implemented by itself, by version number
Pessimistic lock: Shared lock, multiple transactions, can only read not write, plus lock in share mode
Exclusive lock, one transaction, only write, for update
Row lock

What are optimistic and pessimistic locks on the database?

The task of concurrency control in a database management system (DBMS) is to ensure that multiple transactions concurrently access the same data in the database without disrupting the isolation and uniformity of the transaction and the consistency of the database.

Optimistic concurrency control (optimistic lock) and pessimistic concurrency control (pessimistic lock) are the main techniques used in concurrency control.

• Pessimistic lock: Assumes concurrency conflicts and masks all operations that may violate data integrity
• Optimistic Lock: Assume that there will be no concurrency conflicts and only check for violation of data integrity when committing the operation.

7. Index

What is an index??
Database index is a sort of data structure in the database management system, which helps to quickly query and update data in database tables.
The index is equivalent to the dictionary's Sequencer list, if you want to check a word, if you do not use a sequencer, you need to check from page hundreds of.
In addition to data, the database system maintains a data structure that satisfies a particular lookup algorithm that references (points to) data in some way, so that an advanced find algorithm can be implemented on those data structures. This data structure is the index.

Indexes have B + index and hash index, the difference of each
Hash index, the equivalent query efficiency is high,
Cannot be sorted
Cannot make range query

B + Index
Data order
Scope Query

The implementation of an index typically uses a B-tree and its variants, plus trees.

B + trees are found by binary search trees, and then by the balance of binary tree, the second tree evolved.

There is a price to be paid for indexing a table :
One is to increase the storage space of the database,
Second, it takes more time to insert and modify the data (because the index changes as well).

Creating an index can greatly improve your system's performance (benefits):

• By creating a uniqueness index, you can guarantee the uniqueness of each row of data in a database table.
• Can greatly speed up the retrieval of data, which is the main reason for creating indexes.
• The connection between tables and tables can be accelerated, particularly in terms of achieving referential integrity of data.
• When you use grouping and sort clauses for data retrieval, you can also significantly reduce the time to group and sort in queries.
• By using an index, you can improve the performance of your system by using an optimized hidden device during the query.

There are many disadvantages to increasing the index:

• It takes time to create indexes and maintain indexes, and this time increases as the amount of data increases.
• The index needs to occupy physical space, in addition to the data table for the data space, each index also occupies a certain amount of physical space, if you want to establish a clustered index, then the space will be larger.
• When the data in the table is added, deleted and modified, the index is also maintained dynamically, which reduces the maintenance speed of the data.

When to create an index
-(1) A SELECT operation is frequently performed on a table

• (2) The table is very large (record super many), the record content distribution is very wide
• (3) column names often appear in the WHERE clause or join condition

When do I "don't" create an index

• (1) The table often insert/update/delete operation
• (2) The table is very small (record very little)
• (3) Column names are not often used as join conditions or appear in the WHERE clause

In general, you should create indexes on these columns:
(1) In the column that often needs to search, can speed up the search;
(2) on the column that is the primary key, enforce the uniqueness of the column and the arrangement of the data in the organization table;
(3) often used in connected columns, these columns are mainly foreign keys, you can speed up the connection;
(4) Create an index on a column that is often required to search by scope, because the index is sorted and its specified range is continuous;
(5) Create indexes on columns that often need to be sorted, because the index is sorted so that the query can use the sorting of the index to speed up the sorting query time;
(6) Speed up the judgment of the condition by creating an index on the column that is often used in the WHERE clause.

Some columns should not create an index:

1. For those columns that are seldom used or referenced in the query, the index should not be created.
2. For columns that have only a few data values, you should not increase the index.
3. For columns that are defined as text, the image and bit data types should not increase the index
4. You should not create indexes when modifying performance is far greater than retrieving performance.

Will using index queries improve the performance of queries? Why
In general, querying data through an index is faster than a full table scan. But we must also pay attention to its cost.
Indexes require space to store, and they need to be maintained regularly, and the index itself is modified whenever a record is added to a table or the index column is modified.
This means that the insert,delete,update of each record will pay 4, 5 disk I/Os for this.
Because indexes require additional storage space and processing, those unnecessary indexes can slow query response time.
Using index queries does not necessarily improve query performance.

Index range Queries (index range SCAN) are available in two situations:

• Based on a range of searches, the general query returns a result set that is less than 30% of the number of records in the table
• Retrieval based on non-uniqueness Index

#方式一create table t1(    id int,    name char,    age int,    sex enum('male','female'),    unique key uni_id(id),    index ix_name(name) #index没有key);#方式二create index ix_age on t1(age);#方式三alter table t1 add index ix_sex(sex);

SQL statement 1, say three paradigms.

First Normal (1NF): The fields in a database table are single attributes and cannot be divided.
Student Information Form, including name, age, gender, school number and other information
Second Normal form (2NF): satisfies the first paradigm, and the fields in the table must depend entirely on the primary key and not the partial primary key.
To have a primary key, require that all other fields depend on the primary key.
Third paradigm (3NF): satisfies the second paradigm, all fields outside the primary key must be independent of each other
Is that data is stored only in one place, not repeated in multiple tables, can be considered to eliminate the transfer of dependency
Elimination of transitive dependencies, easy to understand, can be seen as "eliminate redundancy."
The so-called transfer function dependency, refers to if there is a "a→b→c" decision relationship, the C transfer function depends on A.

2. Drop, deleteAnd truncate

Simply say the difference.
The drop, Delete, truncate in sql all mean delete, but there are some differences

1. Delete and truncate only delete the table's data without deleting the table's structure
2. Speed, in general: drop> truncate >delete
3. The delete statement is DML, which is placed in the rollback segement , which takes effect after the transaction is committed, and is triggered when the corresponding triggeris executed.
4. Truncate,drop is DDL, the operation takes effect immediately, the original data is not placed in the Rollback segment , and cannot be rolled back. Operation does not trigger trigger.
5. Security: Use Drop and truncate carefully, especially when there is no backup, not rollback

What are the different scenarios used?

• When you no longer need a table, use the drop
• To delete a portion of a data row, use delete, and take the WHERE clause
• Keep the table and delete all the data when using truncate

3, list several kinds of table connection way, what is the difference?

• INNER JOIN (Inner join): Matches records associated with 2 tables.
• Left OUTER join (Outer join): In addition to matching records in 2 tables, the remaining records in the left table are matched, and the fields that do not match in the right table are represented by NULL.
• Right outer join (Outer join): In addition to matching records associated with 2 tables, matches the remaining records in the right table, and the fields that do not match in the left table are represented by NULL.
• Full outer joins: the unmatched data in the connected tables is all displayed.
• Cross join: The Cartesian effect, the result of which is the product of the number of linked tables.

4. What are the database constraints and what are the common constraints?

Database constraints are used to ensure the integrity (correctness and consistency) of database table data. You can guarantee the integrity of your data by defining constraints \ indexes \ triggers.

PRIMARY KEY constraint: primary key;
FOREIGN KEY constraint: foreign key;
Unique constraint: unique;
checking constraints: check;
Null value constraint: NOT null;
Default value constraint: defaults;

5. How to describe many-to-many relationships?

To describe many-to-many relationships in a relational database, you need to create a third data table.
For example, students will need to set up a course information table based on the student information sheet and course Information table, which holds the student ID and course ID.

6. Enumerate several commonly used aggregate functions?

Sum: Sum? AVG: Averaging? Max: Ask for maximum value? Min: To find the minimum value? Count: Find the number of records

7, Super Key, candidate key, primary key, foreign key is what?

Hyper-Key: a property set that uniquely identifies a tuple in a relationship is called a hyper-key of a relational pattern.
A property can be used as a super-key, and multiple properties can be combined together as a super-key.
The super-key contains the candidate and primary keys.
Candidate Key: Is the least-key, that is, a super-key without redundant elements.
Primary key: A combination of data columns or properties in a database table that uniquely and fully identifies the stored data object.
A data column can have only one primary key, and the value of the primary key cannot be missing, that is, it cannot be a null value (NULL).
FOREIGN key: The foreign key of this table is called by the primary key of another table that exists in one table.

8. SELECT statement Keyword order definition order

SELECT DISTINCT <select_list>FROM <left_table><join_type> JOIN <right_table>ON <join_condition>WHERE <where_condition>GROUP BY <group_by_list>HAVING 

Execution order

(7)     SELECT (8)     DISTINCT <select_list>(1)     FROM <left_table>(3)     <join_type> JOIN <right_table>(2)     ON <join_condition>(4)     WHERE <where_condition>(5)     GROUP BY <group_by_list>(6)     HAVING 

Query customers from Hangzhou with fewer than 2 orders.

select a.customer_id, count(b.order_id) as total_ordersfrom table1 as a left join table2 as bon a.customer_id = b.customer_idwhere a.city = 'hangzhou'group by a.customer_idhaving count(b.order_id) < 2order by total_orders desclimit 1;

The idea of database Optimization 1, in the database query statement speed is very slow, how to optimize?

1. Build an index
2. Reduce the association between tables
3. Optimize SQL, try to get SQL to locate data quickly, do not let SQL do the full table query, should go index, the data volume of the table in front
4. Simplify the query field, do not use the field, try to return a small amount of data
5. Try to use PreparedStatement to inquire, do not use statement

2, the idea of database optimization

1.SQL Statement Optimization
1) Try to avoid using the! = or <> operator in the WHERE clause, or discard the engine for a full table scan using the index.
2) Try to avoid null values for the field in the WHERE clause, otherwise it will cause the engine to discard full table scans using the index, such as: Select ID from t where num is null
You can set the default value of 0 on NUM, make sure that the NUM column in the table does not have a null value, and then query:
Select ID from t where num=0
3) It's a good choice to use exists instead of in.
4) Replace the HAVING clause with a WHERE clause because the having will only filter the result set after retrieving all records

2. Index optimization
Look at the index above

3. Database structure optimization
? 1) Paradigm optimization: such as eliminating redundancy (space saving). ）

? 2) Inverse paradigm optimization: such as proper addition of redundancy (reduce join)

? 3) Split table: Partitions separate data physically, and data from different partitions can be stored in data files on different disks. In this way, when querying this table, only need to scan the table partition, instead of full table scan, significantly shorten the query time, the other partition on different disks will be the data transfer to the table of different disk I/O, a well-provisioned partition can transfer data to disk i/ o The competition is evenly dispersed. You can take this approach when you have a large amount of data. Table partitions can be automatically built by month.
4) split is actually split vertically and horizontally split:

4. Server hardware optimization

3, interview answer database optimization problems from the following aspects of the beginning

(1), according to the service level: Configure MySQL performance optimization parameters;
(2), from the system level to enhance the performance of MySQL: The optimization of data table structure, field type, field index, sub-table, library, read and write separation, and so on.
(3), improve performance from the database level: Optimize the SQL statement, the rational use of the field index.
(4), enhance performance from the code level: using Caching and NoSQL database storage, such as Mongodb/memcached/redis, to mitigate the pressure of high concurrency database queries.
(5), reduce the number of database operations, try to use the database access-driven batch processing method.
(6), infrequently used data migration backup, avoid every time in the huge amount of data to retrieve.
(7), upgrade the database server hardware configuration, or build a database cluster.
(8), programming means to prevent SQL injection: using JDBC PreparedStatement bitwise INSERT or query; Regular expression filtering (illegal string filtering);

# # # 4, SQL common commands:

CREATE TABLE Student( ID NUMBER PRIMARY KEY, NAME VARCHAR2(50) NOT NULL);    //建表 CREATE VIEW view_name AS Select * FROM Table_name;  //建视图 Create UNIQUE INDEX index_name ON TableName(col_name);  //建索引 INSERT INTO tablename {column1,column2,…} values(exp1,exp2,…);  //插入 INSERT INTO Viewname {column1,column2,…} values(exp1,exp2,…);   //插入视图实际影响表 UPDATE tablename SET name='zang 3' condition;   //更新数据 DELETE FROM Tablename WHERE condition;  //删除 GRANT (Select,delete,…) ON (对象) TO USER_NAME [WITH GRANT OPTION];   //授权 REVOKE (权限表) ON(对象) FROM USER_NAME [WITH REVOKE OPTION]     //撤权

5, relationship-type non-relational

? For example, there is a student's data:
? Name: Zhang San, gender: Male, School Number: 12345, class: Second grade Class
? There is also a class of data:
? Class: Second Grade One class, class Teacher: John Doe

database type	properties	benefits
relational database SQLite, Oracle, MySQL	1, relational database, refers to the use of a relational model to organize the number database; 2. The most important characteristic of relational database is the consistency of transaction; 3. In simple terms, the relational model refers to the two-dimensional tabular model, and a relational database is a data organization composed of two-dimensional tables and their linkages.	1, easy to understand: two-dimensional table structure is very close to the logic of the world a concept, relational model relative to the mesh, level and other models easier to understand; 2, easy to use: The General SQL language makes it very convenient to operate relational database, 3, easy to maintain: rich integrity (Entity integrity, Referential integrity and user-defined integrity greatly reduce the probability of data redundancy and data inconsistency; 4. SQL support for complex queries.	1, in order to maintain consistency, the great cost is its poor read and write performance, 2, fixed table structure, 3, high concurrent reading and writing requirements, 4, the efficient reading and writing of massive data;
non-relational database MongoDb, Redis, HBase	1, using key-value pairs to store data, 2, distributed, 3, generally does not support acid characteristics; 4, non-relational database is strictly not a database, it should be a number A collection of structured storage methods.	1, without the SQL layer parsing, read and write performance is very high, 2, based on key-value pairs, data is not coupled, easy to expand; 3. Format of stored data: NoSQL storage format is key,value form, document form, picture form, etc., document form, picture form, etc. The relational database supports only the underlying type.	1, no SQL support, high learning and use costs, 2, no transaction processing, additional features such as BI and report support is not good;

6, MySQL two storage engine differences (transaction, lock level, etc.), the respective application scenario

MYISAM does not support transactions, does not support foreign keys, table locks, when inserting data, lock the entire table, look up the total number of rows in the table, do not need a full table scan
INNODB support transactions, support foreign keys, row locks, check table total number of rows, full table scan

2-Interview-database