Python Learning _day42_mysql Table Operations 1

One, configuration file

The service side and the client's character encoding is different, may cause garbled display and so on, in order to unify the character encoding at both ends, can be implemented through the configuration file, of course, such as login account information can also be configured, when the MySQL server is started automatically read the contents of the configuration file. The configuration file (My.ini) must be built under the extracted MySQL file, the format of the specific configuration file is as follows:

#1. When executing the MYSQLD command, the following configuration will take effect when the MySQL service starts[Mysqld]skip-grant-tables#Skip Authorization Table commandport=3306Character_set_server=utf8#set character encoding commandsDefault-storage-engine=innodb#set Storage engine commandsInnodb_file_per_table=1#2. For global configuration of client commands, the following configuration takes effect when the MySQL client command executes[Client]port=3306default-character-set=Utf8user=Rootpassword=123#3. Only for MySQL this client configuration, 2 is the global configuration, and here is only for MySQL this command local configuration[MySQL];p ort=3306;d efault-character-set=Utf8user=Egonpassword=4573#!! If there is no [MySQL], the user's configuration when executing the MySQL command is based on [client]

Second, the storage engine

The storage engine is how to store the data, how to index the stored data, and how to update and query the data. Because the storage of data in a relational database is stored as a table, the storage engine can also be called a table type (that is, the type that stores and operates this table). There is only one storage engine in a database such as Oracle and SQL Server, and all data storage management mechanisms are the same. The MySQL database provides a variety of storage engines. Users can choose different storage engines for the data table according to different requirements, and users can write their own storage engine according to their own needs.
1. mysql-supported storage engine

View the commands for the MySQL supported storage engine: Show Engines\g, view the storage engine commands being used: Show variables like ' storage_engine% ';. MySQL uses the InnoDB storage engine by default.

The storage engines supported by MySQL are described below, respectively:

#InnoDB Storage Enginesupport transactions, whose design goals are primarily for online transaction processing (OLTP) applications. It features a row lock design, supports foreign keys, and supports non-locking reads like Oracle, where the default read operation does not generate locks. From MySQL5.5.8The version starts as the default storage engine. The InnoDB storage engine places the data in a logical table space, which is managed by the InnoDB storage engine itself like a black box. From MySQL4.1 (including 4.1version, you can store the table for each InnoDB storage engine in a separate IBD file. In addition, the InnoDB storage engine supports the use of a bare device (row disk) to establish its tablespace. InnoDB achieves high concurrency by using multi-version concurrency control (MVCC), and implements the SQL standard4 isolation levels, default to the repeatable level, and a use of a type called netx-key locking strategy to avoid the creation of Phantom Reading (phantom) phenomenon. In addition to this, the InnoDB storage engine provides high performance and high-availability features such as insert buffer, two write (double write), Adaptive Hash Index (Adaptive hash indexes), pre-read (read ahead), and more. For storage of data in tables, the InnoDB storage engine uses aggregation (clustered), each table is stored in the order of the primary key, and if no primary key is explicitly specified when the table is defined, the InnoDB storage engine generates one for each row6the ROWID of the byte and takes this as the primary key. The InnoDB storage engine is the most commonly used engine for MySQL databases, and successful applications from companies such as Facebook, Google, and Yahoo! have proven that the InnoDB storage engine is highly available, high performance, and highly scalable. The mastery and understanding of its underlying implementation also requires time and technology to accumulate. If you want to learn more about how the InnoDB storage engine works, implemented, and applied, you can refer to the "MySQL Insider: InnoDB Storage Engine" book. #MyISAM Storage EngineDoes not support transaction, table lock design, support full-text indexing, mainly for some OLAP database applications, in MySQL 5.5.8before the version is the default storage engine (except for the Windows version). A big difference between a database system and a file system is the support for transactions, which are not supported by the MyISAM storage engine. It is not difficult to understand the fundamental. Does the user need to have a transaction in all applications? In the Data warehouse, if there is no ETL these operations, but simply through the report query also need transaction support? In addition, the MyISAM storage engine is unique in that its buffer pool caches only (cache) index files, Instead of caching data files, this is not the same as most databases. #NDB Storage EngineMySQL AB acquired the NDB storage engine from Sony Ericsson Corporation. The NDB storage engine is a clustered storage engine, similar to Oracle's RAC cluster, but unlike the share everything structure of the Oracle RAC, its structure is a cluster architecture that share nothing, thus providing a higher level of high availability. The NDB storage engine is characterized by the fact that the data is all in memory (from 5.1version, the non-indexed data can be placed on disk, so the primary key lookup (primary key lookups) is extremely fast and can be added online NDB data node to improve database performance linearly. Thus, the NDB storage engine is a highly available, high-performance, highly scalable database cluster system, which is also a database application type for OLTP. #Memory Storage EngineAs its name holds, the data in the memory storage engine is stored in RAM, the database restarts or crashes, and the data in the table disappears. It is ideal for staging tables that store temporary data in an OLTP database application or as a dimension table for the Data warehouse in an OLAP database application. The memory storage engine uses a hash index by default instead of the normally familiar B +Tree Index. #infobright Storage EngineThird-party storage engines. It is characterized by columns rather than rows, making it ideal for OLAP database applications. Its official website is http://www.infobright.org/, there are a number of successful data warehousing cases to be analyzed. #Ntse Storage EngineNetEase developed a storage engine for its internal use. The current version does not support transactions, but provides features such as compression, row-level caching, and, in the near future, memory-oriented transaction support. #blackholea black hole storage engine that can be applied to the distribution Master library in primary and standby replication. The MySQL database also has many other storage engines, which are just some of the most commonly used engines. If you like, you can write your own engine, this is the ability of open source to us, but also the charm of open source. 

2, the use of the storage engine

Method One: Specify when the table is under construction

Mode Two: Configure the configuration file

[Mysqld]default-storage-engine=innodbinnodb_file_per_table=1

Third, table data type

The storage engine determines the type of table, and the data stored in the table has different types, each data type has its own width, but the width is optional. Common data types in MySQL include numbers, strings, time types, enumeration types, and collection types, respectively, as described below:

1. Number Type

(1) Integer type

Integer types include: TINYINT SMALLINT mediumint INT BIGINT, the main function is to store age, ID and various numbers, it should be emphasized that the integer type set the number for the display width. The following example:

It can be seen that the width of the setting is not significant, can correctly store the number is whether the number is more than the integer type can receive the range, by setting Zorefill can see the meaning of the display width, as follows:

The default is signed, such as when the input number exceeds the maximum value that can be received by the signed int type, the maximum value of the signed range is stored, as in the following example:

By unsigned you can set int as unsigned, and when the input number exceeds the maximum value that can be accepted by the unsigned int type, the number is stored as the maximum value without the symbol range, as in the following example. Note that if you enter a negative number when you set it to unsigned, it is stored as zero.

(2) Decimal type

The decimal types include the float, double, and decimal three types, the float type supports a maximum number of 255 digits, the largest number of digits after the decimal point is 30, with the increase in decimals, the precision becomes inaccurate; the total number of digits supported by the double type is up to 255. After the decimal point, the maximum number of digits is 30, with the increase of decimals, the precision is higher than float, but it becomes inaccurate; the decimal type supports a maximum of 65 digits, the maximum number of digits after the decimal point is 30, and with the increase in decimals, the accuracy is always accurate, because the inside is stored as a string. , which is needed for accurate numerical calculations.

CREATE table T6 (weight float (256,30); CREATE TABLE T7 (weight double (256,30); CREATE table T8 (Weight decimal ( 65,30); INSERT into T6 values (1.1111111111111111111111111111111111111111111111111111111111111111  INSERT into T7 values (1.1111111111111111111111111111111111111111111111111111111111111111),INSERT INTO T8 VALUES (1.1111111111111111111111111111111111111111111111111111111111111111);

Through the results can be seen the difference between the three accuracy is as follows:

2. Date type

Mainly used to store user registration time, article release time, employee entry time, birth time, expiration time, etc., including the following types of dates:

Date:         month day, such as 2017-11-11time: minutes         and seconds, as:14:11  DATETIME     :11-11 10:14:11, where the range of years is: 1001-9999, the storage time is independent of the time zone, 8 bytes of storage space is used, and the default value is null

TIMESTAMP:    11-11 10:14:11, where the range of years is 1970-2038, the storage time is related to the time zone, 4 bytes of storage space is used, the default value is the current time
Year:        

3. Character type

The character types contain char and varchar, which are different as follows:

#Note: The arguments in char and varchar brackets refer to the length of the character#char type: fixed length, simple rough, wasted space, fast access speedCharacter Length range: 0-255(a Chinese is a character that is a UTF8 encoded 3 bytes) Store: When a value of type char is stored, a space is padded to the right to satisfy the length, for example: Specify a length of 10, save>10 characters are error, deposit <10 characters are padded with spaces until 10 characters are stored for retrieval: When retrieving or saying a query, the detected results automatically delete trailing spaces unless we open pad_char_to_full_length SQL mode (SET sql_mode='Pad_char_to_full_length';)#varchar Type: variable length, precision, space saving, slow access speedCharacter length range: 0-65535,mysql line maximum limit is 65535 bytes, character encoding is Utf-8storage: The varchar type stores the actual contents of the data and is not populated with spaces, if'AB', the trailing spaces will be saved. Emphasize: varchar type will add 1 before real data-2bytes prefix that is used to represent the number of bytes bytes of real data (1-2Bytes Maximum represents 65,535 digits, exactly as MySQL has a maximum byte limit on row, which is sufficient to use if the actual dataThe <255bytes requires a 1Bytes prefix (1bytes=8bit 2**8 The maximum representation of the number is 255) if the real dataThe >255bytes requires a 2Bytes prefix (2bytes=16bit 2**16 The maximum representation of the number is 65535) search: The trailing space will be saved, in the search or query, will also display the contents of the text, including spaces

(1) Differences in width limits

When the length of the data entered is greater than the length of the setting, only the Set length data section is saved from left to right:

When the length of the input data is less than the length of the set, char is stored according to the length of the set, the number of digits is filled with spaces, and varchar stores the length of the actual data, as after the data is created as follows, the result is shown as:

CREATE TABLE T16 (name char (5)), CREATE table t17 (name varchar (5)), insert into t16 values (' a ' ); INSERT into t17 values ('a''pad_char_to_full_length '   fromt17;

(2) Differences in storage mechanisms

As the following table char data is stored in accordance with the set length, not enough space to stand, query in accordance with fixed-length query, fast and efficient, but the memory, varchar is the actual character length of storage, but in order to facilitate the query data, need to add 1-2 bytes prefix before the data, This prefix represents the number of bytes of real data and facilitates data query.

4. Enumerations and collection types

Enumeration: Unum, the value of a field can only be selected in a given range, such as gender:

Set: Set, the value of a field can only be selected in a given range of one or more, as a hobby:

Python Learning _day42_mysql Table Operations 1