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 lock: high overhead, slow lock; 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 overhead and locking 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 5 types of tables: 1, MyISAM2, Heap 3, Merge 4, INNODB 5, MISAM
3. Briefly describe the difference between MyISAM and InnoDB in MySQL database
MyISAM:
Transactions are not supported, but each query is atomic; Supports table-level locking, that is, each operation locks the entire table. The total number of rows stored in the table;
A MYISAM table has three files: index file, table structure file, data file;
The data domain of the index file stores Pointers to the data file. Secondary indexes are basically the same as primary indexes, but secondary indexes are not guaranteed to be unique.
InnoDb:
Acid-supported transactions, which support four isolation levels of transactions; Row-level locking and foreign key constraints are supported, so write concurrency is supported. Total number of rows not stored:
An InnoDb engine stores a file space (shared table space, table size independent of operating system control,
A table may be distributed in multiple files), may be multiple (set to independent table empty, table size is limited by the operating system file size, generally 2 GB), by the operating system file size limit;
A primary key index uses a clustered index (the index’s data domain stores the data file itself), and a secondary index’s data domain stores the value of the primary key. 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 support four transaction isolation level name, and by
The difference between levels? 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? FROM_UNIXTIME converts a Unix timestamp from a MySQL 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:
·. FRM file storage table definition
· Data files with “.myd “(MYData)
Extension Index files have the.MYI (MYIndex) extension
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?
SELECT*FROM TABLE LIMIT 0,50; SELECT*FROM TABLE 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, MEDIUMTEXT
4, LONGTEXT
What are generic SQL functions?
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.
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.
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.
However, if your MySQL table type is InnoDB Tables or BDB Tables, your MySQL can use transaction processing and use SET
AUTOCOMMIT=0 causes MySQL to allow non-autoCOMMIT mode and non-autocommit mode
In 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, and 2(scale) represents the number of decimal places that will be used to store values. 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. SET
2, a BLOB
3, ENUM
4, CHAR
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.
MySQL database master separated from read and write.
4, find the regular table, reduce the amount of data in the 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 duration of lock holding
- Multiple threads try to fetch 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, fuzzy match
2. Do not use an index before OR after the OR statement
3, data type implicit conversion (such as vARCHar without single quotes 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 statements and indexes
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 set the field as null as possible. For example, ‘province’ and ‘gender’ are best applied to ENUM
2. Use joins instead of subqueries
3. Use unions instead of manually created temporary tables
4. Transaction processing
5. Lock tables and optimize transaction processing
6, applicable to foreign keys, optimize lock table
7. Build indexes
8. Optimize the query statement
MySQL, index, primary key, unique index, joint index
What impact does it have on database performance (from both read and write)
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:
Atomicity: indivisibility. Transactions are either all executed or none 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 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 you can think of a transaction as a group of SQL statements bound together as a logical unit of work. If any statement fails, the entire operation fails, and then the operation is 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
When executing SQL statements, use addslashes to convert 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: The time period is stored in yyyY-MM-DD HH:MM:SS format, accurate to second, occupying 8 bytes of storage space. The Datatime type is independent of the time zone
Timestamp: Stored in Timestamp format, occupying 4 bytes and ranging from 1970-1-1 to 2038-1-19, depending on the specified time zone. Datatime. int (‘ Date ‘, ‘Date’, ‘Date’, ‘Date’, ‘Date’, ‘Date’); You can also use the date-time function to calculate Time during the day: store the Time part of the data
Note: Do not use string to store date and time data (usually takes up less storage space than string, you can use the date function for lookup filtering) using int to store date and time is not as good as using timestamp
Indexing is a very important concept for relational databases
A few questions about indexes:
1. What is the purpose of an 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, it can significantly reduce the grouping and sorting time in the query. 2. What are the negative effects of indexes on the 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.
What are the principles for indexing a table?
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 build 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. The inner join is a cross-join with only conditions. Records that meet the conditions are screened out according to a certain condition. Records that do not meet the conditions will not appear in the result set, that is, the inner join only connects the matched rows.
The result set of an outer join contains not only the rows that meet the join criteria, but also the left table, right table, or both tables
These three cases in turn are called left outer join, right outer join, and full outer join. Left outer join, also known as left join, the left table is the main table, all records in the left table will appear in the result set, for those records in the right table do not match, still display, the corresponding field values of the right are filled with NULL. 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).
Create Table,Alter Table,Drop Table, Craete/Drop Index 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:
Entity integrity: Specifies that each row of a 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, including value range and accuracy.
3. Referential integrity: it means that the data of the primary and external keywords of two tables should be consistent, which ensures the consistency of data between tables and prevents data loss or meaningless data from spreading in the database.
4. User-defined integrity: Different relational database systems need some special constraints depending on their application environment. 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 include column constraints (NOT NULL) and table constraints (PRIMARY KEY, Foreign KEY, check, and UNIQUE).
B brother’s public account: Java2b (wechat search)
