今までで一番充実したmysqlの学習ノート、ぜひ一度学習してみませんか？白人のみならず

データベース操作

-- View the current database
    SELECT DATABASE();
-- Show current time, user name, database version
    SELECT now(), user(), version();
-- Create a library
    CREATE DATABASE[ IF NOT EXISTS] Database name Database options
    -- Database options.
        CHARACTER SET charset_name
        COLLATE collation_name
-- View existing libraries
    SHOW DATABASES[ LIKE 'PATTERN']
-- View current library information
    SHOW CREATE DATABASE database name
-- Modify library option information
    ALTER DATABASE library name option information
-- Delete the library
    DROP DATABASE [IF EXISTS] database name
       -- Also delete the directory associated with the database and its contents

-- Create a table
    CREATE [TEMPORARY] TABLE[ IF NOT EXISTS] [library name.] TABLE NAME ( TABLE STRUCTURE DEFINITION ) [ TABLE OPTIONS]
        Each field must have a data type
        No comma after the last field
        TEMPORARY Temporary table, which disappears automatically at the end of the session
        For the definition of fields.
            Field name Data type [NOT NULL | NULL] [DEFAULT default_value] [AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY] [COMMENT 'string']
-- Table Options
    -- character set
        CHARSET = charset_name
        If the table is not set, the database character set is used
    -- storage engine
        ENGINE = engine_name
        Different data structures are used to manage data in the table, and the different structures will lead to different processing methods and provide different characteristic operations, etc.
        Common engines: InnoDB MyISAM Memory/Heap BDB Merge Example CSV MaxDB Archive
        Different engines use different ways to save the table structure and data
        MyISAM table file meaning: .frm table definition, .MYD table data, .MYI table index
        InnoDB table file meaning: .frm table definition, tablespace data and log files
        SHOW ENGINES -- shows the status information of the storage engine
        SHOW ENGINE engine name {LOGS|STATUS} -- shows the log or status information of the storage engine
    -- self-incrementing start number
        AUTO_INCREMENT = number of rows
    -- Data file directory
        DATA DIRECTORY = 'directory'
    -- Index file directory
        INDEX DIRECTORY = 'directory'
    -- table comments
        COMMENT = 'string'
    -- Partitioning options
        PARTITION BY ... (see manual for details)
-- View all tables
    SHOW TABLES[ LIKE 'pattern']
    SHOW TABLES FROM table name
-- View table organization
    SHOW CREATE TABLE table name (more detailed information)
    DESC table name / DESCRIBE table name / EXPLAIN table name / SHOW COLUMNS FROM table name [LIKE 'PATTERN']
    SHOW TABLE STATUS [FROM db_name] [LIKE 'pattern']
-- Modify the table
    -- options for modifying the table itself
        ALTER TABLE table name Table options
        eg: ALTER TABLE table name ENGINE=MYISAM;
    -- rename the table
        RENAME TABLE original table name TO new table name
        RENAME TABLE original table name TO library name. TABLE NAME (can move table to another database)
        -- RENAME can exchange two table names
    -- Modify the field structure of a table (13.1.2. ALTER TABLE syntax)
        ALTER TABLE table name operation name
        -- operation name
            ADD[ COLUMN] field definition -- add field
                AFTER field name -- means add after that field name
                FIRST -- means add in the first
            ADD PRIMARY KEY(field name) -- create primary key
            ADD UNIQUE [index name] (field name) -- creates a unique index
            ADD INDEX [index name] (field name) -- creates a general index
            DROP[ COLUMN] field name -- delete field
            MODIFY[ COLUMN] Field Name Field Attributes -- supports modification of field attributes, cannot modify field name (all original attributes need to be written as well)
            CHANGE[ COLUMN] original field name new field name field attribute -- supports modification of field name
            DROP PRIMARY KEY -- delete the primary key (you need to delete the AUTO_INCREMENT attribute before deleting the primary key)
            DROP INDEX index name -- delete the index
            DROP FOREIGN KEY foreign key -- remove foreign key
-- Delete a table
    DROP TABLE[ IF EXISTS] table name ...
-- Clear table data
    TRUNCATE [TABLE] table name
-- Copy table structure
    CREATE TABLE table name LIKE the name of the table to be copied
-- Duplicate table structure and data
    CREATE TABLE table name [AS] SELECT * FROM table name to be copied
-- Check the table for errors
    CHECK TABLE tbl_name [, tbl_name] ... [option] ...
-- Optimize the table
    OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ...
-- Repair the table
    REPAIR [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ... [QUICK] [EXTENDED] [USE_FRM]
-- Analyze the table
    ANALYZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ...


-- increment
    INSERT [INTO] table name [(list of fields)] VALUES (list of values)[, (list of values), ...]
        -- The list of fields can be omitted if the list of values to be inserted contains all fields and is in the same order.
        -- Multiple data rows can be inserted at the same time!
        REPLACE is exactly the same as INSERT and is interchangeable.
    INSERT [INTO] table name SET field name=value [, field name=value, ...]
-- CHECK
    SELECT list of fields FROM table name [ other clauses]
        -- can be multiple fields from multiple tables
        -- Other clauses can be left out
        -- The list of fields can be replaced by *, indicating all fields
-- Delete
    DELETE FROM table name[ delete conditional clause]
        Without the conditional clause, all will be deleted
-- CHANGE
    UPDATE table name SET field name=new value [, field name=new value] [update condition]


-- Encoding can be set for MySQL, database, tables, fields
-- Data encoding does not need to be the same as client encoding
SHOW VARIABLES LIKE 'character_set_%' -- see all character set encoding entries
    character_set_client The encoding used by the client when sending data to the server
    character_set_results The encoding used by the server to return the results to the client
    character_set_connection Connection level encoding
SET variable_name = variable_value
    SET character_set_client = gbk;
    SET character_set_results = gbk;
    SET character_set_connection = gbk;
SET NAMES GBK; -- equivalent to completing the above three settings
-- Proofreading Sets
    The checkset is used to sort
    SHOW CHARACTER SET [LIKE 'pattern']/SHOW CHARSET [LIKE 'pattern'] View all character sets
    SHOW COLLATION [LIKE 'pattern'] View all checksets
    CHARSET Character set encoding Set character set encoding
    COLLATE checkset encoding Set the checkset encoding



1. Numeric types

-- a. Integer ----------
    Type Byte Range (signed bits)
    tinyint 1 byte -128 ~ 127 unsigned bits: 0 ~ 255
    smallint 2 bytes -32768 ~ 32767
    mediumint 3 bytes -8388608 ~ 8388607
    int 4 bytes
    bigint 8 bytes
    int(M) M means the total number of bits
    - Default presence of signed bits, modified by unsigned property
    - Display width, if a number is not enough to define the number of bits set in the field, then the front is filled with zeros, modified by the zerofill property
        Example: int(5) inserts a number '123', which is filled in as '00123'
    - In the case of meeting the requirements, the smaller the better.
    - MySQL does not have boolean types, but integers 0 and 1. The boolean type is often represented by tinyint(1).

-- b. Floating-point type ----------
    Type Byte Range
    float(single precision) 4 bytes
    double(double precision) 8 bytes
    The floating-point type supports both the unsigned property for sign bits and the zerofill property for display width.
        Unlike integers, the zerofill property is filled with zeros before and after.
    When defining a floating-point type, the total number of bits and the number of decimal places must be specified.
        float(M, D) double(M, D)
        M is the total number of digits and D is the number of decimal digits.
        The size of M and D will determine the range of the floating point number. Unlike integers, which have a fixed range.
        M indicates both the total number of bits (excluding decimal points and plus and minus signs) and the display width (all display symbols are included).
        Scientific notation representation is supported.
        Floating-point numbers indicate approximate values.

-- c. Fixed-point numbers ----------
    decimal -- variable length
    decimal(M, D) M also indicates the total number of digits, D indicates the number of decimal places.
    Saves an exact value without data change, unlike the rounding of floating point numbers.
    Converts a floating point number to a string to save it. Every 9 digits are saved as 4 bytes.

2. String types

-- a. char, varchar ----------
    char fixed-length string, fast, but wastes space
    varchar variable-length string, slow, but space-saving
    M indicates the maximum length that can be stored, which is the number of characters, not bytes.
    Different encodings take up different amount of space.
    char, up to 255 characters, independent of encoding.
    varchar, up to 65535 characters, encoding related.
    A valid record cannot exceed 65535 bytes.
        utf8 is 21844 characters maximum, gbk is 32766 characters maximum, latin1 is 65532 characters maximum
    varchar is variable length and needs to use storage space to save the length of varchar. If the data is less than 255 bytes, one byte is used to save the length, and vice versa two bytes are needed to save it.
    The maximum effective length of a varchar is determined by the maximum row size and the character set used.
    The maximum effective length is 65532 bytes, because when storing a string in varchar, the first byte is empty, there is no data, and then two bytes are needed to store the length of the string, so the effective length is 64432-1-2=65532 bytes.
    Example: If a table is defined as CREATE TABLE tb(c1 int, c2 char(30), c3 varchar(N)) charset=utf8; Ask what is the maximum value of N? A: (65535-1-2-4-30*3)/3

-- b. blob, text ----------
    blob binary string (byte string)
        tinyblob, blob, mediumblob, longblob
    text Non-binary strings (character strings)
        tinytext, text, mediumtext, longtext
    text is defined without defining the length, and the total length is not calculated.
    The text type cannot be given a default value when defined

-- c. binary, varbinary ----------
    Similar to char and varchar, used to hold binary strings, i.e. to hold byte strings instead of character strings.
    char, varchar, text correspond to binary, varbinary, blob.

3. date and time types
    Generally use integer type to save timestamp, because PHP can format timestamp easily.
    datetime 8 bytes date and time 1000-01-01 00:00:00 to 9999-12-31 23:59:59
    date 3 bytes date 1000-01-01 to 9999-12-31
    timestamp 4 bytes timestamp 19700101000000 to 2038-01-19 03:14:07
    time 3 bytes time -838:59:59 to 838:59:59
    year 1 byte year 1901 - 2155
datetime YYYY-MM-DD hh:mm:ss
timestamp YY-MM-DD hh:mm:ss
            YYYYMMDDhhmmss
            YYYYMMDDhhmmss
            YYYYMMDDhhmmss
            YYMMDDhhmmss
date YYYY-MM-DD
            YY-MM-DD
            YYYYMMDD
            YYMMDD
            YYYYMMDD
            YYMMDD
time hh:mm:ss
            hhmmss
            hhmmss
year YYYY
            YY
            YYYY
            YY

4. Enumerations and collections

-- enum(enum) ----------
enum(val1, val2, val3...)
    Make a single selection among known values. The maximum number is 65535.
    Enum values are saved as 2-byte integers (smallint). Each enumerated value is incremented from 1 to 1 in the order in which it is stored.
    It is represented as a string type, but stored as an integer type.
    The index of a NULL value is NULL.
    The index value of an empty string error value is 0.
    
-- set (set) ----------
set(val1, val2, val3...)
    create table tab ( gender set('male', 'female', 'none') );
    insert into tab values ('male, female');
    There can be up to 64 different members. Stored as bigint, 8 bytes in total. Takes the form of bitwise operations.
    Trailing spaces in SET member values are automatically removed when the table is created.


-- PHP perspective
1. functionally satisfying
2. storage space as small as possible, more efficient processing
3. consider compatibility issues

-- IP storage ----------
1. only need to store, available string
2. if you need to calculate, find, etc., store as 4 bytes unsigned int, i.e. unsigned
    1) PHP function conversions
        ip2long can be converted to integer, but will have problems carrying symbols. It needs to be formatted as an unsigned integer.
        Use the sprintf function to format a string
        sprintf("%u", ip2long('192.168.3.134'));
        Then use long2ip to convert the integer back to an IP string
    2) MySQL function conversion (unsigned integer, UNSIGNED)
        INET_ATON('127.0.0.1') to convert IP to integer
        INET_NTOA(2130706433) convert integer to IP


1. PRIMARY Primary Key
    - A field that uniquely identifies a record and can be used as a primary key.
    - A table can have only one primary key.
    - Primary keys are unique.
    - Fields are declared with the primary key identifier.
        It can also be declared after the list of fields
            Example: create table tab ( id int, stu varchar(10), primary key (id));
    - The value of the primary key field cannot be null.
    - The primary key can be composed of multiple fields together. In this case, the method declared after the list of fields is required.
        Example: create table tab ( id int, stu varchar(10), age int, primary key (stu, age)). 2;
2. UNIQUE Unique Index (Unique Constraint)
    Makes it impossible to duplicate the value of a field as well.
3. NULL Constraint
    null is not a data type, it is an attribute of a column.
    Indicates whether the current column can be null, indicating that there is nothing.
    null, is allowed to be null. Default.
    not null, not allowed to be null.
    insert into tab values (null, 'val');
        -- This means that the value of the first field is set to null, depending on whether the field is allowed to be null or not.
4. DEFAULT Default Value Property
    The default value of the current field.
    insert into tab values (default, 'val'); -- This means force the default value.
    create table tab ( add_time timestamp default current_timestamp );
        -- Indicates that the current time timestamp is set to the default value.
        current_date, current_time
AUTO_INCREMENT Auto-growth constraint
    Autogrowth must be an index (primary key or unique)
    Only one field can exist for autogrowth.
    Default is 1 to start autogrowth. Can be set via table attribute auto_increment = x, or alter table tbl auto_increment = x;
6. COMMENT Comments
    Example: create table tab ( id int ) comment 'comment content'. 7;
7. FOREIGN KEY foreign key constraint
    Used to restrict the data integrity of master and slave tables.
    alter table t1 add constraint `t1_t2_fk` foreign key (t1_id) references t2(id);
        -- Associate the t1_id foreign key of table t1 to the id field of table t2.
        -- Each foreign key has a name, which can be specified by constraint
    The table where the foreign key exists is called the slave table (child table), and the table pointed to by the foreign key is called the master table (parent table).
    The main purpose is to control the data stored in the foreign key table (slave table).
    In MySQL, you can use foreign key constraints on InnoDB engine.
    Syntax.
    foreign key (foreign key field) references master table name (associated field) [action on delete of master table record] [action on update of master table record]
    At this point, you need to detect that a foreign key of a slave table needs to be constrained to a value that already exists in the master table. The foreign key can be set to null if there is no association. provided that the foreign key column, which is not not null.
    You can not specify the action when the master table record is changed or updated, then the operation of the master table is denied at this time.
    If on update or on delete is specified: when deleting or updating, there are several actions to choose from as follows.
    1. cascade, a cascade operation. The master table data is updated (primary key value update) and the slave table is updated (foreign key value update). The master table record is deleted, and the related records of the slave table are also deleted. 2.
    2. set null, set to null. the master table data is updated (primary key value updated) and the foreign key of the slave table is set to null. the master table record is deleted and the foreign key of the related record of the slave table is set to null. but note that the foreign key column is required to have no not null attribute constraint.
    3. restrict, which rejects parent table deletions and updates.
    Note that foreign keys are only supported by the InnoDB storage engine. Other engines are not supported.


    -- Normal Format, NF
        - Each table holds information about one entity
        - Each has an ID field as the primary key
        - ID primary key + proto table
    -- 1NF, First Paradigm
        Fields cannot be subdivided, so the first paradigm is satisfied.
    -- 2NF, second paradigm
        Partial dependencies cannot occur provided the first paradigm is satisfied.
        Partial dependencies can be avoided by eliminating conforming primary keys. Add single column key.
    -- 3NF, third paradigm
        Pass dependency cannot occur provided that the second paradigm is satisfied.
        A field depends on a primary key and there are other fields that depend on that field. This is a pass dependency.
        Implementing data from one entity information in one table.


SELECT [ALL|DISTINCT] select_expr FROM -> WHERE -> GROUP BY [total function] -> HAVING -> ORDER BY -> LIMIT
a. select_expr
    -- All fields can be represented by *.
        select * from tb;
    -- can use expressions (calculation formula, function call, field is also an expression)
        select stu, 29+25, now() from tb;
    -- Can use aliases for each column. Suitable for simplifying column identification and avoiding duplication of multiple column identifiers.
        - Use the as keyword, or omit as.
        select stu+10 as add10 from tb;
b. The FROM clause
    Used to identify the source of a query.
    -- Aliases can be given to tables. Use the as keyword.
        SELECT * FROM tb1 AS tt, tb2 AS bb;
    -- The from clause can be followed by multiple tables at the same time.
        -- Multiple tables will be stacked together horizontally and the data will form a Cartesian product.
        SELECT * FROM tb1, tb2;
    -- Prompt the optimizer for index selection
        USE INDEX, IGNORE INDEX, FORCE INDEX
        SELECT * FROM table1 USE INDEX (key1,key2) WHERE key1=1 AND key2=2 AND key3=3;
        SELECT * FROM table1 IGNORE INDEX (key3) WHERE key1=1 AND key2=2 AND key3=3;
c. WHERE clause
    -- Filter from the data source obtained by FROM.
    -- Integer 1 means true, 0 means false.
    -- Expressions consist of operators and operators.
        -- Operators: variables (fields), values, function return values
        -- Operators.
            =, <=>, <>, ! =, <=, <, >=, >, ! , &&, ||,
            in (not) null, (not) like, (not) in, (not) between and, is (not), and, or, not, xor
            is/is not plus ture/false/unknown to check the truth or falsity of a value
            <=> has the same function as <>, <=> can be used for null comparisons
d. GROUP BY clause, grouping clause
    GROUP BY field/alternate [Sort by]
    Grouping will be sorted after grouping. Ascending order: ASC, Descending order: DESC
    The following [total functions] need to be used with GROUP BY.
    count returns the number of different non-NULL values count(*), count(field)
    sum sum
    max to find the maximum value
    min for the minimum value
    avg to find the average
    group_concat returns the result of a string with non-NULL values from a group concatenation. String concatenation within a group.
e. HAVING clause, conditional clause
    Same function and usage as where, but executed at different times.
    where is executed at the beginning to detect the data and filter the original data.
    having performs another filter on the filtered result.
    The having field must be queried, and the where field must be present in the data table.
    where cannot use aliases of fields, having can. This is because the WHERE code may not have determined the column value when it was executed.
    where cannot use the total function. Usually you need to use the total function before you can use having
    The SQL standard requires that HAVING must refer to a column in the GROUP BY clause or be used in the total function.
f. ORDER BY clause, sort clause
    order by sorted field/alternate sort by [,sorted field/alternate sort by]...
    Ascending order: ASC, Descending order: DESC
    Supports sorting of multiple fields.
g. LIMIT clause, limit number of results clause
    Limits the number of processed results only. Consider the processed results as a collection of records in the order they appear, with indexes starting from 0.
    limit Start position, get number of rows
    Omit the first argument to start from index 0. limit Gets the number of rows
h. DISTINCT, ALL options
    distinct removes duplicate rows
    Default is all, all rows


 Combines the results of multiple select queries into a single result collection.
    SELECT ... UNION [ALL|DISTINCT] SELECT ...
    Default DISTINCT method, i.e. all rows returned are unique
    It is recommended that each SELECT query be wrapped in parentheses.
    ORDER BY sorting needs to be combined with LIMIT.
    Need the same number of fields for each SELECT query.
    The list of fields (number, type) should be the same for each SELECT query, as the field names in the results are based on the first SELECT statement.


    -- Subqueries need to be wrapped in parentheses.
-- from type
    after from requires a table and must give the subquery results an alias.
    - Simplifies the conditions within each query.
    - The from type requires the result to be a temporary table that can be used to release the lock on the original table.
    - Subquery returns a table, table-type subquery.
    select * from (select * from tb where id>0) as subfrom where id>1;
-- where type
    - Subquery returns a value, scalar subquery.
    - There is no need to alias the subquery.
    - The table within the where subquery cannot be used directly to update.
    select * from tb where money = (select max(money) from tb);
    -- column subquery
        If the result of the subquery returns a column.
        Completing queries with in or not in
        exists and not exists conditions
            If the subquery returns data, it returns 1 or 0. Commonly used to determine conditions.
            select column1 from t1 where exists (select * from t2);
    -- Row Subquery
        The query condition is a row.
        select * from t1 where (id, gender) in (select id, gender from t2);
        Row constructor: (col1, col2, ...) or ROW(col1, col2, ...)
        The row constructors are usually used to compare with subqueries that return two or more columns.
    -- Special Operators
    ! = all() is equivalent to not in
    = some() is equivalent to in. any is an alias for some
    ! = some() is not equivalent to not in and is not equal to one of them.
    all, some can be used in conjunction with other operators.

    -- Join fields from multiple tables, you can specify join conditions.
-- inner join
    - The default is inner join, you can omit inner.
    - The join can only be sent if the data exists. That is, there must be no empty rows in the result of the join.
    on indicates the join condition. The conditional expression is similar to where. The condition can also be omitted (indicating that the condition is always true)
    You can also use where to express a join condition.
    There is also using, but the field names must be the same. using(field name)
    -- cross join
        i.e., an unconditional inner join.
        select * from tb1 cross join tb2;
-- outer join (outer join)
    - If the data does not exist, it will also appear in the join result.
    -- left outer join left join
        If the data does not exist, the left table record appears, and the right table is filled with null
    -- right outer join right join
        If the data does not exist, the right table record appears and the left table is filled with null
--natural join
    Automatically determines the join conditions to complete the join.
    It is equivalent to omitting using, and will automatically find the same field name.
    natural join
    natural left join
    natural right join

select info.id, info.name, info.stu_num, extra_info.hobby, extra_info.sex from info, extra_info where info.stu_num = extra_info.stu_id;



select * into outfile file address [control format] from table name; -- export table data

load data [local] infile file address [replace|ignore] into table table name [control format]; -- import data
    The default delimiter of the generated data is tab
    local is not specified, then the data file must be on the server
    replace and ignore keywords control the handling of duplicates of existing unique key records
-- controls formatting
fields controls the field format
default: fields terminated by '\t' enclosed by '' escaped by '\\'
    terminated by 'string' -- terminated
    enclosed by 'char' -- encapsulated
    escaped by 'char' -- escaped
    -- Example.
        SELECT a,b,a+b INTO OUTFILE '/tmp/result.text'
        FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
        LINES TERMINATED BY '\n'
        FROM test_table;
lines control row format
Default: lines terminated by '\n'
    terminated by 'string' -- terminated

The data obtained by the select statement can be inserted using INSERT.
The column specification can be omitted, requiring the values () brackets, which provide the values for all fields that appear in column order.
    Alternatively, use the set syntax.
    INSERT INTO tbl_name SET field=value,... ;.
Multiple values can be used at once, in the form of (), (), ();.
    INSERT INTO tbl_name VALUES (), (), ();
You can use expressions when column values are specified.
    INSERT INTO tbl_name VALUES (field_value, 10+10, now());
A special value DEFAULT can be used to indicate that the default value is used for the column.
    INSERT INTO tbl_name VALUES (field_value, DEFAULT);
The result of a query can be passed as the value to be inserted.
    INSERT INTO tbl_name SELECT ... ;
You can specify to update the information of other non-primary key columns in case of primary key (or unique index) conflict for the inserted value.
    INSERT INTO tbl_name VALUES/SET/SELECT ON DUPLICATE KEY UPDATE field=value, ...;

DELETE FROM tbl_name [WHERE where_definition] [ORDER BY ...] [LIMIT row_count]
Delete by condition. where
Specifies the maximum number of rows to be deleted. limit
Can be deleted by sorting condition. order by + limit
Multiple table deletion is supported, using similar join syntax.
delete from Need to delete data multiple tables 1, 2 using table join operation condition.

TRUNCATE [TABLE] tbl_name
Clear data
Delete a rebuilt table
Differences.
1, truncate is to delete the table and create it again, delete is to delete item by item
2, truncate resets the value of auto_increment. And delete does not
3, truncate does not know how many entries were deleted, while delete knows.
4, when used for tables with partitions, truncate will keep the partitions

Backups, which save the structure of the data and the data in the tables.
This is done using the mysqldump command.
-- export
mysqldump [options] db_name [tables]
mysqldump [options] ---database DB1 [DB2 DB3...]
mysqldump [options] --all--database
1. export a table
　　mysqldump -u username -p password library name table name > file name (D:/a.sql)
2. export multiple tables
　　mysqldump -u username -p password library name table1 table2 table3 > filename(D:/a.sql)
3. export all tables
　　mysqldump -u username -p password library name > filename(D:/a.sql)
4. export a library
　　mysqldump -u username -p password --lock-all-tables --database library name > filename(D:/a.sql)
You can -w carry WHERE conditions
--import
1. With mysql logged in.
　　source backup file
2. without logging in
　　mysql -u username -p password library name < backup file

What is a view.
    A view is a virtual table whose contents are defined by a query. Like a real table, a view contains a set of columns and rows of data with names. However, the view does not exist as a stored set of data values in the database. The row and column data come from the tables referenced by the query that freely defines the view and are dynamically generated when the view is referenced.
    A view has a table structure file, but no data file exists.
    For the underlying tables referenced in it, the view acts like a filter. The filter that defines a view can come from one or more tables of the current or other database, or from other views. There are no restrictions on querying through views, and few restrictions on modifying data through them.
    A view is a sql statement for a query stored in the database, it is used for two main reasons: for security reasons, a view can hide some data, e.g.: social security fund table, a view can be used to show only the name, address and not the social security number and salary number, etc. Another reason is that it can make complex queries easy to understand and use.
-- Creating a view
CREATE [OR REPLACE] [ALGORITHM = {
UNDEFINED | MERGE | TEMPTABLE}] VIEW view_name [(column_list)] AS select_statement
    - The view name must be unique and cannot have the same name as a table.
    - The view can use the column names queried by the select statement, or you can specify the corresponding column names yourself.
    - You can specify the algorithm for view execution, which is specified by ALGORITHM.
    - The number of column_list, if it exists, must be equal to the number of columns retrieved by the SELECT statement
-- View structure
    SHOW CREATE VIEW view_name
-- Delete the view
    - After deleting a view, the data still exists.
    - Multiple views can be deleted at the same time.
    DROP VIEW [IF EXISTS] view_name ...
-- Modify the view structure
    - Generally, the view is not modified because not all updates to the view are mapped to the table.
    ALTER VIEW view_name [(column_list)] AS select_statement
-- What views do
    1. simplify business logic
    2. hide the real table structure from the client
-- View Algorithm (ALGORITHM)
    MERGE merge
        Merge the query statement of a view with an external query before execution!
    TEMPTABLE temporary table
        Form a temporary table after the execution of the view, and then do the outer query!
    UNDEFINED Undefined (default), meaning MySQL autonomously goes to the appropriate algorithm.

A transaction is a logical set of operations where the individual units that make up the set either all succeed or all fail.
    - It supports the collective success or collective undo of consecutive SQL.
    - Transactions are a feature of databases in terms of late self-study of data.
    - It needs to be done using InnoDB or BDB storage engine, which has support for auto-commit feature.
    - InnoDB is known as transaction-safe engine.
-- transaction open
    START TRANSACTION; or BEGIN;
    When a transaction is opened, all SQL statements that are executed are recognized as SQL statements within the current transaction.
-- Transaction Commit
    COMMIT;
-- transaction rollback
    ROLLBACK;
    If part of the operation goes wrong, it is mapped to before the transaction is opened.
-- Characteristics of Transactions
    1. Atomicity
        A transaction is an indivisible unit of work in which either all or none of the operations in the transaction occur.
    2. Consistency
        The integrity of data before and after a transaction must be consistent.
        - External data is consistent at the beginning and end of the transaction
        - Operations are continuous throughout the transaction
    3. Isolation
        When multiple users access the database concurrently, one user's transaction cannot be interfered with by other users' things, and the data between multiple concurrent transactions should be isolated from each other.
    4. Durability (Durability)
        Once a transaction is committed, the changes it makes to the data in the database are permanent.
-- Transaction Implementation
    1. requires a table type that is supported by the transaction
    2. open a transaction before executing a set of related operations
    3. when the whole set of operations is completed and all are successful, it will be committed; if there is a failure and you choose to roll back, it will go back to the backup point where the transaction started.
-- Principle of transaction
    This is done by using InnoDB's autocommit feature.
    After a normal MySQL statement is executed, the current data commit operation is visible to other clients.
    A transaction temporarily disables the "autocommit" mechanism and requires commit to commit persistent data operations.
-- Note
    1. Data Definition Language (DDL) statements cannot be rolled back, such as statements that create or cancel a database, and statements that create, cancel, or change a table or stored subroutine. 2.
    2. transactions cannot be nested
-- Save Points
    SAVEPOINT Savepoint name -- set a transaction savepoint
    ROLLBACK TO SAVEPOINT savepoint name -- roll back to savepoint
    RELEASE SAVEPOINT savepoint name -- Delete a savepoint
-- InnoDB autocommit feature setting
    SET autocommit = 0|1; 0 means turn off autocommit, 1 means turn on autocommit.
    - If it is turned off, the result of that common operation is not visible to other clients, and it needs commit to persist the data operation.
    - You can also turn off auto-commit to turn on the transaction. But unlike START TRANSACTION, the
        SET autocommit is to permanently change the server's settings until the next time that setting is modified again. (for the current connection)
        START TRANSACTION records the state before it is opened, and once the transaction is committed or rolled back it needs to be opened again. (for the current transaction)


Table locking is only used to prevent other clients from improperly reading and writing
MyISAM supports table locks, InnoDB supports row locks
-- Locking
    LOCK TABLES tbl_name [AS alias]
-- unlock
    UNLOCK TABLES

Triggers are named database objects associated with a table that are activated when a specific event occurs for that table
    Listens for: record additions, modifications, deletions.
-- Creating a trigger
CREATE TRIGGER trigger_name trigger_time trigger_event ON tbl_name FOR EACH ROW trigger_stmt
    Parameters.
    trigger_time is the action time of the trigger procedure. It can be before or after to indicate whether the trigger is triggered before or after the statement that activates it.
    trigger_event specifies the type of statement that activates the trigger
        INSERT: activates the trigger when a new row is inserted into the table
        UPDATE: activates the trigger when a row is changed
        DELETE: activates the trigger when a row is deleted from the table
    tbl_name: the table to listen to, must be a permanent table, cannot associate the trigger with a TEMPORARY table or view.
    trigger_stmt: the statement to be executed when the trigger is activated. To execute multiple statements, you can use the BEGIN.... ...END compound statement structure
-- Delete
DROP TRIGGER [schema_name.]trigger_name
You can use old and new instead of old and new data
    Update operation, old before update, new after update.
    Delete operation, only old.
    Add operation, only new.
-- Note
    1. For a given table with the same trigger action time and event, there cannot be two triggers.


-- Character concatenation functions
concat(str1,str2,...])
concat_ws(separator,str1,str2,...)

-- branching statements
if condition then
    Execute the statement
elseif condition then
    Execute the statement
else
    Execute the statement
end if;

-- Modify the outermost statement terminator
delimiter Custom Ending Symbols
    SQL statement
Custom end symbols
delimiter ; -- Modify back to the original semicolon

-- Wrap the statement block
begin
    statement block
end

-- Special execution
1. The program is triggered whenever a record is added. 2.
2. Insert into on duplicate key update syntax triggers.
    If there are no duplicate rows, it will trigger before insert, after insert;
    If there is a duplicate record and update, it will trigger before insert, before update, after update;
    If there is a duplicate record but no update occurs, it triggers before insert, before update
Replace syntax Execute before insert, before delete, after delete, after insert if there are rows


--// Local variables ----------
-- Variable declaration
    declare var_name[,...] type [default value]
    This statement is used to declare local variables. To give the variable a default value, include a default clause. The value can be specified as an expression and does not need to be a constant. If there is no default clause, the initial value is null.
-- assigning values
    Use the set and select into statements to assign values to variables.
    - Note: It is possible to use global variables (user-defined variables) within functions


--// Global variable ----------
-- Defining, Assigning
The set statement can define and assign values to variables.
set @var = value;
You can also use the select into statement to initialize and assign values to variables. This requires the select statement to return only one row, but it can be multiple fields, which means assigning values to multiple variables at the same time, and the number of variables needs to match the number of columns in the query.
It is also possible to think of the assignment statement as an expression that is executed by select. In this case, to avoid = being treated as a relational operator, use := instead. (The set statement can use both = and :=).
select @var:=20;
select @v1:=id, @v2=name from t1 limit 1;
select * from tbl_name where @var:=30;
select into can assign the data obtained by querying the table to a variable.
    --| select max(height) into @max_height from tb;
-- Custom variable names
To avoid conflicts between user-defined variables and system identifiers (usually field names) in select statements, user-defined variables use @ as the start symbol before the variable name.
@var=10;
    - Variables are defined and are valid for the entire session cycle (login to logout)



--// control structure ----------
-- if statements
if search_condition then
    statement_list   
[elseif search_condition then
    statement_list]
...
[else
    statement_list]
end if;
-- case statement
CASE value WHEN [compare-value] THEN result
[WHEN [compare-value] THEN result ...]
[ELSE result]
END
-- while loop
[begin_label:] while search_condition do
    statement_list
end while [end_label];
- If you need to terminate the while loop early within the loop, you need to use labels; the labels need to appear in pairs.
    -- Exiting a loop
        Quit the whole loop leave
        exit current loop iterate
        Determine which loop to exit by the exit label



--// Built-in function ----------
-- Numeric functions
abs(x) -- absolute value abs(-10.9) = 10
format(x, d) -- formatted thousandths format(1234567.456, 2) = 1,234,567.46
ceil(x) -- rounding upwards ceil(10.1) = 11
floor(x) -- round down floor (10.1) = 10
round(x) -- rounding to the nearest integer
mod(m, n) -- m%n m mod n for remainder 10%3=1
pi() -- get the circumference of a circle
pow(m, n) -- m^n
sqrt(x) -- square root of arithmetic
rand() -- random number
truncate(x, d) -- truncate d decimal places
-- time and date functions
now(), current_timestamp(); -- current date and time
current_date(); -- current date
current_time(); -- current time
date('yyyy-mm-dd hh:ii:ss'); -- get the date part
time('yyyy-mm-dd hh:ii:ss'); -- get the time part
date_format('yyyy-mm-dd hh:ii:ss', '%d %y %a %d %m %b %j'); -- format the time
unix_timestamp(); -- get unix timestamp
from_unixtime(); -- get time from timestamp
-- string functions
length(string) -- length of string, in bytes
char_length(string) -- the number of characters in the string
substring(str, position [,length]) -- start from position of str, take length of characters
replace(str ,search_str ,replace_str) -- replace search_str with replace_str in str
instr(string ,substring) -- return the position of the first occurrence of substring in string
concat(string [,...])   -- concatenate strings
charset(str) -- return the character set of the string
lcase(string) -- convert to lowercase
left(string, length) -- take the length characters from the left side of string2
load_file(file_name) -- read the content from the file
locate(substring, string [,start_position]) -- same as instr, but you can specify the start position
lpad(string, length, pad) -- repeat with pad at the beginning of the string until the length of the string is length
ltrim(string) -- remove leading spaces
repeat(string, count) -- repeat count times
rpad(string, length, pad) -- add pads after str until length is length
rtrim(string) -- remove the space at the end
strcmp(string1 ,string2) -- compare the size of two strings character by character
-- flow function
case when [condition] then result [when [condition] then result ...] [else result] end Multi-branch
if(expr1,expr2,expr3) Double branching.
-- aggregation functions
count()
sum();
max();
min();
avg();
group_concat()
-- other common functions
md5();
default();

--// Stored functions, custom functions ----------
-- New
    CREATE FUNCTION function_name (parameter list) RETURNS return value type
        function_body
    - Function name, should be a legal identifier and should not conflict with existing keywords.
    - A function should belong to a database, you can use the form db_name.funciton_name to execute the database to which the current function belongs, otherwise the current database.
    - The parameter section, consisting of "parameter name" and "parameter type". Multiple parameters are separated by commas.
    - The function body consists of multiple available mysql statements, flow control, variable declarations, and other statements.
    - Multiple statements should be included using begin... . end statement blocks.
    - There must be a return value statement.
-- delete
    DROP FUNCTION [IF EXISTS] function_name;
-- View
    SHOW FUNCTION STATUS LIKE 'partten'
    SHOW CREATE FUNCTION function_name;
-- modify
    ALTER FUNCTION function_name function options

--// Stored procedures, custom functions ----------
-- Definition
Stored procedure is a piece of code (procedure) that is stored in the database consisting of sql.
A stored procedure is usually used to complete a piece of business logic, such as enrollment, class fee payment, order entry, etc.
And a function is usually focused with a certain function that is considered as a service to other programs and needs to be called in other statements before the function can be called, while a stored procedure cannot be called by others and is executed by itself Executed by call.
-- Create
CREATE PROCEDURE sp_name (list of parameters)
    Procedure Body
Parameter list: different from the function's parameter list, you need to specify the parameter type
IN for input type
OUT, the output type
INOUT, a mixed type
Note that there is no return value.


/* Stored procedures */ ------------------
A procedure is a collection of executable code. It is more oriented towards business logic than functions.
Called: CALL procedure name
-- Note
- No return value.
- Can only be called alone, not interspersed with other statements
-- Parameters
IN|OUT|INOUT Parameter name Data type
IN input: During the call, the data is input to the parameter inside the procedure body
OUT Output: During the call, the result of the procedure body processing is returned to the client
INOUT input and output: both input and output
-- Syntax
CREATE PROCEDURE procedure name (list of parameters)
BEGIN
    PROCEDURE BODY
END


-- root password reset
1. stop the MySQL service
2. [Linux] /usr/local/mysql/bin/safe_mysqld --skip-grant-tables &
    [Windows] mysqld --skip-grant-tables
3. use mysql. 4;
4. UPDATE `user` SET PASSWORD=PASSWORD("password") WHERE `user` = "root";
5. FLUSH PRIVILEGES;
User information table: mysql.user
-- Refresh Permissions
FLUSH PRIVILEGES;
-- add user
CREATE USER username IDENTIFIED BY [PASSWORD] password (string)
    - Must have global CREATE USER privileges for mysql database or have INSERT privileges.
    - You can only create users, not grant permissions.
    - User name, note the quotes: e.g. 'user_name'@'192.168.1.1'
    - Password also needs to be quoted, and plain numeric password also needs to be quoted
    - To specify the password in plain text, the PASSWORD keyword needs to be ignored. To specify the password as a mixed value returned by the PASSWORD() function, the keyword PASSWORD needs to be included
-- Rename the user
RENAME USER old_user TO new_user
-- Set the password
SET PASSWORD = PASSWORD('password') -- set the password for the current user
SET PASSWORD FOR username = PASSWORD('password') -- set password for specified user
-- Delete the user
DROP USER user name
-- assign permissions/add users
GRANT privilege list ON table name TO username [IDENTIFIED BY [PASSWORD] 'password']
    - all privileges means all privileges
    - *. * indicates all tables in all libraries
    - library name. Table name indicates a table under a library
    GRANT ALL PRIVILEGES ON `pms`. * TO 'pms'@'%' IDENTIFIED BY 'pms0817';
-- View Permissions
SHOW GRANTS FOR username
    -- View current user permissions
    SHOW GRANTS; or SHOW GRANTS FOR CURRENT_USER; or SHOW GRANTS FOR CURRENT_USER();
-- Revoke permissions
REVOKE permission list ON table name FROM user name
REVOKE ALL PRIVILEGES, GRANT OPTION FROM username -- revoke all permissions
-- Permission Hierarchy
-- To use GRANT or REVOKE, you must have the GRANT OPTION permission and you must use it for the permission you are granting or revoking.
Global Hierarchy: Global privileges apply to all databases in a given server, mysql.user
    GRANT ALL ON *. * and REVOKE ALL ON *. * only grant and revoke global privileges.
Database level: database privileges apply to all targets in a given database, mysql.db, mysql.host
    GRANT ALL ON db_name.* and REVOKE ALL ON db_name.* only grant and revoke database privileges.
Table Level: Table privileges apply to all columns in a given table. mysql.talbes_priv
    GRANT ALL ON db_name.tbl_name and REVOKE ALL ON db_name.tbl_name grant and revoke table privileges only.
Column Hierarchy: Column privileges apply to a single column in a given table, mysql.columns_priv
    When using REVOKE, you must specify the same column as the authorized column.
-- Permissions list
ALL [PRIVILEGES] -- sets all simple permissions except GRANT OPTION
ALTER -- allows the use of ALTER TABLE
ALTER ROUTINE -- changes or cancels stored subroutines
CREATE -- allows use of CREATE TABLE
CREATE ROUTINE -- creates stored subroutines
CREATE TEMPORARY TABLES -- allows the use of CREATE TEMPORARY TABLE
CREATE USER -- allows CREATE USER, DROP USER, RENAME USER and REVOKE ALL PRIVILEGES.
CREATE VIEW -- CREATE VIEW is allowed.
DELETE -- allows the use of DELETE
DROP -- allows the use of DROP TABLE
EXECUTE -- allows the user to run stored subroutines
FILE -- allows the use of SELECT.... ...INTO OUTFILE and LOAD DATA INFILE
INDEX -- allows the use of CREATE INDEX and DROP INDEX
INSERT -- INSERT is allowed
LOCK TABLES -- allows the use of LOCK TABLES for tables for which you have SELECT privileges
PROCESS -- allows SHOW FULL PROCESSLIST
REFERENCES -- not implemented
RELOAD -- FLUSH is allowed
REPLICATION CLIENT -- allows users to ask for the address of a slave or master server
REPLICATION SLAVE -- used for replicated slave servers (reads binary log events from the master server)
SELECT -- allows SELECT to be used
SHOW DATABASES -- shows all databases
SHOW VIEW -- allows use of SHOW CREATE VIEW
SHUTDOWN -- allow mysqladmin shutdown
SUPER -- allows use of CHANGE MASTER, KILL, PURGE MASTER LOGS and SET GLOBAL statements, mysqladmin debug command; allows you to connect (once) even if max_connections has been reached.
UPDATE -- allows to use UPDATE
USAGE -- synonym for "no privileges"
GRANT OPTION -- allow to grant privileges

-- Analyze and store the keyword distribution of a table
ANALYZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE table name ...
-- Check one or more tables for errors
CHECK TABLE tbl_name [, tbl_name] ... [option] ...
option = {
QUICK | FAST | MEDIUM | EXTENDED | CHANGED}
-- tidy up fragments of data files
OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ...

1. backquotes (`) can be used to wrap identifiers (library names, table names, field names, indexes, aliases) to avoid renaming them with keywords! Chinese can also be used as an identifier!
2. each library directory exists an option file db.opt to save the current database.
3. Comments.
    Single line comment # Comment content
    Multi-line comments /* comment content */
    Single line comment -- comment content (standard SQL comment style, requires a double dash followed by a space character (space, TAB, newline, etc.))
4. pattern wildcard.
    _ any single character
    % any multiple characters, even zero characters
    Single quotes need to be escaped \'
5. The statement terminator within the CMD command line can be ";", "\G", "\g", which only affects the display result. Other places still end with semicolon. delimiter can modify the statement terminator of current conversation.
6. SQL is case-insensitive
7. Clear existing statements: \c

テーブル関連アクション

-- Create a table
    CREATE [TEMPORARY] TABLE[ IF NOT EXISTS] [library name.] TABLE NAME ( TABLE STRUCTURE DEFINITION ) [ TABLE OPTIONS]
        Each field must have a data type
        No comma after the last field
        TEMPORARY Temporary table, which disappears automatically at the end of the session
        For the definition of fields.
            Field name Data type [NOT NULL | NULL] [DEFAULT default_value] [AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY] [COMMENT 'string']
-- Table Options
    -- character set
        CHARSET = charset_name
        If the table is not set, the database character set is used
    -- storage engine
        ENGINE = engine_name
        Different data structures are used to manage data in the table, and the different structures will lead to different processing methods and provide different characteristic operations, etc.
        Common engines: InnoDB MyISAM Memory/Heap BDB Merge Example CSV MaxDB Archive
        Different engines use different ways to save the table structure and data
        MyISAM table file meaning: .frm table definition, .MYD table data, .MYI table index
        InnoDB table file meaning: .frm table definition, tablespace data and log files
        SHOW ENGINES -- shows the status information of the storage engine
        SHOW ENGINE engine name {LOGS|STATUS} -- shows the log or status information of the storage engine
    -- self-incrementing start number
        AUTO_INCREMENT = number of rows
    -- Data file directory
        DATA DIRECTORY = 'directory'
    -- Index file directory
        INDEX DIRECTORY = 'directory'
    -- table comments
        COMMENT = 'string'
    -- Partitioning options
        PARTITION BY ... (see manual for details)
-- View all tables
    SHOW TABLES[ LIKE 'pattern']
    SHOW TABLES FROM table name
-- View table organization
    SHOW CREATE TABLE table name (more detailed information)
    DESC table name / DESCRIBE table name / EXPLAIN table name / SHOW COLUMNS FROM table name [LIKE 'PATTERN']
    SHOW TABLE STATUS [FROM db_name] [LIKE 'pattern']
-- Modify the table
    -- options for modifying the table itself
        ALTER TABLE table name Table options
        eg: ALTER TABLE table name ENGINE=MYISAM;
    -- rename the table
        RENAME TABLE original table name TO new table name
        RENAME TABLE original table name TO library name. TABLE NAME (can move table to another database)
        -- RENAME can exchange two table names
    -- Modify the field structure of a table (13.1.2. ALTER TABLE syntax)
        ALTER TABLE table name operation name
        -- operation name
            ADD[ COLUMN] field definition -- add field
                AFTER field name -- means add after that field name
                FIRST -- means add in the first
            ADD PRIMARY KEY(field name) -- create primary key
            ADD UNIQUE [index name] (field name) -- creates a unique index
            ADD INDEX [index name] (field name) -- creates a general index
            DROP[ COLUMN] field name -- delete field
            MODIFY[ COLUMN] Field Name Field Attributes -- supports modification of field attributes, cannot modify field name (all original attributes need to be written as well)
            CHANGE[ COLUMN] original field name new field name field attribute -- supports modification of field name
            DROP PRIMARY KEY -- delete the primary key (you need to delete the AUTO_INCREMENT attribute before deleting the primary key)
            DROP INDEX index name -- delete the index
            DROP FOREIGN KEY foreign key -- remove foreign key
-- Delete a table
    DROP TABLE[ IF EXISTS] table name ...
-- Clear table data
    TRUNCATE [TABLE] table name
-- Copy table structure
    CREATE TABLE table name LIKE the name of the table to be copied
-- Duplicate table structure and data
    CREATE TABLE table name [AS] SELECT * FROM table name to be copied
-- Check the table for errors
    CHECK TABLE tbl_name [, tbl_name] ... [option] ...
-- Optimize the table
    OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ...
-- Repair the table
    REPAIR [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ... [QUICK] [EXTENDED] [USE_FRM]
-- Analyze the table
    ANALYZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ...

データ操作

-- increment
    INSERT [INTO] table name [(list of fields)] VALUES (list of values)[, (list of values), ...]
        -- The list of fields can be omitted if the list of values to be inserted contains all fields and is in the same order.
        -- Multiple data rows can be inserted at the same time!
        REPLACE is exactly the same as INSERT and is interchangeable.
    INSERT [INTO] table name SET field name=value [, field name=value, ...]
-- CHECK
    SELECT list of fields FROM table name [ other clauses]
        -- can be multiple fields from multiple tables
        -- Other clauses can be left out
        -- The list of fields can be replaced by *, indicating all fields
-- Delete
    DELETE FROM table name[ delete conditional clause]
        Without the conditional clause, all will be deleted
-- CHANGE
    UPDATE table name SET field name=new value [, field name=new value] [update condition]

文字セット・エンコーディング

-- Encoding can be set for MySQL, database, tables, fields
-- Data encoding does not need to be the same as client encoding
SHOW VARIABLES LIKE 'character_set_%' -- see all character set encoding entries
    character_set_client The encoding used by the client when sending data to the server
    character_set_results The encoding used by the server to return the results to the client
    character_set_connection Connection level encoding
SET variable_name = variable_value
    SET character_set_client = gbk;
    SET character_set_results = gbk;
    SET character_set_connection = gbk;
SET NAMES GBK; -- equivalent to completing the above three settings
-- Proofreading Sets
    The checkset is used to sort
    SHOW CHARACTER SET [LIKE 'pattern']/SHOW CHARSET [LIKE 'pattern'] View all character sets
    SHOW COLLATION [LIKE 'pattern'] View all checksets
    CHARSET Character set encoding Set character set encoding
    COLLATE checkset encoding Set the checkset encoding

データ型（カラム型）

1. Numeric types

-- a. Integer ----------
    Type Byte Range (signed bits)
    tinyint 1 byte -128 ~ 127 unsigned bits: 0 ~ 255
    smallint 2 bytes -32768 ~ 32767
    mediumint 3 bytes -8388608 ~ 8388607
    int 4 bytes
    bigint 8 bytes
    int(M) M means the total number of bits
    - Default presence of signed bits, modified by unsigned property
    - Display width, if a number is not enough to define the number of bits set in the field, then the front is filled with zeros, modified by the zerofill property
        Example: int(5) inserts a number '123', which is filled in as '00123'
    - In the case of meeting the requirements, the smaller the better.
    - MySQL does not have boolean types, but integers 0 and 1. The boolean type is often represented by tinyint(1).

-- b. Floating-point type ----------
    Type Byte Range
    float(single precision) 4 bytes
    double(double precision) 8 bytes
    The floating-point type supports both the unsigned property for sign bits and the zerofill property for display width.
        Unlike integers, the zerofill property is filled with zeros before and after.
    When defining a floating-point type, the total number of bits and the number of decimal places must be specified.
        float(M, D) double(M, D)
        M is the total number of digits and D is the number of decimal digits.
        The size of M and D will determine the range of the floating point number. Unlike integers, which have a fixed range.
        M indicates both the total number of bits (excluding decimal points and plus and minus signs) and the display width (all display symbols are included).
        Scientific notation representation is supported.
        Floating-point numbers indicate approximate values.

-- c. Fixed-point numbers ----------
    decimal -- variable length
    decimal(M, D) M also indicates the total number of digits, D indicates the number of decimal places.
    Saves an exact value without data change, unlike the rounding of floating point numbers.
    Converts a floating point number to a string to save it. Every 9 digits are saved as 4 bytes.

2. String types

-- a. char, varchar ----------
    char fixed-length string, fast, but wastes space
    varchar variable-length string, slow, but space-saving
    M indicates the maximum length that can be stored, which is the number of characters, not bytes.
    Different encodings take up different amount of space.
    char, up to 255 characters, independent of encoding.
    varchar, up to 65535 characters, encoding related.
    A valid record cannot exceed 65535 bytes.
        utf8 is 21844 characters maximum, gbk is 32766 characters maximum, latin1 is 65532 characters maximum
    varchar is variable length and needs to use storage space to save the length of varchar. If the data is less than 255 bytes, one byte is used to save the length, and vice versa two bytes are needed to save it.
    The maximum effective length of a varchar is determined by the maximum row size and the character set used.
    The maximum effective length is 65532 bytes, because when storing a string in varchar, the first byte is empty, there is no data, and then two bytes are needed to store the length of the string, so the effective length is 64432-1-2=65532 bytes.
    Example: If a table is defined as CREATE TABLE tb(c1 int, c2 char(30), c3 varchar(N)) charset=utf8; Ask what is the maximum value of N? A: (65535-1-2-4-30*3)/3

-- b. blob, text ----------
    blob binary string (byte string)
        tinyblob, blob, mediumblob, longblob
    text Non-binary strings (character strings)
        tinytext, text, mediumtext, longtext
    text is defined without defining the length, and the total length is not calculated.
    The text type cannot be given a default value when defined

-- c. binary, varbinary ----------
    Similar to char and varchar, used to hold binary strings, i.e. to hold byte strings instead of character strings.
    char, varchar, text correspond to binary, varbinary, blob.

3. date and time types
    Generally use integer type to save timestamp, because PHP can format timestamp easily.
    datetime 8 bytes date and time 1000-01-01 00:00:00 to 9999-12-31 23:59:59
    date 3 bytes date 1000-01-01 to 9999-12-31
    timestamp 4 bytes timestamp 19700101000000 to 2038-01-19 03:14:07
    time 3 bytes time -838:59:59 to 838:59:59
    year 1 byte year 1901 - 2155
datetime YYYY-MM-DD hh:mm:ss
timestamp YY-MM-DD hh:mm:ss
            YYYYMMDDhhmmss
            YYYYMMDDhhmmss
            YYYYMMDDhhmmss
            YYMMDDhhmmss
date YYYY-MM-DD
            YY-MM-DD
            YYYYMMDD
            YYMMDD
            YYYYMMDD
            YYMMDD
time hh:mm:ss
            hhmmss
            hhmmss
year YYYY
            YY
            YYYY
            YY

4. Enumerations and collections

-- enum(enum) ----------
enum(val1, val2, val3...)
    Make a single selection among known values. The maximum number is 65535.
    Enum values are saved as 2-byte integers (smallint). Each enumerated value is incremented from 1 to 1 in the order in which it is stored.
    It is represented as a string type, but stored as an integer type.
    The index of a NULL value is NULL.
    The index value of an empty string error value is 0.
    
-- set (set) ----------
set(val1, val2, val3...)
    create table tab ( gender set('male', 'female', 'none') );
    insert into tab values ('male, female');
    There can be up to 64 different members. Stored as bigint, 8 bytes in total. Takes the form of bitwise operations.
    Trailing spaces in SET member values are automatically removed when the table is created.

タイプを選択

-- PHP perspective
1. functionally satisfying
2. storage space as small as possible, more efficient processing
3. consider compatibility issues

-- IP storage ----------
1. only need to store, available string
2. if you need to calculate, find, etc., store as 4 bytes unsigned int, i.e. unsigned
    1) PHP function conversions
        ip2long can be converted to integer, but will have problems carrying symbols. It needs to be formatted as an unsigned integer.
        Use the sprintf function to format a string
        sprintf("%u", ip2long('192.168.3.134'));
        Then use long2ip to convert the integer back to an IP string
    2) MySQL function conversion (unsigned integer, UNSIGNED)
        INET_ATON('127.0.0.1') to convert IP to integer
        INET_NTOA(2130706433) convert integer to IP

列の属性（列の制約）

1. PRIMARY Primary Key
    - A field that uniquely identifies a record and can be used as a primary key.
    - A table can have only one primary key.
    - Primary keys are unique.
    - Fields are declared with the primary key identifier.
        It can also be declared after the list of fields
            Example: create table tab ( id int, stu varchar(10), primary key (id));
    - The value of the primary key field cannot be null.
    - The primary key can be composed of multiple fields together. In this case, the method declared after the list of fields is required.
        Example: create table tab ( id int, stu varchar(10), age int, primary key (stu, age)). 2;
2. UNIQUE Unique Index (Unique Constraint)
    Makes it impossible to duplicate the value of a field as well.
3. NULL Constraint
    null is not a data type, it is an attribute of a column.
    Indicates whether the current column can be null, indicating that there is nothing.
    null, is allowed to be null. Default.
    not null, not allowed to be null.
    insert into tab values (null, 'val');
        -- This means that the value of the first field is set to null, depending on whether the field is allowed to be null or not.
4. DEFAULT Default Value Property
    The default value of the current field.
    insert into tab values (default, 'val'); -- This means force the default value.
    create table tab ( add_time timestamp