1. SQL select statement complete execution order?

1. The FROM clause assembles data from different sources;

2. The WHERE clause filters rows based on the specified criteria;

3. The group by clause divides data into multiple groups;

4. Use aggregation function for calculation;

5. Use the having clause to filter groups.

6. Calculate all expressions;

Select * from ‘select’;

Use order by to sort the result set.

2. What is SQL injection? How do I prevent SQL injection?

SQL injection gets a database on a vulnerable website by entering (malicious) SQL statements into a Web form, rather than executing the SQL statements as the designer intended. For example, if the SQL statement is select * from user where username = “admin” or “a” = “a”, the SQL statement is still valid and the parameter admin is meaningless.

Ways to prevent SQL injection:

Select * from user where username =? , SQL statement semantics will not change, SQL statement variables using? Even if you pass the argument “admin or ‘a’ = ‘a'”, the whole thing will be queried as a string.

# in mapper mode in Mybatis framework also prevents SQL injection to a large extent ($does not prevent SQL injection).

3. What are the four characteristics of transactions?

Four basic elements for proper execution of database transaction transanction. ACID, Atomicity, Correspondence, Isolation, Durability.

Atomicity: All operations in the entire transaction are either complete or not complete, and cannot be stopped at some intermediate stage. If a transaction fails during execution, it will be rolled back to the state before the transaction began, as if the transaction had never been executed.

Consistency: Database integrity constraints are not broken before and after a transaction.

Isolation: The isolation state performs transactions as if they were the only operation performed by the system at a given time. If you have two transactions, running at the same time and performing the same function, the isolation of the transaction ensures that each transaction is considered by the system to be the only one using the system. This property is sometimes called serialization. To prevent confusion between transaction operations, requests must be serialized or serialized so that only one request is used for the same data at a time.

Persistence: After a transaction completes, changes made to the database by that transaction persist in the database and are not rolled back.

4. What should I pay attention to when using MySQL index?

1. Avoid using WHERE clauses! = or <> otherwise the engine will abandon the index for a full table scan. The optimizer will not be able to determine the number of rows that will die by index, so it needs to search all rows in the table.

2. Do not use OR in the WHERE clause to join conditions. Otherwise, the engine will abandon the use of indexes and perform full table scan, for example, SELECT ID FROM t WHERE num = 10 OR num = 20.

3. Avoid expression operations on fields in the WHERE clause, which will cause the engine to abandon indexes for full table scans.

4. Avoid functional manipulation of fields in the WHERE clause, which will cause the engine to abandon indexes for full table scans.

Select * from ‘%’ where ‘%’ = ‘%’; If fuzzy matching is required, full-text indexes can be used.

5. What is the process of MySQL primary/secondary replication?

1. The Master’s binlog dump thread is responsible for transmitting the Master’s binlog events to the slave.

2. The I/O thread on the Slave receives the binlog sent by the Master and writes it to the relay log.

3. The SQL thread on Slave that reads the relay log and executes it.

SQL threads can only be coordinators if the SQL thread is a coordinator, whether it is a fake thread at the 5.6 library level or a true multi-thread replication at the 5.7 MariaDB level. It is only responsible for reading out the binlog in the relay log and handing it to the worker thread. The Woker thread is responsible for executing specific binlog events.

6. What are the differences between MyISAM and InnoDB in Mysql?

1. InnoDB supports transaction, MyISAM does not support for InnoDB each SQL language encapsulated as a transaction, automatic commit, this will affect the speed, so it is best to put multiple SQL languages between begin and commit, constitute a transaction;

InnoDB supports foreign keys, MyISAM does not. Converting an InnoDB table with foreign keys to MYISAM will fail;

3. InnoDB is a clustered index. Data files are tied together with indexes and must have primary keys. But secondary indexes require two queries, first to the primary key and then to the data through the primary key. Therefore, the primary key should not be too large, because if the primary key is too large, the other indexes will be too large. While MyISAM is a non-clustered index, data files are separated and indexes hold Pointers to data files. Primary and secondary indexes are separate.

Select count(*) from table. InnoDB does not store the number of rows in a table. MyISAM uses a variable to store the number of rows in the entire table. When executing the above statement, you only need to read the variable, which is fast.

Innodb does not support full-text indexing, while MyISAM supports full-text indexing, which is more efficient than MyISAM.

Mysql transaction isolation level

At this isolation level, all transactions can see the results of other Uncommitted transactions. This isolation level is rarely used in real-world applications because its performance is not much better than other levels. Reading uncommitted data is also known as Dirty reads.

Read Committed This is the default isolation level for most database systems (but not MySQL). It satisfies a simple definition of isolation: a transaction can only see the changes made by committed transactions. This isolation level also supports what is called Nonrepeatable Read, because other instances of the same transaction may have new commits during the processing of that instance, so the same SELECT may return different results.

Repeatable Read This is MySQL’s default transaction isolation level and ensures that multiple instances of the same transaction will see the same rows of data when reading data concurrently. In theory, though, this leads to another thorny problem: Phantom Read. Simply put, phantom reading is when a user reads a row in a range, another transaction inserts a new row in that range, and when the user reads a row in that range, a new phantom row is found.

Serializable This is the highest isolation level and addresses the phantom read problem by forcing transactions to be ordered so that they cannot conflict with each other. In short, it places a shared lock on each row of data that is read. At this level, a lot of timeouts and lock contention can result.

Summarize interview answering skills

1. If you don’t understand anything at all, just say you don’t, which makes the impression worse.

2, half do not understand, can not remember, let the interviewer hint.

Don’t understand things, don’t mention a word. Otherwise people will be fooled by asking. Like what can Redis do? If you haven’t used distributed locks, don’t mention caching.

4. Don’t do all the talking. Sometimes you can push yourself. For example, what are the ways to create a new thread? When you’re done, you can add something. Say thread pools are used a lot in development, and the interviewer will probably ask you about some thread pooling techniques.

5. In fact, the interviewer for the Java position may be a c++/ PHP person or a non-technical manager. Encounter do not understand the technology, may be appropriate to say some professional terms blowing brag. However, a non-technical person interviewing for a Java position may not be a very reliable company.

The knowledge that is seldom used in ordinary work can be memorized if you really can’t understand it.

Well, that’s all for today’s sharing.

Cicatang, located in Chengdu High-tech Zone Tianfu Software Park, is an Internet + education company supported by the Park, aiming to provide talent training and delivery services for IT enterprises. At present, there are Java+ big data, front-end full stack, UI design, network security and other courses.