[Required for BAT Interview] To prepare for the big factory, this is how I prepared MySQL! (Answers attached)

Written in front:
The series of articles are based on the principle of changing from simple to complex, and continue to be updated. Partners with good interview questions can also privately write to bloggers. Let's work together! Of course, please correct me if there is anything inappropriate in the article!

(The answer is later, it is recommended to look at the question first, then the answer!!!)

topic

  1. A table with an ID auto-incrementing primary key. After inserting 17 records, delete the 15, 16, 17 records, restart Mysql, and insert a record. Is the ID of this record 18 or 15?
  2. What is the Heap table?
  3. Distinguish between CHAR_LENGTH and LENGTH?
  4. Please briefly describe the names of the four transaction isolation levels supported by InnoDB in Mysql and the differences between each level?
  5. What is the usage of ENUM in MySQL?
  6. How to define REGEXP?
  7. What is the string type of the column?
  8. What storage engine is used in MySQL?
  9. What is the MySQL driver?
  10. What does TIMESTAMP do on the UPDATE CURRENT_TIMESTAMP data type?
  11. What is the difference between primary key and candidate key?
  12. How to log in to MySQL using unix shell
  13. What is myisamchk used for?
  14. What are the method commands for MySQL database server performance analysis?
  15. How to control the size of the heap table?
  16. What is the difference between LIKE and REGEXP operations?
  17. What is the difference between MyISAM static and MyISAM Dynamic?
  18. What is a federated table?
  19. If a table has a column defined as TIMESTAMP, what will happen?
  20. When the column is set to AUTO INCREMENT, what happens if it reaches the maximum value in the table?
  21. How can I find which auto increment was allocated during the last insertion?
  22. What is the difference between BLOG and TEXT?
  23. What is the difference between mysql_fetch_array and mysql_fetch_object?
  24. How do we run batch mode in mysql?
  25. Where will the MyISAM table be stored and also provide its storage format?
  26. What tables are there in MySQL?
  27. MySQL data optimization
  28. Keywords for MySQL
  29. Storage engine
  30. What is ISAM?
  31. How does Mysql optimize DISTINCT?
  32. How to input characters as hexadecimal numbers?
  33. How many columns can be used to create an index?
  34. What is the difference between NOW() and CURRENT_DATE()?
  35. What kind of objects can be created using the CREATE statement?
  36. How many TRIGGERS are allowed in Mysql table?
  37. What is a non-standard string type?
  38. What are common SQL functions?
  39. Explain the access control list
  40. Does MYSQL support transactions?
  41. What field type is good for recording currency in mysql
  42. What are the mysql-related authority tables?
  43. What kinds of locks are there in Mysql?
  44. database backup

answer

A table with an ID auto-incrementing primary key. After inserting 17 records, delete the 15, 16, 17 records, restart Mysql, and insert a record. Is the ID of this record 18 or 15?

(1) If the type of the table is  MyISAM , then it is  18

Because the  MyISAM  table will  record the maximum ID of the  auto-incrementing primary key in the data file,  the maximum ID of the auto-incrementing primary key will not be lost after restarting MySQL  ;

( 2 ) If the type of the table is  InnoDB , then it is  15

InnoDB  tables only  record the maximum ID of  the self-incrementing primary key in memory, so restarting the database or OPTIMIZE the table  will cause the maximum  ID to be  lost

What is the Heap table?

Heap table is a table that uses the MEMORY storage engine. The data of this table is stored in memory. Due to hardware problems or power failure, the data is easy to lose, so data can only be read from other data tables as temporary tables or read-only caches To use.

scenes to be used:

Operations involving transient, non-critical data, such as session management or caching. When the MySQL server is stopped or restarted, the data in the MEMORY table will be lost. Data that can be accessed quickly and with low latency is stored in the memory. The amount of data can be completely contained in memory without causing the operating system to swap out virtual memory pages.

Read-only or read-only data access mode.

BLOB or TEXT fields are not allowed.

Only comparison operators =, <, >, =>, = <can be used.

The HEAP table does not support AUTO_INCREMENT.

The index cannot be NULL.

Distinguish between CHAR_LENGTH and LENGTH

CHAR_LENGTH  is the number of characters, and  LENGTH  is the number of bytes. The two data of Latin  characters are the same, but the

They are different from Unicode  and other encodings.

Please briefly describe the names of the four transaction isolation levels supported by InnoDB in Mysql and the differences between each level?

The four isolation levels defined by the SQL standard are:

read uncommited  : read uncommitted data

read committed : dirty read, non-repeatable read

repeatable read : repeatable read

serializable  : serial things

What is the usage of ENUM in MySQL?

ENUM  is a string object, used to specify a set of predefined values, and can be used when creating a table.

Create table size(name ENUM('Smail,'Medium','Large');

How to define REGEXP?

REGEXP  is pattern matching, where the matching pattern is anywhere in the search value.

What is the string type of the column?

The string types are:

SET

BLOB

ENUM

CHAR

TEXT

VARCHAR

What storage engine is used in MySQL?

The storage engine is called a table type, and data is stored in files using various technologies.

Technology involves:

Storage mechanism

Locking levels

Indexing

Capabilities and functions.

What is the MySQL driver?

The following are  the drivers available in Mysql  :

PHP  driver

JDBC  driver

ODBC  driver

CWRAPPER

PYTHON  driver

PERL  driver

RUBY  driver

CAP11PHP  driver

Ado.net5.mxj

What does TIMESTAMP do on the UPDATE CURRENT_TIMESTAMP data type?

The  TIMESTAMP  column is  updated with Zero  when the table is created . As long as other fields in the table change, UPDATE

The CURRENT_TIMESTAMP  modifier updates the timestamp field to the current time.

What is the difference between primary key and candidate key?

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

The primary key is also a candidate key. By convention, candidate keys can be designated as primary keys and can be used for any foreign key references.

How to log in to MySQL using unix shell

We can log in with the following command:

[mysql dir]/bin/mysql -h hostname -u

What is myisamchk used for?

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

What are the method commands for MySQL database server performance analysis?

Show status, some variable values ​​worth monitoring:

The traffic between Bytes_received and Bytes_sent and the server.

Com_* The command being executed by the server.

Created_* Temporary tables and files created during the query execution period.

Handler_* storage engine operation.

Select_* Different types of connection execution plans.

Sort_*Several sorting information.

Show profiles are used by MySql to analyze the resource consumption of the current session SQL statement execution

How to control the size of the heap table?

The size of the  Heal table can be controlled by the Mysql configuration variable called max_heap_table_size  .

What is the difference between LIKE and REGEXP operations?

The LIKE and REGEXP operators are used to represent ^ and %.

SELECT  *  FROM  <tablename>  WHERE  * REGEXP  "^b" ;

SELECT  *  FROM  <tablename>  WHERE  *  LIKE  "%b" ;

What is the difference between MyISAM static and MyISAM Dynamic?

All fields on MyISAM  Static have a fixed width.

The dynamic MyISAM table will have fields like TEXT, BLOB, etc. to accommodate data types of different lengths.

MyISAM Static  is easier to recover in case of damage.

What is a federated table?

Federated  tables allow access to tables located on other server databases.

If a table has a column defined as TIMESTAMP, what will happen?

Whenever the row is changed, the timestamp field will get the current timestamp.

When the column is set to AUTO INCREMENT, what happens if it reaches the maximum value in the table?

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

How can I find which auto increment was allocated during the last insertion?

LAST_INSERT_ID  will return  the last value assigned by Auto_increment  , and there is no need to specify the table name.

What is the difference between BLOG and TEXT?

BLOB  is a binary object that can hold a variable amount of data. There are four types of  BLOB −

TINYBLOB

BLOB

MEDIUMBLOB

LONGBLOB

They can only differ in the maximum length of value they can hold.

TEXT  is a case-insensitive  BLOB . Four  TEXT  types

TINYTEXT

TEXT

MEDIUMTEXT

LONGTEXT

They correspond to the four  BLOB  types and have the same maximum length and storage requirements. The only difference between BLOB  and  TEXT  types is that  BLOB  values ​​are case-sensitive when sorting and comparing, and  TEXT values ​​are not case-sensitive.

What is the difference between mysql_fetch_array and mysql_fetch_object?

mysql_fetch_array ()  -  Return the result row as an associative array or a regular array from the database.

mysql_fetch_object-  returns the result row from the database as an object.

How do we run batch mode in mysql?

The following commands are used to run in batch mode:

mysql;

mysql mysql.out

Where will the MyISAM table be stored and also provide its storage format?

Each  MyISAM  table is stored on disk in three formats:

·" .Frm " file storage table definition

·Data files have " .MYD " ( MYData ) extension

Index files have " .MYI " ( MYIndex ) extension

What tables are there in MySQL?

There are  5  types of forms:

MyISAM

Heap

Merge

INNODB

ISAM

MySQL data optimization

Optimize data type

Avoid using NULL, NULL needs special treatment, most of the time you should use NOT NULL, or use a special value, such as 0, -1 as the default value.

Possible to use only a smaller field, MySQL after reading data from the disk is stored in memory , and then use cpu cycles and disk I / O read it, which means that the smaller the class data smaller type of space occupied.

Be careful with character set conversion

The character set used by the client or application may be different from the character set of the table itself. This requires MySQL to implicitly convert it during operation. In addition, it is necessary to determine whether the character set such as UTF- 8 supports multi-byte characters. They require more storage space.

Optimize count(my col) and count( ) *

Optimize subqueries

The face of the sub-query, MySQL query optimization engine is not always the most effective, it is to what is often the cause of sub-query into a join query optimizer has been able to correctly handle join queries, and of course, the point to note is , To ensure that the connection column of the connection table (the second table) is indexed. On the first table, MySQL usually performs a full table scan relative to the query subset of the second table. This is a nested loop algorithm Part.

Optimize UNION

  • Using UNION across multiple different databases is an interesting optimization method. UNION returns data from two unrelated tables, which means that there will be no duplicate rows, and the data must also be sorted. We Sort know it is very resource-intensive, especially not a sort of large tables.
  • UNION ALL can greatly accelerate the speed, if you already know that your data will not include re- re-line, or you do not care whether there will be duplicate rows, in both cases UNION ALL is more suitable. In addition, some methods may be employed to avoid the application logic for Free duplicate rows, and so UNION ALL UNION returned result is the same , but not sorted UNION ALL.
Keywords for MySQL

Add index:

alter table  tableName  add  index (index field)

Primary key: primary key

Unique: unique

Global: fulltext

Normal: index

Multiple columns: index index_name

Page level: Engine BDB. Lock an adjacent set of records at a time.

Table level: The engine MyISAM, understood as locking the entire table, can read and write at the same time. Row level: Engine

INNODB, a single row of records is locked, and the specified record is locked, so that other processes can still operate on other records in the same table. Table-level locking is fast, but there are many conflicts, and row-level conflicts are few, but the speed is slow

Storage engine

Storage engine that white is how to store data, how to index data stored and how to update, query the number of implementation technologies such data.

MyISAM: This engine is the earliest provided by mysql. This kind of engine can be divided into static MyISAM, dynamic MyISAM and compressed MyISAM three kinds:

Static MyISAM: If the length of each data column in the data table is fixed in advance, the server will automatically select this table type. Because the space occupied by each record in the data table is the same, the efficiency of this table access and update is very high. When data is corrupted, restore the complex work is relatively easy to do.

Dynamic MyISAM: If a varchar, text or BLOB field appears in the data table , the server will automatically select this table type. With respect to the static MyISAM, such a table memory storage space is relatively small, but due to the varying lengths of each record, so many changes data, the number of the data in the table data may be stored in a discrete memory, leading to efficiency decline. The same time, the memory may be a lot of debris. Therefore, this type of table should often be defragmented with the optimize table command or optimization tool.

Compress MyISAM: The two types of tables mentioned above can be compressed with the myisamchk tool . This type of table further reduce memory consumption, but not after such compression table longer be modified. In addition, because the data is compressed, this kind of table must be decompressed in advance when it is read .

However, no matter what kind of MyISAM table, it currently does not support transactions, row-level locks and foreign key constraints.

MyISAM Merge engine: This type is a variant of the MyISAM type. The merge table is to merge several identical MyISAM tables into a virtual table. Often used in logs and data warehouses.

InnoDB: InnoDB table types can be seen as further product updates for MyISAM, which mention for the transactions, row-level locking mechanism and a foreign key constraint functions.

memory(heap): This type of data table only exists in memory. It uses hash index, so the data access speed is very fast. Because it exists in memory, this type is often used in temporary tables .

archive: This type only supports select and insert statements, and does not support indexes.

Desc[ribe] tablename: View the structure of the data table.

show engines: command to display the storage engines supported by the current database

What is ISAM?

ISAM is  referred to as index sequential access method for short. It was developed by  IBM  to store and retrieve data on secondary storage systems such as magnetic tape .

How does Mysql optimize DISTINCT?

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

How to input characters as hexadecimal numbers?

If you want to enter the characters as hexadecimal numbers, you can enter the hexadecimal numbers with single quotes and the prefix ( X ), or

Use only ( Ox ) prefix to enter hexadecimal numbers.

If the expression context is a string, the hexadecimal number string will be automatically converted to a string.

How many columns can be used to create an index?

Any standard table can create up to  16  index columns.

What is 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 () only displays the current year, month and date.

What kind of objects can be created using the CREATE statement?

The following objects are  created using the CREATE  statement:

DATABASE

EVENT

FUNCTION

INDEX PROCEDURE

TABLE

TRIGGER

USER

VIEW

How many TRIGGERS are allowed in Mysql table?

Six triggers are allowed in the Mysql  table, as follows:

BEFORE INSERT

AFTER INSERT

BEFORE UPDATE

AFTER UPDATE

BEFORE DELETE

AFTER DELETE

What is a non-standard string type?

The following are non-standard string types:

TINYTEXT

TEXT

MEDIUMTEXT

LONGTEXT

What are common SQL functions?

Mathematical function

Abs (num) to find the absolute value

floor (num) rounded down

ceil (num) rounded up

String function

insert (s1,index,length,s2) replacement function

S1 represents the string to be replaced

s2 represents the string to be replaced

Index indicates the position to be replaced, starting from 1

Lebgth represents the length to be replaced

upper (str), ucase (str) changes the letter to uppercase

lower (str), lcase (str) change the letter to lowercase

left(str, length) returns the first length characters of the str string

right (str, length) returns the last length characters of the str string

substring(str, index, length) returns the str string with length characters starting from the index bit (index starting from 1)

reverse (str) output the str string in reverse order

Date function

curdate(), current_date() get the current date

curtime(), current_time() get the current date

now () to get the current date and time

datediff (d1, d2) The difference in days between d1 and d2

adddate (date, num) returns the date starting from date and num days afterwards

subdate (date, num) returns the date starting from date, num days before the date

Aggregate function

Count (field) counts the total number of records according to a field (how many pieces of data are saved in the current database)

sum (field) calculates the sum of values ​​in a field

avg (field) calculates the average value of a field

Max (field), min (field) to find the maximum or minimum value of a field

Explain the access control list

ACL (Access Control List) is a list of permissions associated with an object. This list is  Mysql  based server security model basis, it helps to eliminate the problem that users can not connect.

Mysql  the  ACL (also known as the authorization table) cached in memory. When a user attempts authentication or run command, Mysql  will press Check ACL permissions and authentication information of a predetermined sequence of pictures.

Does MYSQL support transactions?

In the default mode, MYSQL  is  autocommit  mode, all database updates will be immediately submitted, the order by default, mysql does not support transactions.

But if your  MYSQL  table type uses  InnoDB Tables  or  BDB tables  , your  MYSQL  can use transaction processing. Use SET AUTOCOMMIT=0 to allow MYSQL to be in non-autocommit mode.

In autocommit  mode, you must use  COMMIT  to commit your changes, or use  ROLLBACK  to roll back your changes.

Examples are as follows:

START TRANSACTION;

SELECT @A:=SUM(salary) FROM table1 WHERE type=1;

UPDATE table2 SET [email protected] WHERE type=1;

COMMIT;

What field type is good for recording currency in mysql

The NUMERIC  and  DECIMAL  types are  implemented by Mysql  as the same type, which is  allowed in the SQL92  standard. They are used to store values ​​whose accuracy is extremely important, such as data related to money. When declaring a class are these

When one of the types, the precision and scale can be ( and usually are ) specified;

E.g:

salary DECIMAL(9,2)

In this example, 9 (precision) represents the total number of decimal places that will be used to store the value, and  2 (scale) represents the number of decimal places that will be used to store the value. Therefore, in this case, the range of values ​​that can be stored in the salary column is from -9999999.99 to 9999999.99.

In  ANSI/ISO SQL92  , the syntax  DECIMAL(p) is equivalent to  DECIMAL(p,0) .

Similarly, the syntax  DECIMAL is  equivalent to  DECIMAL(p,0) , where the implementation is allowed to determine the value  p . Mysql  currently does not support any of these variants of DECIMAL/NUMERIC data types. This is generally not a serious problem, because the main benefit of these types comes from the ability to clearly control accuracy and scale. DECIMAL and NUMERIC values ​​are stored as strings rather than as binary floating-point numbers in order to preserve the decimal precision of those values.

One character is used for each digit of the value, the decimal point ( if  scale>0) and the " - " sign ( for negative values ) . If  scale  is  0 , DECIMAL and NUMERIC values ​​do not include a decimal point or fractional part.

The  maximum range of DECIMAL and  NUMERIC  values ​​is the  same as DOUBLE  , but for a given  DECIMAL  or NUMERIC column, the actual range can be limited by the precision or scale of the given column.

When such a column is assigned to a bit of the decimal point exceeds a specified  scale  values of the bits allowed, according to the value of  scale  rounded fifty-four into.

When a  DECIMAL  or  NUMERIC  column is assigned a value whose size exceeds the range implied by the specified ( or default) precision  and  scale  , Mysql stores the corresponding endpoint value representing that range.

What are the mysql-related authority tables?

Mysql  server controls the user's access to the database through the permission table. The permission table is stored in the  mysql  database and initialized by the mysql_install_db script.

These privilege tables are user, db, table_priv, columns_priv and host.

What kinds of locks are there in Mysql?

MyISAM  supports table locks, InnoDB  supports table locks and row locks, the default is row locks

Table-level locks: low overhead, fast locking, and no deadlocks. Large locking granularity, the highest probability of lock conflicts, and the amount of concurrency

lowest

Row-level locks: high overhead, slow locking, and deadlocks. The lock strength is small, the probability of lock conflicts is small, and the concurrency is the highest

database backup

Must be logged in

Export the entire database:

mysqldump  -u  username  -p  database name  >  exported file name

Export a table:

mysqldump  -u  username  -p  database name table name >  exported file name

Export a database structure:

mysqldump  -u dbuser -p -d --add-drop-table

dbname >d:/dbname_db.sql

-d No data --add-drop-table adds a drop table before each create statement     Regarding the MySQL interview questions, I reprinted a more classic one, which is worth recommending! Very basic and comprehensive summary:      MySQL Interview Classic 100 Questions (Collection Edition, with answers)