First, the introduction of MySQL
1.1. Introduction to the Database
A database is a warehouse that organizes, stores, and manages data according to its structure, and each database has one or more different APIs for creating, accessing, managing, searching, and replicating the saved data. We can also store the data in a file, but it is relatively slow to read and write data in the file. So, now we're using a relational database management system (RDBMS) to store and manage a large amount of data. The so-called relational database is a database based on the relational model, which uses mathematical concepts and methods such as set algebra to process data in the database.
RDBMS is the characteristic of relational database management system:
1. The data appears in tabular form
2. Various record names for each behavior
3. Data fields corresponding to record names for each column
4. A number of rows and columns form a single sheet
5. A number of forms form database
6. Here are a few terms:
Primary key: The primary key is unique. A data table can contain only one primary key. You can use the primary key to query the data.
FOREIGN key: A foreign key is used to correlate two tables.
Composite key: Composite key (key combination) Multiple columns are used as an index key, and are typically applied to composite indexes.
Index: Use an index to quickly access specific information in a database table. An index is a structure that sorts the values of one or more columns in a database table. A directory similar to a book.
Referential integrity: Referential integrity requires that references to entities that do not exist are not allowed in the relationship. and entity integrity are the integrity constraints that the relational model must satisfy in order to ensure the consistency of the data.
MySQL support large database, support 50 million records of Data Warehouse, 32-bit system table file can support the maximum 4gb,64 bit system support the largest table file is 8TB.
1.2. Common management commands for MySQL
Use database name: Select the MySQL database you want to manipulate, and all MySQL commands are only for that database after using this command.
SHOW DATABASES: Lists a list of databases for the MySQL database management system.
Show TABLES: Displays all the tables that make the database, using the use command to select the database to manipulate.
Show COLUMNS from data table: Displays data table properties, property types, primary key information, whether null, default value, and other information.
DESC table name: Show Table structure
Create database name CharSet ' Utf-8 ': Creates a database that supports Chinese
Drop DATABASE Database name: delete databases
Show index from data table: Displays detailed index information for the data table, including primary key (primary key)
1.3. mysql Data type
1. Numeric type
type |
size |
Range (signed) |
Range (unsigned) |
Use |
TINYINT |
1 bytes |
(-128,127) |
(0,255) |
Small integer value |
SMALLINT |
2 bytes |
(-32 768,32 767) |
(0,65 535) |
Large integer value |
Mediumint |
3 bytes |
(-8 388 608,8 388 607) |
(0,16 777 215) |
Large integer value |
int or integer |
4 bytes |
(-2 147 483 648,2 147 483 647) |
(0,4 294 967 295) |
Large integer value |
BIGINT |
8 bytes |
(-9 233 372 036 854 775 808,9 223 372 036 854 775 807) |
(0,18 446 744 073 709 551 615) |
Maximum integer value |
FLOAT |
4 bytes |
( -3.402 823 466 e+38,1.175 494 351 E-38), 0, (1.175 494 351 e-38,3.402 823 466 351 e+38) |
0, (1.175 494 351 e-38,3.402 823 466 e+38) |
Single precision Floating point value |
DOUBLE |
8 bytes |
(1.797 693 134 862 315 7 e+308,2.225 073 858 507 201 4 E-308), 0, (2.225 073 858 507 201 4 e-308,1.797 693 134 862 315 7 e+3 08) |
0, (2.225 073 858 507 201 4 e-308,1.797 693 134 862 315 7 e+308) |
Double precision Floating point value |
DECIMAL |
For decimal (m,d), if m>d, is m+2 otherwise d+2 |
Values that depend on M and D |
Values that depend on M and D |
Decimal value |
2. Date and Time type
type |
size (bytes) |
Range |
format |
Use |
DATE |
3 |
1000-01-01/9999-12-31 |
Yyyy-mm-dd |
Date value |
Time |
3 |
' -838:59:59 '/' 838:59:59 ' |
HH:MM:SS |
Time Value or duration |
Year |
1 |
1901/2155 |
YYYY |
Year value |
Datetime |
8 |
1000-01-01 00:00:00/9999-12-31 23:59:59 |
YYYY-MM-DD HH:MM:SS |
Blend date and time values |
TIMESTAMP |
4 |
1970-01-01 00:00:00/2037, sometime |
YYYYMMDD HHMMSS |
Mixed date and time values, timestamp |
3. String
type |
size |
Use |
CHAR |
0-255 bytes |
Fixed length string |
VARCHAR |
0-65535 bytes |
Variable length string |
Tinyblob |
0-255 bytes |
A binary string of no more than 255 characters |
Tinytext |
0-255 bytes |
Short text string |
Blob |
0-65 535 bytes |
Long text data in binary form |
TEXT |
0-65 535 bytes |
Long Text data |
Mediumblob |
0-16 777 215 bytes |
Medium-length text data in binary form |
Mediumtext |
0-16 777 215 bytes |
Medium-Length text data |
Longblob |
0-4 294 967 295 bytes |
Large text data in binary form |
Longtext |
0-4 294 967 295 bytes |
Maximum text data |
1.4. Business
1. Definition
A transaction consists of one or more SQL statements of a single cell, in which each MySQL statement is interdependent. As an integral whole, the entire unit will be rolled back if an SQL statement in the cell fails or produces an error. All data that is affected is returned to the previous state of the data, and if all the SQL statements in the cell succeed, the thing is executed smoothly.
2. Transaction characteristics
Only databases or tables that use the InnoDB database engine support transactions in MySQL
Transactions can be used to maintain the integrity of the database, to ensure that a batch of SQL statements are either all executed or not executed
Transactions are used to manage insert,update,delete statements
3. Four major features of business
A. Atomicity of a transaction: a set of transactions, either successful or withdrawn.
B. Stability: There are illegal data (foreign key constraints and the like) transaction recall.
C. Isolation: Transactions run independently of each other. The result of one transaction affects other transactions, and then other transactions are recalled. Transaction 100% isolation requires a sacrifice of speed.
D. Reliability: After the hardware and software crashes, the INNODB data table driver uses the log file to refactor the changes. Reliability and high speed cannot be combined, innodb_flush_log_at_trx_commit option, decide when to save the transaction in the log.
1.5 Index
Python Basics (12)