MySQL Basics-data types and SQL mode-Learning (iii)

0x01

Two directions of MySQL:

Development DBA: Database Design (e-r diagram), SQL development, built-in functions, storage history (stored procedures and storage functions), triggers, Time Scheduler (event Scheduler)
Operations----> Management DBA: Installation, upgrade, backup, recovery, user management, rights management, monitoring, analysis, benchmarking, statement Optimization (SQL statements), data dictionary, configuring servers as needed (server variables: myisam,innodb, cache, log)
SQL Language components:
DDL: Data Definition language
DML: Data Manipulation language
Integrity definition language, part of the DDL feature
Primary KEY constraints, FOREIGN KEY constraints, unique key constraints, conditional constraints, non-null constraints, transaction constraints
View definition: Virtual table, stored SELECT statement
Transaction control:
Embedded SQL and dynamic sql:
DCL: Data Authorization language

The function of the data type:
1. Stored value types
2. Amount of storage space occupied
3. Fixed length, variable length
4. How to be indexed and sorted
5. Is it possible to be indexed

Data dictionary: Dependent systems Catalog (System catalog)
To save metadata on the database server
Meta Data:
The name of the relationship
The names of each field in each relationship
Data type and length of each field
Constraints
The name of the view and the definition of the view on each relationship
Name of authorized user
User's authorization and account information

Data for Statistics classes
Number of each relationship field
Number of rows per relationship
Storage methods for each relationship

The database that holds the metadata
Information_schema
Mysql
Performance_schema

Data type:
Character type
Numeric type
Precise numerical type
Integral type

Decimal Data: Decimal
Approximate numerical type
Single-precision floating-point type
Double-precision floating-point type
Date-Time Type
Date type
Time Type
Date-Time Type
Time stamp
Year
Boolean type
Built-in type
Enum (enum)
Set (SET)

Numeric type:TINYINT SMALLINT mediumint INT BIGINT      DECIMAL     FLOAT Doubal

Character type: char 255 characters          varchar 65,535 characters            tinytext          text            mediumtext         longtext         binary         varbinary     tinyblob

BLOB        Mendiumblob        Longblob ENUM SET

DateTime type: Date time DATETIME TIMESTAMP Year

0x02

Character types (char, varchar, and text) are commonly used property modifiers:
NOT NULL: non-null constraint
Null: Allowed to be empty
Default ' string ': defaults, not for text type
CHARACTER Set Character Set

Show variables like '%char% ' view the default character set
Show Character Set view the character set supported by the database

Show collation sorting rules
Collation ' rules ': modifying collations

Binary,varbinaray and Blob characters common property modifiers
Not NULL
Null
Default does not apply with BLOB

Common property modifiers for shaping
Auto_increment: Automatic growth
Prerequisite: Non-null, and unique, support index
LAST_INSERT_ID (): The last increment can be viewed, when multiple rows are inserted, only the first row of select LAST_INSERT_ID () is recorded;
UNSIGNED: Unsigned
Null
Not NULL
DEFAULT

TRUNCATE is used to clear the data in the table TRUNCATE

Common modifiers for floating-point characters:
Null
Not NULL
Unsignsd
DEFAULT
Use G,f to define how many numbers are in total and how many digits after the decimal point

Date time type commonly used modifiers
Null
Not NULL
DEFAULT

Modifiers for enum and set
Null
Not NULL
DEFAULT ' '

0x03

MySQL has built-in SQL schema Sql_mode: Used to define the operation of the character beyond and to mimic the type of the other database by modifying the global variable
MySQL default mode intercepts data that exceeds the character type char (3) Insert Hello---The end result is that Hel is intercepted
The three commonly used modes of Sql_mode are:
traditional using traditional mode
strict_trans_tables Strict mode only for tables that support transactions
strict_all_tables Use strict mode for all tables
Show global variables like ' sql_mode ';

Set the value of a server variable: typically used only to support dynamic variables
Server variables that support modification
Dynamic variables: Can be modified at MySQL runtime
Static variables: Modify their values in the configuration file and reboot to take effect
Server variables in terms of their effective scope, there are two types of
Global variables: server level, modified only for newly established sessions
Reply variable: Session level, valid only for the current session
When a session is established, the variables are inherited from the global
View server Variables

Format:
Mysql> show [{global|session}] variables [like '];
Mysql> Select @@{globa|session}.variable_name

Instance:
Mysql> mysql> SELECT * from INFORMATION_SCHEMA. Global_variables where variable_name= ' some_variable_name '; View global Variables
Mysql> mysql> SELECT * from INFORMATION_SCHEMA. Session_variables where variable_name= ' some_variable_name '; viewing session Variables

To modify a variable:
Premise: Default only Administrators have permission to modify global variables
Mysql> set {global|session} variable_name= ' value ';
set session sql_mode= ' Strict_all_tables '; ------Change the Sql_mode mode to Strict_all_tables
Change to the above mode and deny write directly to data with more than one character type.
SELECT @ @session. sql_mode;

View:
SELECT * FROM INFORMATION_SCHEMA. Session_variables where variable_name= ' Sql_mode ';
SELECT * FROM INFORMATION_SCHEMA. Global_variables where variable_name= ' Sql_mode ';

Note: Both global and session-level dynamic variable modifications are invalidated after restarting MySQL and want to be permanently valid and can be defined in the Response section in the configuration file [Mysqld]

See what command line arguments are available for the MySQL server variable mysqld--help--verbose

MySQL Basics-data types and SQL mode-Learning (iii)