MySQL Theoretical knowledge Summary

01What are the common relational database and non-relational types? 1. Relational databases use foreign key associations to establish a table-to-table relationship---------Common: SQLite, Oracle, MySQL2. A non-relational database usually means that the data is stored in the database as an object, and the relationship between objects is determined by the properties of each object itself---common ones are: MongoDb, Redis02. MySQL common database engine and comparison? · MyISAM: The default MySQL plug-in storage engine, which is one of the most commonly used storage engines in the Web, data warehousing, and other application environments. InnoDB: For transactional applications, with many features, including acid transaction support. (Row-level lock available) · BDB: An alternative to the INNODB transaction engine that supports commit, rollback, and other transactional features. Memory: Keep all your data in RAM and provide extremely fast access in environments where you need to quickly find references and other similar data. Merge: Allows a MySQL DBA or developer to logically group together a series of equivalent MyISAM tables and reference them as 1 objects. Ideal for VLDB environments such as data warehousing. Archive: Provides the perfect solution for storing and retrieving a large number of rarely cited historical, archival, or security audit information. Federated: The ability to link multiple separate MySQL servers to create a logical database from multiple physical servers. Ideal for distributed environments or data mart environments. Cluster/Ndb:mysql's clustered database engine, especially suitable for applications with high performance lookup requirements, also requires the highest uptime and availability.        Other: The other storage engines include CSV (referencing a comma-delimited file used as a database table), blackhole (for temporary suppression of application input to the database), and the example engine, which can help with the quick creation of a custom plug-in storage engine. Generally do not use the transaction, please use the MyISAM engine, use the transaction, the general use of InnoDB------Fairies, the main thing we're using now is innodb. Note that changing the storage_engine configuration variable makes it easy to change the default storage engine for the MySQL server. 03. Outlining the three paradigms of data? What is a paradigm: in short, database design has a great relationship to the data storage performance, as well as the developer's ability to manipulate the data. Therefore, the establishment of a scientific, normative database is required to meet some specifications to optimize the data storage method.        In a relational database, these specifications can be called paradigms. What is the three paradigms: the first paradigm: when all the properties of the relational mode R are not decomposed into more basic units of data, the R is satisfied with the first paradigm, and précis-writers is 1NF.        Satisfying the first paradigm is the minimum requirement for the normalization of relational schemas, otherwise there will be many basic operations that cannot be implemented in such a relational pattern.        The second paradigm: if the relationship pattern R satisfies the first paradigm, and all non-principal properties of R are completely dependent on each candidate key attribute of R, the R satisfies the second normal form and précis-writers is 2NF.        The third paradigm: set R is a relational pattern satisfying the first paradigm condition, X is any set of properties of R, if x non-transitive relies on any one of R's candidate keywords, and R satisfies the third normal form, précis-writers is 3NF. Note: A relationship is essentially a two-dimensional table in which each row is a tuple, and each column is an attribute04. What is a transaction?        How does MySQL support transactions?        A transaction is a batch of SQL statements, but the batch is an atom, indivisible, either executed, or rolled back (rollback) is not executed. The four main characteristics of a transaction are often said acid:1. atomicity (all operations are either all successful or all failed to rollback)2. Consistency (the transaction must be in a consistent state before and after execution.) )3Isolation (Database-enabled transactions for each user, cannot be interfered by operations of other transactions, and multiple concurrent transactions are isolated from each other)4. Persistence (Once a transaction is committed, a change to the data in the database is permanent, even if a failure is still able to recover the last update through the log) only databases or tables that use the INNODB database engine in MySQL support transactions There are two main methods of MYSQL transaction processing:1, begin, ROLLBACK, commit to begin a transaction ROLLBACK transaction rollback Commit TRANSACTION Acknowledgement2, directly using set to change the MySQL auto-commit mode: Set autocommit=0 Prohibit auto-commit SET autocommit=1turn on auto-commit05briefly describe a one-to-many and many-to-many application scenarios in database design. One-to-many: students and classes---A student can only belong to a class, a class may have multiple students many-to-many: students and courses---A student can choose multiple courses, a course can also be selected by multiple students06How do I implement a marketplace commodity counter based on a database? Create a Marketplace table---contains (ID, product name, corresponding quantity per item) CREATE TABLE product (ID primary key auto_increment, PName Varc Har (64), Pcount int); 07. Describe triggers, functions, views, stored procedures?                Trigger: A trigger is a special stored procedure, which is a block of code that is automatically executed by MySQL at INSERT, UPDATE, delete. Create Trigger Trigger_name after/before insert/update/Delete on table name forEach row begin SQL statement: (one or more sentences of the triggered statement) the End Function: many built-in functions are available in MySQL, and you can customize the function                    　　      (Implementation programmer needs SQL logic processing) Custom function creation syntax: Create: Creation function name (parameter list)                        RETURNS return value type function body modification: Alter function name [characteristic ...]                                Delete: Drop function [IF EXISTS] Functions name invocation: SELECT function name (parameter list) Views: A view is a virtual table formed by the results of a query, which is a projection of a table from an operation CREATE VIEW view_name as SELECT statement stored procedure: Encapsulates a piece of code when it is executed                        Can be implemented by invoking the stored procedure (it is more efficient to execute the SQL statement after the first compilation without having to compile again) CREATE procedure stored procedure name (parameter, parameter,...) Begin//Code End 08. MySQL Index kind index is a special kind of file (the index on the InnoDB data table is a part of the table space), more generally speaking, the database index is like a book in front of the directory, can speed up the database query speed MySQL index type:1. Normal index: This is the most basic index, it has no restrictions2. Unique index: The value of an indexed column must be unique, but a null value is allowed, and if it is a composite index, the combination of column values must be unique3. Full-Text Indexing: Full-text indexes are available only for MyISAM tables, can be created as part of a CREATE TABLE statement from char, varchar, or text columns, or subsequently added using ALTER TABLE or CREATE INDEX (Remember that for large data tables, generating a full-text index is a very time consuming way to consume hard disk space.)4. Single-column index, multicolumn index: Multiple single-column indexes differ from the query effect of a single multicolumn index, because MySQL can only use one index when executing a query, and one of the most restrictive indexes is selected from multiple indexes. 5. Combined index (leftmost prefix): A simple understanding is a way to further squeeze index efficiency from the left-most starting combination (a single-column index) 09Under what circumstances does the index follow the rule with the leftmost prefix? 10What is the difference between a primary key and a foreign key? 1A primary key is a unique identifier that can determine a record2A foreign key is used to associate with another table, which is a field that determines the record of another table and is used to maintain data consistency11.    What are the common functions of MySQL? Aggregate function: AVG (COL) returns the average of the specified column count (col) returns the number of non-null values in the specified column min (col) returns the minimum value of the specified column MAX (col) Returns the maximum value of the specified column SUM (col) returns all the values of the specified column and Group_concat (COL) Returns the result of a combination of column values belonging to a set of mathematical functions: ABS (x) returns the absolute value of X Binary for value BIN (x) returns X (Oct returns octal, HEX returns hex)12List of 8 scenarios that create an index but cannot hit the index. 1If there is an or in the condition, it will not be used even if there is a conditional index (which is why the use of or is minimized)2for multi-column indexes, not the first part of use, the index is not used3.like query is in%Start4If the column type is a string, be sure to use quotation marks in the condition to reference the data, otherwise you will not use the index5If MySQL estimates that using a full table scan is faster than using an index, the index is not used6Querying for small tables7prompt not to use index8statistical data is untrue9the index column in the composite index that is not the first position is referenced separately.13. How do I turn on slow log queries? 1 perform SHOW VARIABLES like "%slow%"To see if MySQL turns on slow query slow_query_log slow query on status OFF not on to open slow_query_log_file slow query log storage location (this directory requires M Ysql The writable permissions of the running account, generally set to the MySQL data storage directory)2Modify the configuration file (placed under [mysqld]) and restart the Long_query_time query for more than a few seconds before logging3whether the test was successful4Information format for slow query log files14. Database Import and Export command (structure +data)? 1. Export the entire database mysqldump-u user name-p password database name >exported filenames such as: C:\Users\jack> Mysqldump-uroot-pmysql sva_rec >E:\sva_rec.sql2. Export a table, including the table structure and data mysqldump-u user name-p password database name table name >exported filenames such as: C:\Users\jack> Mysqldump-uroot-pmysql sva_rec date_rec_drv>E:\date_rec_drv.sql3. Export a database structure for example: C:\Users\jack> mysqldump-uroot-pmysql-d sva_rec >E:\sva_rec.sql4. Export a table with only a table structure mysqldump-u user name-p password-d database name Table name >exported filenames such as: C:\Users\jack> mysqldump-uroot-pmysql-d sva_rec date_rec_drv>E:\date_rec_drv.sql5Import the database common Source command into MySQL database console, such as MySQL-U Root-p MySQL>The use database then uses the source command, followed by the script file (for example, the. SQL used here) MySQL>Source D:wcnc_db.sql15. Database optimization scheme? The general idea from the following aspects:1, select the most applicable field property2. Use connection (join) instead of subquery (sub-Queries)3, using Union (union) instead of manually created temporary tables4, transactions (when multiple users use the same data source at the same time, it can use the method of locking the database to provide users with a secure way to access, so as to ensure that the user's operation is not disturbed by other users)5. Lock the table (in some cases we can get better performance by locking the table)6, the use of foreign keys (locking the table method can maintain the integrity of the data, but it does not guarantee the relevance of data.) This time we can use the foreign key)7, using the index8, optimized query statements (in most cases, using indexes can improve the speed of queries, but if the SQL statement is not used properly, the index will not function as it should)16What is the difference between. Char and varchar? Char: fixed length, the number of accesses to char is relatively fast varchar: indefinite length, the access speed is relatively slow how to choose can be based on a few aspects of consideration:1for the MyISAM table, use char as much as possible, especially for those MyISAM and ISAM data tables that often need to be modified to form fragments, and its disadvantage is to occupy disk space;2, for the InnoDB table, because its data row internal storage format does not differentiate between fixed-length data rows and variable-length data rows (all data rows share a header section, which holds pointers to the respective data columns), so using the char type is not necessarily better than using the varchar type is good. In fact, since the char type usually takes up more space than the varchar type, it reduces the footprint and reduces the disk I/O, the use of varchar type is more advantageous;3, store very short information, such as house number 101,201...               Such a short message should be char, because varchar also has a byte to store the information length, the intention of saving storage is now worth the candle. 4, fixed-length. For example, using the UUID as the primary key, the use of char should be more appropriate.               Because of his fixed length, the varchar dynamically disappears according to the length of the character, and also takes up a length of information. 5, a very frequent change of column. Because varchar has to have extra calculations for each storage, to get the length of the work, if a very frequent change, then there will be a lot of energy for the calculation, which is not required for char. 17brief description of MySQL execution plan?    The Execute plan explain command is the primary way to see how the query optimizer decides to execute a query. This feature has limitations and does not always tell the truth, but its output is the best information that can be obtained, and the execution process is reversed by the output result18. Under the unique index of name, the following differences are outlined: SELECT* fromTB WHERE name = ' Oldboy '-------------find all the name in the TB table =data Select for ' Oldboy '* fromTB WHERE name = ' Oldboy ' LIMIT 1------Find all the data in the TB table for name = ' Oldboy ' only take the first one of them

MySQL Theoretical knowledge Summary