June 11 python review MySQL

01. List Common relational databases and non-relational types.

1. Relational databases use foreign key associations to establish relationships between tables,---------Common: SQLite, Oracle, MySQL

2. 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: MongoDb, Redis

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 large amounts of historically, archived, or security audit information that is rarely referenced.

· 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, the use of transactions, the general use of InnoDB------Fairy AH learning now we use mainly InnoDB note, by changing the Storage_engine configuration variables, The default storage engine for MySQL servers can be easily changed.

03. Describe the three main 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 are the three main 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 attribute

04. 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 fail back)

2. Consistency (the transaction must be in a consistent state before and after execution.) ）

3. Isolation (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 (Once a transaction has been committed, the changes to the data in the database are 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, with Begin, ROLLBACK, COMMIT to achieve begin start a transaction ROLLBACK transaction rollback COMMIT transaction acknowledgment

2, directly with set to change the MySQL auto-commit mode: Set autocommit=0 prohibit auto-commit set autocommit=1 turn on auto-commit

05. Describe a one-to-many and many-to-many scenarios in database design?

One-to-many: students and class---a student can belong to only one class, a class can have more than one student

Many-to-many: students and courses---a student can choose multiple courses, and a course can be selected by multiple students

06. How to implement the mall commodity counter based on the database?

Create a Marketplace table---contains (ID, product name, each item corresponds to quantity)

CREATE TABLE Product

(ID primary Key auto_increment,

PName varchar (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

For each row

Begin

SQL statement: (a sentence or multiple sentences of a triggered statement)

End

Functions: Many built-in functions are available in MySQL, and you can also customize functions (Implementing programmers need SQL logic processing)

Custom Function Creation Syntax:

Created: Create function name (parameter list)

RETURNS return value type function body

Modified: 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 through some sort of operation.

CREATE VIEW view_name AS SELECT statement

Stored procedures: to encapsulate a piece of code, when you want to execute the code, you can call the stored procedure to implement (after the first compilation after the call does not need to compile again, more efficient than the execution of SQL statements)

Create procedure stored procedure name (parameters, parameter,...)

Begin

Code

End

. mysql Index type

An index is a special kind of file (an index on a InnoDB data table is an integral part of a table space), and more generally, a database index is like a directory in front of a book, speeding up the query speed of a database.

Types of MySQL indexes:

1. Normal index: This is the most basic index, it does not have any restrictions

2. 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 unique

3. Full-Text Indexing: Full-text indexing is 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)

09. Under what circumstances does the index follow the rule with the leftmost prefix?

10. What is the difference between a primary key and a foreign key?

1. Primary key is a unique identifier that can determine a record

2. Foreign keys are used in association with another table, which is a field that determines the record of another table and is used to maintain data consistency

What are the common functions of MySQL?

Aggregation functions:

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 the sum of all values of the specified column

Group_concat (COL) Returns the result of a combination of column value connections belonging to a group

Mathematical functions:

ABS (x) returns the absolute value of X

BIN (x) returns the binary of X (Oct returns octal, HEX returns hex)

12. List the 8 scenarios in which the index was created but not hit.

1. If 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)

2. For multi-column indexes, not the first part of use, the index is not used

3.like queries are preceded by%

4. If 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 index

5. If MySQL estimates that using a full table scan is faster than using an index, the index is not used

6 Query for small tables

7 tips do not use indexes

8 Statistical data untrue

9. Separate references to index columns in the compound index that are not the first position.

13. How do I turn on slow log queries?

1 Execute 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 The directory requires the writable permissions of the MySQL running account and is generally set to the MySQL data storage directory.

2 Modify the configuration file (placed under [mysqld]), restart the Long_query_time query more than how many seconds to log

3 The test is successful

4 Information format for slow query log files

14. Database Import and Export command (structure + data)?

1. Export the entire database

Mysqldump-u User name-p password database name > exported file name

For example:c:\users\jack> mysqldump-uroot-pmysql sva_rec > E:\sva_rec.sql

2. Export a table, including table structure and data

Mysqldump-u User name-p password database name table name > exported file name

For example:c:\users\jack> mysqldump-uroot-pmysql sva_rec date_rec_drv> e:\date_rec_drv.sql

3. Export a database structure

For example:c:\users\jack> mysqldump-uroot-pmysql-d sva_rec > E:\sva_rec.sql

4. Export a table with only a table structure

Mysqldump-u User name-p password-d database name Table name > exported file name

For example:c:\users\jack> mysqldump-uroot-pmysql-d sva_rec date_rec_drv> e:\date_rec_drv.sql

5. Import the database

Common source Commands

Go to MySQL Database console,

such as Mysql-u root-p mysql>use database

Then use the source command, followed by the script file (for example, the. SQL used here)

Mysql>source D:wcnc_db.sql

15. Database Optimization program?

The general idea from the following aspects:

1. Select the most applicable field properties

2. Use connection (join) instead of subquery (sub-queries)

3. Use Union (Union) instead of manually created temporary table

4, 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. Use Index

8, optimized query statement (in most cases, using an index can improve the speed of the query, but if the SQL statement is not used properly, the index will not play its due role)

What is the difference between. Char and varchar?

Char: fixed length, char has relatively fast access

VARCHAR: variable length, relatively slow access speed

How to choose can be based on a few aspects of consideration:

1, for MyISAM table, as far as possible to use char, for those who often need to modify and easily form fragments of MyISAM and ISAM data table is more so, its disadvantage is to occupy disk space;

2, for the InnoDB table, because its data row internal storage format for fixed-length data rows and variable-length data rows are not differentiated (all data rows share a header section, which contains pointers to the relevant data columns), Therefore, using the char type is not necessarily better than using a varchar type. In fact, because the char type usually takes up more space than the varchar type, it is more advantageous to use the varchar type from the point of view of reducing space consumption and reducing disk I/O;

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. Column with very frequent changes. 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.

17. Describe the 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 result

18. Under the unique index of name, the following differences are outlined:

SELECT * from TB where name = ' Oldboy '-------------find data for all name = ' Oldboy ' in the TB table

SELECT * from TB where name = ' Oldboy ' LIMIT 1------Find the data for all name = ' Oldboy ' in the TB table take only the first one of them

June 11 python review MySQL