Python full stack development * MySQL data type * 180829

* Operation of the library (increase and deletion check)
I. System database
View system library commands show databases
1.information_schema:
Virtual library, does not occupy disk space, storage is the database startup parameters, such as user table information, column information, permission information, character information, etc.
2.performance_schema:
MySQL 5.5 started a new database: Mainly used to collect database server performance parameters, record the processing of query requests occurred in various events, locks and other phenomena
3.MYSLQ:
Authorization Library, access information for primary storage system users
4.test:
MySQL database system automatically created test database
Two. Create a database
1. SOS syntax: help CREATE DATABASE;
2. Creating database Syntax Create database name CharSet UTF8;
3. Database naming rules:
Can consist of letters, numbers, underscores, @, #, $
Case sensitive
Uniqueness
You cannot use keywords such as create select
Numbers cannot be used alone
Maximum 128 bits
Three. Database-related Operations
1. View the database show databases;
2. View current library show create database db1;
3. View your library Select database ()
4. Select database Use database name
5. Delete the database drop databases name
6. Alter DATABASE DB1 CharSet UTF8;
Four additions:
The 1.SQL language is primarily used to access data, query data, update data, and manage relational database systems, and SQL is developed by IBM. There are 3 types of SQL languages:
(1) DDL statement Database Definition Language: Database, table, view, index, stored procedure, such as Create DROP ALTER
(2) DML statement Database manipulation language: Inserting data insert, deleting data Delete, updating data update, querying data Select
(3) DCL Statement Database Control Language: for example, to control user access rights grant, REVOKE

* Operation of the table
I. Storage Engine
1. The tables in the database should also have different types, the table type is different, will correspond to the MySQL different access mechanism, the table type is also called 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.
2. There is only one storage engine in databases such as Oracle and SQL Server, and all data storage management mechanisms are the same
The 3.MYSQL database provides a variety of storage engines. Users can choose different storage engines for their data tables according to different requirements, and users can also
Your own need to write your own storage engine
Two. mysql-supported storage engine
View all supported engines show engines\g;
View the storage engine being used show variables like ' storage_engine% ';
1.InnoDB Storage Engine
2.MyISAM Storage Engine
3.Memory Storage Engine
4.BLACKHOLE Black hole Storage Engine
5. Specify the table type/Storage Engine command:
CREATE TABLE t1 (id int) engine=innodb;# is not written by default is InnoDB
Small exercise:
Create four tables with Innodb,myisam,memory,blackhole storage engine for insert data testing
CREATE TABLE t1 (id int) Engine=innodb;
CREATE TABLE t2 (id int) Engine=myisam;
CREATE table t3 (id int) engine=memory;
CREATE table t4 (id int) engine=blackhole;
To view the files in the database:
(1). frm is a frame structure that stores data tables
(2). IBD is a MySQL data file
(3). MyD is the extension of the data file for the MyISAM table
(4). Myi is the extension of the index of the MyISAM table
(5) Memory after restarting MySQL or restarting the machine, the data in the table is emptied
(6) Blackhole Insert any data into the table, which is equivalent to throw into the black hole, the table will never save records.
Three. The table describes:
Table is equivalent to a file, a record in a table is equivalent to a row of the file, but a record in the table has a corresponding title, called the table field.
Id,name,sex,age,birth is called a field, the rest, a row of content is called a record
Four. Create a table
Grammar:
CREATE TABLE Table name (
Field name 1 type [(width) constraint],
Field Name 2 Type [(width) constraint],
Field Name 3 Type [(width) constraint]
);
#注意:
1. Field names cannot be the same in the same table
2. Width and constraints are optional
3. Field names and types are required
1. Create a database
Create DATABASE DB2 (CharSet UTF8;) can be omitted
2. Using the database
Use DB2
3. Create a table
CREATE TABLE A1 (
ID int,
Name varchar (50)
Age Int (3)
);
4. Inserting records into a table
INSERT INTO A1 values
(1, "MJ", 18),
(2, "WU", 28);
5. Querying the data and structure of the table
(1) Querying the data stored in the table
SELECT * FROM A1;
(2) View the structure of the A1 table
DESC A1;
(3) View the detailed structure of the table
Show CREATE TABLE a1\g;
6. Copying a table
(1) Creating a new data db3 create Database db3 charset UTF8;
(2) using DB3 use DB3
(3) Copy table structure and copy record CREATE TABLE B1 select * from db2.a1
(4) View the data and table structure in the DB3 folder: SELECT * from DB3.B1;
7. If you copy only the table structure, do not log
#在db2数据库下新创建一个b2表, give a Where condition, condition is not set, condition is false, copy table structure only
CREATE TABLE b2 select * from db2.a1 where 1>5;
View table Structure desc B2;
View data in the table structure, is empty data; SELECT * from B2;
Method Two:
CREATE table b3 like db2.a1;
7. Delete the table:
drop table indicates;

* Data type
Introduced:
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.
Detailed reference Link: http://www.runoob.com/mysql/mysql-data-types.html
I. MySQL common data types Summary:
1. Digital
Integral type: tinyint int bigint
Decimal:
FLOAT: Not accurate when the number of bits is longer
Double: not accurate when the number of bits is longer
Decimal: The precise internal principle is stored in string form;
2. String:
CHAR (10): Simple rough, wasted space, fast access speed
VARCHAR: precision, space saving, slow access speed
SQL optimization: When you create a table, the type of the fixed length (gender) is put forward, and the length (address description information) is placed backwards
Larger than 255 characters, the file path is stored in the database, (only the path or URL is stored in the image video database).
3. Time Type common datetime
4. Enumeration types and collection types
Two. Numeric type
(i) Integer type: TINYINT SMALLINT mediumint INT BIGINT Default signed
Function: Store age, grade, ID, various numbers, etc.
1.tinyint[(m)] [unsigned] [Zerofill]
A small integer that is used to hold some range of integer numeric ranges:
There are symbols:
-128 ~ 127
Unsigned:
0 ~ 255
There is no boolean value in MySQL and is constructed using tinyint (1).
2. int[(m)][unsigned][zerofill]
Integers, data types are used to hold some range of integer numeric ranges:
There are symbols:
-2147483648 ~ 2147483647
Unsigned:
0 ~ 4294967295
3.bigint[(m)][unsigned][zerofill]
A large integer that is used to hold some range of integer numeric ranges:
There are symbols:
-9223372036854775808 ~ 9223372036854775807
Unsigned:
0 ~ 18446744073709551615
Attention:
The default is signed; [unsigned] (Can be set)
The storage behind the int type is the display width, not the storage width
Zerofill with 0 Fill mysql> create TABLE t4 (ID int (5) unsigned zerofill);
When you specify a width for the type, just specify the display width of the query results, regardless of the storage scope, as follows
In fact, we don't have to specify a display width for an integer type, so you can use the default.
The default display width, which is based on the maximum value plus 1
(b) Floating-point type (storage salary, height, weight, system parameters)
1. Fixed-point number type: Dec equals decimal
2. Float Type: float double
Grammar:
Single precision float:float[(m,d)] [UNSIGNED] [Zerofill]
Parameter explanation: M is full length and D is the number after the decimal point. m maximum value is 255,d maximum of 30
There are symbols:
-3.402823466E+38 to-1.175494351e-38,
1.175494351E-38 to 3.402823466E+38
Unsigned:
1.175494351E-38 to 3.402823466E+38
Accuracy:
As the number of decimals increases, the accuracy becomes inaccurate * * *
Double precision double:double[(m,d)] [UNSIGNED] [Zerofill]
Parameter explanation: Double-precision floating-point number (non-accurate decimal value), M is full length, D is after decimal point. M maximum value is 255,d maximum of 30
There are symbols:
-1.7976931348623157E+308 to-2.2250738585072014e-308
2.2250738585072014E-308 to 1.7976931348623157E+308
Unsigned:
2.2250738585072014E-308 to 1.7976931348623157E+308
Accuracy:
With the increase in decimals, accuracy is higher than float, but it can become inaccurate * * *
Precision decimal:decimal[(m[,d]) [unsigned] [Zerofill]
Parameter explanation: The exact decimal value, M is the total number of integers (minus sign), and D is the number after the decimal point. The M maximum value is 65,d maximum of 30.
Accuracy:
With the increase of decimals, accuracy is always accurate * * *
This type is required for precise numerical calculations
The reason that decaimal can store exact values is because they are stored internally as strings.
Three. Date Type: (Date time DATETIME TIMESTAMP year)
Role: Store user registration time, article release time, employee entry time, birth time, expiration time, etc.
Grammar:
Copy Code
Grammar:
Year
YYYY (1901/2155)
CREATE TABLE T8 (Born_year year), #无论year指定何种宽度, last year by default (4)

DATE
YYYY-MM-DD (1000-01-01/9999-12-31)

Time
HH:MM:SS (' -838:59:59 '/' 838:59:59 ')

Datetime

Yyyy-mm-dd HH:MM:SS (1000-01-01 00:00:00/9999-12-31 23:59:59 Y)
CREATE TABLE T9 (d date,t time,dt datetime);
INSERT into T9 values (now (), now (), now ())
Call the now () function that comes with MySQL to get the time specified by the current type

TIMESTAMP

YYYYMMDD Hhmmss (1970-01-01 00:00:00/2037)
CREATE TABLE T10 (time timestamp);
INSERT into T10 values (now ());
Add:
In many of the scenarios that are actually applied, both of these date types are available to meet our needs, with storage accuracy of seconds, but in some cases they show their merits and demerits.
Here's a summary of the differences between the two types of dates.

The date range for 1.DATETIME is 1001--9999 years, and the timestamp time range is 1970--2038 years.

2.DATETIME storage Time is independent of time zone, timestamp storage time is related to time zone, and the displayed value depends on time zone. On the MySQL server,
The operating system and client connections all have time zone settings.

The 3.DATETIME uses 8 bytes of storage space, and the timestamp storage space is 4 bytes. As a result, timestamp is more space efficient than DateTime.

The default value for 4.DATETIME for null;timestamp fields is not empty by default (not NULL), the default is the current time (Current_timestamp),
If no special handling is made, and the UPDATE statement does not specify an updated value for the column, the default update is the current time.
Attention:
#1. When inserting a time separately, you need to insert it in the form of a string, in the corresponding format
#2. When inserting a year, use 4-bit values as much as possible
#3. When inserting a two-bit year, <=69, starting with 20, such as 50, result 2050
>=70, starting with 19, for example 71, result 1971
CREATE table T12 (y year);
INSERT into T12 values (50), (71);
Four. Character type:
Note: The arguments in char and varchar brackets refer to the length of the character
1. Char type: fixed length, simple rough, wasted space, fast access speed
Character Length range: 0-255 (one Chinese is a character, 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 will be error, save <10 characters will be filled with space until enough 10 characters to store
Retrieval:
When retrieving or saying a query, the results are automatically deleted trailing blanks unless we open pad_char_to_full_length SQL mode (set SQL mode: Set sql_mode = ' pad_char_to_full_length ';
default mode for querying sql: SELECT @ @sql_mode;)
2.varchar Type: variable length, precision, space saving, slow access speed
Character Length range: 0-65535 (if greater than 21845 prompts for other types.) MySQL line maximum limit is 65535 bytes, character encoding is utf-8:https://dev.mysql.com/doc/refman/5.7/en/column-count-limit.html
Store:
The varchar type stores the actual contents of the data, does not fill with spaces, and if ' AB ', the trailing spaces are saved.
Emphasis: The varchar type will prefix the real data with 1-2bytes, which is used to represent the bytes bytes of real data (1-2bytes maximum of 65,535 digits, exactly the maximum byte limit of MySQL to row, which is sufficient)
A 1Bytes prefix is required if the true data <255bytes (1bytes=8bit 2**8 The maximum representation of the number is 255)
A 2Bytes prefix is required if the true data >255bytes (2bytes=16bit 2**16 The maximum representation of the number is 65535)
Retrieval:
The trailing space will be saved, and the contents including the blanks will be displayed normally when the query is retrieved or queried.
3. Related functions:
Length (): View the number of bytes
Char_length (): View number of characters
To view the number of bytes
#char类型: 3 Chinese characters + 2 spaces =11bytes
#varchar类型: 3 Chinese characters + 1 spaces =10bytes
Summarize:
#常用字符串系列: char and varchar
Note: Although varchar is more flexible to use, the char data type can be processed faster and sometimes more than 50% of the varchar processing speed from the overall system performance perspective. Therefore, users should consider various factors in designing the database in order to achieve the best balance

#其他字符串系列 (efficiency: char>varchar>text)
Text series Tinytext text Mediumtext longtext
BLOB series Tinyblob blob Mediumblob Longblob
Binary Series Binary VARBINARY

The Text:text data type is used to hold large, variable-length strings that can be set up to 65535 (2**16 1) characters.
Mediumtext:a TEXT column with A maximum length of 16,777,215 (2**24? 1) characters.
Longtext:a TEXT column with A maximum length of 4,294,967,295 or 4GB (2**32? 1) characters.

Five. Enumeration types and collection types
The value of a field can be selected only in a given range, such as a radio box, a multi-marquee
Enum: Radio can only select a value within a given range, such as gender sex sex male male/female female
Set: Multiple selection in a given range can choose one or more values (hobby 1, hobby 2, hobby 3 ...) ）
Example:
mysql> CREATE TABLE consumer (
ID int,
Name varchar (50),
--Sex enum (' Male ', ' female ', ' other '),
-Level enum (' Vip1 ', ' vip2 ', ' vip3 ', ' vip4 '), #在指定范围内, choose one more
Fav set (' Play ', ' music ', ' read ', ' study ') #在指定范围内, multi-Select
);
mysql> INSERT INTO consumer values
(1, ' Zhao Yun ', ' Male ', ' vip2 ', ' read,study '),
--(2, ' Zhao Yun 2 ', ' Other ', ' vip4 ', ' play ');
Six. Integrity constraints
(i) Introduction
Constraints are the same as the width of the data type, which are optional parameters
Role: To ensure the integrity and consistency of data
Two
PRIMARY KEY (PK) #标识该字段为该表的主键, can uniquely identify the record
FOREIGN KEY (FK) #标识该字段为该表的外键
Not NULL #标识该字段不能为空
UNIQUE KEY (UK) #标识该字段的值是唯一的
Auto_increment #标识该字段的值自动增长 (integer type, and primary key)
DEFAULT #为该字段设置默认值
Description
#1. Allow null, default NULL, can set not NULL, field is not allowed to be empty, must be assigned
#2. Whether a field has a default value, the default value is NULL, and if the record is inserted without assigning a value to the field, this field uses the default value
Sex enum (' Male ', ' female ') not null default ' male '

#必须为正值 (unsigned) does not allow null default is 20
Age Int. unsigned not NULL default 20
3. Whether it is a key
Primary KEY Primary Key
FOREIGN key foreign key
Index (Index,unique ...)
UNSIGNED #无符号
Zerofill #使用0填充
1.not null vs. default
Nullable, NULL indicates NULL, non-string
Not null-non-nullable
Null-Nullable

Default value, you can specify a default value when creating a column, and automatically add a default value when inserting data if it is not actively set

CREATE TABLE TB1 (
Nid int not null Defalut 2,
num int NOT NULL
);
2.unique
Called single-column unique in MySQL
First
CREATE TABLE Department (
ID int,
Name Char (TEN) Unique
);
INSERT INTO department values (1, ' it '), (2, ' sale ');
Second:
CREATE TABLE Department (
ID int,
Name Char (10),
Unique (ID),
Unique (name)
);
INSERT INTO department values (1, ' it '), (2, ' sale ');
Union unique:
Mysql> CREATE TABLE Services (
ID int,
IP char (15),
Port int,
Unique (ID),
Unique (Ip,port)
);
INSERT INTO services values
(1, ' 192,168,11,23 ', 80),
(2, ' 192,168,11,23 ', 81),
(3, ' 192,168,11,25 ', 80);

3.primary key NOT NULL + unique chemical reaction, equivalent to setting primary key for ID
Single-row key
Multi-column Master key (composite primary key)
A constraint is equivalent to a NOT null unique, and the value of the field is not null and unique:
The storage Engine is (INNODB) by default: For the InnoDB storage engine, a table must have a primary key.
(1) Single-column primary key
Create a t14 table, set the primary key for the ID field, and only the different records
CREATE TABLE T14 (
ID int PRIMARY KEY,
Name Char (16)
);
INSERT INTO t14 values
(1, ' Xiaoma '),
(2, ' Xiaohong ');
Error: INSERT into t14 values (2, ' wxxx ');
(2) Composite primary key
CREATE TABLE T16 (
IP char (15),
Port int,
Primary KEY (Ip,port)
);

INSERT INTO t16 values
(' 1.1.1.2 ', 80),
(' 1.1.1.2 ', 81);

4.auto_increment
Constraint: The field of the constraint is autogrow, and the constrained field must be constrained by key at the same time
Example:
CREATE TABLE Student (
ID int primary KEY auto_increment,
Name varchar (20),
Sex enum (' Male ', ' female ') default ' male '
);
Do not specify an ID
INSERT into student (name) values (' Old white '), (' Little white ')
Specify ID
INSERT into student values (4, ' ASB ', ' female ');
Insert a record that does not specify an ID again, and the last record continues to grow
mysql> INSERT into student (name) VALUES (' White ');
Delete Note:
For self-increment fields, after deleting with delete and inserting values, the field continues to grow as it was before it was deleted
Example: Delete:
Delete from student;
INSERT into student (name) VALUES (' YSB ');
Effect:
ID | name | sex |
+----+------+------+
| 9 | YSB | Male |
You should use TRUNCATE to clear the table, and delete the record, rather than deleting it, truncate is to empty the table and use it to delete the large table.
Truncate emptying the table
Truncate student
INSERT into student (name) VALUES (' Xiaobai ');
| ID | name | sex |
+----+---------+------+
| 1 | Xiaobai | Male |
Add:
View the words that are available at the beginning of Auto_inc
Show variables like ' auto_inc% ';
Step auto_increment_increment, default = 1
# start offset Auto_increment_offset, default is 1

Set the step to session settings, only valid in this connection
Set session auto_increment_increment=5;
#全局设置步长 are valid.
Set global auto_increment_increment=5;

# Set Start offset
Set global auto_increment_offset=3;
Attention:
If the value of Auto_increment_offset is greater than the value of auto_increment_increment, the value of Auto_increment_offset is ignored
After setting the starting offset and step, execute show variables like ' auto_inc% ' again;
The discovery is the same as before, you must exit first, and then login to be effective.

The clear table distinguishes between delete and truncate:
Delete from T1; #如果有自增id, the new data is still the same as the beginning of the last one before deletion.
TRUNCATE table T1; The data volume is large, the deletion speed is faster than the previous one, and starts directly from zero.
5.foreign Key
Scene:
The company has 3 departments, but there are 100 million of employees, that means the department this field needs to be repeated storage, the longer the department name, the more wasted.
Workaround:
We can definitely define a departmental table.
Employee Information sheet associated with this table, how to associate, that is, foreign key
One is the employee table, referred to as the EMP Table (association table, also from the table)
One is the department table, or the DEP table (the associated table, also called the Main table)
Code:
#1. Create a table when it is created, and then create the associated table
# first create the associated table (DEP table)
CREATE TABLE DEP (
ID int PRIMARY KEY,
Name varchar () is not NULL,
Descripe varchar () NOT NULL
);

#再创建关联表 (EMP table)
CREATE TABLE EMP (
ID int PRIMARY KEY,
Name varchar () is not NULL,
The age int is not NULL,
dep_id int,
Constraint FK_DEP foreign KEY (dep_id) references dep (ID)
);

#2. When inserting records, insert records into the associated table before inserting records into the associated table

INSERT INTO DEP values
(1, ' It ', ' IT Technology limited '),
(2, ' Sales department ', ' sales department '),
(3, ' Finance department ', ' spending too many departments ');

INSERT INTO EMP values
(1, ' Zhangsan ', 18, 1),
(2, ' Lisi ', 19, 1),
(3, ' Egon ', 20,2),
(4, ' Yuanhao ', 40, 3),
(5, ' Alex ', 18,2);
3. Delete a table
#按道理来说, a department in the department table was deleted, and the associated records for the employee table were deleted successively.
However, after you delete the records for the employee table, delete the current department without any problems
The above Delete table record operation is more cumbersome, according to the truth, cut off a department, the staff of the department will be cut off. In fact, there is a very important element in the construction of the table,
Call sync Delete, sync update
Note: In the association table, add
ON DELETE Cascade #同步删除
ON UPDATE Cascade #同步更新
Code: Copying code
CREATE TABLE EMP (
ID int PRIMARY KEY,
Name varchar () is not NULL,
The age int is not NULL,
dep_id int,
Constraint FK_DEP foreign KEY (dep_id) references dep (ID)
ON DELETE Cascade #同步删除
ON UPDATE Cascade #同步更新
);
#再去删被关联表 (DEP) records, and records in the associated table (EMP) are also deleted
Delete from DEP where id=3;
To change the records of the associated table (DEP), the records in the associated table (EMP) are changed
Update DEP set id=222 where id=2;

Python full stack development * MySQL data type * 180829